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@1171 | 77 | organiser varchar |
paul@1083 | 78 | ); |
paul@1083 | 79 | |
paul@1106 | 80 | create index freebusy_other_start on freebusy_other(store_user, other, "start"); |
paul@1106 | 81 | create index freebusy_other_end on freebusy_other(store_user, other, "end"); |
paul@1106 | 82 | |
paul@1083 | 83 | create table freebusy_providers ( |
paul@1083 | 84 | store_user varchar not null, |
paul@1083 | 85 | object_uid varchar not null, |
paul@1083 | 86 | object_recurrenceid varchar |
paul@1083 | 87 | ); |
paul@1083 | 88 | |
paul@1106 | 89 | create index freebusy_providers_store_user on freebusy_providers(store_user); |
paul@1106 | 90 | |
paul@1083 | 91 | create table freebusy_provider_datetimes ( |
paul@1083 | 92 | store_user varchar not null, |
paul@1089 | 93 | "start" varchar |
paul@1083 | 94 | ); |
paul@1083 | 95 | |
paul@1106 | 96 | create index freebusy_provider_datetimes_store_user on freebusy_provider_datetimes(store_user); |
paul@1106 | 97 | |
paul@1083 | 98 | -- Object store request details. |
paul@1083 | 99 | |
paul@1083 | 100 | create table requests ( |
paul@1083 | 101 | store_user varchar not null, |
paul@1083 | 102 | object_uid varchar not null, |
paul@1083 | 103 | object_recurrenceid varchar, |
paul@1083 | 104 | request_type varchar |
paul@1083 | 105 | ); |
paul@1083 | 106 | |
paul@1106 | 107 | create index requests_object_uid on requests(store_user, object_uid); |
paul@1106 | 108 | |
paul@1083 | 109 | |
paul@1083 | 110 | |
paul@1083 | 111 | -- Journal store tables. |
paul@1083 | 112 | |
paul@1083 | 113 | -- Journal free/busy details. |
paul@1083 | 114 | |
paul@1083 | 115 | create table quota_freebusy ( |
paul@1083 | 116 | quota varchar not null, |
paul@1083 | 117 | user_group varchar not null, |
paul@1083 | 118 | "start" varchar not null, |
paul@1083 | 119 | "end" varchar not null, |
paul@1083 | 120 | object_uid varchar, |
paul@1083 | 121 | transp varchar, |
paul@1083 | 122 | object_recurrenceid varchar, |
paul@1083 | 123 | summary varchar, |
paul@1083 | 124 | organiser varchar, |
paul@1171 | 125 | attendee varchar |
paul@1083 | 126 | ); |
paul@1083 | 127 | |
paul@1106 | 128 | create index quota_freebusy_start on quota_freebusy(quota, user_group, "start"); |
paul@1106 | 129 | create index quota_freebusy_end on quota_freebusy(quota, user_group, "end"); |
paul@1106 | 130 | |
paul@1083 | 131 | create table user_freebusy ( |
paul@1083 | 132 | quota varchar not null, |
paul@1083 | 133 | store_user varchar not null, |
paul@1083 | 134 | "start" varchar not null, |
paul@1083 | 135 | "end" varchar not null, |
paul@1083 | 136 | object_uid varchar, |
paul@1083 | 137 | transp varchar, |
paul@1083 | 138 | object_recurrenceid varchar, |
paul@1083 | 139 | summary varchar, |
paul@1171 | 140 | organiser varchar |
paul@1083 | 141 | ); |
paul@1083 | 142 | |
paul@1106 | 143 | create index user_freebusy_start on user_freebusy(quota, store_user, "start"); |
paul@1106 | 144 | create index user_freebusy_end on user_freebusy(quota, store_user, "end"); |
paul@1106 | 145 | |
paul@1083 | 146 | -- Journal user groups and limits. |
paul@1083 | 147 | |
paul@1083 | 148 | create table quota_limits ( |
paul@1089 | 149 | quota varchar not null, |
paul@1083 | 150 | user_group varchar not null, |
paul@1083 | 151 | quota_limit varchar not null, |
paul@1168 | 152 | primary key(quota, user_group) |
paul@1083 | 153 | ); |
paul@1083 | 154 | |
paul@1083 | 155 | create table user_groups ( |
paul@1089 | 156 | quota varchar not null, |
paul@1083 | 157 | store_user varchar not null, |
paul@1083 | 158 | user_group varchar not null, |
paul@1168 | 159 | primary key(quota, store_user, user_group) |
paul@1083 | 160 | ); |