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