imip-agent

Annotated conf/postgresql/schema.sql

1413:0d89c84c798a
1198:7d1cbe361b3b
2017-12-03 Paul Boddie file changeset files shortlog Support date-datetime comparisons when determining the period collection limit. client-editing-simplification
paul@1083 1 
-- Object store tables.
paul@1083 2 
 
 paul@1083      3 
create table objects (
 
 paul@1083      4 
    store_user varchar not null,
 
 paul@1083      5 
    object_uid varchar not null,
 
 paul@1083      6 
    object_text varchar not null,
 
 paul@1083      7 
    status varchar not null, -- 'active', 'cancelled'
 
 paul@1083      8 
    primary key(store_user, object_uid)
 
 paul@1083      9 
);
 
 paul@1083     10 
 
 paul@1083     11 
create table countered_objects (
 
 paul@1083     12 
    store_user varchar not null,
 
 paul@1083     13 
    other varchar not null,
 
 paul@1083     14 
    object_uid varchar not null,
 
 paul@1083     15 
    object_text varchar not null,
 
 paul@1083     16 
    primary key(store_user, object_uid)
 
 paul@1083     17 
);
 
 paul@1083     18 
 
 paul@1083     19 
create table recurrences (
 
 paul@1083     20 
    store_user varchar not null,
 
 paul@1083     21 
    object_uid varchar not null,
 
 paul@1083     22 
    object_recurrenceid varchar not null,
 
 paul@1083     23 
    object_text varchar not null,
 
 paul@1083     24 
    status varchar not null, -- 'active', 'cancelled'
 
 paul@1083     25 
    primary key(store_user, object_uid, object_recurrenceid)
 
 paul@1083     26 
);
 
 paul@1083     27 
 
 paul@1083     28 
create table countered_recurrences (
 
 paul@1083     29 
    store_user varchar not null,
 
 paul@1083     30 
    other varchar not null,
 
 paul@1083     31 
    object_uid varchar not null,
 
 paul@1083     32 
    object_recurrenceid varchar not null,
 
 paul@1083     33 
    object_text varchar not null,
 
 paul@1083     34 
    primary key(store_user, object_uid, object_recurrenceid)
 
 paul@1083     35 
);
 
 paul@1083     36 
 
 paul@1083     37 
-- Object store free/busy details.
 
 paul@1083     38 
 
 paul@1083     39 
create table freebusy (
 
 paul@1083     40 
    store_user varchar not null,
 
 paul@1083     41 
    "start" varchar not null,
 
 paul@1083     42 
    "end" varchar not null,
 
 paul@1083     43 
    object_uid varchar,
 
 paul@1083     44 
    transp varchar,
 
 paul@1083     45 
    object_recurrenceid varchar,
 
 paul@1083     46 
    summary varchar,
 
 paul@1171     47 
    organiser varchar
 
 paul@1083     48 
);
 
 paul@1083     49 
 
 paul@1106     50 
create index freebusy_start on freebusy(store_user, "start");
 
 paul@1106     51 
create index freebusy_end on freebusy(store_user, "end");
 
 paul@1106     52 
 
 paul@1083     53 
create table freebusy_offers (
 
 paul@1083     54 
    store_user varchar not null,
 
 paul@1083     55 
    "start" varchar not null,
 
 paul@1083     56 
    "end" varchar not null,
 
 paul@1083     57 
    object_uid varchar,
 
 paul@1083     58 
    transp varchar,
 
 paul@1083     59 
    object_recurrenceid varchar,
 
 paul@1083     60 
    summary varchar,
 
 paul@1083     61 
    organiser varchar,
 
 paul@1083     62 
    expires varchar
 
 paul@1083     63 
);
 
 paul@1083     64 
 
 paul@1106     65 
create index freebusy_offers_start on freebusy_offers(store_user, "start");
 
 paul@1106     66 
create index freebusy_offers_end on freebusy_offers(store_user, "end");
 
 paul@1106     67 
 
 paul@1083     68 
create table freebusy_other (
 
 paul@1083     69 
    store_user varchar not null,
 
 paul@1083     70 
    other varchar not null,
 
 paul@1083     71 
    "start" varchar not null,
 
 paul@1083     72 
    "end" varchar not null,
 
 paul@1083     73 
    object_uid varchar,
 
 paul@1083     74 
    transp varchar,
 
 paul@1083     75 
    object_recurrenceid varchar,
 
 paul@1083     76 
    summary varchar,
 
 paul@1192     77 
    organiser varchar,
 
 paul@1192     78 
    attendee varchar -- used by quotas
 
 paul@1083     79 
);
 
 paul@1083     80 
 
 paul@1106     81 
create index freebusy_other_start on freebusy_other(store_user, other, "start");
 
 paul@1106     82 
create index freebusy_other_end on freebusy_other(store_user, other, "end");
 
 paul@1106     83 
 
 paul@1083     84 
create table freebusy_providers (
 
 paul@1083     85 
    store_user varchar not null,
 
 paul@1083     86 
    object_uid varchar not null,
 
 paul@1083     87 
    object_recurrenceid varchar
 
 paul@1083     88 
);
 
 paul@1083     89 
 
 paul@1106     90 
