diff options
6 files changed, 148 insertions, 0 deletions
diff --git a/src/db/migrations/2024102301_0.1.0_extensions.down.sql b/src/db/migrations/2024102301_0.1.0_extensions.down.sql new file mode 100644 index 0000000..56cfbcc --- /dev/null +++ b/src/db/migrations/2024102301_0.1.0_extensions.down.sql @@ -0,0 +1,4 @@ +drop extension "uuid-ossp"; +drop extension "citext"; + +drop schema "phtx_ext"; diff --git a/src/db/migrations/2024102301_0.1.0_extensions.up.sql b/src/db/migrations/2024102301_0.1.0_extensions.up.sql new file mode 100644 index 0000000..56f3a19 --- /dev/null +++ b/src/db/migrations/2024102301_0.1.0_extensions.up.sql @@ -0,0 +1,7 @@ +create schema "phtx_ext"; + +create extension "uuid-ossp" + with schema "phtx_ext"; + +create extension "citext" + with schema "phtx_ext"; diff --git a/src/db/migrations/2024102302_0.1.0_schema.down.sql b/src/db/migrations/2024102302_0.1.0_schema.down.sql new file mode 100644 index 0000000..1723230 --- /dev/null +++ b/src/db/migrations/2024102302_0.1.0_schema.down.sql @@ -0,0 +1 @@ +drop schema phtx; diff --git a/src/db/migrations/2024102302_0.1.0_schema.up.sql b/src/db/migrations/2024102302_0.1.0_schema.up.sql new file mode 100644 index 0000000..3c0a019 --- /dev/null +++ b/src/db/migrations/2024102302_0.1.0_schema.up.sql @@ -0,0 +1 @@ +create schema phtx; diff --git a/src/db/migrations/2024102303_0.1.0_tables.down.sql b/src/db/migrations/2024102303_0.1.0_tables.down.sql new file mode 100644 index 0000000..0cfc411 --- /dev/null +++ b/src/db/migrations/2024102303_0.1.0_tables.down.sql @@ -0,0 +1,16 @@ +drop table phtx."image_field_enumerated"; +drop table phtx."image_field_float"; +drop table phtx."image_field_integer"; +drop table phtx."image_field_text"; +drop table phtx."image_field"; +drop table phtx."field"; +drop table phtx."image_data_local"; +drop table phtx."image_data"; +drop table phtx."image"; +drop table phtx."login_session"; +drop table phtx."user"; + +drop type phtx.image_data_image_type; +drop type phtx.image_data_storage_type; +drop type phtx.image_data_file_format; +drop type phtx.image_field_type; diff --git a/src/db/migrations/2024102303_0.1.0_tables.up.sql b/src/db/migrations/2024102303_0.1.0_tables.up.sql new file mode 100644 index 0000000..428c3c2 --- /dev/null +++ b/src/db/migrations/2024102303_0.1.0_tables.up.sql @@ -0,0 +1,119 @@ +create type phtx.image_data_image_type as enum ('full', 'preview'); +create type phtx.image_data_storage_type as enum ('local'); +create type phtx.image_data_file_format as enum ('jpeg', 'jxl', 'x-canon-cr2'); +create type phtx.image_field_type as enum ('text', 'integer', 'float', 'enumerated'); + +create table phtx."user" ( + user_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + user_email phtx_ext.citext unique not null, + user_username phtx_ext.citext unique not null, + user_display_name text, + user_password_hash text, + user_time_created timestamptz not null default now(), + user_time_password_changed timestamptz, + user_time_email_confirmed timestamptz +); + +create table phtx."login_session" ( + login_session_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + login_session_time_created timestamptz not null default now(), + login_session_time_last_active timestamptz not null default now(), + login_session_duration interval not null, + login_session_time_logged_out timestamptz null, + login_session_ip_address varchar(50) not null, + login_session_user_agent varchar(500) not null, + login_session_referer varchar(500) not null, + login_session_user_uuid uuid not null references phtx."user" (user_uuid) +); + +create table phtx."image" ( + image_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_original_filename text not null, + image_title text, + image_description text, + image_owner uuid not null references phtx."user" (user_uuid) +); + +create table phtx."image_data" ( + image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_data_image uuid not null references phtx."image" (image_uuid), + image_data_storage_type phtx.image_data_storage_type not null, + image_data_image_type phtx.image_data_image_type not null, + image_data_dimensions_width integer not null, + image_data_dimensions_height integer not null, + image_data_file_format phtx.image_data_file_format not null, + + -- Compound key to constrain specific storage types + unique (image_data_uuid, image_data_storage_type) +); + +create unique index "only_one_full_image" on phtx."image_data" (image_data_image) + where (image_data_image_type = 'full'); + +create table phtx."image_data_local" ( + image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_data_storage_type phtx.image_data_storage_type check ( image_data_storage_type = 'local' ), + + image_data_file_path text not null, + image_data_file_sha256 varchar(64), + + foreign key (image_data_uuid, image_data_storage_type) references phtx."image_data" (image_data_uuid, image_data_storage_type) +); + +create table phtx."field" ( + field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + field_type phtx.image_field_type not null, + field_user uuid references phtx."user" (user_uuid), + + -- Compound key to constrain image field types + unique (field_uuid, field_type) +); + +create table phtx."image_field" ( + image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_field_type phtx.image_field_type not null, + image_field_image uuid not null references phtx."image" (image_uuid), + image_field_field uuid not null references phtx."field" (field_uuid), + + -- Constrain an image-field record so its type matches the referenced field definition + foreign key (image_field_field, image_field_type) references phtx."field" (field_uuid, field_type), + + -- Compound key to constrain value types + unique (image_field_uuid, image_field_type) +); + +create table phtx."image_field_text" ( + image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_field_type phtx.image_field_type not null check (image_field_type = 'text'), + + image_field_value text not null, + + foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type) +); + +create table phtx."image_field_integer" ( + image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_field_type phtx.image_field_type not null check (image_field_type = 'integer'), + + image_field_value bigint not null, + + foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type) +); + +create table phtx."image_field_float" ( + image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_field_type phtx.image_field_type not null check (image_field_type = 'float'), + + image_field_value double precision not null, + + foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type) +); + +create table phtx."image_field_enumerated" ( + image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(), + image_field_type phtx.image_field_type not null check (image_field_type = 'integer'), + + image_field_value text not null, + + foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type) +); |