Line data Source code
1 : //
2 : // database_sqlite.c
3 : // cloudsync
4 : //
5 : // Created by Marco Bambini on 03/12/25.
6 : //
7 :
8 : #include "../cloudsync.h"
9 : #include "../database.h"
10 : #include "../dbutils.h"
11 : #include "../utils.h"
12 : #include "../sql.h"
13 :
14 : #include <inttypes.h>
15 : #include <string.h>
16 : #include <stdlib.h>
17 :
18 : #ifndef SQLITE_CORE
19 : #include "sqlite3ext.h"
20 : #else
21 : #include "sqlite3.h"
22 : #endif
23 :
24 : #ifndef SQLITE_CORE
25 : SQLITE_EXTENSION_INIT3
26 : #endif
27 :
28 : // MARK: - SQL -
29 :
30 0 : char *sql_build_drop_table (const char *table_name, char *buffer, int bsize, bool is_meta) {
31 0 : char *sql = NULL;
32 :
33 0 : if (is_meta) {
34 0 : sql = sqlite3_snprintf(bsize, buffer, "DROP TABLE IF EXISTS \"%w_cloudsync\";", table_name);
35 0 : } else {
36 0 : sql = sqlite3_snprintf(bsize, buffer, "DROP TABLE IF EXISTS \"%w\";", table_name);
37 : }
38 :
39 0 : return sql;
40 : }
41 :
42 10017 : char *sql_escape_identifier (const char *name, char *buffer, size_t bsize) {
43 10017 : return sqlite3_snprintf((int)bsize, buffer, "%q", name);
44 : }
45 :
46 784 : char *sql_build_select_nonpk_by_pk (cloudsync_context *data, const char *table_name, const char *schema) {
47 784 : UNUSED_PARAMETER(schema);
48 784 : char *sql = NULL;
49 :
50 : /*
51 : This SQL statement dynamically generates a SELECT query for a specified table.
52 : It uses Common Table Expressions (CTEs) to construct the column names and
53 : primary key conditions based on the table schema, which is obtained through
54 : the `pragma_table_info` function.
55 :
56 : 1. `col_names` CTE:
57 : - Retrieves a comma-separated list of non-primary key column names from
58 : the specified table's schema.
59 :
60 : 2. `pk_where` CTE:
61 : - Retrieves a condition string representing the primary key columns in the
62 : format: "column1=? AND column2=? AND ...", used to create the WHERE clause
63 : for selecting rows based on primary key values.
64 :
65 : 3. Final SELECT:
66 : - Constructs the complete SELECT statement as a string, combining:
67 : - Column names from `col_names`.
68 : - The target table name.
69 : - The WHERE clause conditions from `pk_where`.
70 :
71 : The resulting query can be used to select rows from the table based on primary
72 : key values, and can be executed within the application to retrieve data dynamically.
73 : */
74 :
75 : // Unfortunately in SQLite column names (or table names) cannot be bound parameters in a SELECT statement
76 : // otherwise we should have used something like SELECT 'SELECT ? FROM %w WHERE rowid=?';
77 : char buffer[1024];
78 784 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
79 :
80 : #if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES
81 : if (table->rowid_only) {
82 : sql = memory_mprintf(SQL_BUILD_SELECT_NONPK_COLS_BY_ROWID, table->name, table->name);
83 : goto process_process;
84 : }
85 : #endif
86 :
87 784 : sql = cloudsync_memory_mprintf(SQL_BUILD_SELECT_NONPK_COLS_BY_PK, table_name, table_name, singlequote_escaped_table_name);
88 :
89 : #if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES
90 : process_process:
91 : #endif
92 784 : if (!sql) return NULL;
93 :
94 784 : char *query = NULL;
95 784 : int rc = database_select_text(data, sql, &query);
96 784 : cloudsync_memory_free(sql);
97 :
98 784 : return (rc == DBRES_OK) ? query : NULL;
99 784 : }
100 :
101 824 : char *sql_build_delete_by_pk (cloudsync_context *data, const char *table_name, const char *schema) {
102 824 : UNUSED_PARAMETER(schema);
103 : char buffer[1024];
104 824 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
105 824 : char *sql = cloudsync_memory_mprintf(SQL_BUILD_DELETE_ROW_BY_PK, table_name, singlequote_escaped_table_name);
106 824 : if (!sql) return NULL;
107 :
108 824 : char *query = NULL;
109 824 : int rc = database_select_text(data, sql, &query);
110 824 : cloudsync_memory_free(sql);
111 :
112 824 : return (rc == DBRES_OK) ? query : NULL;
113 824 : }
114 :
115 824 : char *sql_build_insert_pk_ignore (cloudsync_context *data, const char *table_name, const char *schema) {
116 824 : UNUSED_PARAMETER(schema);
117 : char buffer[1024];
118 824 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
119 824 : char *sql = cloudsync_memory_mprintf(SQL_BUILD_INSERT_PK_IGNORE, table_name, table_name, singlequote_escaped_table_name);
120 824 : if (!sql) return NULL;
121 :
122 824 : char *query = NULL;
123 824 : int rc = database_select_text(data, sql, &query);
124 824 : cloudsync_memory_free(sql);
125 :
126 824 : return (rc == DBRES_OK) ? query : NULL;
127 824 : }
128 :
129 1894 : char *sql_build_upsert_pk_and_col (cloudsync_context *data, const char *table_name, const char *colname, const char *schema) {
130 1894 : UNUSED_PARAMETER(schema);
131 : char buffer[1024];
132 : char buffer2[1024];
133 1894 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
134 1894 : char *singlequote_escaped_col_name = sql_escape_identifier(colname, buffer2, sizeof(buffer2));
135 1894 : char *sql = cloudsync_memory_mprintf(
136 1894 : SQL_BUILD_UPSERT_PK_AND_COL,
137 1894 : table_name,
138 1894 : table_name,
139 1894 : singlequote_escaped_table_name,
140 1894 : singlequote_escaped_col_name,
141 1894 : singlequote_escaped_col_name
142 : );
143 1894 : if (!sql) return NULL;
144 :
145 1894 : char *query = NULL;
146 1894 : int rc = database_select_text(data, sql, &query);
147 1894 : cloudsync_memory_free(sql);
148 :
149 1894 : return (rc == DBRES_OK) ? query : NULL;
150 1894 : }
151 :
152 985 : char *sql_build_upsert_pk_and_multi_cols (cloudsync_context *data, const char *table_name, const char **colnames, int ncolnames, const char *schema) {
153 985 : UNUSED_PARAMETER(schema);
154 985 : if (ncolnames <= 0 || !colnames) return NULL;
155 :
156 : // Get PK column names via pragma_table_info (same approach as database_pk_names)
157 985 : char **pk_names = NULL;
158 985 : int npks = 0;
159 985 : int rc = database_pk_names(data, table_name, &pk_names, &npks);
160 985 : if (rc != DBRES_OK || npks <= 0 || !pk_names) return NULL;
161 :
162 : // Build column list: "pk1","pk2","col_a","col_b"
163 985 : char *col_list = cloudsync_memory_mprintf("\"%w\"", pk_names[0]);
164 985 : if (!col_list) goto fail;
165 1378 : for (int i = 1; i < npks; i++) {
166 393 : char *prev = col_list;
167 393 : col_list = cloudsync_memory_mprintf("%s,\"%w\"", prev, pk_names[i]);
168 393 : cloudsync_memory_free(prev);
169 393 : if (!col_list) goto fail;
170 393 : }
171 2461 : for (int i = 0; i < ncolnames; i++) {
172 1476 : char *prev = col_list;
173 1476 : col_list = cloudsync_memory_mprintf("%s,\"%w\"", prev, colnames[i]);
174 1476 : cloudsync_memory_free(prev);
175 1476 : if (!col_list) goto fail;
176 1476 : }
177 :
178 : // Build bind list: ?,?,?,?
179 985 : int total = npks + ncolnames;
180 985 : char *binds = (char *)cloudsync_memory_alloc(total * 2);
181 985 : if (!binds) { cloudsync_memory_free(col_list); goto fail; }
182 985 : int pos = 0;
183 3839 : for (int i = 0; i < total; i++) {
184 2854 : if (i > 0) binds[pos++] = ',';
185 2854 : binds[pos++] = '?';
186 2854 : }
187 985 : binds[pos] = '\0';
188 :
189 : // Build excluded set: "col_a"=EXCLUDED."col_a","col_b"=EXCLUDED."col_b"
190 985 : char *excl = cloudsync_memory_mprintf("\"%w\"=EXCLUDED.\"%w\"", colnames[0], colnames[0]);
191 985 : if (!excl) { cloudsync_memory_free(col_list); cloudsync_memory_free(binds); goto fail; }
192 1476 : for (int i = 1; i < ncolnames; i++) {
193 491 : char *prev = excl;
194 491 : excl = cloudsync_memory_mprintf("%s,\"%w\"=EXCLUDED.\"%w\"", prev, colnames[i], colnames[i]);
195 491 : cloudsync_memory_free(prev);
196 491 : if (!excl) { cloudsync_memory_free(col_list); cloudsync_memory_free(binds); goto fail; }
197 491 : }
198 :
199 : // Assemble final SQL
200 985 : char *sql = cloudsync_memory_mprintf(
201 : "INSERT INTO \"%w\" (%s) VALUES (%s) ON CONFLICT DO UPDATE SET %s;",
202 985 : table_name, col_list, binds, excl
203 : );
204 :
205 985 : cloudsync_memory_free(col_list);
206 985 : cloudsync_memory_free(binds);
207 985 : cloudsync_memory_free(excl);
208 2363 : for (int i = 0; i < npks; i++) cloudsync_memory_free(pk_names[i]);
209 985 : cloudsync_memory_free(pk_names);
210 985 : return sql;
211 :
212 : fail:
213 0 : if (pk_names) {
214 0 : for (int i = 0; i < npks; i++) cloudsync_memory_free(pk_names[i]);
215 0 : cloudsync_memory_free(pk_names);
216 0 : }
217 0 : return NULL;
218 985 : }
219 :
220 705 : char *sql_build_update_pk_and_multi_cols (cloudsync_context *data, const char *table_name, const char **colnames, int ncolnames, const char *schema) {
221 705 : UNUSED_PARAMETER(schema);
222 705 : if (ncolnames <= 0 || !colnames) return NULL;
223 :
224 : // Get PK column names
225 705 : char **pk_names = NULL;
226 705 : int npks = 0;
227 705 : int rc = database_pk_names(data, table_name, &pk_names, &npks);
228 705 : if (rc != DBRES_OK || npks <= 0 || !pk_names) return NULL;
229 :
230 : // Build SET clause: "col_a"=?npks+1,"col_b"=?npks+2
231 : // Uses numbered parameters to match merge_flush_pending bind order:
232 : // positions 1..npks are PKs, npks+1..npks+ncolnames are column values.
233 705 : char *set_clause = cloudsync_memory_mprintf("\"%w\"=?%d", colnames[0], npks + 1);
234 705 : if (!set_clause) goto fail;
235 1037 : for (int i = 1; i < ncolnames; i++) {
236 332 : char *prev = set_clause;
237 332 : set_clause = cloudsync_memory_mprintf("%s,\"%w\"=?%d", prev, colnames[i], npks + 1 + i);
238 332 : cloudsync_memory_free(prev);
239 332 : if (!set_clause) goto fail;
240 332 : }
241 :
242 : // Build WHERE clause: "pk1"=?1 AND "pk2"=?2
243 705 : char *where_clause = cloudsync_memory_mprintf("\"%w\"=?%d", pk_names[0], 1);
244 705 : if (!where_clause) { cloudsync_memory_free(set_clause); goto fail; }
245 1120 : for (int i = 1; i < npks; i++) {
246 415 : char *prev = where_clause;
247 415 : where_clause = cloudsync_memory_mprintf("%s AND \"%w\"=?%d", prev, pk_names[i], 1 + i);
248 415 : cloudsync_memory_free(prev);
249 415 : if (!where_clause) { cloudsync_memory_free(set_clause); goto fail; }
250 415 : }
251 :
252 : // Assemble: UPDATE "table" SET ... WHERE ...
253 705 : char *sql = cloudsync_memory_mprintf(
254 : "UPDATE \"%w\" SET %s WHERE %s;",
255 705 : table_name, set_clause, where_clause
256 : );
257 :
258 705 : cloudsync_memory_free(set_clause);
259 705 : cloudsync_memory_free(where_clause);
260 1825 : for (int i = 0; i < npks; i++) cloudsync_memory_free(pk_names[i]);
261 705 : cloudsync_memory_free(pk_names);
262 705 : return sql;
263 :
264 : fail:
265 0 : if (pk_names) {
266 0 : for (int i = 0; i < npks; i++) cloudsync_memory_free(pk_names[i]);
267 0 : cloudsync_memory_free(pk_names);
268 0 : }
269 0 : return NULL;
270 705 : }
271 :
272 1893 : char *sql_build_select_cols_by_pk (cloudsync_context *data, const char *table_name, const char *colname, const char *schema) {
273 1893 : UNUSED_PARAMETER(schema);
274 1893 : char *colnamequote = "\"";
275 : char buffer[1024];
276 : char buffer2[1024];
277 1893 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
278 1893 : char *singlequote_escaped_col_name = sql_escape_identifier(colname, buffer2, sizeof(buffer2));
279 1893 : char *sql = cloudsync_memory_mprintf(
280 1893 : SQL_BUILD_SELECT_COLS_BY_PK_FMT,
281 1893 : table_name,
282 1893 : colnamequote,
283 1893 : singlequote_escaped_col_name,
284 1893 : colnamequote,
285 1893 : singlequote_escaped_table_name
286 : );
287 1893 : if (!sql) return NULL;
288 :
289 1893 : char *query = NULL;
290 1893 : int rc = database_select_text(data, sql, &query);
291 1893 : cloudsync_memory_free(sql);
292 :
293 1893 : return (rc == DBRES_OK) ? query : NULL;
294 1893 : }
295 :
296 824 : char *sql_build_rekey_pk_and_reset_version_except_col (cloudsync_context *data, const char *table_name, const char *except_col) {
297 824 : UNUSED_PARAMETER(data);
298 :
299 824 : char *meta_ref = database_build_meta_ref(NULL, table_name);
300 824 : if (!meta_ref) return NULL;
301 :
302 824 : char *result = cloudsync_memory_mprintf(SQL_CLOUDSYNC_REKEY_PK_AND_RESET_VERSION_EXCEPT_COL, meta_ref, except_col);
303 824 : cloudsync_memory_free(meta_ref);
304 824 : return result;
305 824 : }
306 :
307 824 : char *database_table_schema (const char *table_name) {
308 824 : return NULL;
309 : }
310 :
311 1648 : char *database_build_meta_ref (const char *schema, const char *table_name) {
312 : // schema unused in SQLite
313 1648 : return cloudsync_memory_mprintf("%s_cloudsync", table_name);
314 : }
315 :
316 824 : char *database_build_base_ref (const char *schema, const char *table_name) {
317 : // schema unused in SQLite
318 824 : return cloudsync_string_dup(table_name);
319 : }
320 :
321 327 : char *database_build_blocks_ref (const char *schema, const char *table_name) {
322 : // schema unused in SQLite
323 327 : return cloudsync_memory_mprintf("%s_cloudsync_blocks", table_name);
324 : }
325 :
326 : // SQLite version: schema parameter unused (SQLite has no schemas).
327 11 : char *sql_build_delete_cols_not_in_schema_query (const char *schema, const char *table_name, const char *meta_ref, const char *pkcol) {
328 11 : UNUSED_PARAMETER(schema);
329 11 : return cloudsync_memory_mprintf(
330 : "DELETE FROM \"%w\" WHERE col_name NOT IN ("
331 : "SELECT name FROM pragma_table_info('%q') "
332 : "UNION SELECT '%s'"
333 : ");",
334 11 : meta_ref, table_name, pkcol
335 : );
336 : }
337 :
338 846 : char *sql_build_pk_collist_query (const char *schema, const char *table_name) {
339 846 : UNUSED_PARAMETER(schema);
340 846 : return cloudsync_memory_mprintf(
341 : "SELECT group_concat('\"' || format('%%w', name) || '\"', ',') "
342 : "FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;",
343 846 : table_name
344 : );
345 : }
346 :
347 0 : char *sql_build_pk_decode_selectlist_query (const char *schema, const char *table_name) {
348 0 : UNUSED_PARAMETER(schema);
349 0 : return cloudsync_memory_mprintf(
350 : "SELECT group_concat("
351 : "'cloudsync_pk_decode(pk, ' || pk || ') AS ' || '\"' || format('%%w', name) || '\"', ','"
352 : ") "
353 : "FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;",
354 0 : table_name
355 : );
356 : }
357 :
358 11 : char *sql_build_pk_qualified_collist_query (const char *schema, const char *table_name) {
359 11 : UNUSED_PARAMETER(schema);
360 :
361 : char buffer[1024];
362 11 : char *singlequote_escaped_table_name = sql_escape_identifier(table_name, buffer, sizeof(buffer));
363 11 : if (!singlequote_escaped_table_name) return NULL;
364 :
365 11 : return cloudsync_memory_mprintf(
366 : "SELECT group_concat('\"%w\".\"' || format('%%w', name) || '\"', ',') "
367 11 : "FROM pragma_table_info('%s') WHERE pk>0 ORDER BY pk;", singlequote_escaped_table_name, singlequote_escaped_table_name
368 : );
369 11 : }
370 :
371 846 : char *sql_build_insert_missing_pks_query(const char *schema, const char *table_name,
372 : const char *pkvalues_identifiers,
373 : const char *base_ref, const char *meta_ref,
374 : const char *filter) {
375 846 : UNUSED_PARAMETER(schema);
376 :
377 : // SQLite: Use NOT EXISTS with cloudsync_pk_encode (same approach as PostgreSQL).
378 : // This avoids needing pk_decode select list which requires executing a query.
379 846 : if (filter) {
380 20 : return cloudsync_memory_mprintf(
381 : "SELECT cloudsync_insert('%q', %s) "
382 : "FROM \"%w\" "
383 : "WHERE (%s) AND NOT EXISTS ("
384 : " SELECT 1 FROM \"%w\" WHERE pk = cloudsync_pk_encode(%s)"
385 : ");",
386 20 : table_name, pkvalues_identifiers, base_ref, filter, meta_ref, pkvalues_identifiers
387 : );
388 : }
389 826 : return cloudsync_memory_mprintf(
390 : "SELECT cloudsync_insert('%q', %s) "
391 : "FROM \"%w\" "
392 : "WHERE NOT EXISTS ("
393 : " SELECT 1 FROM \"%w\" WHERE pk = cloudsync_pk_encode(%s)"
394 : ");",
395 826 : table_name, pkvalues_identifiers, base_ref, meta_ref, pkvalues_identifiers
396 : );
397 846 : }
398 :
399 : // MARK: - PRIVATE -
400 :
401 24643 : static int database_select1_value (cloudsync_context *data, const char *sql, char **ptr_value, int64_t *int_value, DBTYPE expected_type) {
402 24643 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
403 :
404 : // init values and sanity check expected_type
405 24643 : if (ptr_value) *ptr_value = NULL;
406 24643 : *int_value = 0;
407 24643 : if (expected_type != DBTYPE_INTEGER && expected_type != DBTYPE_TEXT && expected_type != DBTYPE_BLOB) return SQLITE_MISUSE;
408 :
409 24643 : sqlite3_stmt *vm = NULL;
410 24643 : int rc = sqlite3_prepare_v2((sqlite3 *)db, sql, -1, &vm, NULL);
411 24643 : if (rc != SQLITE_OK) goto cleanup_select;
412 :
413 : // ensure at least one column
414 24642 : if (sqlite3_column_count(vm) < 1) {rc = SQLITE_MISMATCH; goto cleanup_select;}
415 :
416 24642 : rc = sqlite3_step(vm);
417 24642 : if (rc == SQLITE_DONE) {rc = SQLITE_OK; goto cleanup_select;} // no rows OK
418 23883 : if (rc != SQLITE_ROW) goto cleanup_select;
419 :
420 : // sanity check column type
421 23883 : DBTYPE type = (DBTYPE)sqlite3_column_type(vm, 0);
422 23883 : if (type == SQLITE_NULL) {rc = SQLITE_OK; goto cleanup_select;}
423 23840 : if (type != expected_type) {rc = SQLITE_MISMATCH; goto cleanup_select;}
424 :
425 23840 : if (expected_type == DBTYPE_INTEGER) {
426 7962 : *int_value = (int64_t)sqlite3_column_int64(vm, 0);
427 7962 : } else {
428 15878 : const void *value = (expected_type == DBTYPE_TEXT) ? (const void *)sqlite3_column_text(vm, 0) : (const void *)sqlite3_column_blob(vm, 0);
429 15878 : int len = sqlite3_column_bytes(vm, 0);
430 15878 : if (len) {
431 15878 : char *ptr = cloudsync_memory_alloc(len + 1);
432 15878 : if (!ptr) {rc = SQLITE_NOMEM; goto cleanup_select;}
433 :
434 15878 : if (len > 0 && value) memcpy(ptr, value, len);
435 15878 : if (expected_type == DBTYPE_TEXT) ptr[len] = 0; // NULL terminate in case of TEXT
436 :
437 15878 : *ptr_value = ptr;
438 15878 : *int_value = len;
439 15878 : }
440 : }
441 23840 : rc = SQLITE_OK;
442 :
443 : cleanup_select:
444 24643 : if (vm) sqlite3_finalize(vm);
445 24643 : return rc;
446 24643 : }
447 :
448 0 : static int database_select2_values (cloudsync_context *data, const char *sql, char **value, int64_t *len, int64_t *value2) {
449 0 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
450 :
451 : // init values and sanity check expected_type
452 0 : *value = NULL;
453 0 : *value2 = 0;
454 0 : *len = 0;
455 :
456 0 : sqlite3_stmt *vm = NULL;
457 0 : int rc = sqlite3_prepare_v2((sqlite3 *)db, sql, -1, &vm, NULL);
458 0 : if (rc != SQLITE_OK) goto cleanup_select;
459 :
460 : // ensure column count
461 0 : if (sqlite3_column_count(vm) < 2) {rc = SQLITE_MISMATCH; goto cleanup_select;}
462 :
463 0 : rc = sqlite3_step(vm);
464 0 : if (rc == SQLITE_DONE) {rc = SQLITE_OK; goto cleanup_select;} // no rows OK
465 0 : if (rc != SQLITE_ROW) goto cleanup_select;
466 :
467 : // sanity check column types
468 0 : if (sqlite3_column_type(vm, 0) != SQLITE_BLOB) {rc = SQLITE_MISMATCH; goto cleanup_select;}
469 0 : if (sqlite3_column_type(vm, 1) != SQLITE_INTEGER) {rc = SQLITE_MISMATCH; goto cleanup_select;}
470 :
471 : // 1st column is BLOB
472 0 : const void *blob = (const void *)sqlite3_column_blob(vm, 0);
473 0 : int blob_len = sqlite3_column_bytes(vm, 0);
474 0 : if (blob_len) {
475 0 : char *ptr = cloudsync_memory_alloc(blob_len);
476 0 : if (!ptr) {rc = SQLITE_NOMEM; goto cleanup_select;}
477 :
478 0 : if (blob_len > 0 && blob) memcpy(ptr, blob, blob_len);
479 0 : *value = ptr;
480 0 : *len = blob_len;
481 0 : }
482 :
483 : // 2nd column is INTEGER
484 0 : *value2 = (int64_t)sqlite3_column_int64(vm, 1);
485 :
486 0 : rc = SQLITE_OK;
487 :
488 : cleanup_select:
489 0 : if (vm) sqlite3_finalize(vm);
490 0 : return rc;
491 : }
492 :
493 8157 : bool database_system_exists (cloudsync_context *data, const char *name, const char *type) {
494 8157 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
495 8157 : sqlite3_stmt *vm = NULL;
496 8157 : bool result = false;
497 :
498 : char sql[1024];
499 8157 : snprintf(sql, sizeof(sql), "SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type='%s' AND name=?1 COLLATE NOCASE);", type);
500 8157 : int rc = sqlite3_prepare_v2(db, sql, -1, &vm, NULL);
501 8157 : if (rc != SQLITE_OK) goto finalize;
502 :
503 8157 : rc = sqlite3_bind_text(vm, 1, name, -1, SQLITE_STATIC);
504 8157 : if (rc != SQLITE_OK) goto finalize;
505 :
506 8157 : rc = sqlite3_step(vm);
507 16314 : if (rc == SQLITE_ROW) {
508 8157 : result = (bool)sqlite3_column_int(vm, 0);
509 8157 : rc = SQLITE_OK;
510 8157 : }
511 :
512 : finalize:
513 8157 : 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));
514 8157 : if (vm) sqlite3_finalize(vm);
515 8157 : return result;
516 : }
517 :
518 : // MARK: - GENERAL -
519 :
520 69766 : int database_exec (cloudsync_context *data, const char *sql) {
521 69766 : return sqlite3_exec((sqlite3 *)cloudsync_db(data), sql, NULL, NULL, NULL);
522 : }
523 :
524 2322 : int database_exec_callback (cloudsync_context *data, const char *sql, int (*callback)(void *xdata, int argc, char **values, char **names), void *xdata) {
525 2322 : return sqlite3_exec((sqlite3 *)cloudsync_db(data), sql, callback, xdata, NULL);
526 : }
527 :
528 3966 : int database_write (cloudsync_context *data, const char *sql, const char **bind_values, DBTYPE bind_types[], int bind_lens[], int bind_count) {
529 3966 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
530 3966 : sqlite3_stmt *vm = NULL;
531 3966 : int rc = sqlite3_prepare_v2((sqlite3 *)db, sql, -1, &vm, NULL);
532 3966 : if (rc != SQLITE_OK) goto cleanup_write;
533 :
534 14247 : for (int i=0; i<bind_count; ++i) {
535 10283 : switch (bind_types[i]) {
536 : case SQLITE_NULL:
537 1 : rc = sqlite3_bind_null(vm, i+1);
538 1 : break;
539 : case SQLITE_TEXT:
540 8707 : rc = sqlite3_bind_text(vm, i+1, bind_values[i], bind_lens[i], SQLITE_STATIC);
541 8707 : break;
542 : case SQLITE_BLOB:
543 757 : rc = sqlite3_bind_blob(vm, i+1, bind_values[i], bind_lens[i], SQLITE_STATIC);
544 757 : break;
545 : case SQLITE_INTEGER: {
546 817 : sqlite3_int64 value = strtoll(bind_values[i], NULL, 0);
547 817 : rc = sqlite3_bind_int64(vm, i+1, value);
548 817 : } break;
549 : case SQLITE_FLOAT: {
550 1 : double value = strtod(bind_values[i], NULL);
551 1 : rc = sqlite3_bind_double(vm, i+1, value);
552 1 : } break;
553 : }
554 10283 : if (rc != SQLITE_OK) goto cleanup_write;
555 10283 : }
556 :
557 : // execute statement
558 3964 : rc = sqlite3_step(vm);
559 3964 : if (rc == SQLITE_DONE) rc = SQLITE_OK;
560 :
561 : cleanup_write:
562 3966 : if (vm) sqlite3_finalize(vm);
563 3966 : return rc;
564 : }
565 :
566 8721 : int database_select_int (cloudsync_context *data, const char *sql, int64_t *value) {
567 8721 : return database_select1_value(data, sql, NULL, value, DBTYPE_INTEGER);
568 : }
569 :
570 15148 : int database_select_text (cloudsync_context *data, const char *sql, char **value) {
571 15148 : int64_t len = 0;
572 15148 : return database_select1_value(data, sql, value, &len, DBTYPE_TEXT);
573 : }
574 :
575 774 : int database_select_blob (cloudsync_context *data, const char *sql, char **value, int64_t *len) {
576 774 : return database_select1_value(data, sql, value, len, DBTYPE_BLOB);
577 : }
578 :
579 0 : int database_select_blob_int (cloudsync_context *data, const char *sql, char **value, int64_t *len, int64_t *value2) {
580 0 : return database_select2_values(data, sql, value, len, value2);
581 : }
582 :
583 1260 : const char *database_errmsg (cloudsync_context *data) {
584 1260 : return sqlite3_errmsg((sqlite3 *)cloudsync_db(data));
585 : }
586 :
587 1259 : int database_errcode (cloudsync_context *data) {
588 1259 : return sqlite3_errcode((sqlite3 *)cloudsync_db(data));
589 : }
590 :
591 1 : void database_log_warning (cloudsync_context *data, const char *message) {
592 1 : sqlite3_log(SQLITE_WARNING, "cloudsync: %s", message ? message : "");
593 1 : }
594 :
595 7135 : bool database_in_transaction (cloudsync_context *data) {
596 7135 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
597 7135 : bool in_transaction = (sqlite3_get_autocommit(db) != true);
598 7135 : return in_transaction;
599 : }
600 :
601 79 : int database_fragment_lock (cloudsync_context *data, const char *value_id) {
602 : // writers are already serialized
603 79 : return DBRES_OK;
604 : }
605 :
606 5591 : bool database_table_exists (cloudsync_context *data, const char *name, const char *schema) {
607 5591 : UNUSED_PARAMETER(schema);
608 5591 : return database_system_exists(data, name, "table");
609 : }
610 :
611 4755 : bool database_internal_table_exists (cloudsync_context *data, const char *name) {
612 4755 : return database_table_exists(data, name, NULL);
613 : }
614 :
615 2565 : bool database_trigger_exists (cloudsync_context *data, const char *name) {
616 2565 : return database_system_exists(data, name, "trigger");
617 : }
618 :
619 1646 : int database_count_pk (cloudsync_context *data, const char *table_name, bool not_null, const char *schema) {
620 1646 : UNUSED_PARAMETER(schema);
621 : char buffer[1024];
622 1646 : char *sql = NULL;
623 :
624 1646 : if (not_null) {
625 0 : sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0 AND \"notnull\"=1;", table_name);
626 0 : } else {
627 1646 : sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0;", table_name);
628 : }
629 :
630 1646 : int64_t count = 0;
631 1646 : int rc = database_select_int(data, sql, &count);
632 1646 : if (rc != DBRES_OK) return -1;
633 1646 : return (int)count;
634 1646 : }
635 :
636 824 : int database_count_nonpk (cloudsync_context *data, const char *table_name, const char *schema) {
637 824 : UNUSED_PARAMETER(schema);
638 : char buffer[1024];
639 824 : char *sql = NULL;
640 :
641 824 : sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=0;", table_name);
642 824 : int64_t count = 0;
643 824 : int rc = database_select_int(data, sql, &count);
644 824 : if (rc != DBRES_OK) return -1;
645 824 : return (int)count;
646 824 : }
647 :
648 670 : int database_count_int_pk (cloudsync_context *data, const char *table_name, const char *schema) {
649 670 : UNUSED_PARAMETER(schema);
650 : char buffer[1024];
651 670 : char *sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=1 AND \"type\" LIKE '%%INT%%';", table_name);
652 :
653 670 : int64_t count = 0;
654 670 : int rc = database_select_int(data, sql, &count);
655 670 : if (rc != DBRES_OK) return -1;
656 670 : return (int)count;
657 670 : }
658 :
659 820 : int database_count_notnull_without_default (cloudsync_context *data, const char *table_name, const char *schema) {
660 820 : UNUSED_PARAMETER(schema);
661 : char buffer[1024];
662 820 : char *sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT count(*) FROM pragma_table_info('%q') WHERE pk=0 AND \"notnull\"=1 AND \"dflt_value\" IS NULL;", table_name);
663 :
664 820 : int64_t count = 0;
665 820 : int rc = database_select_int(data, sql, &count);
666 820 : if (rc != DBRES_OK) return -1;
667 820 : return (int)count;
668 820 : }
669 :
670 0 : int database_cleanup (cloudsync_context *data) {
671 0 : char *sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'cloudsync_%' AND name NOT LIKE '%_cloudsync';";
672 0 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
673 :
674 0 : char **result = NULL;
675 0 : char *errmsg = NULL;
676 : int nrows, ncols;
677 0 : int rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &errmsg);
678 0 : if (rc != SQLITE_OK) {
679 0 : cloudsync_set_error(data, (errmsg) ? errmsg : "Error retrieving augmented tables", rc);
680 0 : goto exit_cleanup;
681 : }
682 :
683 0 : for (int i = ncols; i < nrows+ncols; i+=ncols) {
684 0 : int rc2 = cloudsync_cleanup(data, result[i]);
685 0 : if (rc2 != SQLITE_OK) {rc = rc2; goto exit_cleanup;}
686 0 : }
687 :
688 : exit_cleanup:
689 0 : if (result) sqlite3_free_table(result);
690 0 : if (errmsg) sqlite3_free(errmsg);
691 0 : return rc;
692 : }
693 :
694 : // MARK: - TRIGGERS and META -
695 :
696 821 : int database_create_metatable (cloudsync_context *data, const char *table_name) {
697 : DEBUG_DBFUNCTION("database_create_metatable %s", table_name);
698 :
699 : // table_name cannot be longer than 512 characters so static buffer size is computed accordling to that value
700 : char buffer[2048];
701 :
702 : // WITHOUT ROWID is available starting from SQLite version 3.8.2 (2013-12-06) and later
703 821 : char *sql = sqlite3_snprintf(sizeof(buffer), buffer, "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_name, table_name, table_name);
704 :
705 821 : int rc = database_exec(data, sql);
706 : DEBUG_SQL("\n%s", sql);
707 821 : return rc;
708 : }
709 :
710 855 : int database_create_insert_trigger (cloudsync_context *data, const char *table_name, char *trigger_when) {
711 : // NEW.prikey1, NEW.prikey2...
712 : char buffer[1024];
713 855 : char *trigger_name = sqlite3_snprintf(sizeof(buffer), buffer, "cloudsync_after_insert_%s", table_name);
714 855 : if (database_trigger_exists(data, trigger_name)) return SQLITE_OK;
715 :
716 : char buffer2[2048];
717 837 : char *sql2 = sqlite3_snprintf(sizeof(buffer2), buffer2, "SELECT group_concat('NEW.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table_name);
718 :
719 837 : char *pkclause = NULL;
720 837 : int rc = database_select_text(data, sql2, &pkclause);
721 837 : if (rc != SQLITE_OK) return rc;
722 837 : char *pkvalues = (pkclause) ? pkclause : "NEW.rowid";
723 :
724 837 : char *sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER INSERT ON \"%w\" %s BEGIN SELECT cloudsync_insert('%q', %s); END", trigger_name, table_name, trigger_when, table_name, pkvalues);
725 837 : if (pkclause) cloudsync_memory_free(pkclause);
726 837 : if (!sql) return SQLITE_NOMEM;
727 :
728 837 : rc = database_exec(data, sql);
729 : DEBUG_SQL("\n%s", sql);
730 837 : cloudsync_memory_free(sql);
731 837 : return rc;
732 855 : }
733 :
734 7 : int database_create_update_trigger_gos (cloudsync_context *data, const char *table_name) {
735 : // Grow Only Set
736 : // In a grow-only set, the update operation is not allowed.
737 : // 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,
738 : // without ever removing or modifying them.
739 : // Once an element is added to the set, it remains there permanently, which guarantees that the set only grows over time.
740 : char buffer[1024];
741 7 : char *trigger_name = sqlite3_snprintf(sizeof(buffer), buffer, "cloudsync_before_update_%s", table_name);
742 7 : if (database_trigger_exists(data, trigger_name)) return SQLITE_OK;
743 :
744 : char buffer2[2048+512];
745 7 : char *sql = sqlite3_snprintf(sizeof(buffer2), buffer2, "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_name, table_name, table_name);
746 :
747 7 : int rc = database_exec(data, sql);
748 : DEBUG_SQL("\n%s", sql);
749 7 : return rc;
750 7 : }
751 :
752 848 : int database_create_update_trigger (cloudsync_context *data, const char *table_name, const char *trigger_when) {
753 : // NEW.prikey1, NEW.prikey2, OLD.prikey1, OLD.prikey2, NEW.col1, OLD.col1, NEW.col2, OLD.col2...
754 :
755 : char buffer[1024];
756 848 : char *trigger_name = sqlite3_snprintf(sizeof(buffer), buffer, "cloudsync_after_update_%s", table_name);
757 848 : if (database_trigger_exists(data, trigger_name)) return SQLITE_OK;
758 :
759 : // generate VALUES clause for all columns using a CTE to avoid compound SELECT limits
760 : // first, get all primary key columns in order
761 : char buffer2[2048];
762 830 : char *sql2 = sqlite3_snprintf(sizeof(buffer2), buffer2, "SELECT group_concat('('||quote('%q')||', NEW.\"' || format('%%w', name) || '\", OLD.\"' || format('%%w', name) || '\")', ', ') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table_name, table_name);
763 :
764 830 : char *pk_values_list = NULL;
765 830 : int rc = database_select_text(data, sql2, &pk_values_list);
766 830 : if (rc != SQLITE_OK) return rc;
767 :
768 : // then get all regular columns in order
769 830 : sql2 = sqlite3_snprintf(sizeof(buffer2), buffer2, "SELECT group_concat('('||quote('%q')||', NEW.\"' || format('%%w', name) || '\", OLD.\"' || format('%%w', name) || '\")', ', ') FROM pragma_table_info('%q') WHERE pk=0 ORDER BY cid;", table_name, table_name);
770 :
771 830 : char *col_values_list = NULL;
772 830 : rc = database_select_text(data, sql2, &col_values_list);
773 830 : if (rc != SQLITE_OK) {
774 0 : if (pk_values_list) cloudsync_memory_free(pk_values_list);
775 0 : return rc;
776 : }
777 :
778 : // build the complete VALUES query
779 830 : char *values_query = NULL;
780 830 : if (col_values_list && strlen(col_values_list) > 0) {
781 : // Table has both primary keys and regular columns
782 790 : values_query = cloudsync_memory_mprintf(
783 : "WITH column_data(table_name, new_value, old_value) AS (VALUES %s, %s) "
784 : "SELECT table_name, new_value, old_value FROM column_data",
785 790 : pk_values_list, col_values_list);
786 790 : } else {
787 : // Table has only primary keys
788 40 : values_query = cloudsync_memory_mprintf(
789 : "WITH column_data(table_name, new_value, old_value) AS (VALUES %s) "
790 : "SELECT table_name, new_value, old_value FROM column_data",
791 40 : pk_values_list);
792 : }
793 :
794 830 : if (pk_values_list) cloudsync_memory_free(pk_values_list);
795 830 : if (col_values_list) cloudsync_memory_free(col_values_list);
796 830 : if (!values_query) return SQLITE_NOMEM;
797 :
798 : // create the trigger with aggregate function
799 830 : char *sql = cloudsync_memory_mprintf(
800 : "CREATE TRIGGER \"%w\" AFTER UPDATE ON \"%w\" %s BEGIN "
801 : "SELECT cloudsync_update(table_name, new_value, old_value) FROM (%s); "
802 : "END",
803 830 : trigger_name, table_name, trigger_when, values_query);
804 :
805 830 : cloudsync_memory_free(values_query);
806 830 : if (!sql) return SQLITE_NOMEM;
807 :
808 830 : rc = database_exec(data, sql);
809 : DEBUG_SQL("\n%s", sql);
810 830 : cloudsync_memory_free(sql);
811 830 : return rc;
812 848 : }
813 :
814 7 : int database_create_delete_trigger_gos (cloudsync_context *data, const char *table_name) {
815 : // Grow Only Set
816 : // In a grow-only set, the delete operation is not allowed.
817 :
818 : char buffer[1024];
819 7 : char *trigger_name = sqlite3_snprintf(sizeof(buffer), buffer, "cloudsync_before_delete_%s", table_name);
820 7 : if (database_trigger_exists(data, trigger_name)) return SQLITE_OK;
821 :
822 : char buffer2[2048+512];
823 7 : char *sql = sqlite3_snprintf(sizeof(buffer2), buffer2, "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_name, table_name, table_name);
824 :
825 7 : int rc = database_exec(data, sql);
826 : DEBUG_SQL("\n%s", sql);
827 7 : return rc;
828 7 : }
829 :
830 848 : int database_create_delete_trigger (cloudsync_context *data, const char *table_name, const char *trigger_when) {
831 : // OLD.prikey1, OLD.prikey2...
832 :
833 : char buffer[1024];
834 848 : char *trigger_name = sqlite3_snprintf(sizeof(buffer), buffer, "cloudsync_after_delete_%s", table_name);
835 848 : if (database_trigger_exists(data, trigger_name)) return SQLITE_OK;
836 :
837 : char buffer2[1024];
838 830 : char *sql2 = sqlite3_snprintf(sizeof(buffer2), buffer2, "SELECT group_concat('OLD.\"' || format('%%w', name) || '\"', ',') FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk;", table_name);
839 :
840 830 : char *pkclause = NULL;
841 830 : int rc = database_select_text(data, sql2, &pkclause);
842 830 : if (rc != SQLITE_OK) return rc;
843 830 : char *pkvalues = (pkclause) ? pkclause : "OLD.rowid";
844 :
845 830 : char *sql = cloudsync_memory_mprintf("CREATE TRIGGER \"%w\" AFTER DELETE ON \"%w\" %s BEGIN SELECT cloudsync_delete('%q',%s); END", trigger_name, table_name, trigger_when, table_name, pkvalues);
846 830 : if (pkclause) cloudsync_memory_free(pkclause);
847 830 : if (!sql) return SQLITE_NOMEM;
848 :
849 830 : rc = database_exec(data, sql);
850 : DEBUG_SQL("\n%s", sql);
851 830 : cloudsync_memory_free(sql);
852 830 : return rc;
853 848 : }
854 :
855 : // Build trigger WHEN clauses, optionally incorporating a row-level filter.
856 : // INSERT/UPDATE use NEW-prefixed filter, DELETE uses OLD-prefixed filter.
857 : // Returns dynamically-allocated strings that must be freed with cloudsync_memory_free.
858 855 : static void database_build_trigger_when(
859 : cloudsync_context *data, const char *table_name, const char *filter,
860 : char **when_new_out, char **when_old_out)
861 : {
862 855 : char *new_filter_str = NULL;
863 855 : char *old_filter_str = NULL;
864 :
865 855 : if (filter) {
866 : char sql_cols[1024];
867 40 : sqlite3_snprintf(sizeof(sql_cols), sql_cols,
868 20 : "SELECT name FROM pragma_table_info('%q') ORDER BY cid;", table_name);
869 :
870 : char *col_names[256];
871 20 : int ncols = 0;
872 :
873 20 : sqlite3_stmt *col_vm = NULL;
874 20 : int col_rc = sqlite3_prepare_v2((sqlite3 *)cloudsync_db(data), sql_cols, -1, &col_vm, NULL);
875 20 : if (col_rc == SQLITE_OK) {
876 90 : while (sqlite3_step(col_vm) == SQLITE_ROW && ncols < 256) {
877 70 : const char *name = (const char *)sqlite3_column_text(col_vm, 0);
878 70 : if (name) {
879 70 : char *dup = cloudsync_memory_mprintf("%s", name);
880 70 : if (!dup) break;
881 70 : col_names[ncols++] = dup;
882 70 : }
883 : }
884 20 : sqlite3_finalize(col_vm);
885 20 : }
886 :
887 20 : if (ncols > 0) {
888 20 : new_filter_str = cloudsync_filter_add_row_prefix(filter, "NEW", col_names, ncols);
889 20 : old_filter_str = cloudsync_filter_add_row_prefix(filter, "OLD", col_names, ncols);
890 90 : for (int i = 0; i < ncols; ++i) cloudsync_memory_free(col_names[i]);
891 20 : }
892 20 : }
893 :
894 855 : if (new_filter_str) {
895 20 : *when_new_out = cloudsync_memory_mprintf(
896 20 : "FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0 AND (%s)", table_name, new_filter_str);
897 20 : } else {
898 835 : *when_new_out = cloudsync_memory_mprintf(
899 835 : "FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0", table_name);
900 : }
901 :
902 855 : if (old_filter_str) {
903 20 : *when_old_out = cloudsync_memory_mprintf(
904 20 : "FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0 AND (%s)", table_name, old_filter_str);
905 20 : } else {
906 835 : *when_old_out = cloudsync_memory_mprintf(
907 835 : "FOR EACH ROW WHEN cloudsync_is_sync('%q') = 0", table_name);
908 : }
909 :
910 855 : if (new_filter_str) cloudsync_memory_free(new_filter_str);
911 855 : if (old_filter_str) cloudsync_memory_free(old_filter_str);
912 855 : }
913 :
914 855 : int database_create_triggers (cloudsync_context *data, const char *table_name, table_algo algo, const char *filter) {
915 : DEBUG_DBFUNCTION("database_create_triggers %s", table_name);
916 :
917 855 : if (dbutils_settings_check_version(data, "0.8.25") <= 0) {
918 0 : database_delete_triggers(data, table_name);
919 0 : }
920 :
921 855 : char *trigger_when_new = NULL;
922 855 : char *trigger_when_old = NULL;
923 855 : database_build_trigger_when(data, table_name, filter,
924 : &trigger_when_new, &trigger_when_old);
925 :
926 855 : if (!trigger_when_new || !trigger_when_old) {
927 0 : if (trigger_when_new) cloudsync_memory_free(trigger_when_new);
928 0 : if (trigger_when_old) cloudsync_memory_free(trigger_when_old);
929 0 : return SQLITE_NOMEM;
930 : }
931 :
932 : // INSERT TRIGGER (uses NEW prefix)
933 855 : int rc = database_create_insert_trigger(data, table_name, trigger_when_new);
934 855 : if (rc != SQLITE_OK) goto done;
935 :
936 : // UPDATE TRIGGER (uses NEW prefix)
937 855 : if (algo == table_algo_crdt_gos) rc = database_create_update_trigger_gos(data, table_name);
938 848 : else rc = database_create_update_trigger(data, table_name, trigger_when_new);
939 855 : if (rc != SQLITE_OK) goto done;
940 :
941 : // DELETE TRIGGER (uses OLD prefix)
942 1703 : if (algo == table_algo_crdt_gos) rc = database_create_delete_trigger_gos(data, table_name);
943 848 : else rc = database_create_delete_trigger(data, table_name, trigger_when_old);
944 :
945 : done:
946 855 : if (rc != SQLITE_OK) DEBUG_ALWAYS("database_create_triggers error %s (%d)", sqlite3_errmsg(cloudsync_db(data)), rc);
947 855 : cloudsync_memory_free(trigger_when_new);
948 855 : cloudsync_memory_free(trigger_when_old);
949 855 : return rc;
950 855 : }
951 :
952 51 : int database_delete_triggers (cloudsync_context *data, const char *table) {
953 : DEBUG_DBFUNCTION("database_delete_triggers %s", table);
954 :
955 : // from cloudsync_table_sanity_check we already know that 2048 is OK
956 : char buffer[2048];
957 51 : size_t blen = sizeof(buffer);
958 51 : int rc = SQLITE_ERROR;
959 :
960 51 : char *sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_before_update_%w\";", table);
961 51 : rc = database_exec(data, sql);
962 51 : if (rc != SQLITE_OK) goto finalize;
963 :
964 51 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_before_delete_%w\";", table);
965 51 : rc = database_exec(data, sql);
966 51 : if (rc != SQLITE_OK) goto finalize;
967 :
968 51 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_insert_%w\";", table);
969 51 : rc = database_exec(data, sql);
970 51 : if (rc != SQLITE_OK) goto finalize;
971 :
972 51 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_update_%w\";", table);
973 51 : rc = database_exec(data, sql);
974 51 : if (rc != SQLITE_OK) goto finalize;
975 :
976 51 : sql = sqlite3_snprintf((int)blen, buffer, "DROP TRIGGER IF EXISTS \"cloudsync_after_delete_%w\";", table);
977 51 : rc = database_exec(data, sql);
978 51 : if (rc != SQLITE_OK) goto finalize;
979 :
980 : finalize:
981 51 : if (rc != SQLITE_OK) DEBUG_ALWAYS("dbutils_delete_triggers error %s (%s)", database_errmsg(data), sql);
982 51 : return rc;
983 : }
984 :
985 : // MARK: - SCHEMA -
986 :
987 757 : int64_t database_schema_version (cloudsync_context *data) {
988 757 : int64_t value = 0;
989 757 : int rc = database_select_int(data, SQL_SCHEMA_VERSION, &value);
990 757 : return (rc == DBRES_OK) ? value : 0;
991 : }
992 :
993 769 : uint64_t database_schema_hash (cloudsync_context *data) {
994 769 : int64_t value = 0;
995 769 : int rc = database_select_int(data, "SELECT hash FROM cloudsync_schema_versions ORDER BY seq DESC limit 1;", &value);
996 769 : return (rc == DBRES_OK) ? (uint64_t)value : 0;
997 : }
998 :
999 4 : bool database_check_schema_hash (cloudsync_context *data, uint64_t hash) {
1000 : // a change from the current version of the schema or from previous known schema can be applied
1001 : // a change from a newer schema version not yet applied to this peer cannot be applied
1002 : // so a schema hash is valid if it exists in the cloudsync_schema_versions table
1003 :
1004 : // the idea is to allow changes on stale peers and to be able to apply these changes on peers with newer schema,
1005 : // but it requires alter table operation on augmented tables only add new columns and never drop columns for backward compatibility
1006 : char sql[1024];
1007 4 : snprintf(sql, sizeof(sql), "SELECT 1 FROM cloudsync_schema_versions WHERE hash = (%" PRId64 ")", (int64_t)hash);
1008 :
1009 4 : int64_t value = 0;
1010 4 : database_select_int(data, sql, &value);
1011 4 : return (value == 1);
1012 : }
1013 :
1014 821 : int database_update_schema_hash (cloudsync_context *data, uint64_t *hash) {
1015 : // Build normalized schema string using only: column name (lowercase), type (SQLite affinity), pk flag
1016 : // Format: tablename:colname:affinity:pk,... (ordered by table name, then column id)
1017 : // This makes the hash resilient to formatting, quoting, case differences and portable across databases
1018 : //
1019 : // Type mapping (simplified from SQLite affinity rules for cross-database compatibility):
1020 : // - Types containing 'INT' → 'integer'
1021 : // - Types containing 'CHAR', 'CLOB', 'TEXT' → 'text'
1022 : // - Types containing 'BLOB' or empty → 'blob'
1023 : // - Types containing 'REAL', 'FLOA', 'DOUB' → 'real'
1024 : // - Types exactly 'NUMERIC' or 'DECIMAL' → 'numeric'
1025 : // - Everything else → 'text' (default)
1026 : //
1027 : // NOTE: This deviates from SQLite's actual affinity rules where unknown types get NUMERIC affinity.
1028 : // We use 'text' as default to improve cross-database compatibility with PostgreSQL, where types
1029 : // like TIMESTAMPTZ, UUID, JSON, etc. are commonly used and map to 'text' in the PostgreSQL
1030 : // implementation. This ensures schemas with PostgreSQL-specific type names in SQLite DDL
1031 : // will hash consistently across both databases.
1032 821 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
1033 :
1034 821 : char **tables = NULL;
1035 : int ntables, tcols;
1036 821 : int rc = sqlite3_get_table(db, "SELECT DISTINCT tbl_name FROM cloudsync_table_settings ORDER BY tbl_name;",
1037 : &tables, &ntables, &tcols, NULL);
1038 821 : if (rc != SQLITE_OK || ntables == 0) {
1039 0 : if (tables) sqlite3_free_table(tables);
1040 0 : return SQLITE_ERROR;
1041 : }
1042 :
1043 821 : char *schema = NULL;
1044 821 : size_t schema_len = 0;
1045 821 : size_t schema_cap = 0;
1046 :
1047 1717 : for (int t = 1; t <= ntables; t++) {
1048 897 : const char *tbl_name = tables[t];
1049 :
1050 : // Query pragma_table_info for this table with normalized type
1051 897 : char *col_sql = cloudsync_memory_mprintf(
1052 : "SELECT LOWER(name), "
1053 : "CASE "
1054 : " WHEN UPPER(type) LIKE '%%INT%%' THEN 'integer' "
1055 : " WHEN UPPER(type) LIKE '%%CHAR%%' OR UPPER(type) LIKE '%%CLOB%%' OR UPPER(type) LIKE '%%TEXT%%' THEN 'text' "
1056 : " WHEN UPPER(type) LIKE '%%BLOB%%' OR type = '' THEN 'blob' "
1057 : " WHEN UPPER(type) LIKE '%%REAL%%' OR UPPER(type) LIKE '%%FLOA%%' OR UPPER(type) LIKE '%%DOUB%%' THEN 'real' "
1058 : " WHEN UPPER(type) IN ('NUMERIC', 'DECIMAL') THEN 'numeric' "
1059 : " ELSE 'text' "
1060 : "END, "
1061 : "CASE WHEN pk > 0 THEN '1' ELSE '0' END "
1062 897 : "FROM pragma_table_info('%q') ORDER BY cid;", tbl_name);
1063 :
1064 897 : if (!col_sql) {
1065 0 : if (schema) cloudsync_memory_free(schema);
1066 0 : sqlite3_free_table(tables);
1067 0 : return SQLITE_NOMEM;
1068 : }
1069 :
1070 897 : char **cols = NULL;
1071 : int nrows, ncols;
1072 897 : rc = sqlite3_get_table(db, col_sql, &cols, &nrows, &ncols, NULL);
1073 897 : cloudsync_memory_free(col_sql);
1074 :
1075 897 : if (rc != SQLITE_OK || ncols != 3) {
1076 1 : if (cols) sqlite3_free_table(cols);
1077 1 : if (schema) cloudsync_memory_free(schema);
1078 1 : sqlite3_free_table(tables);
1079 1 : return SQLITE_ERROR;
1080 : }
1081 :
1082 : // Append each column: tablename:colname:affinity:pk
1083 4023 : for (int r = 1; r <= nrows; r++) {
1084 3127 : const char *col_name = cols[r * 3];
1085 3127 : const char *col_type = cols[r * 3 + 1];
1086 3127 : const char *col_pk = cols[r * 3 + 2];
1087 :
1088 : // Calculate required size: tbl_name:col_name:col_type:col_pk,
1089 3127 : size_t entry_len = strlen(tbl_name) + 1 + strlen(col_name) + 1 + strlen(col_type) + 1 + strlen(col_pk) + 1;
1090 :
1091 3127 : if (schema_len + entry_len + 1 > schema_cap) {
1092 825 : schema_cap = (schema_cap == 0) ? 1024 : schema_cap * 2;
1093 825 : if (schema_cap < schema_len + entry_len + 1) schema_cap = schema_len + entry_len + 1;
1094 825 : char *new_schema = cloudsync_memory_realloc(schema, schema_cap);
1095 825 : if (!new_schema) {
1096 0 : if (schema) cloudsync_memory_free(schema);
1097 0 : sqlite3_free_table(cols);
1098 0 : sqlite3_free_table(tables);
1099 0 : return SQLITE_NOMEM;
1100 : }
1101 825 : schema = new_schema;
1102 825 : }
1103 :
1104 3127 : int written = snprintf(schema + schema_len, schema_cap - schema_len, "%s:%s:%s:%s,",
1105 : tbl_name, col_name, col_type, col_pk);
1106 3127 : schema_len += written;
1107 3127 : }
1108 :
1109 896 : sqlite3_free_table(cols);
1110 896 : }
1111 :
1112 820 : sqlite3_free_table(tables);
1113 :
1114 820 : if (!schema || schema_len == 0) return SQLITE_ERROR;
1115 :
1116 : // Remove trailing comma
1117 820 : if (schema_len > 0 && schema[schema_len - 1] == ',') {
1118 820 : schema[schema_len - 1] = '\0';
1119 820 : schema_len--;
1120 820 : }
1121 :
1122 : DEBUG_MERGE("database_update_schema_hash len %zu schema %s", schema_len, schema);
1123 820 : sqlite3_uint64 h = fnv1a_hash(schema, schema_len);
1124 820 : cloudsync_memory_free(schema);
1125 820 : if (hash && *hash == h) return SQLITE_CONSTRAINT;
1126 :
1127 : char sql[1024];
1128 815 : snprintf(sql, sizeof(sql), "INSERT INTO cloudsync_schema_versions (hash, seq) "
1129 : "VALUES (%" PRId64 ", COALESCE((SELECT MAX(seq) FROM cloudsync_schema_versions), 0) + 1) "
1130 : "ON CONFLICT(hash) DO UPDATE SET "
1131 : " seq = (SELECT COALESCE(MAX(seq), 0) + 1 FROM cloudsync_schema_versions);", (int64_t)h);
1132 815 : rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
1133 815 : if (rc == SQLITE_OK && hash) *hash = h;
1134 815 : return rc;
1135 821 : }
1136 :
1137 : // MARK: - VM -
1138 :
1139 37433 : int databasevm_prepare (cloudsync_context *data, const char *sql, dbvm_t **vm, int flags) {
1140 37433 : return sqlite3_prepare_v3((sqlite3 *)cloudsync_db(data), sql, -1, flags, (sqlite3_stmt **)vm, NULL);
1141 : }
1142 :
1143 557921 : int databasevm_step (dbvm_t *vm) {
1144 557921 : return sqlite3_step((sqlite3_stmt *)vm);
1145 : }
1146 :
1147 37432 : void databasevm_finalize (dbvm_t *vm) {
1148 37432 : sqlite3_finalize((sqlite3_stmt *)vm);
1149 37432 : }
1150 :
1151 519635 : void databasevm_reset (dbvm_t *vm) {
1152 519635 : sqlite3_reset((sqlite3_stmt *)vm);
1153 519635 : }
1154 :
1155 281277 : void databasevm_clear_bindings (dbvm_t *vm) {
1156 281277 : sqlite3_clear_bindings((sqlite3_stmt *)vm);
1157 281277 : }
1158 :
1159 7373 : int64_t databasevm_changes (dbvm_t *vm) {
1160 7373 : return (int64_t)sqlite3_changes64(sqlite3_db_handle((sqlite3_stmt *)vm));
1161 : }
1162 :
1163 0 : const char *databasevm_sql (dbvm_t *vm) {
1164 0 : return sqlite3_sql((sqlite3_stmt *)vm);
1165 : // the following allocates memory that needs to be freed
1166 : // return sqlite3_expanded_sql((sqlite3_stmt *)vm);
1167 : }
1168 :
1169 0 : static int database_pk_rowid (sqlite3 *db, const char *table_name, char ***names, int *count) {
1170 : char buffer[2048];
1171 0 : char *sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT rowid FROM %Q LIMIT 0;", table_name);
1172 0 : if (!sql) return SQLITE_NOMEM;
1173 :
1174 0 : sqlite3_stmt *vm = NULL;
1175 0 : int rc = sqlite3_prepare_v2(db, sql, -1, &vm, NULL);
1176 0 : if (rc != SQLITE_OK) goto cleanup;
1177 :
1178 : {
1179 0 : char **r = (char**)cloudsync_memory_alloc(sizeof(char*));
1180 0 : if (!r) {rc = SQLITE_NOMEM; goto cleanup;}
1181 0 : r[0] = cloudsync_string_dup("rowid");
1182 0 : if (!r[0]) {cloudsync_memory_free(r); rc = SQLITE_NOMEM; goto cleanup;}
1183 0 : *names = r;
1184 0 : *count = 1;
1185 0 : }
1186 :
1187 : cleanup:
1188 0 : if (vm) sqlite3_finalize(vm);
1189 0 : return rc;
1190 0 : }
1191 :
1192 1736 : int database_pk_names (cloudsync_context *data, const char *table_name, char ***names, int *count) {
1193 : char buffer[2048];
1194 1736 : char *sql = sqlite3_snprintf(sizeof(buffer), buffer, "SELECT name FROM pragma_table_info(%Q) WHERE pk > 0 ORDER BY pk;", table_name);
1195 1736 : if (!sql) return SQLITE_NOMEM;
1196 :
1197 1736 : sqlite3 *db = (sqlite3 *)cloudsync_db(data);
1198 1736 : sqlite3_stmt *vm = NULL;
1199 :
1200 1736 : int rc = sqlite3_prepare_v2(db, sql, -1, &vm, NULL);
1201 1736 : if (rc != SQLITE_OK) goto cleanup;
1202 :
1203 : // count PK columns
1204 1736 : int rows = 0;
1205 4320 : while ((rc = sqlite3_step(vm)) == SQLITE_ROW) rows++;
1206 1736 : if (rc != SQLITE_DONE) goto cleanup;
1207 :
1208 1736 : if (rows == 0) {
1209 0 : sqlite3_finalize(vm);
1210 : // no declared PKs so check for rowid availability
1211 0 : return database_pk_rowid(db, table_name, names, count);
1212 : }
1213 :
1214 : // reset vm to read PKs again
1215 1736 : rc = sqlite3_reset(vm);
1216 1736 : if (rc != SQLITE_OK) goto cleanup;
1217 :
1218 : // allocate array
1219 1736 : char **r = (char**)cloudsync_memory_zeroalloc(sizeof(char*) * rows);
1220 1736 : if (!r) {rc = SQLITE_NOMEM; goto cleanup;}
1221 :
1222 1736 : int i = 0;
1223 4320 : while ((rc = sqlite3_step(vm)) == SQLITE_ROW) {
1224 2584 : const char *txt = (const char*)sqlite3_column_text(vm, 0);
1225 2584 : if (!txt) {rc = SQLITE_ERROR; goto cleanup_r;}
1226 2584 : r[i] = cloudsync_string_dup(txt);
1227 2584 : if (!r[i]) { rc = SQLITE_NOMEM; goto cleanup_r;}
1228 2584 : i++;
1229 : }
1230 1736 : if (rc != SQLITE_DONE) goto cleanup_r;
1231 1736 : rc = SQLITE_OK;
1232 :
1233 1736 : *names = r;
1234 1736 : *count = rows;
1235 1736 : goto cleanup;
1236 :
1237 : cleanup_r:
1238 0 : for (int j = 0; j < i; j++) {
1239 0 : if (r[j]) cloudsync_memory_free(r[j]);
1240 0 : }
1241 0 : cloudsync_memory_free(r);
1242 :
1243 : cleanup:
1244 1736 : if (vm) sqlite3_finalize(vm);
1245 1736 : return rc;
1246 1736 : }
1247 :
1248 : // MARK: - BINDING -
1249 :
1250 403904 : int databasevm_bind_blob (dbvm_t *vm, int index, const void *value, uint64_t size) {
1251 403904 : return sqlite3_bind_blob64((sqlite3_stmt *)vm, index, value, size, SQLITE_STATIC);
1252 : }
1253 :
1254 0 : int databasevm_bind_double (dbvm_t *vm, int index, double value) {
1255 0 : return sqlite3_bind_double((sqlite3_stmt *)vm, index, value);
1256 : }
1257 :
1258 506868 : int databasevm_bind_int (dbvm_t *vm, int index, int64_t value) {
1259 506868 : return sqlite3_bind_int64((sqlite3_stmt *)vm, index, value);
1260 : }
1261 :
1262 1997 : int databasevm_bind_null (dbvm_t *vm, int index) {
1263 1997 : return sqlite3_bind_null((sqlite3_stmt *)vm, index);
1264 : }
1265 :
1266 589527 : int databasevm_bind_text (dbvm_t *vm, int index, const char *value, int size) {
1267 589527 : return sqlite3_bind_text((sqlite3_stmt *)vm, index, value, size, SQLITE_STATIC);
1268 : }
1269 :
1270 31031 : int databasevm_bind_value (dbvm_t *vm, int index, dbvalue_t *value) {
1271 31031 : return sqlite3_bind_value((sqlite3_stmt *)vm, index, (const sqlite3_value *)value);
1272 : }
1273 :
1274 : // MARK: - VALUE -
1275 :
1276 767369 : const void *database_value_blob (dbvalue_t *value) {
1277 767369 : return sqlite3_value_blob((sqlite3_value *)value);
1278 : }
1279 :
1280 159601 : double database_value_double (dbvalue_t *value) {
1281 159601 : return sqlite3_value_double((sqlite3_value *)value);
1282 : }
1283 :
1284 1168384 : int64_t database_value_int (dbvalue_t *value) {
1285 1168384 : return (int64_t)sqlite3_value_int64((sqlite3_value *)value);
1286 : }
1287 :
1288 373120 : const char *database_value_text (dbvalue_t *value) {
1289 373120 : return (const char *)sqlite3_value_text((sqlite3_value *)value);
1290 : }
1291 :
1292 1686868 : int database_value_bytes (dbvalue_t *value) {
1293 1686868 : return sqlite3_value_bytes((sqlite3_value *)value);
1294 : }
1295 :
1296 4319407 : int database_value_type (dbvalue_t *value) {
1297 4319407 : return sqlite3_value_type((sqlite3_value *)value);
1298 : }
1299 :
1300 29723 : void database_value_free (dbvalue_t *value) {
1301 29723 : sqlite3_value_free((sqlite3_value *)value);
1302 29723 : }
1303 :
1304 29723 : void *database_value_dup (dbvalue_t *value) {
1305 29723 : return sqlite3_value_dup((const sqlite3_value *)value);
1306 : }
1307 :
1308 :
1309 : // MARK: - COLUMN -
1310 :
1311 264 : const void *database_column_blob (dbvm_t *vm, int index, size_t *len) {
1312 264 : if (len) *len = sqlite3_column_bytes((sqlite3_stmt *)vm, index);
1313 264 : return sqlite3_column_blob((sqlite3_stmt *)vm, index);
1314 : }
1315 :
1316 0 : double database_column_double (dbvm_t *vm, int index) {
1317 0 : return sqlite3_column_double((sqlite3_stmt *)vm, index);
1318 : }
1319 :
1320 203570 : int64_t database_column_int (dbvm_t *vm, int index) {
1321 203570 : return (int64_t)sqlite3_column_int64((sqlite3_stmt *)vm, index);
1322 : }
1323 :
1324 45526 : const char *database_column_text (dbvm_t *vm, int index) {
1325 45526 : return (const char *)sqlite3_column_text((sqlite3_stmt *)vm, index);
1326 : }
1327 :
1328 174312 : dbvalue_t *database_column_value (dbvm_t *vm, int index) {
1329 174312 : return (dbvalue_t *)sqlite3_column_value((sqlite3_stmt *)vm, index);
1330 : }
1331 :
1332 1106 : int database_column_bytes (dbvm_t *vm, int index) {
1333 1106 : return sqlite3_column_bytes((sqlite3_stmt *)vm, index);
1334 : }
1335 :
1336 6518 : int database_column_type (dbvm_t *vm, int index) {
1337 6518 : return sqlite3_column_type((sqlite3_stmt *)vm, index);
1338 : }
1339 :
1340 : // MARK: - SAVEPOINT -
1341 :
1342 29755 : int database_begin_savepoint (cloudsync_context *data, const char *savepoint_name) {
1343 : char sql[1024];
1344 29755 : snprintf(sql, sizeof(sql), "SAVEPOINT %s;", savepoint_name);
1345 29755 : return database_exec(data, sql);
1346 : }
1347 :
1348 29611 : int database_commit_savepoint (cloudsync_context *data, const char *savepoint_name) {
1349 : char sql[1024];
1350 29611 : snprintf(sql, sizeof(sql), "RELEASE %s;", savepoint_name);
1351 29611 : return database_exec(data, sql);
1352 : }
1353 :
1354 145 : int database_rollback_savepoint (cloudsync_context *data, const char *savepoint_name) {
1355 : char sql[1024];
1356 145 : snprintf(sql, sizeof(sql), "ROLLBACK TO %s; RELEASE %s;", savepoint_name, savepoint_name);
1357 145 : return database_exec(data, sql);
1358 : }
1359 :
1360 : // MARK: - MEMORY -
1361 :
1362 136757 : void *dbmem_alloc (uint64_t size) {
1363 136757 : return sqlite3_malloc64((sqlite3_uint64)size);
1364 : }
1365 :
1366 15068 : void *dbmem_zeroalloc (uint64_t size) {
1367 15068 : void *ptr = (void *)dbmem_alloc(size);
1368 15068 : if (!ptr) return NULL;
1369 :
1370 15061 : memset(ptr, 0, (size_t)size);
1371 15061 : return ptr;
1372 15068 : }
1373 :
1374 14405 : void *dbmem_realloc (void *ptr, uint64_t new_size) {
1375 14405 : return sqlite3_realloc64(ptr, (sqlite3_uint64)new_size);
1376 : }
1377 :
1378 39656 : char *dbmem_vmprintf (const char *format, va_list list) {
1379 39656 : return sqlite3_vmprintf(format, list);
1380 : }
1381 :
1382 39656 : char *dbmem_mprintf(const char *format, ...) {
1383 : va_list ap;
1384 : char *z;
1385 :
1386 39656 : va_start(ap, format);
1387 39656 : z = dbmem_vmprintf(format, ap);
1388 39656 : va_end(ap);
1389 :
1390 39656 : return z;
1391 : }
1392 :
1393 191076 : void dbmem_free (void *ptr) {
1394 191076 : sqlite3_free(ptr);
1395 191076 : }
1396 :
1397 0 : uint64_t dbmem_size (void *ptr) {
1398 0 : return (uint64_t)sqlite3_msize(ptr);
1399 : }
1400 :
1401 :
|
