-USING: io io.files io.files.temp io.directories io.launcher
-kernel namespaces prettyprint tools.test db.sqlite db sequences
-continuations db.types db.tuples unicode accessors arrays
-sorting layouts math.parser ;
+USING: accessors arrays db db.sqlite db.tuples db.types io.directories
+io.files.temp kernel layouts literals math.parser namespaces sequences
+sorting splitting tools.test ;
IN: db.sqlite.tests
+: normalize ( str -- str' )
+ " \n" split harvest " " join ;
+
+! delete-trigger-restrict
+${
+ {
+ "CREATE TRIGGER fkd_TREE_NODE_NODE_ID_id "
+ "BEFORE DELETE ON NODE "
+ "FOR EACH ROW BEGIN "
+ "SELECT RAISE(ROLLBACK, "
+ "'delete on table \"NODE\" violates "
+ "foreign key constraint \"fkd_TREE_NODE_NODE_ID_id\"') "
+ "WHERE (SELECT ID FROM NODE WHERE ID = OLD.ID) IS NOT NULL; END;"
+ } concat
+} [
+ {
+ { "table-name" "TREE" }
+ { "table-id" "NODE" }
+ { "foreign-table-name" "NODE"}
+ { "foreign-table-id" "ID" }
+ } [ delete-trigger-restrict ] with-variables
+ normalize
+] unit-test
+
+! insert-trigger
+${
+ {
+ "CREATE TRIGGER fki_TREE_NODE_NODE_ID_id "
+ "BEFORE INSERT ON TREE "
+ "FOR EACH ROW BEGIN "
+ "SELECT RAISE(ROLLBACK, "
+ "'insert on table \"TREE\" violates "
+ "foreign key constraint \"fki_TREE_NODE_NODE_ID_id\"') "
+ "WHERE (SELECT ID FROM NODE WHERE ID = NEW.NODE) IS NULL; END;"
+ } concat
+} [
+ {
+ { "table-name" "TREE" }
+ { "table-id" "NODE" }
+ { "foreign-table-name" "NODE"}
+ { "foreign-table-id" "ID" }
+ } [ insert-trigger ] with-variables normalize
+] unit-test
+
: db-path ( -- path ) "test-" cell number>string ".db" 3append temp-file ;
: test.db ( -- sqlite-db ) db-path <sqlite-db> ;
CREATE TRIGGER fki_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id
BEFORE INSERT ON ${table-name}
FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, 'insert on table \"${table-name}\" violates foreign key constraint \"fki_${table-name}_$table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
+ SELECT RAISE(ROLLBACK, 'insert on table \"${table-name}\" violates foreign key constraint \"fki_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
WHERE (SELECT ${foreign-table-id} FROM ${foreign-table-name} WHERE ${foreign-table-id} = NEW.${table-id}) IS NULL;
END;
" interpolate>string ;
CREATE TRIGGER fki_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id
BEFORE INSERT ON ${table-name}
FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, 'insert on table \"${table-name}\" violates foreign key constraint \"fki_${table-name}_$table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
+ SELECT RAISE(ROLLBACK, 'insert on table \"${table-name}\" violates foreign key constraint \"fki_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
WHERE NEW.${table-id} IS NOT NULL
AND (SELECT ${foreign-table-id} FROM ${foreign-table-name} WHERE ${foreign-table-id} = NEW.${table-id}) IS NULL;
END;
CREATE TRIGGER fku_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id
BEFORE UPDATE ON ${table-name}
FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, 'update on table \"${table-name}\" violates foreign key constraint \"fku_${table-name}_$table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
+ SELECT RAISE(ROLLBACK, 'update on table \"${table-name}\" violates foreign key constraint \"fku_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
WHERE (SELECT ${foreign-table-id} FROM ${foreign-table-name} WHERE ${foreign-table-id} = NEW.${table-id}) IS NULL;
END;
" interpolate>string ;
CREATE TRIGGER fku_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id
BEFORE UPDATE ON ${table-name}
FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, 'update on table \"${table-name}\" violates foreign key constraint \"fku_${table-name}_$table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
+ SELECT RAISE(ROLLBACK, 'update on table \"${table-name}\" violates foreign key constraint \"fku_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
WHERE NEW.${table-id} IS NOT NULL
AND (SELECT ${foreign-table-id} FROM ${foreign-table-name} WHERE ${foreign-table-id} = NEW.${table-id}) IS NULL;
END;
CREATE TRIGGER fkd_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id
BEFORE DELETE ON ${foreign-table-name}
FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, 'delete on table \"${foreign-table-name}\" violates foreign key constraint \"fkd_${table-name}_$table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
+ SELECT RAISE(ROLLBACK, 'delete on table \"${foreign-table-name}\" violates foreign key constraint \"fkd_${table-name}_${table-id}_${foreign-table-name}_${foreign-table-id}_id\"')
WHERE (SELECT ${foreign-table-id} FROM ${foreign-table-name} WHERE ${foreign-table-id} = OLD.${foreign-table-id}) IS NOT NULL;
END;
" interpolate>string ;