1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
CREATE TABLE optics_type (optics_type_id INTEGER PRIMARY KEY, name VARCHAR NOT NULL);
INSERT INTO optics_type (name) VALUES('Astrograph');
INSERT INTO optics_type (name) VALUES('Binoculars');
INSERT INTO optics_type (name) VALUES('Cassegrain');
INSERT INTO optics_type (name) VALUES('Dobsonian');
INSERT INTO optics_type (name) VALUES('Maksutov');
INSERT INTO optics_type (name) VALUES('Newton');
INSERT INTO optics_type (name) VALUES('Naked Eye');
INSERT INTO optics_type (name) VALUES('Refractor');
INSERT INTO optics_type (name) VALUES('Ritchey-Chretien');
INSERT INTO optics_type (name) VALUES('Schmidt-Cassegrain');
INSERT INTO optics_type (name) VALUES('Solar');
INSERT INTO optics_type (name) VALUES('Spotting');
UPDATE optics SET type = 1 WHERE type = 'Astrograph';
UPDATE optics SET type = 2 WHERE type = 'Binoculars';
UPDATE optics SET type = 3 WHERE type = 'Cassegrain';
UPDATE optics SET type = 4 WHERE type = 'Dobsonian';
UPDATE optics SET type = 5 WHERE type = 'Maksutov';
UPDATE optics SET type = 6 WHERE type = 'Newton';
UPDATE optics SET type = 7 WHERE type = 'Naked Eye';
UPDATE optics SET type = 8 WHERE type = 'Refractor';
UPDATE optics SET type = 9 WHERE type = 'Ritchey-Chretien';
UPDATE optics SET type = 10 WHERE type = 'Schmidt-Cassegrain';
UPDATE optics SET type = 11 WHERE type = 'Solar';
UPDATE optics SET type = 12 WHERE type = 'Spotting';
CREATE TABLE tmp_optics AS SELECT * FROM optics;
DROP TRIGGER genfkey8_insert_referencing;
DROP TRIGGER genfkey8_update_referencing;
DROP TRIGGER genfkey8_delete_referenced;
DROP TRIGGER genfkey8_update_referenced;
DROP TABLE optics;
CREATE TABLE optics (optic_id INTEGER PRIMARY KEY, model VARCHAR NOT NULL, vendor VARCHAR, optics_type_id INT NOT NULL REFERENCES optics_type(optics_type_id), focal_length FLOAT NOT NULL, aperture FLOAT NOT NULL, hFlip BOOLEAN, vFlip BOOLEAN, light_transmission FLOAT);
CREATE TRIGGER genfkey8_insert_referencing BEFORE INSERT ON "observations" WHEN new."optics_id" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "optics" WHERE new."optics_id" == "optic_id") BEGIN SELECT RAISE(ABORT, 'genfkey8_insert_referencing constraint failed'); END;
CREATE TRIGGER genfkey8_update_referencing BEFORE UPDATE OF optics_id ON "observations" WHEN new."optics_id" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "optics" WHERE new."optics_id" == "optic_id") BEGIN SELECT RAISE(ABORT, 'genfkey8_update_referencing constraint failed'); END;
CREATE TRIGGER genfkey8_delete_referenced BEFORE DELETE ON "optics" WHEN EXISTS (SELECT 1 FROM "observations" WHERE old."optic_id" == "optics_id") BEGIN SELECT RAISE(ABORT, 'genfkey8_delete_referenced constraint failed'); END;
CREATE TRIGGER genfkey8_update_referenced AFTER UPDATE OF optic_id ON "optics" WHEN EXISTS (SELECT 1 FROM "observations" WHERE old."optic_id" == "optics_id") BEGIN SELECT RAISE(ABORT, 'genfkey8_update_referenced constraint failed'); END;
INSERT INTO optics SELECT optic_id, model, vendor, type AS optics_type_id, focal_length, aperture, hFlip, vFlip, light_transmission FROM tmp_optics;
DROP TABLE tmp_optics;
CREATE TRIGGER genfkey12_insert_referencing BEFORE INSERT ON "optics" WHEN new."optics_type_id" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "optics_type" WHERE new."optics_type_id" == "optics_type_id") BEGIN SELECT RAISE(ABORT, 'genfkey12_insert_referencing constraint failed'); END;
CREATE TRIGGER genfkey12_update_referencing BEFORE UPDATE OF optics_type_id ON "optics" WHEN new."optics_type_id" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "optics_type" WHERE new."optics_type_id" == "optics_type_id") BEGIN SELECT RAISE(ABORT, 'genfkey12_update_referencing constraint failed'); END;
CREATE TRIGGER genfkey12_delete_referenced BEFORE DELETE ON "optics_type" WHEN EXISTS (SELECT 1 FROM "optics" WHERE old."optics_type_id" == "optics_type_id") BEGIN SELECT RAISE(ABORT, 'genfkey12_delete_referenced constraint failed'); END;
CREATE TRIGGER genfkey12_update_referenced AFTER UPDATE OF optics_type_id ON "optics_type" WHEN EXISTS (SELECT 1 FROM "optics" WHERE old."optica_type_id" == "optics_type_id") BEGIN SELECT RAISE(ABORT, 'genfkey12_update_referenced constraint failed'); END;
|
