#include <inttypes.h>

#include <stdbool.h>

#include <limits.h>

#include <stdint.h>

#include <stdlib.h>

#include <string.h>

#include <assert.h>

#include <stdio.h>

#include <errno.h>

#include <math.h>

#include "cloudsync.h"

#include "cloudsync_private.h"

#include "lz4.h"

#include "pk.h"

#include "vtab.h"

#include "utils.h"

#include "dbutils.h"

#ifndef CLOUDSYNC_OMIT_NETWORK

#include "network.h"

#ifdef _WIN32

#include <winsock2.h>

#include <ws2tcpip.h>

#include <arpa/inet.h> // for htonl, htons, ntohl, ntohs

#include <netinet/in.h> // for struct sockaddr_in, INADDR_ANY, etc. (if needed)

#ifndef htonll

#if __BIG_ENDIAN__

#define htonll(x) (x)

#define ntohll(x) (x)

#ifndef htobe64

#define htonll(x) ((uint64_t)htonl((x) & 0xFFFFFFFF) << 32 | (uint64_t)htonl((x) >> 32))

#define ntohll(x) ((uint64_t)ntohl((x) & 0xFFFFFFFF) << 32 | (uint64_t)ntohl((x) >> 32))

#define htonll(x) htobe64(x)

#define ntohll(x) be64toh(x)

#ifndef SQLITE_CORE

#ifndef UNUSED_PARAMETER

#define UNUSED_PARAMETER(X) (void)(X)

#ifdef _WIN32

#define APIEXPORT __declspec(dllexport)

#define APIEXPORT

#define CLOUDSYNC_DEFAULT_ALGO "cls"

#define CLOUDSYNC_INIT_NTABLES 128

#define CLOUDSYNC_VALUE_NOTSET -1

#define CLOUDSYNC_MIN_DB_VERSION 0

#define CLOUDSYNC_PAYLOAD_MINBUF_SIZE 512*1024

#define CLOUDSYNC_PAYLOAD_VERSION 1

#define CLOUDSYNC_PAYLOAD_SIGNATURE 'CLSY'

#define CLOUDSYNC_PAYLOAD_APPLY_CALLBACK_KEY "cloudsync_payload_apply_callback"

#ifndef MAX

#define MAX(a, b) (((a)>(b))?(a):(b))

#define DEBUG_SQLITE_ERROR(_rc, _fn, _db) do {if (_rc != SQLITE_OK) printf("Error in %s: %s\n", _fn, sqlite3_errmsg(_db));} while (0)

typedef enum {

CLOUDSYNC_PK_INDEX_TBL = 0,

CLOUDSYNC_PK_INDEX_PK = 1,

CLOUDSYNC_PK_INDEX_COLNAME = 2,

CLOUDSYNC_PK_INDEX_COLVALUE = 3,

CLOUDSYNC_PK_INDEX_COLVERSION = 4,

CLOUDSYNC_PK_INDEX_DBVERSION = 5,

CLOUDSYNC_PK_INDEX_SITEID = 6,

CLOUDSYNC_PK_INDEX_CL = 7,

CLOUDSYNC_PK_INDEX_SEQ = 8

} CLOUDSYNC_PK_INDEX;

typedef enum {

CLOUDSYNC_STMT_VALUE_ERROR = -1,

CLOUDSYNC_STMT_VALUE_UNCHANGED = 0,

CLOUDSYNC_STMT_VALUE_CHANGED = 1,

} CLOUDSYNC_STMT_VALUE;

typedef struct {

sqlite3_context *context;

int index;

} cloudsync_pk_decode_context;

#define SYNCBIT_SET(_data) _data->insync = 1

#define SYNCBIT_RESET(_data) _data->insync = 0

#define BUMP_SEQ(_data) ((_data)->seq += 1, (_data)->seq - 1)

