1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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)
);
|
