imip-agent

Annotated conf/postgresql/schema.sql

1338:8d0357f49f6d
1198:7d1cbe361b3b
2017-10-17 Paul Boddie file changeset files shortlog Allow the journal and publisher to be absent. Changed store, publisher and journal retrieval functions to obtain objects using the configuration defaults if the parameters are unspecified. Changed the client to not perform publisher and journal retrieval if those objects are not specified.
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);