imip-agent

Annotated conf/postgresql/schema.sql

1168:0d4e3b79f680
1155:187be45826e6
1106:e97c3208d57e
2016-04-22 Paul Boddie file changeset files shortlog Added tentative translations.
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@1083     47 
    organiser varchar,
 
 paul@1083     48 
    expires varchar
 
 paul@1083     49 
);
 
 paul@1083     50 
 
 paul@1106     51 
create index freebusy_start on freebusy(store_user, "start");
 
 paul@1106     52 
create index freebusy_end on freebusy(store_user, "end");
 
 paul@1106     53 
 
 paul@1083     54 
create table freebusy_offers (
 
 paul@1083     55 
    store_user varchar not null,
 
 paul@1083     56 
    "start" varchar not null,
 
 paul@1083     57 
    "end" varchar not null,
 
 paul@1083     58 
    object_uid varchar,
 
 paul@1083     59 
    transp varchar,
 
 paul@1083     60 
    object_recurrenceid varchar,
 
 paul@1083     61 
    summary varchar,
 
 paul@1083     62 
    organiser varchar,
 
 paul@1083     63 
    expires varchar
 
 paul@1083     64 
);
 
 paul@1083     65 
 
 paul@1106     66 
create index freebusy_offers_start on freebusy_offers(store_user, "start");
 
 paul@1106     67 
create index freebusy_offers_end on freebusy_offers(store_user, "end");
 
 paul@1106     68 
 
 paul@1083     69 
create table freebusy_other (
 
 paul@1083     70 
    store_user varchar not null,
 
 paul@1083     71 
    other varchar not null,
 
 paul@1083     72 
    "start" varchar not null,
 
 paul@1083     73 
    "end" varchar not null,
 
 paul@1083     74 
    object_uid varchar,
 
 paul@1083     75 
    transp varchar,
 
 paul@1083     76 
    object_recurrenceid varchar,
 
 paul@1083     77 
    summary varchar,
 
 paul@1083     78 
    organiser varchar,
 
 paul@1083     79 
    expires varchar
 
 paul@1083     80 
);
 
 paul@1083     81 
 
 paul@1106     82 
create index freebusy_other_start on freebusy_other(store_user, other, "start");
 
 paul@1106     83 
create index freebusy_other_end on freebusy_other(store_user, other, "end");
 
 paul@1106     84 
 
 paul@1083     85 
create table freebusy_providers (
 
 paul@1083     86 
    store_user varchar not null,
 
 paul@1083     87 
    object_uid varchar not null,
 
 paul@1083     88 
    object_recurrenceid varchar
 
 paul@1083     89 
);
 
 paul@1083     90 
 
 paul@1106     91 
create index freebusy_providers_store_user on freebusy_providers(store_user);
 
 paul@1106     92 
 
 paul@1083     93 
create table freebusy_provider_datetimes (
 
 paul@1083     94 
    store_user varchar not null,
 
 paul@1089     95 
    "start" varchar
 
 paul@1083     96 
);
 
 paul@1083     97 
 
 paul@1106     98 
create index freebusy_provider_datetimes_store_user on freebusy_provider_datetimes(store_user);
 
 paul@1106     99 
 
 paul@1083    100 
-- Object store request details.
 
 paul@1083    101 
 
 paul@1083    102 
create table requests (
 
 paul@1083    103 
    store_user varchar not null,
 
 paul@1083    104 
    object_uid varchar not null,
 
 paul@1083    105 
    object_recurrenceid varchar,
 
 paul@1083    106 
    request_type varchar
 
 paul@1083    107 
);
 
 paul@1083    108 
 
 paul@1106    109 
create index requests_object_uid on requests(store_user, object_uid);
 
 paul@1106    110 
 
 paul@1083    111 
 
 paul@1083    112 
 
 paul@1083    113 
-- Journal store tables.
 
 paul@1083    114 
 
 paul@1083    115 
-- Journal free/busy details.
 
 paul@1083    116 
 
 paul@1083    117 
create table quota_freebusy (
 
 paul@1083    118 
    quota varchar not null,
 
 paul@1083    119 
    user_group varchar not null,
 
 paul@1083    120 
    "start" varchar not null,
 
 paul@1083    121 
    "end" varchar not null,
 
 paul@1083    122 
    object_uid varchar,
 
 paul@1083    123 
    transp varchar,
 
 paul@1083    124 
    object_recurrenceid varchar,
 
 paul@1083    125 
    summary varchar,
 
 paul@1083    126 
    organiser varchar,
 
 paul@1083    127 
    expires varchar
 
 paul@1083    128 
);
 
 paul@1083    129 
 
 paul@1106    130 
create index quota_freebusy_start on quota_freebusy(quota, user_group, "start");
 
 paul@1106    131 
create index quota_freebusy_end on quota_freebusy(quota, user_group, "end");
 
 paul@1106    132 
 
 paul@1083    133 
create table user_freebusy (
 
 paul@1083    134 
    quota varchar not null,
 
 paul@1083    135 
    store_user varchar not null,
 
 paul@1083    136 
    "start" varchar not null,
 
 paul@1083    137 
    "end" varchar not null,
 
 paul@1083    138 
    object_uid varchar,
 
 paul@1083    139 
    transp varchar,
 
 paul@1083    140 
    object_recurrenceid varchar,
 
 paul@1083    141 
    summary varchar,
 
 paul@1083    142 
    organiser varchar,
 
 paul@1083    143 
    expires varchar
 
 paul@1083    144 
);
 
 paul@1083    145 
 
 paul@1106    146 
create index user_freebusy_start on user_freebusy(quota, store_user, "start");
 
 paul@1106    147 
create index user_freebusy_end on user_freebusy(quota, store_user, "end");
 
 paul@1106    148 
 
 paul@1083    149 
-- Journal user groups and limits.
 
 paul@1083    150 
 
 paul@1083    151 
create table quota_limits (
 
 paul@1089    152 
    quota varchar not null,
 
 paul@1083    153 
    user_group varchar not null,
 
 paul@1083    154 
    quota_limit varchar not null,
 
 paul@1083    155 
    primary key(user_group)
 
 paul@1083    156 
);
 
 paul@1083    157 
 
 paul@1083    158 
create table user_groups (
 
 paul@1089    159 
    quota varchar not null,
 
 paul@1083    160 
    store_user varchar not null,
 
 paul@1083    161 
    user_group varchar not null,
 
 paul@1083    162 
    primary key(store_user, user_group)
 
 paul@1083    163 
);