Hello !
I'm trying to migrate a database from PostgreSQL to SQLite but could not find
a way to do it and stay DRY, is it possible to do it ?
See example bellow.
Cheers !
======
Using literals for one_type is not an acceptable option !
one_type varchar NOT NULL DEFAULT 'tuple'
CHECK( (one_type IN('simple', 'tuple')) (NOT (one_type = 'simple' AND active
= FALSE)) )
====== PostgreSQL
CREATE TYPE "mytype" AS ENUM ('simple', 'tuple');
CREATE TABLE "use_mytype"(
?? ?id SERIAL PRIMARY KEY,
?? ?one_type mytype NOT NULL DEFAULT 'tuple',
?? ?active BOOLEAN,
?? ?CONSTRAINT "one_type_and_active" CHECK(NOT (one_type = 'simple' AND
active = FALSE))
);
====== SQLite
CREATE TABLE "mytype"(id INTEGER PRIMARY KEY, name VARCHAR);
insert into mytype(id, name) values (1,'simple'), (2,'tuple');
CREATE TABLE "use_mytype"(
?? ?id INTEGER PRIMARY KEY,
?? ?one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use
a default here ?
?? ?active BOOLEAN
);
CREATE TRIGGER "use_mytype_insert_trigger"
BEFORE INSERT ON "use_mytype"
BEGIN
?? ?SELECT RAISE(ROLLBACK, 'Not permited !')
?? ??? ?WHERE (NEW.one_type = 'simple' AND NEW.active = 0);
END;
-- NOT DRY (Repetition is the mother of bugs)
CREATE TRIGGER "use_mytype_update_trigger"
BEFORE UPDATE OF one_type ON "use_mytype"
BEGIN
?? ?SELECT RAISE(ROLLBACK, 'Not permited !')
?? ??? ?WHERE (NEW.one_type = 'simple' AND NEW.active = 0);
END;
======
