SQLite script does not work for V4

[Gergely Papp]

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;

[Boris Gulay]

Hello. Thanx for contributing. What about indexes in 2018121200, should they be created on new ident_switch table?

[Boris Gulay]

Fix sqlite scripts. Closes #35.

→ <<cset 89791e1f4988>>