typedef struct {

table_algo algo; // CRDT algoritm associated to the table

char *name; // table name

char **col_name; // array of column names

sqlite3_stmt **col_merge_stmt; // array of merge insert stmt (indexed by col_name)

sqlite3_stmt **col_value_stmt; // array of column value stmt (indexed by col_name)

int *col_id; // array of column id

int ncols; // number of non primary key cols

int npks; // number of primary key cols

bool enabled; // flag to check if a table is enabled or disabled

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

bool rowid_only; // a table with no primary keys other than the implicit rowid

#endif

char **pk_name; // array of primary key names

// precompiled statements

sqlite3_stmt *meta_pkexists_stmt; // check if a primary key already exist in the augmented table

sqlite3_stmt *meta_sentinel_update_stmt; // update a local sentinel row

sqlite3_stmt *meta_sentinel_insert_stmt; // insert a local sentinel row

sqlite3_stmt *meta_row_insert_update_stmt; // insert/update a local row

sqlite3_stmt *meta_row_drop_stmt; // delete rows from meta

sqlite3_stmt *meta_update_move_stmt; // update rows in meta when pk changes

sqlite3_stmt *meta_local_cl_stmt; // compute local cl value

sqlite3_stmt *meta_winner_clock_stmt; // get the rowid of the last inserted/updated row in the meta table

sqlite3_stmt *meta_merge_delete_drop;

sqlite3_stmt *meta_zero_clock_stmt;

sqlite3_stmt *meta_col_version_stmt;

sqlite3_stmt *meta_site_id_stmt;

sqlite3_stmt *real_col_values_stmt; // retrieve all column values based on pk

sqlite3_stmt *real_merge_delete_stmt;

sqlite3_stmt *real_merge_sentinel_stmt;

} cloudsync_table_context;

struct cloudsync_pk_decode_bind_context {

};

struct cloudsync_context {

};

typedef struct {

char *buffer;

size_t balloc;

size_t bused;

uint64_t nrows;

uint16_t ncols;

} cloudsync_network_payload;

#ifdef _MSC_VER

#pragma pack(push, 1) // For MSVC: pack struct with 1-byte alignment

#define PACKED

#define PACKED __attribute__((__packed__))

typedef struct PACKED {

} cloudsync_network_header;

#ifdef _MSC_VER

#pragma pack(pop)

#if CLOUDSYNC_UNITTEST

bool force_uncompressed_blob = false;

#define CHECK_FORCE_UNCOMPRESSED_BUFFER() if (force_uncompressed_blob) use_uncompressed_buffer = true

#define CHECK_FORCE_UNCOMPRESSED_BUFFER()

int db_version_rebuild_stmt (sqlite3 *db, cloudsync_context *data);

int cloudsync_load_siteid (sqlite3 *db, cloudsync_context *data);

int local_mark_insert_or_update_meta (sqlite3 *db, cloudsync_table_context *table, const char *pk, size_t pklen, const char *col_name, sqlite3_int64 db_version, int seq);

CLOUDSYNC_STMT_VALUE stmt_execute (sqlite3_stmt *stmt, cloudsync_context *data) {

int rc = sqlite3_step(stmt);

if (rc != SQLITE_ROW && rc != SQLITE_DONE) {

if (data) DEBUG_SQLITE_ERROR(rc, "stmt_execute", sqlite3_db_handle(stmt));

sqlite3_reset(stmt);

return CLOUDSYNC_STMT_VALUE_ERROR;

}

CLOUDSYNC_STMT_VALUE result = CLOUDSYNC_STMT_VALUE_CHANGED;

if (stmt == data->data_version_stmt) {

int version = sqlite3_column_int(stmt, 0);

if (version != data->data_version) {

data->data_version = version;

} else {

result = CLOUDSYNC_STMT_VALUE_UNCHANGED;

}

} else if (stmt == data->schema_version_stmt) {

int version = sqlite3_column_int(stmt, 0);

if (version > data->schema_version) {

data->schema_version = version;

} else {

result = CLOUDSYNC_STMT_VALUE_UNCHANGED;

}

} else if (stmt == data->db_version_stmt) {

data->db_version = (rc == SQLITE_DONE) ? CLOUDSYNC_MIN_DB_VERSION : sqlite3_column_int64(stmt, 0);

}

sqlite3_reset(stmt);

return result;

}

int stmt_count (sqlite3_stmt *stmt, const char *value, size_t len, int type) {

int result = -1;

int rc = SQLITE_OK;

if (value) {

rc = (type == SQLITE_TEXT) ? sqlite3_bind_text(stmt, 1, value, (int)len, SQLITE_STATIC) : sqlite3_bind_blob(stmt, 1, value, (int)len, SQLITE_STATIC);

if (rc != SQLITE_OK) goto cleanup;

}

rc = sqlite3_step(stmt);

if (rc == SQLITE_DONE) {

result = 0;

rc = SQLITE_OK;

} else if (rc == SQLITE_ROW) {

result = sqlite3_column_int(stmt, 0);

rc = SQLITE_OK;

}

cleanup:

DEBUG_SQLITE_ERROR(rc, "stmt_count", sqlite3_db_handle(stmt));

sqlite3_reset(stmt);

return result;

}

sqlite3_stmt *stmt_reset (sqlite3_stmt *stmt) {

sqlite3_clear_bindings(stmt);

sqlite3_reset(stmt);

return NULL;

}

