Class Schema.

This class manipulates the Archiveopteryx database schema. It knows all the schema revisions and can upgrade a database to the latest schema version automatically.

Schema::Schema( EventHandler * owner, bool upgrade, bool commit )

Creates a new Schema object to check that the existing schema is one that the running server understands. If upgrade is true (which it is not, by default) and the schema is too old, it will be upgraded. (If upgrade is false, a "please upgrade" message will be issued.)

If commit is false (which it also is not, by default), the SQL statements performed during the upgrade will not be COMMITted, but their success or failure will be reported.

The owner will be notified of progress via the Query returned by result().

static void Schema::checkRevision( EventHandler * owner )

This function is responsible for checking that the running server is compatible with the existing database schema, and to notify owner when the verification is complete.

If the schema is not compatible, a disaster is logged.

The function expects to be called from ::main(), and should be the first database transaction.

void Schema::describeStep( const EString & description )

This private helper logs a description of the step currently being made.

void Schema::execute()

Checks or upgrades the schema as required.

Reimplements EventHandler::execute().

void Schema::fail( const EString & s, Query * q )

Given an error message s and, optionally, the query q that caused the error, this private helper function logs a suitable set of Disaster messages (including the Query::description()) and sets the error message for d->result to s.

Query * Schema::result() const

Returns a Query object that can be used to track the progress of the Schema verification or upgradation. The Query's owner is set by the constructor when the Schema is created.

EString Schema::serverVersion() const

After execute() has completed, this function returns the version ("8.1.3") of the running Postgres server.

bool Schema::singleStep()

Uses a helper function to upgrade the schema from d->revision to d->revision+1. Returns false if the helper has not yet completed its work.

bool Schema::stepTo10()

Add "on delete cascade" to the mailboxes.owner reference.

bool Schema::stepTo11()

Delete the revisions sequence.

bool Schema::stepTo12()

Reverse stepTo10(). We don't want to delete rows in mailboxes.

bool Schema::stepTo13()

Create the annotation_names and annotations tables.

bool Schema::stepTo14()

Add the tables required to support views.

bool Schema::stepTo15()

Add "on delete cascade" to the subscriptions/annotations.owner references.

bool Schema::stepTo16()

Add the aliases table.

bool Schema::stepTo17()

Drop the aliases table from #16 (never released) and recreate it, with a reference to the address, and a link from users.

bool Schema::stepTo18()

Add the scripts table.

bool Schema::stepTo19()

Add the date_fields table.

bool Schema::stepTo2()

Changes the type of users.login and users.secret to text to remove the made-up length restriction on the earlier varchar field.

bool Schema::stepTo20()

Populate the date_fields table from header_fields.

bool Schema::stepTo21()

Remove unnecessary stuff from annotations.

bool Schema::stepTo22()

For any two flag names that differ only in case, moves all flags from one to the other and removes the unused one. Then adds an index to ensure uniqueness in the future.

bool Schema::stepTo23()

Add the deleted_messages table.

bool Schema::stepTo24()

Create threads/thread_members if they don't exist already.

bool Schema::stepTo25()

Adds the modsequences table.

bool Schema::stepTo26()

Alters deleted_messages.deleted_at to be a timestamp with time zone.

bool Schema::stepTo27()

Add "on delete cascade" to the mailboxes.owner reference.

bool Schema::stepTo28()

Create the deliveries table.

bool Schema::stepTo29()

Replace views.suidnext with nextmodseq.

bool Schema::stepTo3()

Merges the binary_parts table into bodyparts.

bool Schema::stepTo30()

Create the access_keys table.

bool Schema::stepTo31()

Add indexes on addresses and deleted_messages.

bool Schema::stepTo32()

The address_fields table lacks many of the rows it should have had in revisions prior to 33. This upgrade removes all existing rows, adds a new column with data we need to keep, parses header_fields to generate the new rows, and kills the now unnecessary header_fields rows.

Well, actually it doesn't do the last step yet. The MessageHeaderFetcher is careful to disregard these rows, so the do no harm.

bool Schema::stepTo33()

Add some indexes to speed up message deletion.

bool Schema::stepTo34()

Add tried_at to deliveries.

bool Schema::stepTo35()

Add sender to deliveries too.

bool Schema::stepTo36()

Grant "update" on deliveries to aox, because although stepTo28() did that, schema/grant-privileges did not.

bool Schema::stepTo37()

Create the unparsed_messages table if it doesn't exist already. After this revision, the table exists, but is unfilled; and the upgraded schema and schema.pg ought to be in sync.

bool Schema::stepTo38()

Grant insert on unparsed_messages.

bool Schema::stepTo39()

Add a unique constraint to scripts.

bool Schema::stepTo4()

Move message flags from the messages table to the extra_flags table, now renamed just "flags".

bool Schema::stepTo40()

Relax the deleted_messages.deleted_by constraint.

bool Schema::stepTo41()

Populate unparsed_messages.

bool Schema::stepTo42()

Insert modsequences for any messages that don't have them.

bool Schema::stepTo43()

Make nextmodseq be per-mailbox.

bool Schema::stepTo44()

Add some primary keys (the easy ones).

bool Schema::stepTo45()

Add an index on users.login.

bool Schema::stepTo46()

Remove duplicates from deleted_messages, and add a primary key.

bool Schema::stepTo47()

Unconstrain annotations.owner and add a surrogate key.

bool Schema::stepTo48()

