DDL

schema_attachments.sql

CREATE TABLE public.attachments
(
	id      SERIAL  NOT NULL
		CONSTRAINT attachments_pk
			PRIMARY KEY,
	file    UUID    NOT NULL,
	content INTEGER NOT NULL
);

ALTER TABLE public.attachments
	OWNER TO postgres;

CREATE UNIQUE INDEX attachments_file_content_uindex
	ON public.attachments (file, content);

schema_categories.sql

CREATE TABLE public.categories
(
	id            SERIAL                                          NOT NULL
		CONSTRAINT categories_pk
			PRIMARY KEY,
	name          TEXT                                            NOT NULL,
	alias         VARCHAR(68)                                     NOT NULL,
	service       INTEGER                                         NOT NULL
		CONSTRAINT categories_services_id_fk
			REFERENCES public.services
			ON DELETE CASCADE,
	type          VARCHAR(60) DEFAULT 'manual'::CHARACTER VARYING NOT NULL,
	priority      SMALLINT    DEFAULT 1                           NOT NULL,
	enable        BOOLEAN     DEFAULT FALSE                       NOT NULL,
	creator       INTEGER     DEFAULT 1                           NOT NULL,
	latest_editor INTEGER     DEFAULT 1                           NOT NULL,
	name_i18n     INTEGER,
	name_i        VARCHAR(45)
);

ALTER TABLE public.categories
	OWNER TO postgres;

CREATE INDEX categories_service_index
	ON public.categories (service);

schema_content_excludes.sql

CREATE TABLE public.content_excludes
(
	id          SERIAL  NOT NULL
		CONSTRAINT content_excludes_pk
			PRIMARY KEY,
	content_id  INTEGER NOT NULL
		CONSTRAINT content_excludes_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	category_id INTEGER NOT NULL
		CONSTRAINT content_excludes_categories_id_fk
			REFERENCES public.categories
			ON DELETE CASCADE
);

ALTER TABLE public.content_excludes
	OWNER TO postgres;

schema_content_group_links.sql

CREATE TABLE public.content_group_links
(
	id         SERIAL  NOT NULL
		CONSTRAINT content_group_links_pk
			PRIMARY KEY,
	content_id INTEGER NOT NULL
		CONSTRAINT content_group_links_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	group_id   INTEGER NOT NULL
		CONSTRAINT content_group_links_groups_id_fk
			REFERENCES public.groups
			ON DELETE CASCADE
);

ALTER TABLE public.content_group_links
	OWNER TO postgres;

schema_content_includes.sql

CREATE TABLE public.content_includes
(
	id          SERIAL  NOT NULL
		CONSTRAINT content_includes_pk
			PRIMARY KEY,
	content_id  INTEGER NOT NULL
		CONSTRAINT content_includes_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	category_id INTEGER NOT NULL
		CONSTRAINT content_includes_categories_id_fk
			REFERENCES public.categories
			ON DELETE CASCADE
);

ALTER TABLE public.content_includes
	OWNER TO postgres;

schema_content_tag_links.sql

CREATE TABLE public.content_tag_links
(
	tag           INTEGER           NOT NULL
		CONSTRAINT content_tag_links_tags_id_fk
			REFERENCES public.tags
			ON DELETE CASCADE,
	content       INTEGER           NOT NULL
		CONSTRAINT content_tag_links_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	id            SERIAL            NOT NULL
		CONSTRAINT content_tag_links_pk
			PRIMARY KEY,
	creator       INTEGER DEFAULT 1 NOT NULL,
	latest_editor INTEGER DEFAULT 1 NOT NULL
);

ALTER TABLE public.content_tag_links
	OWNER TO postgres;

CREATE INDEX content_tag_links_content_index
	ON public.content_tag_links (content DESC);

CREATE INDEX content_tag_links_tag_index
	ON public.content_tag_links (tag DESC);

CREATE UNIQUE INDEX content_tag_links_tag_content_uindex
	ON public.content_tag_links (tag, content);

schema_contents.sql

CREATE TABLE public.contents
(
	id             SERIAL                              NOT NULL
		CONSTRAINT contents_pk
			PRIMARY KEY,
	name           TEXT,
	type           VARCHAR(16)                         NOT NULL,
	file           UUID                                NOT NULL,
	category       INTEGER
		CONSTRAINT contents_categories_id_fk
			REFERENCES public.categories
			ON DELETE CASCADE,
	description    TEXT,
	priority       SMALLINT DEFAULT 1                  NOT NULL,
	enable         BOOLEAN  DEFAULT FALSE              NOT NULL,
	sys_1          TEXT,
	sys_2          TEXT,
	text_field_1   TEXT,
	text_field_2   TEXT,
	text_field_3   TEXT,
	text_field_4   TEXT,
	name_i18n      INTEGER,
	protected      BOOLEAN  DEFAULT FALSE              NOT NULL,
	uidx           UUID     DEFAULT uuid_generate_v4() NOT NULL,
	name_i         VARCHAR(45),
	description_i  VARCHAR(45),
	text_field_1_i VARCHAR(45),
	text_field_2_i VARCHAR(45),
	text_field_3_i VARCHAR(45),
	text_field_4_i VARCHAR(45),
	file_i         VARCHAR(45)
);

