Line data Source code
1 : //
2 : // dbutils.c
3 : // cloudsync
4 : //
5 : // Created by Marco Bambini on 23/09/24.
6 : //
7 :
8 : #include <stdlib.h>
9 : #include "utils.h"
10 : #include "dbutils.h"
11 : #include "cloudsync.h"
12 :
13 : #ifndef SQLITE_CORE
14 : SQLITE_EXTENSION_INIT3
15 : #endif
16 :
17 : #if CLOUDSYNC_UNITTEST
18 : char *OUT_OF_MEMORY_BUFFER = "OUT_OF_MEMORY_BUFFER";
19 : #ifndef SQLITE_MAX_ALLOCATION_SIZE
20 : #define SQLITE_MAX_ALLOCATION_SIZE 2147483391
21 : #endif
22 : #endif
23 :
24 : typedef struct {
25 : int type;
26 : int len;
27 : int rc;
28 : union {
29 : sqlite3_int64 intValue;
30 : double doubleValue;
31 : char *stringValue;
32 : } value;
33 : } DATABASE_RESULT;
34 :
35 : typedef struct {
36 : sqlite3 *db;
37 : cloudsync_context *data;
38 : } dbutils_settings_table_context;
39 :
40 : // MARK: - General -
41 :
42 3075 : DATABASE_RESULT dbutils_exec (sqlite3_context *context, sqlite3 *db, const char *sql, const char **values, int types[], int lens[], int count, DATABASE_RESULT results[], int expected_types[], int result_count) {
43 : DEBUG_DBFUNCTION("dbutils_exec %s", sql);
44 :
45 3075 : sqlite3_stmt *pstmt = NULL;
46 3075 : bool is_write = (result_count == 0);
47 : #ifdef CLOUDSYNC_UNITTEST
48 3075 : bool is_test = (result_count == 1 && expected_types[0] == SQLITE_NOMEM);
49 : #endif
50 3075 : int type = 0;
51 :
52 : // compile sql
53 3075 : int rc = sqlite3_prepare_v2(db, sql, -1, &pstmt, NULL);
54 3075 : if (rc != SQLITE_OK) goto dbutils_exec_finalize;
55 :
56 : // check bindings
57 3955 : for (int i=0; i<count; ++i) {
58 881 : switch (types[i]) {
59 : case SQLITE_NULL:
60 1 : rc = sqlite3_bind_null(pstmt, i+1);
61 1 : break;
62 : case SQLITE_TEXT:
63 723 : rc = sqlite3_bind_text(pstmt, i+1, values[i], lens[i], SQLITE_STATIC);
64 723 : break;
65 : case SQLITE_BLOB:
66 50 : rc = sqlite3_bind_blob(pstmt, i+1, values[i], lens[i], SQLITE_STATIC);
67 50 : break;
68 : case SQLITE_INTEGER: {
69 106 : sqlite3_int64 value = strtoll(values[i], NULL, 0);
70 106 : rc = sqlite3_bind_int64(pstmt, i+1, value);
71 106 : } break;
72 : case SQLITE_FLOAT: {
73 1 : double value = strtod(values[i], NULL);
74 1 : rc = sqlite3_bind_double(pstmt, i+1, value);
75 1 : } break;
76 : }
77 881 : if (rc != SQLITE_OK) goto dbutils_exec_finalize;
78 881 : }
79 :
80 : // execute statement
81 3074 : rc = sqlite3_step(pstmt);
82 :
83 : // check return value based on pre-condition
84 3074 : if (rc != SQLITE_ROW) goto dbutils_exec_finalize;
85 2686 : if (is_write) goto dbutils_exec_finalize;
86 :
87 : // process result (if any)
88 5373 : for (int i=0; i<result_count; ++i) {
89 2688 : type = sqlite3_column_type(pstmt, i);
90 2688 : results[i].type = type;
91 :
92 2688 : if (type == SQLITE_NULL) {
93 32 : rc = SQLITE_OK;
94 32 : continue;
95 : }
96 :
97 : #ifdef CLOUDSYNC_UNITTEST
98 2656 : if (is_test) type = SQLITE_BLOB;
99 : #endif
100 2656 : if (type != expected_types[i]) {
101 1 : rc = SQLITE_ERROR;
102 1 : goto dbutils_exec_finalize;
103 : }
104 : #ifdef CLOUDSYNC_UNITTEST
105 : #endif
106 :
107 : // type == expected_type
108 2655 : if (type == SQLITE_INTEGER) results[i].value.intValue = sqlite3_column_int64(pstmt, i);
109 1826 : else if (type == SQLITE_FLOAT) results[i].value.doubleValue = sqlite3_column_double(pstmt, i);
110 : else {
111 : // TEXT or BLOB
112 1826 : int len = sqlite3_column_bytes(pstmt, i);
113 1826 : results[i].len = len;
114 : #if CLOUDSYNC_UNITTEST
115 1826 : if (is_test) len = SQLITE_MAX_ALLOCATION_SIZE + 1;
116 : #endif
117 :
118 1826 : char *buffer = NULL;
119 1826 : if (type == SQLITE_BLOB) {
120 53 : const void *bvalue = sqlite3_column_blob(pstmt, i);
121 53 : if (bvalue) {
122 53 : buffer = (char *)cloudsync_memory_alloc(len);
123 53 : if (!buffer) {rc = SQLITE_NOMEM; goto dbutils_exec_finalize;}
124 53 : memcpy(buffer, bvalue, len);
125 53 : }
126 53 : } else {
127 1773 : const unsigned char *value = sqlite3_column_text(pstmt, i);
128 1773 : if (value) buffer = cloudsync_string_dup((const char *)value, false);
129 : }
130 1826 : results[i].value.stringValue = buffer;
131 : }
132 2655 : }
133 :
134 2685 : rc = SQLITE_OK;
135 : dbutils_exec_finalize:
136 3075 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
137 3075 : if (rc != SQLITE_OK) {
138 : #ifdef CLOUDSYNC_UNITTEST
139 2 : if (is_test) count = -1;
140 : #endif
141 2 : if (count != -1) DEBUG_ALWAYS("Error executing %s in dbutils_exec (%s).", sql, sqlite3_errmsg(db));
142 2 : if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1);
143 2 : }
144 3075 : if (pstmt) sqlite3_finalize(pstmt);
145 :
146 3075 : if (is_write) {
147 341 : DATABASE_RESULT result = {0};
148 341 : result.rc = rc;
149 341 : return result;
150 : }
151 :
152 2734 : results[0].rc = rc;
153 2734 : return results[0];
154 3075 : }
155 :
156 340 : int dbutils_write (sqlite3 *db, sqlite3_context *context, const char *sql, const char **values, int types[], int lens[], int count) {
157 340 : DATABASE_RESULT result = dbutils_exec(context, db, sql, values, types, lens, count, NULL, NULL, 0);
158 340 : return result.rc;
159 : }
160 :
161 1 : int dbutils_write_simple (sqlite3 *db, const char *sql) {
162 1 : DATABASE_RESULT result = dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, NULL, NULL, 0);
163 1 : return result.rc;
164 : }
165 :
166 872 : sqlite3_int64 dbutils_int_select (sqlite3 *db, const char *sql) {
167 : // used only for cound(*), hash, or 1, so return -1 to signal an error
168 872 : DATABASE_RESULT results[1] = {0};
169 872 : int expected_types[1] = {SQLITE_INTEGER};
170 872 : dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
171 872 : return (results[0].rc == SQLITE_OK) ? results[0].value.intValue : -1;
172 : }
173 :
174 1804 : char *dbutils_text_select (sqlite3 *db, const char *sql) {
175 1804 : DATABASE_RESULT results[1] = {0};
176 1804 : int expected_types[1] = {SQLITE_TEXT};
177 1804 : dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
178 1804 : return results[0].value.stringValue;
179 : }
180 :
181 52 : char *dbutils_blob_select (sqlite3 *db, const char *sql, int *size, sqlite3_context *context, int *rc) {
182 52 : DATABASE_RESULT results[1] = {0};
183 52 : int expected_types[1] = {SQLITE_BLOB};
184 52 : dbutils_exec(context, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
185 52 : *size = results[0].len;
186 52 : *rc = results[0].rc;
187 52 : return results[0].value.stringValue;
188 : }
189 :
190 1 : int dbutils_blob_int_int_select (sqlite3 *db, const char *sql, char **blob, int *size, sqlite3_int64 *int1, sqlite3_int64 *int2) {
191 1 : DATABASE_RESULT results[3] = {0};
192 1 : int expected_types[3] = {SQLITE_BLOB, SQLITE_INTEGER, SQLITE_INTEGER};
193 1 : dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, results, expected_types, 3);
194 1 : *size = results[0].len;
195 1 : *blob = results[0].value.stringValue;
196 1 : *int1 = results[1].value.intValue;
197 1 : *int2 = results[2].value.intValue;
198 1 : return results[0].rc;
199 : }
200 :
201 5 : sqlite3_int64 dbutils_select (sqlite3 *db, const char *sql, const char **values, int types[], int lens[], int count, int expected_type) {
202 : // used only in unit-test
203 5 : DATABASE_RESULT results[1] = {0};
204 5 : int expected_types[1] = {expected_type};
205 5 : dbutils_exec(NULL, db, sql, values, types, lens, count, results, expected_types, 1);
206 5 : return results[0].value.intValue;
207 : }
208 :
209 : // MARK: -
210 :
211 : // compares two SQLite values and returns an integer indicating the comparison result
212 2477 : int dbutils_value_compare (sqlite3_value *lvalue, sqlite3_value *rvalue) {
213 2477 : if (lvalue == rvalue) return 0;
214 2477 : if (!lvalue) return -1;
215 2477 : if (!rvalue) return 1;
216 :
217 2477 : int l_type = (lvalue) ? sqlite3_value_type(lvalue) : SQLITE_NULL;
218 2477 : int r_type = sqlite3_value_type(rvalue);
219 :
220 : // early exit if types differ, null is less than all types
221 2477 : if (l_type != r_type) return (r_type - l_type);
222 :
223 : // at this point lvalue and rvalue are of the same type
224 2476 : switch (l_type) {
225 : case SQLITE_INTEGER: {
226 572 : sqlite3_int64 l_int = sqlite3_value_int64(lvalue);
227 572 : sqlite3_int64 r_int = sqlite3_value_int64(rvalue);
228 572 : return (l_int < r_int) ? -1 : (l_int > r_int);
229 : } break;
230 :
231 : case SQLITE_FLOAT: {
232 3 : double l_double = sqlite3_value_double(lvalue);
233 3 : double r_double = sqlite3_value_double(rvalue);
234 3 : return (l_double < r_double) ? -1 : (l_double > r_double);
235 : } break;
236 :
237 : case SQLITE_NULL:
238 34 : break;
239 :
240 : case SQLITE_TEXT: {
241 1864 : const unsigned char *l_text = sqlite3_value_text(lvalue);
242 1864 : const unsigned char *r_text = sqlite3_value_text(rvalue);
243 1864 : return strcmp((const char *)l_text, (const char *)r_text);
244 : } break;
245 :
246 : case SQLITE_BLOB: {
247 3 : const void *l_blob = sqlite3_value_blob(lvalue);
248 3 : const void *r_blob = sqlite3_value_blob(rvalue);
249 3 : int l_size = sqlite3_value_bytes(lvalue);
250 3 : int r_size = sqlite3_value_bytes(rvalue);
251 3 : int cmp = memcmp(l_blob, r_blob, (l_size < r_size) ? l_size : r_size);
252 3 : return (cmp != 0) ? cmp : (l_size - r_size);
253 : } break;
254 : }
255 :
256 34 : return 0;
257 2477 : }
258 :
259 16 : void dbutils_context_result_error (sqlite3_context *context, const char *format, ...) {
260 : char buffer[4096];
261 :
262 : va_list arg;
263 16 : va_start (arg, format);
264 16 : vsnprintf(buffer, sizeof(buffer), format, arg);
265 16 : va_end (arg);
266 :
267 16 : if (context) sqlite3_result_error(context, buffer, -1);
268 16 : }
269 :
270 : // MARK: -
271 :
272 28 : void dbutils_debug_value (sqlite3_value *value) {
273 28 : switch (sqlite3_value_type(value)) {
274 : case SQLITE_INTEGER:
275 7 : printf("\t\tINTEGER: %lld\n", sqlite3_value_int64(value));
276 7 : break;
277 : case SQLITE_FLOAT:
278 7 : printf("\t\tFLOAT: %f\n", sqlite3_value_double(value));
279 7 : break;
280 : case SQLITE_TEXT:
281 6 : printf("\t\tTEXT: %s (%d)\n", sqlite3_value_text(value), sqlite3_value_bytes(value));
282 6 : break;
283 : case SQLITE_BLOB:
284 6 : printf("\t\tBLOB: %p (%d)\n", (char *)sqlite3_value_blob(value), sqlite3_value_bytes(value));
285 6 : break;
286 : case SQLITE_NULL:
287 2 : printf("\t\tNULL\n");
288 2 : break;
289 : }
290 28 : }
291 :
292 14 : void dbutils_debug_values (int argc, sqlite3_value **argv) {
293 42 : for (int i = 0; i < argc; i++) {
294 28 : dbutils_debug_value(argv[i]);
295 28 : }
296 14 : }
297 :
298 57 : int dbutils_debug_stmt (sqlite3 *db, bool print_result) {
299 57 : sqlite3_stmt *stmt = NULL;
300 57 : int counter = 0;
301 58 : while ((stmt = sqlite3_next_stmt(db, stmt))) {
302 1 : ++counter;
303 1 : if (print_result) printf("Unfinalized stmt statement: %p\n", stmt);
304 : }
305 57 : return counter;
306 : }
307 :
308 : // MARK: -
309 :
310 1289 : int dbutils_register_function (sqlite3 *db, const char *name, void (*ptr)(sqlite3_context*,int,sqlite3_value**), int nargs, char **pzErrMsg, void *ctx, void (*ctx_free)(void *)) {
311 : DEBUG_DBFUNCTION("dbutils_register_function %s", name);
312 :
313 1289 : const int DEFAULT_FLAGS = SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC;
314 1289 : int rc = sqlite3_create_function_v2(db, name, nargs, DEFAULT_FLAGS, ctx, ptr, NULL, NULL, ctx_free);
315 :
316 1289 : if (rc != SQLITE_OK) {
317 1 : if (pzErrMsg) *pzErrMsg = cloudsync_memory_mprintf("Error creating function %s: %s", name, sqlite3_errmsg(db));
318 1 : return rc;
319 : }
320 :
321 1288 : return SQLITE_OK;
322 1289 : }
323 :
324 47 : int dbutils_register_aggregate (sqlite3 *db, const char *name, void (*xstep)(sqlite3_context*,int,sqlite3_value**), void (*xfinal)(sqlite3_context*), int nargs, char **pzErrMsg, void *ctx, void (*ctx_free)(void *)) {
325 : DEBUG_DBFUNCTION("dbutils_register_aggregate %s", name);
326 :
327 47 : const int DEFAULT_FLAGS = SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC;
328 47 : int rc = sqlite3_create_function_v2(db, name, nargs, DEFAULT_FLAGS, ctx, NULL, xstep, xfinal, ctx_free);
329 :
330 47 : if (rc != SQLITE_OK) {
331 1 : if (pzErrMsg) *pzErrMsg = cloudsync_memory_mprintf("Error creating aggregate function %s: %s", name, sqlite3_errmsg(db));
332 1 : return rc;
333 : }
334 :
335 46 : return SQLITE_OK;
336 47 : }
337 :
338 753 : bool dbutils_system_exists (sqlite3 *db, const char *name, const char *type) {
339 : DEBUG_DBFUNCTION("dbutils_system_exists %s: %s", type, name);
340 :
341 753 : sqlite3_stmt *vm = NULL;
342 753 : bool result = false;
343 :
344 : char sql[1024];
345 753 : snprintf(sql, sizeof(sql), "SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type='%s' AND name=?1 COLLATE NOCASE);", type);
346 753 : int rc = sqlite3_prepare_v2(db, sql, -1, &vm, NULL);
347 753 : if (rc != SQLITE_OK) goto finalize;
348 :
349 753 : rc = sqlite3_bind_text(vm, 1, name, -1, SQLITE_STATIC);
350 753 : if (rc != SQLITE_OK) goto finalize;
351 :
352 753 : rc = sqlite3_step(vm);
353 1506 : if (rc == SQLITE_ROW) {
354 753 : result = (bool)sqlite3_column_int(vm, 0);
355 753 : rc = SQLITE_OK;
356 753 : }
357 :
358 : finalize:
359 753 : if (rc != SQLITE_OK) DEBUG_ALWAYS("Error executing %s in dbutils_system_exists for type %s name %s (%s).", sql, type, name, sqlite3_errmsg(db));
360 753 : if (vm) sqlite3_finalize(vm);
361 753 : return result;
362 : }
363 :
364 458 : bool dbutils_table_exists (sqlite3 *db, const char *name) {
365 458 : return dbutils_system_exists(db, name, "table");
366 : }
367 :
368 294 : bool dbutils_trigger_exists (sqlite3 *db, const char *name) {
369 294 : return dbutils_system_exists(db, name, "trigger");
370 : }
371 :
372 113 : bool dbutils_table_sanity_check (sqlite3 *db, sqlite3_context *context, const char *name, bool skip_int_pk_check) {
373 : DEBUG_DBFUNCTION("dbutils_table_sanity_check %s", name);
374 :
375 : char buffer[2048];
376 113 : size_t blen = sizeof(buffer);
377 :
378 : // sanity check table name
379 113 : if (name == NULL) {
380 1 : dbutils_context_result_error(context, "%s", "cloudsync_init requires a non-null table parameter");
381 1 : return false;
382 : }
383 :
384 : // avoid allocating heap memory for SQL statements by setting a maximum length of 1900 characters
385 : // for table names. This limit is reasonable and helps prevent memory management issues.
386 112 : const size_t maxlen = blen - 148;
387 112 : if (strlen(name) > maxlen) {
388 1 : dbutils_context_result_error(context, "Table name cannot be longer than %d characters", maxlen);
389 1 : return false;
390 : }
391 :
392 : // check if table exists
393 111 : if (dbutils_table_exists(db, name) == false) {
394 2 : dbutils_context_result_error(context, "Table %s does not exist", name);
395 2 : return false;
396 : }
397 :
398 : // no more than 128 columns can be used as a composite primary key (SQLite hard limit)
399 109 : char *sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0;", name);
400 109 : sqlite3_int64 count = dbutils_int_select(db, sql);
401 109 : if (count > 128) {
402 1 : dbutils_context_result_error(context, "No more than 128 columns can be used to form a composite primary key");
403 1 : return false;
404 108 : } else if (count == -1) {
405 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
406 0 : return false;
407 : }
408 :
409 : #if CLOUDSYNC_DISABLE_ROWIDONLY_TABLES
410 : // if count == 0 means that rowid will be used as primary key (BTW: very bad choice for the user)
411 108 : if (count == 0) {
412 1 : dbutils_context_result_error(context, "Rowid only tables are not supported, all primary keys must be explicitly set and declared as NOT NULL (table %s)", name);
413 1 : return false;
414 : }
415 : #endif
416 :
417 107 : if (!skip_int_pk_check) {
418 79 : if (count == 1) {
419 : // the affinity of a column is determined by the declared type of the column,
420 : // according to the following rules in the order shown:
421 : // 1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.
422 19 : sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=1 AND \"type\" LIKE '%%INT%%';", name);
423 19 : sqlite3_int64 count2 = dbutils_int_select(db, sql);
424 19 : if (count == count2) {
425 2 : dbutils_context_result_error(context, "Table %s uses an single-column INTEGER primary key. For CRDT replication, primary keys must be globally unique. Consider using a TEXT primary key with UUIDs or ULID to avoid conflicts across nodes. If you understand the risk and still want to use this INTEGER primary key, set the third argument of the cloudsync_init function to 1 to skip this check.", name);
426 2 : return false;
427 : }
428 17 : if (count2 == -1) {
429 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
430 0 : return false;
431 : }
432 17 : }
433 77 : }
434 :
435 : // if user declared explicit primary key(s) then make sure they are all declared as NOT NULL
436 105 : if (count > 0) {
437 105 : sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0 AND \"notnull\"=1;", name);
438 105 : sqlite3_int64 count2 = dbutils_int_select(db, sql);
439 105 : if (count2 == -1) {
440 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
441 0 : return false;
442 : }
443 105 : if (count != count2) {
444 1 : dbutils_context_result_error(context, "All primary keys must be explicitly declared as NOT NULL (table %s)", name);
445 1 : return false;
446 : }
447 104 : }
448 :
449 : // check for columns declared as NOT NULL without a DEFAULT value.
450 : // Otherwise, col_merge_stmt would fail if changes to other columns are inserted first.
451 104 : sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=0 AND \"notnull\"=1 AND \"dflt_value\" IS NULL;", name);
452 104 : sqlite3_int64 count3 = dbutils_int_select(db, sql);
453 104 : if (count3 == -1) {
454 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
455 0 : return false;
456 : }
457 104 : if (count3 > 0) {
458 1 : dbutils_context_result_error(context, "All non-primary key columns declared as NOT NULL must have a DEFAULT value. (table %s)", name);
459 1 : return false;
460 : }
461 :
462 103 : return true;
463 113 : }
464 :
465 29 : int dbutils_delete_triggers (sqlite3 *db, const char *table) {
466 : DEBUG_DBFUNCTION("dbutils_delete_triggers %s", table);
467 :
468 : // from dbutils_table_sanity_check we already know that 2048 is OK
469 : char buffer[2048];
470 29 : size_t blen = sizeof(buffer);
471 29 : int rc = SQLITE_ERROR;
472 :
473 29 : char *sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_before_update_%w\";", table);
474 29 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
475 29 : if (rc != SQLITE_OK) goto finalize;
476 :
477 29 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_before_delete_%w\";", table);
478 29 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
479 29 : if (rc != SQLITE_OK) goto finalize;
480 :
481 29 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_insert_%w\";", table);
482 29 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
483 29 : if (rc != SQLITE_OK) goto finalize;
484 :
485 29 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_update_%w\";", table);
486 29 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
487 29 : if (rc != SQLITE_OK) goto finalize;
488 :
489 29 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_delete_%w\";", table);
490 29 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
491 29 : if (rc != SQLITE_OK) goto finalize;
492 :
493 : finalize:
494 29 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_delete_triggers error %s (%s)", sqlite3_errmsg(db), sql);
495 29 : return rc;
496 : }
497 :
498 98 : int dbutils_check_triggers (sqlite3 *db, const char *table, table_algo algo) {
499 : DEBUG_DBFUNCTION("dbutils_check_triggers %s", table);
500 :
501 98 : char *trigger_name = NULL;
502 98 : int rc = SQLITE_NOMEM;
503 :
504 : // common part
505 98 : char *trigger_when = cloudsync_memory_mprintf("FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0", table);
506 98 : if (!trigger_when) goto finalize;
507 :
508 : // INSERT TRIGGER
509 : // NEW.prikey1, NEW.prikey2...
510 98 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_insert_%s", table);
511 98 : if (!trigger_name) goto finalize;
512 :
513 98 : if (!dbutils_trigger_exists(db, trigger_name)) {
514 92 : rc = SQLITE_NOMEM;
515 92 : char *sql = cloudsync_memory_mprintf("SELECT group_concat('NEW.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table);
516 92 : if (!sql) goto finalize;
517 :
518 92 : char *pkclause = dbutils_text_select(db, sql);
519 92 : char *pkvalues = (pkclause) ? pkclause : "NEW.rowid";
520 92 : cloudsync_memory_free(sql);
521 :
522 92 : sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER INSERT ON \"%w\" %s BEGIN SELECT cloudsync_insert('%q', %s); END", trigger_name, table, trigger_when, table, pkvalues);
523 92 : if (pkclause) cloudsync_memory_free(pkclause);
524 92 : if (!sql) goto finalize;
525 :
526 92 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
527 : DEBUG_SQL("\n%s", sql);
528 92 : cloudsync_memory_free(sql);
529 92 : if (rc != SQLITE_OK) goto finalize;
530 92 : }
531 98 : cloudsync_memory_free(trigger_name);
532 98 : trigger_name = NULL;
533 98 : rc = SQLITE_NOMEM;
534 :
535 98 : if (algo != table_algo_crdt_gos) {
536 91 : rc = SQLITE_NOMEM;
537 :
538 : // UPDATE TRIGGER
539 : // NEW.prikey1, NEW.prikey2, OLD.prikey1, OLD.prikey2, NEW.col1, OLD.col1, NEW.col2, OLD.col2...
540 91 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_update_%s", table);
541 91 : if (!trigger_name) goto finalize;
542 :
543 91 : if (!dbutils_trigger_exists(db, trigger_name)) {
544 85 : char *sql = cloudsync_memory_mprintf("SELECT group_concat('NEW.\"' || format('%%w', name) || '\"', ',') || ',' || group_concat('OLD.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table);
545 85 : if (!sql) goto finalize;
546 :
547 85 : char *pkclause = dbutils_text_select(db, sql);
548 85 : char *pkvalues = (pkclause) ? pkclause : "NEW.rowid,OLD.rowid";
549 85 : cloudsync_memory_free(sql);
550 :
551 85 : sql = cloudsync_memory_mprintf("SELECT group_concat('NEW.\"' || format('%%w', name) || '\"' || ', OLD.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk=0 ORDER BY cid;", table);
552 85 : if (!sql) goto finalize;
553 85 : char *colvalues = dbutils_text_select(db, sql);
554 85 : cloudsync_memory_free(sql);
555 :
556 85 : if (colvalues == NULL) {
557 31 : sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER UPDATE ON \"%w\" %s BEGIN SELECT cloudsync_update('%q',%s); END", trigger_name, table, trigger_when, table, pkvalues);
558 31 : } else {
559 54 : sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER UPDATE ON \"%w\" %s BEGIN SELECT cloudsync_update('%q',%s,%s); END", trigger_name, table, trigger_when, table, pkvalues, colvalues);
560 54 : cloudsync_memory_free(colvalues);
561 : }
562 85 : if (pkclause) cloudsync_memory_free(pkclause);
563 85 : if (!sql) goto finalize;
564 :
565 85 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
566 : DEBUG_SQL("\n%s", sql);
567 85 : cloudsync_memory_free(sql);
568 85 : if (rc != SQLITE_OK) goto finalize;
569 85 : }
570 91 : cloudsync_memory_free(trigger_name);
571 91 : trigger_name = NULL;
572 91 : } else {
573 : // Grow Only Set
574 : // In a grow-only set, the update operation is not allowed.
575 : // A grow-only set is a type of CRDT (Conflict-free Replicated Data Type) where the only permissible operation is to add elements to the set,
576 : // without ever removing or modifying them.
577 : // Once an element is added to the set, it remains there permanently, which guarantees that the set only grows over time.
578 7 : trigger_name = cloudsync_memory_mprintf("cloudsync_before_update_%s", table);
579 7 : if (!trigger_name) goto finalize;
580 :
581 7 : if (!dbutils_trigger_exists(db, trigger_name)) {
582 7 : char *sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" BEFORE UPDATE ON \"%w\" FOR EACH ROW WHEN cloudsync_is_enabled('%q') = 1 BEGIN SELECT RAISE(ABORT, 'Error: UPDATE operation is not allowed on table %w.'); END", trigger_name, table, table, table);
583 7 : if (!sql) goto finalize;
584 :
585 7 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
586 : DEBUG_SQL("\n%s", sql);
587 7 : cloudsync_memory_free(sql);
588 7 : if (rc != SQLITE_OK) goto finalize;
589 7 : }
590 7 : cloudsync_memory_free(trigger_name);
591 7 : trigger_name = NULL;
592 : }
593 :
594 : // DELETE TRIGGER
595 : // OLD.prikey1, OLD.prikey2...
596 98 : if (algo != table_algo_crdt_gos) {
597 91 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_delete_%s", table);
598 91 : if (!trigger_name) goto finalize;
599 :
600 91 : if (!dbutils_trigger_exists(db, trigger_name)) {
601 85 : char *sql = cloudsync_memory_mprintf("SELECT group_concat('OLD.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table);
602 85 : if (!sql) goto finalize;
603 :
604 85 : char *pkclause = dbutils_text_select(db, sql);
605 85 : char *pkvalues = (pkclause) ? pkclause : "OLD.rowid";
606 85 : cloudsync_memory_free(sql);
607 :
608 85 : sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER DELETE ON \"%w\" %s BEGIN SELECT cloudsync_delete('%q',%s); END", trigger_name, table, trigger_when, table, pkvalues);
609 85 : if (pkclause) cloudsync_memory_free(pkclause);
610 85 : if (!sql) goto finalize;
611 :
612 85 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
613 : DEBUG_SQL("\n%s", sql);
614 85 : cloudsync_memory_free(sql);
615 85 : if (rc != SQLITE_OK) goto finalize;
616 85 : }
617 :
618 91 : cloudsync_memory_free(trigger_name);
619 91 : trigger_name = NULL;
620 91 : } else {
621 : // Grow Only Set
622 : // In a grow-only set, the delete operation is not allowed.
623 7 : trigger_name = cloudsync_memory_mprintf("cloudsync_before_delete_%s", table);
624 7 : if (!trigger_name) goto finalize;
625 :
626 7 : if (!dbutils_trigger_exists(db, trigger_name)) {
627 7 : char *sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" BEFORE DELETE ON \"%w\" FOR EACH ROW WHEN cloudsync_is_enabled('%q') = 1 BEGIN SELECT RAISE(ABORT, 'Error: DELETE operation is not allowed on table %w.'); END", trigger_name, table, table, table);
628 7 : if (!sql) goto finalize;
629 :
630 7 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
631 : DEBUG_SQL("\n%s", sql);
632 7 : cloudsync_memory_free(sql);
633 7 : if (rc != SQLITE_OK) goto finalize;
634 7 : }
635 7 : cloudsync_memory_free(trigger_name);
636 7 : trigger_name = NULL;
637 : }
638 :
639 98 : rc = SQLITE_OK;
640 :
641 : finalize:
642 98 : if (trigger_name) cloudsync_memory_free(trigger_name);
643 98 : if (trigger_when) cloudsync_memory_free(trigger_when);
644 98 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_create_triggers error %s (%d)", sqlite3_errmsg(db), rc);
645 98 : return rc;
646 : }
647 :
648 98 : int dbutils_check_metatable (sqlite3 *db, const char *table, table_algo algo) {
649 : DEBUG_DBFUNCTION("dbutils_check_metatable %s", table);
650 :
651 : // WITHOUT ROWID is available starting from SQLite version 3.8.2 (2013-12-06) and later
652 98 : char *sql = cloudsync_memory_mprintf("CREATE TABLE IF NOT EXISTS \"%w_cloudsync\" (pk BLOB NOT NULL, col_name TEXT NOT NULL, col_version INTEGER, db_version INTEGER, site_id INTEGER DEFAULT 0, seq INTEGER, PRIMARY KEY (pk, col_name)) WITHOUT ROWID; CREATE INDEX IF NOT EXISTS \"%w_cloudsync_db_idx\" ON \"%w_cloudsync\" (db_version);", table, table, table);
653 98 : if (!sql) return SQLITE_NOMEM;
654 :
655 98 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
656 : DEBUG_SQL("\n%s", sql);
657 98 : cloudsync_memory_free(sql);
658 :
659 98 : return rc;
660 98 : }
661 :
662 :
663 46 : sqlite3_int64 dbutils_schema_version (sqlite3 *db) {
664 : DEBUG_DBFUNCTION("dbutils_schema_version");
665 :
666 46 : return dbutils_int_select(db, "PRAGMA schema_version;");
667 : }
668 :
669 380 : bool dbutils_is_star_table (const char *table_name) {
670 380 : return (table_name && (strlen(table_name) == 1) && table_name[0] == '*');
671 : }
672 :
673 : // MARK: - Settings -
674 :
675 8 : int binary_comparison (int x, int y) {
676 8 : if (x == y) return 0;
677 3 : if (x > y) return 1;
678 2 : return -1;
679 8 : }
680 :
681 171 : char *dbutils_settings_get_value (sqlite3 *db, const char *key, char *buffer, size_t blen) {
682 : DEBUG_SETTINGS("dbutils_settings_get_value key: %s", key);
683 :
684 : // check if heap allocation must be forced
685 171 : if (!buffer || blen == 0) blen = 0;
686 171 : size_t size = 0;
687 :
688 171 : sqlite3_stmt *vm = NULL;
689 171 : char *sql = "SELECT value FROM cloudsync_settings WHERE key=?1;";
690 171 : int rc = sqlite3_prepare(db, sql, -1, &vm, NULL);
691 171 : if (rc != SQLITE_OK) goto finalize_get_value;
692 :
693 171 : rc = sqlite3_bind_text(vm, 1, key, -1, SQLITE_STATIC);
694 171 : if (rc != SQLITE_OK) goto finalize_get_value;
695 :
696 171 : rc = sqlite3_step(vm);
697 171 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
698 86 : else if (rc != SQLITE_ROW) goto finalize_get_value;
699 :
700 : // SQLITE_ROW case
701 171 : if (sqlite3_column_type(vm, 0) == SQLITE_NULL) {
702 85 : rc = SQLITE_OK;
703 85 : goto finalize_get_value;
704 : }
705 :
706 86 : const unsigned char *value = sqlite3_column_text(vm, 0);
707 : #if CLOUDSYNC_UNITTEST
708 86 : size = (buffer == OUT_OF_MEMORY_BUFFER) ? (SQLITE_MAX_ALLOCATION_SIZE + 1) :(size_t)sqlite3_column_bytes(vm, 0);
709 : #else
710 : size = (size_t)sqlite3_column_bytes(vm, 0);
711 : #endif
712 86 : if (size + 1 > blen) {
713 2 : buffer = cloudsync_memory_alloc((sqlite3_uint64)(size + 1));
714 2 : if (!buffer) {
715 1 : rc = SQLITE_NOMEM;
716 1 : goto finalize_get_value;
717 : }
718 1 : }
719 :
720 85 : memcpy(buffer, value, size+1);
721 85 : rc = SQLITE_OK;
722 :
723 : finalize_get_value:
724 : #if CLOUDSYNC_UNITTEST
725 171 : if ((rc == SQLITE_NOMEM) && (size == SQLITE_MAX_ALLOCATION_SIZE + 1)) rc = SQLITE_OK;
726 : #endif
727 171 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_settings_get_value error %s", sqlite3_errmsg(db));
728 171 : if (vm) sqlite3_finalize(vm);
729 :
730 171 : return buffer;
731 : }
732 :
733 151 : int dbutils_settings_set_key_value (sqlite3 *db, sqlite3_context *context, const char *key, const char *value) {
734 : DEBUG_SETTINGS("dbutils_settings_set_key_value key: %s value: %s", key, value);
735 :
736 151 : int rc = SQLITE_OK;
737 151 : if (db == NULL) db = sqlite3_context_db_handle(context);
738 :
739 151 : if (key && value) {
740 150 : char *sql = "REPLACE INTO cloudsync_settings (key, value) VALUES (?1, ?2);";
741 150 : const char *values[] = {key, value};
742 150 : int types[] = {SQLITE_TEXT, SQLITE_TEXT};
743 150 : int lens[] = {-1, -1};
744 150 : rc = dbutils_write(db, context, sql, values, types, lens, 2);
745 150 : }
746 :
747 151 : if (value == NULL) {
748 1 : char *sql = "DELETE FROM cloudsync_settings WHERE key = ?1;";
749 1 : const char *values[] = {key};
750 1 : int types[] = {SQLITE_TEXT};
751 1 : int lens[] = {-1};
752 1 : rc = dbutils_write(db, context, sql, values, types, lens, 1);
753 1 : }
754 :
755 151 : cloudsync_context *data = (context) ? (cloudsync_context *)sqlite3_user_data(context) : NULL;
756 151 : if (rc == SQLITE_OK && data) cloudsync_sync_key(data, key, value);
757 151 : return rc;
758 : }
759 :
760 166 : int dbutils_settings_get_int_value (sqlite3 *db, const char *key) {
761 : DEBUG_SETTINGS("dbutils_settings_get_int_value key: %s", key);
762 166 : char buffer[256] = {0};
763 166 : if (dbutils_settings_get_value(db, key, buffer, sizeof(buffer)) == NULL) return -1;
764 :
765 166 : return (int)strtol(buffer, NULL, 0);
766 166 : }
767 :
768 2 : int dbutils_settings_check_version (sqlite3 *db) {
769 : DEBUG_SETTINGS("dbutils_settings_check_version");
770 : char buffer[256];
771 2 : if (dbutils_settings_get_value(db, CLOUDSYNC_KEY_LIBVERSION, buffer, sizeof(buffer)) == NULL) return -666;
772 :
773 : int major1, minor1, patch1;
774 : int major2, minor2, patch2;
775 2 : int count1 = sscanf(buffer, "%d.%d.%d", &major1, &minor1, &patch1);
776 2 : int count2 = sscanf(CLOUDSYNC_VERSION, "%d.%d.%d", &major2, &minor2, &patch2);
777 :
778 2 : if (count1 != 3 || count2 != 3) return -666;
779 :
780 2 : int res = 0;
781 2 : if ((res = binary_comparison(major1, major2)) == 0) {
782 2 : if ((res = binary_comparison(minor1, minor2)) == 0) {
783 1 : return binary_comparison(patch1, patch2);
784 : }
785 1 : }
786 :
787 : DEBUG_SETTINGS(" %s %s (%d)", buffer, CLOUDSYNC_VERSION, res);
788 1 : return res;
789 2 : }
790 :
791 124 : char *dbutils_table_settings_get_value (sqlite3 *db, const char *table, const char *column, const char *key, char *buffer, size_t blen) {
792 : DEBUG_SETTINGS("dbutils_table_settings_get_value table: %s column: %s key: %s", table, column, key);
793 :
794 : // check if heap allocation must be forced
795 124 : if (!buffer || blen == 0) blen = 0;
796 124 : size_t size = 0;
797 :
798 124 : sqlite3_stmt *vm = NULL;
799 124 : char *sql = "SELECT value FROM cloudsync_table_settings WHERE (tbl_name=?1 AND col_name=?2 AND key=?3);";
800 124 : int rc = sqlite3_prepare(db, sql, -1, &vm, NULL);
801 124 : if (rc != SQLITE_OK) goto finalize_get_value;
802 :
803 124 : rc = sqlite3_bind_text(vm, 1, table, -1, SQLITE_STATIC);
804 124 : if (rc != SQLITE_OK) goto finalize_get_value;
805 :
806 124 : rc = sqlite3_bind_text(vm, 2, (column) ? column : "*", -1, SQLITE_STATIC);
807 124 : if (rc != SQLITE_OK) goto finalize_get_value;
808 :
809 124 : rc = sqlite3_bind_text(vm, 3, key, -1, SQLITE_STATIC);
810 124 : if (rc != SQLITE_OK) goto finalize_get_value;
811 :
812 124 : rc = sqlite3_step(vm);
813 124 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
814 54 : else if (rc != SQLITE_ROW) goto finalize_get_value;
815 :
816 : // SQLITE_ROW case
817 124 : if (sqlite3_column_type(vm, 0) == SQLITE_NULL) {
818 70 : rc = SQLITE_OK;
819 70 : goto finalize_get_value;
820 : }
821 :
822 54 : const unsigned char *value = sqlite3_column_text(vm, 0);
823 : #if CLOUDSYNC_UNITTEST
824 54 : size = (buffer == OUT_OF_MEMORY_BUFFER) ? (SQLITE_MAX_ALLOCATION_SIZE + 1) :(size_t)sqlite3_column_bytes(vm, 0);
825 : #else
826 : size = (size_t)sqlite3_column_bytes(vm, 0);
827 : #endif
828 54 : if (size + 1 > blen) {
829 2 : buffer = cloudsync_memory_alloc((sqlite3_uint64)(size + 1));
830 2 : if (!buffer) {
831 1 : rc = SQLITE_NOMEM;
832 1 : goto finalize_get_value;
833 : }
834 1 : }
835 :
836 53 : memcpy(buffer, value, size+1);
837 53 : rc = SQLITE_OK;
838 :
839 : finalize_get_value:
840 : #if CLOUDSYNC_UNITTEST
841 124 : if ((rc == SQLITE_NOMEM) && (size == SQLITE_MAX_ALLOCATION_SIZE + 1)) rc = SQLITE_OK;
842 : #endif
843 124 : if (rc != SQLITE_OK) {
844 0 : DEBUG_ALWAYS("cloudsync_table_settings error %s", sqlite3_errmsg(db));
845 0 : }
846 124 : if (vm) sqlite3_finalize(vm);
847 :
848 124 : return buffer;
849 : }
850 :
851 81 : int dbutils_table_settings_set_key_value (sqlite3 *db, sqlite3_context *context, const char *table, const char *column, const char *key, const char *value) {
852 : DEBUG_SETTINGS("dbutils_table_settings_set_key_value table: %s column: %s key: %s", table, column, key);
853 :
854 81 : int rc = SQLITE_OK;
855 81 : if (db == NULL) db = sqlite3_context_db_handle(context);
856 :
857 : // sanity check tbl_name
858 81 : if (table == NULL) {
859 1 : if (context) sqlite3_result_error(context, "cloudsync_set_table/set_column requires a non-null table parameter", -1);
860 1 : return SQLITE_ERROR;
861 : }
862 :
863 : // sanity check column name
864 80 : if (column == NULL) column = "*";
865 :
866 : // remove all table_name entries
867 80 : if (key == NULL) {
868 6 : char *sql = "DELETE FROM cloudsync_table_settings WHERE tbl_name=?1;";
869 6 : const char *values[] = {table};
870 6 : int types[] = {SQLITE_TEXT};
871 6 : int lens[] = {-1};
872 6 : rc = dbutils_write(db, context, sql, values, types, lens, 1);
873 6 : return rc;
874 : }
875 :
876 74 : if (key && value) {
877 73 : char *sql = "REPLACE INTO cloudsync_table_settings (tbl_name, col_name, key, value) VALUES (?1, ?2, ?3, ?4);";
878 73 : const char *values[] = {table, column, key, value};
879 73 : int types[] = {SQLITE_TEXT, SQLITE_TEXT, SQLITE_TEXT, SQLITE_TEXT};
880 73 : int lens[] = {-1, -1, -1, -1};
881 73 : rc = dbutils_write(db, context, sql, values, types, lens, 4);
882 73 : }
883 :
884 74 : if (value == NULL) {
885 1 : char *sql = "DELETE FROM cloudsync_table_settings WHERE (tbl_name=?1 AND col_name=?2 AND key=?3);";
886 1 : const char *values[] = {table, column, key};
887 1 : int types[] = {SQLITE_TEXT, SQLITE_TEXT, SQLITE_TEXT};
888 1 : int lens[] = {-1, -1, -1};
889 1 : rc = dbutils_write(db, context, sql, values, types, lens, 3);
890 1 : }
891 :
892 : // unused in this version
893 : // cloudsync_context *data = (context) ? (cloudsync_context *)sqlite3_user_data(context) : NULL;
894 : // if (rc == SQLITE_OK && data) cloudsync_sync_table_key(data, table, column, key, value);
895 74 : return rc;
896 81 : }
897 :
898 242 : sqlite3_int64 dbutils_table_settings_count_tables (sqlite3 *db) {
899 : DEBUG_SETTINGS("dbutils_table_settings_count_tables");
900 242 : return dbutils_int_select(db, "SELECT count(*) FROM cloudsync_table_settings WHERE key='algo';");
901 : }
902 :
903 121 : table_algo dbutils_table_settings_get_algo (sqlite3 *db, const char *table_name) {
904 : DEBUG_SETTINGS("dbutils_table_settings_get_algo %s", table_name);
905 :
906 : char buffer[512];
907 121 : char *value = dbutils_table_settings_get_value(db, table_name, "*", "algo", buffer, sizeof(buffer));
908 121 : return (value) ? crdt_algo_from_name(value) : table_algo_none;
909 : }
910 :
911 96 : int dbutils_settings_load_callback (void *xdata, int ncols, char **values, char **names) {
912 96 : cloudsync_context *data = (cloudsync_context *)xdata;
913 :
914 192 : for (int i=0; i<ncols; i+=2) {
915 96 : const char *key = values[i];
916 96 : const char *value = values[i+1];
917 96 : cloudsync_sync_key(data, key, value);
918 : DEBUG_SETTINGS("key: %s value: %s", key, value);
919 96 : }
920 :
921 96 : return 0;
922 : }
923 :
924 : bool table_add_to_context (sqlite3 *db, cloudsync_context *data, table_algo algo, const char *table_name);
925 :
926 2 : int dbutils_settings_table_load_callback (void *xdata, int ncols, char **values, char **names) {
927 2 : dbutils_settings_table_context *context = (dbutils_settings_table_context *)xdata;
928 2 : cloudsync_context *data = context->data;
929 2 : sqlite3 *db = context->db;
930 :
931 4 : for (int i=0; i<ncols; i+=4) {
932 2 : const char *table_name = values[i];
933 : // const char *col_name = values[i+1];
934 2 : const char *key = values[i+2];
935 2 : const char *value = values[i+3];
936 2 : if (strcmp(key, "algo")!=0) continue;
937 :
938 2 : table_add_to_context(db, data, crdt_algo_from_name(value), table_name);
939 : DEBUG_SETTINGS("load tbl_name: %s value: %s", key, value);
940 2 : }
941 :
942 2 : return 0;
943 : }
944 :
945 1 : bool dbutils_migrate (sqlite3 *db) {
946 : // dbutils_settings_check_version comparison failed
947 : // so check for logic migration here (if necessary)
948 1 : return true;
949 : }
950 :
951 48 : int dbutils_settings_load (sqlite3 *db, cloudsync_context *data) {
952 : DEBUG_SETTINGS("dbutils_settings_load %p", data);
953 :
954 : // load global settings
955 48 : const char *sql = "SELECT key, value FROM cloudsync_settings;";
956 48 : int rc = sqlite3_exec(db, sql, dbutils_settings_load_callback, data, NULL);
957 48 : if (rc != SQLITE_OK) DEBUG_ALWAYS("cloudsync_load_settings error: %s", sqlite3_errmsg(db));
958 :
959 : // load table-specific settings
960 48 : dbutils_settings_table_context xdata = {.db = db, .data = data};
961 48 : sql = "SELECT lower(tbl_name), lower(col_name), key, value FROM cloudsync_table_settings ORDER BY tbl_name;";
962 48 : rc = sqlite3_exec(db, sql, dbutils_settings_table_load_callback, &xdata, NULL);
963 48 : if (rc != SQLITE_OK) DEBUG_ALWAYS("cloudsync_load_settings error: %s", sqlite3_errmsg(db));
964 :
965 48 : return SQLITE_OK;
966 : }
967 :
968 48 : int dbutils_settings_init (sqlite3 *db, void *cloudsync_data, sqlite3_context *context) {
969 : DEBUG_SETTINGS("dbutils_settings_init %p", context);
970 :
971 48 : cloudsync_context *data = (cloudsync_context *)cloudsync_data;
972 48 : if (!data) data = (cloudsync_context *)sqlite3_user_data(context);
973 :
974 : // check if cloudsync_settings table exists
975 48 : bool settings_exists = dbutils_table_exists(db, CLOUDSYNC_SETTINGS_NAME);
976 48 : if (settings_exists == false) {
977 : DEBUG_SETTINGS("cloudsync_settings does not exist (creating a new one)");
978 :
979 : char sql[1024];
980 46 : int rc = SQLITE_OK;
981 :
982 : // create table and fill-in initial data
983 46 : snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS cloudsync_settings (key TEXT PRIMARY KEY NOT NULL COLLATE NOCASE, value TEXT);");
984 46 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
985 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
986 :
987 : // library version
988 46 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_settings (key, value) VALUES ('%s', '%s');", CLOUDSYNC_KEY_LIBVERSION, CLOUDSYNC_VERSION);
989 46 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
990 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
991 :
992 : // schema version
993 46 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_settings (key, value) VALUES ('%s', %lld);", CLOUDSYNC_KEY_SCHEMAVERSION, (long long)dbutils_schema_version(db));
994 46 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
995 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
996 46 : }
997 :
998 48 : if (dbutils_table_exists(db, CLOUDSYNC_SITEID_NAME) == false) {
999 : DEBUG_SETTINGS("cloudsync_site_id does not exist (creating a new one)");
1000 :
1001 : // create table and fill-in initial data
1002 : // site_id is implicitly indexed
1003 : // the rowid column is the primary key
1004 46 : char *sql = "CREATE TABLE IF NOT EXISTS cloudsync_site_id (site_id BLOB UNIQUE NOT NULL);";
1005 46 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1006 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1007 :
1008 : // siteid (to uniquely identify this local copy of the database)
1009 : uint8_t site_id[UUID_LEN];
1010 46 : if (cloudsync_uuid_v7(site_id) == -1) {if (context) sqlite3_result_error(context, "Unable to create UUIDv7 site_id", -1); return SQLITE_ERROR;}
1011 :
1012 : // rowid 0 means local site_id
1013 46 : sql = "INSERT INTO cloudsync_site_id (rowid, site_id) VALUES (?, ?);";
1014 46 : const char *values[] = {"0", (const char *)&site_id};
1015 46 : int types[] = {SQLITE_INTEGER, SQLITE_BLOB};
1016 46 : int lens[] = {-1, UUID_LEN};
1017 46 : rc = dbutils_write(db, context, sql, values, types, lens, 2);
1018 46 : if (rc != SQLITE_OK) return rc;
1019 46 : }
1020 :
1021 : // check if cloudsync_table_settings table exists
1022 48 : if (dbutils_table_exists(db, CLOUDSYNC_TABLE_SETTINGS_NAME) == false) {
1023 : DEBUG_SETTINGS("cloudsync_table_settings does not exist (creating a new one)");
1024 :
1025 46 : char *sql = "CREATE TABLE IF NOT EXISTS cloudsync_table_settings (tbl_name TEXT NOT NULL COLLATE NOCASE, col_name TEXT NOT NULL COLLATE NOCASE, key TEXT, value TEXT, PRIMARY KEY(tbl_name,key));";
1026 46 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1027 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1028 46 : }
1029 :
1030 : // check if cloudsync_settings table exists
1031 48 : bool schema_versions_exists = dbutils_table_exists(db, CLOUDSYNC_SCHEMA_VERSIONS_NAME);
1032 48 : if (schema_versions_exists == false) {
1033 : DEBUG_SETTINGS("cloudsync_schema_versions does not exist (creating a new one)");
1034 :
1035 46 : int rc = SQLITE_OK;
1036 :
1037 : // create table
1038 46 : char *sql = "CREATE TABLE IF NOT EXISTS cloudsync_schema_versions (hash INTEGER PRIMARY KEY, seq INTEGER NOT NULL)";
1039 46 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1040 46 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1041 46 : }
1042 :
1043 : // cloudsync_settings table exists so load it
1044 48 : dbutils_settings_load(db, data);
1045 :
1046 : // check if some process changed schema outside of the lib
1047 : /*
1048 : if ((settings_exists == true) && (data->schema_version != dbutils_schema_version(db))) {
1049 : // SOMEONE CHANGED SCHEMAs SO WE NEED TO RECHECK AUGMENTED TABLES and RELATED TRIGGERS
1050 : assert(0);
1051 : }
1052 : */
1053 :
1054 48 : return SQLITE_OK;
1055 48 : }
1056 :
1057 95 : int dbutils_update_schema_hash(sqlite3 *db, uint64_t *hash) {
1058 95 : char *schemasql = "SELECT group_concat(LOWER(sql)) FROM sqlite_master "
1059 : "WHERE type = 'table' AND name IN (SELECT tbl_name FROM cloudsync_table_settings ORDER BY tbl_name) "
1060 : "ORDER BY name;";
1061 95 : char *schema = dbutils_text_select(db, schemasql);
1062 95 : if (!schema) return SQLITE_ERROR;
1063 :
1064 95 : sqlite3_uint64 h = fnv1a_hash(schema, strlen(schema));
1065 95 : cloudsync_memory_free(schema);
1066 95 : if (hash && *hash == h) return SQLITE_CONSTRAINT;
1067 :
1068 : char sql[1024];
1069 91 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_schema_versions (hash, seq) "
1070 : "VALUES (%lld, COALESCE((SELECT MAX(seq) FROM cloudsync_schema_versions), 0) + 1) "
1071 : "ON CONFLICT(hash) DO UPDATE SET "
1072 : " seq = (SELECT COALESCE(MAX(seq), 0) + 1 FROM cloudsync_schema_versions);", (sqlite3_int64)h);
1073 91 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1074 91 : if (rc == SQLITE_OK && hash) *hash = h;
1075 91 : return rc;
1076 95 : }
1077 :
1078 48 : sqlite3_uint64 dbutils_schema_hash (sqlite3 *db) {
1079 : DEBUG_DBFUNCTION("dbutils_schema_version");
1080 :
1081 48 : return (sqlite3_uint64)dbutils_int_select(db, "SELECT hash FROM cloudsync_schema_versions ORDER BY seq DESC limit 1;");
1082 : }
1083 :
1084 3 : bool dbutils_check_schema_hash (sqlite3 *db, sqlite3_uint64 hash) {
1085 : DEBUG_DBFUNCTION("dbutils_check_schema_hash");
1086 :
1087 : // a change from the current version of the schema or from previous known schema can be applied
1088 : // a change from a newer schema version not yet applied to this peer cannot be applied
1089 : // so a schema hash is valid if it exists in the cloudsync_schema_versions table
1090 :
1091 : // the idea is to allow changes on stale peers and to be able to apply these changes on peers with newer schema,
1092 : // but it requires alter table operation on augmented tables only add new columns and never drop columns for backward compatibility
1093 : char sql[1024];
1094 3 : snprintf(sql, sizeof(sql), "SELECT 1 FROM cloudsync_schema_versions WHERE hash = (%lld)", hash);
1095 :
1096 3 : return (dbutils_int_select(db, sql) == 1);
1097 : }
1098 :
1099 :
1100 2 : int dbutils_settings_cleanup (sqlite3 *db) {
1101 2 : const char *sql = "DROP TABLE IF EXISTS cloudsync_settings; DROP TABLE IF EXISTS cloudsync_site_id; DROP TABLE IF EXISTS cloudsync_table_settings; DROP TABLE IF EXISTS cloudsync_schema_versions; ";
1102 2 : return sqlite3_exec(db, sql, NULL, NULL, NULL);
1103 : }
|
