Forum OpenACS Q&A: Deleting categories in ecommerce

Posted by Dave Bauer on 12/17/00 09:35 PM

When I attempt to remove a category from the ecommerce module,
everything goes appears to work fine, but the category is still
there. All the items are still in the categories. No errors are
reported. I set LogSQLErrors and Verbose to "on" and there are no
errors in the log.

Has anyone else seen this before? I am using Postgres 7.0.3 and
OpenACS 3.2.4 and Aolserver 3.2.

2: Response to Deleting categories in ecommerce (response to 1)

Posted by Don Baccus on 12/17/00 11:08 PM

Is there a "delete" query in the log file? If so, could you post it here?

3: Response to Deleting categories in ecommerce (response to 1)

Posted by Dave Bauer on 12/18/00 01:47 AM

I think this is the relevant part. It is very long. It is everything between the "begin transacation" and "end transaction"

[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'begin transaction;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Ns_PgExec: Entering transaction
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'begin transaction'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2);'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2)'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'select subcategory_id from ec_subcategories where category_id=2;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'select subcategory_id from ec_subcategories where category_id=2'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_subsubcategory_product_map 
where subsubcategory_id in (select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2));'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_subsubcategory_product_map 
where subsubcategory_id in (select subsubcategory_id from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2))'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2);'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_subsubcategories where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2)'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_subcategory_product_map
where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2);'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_subcategory_product_map
where subcategory_id in (select subcategory_id from ec_subcategories where category_id=2)'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_subcat_prod_map_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_subcat_prod_map_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_subcat_prod_map_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (1, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_subcat_prod_map_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (1, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_subcategories where category_id=2;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_subcategories where category_id=2'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_subcategories_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_subcategories_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_subcategories_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (1, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_subcategories_audit
    (subcategory_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (1, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_category_product_map where category_id=2;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_category_product_map where category_id=2'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_category_product_map_audit
    (category_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_category_product_map_audit
    (category_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_category_template_map where category_id=2;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_category_template_map where category_id=2'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'delete from ec_categories where category_id=2;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'delete from ec_categories where category_id=2'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'insert into ec_categories_audit
    (category_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t');'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'insert into ec_categories_audit
    (category_id, last_modified, 
    last_modifying_user, modified_ip_address, delete_p)
    values
    (2, sysdate(), 
    '6', '216.254.100.170','t')
    '
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'end transaction;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Ns_PgExec: Committing transaction
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'end transaction'

Here is what came immediately after:

[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'select ad_group_member_p(6, system_administrator_group_id()) from dual;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'select ad_group_member_p(6, system_administrator_group_id()) from dual'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: Querying 'select category_id, sort_key, category_name from ec_categories order by sort_key;'
[17/Dec/2000:14:33:05][20999.5125][-conn1-] Notice: dbinit: sql(localhost::nh): 'select category_id, sort_key, category_name from ec_categories order by sort_key'

Thanks for any assistance.

4: Response to Deleting categories in ecommerce (response to 1)

Posted by Dan Wickstrom on 12/18/00 04:15 AM

I think your problem is related to this thread: https://openacs.org/forums/message-view?message_id=16942

This problem has been fixed in the latest cvs sources.

5: Response to Deleting categories in ecommerce (response to 1)

Posted by Dave Bauer on 12/18/00 04:50 AM

Not to be a pain, but as I see it, I will either have to drop all the triggers and then create new ones, or start over with a fresh database and reinstall. Is this correct? I don't have alot invested in the db yet, so I will probably just drop and recreate the whole thing.

6: Response to Deleting categories in ecommerce (response to 1)

Posted by Dan Wickstrom on 12/18/00 04:18 PM

Either way is fine, but it would be fairly simple to create an upgrade.sql script that contains the appropriate drop and create trigger statements to change the triggers from before to after triggers. You could then just do: psql -f upgrade.sql and you would be up and running without having to reload any data.

7: Response to Deleting categories in ecommerce (response to 1)

Posted by Dave Bauer on 02/10/01 03:51 AM

Hi I fixed all my triggers from before to after. I kept a log of the SQL results and there were no errors.

The problem I am having is if there is info in ec_user_sessions about product or category I get this error:

[10/Feb/2001:02:40:21][25652.1073157][-conn7-] Error: Ns_PgExec: result status: 7 message: ERROR: <unnamed> referential integrity violation - key in ec_categories still referenced from ec_user_session_info

[10/Feb/2001:02:40:21][25652.1073157][-conn7-] Error: dbinit: error(localhost::nh,ERROR: <unnamed> referential integrity violation - key in ec_categories still referenced from ec_user_session_info
): 'delete from ec_categories where category_id=1'

[10/Feb/2001:02:40:21][25652.1073157][-conn7-] Error: Database operation "dml" failed
Database operation "dml" failed
while executing
"ns_db dml $db "delete from ec_categories where category_id=$category_id""
(file "/web/nh/www/admin/ecommerce/cat/category-delete-2.tcl" line 94)
invoked from within
"source $script"
(procedure "ns_sourceproc" line 6)
invoked from within
"ns_sourceproc cns35 {}"

Any ideas? I understand why it fails, but shouldn't this be taken care of by OpenACS to not allow the deleting without causing a database error message?

8: Response to Deleting categories in ecommerce (response to 1)

Posted by Dave Bauer on 02/10/01 04:16 AM

OK. I looked into this more.

www/admin/ecommerce/cat/category-delete-2.tcl

deletes from quite a few table to make sure no contrainsts are violated. There is no mention of ec_user_session_info in that file.

I cannot find any information about what ec_useer_session_info is actually used for. I don't think it is necessary to save the product and category information in this table. I am not even sure how long it stays in this table anyway.

Should we be removing the references to category and product ids from this table? Basically any time a visitor looks at a product page or category page, it is logged in this table. I cannot remove a product or category if anyone has looked at its page. At least that is what appears to be happening to me.

9: Response to Deleting categories in ecommerce (response to 1)

Posted by Dan Wickstrom on 02/10/01 03:57 PM

Search on ec_user_session_info in www/admin/ecommerce/customer-service/spam-2.tcl and www/admin/ecommerce/customer-service/spam-3.tcl. It looks like the information is used to spam users who have recently viewed a product or category. The fact that you can't delete a product or category seems like a design oversight. Since you only need the ec_user_session_info for creating ecommerce spam, you should be able a add triggers to delete the corresponding entries from the ec_user_session_info table when deleting a product or category.

10: Response to Deleting categories in ecommerce (response to 1)

Posted by Dave Bauer on 02/10/01 06:45 PM

Thanks. I thought it was just an oversight in the design.