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.stu...@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.stu...@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,'postmas...@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: fri...@public.com > message-id: relay.comanche.denmark.eu > from: l...@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,'b...@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','testus...@mail.example.org','2015-03-01 > > 20:01:54.950333','<20150301200153.a950280...@ip-10-223-10-27.us-west-1.compute.internal>',1,0); > INSERT INTO "bounceevent" > VALUES(2,'testlist1.mail.example.org','testus...@mail.example.org','2015-03-01 > > 20:01:56.083932','<20150301200155.c229880...@ip-10-223-10-27.us-west-1.compute.internal>',1,0); > INSERT INTO "bounceevent" > VALUES(3,'testlist1.mail.example.org','testus...@mail.example.org','2015-03-01 > > 20:02:20.319257','<20150301200219.244be80...@ip-10-223-10-27.us-west-1.compute.internal>',1,0); > INSERT INTO "bounceevent" > VALUES(4,'testlist1.mail.example.org','testus...@mail.example.org','2015-03-01 > > 20:03:14.618731','<20150301200313.bd8e180...@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,'o...@example.com'); > INSERT INTO "acceptablealias" VALUES(2,1,'t...@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,'theb...@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
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Mailman-Developers mailing list Mailman-Developers@python.org https://mail.python.org/mailman/listinfo/mailman-developers Mailman FAQ: http://wiki.list.org/x/AgA3 Searchable Archives: http://www.mail-archive.com/mailman-developers%40python.org/ Unsubscribe: https://mail.python.org/mailman/options/mailman-developers/archive%40jab.org Security Policy: http://wiki.list.org/x/QIA9
