From faa56a174328af3ab76ccd1a5b4c9d630ac9779f Mon Sep 17 00:00:00 2001 From: Barry Warsaw Date: Sun, 12 Feb 2012 10:01:07 -0500 Subject: * Schema migrations have been implemented. --- src/mailman/database/base.py | 105 +++++-- src/mailman/database/docs/__init__.py | 0 src/mailman/database/docs/migration.rst | 145 +++++++++ src/mailman/database/model.py | 27 ++ src/mailman/database/postgresql.py | 8 +- src/mailman/database/schema/__init__.py | 0 src/mailman/database/schema/postgres.sql | 345 +++++++++++++++++++++ .../database/schema/s_00000000000000_base.py | 55 ++++ src/mailman/database/schema/sqlite.sql | 321 +++++++++++++++++++ src/mailman/database/sql/__init__.py | 0 src/mailman/database/sql/postgres.sql | 345 --------------------- src/mailman/database/sql/sqlite.sql | 321 ------------------- src/mailman/database/sqlite.py | 7 +- 13 files changed, 971 insertions(+), 708 deletions(-) create mode 100644 src/mailman/database/docs/__init__.py create mode 100644 src/mailman/database/docs/migration.rst create mode 100644 src/mailman/database/schema/__init__.py create mode 100644 src/mailman/database/schema/postgres.sql create mode 100644 src/mailman/database/schema/s_00000000000000_base.py create mode 100644 src/mailman/database/schema/sqlite.sql delete mode 100644 src/mailman/database/sql/__init__.py delete mode 100644 src/mailman/database/sql/postgres.sql delete mode 100644 src/mailman/database/sql/sqlite.sql (limited to 'src/mailman/database') diff --git a/src/mailman/database/base.py b/src/mailman/database/base.py index e28ca1893..a69e99395 100644 --- a/src/mailman/database/base.py +++ b/src/mailman/database/base.py @@ -24,18 +24,18 @@ __all__ = [ import os +import sys import logging from flufl.lock import Lock from lazr.config import as_boolean +from pkg_resources import resource_listdir, resource_string from storm.cache import GenerationalCache from storm.locals import create_database, Store from zope.interface import implements -import mailman.version - from mailman.config import config -from mailman.interfaces.database import IDatabase, SchemaVersionMismatchError +from mailman.interfaces.database import IDatabase from mailman.model.version import Version from mailman.utilities.string import expand @@ -51,6 +51,10 @@ class StormBaseDatabase: Use this as a base class for your DB-specific derived classes. """ + # Tag used to distinguish the database being used. Override this in base + # classes. + TAG = '' + implements(IDatabase) def __init__(self): @@ -87,15 +91,6 @@ class StormBaseDatabase: """ raise NotImplementedError - def _get_schema(self): - """Return the database schema as a string. - - This will be loaded into the database when it is first created. - - Base classes *must* override this. - """ - raise NotImplementedError - def _pre_reset(self, store): """Clean up method for testing. @@ -147,34 +142,80 @@ class StormBaseDatabase: store = Store(database, GenerationalCache()) database.DEBUG = (as_boolean(config.database.debug) if debug is None else debug) - # Check the master / schema database to see if the version table - # exists. If so, then we assume the database schema is correctly - # initialized. Storm does not currently provide schema creation. - if not self._database_exists(store): - # Initialize the database. Start by getting the schema and - # discarding all blank and comment lines. - lines = self._get_schema().splitlines() - lines = (line for line in lines + self.store = store + self.load_migrations() + store.commit() + + def load_migrations(self): + """Load all not-yet loaded migrations.""" + migrations_path = config.database.migrations_path + if '.' in migrations_path: + parent, dot, child = migrations_path.rpartition('.') + else: + parent = migrations_path + child ='' + # If the database does not yet exist, load the base schema. + filenames = sorted(resource_listdir(parent, child)) + # Find out which schema migrations have already been loaded. + if self._database_exists(self.store): + versions = set(version.version for version in + self.store.find(Version, component='schema')) + else: + versions = set() + for filename in filenames: + module_fn, extension = os.path.splitext(filename) + if extension != '.py': + continue + parts = module_fn.split('_') + if len(parts) < 2: + continue + version = parts[1] + if version in versions: + # This one is already loaded. + continue + module_path = migrations_path + '.' + module_fn + __import__(module_path) + upgrade = getattr(sys.modules[module_path], 'upgrade', None) + if upgrade is None: + continue + upgrade(self, self.store, version, module_path) + + def load_schema(self, store, version, filename, module_path): + """Load the schema from a file. + + This is a helper method for migration classes to call. + + :param store: The Storm store to load the schema into. + :type store: storm.locals.Store` + :param version: The schema version identifier of the form + YYYYMMDDHHMMSS. + :type version: string + :param filename: The file name containing the schema to load. Pass + `None` if there is no schema file to load. + :type filename: string + :param module_path: The fully qualified Python module path to the + migration module being loaded. This is used to record information + for use by the test suite. + :type module_path: string + """ + if filename is not None: + contents = resource_string('mailman.database.schema', filename) + # Discard all blank and comment lines. + lines = (line for line in contents.splitlines() if line.strip() != '' and line.strip()[:2] != '--') sql = NL.join(lines) for statement in sql.split(';'): if statement.strip() != '': store.execute(statement + ';') - # Validate schema version. - v = store.find(Version, component='schema').one() - if not v: - # Database has not yet been initialized - v = Version(component='schema', - version=mailman.version.DATABASE_SCHEMA_VERSION) - store.add(v) - elif v.version <> mailman.version.DATABASE_SCHEMA_VERSION: - # XXX Update schema - raise SchemaVersionMismatchError(v.version) - self.store = store - store.commit() + # Add a marker that indicates the migration version being applied. + store.add(Version(component='schema', version=version)) + # Add a marker so that the module name can be found later. This is + # used by the test suite to reset the database between tests. + store.add(Version(component=version, version=module_path)) def _reset(self): """See `IDatabase`.""" from mailman.database.model import ModelMeta self.store.rollback() ModelMeta._reset(self.store) + self.store.commit() diff --git a/src/mailman/database/docs/__init__.py b/src/mailman/database/docs/__init__.py new file mode 100644 index 000000000..e69de29bb diff --git a/src/mailman/database/docs/migration.rst b/src/mailman/database/docs/migration.rst new file mode 100644 index 000000000..930f7d245 --- /dev/null +++ b/src/mailman/database/docs/migration.rst @@ -0,0 +1,145 @@ +================= +Schema migrations +================= + +The SQL database schema will over time require upgrading to support new +features. This is supported via schema migration. + +Migrations are embodied in individual Python classes, which themselves may +load SQL into the database. The naming scheme for migration files is: + + s_YYYYMMDDHHMMSS_comment.py + +where `YYYYMMDDHHMMSS` is a required numeric year, month, day, hour, minute, +and second specifier providing unique ordering for processing. Only this +component of the file name is used to determine the ordering. (The `s_` +prefix is required due to Python module naming requirements). + +The optional `comment` part of the file name can be used as a short +description for the migration, although comments and docstrings in the +migration files should be used for more detailed descriptions. + +Migrations are applied automatically when Mailman starts up, but can also be +applied at any time by calling in the API directly. Once applied, a +migration's version string is registered so it will not be applied again. + +We see that the base migration is already applied. + + >>> from mailman.model.version import Version + >>> results = config.db.store.find(Version, component='schema') + >>> results.count() + 1 + >>> base = results.one() + >>> print base.component + schema + >>> print base.version + 00000000000000 + + +Migrations +========== + +Migrations can be loaded at any time, and can be found in the migrations path +specified in the configuration file. + +.. Create a temporary directory for the migrations:: + + >>> import os, sys, tempfile + >>> tempdir = tempfile.mkdtemp() + >>> path = os.path.join(tempdir, 'migrations') + >>> os.makedirs(path) + >>> sys.path.append(tempdir) + >>> config.push('migrations', """ + ... [database] + ... migrations_path: migrations + ... """) + +Here is an example migrations module. The key part of this interface is the +``upgrade()`` method, which takes four arguments: + + * `database` - The database class, as derived from `StormBaseDatabase` + * `store` - The Storm `Store` object. + * `version` - The version string as derived from the migrations module's file + name. This will include only the `YYYYMMDDHHMMSS` string. + * `module_path` - The dotted module path to the migrations module, suitable + for lookup in `sys.modules`. + +This migration module just adds a marker to the `version` table. + + >>> with open(os.path.join(path, '__init__.py'), 'w') as fp: + ... pass + >>> with open(os.path.join(path, 's_20120211000000.py'), 'w') as fp: + ... print >> fp, """ + ... from __future__ import unicode_literals + ... from mailman.model.version import Version + ... def upgrade(database, store, version, module_path): + ... v = Version(component='test', version=version) + ... store.add(v) + ... database.load_schema(store, version, None, module_path) + ... """ + +This will load the new migration, since it hasn't been loaded before. + + >>> config.db.load_migrations() + >>> results = config.db.store.find(Version, component='schema') + >>> for result in sorted(result.version for result in results): + ... print result + 00000000000000 + 20120211000000 + >>> test = config.db.store.find(Version, component='test').one() + >>> print test.version + 20120211000000 + +Migrations will only be loaded once. + + >>> with open(os.path.join(path, 's_20120211000001.py'), 'w') as fp: + ... print >> fp, """ + ... from __future__ import unicode_literals + ... from mailman.model.version import Version + ... _marker = 801 + ... def upgrade(database, store, version, module_path): + ... global _marker + ... # Pad enough zeros on the left to reach 14 characters wide. + ... marker = '{0:=#014d}'.format(_marker) + ... _marker += 1 + ... v = Version(component='test', version=marker) + ... store.add(v) + ... database.load_schema(store, version, None, module_path) + ... """ + +The first time we load this new migration, we'll get the 801 marker. + + >>> config.db.load_migrations() + >>> results = config.db.store.find(Version, component='schema') + >>> for result in sorted(result.version for result in results): + ... print result + 00000000000000 + 20120211000000 + 20120211000001 + >>> test = config.db.store.find(Version, component='test') + >>> for marker in sorted(marker.version for marker in test): + ... print marker + 00000000000801 + 20120211000000 + +We do not get an 802 marker because the migration has already been loaded. + + >>> config.db.load_migrations() + >>> results = config.db.store.find(Version, component='schema') + >>> for result in sorted(result.version for result in results): + ... print result + 00000000000000 + 20120211000000 + 20120211000001 + >>> test = config.db.store.find(Version, component='test') + >>> for marker in sorted(marker.version for marker in test): + ... print marker + 00000000000801 + 20120211000000 + +.. Clean up the temporary directory:: + + >>> config.pop('migrations') + >>> sys.path.remove(tempdir) + >>> import shutil + >>> shutil.rmtree(tempdir) diff --git a/src/mailman/database/model.py b/src/mailman/database/model.py index 9f6bf9845..c45517c9b 100644 --- a/src/mailman/database/model.py +++ b/src/mailman/database/model.py @@ -25,6 +25,8 @@ __all__ = [ ] +import sys + from operator import attrgetter from storm.properties import PropertyPublisherMeta @@ -50,12 +52,37 @@ class ModelMeta(PropertyPublisherMeta): @staticmethod def _reset(store): from mailman.config import config + from mailman.model.version import Version config.db._pre_reset(store) + # Give each schema migration a chance to do its pre-reset. See below + # for calling its post reset too. + versions = sorted(version.version for version in + store.find(Version, component='schema')) + migrations = {} + for version in versions: + # We have to give the migrations module that loaded this version a + # chance to do both pre- and post-reset operations. The following + # find the actual the module path for the migration. See + # StormBaseDatabase.load_schema(). + migration = store.find(Version, component=version).one() + if migration is None: + continue + migrations[version] = module_path = migration.version + module = sys.modules[module_path] + pre_reset = getattr(module, 'pre_reset', None) + if pre_reset is not None: + pre_reset(store) # Make sure this is deterministic, by sorting on the storm table name. classes = sorted(ModelMeta._class_registry, key=attrgetter('__storm_table__')) for model_class in classes: store.find(model_class).remove() + # Now give each migration a chance to do post-reset operations. + for version in versions: + module = sys.modules[migrations[version]] + post_reset = getattr(module, 'post_reset', None) + if post_reset is not None: + post_reset(store) config.db._post_reset(store) diff --git a/src/mailman/database/postgresql.py b/src/mailman/database/postgresql.py index 9ad8f74b5..988f7a1af 100644 --- a/src/mailman/database/postgresql.py +++ b/src/mailman/database/postgresql.py @@ -26,7 +26,6 @@ __all__ = [ from operator import attrgetter -from pkg_resources import resource_string from mailman.database.base import StormBaseDatabase @@ -35,6 +34,8 @@ from mailman.database.base import StormBaseDatabase class PostgreSQLDatabase(StormBaseDatabase): """Database class for PostgreSQL.""" + TAG = 'postgres' + def _database_exists(self, store): """See `BaseDatabase`.""" table_query = ('SELECT table_name FROM information_schema.tables ' @@ -43,16 +44,13 @@ class PostgreSQLDatabase(StormBaseDatabase): store.execute(table_query)) return 'version' in table_names - def _get_schema(self): - """See `BaseDatabase`.""" - return resource_string('mailman.database.sql', 'postgres.sql') - def _post_reset(self, store): """PostgreSQL-specific test suite cleanup. Reset the _id_seq.last_value so that primary key ids restart from zero for new tests. """ + super(PostgreSQLDatabase, self)._post_reset(store) from mailman.database.model import ModelMeta classes = sorted(ModelMeta._class_registry, key=attrgetter('__storm_table__')) diff --git a/src/mailman/database/schema/__init__.py b/src/mailman/database/schema/__init__.py new file mode 100644 index 000000000..e69de29bb diff --git a/src/mailman/database/schema/postgres.sql b/src/mailman/database/schema/postgres.sql new file mode 100644 index 000000000..bb209c4aa --- /dev/null +++ b/src/mailman/database/schema/postgres.sql @@ -0,0 +1,345 @@ +CREATE TABLE mailinglist ( + id SERIAL NOT NULL, + -- List identity + list_name TEXT, + mail_host TEXT, + include_list_post_header BOOLEAN, + include_rfc2369_headers BOOLEAN, + -- Attributes not directly modifiable via the web u/i + created_at TIMESTAMP, + admin_member_chunksize INTEGER, + next_request_id INTEGER, + next_digest_number INTEGER, + digest_last_sent_at TIMESTAMP, + volume INTEGER, + last_post_at TIMESTAMP, + accept_these_nonmembers BYTEA, + acceptable_aliases_id INTEGER, + admin_immed_notify BOOLEAN, + admin_notify_mchanges BOOLEAN, + administrivia BOOLEAN, + advertised BOOLEAN, + anonymous_list BOOLEAN, + archive BOOLEAN, + archive_private BOOLEAN, + archive_volume_frequency INTEGER, + -- Automatic responses. + autorespond_owner INTEGER, + autoresponse_owner_text TEXT, + autorespond_postings INTEGER, + autoresponse_postings_text TEXT, + autorespond_requests INTEGER, + autoresponse_request_text TEXT, + autoresponse_grace_period TEXT, + -- Bounces. + forward_unrecognized_bounces_to INTEGER, + process_bounces BOOLEAN, + bounce_info_stale_after TEXT, + bounce_matching_headers TEXT, + bounce_notify_owner_on_disable BOOLEAN, + bounce_notify_owner_on_removal BOOLEAN, + bounce_score_threshold INTEGER, + bounce_you_are_disabled_warnings INTEGER, + bounce_you_are_disabled_warnings_interval TEXT, + -- Content filtering. + filter_content BOOLEAN, + collapse_alternatives BOOLEAN, + convert_html_to_plaintext BOOLEAN, + default_member_action INTEGER, + default_nonmember_action INTEGER, + description TEXT, + digest_footer TEXT, + digest_header TEXT, + digest_is_default BOOLEAN, + digest_send_periodic BOOLEAN, + digest_size_threshold REAL, + digest_volume_frequency INTEGER, + digestable BOOLEAN, + discard_these_nonmembers BYTEA, + emergency BOOLEAN, + encode_ascii_prefixes BOOLEAN, + first_strip_reply_to BOOLEAN, + forward_auto_discards BOOLEAN, + gateway_to_mail BOOLEAN, + gateway_to_news BOOLEAN, + generic_nonmember_action INTEGER, + goodbye_msg TEXT, + header_matches BYTEA, + hold_these_nonmembers BYTEA, + info TEXT, + linked_newsgroup TEXT, + max_days_to_hold INTEGER, + max_message_size INTEGER, + max_num_recipients INTEGER, + member_moderation_notice TEXT, + mime_is_default_digest BOOLEAN, + moderator_password TEXT, + msg_footer TEXT, + msg_header TEXT, + new_member_options INTEGER, + news_moderation INTEGER, + news_prefix_subject_too BOOLEAN, + nntp_host TEXT, + nondigestable BOOLEAN, + nonmember_rejection_notice TEXT, + obscure_addresses BOOLEAN, + personalize INTEGER, + pipeline TEXT, + post_id INTEGER, + preferred_language TEXT, + private_roster BOOLEAN, + real_name TEXT, + reject_these_nonmembers BYTEA, + reply_goes_to_list INTEGER, + reply_to_address TEXT, + require_explicit_destination BOOLEAN, + respond_to_post_requests BOOLEAN, + scrub_nondigest BOOLEAN, + send_goodbye_msg BOOLEAN, + send_reminders BOOLEAN, + send_welcome_msg BOOLEAN, + start_chain TEXT, + subject_prefix TEXT, + subscribe_auto_approval BYTEA, + subscribe_policy INTEGER, + topics BYTEA, + topics_bodylines_limit INTEGER, + topics_enabled BOOLEAN, + unsubscribe_policy INTEGER, + welcome_msg TEXT, + moderation_callback TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE _request ( + id SERIAL NOT NULL, + "key" TEXT, + request_type INTEGER, + data_hash BYTEA, + mailing_list_id INTEGER, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT _request_mailing_list_id_fk + -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); + +CREATE TABLE acceptablealias ( + id SERIAL NOT NULL, + "alias" TEXT NOT NULL, + mailing_list_id INTEGER NOT NULL, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT acceptablealias_mailing_list_id_fk + -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_acceptablealias_mailing_list_id + ON acceptablealias (mailing_list_id); +CREATE INDEX ix_acceptablealias_alias ON acceptablealias ("alias"); + +CREATE TABLE preferences ( + id SERIAL NOT NULL, + acknowledge_posts BOOLEAN, + hide_address BOOLEAN, + preferred_language TEXT, + receive_list_copy BOOLEAN, + receive_own_postings BOOLEAN, + delivery_mode INTEGER, + delivery_status INTEGER, + PRIMARY KEY (id) + ); + +CREATE TABLE address ( + id SERIAL NOT NULL, + email TEXT, + _original TEXT, + real_name TEXT, + verified_on TIMESTAMP, + registered_on TIMESTAMP, + user_id INTEGER, + preferences_id INTEGER, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT address_preferences_id_fk + -- FOREIGN KEY (preferences_id) REFERENCES preferences (id) + ); + +CREATE TABLE "user" ( + id SERIAL NOT NULL, + real_name TEXT, + password BYTEA, + _user_id UUID, + _created_on TIMESTAMP, + _preferred_address_id INTEGER, + preferences_id INTEGER, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT user_preferences_id_fk + -- FOREIGN KEY (preferences_id) REFERENCES preferences (id), + -- XXX: config.db_reset() triggers IntegrityError + -- CONSTRAINT _preferred_address_id_fk + -- FOREIGN KEY (_preferred_address_id) REFERENCES address (id) + ); +CREATE INDEX ix_user_user_id ON "user" (_user_id); + +-- since user and address have circular foreign key refs, the +-- constraint on the address table has to be added after +-- the user table is created +-- +-- XXX: users.rst triggers an IntegrityError +-- ALTER TABLE address ADD +-- CONSTRAINT address_user_id_fk +-- FOREIGN KEY (user_id) REFERENCES "user" (id); + +CREATE TABLE autoresponserecord ( + id SERIAL NOT NULL, + address_id INTEGER, + mailing_list_id INTEGER, + response_type INTEGER, + date_sent TIMESTAMP, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT autoresponserecord_address_id_fk + -- FOREIGN KEY (address_id) REFERENCES address (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT autoresponserecord_mailing_list_id + -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_autoresponserecord_address_id + ON autoresponserecord (address_id); +CREATE INDEX ix_autoresponserecord_mailing_list_id + ON autoresponserecord (mailing_list_id); + +CREATE TABLE bounceevent ( + id SERIAL NOT NULL, + list_name TEXT, + email TEXT, + "timestamp" TIMESTAMP, + message_id TEXT, + context INTEGER, + processed BOOLEAN, + PRIMARY KEY (id) + ); + +CREATE TABLE contentfilter ( + id SERIAL NOT NULL, + mailing_list_id INTEGER, + filter_pattern TEXT, + filter_type INTEGER, + PRIMARY KEY (id), + CONSTRAINT contentfilter_mailing_list_id + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_contentfilter_mailing_list_id + ON contentfilter (mailing_list_id); + +CREATE TABLE domain ( + id SERIAL NOT NULL, + mail_host TEXT, + base_url TEXT, + description TEXT, + contact_address TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE language ( + id SERIAL NOT NULL, + code TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE member ( + id SERIAL NOT NULL, + _member_id UUID, + role INTEGER, + mailing_list TEXT, + moderation_action INTEGER, + address_id INTEGER, + preferences_id INTEGER, + user_id INTEGER, + PRIMARY KEY (id) + -- XXX: config.db_reset() triggers IntegrityError + -- , + -- CONSTRAINT member_address_id_fk + -- FOREIGN KEY (address_id) REFERENCES address (id), + -- XXX: config.db_reset() triggers IntegrityError + -- CONSTRAINT member_preferences_id_fk + -- FOREIGN KEY (preferences_id) REFERENCES preferences (id), + -- CONSTRAINT member_user_id_fk + -- FOREIGN KEY (user_id) REFERENCES "user" (id) + ); +CREATE INDEX ix_member__member_id ON member (_member_id); +CREATE INDEX ix_member_address_id ON member (address_id); +CREATE INDEX ix_member_preferences_id ON member (preferences_id); + +CREATE TABLE message ( + id SERIAL NOT NULL, + message_id_hash BYTEA, + path BYTEA, + message_id TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE onelastdigest ( + id SERIAL NOT NULL, + mailing_list_id INTEGER, + address_id INTEGER, + delivery_mode INTEGER, + PRIMARY KEY (id), + CONSTRAINT onelastdigest_mailing_list_id_fk + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist(id), + CONSTRAINT onelastdigest_address_id_fk + FOREIGN KEY (address_id) REFERENCES address(id) + ); + +CREATE TABLE pended ( + id SERIAL NOT NULL, + token BYTEA, + expiration_date TIMESTAMP, + PRIMARY KEY (id) + ); + +CREATE TABLE pendedkeyvalue ( + id SERIAL NOT NULL, + "key" TEXT, + value TEXT, + pended_id INTEGER, + PRIMARY KEY (id) + -- , + -- XXX: config.db_reset() triggers IntegrityError + -- CONSTRAINT pendedkeyvalue_pended_id_fk + -- FOREIGN KEY (pended_id) REFERENCES pended (id) + ); + +CREATE TABLE version ( + id SERIAL NOT NULL, + component TEXT, + version TEXT, + PRIMARY KEY (id) + ); + +CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id); +CREATE INDEX ix_address_preferences_id ON address (preferences_id); +CREATE INDEX ix_address_user_id ON address (user_id); +CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id); +CREATE INDEX ix_user_preferences_id ON "user" (preferences_id); + +CREATE TABLE ban ( + id SERIAL NOT NULL, + email TEXT, + mailing_list TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE uid ( + -- Keep track of all assigned unique ids to prevent re-use. + id SERIAL NOT NULL, + uid UUID, + PRIMARY KEY (id) + ); +CREATE INDEX ix_uid_uid ON uid (uid); diff --git a/src/mailman/database/schema/s_00000000000000_base.py b/src/mailman/database/schema/s_00000000000000_base.py new file mode 100644 index 000000000..d703088d6 --- /dev/null +++ b/src/mailman/database/schema/s_00000000000000_base.py @@ -0,0 +1,55 @@ +# Copyright (C) 2012 by the Free Software Foundation, Inc. +# +# This file is part of GNU Mailman. +# +# GNU Mailman is free software: you can redistribute it and/or modify it under +# the terms of the GNU General Public License as published by the Free +# Software Foundation, either version 3 of the License, or (at your option) +# any later version. +# +# GNU Mailman is distributed in the hope that it will be useful, but WITHOUT +# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or +# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for +# more details. +# +# You should have received a copy of the GNU General Public License along with +# GNU Mailman. If not, see . + +"""Load the base schema.""" + +from __future__ import absolute_import, print_function, unicode_literals + +__metaclass__ = type +__all__ = [ + 'upgrade', + 'post_reset', + 'pre_reset', + ] + + +_migration_path = None +VERSION = '00000000000000' + + + +def upgrade(database, store, version, module_path): + filename = '{0}.sql'.format(database.TAG) + database.load_schema(store, version, filename, module_path) + + +def pre_reset(store): + global _migration_path + # Save the entry in the Version table for the test suite reset. This will + # be restored below. + from mailman.model.version import Version + result = store.find(Version, component=VERSION).one() + # Yes, we abuse this field. + _migration_path = result.version + + +def post_reset(store): + from mailman.model.version import Version + # We need to preserve the Version table entry for this migration, since + # its existence defines the fact that the tables have been loaded. + store.add(Version(component='schema', version=VERSION)) + store.add(Version(component=VERSION, version=_migration_path)) diff --git a/src/mailman/database/schema/sqlite.sql b/src/mailman/database/schema/sqlite.sql new file mode 100644 index 000000000..74d523cf2 --- /dev/null +++ b/src/mailman/database/schema/sqlite.sql @@ -0,0 +1,321 @@ +PRAGMA foreign_keys = ON; + +CREATE TABLE _request ( + id INTEGER NOT NULL, + "key" TEXT, + request_type INTEGER, + data_hash TEXT, + mailing_list_id INTEGER, + PRIMARY KEY (id), + CONSTRAINT _request_mailing_list_id_fk + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); + +CREATE TABLE acceptablealias ( + id INTEGER NOT NULL, + "alias" TEXT NOT NULL, + mailing_list_id INTEGER NOT NULL, + PRIMARY KEY (id), + CONSTRAINT acceptablealias_mailing_list_id_fk + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_acceptablealias_mailing_list_id + ON acceptablealias (mailing_list_id); +CREATE INDEX ix_acceptablealias_alias ON acceptablealias ("alias"); + +CREATE TABLE address ( + id INTEGER NOT NULL, + email TEXT, + _original TEXT, + real_name TEXT, + verified_on TIMESTAMP, + registered_on TIMESTAMP, + user_id INTEGER, + preferences_id INTEGER, + PRIMARY KEY (id), + CONSTRAINT address_user_id_fk + FOREIGN KEY (user_id) REFERENCES user (id), + CONSTRAINT address_preferences_id_fk + FOREIGN KEY (preferences_id) REFERENCES preferences (id) + ); + +CREATE TABLE autoresponserecord ( + id INTEGER NOT NULL, + address_id INTEGER, + mailing_list_id INTEGER, + response_type INTEGER, + date_sent TIMESTAMP, + PRIMARY KEY (id), + CONSTRAINT autoresponserecord_address_id_fk + FOREIGN KEY (address_id) REFERENCES address (id), + CONSTRAINT autoresponserecord_mailing_list_id + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_autoresponserecord_address_id + ON autoresponserecord (address_id); +CREATE INDEX ix_autoresponserecord_mailing_list_id + ON autoresponserecord (mailing_list_id); + +CREATE TABLE bounceevent ( + id INTEGER NOT NULL, + list_name TEXT, + email TEXT, + 'timestamp' TIMESTAMP, + message_id TEXT, + context INTEGER, + processed BOOLEAN, + PRIMARY KEY (id) + ); + +CREATE TABLE contentfilter ( + id INTEGER NOT NULL, + mailing_list_id INTEGER, + filter_pattern TEXT, + filter_type INTEGER, + PRIMARY KEY (id), + CONSTRAINT contentfilter_mailing_list_id + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) + ); +CREATE INDEX ix_contentfilter_mailing_list_id + ON contentfilter (mailing_list_id); + +CREATE TABLE domain ( + id INTEGER NOT NULL, + mail_host TEXT, + base_url TEXT, + description TEXT, + contact_address TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE language ( + id INTEGER NOT NULL, + code TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE mailinglist ( + id INTEGER NOT NULL, + -- List identity + list_name TEXT, + mail_host TEXT, + include_list_post_header BOOLEAN, + include_rfc2369_headers BOOLEAN, + -- Attributes not directly modifiable via the web u/i + created_at TIMESTAMP, + admin_member_chunksize INTEGER, + next_request_id INTEGER, + next_digest_number INTEGER, + digest_last_sent_at TIMESTAMP, + volume INTEGER, + last_post_at TIMESTAMP, + accept_these_nonmembers BLOB, + acceptable_aliases_id INTEGER, + admin_immed_notify BOOLEAN, + admin_notify_mchanges BOOLEAN, + administrivia BOOLEAN, + advertised BOOLEAN, + anonymous_list BOOLEAN, + archive BOOLEAN, + archive_private BOOLEAN, + archive_volume_frequency INTEGER, + -- Automatic responses. + autorespond_owner INTEGER, + autoresponse_owner_text TEXT, + autorespond_postings INTEGER, + autoresponse_postings_text TEXT, + autorespond_requests INTEGER, + autoresponse_request_text TEXT, + autoresponse_grace_period TEXT, + -- Bounces. + forward_unrecognized_bounces_to INTEGER, + process_bounces BOOLEAN, + bounce_info_stale_after TEXT, + bounce_matching_headers TEXT, + bounce_notify_owner_on_disable BOOLEAN, + bounce_notify_owner_on_removal BOOLEAN, + bounce_score_threshold INTEGER, + bounce_you_are_disabled_warnings INTEGER, + bounce_you_are_disabled_warnings_interval TEXT, + -- Content filtering. + filter_content BOOLEAN, + collapse_alternatives BOOLEAN, + convert_html_to_plaintext BOOLEAN, + default_member_action INTEGER, + default_nonmember_action INTEGER, + description TEXT, + digest_footer TEXT, + digest_header TEXT, + digest_is_default BOOLEAN, + digest_send_periodic BOOLEAN, + digest_size_threshold FLOAT, + digest_volume_frequency INTEGER, + digestable BOOLEAN, + discard_these_nonmembers BLOB, + emergency BOOLEAN, + encode_ascii_prefixes BOOLEAN, + first_strip_reply_to BOOLEAN, + forward_auto_discards BOOLEAN, + gateway_to_mail BOOLEAN, + gateway_to_news BOOLEAN, + generic_nonmember_action INTEGER, + goodbye_msg TEXT, + header_matches BLOB, + hold_these_nonmembers BLOB, + info TEXT, + linked_newsgroup TEXT, + max_days_to_hold INTEGER, + max_message_size INTEGER, + max_num_recipients INTEGER, + member_moderation_notice TEXT, + mime_is_default_digest BOOLEAN, + moderator_password TEXT, + msg_footer TEXT, + msg_header TEXT, + new_member_options INTEGER, + news_moderation INTEGER, + news_prefix_subject_too BOOLEAN, + nntp_host TEXT, + nondigestable BOOLEAN, + nonmember_rejection_notice TEXT, + obscure_addresses BOOLEAN, + personalize INTEGER, + pipeline TEXT, + post_id INTEGER, + preferred_language TEXT, + private_roster BOOLEAN, + real_name TEXT, + reject_these_nonmembers BLOB, + reply_goes_to_list INTEGER, + reply_to_address TEXT, + require_explicit_destination BOOLEAN, + respond_to_post_requests BOOLEAN, + scrub_nondigest BOOLEAN, + send_goodbye_msg BOOLEAN, + send_reminders BOOLEAN, + send_welcome_msg BOOLEAN, + start_chain TEXT, + subject_prefix TEXT, + subscribe_auto_approval BLOB, + subscribe_policy INTEGER, + topics BLOB, + topics_bodylines_limit INTEGER, + topics_enabled BOOLEAN, + unsubscribe_policy INTEGER, + welcome_msg TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE member ( + id INTEGER NOT NULL, + _member_id TEXT, + role INTEGER, + mailing_list TEXT, + moderation_action INTEGER, + address_id INTEGER, + preferences_id INTEGER, + user_id INTEGER, + PRIMARY KEY (id), + CONSTRAINT member_address_id_fk + FOREIGN KEY (address_id) REFERENCES address (id), + CONSTRAINT member_preferences_id_fk + FOREIGN KEY (preferences_id) REFERENCES preferences (id) + CONSTRAINT member_user_id_fk + FOREIGN KEY (user_id) REFERENCES user (id) + ); +CREATE INDEX ix_member__member_id ON member (_member_id); +CREATE INDEX ix_member_address_id ON member (address_id); +CREATE INDEX ix_member_preferences_id ON member (preferences_id); + +CREATE TABLE message ( + id INTEGER NOT NULL, + message_id_hash TEXT, + path TEXT, + message_id TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE onelastdigest ( + id INTEGER NOT NULL, + mailing_list_id INTEGER, + address_id INTEGER, + delivery_mode INTEGER, + PRIMARY KEY (id), + CONSTRAINT onelastdigest_mailing_list_id_fk + FOREIGN KEY (mailing_list_id) REFERENCES mailinglist(id), + CONSTRAINT onelastdigest_address_id_fk + FOREIGN KEY (address_id) REFERENCES address(id) + ); + +CREATE TABLE pended ( + id INTEGER NOT NULL, + token TEXT, + expiration_date TIMESTAMP, + PRIMARY KEY (id) + ); + +CREATE TABLE pendedkeyvalue ( + id INTEGER NOT NULL, + "key" TEXT, + value TEXT, + pended_id INTEGER, + PRIMARY KEY (id), + CONSTRAINT pendedkeyvalue_pended_id_fk + FOREIGN KEY (pended_id) REFERENCES pended (id) + ); + +CREATE TABLE preferences ( + id INTEGER NOT NULL, + acknowledge_posts BOOLEAN, + hide_address BOOLEAN, + preferred_language TEXT, + receive_list_copy BOOLEAN, + receive_own_postings BOOLEAN, + delivery_mode INTEGER, + delivery_status INTEGER, + PRIMARY KEY (id) + ); + +CREATE TABLE user ( + id INTEGER NOT NULL, + real_name TEXT, + password BINARY, + _user_id TEXT, + _created_on TIMESTAMP, + _preferred_address_id INTEGER, + preferences_id INTEGER, + PRIMARY KEY (id), + CONSTRAINT user_preferences_id_fk + FOREIGN KEY (preferences_id) REFERENCES preferences (id), + CONSTRAINT _preferred_address_id_fk + FOREIGN KEY (_preferred_address_id) REFERENCES address (id) + ); +CREATE INDEX ix_user_user_id ON user (_user_id); + +CREATE TABLE version ( + id INTEGER NOT NULL, + component TEXT, + version TEXT, + PRIMARY KEY (id) + ); + +CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id); +CREATE INDEX ix_address_preferences_id ON address (preferences_id); +CREATE INDEX ix_address_user_id ON address (user_id); +CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id); +CREATE INDEX ix_user_preferences_id ON user (preferences_id); + +CREATE TABLE ban ( + id INTEGER NOT NULL, + email TEXT, + mailing_list TEXT, + PRIMARY KEY (id) + ); + +CREATE TABLE uid ( + -- Keep track of all assigned unique ids to prevent re-use. + id INTEGER NOT NULL, + uid TEXT, + PRIMARY KEY (id) + ); +CREATE INDEX ix_uid_uid ON uid (uid); diff --git a/src/mailman/database/sql/__init__.py b/src/mailman/database/sql/__init__.py deleted file mode 100644 index e69de29bb..000000000 diff --git a/src/mailman/database/sql/postgres.sql b/src/mailman/database/sql/postgres.sql deleted file mode 100644 index 60c05e340..000000000 --- a/src/mailman/database/sql/postgres.sql +++ /dev/null @@ -1,345 +0,0 @@ -CREATE TABLE mailinglist ( - id SERIAL NOT NULL, - -- List identity - list_name TEXT, - mail_host TEXT, - include_list_post_header BOOLEAN, - include_rfc2369_headers BOOLEAN, - -- Attributes not directly modifiable via the web u/i - created_at TIMESTAMP, - admin_member_chunksize INTEGER, - next_request_id INTEGER, - next_digest_number INTEGER, - digest_last_sent_at TIMESTAMP, - volume INTEGER, - last_post_at TIMESTAMP, - accept_these_nonmembers BYTEA, - acceptable_aliases_id INTEGER, - admin_immed_notify BOOLEAN, - admin_notify_mchanges BOOLEAN, - administrivia BOOLEAN, - advertised BOOLEAN, - anonymous_list BOOLEAN, - archive BOOLEAN, - archive_private BOOLEAN, - archive_volume_frequency INTEGER, - -- Automatic responses. - autorespond_owner INTEGER, - autoresponse_owner_text TEXT, - autorespond_postings INTEGER, - autoresponse_postings_text TEXT, - autorespond_requests INTEGER, - autoresponse_request_text TEXT, - autoresponse_grace_period TEXT, - -- Bounces. - forward_unrecognized_bounces_to INTEGER, - process_bounces BOOLEAN, - bounce_info_stale_after TEXT, - bounce_matching_headers TEXT, - bounce_notify_owner_on_disable BOOLEAN, - bounce_notify_owner_on_removal BOOLEAN, - bounce_score_threshold INTEGER, - bounce_you_are_disabled_warnings INTEGER, - bounce_you_are_disabled_warnings_interval TEXT, - -- Content filtering. - filter_content BOOLEAN, - collapse_alternatives BOOLEAN, - convert_html_to_plaintext BOOLEAN, - default_member_action INTEGER, - default_nonmember_action INTEGER, - description TEXT, - digest_footer TEXT, - digest_header TEXT, - digest_is_default BOOLEAN, - digest_send_periodic BOOLEAN, - digest_size_threshold REAL, - digest_volume_frequency INTEGER, - digestable BOOLEAN, - discard_these_nonmembers BYTEA, - emergency BOOLEAN, - encode_ascii_prefixes BOOLEAN, - first_strip_reply_to BOOLEAN, - forward_auto_discards BOOLEAN, - gateway_to_mail BOOLEAN, - gateway_to_news BOOLEAN, - generic_nonmember_action INTEGER, - goodbye_msg TEXT, - header_matches BYTEA, - hold_these_nonmembers BYTEA, - info TEXT, - linked_newsgroup TEXT, - max_days_to_hold INTEGER, - max_message_size INTEGER, - max_num_recipients INTEGER, - member_moderation_notice TEXT, - mime_is_default_digest BOOLEAN, - moderator_password TEXT, - msg_footer TEXT, - msg_header TEXT, - new_member_options INTEGER, - news_moderation INTEGER, - news_prefix_subject_too BOOLEAN, - nntp_host TEXT, - nondigestable BOOLEAN, - nonmember_rejection_notice TEXT, - obscure_addresses BOOLEAN, - personalize INTEGER, - pipeline TEXT, - post_id INTEGER, - preferred_language TEXT, - private_roster BOOLEAN, - real_name TEXT, - reject_these_nonmembers BYTEA, - reply_goes_to_list INTEGER, - reply_to_address TEXT, - require_explicit_destination BOOLEAN, - respond_to_post_requests BOOLEAN, - scrub_nondigest BOOLEAN, - send_goodbye_msg BOOLEAN, - send_reminders BOOLEAN, - send_welcome_msg BOOLEAN, - start_chain TEXT, - subject_prefix TEXT, - subscribe_auto_approval BYTEA, - subscribe_policy INTEGER, - topics BYTEA, - topics_bodylines_limit INTEGER, - topics_enabled BOOLEAN, - unsubscribe_policy INTEGER, - welcome_msg TEXT, - moderation_callback TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE _request ( - id SERIAL NOT NULL, - "key" TEXT, - request_type INTEGER, - data_hash BYTEA, - mailing_list_id INTEGER, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT _request_mailing_list_id_fk - -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); - -CREATE TABLE acceptablealias ( - id SERIAL NOT NULL, - "alias" TEXT NOT NULL, - mailing_list_id INTEGER NOT NULL, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT acceptablealias_mailing_list_id_fk - -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_acceptablealias_mailing_list_id - ON acceptablealias (mailing_list_id); -CREATE INDEX ix_acceptablealias_alias ON acceptablealias ("alias"); - -CREATE TABLE preferences ( - id SERIAL NOT NULL, - acknowledge_posts BOOLEAN, - hide_address BOOLEAN, - preferred_language TEXT, - receive_list_copy BOOLEAN, - receive_own_postings BOOLEAN, - delivery_mode INTEGER, - delivery_status INTEGER, - PRIMARY KEY (id) - ); - -CREATE TABLE address ( - id SERIAL NOT NULL, - email TEXT, - _original TEXT, - real_name TEXT, - verified_on TIMESTAMP, - registered_on TIMESTAMP, - user_id INTEGER, - preferences_id INTEGER, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT address_preferences_id_fk - -- FOREIGN KEY (preferences_id) REFERENCES preferences (id) - ); - -CREATE TABLE "user" ( - id SERIAL NOT NULL, - real_name TEXT, - password BYTEA, - _user_id UUID, - _created_on TIMESTAMP, - _preferred_address_id INTEGER, - preferences_id INTEGER, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT user_preferences_id_fk - -- FOREIGN KEY (preferences_id) REFERENCES preferences (id), - -- XXX: config.db_reset() triggers IntegrityError - -- CONSTRAINT _preferred_address_id_fk - -- FOREIGN KEY (_preferred_address_id) REFERENCES address (id) - ); -CREATE INDEX ix_user_user_id ON "user" (_user_id); - --- since user and address have circular foreign key refs, the --- constraint on the address table has to be added after --- the user table is created --- --- XXX: users.rst triggers an IntegrityError --- ALTER TABLE address ADD --- CONSTRAINT address_user_id_fk --- FOREIGN KEY (user_id) REFERENCES "user" (id); - -CREATE TABLE autoresponserecord ( - id SERIAL NOT NULL, - address_id INTEGER, - mailing_list_id INTEGER, - response_type INTEGER, - date_sent TIMESTAMP, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT autoresponserecord_address_id_fk - -- FOREIGN KEY (address_id) REFERENCES address (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT autoresponserecord_mailing_list_id - -- FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_autoresponserecord_address_id - ON autoresponserecord (address_id); -CREATE INDEX ix_autoresponserecord_mailing_list_id - ON autoresponserecord (mailing_list_id); - -CREATE TABLE bounceevent ( - id SERIAL NOT NULL, - list_name TEXT, - email TEXT, - "timestamp" TIMESTAMP, - message_id TEXT, - context INTEGER, - processed BOOLEAN, - PRIMARY KEY (id) - ); - -CREATE TABLE contentfilter ( - id SERIAL NOT NULL, - mailing_list_id INTEGER, - filter_pattern TEXT, - filter_type INTEGER, - PRIMARY KEY (id), - CONSTRAINT contentfilter_mailing_list_id - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_contentfilter_mailing_list_id - ON contentfilter (mailing_list_id); - -CREATE TABLE domain ( - id SERIAL NOT NULL, - mail_host TEXT, - base_url TEXT, - description TEXT, - contact_address TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE language ( - id SERIAL NOT NULL, - code TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE member ( - id SERIAL NOT NULL, - _member_id UUID, - role INTEGER, - mailing_list TEXT, - moderation_action INTEGER, - address_id INTEGER, - preferences_id INTEGER, - user_id INTEGER, - PRIMARY KEY (id) - -- XXX: config.db_reset() triggers IntegrityError - -- , - -- CONSTRAINT member_address_id_fk - -- FOREIGN KEY (address_id) REFERENCES address (id), - -- XXX: config.db_reset() triggers IntegrityError - -- CONSTRAINT member_preferences_id_fk - -- FOREIGN KEY (preferences_id) REFERENCES preferences (id), - -- CONSTRAINT member_user_id_fk - -- FOREIGN KEY (user_id) REFERENCES "user" (id) - ); -CREATE INDEX ix_member__member_id ON member (_member_id); -CREATE INDEX ix_member_address_id ON member (address_id); -CREATE INDEX ix_member_preferences_id ON member (preferences_id); - -CREATE TABLE message ( - id SERIAL NOT NULL, - message_id_hash BYTEA, - path BYTEA, - message_id TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE onelastdigest ( - id SERIAL NOT NULL, - mailing_list_id INTEGER, - address_id INTEGER, - delivery_mode INTEGER, - PRIMARY KEY (id), - CONSTRAINT onelastdigest_mailing_list_id_fk - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist(id), - CONSTRAINT onelastdigest_address_id_fk - FOREIGN KEY (address_id) REFERENCES address(id) - ); - -CREATE TABLE pended ( - id SERIAL NOT NULL, - token BYTEA, - expiration_date TIMESTAMP, - PRIMARY KEY (id) - ); - -CREATE TABLE pendedkeyvalue ( - id SERIAL NOT NULL, - "key" TEXT, - value TEXT, - pended_id INTEGER, - PRIMARY KEY (id) - -- , - -- XXX: config.db_reset() triggers IntegrityError - -- CONSTRAINT pendedkeyvalue_pended_id_fk - -- FOREIGN KEY (pended_id) REFERENCES pended (id) - ); - -CREATE TABLE version ( - id SERIAL NOT NULL, - component TEXT, - version INTEGER, - PRIMARY KEY (id) - ); - -CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id); -CREATE INDEX ix_address_preferences_id ON address (preferences_id); -CREATE INDEX ix_address_user_id ON address (user_id); -CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id); -CREATE INDEX ix_user_preferences_id ON "user" (preferences_id); - -CREATE TABLE ban ( - id SERIAL NOT NULL, - email TEXT, - mailing_list TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE uid ( - -- Keep track of all assigned unique ids to prevent re-use. - id SERIAL NOT NULL, - uid UUID, - PRIMARY KEY (id) - ); -CREATE INDEX ix_uid_uid ON uid (uid); diff --git a/src/mailman/database/sql/sqlite.sql b/src/mailman/database/sql/sqlite.sql deleted file mode 100644 index 5987f1879..000000000 --- a/src/mailman/database/sql/sqlite.sql +++ /dev/null @@ -1,321 +0,0 @@ -PRAGMA foreign_keys = ON; - -CREATE TABLE _request ( - id INTEGER NOT NULL, - "key" TEXT, - request_type INTEGER, - data_hash TEXT, - mailing_list_id INTEGER, - PRIMARY KEY (id), - CONSTRAINT _request_mailing_list_id_fk - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); - -CREATE TABLE acceptablealias ( - id INTEGER NOT NULL, - "alias" TEXT NOT NULL, - mailing_list_id INTEGER NOT NULL, - PRIMARY KEY (id), - CONSTRAINT acceptablealias_mailing_list_id_fk - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_acceptablealias_mailing_list_id - ON acceptablealias (mailing_list_id); -CREATE INDEX ix_acceptablealias_alias ON acceptablealias ("alias"); - -CREATE TABLE address ( - id INTEGER NOT NULL, - email TEXT, - _original TEXT, - real_name TEXT, - verified_on TIMESTAMP, - registered_on TIMESTAMP, - user_id INTEGER, - preferences_id INTEGER, - PRIMARY KEY (id), - CONSTRAINT address_user_id_fk - FOREIGN KEY (user_id) REFERENCES user (id), - CONSTRAINT address_preferences_id_fk - FOREIGN KEY (preferences_id) REFERENCES preferences (id) - ); - -CREATE TABLE autoresponserecord ( - id INTEGER NOT NULL, - address_id INTEGER, - mailing_list_id INTEGER, - response_type INTEGER, - date_sent TIMESTAMP, - PRIMARY KEY (id), - CONSTRAINT autoresponserecord_address_id_fk - FOREIGN KEY (address_id) REFERENCES address (id), - CONSTRAINT autoresponserecord_mailing_list_id - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_autoresponserecord_address_id - ON autoresponserecord (address_id); -CREATE INDEX ix_autoresponserecord_mailing_list_id - ON autoresponserecord (mailing_list_id); - -CREATE TABLE bounceevent ( - id INTEGER NOT NULL, - list_name TEXT, - email TEXT, - 'timestamp' TIMESTAMP, - message_id TEXT, - context INTEGER, - processed BOOLEAN, - PRIMARY KEY (id) - ); - -CREATE TABLE contentfilter ( - id INTEGER NOT NULL, - mailing_list_id INTEGER, - filter_pattern TEXT, - filter_type INTEGER, - PRIMARY KEY (id), - CONSTRAINT contentfilter_mailing_list_id - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id) - ); -CREATE INDEX ix_contentfilter_mailing_list_id - ON contentfilter (mailing_list_id); - -CREATE TABLE domain ( - id INTEGER NOT NULL, - mail_host TEXT, - base_url TEXT, - description TEXT, - contact_address TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE language ( - id INTEGER NOT NULL, - code TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE mailinglist ( - id INTEGER NOT NULL, - -- List identity - list_name TEXT, - mail_host TEXT, - include_list_post_header BOOLEAN, - include_rfc2369_headers BOOLEAN, - -- Attributes not directly modifiable via the web u/i - created_at TIMESTAMP, - admin_member_chunksize INTEGER, - next_request_id INTEGER, - next_digest_number INTEGER, - digest_last_sent_at TIMESTAMP, - volume INTEGER, - last_post_at TIMESTAMP, - accept_these_nonmembers BLOB, - acceptable_aliases_id INTEGER, - admin_immed_notify BOOLEAN, - admin_notify_mchanges BOOLEAN, - administrivia BOOLEAN, - advertised BOOLEAN, - anonymous_list BOOLEAN, - archive BOOLEAN, - archive_private BOOLEAN, - archive_volume_frequency INTEGER, - -- Automatic responses. - autorespond_owner INTEGER, - autoresponse_owner_text TEXT, - autorespond_postings INTEGER, - autoresponse_postings_text TEXT, - autorespond_requests INTEGER, - autoresponse_request_text TEXT, - autoresponse_grace_period TEXT, - -- Bounces. - forward_unrecognized_bounces_to INTEGER, - process_bounces BOOLEAN, - bounce_info_stale_after TEXT, - bounce_matching_headers TEXT, - bounce_notify_owner_on_disable BOOLEAN, - bounce_notify_owner_on_removal BOOLEAN, - bounce_score_threshold INTEGER, - bounce_you_are_disabled_warnings INTEGER, - bounce_you_are_disabled_warnings_interval TEXT, - -- Content filtering. - filter_content BOOLEAN, - collapse_alternatives BOOLEAN, - convert_html_to_plaintext BOOLEAN, - default_member_action INTEGER, - default_nonmember_action INTEGER, - description TEXT, - digest_footer TEXT, - digest_header TEXT, - digest_is_default BOOLEAN, - digest_send_periodic BOOLEAN, - digest_size_threshold FLOAT, - digest_volume_frequency INTEGER, - digestable BOOLEAN, - discard_these_nonmembers BLOB, - emergency BOOLEAN, - encode_ascii_prefixes BOOLEAN, - first_strip_reply_to BOOLEAN, - forward_auto_discards BOOLEAN, - gateway_to_mail BOOLEAN, - gateway_to_news BOOLEAN, - generic_nonmember_action INTEGER, - goodbye_msg TEXT, - header_matches BLOB, - hold_these_nonmembers BLOB, - info TEXT, - linked_newsgroup TEXT, - max_days_to_hold INTEGER, - max_message_size INTEGER, - max_num_recipients INTEGER, - member_moderation_notice TEXT, - mime_is_default_digest BOOLEAN, - moderator_password TEXT, - msg_footer TEXT, - msg_header TEXT, - new_member_options INTEGER, - news_moderation INTEGER, - news_prefix_subject_too BOOLEAN, - nntp_host TEXT, - nondigestable BOOLEAN, - nonmember_rejection_notice TEXT, - obscure_addresses BOOLEAN, - personalize INTEGER, - pipeline TEXT, - post_id INTEGER, - preferred_language TEXT, - private_roster BOOLEAN, - real_name TEXT, - reject_these_nonmembers BLOB, - reply_goes_to_list INTEGER, - reply_to_address TEXT, - require_explicit_destination BOOLEAN, - respond_to_post_requests BOOLEAN, - scrub_nondigest BOOLEAN, - send_goodbye_msg BOOLEAN, - send_reminders BOOLEAN, - send_welcome_msg BOOLEAN, - start_chain TEXT, - subject_prefix TEXT, - subscribe_auto_approval BLOB, - subscribe_policy INTEGER, - topics BLOB, - topics_bodylines_limit INTEGER, - topics_enabled BOOLEAN, - unsubscribe_policy INTEGER, - welcome_msg TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE member ( - id INTEGER NOT NULL, - _member_id TEXT, - role INTEGER, - mailing_list TEXT, - moderation_action INTEGER, - address_id INTEGER, - preferences_id INTEGER, - user_id INTEGER, - PRIMARY KEY (id), - CONSTRAINT member_address_id_fk - FOREIGN KEY (address_id) REFERENCES address (id), - CONSTRAINT member_preferences_id_fk - FOREIGN KEY (preferences_id) REFERENCES preferences (id) - CONSTRAINT member_user_id_fk - FOREIGN KEY (user_id) REFERENCES user (id) - ); -CREATE INDEX ix_member__member_id ON member (_member_id); -CREATE INDEX ix_member_address_id ON member (address_id); -CREATE INDEX ix_member_preferences_id ON member (preferences_id); - -CREATE TABLE message ( - id INTEGER NOT NULL, - message_id_hash TEXT, - path TEXT, - message_id TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE onelastdigest ( - id INTEGER NOT NULL, - mailing_list_id INTEGER, - address_id INTEGER, - delivery_mode INTEGER, - PRIMARY KEY (id), - CONSTRAINT onelastdigest_mailing_list_id_fk - FOREIGN KEY (mailing_list_id) REFERENCES mailinglist(id), - CONSTRAINT onelastdigest_address_id_fk - FOREIGN KEY (address_id) REFERENCES address(id) - ); - -CREATE TABLE pended ( - id INTEGER NOT NULL, - token TEXT, - expiration_date TIMESTAMP, - PRIMARY KEY (id) - ); - -CREATE TABLE pendedkeyvalue ( - id INTEGER NOT NULL, - "key" TEXT, - value TEXT, - pended_id INTEGER, - PRIMARY KEY (id), - CONSTRAINT pendedkeyvalue_pended_id_fk - FOREIGN KEY (pended_id) REFERENCES pended (id) - ); - -CREATE TABLE preferences ( - id INTEGER NOT NULL, - acknowledge_posts BOOLEAN, - hide_address BOOLEAN, - preferred_language TEXT, - receive_list_copy BOOLEAN, - receive_own_postings BOOLEAN, - delivery_mode INTEGER, - delivery_status INTEGER, - PRIMARY KEY (id) - ); - -CREATE TABLE user ( - id INTEGER NOT NULL, - real_name TEXT, - password BINARY, - _user_id TEXT, - _created_on TIMESTAMP, - _preferred_address_id INTEGER, - preferences_id INTEGER, - PRIMARY KEY (id), - CONSTRAINT user_preferences_id_fk - FOREIGN KEY (preferences_id) REFERENCES preferences (id), - CONSTRAINT _preferred_address_id_fk - FOREIGN KEY (_preferred_address_id) REFERENCES address (id) - ); -CREATE INDEX ix_user_user_id ON user (_user_id); - -CREATE TABLE version ( - id INTEGER NOT NULL, - component TEXT, - version INTEGER, - PRIMARY KEY (id) - ); - -CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id); -CREATE INDEX ix_address_preferences_id ON address (preferences_id); -CREATE INDEX ix_address_user_id ON address (user_id); -CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id); -CREATE INDEX ix_user_preferences_id ON user (preferences_id); - -CREATE TABLE ban ( - id INTEGER NOT NULL, - email TEXT, - mailing_list TEXT, - PRIMARY KEY (id) - ); - -CREATE TABLE uid ( - -- Keep track of all assigned unique ids to prevent re-use. - id INTEGER NOT NULL, - uid TEXT, - PRIMARY KEY (id) - ); -CREATE INDEX ix_uid_uid ON uid (uid); diff --git a/src/mailman/database/sqlite.py b/src/mailman/database/sqlite.py index f48ea19c7..2677d0d71 100644 --- a/src/mailman/database/sqlite.py +++ b/src/mailman/database/sqlite.py @@ -27,7 +27,6 @@ __all__ = [ import os -from pkg_resources import resource_string from urlparse import urlparse from mailman.database.base import StormBaseDatabase @@ -37,6 +36,8 @@ from mailman.database.base import StormBaseDatabase class SQLiteDatabase(StormBaseDatabase): """Database class for SQLite.""" + TAG = 'sqlite' + def _database_exists(self, store): """See `BaseDatabase`.""" table_query = 'select tbl_name from sqlite_master;' @@ -53,7 +54,3 @@ class SQLiteDatabase(StormBaseDatabase): # Ignore errors if fd > 0: os.close(fd) - - def _get_schema(self): - """See `BaseDatabase`.""" - return resource_string('mailman.database.sql', 'sqlite.sql') -- cgit v1.3.1