ALTER TABLE public.contents
	OWNER TO postgres;

CREATE INDEX contents_category_index
	ON public.contents (category);

CREATE UNIQUE INDEX contents_uidx_uindex
	ON public.contents (uidx);

schema_group_category_links.sql

CREATE TABLE public.group_category_links
(
	id          SERIAL  NOT NULL
		CONSTRAINT group_category_links_pk
			PRIMARY KEY,
	category_id INTEGER NOT NULL
		CONSTRAINT group_category_links_categories_id_fk
			REFERENCES public.categories
			ON DELETE CASCADE,
	group_id    INTEGER NOT NULL
		CONSTRAINT group_category_links_groups_id_fk
			REFERENCES public.groups
			ON DELETE CASCADE
);

ALTER TABLE public.group_category_links
	OWNER TO postgres;

schema_groups.sql

CREATE TABLE public.groups
(
	id          SERIAL      NOT NULL
		CONSTRAINT groups_pk
			PRIMARY KEY,
	name        VARCHAR(82) NOT NULL,
	description TEXT        NOT NULL
);

ALTER TABLE public.groups
	OWNER TO postgres;

CREATE UNIQUE INDEX groups_name_uindex
	ON public.groups (name);

schema_i18n_files.sql

CREATE TABLE public.i18n_files
(
	id      SERIAL      NOT NULL
		CONSTRAINT i18n_files_pk
			PRIMARY KEY,
	key     VARCHAR(45) NOT NULL,
	lang_id INTEGER     NOT NULL
		CONSTRAINT i18n_files_languages_id_fk
			REFERENCES public.languages
			ON DELETE CASCADE,
	value   UUID        NOT NULL
);

ALTER TABLE public.i18n_files
	OWNER TO postgres;

CREATE UNIQUE INDEX i18n_files_key_lang_id_uindex
	ON public.i18n_files (key, lang_id);

schema_i18n_texts.sql

CREATE TABLE public.i18n_texts
(
	id      SERIAL      NOT NULL
		CONSTRAINT i18n_texts_pk
			PRIMARY KEY,
	key     VARCHAR(45) NOT NULL,
	lang_id INTEGER     NOT NULL
		CONSTRAINT i18n_texts_languages_id_fk
			REFERENCES public.languages
			ON DELETE CASCADE,
	value   TEXT        NOT NULL
);

ALTER TABLE public.i18n_texts
	OWNER TO postgres;

CREATE UNIQUE INDEX i18n_texts_key_lang_id_uindex
	ON public.i18n_texts (key, lang_id);

schema_languages.sql

CREATE TABLE public.languages
(
	id   SERIAL     NOT NULL
		CONSTRAINT language_pk
			PRIMARY KEY,
	name VARCHAR(4) NOT NULL
);

ALTER TABLE public.languages
	OWNER TO postgres;

CREATE UNIQUE INDEX language_name_uindex
	ON public.languages (name);

schema_locations.sql

CREATE TABLE public.locations
(
	id          SERIAL      NOT NULL
		CONSTRAINT locations_pk
			PRIMARY KEY,
	name        VARCHAR(60) NOT NULL,
	content_vps VARCHAR(20) NOT NULL
);

ALTER TABLE public.locations
	OWNER TO postgres;

schema_operators.sql

CREATE TABLE public.operators
(
	id   SERIAL NOT NULL
		CONSTRAINT operators_pk
			PRIMARY KEY,
	name TEXT   NOT NULL
);

ALTER TABLE public.operators
	OWNER TO postgres;

schema_override_content_params.sql

CREATE TABLE public.override_content_params
(
	id          SERIAL  NOT NULL
		CONSTRAINT override_content_params_pk
			PRIMARY KEY,
	content_id  INTEGER NOT NULL
		CONSTRAINT override_content_params_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	category_id INTEGER NOT NULL
		CONSTRAINT override_content_params_categories_id_fk
			REFERENCES public.categories
			ON DELETE CASCADE,
	priority    INTEGER,
	enable      BOOLEAN,
	protected   BOOLEAN
);

