-- qbit-cms reference DDL (PostgreSQL), generated from the qbit's Liquibase changelog -- (src/main/resources/qbit-cms/db/changelog/qbit-cms.changelog.yaml) with cms.tablePrefix=cms_ and every feature on. -- For hosts that do not run Liquibase. The changelog is the source of truth; regenerate on schema changes. CREATE TABLE public.cms_author ( id bigint NOT NULL, name character varying(40) NOT NULL, short_name character varying(20) NOT NULL, description character varying(255), slug character varying(40) NOT NULL, email character varying(255), profile_media_id bigint, profile_image_url character varying(500), website character varying(255), social_links text, is_active boolean, display_order integer, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_author ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_author_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog ( id bigint NOT NULL, short_name character varying(20) NOT NULL, name character varying(40) NOT NULL, description character varying(255), slug character varying(40) NOT NULL, blog_type_id bigint NOT NULL, meta_title character varying(255), meta_description character varying(500), meta_keywords character varying(255), site_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_post ( id bigint NOT NULL, name character varying(40) NOT NULL, short_name character varying(20) NOT NULL, description character varying(255), title character varying(100), slug character varying(100) NOT NULL, content text NOT NULL, author_id bigint NOT NULL, blog_id bigint NOT NULL, is_published boolean, publish_date timestamp without time zone, featured_media_id bigint, featured_image character varying(255), meta_title character varying(255), meta_description character varying(500), meta_keywords character varying(255), read_time integer, view_count bigint, is_featured boolean NOT NULL, excerpt text, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog_post ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_post_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_post_media ( id bigint NOT NULL, blog_post_id bigint NOT NULL, media_id bigint NOT NULL, sort_order integer NOT NULL, alt_text character varying(1000), caption character varying(2000), url character varying(1000) NOT NULL, thumbnail_url character varying(1000), width integer, height integer, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog_post_media ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_post_media_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_post_tag_int ( id bigint NOT NULL, blog_post_id bigint NOT NULL, blog_tag_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog_post_tag_int ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_post_tag_int_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_post_version ( id bigint NOT NULL, blog_post_id bigint, project_id bigint, project_update_id bigint, name character varying(100) NOT NULL, variant_key character varying(100) NOT NULL, channel character varying(40) NOT NULL, title character varying(200), content text NOT NULL, content_format character varying(20) NOT NULL, status character varying(20) NOT NULL, source_digest character varying(64), source_commit character varying(64), published_url character varying(2000), published_date timestamp without time zone, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL, CONSTRAINT ck_cms_blog_post_version_channel CHECK (((channel)::text = ANY ((ARRAY['WEBSITE'::character varying, 'LINKEDIN'::character varying, 'MEDIUM'::character varying, 'FACEBOOK'::character varying, 'OTHER'::character varying])::text[]))), CONSTRAINT ck_cms_blog_post_version_content_format CHECK (((content_format)::text = ANY ((ARRAY['MARKDOWN'::character varying, 'PLAIN_TEXT'::character varying])::text[]))), CONSTRAINT ck_cms_blog_post_version_status CHECK (((status)::text = ANY ((ARRAY['DRAFT'::character varying, 'READY'::character varying, 'PUBLISHED'::character varying, 'ARCHIVED'::character varying])::text[]))) ); ALTER TABLE public.cms_blog_post_version ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_post_version_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_tag ( id bigint NOT NULL, name character varying(40) NOT NULL, slug character varying(40) NOT NULL, description character varying(255), blog_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog_tag ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_tag_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_blog_type ( id bigint NOT NULL, name character varying(100) NOT NULL, code character varying(50) NOT NULL, description character varying(255), is_active boolean NOT NULL, sort_order integer NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_blog_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_blog_type_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_domain ( id bigint NOT NULL, name character varying(40) NOT NULL, short_name character varying(20) NOT NULL, description character varying(255), fqdn character varying(255) NOT NULL, is_primary boolean NOT NULL, site_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_domain ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_domain_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_media ( id bigint NOT NULL, name character varying(200) NOT NULL, slug character varying(100), description character varying(500), alt_text character varying(1000), caption character varying(2000), credit character varying(500), license character varying(500), media_type_id bigint NOT NULL, file_name character varying(255), file_extension character varying(20), mime_type character varying(100), file_size bigint, width integer, height integer, duration integer, checksum character varying(64), is_active boolean NOT NULL, public_url character varying(500), storage_key character varying(500), thumbnail_url character varying(500), gallery_url character varying(500), slideshow_url character varying(500), site_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_media ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_media_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_media_type ( id bigint NOT NULL, name character varying(100) NOT NULL, code character varying(50) NOT NULL, description character varying(500), is_active boolean NOT NULL, sort_order integer NOT NULL, allowed_mime_types character varying(1000), allowed_extensions character varying(500), max_file_size_mb integer, thumbnail_enabled boolean NOT NULL, thumbnail_width integer, thumbnail_format character varying(10) NOT NULL, thumbnail_quality integer, gallery_enabled boolean NOT NULL, gallery_width integer, gallery_format character varying(10) NOT NULL, gallery_quality integer, slideshow_enabled boolean NOT NULL, slideshow_width integer, slideshow_format character varying(10) NOT NULL, slideshow_quality integer, strip_metadata boolean NOT NULL, require_alt_text boolean NOT NULL, require_caption boolean NOT NULL, default_credit character varying(500), default_license character varying(500), cache_class character varying(20) NOT NULL, storage_prefix character varying(100) NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL, CONSTRAINT ck_cms_media_type_cache_class CHECK (((cache_class)::text = ANY ((ARRAY['IMMUTABLE'::character varying, 'MUTABLE'::character varying])::text[]))), CONSTRAINT ck_cms_media_type_gallery_format CHECK (((gallery_format)::text = ANY ((ARRAY['JPEG'::character varying, 'PNG'::character varying, 'WEBP'::character varying])::text[]))), CONSTRAINT ck_cms_media_type_max_file_size_mb CHECK (((max_file_size_mb IS NULL) OR (max_file_size_mb > 0))), CONSTRAINT ck_cms_media_type_slideshow_format CHECK (((slideshow_format)::text = ANY ((ARRAY['JPEG'::character varying, 'PNG'::character varying, 'WEBP'::character varying])::text[]))), CONSTRAINT ck_cms_media_type_thumbnail_format CHECK (((thumbnail_format)::text = ANY ((ARRAY['JPEG'::character varying, 'PNG'::character varying, 'WEBP'::character varying])::text[]))) ); ALTER TABLE public.cms_media_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_media_type_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_menu ( id bigint NOT NULL, name character varying(100) NOT NULL, slug character varying(100) NOT NULL, description character varying(255), site_id bigint NOT NULL, menu_type character varying(50) NOT NULL, is_active boolean, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_menu ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_menu_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_menu_item ( id bigint NOT NULL, menu_id bigint NOT NULL, parent_id bigint, name character varying(100) NOT NULL, slug character varying(100) NOT NULL, item_type character varying(50) NOT NULL, link_url character varying(500), route_path character varying(200), target character varying(20), icon character varying(100), description character varying(255), display_order integer, is_active boolean, css_classes character varying(200), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_menu_item ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_menu_item_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_page ( id bigint NOT NULL, name character varying(100) NOT NULL, slug character varying(100) NOT NULL, title character varying(200) NOT NULL, content text, page_type_id bigint NOT NULL, template character varying(50), is_published boolean NOT NULL, publish_date timestamp without time zone, sort_order integer NOT NULL, parent_page_id bigint, site_id bigint NOT NULL, meta_description character varying(500), meta_keywords character varying(255), og_image character varying(255), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_page ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_page_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_page_section ( id bigint NOT NULL, name character varying(100) NOT NULL, css_class_name character varying(255), section_type_id bigint NOT NULL, content text, sort_order integer NOT NULL, page_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_page_section ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_page_section_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_page_section_type ( id bigint NOT NULL, name character varying(100) NOT NULL, code character varying(50) NOT NULL, description character varying(255), is_active boolean NOT NULL, sort_order integer NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_page_section_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_page_section_type_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_page_type ( id bigint NOT NULL, name character varying(100) NOT NULL, code character varying(50) NOT NULL, description character varying(255), is_active boolean NOT NULL, sort_order integer NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_page_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_page_type_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_photo ( id bigint NOT NULL, photo_album_id bigint NOT NULL, media_id bigint, title character varying(200) NOT NULL, slug character varying(100) NOT NULL, caption character varying(500), description character varying(1000), score integer, is_favorite boolean NOT NULL, is_album_cover boolean NOT NULL, sort_order integer NOT NULL, is_published boolean NOT NULL, publish_date timestamp without time zone, original_url character varying(500), gallery_url character varying(500), slideshow_url character varying(500), thumbnail_url character varying(500), original_filename character varying(255), storage_key character varying(500), mime_type character varying(100), file_size bigint, width integer, height integer, exif_date_taken timestamp without time zone, exif_camera_make character varying(100), exif_camera_model character varying(100), exif_lens_model character varying(200), exif_focal_length character varying(20), exif_aperture character varying(20), exif_shutter_speed character varying(20), exif_iso integer, exif_white_balance character varying(50), exif_flash character varying(50), exif_gps_latitude double precision, exif_gps_longitude double precision, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); CREATE TABLE public.cms_photo_album ( id bigint NOT NULL, site_id bigint NOT NULL, photo_album_folder_id bigint, project_id bigint, project_update_id bigint, name character varying(100) NOT NULL, short_name character varying(40) NOT NULL, slug character varying(100) NOT NULL, title character varying(200), description character varying(1000), cover_photo_id bigint, cover_image_url character varying(500), is_published boolean NOT NULL, publish_date timestamp without time zone, sort_order integer NOT NULL, location character varying(200), location_latitude double precision, location_longitude double precision, album_date timestamp without time zone, album_end_date timestamp without time zone, primary_camera character varying(100), primary_lens character varying(100), weather character varying(100), meta_title character varying(255), meta_description character varying(500), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); CREATE TABLE public.cms_photo_album_folder ( id bigint NOT NULL, site_id bigint NOT NULL, parent_id bigint, name character varying(100) NOT NULL, slug character varying(100) NOT NULL, description character varying(500), folder_image_url character varying(500), is_published boolean NOT NULL, sort_order integer NOT NULL, meta_title character varying(255), meta_description character varying(500), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_photo_album_folder ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_photo_album_folder_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); ALTER TABLE public.cms_photo_album ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_photo_album_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); ALTER TABLE public.cms_photo ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_photo_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_photo_tag ( id bigint NOT NULL, name character varying(100) NOT NULL, slug character varying(100) NOT NULL, description character varying(255), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_photo_tag ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_photo_tag_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_photo_tag_int ( id bigint NOT NULL, photo_id bigint NOT NULL, photo_tag_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_photo_tag_int ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_photo_tag_int_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_project ( id bigint NOT NULL, name character varying(100) NOT NULL, short_name character varying(20) NOT NULL, slug character varying(100) NOT NULL, title character varying(200) NOT NULL, description text, content text, project_status character varying(20) NOT NULL, github_url character varying(255), live_url character varying(255), featured_media_id bigint, featured_image character varying(255), image_alt character varying(500), image_caption character varying(1000), image_focal_x integer, image_focal_y integer, start_date timestamp without time zone, end_date timestamp without time zone, is_featured boolean NOT NULL, sort_order integer NOT NULL, site_id bigint NOT NULL, category_id bigint NOT NULL, is_published boolean NOT NULL, publish_date timestamp without time zone, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL, CONSTRAINT ck_cms_project_image_focal_x CHECK (((image_focal_x >= 0) AND (image_focal_x <= 100))), CONSTRAINT ck_cms_project_image_focal_y CHECK (((image_focal_y >= 0) AND (image_focal_y <= 100))), CONSTRAINT ck_cms_project_project_status CHECK (((project_status)::text = ANY ((ARRAY['PLANNED'::character varying, 'IN_PROGRESS'::character varying, 'ACTIVE'::character varying, 'ARCHIVED'::character varying])::text[]))) ); CREATE TABLE public.cms_project_category ( id bigint NOT NULL, site_id bigint NOT NULL, name character varying(100) NOT NULL, title character varying(200) NOT NULL, slug character varying(100) NOT NULL, section character varying(20), description character varying(1000), featured_media_id bigint, featured_image character varying(500), sort_order integer NOT NULL, is_published boolean NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_project_category ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_project_category_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); ALTER TABLE public.cms_project ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_project_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_project_update ( id bigint NOT NULL, project_id bigint NOT NULL, slug character varying(100) NOT NULL, title character varying(200) NOT NULL, excerpt character varying(1000), content text NOT NULL, update_date timestamp without time zone NOT NULL, is_published boolean NOT NULL, publish_date timestamp without time zone, sort_order integer NOT NULL, featured_media_id bigint, featured_image character varying(500), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_project_update ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_project_update_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_redirect ( id bigint NOT NULL, name character varying(100) NOT NULL, description character varying(500), source_path character varying(500) NOT NULL, target_path character varying(500) NOT NULL, redirect_type_id bigint NOT NULL, match_type character varying(20) NOT NULL, is_active boolean NOT NULL, priority integer NOT NULL, preserve_query_params boolean NOT NULL, hit_count bigint NOT NULL, last_hit_date timestamp without time zone, expiry_date timestamp without time zone, site_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_redirect ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_redirect_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_redirect_type ( id bigint NOT NULL, name character varying(100) NOT NULL, code character varying(50) NOT NULL, description character varying(500), http_status_code integer NOT NULL, is_active boolean NOT NULL, sort_order integer NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_redirect_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_redirect_type_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_site ( id bigint NOT NULL, name character varying(40) NOT NULL, short_name character varying(20) NOT NULL, description character varying(255), create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_site ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_site_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); CREATE TABLE public.cms_snippet ( id bigint NOT NULL, name character varying(100) NOT NULL, short_name character varying(50) NOT NULL, content text, css_class_name character varying(255), is_active boolean NOT NULL, sort_order integer NOT NULL, site_id bigint NOT NULL, create_date timestamp without time zone NOT NULL, modify_date timestamp without time zone NOT NULL ); ALTER TABLE public.cms_snippet ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.cms_snippet_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); ALTER TABLE ONLY public.cms_author ADD CONSTRAINT pk_cms_author PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT pk_cms_blog PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT pk_cms_blog_post PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_post_media ADD CONSTRAINT pk_cms_blog_post_media PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_post_tag_int ADD CONSTRAINT pk_cms_blog_post_tag_int PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT pk_cms_blog_post_version PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_tag ADD CONSTRAINT pk_cms_blog_tag PRIMARY KEY (id); ALTER TABLE ONLY public.cms_blog_type ADD CONSTRAINT pk_cms_blog_type PRIMARY KEY (id); ALTER TABLE ONLY public.cms_domain ADD CONSTRAINT pk_cms_domain PRIMARY KEY (id); ALTER TABLE ONLY public.cms_media ADD CONSTRAINT pk_cms_media PRIMARY KEY (id); ALTER TABLE ONLY public.cms_media_type ADD CONSTRAINT pk_cms_media_type PRIMARY KEY (id); ALTER TABLE ONLY public.cms_menu ADD CONSTRAINT pk_cms_menu PRIMARY KEY (id); ALTER TABLE ONLY public.cms_menu_item ADD CONSTRAINT pk_cms_menu_item PRIMARY KEY (id); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT pk_cms_page PRIMARY KEY (id); ALTER TABLE ONLY public.cms_page_section ADD CONSTRAINT pk_cms_page_section PRIMARY KEY (id); ALTER TABLE ONLY public.cms_page_section_type ADD CONSTRAINT pk_cms_page_section_type PRIMARY KEY (id); ALTER TABLE ONLY public.cms_page_type ADD CONSTRAINT pk_cms_page_type PRIMARY KEY (id); ALTER TABLE ONLY public.cms_photo ADD CONSTRAINT pk_cms_photo PRIMARY KEY (id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT pk_cms_photo_album PRIMARY KEY (id); ALTER TABLE ONLY public.cms_photo_album_folder ADD CONSTRAINT pk_cms_photo_album_folder PRIMARY KEY (id); ALTER TABLE ONLY public.cms_photo_tag ADD CONSTRAINT pk_cms_photo_tag PRIMARY KEY (id); ALTER TABLE ONLY public.cms_photo_tag_int ADD CONSTRAINT pk_cms_photo_tag_int PRIMARY KEY (id); ALTER TABLE ONLY public.cms_project ADD CONSTRAINT pk_cms_project PRIMARY KEY (id); ALTER TABLE ONLY public.cms_project_category ADD CONSTRAINT pk_cms_project_category PRIMARY KEY (id); ALTER TABLE ONLY public.cms_project_update ADD CONSTRAINT pk_cms_project_update PRIMARY KEY (id); ALTER TABLE ONLY public.cms_redirect ADD CONSTRAINT pk_cms_redirect PRIMARY KEY (id); ALTER TABLE ONLY public.cms_redirect_type ADD CONSTRAINT pk_cms_redirect_type PRIMARY KEY (id); ALTER TABLE ONLY public.cms_site ADD CONSTRAINT pk_cms_site PRIMARY KEY (id); ALTER TABLE ONLY public.cms_snippet ADD CONSTRAINT pk_cms_snippet PRIMARY KEY (id); ALTER TABLE ONLY public.cms_author ADD CONSTRAINT uq_cms_author_name UNIQUE (name); ALTER TABLE ONLY public.cms_author ADD CONSTRAINT uq_cms_author_slug UNIQUE (slug); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT uq_cms_blog_name UNIQUE (name); ALTER TABLE ONLY public.cms_blog_post_media ADD CONSTRAINT uq_cms_blog_post_media_blog_post_id_media_id UNIQUE (blog_post_id, media_id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT uq_cms_blog_post_name_blog_id UNIQUE (name, blog_id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT uq_cms_blog_post_short_name_blog_id UNIQUE (short_name, blog_id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT uq_cms_blog_post_slug_blog_id UNIQUE (slug, blog_id); ALTER TABLE ONLY public.cms_blog_post_tag_int ADD CONSTRAINT uq_cms_blog_post_tag_int_blog_post_id_blog_tag_id UNIQUE (blog_post_id, blog_tag_id); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT uq_cms_blog_post_version_blog_post_id_variant_key UNIQUE (blog_post_id, variant_key); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT uq_cms_blog_post_version_project_id_variant_key UNIQUE (project_id, variant_key); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT uq_cms_blog_post_version_project_update_id_variant_key UNIQUE (project_update_id, variant_key); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT uq_cms_blog_short_name UNIQUE (short_name); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT uq_cms_blog_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_blog_tag ADD CONSTRAINT uq_cms_blog_tag_name_blog_id UNIQUE (name, blog_id); ALTER TABLE ONLY public.cms_blog_tag ADD CONSTRAINT uq_cms_blog_tag_slug_blog_id UNIQUE (slug, blog_id); ALTER TABLE ONLY public.cms_blog_type ADD CONSTRAINT uq_cms_blog_type_code UNIQUE (code); ALTER TABLE ONLY public.cms_blog_type ADD CONSTRAINT uq_cms_blog_type_name UNIQUE (name); ALTER TABLE ONLY public.cms_domain ADD CONSTRAINT uq_cms_domain_fqdn UNIQUE (fqdn); ALTER TABLE ONLY public.cms_domain ADD CONSTRAINT uq_cms_domain_name UNIQUE (name); ALTER TABLE ONLY public.cms_domain ADD CONSTRAINT uq_cms_domain_short_name UNIQUE (short_name); ALTER TABLE ONLY public.cms_media_type ADD CONSTRAINT uq_cms_media_type_code UNIQUE (code); ALTER TABLE ONLY public.cms_menu ADD CONSTRAINT uq_cms_menu_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_page_section_type ADD CONSTRAINT uq_cms_page_section_type_code UNIQUE (code); ALTER TABLE ONLY public.cms_page_section_type ADD CONSTRAINT uq_cms_page_section_type_name UNIQUE (name); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT uq_cms_page_slug_parent_page_id UNIQUE (slug, parent_page_id); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT uq_cms_page_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_page_type ADD CONSTRAINT uq_cms_page_type_code UNIQUE (code); ALTER TABLE ONLY public.cms_page_type ADD CONSTRAINT uq_cms_page_type_name UNIQUE (name); ALTER TABLE ONLY public.cms_photo_album_folder ADD CONSTRAINT uq_cms_photo_album_folder_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT uq_cms_photo_album_short_name_site_id UNIQUE (short_name, site_id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT uq_cms_photo_album_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_photo ADD CONSTRAINT uq_cms_photo_slug_photo_album_id UNIQUE (slug, photo_album_id); ALTER TABLE ONLY public.cms_photo_tag_int ADD CONSTRAINT uq_cms_photo_tag_int_photo_id_photo_tag_id UNIQUE (photo_id, photo_tag_id); ALTER TABLE ONLY public.cms_photo_tag ADD CONSTRAINT uq_cms_photo_tag_name UNIQUE (name); ALTER TABLE ONLY public.cms_photo_tag ADD CONSTRAINT uq_cms_photo_tag_slug UNIQUE (slug); ALTER TABLE ONLY public.cms_project_category ADD CONSTRAINT uq_cms_project_category_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_project ADD CONSTRAINT uq_cms_project_slug_site_id UNIQUE (slug, site_id); ALTER TABLE ONLY public.cms_project_update ADD CONSTRAINT uq_cms_project_update_slug_project_id UNIQUE (slug, project_id); ALTER TABLE ONLY public.cms_redirect_type ADD CONSTRAINT uq_cms_redirect_type_code UNIQUE (code); ALTER TABLE ONLY public.cms_site ADD CONSTRAINT uq_cms_site_name UNIQUE (name); ALTER TABLE ONLY public.cms_snippet ADD CONSTRAINT uq_cms_snippet_short_name_site_id UNIQUE (short_name, site_id); CREATE INDEX idx_cms_blog_post_author_id ON public.cms_blog_post USING btree (author_id); CREATE INDEX idx_cms_blog_post_blog_id_is_published ON public.cms_blog_post USING btree (blog_id, is_published); CREATE INDEX idx_cms_blog_post_media_blog_post_id_sort_order ON public.cms_blog_post_media USING btree (blog_post_id, sort_order); CREATE INDEX idx_cms_blog_post_tag_int_blog_tag_id ON public.cms_blog_post_tag_int USING btree (blog_tag_id); CREATE INDEX idx_cms_blog_post_version_blog_post_id_channel ON public.cms_blog_post_version USING btree (blog_post_id, channel); CREATE INDEX idx_cms_media_media_type_id ON public.cms_media USING btree (media_type_id); CREATE INDEX idx_cms_media_site_id_name ON public.cms_media USING btree (site_id, name); CREATE INDEX idx_cms_menu_item_menu_id ON public.cms_menu_item USING btree (menu_id); CREATE INDEX idx_cms_page_section_page_id ON public.cms_page_section USING btree (page_id); CREATE INDEX idx_cms_page_site_id_is_published ON public.cms_page USING btree (site_id, is_published); CREATE INDEX idx_cms_photo_album_folder_parent_id ON public.cms_photo_album_folder USING btree (parent_id); CREATE INDEX idx_cms_photo_album_is_published ON public.cms_photo_album USING btree (is_published); CREATE INDEX idx_cms_photo_album_photo_album_folder_id ON public.cms_photo_album USING btree (photo_album_folder_id); CREATE INDEX idx_cms_photo_album_project_id ON public.cms_photo_album USING btree (project_id); CREATE INDEX idx_cms_photo_album_project_update_id ON public.cms_photo_album USING btree (project_update_id); CREATE INDEX idx_cms_photo_photo_album_id_sort_order ON public.cms_photo USING btree (photo_album_id, sort_order); CREATE INDEX idx_cms_photo_tag_int_photo_tag_id ON public.cms_photo_tag_int USING btree (photo_tag_id); CREATE INDEX idx_cms_project_category_id_is_published_sort_order ON public.cms_project USING btree (category_id, is_published, sort_order); CREATE INDEX idx_cms_project_update_project_id_is_published_update_date ON public.cms_project_update USING btree (project_id, is_published, update_date); CREATE INDEX idx_cms_redirect_site_id_source_path ON public.cms_redirect USING btree (site_id, source_path); ALTER TABLE ONLY public.cms_author ADD CONSTRAINT fk_cms_author_profile_media_id FOREIGN KEY (profile_media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT fk_cms_blog_blog_type_id FOREIGN KEY (blog_type_id) REFERENCES public.cms_blog_type(id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT fk_cms_blog_post_author_id FOREIGN KEY (author_id) REFERENCES public.cms_author(id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT fk_cms_blog_post_blog_id FOREIGN KEY (blog_id) REFERENCES public.cms_blog(id); ALTER TABLE ONLY public.cms_blog_post ADD CONSTRAINT fk_cms_blog_post_featured_media_id FOREIGN KEY (featured_media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_blog_post_media ADD CONSTRAINT fk_cms_blog_post_media_blog_post_id FOREIGN KEY (blog_post_id) REFERENCES public.cms_blog_post(id); ALTER TABLE ONLY public.cms_blog_post_media ADD CONSTRAINT fk_cms_blog_post_media_media_id FOREIGN KEY (media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_blog_post_tag_int ADD CONSTRAINT fk_cms_blog_post_tag_int_blog_post_id FOREIGN KEY (blog_post_id) REFERENCES public.cms_blog_post(id); ALTER TABLE ONLY public.cms_blog_post_tag_int ADD CONSTRAINT fk_cms_blog_post_tag_int_blog_tag_id FOREIGN KEY (blog_tag_id) REFERENCES public.cms_blog_tag(id); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT fk_cms_blog_post_version_blog_post_id FOREIGN KEY (blog_post_id) REFERENCES public.cms_blog_post(id); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT fk_cms_blog_post_version_project_id FOREIGN KEY (project_id) REFERENCES public.cms_project(id); ALTER TABLE ONLY public.cms_blog_post_version ADD CONSTRAINT fk_cms_blog_post_version_project_update_id FOREIGN KEY (project_update_id) REFERENCES public.cms_project_update(id); ALTER TABLE ONLY public.cms_blog ADD CONSTRAINT fk_cms_blog_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_blog_tag ADD CONSTRAINT fk_cms_blog_tag_blog_id FOREIGN KEY (blog_id) REFERENCES public.cms_blog(id); ALTER TABLE ONLY public.cms_domain ADD CONSTRAINT fk_cms_domain_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_media ADD CONSTRAINT fk_cms_media_media_type_id FOREIGN KEY (media_type_id) REFERENCES public.cms_media_type(id); ALTER TABLE ONLY public.cms_media ADD CONSTRAINT fk_cms_media_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_menu_item ADD CONSTRAINT fk_cms_menu_item_menu_id FOREIGN KEY (menu_id) REFERENCES public.cms_menu(id); ALTER TABLE ONLY public.cms_menu_item ADD CONSTRAINT fk_cms_menu_item_parent_id FOREIGN KEY (parent_id) REFERENCES public.cms_menu_item(id); ALTER TABLE ONLY public.cms_menu ADD CONSTRAINT fk_cms_menu_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT fk_cms_page_page_type_id FOREIGN KEY (page_type_id) REFERENCES public.cms_page_type(id); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT fk_cms_page_parent_page_id FOREIGN KEY (parent_page_id) REFERENCES public.cms_page(id); ALTER TABLE ONLY public.cms_page_section ADD CONSTRAINT fk_cms_page_section_page_id FOREIGN KEY (page_id) REFERENCES public.cms_page(id); ALTER TABLE ONLY public.cms_page_section ADD CONSTRAINT fk_cms_page_section_section_type_id FOREIGN KEY (section_type_id) REFERENCES public.cms_page_section_type(id); ALTER TABLE ONLY public.cms_page ADD CONSTRAINT fk_cms_page_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT fk_cms_photo_album_cover_photo_id FOREIGN KEY (cover_photo_id) REFERENCES public.cms_photo(id); ALTER TABLE ONLY public.cms_photo_album_folder ADD CONSTRAINT fk_cms_photo_album_folder_parent_id FOREIGN KEY (parent_id) REFERENCES public.cms_photo_album_folder(id); ALTER TABLE ONLY public.cms_photo_album_folder ADD CONSTRAINT fk_cms_photo_album_folder_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT fk_cms_photo_album_photo_album_folder_id FOREIGN KEY (photo_album_folder_id) REFERENCES public.cms_photo_album_folder(id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT fk_cms_photo_album_project_id FOREIGN KEY (project_id) REFERENCES public.cms_project(id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT fk_cms_photo_album_project_update_id FOREIGN KEY (project_update_id) REFERENCES public.cms_project_update(id); ALTER TABLE ONLY public.cms_photo_album ADD CONSTRAINT fk_cms_photo_album_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_photo ADD CONSTRAINT fk_cms_photo_media_id FOREIGN KEY (media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_photo ADD CONSTRAINT fk_cms_photo_photo_album_id FOREIGN KEY (photo_album_id) REFERENCES public.cms_photo_album(id); ALTER TABLE ONLY public.cms_photo_tag_int ADD CONSTRAINT fk_cms_photo_tag_int_photo_id FOREIGN KEY (photo_id) REFERENCES public.cms_photo(id); ALTER TABLE ONLY public.cms_photo_tag_int ADD CONSTRAINT fk_cms_photo_tag_int_photo_tag_id FOREIGN KEY (photo_tag_id) REFERENCES public.cms_photo_tag(id); ALTER TABLE ONLY public.cms_project_category ADD CONSTRAINT fk_cms_project_category_featured_media_id FOREIGN KEY (featured_media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_project ADD CONSTRAINT fk_cms_project_category_id FOREIGN KEY (category_id) REFERENCES public.cms_project_category(id); ALTER TABLE ONLY public.cms_project_category ADD CONSTRAINT fk_cms_project_category_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_project ADD CONSTRAINT fk_cms_project_featured_media_id FOREIGN KEY (featured_media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_project ADD CONSTRAINT fk_cms_project_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_project_update ADD CONSTRAINT fk_cms_project_update_featured_media_id FOREIGN KEY (featured_media_id) REFERENCES public.cms_media(id); ALTER TABLE ONLY public.cms_project_update ADD CONSTRAINT fk_cms_project_update_project_id FOREIGN KEY (project_id) REFERENCES public.cms_project(id); ALTER TABLE ONLY public.cms_redirect ADD CONSTRAINT fk_cms_redirect_redirect_type_id FOREIGN KEY (redirect_type_id) REFERENCES public.cms_redirect_type(id); ALTER TABLE ONLY public.cms_redirect ADD CONSTRAINT fk_cms_redirect_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id); ALTER TABLE ONLY public.cms_snippet ADD CONSTRAINT fk_cms_snippet_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);