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 5832 : 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 5832 : sqlite3_stmt *pstmt = NULL;
48 5832 : bool is_write = (result_count == 0);
49 : #ifdef CLOUDSYNC_UNITTEST
50 5832 : bool is_test = (result_count == 1 && expected_types[0] == SQLITE_NOMEM);
51 : #endif
52 5832 : int type = 0;
53 :
54 : // compile sql
55 5832 : int rc = sqlite3_prepare_v2(db, sql, -1, &pstmt, NULL);
56 5832 : if (rc != SQLITE_OK) goto dbutils_exec_finalize;
57 :
58 : // check bindings
59 7144 : for (int i=0; i<count; ++i) {
60 1313 : switch (types[i]) {
61 : case SQLITE_NULL:
62 1 : rc = sqlite3_bind_null(pstmt, i+1);
63 1 : break;
64 : case SQLITE_TEXT:
65 1073 : rc = sqlite3_bind_text(pstmt, i+1, values[i], lens[i], SQLITE_STATIC);
66 1073 : break;
67 : case SQLITE_BLOB:
68 91 : rc = sqlite3_bind_blob(pstmt, i+1, values[i], lens[i], SQLITE_STATIC);
69 91 : break;
70 : case SQLITE_INTEGER: {
71 147 : sqlite3_int64 value = strtoll(values[i], NULL, 0);
72 147 : rc = sqlite3_bind_int64(pstmt, i+1, value);
73 147 : } 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 1313 : if (rc != SQLITE_OK) goto dbutils_exec_finalize;
80 1313 : }
81 :
82 : // execute statement
83 5831 : rc = sqlite3_step(pstmt);
84 :
85 : // check return value based on pre-condition
86 5831 : if (rc != SQLITE_ROW) goto dbutils_exec_finalize;
87 5233 : if (is_write) goto dbutils_exec_finalize;
88 :
89 : // process result (if any)
90 10467 : for (int i=0; i<result_count; ++i) {
91 5235 : type = sqlite3_column_type(pstmt, i);
92 5235 : results[i].type = type;
93 :
94 5235 : if (type == SQLITE_NULL) {
95 36 : rc = SQLITE_OK;
96 36 : continue;
97 : }
98 :
99 : #ifdef CLOUDSYNC_UNITTEST
100 5199 : if (is_test) type = SQLITE_BLOB;
101 : #endif
102 5199 : 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 5198 : if (type == SQLITE_INTEGER) results[i].value.intValue = sqlite3_column_int64(pstmt, i);
111 3958 : else if (type == SQLITE_FLOAT) results[i].value.doubleValue = sqlite3_column_double(pstmt, i);
112 : else {
113 : // TEXT or BLOB
114 3958 : int len = sqlite3_column_bytes(pstmt, i);
115 3958 : results[i].len = len;
116 : #if CLOUDSYNC_UNITTEST
117 3958 : if (is_test) len = SQLITE_MAX_ALLOCATION_SIZE + 1;
118 : #endif
119 :
120 3958 : char *buffer = NULL;
121 3958 : if (type == SQLITE_BLOB) {
122 94 : const void *bvalue = sqlite3_column_blob(pstmt, i);
123 94 : if (bvalue) {
124 94 : buffer = (char *)cloudsync_memory_alloc(len);
125 94 : if (!buffer) {rc = SQLITE_NOMEM; goto dbutils_exec_finalize;}
126 94 : memcpy(buffer, bvalue, len);
127 94 : }
128 94 : } else {
129 3864 : const unsigned char *value = sqlite3_column_text(pstmt, i);
130 3864 : if (value) buffer = cloudsync_string_dup((const char *)value, false);
131 : }
132 3958 : results[i].value.stringValue = buffer;
133 : }
134 5198 : }
135 :
136 5232 : rc = SQLITE_OK;
137 : dbutils_exec_finalize:
138 5832 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
139 5832 : 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 5832 : if (pstmt) sqlite3_finalize(pstmt);
147 :
148 5832 : if (is_write) {
149 510 : DATABASE_RESULT result = {0};
150 510 : result.rc = rc;
151 510 : return result;
152 : }
153 :
154 5322 : results[0].rc = rc;
155 5322 : return results[0];
156 5832 : }
157 :
158 509 : int dbutils_write (sqlite3 *db, sqlite3_context *context, const char *sql, const char **values, int types[], int lens[], int count) {
159 509 : DATABASE_RESULT result = dbutils_exec(context, db, sql, values, types, lens, count, NULL, NULL, 0);
160 509 : 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 1324 : 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 1324 : DATABASE_RESULT results[1] = {0};
171 1324 : int expected_types[1] = {SQLITE_INTEGER};
172 1324 : dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
173 1324 : return (results[0].rc == SQLITE_OK) ? results[0].value.intValue : -1;
174 : }
175 :
176 3899 : char *dbutils_text_select (sqlite3 *db, const char *sql) {
177 3899 : DATABASE_RESULT results[1] = {0};
178 3899 : int expected_types[1] = {SQLITE_TEXT};
179 3899 : dbutils_exec(NULL, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
180 3899 : return results[0].value.stringValue;
181 : }
182 :
183 93 : char *dbutils_blob_select (sqlite3 *db, const char *sql, int *size, sqlite3_context *context, int *rc) {
184 93 : DATABASE_RESULT results[1] = {0};
185 93 : int expected_types[1] = {SQLITE_BLOB};
186 93 : dbutils_exec(context, db, sql, NULL, NULL, NULL, 0, results, expected_types, 1);
187 93 : *size = results[0].len;
188 93 : *rc = results[0].rc;
189 93 : 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 22714 : int dbutils_value_compare (sqlite3_value *lvalue, sqlite3_value *rvalue) {
215 22714 : if (lvalue == rvalue) return 0;
216 22714 : if (!lvalue) return -1;
217 22714 : if (!rvalue) return 1;
218 :
219 22714 : int l_type = (lvalue) ? sqlite3_value_type(lvalue) : SQLITE_NULL;
220 22714 : int r_type = sqlite3_value_type(rvalue);
221 :
222 : // early exit if types differ, null is less than all types
223 22714 : if (l_type != r_type) return (r_type - l_type);
224 :
225 : // at this point lvalue and rvalue are of the same type
226 22709 : switch (l_type) {
227 : case SQLITE_INTEGER: {
228 6938 : sqlite3_int64 l_int = sqlite3_value_int64(lvalue);
229 6938 : sqlite3_int64 r_int = sqlite3_value_int64(rvalue);
230 6938 : 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 15127 : const unsigned char *l_text = sqlite3_value_text(lvalue);
244 15127 : const unsigned char *r_text = sqlite3_value_text(rvalue);
245 15127 : return strcmp((const char *)l_text, (const char *)r_text);
246 : } break;
247 :
248 : case SQLITE_BLOB: {
249 7 : const void *l_blob = sqlite3_value_blob(lvalue);
250 7 : const void *r_blob = sqlite3_value_blob(rvalue);
251 7 : int l_size = sqlite3_value_bytes(lvalue);
252 7 : int r_size = sqlite3_value_bytes(rvalue);
253 7 : int cmp = memcmp(l_blob, r_blob, (l_size < r_size) ? l_size : r_size);
254 7 : return (cmp != 0) ? cmp : (l_size - r_size);
255 : } break;
256 : }
257 :
258 634 : return 0;
259 22714 : }
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 97 : int dbutils_debug_stmt (sqlite3 *db, bool print_result) {
301 97 : sqlite3_stmt *stmt = NULL;
302 97 : int counter = 0;
303 98 : while ((stmt = sqlite3_next_stmt(db, stmt))) {
304 1 : ++counter;
305 1 : if (print_result) printf("Unfinalized stmt statement: %p\n", stmt);
306 : }
307 97 : return counter;
308 : }
309 :
310 : // MARK: -
311 :
312 2495 : 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 2495 : const int DEFAULT_FLAGS = SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC;
316 2495 : int rc = sqlite3_create_function_v2(db, name, nargs, DEFAULT_FLAGS, ctx, ptr, NULL, NULL, ctx_free);
317 :
318 2495 : 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 2494 : return SQLITE_OK;
324 2495 : }
325 :
326 173 : 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 173 : const int DEFAULT_FLAGS = SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC;
330 173 : int rc = sqlite3_create_function_v2(db, name, nargs, DEFAULT_FLAGS, ctx, NULL, xstep, xfinal, ctx_free);
331 :
332 173 : 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 172 : return SQLITE_OK;
338 173 : }
339 :
340 1157 : bool dbutils_system_exists (sqlite3 *db, const char *name, const char *type) {
341 : DEBUG_DBFUNCTION("dbutils_system_exists %s: %s", type, name);
342 :
343 1157 : sqlite3_stmt *vm = NULL;
344 1157 : bool result = false;
345 :
346 : char sql[1024];
347 1157 : snprintf(sql, sizeof(sql), "SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type='%s' AND name=?1 COLLATE NOCASE);", type);
348 1157 : int rc = sqlite3_prepare_v2(db, sql, -1, &vm, NULL);
349 1157 : if (rc != SQLITE_OK) goto finalize;
350 :
351 1157 : rc = sqlite3_bind_text(vm, 1, name, -1, SQLITE_STATIC);
352 1157 : if (rc != SQLITE_OK) goto finalize;
353 :
354 1157 : rc = sqlite3_step(vm);
355 2314 : if (rc == SQLITE_ROW) {
356 1157 : result = (bool)sqlite3_column_int(vm, 0);
357 1157 : rc = SQLITE_OK;
358 1157 : }
359 :
360 : finalize:
361 1157 : 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 1157 : if (vm) sqlite3_finalize(vm);
363 1157 : return result;
364 : }
365 :
366 715 : bool dbutils_table_exists (sqlite3 *db, const char *name) {
367 715 : return dbutils_system_exists(db, name, "table");
368 : }
369 :
370 441 : bool dbutils_trigger_exists (sqlite3 *db, const char *name) {
371 441 : return dbutils_system_exists(db, name, "trigger");
372 : }
373 :
374 160 : 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 160 : size_t blen = sizeof(buffer);
379 :
380 : // sanity check table name
381 160 : 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 159 : const size_t maxlen = blen - 148;
389 159 : 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 158 : 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 156 : char *sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0;", name);
402 156 : sqlite3_int64 count = dbutils_int_select(db, sql);
403 156 : 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 155 : } 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 155 : 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 154 : if (!skip_int_pk_check) {
420 103 : 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 19 : sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=1 AND \"type\" LIKE '%%INT%%';", name);
425 19 : sqlite3_int64 count2 = dbutils_int_select(db, sql);
426 19 : 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 17 : if (count2 == -1) {
431 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
432 0 : return false;
433 : }
434 17 : }
435 101 : }
436 :
437 : // if user declared explicit primary key(s) then make sure they are all declared as NOT NULL
438 152 : if (count > 0) {
439 152 : sql = sqlite3_snprintf((int)blen, buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0 AND \"notnull\"=1;", name);
440 152 : sqlite3_int64 count2 = dbutils_int_select(db, sql);
441 152 : if (count2 == -1) {
442 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
443 0 : return false;
444 : }
445 152 : 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 151 : }
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 151 : 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 151 : sqlite3_int64 count3 = dbutils_int_select(db, sql);
455 151 : if (count3 == -1) {
456 0 : dbutils_context_result_error(context, "%s", sqlite3_errmsg(db));
457 0 : return false;
458 : }
459 151 : 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 150 : return true;
465 160 : }
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 147 : int dbutils_check_triggers (sqlite3 *db, const char *table, table_algo algo) {
501 : DEBUG_DBFUNCTION("dbutils_check_triggers %s", table);
502 :
503 147 : if (dbutils_settings_check_version(db, "0.8.25") <= 0) {
504 0 : dbutils_delete_triggers(db, table);
505 0 : }
506 :
507 147 : char *trigger_name = NULL;
508 147 : int rc = SQLITE_NOMEM;
509 :
510 : // common part
511 147 : char *trigger_when = cloudsync_memory_mprintf("FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0", table);
512 147 : if (!trigger_when) goto finalize;
513 :
514 : // INSERT TRIGGER
515 : // NEW.prikey1, NEW.prikey2...
516 147 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_insert_%s", table);
517 147 : if (!trigger_name) goto finalize;
518 :
519 147 : if (!dbutils_trigger_exists(db, trigger_name)) {
520 139 : rc = SQLITE_NOMEM;
521 139 : 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 139 : if (!sql) goto finalize;
523 :
524 139 : char *pkclause = dbutils_text_select(db, sql);
525 139 : char *pkvalues = (pkclause) ? pkclause : "NEW.rowid";
526 139 : cloudsync_memory_free(sql);
527 :
528 139 : 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 139 : if (pkclause) cloudsync_memory_free(pkclause);
530 139 : if (!sql) goto finalize;
531 :
532 139 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
533 : DEBUG_SQL("\n%s", sql);
534 139 : cloudsync_memory_free(sql);
535 139 : if (rc != SQLITE_OK) goto finalize;
536 139 : }
537 147 : cloudsync_memory_free(trigger_name);
538 147 : trigger_name = NULL;
539 147 : rc = SQLITE_NOMEM;
540 :
541 147 : if (algo != table_algo_crdt_gos) {
542 140 : 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 140 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_update_%s", table);
547 140 : if (!trigger_name) goto finalize;
548 :
549 140 : 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 132 : 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 132 : table, table);
556 132 : if (!pk_values_sql) goto finalize;
557 :
558 132 : char *pk_values_list = dbutils_text_select(db, pk_values_sql);
559 132 : cloudsync_memory_free(pk_values_sql);
560 :
561 : // Then get all regular columns in order
562 132 : 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 132 : table, table);
566 132 : if (!col_values_sql) goto finalize;
567 :
568 132 : char *col_values_list = dbutils_text_select(db, col_values_sql);
569 132 : cloudsync_memory_free(col_values_sql);
570 :
571 : // Build the complete VALUES query
572 : char *values_query;
573 132 : if (col_values_list && strlen(col_values_list) > 0) {
574 : // Table has both primary keys and regular columns
575 97 : 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 97 : pk_values_list, col_values_list);
579 97 : cloudsync_memory_free(col_values_list);
580 97 : } 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 132 : if (pk_values_list) cloudsync_memory_free(pk_values_list);
589 132 : if (!values_query) goto finalize;
590 :
591 : // Create the trigger with aggregate function
592 132 : 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 132 : trigger_name, table, trigger_when, values_query);
597 :
598 132 : cloudsync_memory_free(values_query);
599 132 : if (!sql) goto finalize;
600 :
601 132 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
602 : DEBUG_SQL("\n%s", sql);
603 132 : cloudsync_memory_free(sql);
604 132 : if (rc != SQLITE_OK) goto finalize;
605 132 : }
606 140 : cloudsync_memory_free(trigger_name);
607 140 : trigger_name = NULL;
608 140 : } 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 147 : if (algo != table_algo_crdt_gos) {
633 140 : trigger_name = cloudsync_memory_mprintf("cloudsync_after_delete_%s", table);
634 140 : if (!trigger_name) goto finalize;
635 :
636 140 : if (!dbutils_trigger_exists(db, trigger_name)) {
637 132 : 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 132 : if (!sql) goto finalize;
639 :
640 132 : char *pkclause = dbutils_text_select(db, sql);
641 132 : char *pkvalues = (pkclause) ? pkclause : "OLD.rowid";
642 132 : cloudsync_memory_free(sql);
643 :
644 132 : 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 132 : if (pkclause) cloudsync_memory_free(pkclause);
646 132 : if (!sql) goto finalize;
647 :
648 132 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
649 : DEBUG_SQL("\n%s", sql);
650 132 : cloudsync_memory_free(sql);
651 132 : if (rc != SQLITE_OK) goto finalize;
652 132 : }
653 :
654 140 : cloudsync_memory_free(trigger_name);
655 140 : trigger_name = NULL;
656 140 : } 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 147 : rc = SQLITE_OK;
676 :
677 : finalize:
678 147 : if (trigger_name) cloudsync_memory_free(trigger_name);
679 147 : if (trigger_when) cloudsync_memory_free(trigger_when);
680 147 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_create_triggers error %s (%d)", sqlite3_errmsg(db), rc);
681 147 : return rc;
682 : }
683 :
684 145 : 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 145 : 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 145 : if (!sql) return SQLITE_NOMEM;
690 :
691 145 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
692 : DEBUG_SQL("\n%s", sql);
693 145 : cloudsync_memory_free(sql);
694 :
695 145 : return rc;
696 145 : }
697 :
698 :
699 87 : sqlite3_int64 dbutils_schema_version (sqlite3 *db) {
700 : DEBUG_DBFUNCTION("dbutils_schema_version");
701 :
702 87 : return dbutils_int_select(db, "PRAGMA schema_version;");
703 : }
704 :
705 1141 : bool dbutils_is_star_table (const char *table_name) {
706 1141 : return (table_name && (strlen(table_name) == 1) && table_name[0] == '*');
707 : }
708 :
709 : // MARK: - Settings -
710 :
711 452 : int binary_comparison (int x, int y) {
712 452 : if (x == y) return 0;
713 151 : if (x > y) return 1;
714 2 : return -1;
715 452 : }
716 :
717 419 : 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 419 : if (!buffer || blen == 0) blen = 0;
722 419 : size_t size = 0;
723 :
724 419 : sqlite3_stmt *vm = NULL;
725 419 : char *sql = "SELECT value FROM cloudsync_settings WHERE key=?1;";
726 419 : int rc = sqlite3_prepare(db, sql, -1, &vm, NULL);
727 419 : if (rc != SQLITE_OK) goto finalize_get_value;
728 :
729 419 : rc = sqlite3_bind_text(vm, 1, key, -1, SQLITE_STATIC);
730 419 : if (rc != SQLITE_OK) goto finalize_get_value;
731 :
732 419 : rc = sqlite3_step(vm);
733 419 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
734 262 : else if (rc != SQLITE_ROW) goto finalize_get_value;
735 :
736 : // SQLITE_ROW case
737 419 : if (sqlite3_column_type(vm, 0) == SQLITE_NULL) {
738 157 : rc = SQLITE_OK;
739 157 : goto finalize_get_value;
740 : }
741 :
742 262 : const unsigned char *value = sqlite3_column_text(vm, 0);
743 : #if CLOUDSYNC_UNITTEST
744 262 : 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 262 : 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 261 : memcpy(buffer, value, size+1);
757 261 : rc = SQLITE_OK;
758 :
759 : finalize_get_value:
760 : #if CLOUDSYNC_UNITTEST
761 419 : if ((rc == SQLITE_NOMEM) && (size == SQLITE_MAX_ALLOCATION_SIZE + 1)) rc = SQLITE_OK;
762 : #endif
763 419 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_settings_get_value error %s", sqlite3_errmsg(db));
764 419 : if (vm) sqlite3_finalize(vm);
765 :
766 419 : return buffer;
767 : }
768 :
769 232 : 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 232 : int rc = SQLITE_OK;
773 232 : if (db == NULL) db = sqlite3_context_db_handle(context);
774 :
775 232 : if (key && value) {
776 231 : char *sql = "REPLACE INTO cloudsync_settings (key, value) VALUES (?1, ?2);";
777 231 : const char *values[] = {key, value};
778 231 : int types[] = {SQLITE_TEXT, SQLITE_TEXT};
779 231 : int lens[] = {-1, -1};
780 231 : rc = dbutils_write(db, context, sql, values, types, lens, 2);
781 231 : }
782 :
783 232 : 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 232 : cloudsync_context *data = (context) ? (cloudsync_context *)sqlite3_user_data(context) : NULL;
792 232 : if (rc == SQLITE_OK && data) cloudsync_sync_key(data, key, value);
793 232 : return rc;
794 : }
795 :
796 266 : int dbutils_settings_get_int_value (sqlite3 *db, const char *key) {
797 : DEBUG_SETTINGS("dbutils_settings_get_int_value key: %s", key);
798 266 : char buffer[256] = {0};
799 266 : if (dbutils_settings_get_value(db, key, buffer, sizeof(buffer)) == NULL) return -1;
800 :
801 266 : return (int)strtol(buffer, NULL, 0);
802 266 : }
803 :
804 150 : int dbutils_settings_check_version (sqlite3 *db, const char *version) {
805 : DEBUG_SETTINGS("dbutils_settings_check_version");
806 : char buffer[256];
807 150 : 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 150 : int count1 = sscanf(buffer, "%d.%d.%d", &major1, &minor1, &patch1);
812 150 : int count2 = sscanf((version == NULL ? CLOUDSYNC_VERSION : version), "%d.%d.%d", &major2, &minor2, &patch2);
813 :
814 150 : if (count1 != 3 || count2 != 3) return -666;
815 :
816 150 : int res = 0;
817 150 : if ((res = binary_comparison(major1, major2)) == 0) {
818 150 : if ((res = binary_comparison(minor1, minor2)) == 0) {
819 149 : return binary_comparison(patch1, patch2);
820 : }
821 1 : }
822 :
823 : DEBUG_SETTINGS(" %s %s (%d)", buffer, CLOUDSYNC_VERSION, res);
824 1 : return res;
825 150 : }
826 :
827 171 : 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 171 : if (!buffer || blen == 0) blen = 0;
832 171 : size_t size = 0;
833 :
834 171 : sqlite3_stmt *vm = NULL;
835 171 : char *sql = "SELECT value FROM cloudsync_table_settings WHERE (tbl_name=?1 AND col_name=?2 AND key=?3);";
836 171 : int rc = sqlite3_prepare(db, sql, -1, &vm, NULL);
837 171 : if (rc != SQLITE_OK) goto finalize_get_value;
838 :
839 171 : rc = sqlite3_bind_text(vm, 1, table, -1, SQLITE_STATIC);
840 171 : if (rc != SQLITE_OK) goto finalize_get_value;
841 :
842 171 : rc = sqlite3_bind_text(vm, 2, (column) ? column : "*", -1, SQLITE_STATIC);
843 171 : if (rc != SQLITE_OK) goto finalize_get_value;
844 :
845 171 : rc = sqlite3_bind_text(vm, 3, key, -1, SQLITE_STATIC);
846 171 : if (rc != SQLITE_OK) goto finalize_get_value;
847 :
848 171 : rc = sqlite3_step(vm);
849 171 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
850 54 : else if (rc != SQLITE_ROW) goto finalize_get_value;
851 :
852 : // SQLITE_ROW case
853 171 : if (sqlite3_column_type(vm, 0) == SQLITE_NULL) {
854 117 : rc = SQLITE_OK;
855 117 : 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 171 : if ((rc == SQLITE_NOMEM) && (size == SQLITE_MAX_ALLOCATION_SIZE + 1)) rc = SQLITE_OK;
878 : #endif
879 171 : if (rc != SQLITE_OK) {
880 0 : DEBUG_ALWAYS("cloudsync_table_settings error %s", sqlite3_errmsg(db));
881 0 : }
882 171 : if (vm) sqlite3_finalize(vm);
883 :
884 171 : return buffer;
885 : }
886 :
887 128 : 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 128 : int rc = SQLITE_OK;
891 128 : if (db == NULL) db = sqlite3_context_db_handle(context);
892 :
893 : // sanity check tbl_name
894 128 : 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 127 : if (column == NULL) column = "*";
901 :
902 : // remove all table_name entries
903 127 : 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 121 : if (key && value) {
913 120 : char *sql = "REPLACE INTO cloudsync_table_settings (tbl_name, col_name, key, value) VALUES (?1, ?2, ?3, ?4);";
914 120 : const char *values[] = {table, column, key, value};
915 120 : int types[] = {SQLITE_TEXT, SQLITE_TEXT, SQLITE_TEXT, SQLITE_TEXT};
916 120 : int lens[] = {-1, -1, -1, -1};
917 120 : rc = dbutils_write(db, context, sql, values, types, lens, 4);
918 120 : }
919 :
920 121 : 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 121 : return rc;
932 128 : }
933 :
934 377 : sqlite3_int64 dbutils_table_settings_count_tables (sqlite3 *db) {
935 : DEBUG_SETTINGS("dbutils_table_settings_count_tables");
936 377 : return dbutils_int_select(db, "SELECT count(*) FROM cloudsync_table_settings WHERE key='algo';");
937 : }
938 :
939 168 : 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 168 : char *value = dbutils_table_settings_get_value(db, table_name, "*", "algo", buffer, sizeof(buffer));
944 168 : return (value) ? crdt_algo_from_name(value) : table_algo_none;
945 : }
946 :
947 178 : int dbutils_settings_load_callback (void *xdata, int ncols, char **values, char **names) {
948 178 : cloudsync_context *data = (cloudsync_context *)xdata;
949 :
950 356 : for (int i=0; i<ncols; i+=2) {
951 178 : const char *key = values[i];
952 178 : const char *value = values[i+1];
953 178 : cloudsync_sync_key(data, key, value);
954 : DEBUG_SETTINGS("key: %s value: %s", key, value);
955 178 : }
956 :
957 178 : return 0;
958 : }
959 :
960 : bool table_add_to_context (sqlite3 *db, cloudsync_context *data, table_algo algo, const char *table_name);
961 :
962 2 : int dbutils_settings_table_load_callback (void *xdata, int ncols, char **values, char **names) {
963 2 : dbutils_settings_table_context *context = (dbutils_settings_table_context *)xdata;
964 2 : cloudsync_context *data = context->data;
965 2 : sqlite3 *db = context->db;
966 :
967 4 : for (int i=0; i<ncols; i+=4) {
968 2 : const char *table_name = values[i];
969 : // const char *col_name = values[i+1];
970 2 : const char *key = values[i+2];
971 2 : const char *value = values[i+3];
972 2 : if (strcmp(key, "algo")!=0) continue;
973 :
974 2 : if (dbutils_check_triggers(db, table_name, crdt_algo_from_name(value)) != SQLITE_OK) return SQLITE_MISUSE;
975 2 : 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 2 : }
979 :
980 2 : return 0;
981 2 : }
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 89 : int dbutils_settings_load (sqlite3 *db, cloudsync_context *data) {
990 : DEBUG_SETTINGS("dbutils_settings_load %p", data);
991 :
992 : // load global settings
993 89 : const char *sql = "SELECT key, value FROM cloudsync_settings;";
994 89 : int rc = sqlite3_exec(db, sql, dbutils_settings_load_callback, data, NULL);
995 89 : if (rc != SQLITE_OK) DEBUG_ALWAYS("cloudsync_load_settings error: %s", sqlite3_errmsg(db));
996 :
997 : // load table-specific settings
998 89 : dbutils_settings_table_context xdata = {.db = db, .data = data};
999 89 : sql = "SELECT lower(tbl_name), lower(col_name), key, value FROM cloudsync_table_settings ORDER BY tbl_name;";
1000 89 : rc = sqlite3_exec(db, sql, dbutils_settings_table_load_callback, &xdata, NULL);
1001 89 : if (rc != SQLITE_OK) DEBUG_ALWAYS("cloudsync_load_settings error: %s", sqlite3_errmsg(db));
1002 :
1003 89 : return SQLITE_OK;
1004 : }
1005 :
1006 89 : int dbutils_settings_init (sqlite3 *db, void *cloudsync_data, sqlite3_context *context) {
1007 : DEBUG_SETTINGS("dbutils_settings_init %p", context);
1008 :
1009 89 : cloudsync_context *data = (cloudsync_context *)cloudsync_data;
1010 89 : if (!data) data = (cloudsync_context *)sqlite3_user_data(context);
1011 :
1012 : // check if cloudsync_settings table exists
1013 89 : bool settings_exists = dbutils_table_exists(db, CLOUDSYNC_SETTINGS_NAME);
1014 89 : if (settings_exists == false) {
1015 : DEBUG_SETTINGS("cloudsync_settings does not exist (creating a new one)");
1016 :
1017 : char sql[1024];
1018 87 : int rc = SQLITE_OK;
1019 :
1020 : // create table and fill-in initial data
1021 87 : snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS cloudsync_settings (key TEXT PRIMARY KEY NOT NULL COLLATE NOCASE, value TEXT);");
1022 87 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1023 87 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1024 :
1025 : // library version
1026 87 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_settings (key, value) VALUES ('%s', '%s');", CLOUDSYNC_KEY_LIBVERSION, CLOUDSYNC_VERSION);
1027 87 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1028 87 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1029 :
1030 : // schema version
1031 87 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_settings (key, value) VALUES ('%s', %lld);", CLOUDSYNC_KEY_SCHEMAVERSION, (long long)dbutils_schema_version(db));
1032 87 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1033 87 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1034 87 : }
1035 :
1036 89 : 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 87 : char *sql = "CREATE TABLE IF NOT EXISTS cloudsync_site_id (site_id BLOB UNIQUE NOT NULL);";
1043 87 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1044 87 : 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 87 : 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 87 : sql = "INSERT INTO cloudsync_site_id (rowid, site_id) VALUES (?, ?);";
1052 87 : const char *values[] = {"0", (const char *)&site_id};
1053 87 : int types[] = {SQLITE_INTEGER, SQLITE_BLOB};
1054 87 : int lens[] = {-1, UUID_LEN};
1055 87 : rc = dbutils_write(db, context, sql, values, types, lens, 2);
1056 87 : if (rc != SQLITE_OK) return rc;
1057 87 : }
1058 :
1059 : // check if cloudsync_table_settings table exists
1060 89 : 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 87 : 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 87 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1065 87 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1066 87 : }
1067 :
1068 : // check if cloudsync_settings table exists
1069 89 : bool schema_versions_exists = dbutils_table_exists(db, CLOUDSYNC_SCHEMA_VERSIONS_NAME);
1070 89 : if (schema_versions_exists == false) {
1071 : DEBUG_SETTINGS("cloudsync_schema_versions does not exist (creating a new one)");
1072 :
1073 87 : int rc = SQLITE_OK;
1074 :
1075 : // create table
1076 87 : char *sql = "CREATE TABLE IF NOT EXISTS cloudsync_schema_versions (hash INTEGER PRIMARY KEY, seq INTEGER NOT NULL)";
1077 87 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1078 87 : if (rc != SQLITE_OK) {if (context) sqlite3_result_error(context, sqlite3_errmsg(db), -1); return rc;}
1079 87 : }
1080 :
1081 : // cloudsync_settings table exists so load it
1082 89 : 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 89 : return SQLITE_OK;
1093 89 : }
1094 :
1095 142 : int dbutils_update_schema_hash(sqlite3 *db, uint64_t *hash) {
1096 142 : 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 142 : char *schema = dbutils_text_select(db, schemasql);
1100 142 : if (!schema) return SQLITE_ERROR;
1101 :
1102 142 : sqlite3_uint64 h = fnv1a_hash(schema, strlen(schema));
1103 142 : cloudsync_memory_free(schema);
1104 142 : if (hash && *hash == h) return SQLITE_CONSTRAINT;
1105 :
1106 : char sql[1024];
1107 138 : 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 138 : int rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1112 138 : if (rc == SQLITE_OK && hash) *hash = h;
1113 138 : return rc;
1114 142 : }
1115 :
1116 89 : sqlite3_uint64 dbutils_schema_hash (sqlite3 *db) {
1117 : DEBUG_DBFUNCTION("dbutils_schema_version");
1118 :
1119 89 : 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 : }
|
