sqlite_foreign_key.anubis
5.45 KB
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
*Project* Anubis
*Title* SQLite Foreign Key functions
*Copyright* Copyright (c) Cédric Ricard 2007.
*Author* Cédric Ricard
*Created* 2007 12 21
*Satus* Released
*Compatibility* 1.8.4
read tools/basis.anubis
read data_base/sqlite.anubis
read data_base/db_tools.anubis
read system/logger.anubis
define Maybe(One)
my_sql_query
(
Database db,
String sql,
Logger log
) =
if sql_query(db, sql) is
{
error(sql_error) then logError(log, "SQL ERROR: " + sql_error.text + "\n ...executing query [" + sql + "]"); failure,
ok(_,_,_) then success(unique)
}.
public define Maybe(One)
alter_trigger
(
Database db,
String trigger_name,
String sql,
Logger log
) =
if sql_query(db, "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = " + db_make_sql_string(trigger_name)) is
{
error(sql_error) then logError(log, "SQL ERROR: " + sql_error.text + "\n ...looking for trigger named [" + trigger_name + "]"); failure,
ok(_,cursor,_) then
if cursor(unique) is
{
error(sql_error) then logError(log, "alter_trigger ERROR '" + sql_error.text + "'"); failure,
no_more_row then my_sql_query(db, sql, log),
row(current) then
with old_sql = text(current)(0),
if old_sql /= sql then
logDebug(log, "Old trigger was:\n" + old_sql + "\n");
logDebug(log, "New trigger is:\n" + sql + "\n");
logInfo(log, "Updating trigger '" + trigger_name + "'...");
forget(my_sql_query(db, "DROP TRIGGER [" + trigger_name + "]", log));
if my_sql_query(db, sql, log) is success(_) then logInfo (log, " --> ok."); success(unique)
else logError(log, " --> error!"); failure
else
success(unique)
}
}.
public type FK_Null:
null,
not_null.
public type FK_Cascade:
delete_in_cascade,
block_if_children.
public define Maybe(One)
make_foreign_key
(
Database db,
String table_name,
String field_name,
String foreign_table_name,
String foreign_field_name,
FK_Null fk_null,
FK_Cascade fk_cascade,
Logger log
) =
with constrait_name = table_name + "__" + field_name + "__" + foreign_table_name,
sql_create =
"CREATE TRIGGER [fki__" + constrait_name + "] \n"
+ "BEFORE INSERT ON [" + table_name + "] \n"
+ "FOR EACH ROW BEGIN \n"
+ " SELECT RAISE(ROLLBACK, 'insert on table \"" + table_name + "\" violates foreign key constraint \"fki__" + constrait_name + "\"') \n"
+ " WHERE " + (if fk_null is null then " NEW.[" + field_name + "] IS NOT NULL AND \n" else "\n")
+ " (SELECT [" + foreign_field_name + "] FROM [" + foreign_table_name + "] WHERE [" + foreign_field_name + "] = NEW.[" + field_name + "]) IS NULL; \n"
+ "END",
sql_update =
"CREATE TRIGGER [fku__" + constrait_name + "] \n"
+ "BEFORE UPDATE ON [" + table_name + "] \n"
+ "FOR EACH ROW BEGIN \n"
+ " SELECT RAISE(ROLLBACK, 'update on table \"" + table_name + "\" violates foreign key constraint \"fku__" + constrait_name + "\"') \n"
+ " WHERE " + (if fk_null is null then " NEW.[" + field_name + "] IS NOT NULL AND \n" else "\n")
+ " (SELECT [" + foreign_field_name + "] FROM [" + foreign_table_name + "] WHERE [" + foreign_field_name + "] = NEW.[" + field_name + "]) IS NULL; \n"
+ "END",
sql_delete =
"CREATE TRIGGER [fkd__" + constrait_name + "] \n"
+ "BEFORE DELETE ON [" + foreign_table_name + "] \n"
+ "FOR EACH ROW BEGIN \n"
+ if fk_cascade is {
delete_in_cascade then
" DELETE from [" + table_name + "] WHERE [" + field_name + "] = OLD.[" + foreign_field_name + "];\n",
block_if_children then
" SELECT RAISE(ROLLBACK, 'delete on table \"" + foreign_table_name + "\" violates foreign key constraint \"fku__" + constrait_name + "\"') \n"
+ " WHERE (SELECT [" + field_name + "] FROM [" + table_name + "] WHERE [" + field_name + "] = OLD.[" + foreign_field_name + "]) IS NOT NULL; \n"
}
+ "END",
if alter_trigger(db, "fki__" + constrait_name, sql_create, log) is failure
then failure
else if alter_trigger(db, "fku__" + constrait_name, sql_update, log) is failure
then failure
else if alter_trigger(db, "fkd__" + constrait_name, sql_delete, log) is failure
then failure
else
success(unique).
public define Maybe(One)
make_foreign_key
(
Database db,
String table_name,
String field_name,
String foreign_table_name,
String foreign_field_name,
Bool field_can_be_null,
Bool delete, //delete_in_cascade ?
Logger log
) =
make_foreign_key
(
db,
table_name,
field_name,
foreign_table_name,
foreign_field_name,
if field_can_be_null then null else not_null,
if delete then delete_in_cascade else block_if_children,
log
).