static int create_table (struct gml_params *params, const char *table) { /* attempting to create the DB table */ struct gml_column *col; char sql[4192]; char sql2[1024]; int ret; char *err_msg = NULL; sqlite3_stmt *stmt; /* CREATE TABLE SQL statement */ sprintf (sql, "CREATE TABLE %s (\n", table); strcat (sql, "PkUID INTEGER PRIMARY KEY AUTOINCREMENT"); col = params->first; while (col) { switch (col->type) { case COLUMN_INT: sprintf (sql2, ",\n%s INTEGER", col->name); break; case COLUMN_FLOAT: sprintf (sql2, ",\n%s DOUBLE", col->name); break; default: sprintf (sql2, ",\n%s TEXT", col->name); break; }; if (*sql2 != '\0') { strcat (sql, sql2); } col = col->next; } strcat (sql, "\n)"); ret = sqlite3_exec (params->db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { fprintf (stderr, "CREATE TABLE '%s' error: %s\n", table, err_msg); sqlite3_free (err_msg); return 0; } sprintf (sql, "SELECT AddGeometryColumn('%s', 'geometry', %d", table, params->srid); switch (params->geometry_type) { case GEOM_POINT: strcat (sql, ", 'POINT'"); params->declared_type = GAIA_POINT; break; case GEOM_MULTIPOINT: strcat (sql, ", 'MULTIPOINT'"); params->declared_type = GAIA_MULTIPOINT; break; case GEOM_LINESTRING: strcat (sql, ", 'LINESTRING'"); params->declared_type = GAIA_LINESTRING; break; case GEOM_MULTILINESTRING: strcat (sql, ", 'MULTILINESTRING'"); params->declared_type = GAIA_MULTILINESTRING; break; case GEOM_POLYGON: strcat (sql, ", 'POLYGON'"); params->declared_type = GAIA_POLYGON; break; case GEOM_MULTIPOLYGON: strcat (sql, ", 'MULTIPOLYGON'"); params->declared_type = GAIA_MULTIPOLYGON; break; default: strcat (sql, ", 'GEOMETRYCOLLECTION'"); params->declared_type = GAIA_GEOMETRYCOLLECTION; break; } strcat (sql, ", 'XY')"); ret = sqlite3_exec (params->db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { fprintf (stderr, "ADD GEOMETRY COLUMN '%s' error: %s\n", table, err_msg); sqlite3_free (err_msg); return 0; } /* creating a prepared statemet for INSERST INTO */ sprintf (sql, "INSERT INTO %s (", table); strcat (sql, "PkUID"); col = params->first; while (col) { sprintf (sql2, ", %s", col->name); strcat (sql, sql2); col = col->next; } strcat (sql, ", geometry) VALUES (NULL"); col = params->first; while (col) { strcat (sql, ", ?"); col = col->next; } strcat (sql, ", ?)"); ret = sqlite3_prepare_v2 (params->db_handle, sql, strlen (sql), &stmt, NULL); if (ret != SQLITE_OK) { fprintf (stderr, "SQL error: %s\n%s\n", sql, sqlite3_errmsg (params->db_handle)); return 0; } params->stmt = stmt; /* the complete data load is handled as an unique SQL Transaction */ ret = sqlite3_exec (params->db_handle, "BEGIN", NULL, NULL, &err_msg); if (ret != SQLITE_OK) { fprintf (stderr, "BEGIN TRANSACTION error: %s\n", err_msg); sqlite3_free (err_msg); return 0; } return 1; }
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName, const QStringList& tagKeys ) { Q_UNUSED( tagKeys ); QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) ); for ( int i = 0; i < tagKeys.count(); ++i ) sqlInsertLine += QString( ",?" ); sqlInsertLine += ", GeomFromWKB(?, 4326))"; sqlite3_stmt* stmtInsert; if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, 0 ) != SQLITE_OK ) { mError = "Prepare SELECT FROM ways failed."; return; } QgsOSMWayIterator ways = listWays(); QgsOSMWay w; while (( w = ways.next() ).isValid() ) { QgsOSMTags t = tags( true, w.id() ); QgsPolyline polyline = wayPoints( w.id() ); if ( polyline.count() < 2 ) continue; // invalid way bool isArea = ( polyline.first() == polyline.last() ); // closed way? // some closed ways are not really areas if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) ) { if ( t.value( "area" ) != "yes" ) // even though "highway" is line by default, "area"="yes" may override that isArea = false; } if ( closed != isArea ) continue; // skip if it's not what we're looking for QgsGeometry* geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline ); int col = 0; sqlite3_bind_int64( stmtInsert, ++col, w.id() ); // tags for ( int i = 0; i < tagKeys.count(); ++i ) { if ( t.contains( tagKeys[i] ) ) sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT ); else sqlite3_bind_null( stmtInsert, ++col ); } sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), geom->wkbSize(), SQLITE_STATIC ); int insertRes = sqlite3_step( stmtInsert ); if ( insertRes != SQLITE_DONE ) { mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes ); break; } sqlite3_reset( stmtInsert ); sqlite3_clear_bindings( stmtInsert ); delete geom; } sqlite3_finalize( stmtInsert ); }
/* opening or creation of database */ void db_init (void) { gint res; debug_enter ("db_init"); db_open (); /* create info table/check versioning info */ debug1 (DEBUG_DB, "current DB schema version: %d", db_get_schema_version ()); if (-1 == db_get_schema_version ()) { /* no schema version available -> first installation without tables... */ db_set_schema_version (SCHEMA_TARGET_VERSION); /* nothing exists yet, tables will be created below */ } if (SCHEMA_TARGET_VERSION < db_get_schema_version ()) g_error ("Fatal: The cache database was created by a newer version of Liferea than this one!"); if (SCHEMA_TARGET_VERSION > db_get_schema_version ()) { /* do table migration */ if (db_get_schema_version () < 5) g_error ("This version of Liferea doesn't support migrating from such an old DB file!"); if (db_get_schema_version () == 5 || db_get_schema_version () == 6) { debug0 (DEBUG_DB, "dropping triggers in preparation of database migration"); db_exec ("BEGIN; " "DROP TRIGGER item_removal; " "DROP TRIGGER item_insert; " "END;"); } if (db_get_schema_version () == 5) { /* 1.4.9 -> 1.4.10 adding parent_item_id to itemset relation */ debug0 (DEBUG_DB, "migrating from schema version 5 to 6 (this drops all comments)"); db_exec ("BEGIN; " "DELETE FROM itemsets WHERE comment = 1; " "DELETE FROM items WHERE comment = 1; " "CREATE TEMPORARY TABLE itemsets_backup(item_id,node_id,read,comment); " "INSERT INTO itemsets_backup SELECT item_id,node_id,read,comment FROM itemsets; " "DROP TABLE itemsets; " "CREATE TABLE itemsets (" " item_id INTEGER," " parent_item_id INTEGER," " node_id TEXT," " read INTEGER," " comment INTEGER," " PRIMARY KEY (item_id, node_id)" "); " "INSERT INTO itemsets SELECT item_id,0,node_id,read,comment FROM itemsets_backup; " "DROP TABLE itemsets_backup; " "REPLACE INTO info (name, value) VALUES ('schemaVersion',6); " "END;"); } if (db_get_schema_version () == 6) { /* 1.4.15 -> 1.4.16 adding parent_node_id to itemset relation */ debug0 (DEBUG_DB, "migrating from schema version 6 to 7 (this drops all comments)"); db_exec ("BEGIN; " "DELETE FROM itemsets WHERE comment = 1; " "DELETE FROM items WHERE comment = 1; " "CREATE TEMPORARY TABLE itemsets_backup(item_id,node_id,read,comment); " "INSERT INTO itemsets_backup SELECT item_id,node_id,read,comment FROM itemsets; " "DROP TABLE itemsets; " "CREATE TABLE itemsets (" " item_id INTEGER," " parent_item_id INTEGER," " node_id TEXT," " parent_node_id TEXT," " read INTEGER," " comment INTEGER," " PRIMARY KEY (item_id, node_id)" "); " "INSERT INTO itemsets SELECT item_id,0,node_id,node_id,read,comment FROM itemsets_backup; " "DROP TABLE itemsets_backup; " "REPLACE INTO info (name, value) VALUES ('schemaVersion',7); " "END;"); } if (db_get_schema_version () == 7) { /* 1.7.1 -> 1.7.2 dropping the itemsets and attention_stats relation */ db_exec ("BEGIN; " "CREATE TEMPORARY TABLE items_backup(" " item_id, " " title, " " read, " " updated, " " popup, " " marked, " " source, " " source_id, " " valid_guid, " " description, " " date, " " comment_feed_id, " " comment); " "INSERT into items_backup SELECT ROWID, title, read, updated, popup, marked, source, source_id, valid_guid, description, date, comment_feed_id, comment FROM items; " "DROP TABLE items; " "CREATE TABLE items (" " item_id INTEGER," " parent_item_id INTEGER," " node_id TEXT," " parent_node_id TEXT," " title TEXT," " read INTEGER," " updated INTEGER," " popup INTEGER," " marked INTEGER," " source TEXT," " source_id TEXT," " valid_guid INTEGER," " description TEXT," " date INTEGER," " comment_feed_id INTEGER," " comment INTEGER," " PRIMARY KEY (item_id)" ");" "INSERT INTO items SELECT itemsets.item_id, parent_item_id, node_id, parent_node_id, title, itemsets.read, updated, popup, marked, source, source_id, valid_guid, description, date, comment_feed_id, itemsets.comment FROM items_backup JOIN itemsets ON itemsets.item_id = items_backup.item_id; " "DROP TABLE items_backup; " "DROP TABLE itemsets; " "REPLACE INTO info (name, value) VALUES ('schemaVersion',8); " "END;" ); db_exec ("DROP TABLE attention_stats"); /* this is unconditional, no checks and backups needed */ } if (db_get_schema_version () == 8) { gchar *sql; sqlite3_stmt *stmt; /* 1.7.3 -> 1.7.4 change search folder handling */ db_exec ("BEGIN; " "DROP TABLE view_state; " "DROP TABLE update_state; " "CREATE TABLE search_folder_items (" " node_id STRING," " item_id INTEGER," " PRIMARY KEY (node_id, item_id)" ");" "REPLACE INTO info (name, value) VALUES ('schemaVersion',9); " "END;" ); debug0 (DEBUG_DB, "Removing all views."); sql = sqlite3_mprintf("SELECT name FROM sqlite_master WHERE type='view';"); res = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL); sqlite3_free (sql); if (SQLITE_OK != res) { debug1 (DEBUG_DB, "Could not determine views (error=%d)", res); } else { sqlite3_reset (stmt); while (sqlite3_step (stmt) == SQLITE_ROW) { const gchar *viewName = sqlite3_column_text (stmt, 0) + strlen("view_"); gchar *copySql = g_strdup_printf("INSERT INTO search_folder_items (node_id, item_id) SELECT '%s',item_id FROM view_%s;", viewName, viewName); db_exec (copySql); db_view_remove (viewName); g_free (copySql); } sqlite3_finalize (stmt); } } if (db_get_schema_version () == 9) { /* A parent node id to search folder relation to allow cleanups */ db_exec ("BEGIN; " "DROP TABLE search_folder_items; " "CREATE TABLE search_folder_items (" " node_id STRING," " parent_node_id STRING," " item_id INTEGER," " PRIMARY KEY (node_id, item_id)" ");" "REPLACE INTO info (name, value) VALUES ('schemaVersion',10); " "END;" ); searchFolderRebuild = TRUE; } } if (SCHEMA_TARGET_VERSION != db_get_schema_version ()) g_error ("Fatal: DB schema version not up-to-date! Running with --debug-db could give some hints about the problem!"); /* Vacuuming... */ db_vacuum (); /* Schema creation */ debug_start_measurement (DEBUG_DB); db_begin_transaction (); /* 1. Create tables if they do not exist yet */ db_exec ("CREATE TABLE items (" " item_id INTEGER," " parent_item_id INTEGER," " node_id TEXT," /* FIXME: migrate node ids to real integers */ " parent_node_id TEXT," /* FIXME: migrate node ids to real integers */ " title TEXT," " read INTEGER," " updated INTEGER," " popup INTEGER," " marked INTEGER," " source TEXT," " source_id TEXT," " valid_guid INTEGER," " description TEXT," " date INTEGER," " comment_feed_id TEXT," " comment INTEGER," " PRIMARY KEY (item_id)" ");"); db_exec ("CREATE INDEX items_idx ON items (source_id);"); db_exec ("CREATE INDEX items_idx2 ON items (comment_feed_id);"); db_exec ("CREATE INDEX items_idx3 ON items (node_id);"); db_exec ("CREATE INDEX items_idx4 ON items (item_id);"); db_exec ("CREATE INDEX items_idx5 ON items (parent_item_id);"); db_exec ("CREATE INDEX items_idx6 ON items (parent_node_id);"); db_exec ("CREATE TABLE metadata (" " item_id INTEGER," " nr INTEGER," " key TEXT," " value TEXT," " PRIMARY KEY (item_id, nr)" ");"); db_exec ("CREATE INDEX metadata_idx ON metadata (item_id);"); db_exec ("CREATE TABLE subscription (" " node_id STRING," " source STRING," " orig_source STRING," " filter_cmd STRING," " update_interval INTEGER," " default_interval INTEGER," " discontinued INTEGER," " available INTEGER," " PRIMARY KEY (node_id)" ");"); db_exec ("CREATE TABLE subscription_metadata (" " node_id STRING," " nr INTEGER," " key TEXT," " value TEXT," " PRIMARY KEY (node_id, nr)" ");"); db_exec ("CREATE INDEX subscription_metadata_idx ON subscription_metadata (node_id);"); db_exec ("CREATE TABLE node (" " node_id STRING," " parent_id STRING," " title STRING," " type INTEGER," " expanded INTEGER," " view_mode INTEGER," " sort_column INTEGER," " sort_reversed INTEGER," " PRIMARY KEY (node_id)" ");"); db_exec ("CREATE TABLE search_folder_items (" " node_id STRING," " parent_node_id STRING," " item_id INTEGER," " PRIMARY KEY (node_id, item_id)" ");"); db_end_transaction (); debug_end_measurement (DEBUG_DB, "table setup"); /* 2. Removing old triggers */ db_exec ("DROP TRIGGER item_insert;"); db_exec ("DROP TRIGGER item_update;"); db_exec ("DROP TRIGGER item_removal;"); db_exec ("DROP TRIGGER subscription_removal;"); /* 3. Cleanup of DB */ /* Note: do not check on subscriptions here, as non-subscription node types (e.g. news bin) do contain items too. */ debug0 (DEBUG_DB, "Checking for items without a feed list node...\n"); db_exec ("DELETE FROM items WHERE comment = 0 AND node_id NOT IN " "(SELECT node_id FROM node);"); debug0 (DEBUG_DB, "Checking for comments without parent item...\n"); db_exec ("BEGIN; " " CREATE TEMP TABLE tmp_id ( id );" " INSERT INTO tmp_id SELECT item_id FROM items WHERE comment = 1 AND parent_item_id NOT IN (SELECT item_id FROM items WHERE comment = 0);" /* limit to 1000 items as it is very slow */ " DELETE FROM items WHERE item_id IN (SELECT id FROM tmp_id LIMIT 1000);" " DROP TABLE tmp_id;" "END;"); debug0 (DEBUG_DB, "Checking for search folder items without a feed list node...\n"); db_exec ("DELETE FROM search_folder_items WHERE parent_node_id NOT IN " "(SELECT node_id FROM node);"); debug0 (DEBUG_DB, "Checking for search folder items without a search folder...\n"); db_exec ("DELETE FROM search_folder_items WHERE node_id NOT IN " "(SELECT node_id FROM node);"); debug0 (DEBUG_DB, "Checking for search folder with comments...\n"); db_exec ("DELETE FROM search_folder_items WHERE comment = 1;"); debug0 (DEBUG_DB, "Checking for subscription metadata without node...\n"); db_exec ("DELETE FROM subscription_metadata WHERE node_id NOT IN " "(SELECT node_id FROM node);"); debug0 (DEBUG_DB, "DB cleanup finished. Continuing startup."); /* 4. Creating triggers (after cleanup so it is not slowed down by triggers) */ /* This trigger does explicitely not remove comments! */ db_exec ("CREATE TRIGGER item_removal DELETE ON items " "BEGIN " " DELETE FROM metadata WHERE item_id = old.item_id; " " DELETE FROM search_folder_items WHERE item_id = old.item_id; " "END;"); db_exec ("CREATE TRIGGER subscription_removal DELETE ON subscription " "BEGIN " " DELETE FROM node WHERE node_id = old.node_id; " " DELETE FROM subscription_metadata WHERE node_id = old.node_id; " " DELETE FROM search_folder_items WHERE parent_node_id = old.node_id; " "END;"); /* Note: view counting triggers are set up in the view preparation code (see db_view_create()) */ /* prepare statements */ db_new_statement ("itemsetLoadStmt", "SELECT item_id FROM items WHERE node_id = ?"); db_new_statement ("itemsetLoadOffsetStmt", "SELECT item_id FROM items WHERE comment = 0 LIMIT ? OFFSET ?"); db_new_statement ("itemsetReadCountStmt", "SELECT COUNT(item_id) FROM items " "WHERE read = 0 AND node_id = ?"); db_new_statement ("itemsetItemCountStmt", "SELECT COUNT(item_id) FROM items " "WHERE node_id = ?"); db_new_statement ("itemsetRemoveStmt", "DELETE FROM items WHERE item_id = ? OR (comment = 1 AND parent_item_id = ?)"); db_new_statement ("itemsetRemoveAllStmt", "DELETE FROM items WHERE node_id = ? OR (comment = 1 AND parent_node_id = ?)"); db_new_statement ("itemsetMarkAllPopupStmt", "UPDATE items SET popup = 0 WHERE node_id = ?"); db_new_statement ("itemLoadStmt", "SELECT " "title," "read," "updated," "popup," "marked," "source," "source_id," "valid_guid," "description," "date," "comment_feed_id," "comment," "item_id," "parent_item_id, " "node_id, " "parent_node_id " " FROM items WHERE item_id = ?"); db_new_statement ("itemUpdateStmt", "REPLACE INTO items (" "title," "read," "updated," "popup," "marked," "source," "source_id," "valid_guid," "description," "date," "comment_feed_id," "comment," "item_id," "parent_item_id," "node_id," "parent_node_id" ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); db_new_statement ("itemStateUpdateStmt", "UPDATE items SET read=?, marked=?, updated=? " "WHERE item_id=?"); db_new_statement ("duplicatesFindStmt", "SELECT item_id FROM items WHERE source_id = ?"); db_new_statement ("duplicateNodesFindStmt", "SELECT node_id FROM items WHERE item_id IN " "(SELECT item_id FROM items WHERE source_id = ?)"); db_new_statement ("duplicatesMarkReadStmt", "UPDATE items SET read = 1, updated = 0 WHERE source_id = ?"); db_new_statement ("metadataLoadStmt", "SELECT key,value,nr FROM metadata WHERE item_id = ? ORDER BY nr"); db_new_statement ("metadataUpdateStmt", "REPLACE INTO metadata (item_id,nr,key,value) VALUES (?,?,?,?)"); db_new_statement ("subscriptionUpdateStmt", "REPLACE INTO subscription (" "node_id," "source," "orig_source," "filter_cmd," "update_interval," "default_interval," "discontinued," "available" ") VALUES (?,?,?,?,?,?,?,?)"); db_new_statement ("subscriptionRemoveStmt", "DELETE FROM subscription WHERE node_id = ?"); db_new_statement ("subscriptionLoadStmt", "SELECT " "node_id," "source," "orig_source," "filter_cmd," "update_interval," "default_interval," "discontinued," "available " "FROM subscription"); db_new_statement ("subscriptionMetadataLoadStmt", "SELECT key,value,nr FROM subscription_metadata WHERE node_id = ? ORDER BY nr"); db_new_statement ("subscriptionMetadataUpdateStmt", "REPLACE INTO subscription_metadata (node_id,nr,key,value) VALUES (?,?,?,?)"); db_new_statement ("nodeUpdateStmt", "REPLACE INTO node (node_id,parent_id,title,type,expanded,view_mode,sort_column,sort_reversed) VALUES (?,?,?,?,?,?,?,?)"); db_new_statement ("itemUpdateSearchFoldersStmt", "REPLACE INTO search_folder_items (node_id, parent_node_id, item_id) VALUES (?,?,?)"); db_new_statement ("itemRemoveFromSearchFolderStmt", "DELETE FROM search_folder_items WHERE node_id =? AND item_id = ?;"); db_new_statement ("searchFolderLoadStmt", "SELECT item_id FROM search_folder_items WHERE node_id = ?;"); db_new_statement ("searchFolderCountStmt", "SELECT count(item_id) FROM search_folder_items WHERE node_id = ?;"); db_new_statement ("nodeIdListStmt", "SELECT node_id FROM node;"); db_new_statement ("nodeRemoveStmt", "DELETE FROM node WHERE node_id = ?;"); g_assert (sqlite3_get_autocommit (db)); debug_exit ("db_init"); }
int main(void){ int r; char* errmsg; sqlite3* conW; sqlite3* conR; r = sqlite3_open("/scratch/sinclair/tmp/write_database.sql", &conW); if(r) {printf("Can't open db: %s\n", sqlite3_errmsg(conW)); exit(1);} r = sqlite3_open("/scratch/sinclair/tmp/read_database.sql", &conR); if(r) {printf("Can't open db: %s\n", sqlite3_errmsg(conR)); exit(1);} r = sqlite3_exec(conW, "BEGIN", 0, 0, &errmsg); if(r!=SQLITE_OK) {printf("Can't start transaction: %s\n", errmsg); exit(1);} char *tables[]={ "table0", "table1", "table2", "table3", "table4", "table5", "table6", "table7", "table8", "table9"}; int n = 10; int j = 0; int i; char queryR[1024]; char queryW[1024]; sqlite3_stmt* stmR; sqlite3_stmt* stmW; for(i=0; i<n; i++){ snprintf(queryW, sizeof(queryW), "%s%s%s", "CREATE table '", tables[i], "' (one text, two text, three integer)"); r = sqlite3_exec(conW, queryW, 0, 0, &errmsg); if(r!=SQLITE_OK) {printf("Can't execute: %s\n", errmsg); exit(1);} snprintf(queryR, sizeof(queryR), "%s%s%s", "SELECT * from '", tables[i], "'"); r = sqlite3_prepare_v2(conR, queryR, -1, &stmR, 0); if(r!=SQLITE_OK) {printf("Can't prepare read: %s\n", sqlite3_errmsg(conR)); exit(1);} snprintf(queryW, sizeof(queryW), "%s%s%s", "INSERT into '", tables[i], "' values (?,?,?)"); r = sqlite3_prepare_v2(conW, queryW, -1, &stmW, 0); if(r!=SQLITE_OK) {printf("Can't prepare write: %s\n", sqlite3_errmsg(conW)); exit(1);} while (1){ j++; r = sqlite3_step(stmR); if (r == SQLITE_DONE) {break;} if (r != SQLITE_ROW) {printf("Can't step read statement (%d): %s\n", r, sqlite3_errmsg(conR)); exit(1);} r = sqlite3_bind_text(stmW, 1, sqlite3_column_text(stmR, 0), 6, 0); if(r!=SQLITE_OK) {printf("Row %d, can't bind first var of write statement (%d): %s\n", j, r, sqlite3_errmsg(conW)); exit(1);} r = sqlite3_bind_text(stmW, 2, sqlite3_column_text(stmR, 1), 6, 0); if(r!=SQLITE_OK) {printf("Row %d, can't bind second var of write statement (%d): %s\n", j, r, sqlite3_errmsg(conW)); exit(1);} r = sqlite3_bind_int( stmW, 3, sqlite3_column_int( stmR, 2) +1); if(r!=SQLITE_OK) {printf("Row %d, can't bind third var of write statement (%d): %s\n", j, r, sqlite3_errmsg(conW)); exit(1);} r = sqlite3_step(stmW); if(r!=SQLITE_DONE) {printf("Can't step on write statement (%d): %s\n", r, sqlite3_errmsg(conW)); exit(1);} r = sqlite3_reset(stmW); if(r!=SQLITE_OK) {printf("Can't reset the write statement (%d): %s\n", r, sqlite3_errmsg(conW)); exit(1);}}} sqlite3_close(conR); r = sqlite3_exec(conW, "COMMIT", 0, 0, &errmsg); if(r!=SQLITE_OK) {printf("Can't commit transaction: %s\n", errmsg); exit(1);} sqlite3_close(conW); return 0;}
int search_movies(movie_t* items, uint32_t* num_results, const char* terms) { char buffer[1024]; sqlite3_stmt* query; int retval; uint32_t count; if (terms == NULL) { strcpy(buffer, "SELECT COUNT(*) FROM movies"); } else { if ((sizeof(buffer)-50) < sizeof(terms)) { log_debug(ERROR,"search_movies(): terms exceeds buffer"); return MI_EXIT_ERROR; } strcpy(buffer, "SELECT COUNT(*) FROM movies WHERE "); strcat(buffer, terms); } log_debug(INFO,"search_movies(): starting query"); log_debug(INFO,buffer); if (sqlite3_prepare_v2(db_handle,buffer,-1,&query,NULL) != SQLITE_OK) { log_debug(ERROR,"search_movies(): error executing query"); log_debug(ERROR,sqlite3_errmsg(db_handle)); return MI_EXIT_ERROR; } retval = sqlite3_step(query); if (retval == SQLITE_ROW) *num_results = (uint32_t)sqlite3_column_int(query,0); else if (retval == SQLITE_DONE) { log_debug(INFO,"search_movies(): no results for query"); sqlite3_finalize(query); return MI_NO_RESULTS; } else { log_debug(ERROR,"search_movies(): unknown error"); log_debug(ERROR,sqlite3_errmsg(db_handle)); sqlite3_finalize(query); return MI_EXIT_ERROR; } if (*num_results == 0) { log_debug(INFO,"search_movies(): no results for query"); sqlite3_finalize(query); return MI_NO_RESULTS; } if (terms == NULL) { strpcy(buffer, "SELECT * FROM movies"); } else { strcpy(buffer, "SELECT * FROM movies WHERE "); strcat(buffer, terms); } log_debug(INFO,"search_movies(): starting query"); log_debug(INFO,buffer); if (sqlite3_prepare_v2(db_handle,buffer,-1,&query,NULL) != SQLITE_OK) { log_debug(ERROR,"search_movies(): error executing query"); log_debug(ERROR,sqlite3_errmsg(db_handle)); return MI_EXIT_ERROR; } items = malloc(sizeof(movie_t)*(*num_results)); count = 0; log_debug(INFO,"search_movies(): staring row processing"); while (1) { retval = sqlite3_step(query); if (retval == SQLITE_ROW) { items[count].code = (uint32_t)sqlite3_column_int(query,0); items[count].type = (medium_t)sqlite3_column_int(query,1); items[count].genre = (genre_t)sqlite3_column_int(query,2); strcpy(items[count].title, (char *)sqlite3_column_text(query,3)); strcpy(items[count].director,(char *)sqlite3_column_text(query,4)); strcpy(items[count].studio, (char *)sqlite3_column_text(query,5)); items[count].rating = (short)sqlite3_column_int(query,6); count++; sprintf(buffer,"search_movies(): loaded row %u", count); log_debug(INFO,buffer); } else if (retval == SQLITE_DONE) { /* all done */ log_debug(INFO,"search_movies(): row processing done"); sprintf(buffer,"%u rows processed out of %u rows actual",count,*num_results); log_debug(INFO,buffer); break; } else { /* error of some sort */ log_debug(ERROR,"search_movies: error during row processing"); log_debug(ERROR,sqlite3_errmsg(db_handle)); sprintf(buffer,"%u rows processed out of %u rows actual",count,*num_results); log_debug(INFO,buffer); *num_results = 0; free(items); items = NULL; sqlite3_finalize(query); return MI_EXIT_ERROR; } } sqlite3_finalize(query); *num_results = count; return MI_EXIT_OK; }
static GList* daemon_data_sqlite_data_real_GetLog (DaemonDataIDataAccess* base, const gchar* channel, const gchar* server, GError** error) { DaemonDataSqliteData * self; GList* result = NULL; gchar* _tmp0_; gchar* commandText; sqlite3_stmt* statement = NULL; sqlite3_stmt* _tmp1_ = NULL; gchar* _tmp2_; gchar* _tmp3_; gint _result_; GList* results; GError * _inner_error_ = NULL; self = (DaemonDataSqliteData*) base; g_return_val_if_fail (channel != NULL, NULL); g_return_val_if_fail (server != NULL, NULL); _tmp0_ = g_strdup ("SELECT * FROM Log WHERE Channel = @1 AND Server = @2 ORDER BY Timestam" \ "p DESC LIMIT 50"); commandText = _tmp0_; sqlite3_prepare_v2 (self->priv->_database, commandText, -1, &_tmp1_, NULL); _sqlite3_finalize0 (statement); statement = _tmp1_; _tmp2_ = g_strdup (channel); sqlite3_bind_text (statement, 1, _tmp2_, -1, g_free); _tmp3_ = g_strdup (server); sqlite3_bind_text (statement, 2, _tmp3_, -1, g_free); _result_ = 0; results = NULL; { gboolean _tmp4_; _tmp4_ = TRUE; while (TRUE) { gint _tmp5_; if (!_tmp4_) { if (!(_result_ == SQLITE_ROW)) { break; } } _tmp4_ = FALSE; _tmp5_ = sqlite3_step (statement); _result_ = _tmp5_; switch (_result_) { case SQLITE_DONE: { { break; } } case SQLITE_ROW: { { const gchar* _tmp6_ = NULL; gchar* _tmp7_; gchar* username; const gchar* _tmp8_ = NULL; gchar* _tmp9_; gchar* data; const gchar* _tmp10_ = NULL; gchar* _tmp11_; gchar* eventChannel; const gchar* _tmp12_ = NULL; gchar* _tmp13_; gchar* eventServer; gint64 _tmp14_; GDateTime* _tmp15_ = NULL; GDateTime* timestamp; gint _tmp16_; DaemonDataEventTypes type; DaemonEventsLogEvent* current; DaemonEventsLogEvent* _tmp21_; _tmp6_ = sqlite3_column_text (statement, 0); _tmp7_ = g_strdup (_tmp6_); username = _tmp7_; _tmp8_ = sqlite3_column_text (statement, 1); _tmp9_ = g_strdup (_tmp8_); data = _tmp9_; _tmp10_ = sqlite3_column_text (statement, 2); _tmp11_ = g_strdup (_tmp10_); eventChannel = _tmp11_; _tmp12_ = sqlite3_column_text (statement, 3); _tmp13_ = g_strdup (_tmp12_); eventServer = _tmp13_; _tmp14_ = sqlite3_column_int64 (statement, 4); _tmp15_ = daemon_helpers_date_time_converter_FromUnixTimestamp (_tmp14_); timestamp = _tmp15_; _tmp16_ = sqlite3_column_int (statement, 5); type = (DaemonDataEventTypes) _tmp16_; current = NULL; switch (type) { case DAEMON_DATA_EVENT_TYPES_Joined: { { DaemonEventsStatusEvent* _tmp17_ = NULL; _tmp17_ = daemon_events_status_event_new_WithTimestamp (username, DAEMON_EVENTS_STATUS_CHANGE_Join, eventChannel, eventServer, timestamp); _g_object_unref0 (current); current = (DaemonEventsLogEvent*) _tmp17_; break; } } case DAEMON_DATA_EVENT_TYPES_Left: { { DaemonEventsStatusEvent* _tmp18_ = NULL; _tmp18_ = daemon_events_status_event_new_WithTimestamp (username, DAEMON_EVENTS_STATUS_CHANGE_Leave, eventChannel, eventServer, timestamp); _g_object_unref0 (current); current = (DaemonEventsLogEvent*) _tmp18_; break; } } case DAEMON_DATA_EVENT_TYPES_ChangedName: { { DaemonEventsChangeNameEvent* _tmp19_ = NULL; _tmp19_ = daemon_events_change_name_event_new_WithTimestamp (username, data, eventChannel, eventServer, timestamp); _g_object_unref0 (current); current = (DaemonEventsLogEvent*) _tmp19_; break; } } case DAEMON_DATA_EVENT_TYPES_Message: { { DaemonEventsMessageEvent* _tmp20_ = NULL; _tmp20_ = daemon_events_message_event_new_WithTimestamp (username, data, eventChannel, eventServer, timestamp); _g_object_unref0 (current); current = (DaemonEventsLogEvent*) _tmp20_; break; } } default: break; } _tmp21_ = _g_object_ref0 (current); results = g_list_append (results, _tmp21_); _g_object_unref0 (current); _g_date_time_unref0 (timestamp); _g_free0 (eventServer); _g_free0 (eventChannel); _g_free0 (data); _g_free0 (username); break; } } default: { { const gchar* _tmp22_ = NULL; GError* _tmp23_ = NULL; _tmp22_ = sqlite3_errmsg (self->priv->_database); _tmp23_ = g_error_new_literal (DAEMON_DATA_DATA_ACCESS_ERROR, DAEMON_DATA_DATA_ACCESS_ERROR_ReadError, _tmp22_); _inner_error_ = _tmp23_; if (_inner_error_->domain == DAEMON_DATA_DATA_ACCESS_ERROR) { g_propagate_error (error, _inner_error_); __g_list_free__g_object_unref0_0 (results); _sqlite3_finalize0 (statement); _g_free0 (commandText); return NULL; } else { __g_list_free__g_object_unref0_0 (results); _sqlite3_finalize0 (statement); _g_free0 (commandText); g_critical ("file %s: line %d: uncaught error: %s (%s, %d)", __FILE__, __LINE__, _inner_error_->message, g_quark_to_string (_inner_error_->domain), _inner_error_->code); g_clear_error (&_inner_error_); return NULL; } } } } } } result = results; _sqlite3_finalize0 (statement); _g_free0 (commandText); return result; }
/** * This function is invoked as: * * _TOKENIZE('<token_table>', <data_row_id>, <data>, <delimiter>, * <use_token_index>, <data_tag>) * * If <use_token_index> is omitted, it is treated as 0. * If <data_tag> is omitted, it is treated as NULL. * * It will split <data> on each instance of <delimiter> and insert each token * into <token_table>. The following columns in <token_table> are used: * token TEXT, source INTEGER, token_index INTEGER, tag (any type) * The token_index column is not required if <use_token_index> is 0. * The tag column is not required if <data_tag> is NULL. * * One row is inserted for each token in <data>. * In each inserted row, 'source' is <data_row_id>. * In the first inserted row, 'token' is the hex collation key of * the entire <data> string, and 'token_index' is 0. * In each row I (where 1 <= I < N, and N is the number of tokens in <data>) * 'token' will be set to the hex collation key of the I:th token (0-based). * If <use_token_index> != 0, 'token_index' is set to I. * If <data_tag> is not NULL, 'tag' is set to <data_tag>. * * In other words, there will be one row for the entire string, * and one row for each token except the first one. * * The function returns the number of tokens generated. */ static void tokenize(sqlite3_context * context, int argc, sqlite3_value ** argv) { //LOGD("enter tokenize"); int err; int useTokenIndex = 0; int useDataTag = 0; if (!(argc >= 4 || argc <= 6)) { LOGE("Tokenize requires 4 to 6 arguments"); sqlite3_result_null(context); return; } if (argc > 4) { useTokenIndex = sqlite3_value_int(argv[4]); } if (argc > 5) { useDataTag = (sqlite3_value_type(argv[5]) != SQLITE_NULL); } sqlite3 * handle = sqlite3_context_db_handle(context); UCollator* collator = (UCollator*)sqlite3_user_data(context); char const * tokenTable = (char const *)sqlite3_value_text(argv[0]); if (tokenTable == NULL) { LOGE("tokenTable null"); sqlite3_result_null(context); return; } // Get or create the prepared statement for the insertions sqlite3_stmt * statement = (sqlite3_stmt *)sqlite3_get_auxdata(context, 0); if (!statement) { char const * tokenIndexCol = useTokenIndex ? ", token_index" : ""; char const * tokenIndexParam = useTokenIndex ? ", ?" : ""; char const * dataTagCol = useDataTag ? ", tag" : ""; char const * dataTagParam = useDataTag ? ", ?" : ""; char * sql = sqlite3_mprintf("INSERT INTO %s (token, source%s%s) VALUES (?, ?%s%s);", tokenTable, tokenIndexCol, dataTagCol, tokenIndexParam, dataTagParam); err = sqlite3_prepare_v2(handle, sql, -1, &statement, NULL); sqlite3_free(sql); if (err) { LOGE("prepare failed"); sqlite3_result_null(context); return; } // This binds the statement to the table it was compiled against, which is argv[0]. // If this function is ever called with a different table the finalizer will be called // and sqlite3_get_auxdata() will return null above, forcing a recompile for the new table. sqlite3_set_auxdata(context, 0, statement, tokenize_auxdata_delete); } else { // Reset the cached statement so that binding the row ID will work properly sqlite3_reset(statement); } // Bind the row ID of the source row int64_t rowID = sqlite3_value_int64(argv[1]); err = sqlite3_bind_int64(statement, 2, rowID); if (err != SQLITE_OK) { LOGE("bind failed"); sqlite3_result_null(context); return; } // Bind <data_tag> to the tag column if (useDataTag) { int dataTagParamIndex = useTokenIndex ? 4 : 3; err = sqlite3_bind_value(statement, dataTagParamIndex, argv[5]); if (err != SQLITE_OK) { LOGE("bind failed"); sqlite3_result_null(context); return; } } // Get the raw bytes for the string to tokenize // the string will be modified by following code // however, sqlite did not reuse the string, so it is safe to not dup it UChar * origData = (UChar *)sqlite3_value_text16(argv[2]); if (origData == NULL) { sqlite3_result_null(context); return; } // Get the raw bytes for the delimiter const UChar * delim = (const UChar *)sqlite3_value_text16(argv[3]); if (delim == NULL) { LOGE("can't get delimiter"); sqlite3_result_null(context); return; } UChar * token = NULL; UChar *state; int numTokens = 0; do { if (numTokens == 0) { token = origData; } // Reset the program so we can use it to perform the insert sqlite3_reset(statement); UErrorCode status = U_ZERO_ERROR; char keybuf[1024]; uint32_t result = ucol_getSortKey(collator, token, -1, (uint8_t*)keybuf, sizeof(keybuf)-1); if (result > sizeof(keybuf)) { // TODO allocate memory for this super big string LOGE("ucol_getSortKey needs bigger buffer %d", result); break; } uint32_t keysize = result-1; uint32_t base16Size = keysize*2; char *base16buf = (char*)malloc(base16Size); base16Encode(base16buf, keybuf, keysize); err = sqlite3_bind_text(statement, 1, base16buf, base16Size, SQLITE_STATIC); if (err != SQLITE_OK) { LOGE(" sqlite3_bind_text16 error %d", err); free(base16buf); break; } if (useTokenIndex) { err = sqlite3_bind_int(statement, 3, numTokens); if (err != SQLITE_OK) { LOGE(" sqlite3_bind_int error %d", err); free(base16buf); break; } } err = sqlite3_step(statement); free(base16buf); if (err != SQLITE_DONE) { LOGE(" sqlite3_step error %d", err); break; } numTokens++; if (numTokens == 1) { // first call u_strtok_r(origData, delim, &state); } } while ((token = u_strtok_r(NULL, delim, &state)) != NULL); sqlite3_result_int(context, numTokens); }
void GDataBase::open(std::string _filename){ isOpen = !sqlite3_open(_filename.c_str(), &db); sqlite3_prepare_v2(db, cmd_word_type.c_str(), -1, &stmt_word_type, &tail); }//open(string _filename)
void daemon_data_sqlite_data_LogMessageEvent (DaemonDataSqliteData* self, DaemonEventsMessageEvent* event, GError** error) { gchar* _tmp0_; gchar* commandText; sqlite3_stmt* statement = NULL; sqlite3_stmt* _tmp1_ = NULL; const gchar* _tmp2_ = NULL; gchar* _tmp3_; const gchar* _tmp4_ = NULL; gchar* _tmp5_; const gchar* _tmp6_ = NULL; gchar* _tmp7_; const gchar* _tmp8_ = NULL; gchar* _tmp9_; gint64 _tmp10_; gint _tmp11_; GError * _inner_error_ = NULL; g_return_if_fail (self != NULL); g_return_if_fail (event != NULL); _tmp0_ = g_strdup ("INSERT INTO Log (Username, Data, Channel, Server, Timestamp, Type) VAL" \ "UES (@1, @2, @3, @4, @5, @6)"); commandText = _tmp0_; sqlite3_prepare_v2 (self->priv->_database, commandText, -1, &_tmp1_, NULL); _sqlite3_finalize0 (statement); statement = _tmp1_; _tmp2_ = daemon_events_log_event_get_Username ((DaemonEventsLogEvent*) event); _tmp3_ = g_strdup (_tmp2_); sqlite3_bind_text (statement, 1, _tmp3_, -1, g_free); _tmp4_ = daemon_events_message_event_get_Message (event); _tmp5_ = g_strdup (_tmp4_); sqlite3_bind_text (statement, 2, _tmp5_, -1, g_free); _tmp6_ = daemon_events_log_event_get_Channel ((DaemonEventsLogEvent*) event); _tmp7_ = g_strdup (_tmp6_); sqlite3_bind_text (statement, 3, _tmp7_, -1, g_free); _tmp8_ = daemon_events_log_event_get_Server ((DaemonEventsLogEvent*) event); _tmp9_ = g_strdup (_tmp8_); sqlite3_bind_text (statement, 4, _tmp9_, -1, g_free); _tmp10_ = daemon_events_log_event_get_UnixTimestamp ((DaemonEventsLogEvent*) event); sqlite3_bind_int64 (statement, 5, _tmp10_); sqlite3_bind_int (statement, 6, (gint) DAEMON_DATA_EVENT_TYPES_Message); _tmp11_ = sqlite3_step (statement); if (_tmp11_ != SQLITE_DONE) { const gchar* _tmp12_ = NULL; GError* _tmp13_ = NULL; _tmp12_ = sqlite3_errmsg (self->priv->_database); _tmp13_ = g_error_new_literal (DAEMON_DATA_DATA_ACCESS_ERROR, DAEMON_DATA_DATA_ACCESS_ERROR_WriteError, _tmp12_); _inner_error_ = _tmp13_; if (_inner_error_->domain == DAEMON_DATA_DATA_ACCESS_ERROR) { g_propagate_error (error, _inner_error_); _sqlite3_finalize0 (statement); _g_free0 (commandText); return; } else { _sqlite3_finalize0 (statement); _g_free0 (commandText); g_critical ("file %s: line %d: uncaught error: %s (%s, %d)", __FILE__, __LINE__, _inner_error_->message, g_quark_to_string (_inner_error_->domain), _inner_error_->code); g_clear_error (&_inner_error_); return; } } _sqlite3_finalize0 (statement); _g_free0 (commandText); }
void QuickStoneSystem::initQuickStoneData(void) { std::string sql; int result; char *zErrorMsg = 0; CCASSERT(_sqlitedb, "sqlitedb==null"); // 查询表字段 sql = "select data1 from QuiteStone_Table"; result = sqlite3_exec(_sqlitedb, sql.c_str(), NULL, NULL, &zErrorMsg); // 表不存在 if (result != SQLITE_OK) { // 建表 sql.clear(); sql = "create table if not exists QuiteStone_Table("; for (int i = QuickStoneKey_F; i < QuickStoneKey_Max; i++) { sql.append("data"); sql.append(__String::createWithFormat("%d", i)->getCString()); sql.append(" integer"); if (i != QuickStoneKey_Max - 1) { sql.append(","); } } sql.append(")"); result = sqlite3_exec(_sqlitedb, sql.c_str(), NULL, NULL, &zErrorMsg); if (result == SQLITE_OK) { // 插入数据 sql.clear(); sql = sql.append("insert into QuiteStone_Table values ("); for (int i = QuickStoneKey_F; i < QuickStoneKey_Max; i++) { sql.append( StringUtils::format("%d", getQuickStoneDataWithKey((QuickStoneKey)i)->haveNum)); if (i != QuickStoneKey_Max - 1) { sql.append(", "); } } sql.append(")"); result = sqlite3_exec(_sqlitedb, sql.c_str(), NULL, NULL, &zErrorMsg); if (result != SQLITE_OK) CCLOG("insert err"); } } // 查询数据 sql.clear(); sql = sql.append("select "); for (int i = QuickStoneKey_F; i < QuickStoneKey_Max; i++) { sql.append("data"); sql.append(__String::createWithFormat("%d", i)->getCString()); if (i != QuickStoneKey_Max - 1) { sql.append(","); } } sql.append(" from QuiteStone_Table"); sqlite3_stmt *statement; if (sqlite3_prepare_v2(_sqlitedb, sql.c_str(), -1, &statement, nullptr) == SQLITE_OK) { // 初始化列表 while (sqlite3_step(statement) == SQLITE_ROW) { for (int i = QuickStoneKey_F; i < QuickStoneKey_Max; i++) { int num = sqlite3_column_int(statement, i - 1); getQuickStoneDataWithKey((QuickStoneKey)i)->haveNum = num; } } } sqlite3_finalize(statement); }
/******************************************* * 角色系统 *******************************************/ void GameRoleSystem::initRoleInfo( sqlite3* db ) { for ( int i = 0; i<3; i++ ) { _roleConfig[i] = CSV_MGR()->GetCsvRoleUpgrade()->GetByID( i+1 ); } std::string sql; int result; char *zErrorMsg = 0; // 查询表字段 sql = "select type from roleInfo_Table"; result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); // 表不存在 if ( result != SQLITE_OK ) { // CCASSERT( result == SQLITE_OK, "roleinfo table not existes"); sql="create table if not exists roleInfo_Table("\ "type integer"\ ",roleGrade integer"\ ",isunlock integer"\ ",roleExp integer"\ ",isOption integer" ")"; result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); if(result == SQLITE_OK) { for ( int i =0; i<3; i++ ) { // 插入数据 用户基础数据 sql.clear(); sql = __String::createWithFormat("insert into roleInfo_Table values( %d, %d, %d, %d, %d )", _roleConfig[i]->roleId, _roleConfig[i]->roleGrade, _roleConfig[i]->isLock, _roleConfig[i]->CurExp, _roleConfig[i]->isOption)->getCString(); result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); if(result!=SQLITE_OK) CCLOG("insert err"); } } } // 查询语句 sql = " select type, roleGrade, isunlock, roleExp, isOption from roleInfo_Table"; sqlite3_stmt *statement; if ( sqlite3_prepare_v2(db, sql.c_str(), -1, &statement, nullptr) == SQLITE_OK ) { // 初始化角色列表 while (sqlite3_step(statement) == SQLITE_ROW) { // 获得解锁状态 int id = sqlite3_column_int(statement, 0); int grade = sqlite3_column_int(statement, 1); int islock = sqlite3_column_int(statement, 2); int exp = sqlite3_column_int(statement, 3); int isOption = sqlite3_column_int(statement, 4); _roleConfig[id-1]->roleGrade = grade; _roleConfig[id-1]->CurExp = exp; _roleConfig[id-1]->isLock = islock; _roleConfig[id-1]->isOption = isOption; if ( 1 == isOption ) { // mRoleConfig.roleIndex = id; } } } sqlite3_finalize(statement); }
void GameBuySystem::initGoodsData( sqlite3* db ) { std::string sql; int result; char *zErrorMsg = 0; CCASSERT(db, "sqlitedb==null"); // 查询表字段 sql = "select data1 from initGoods_Table"; result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); // 表不存在 if ( result != SQLITE_OK ) { // CCASSERT( result == SQLITE_OK, "roleinfo table not existes"); // 建表 sql.clear(); sql="create table if not exists initGoods_Table("; for ( int i=BuyKey_Dazhao; i<BuyKey_Max; i++) { sql.append( "data" ); sql.append( __String::createWithFormat("%d", i)->getCString() ); sql.append( " integer"); if ( i != BuyKey_Max-1) { sql.append( ","); } } sql.append( ")"); result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); if(result == SQLITE_OK) { // 插入数据 sql.clear(); sql = sql.append( "insert into initGoods_Table values (" ); for ( int i=BuyKey_Dazhao; i<BuyKey_Max; i++) { sql.append( "0" ); if ( i != BuyKey_Max-1) { sql.append( ", "); } } sql.append( ")"); result=sqlite3_exec(db,sql.c_str(),NULL,NULL,&zErrorMsg); if(result!=SQLITE_OK) CCLOG("insert err"); } } // 查询数据 sql.clear(); sql = sql.append( "select " ); for ( int i=BuyKey_Dazhao; i<BuyKey_Max; i++) { sql.append( "data" ); sql.append( __String::createWithFormat("%d", i)->getCString() ); if ( i != BuyKey_Max-1) { sql.append( ","); } } sql.append( " from initGoods_Table"); sqlite3_stmt *statement; if ( sqlite3_prepare_v2(db, sql.c_str(), -1, &statement, nullptr) == SQLITE_OK ) { // 初始化列表 while (sqlite3_step(statement) == SQLITE_ROW) { for ( int i=BuyKey_Dazhao; i<BuyKey_Max; i++) { int num = sqlite3_column_int(statement, i-1); getBuyGoodsDataWithKey((BuyListKey )i)->number = num; } } } sqlite3_finalize(statement); }
int prepare_statement_vcf_query_fields(sqlite3 *db, sqlite3_stmt **stmt) { char sql[] = "INSERT INTO record_query_fields VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15)"; return sqlite3_prepare_v2(db, sql, strlen(sql) + 300, stmt, NULL); }
int MFTtest(struct MFT *u3, int countresult) { sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL; char *sql; int rc; int j; unsigned int i; char *buffer = (char *)malloc(500); memset(buffer, 0x00, sizeof(char)*500); int error = sqlite3_open("./case/info.db", &db); if(error) { fprintf(stderr, "DB접근이 어렵습니다. (오류 %s)\n", sqlite3_errmsg(db)); } fprintf(stdout, "DB연결 완료.\n"); // case를 -n인자로 받은 폴더 명 수정 필요. if(sqlite3_open("./case/info.db", &db) != SQLITE_OK) { fprintf(stderr, "DB접근이 어렵습니다. (오류 %s)\n", sqlite3_errmsg(db)); } //MFT 테이블 생성 sql = "CREATE TABLE IF NOT EXISTS MFT (FULLPATH TEXT , FN_AccessTm INT, FN_ModifiedTm INT, FN_ChangeTm INT, FN_CreatedTm INT, SI_AccessTm INT, SI_ModifiedTm INT, SI_ChangeTm INT, SI_CreatedTm INT);"; if( sqlite3_exec(db, sql, NULL, NULL, NULL) == SQLITE_OK) { fprintf(stderr, ">> SQLite Table creation Succeeded!\n"); } else { puts("테이블 생성에 실패했습니다."); exit(1); } //데이터 추가 코드. char* errorMsg = NULL; rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &errorMsg); fprintf(stderr, " Commit begin result : %s\n", errorMsg); sprintf (buffer,"INSERT INTO MFT(FULLPATH , FN_AccessTm, FN_ModifiedTm, FN_ChangeTm, FN_CreatedTm, SI_AccessTm, SI_ModifiedTm, SI_ChangeTm, SI_CreatedTm) VALUES ( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)"); if(sqlite3_prepare_v2(db, buffer, strlen(buffer), &stmt, NULL) == SQLITE_OK) { puts(">> Prepared Statement is ready : Succeeded!\n"); } else { puts("테이블 값 입력에 실패하였습니다."); } for( i=0; i<countresult; i++){ sqlite3_bind_text(stmt, 1, u3[i].PATH, strlen(u3[i].PATH), SQLITE_STATIC); sqlite3_bind_int(stmt, 2, (int)(u3[i].FN_atime)); sqlite3_bind_int(stmt, 3, (int)(u3[i].FN_mtime)); sqlite3_bind_int(stmt, 4, (int)(u3[i].FN_ctime)); sqlite3_bind_int(stmt, 5, (int)(u3[i].FN_crtime)); sqlite3_bind_int(stmt, 6, (int)(u3[i].SI_atime)); sqlite3_bind_int(stmt, 7, (int)(u3[i].SI_mtime)); sqlite3_bind_int(stmt, 8, (int)(u3[i].SI_ctime)); sqlite3_bind_int(stmt, 9, (int)(u3[i].SI_crtime)); if ( sqlite3_step(stmt) != SQLITE_DONE ) { fprintf(stderr, ">> SQLite Insert failed! \n"); } sqlite3_reset(stmt); } rc = sqlite3_exec(db, "COMMIT TRANSACTION;", NULL, NULL, &errorMsg); fprintf(stderr, " Commit result : %s\n", errorMsg); sqlite3_finalize(stmt); sqlite3_close(db); return 0; }
int main(int argc, char **argv){ sqlite3_int64 iBegin; /* Start time of this program */ int quietFlag = 0; /* True if --quiet or -q */ int verboseFlag = 0; /* True if --verbose or -v */ char *zInsSql = 0; /* SQL statement for --load-db or --load-sql */ int iFirstInsArg = 0; /* First argv[] to use for --load-db or --load-sql */ sqlite3 *db = 0; /* The open database connection */ sqlite3_stmt *pStmt; /* A prepared statement */ int rc; /* Result code from SQLite interface calls */ Blob *pSql; /* For looping over SQL scripts */ Blob *pDb; /* For looping over template databases */ int i; /* Loop index for the argv[] loop */ int onlySqlid = -1; /* --sqlid */ int onlyDbid = -1; /* --dbid */ int nativeFlag = 0; /* --native-vfs */ int rebuildFlag = 0; /* --rebuild */ int vdbeLimitFlag = 0; /* --limit-vdbe */ int timeoutTest = 0; /* undocumented --timeout-test flag */ int runFlags = 0; /* Flags sent to runSql() */ char *zMsg = 0; /* Add this message */ int nSrcDb = 0; /* Number of source databases */ char **azSrcDb = 0; /* Array of source database names */ int iSrcDb; /* Loop over all source databases */ int nTest = 0; /* Total number of tests performed */ char *zDbName = ""; /* Appreviated name of a source database */ const char *zFailCode = 0; /* Value of the TEST_FAILURE environment variable */ int cellSzCkFlag = 0; /* --cell-size-check */ int sqlFuzz = 0; /* True for SQL fuzz testing. False for DB fuzz */ int iTimeout = 120; /* Default 120-second timeout */ int nMem = 0; /* Memory limit */ char *zExpDb = 0; /* Write Databases to files in this directory */ char *zExpSql = 0; /* Write SQL to files in this directory */ void *pHeap = 0; /* Heap for use by SQLite */ iBegin = timeOfDay(); #ifdef __unix__ signal(SIGALRM, timeoutHandler); #endif g.zArgv0 = argv[0]; zFailCode = getenv("TEST_FAILURE"); for(i=1; i<argc; i++){ const char *z = argv[i]; if( z[0]=='-' ){ z++; if( z[0]=='-' ) z++; if( strcmp(z,"cell-size-check")==0 ){ cellSzCkFlag = 1; }else if( strcmp(z,"dbid")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); onlyDbid = integerValue(argv[++i]); }else if( strcmp(z,"export-db")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); zExpDb = argv[++i]; }else if( strcmp(z,"export-sql")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); zExpSql = argv[++i]; }else if( strcmp(z,"help")==0 ){ showHelp(); return 0; }else if( strcmp(z,"limit-mem")==0 ){ #if !defined(SQLITE_ENABLE_MEMSYS3) && !defined(SQLITE_ENABLE_MEMSYS5) fatalError("the %s option requires -DSQLITE_ENABLE_MEMSYS5 or _MEMSYS3", argv[i]); #else if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); nMem = integerValue(argv[++i]); #endif }else if( strcmp(z,"limit-vdbe")==0 ){ vdbeLimitFlag = 1; }else if( strcmp(z,"load-sql")==0 ){ zInsSql = "INSERT INTO xsql(sqltext) VALUES(CAST(readfile(?1) AS text))"; iFirstInsArg = i+1; break; }else if( strcmp(z,"load-db")==0 ){ zInsSql = "INSERT INTO db(dbcontent) VALUES(readfile(?1))"; iFirstInsArg = i+1; break; }else if( strcmp(z,"m")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); zMsg = argv[++i]; }else if( strcmp(z,"native-vfs")==0 ){ nativeFlag = 1; }else if( strcmp(z,"quiet")==0 || strcmp(z,"q")==0 ){ quietFlag = 1; verboseFlag = 0; }else if( strcmp(z,"rebuild")==0 ){ rebuildFlag = 1; }else if( strcmp(z,"result-trace")==0 ){ runFlags |= SQL_OUTPUT; }else if( strcmp(z,"sqlid")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); onlySqlid = integerValue(argv[++i]); }else if( strcmp(z,"timeout")==0 ){ if( i>=argc-1 ) fatalError("missing arguments on %s", argv[i]); iTimeout = integerValue(argv[++i]); }else if( strcmp(z,"timeout-test")==0 ){ timeoutTest = 1; #ifndef __unix__ fatalError("timeout is not available on non-unix systems"); #endif }else if( strcmp(z,"verbose")==0 || strcmp(z,"v")==0 ){ quietFlag = 0; verboseFlag = 1; runFlags |= SQL_TRACE; }else { fatalError("unknown option: %s", argv[i]); } }else{ nSrcDb++; azSrcDb = safe_realloc(azSrcDb, nSrcDb*sizeof(azSrcDb[0])); azSrcDb[nSrcDb-1] = argv[i]; } } if( nSrcDb==0 ) fatalError("no source database specified"); if( nSrcDb>1 ){ if( zMsg ){ fatalError("cannot change the description of more than one database"); } if( zInsSql ){ fatalError("cannot import into more than one database"); } } /* Process each source database separately */ for(iSrcDb=0; iSrcDb<nSrcDb; iSrcDb++){ rc = sqlite3_open(azSrcDb[iSrcDb], &db); if( rc ){ fatalError("cannot open source database %s - %s", azSrcDb[iSrcDb], sqlite3_errmsg(db)); } rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS db(\n" " dbid INTEGER PRIMARY KEY, -- database id\n" " dbcontent BLOB -- database disk file image\n" ");\n" "CREATE TABLE IF NOT EXISTS xsql(\n" " sqlid INTEGER PRIMARY KEY, -- SQL script id\n" " sqltext TEXT -- Text of SQL statements to run\n" ");" "CREATE TABLE IF NOT EXISTS readme(\n" " msg TEXT -- Human-readable description of this file\n" ");", 0, 0, 0); if( rc ) fatalError("cannot create schema: %s", sqlite3_errmsg(db)); if( zMsg ){ char *zSql; zSql = sqlite3_mprintf( "DELETE FROM readme; INSERT INTO readme(msg) VALUES(%Q)", zMsg); rc = sqlite3_exec(db, zSql, 0, 0, 0); sqlite3_free(zSql); if( rc ) fatalError("cannot change description: %s", sqlite3_errmsg(db)); } if( zInsSql ){ sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0, readfileFunc, 0, 0); rc = sqlite3_prepare_v2(db, zInsSql, -1, &pStmt, 0); if( rc ) fatalError("cannot prepare statement [%s]: %s", zInsSql, sqlite3_errmsg(db)); rc = sqlite3_exec(db, "BEGIN", 0, 0, 0); if( rc ) fatalError("cannot start a transaction"); for(i=iFirstInsArg; i<argc; i++){ sqlite3_bind_text(pStmt, 1, argv[i], -1, SQLITE_STATIC); sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); if( rc ) fatalError("insert failed for %s", argv[i]); } sqlite3_finalize(pStmt); rc = sqlite3_exec(db, "COMMIT", 0, 0, 0); if( rc ) fatalError("cannot commit the transaction: %s", sqlite3_errmsg(db)); rebuild_database(db); sqlite3_close(db); return 0; } if( zExpDb!=0 || zExpSql!=0 ){ sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0, writefileFunc, 0, 0); if( zExpDb!=0 ){ const char *zExDb = "SELECT writefile(printf('%s/db%06d.db',?1,dbid),dbcontent)," " dbid, printf('%s/db%06d.db',?1,dbid), length(dbcontent)" " FROM db WHERE ?2<0 OR dbid=?2;"; rc = sqlite3_prepare_v2(db, zExDb, -1, &pStmt, 0); if( rc ) fatalError("cannot prepare statement [%s]: %s", zExDb, sqlite3_errmsg(db)); sqlite3_bind_text64(pStmt, 1, zExpDb, strlen(zExpDb), SQLITE_STATIC, SQLITE_UTF8); sqlite3_bind_int(pStmt, 2, onlyDbid); while( sqlite3_step(pStmt)==SQLITE_ROW ){ printf("write db-%d (%d bytes) into %s\n", sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,3), sqlite3_column_text(pStmt,2)); } sqlite3_finalize(pStmt); } if( zExpSql!=0 ){ const char *zExSql = "SELECT writefile(printf('%s/sql%06d.txt',?1,sqlid),sqltext)," " sqlid, printf('%s/sql%06d.txt',?1,sqlid), length(sqltext)" " FROM xsql WHERE ?2<0 OR sqlid=?2;"; rc = sqlite3_prepare_v2(db, zExSql, -1, &pStmt, 0); if( rc ) fatalError("cannot prepare statement [%s]: %s", zExSql, sqlite3_errmsg(db)); sqlite3_bind_text64(pStmt, 1, zExpSql, strlen(zExpSql), SQLITE_STATIC, SQLITE_UTF8); sqlite3_bind_int(pStmt, 2, onlySqlid); while( sqlite3_step(pStmt)==SQLITE_ROW ){ printf("write sql-%d (%d bytes) into %s\n", sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,3), sqlite3_column_text(pStmt,2)); } sqlite3_finalize(pStmt); } sqlite3_close(db); return 0; } /* Load all SQL script content and all initial database images from the ** source db */ blobListLoadFromDb(db, "SELECT sqlid, sqltext FROM xsql", onlySqlid, &g.nSql, &g.pFirstSql); if( g.nSql==0 ) fatalError("need at least one SQL script"); blobListLoadFromDb(db, "SELECT dbid, dbcontent FROM db", onlyDbid, &g.nDb, &g.pFirstDb); if( g.nDb==0 ){ g.pFirstDb = safe_realloc(0, sizeof(Blob)); memset(g.pFirstDb, 0, sizeof(Blob)); g.pFirstDb->id = 1; g.pFirstDb->seq = 0; g.nDb = 1; sqlFuzz = 1; } /* Print the description, if there is one */ if( !quietFlag ){ zDbName = azSrcDb[iSrcDb]; i = strlen(zDbName) - 1; while( i>0 && zDbName[i-1]!='/' && zDbName[i-1]!='\\' ){ i--; } zDbName += i; sqlite3_prepare_v2(db, "SELECT msg FROM readme", -1, &pStmt, 0); if( pStmt && sqlite3_step(pStmt)==SQLITE_ROW ){ printf("%s: %s\n", zDbName, sqlite3_column_text(pStmt,0)); } sqlite3_finalize(pStmt); } /* Rebuild the database, if requested */ if( rebuildFlag ){ if( !quietFlag ){ printf("%s: rebuilding... ", zDbName); fflush(stdout); } rebuild_database(db); if( !quietFlag ) printf("done\n"); } /* Close the source database. Verify that no SQLite memory allocations are ** outstanding. */ sqlite3_close(db); if( sqlite3_memory_used()>0 ){ fatalError("SQLite has memory in use before the start of testing"); } /* Limit available memory, if requested */ if( nMem>0 ){ sqlite3_shutdown(); pHeap = malloc(nMem); if( pHeap==0 ){ fatalError("failed to allocate %d bytes of heap memory", nMem); } sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nMem, 128); } /* Register the in-memory virtual filesystem */ formatVfs(); inmemVfsRegister(); /* Run a test using each SQL script against each database. */ if( !verboseFlag && !quietFlag ) printf("%s:", zDbName); for(pSql=g.pFirstSql; pSql; pSql=pSql->pNext){ for(pDb=g.pFirstDb; pDb; pDb=pDb->pNext){ int openFlags; const char *zVfs = "inmem"; sqlite3_snprintf(sizeof(g.zTestName), g.zTestName, "sqlid=%d,dbid=%d", pSql->id, pDb->id); if( verboseFlag ){ printf("%s\n", g.zTestName); fflush(stdout); }else if( !quietFlag ){ static int prevAmt = -1; int idx = pSql->seq*g.nDb + pDb->id - 1; int amt = idx*10/(g.nDb*g.nSql); if( amt!=prevAmt ){ printf(" %d%%", amt*10); fflush(stdout); prevAmt = amt; } } createVFile("main.db", pDb->sz, pDb->a); openFlags = SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE; if( nativeFlag && pDb->sz==0 ){ openFlags |= SQLITE_OPEN_MEMORY; zVfs = 0; } rc = sqlite3_open_v2("main.db", &db, openFlags, zVfs); if( rc ) fatalError("cannot open inmem database"); if( cellSzCkFlag ) runSql(db, "PRAGMA cell_size_check=ON", runFlags); setAlarm(iTimeout); #ifndef SQLITE_OMIT_PROGRESS_CALLBACK if( sqlFuzz || vdbeLimitFlag ){ sqlite3_progress_handler(db, 100000, progressHandler, &vdbeLimitFlag); } #endif do{ runSql(db, (char*)pSql->a, runFlags); }while( timeoutTest ); setAlarm(0); sqlite3_close(db); if( sqlite3_memory_used()>0 ) fatalError("memory leak"); reformatVfs(); nTest++; g.zTestName[0] = 0; /* Simulate an error if the TEST_FAILURE environment variable is "5". ** This is used to verify that automated test script really do spot ** errors that occur in this test program. */ if( zFailCode ){ if( zFailCode[0]=='5' && zFailCode[1]==0 ){ fatalError("simulated failure"); }else if( zFailCode[0]!=0 ){ /* If TEST_FAILURE is something other than 5, just exit the test ** early */ printf("\nExit early due to TEST_FAILURE being set\n"); iSrcDb = nSrcDb-1; goto sourcedb_cleanup; } } } } if( !quietFlag && !verboseFlag ){ printf(" 100%% - %d tests\n", g.nDb*g.nSql); } /* Clean up at the end of processing a single source database */ sourcedb_cleanup: blobListFree(g.pFirstSql); blobListFree(g.pFirstDb); reformatVfs(); } /* End loop over all source databases */ if( !quietFlag ){ sqlite3_int64 iElapse = timeOfDay() - iBegin; printf("fuzzcheck: 0 errors out of %d tests in %d.%03d seconds\n" "SQLite %s %s\n", nTest, (int)(iElapse/1000), (int)(iElapse%1000), sqlite3_libversion(), sqlite3_sourceid()); } free(azSrcDb); free(pHeap); return 0; }
/* ** Run multiple commands of SQL. Similar to sqlite3_exec(), but does not ** stop if an error is encountered. */ static void runSql(sqlite3 *db, const char *zSql, unsigned runFlags){ const char *zMore; sqlite3_stmt *pStmt; while( zSql && zSql[0] ){ zMore = 0; pStmt = 0; sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zMore); if( zMore==zSql ) break; if( runFlags & SQL_TRACE ){ const char *z = zSql; int n; while( z<zMore && ISSPACE(z[0]) ) z++; n = (int)(zMore - z); while( n>0 && ISSPACE(z[n-1]) ) n--; if( n==0 ) break; if( pStmt==0 ){ printf("TRACE: %.*s (error: %s)\n", n, z, sqlite3_errmsg(db)); }else{ printf("TRACE: %.*s\n", n, z); } } zSql = zMore; if( pStmt ){ if( (runFlags & SQL_OUTPUT)==0 ){ while( SQLITE_ROW==sqlite3_step(pStmt) ){} }else{ int nCol = -1; while( SQLITE_ROW==sqlite3_step(pStmt) ){ int i; if( nCol<0 ){ nCol = sqlite3_column_count(pStmt); }else if( nCol>0 ){ printf("--------------------------------------------\n"); } for(i=0; i<nCol; i++){ int eType = sqlite3_column_type(pStmt,i); printf("%s = ", sqlite3_column_name(pStmt,i)); switch( eType ){ case SQLITE_NULL: { printf("NULL\n"); break; } case SQLITE_INTEGER: { printf("INT %s\n", sqlite3_column_text(pStmt,i)); break; } case SQLITE_FLOAT: { printf("FLOAT %s\n", sqlite3_column_text(pStmt,i)); break; } case SQLITE_TEXT: { printf("TEXT [%s]\n", sqlite3_column_text(pStmt,i)); break; } case SQLITE_BLOB: { printf("BLOB (%d bytes)\n", sqlite3_column_bytes(pStmt,i)); break; } } } } } sqlite3_finalize(pStmt); } } }
void makedb(int compress) { ssize_t nchars; char *line = 0; size_t len = 0; sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL; char *query; int rc = 0; int i = 0; int idx = 0; char *errmsg = NULL; FILE *file = fopen("./sample.txt", "r"); if (file == NULL) err(EXIT_FAILURE, "fopen failed"); remove(DBPATH); db = init_db(); if (compress) query = "CREATE VIRTUAL TABLE fts USING FTS4(data, i, tokenize=porter, " "compress=zip, uncompress=unzip)"; else query = "CREATE VIRTUAL TABLE fts USING FTS4(data, i, tokenize=porter)"; rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL); if (rc != SQLITE_OK) { warnx("sqlite3_prepare failed while creating db"); sqlite3_close(db); fclose(file); sqlite3_free(query); exit(EXIT_FAILURE); } sqlite3_step(stmt); sqlite3_finalize(stmt); sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg); if (errmsg) { warnx("%s", errmsg); fclose(file); sqlite3_close(db); free(errmsg); exit(EXIT_FAILURE); } while ((nchars = getline(&line, &len, file)) != -1) { query = "INSERT INTO fts VALUES(:line, :i)"; rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL); if (rc != SQLITE_OK) { warnx("sqlite3_prepare failed while inserting data"); warnx("%s", sqlite3_errmsg(db)); sqlite3_close(db); fclose(file); exit(EXIT_FAILURE); } idx = sqlite3_bind_parameter_index(stmt, ":line"); rc = sqlite3_bind_text(stmt, idx, line, -1, NULL); if (rc != SQLITE_OK) { warnx("%s", sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_close(db); fclose(file); exit(EXIT_FAILURE); } idx = sqlite3_bind_parameter_index(stmt, ":i"); rc = sqlite3_bind_int(stmt, idx, i); if (rc != SQLITE_OK) { warnx("%s", sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_close(db); fclose(file); exit(EXIT_FAILURE); } sqlite3_step(stmt); sqlite3_finalize(stmt); } sqlite3_exec(db, "END", NULL, NULL, &errmsg); if (errmsg) { warnx("%s", errmsg); free(errmsg); } fclose(file); sqlite3_close(db); }
int StoreRRTable(const cfac_t *cfac, sqlite3 *db, unsigned long int sid, FILE *fp, int swp) { int retval = 0; int rc; sqlite3_stmt *stmt; char *sql; sql = "INSERT INTO cstrengths" \ " (cid, e, strength)" \ " VALUES (?, ?, ?)"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, sid); sqlite3_bind_int(stmt, 2, CFACDB_CS_PI); sqlite3_exec(db, "BEGIN", 0, 0, 0); while (retval == 0) { RR_HEADER h; int n, i; n = ReadRRHeader(fp, &h, swp); if (n == 0) { break; } sqlite3_bind_int(stmt, 3, h.qk_mode); for (i = 0; i < h.ntransitions && retval == 0; i++) { RR_RECORD r; unsigned long int cid; int t; double ap0, ap1, ap2, ap3; n = ReadRRRecord(fp, &r, swp, &h); if (n == 0) { break; } if (h.qk_mode == QK_FIT && r.params[0]) { ap0 = r.params[0]; ap1 = r.params[1]; ap2 = r.params[2]; ap3 = r.params[3]; } else { ap0 = r.strength[h.n_usr - 1]* pow(h.usr_egrid[h.n_usr - 1], 3.5 + r.kl); ap1 = ap2 = ap3 = 0.0; } retval = StoreCTransition(db, sid, CFACDB_CS_PI, h.qk_mode, r.b, r.f, r.kl, ap0, ap1, ap2, ap3, &cid); if (retval != 0) { break; } sqlite3_bind_int(stmt, 1, cid); for (t = 0; t < h.n_usr; t++) { sqlite3_bind_double(stmt, 2, h.usr_egrid[t]); sqlite3_bind_double(stmt, 3, r.strength[t]); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); retval = -1; break; } sqlite3_reset(stmt); } free(r.params); free(r.strength); } free(h.tegrid); free(h.egrid); free(h.usr_egrid); } sqlite3_exec(db, "COMMIT", 0, 0, 0); sqlite3_finalize(stmt); return retval; }
// Executes a single query. // If transaction = TRUE, the query is assumed to be executed in a transaction // (which has already been initiated) // The return path (if any) and lastid (0 if not requested) are stored in *res // and *lastid. The caller of this function is responsible for sending back the // final response. If this function returns anything other than SQLITE_DONE, // the query has failed. // It is assumed that the first `flags' part of the queue item has already been // fetched. static int db_queue_process_one(sqlite3 *db, char *q, gboolean nocache, gboolean transaction, GAsyncQueue **res, gint64 *lastid) { char *query = darray_get_ptr(q); *res = NULL; *lastid = 0; // Would be nice to have the parameters logged g_debug("db: Executing \"%s\"", query); // Get statement handler int r = SQLITE_ROW; sqlite3_stmt *s; if(nocache ? sqlite3_prepare_v2(db, query, -1, &s, NULL) : db_queue_process_prepare(db, query, &s)) { g_critical("SQLite3 error preparing `%s': %s", query, sqlite3_errmsg(db)); r = SQLITE_ERROR; } // Bind parameters int t, n; int i = 1; char *a; while((t = darray_get_int32(q)) != DBQ_END && t != DBQ_RES) { if(r == SQLITE_ERROR) continue; switch(t) { case DBQ_NULL: sqlite3_bind_null(s, i); break; case DBQ_INT: sqlite3_bind_int(s, i, darray_get_int32(q)); break; case DBQ_INT64: sqlite3_bind_int64(s, i, darray_get_int64(q)); break; case DBQ_TEXT: sqlite3_bind_text(s, i, darray_get_string(q), -1, SQLITE_STATIC); break; case DBQ_BLOB: a = darray_get_dat(q, &n); sqlite3_bind_blob(s, i, a, n, SQLITE_STATIC); break; } i++; } // Fetch information about what results we need to send back gboolean wantlastid = FALSE; char columns[20]; // 20 should be enough for everyone n = 0; if(t == DBQ_RES) { *res = darray_get_ptr(q); while((t = darray_get_int32(q)) != DBQ_END) { if(t == DBQ_LASTID) wantlastid = TRUE; else columns[n++] = t; } } // Execute query while(r == SQLITE_ROW) { // do the step() if(transaction) r = sqlite3_step(s); else while((r = sqlite3_step(s)) == SQLITE_BUSY) ; if(r != SQLITE_DONE && r != SQLITE_ROW) g_critical("SQLite3 error on step() of `%s': %s", query, sqlite3_errmsg(db)); // continue with the next step() if we're not going to do anything with the results if(r != SQLITE_ROW || !*res || !n) continue; // send back a response GByteArray *rc = g_byte_array_new(); darray_init(rc); darray_add_int32(rc, r); for(i=0; i<n; i++) { switch(columns[i]) { case DBQ_INT: darray_add_int32( rc, sqlite3_column_int( s, i)); break; case DBQ_INT64: darray_add_int64( rc, sqlite3_column_int64(s, i)); break; case DBQ_TEXT: darray_add_string(rc, (char *)sqlite3_column_text( s, i)); break; case DBQ_BLOB: darray_add_dat( rc, sqlite3_column_blob( s, i), sqlite3_column_bytes(s, i)); break; default: g_warn_if_reached(); } } g_async_queue_push(*res, g_byte_array_free(rc, FALSE)); } // Fetch last id, if requested if(r == SQLITE_DONE && wantlastid) *lastid = sqlite3_last_insert_rowid(db); sqlite3_reset(s); if(nocache) sqlite3_finalize(s); return r; }
int StoreInit(const cfac_t *cfac, const char *fn, int reset, sqlite3 **db, unsigned long *sid) { int retval = 0; struct stat sb; sqlite3_stmt *stmt; int rc; const char *sql; char *errmsg; int need_truncate = 0, need_init = 0; *sid = (unsigned long) time(NULL); if (reset) { need_truncate = 1; need_init = 1; } if (stat(fn, &sb) == -1) { if (errno == ENOENT) { need_truncate = 0; need_init = 1; } else { perror("stat"); return -1; } } else { if (sb.st_size == 0) { need_truncate = 0; need_init = 1; } } if (need_truncate) { if (truncate(fn, 0)) { return -1; } } rc = sqlite3_open(fn, db); if (rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(*db)); sqlite3_close(*db); return -1; } rc = sqlite3_exec(*db, "PRAGMA foreign_keys = ON", NULL, NULL, &errmsg); if (rc != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(*db); return -1; } if (need_init) { int i = 0; while ((sql = schema_str[i])) { rc = sqlite3_exec(*db, sql, NULL, NULL, &errmsg); if (rc != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(*db); retval = -1; break; } i++; } sql = "INSERT INTO cfacdb" \ " (property, value)" \ " VALUES ('format', ?)"; sqlite3_prepare_v2(*db, sql, -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, CFACDB_FORMAT_VERSION); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(*db)); sqlite3_close(*db); retval = -1; } sqlite3_reset(stmt); } else { int db_format; sql = "SELECT value FROM cfacdb WHERE property = 'format'"; rc = sqlite3_exec(*db, sql, format_cb, &db_format, &errmsg); if (rc != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(*db); return -1; } if (db_format != CFACDB_FORMAT_VERSION) { fprintf(stderr, "Incompatible DB format %d, expected %d\n", db_format, CFACDB_FORMAT_VERSION); sqlite3_free(errmsg); sqlite3_close(*db); return -1; } } sql = "INSERT INTO sessions" \ " (sid, version, uta, cmdline)" \ " VALUES (?, ?, ?, '')"; sqlite3_prepare_v2(*db, sql, -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, *sid); sqlite3_bind_int(stmt, 2, 10000*CFAC_VERSION + 100*CFAC_SUBVERSION + CFAC_SUBSUBVERSION); sqlite3_bind_int(stmt, 3, cfac->uta ? 1:0); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(*db)); sqlite3_close(*db); retval = -1; } sqlite3_reset(stmt); sql = "INSERT INTO species (sid, symbol, anum, mass) VALUES (?, ?, ?, ?)"; sqlite3_prepare_v2(*db, sql, -1, &stmt, NULL); sqlite3_bind_int (stmt, 1, *sid); SQLITE3_BIND_STR (stmt, 2, cfac_get_atomic_symbol(cfac)); sqlite3_bind_int (stmt, 3, cfac_get_atomic_number(cfac)); sqlite3_bind_double(stmt, 4, cfac_get_atomic_mass(cfac)); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(*db)); sqlite3_close(*db); retval = -1; } sqlite3_finalize(stmt); return retval; }
int StoreCITable(sqlite3 *db, unsigned long int sid, FILE *fp, int swp) { int retval = 0; int rc; sqlite3_stmt *stmt; char *sql; sql = "INSERT INTO cstrengths" \ " (cid, e, strength)" \ " VALUES (?, ?, ?)"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); sqlite3_exec(db, "BEGIN", 0, 0, 0); while (retval == 0) { CI_HEADER h; int n, i; n = ReadCIHeader(fp, &h, swp); if (n == 0) { break; } for (i = 0; i < h.ntransitions && retval == 0; i++) { CI_RECORD r; unsigned long int cid; int t; n = ReadCIRecord(fp, &r, swp, &h); if (n == 0) { break; } retval = StoreCTransition(db, sid, CFACDB_CS_CI, h.qk_mode, r.b, r.f, r.kl, r.params[0], r.params[1], r.params[2], r.params[3], &cid); if (retval != 0) { break; } sqlite3_bind_int(stmt, 1, cid); for (t = 0; t < h.n_usr; t++) { sqlite3_bind_double(stmt, 2, h.usr_egrid[t]); sqlite3_bind_double(stmt, 3, r.strength[t]); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); retval = -1; break; } sqlite3_reset(stmt); } free(r.params); free(r.strength); } free(h.tegrid); free(h.egrid); free(h.usr_egrid); } sqlite3_exec(db, "COMMIT", 0, 0, 0); sqlite3_finalize(stmt); return retval; }
int StoreTRTable(sqlite3 *db, unsigned long int sid, FILE *fp, int swp) { int retval = 0; int rc; sqlite3_stmt *stmt; char *sql; sql = "INSERT INTO rtransitions" \ " (sid, ini_id, fin_id, mpole, rme, mode)" \ " VALUES (?, ?, ?, ?, ?, ?)"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, sid); sqlite3_exec(db, "BEGIN", 0, 0, 0); while (retval == 0) { TR_HEADER h; int n, i; n = ReadTRHeader(fp, &h, swp); if (n == 0) { break; } /* TODO: h.gauge ? */ for (i = 0; i < h.ntransitions; i++) { TR_RECORD r; TR_EXTRA rx; n = ReadTRRecord(fp, &r, &rx, swp); if (n == 0) { break; } sqlite3_bind_int (stmt, 2, r.lower); sqlite3_bind_int (stmt, 3, r.upper); sqlite3_bind_int (stmt, 4, h.multipole); sqlite3_bind_double(stmt, 5, r.rme); sqlite3_bind_int (stmt, 6, h.mode); sqlite3_bind_double(stmt, 7, rx.de); sqlite3_bind_double(stmt, 8, rx.sdev); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); retval = -1; break; } sqlite3_reset(stmt); } } sqlite3_exec(db, "COMMIT", 0, 0, 0); sqlite3_finalize(stmt); return retval; }
int StoreCETable(sqlite3 *db, unsigned long int sid, FILE *fp, int swp) { int retval = 0; int rc; sqlite3_stmt *stmt; char *sql; sql = "INSERT INTO cstrengths" \ " (cid, e, strength)" \ " VALUES (?, ?, ?)"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); sqlite3_exec(db, "BEGIN", 0, 0, 0); while (retval == 0) { CE_HEADER h; int n, i; n = ReadCEHeader(fp, &h, swp); if (n == 0) { break; } for (i = 0; i < h.ntransitions; i++) { CE_RECORD r; unsigned long int cid; int k, p2; n = ReadCERecord(fp, &r, swp, &h); if (n == 0) { break; } retval = StoreCTransition(db, sid, CFACDB_CS_CE, QK_EXACT, r.lower, r.upper, 0, r.bethe, r.born[0], r.born[1], 0.0, &cid); if (retval != 0) { break; } sqlite3_bind_int(stmt, 1, cid); p2 = 0; for (k = 0; k < r.nsub && retval == 0; k++) { int t; /* TODO: msub */ for (t = 0; t < h.n_usr; t++, p2++) { sqlite3_bind_double(stmt, 2, h.usr_egrid[t]); sqlite3_bind_double(stmt, 3, r.strength[p2]); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); retval = -1; break; } sqlite3_reset(stmt); } } if (h.msub) { free(r.params); } free(r.strength); } free(h.tegrid); free(h.egrid); free(h.usr_egrid); } sqlite3_exec(db, "COMMIT", 0, 0, 0); sqlite3_finalize(stmt); return retval; }
void FriendReposition(LPSESS sess, const char *friendname, int newpos, int promo) { const char *pztail; sqlite3_stmt *pstmt; LPFRIEND pfriend; int i, rc, len, findex; int onepos, nfriends; char buf[256], asdf[32], *tmp; if (newpos > 25 || newpos < 0) { sprintf(buf, "Can't %smote to that position.", promo ? "pro" : "de"); Send0x0F(sess, EID_ERROR, 0, "", buf); return; } findex = FriendScan(sess, friendname); if (findex == -1) { sprintf(buf, "%s was not in your friends list.", friendname); Send0x0F(sess, EID_ERROR, 0, "", buf); return; } onepos = !newpos; nfriends = sess->friends->numelem; newpos = newpos ? (promo ? newpos - 1 : nfriends - newpos) : (findex + (promo ? -1 : 1)); if (newpos >= nfriends) newpos = nfriends - 1; else if (newpos < 0) newpos = 0; pfriend = sess->friends->elem[findex]; if (findex != newpos) { if (newpos < findex) { for (i = findex; i != newpos; i--) sess->friends->elem[i] = sess->friends->elem[i - 1]; len = sprintf(buf, "UPDATE friends SET pos=pos+1 WHERE user=? AND pos>=%d AND pos<%d", newpos + 1, findex + 1); } else { for (i = findex; i != newpos; i++) sess->friends->elem[i] = sess->friends->elem[i + 1]; len = sprintf(buf, "UPDATE friends SET pos=pos-1 WHERE user=? AND pos>%d AND pos<=%d", findex + 1, newpos + 1); } sess->friends->elem[newpos] = pfriend; rc = sqlite3_prepare_v2(db_accounts, buf, len, &pstmt, &pztail); if (rc != SQLITE_OK) { printf("sqlite3_prepare_v2() failed in FriendReposition() UPDATE1: %s\n", sqlite3_errmsg(db_accounts)); return; } sqlite3_bind_text(pstmt, 1, sess->username, -1, SQLITE_STATIC); sqlite3_step(pstmt); sqlite3_finalize(pstmt); rc = sqlite3_prepare_v2(db_accounts, "UPDATE friends SET pos=? WHERE user=? AND friend=?", -1, &pstmt, &pztail); if (rc != SQLITE_OK) { printf("sqlite3_prepare_v2() failed in FriendReposition() UPDATE2: %s\n", sqlite3_errmsg(db_accounts)); return; } sqlite3_bind_int(pstmt, 1, newpos + 1); sqlite3_bind_text(pstmt, 2, sess->username, -1, SQLITE_STATIC); sqlite3_bind_text(pstmt, 3, friendname, -1, SQLITE_STATIC); sqlite3_step(pstmt); sqlite3_finalize(pstmt); } newpos = promo ? newpos + 1 : nfriends - newpos; if (onepos) { sprintf(buf, "%smoted %s %s one position in your friends list.", promo ? "Pro" : "De", friendname, promo ? "up" : "down"); } else { if (newpos == 1) { tmp = "the"; } else { sprintf(asdf, "position %d from the", newpos); tmp = asdf; } sprintf(buf, "%smoted %s to %s %s of your friends list.", promo ? "Pro" : "De", friendname, tmp, promo ? "top" : "bottom"); } Send0x0F(sess, EID_INFO, 0, "", buf); }
int StoreENTable(sqlite3 *db, unsigned long int sid, FILE *fp, int swp) { int retval = 0; int rc; sqlite3_stmt *stmt; char *sql; sql = "INSERT INTO levels" \ " (sid, id, nele, name, e, g, vn, vl, p, ibase, ncomplex, sname)" \ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, sid); sqlite3_exec(db, "BEGIN", 0, 0, 0); while (retval == 0) { EN_HEADER h; int i, n; n = ReadENHeader(fp, &h, swp); if (n == 0) { break; } sqlite3_bind_int(stmt, 3, h.nele); for (i = 0; i < h.nlevels; i++) { EN_RECORD r; int p, vnl, vn, vl, g, ibase; n = ReadENRecord(fp, &r, swp); if (n == 0) { break; } if (r.p < 0) { p = 1; vnl = -r.p; } else { p = 0; vnl = r.p; } g = JFromENRecord(&r) + 1; vn = vnl/100; vl = vnl - 100*vn; ibase = IBaseFromENRecord(&r); sqlite3_bind_int (stmt, 2, r.ilev); SQLITE3_BIND_STR (stmt, 4, r.name); sqlite3_bind_double(stmt, 5, r.energy); sqlite3_bind_int (stmt, 6, g); sqlite3_bind_int (stmt, 7, vn); sqlite3_bind_int (stmt, 8, vl); sqlite3_bind_int (stmt, 9, p); if (ibase >= 0) { sqlite3_bind_int (stmt, 10, ibase); } else { sqlite3_bind_null(stmt, 10); } SQLITE3_BIND_STR (stmt, 11, r.ncomplex); SQLITE3_BIND_STR (stmt, 12, r.sname); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); retval = -1; break; } sqlite3_reset(stmt); } } sqlite3_exec(db, "COMMIT", 0, 0, 0); sqlite3_finalize(stmt); return retval; }
int applyUpdate(sqlite3* db, const Instruction* instr) { int nCol = instr->table->nCol; sqlite_value* valsBefore = instr->values; sqlite_value* valsAfter = instr->values + nCol; std::vector<std::string> columnNames = getColumnNames(db, instr->table->tableName); std::string sql; sql = sql + "UPDATE " + instr->table->tableName + " SET"; // sets for (int n=0, i=0; i < nCol; i++) { const auto& name = columnNames.at(i); const auto val = valsAfter[i]; if (val.type) { if (n > 0) { sql += ", "; } sql = sql + " " + name + " = " + "?"; n++; } } //wheres sql += " WHERE "; for (int n=0, i=0; i < nCol; i++) { const auto& name = columnNames.at(i); const auto val = valsBefore[i]; if (val.type) { if (n > 0) { sql += " AND"; } sql = sql + " " + name + " = " + "?"; n++; } } sqlite3_stmt* stmt; int rc; rc = sqlite3_prepare_v2(db, sql.data(), sql.size(), &stmt, nullptr); if (rc != SQLITE_OK) { std::cerr << "applyUpdate: Failed preparing sql " << sql << std::endl; return 1; } int n = 1; for (int i=0; i < nCol; i++) { sqlite_value* val = &valsAfter[i]; if (val->type) { if (bindValue(stmt, n, val)) { return 1; } n++; } } for (int i=0; i < nCol; i++) { sqlite_value* val = &valsBefore[i]; if (val->type) { if (bindValue(stmt, n, val)) { return 1; } n++; } } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if (rc != SQLITE_DONE) { return rc; } return SQLITE_OK; }
CONFUGA_IAPI int confugaS_catalog_sync (confuga *C) { static const char SQL[] = "SELECT COUNT(*) FROM Confuga.StorageNode WHERE strftime('%s', 'now', '-2 minutes') <= lastheardfrom;" "BEGIN IMMEDIATE TRANSACTION;" "UPDATE Confuga.StorageNode" " SET address = ?, avail = ?, backend = ?, bytes_read = ?, bytes_written = ?, cpu = ?, cpus = ?, lastheardfrom = ?, load1 = ?, load5 = ?, load15 = ?, memory_avail = ?, memory_total = ?, minfree = ?, name = ?, opsys = ?, opsysversion = ?, owner = ?, port = ?, starttime = ?, total = ?, total_ops = ?, url = ?, version = ?" " WHERE hostport = ? || ':' || ? OR" " hostport = ? || ':' || ? OR" " 'chirp://' || hostport = ?;" "END TRANSACTION;"; int rc; sqlite3 *db = C->db; sqlite3_stmt *stmt = NULL; const char *current = SQL; time_t stoptime = time(NULL)+15; struct catalog_query *Q = NULL; struct jx *j = NULL; sqlcatch(sqlite3_prepare_v2(db, current, -1, &stmt, ¤t)); sqlcatchcode(sqlite3_step(stmt), SQLITE_ROW); if (sqlite3_column_int(stmt, 0) > 0) { rc = 0; goto out; } sqlcatch(sqlite3_finalize(stmt); stmt = NULL); debug(D_DEBUG|D_CONFUGA, "syncing with catalog"); Q = catalog_query_create(C->catalog_host, C->catalog_port, stoptime); CATCH(Q == NULL ? errno : 0); /* FIXME sqlcatch is silent about EAGAIN, what should we do? */ sqlcatch(sqlite3_prepare_v2(db, current, -1, &stmt, ¤t)); sqlcatchcode(sqlite3_step(stmt), SQLITE_DONE); sqlcatch(sqlite3_finalize(stmt); stmt = NULL); sqlcatch(sqlite3_prepare_v2(db, current, -1, &stmt, ¤t)); while ((j = catalog_query_read(Q, stoptime))) { const char *type = jx_lookup_string(j, "type"); if (type && strcmp(type, "chirp") == 0) { int n = 1; /* UPDATE */ sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "address"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "avail"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "backend"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "bytes_read"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "bytes_written"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "cpu"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "cpus"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "lastheardfrom"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "load1"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "load5"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "load15"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "memory_avail"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "memory_total"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "minfree"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "name"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "opsys"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "opsysversion"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "owner"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "port"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "starttime"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "total"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "total_ops"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "url"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "version"), -1, SQLITE_TRANSIENT)); /* WHERE hostport = ? */ sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "name"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "port"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "address"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "port"), -1, SQLITE_TRANSIENT)); sqlcatch(sqlite3_bind_text(stmt, n++, jx_lookup_string(j, "url"), -1, SQLITE_TRANSIENT)); sqlcatchcode(sqlite3_step(stmt), SQLITE_DONE); sqlcatch(sqlite3_reset(stmt)); sqlcatch(sqlite3_clear_bindings(stmt)); } } sqlcatch(sqlite3_finalize(stmt); stmt = NULL); sqlcatch(sqlite3_prepare_v2(db, current, -1, &stmt, ¤t)); sqlcatchcode(sqlite3_step(stmt), SQLITE_DONE); sqlcatch(sqlite3_finalize(stmt); stmt = NULL); rc = 0; goto out; out: if (Q) catalog_query_delete(Q); sqlite3_finalize(stmt); sqlend(db); return rc; }
CATCHUNIX(chirp_reli_setacl(host->hostport, template, whoami, "rwldpa", stoptime)); CATCHUNIX(snprintf(template, sizeof(template), "%s/file/" CONFUGA_FID_PRIFMT, host->root, CONFUGA_FID_PRIARGS(empty))); CATCHUNIXIGNORE(chirp_reli_putfile_buffer(host->hostport, template, "", S_IRUSR, 0, stoptime), EEXIST); CATCH(confugaR_register(C, empty, 0, sid)); CATCHUNIX(snprintf(template, sizeof(template), "%s/open", host->root)); CATCHUNIXIGNORE(chirp_reli_mkdir(host->hostport, template, S_IRWXU, stoptime), EEXIST); CATCHUNIX(chirp_reli_setacl(host->hostport, template, whoami, "rwldpa", stoptime)); CATCHUNIX(snprintf(template, sizeof(template), "%s/tickets", host->root)); CATCHUNIXIGNORE(chirp_reli_mkdir(host->hostport, template, S_IRWXU, stoptime), EEXIST); CATCHUNIX(chirp_reli_setacl(host->hostport, template, whoami, "rwldpa", stoptime)); debug(D_DEBUG, "setting `%s' to initialized", host->hostport); sqlcatch(sqlite3_prepare_v2(db, current, -1, &stmt, ¤t)); sqlcatch(sqlite3_bind_text(stmt, 1, host->hostport, -1, SQLITE_STATIC)); sqlcatchcode(sqlite3_step(stmt), SQLITE_DONE); sqlcatch(sqlite3_finalize(stmt); stmt = NULL); debug(D_CONFUGA, "%s/%s initialized", host->hostport, host->root); rc = 0; goto out; out: sqlite3_finalize(stmt); return rc; } CONFUGA_IAPI int confugaS_node_insert (confuga *C, const char *hostport, const char *root) { static const char SQL[] =
int gpgsql_stepx (sqlite3 *db, sqlite3_stmt **stmtp, gpgsql_stepx_callback callback, void *cookie, char **errmsg, const char *sql, ...) { int rc; int err = 0; sqlite3_stmt *stmt = NULL; va_list va; int args; enum gpgsql_arg_type t; int i; int cols; /* Names of the columns. We initialize this lazily to avoid the overhead in case the query doesn't return any results. */ const char **azColName = 0; int callback_initialized = 0; const char **azVals = 0; callback_initialized = 0; if (stmtp && *stmtp) { stmt = *stmtp; /* Make sure this statement is associated with the supplied db. */ log_assert (db == sqlite3_db_handle (stmt)); #if DEBUG_TOFU_CACHE prepares_saved ++; #endif } else { const char *tail = NULL; rc = sqlite3_prepare_v2 (db, sql, -1, &stmt, &tail); if (rc) log_fatal ("failed to prepare SQL: %s", sql); /* We can only process a single statement. */ if (tail) { while (*tail == ' ' || *tail == ';' || *tail == '\n') tail ++; if (*tail) log_fatal ("sqlite3_stepx can only process a single SQL statement." " Second statement starts with: '%s'\n", tail); } if (stmtp) *stmtp = stmt; } #if DEBUG_TOFU_CACHE queries ++; #endif args = sqlite3_bind_parameter_count (stmt); va_start (va, sql); if (args) { for (i = 1; i <= args; i ++) { t = va_arg (va, enum gpgsql_arg_type); switch (t) { case SQLITE_ARG_INT: { int value = va_arg (va, int); err = sqlite3_bind_int (stmt, i, value); break; } case SQLITE_ARG_LONG_LONG: { long long value = va_arg (va, long long); err = sqlite3_bind_int64 (stmt, i, value); break; } case SQLITE_ARG_STRING: { char *text = va_arg (va, char *); err = sqlite3_bind_text (stmt, i, text, -1, SQLITE_STATIC); break; } case SQLITE_ARG_BLOB: { char *blob = va_arg (va, void *); long long length = va_arg (va, long long); err = sqlite3_bind_blob (stmt, i, blob, length, SQLITE_STATIC); break; } default: /* Internal error. Likely corruption. */ log_fatal ("Bad value for parameter type %d.\n", t); } if (err) { log_fatal ("Error binding parameter %d\n", i); goto out; } } } t = va_arg (va, enum gpgsql_arg_type); log_assert (t == SQLITE_ARG_END); va_end (va); for (;;) { rc = sqlite3_step (stmt); if (rc != SQLITE_ROW) /* No more data (SQLITE_DONE) or an error occurred. */ break; if (! callback) continue; if (! callback_initialized) { cols = sqlite3_column_count (stmt); azColName = xmalloc (2 * cols * sizeof (const char *) + 1); for (i = 0; i < cols; i ++) azColName[i] = sqlite3_column_name (stmt, i); callback_initialized = 1; } azVals = &azColName[cols]; for (i = 0; i < cols; i ++) { azVals[i] = sqlite3_column_text (stmt, i); if (! azVals[i] && sqlite3_column_type (stmt, i) != SQLITE_NULL) /* Out of memory. */ { err = SQLITE_NOMEM; break; } } if (callback (cookie, cols, (char **) azVals, (char **) azColName, stmt)) /* A non-zero result means to abort. */ { err = SQLITE_ABORT; break; } } out: xfree (azColName); if (stmtp) rc = sqlite3_reset (stmt); else rc = sqlite3_finalize (stmt); if (rc == SQLITE_OK && err) /* Local error. */ { rc = err; if (errmsg) { const char *e = sqlite3_errstr (err); size_t l = strlen (e) + 1; *errmsg = sqlite3_malloc (l); if (! *errmsg) log_fatal ("Out of memory.\n"); memcpy (*errmsg, e, l); } } else if (rc != SQLITE_OK && errmsg) /* Error reported by sqlite. */ { const char * e = sqlite3_errmsg (db); size_t l = strlen (e) + 1; *errmsg = sqlite3_malloc (l); if (! *errmsg) log_fatal ("Out of memory.\n"); memcpy (*errmsg, e, l); } return rc; }
void GameTaskSystem::initGameProperty( void ) { std::string sql; int result; char *zErrorMsg = 0; CCASSERT(_sqlitedb, "sqlitedb==null"); // 查询表字段 sql = "select data0 from gamePro_Table"; result=sqlite3_exec(_sqlitedb,sql.c_str(),NULL,NULL,&zErrorMsg); // 表不存在 if ( result != SQLITE_OK ) { // CCASSERT( result == SQLITE_OK, "roleinfo table not existes"); sql.clear(); sql="create table if not exists gamePro_Table("; for ( int i=GamePrKey_killMonNum; i<GamePrKey_Max; i++) { sql.append( "data" ); sql.append( __String::createWithFormat("%d", i)->getCString() ); sql.append( " integer"); if ( i != GamePrKey_Max-1) { sql.append( ","); } } sql.append( ")"); result=sqlite3_exec(_sqlitedb,sql.c_str(),NULL,NULL,&zErrorMsg); if(result == SQLITE_OK) { // 插入数据 sql.clear(); sql = sql.append( "insert into gamePro_Table values (" ); for ( int i=GamePrKey_killMonNum; i<GamePrKey_Max; i++) { sql.append( "1" ); if ( i != GamePrKey_Max-1) { sql.append( ", "); } } sql.append( ")"); result=sqlite3_exec(_sqlitedb,sql.c_str(),NULL,NULL,&zErrorMsg); if(result!=SQLITE_OK) CCLOG("insert err"); } } // 插入数据 sql.clear(); sql = sql.append( "select " ); for ( int i=GamePrKey_killMonNum; i<GamePrKey_Max; i++) { sql.append( "data" ); sql.append( __String::createWithFormat("%d", i)->getCString() ); if ( i != GamePrKey_Max-1) { sql.append( ","); } } sql.append( " from gamePro_Table"); sqlite3_stmt *statement; if ( sqlite3_prepare_v2(_sqlitedb, sql.c_str(), -1, &statement, nullptr) == SQLITE_OK ) { // 初始化列表 while (sqlite3_step(statement) == SQLITE_ROW) { for ( int i=GamePrKey_killMonNum; i<GamePrKey_Max; i++) { _GamePropertyArr[i] = sqlite3_column_int(statement, i); } } } sqlite3_finalize(statement); }