imip-agent

Annotated docs/wiki/DatabaseStore

1427:4d0d5b25bbbd
1214:3bc482b5f33e
2017-12-16 Paul Boddie file changeset files shortlog Structured the tests for more convenient selection. Added another test case (test5). client-editing-simplification
paul@1089 1 
= Database Store =
paul@1089 2 
 
 paul@1089      3 
The database data store offers a mechanism for storing calendar objects and free/busy
 
 paul@1089      4 
details in a database system, currently focusing on relational database systems, with
 
 paul@1089      5 
specific support for PostgreSQL.
 
 paul@1089      6 
 
 paul@1089      7 
Benefits of the database store include convenient and standardised interfaces for
 
 paul@1089      8 
querying and inspecting the data, together with various guarantees around the durability
 
 paul@1089      9 
of the stored data. However, more administrative knowledge is usually required to operate
 
 paul@1089     10 
database installations satisfactorily, and activities such as archiving require extra
 
 paul@1089     11 
planning and expertise.
 
 paul@1089     12 
 
 paul@1089     13 
The [[../FileStore|file store]] offers a more convenient method of managing calendar
 
 paul@1089     14 
data, albeit offering arguably less scalability and less convenience when data volumes
 
 paul@1089     15 
become significantly large.
 
 paul@1093     16 
 
 paul@1093     17 
== Configuration Settings ==
 
 paul@1093     18 
 
 paul@1214     19 
The [[../Configuration|configuration]] files (`config.sh` and `config.txt`) need to be
 
 paul@1093     20 
updated when choosing a database store.
 
 paul@1093     21 
 
 paul@1093     22 
{{{#!table
 
 paul@1093     23 
'''File''' || '''Setting''' || '''Value''' || '''Description'''
 
 paul@1093     24 
==
 
 paul@1093     25 
`config.sh`
 
 paul@1093     26 
||<rowspan="2"> `STORE_TYPE`
 
 paul@1093     27 
||<rowspan="2"> `postgresql`
 
 paul@1093     28 
||<rowspan="2"> Selects a specific database store type; currently, only `postgresql`
 
 paul@1093     29 
             .. is supported
 
 paul@1093     30 
==
 
 paul@1214     31 
`config.txt`
 
 paul@1093     32 
==
 
 paul@1214     33 
<rowspan="2"> `config.txt`
 
 paul@1093     34 
|| `STORE_DIR`
 
 paul@1093     35 
|| `dbname=imip_agent`
 
 paul@1093     36 
|| Indicates a connection string for the store database
 
 paul@1093     37 
==
 
 paul@1093     38 
`JOURNAL_DIR`
 
 paul@1093     39 
|| `dbname=imip_agent`
 
 paul@1093     40 
|| Indicates a connection string for the journal database
 
 paul@1093     41 
}}}
 
 paul@1093     42 
 
 paul@1093     43 
== Journal Structure ==
 
 paul@1093     44 
 
 paul@1093     45 
The journal information is retained in a collection of tables. Unlike the structure of
 
 paul@1093     46 
the [[../FileStore|file store]] in the [[../FilesystemUsage|filesystem]], each table
 
 paul@1093     47 
contains details for all quota groups, with each query indicating the group to select
 
 paul@1093     48 
the appropriate information.
 
 paul@1093     49 
 
 paul@1093     50 
{{{#!table
 
 paul@1093     51 
'''Table''' || '''Purpose'''
 
 paul@1093     52 
==
 
 paul@1198     53 
`journal_freebusy_other`
 
 paul@1192     54 
|| Period descriptions describing reservations for resources sharing a quota
 
 paul@1192     55 
.. (`store_user`) made by users or groups (`other`), structured similarly to the
 
 paul@1192     56 
.. `freebusy` table in the data store; this may be the same table as the one employed
 
 paul@1192     57 
.. by the data store to store received or deduced free/busy details
 
 paul@1093     58 
==
 
 paul@1198     59 
`journal_freebusy_providers`
 
 paul@1196     60 
|| Details of [[../EventRecurrences|recurring events]] for which new free/busy records
 
 paul@1196     61 
.. must be [[../CronIntegration|periodically generated]] because these events recur
 
 paul@1196     62 
.. indefinitely, selectable for each user (`store_user`)
 
 paul@1196     63 
==
 
 paul@1198     64 
`journal_freebusy_provider_datetimes`
 
 paul@1196     65 
|| Date/time details associated with the `freebusy_providers` information
 
 paul@1196     66 
==
 
 paul@1196     67 
`quota_delegates`
 
 paul@1196     68 
|| Details of the identities (`store_user`) employing each quota (`quota`) to
 
 paul@1196     69 
.. consolidate their schedules, between which delegation may take place if their
 
 paul@1196     70 
.. schedules permit it
 
 paul@1196     71 
==
 
 paul@1093     72 
`quota_limits`
 
 paul@1093     73 
|| A mapping from user identities or group identifiers to quota limits
 
 paul@1093     74 
==
 
 paul@1198     75 
`journal_objects`
 
 paul@1198     76 
|| Records for each quota (`store_user`) containing received event data (`object_text`)
 
 paul@1198     77 
==
 
 paul@1198     78 
`journal_recurrences`
 
 paul@1198     79 
|| Records for each quota (`store_user`) containing received recurrence event data
 
 paul@1198     80 
.. (`object_text`)
 
 paul@1198     81 
==
 
 paul@1093     82 
`user_groups`
 
 paul@1093     83 
|| A mapping from user identities to group identifiers indicating the sharing of a quota
 
 paul@1093     84 
.. across a number of users
 
 paul@1093     85 
}}}
 
 paul@1093     86 
 
 paul@1198     87 
