migration_common.anubis
5.28 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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
/*
* Created by PyramIDE.
* User: ricard
* Date: 03/12/2008
* Time: 13:07
*
*/
read tools/basis.anubis
read system/logger.anubis
read data_base/sqlite.anubis
read data_base/alter_table.anubis
read data_base/sqlite_foreign_key.anubis
read calexium_lib/database/db_utils.anubis
read calexium_lib/net_services_protocols/logger_service.anubis
read ezmailbox_constants.anubis
public type DbTable:
table(String name, String create_query),
table(String db_name, String name, String create_query).
public define Bool
is_table_exists
(
SQLite3DataBase db,
String dbName,
String table_name
)=
if sql_query_timeout(db, "SELECT * FROM " + dbName + ".sqlite_master WHERE type ='table' AND name = @table_name", [bind_String("@table_name", table_name)], "is_table_exists("+table_name+")") is
{
error(_) then false, //error in the SQL request
ok(_, cursor, _) then
if cursor(unique) is
{
error(_) then false,
no_more_row then false,
row(current) then true
}
}.
public define Bool
is_table_exists
(
SQLite3DataBase db,
String table_name
)=
is_table_exists(db, "main", table_name).
public define Bool
is_empty_database
(
SQLite3DataBase db,
String dbName,
)=
if sql_query_timeout(db, "SELECT * FROM " + dbName + ".sqlite_master", [], "is_empty_database("+dbName+")") is
{
error(_) then true, //error in the SQL request
ok(_, cursor, _) then
if cursor(unique) is
{
error(_) then true,
no_more_row then true,
row(current) then false
}
}.
public define Bool
is_empty_database
(
SQLite3DataBase db,
)=
is_empty_database(db, "main").
public define Maybe(One)
drop_all_triggers
(
SQLite3DataBase db,
Logger log
) =
if sqlite3_query(db, "SELECT name FROM sqlite_master WHERE type = 'trigger'", []) is
{
error(err) then logError(log, db_error(err, "drop_all_triggers")); failure,
ok(_, cursor, _) then
with drop_trigger = (String name) |-> if sqlite3_query(db, "DROP TRIGGER " + name, []) is
{
error(err2) then logError(log, db_error(err2, "Failed to drop trigger '" + name + "'")),
ok(_, cursor, _) then unique
},
map_forget(drop_trigger, db_get_string_list(cursor, []));
success(unique)
}.
public define Maybe(One)
create_tables
(
SQLite3DataBase db,
Logger logger,
List(DbTable) tables,
String version_string
) =
if tables is
{
[] then success(unique),
[h . t] then
if h is
{
table(table_name, create_string) then
if alter_table(db, table_name, create_string, logger) is
{
failure then logError(logger, "Version "+version_string+": Can't create '"+table_name+"' table"); failure,
success(_) then create_tables(db, logger, t, version_string)
},
table(db_name, table_name, create_string) then
if alter_table(db, db_name, table_name, create_string, logger) is
{
failure then logError(logger, "Version "+version_string+": Can't create '"+db_name+"."+table_name+"' table"); failure,
success(_) then create_tables(db, logger, t, version_string)
}
}
}.
public define Maybe(One)
create_indexes
(
SQLite3DataBase db,
Logger logger,
List(String) indexes,
String version_string
)=
if indexes is
{
[] then success(unique),
[h . t] then
if sql_query_timeout(db, h, "Version "+version_string+": create indexes") is
{
failure then failure,
success(_) then
create_indexes(db, logger, t, version_string)
}
}.
public define One
make_foreign_key
(
SQLite3DataBase db,
String table_name,
String field_name,
String foreign_table_name,
String foreign_field_name,
Bool fk_null,
Bool fk_cascade,
Logger log
) =
if (Maybe(One))make_foreign_key(db, table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade, log) is
{
failure then
logError(log, "Can't create foreign key constraint between '"+table_name+"' and '"+foreign_table_name+"' tables"),
success(_) then
unique
}.
public define Maybe(One)
make_foreign_keys
(
SQLite3DataBase db,
Logger logger,
List((String, String, String, String, FK_Null, FK_Cascade)) relations,
String version_string
)=
if relations is
{
[] then success(unique),
[h . t] then
if h is (table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade) then
if (Maybe(One))make_foreign_key(db, table_name, field_name, foreign_table_name, foreign_field_name, fk_null, fk_cascade, logger) is
{
failure then
logError(logger, "Version "+version_string+": Can't create foreign key constraint between '"+table_name+"' and '"+foreign_table_name+"' tables"); failure,
success(_) then
make_foreign_keys(db, logger, t, version_string)
}
}.