create index freebusy_providers_store_user on freebusy_providers(store_user);
 
 paul@1106     91 
 
 paul@1083     92 
create table freebusy_provider_datetimes (
 
 paul@1083     93 
    store_user varchar not null,
 
 paul@1089     94 
    "start" varchar
 
 paul@1083     95 
);
 
 paul@1083     96 
 
 paul@1106     97 
create index freebusy_provider_datetimes_store_user on freebusy_provider_datetimes(store_user);
 
 paul@1106     98 
 
 paul@1083     99 
-- Object store request details.
 
 paul@1083    100 
 
 paul@1083    101 
create table requests (
 
 paul@1083    102 
    store_user varchar not null,
 
 paul@1083    103 
    object_uid varchar not null,
 
 paul@1083    104 
    object_recurrenceid varchar,
 
 paul@1083    105 
    request_type varchar
 
 paul@1083    106 
);
 
 paul@1083    107 
 
 paul@1106    108 
create index requests_object_uid on requests(store_user, object_uid);
 
 paul@1106    109 
 
 paul@1083    110 
 
 paul@1083    111 
 
 paul@1083    112 
-- Journal store tables.
 
 paul@1083    113 
 
 paul@1176    114 
create table quota_delegates (
 
 paul@1176    115 
    quota varchar not null,
 
 paul@1176    116 
    store_user varchar not null,
 
 paul@1176    117 
    primary key(quota, store_user)
 
 paul@1176    118 
);
 
 paul@1176    119 
 
 paul@1083    120 
-- Journal user groups and limits.
 
 paul@1083    121 
 
 paul@1083    122 
create table quota_limits (
 
 paul@1089    123 
    quota varchar not null,
 
 paul@1083    124 
    user_group varchar not null,
 
 paul@1083    125 
    quota_limit varchar not null,
 
 paul@1168    126 
    primary key(quota, user_group)
 
 paul@1083    127 
);
 
 paul@1083    128 
 
 paul@1083    129 
create table user_groups (
 
 paul@1089    130 
    quota varchar not null,
 
 paul@1083    131 
    store_user varchar not null,
 
 paul@1083    132 
    user_group varchar not null,
 
 paul@1168    133 
    primary key(quota, store_user, user_group)
 
 paul@1083    134 
);
 
 paul@1198    135 
 
 paul@1198    136 
-- Separate object store tables.
 
 paul@1198    137 
 
 paul@1198    138 
create table journal_objects (
 
 paul@1198    139 
    store_user varchar not null,
 
 paul@1198    140 
    object_uid varchar not null,
 
 paul@1198    141 
    object_text varchar not null,
 
 paul@1198    142 
    status varchar not null, -- 'active', 'cancelled'
 
 paul@1198    143 
    primary key(store_user, object_uid)
 
 paul@1198    144 
);
 
 paul@1198    145 
 
 paul@1198    146 
create table journal_recurrences (
 
 paul@1198    147 
    store_user varchar not null,
 
 paul@1198    148 
    object_uid varchar not null,
 
 paul@1198    149 
    object_recurrenceid varchar not null,
 
 paul@1198    150 
    object_text varchar not null,
 
 paul@1198    151 
    status varchar not null, -- 'active', 'cancelled'
 
 paul@1198    152 
    primary key(store_user, object_uid, object_recurrenceid)
 
 paul@1198    153 
);
 
 paul@1198    154 
 
 paul@1198    155 
-- Separate object store free/busy details.
 
 paul@1198    156 
 
 paul@1198    157 
create table journal_freebusy_other (
 
 paul@1198    158 
    store_user varchar not null,
 
 paul@1198    159 
    other varchar not null,
 
 paul@1198    160 
    "start" varchar not null,
 
 paul@1198    161 
    "end" varchar not null,
 
 paul@1198    162 
    object_uid varchar,
 
 paul@1198    163 
    transp varchar,
 
 paul@1198    164 
    object_recurrenceid varchar,
 
 paul@1198    165 
    summary varchar,
 
 paul@1198    166 
    organiser varchar,
 
 paul@1198    167 
    attendee varchar -- used by quotas
 
 paul@1198    168 
);
 
 paul@1198    169 
 
 paul@1198    170 
create index journal_freebusy_other_start on journal_freebusy_other(store_user, other, "start");
 
 paul@1198    171 
create index journal_freebusy_other_end on journal_freebusy_other(store_user, other, "end");
 
 paul@1198    172 
 
 paul@1198    173 
create table journal_freebusy_providers (
 
 paul@1198    174 
    store_user varchar not null,
 
 paul@1198    175 
    object_uid varchar not null,
 
 paul@1198    176 
    object_recurrenceid varchar
 
 paul@1198    177 
);
 
 paul@1198    178 
 
 paul@1198    179 
create index journal_freebusy_providers_store_user on journal_freebusy_providers(store_user);
 
 paul@1198    180 
 
 paul@1198    181 
create table journal_freebusy_provider_datetimes (
 
 paul@1198    182 
    store_user varchar not null,
 
 paul@1198    183 
    "start" varchar
 
 paul@1198    184 
);
 
 paul@1198    185 
 
 paul@1198    186 
create index journal_freebusy_provider_datetimes_store_user on journal_freebusy_provider_datetimes(store_user);