migration_common_sqlite3.anubis
6.13 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
189
190
191
192
193
194
195
/*
* 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 //for sqlite3 version of alter_table
read data_base/db_types.anubis //FK...
read data_base/sqlite_foreign_key.anubis
read db_get_helpers.anubis
read xlib/database/db_utils.anubis
//read xlib/net_services_protocols/logger_service.anubis
transmit db_version.anubis
transmit settings_sqlite3.anubis
public type DbTable:
table(String name, String create_query),
table(String db_name, String name, String create_query),
view(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 tbl_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,
(LogLevel, String) -> One log
) =
if sqlite3_query(db, "SELECT name FROM sqlite_master WHERE type = 'trigger'", []) is
{
error(err) then log(logError, 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 log(logError, db_error(err2, "Failed to drop trigger '" + name + "'")),
ok(_, cursor, _) then unique
},
map_forget(drop_trigger, db_get_List_String(cursor, []));
success(unique)
}.
public define Maybe(One)
create_tables
(
SQLite3DataBase db,
(LogLevel, String) -> One 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 logger(logError, "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 logger(logError, "Version "+version_string+": Can't create '"+db_name+"."+table_name+"' table"); failure,
success(_) then create_tables(db, logger, t, version_string)
},
view(view_name, create_string) then
if alter_view(db, view_name, create_string, logger) is
{
failure then logger(logError, "Version "+version_string+": Can't create '"+view_name+"' view"); failure,
success(_) then create_tables(db, logger, t, version_string)
},
}
}.
public define Maybe(One)
create_indexes
(
SQLite3DataBase db,
(LogLevel, String) -> One 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
{
error(_) then failure,
ok(_,_,_) 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,
(LogLevel, String) -> One 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
log(logError, "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,
(LogLevel, String) -> One 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
logger(logError, "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)
}
}.