1CREATE TABLE IF NOT EXISTS Tiles (id INTEGER NOT NULL PRIMARY KEY, X INTEGER NOT NULL, Y INTEGER NOT NULL, Zoom INTEGER NOT NULL, Type UNSIGNED INTEGER NOT NULL, CacheTime DATETIME); 2CREATE INDEX IF NOT EXISTS IndexOfTiles ON Tiles (X, Y, Zoom, Type); 3 4CREATE TABLE IF NOT EXISTS TilesData (id INTEGER NOT NULL PRIMARY KEY CONSTRAINT fk_Tiles_id REFERENCES Tiles(id) ON DELETE CASCADE, Tile BLOB NULL); 5 6-- Foreign Key Preventing insert 7CREATE TRIGGER fki_TilesData_id_Tiles_id 8BEFORE INSERT ON [TilesData] 9FOR EACH ROW BEGIN 10 SELECT RAISE(ROLLBACK, 'insert on table "TilesData" violates foreign key constraint "fki_TilesData_id_Tiles_id"') 11 WHERE (SELECT id FROM Tiles WHERE id = NEW.id) IS NULL; 12END; 13 14-- Foreign key preventing update 15CREATE TRIGGER fku_TilesData_id_Tiles_id 16BEFORE UPDATE ON [TilesData] 17FOR EACH ROW BEGIN 18 SELECT RAISE(ROLLBACK, 'update on table "TilesData" violates foreign key constraint "fku_TilesData_id_Tiles_id"') 19 WHERE (SELECT id FROM Tiles WHERE id = NEW.id) IS NULL; 20END; 21 22-- Cascading Delete 23CREATE TRIGGER fkdc_TilesData_id_Tiles_id 24BEFORE DELETE ON Tiles 25FOR EACH ROW BEGIN 26 DELETE FROM TilesData WHERE TilesData.id = OLD.id; 27END;