Using the messages table from dbmail-1.2.3 as an example: dbmail=# \d messages <snip> Foreign Key constraints: $1 FOREIGN KEY (mailbox_idnr) REFERENCES mailboxes(mailbox_idnr) ON UPDATE NO ACTION ON DELETE NO ACTION
To drop that constraint:
dbmail=# ALTER TABLE messages DROP CONSTRAINT "$1";
if you want to be able to turn it on or off easily, here's a plpgsql function
to do it:
CREATE OR REPLACE FUNCTION toggle_messages_constraint(INTEGER)
RETURNS VARCHAR AS '
DECLARE cmd VARCHAR;
BEGIN
IF $1 = 0
THEN
RAISE NOTICE ''Dropping constraint on messages'';
EXECUTE ''ALTER TABLE messages DROP CONSTRAINT "$1"'';
RETURN ''OFF'';
ELSE
RAISE NOTICE ''Adding constraint to messages'';
ALTER TABLE messages ADD FOREIGN KEY(mailbox_idnr) REFERENCES
mailboxes(mailbox_idnr);
RETURN ''ON'';
END IF;
RETURN ''NO CHANGE'';
END;
'
LANGUAGE plpgsql;
You may first have to do the following to enable plpgsql:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/lib/postgresql/plpgsql.so' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
Obviously, replace the path to your plpgsql.so file.
Now you can drop/add the constraint easily with:
dbmail=# select toggle_messages_constraint(0);
NOTICE: Dropping constraint on messages
toggle_messages_constraint
----------------------------
OFF
(1 row)
dbmail=# select toggle_messages_constraint(1);
NOTICE: Adding constraint to messages
toggle_messages_constraint
----------------------------
ON
(1 row)
That good enough for you?
-Feargal.
On Tue, 03 Feb 2004 17:33:27 +0100
Ilja Booij <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I was trying the PostgreSQL script, but it failed on some foreign key
> checks. I'd like to switch off foreign keys temporarily to enable the
> tables to be filled.
>
> Does anybody know the postgres commands for this? (if there are any)
>
> Ilja
>
> Paul J Stevens wrote:
>
> > I've just finished uploading the updated experimental packages for
> > dbmail-2.0
> >
> > They are CVS snapshots, not rc1
> >
> > They contain migration scripts for mysql and postgresql. Pure SQL. More
> > tests are required for these. I'll add them here as well.
> >
> > There are still some rough edges to these packages: lmtpd documentation
> > is missing, installation or migration of the database is not even
> > attempted, ldap functionality has to be tested and documented.
> >
> > Just add:
> >
> > deb http://debian.nfgd.net/debian experimental/
> >
> > to your sources list.
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> > # SQL for upgrading from dbmail-1.2 to dbmail-2.0
> >
> > SET FOREIGN_KEY_CHECKS=0;
> > SET SQL_LOG_OFF=1;
> > SET SQL_LOG_UPDATE=0;
> >
> > # required tables:
> > RENAME table aliases to aliases_1, users to users_1, mailboxes to
> > mailboxes_1, messages to messages_1, messageblks to messageblks_1;
> >
> > # optional tables:
> > RENAME table auto_notifications to auto_notifications_1;
> > RENAME table auto_replies to auto_replies_1;
> >
> >
> > DROP TABLE IF EXISTS aliases;
> > CREATE TABLE aliases (
> > alias_idnr bigint(21) NOT NULL auto_increment,
> > alias varchar(100) NOT NULL default '',
> > deliver_to varchar(250) NOT NULL default '',
> > client_idnr bigint(21) NOT NULL default '0',
> > PRIMARY KEY (alias_idnr),
> > INDEX (alias),
> > INDEX (client_idnr)
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS users;
> > CREATE TABLE users (
> > user_idnr bigint(21) NOT NULL auto_increment,
> > userid varchar(100) NOT NULL default '',
> > passwd varchar(34) NOT NULL default '',
> > client_idnr bigint(21) NOT NULL default '0',
> > maxmail_size bigint(21) NOT NULL default '0',
> > curmail_size bigint(21) NOT NULL default '0',
> > encryption_type varchar(20) NOT NULL default '',
> > last_login datetime NOT NULL default '1979-11-03 22:05:58',
> > PRIMARY KEY (user_idnr),
> > UNIQUE INDEX (userid)
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS mailboxes;
> > CREATE TABLE mailboxes (
> > mailbox_idnr bigint(21) NOT NULL auto_increment,
> > owner_idnr bigint(21) NOT NULL default '0',
> > name varchar(100) NOT NULL default '',
> > seen_flag tinyint(1) NOT NULL default '0',
> > answered_flag tinyint(1) NOT NULL default '0',
> > deleted_flag tinyint(1) NOT NULL default '0',
> > flagged_flag tinyint(1) NOT NULL default '0',
> > recent_flag tinyint(1) NOT NULL default '0',
> > draft_flag tinyint(1) NOT NULL default '0',
> > no_inferiors tinyint(1) NOT NULL default '0',
> > no_select tinyint(1) NOT NULL default '0',
> > permission tinyint(1) default '2',
> > PRIMARY KEY (mailbox_idnr),
> > INDEX (name),
> > INDEX (owner_idnr),
> > FOREIGN KEY (`owner_idnr`)
> > REFERENCES `users` (`user_idnr`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS subscription;
> > CREATE TABLE subscription (
> > user_id bigint(21) not null default '0',
> > mailbox_id bigint(21) not null,
> > primary key (user_id, mailbox_id),
> > index (user_id),
> > index (mailbox_id),
> > FOREIGN KEY (`user_id`)
> > REFERENCES `users` (`user_idnr`) ON DELETE CASCADE,
> > FOREIGN KEY (`mailbox_id`)
> > REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE,
> >
> > ) TYPE=InnoDB;
> >
> >
> > DROP TABLE IF EXISTS acl;
> > CREATE TABLE acl (
> > user_id bigint(21) NOT NULL,
> > mailbox_id bigint(21) NOT NULL,
> > lookup_flag tinyint(1) default '0' not null,
> > read_flag tinyint(1) default '0' not null,
> > seen_flag tinyint(1) default '0' not null,
> > write_flag tinyint(1) default '0' not null,
> > insert_flag tinyint(1) default '0' not null,
> > post_flag tinyint(1) default '0' not null,
> > create_flag tinyint(1) default '0' not null,
> > delete_flag tinyint(1) default '0' not null,
> > administer_flag tinyint(1) default '0' not null,
> > PRIMARY KEY(user_id, mailbox_id),
> > index (user_id),
> > index (mailbox_id),
> > FOREIGN KEY (`user_id`)
> > REFERENCES `users` (`user_idnr`) ON DELETE CASCADE,
> > FOREIGN KEY (`mailbox_id`)
> > REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> >
> > DROP TABLE IF EXISTS physmessage;
> > CREATE TABLE physmessage (
> > id bigint(21) NOT NULL auto_increment,
> > messagesize bigint(21) NOT NULL default '0',
> > rfcsize bigint(21) NOT NULL default '0',
> > internal_date datetime NOT NULL default '0000-00-00 00:00:00',
> > PRIMARY KEY (id)
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS messages;
> > CREATE TABLE messages (
> > message_idnr bigint(21) NOT NULL auto_increment,
> > mailbox_idnr bigint(21) NOT NULL default '0',
> > physmessage_id bigint(21) NOT NULL default '0',
> > seen_flag tinyint(1) NOT NULL default '0',
> > answered_flag tinyint(1) NOT NULL default '0',
> > deleted_flag tinyint(1) NOT NULL default '0',
> > flagged_flag tinyint(1) NOT NULL default '0',
> > recent_flag tinyint(1) NOT NULL default '0',
> > draft_flag tinyint(1) NOT NULL default '0',
> > unique_id varchar(70) NOT NULL default '',
> > status tinyint(3) unsigned zerofill NOT NULL default '000',
> > PRIMARY KEY (message_idnr),
> > INDEX physmessage_id (physmessage_id),
> > INDEX mailbox_idnr (mailbox_idnr),
> > INDEX seen_flag (seen_flag),
> > INDEX unique_id (unique_id),
> > INDEX status (status),
> > FOREIGN KEY (`physmessage_id`)
> > REFERENCES `physmessage` (`id`) ON DELETE CASCADE,
> > FOREIGN KEY (`mailbox_idnr`)
> > REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS messageblks;
> > CREATE TABLE messageblks (
> > messageblk_idnr bigint(21) NOT NULL auto_increment,
> > physmessage_id bigint(21) NOT NULL default '0',
> > messageblk longtext NOT NULL,
> > blocksize bigint(21) NOT NULL default '0',
> > PRIMARY KEY (messageblk_idnr),
> > INDEX physmsg_index (physmessage_id),
> > FOREIGN KEY (`physmessage_id`)
> > REFERENCES `physmessage` (`id`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS auto_notifications;
> > CREATE TABLE auto_notifications (
> > auto_notify_idnr bigint(21) NOT NULL AUTO_INCREMENT,
> > user_idnr bigint(21) NOT NULL,
> > notify_address VARCHAR(100) NOT NULL,
> > PRIMARY KEY (auto_notify_idnr),
> > INDEX (user_idnr),
> > FOREIGN KEY (`user_idnr`)
> > REFERENCES `users`(`user_idnr`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> > DROP TABLE IF EXISTS auto_replies;
> > CREATE TABLE auto_replies (
> > auto_reply_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
> > user_idnr bigint(21) DEFAULT '0' NOT NULL,
> > reply_body mediumtext,
> > PRIMARY KEY (auto_reply_idnr),
> > INDEX (user_idnr),
> > FOREIGN KEY (`user_idnr`)
> > REFERENCES `users`(`user_idnr`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> > SET FOREIGN_KEY_CHECKS=0;
> > INSERT INTO aliases SELECT * from aliases_1;
> >
> > INSERT INTO subscription ( user_id, mailbox_id ) SELECT owner_idnr,
> > mailbox_idnr FROM mailboxes_1 where is_subscribed > 0;
> >
> > INSERT INTO mailboxes ( mailbox_idnr, owner_idnr, name, seen_flag,
> > answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag,
> > no_inferiors, no_select, permission )
> > SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag,
> > deleted_flag, flagged_flag, recent_flag, draft_flag, no_inferiors,
> > no_select, permission FROM mailboxes_1;
> >
> > INSERT INTO messages ( message_idnr, mailbox_idnr, seen_flag,
> > answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag,
> > unique_id )
> > SELECT message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag,
> > flagged_flag, recent_flag, draft_flag, unique_id FROM messages_1;
> >
> > INSERT INTO physmessage ( id, messagesize, rfcsize, internal_date)
> > SELECT message_idnr, messagesize, rfcsize, internal_date FROM messages_1;
> >
> > UPDATE messages SET physmessage_id = message_idnr;
> >
> > INSERT INTO messageblks ( messageblk_idnr, physmessage_id, messageblk,
> > blocksize )
> > SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM
> > messageblks_1;
> >
> >
> > INSERT INTO users ( user_idnr, userid, passwd, client_idnr, maxmail_size,
> > encryption_type, last_login, curmail_size )
> > SELECT u.*, sum(p.messagesize) AS curmail_size
> > FROM users_1 u LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr LEFT
> > JOIN messages m ON m.mailbox_idnr = b.mailbox_idnr LEFT JOIN physmessage p
> > ON m.physmessage_id = p.id
> > GROUP BY u.user_idnr;
> >
> > INSERT INTO auto_replies SELECT * FROM auto_replies_1;
> > INSERT INTO auto_notifications SELECT * FROM auto_notifications_1;
> >
> > DROP TABLE auto_replies_1, auto_notifications_1, aliases_1, users_1,
> > mailboxes_1, messages_1, messageblks_1;
> >
> > SET FOREIGN_KEY_CHECKS=1;
> >
> >
> > ------------------------------------------------------------------------
> >
> > -- SQL for upgrading from dbmail-1.2 to dbmail-2.0
> >
> > -- required tables:
> > CREATE TABLE aliases_1 AS SELECT * FROM aliases;
> > CREATE TABLE users_1 AS SELECT * FROM users;
> > CREATE TABLE mailboxes_1 AS SELECT * FROM mailboxes;
> > CREATE TABLE messages_1 AS SELECT * FROM messages;
> > CREATE TABLE messageblks_1 AS SELECT * FROM messageblks;
> >
> > DROP TABLE aliases;
> > DROP TABLE messageblks;
> > DROP TABLE messages;
> > DROP TABLE mailboxes;
> > DROP TABLE users;
> > -- create dbmail-2 tables
> >
> > CREATE TABLE aliases (
> > alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
> > alias VARCHAR(100) NOT NULL,
> > deliver_to VARCHAR(250) NOT NULL,
> > client_idnr INT8 DEFAULT '0' NOT NULL,
> > PRIMARY KEY (alias_idnr)
> > );
> >
> > CREATE UNIQUE INDEX aliases_alias_idx ON aliases(alias);
> > CREATE UNIQUE INDEX aliases_alias_low_idx ON aliases(lower(alias));
> >
> > CREATE TABLE users (
> > user_idnr INT8 DEFAULT nextval('user_idnr_seq'),
> > userid VARCHAR(100) NOT NULL,
> > passwd VARCHAR(34) NOT NULL,
> > client_idnr INT8 DEFAULT '0' NOT NULL,
> > maxmail_size INT8 DEFAULT '0' NOT NULL,
> > curmail_size INT8 DEFAULT '0' NOT NULL,
> > encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
> > last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
> > PRIMARY KEY (user_idnr)
> > );
> > CREATE INDEX users_name_idx ON users(userid);
> >
> > CREATE TABLE mailboxes (
> > mailbox_idnr INT8 DEFAULT nextval('mailbox_idnr_seq'),
> > owner_idnr INT8 NOT NULL,
> > name VARCHAR(100) NOT NULL,
> > seen_flag INT2 DEFAULT '0' NOT NULL,
> > answered_flag INT2 DEFAULT '0' NOT NULL,
> > deleted_flag INT2 DEFAULT '0' NOT NULL,
> > flagged_flag INT2 DEFAULT '0' NOT NULL,
> > recent_flag INT2 DEFAULT '0' NOT NULL,
> > draft_flag INT2 DEFAULT '0' NOT NULL,
> > no_inferiors INT2 DEFAULT '0' NOT NULL,
> > no_select INT2 DEFAULT '0' NOT NULL,
> > permission INT2 DEFAULT '2',
> > PRIMARY KEY (mailbox_idnr),
> > FOREIGN KEY (owner_idnr) REFERENCES users(user_idnr) ON DELETE CASCADE
> > );
> > CREATE INDEX mailboxes_owner_idx ON mailboxes(owner_idnr);
> > CREATE INDEX mailboxes_name_idx ON mailboxes(name);
> >
> > CREATE TABLE subscription (
> > user_id INT8 NOT NULL,
> > mailbox_id INT8 NOT NULL,
> > PRIMARY KEY (user_id, mailbox_id),
> > FOREIGN KEY (user_id) REFERENCES users(user_idnr) ON DELETE CASCADE,
> > FOREIGN KEY (mailbox_id)
> > REFERENCES mailboxes(mailbox_idnr) ON DELETE CASCADE
> > );
> >
> > CREATE TABLE acl (
> > user_id INT8 NOT NULL,
> > mailbox_id INT8 NOT NULL,
> > lookup_flag INT2 DEFAULT '0' NOT NULL,
> > read_flag INT2 DEFAULT '0' NOT NULL,
> > seen_flag INT2 DEFAULT '0' NOT NULL,
> > write_flag INT2 DEFAULT '0' NOT NULL,
> > insert_flag INT2 DEFAULT '0' NOT NULL,
> > post_flag INT2 DEFAULT '0' NOT NULL,
> > create_flag INT2 DEFAULT '0' NOT NULL,
> > delete_flag INT2 DEFAULT '0' NOT NULL,
> > administer_flag INT2 DEFAULT '0' NOT NULL,
> > PRIMARY KEY (user_id, mailbox_id),
> > FOREIGN KEY (user_id) REFERENCES users(user_idnr) ON DELETE CASCADE,
> > FOREIGN KEY (mailbox_id) REFERENCES mailboxes(mailbox_idnr) ON DELETE
> > CASCADE
> > );
> >
> > CREATE SEQUENCE physmessage_id_seq;
> > CREATE TABLE physmessage (
> > id INT8 DEFAULT nextval('physmessage_id_seq'),
> > messagesize INT8 DEFAULT '0' NOT NULL,
> > rfcsize INT8 DEFAULT '0' NOT NULL,
> > internal_date TIMESTAMP,
> > PRIMARY KEY(id)
> > );
> >
> > CREATE TABLE messages (
> > message_idnr INT8 DEFAULT nextval('message_idnr_seq'),
> > mailbox_idnr INT8 DEFAULT '0' NOT NULL,
> > physmessage_id INT8 DEFAULT '0' NOT NULL,
> > seen_flag INT2 DEFAULT '0' NOT NULL,
> > answered_flag INT2 DEFAULT '0' NOT NULL,
> > deleted_flag INT2 DEFAULT '0' NOT NULL,
> > flagged_flag INT2 DEFAULT '0' NOT NULL,
> > recent_flag INT2 DEFAULT '0' NOT NULL,
> > draft_flag INT2 DEFAULT '0' NOT NULL,
> > unique_id varchar(70) NOT NULL,
> > status INT2 DEFAULT '000' NOT NULL,
> > PRIMARY KEY (message_idnr),
> > FOREIGN KEY (physmessage_id) REFERENCES physmessage(id) ON DELETE
> > CASCADE,
> > FOREIGN KEY (mailbox_idnr) REFERENCES mailboxes(mailbox_idnr) ON DELETE
> > CASCADE
> > );
> > CREATE INDEX messages_mailbox_idx ON messages(mailbox_idnr);
> > CREATE INDEX messages_physmessage_idx ON messages(physmessage_id);
> > CREATE INDEX messages_seen_flag_idx ON messages(seen_flag);
> > CREATE INDEX messages_unique_id_idx ON messages(unique_id);
> > CREATE INDEX messages_status_idx ON messages(status);
> >
> > CREATE TABLE messageblks (
> > messageblk_idnr INT8 DEFAULT nextval('messageblk_idnr_seq'),
> > physmessage_id INT8 DEFAULT '0' NOT NULL,
> > messageblk TEXT NOT NULL,
> > blocksize INT8 DEFAULT '0' NOT NULL,
> > PRIMARY KEY (messageblk_idnr),
> > FOREIGN KEY (physmessage_id) REFERENCES physmessage (id) ON DELETE
> > CASCADE
> > );
> > CREATE INDEX messageblks_physmessage_idx ON messageblks(physmessage_id);
> >
> >
> > -- fillerup
> >
> > INSERT INTO aliases SELECT * from aliases_1;
> >
> > INSERT INTO subscription ( user_id, mailbox_id ) SELECT owner_idnr,
> > mailbox_idnr FROM mailboxes_1 where is_subscribed > 0;
> >
> > INSERT INTO mailboxes ( mailbox_idnr, owner_idnr, name, seen_flag,
> > answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag,
> > no_inferiors, no_select, permission )
> > SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag,
> > deleted_flag, flagged_flag, recent_flag, draft_flag, no_inferiors,
> > no_select, permission FROM mailboxes_1;
> >
> > INSERT INTO messages ( message_idnr, mailbox_idnr, seen_flag,
> > answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag,
> > unique_id )
> > SELECT message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag,
> > flagged_flag, recent_flag, draft_flag, unique_id FROM messages_1;
> >
> > INSERT INTO physmessage ( id, messagesize, rfcsize, internal_date)
> > SELECT message_idnr, messagesize, rfcsize, internal_date FROM messages_1;
> >
> > UPDATE messages SET physmessage_id = message_idnr;
> >
> > INSERT INTO messageblks ( messageblk_idnr, physmessage_id, messageblk,
> > blocksize )
> > SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM
> > messageblks_1;
> >
> >
> > INSERT INTO users ( user_idnr, userid, passwd, client_idnr, maxmail_size,
> > encryption_type, last_login, curmail_size )
> > SELECT u.*, sum(p.messagesize) AS curmail_size
> > FROM users_1 u
> > LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr
> > LEFT JOIN messages m ON m.mailbox_idnr = b.mailbox_idnr
> > LEFT JOIN physmessage p ON m.physmessage_id = p.id
> > GROUP BY u.user_idnr, u.userid, u.passwd, u.client_idnr, u.maxmail_size,
> > u.encryption_type,u.last_login;
> >
> > DROP TABLE aliases_1, users_1, mailboxes_1, messages_1, messageblks_1;
> >
>
> --
> IC&S
> Stadhouderslaan 57
> 3583 JD Utrecht
>
> PGP-key:
> http://www.ic-s.nl/keys/ilja.txt
>
> _______________________________________________
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
pgpgkUcQth522.pgp
Description: PGP signature
