Forum OpenACS Q&A: An fk (from my table to apm_packages) issue maybe?

Hi, trying to see why my package won't delete cleanly...

I'll post detail messages after this one, and I will make this one an abstract.

I have a table for websites, with two of the fields being site_id (the pk) and package_id (an fk), the latter being the object ID of the apm_package instance that gets created when a site package is instantiated.

So, the package object is one object ID, while the website is a different object which has a pointer to the package.

When trying to delete the package, if I don't first delete the row in the sites table, the package won't delete due to an fk involving context_id. I'll post the relevent parts of the websites table in a subsequent message in this thread.

I found this while writing an automated test which will create and destroy a website; the test now has code that specifically deletes the websites row. After running this code, it then deletes the package and site node.

-Jim, more in a sec...

Posted by Jim Lynch on
create table websites
    site_id integer
      constraint sts__site_id__pk
        primary key
      constraint sts__site_id__acs_objects_fk
        references acs_objects(object_id)
      on delete cascade,
    -- associate with which package instance, so that each page can quickly determine site
    package_id integer
      constraint ws__package_id__un
      constraint ws__package_id__nn
        not null
      constraint ws__package_id__acs_objects__fk
        references acs_objects(object_id)
      on delete cascade

Posted by Jim Lynch on
# the automated test:

aa_register_case -cats {smoke} creating_a_site {

    this finds out if there is any problem with creating a site,
    and having created one, is everything pointed correctly

    @author jiml Jim Lynch
} {
    aa_run_with_teardown  -test_code {
        set root_node_id [site_node::get_node_id -url "/"]

        set nod_nam "aaaa-[ns_mktemp XXXXXX]"

        set my_node_id \
            [site_node::new \
                 -name $nod_nam \
                 -parent_id $root_node_id]

        set my_pkg_id \
            [site_node::instantiate_and_mount \
                -package_key website \
                -node_name $nod_nam \
                -context_id $root_node_id]

        aa_true site_row_exists [db_0or1row get_site {
                simple_twopage_sites sts
                sts.package_id = :my_pkg_id

        # site should now exist. do stuff to it
        # (nothing here yet)
        # now tear down site.

        #db_0or1row delete_site {
        #    select
        #        website__delete(:site_id)

        site_node_delete_package_instance -node_id $my_node_id

        aa_false site_gone [db_0or1row site_gone_test {
                acs_objects o
                o.object_id = :site_id
    } -teardown_code {
        site_node::update_cache \
            -node_id [site_node::get_node_id -url "/"]

Posted by Dave Bauer on
For a mapping table, that doesn't contain object rows, just use on delete cascade on your foreign key constraints.

If you have a table of objects that has a foreign key to another table of objects, due to historical limitations, you'll need to acs_object__delete the dependant objects first.

For example any objects that have acs_objects.package_id (or context_id) as the package_id you want to delete must be acs_object__deleted before the package is deleted.

You'd have to put that in a before-uninstantiate callback that gets called when a package instance is deleted.

Posted by Jim Lynch on
Thanks Dave; that took care of it