Arwid ThornströmandGitHub
fde660187a
* added new collection product table * dont remove old * fixed fields * correct number
44 lines
2.0 KiB
SQL
44 lines
2.0 KiB
SQL
-- Drop the table if it exists to start fresh
|
|
DROP TABLE IF EXISTS editorial_collection_products;
|
|
|
|
-- Create editorial_collection_products table
|
|
CREATE TABLE editorial_collection_products (
|
|
id SERIAL PRIMARY KEY,
|
|
editorial_page_id INTEGER NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
print_id INTEGER NOT NULL,
|
|
inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
|
|
-- Foreign key constraints with CASCADE DELETE
|
|
CONSTRAINT fk_editorial_collection_products_editorial_page
|
|
FOREIGN KEY (editorial_page_id) REFERENCES editorial_pages(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_editorial_collection_products_product
|
|
FOREIGN KEY (product_id) REFERENCES "product-products"(productid) ON DELETE CASCADE,
|
|
CONSTRAINT fk_editorial_collection_products_print
|
|
FOREIGN KEY (print_id) REFERENCES "product-printproducts"(printid) ON DELETE CASCADE,
|
|
|
|
-- Ensure unique combination of editorial page, product and print
|
|
UNIQUE(editorial_page_id, product_id, print_id)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_editorial_collection_products_editorial_page_id
|
|
ON editorial_collection_products(editorial_page_id);
|
|
CREATE INDEX idx_editorial_collection_products_product_id
|
|
ON editorial_collection_products(product_id);
|
|
CREATE INDEX idx_editorial_collection_products_print_id
|
|
ON editorial_collection_products(print_id);
|
|
|
|
-- Migrate existing data from editorial_pages metadata
|
|
INSERT INTO editorial_collection_products (editorial_page_id, product_id, print_id)
|
|
SELECT
|
|
ep.id as editorial_page_id,
|
|
CAST(product_id AS INTEGER) as product_id,
|
|
pp.printid
|
|
FROM editorial_pages ep
|
|
CROSS JOIN LATERAL jsonb_array_elements_text(ep.metadata->'collection_products') AS product_id
|
|
INNER JOIN "product-printproducts" pp ON pp.productid = CAST(product_id AS INTEGER)
|
|
WHERE ep.metadata->'collection_products' IS NOT NULL
|
|
AND jsonb_typeof(ep.metadata->'collection_products') = 'array'
|
|
AND jsonb_array_length(ep.metadata->'collection_products') > 0;
|