int stmts_add_tocontext (sqlite3 *db, cloudsync_context *data) {

DEBUG_DBFUNCTION("cloudsync_add_stmts");

if (data->data_version_stmt == NULL) {

const char *sql = "PRAGMA data_version;";

int rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &data->data_version_stmt, NULL);

DEBUG_STMT("data_version_stmt %p", data->data_version_stmt);

if (rc != SQLITE_OK) return rc;

DEBUG_SQL("data_version_stmt: %s", sql);

}

if (data->schema_version_stmt == NULL) {

const char *sql = "PRAGMA schema_version;";

int rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &data->schema_version_stmt, NULL);

DEBUG_STMT("schema_version_stmt %p", data->schema_version_stmt);

if (rc != SQLITE_OK) return rc;

DEBUG_SQL("schema_version_stmt: %s", sql);

}

if (data->getset_siteid_stmt == NULL) {

// get and set index of the site_id

// in SQLite, we can’t directly combine an INSERT and a SELECT to both insert a row and return an identifier (rowid) in a single statement,

// however, we can use a workaround by leveraging the INSERT statement with ON CONFLICT DO UPDATE and then combining it with RETURNING rowid

const char *sql = "INSERT INTO cloudsync_site_id (site_id) VALUES (?) ON CONFLICT(site_id) DO UPDATE SET site_id = site_id RETURNING rowid;";

int rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &data->getset_siteid_stmt, NULL);

DEBUG_STMT("getset_siteid_stmt %p", data->getset_siteid_stmt);

if (rc != SQLITE_OK) return rc;

DEBUG_SQL("getset_siteid_stmt: %s", sql);

}

return db_version_rebuild_stmt(db, data);

}

