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