void* csync_db_begin(const char *err, const char *fmt, ...) { db_stmt_p stmt = NULL; char *sql; va_list ap; int rc, busyc = 0; char *ppTail; va_start(ap, fmt); VASPRINTF(&sql, fmt, ap); va_end(ap); in_sql_query++; csync_db_maybegin(); csync_debug(2, "SQL: %s\n", sql); while (1) { rc = db_prepare_stmt(db, sql, &stmt, &ppTail); if ( rc != DB_BUSY ) break; if (busyc++ > get_dblock_timeout()) { db = 0; csync_fatal(DEADLOCK_MESSAGE); } csync_debug(2, "Database is busy, sleeping a sec.\n"); sleep(1); } if ( rc != DB_OK && err ) csync_fatal("Database Error: %s [%d]: %s on executing %s\n", err, rc, db_errmsg(db), sql); free(sql); return stmt; }
static void db_vacuum (void) { sqlite3_stmt *stmt; gint res, page_count, freelist_count; /* Determine fragmentation ratio using PRAGMA page_count PRAGMA freelist_count as suggested by adriatic in this blog post http://jeff.ecchi.ca/blog/2011/12/24/investigating-lifereas-startup-performance/#comment-19989 and perform VACUUM only when needed. */ db_prepare_stmt (&stmt, "PRAGMA page_count"); sqlite3_reset (stmt); res = sqlite3_step (stmt); if (SQLITE_ROW != res) g_error ("Could not determine page count (error code %d)!", res); page_count = sqlite3_column_int (stmt, 0); sqlite3_finalize (stmt); db_prepare_stmt (&stmt, "PRAGMA freelist_count"); sqlite3_reset (stmt); res = sqlite3_step (stmt); if (SQLITE_ROW != res) g_error ("Could not determine free list count (error code %d)!", res); freelist_count = sqlite3_column_int (stmt, 0); sqlite3_finalize (stmt); float fragmentation = (100 * (float)freelist_count/page_count); if (fragmentation > VACUUM_ON_FRAGMENTATION_RATIO) { debug2 (DEBUG_DB, "Performing VACUUM as freelist count/page count ratio %2.2f > %d", fragmentation, VACUUM_ON_FRAGMENTATION_RATIO); debug_start_measurement (DEBUG_DB); db_exec ("VACUUM;"); debug_end_measurement (DEBUG_DB, "VACUUM"); } else { debug2 (DEBUG_DB, "No VACUUM as freelist count/page count ratio %2.2f <= %d", fragmentation, VACUUM_ON_FRAGMENTATION_RATIO); } }
static sqlite3_stmt * db_get_statement (const gchar *name) { sqlite3_stmt *statement; gchar *sql; sql = (gchar *) g_hash_table_lookup (statements, name); db_prepare_stmt (&statement, sql); if (!statement) g_error ("Fatal: unknown prepared statement \"%s\" requested!", name); sqlite3_reset (statement); return statement; }
static gboolean db_table_exists (const gchar *name) { gchar *sql; sqlite3_stmt *stmt; gint res; sql = sqlite3_mprintf ("SELECT COUNT(type) FROM sqlite_master WHERE type = 'table' AND name = '%s';", name); db_prepare_stmt (&stmt, sql); sqlite3_reset (stmt); sqlite3_step (stmt); res = sqlite3_column_int (stmt, 0); sqlite3_finalize (stmt); sqlite3_free (sql); return (1 == res); }
static gint db_get_schema_version (void) { guint schemaVersion; sqlite3_stmt *stmt; if (!db_table_exists ("info")) { db_exec ("CREATE TABLE info ( " " name TEXT, " " value TEXT, " " PRIMARY KEY (name) " ");"); db_set_schema_version (-1); } db_prepare_stmt (&stmt, "SELECT value FROM info WHERE name = 'schemaVersion'"); sqlite3_step (stmt); schemaVersion = sqlite3_column_int (stmt, 0); sqlite3_finalize (stmt); return schemaVersion; }