Hi everyone,
I am trying to use the OpenBoxes API to completely delete some test products and their linked inventory/lot items so I can start fresh.
Every time I try to run a delete command on a product, the API blocks it and throws a 500 server error saying the resource cannot be deleted because of an existing association.
What is the correct way or sequence to completely delete products and inventory items using the API?
Thanks,
Mustafa
Admittedly, this is harder than it needs to be via the API. But it hasn’t been implemented on purpose since it’s a very destructive operation.
In the simple/happy case, the DELETE HTTP method should work on the products resource if there are no other associations on the given product.
curl --location --request DELETE 'https://my.openboxes.com/openboxes/api/products/ff808181a0aa717001a0ab43369e0000' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic ••••••' \
--header 'Cookie: JSESSIONID=*******'
You should get a 204 No Content if it works properly.
The problem is when a Product record is associated with other objects in the database (e.g. transactions, inventory items, etc). Here’s a full list of tables that are associated with a product and/or inventory item.
| Category | Table | FK column(s) | References |
|---|---|---|---|
| Analytical | inventory_snapshot | product_id, inventory_item_id | product, inventory_item |
| Analytical | lot_dimension | inventory_item_id | inventory_item |
| Analytical | product_availability | product_id, inventory_item_id | product, inventory_item |
| Analytical | product_dimension | product_id | product |
| Auditing | product_merge_logger | primary_product_id, obsolete_product_id | product |
| Core | inventory_item | product_id | product |
| Reference | inventory_level | product_id | product |
| Reference | product_association | product_id, associated_product_id | product |
| Reference | product_attribute | product_id | product |
| Reference | product_catalog_item | product_id | product |
| Reference | product_component | assembly_product_id, component_product_id | product |
| Reference | product_document | product_id | product |
| Reference | product_group_product | product_id | product |
| Reference | product_package | product_id | product |
| Reference | product_supplier | product_id | product |
| Reference | product_tag | product_id | product |
| Reference | synonym | product_id | product |
| Transactional | consumption | product_id, inventory_item_id | product, inventory_item |
| Transactional | cycle_count_item | product_id, inventory_item_id | product, inventory_item |
| Transactional | cycle_count_request | product_id | product |
| Transactional | fulfillment_item | inventory_item_id | inventory_item |
| Transactional | invoice_item | product_id | product |
| Transactional | order_item | product_id, inventory_item_id | product, inventory_item |
| Transactional | picklist_item | inventory_item_id | inventory_item |
| Transactional | receipt_item | product_id, inventory_item_id | product, inventory_item |
| Transactional | requisition_item | product_id, inventory_item_id | product, inventory_item |
| Transactional | shipment_item | product_id, inventory_item_id | product, inventory_item |
| Transactional | transaction_entry | product_id, inventory_item_id | product, inventory_item |
The reason this fails is because GenericApiService.deleteObject() endpoint just calls domainObject.delete() with no cascade options. In addition, the Product domain does not configure cascade delete on most of the associations (some are there, so you might get lucky sending a delete with the 13 or so associations that are set to cascade-delete-orphan). But for the other associations, we were a little nervous that people would do bad things by accident so we stopped adding the cascade. Therefore, on delete of a product every record in the database that has a foreign key to Product or InventoryItem will not be cascade deleted and as a consequence, Hibernate probably throws a DataIntegrityViolationException which manifests as a 500 Internal Server Error.
curl --location --request DELETE 'https://openboxes2.ngrok.io/openboxes/api/products/ff808181a0aa717001a0ab682e990003' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--header 'Cookie: JSESSIONID=****************'
{
"errorCode": 500,
"errorMessage": "Resource could not be deleted because of an existing association"
}
In the short term, I don’t think there’s a good way to do the cascade delete through the Generic API unless you have lots patience and determination. You would likely need to fetch every object from every table that is associated with the product table (see table above), iterate over every record (we don’t have a way to filter these by product yet), determine whether it’s associated with the given product, delete the record, and then once all of the dependent records are deleted we can delete the inventory item and products. 
Again, this should be something we allow through the API (with the right permissions), so I’m going to create a GitHub issue for it. With the error message above, we could/should include more information on what associations exist and would need to be deleted, including their IDs. That will ease the cascade delete exercise and give you a list of objects that need to be deleted. But what we really probably want to do is return a 400 Bad Request with validation errors and then allow you to
DELETE /api/products/ff808181a0aa717001a0ab682e990003?force=true
I’ll consider both of these options for the ticket I’m going to create for this request, but that’s going to need a thorough design and security review before it can be implemented.
So getting back to the problem. From here you have a few options.
1. You can attempt to delete the product from the database.
DELETE /api/products/ff808181a0aa717001a0ab682e990003
2. If you encounter a 500 Internal Server Error, you can soft-delete using a PUT request, but I assume that’s not sufficient.
curl --location --request PUT 'https://my.openboxes.com/openboxes/api/products/ff808181a0aa717001a0ab43369e0000' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic ••••••' \
--header 'Cookie: JSESSIONID=****************' \
--data '{
"active": "true"
}'
3. You could execute a relatively long (straightforward) sequence of SQL statements on the database to ensure you delete all associated records. I have not tested this, but it seems like it covers most, if not all of the associations you’ll encounter. The problem is that you might leave a lot of data (orphaned requisitions) that should not be left.
SET @product_id = 'ff808181a0aa717001a0ab43369e0000';
-- Tier 1: inventory_item_id only, no product_id column
DELETE FROM lot_dimension WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = @product_id);
DELETE FROM fulfillment_item WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = @product_id);
DELETE FROM picklist_item WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = @product_id);
-- Tier 2: has product_id directly; ordered so invoice_item/receipt_item go
-- before shipment_item, and shipment_item before order_item
DELETE FROM consumption WHERE product_id = @product_id;
DELETE FROM cycle_count_item WHERE product_id = @product_id;
DELETE FROM inventory_snapshot WHERE product_id = @product_id;
DELETE FROM product_availability WHERE product_id = @product_id;
DELETE FROM transaction_entry WHERE product_id = @product_id;
DELETE FROM requisition_item WHERE product_id = @product_id;
DELETE FROM invoice_item WHERE product_id = @product_id;
DELETE FROM receipt_item WHERE product_id = @product_id;
DELETE FROM shipment_item WHERE product_id = @product_id;
DELETE FROM order_item WHERE product_id = @product_id;
-- Tier 3: now safe — nothing left pointing at its rows
DELETE FROM inventory_item WHERE product_id = @product_id;
-- Tier 4: pure product_id/self-referential tables, no inventory_item involvement
DELETE FROM cycle_count_request WHERE product_id = @product_id;
DELETE FROM inventory_level WHERE product_id = @product_id;
DELETE FROM product_association WHERE product_id = @product_id OR associated_product_id = @product_id;
DELETE FROM product_attribute WHERE product_id = @product_id;
DELETE FROM product_catalog_item WHERE product_id = @product_id;
DELETE FROM product_component WHERE component_product_id = @product_id OR assembly_product_id = @product_id;
DELETE FROM product_dimension WHERE product_id = @product_id;
DELETE FROM product_document WHERE product_id = @product_id;
DELETE FROM product_group_product WHERE product_id = @product_id;
DELETE FROM product_merge_logger WHERE obsolete_product_id = @product_id OR primary_product_id = @product_id;
DELETE FROM product_package WHERE product_id = @product_id;
DELETE FROM product_supplier WHERE product_id = @product_id;
DELETE FROM product_tag WHERE product_id = @product_id;
DELETE FROM synonym WHERE product_id = @product_id;
-- Tier 5
DELETE FROM product WHERE id = @product_id;
4. And if you’re impatient and need something to work today, you could (but I really wouldn’t) put a few of these solutions together to create your own hard-delete i.e. execute the SQL approach via a database trigger that is conditionally executed off the soft-delete PUT request I mentioned earlier (i.e. when product becomes inactive you delete it).
Here’s the trigger definition. I added some additional protection (ABC Class property) but you can remove that if you’d like.
DELIMITER $$
CREATE TRIGGER trg_delete_product_cascade
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
IF NEW.active = 0 AND OLD.active = 1 AND NEW.abcClass = '__DELETE__' THEN
CALL delete_product_cascade(NEW.id, NULL);
END IF;
END$$
DELIMITER ;
Verify that the trigger has been created
SHOW TRIGGERS FROM openboxes LIKE 'product';
Create the stored procedure (see below).
Call the stored procedure by product ID or product code
CALL delete_product_cascade('ff808181a0aa717001a0ab682e990003');
CALL delete_product_cascade('PROD-001');
If that works, then you could test that the trigger gets executed properly on the appropriate update SQL
UPDATE product
SET active = false, abcClass = '__DELETE__'
WHERE product_code = 'PROD-001';
And lastly the Product API request would look like this
curl --location --request PUT 'https://my.openboxes.com/openboxes/api/products/ff808181a0aa717001a0ab682e990003' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic ••••••' \
--header 'Cookie: JSESSIONID=******' \
--data '{
"active": "false",
"abcClass": "__DELETE__"
}'
And voila! Hopefully.
Here’s the stored procedure definition (I wanted to leave this until the end of the post since it’s a larger block of text.
DELIMITER $$
CREATE PROCEDURE delete_product_cascade(
IN p_product_identifier VARCHAR(255)
)
BEGIN
DECLARE v_product_id VARCHAR(255);
-- Try as id first, fall back to productCode
SELECT id INTO v_product_id FROM product WHERE id = p_product_identifier LIMIT 1;
IF v_product_id IS NULL THEN
SELECT id INTO v_product_id FROM product WHERE productCode = p_product_identifier LIMIT 1;
END IF;
IF v_product_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found by id or productCode';
END IF;
START TRANSACTION;
DELETE FROM lot_dimension WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = v_product_id);
DELETE FROM fulfillment_item WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = v_product_id);
DELETE FROM picklist_item WHERE inventory_item_id IN (SELECT id FROM inventory_item WHERE product_id = v_product_id);
DELETE FROM consumption WHERE product_id = v_product_id;
DELETE FROM cycle_count_item WHERE product_id = v_product_id;
DELETE FROM inventory_snapshot WHERE product_id = v_product_id;
DELETE FROM product_availability WHERE product_id = v_product_id;
DELETE FROM transaction_entry WHERE product_id = v_product_id;
DELETE FROM requisition_item WHERE product_id = v_product_id;
DELETE FROM invoice_item WHERE product_id = v_product_id;
DELETE FROM receipt_item WHERE product_id = v_product_id;
DELETE FROM shipment_item WHERE product_id = v_product_id;
DELETE FROM order_item
Finally, just make sure you drop the trigger and stored procedure once you delete all of the products you want to delete so those aren’t left around for someone to accidentally use.
DROP TRIGGER IF EXISTS trg_delete_product_cascade;
DROP PROCEDURE IF EXISTS delete_product_cascade;