48 lines
2.9 KiB
SQL
48 lines
2.9 KiB
SQL
DROP TABLE IF EXISTS editorial_pages CASCADE;
|
|
DROP TABLE IF EXISTS editorial_content CASCADE;
|
|
DROP TYPE IF EXISTS editorial_type CASCADE;
|
|
|
|
-- editorial_pages
|
|
|
|
CREATE TABLE editorial_pages as (SELECT * FROM campaign_pages);
|
|
COMMENT ON COLUMN editorial_pages.name IS 'Used as title in admin only';
|
|
ALTER TABLE editorial_pages ADD CONSTRAINT editorial_pages_primary_key PRIMARY KEY (id);
|
|
ALTER TABLE editorial_pages ALTER COLUMN inserted SET DEFAULT now();
|
|
ALTER TABLE editorial_pages ALTER COLUMN inserted SET NOT NULL;
|
|
ALTER TABLE editorial_pages ALTER COLUMN name SET NOT NULL;
|
|
ALTER TABLE editorial_pages ALTER COLUMN description SET NOT NULL;
|
|
ALTER TABLE editorial_pages ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{"profile_image": null, "listing_products": []}';
|
|
CREATE TYPE editorial_type AS ENUM ('campaign', 'influencer_collection');
|
|
ALTER TABLE editorial_pages ADD COLUMN IF NOT EXISTS type editorial_type NOT NULL DEFAULT 'campaign';
|
|
|
|
-- create sequence on primary key
|
|
CREATE SEQUENCE editorial_pages_id_seq OWNED BY editorial_pages.id;
|
|
SELECT SETVAL('editorial_pages_id_seq', (select max(id) + 1 from editorial_pages), false);
|
|
ALTER TABLE editorial_pages ALTER COLUMN id SET DEFAULT nextval('editorial_pages_id_seq');
|
|
|
|
-- editorial_content
|
|
|
|
CREATE TABLE editorial_content as (SELECT * FROM campaign_page_content);
|
|
ALTER TABLE editorial_content ADD CONSTRAINT editorial_content_primary_key PRIMARY KEY (id);
|
|
ALTER TABLE editorial_content ALTER COLUMN inserted SET DEFAULT now();
|
|
ALTER TABLE editorial_content ALTER COLUMN inserted SET NOT NULL;
|
|
ALTER TABLE editorial_content ALTER COLUMN market_locale_id SET NOT NULL;
|
|
ALTER TABLE editorial_content ADD COLUMN IF NOT EXISTS highlighted BOOL NOT NULL DEFAULT 'false';
|
|
ALTER TABLE editorial_content RENAME COLUMN campaign_page_id TO editorial_page_id;
|
|
ALTER TABLE editorial_content ALTER COLUMN editorial_page_id SET NOT NULL;
|
|
ALTER TABLE editorial_content ADD CONSTRAINT editorial_foreign_key FOREIGN KEY (editorial_page_id) REFERENCES editorial_pages (id);
|
|
ALTER TABLE editorial_content ADD CONSTRAINT editorial_market_foreign_key FOREIGN KEY (market_locale_id) REFERENCES market_locales (id);
|
|
ALTER TABLE editorial_content ADD CONSTRAINT editorial_content_unique_key UNIQUE (slug, market_locale_id);
|
|
ALTER TABLE editorial_content ALTER COLUMN noindex SET DEFAULT false;
|
|
|
|
-- create sequence on primary key
|
|
CREATE SEQUENCE editorial_content_id_seq OWNED BY editorial_content.id;
|
|
SELECT SETVAL('editorial_content_id_seq', (select max(id) + 1 from editorial_content), false);
|
|
ALTER TABLE editorial_content ALTER COLUMN id SET DEFAULT nextval('editorial_content_id_seq');
|
|
|
|
CREATE INDEX ON editorial_content(editorial_page_id);
|
|
CREATE INDEX ON editorial_content(market_locale_id);
|
|
|
|
CREATE TRIGGER updated_timestamp BEFORE UPDATE ON public.editorial_pages FOR EACH ROW EXECUTE PROCEDURE public.updated_timestamp();
|
|
CREATE TRIGGER updated_timestamp BEFORE UPDATE ON public.editorial_content FOR EACH ROW EXECUTE PROCEDURE public.updated_timestamp();
|