Grant select,update on annotations_id_seq.

bool Schema::stepTo49()

Grant privileges on threads and thread_members.

bool Schema::stepTo5()

Add some indices on header_fields, address_fields, and flags.

bool Schema::stepTo50()

Add deliveries.delivered_at.

bool Schema::stepTo51()

Split delivery_recipients away from deliveries.

bool Schema::stepTo52()

Add delivery_recipients.action and last_attempt.

bool Schema::stepTo53()

We need permissions on the delivery_recipients sequence too.

bool Schema::stepTo54()

Make (mailbox,uid) unique in deliveries.

bool Schema::stepTo55()

Convert mUTF-7 mailbox names to UTF-8.

bool Schema::stepTo56()

Create the vacation_responses table.

bool Schema::stepTo57()

Rename vacation_responses to autoresponses. (We do this by dropping the old table and creating a new one, so that the sequence is also renamed.)

bool Schema::stepTo58()

Add a missing "on delete cascade" clause to scripts.

bool Schema::stepTo59()

Delete duplicate addresses: By mistake the unique index used a case-sensitive domain. We keep the oldest version seen.

bool Schema::stepTo6()

Move bodyparts.bytes/lines to the part_numbers table.

bool Schema::stepTo60()

Split messages into two, and clean up the resulting mess.

bool Schema::stepTo61()

Grant select,update on messages_id_seq.

bool Schema::stepTo62()

Create a trigger on deleted_messages to remove the message.

bool Schema::stepTo63()

Add deleted_messages.modseq.

bool Schema::stepTo64()

Make deleted_messages.message cascade on delete.

bool Schema::stepTo65()

Grant "update" on threads to aox, so that the threader can lock the table in exclusive mode.

bool Schema::stepTo66()

Change the unique constraint on threads to include "mailbox".

bool Schema::stepTo67()

Create a couple of new indexes to make "aox vacuum" faster, and help to look for specific message-ids.

bool Schema::stepTo68()

Add a table to log connections.

bool Schema::stepTo69()

Make subscriptions:(owner,mailbox) unique.

bool Schema::stepTo7()

Add header_fields.position.

bool Schema::stepTo70()

Add a table to refer to mailboxes that sieve scripts depend on.

bool Schema::stepTo71()

Grant some missing privileges. Sigh.

bool Schema::stepTo72()

Fix incorrect 2.09 EXPUNGEs.

bool Schema::stepTo73()

Split connections.client into address/port.

bool Schema::stepTo74()

Make bodyparts.hash non-unique.

bool Schema::stepTo75()

...but don't make it non-indexed.

bool Schema::stepTo76()

Add an index on d_m(mailbox,modseq) plus a couple of cleanups.

bool Schema::stepTo77()

Add an ldapdn column to users (if it doesn't already exist).

3.0.3/schema.pg was mistakenly released with mailstore.revision=76, but with all the changes from schema #77. So fresh installations of 3.0.3 will later try to execute stepTo77(), and we need to silently succeed if there's nothing to do.

bool Schema::stepTo78()

Move mailbox_messages.idate to messages.idate.

bool Schema::stepTo79()

Create thread_indexes.

bool Schema::stepTo8()

Make address_fields refer to header_fields.

bool Schema::stepTo80()

Add "on delete cascade" to thread_indexes.message.

bool Schema::stepTo81()

Fixes mailbox ownership and installs a trigger to keep it right.

aoximport and perhaps other code could create mailboxes such as /users/foo/stuff without knowing that /users/foo is someone's home, and therefore the new mailbox should be owned by foo.

bool Schema::stepTo82()

Installs a trigger to prevent deleting mailboxes that have to be there for one reason or another.

What we really want is to delete the mail in the mailbox when the mailbox is deleted, but to do that we need (at a minimum) the responsible user. So what we must do is prevent the deletion, and in the application code we must delete the messages before deleting the mailbox.

However, if any bad mailboxes already exist (as they do, not sure why) then aox upgrade schema can delete any mail them. aox upgrade schema knows who ran it.

bool Schema::stepTo83()

Move the Seen and Deleted flags into mailbox_messages, where we can test them much faster.

bool Schema::stepTo84()

Add a retention_policies table for use by "aox set/show retention".

bool Schema::stepTo85()

Starting with schema 80, we want aox upgrade schema to be able to downgrade. We do that by storing downgrade functions in the database. This schema adds the first few.

bool Schema::stepTo86()

We want to cache retention_policies rows. For that to work we have to clear the cache when a new row is added or one is deleted.

bool Schema::stepTo87()

Materialise internal nodes in the mailbox tree.

bool Schema::stepTo88()

Make users.alias nullable to help "aox delete user".

bool Schema::stepTo89()

Change connections.userid to username to help "aox delete user".

bool Schema::stepTo9()

Remove the recent_messages table altogether.

bool Schema::stepTo90()

Add a deliveries column so we can deliver mail later, not now.

bool Schema::stepTo91()

Nothing. Needed so that the right downgrade_to_ functions are written (they were bad in schema 90).

bool Schema::stepTo92()

Reintroduce connections.userid, for logging (only).

bool Schema::stepTo93()

Add users.quota, and set it to the 31-bit int_max. A bigger value might tickle some clients, and a smaller value should really not be the default.

bool Schema::stepTo94()

Rename group_members.groupname to groupid

bool Schema::stepTo95()

Create and link to thread_roots for THREAD=REFS support.