SQLite script does not work for V4
SQLite has no working ALTER COLUMN operation. Tables must be dropped and recreated instead. Also there is an issue with the script I originally provided, so the id column is not auto assigned properly. Please update the initialization and update script as below.
plugins/ident_switch/SQL/sqlite.initial.sql
CREATE TABLE ident_switch ( id integer PRIMARY KEY, user_id integer NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, iid integer NOT NULL REFERENCES identities(identity_id) ON DELETE CASCADE ON UPDATE CASCADE UNIQUE, username varchar(64), password varchar(64), imap_host varchar(64), imap_port integer CHECK(imap_port > 0 AND imap_port <= 65535), label varchar(32), flags integer NOT NULL DEFAULT(0), smtp_host varchar(64), smtp_port integer CHECK(smtp_port > 0 AND smtp_port <= 65535), UNIQUE (user_id, label) ); CREATE INDEX IX_ident_switch_user_id ON ident_switch(user_id); CREATE INDEX IX_ident_switch_iid on ident_switch(iid);
plugins/ident_switch/SQL/sqlite/2018121800.sql
PRAGMA foreign_keys=off; BEGIN TRANSACTION;
ALTER TABLE ident_switch RENAME TO ident_switch_old;
CREATE TABLE ident_switch ( id integer PRIMARY KEY, user_id integer NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, iid integer NOT NULL REFERENCES identities(identity_id) ON DELETE CASCADE ON UPDATE CASCADE UNIQUE, username varchar(64), password varchar(64), imap_host varchar(64), imap_port integer CHECK(imap_port > 0 AND imap_port <= 65535), label varchar(32), flags integer NOT NULL DEFAULT(0), smtp_host varchar(64), smtp_port integer CHECK(smtp_port > 0 AND smtp_port <= 65535), UNIQUE (user_id, label) );
INSERT OR ROLLBACK INTO ident_switch (id,user_id,iid,username,password,imap_host,imap_port,label,flags,smtp_host,smtp_port) SELECT id,user_id,iid,username,password,host,port,label,flags,host,port FROM ident_switch_old; DROP TABLE ident_switch_old; COMMIT; PRAGMA foreign_keys=on;
Comments (3)
-
repo owner -
repo owner - changed status to open
-
repo owner - changed status to closed
Fix sqlite scripts. Closes
#35.→ <<cset 89791e1f4988>>
- Log in to comment
Hello. Thanx for contributing. What about indexes in 2018121200, should they be created on new ident_switch table?