The `journal_` prefix is employed for certain tables in order to allow the combination of
 
 paul@1198     88 
the journal and store databases without each kind of data getting mixed up.
 
 paul@1198     89 
 
 paul@1093     90 
== Store Structure ==
 
 paul@1093     91 
 
 paul@1093     92 
The store information is retained in a collection of tables. Unlike the structure of
 
 paul@1093     93 
the [[../FileStore|file store]] in the [[../FilesystemUsage|filesystem]], each table
 
 paul@1093     94 
contains details for all users, with each query indicating the user to select
 
 paul@1093     95 
the appropriate information.
 
 paul@1093     96 
 
 paul@1093     97 
{{{#!table
 
 paul@1093     98 
'''Table''' || '''Purpose'''
 
 paul@1093     99 
==
 
 paul@1093    100 
`countered_objects`
 
 paul@1093    101 
|| Retains counter-proposals corresponding to events held in the `objects` table
 
 paul@1093    102 
.. received by each user (`store_user`) from another user (`other`)
 
 paul@1093    103 
==
 
 paul@1093    104 
`countered_recurrences`
 
 paul@1093    105 
|| Retains counter-proposals corresponding to events held in the `recurrences` table
 
 paul@1093    106 
.. received by each user (`store_user`) from another user (`other`)
 
 paul@1093    107 
==
 
 paul@1093    108 
`freebusy`
 
 paul@1093    109 
|| Period descriptions indicating start and end datetimes (in UTC), unique identifier
 
 paul@1093    110 
.. (`object_uid`), transparency, recurrence identifier (`object_recurrenceid`),
 
 paul@1093    111 
.. summary and organiser, reflecting the schedule of each user (`store_user`)
 
 paul@1093    112 
==
 
 paul@1093    113 
`freebusy_offers`
 
 paul@1093    114 
|| Period descriptions for scheduling offers made by counter-proposals sent by each
 
 paul@1093    115 
.. user (`store_user`)
 
 paul@1093    116 
==
 
 paul@1093    117 
`freebusy_other`
 
 paul@1093    118 
|| Period descriptions received or deduced for a user (`store_user`) structured
 
 paul@1093    119 
.. similarly to the user's own `freebusy` records
 
 paul@1093    120 
==
 
 paul@1093    121 
`freebusy_providers`
 
 paul@1093    122 
|| Details of [[../EventRecurrences|recurring events]] for which new free/busy records
 
 paul@1093    123 
.. must be [[../CronIntegration|periodically generated]] because these events recur
 
 paul@1093    124 
.. indefinitely, selectable for each user (`store_user`)
 
 paul@1093    125 
==
 
 paul@1093    126 
`freebusy_provider_datetimes`
 
 paul@1093    127 
|| Date/time details associated with the `freebusy_providers` information
 
 paul@1093    128 
==
 
 paul@1093    129 
`objects`
 
 paul@1198    130 
|| Records for each user (`store_user`) containing received event data (`object_text`)
 
 paul@1093    131 
==
 
 paul@1093    132 
`requests`
 
 paul@1093    133 
|| A collections of records, each belonging to a specific user (`store_user`)
 
 paul@1093    134 
.. consisting of a unique identifier (`object_uid`), normalised recurrence identifier
 
 paul@1093    135 
.. (`object_recurrenceid`) if appropriate, and (optionally) a scheduling method,
 
 paul@1093    136 
.. indicating the availability of an incoming scheduling request for handling by a user
 
 paul@1093    137 
==
 
 paul@1093    138 
`recurrences`
 
 paul@1198    139 
|| Records for each user (`store_user`) containing received recurrence event data
 
 paul@1093    140 
.. (`object_text`)
 
 paul@1093    141 
}}}