[Mailman-Developers] attempting to delete mailing list or domain gives sqlalchemy error
Abhilash Raj
raj.abhilash1 at gmail.com
Thu Mar 5 07:52:31 CET 2015
Hi Andrew,
On Thursday 05 March 2015 06:08 AM, Andrew Stuart wrote:
> Hi Abhilash,
>
> Sorry I should add that I am wondering if the presence of “acceptable aliases” in the list configuration is causing a problem deleting the list that has those aliases. Is that a possibility? The sqlalchemy error mentions acceptable aliases and also my database state contains acceptable aliases records for the list in question.
Actually you have suspected right. Before removing the mailing_list
record, we need to remove all records from `acceptablealias` table. We
can either use a `MailingListDeleteEvent` event or a new `__delete__`
method in `MailingList` model.
The error is caused because deleting domains deletes all the mailing
list associated with it, and since acceptablealias.mailing_list_id is
set to `nullable=False` the deletion of a list is not possible.
@barry: this looks like a bug to me, what do you say?
> thanks
>
> Andrew
>
> On 5 Mar 2015, at 11:16 am, Andrew Stuart <andrew.stuart at supercoders.com.au> wrote:
>
> Hi Abhilash
>
> Requesting your help - do you have some time to help me with this one please? You seem to know a fair bit about the database and sqlalchemy.
>
> Do you have any guesses as to what is going on here? It’s becoming quite a problem for me because the database in its stuck state won’t complete my test suite.
>
> thanks
>
> Andrew
>
>
>
> On 2 Mar 2015, at 7:41 am, Andrew Stuart <andrew.stuart at supercoders.com.au> wrote:
>
> After I run my tests, the Mailman database seems to get into a state in which it is not possible to delete my test mailing list and test domain.
>
> I figure it is something to do with the database state but I can’t see what at this stage.
>
>
> #########################################################################################################
> ###################################Attempting to delete a domain gives sqlalchemy error
> #########################################################################################################
>
> curl -X DELETE --verbose --header "authorization: Basic cmVzdGFkbWluOnJlc3RwYXNz" http://localhost:8001/3.0/domains/mail.example.org
>
> Traceback (most recent call last):
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
> context)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
> cursor.execute(statement, parameters)
> sqlite3.IntegrityError: NOT NULL constraint failed: acceptablealias.mailing_list_id
>
> The above exception was the direct cause of the following exception:
>
> Traceback (most recent call last):
> File "/usr/lib/python3.4/wsgiref/handlers.py", line 137, in run
> self.result = application(self.environ, self.start_response)
> File "/home/ubuntu/mailman/src/mailman/database/transaction.py", line 57, in wrapper
> config.db.commit()
> File "/home/ubuntu/mailman/src/mailman/database/base.py", line 56, in commit
> self.store.commit()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 776, in commit
> self.transaction.commit()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 377, in commit
> self._prepare_impl()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 357, in _prepare_impl
> self.session.flush()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1919, in flush
> self._flush(objects)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2037, in _flush
> transaction.rollback(_capture_exception=True)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
> raise value
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2001, in _flush
> flush_context.execute()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 372, in execute
> rec.execute(self)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 526, in execute
> uow
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 60, in save_obj
> mapper, table, update)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 518, in _emit_update_statements
> execute(statement, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 729, in execute
> return meth(self, multiparams, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
> compiled_sql, distilled_params
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 958, in _execute_context
> context)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
> exc_info
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 181, in reraise
> raise value.with_traceback(tb)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
> context)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: acceptablealias.mailing_list_id 'UPDATE acceptablealias SET mailing_list_id=? WHERE acceptablealias.id = ?' (None, 1)
>
>
>
> #########################################################################################################
> ###################################Attempting to delete a mailing list gives sqlalchemy error
> #########################################################################################################
>
> curl -X DELETE --verbose --header "authorization: Basic cmVzdGFkbWluOnJlc3RwYXNz" http://localhost:8001/3.0/lists/testlist1.mail.example.org
>
> Traceback (most recent call last):
> File "/usr/lib/python3.4/wsgiref/handlers.py", line 137, in run
> self.result = application(self.environ, self.start_response)
> File "/home/ubuntu/mailman/src/mailman/database/transaction.py", line 56, in wrapper
> rtn = function(*args, **kws)
> File "/home/ubuntu/mailman/src/mailman/rest/wsgiapp.py", line 62, in __call__
> environ, start_response)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/falcon/api.py", line 175, in __call__
> responder(req, resp, **params)
> File "/home/ubuntu/mailman/src/mailman/rest/lists.py", line 145, in on_delete
> remove_list(self._mlist)
> File "/home/ubuntu/mailman/src/mailman/app/lifecycle.py", line 101, in remove_list
> call_name(config.mta.incoming).delete(mlist)
> File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 69, in create
> self.regenerate()
> File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 83, in regenerate
> self._generate_lmtp_file(fp)
> File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 116, in _generate_lmtp_file
> for list_name, mail_host in list_manager.name_components:
> File "/home/ubuntu/mailman/src/mailman/model/listmanager.py", line 119, in name_components
> MailingList.list_name):
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/query.py", line 1011, in values
> return iter(q)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/query.py", line 2437, in __iter__
> self.session._autoflush()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1208, in _autoflush
> util.raise_from_cause(e)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
> raise value
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1198, in _autoflush
> self.flush()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1919, in flush
> self._flush(objects)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2037, in _flush
> transaction.rollback(_capture_exception=True)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
> raise value
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2001, in _flush
> flush_context.execute()
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 372, in execute
> rec.execute(self)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 526, in execute
> uow
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 60, in save_obj
> mapper, table, update)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 518, in _emit_update_statements
> execute(statement, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 729, in execute
> return meth(self, multiparams, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
> compiled_sql, distilled_params
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 958, in _execute_context
> context)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
> exc_info
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 181, in reraise
> raise value.with_traceback(tb)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
> context)
> File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (IntegrityError) NOT NULL constraint failed: acceptablealias.mailing_list_id 'UPDATE acceptablealias SET mailing_list_id=? WHERE acceptablealias.id = ?' (None, 1)
>
>
> #########################################################################################################
> This is the database at the time of the errors - note I have had to remove an accumulation of UID and preferences INSERTS.
> #########################################################################################################
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE pended (
> id INTEGER NOT NULL,
> token VARCHAR,
> expiration_date DATETIME,
> PRIMARY KEY (id)
> );
> CREATE TABLE preferences (
> id INTEGER NOT NULL,
> acknowledge_posts BOOLEAN,
> hide_address BOOLEAN,
> preferred_language VARCHAR,
> receive_list_copy BOOLEAN,
> receive_own_postings BOOLEAN,
> delivery_mode INTEGER,
> delivery_status INTEGER,
> PRIMARY KEY (id),
> CHECK (acknowledge_posts IN (0, 1)),
> CHECK (hide_address IN (0, 1)),
> CHECK (receive_list_copy IN (0, 1)),
> CHECK (receive_own_postings IN (0, 1))
> );
> CREATE TABLE domain (
> id INTEGER NOT NULL,
> mail_host VARCHAR,
> base_url VARCHAR,
> description VARCHAR,
> contact_address VARCHAR,
> PRIMARY KEY (id)
> );
> INSERT INTO "domain" VALUES(1,'mail.example.org','http://mail.example.org',NULL,'postmaster at mail.example.org');
> CREATE TABLE message (
> id INTEGER NOT NULL,
> message_id VARCHAR,
> message_id_hash VARCHAR,
> path VARCHAR,
> PRIMARY KEY (id)
> );
> CREATE TABLE mailinglist (
> id INTEGER NOT NULL,
> list_name VARCHAR,
> mail_host VARCHAR,
> list_id VARCHAR,
> allow_list_posts BOOLEAN,
> include_rfc2369_headers BOOLEAN,
> advertised BOOLEAN,
> anonymous_list BOOLEAN,
> created_at DATETIME,
> next_request_id INTEGER,
> next_digest_number INTEGER,
> digest_last_sent_at DATETIME,
> volume INTEGER,
> last_post_at DATETIME,
> accept_these_nonmembers BLOB,
> admin_immed_notify BOOLEAN,
> admin_notify_mchanges BOOLEAN,
> administrivia BOOLEAN,
> archive_policy INTEGER,
> autoresponse_grace_period DATETIME,
> autorespond_owner INTEGER,
> autoresponse_owner_text VARCHAR,
> autorespond_postings INTEGER,
> autoresponse_postings_text VARCHAR,
> autorespond_requests INTEGER,
> autoresponse_request_text VARCHAR,
> filter_action INTEGER,
> filter_content BOOLEAN,
> collapse_alternatives BOOLEAN,
> convert_html_to_plaintext BOOLEAN,
> bounce_info_stale_after DATETIME,
> bounce_matching_headers VARCHAR,
> 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 DATETIME,
> forward_unrecognized_bounces_to INTEGER,
> process_bounces BOOLEAN,
> default_member_action INTEGER,
> default_nonmember_action INTEGER,
> description VARCHAR,
> digest_footer_uri VARCHAR,
> digest_header_uri VARCHAR,
> 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,
> footer_uri VARCHAR,
> forward_auto_discards BOOLEAN,
> gateway_to_mail BOOLEAN,
> gateway_to_news BOOLEAN,
> goodbye_message_uri VARCHAR,
> header_matches BLOB,
> header_uri VARCHAR,
> hold_these_nonmembers BLOB,
> info VARCHAR,
> linked_newsgroup VARCHAR,
> max_days_to_hold INTEGER,
> max_message_size INTEGER,
> max_num_recipients INTEGER,
> member_moderation_notice VARCHAR,
> mime_is_default_digest BOOLEAN,
> moderator_password BLOB,
> newsgroup_moderation INTEGER,
> nntp_prefix_subject_too BOOLEAN,
> nondigestable BOOLEAN,
> nonmember_rejection_notice VARCHAR,
> obscure_addresses BOOLEAN,
> owner_chain VARCHAR,
> owner_pipeline VARCHAR,
> personalize INTEGER,
> post_id INTEGER,
> posting_chain VARCHAR,
> posting_pipeline VARCHAR,
> preferred_language VARCHAR,
> display_name VARCHAR,
> reject_these_nonmembers BLOB,
> reply_goes_to_list INTEGER,
> reply_to_address VARCHAR,
> require_explicit_destination BOOLEAN,
> respond_to_post_requests BOOLEAN,
> scrub_nondigest BOOLEAN,
> send_goodbye_message BOOLEAN,
> send_welcome_message BOOLEAN,
> subject_prefix VARCHAR,
> topics BLOB,
> topics_bodylines_limit INTEGER,
> topics_enabled BOOLEAN,
> welcome_message_uri VARCHAR,
> PRIMARY KEY (id),
> CHECK (allow_list_posts IN (0, 1)),
> CHECK (include_rfc2369_headers IN (0, 1)),
> CHECK (advertised IN (0, 1)),
> CHECK (anonymous_list IN (0, 1)),
> CHECK (admin_immed_notify IN (0, 1)),
> CHECK (admin_notify_mchanges IN (0, 1)),
> CHECK (administrivia IN (0, 1)),
> CHECK (filter_content IN (0, 1)),
> CHECK (collapse_alternatives IN (0, 1)),
> CHECK (convert_html_to_plaintext IN (0, 1)),
> CHECK (bounce_notify_owner_on_disable IN (0, 1)),
> CHECK (bounce_notify_owner_on_removal IN (0, 1)),
> CHECK (process_bounces IN (0, 1)),
> CHECK (digest_is_default IN (0, 1)),
> CHECK (digest_send_periodic IN (0, 1)),
> CHECK (digestable IN (0, 1)),
> CHECK (emergency IN (0, 1)),
> CHECK (encode_ascii_prefixes IN (0, 1)),
> CHECK (first_strip_reply_to IN (0, 1)),
> CHECK (forward_auto_discards IN (0, 1)),
> CHECK (gateway_to_mail IN (0, 1)),
> CHECK (gateway_to_news IN (0, 1)),
> CHECK (mime_is_default_digest IN (0, 1)),
> CHECK (nntp_prefix_subject_too IN (0, 1)),
> CHECK (nondigestable IN (0, 1)),
> CHECK (obscure_addresses IN (0, 1)),
> CHECK (require_explicit_destination IN (0, 1)),
> CHECK (respond_to_post_requests IN (0, 1)),
> CHECK (scrub_nondigest IN (0, 1)),
> CHECK (send_goodbye_message IN (0, 1)),
> CHECK (send_welcome_message IN (0, 1)),
> CHECK (topics_enabled IN (0, 1))
> );
> INSERT INTO "mailinglist" VALUES(1,'testlist1','mail.example.org','testlist1.mail.example.org',0,0,0,1,'2015-03-01 20:04:21.186669',1,1,NULL,1,NULL,X'80049503000000000000005D942E',0,1,0,0,'1970-02-15 00:00:00.000000',1,'the owner',2,'the mailing list',1,'the robot',2,1,0,1,'1970-01-08 00:00:00.000000','
> # Lines that *start* with a ''#'' are comments.
> to: friend at public.com
> message-id: relay.comanche.denmark.eu
> from: list at listme.com
> from: .*@uplinkpro.com
> ',1,1,5,3,'1970-01-08 00:00:00.000000',1,1,0,2,'This is my mailing list','mailman:///$listname/$language/footer-generic.txt',NULL,0,1,10.5,1,1,X'80049503000000000000005D942E',0,0,1,'mailman:///$listname/$language/footer-generic.txt',1,0,0,'',X'80049503000000000000005D942E',NULL,X'80049503000000000000005D942E','','',0,40,10,'',0,NULL,0,1,1,'',1,'default-owner-chain','default-owner-pipeline',0,1,'default-posting-chain','virgin','en','Fnords',X'80049503000000000000005D942E',1,'bee at example.com',1,1,0,1,0,'[ant]',X'80049503000000000000005D942E',5,0,'mailman:///welcome.txt');
> CREATE TABLE bounceevent (
> id INTEGER NOT NULL,
> list_id VARCHAR,
> email VARCHAR,
> timestamp DATETIME,
> message_id VARCHAR,
> context INTEGER,
> processed BOOLEAN,
> PRIMARY KEY (id),
> CHECK (processed IN (0, 1))
> );
> INSERT INTO "bounceevent" VALUES(1,'testlist1.mail.example.org','testuser1 at mail.example.org','2015-03-01 20:01:54.950333','<20150301200153.A950280974 at ip-10-223-10-27.us-west-1.compute.internal>',1,0);
> INSERT INTO "bounceevent" VALUES(2,'testlist1.mail.example.org','testuser1 at mail.example.org','2015-03-01 20:01:56.083932','<20150301200155.C229880974 at ip-10-223-10-27.us-west-1.compute.internal>',1,0);
> INSERT INTO "bounceevent" VALUES(3,'testlist1.mail.example.org','testuser1 at mail.example.org','2015-03-01 20:02:20.319257','<20150301200219.244BE80974 at ip-10-223-10-27.us-west-1.compute.internal>',1,0);
> INSERT INTO "bounceevent" VALUES(4,'testlist1.mail.example.org','testuser1 at mail.example.org','2015-03-01 20:03:14.618731','<20150301200313.BD8E180974 at ip-10-223-10-27.us-west-1.compute.internal>',1,0);
> CREATE TABLE uid (
> id INTEGER NOT NULL,
> uid CHAR(32),
> PRIMARY KEY (id)
> );
> CREATE TABLE ban (
> id INTEGER NOT NULL,
> email VARCHAR,
> list_id VARCHAR,
> PRIMARY KEY (id)
> );
> CREATE TABLE user (
> id INTEGER NOT NULL,
> display_name VARCHAR,
> password VARCHAR,
> _user_id CHAR(32),
> _created_on DATETIME,
> _preferred_address_id INTEGER,
> preferences_id INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(preferences_id) REFERENCES preferences (id)
> );
> INSERT INTO "user" VALUES(1,'Server Owner','$6$rounds=101751$cOyechNgTQqeyfTJ$2lHEhtTCYTUuQ37CkZ/ncyptVRW3I9T61WxW8BV6sbzMa2KDJBoU6uRpiWyowbtVej.QTZnL9Ww0YXXM3lPdb/','4e0a784aeaa84b37b0e731aef00683aa','2015-03-01 20:00:41.531120',NULL,2);
> INSERT INTO "user" VALUES(2,'test user 1','$6$rounds=90188$eLE.L0U2hfbpa9QG$SKzS7PNvGiEvPjKSTDjG5PgDtIvsjARyt6HOtia24zT3eT3w99TCuXlL2ZrL.t7B3xLxX4PuWTolrjd1lZ6Qg.','23856abb3f414114b9cfde92d9557ecb','2015-03-01 20:00:58.143566',NULL,34);
> INSERT INTO "user" VALUES(4,'','$6$rounds=94146$ydwCyzFScrDi8mJt$Ia4w6zMnEdePdWvm3vuBrn8d0MpWstdmN0mLtImqJ3k/aLaEkZShgw.0aZU4Q7eh1sTuxUAtY7SLkSOWYj0RN.','c5e5dfd8a11e439a868cba6bdedc966e','2015-03-01 20:01:20.944564',NULL,92);
> CREATE TABLE listarchiver (
> id INTEGER NOT NULL,
> mailing_list_id INTEGER NOT NULL,
> name VARCHAR NOT NULL,
> _is_enabled BOOLEAN,
> PRIMARY KEY (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id),
> CHECK (_is_enabled IN (0, 1))
> );
> INSERT INTO "listarchiver" VALUES(1,1,'prototype',1);
> INSERT INTO "listarchiver" VALUES(2,1,'mail-archive',1);
> INSERT INTO "listarchiver" VALUES(3,1,'mhonarc',1);
> CREATE TABLE acceptablealias (
> id INTEGER NOT NULL,
> mailing_list_id INTEGER NOT NULL,
> alias VARCHAR NOT NULL,
> PRIMARY KEY (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
> );
> INSERT INTO "acceptablealias" VALUES(1,1,'one at example.com');
> INSERT INTO "acceptablealias" VALUES(2,1,'two at example.com');
> CREATE TABLE pendedkeyvalue (
> id INTEGER NOT NULL,
> "key" VARCHAR,
> value VARCHAR,
> pended_id INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(pended_id) REFERENCES pended (id)
> );
> CREATE TABLE contentfilter (
> id INTEGER NOT NULL,
> mailing_list_id INTEGER,
> filter_type INTEGER,
> filter_pattern VARCHAR,
> PRIMARY KEY (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
> );
> CREATE TABLE _request (
> id INTEGER NOT NULL,
> "key" VARCHAR,
> request_type INTEGER,
> data_hash VARCHAR,
> mailing_list_id INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
> );
> CREATE TABLE address (
> id INTEGER NOT NULL,
> email VARCHAR,
> _original VARCHAR,
> display_name VARCHAR,
> verified_on DATETIME,
> registered_on DATETIME,
> user_id INTEGER,
> preferences_id INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(user_id) REFERENCES user (id),
> FOREIGN KEY(preferences_id) REFERENCES preferences (id)
> );
> INSERT INTO "address" VALUES(1,'theboss at mail.example.org',NULL,'Server Owner',NULL,'2015-03-01 20:00:41.530793',1,1);
> CREATE TABLE autoresponserecord (
> id INTEGER NOT NULL,
> address_id INTEGER,
> mailing_list_id INTEGER,
> response_type INTEGER,
> date_sent DATE,
> PRIMARY KEY (id),
> FOREIGN KEY(address_id) REFERENCES address (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
> );
> CREATE TABLE onelastdigest (
> id INTEGER NOT NULL,
> mailing_list_id INTEGER,
> address_id INTEGER,
> delivery_mode INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id),
> FOREIGN KEY(address_id) REFERENCES address (id)
> );
> CREATE TABLE member (
> id INTEGER NOT NULL,
> _member_id CHAR(32),
> role INTEGER,
> list_id VARCHAR,
> moderation_action INTEGER,
> address_id INTEGER,
> preferences_id INTEGER,
> user_id INTEGER,
> PRIMARY KEY (id),
> FOREIGN KEY(address_id) REFERENCES address (id),
> FOREIGN KEY(preferences_id) REFERENCES preferences (id),
> FOREIGN KEY(user_id) REFERENCES user (id)
> );
> CREATE TABLE alembic_version (
> version_num VARCHAR(32) NOT NULL
> );
> INSERT INTO "alembic_version" VALUES('33e1f5f6fa8');
> CREATE INDEX ix_uid_uid ON uid (uid);
> CREATE INDEX ix_user_preferences_id ON user (preferences_id);
> CREATE INDEX ix_user__user_id ON user (_user_id);
> CREATE INDEX ix_listarchiver_mailing_list_id ON listarchiver (mailing_list_id);
> CREATE INDEX ix_acceptablealias_mailing_list_id ON acceptablealias (mailing_list_id);
> CREATE INDEX ix_acceptablealias_alias ON acceptablealias (alias);
> CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id);
> CREATE INDEX ix_contentfilter_mailing_list_id ON contentfilter (mailing_list_id);
> CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id);
> CREATE INDEX ix_address_user_id ON address (user_id);
> CREATE INDEX ix_address_preferences_id ON address (preferences_id);
> CREATE INDEX ix_autoresponserecord_address_id ON autoresponserecord (address_id);
> CREATE INDEX ix_autoresponserecord_mailing_list_id ON autoresponserecord (mailing_list_id);
> COMMIT;
>
>
--
thanks,
Abhilash Raj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/mailman-developers/attachments/20150305/4036943a/attachment-0001.sig>
More information about the Mailman-Developers
mailing list