ALTER TABLE public.override_content_params
	OWNER TO postgres;

schema_points.sql

CREATE TABLE public.points
(
	id                 SERIAL                NOT NULL
		CONSTRAINT point_pk
			PRIMARY KEY,
	service_id         INTEGER               NOT NULL
		CONSTRAINT points_services_id_fk
			REFERENCES public.services
			ON DELETE CASCADE,
	sskey              VARCHAR(22)           NOT NULL,
	forward_url        VARCHAR(2000)         NOT NULL,
	description        TEXT                  NOT NULL,
	flow_proxy_storage TEXT,
	flow_proxy_auth    TEXT,
	pass_status        BOOLEAN DEFAULT FALSE NOT NULL
);

ALTER TABLE public.points
	OWNER TO postgres;

CREATE UNIQUE INDEX points_sskey_uindex
	ON public.points (sskey);

CREATE INDEX points_service_id_index
	ON public.points (service_id);

schema_previews.sql

CREATE TABLE public.previews
(
	id            SERIAL             NOT NULL
		CONSTRAINT previews_pk
			PRIMARY KEY,
	type          VARCHAR(35)        NOT NULL,
	file          UUID               NOT NULL,
	content       INTEGER            NOT NULL
		CONSTRAINT previews_contents_id_fk
			REFERENCES public.contents
			ON DELETE CASCADE,
	priority      SMALLINT DEFAULT 1 NOT NULL,
	creator       INTEGER  DEFAULT 1 NOT NULL,
	latest_editor INTEGER  DEFAULT 1 NOT NULL
);

ALTER TABLE public.previews
	OWNER TO postgres;

CREATE INDEX previews_content_index
	ON public.previews (content);

schema_services.sql

CREATE TABLE public.services
(
	id            SERIAL            NOT NULL
		CONSTRAINT services_pk
			PRIMARY KEY,
	name          TEXT              NOT NULL,
	alias         VARCHAR(68)       NOT NULL,
	operator_id   INTEGER,
	location_id   INTEGER           NOT NULL,
	creator       INTEGER DEFAULT 1 NOT NULL,
	latest_editor INTEGER DEFAULT 1 NOT NULL
);

ALTER TABLE public.services
	OWNER TO postgres;

schema_sessions.sql

CREATE TABLE public.sessions
(
	id            SERIAL       NOT NULL
		CONSTRAINT sessions_pkey
			PRIMARY KEY,
	user_id       INTEGER      NOT NULL
		CONSTRAINT sessions_users_id_fk
			REFERENCES public.users
			ON DELETE CASCADE,
	refresh_token UUID         NOT NULL,
	user_agent    VARCHAR(200) NOT NULL,
	fingerprint   VARCHAR(200) NOT NULL,
	ip            VARCHAR(15)  NOT NULL,
	expires_in    BIGINT       NOT NULL
);

ALTER TABLE public.sessions
	OWNER TO postgres;

CREATE UNIQUE INDEX idx_session
	ON public.sessions (refresh_token, fingerprint);

schema_tags.sql

CREATE TABLE public.tags
(
	id                SERIAL            NOT NULL
		CONSTRAINT tags_pk
			PRIMARY KEY,
	name              VARCHAR(64)       NOT NULL,
	display_name      TEXT              NOT NULL,
	description       TEXT,
	name_i18n         INTEGER,
	display_name_i18n INTEGER,
	priority          INTEGER DEFAULT 1 NOT NULL,
	name_i            VARCHAR(45),
	display_name_i    VARCHAR(45)
);

ALTER TABLE public.tags
	OWNER TO postgres;

schema_upgrades.sql

CREATE TABLE public.upgrades
(
	id       SERIAL                                         NOT NULL
		CONSTRAINT upgrade_pk
			PRIMARY KEY,
	name     VARCHAR(64)                                    NOT NULL,
	value    VARCHAR(260)                                   NOT NULL,
	switch   VARCHAR(6) DEFAULT 'string'::CHARACTER VARYING NOT NULL,
	point_id INTEGER                                        NOT NULL
		CONSTRAINT upgrades_points_id_fk
			REFERENCES public.points
			ON DELETE CASCADE
);

ALTER TABLE public.upgrades
	OWNER TO postgres;

schema_users.sql

CREATE TABLE public.users
(
	id            SERIAL       NOT NULL
		CONSTRAINT users_pkey
			PRIMARY KEY,
	email         VARCHAR(200) NOT NULL,
	full_name     VARCHAR(200) NOT NULL,
	password_hash BYTEA        NOT NULL
);

ALTER TABLE public.users
	OWNER TO postgres;

CREATE UNIQUE INDEX uix_users_email
	ON public.users (email);