Files
qbit-cms/docs/ddl/postgres.sql
James MaesandClaude Fable 5.2 9c17458fec fix: keep value-list fields to the values of their lists
QQQ offers a fixed list (a possible-value source of an enum) in a form but
accepts any value in a write: through the API a project could be saved with
a status that is not on the list. Found by a live check in a host
application.

- CmsValueListCheck: a pre-insert and pre-update customizer on every table
  with a value-list field; a record with another value gets an error and is
  not written, and an update is checked only for the fields it names.
- Changelog: check constraints on the same columns (as kof22 has for
  media_type and blog_post_version; the project status check is new), and
  kof22's checks on media_type.max_file_size_mb and the focal point of a
  project's image. They are part of the not yet released
  1.0.0-constraints-<feature> changesets.
- Reference DDL regenerated.

Tests: 37, Checkstyle 0, guardrails and coverage checks pass.

Co-Authored-By: Claude Fable 5.2 <noreply@anthropic.com>
2026-11-18 12:26:33 -06:00

1164 lines
41 KiB
SQL

-- 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);