char *db_version_build_query (sqlite3 *db) {

// this function must be manually called each time tables changes

// because the query plan changes too and it must be re-prepared

// unfortunately there is no other way

// we need to execute a query like:

/*

// the good news is that the query can be computed in SQLite without the need to do any extra computation from the host language

const char *sql = "WITH table_names AS ("

"SELECT format('%w', name) as tbl_name "

"FROM sqlite_master "

"WHERE type='table' "

"AND name LIKE '%_cloudsync'"

"), "

"query_parts AS ("

"SELECT 'SELECT max(db_version) as version FROM \"' || tbl_name || '\"' as part FROM table_names"

"), "

"combined_query AS ("

"SELECT GROUP_CONCAT(part, ' UNION ALL ') || ' UNION SELECT value as version FROM cloudsync_settings WHERE key = ''pre_alter_dbversion''' as full_query FROM query_parts"

") "

"SELECT CONCAT('SELECT max(version) as version FROM (', full_query, ');') FROM combined_query;";

return dbutils_text_select(db, sql);

}

int db_version_rebuild_stmt (sqlite3 *db, cloudsync_context *data) {

if (data->db_version_stmt) {

sqlite3_finalize(data->db_version_stmt);

data->db_version_stmt = NULL;

}

sqlite3_int64 count = dbutils_table_settings_count_tables(db);

if (count == 0) return SQLITE_OK;

else if (count == -1) {

dbutils_context_result_error(data->sqlite_ctx, "%s", sqlite3_errmsg(db));

return SQLITE_ERROR;

}

char *sql = db_version_build_query(db);

if (!sql) return SQLITE_NOMEM;

DEBUG_SQL("db_version_stmt: %s", sql);

int rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &data->db_version_stmt, NULL);

DEBUG_STMT("db_version_stmt %p", data->db_version_stmt);

cloudsync_memory_free(sql);

return rc;

}

int db_version_rerun (sqlite3 *db, cloudsync_context *data) {

CLOUDSYNC_STMT_VALUE schema_changed = stmt_execute(data->schema_version_stmt, data);

if (schema_changed == CLOUDSYNC_STMT_VALUE_ERROR) return -1;

if (schema_changed == CLOUDSYNC_STMT_VALUE_CHANGED) {

int rc = db_version_rebuild_stmt(db, data);

if (rc != SQLITE_OK) return -1;

}

CLOUDSYNC_STMT_VALUE rc = stmt_execute(data->db_version_stmt, data);

if (rc == CLOUDSYNC_STMT_VALUE_ERROR) return -1;

return 0;

}

int db_version_check_uptodate (sqlite3 *db, cloudsync_context *data) {

// perform a PRAGMA data_version to check if some other process write any data

CLOUDSYNC_STMT_VALUE rc = stmt_execute(data->data_version_stmt, data);

if (rc == CLOUDSYNC_STMT_VALUE_ERROR) return -1;

// db_version is already set and there is no need to update it

if (data->db_version != CLOUDSYNC_VALUE_NOTSET && rc == CLOUDSYNC_STMT_VALUE_UNCHANGED) return 0;

return db_version_rerun(db, data);

}

sqlite3_int64 db_version_next (sqlite3 *db, cloudsync_context *data, sqlite3_int64 merging_version) {

int rc = db_version_check_uptodate(db, data);

if (rc != SQLITE_OK) return -1;

sqlite3_int64 result = data->db_version + 1;

if (result < data->pending_db_version) result = data->pending_db_version;

if (merging_version != CLOUDSYNC_VALUE_NOTSET && result < merging_version) result = merging_version;

data->pending_db_version = result;

return result;

}

void *cloudsync_get_auxdata (sqlite3_context *context) {

cloudsync_context *data = (context) ? (cloudsync_context *)sqlite3_user_data(context) : NULL;

return (data) ? data->aux_data : NULL;

}

void cloudsync_set_auxdata (sqlite3_context *context, void *xdata) {

cloudsync_context *data = (context) ? (cloudsync_context *)sqlite3_user_data(context) : NULL;

if (data) data->aux_data = xdata;

}

char *cloudsync_pk_context_tbl (cloudsync_pk_decode_bind_context *ctx, int64_t *tbl_len) {

*tbl_len = ctx->tbl_len;

return ctx->tbl;

}

void *cloudsync_pk_context_pk (cloudsync_pk_decode_bind_context *ctx, int64_t *pk_len) {

*pk_len = ctx->pk_len;

return (void *)ctx->pk;

}

char *cloudsync_pk_context_colname (cloudsync_pk_decode_bind_context *ctx, int64_t *colname_len) {

*colname_len = ctx->col_name_len;

return ctx->col_name;

}

int64_t cloudsync_pk_context_cl (cloudsync_pk_decode_bind_context *ctx) {

return ctx->cl;

}

int64_t cloudsync_pk_context_dbversion (cloudsync_pk_decode_bind_context *ctx) {

return ctx->db_version;

}

char *table_build_values_sql (sqlite3 *db, cloudsync_table_context *table) {

char *sql = NULL;

/*

// Unfortunately in SQLite column names (or table names) cannot be bound parameters in a SELECT statement

// otherwise we should have used something like SELECT 'SELECT ? FROM %w WHERE rowid=?';

char *singlequote_escaped_table_name = cloudsync_memory_mprintf("%q", table->name);

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

if (table->rowid_only) {

sql = memory_mprintf("WITH col_names AS (SELECT group_concat('\"' || format('%%w', name) || '\"', ',') AS cols FROM pragma_table_info('%q') WHERE pk=0 ORDER BY cid) SELECT 'SELECT ' || (SELECT cols FROM col_names) || ' FROM \"%w\" WHERE rowid=?;'", table->name, table->name);

goto process_process;

}

#endif

sql = cloudsync_memory_mprintf("WITH col_names AS (SELECT group_concat('\"' || format('%%w', name) || '\"', ',') AS cols FROM pragma_table_info('%q') WHERE pk=0 ORDER BY cid), pk_where AS (SELECT group_concat('\"' || format('%%w', name) || '\"', '=? AND ') || '=?' AS pk_clause FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk) SELECT 'SELECT ' || (SELECT cols FROM col_names) || ' FROM \"%w\" WHERE ' || (SELECT pk_clause FROM pk_where) || ';'", table->name, table->name, singlequote_escaped_table_name);

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

process_process:

cloudsync_memory_free(singlequote_escaped_table_name);

if (!sql) return NULL;

char *query = dbutils_text_select(db, sql);

cloudsync_memory_free(sql);

return query;

}

char *table_build_mergedelete_sql (sqlite3 *db, cloudsync_table_context *table) {

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

if (table->rowid_only) {

char *sql = memory_mprintf("DELETE FROM \"%w\" WHERE rowid=?;", table->name);

return sql;

}

#endif

char *singlequote_escaped_table_name = cloudsync_memory_mprintf("%q", table->name);

char *sql = cloudsync_memory_mprintf("WITH pk_where AS (SELECT group_concat('\"' || format('%%w', name) || '\"', '=? AND ') || '=?' AS pk_clause FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk) SELECT 'DELETE FROM \"%w\" WHERE ' || (SELECT pk_clause FROM pk_where) || ';'", table->name, singlequote_escaped_table_name);

cloudsync_memory_free(singlequote_escaped_table_name);

if (!sql) return NULL;

char *query = dbutils_text_select(db, sql);

cloudsync_memory_free(sql);

return query;

}

char *table_build_mergeinsert_sql (sqlite3 *db, cloudsync_table_context *table, const char *colname) {

char *sql = NULL;

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

if (table->rowid_only) {

if (colname == NULL) {

// INSERT OR IGNORE INTO customers (first_name,last_name) VALUES (?,?);

sql = memory_mprintf("INSERT OR IGNORE INTO \"%w\" (rowid) VALUES (?);", table->name);

} else {

// INSERT INTO customers (first_name,last_name,age) VALUES (?,?,?) ON CONFLICT DO UPDATE SET age=?;

sql = memory_mprintf("INSERT INTO \"%w\" (rowid, \"%w\") VALUES (?, ?) ON CONFLICT DO UPDATE SET \"%w\"=?;", table->name, colname, colname);

}

return sql;

}

#endif

char *singlequote_escaped_table_name = cloudsync_memory_mprintf("%q", table->name);

if (colname == NULL) {

// is sentinel insert

sql = cloudsync_memory_mprintf("WITH pk_where AS (SELECT group_concat('\"' || format('%%w', name) || '\"') AS pk_clause FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk), pk_bind AS (SELECT group_concat('?') AS pk_binding FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk) SELECT 'INSERT OR IGNORE INTO \"%w\" (' || (SELECT pk_clause FROM pk_where) || ') VALUES (' || (SELECT pk_binding FROM pk_bind) || ');'", table->name, table->name, singlequote_escaped_table_name);

} else {

char *singlequote_escaped_col_name = cloudsync_memory_mprintf("%q", colname);

sql = cloudsync_memory_mprintf("WITH pk_where AS (SELECT group_concat('\"' || format('%%w', name) || '\"') AS pk_clause FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk), pk_bind AS (SELECT group_concat('?') AS pk_binding FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk) SELECT 'INSERT INTO \"%w\" (' || (SELECT pk_clause FROM pk_where) || ',\"%w\") VALUES (' || (SELECT pk_binding FROM pk_bind) || ',?) ON CONFLICT DO UPDATE SET \"%w\"=?;'", table->name, table->name, singlequote_escaped_table_name, singlequote_escaped_col_name, singlequote_escaped_col_name);

cloudsync_memory_free(singlequote_escaped_col_name);

}

cloudsync_memory_free(singlequote_escaped_table_name);

if (!sql) return NULL;

char *query = dbutils_text_select(db, sql);

cloudsync_memory_free(sql);

return query;

}

char *table_build_value_sql (sqlite3 *db, cloudsync_table_context *table, const char *colname) {

char *colnamequote = dbutils_is_star_table(colname) ? "" : "\"";

#if !CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

if (table->rowid_only) {

char *sql = memory_mprintf("SELECT %s%w%s FROM \"%w\" WHERE rowid=?;", colnamequote, colname, colnamequote, table->name);

return sql;

}

#endif

// SELECT age FROM customers WHERE first_name=? AND last_name=?;

char *singlequote_escaped_table_name = cloudsync_memory_mprintf("%q", table->name);

char *singlequote_escaped_col_name = cloudsync_memory_mprintf("%q", colname);

char *sql = cloudsync_memory_mprintf("WITH pk_where AS (SELECT group_concat('\"' || format('%%w', name) || '\"', '=? AND ') || '=?' AS pk_clause FROM pragma_table_info('%q') WHERE pk>0 ORDER BY pk) SELECT 'SELECT %s%w%s FROM \"%w\" WHERE ' || (SELECT pk_clause FROM pk_where) || ';'", table->name, colnamequote, singlequote_escaped_col_name, colnamequote, singlequote_escaped_table_name);

cloudsync_memory_free(singlequote_escaped_col_name);

cloudsync_memory_free(singlequote_escaped_table_name);

if (!sql) return NULL;

char *query = dbutils_text_select(db, sql);

cloudsync_memory_free(sql);

return query;

}

cloudsync_table_context *table_create (const char *name, table_algo algo) {

DEBUG_DBFUNCTION("table_create %s", name);

cloudsync_table_context *table = (cloudsync_table_context *)cloudsync_memory_zeroalloc(sizeof(cloudsync_table_context));

if (!table) return NULL;

table->algo = algo;

table->name = cloudsync_string_dup(name, true);

if (!table->name) {

cloudsync_memory_free(table);

return NULL;

}

table->enabled = true;

return table;

}

void table_free (cloudsync_table_context *table) {

DEBUG_DBFUNCTION("table_free %s", (table) ? (table->name) : "NULL");

if (!table) return;

if (table->ncols > 0) {

if (table->col_name) {

for (int i=0; i<table->ncols; ++i) {

cloudsync_memory_free(table->col_name[i]);

}

cloudsync_memory_free(table->col_name);

}

if (table->col_merge_stmt) {

for (int i=0; i<table->ncols; ++i) {

sqlite3_finalize(table->col_merge_stmt[i]);

}

cloudsync_memory_free(table->col_merge_stmt);

}

if (table->col_value_stmt) {

for (int i=0; i<table->ncols; ++i) {

sqlite3_finalize(table->col_value_stmt[i]);

}

cloudsync_memory_free(table->col_value_stmt);

}

if (table->col_id) {

cloudsync_memory_free(table->col_id);

}

}

if (table->pk_name) sqlite3_free_table(table->pk_name);

if (table->name) cloudsync_memory_free(table->name);

if (table->meta_pkexists_stmt) sqlite3_finalize(table->meta_pkexists_stmt);

if (table->meta_sentinel_update_stmt) sqlite3_finalize(table->meta_sentinel_update_stmt);

if (table->meta_sentinel_insert_stmt) sqlite3_finalize(table->meta_sentinel_insert_stmt);

if (table->meta_row_insert_update_stmt) sqlite3_finalize(table->meta_row_insert_update_stmt);

if (table->meta_row_drop_stmt) sqlite3_finalize(table->meta_row_drop_stmt);

if (table->meta_update_move_stmt) sqlite3_finalize(table->meta_update_move_stmt);

if (table->meta_local_cl_stmt) sqlite3_finalize(table->meta_local_cl_stmt);

if (table->meta_winner_clock_stmt) sqlite3_finalize(table->meta_winner_clock_stmt);

if (table->meta_merge_delete_drop) sqlite3_finalize(table->meta_merge_delete_drop);

if (table->meta_zero_clock_stmt) sqlite3_finalize(table->meta_zero_clock_stmt);

if (table->meta_col_version_stmt) sqlite3_finalize(table->meta_col_version_stmt);

if (table->meta_site_id_stmt) sqlite3_finalize(table->meta_site_id_stmt);

if (table->real_col_values_stmt) sqlite3_finalize(table->real_col_values_stmt);

if (table->real_merge_delete_stmt) sqlite3_finalize(table->real_merge_delete_stmt);

if (table->real_merge_sentinel_stmt) sqlite3_finalize(table->real_merge_sentinel_stmt);

cloudsync_memory_free(table);

}

int table_add_stmts (sqlite3 *db, cloudsync_table_context *table, int ncols) {

int rc = SQLITE_OK;

char *sql = NULL;

// META TABLE statements

// 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));

// precompile the pk exists statement

// we do not need an index on the pk column because it is already covered by the fact that it is part of the prikeys

// EXPLAIN QUERY PLAN reports: SEARCH table_name USING PRIMARY KEY (pk=?)

sql = cloudsync_memory_mprintf("SELECT EXISTS(SELECT 1 FROM \"%w_cloudsync\" WHERE pk = ? LIMIT 1);", table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_pkexists_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_pkexists_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// precompile the update local sentinel statement

sql = cloudsync_memory_mprintf("UPDATE \"%w_cloudsync\" SET col_version = CASE col_version %% 2 WHEN 0 THEN col_version + 1 ELSE col_version + 2 END, db_version = ?, seq = ?, site_id = 0 WHERE pk = ? AND col_name = '%s';", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_sentinel_update_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_sentinel_update_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// precompile the insert local sentinel statement

sql = cloudsync_memory_mprintf("INSERT INTO \"%w_cloudsync\" (pk, col_name, col_version, db_version, seq, site_id) SELECT ?, '%s', 1, ?, ?, 0 WHERE 1 ON CONFLICT DO UPDATE SET col_version = CASE col_version %% 2 WHEN 0 THEN col_version + 1 ELSE col_version + 2 END, db_version = ?, seq = ?, site_id = 0;", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_sentinel_insert_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_sentinel_insert_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// precompile the insert/update local row statement

sql = cloudsync_memory_mprintf("INSERT INTO \"%w_cloudsync\" (pk, col_name, col_version, db_version, seq, site_id ) SELECT ?, ?, ?, ?, ?, 0 WHERE 1 ON CONFLICT DO UPDATE SET col_version = col_version + 1, db_version = ?, seq = ?, site_id = 0;", table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_row_insert_update_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_row_insert_update_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// precompile the delete rows from meta

sql = cloudsync_memory_mprintf("DELETE FROM \"%w_cloudsync\" WHERE pk=? AND col_name!='%s';", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_row_drop_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_row_drop_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// precompile the update rows from meta when pk changes

// see https://github.com/sqliteai/sqlite-sync/blob/main/docs/PriKey.md for more details

sql = cloudsync_memory_mprintf("UPDATE OR REPLACE \"%w_cloudsync\" SET pk=?, db_version=?, col_version=1, seq=cloudsync_seq(), site_id=0 WHERE (pk=? AND col_name!='%s');", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_update_move_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_update_move_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// local cl

sql = cloudsync_memory_mprintf("SELECT COALESCE((SELECT col_version FROM \"%w_cloudsync\" WHERE pk=? AND col_name='%s'), (SELECT 1 FROM \"%w_cloudsync\" WHERE pk=?));", table->name, CLOUDSYNC_TOMBSTONE_VALUE, table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_local_cl_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_local_cl_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// rowid of the last inserted/updated row in the meta table

sql = cloudsync_memory_mprintf("INSERT OR REPLACE INTO \"%w_cloudsync\" (pk, col_name, col_version, db_version, seq, site_id) VALUES (?, ?, ?, cloudsync_db_version_next(?), ?, ?) RETURNING ((db_version << 30) | seq);", table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_winner_clock_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_winner_clock_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

sql = cloudsync_memory_mprintf("DELETE FROM \"%w_cloudsync\" WHERE pk=? AND col_name!='%s';", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_merge_delete_drop: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_merge_delete_drop, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// zero clock

sql = cloudsync_memory_mprintf("UPDATE \"%w_cloudsync\" SET col_version = 0, db_version = cloudsync_db_version_next(?) WHERE pk=? AND col_name!='%s';", table->name, CLOUDSYNC_TOMBSTONE_VALUE);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_zero_clock_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_zero_clock_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// col_version

sql = cloudsync_memory_mprintf("SELECT col_version FROM \"%w_cloudsync\" WHERE pk=? AND col_name=?;", table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_col_version_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_col_version_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// site_id

sql = cloudsync_memory_mprintf("SELECT site_id FROM \"%w_cloudsync\" WHERE pk=? AND col_name=?;", table->name);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("meta_site_id_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->meta_site_id_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

// REAL TABLE statements

// precompile the get column value statement

if (ncols > 0) {

sql = table_build_values_sql(db, table);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("real_col_values_stmt: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->real_col_values_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

}

sql = table_build_mergedelete_sql(db, table);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("real_merge_delete: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->real_merge_delete_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

sql = table_build_mergeinsert_sql(db, table, NULL);

if (!sql) {rc = SQLITE_NOMEM; goto cleanup;}

DEBUG_SQL("real_merge_sentinel: %s", sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->real_merge_sentinel_stmt, NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) goto cleanup;

cleanup:

if (rc != SQLITE_OK) printf("table_add_stmts error: %s\n", sqlite3_errmsg(db));

return rc;

}

cloudsync_table_context *table_lookup (cloudsync_context *data, const char *table_name) {

DEBUG_DBFUNCTION("table_lookup %s", table_name);

for (int i=0; i<data->tables_count; ++i) {

const char *name = (data->tables[i]) ? data->tables[i]->name : NULL;

if ((name) && (strcasecmp(name, table_name) == 0)) {

return data->tables[i];

}

}

return NULL;

}

sqlite3_stmt *table_column_lookup (cloudsync_table_context *table, const char *col_name, bool is_merge, int *index) {

DEBUG_DBFUNCTION("table_column_lookup %s", col_name);

for (int i=0; i<table->ncols; ++i) {

if (strcasecmp(table->col_name[i], col_name) == 0) {

if (index) *index = i;

return (is_merge) ? table->col_merge_stmt[i] : table->col_value_stmt[i];

}

}

if (index) *index = -1;

return NULL;

}

int table_remove (cloudsync_context *data, const char *table_name) {

DEBUG_DBFUNCTION("table_remove %s", table_name);

for (int i=0; i<data->tables_count; ++i) {

const char *name = (data->tables[i]) ? data->tables[i]->name : NULL;

if ((name) && (strcasecmp(name, table_name) == 0)) {

data->tables[i] = NULL;

return i;

}

}

return -1;

}

int table_add_to_context_cb (void *xdata, int ncols, char **values, char **names) {

cloudsync_table_context *table = (cloudsync_table_context *)xdata;

sqlite3 *db = sqlite3_db_handle(table->meta_pkexists_stmt);

if (!db) return SQLITE_ERROR;

int index = table->ncols;

for (int i=0; i<ncols; i+=2) {

const char *name = values[i];

int cid = (int)strtol(values[i+1], NULL, 0);

table->col_id[index] = cid;

table->col_name[index] = cloudsync_string_dup(name, true);

if (!table->col_name[index]) return 1;

char *sql = table_build_mergeinsert_sql(db, table, name);

if (!sql) return SQLITE_NOMEM;

DEBUG_SQL("col_merge_stmt[%d]: %s", index, sql);

int rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->col_merge_stmt[index], NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) return rc;

if (!table->col_merge_stmt[index]) return SQLITE_MISUSE;

sql = table_build_value_sql(db, table, name);

if (!sql) return SQLITE_NOMEM;

DEBUG_SQL("col_value_stmt[%d]: %s", index, sql);

rc = sqlite3_prepare_v3(db, sql, -1, SQLITE_PREPARE_PERSISTENT, &table->col_value_stmt[index], NULL);

cloudsync_memory_free(sql);

if (rc != SQLITE_OK) return rc;

if (!table->col_value_stmt[index]) return SQLITE_MISUSE;

}

table->ncols += 1;

return 0;

}

bool table_add_to_context (sqlite3 *db, cloudsync_context *data, table_algo algo, const char *table_name) {

DEBUG_DBFUNCTION("cloudsync_context_add_table %s", table_name);

// check if table is already in the global context and in that case just return

cloudsync_table_context *table = table_lookup(data, table_name);

if (table) return true;

// is there any space available?

if (data->tables_alloc <= data->tables_count + 1) {

// realloc tables

cloudsync_table_context **clone = (cloudsync_table_context **)cloudsync_memory_realloc(data->tables, sizeof(cloudsync_table_context) * data->tables_alloc + CLOUDSYNC_INIT_NTABLES);

if (!clone) goto abort_add_table;

// reset new entries

for (int i=data->tables_alloc; i<data->tables_alloc + CLOUDSYNC_INIT_NTABLES; ++i) {

clone[i] = NULL;

}

// replace old ptr

data->tables = clone;

data->tables_alloc += CLOUDSYNC_INIT_NTABLES;

}

// setup a new table context

table = table_create(table_name, algo);

if (!table) return false;

// fill remaining metadata in the table

char *sql = cloudsync_memory_mprintf("SELECT count(*) FROM pragma_table_info('%q') WHERE pk>0;", table_name);

if (!sql) goto abort_add_table;

table->npks = (int)dbutils_int_select(db, sql);

cloudsync_memory_free(sql);

if (table->npks == -1) {

dbutils_context_result_error(data->sqlite_ctx, "%s", sqlite3_errmsg(db));

goto abort_add_table;

}

if (table->npks == 0) {

#if CLOUDSYNC_DISABLE_ROWIDONLY_TABLES

return false;

#else

table->rowid_only = true;

table->npks = 1; // rowid

#endif

}

sql = cloudsync_memory_mprintf("SELECT count(*) FROM pragma_table_info('%q') WHERE pk=0;", table_name);

if (!sql) goto abort_add_table;

int64_t ncols = (int64_t)dbutils_int_select(db, sql);

cloudsync_memory_free(sql);

if (ncols == -1) {

dbutils_context_result_error(data->sqlite_ctx, "%s", sqlite3_errmsg(db));

goto abort_add_table;

}

int rc = table_add_stmts(db, table, (int)ncols);

if (rc != SQLITE_OK) goto abort_add_table;

// a table with only pk(s) is totally legal

if (ncols > 0) {

table->col_name = (char **)cloudsync_memory_alloc((sqlite3_uint64)(sizeof(char *) * ncols));

if (!table->col_name) goto abort_add_table;

table->col_id = (int *)cloudsync_memory_alloc((sqlite3_uint64)(sizeof(int) * ncols));

if (!table->col_id) goto abort_add_table;

table->col_merge_stmt = (sqlite3_stmt **)cloudsync_memory_alloc((sqlite3_uint64)(sizeof(sqlite3_stmt *) * ncols));

if (!table->col_merge_stmt) goto abort_add_table;

table->col_value_stmt = (sqlite3_stmt **)cloudsync_memory_alloc((sqlite3_uint64)(sizeof(sqlite3_stmt *) * ncols));

if (!table->col_value_stmt) goto abort_add_table;

sql = cloudsync_memory_mprintf("SELECT name, cid FROM pragma_table_info('%q') WHERE pk=0 ORDER BY cid;", table_name);

if (!sql) goto abort_add_table;

int rc = sqlite3_exec(db, sql, table_add_to_context_cb, (void *)table, NULL);

cloudsync_memory_free(sql);

if (rc == SQLITE_ABORT) goto abort_add_table;

}

// lookup the first free slot

for (int i=0; i<data->tables_alloc; ++i) {

if (data->tables[i] == NULL) {

data->tables[i] = table;

if (i > data->tables_count - 1) ++data->tables_count;

break;

}

}

return true;

abort_add_table:

table_free(table);

return false;

}