void SerializedMap<T,A>::set(::boost::uint64_t id, const T & value) { int result; try { // Step 1 is to serialize the object. There is no way to know how big the object will be // so unfortunately, this will incur an extra copy. // The binary flag is important here. std::ostringstream oss(std::ios_base::binary); oarchive_t oa(oss); oa << value; //std::cout << "Saving " << oss.str().size() << " bytes\n"; // Step 2 is to bind the frameId and data to the insert statement. // http://sqlite.org/c3ref/bind_blob.html result = sqlite3_bind_int64(_iStmt, 1, id); SM_ASSERT_EQ(SqlException, result, SQLITE_OK, "Unable to bind id " << id << " to INSERT statement: " << sqlite3_errmsg(_db->db())); result = static_cast<int>(sqlite3_bind_blob(_iStmt, 2, oss.str().c_str(), oss.str().size(), SQLITE_TRANSIENT)); SM_ASSERT_EQ(SqlException, result, SQLITE_OK, "Unable to bind blob of size " << oss.str().size() << " to INSERT statement: " << sqlite3_errmsg(_db->db())); // Finally, we execute the bound insert statement. result = sqlite3_step(_iStmt); SM_ASSERT_EQ(SqlException, result, SQLITE_DONE, "Unable to execute INSERT statement for id " << id << " and blob of size " << oss.str().size() << ": " << sqlite3_errmsg(_db->db())); } catch(const SqlException & e) { sqlite3_reset(_iStmt); throw; } // After executing, we have to reset the statement // http://www.sqlite.org/c3ref/step.html result = sqlite3_reset(_iStmt); SM_ASSERT_EQ(SqlException, result, SQLITE_OK, "Unable to reset the INSERT statement: " << sqlite3_errmsg(_db->db())); }
static GError * __del_container_one_service(struct sqlx_sqlite3_s *sq3, struct oio_url_s *url, const char *srvtype, gint64 seq) { static const char *sql = "DELETE FROM services WHERE cid = ? AND srvtype = ? AND seq = ?"; sqlite3_stmt *stmt = NULL; GError *err = NULL; int rc; sqlite3_prepare_debug(rc, sq3->db, sql, -1, &stmt, NULL); if (rc != SQLITE_OK) err = M1_SQLITE_GERROR(sq3->db, rc); else { (void) sqlite3_bind_blob(stmt, 1, oio_url_get_id(url), oio_url_get_id_size(url), NULL); (void) sqlite3_bind_text(stmt, 2, srvtype, -1, NULL); (void) sqlite3_bind_int64(stmt, 3, seq); sqlite3_step_debug_until_end (rc, stmt); if (rc != SQLITE_OK && rc != SQLITE_DONE) err = M1_SQLITE_GERROR(sq3->db, rc); sqlite3_finalize_debug(rc, stmt); } return err; }
static bool insert_pr(struct prbot_pr *pr) { sqlite3_stmt *stmt; if (sqlite3_prepare(db, INSERT_PR, -1, &stmt, NULL) != SQLITE_OK) { // Something broke. :( return false; } sqlite3_bind_text(stmt, 1, pr->nick, -1, NULL); sqlite3_bind_text(stmt, 2, pr->lift, -1, NULL); sqlite3_bind_int64(stmt, 3, (sqlite3_int64) pr->date); sqlite3_bind_int(stmt, 4, pr->sets); sqlite3_bind_int(stmt, 5, pr->reps); sqlite3_bind_double(stmt, 6, pr->kgs); if (sqlite3_step(stmt) != SQLITE_DONE) { // Couldn't run this statement. return false; } if (sqlite3_finalize(stmt)) { // Couldn't finalize statement. return false; } return true; }
SQLiteBundleSet::SQLiteBundleSet(const size_t id, bool persistant, dtn::data::BundleSet::Listener *listener, dtn::data::Size bf_size, dtn::storage::SQLiteDatabase& database) : _set_id(id), _bf_size(bf_size), _bf(bf_size * 8), _listener(listener), _consistent(true),_sqldb(database), _persistent(persistant) { // if this is a persitant bundle-set if (_persistent) { // rebuild the bloom filter rebuild_bloom_filter(); // load the next expiration from the storage try { SQLiteDatabase::Statement st(_sqldb._database, SQLiteDatabase::_sql_queries[SQLiteDatabase::BUNDLE_SET_EXPIRE_NEXT_TIMESTAMP]); sqlite3_bind_int64(*st, 1, _set_id); int err = st.step(); if (err == SQLITE_ROW) { _next_expiration = sqlite3_column_int64(*st, 0); } } catch (const SQLiteDatabase::SQLiteQueryException&) { // error } } }
void SQLiteBundleSet::rebuild_bloom_filter() { // rebuild the bloom-filter _bf.clear(); try { SQLiteDatabase::Statement st(_sqldb._database, SQLiteDatabase::_sql_queries[SQLiteDatabase::BUNDLE_SET_GET_ALL]); sqlite3_bind_int64(*st, 1, _set_id); std::set<dtn::data::MetaBundle> ret; dtn::data::BundleID id; while (st.step() == SQLITE_ROW) { // get the bundle id get_bundleid(st, id); id.addTo(_bf); } _consistent = true; } catch (const SQLiteDatabase::SQLiteQueryException&) { // error } }
static int term_select(fulltext_vtab *v, const char *zTerm, int nTerm, sqlite_int64 iFirst, sqlite_int64 *rowid, DocList *out){ sqlite3_stmt *s; int rc = sql_get_statement(v, TERM_SELECT_STMT, &s); if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_text(s, 1, zTerm, nTerm, SQLITE_TRANSIENT); if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_int64(s, 2, iFirst); if( rc!=SQLITE_OK ) return rc; rc = sql_step_statement(v, TERM_SELECT_STMT, &s); if( rc!=SQLITE_ROW ) return rc==SQLITE_DONE ? SQLITE_ERROR : rc; *rowid = sqlite3_column_int64(s, 0); docListInit(out, DL_POSITIONS_OFFSETS, sqlite3_column_blob(s, 1), sqlite3_column_bytes(s, 1)); rc = sqlite3_step(s); return rc==SQLITE_DONE ? SQLITE_OK : rc; }
/* caller must free the memory */ csync_file_stat_t *csync_statedb_get_stat_by_hash(CSYNC *ctx, uint64_t phash) { csync_file_stat_t *st = NULL; int rc; if( !ctx || ctx->db_is_empty ) { return NULL; } if( ctx->statedb.by_hash_stmt == NULL ) { const char *hash_query = "SELECT * FROM metadata WHERE phash=?1"; SQLITE_BUSY_HANDLED(sqlite3_prepare_v2(ctx->statedb.db, hash_query, strlen(hash_query), &ctx->statedb.by_hash_stmt, NULL)); ctx->statedb.lastReturnValue = rc; if( rc != SQLITE_OK ) { CSYNC_LOG(CSYNC_LOG_PRIORITY_ERROR, "WRN: Unable to create stmt for hash query."); return NULL; } } if( ctx->statedb.by_hash_stmt == NULL ) { return NULL; } sqlite3_bind_int64(ctx->statedb.by_hash_stmt, 1, (long long signed int)phash); rc = _csync_file_stat_from_metadata_table(&st, ctx->statedb.by_hash_stmt); ctx->statedb.lastReturnValue = rc; if( !(rc == SQLITE_ROW || rc == SQLITE_DONE) ) { CSYNC_LOG(CSYNC_LOG_PRIORITY_ERROR, "WRN: Could not get line from metadata: %d!", rc); } sqlite3_reset(ctx->statedb.by_hash_stmt); return st; }
void QgsOSMXmlImport::readNode( QXmlStreamReader& xml ) { // <node id="2197214" lat="50.0682113" lon="14.4348483" user="******" uid="595326" visible="true" version="10" changeset="10714591" timestamp="2012-02-17T19:58:49Z"> QXmlStreamAttributes attrs = xml.attributes(); QgsOSMId id = attrs.value( "id" ).toString().toLongLong(); double lat = attrs.value( "lat" ).toString().toDouble(); double lon = attrs.value( "lon" ).toString().toDouble(); // insert to DB sqlite3_bind_int64( mStmtInsertNode, 1, id ); sqlite3_bind_double( mStmtInsertNode, 2, lat ); sqlite3_bind_double( mStmtInsertNode, 3, lon ); if ( sqlite3_step( mStmtInsertNode ) != SQLITE_DONE ) { xml.raiseError( QString( "Storing node %1 failed." ).arg( id ) ); } sqlite3_reset( mStmtInsertNode ); while ( !xml.atEnd() ) { xml.readNext(); if ( xml.isEndElement() ) // </node> break; if ( xml.isStartElement() ) { if ( xml.name() == "tag" ) readTag( false, id, xml ); else xml.raiseError( "Invalid tag in <node>" ); } } }
void irc_sq_lite_db_Insert (IRCMessage* message) { char* commandText; sqlite3_stmt* statement; sqlite3_stmt* _tmp0_ = NULL; sqlite3_stmt* _tmp1_; g_return_if_fail (message != NULL); if (irc_sq_lite_db_DataBase == NULL) { fprintf (stdout, "Datenbank wird erstellt!\n"); irc_sq_lite_db_CreateDB (); } commandText = g_strdup ("INSERT INTO MyLogData (Username, Message, Channel, Server, Timestamp) " \ "VALUES (@1, @2, @3, @4, @5)"); statement = NULL; sqlite3_prepare_v2 (irc_sq_lite_db_DataBase, commandText, -1, &_tmp0_, NULL); statement = (_tmp1_ = _tmp0_, _sqlite3_finalize0 (statement), _tmp1_); sqlite3_bind_text (statement, 1, g_strdup (irc_message_get_Username (message)), -1, g_free); sqlite3_bind_text (statement, 2, g_strdup (irc_message_get_MessageContent (message)), -1, g_free); sqlite3_bind_text (statement, 3, g_strdup (irc_message_get_Channel (message)), -1, g_free); sqlite3_bind_text (statement, 4, g_strdup (irc_message_get_Server (message)), -1, g_free); sqlite3_bind_int64 (statement, 5, irc_message_get_UnixTime (message)); sqlite3_step (statement); _sqlite3_finalize0 (statement); _g_free0 (commandText); }
/* * Find unchanged files from a specified time from the DB * Input: * query_callback : query callback fuction to handle * result records from the query * for_time : Time from where the file/s are not changed * */ int gf_sqlite3_find_unchanged_for_time (void *db_conn, gf_query_callback_t query_callback, void *query_cbk_args, gfdb_time_t *for_time) { int ret = -1; char *query_str = NULL; gf_sql_connection_t *sql_conn = db_conn; sqlite3_stmt *prep_stmt = NULL; long int for_time_usec = 0; CHECK_SQL_CONN (sql_conn, out); GF_VALIDATE_OR_GOTO(GFDB_STR_SQLITE3, query_callback, out); query_str = "select GF_FILE_TB.GF_ID," " (select group_concat( GF_PID || ',' || FNAME || ','" " || FPATH || ',' || W_DEL_FLAG ||',' || LINK_UPDATE , '::')" " from GF_FLINK_TB where GF_FILE_TB.GF_ID = GF_FLINK_TB.GF_ID)" " from GF_FILE_TB where " /*First condition: For writes*/ "((" GF_COL_TB_WSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_WMSEC ") <= ? )" " OR " /*Second condition: For reads*/ "((" GF_COL_TB_RWSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_RWMSEC ") <= ?)"; for_time_usec = gfdb_time_2_usec(for_time); ret = sqlite3_prepare(sql_conn->sqlite3_db_conn, query_str, -1, &prep_stmt, 0); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed preparing statment %s : %s", query_str, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind write wind time*/ ret = sqlite3_bind_int64(prep_stmt, 1, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read wind time*/ ret = sqlite3_bind_int64(prep_stmt, 2, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Execute the query*/ ret = gf_sql_query_function(prep_stmt, query_callback, query_cbk_args); if (ret) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed Query %s", query_str); goto out; } ret = 0; out: sqlite3_finalize(prep_stmt); return ret; }
// Bind a 64bits int value to a parameter "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement void Statement::bind(const char* apName, const sqlite3_int64& aValue) { int index = sqlite3_bind_parameter_index(mStmtPtr, apName); int ret = sqlite3_bind_int64(mStmtPtr, index, aValue); check(ret); }
int db_share_files( const struct pub_file *files, size_t count, const struct client *owner ) { /* todo: do it in transaction */ while ( count-- > 0 ) { sqlite3_stmt *stmt; const char *ext; int ext_len; int i; uint64_t fid; if ( !files->name_len ) { files++; continue; } fid = MAKE_FID(files->hash); // find extension ext = file_extension(files->name, files->name_len); if ( ext ) ext_len = files->name + files->name_len - ext; else ext_len = 0; i=1; stmt = s_stmt[SHARE_UPD]; DB_CHECK( SQLITE_OK == sqlite3_reset(stmt) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, files->name, files->name_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, ext, ext_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, files->size) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->type) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->media_length) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->media_bitrate) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, files->media_codec, files->media_codec_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, fid) ); DB_CHECK( SQLITE_DONE == sqlite3_step(stmt) ); if ( !sqlite3_changes(s_db) ) { i=1; stmt = s_stmt[SHARE_INS]; DB_CHECK( SQLITE_OK == sqlite3_reset(stmt) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, fid) ); DB_CHECK( SQLITE_OK == sqlite3_bind_blob(stmt, i++, files->hash, sizeof(files->hash), SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, files->name, files->name_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, ext, ext_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, files->size) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->type) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->media_length) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->media_bitrate) ); DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, files->media_codec, files->media_codec_len, SQLITE_STATIC) ); DB_CHECK( SQLITE_DONE == sqlite3_step(stmt) ); } i=1; stmt = s_stmt[SHARE_SRC]; DB_CHECK( SQLITE_OK == sqlite3_reset(stmt) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, fid) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, MAKE_SID(owner)) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->complete) ); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, files->rating) ); DB_CHECK( SQLITE_DONE == sqlite3_step(stmt) ); files++; } return 1; failed: ED2KD_LOGERR("failed to add file to db (%s)", sqlite3_errmsg(s_db)); return 0; }
statement& statement::bind(int idx, uint64_t value) { THROW_ERR(sqlite3_bind_int64(stmt_, idx, static_cast<int64_t>(value))); return *this; }
static VALUE db_execute(int argc, VALUE *argv, VALUE self) { sqlite3 * db = NULL; void **ppDB = NULL; sqlite3_stmt *statement = NULL; const char* sql = NULL; VALUE arRes = rb_ary_new(); VALUE* colNames = NULL; int nRes = 0; char * szErrMsg = 0; int is_batch = 0; if ((argc < 2) || (argc > 3)) rb_raise(rb_eArgError, "wrong # of arguments(%d for 3)",argc); Data_Get_Struct(self, void *, ppDB); db = (sqlite3 *)rho_db_get_handle(*ppDB); sql = RSTRING_PTR(argv[0]); is_batch = argv[1] == Qtrue ? 1 : 0; RAWTRACE1("db_execute: %s", sql); PROF_START_CREATED("SQLITE"); if ( is_batch ) { PROF_START_CREATED("SQLITE_EXEC"); rho_db_lock(*ppDB); nRes = sqlite3_exec(db, sql, NULL, NULL, &szErrMsg); rho_db_unlock(*ppDB); PROF_STOP("SQLITE_EXEC"); } else { rho_db_lock(*ppDB); PROF_START_CREATED("SQLITE_PREPARE"); nRes = rho_db_prepare_statement(*ppDB, sql, -1, &statement); PROF_STOP("SQLITE_PREPARE"); //nRes = sqlite3_prepare_v2(db, sql, -1, &statement, NULL); if ( nRes != SQLITE_OK) { szErrMsg = (char *)sqlite3_errmsg(db); rho_db_unlock(*ppDB); rb_raise(rb_eArgError, "could not prepare statement: %d; Message: %s",nRes, (szErrMsg?szErrMsg:"")); } if ( argc > 2 ) { int i = 0; VALUE args = argv[2]; if ( RARRAY_LEN(args) > 0 && TYPE(RARRAY_PTR(args)[0]) == T_ARRAY ) args = RARRAY_PTR(args)[0]; for( ; i < RARRAY_LEN(args); i++ ) { VALUE arg = RARRAY_PTR(args)[i]; if (NIL_P(arg)) { sqlite3_bind_null(statement, i+1); continue; } switch( TYPE(arg) ) { case T_STRING: sqlite3_bind_text(statement, i+1, RSTRING_PTR(arg), RSTRING_LEN(arg), SQLITE_TRANSIENT); break; case T_FLOAT: sqlite3_bind_double(statement, i+1, NUM2DBL(arg)); break; case T_FIXNUM: case T_BIGNUM: sqlite3_bind_int64(statement, i+1, NUM2LL(arg)); break; default: { VALUE strVal = rb_funcall(arg, rb_intern("to_s"), 0); sqlite3_bind_text(statement, i+1, RSTRING_PTR(strVal), -1, SQLITE_TRANSIENT); } break; } } } PROF_START_CREATED("SQLITE_EXEC"); nRes = sqlite3_step(statement); PROF_STOP("SQLITE_EXEC"); while( nRes== SQLITE_ROW ) { int nCount = sqlite3_data_count(statement); int nCol = 0; VALUE hashRec = rb_hash_new(); //if ( !colNames ) // colNames = getColNames(statement, nCount); for(;nCol<nCount;nCol++){ int nColType = sqlite3_column_type(statement,nCol); const char* szColName = sqlite3_column_name(statement,nCol); VALUE colName = rb_str_new2(szColName); VALUE colValue = Qnil; switch(nColType){ case SQLITE_NULL: break; case SQLITE_FLOAT: { double dVal = sqlite3_column_double(statement, nCol); colValue = DBL2NUM(dVal); break; } case SQLITE_INTEGER: { sqlite_int64 nVal = sqlite3_column_int64(statement, nCol); colValue = LL2NUM(nVal); break; } default:{ sqlite3_value * sqlValue = sqlite3_column_value(statement, nCol); int nLen = sqlite3_value_bytes(sqlValue); const char* szValue = (const char *)sqlite3_value_text(sqlValue); //char *text = (char *)sqlite3_column_text(statement, nCol); colValue = rb_str_new(szValue, nLen); break; } } rb_hash_aset(hashRec, colName/*colNames[nCol]*/, colValue); } rb_ary_push(arRes, hashRec); PROF_START_CREATED("SQLITE_EXEC"); nRes = sqlite3_step(statement); PROF_STOP("SQLITE_EXEC"); } rho_db_unlock(*ppDB); } if ( statement ) //sqlite3_finalize(statement); sqlite3_reset(statement); if ( colNames ) free(colNames); if ( nRes != SQLITE_OK && nRes != SQLITE_ROW && nRes != SQLITE_DONE ) { if ( !szErrMsg ) szErrMsg = (char*)sqlite3_errmsg(db); rb_raise(rb_eArgError, "could not execute statement: %d; Message: %s",nRes, (szErrMsg?szErrMsg:"")); } PROF_STOP("SQLITE"); return arRes; }
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName, const QStringList& tagKeys, const QStringList& notNullTagKeys ) { 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, nullptr ) != 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? // filter out closed way that are not areas through tags if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) ) { // make sure tags that indicate areas are taken into consideration when deciding on a closed way is or isn't an area // and allow for a closed way to be exported both as a polygon and a line in case both area and non-area tags are present if (( t.value( "area" ) != "yes" && !t.contains( "amenity" ) && !t.contains( "landuse" ) && !t.contains( "building" ) && !t.contains( "natural" ) && !t.contains( "leisure" ) && !t.contains( "aeroway" ) ) || !closed ) isArea = false; } if ( closed != isArea ) continue; // skip if it's not what we're looking for //check not null tags bool skipNull = false; for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i ) if ( !t.contains( notNullTagKeys[i] ) ) skipNull = true; if ( skipNull ) continue; 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 ); } if ( !geom.isEmpty() ) sqlite3_bind_blob( stmtInsert, ++col, geom.asWkb(), ( int ) geom.wkbSize(), SQLITE_STATIC ); else sqlite3_bind_null( stmtInsert, ++col ); 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 ); } sqlite3_finalize( stmtInsert ); }
void Database::Statement::bind(int parameter, uint64_t value) { if(!parameter) return; if(sqlite3_bind_int64(mStmt, parameter, sqlite3_int64(value)) != SQLITE_OK) throw DatabaseException(mDb, String("Unable to bind parameter ") + String::number(parameter)); }
static void result_or_bind(sqlite3_context *ctx, sqlite3_stmt *stmt, int idx, char *data, int len, int type) { char *endp; if (!data) { if (ctx) { sqlite3_result_null(ctx); } else { sqlite3_bind_null(stmt, idx); } return; } if (type == SQLITE_INTEGER) { sqlite_int64 val; #if defined(_WIN32) || defined(_WIN64) char endc; if (sscanf(data, "%I64d%c", &val, &endc) == 1) { if (ctx) { sqlite3_result_int64(ctx, val); } else { sqlite3_bind_int64(stmt, idx, val); } return; } #else endp = 0; #ifdef __osf__ val = strtol(data, &endp, 0); #else val = strtoll(data, &endp, 0); #endif if (endp && (endp != data) && !*endp) { if (ctx) { sqlite3_result_int64(ctx, val); } else { sqlite3_bind_int64(stmt, idx, val); } return; } #endif } else if (type == SQLITE_FLOAT) { double val; endp = 0; val = strtod(data, &endp); if (endp && (endp != data) && !*endp) { if (ctx) { sqlite3_result_double(ctx, val); } else { sqlite3_bind_double(stmt, idx, val); } return; } } if (ctx) { sqlite3_result_text(ctx, data, len, SQLITE_TRANSIENT); } else { sqlite3_bind_text(stmt, idx, data, len, SQLITE_TRANSIENT); } }
int main (int argc, char *argv[]) { int ret; sqlite3 *handle; sqlite3_stmt *stmt; char sql[256]; char *err_msg = NULL; double x; double y; int pk; int ix; int iy; gaiaGeomCollPtr geo = NULL; unsigned char *blob; int blob_size; int i; char **results; int n_rows; int n_columns; char *count; clock_t t0; clock_t t1; void *cache; if (argc != 2) { fprintf (stderr, "usage: %s test_db_path\n", argv[0]); return -1; } /* trying to connect the test DB: - this demo is intended to create a new, empty database */ ret = sqlite3_open_v2 (argv[1], &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL); if (ret != SQLITE_OK) { printf ("cannot open '%s': %s\n", argv[1], sqlite3_errmsg (handle)); sqlite3_close (handle); return -1; } cache = spatialite_alloc_connection (); spatialite_init_ex (handle, cache, 0); /* showing the SQLite version */ printf ("SQLite version: %s\n", sqlite3_libversion ()); /* showing the SpatiaLite version */ printf ("SpatiaLite version: %s\n", spatialite_version ()); printf ("\n\n"); /* we are supposing this one is an empty database, so we have to create the Spatial Metadata */ strcpy (sql, "SELECT InitSpatialMetadata(1)"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("InitSpatialMetadata() error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } /* now we can create the test table for simplicity we'll define only one column, the primary key */ strcpy (sql, "CREATE TABLE test ("); strcat (sql, "PK INTEGER NOT NULL PRIMARY KEY)"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("CREATE TABLE 'test' error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } /* ... we'll add a Geometry column of POINT type to the test table */ strcpy (sql, "SELECT AddGeometryColumn('test', 'geom', 3003, 'POINT', 2)"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("AddGeometryColumn() error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } /* and finally we'll enable this geo-column to have a Spatial Index based on R*Tree */ strcpy (sql, "SELECT CreateSpatialIndex('test', 'geom')"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("CreateSpatialIndex() error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } printf ("\nnow we are going to insert 1 million POINTs; wait, please ...\n\n"); t0 = clock (); /* beginning a transaction *** this step is absolutely critical *** the SQLite engine is a TRANSACTIONAL one the whole batch of INSERTs has to be performed as an unique transaction, otherwise performance will be surely very poor */ strcpy (sql, "BEGIN"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("BEGIN error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } /* preparing to populate the test table we'll use a Prepared Statement we can reuse in order to insert each row */ strcpy (sql, "INSERT INTO test (pk, geom) VALUES (?, ?)"); ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL); if (ret != SQLITE_OK) { /* an error occurred */ printf ("INSERT SQL error: %s\n", sqlite3_errmsg (handle)); goto abort; } pk = 0; for (ix = 0; ix < 1000; ix++) { x = 1000000.0 + (ix * 10.0); for (iy = 0; iy < 1000; iy++) { /* this double loop will insert 1 million rows into the the test table */ y = 4000000.0 + (iy * 10.0); pk++; if ((pk % 25000) == 0) { t1 = clock (); printf ("insert row: %d\t\t[elapsed time: %1.3f]\n", pk, (double) (t1 - t0) / CLOCKS_PER_SEC); } /* preparing the geometry to insert */ geo = gaiaAllocGeomColl (); geo->Srid = 3003; gaiaAddPointToGeomColl (geo, x, y); /* transforming this geometry into the SpatiaLite BLOB format */ gaiaToSpatiaLiteBlobWkb (geo, &blob, &blob_size); /* we can now destroy the geometry object */ gaiaFreeGeomColl (geo); /* resetting Prepared Statement and bindings */ sqlite3_reset (stmt); sqlite3_clear_bindings (stmt); /* binding parameters to Prepared Statement */ sqlite3_bind_int64 (stmt, 1, pk); sqlite3_bind_blob (stmt, 2, blob, blob_size, free); /* performing actual row insert */ ret = sqlite3_step (stmt); if (ret == SQLITE_DONE || ret == SQLITE_ROW) ; else { /* an unexpected error occurred */ printf ("sqlite3_step() error: %s\n", sqlite3_errmsg (handle)); sqlite3_finalize (stmt); goto abort; } } } /* we have now to finalize the query [memory cleanup] */ sqlite3_finalize (stmt); /* committing the transaction *** this step is absolutely critical *** if we don't confirm the still pending transaction, any update will be lost */ strcpy (sql, "COMMIT"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("COMMIT error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } /* now we'll optimize the table */ strcpy (sql, "ANALYZE test"); ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("ANALYZE error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } for (ix = 0; ix < 3; ix++) { printf ("\nperforming test#%d - not using Spatial Index\n", ix); /* now we'll perform the spatial query WITHOUT using the Spatial Index we'll loop 3 times in order to avoid buffering-caching side effects */ strcpy (sql, "SELECT Count(*) FROM test "); strcat (sql, "WHERE MbrWithin(geom, BuildMbr("); strcat (sql, "1000400.5, 4000400.5, "); strcat (sql, "1000450.5, 4000450.5))"); t0 = clock (); ret = sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("NoSpatialIndex SQL error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } count = ""; for (i = 1; i <= n_rows; i++) { count = results[(i * n_columns) + 0]; } t1 = clock (); printf ("Count(*) = %d\t\t[elapsed time: %1.4f]\n", atoi (count), (double) (t1 - t0) / CLOCKS_PER_SEC); /* we can now free the table results */ sqlite3_free_table (results); } for (ix = 0; ix < 3; ix++) { printf ("\nperforming test#%d - using the R*Tree Spatial Index\n", ix); /* now we'll perform the spatial query USING the R*Tree Spatial Index we'll loop 3 times in order to avoid buffering-caching side effects */ strcpy (sql, "SELECT Count(*) FROM test "); strcat (sql, "WHERE MbrWithin(geom, BuildMbr("); strcat (sql, "1000400.5, 4000400.5, "); strcat (sql, "1000450.5, 4000450.5)) AND ROWID IN ("); strcat (sql, "SELECT pkid FROM idx_test_geom WHERE "); strcat (sql, "xmin > 1000400.5 AND "); strcat (sql, "xmax < 1000450.5 AND "); strcat (sql, "ymin > 4000400.5 AND "); strcat (sql, "ymax < 4000450.5)"); /* YES, this query is a very unhappy one the idea is simply to simulate exactly the same conditions as above */ t0 = clock (); ret = sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns, &err_msg); if (ret != SQLITE_OK) { /* an error occurred */ printf ("SpatialIndex SQL error: %s\n", err_msg); sqlite3_free (err_msg); goto abort; } count = ""; for (i = 1; i <= n_rows; i++) { count = results[(i * n_columns) + 0]; } t1 = clock (); printf ("Count(*) = %d\t\t[elapsed time: %1.4f]\n", atoi (count), (double) (t1 - t0) / CLOCKS_PER_SEC); /* we can now free the table results */ sqlite3_free_table (results); } /* disconnecting the test DB */ ret = sqlite3_close (handle); if (ret != SQLITE_OK) { printf ("close() error: %s\n", sqlite3_errmsg (handle)); return -1; } spatialite_cleanup_ex (cache); printf ("\n\nsample successfully terminated\n"); return 0; abort: sqlite3_close (handle); spatialite_cleanup_ex (cache); spatialite_shutdown(); return -1; }
/** * Stores an hdb_entry in the database. If flags contains HDB_F_REPLACE * a previous entry may be replaced. * * @param context The current krb5_context * @param db Heimdal database handle * @param flags May currently only contain HDB_F_REPLACE * @param entry The data to store * * @return 0 if everything worked, an error code if not */ static krb5_error_code hdb_sqlite_store(krb5_context context, HDB *db, unsigned flags, hdb_entry_ex *entry) { int ret; int i; sqlite_int64 entry_id; char *principal_string = NULL; char *alias_string; const HDB_Ext_Aliases *aliases; hdb_sqlite_db *hsdb = (hdb_sqlite_db *)(db->hdb_db); krb5_data value; sqlite3_stmt *get_ids = hsdb->get_ids; ret = hdb_sqlite_exec_stmt(context, hsdb->db, "BEGIN IMMEDIATE TRANSACTION", EINVAL); if(ret != SQLITE_OK) { krb5_set_error_string(context, "SQLite BEGIN TRANSACTION failed: %s", sqlite3_errmsg(hsdb->db)); goto rollback; } ret = krb5_unparse_name(context, entry->entry.principal, &principal_string); if (ret) { goto rollback; } ret = hdb_seal_keys(context, db, &entry->entry); if(ret) { goto rollback; } ret = hdb_entry2value(context, &entry->entry, &value); if(ret) { goto rollback; } sqlite3_bind_text(get_ids, 1, principal_string, -1, SQLITE_STATIC); ret = hdb_sqlite_step(context, hsdb->db, get_ids); if(ret == SQLITE_DONE) { /* No such principal */ sqlite3_bind_blob(hsdb->add_entry, 1, value.data, value.length, SQLITE_STATIC); ret = hdb_sqlite_step(context, hsdb->db, hsdb->add_entry); sqlite3_clear_bindings(hsdb->add_entry); sqlite3_reset(hsdb->add_entry); if(ret != SQLITE_DONE) goto rollback; sqlite3_bind_text(hsdb->add_principal, 1, principal_string, -1, SQLITE_STATIC); ret = hdb_sqlite_step(context, hsdb->db, hsdb->add_principal); sqlite3_clear_bindings(hsdb->add_principal); sqlite3_reset(hsdb->add_principal); if(ret != SQLITE_DONE) goto rollback; entry_id = sqlite3_column_int64(get_ids, 1); } else if(ret == SQLITE_ROW) { /* Found a principal */ if(! (flags & HDB_F_REPLACE)) /* Not allowed to replace it */ goto rollback; entry_id = sqlite3_column_int64(get_ids, 1); sqlite3_bind_int64(hsdb->delete_aliases, 1, entry_id); ret = hdb_sqlite_step_once(context, db, hsdb->delete_aliases); if(ret != SQLITE_DONE) goto rollback; sqlite3_bind_blob(hsdb->update_entry, 1, value.data, value.length, SQLITE_STATIC); sqlite3_bind_int64(hsdb->update_entry, 2, entry_id); ret = hdb_sqlite_step_once(context, db, hsdb->update_entry); if(ret != SQLITE_DONE) goto rollback; } else { /* Error! */ goto rollback; } ret = hdb_entry_get_aliases(&entry->entry, &aliases); if(ret || aliases == NULL) goto commit; for(i = 0; i < aliases->aliases.len; i++) { ret = krb5_unparse_name(context, &aliases->aliases.val[i], &alias_string); if (ret) { free(alias_string); goto rollback; } sqlite3_bind_text(hsdb->add_alias, 1, alias_string, -1, SQLITE_STATIC); sqlite3_bind_int64(hsdb->add_alias, 2, entry_id); ret = hdb_sqlite_step_once(context, db, hsdb->add_alias); free(alias_string); if(ret != SQLITE_DONE) goto rollback; } ret = 0; commit: free(principal_string); krb5_data_free(&value); sqlite3_clear_bindings(get_ids); sqlite3_reset(get_ids); ret = hdb_sqlite_exec_stmt(context, hsdb->db, "COMMIT", EINVAL); if(ret != SQLITE_OK) krb5_warnx(context, "hdb-sqlite: COMMIT problem: %d: %s", ret, sqlite3_errmsg(hsdb->db)); return ret; rollback: krb5_warnx(context, "hdb-sqlite: store rollback problem: %d: %s", ret, sqlite3_errmsg(hsdb->db)); free(principal_string); ret = hdb_sqlite_exec_stmt(context, hsdb->db, "ROLLBACK", EINVAL); return ret; }
static void vxpath_read_row (VirtualXPathCursorPtr cursor) { /* trying to read a row from the real-table */ sqlite3_stmt *stmt; int ret; sqlite3_int64 pk; int eof; if (cursor->stmt == NULL || cursor->xpathExpr == NULL) return; if (cursor->xpathObj) xmlXPathFreeObject (cursor->xpathObj); if (cursor->xpathContext) xmlXPathFreeContext (cursor->xpathContext); if (cursor->xmlDoc) xmlFreeDoc (cursor->xmlDoc); cursor->xmlDoc = NULL; cursor->xpathContext = NULL; cursor->xpathObj = NULL; stmt = cursor->stmt; sqlite3_bind_int64 (stmt, 1, cursor->current_row); while (1) { ret = sqlite3_step (stmt); if (ret == SQLITE_ROW) { pk = sqlite3_column_int64 (stmt, 0); /* filtering the PK value */ eof = 0; switch (cursor->keyOp1) { case SQLITE_INDEX_CONSTRAINT_EQ: if (pk > cursor->keyVal1) eof = 1; break; case SQLITE_INDEX_CONSTRAINT_LT: if (pk >= cursor->keyVal1) eof = 1; break; case SQLITE_INDEX_CONSTRAINT_LE: if (pk > cursor->keyVal1) eof = 1; break; }; switch (cursor->keyOp2) { case SQLITE_INDEX_CONSTRAINT_EQ: if (pk > cursor->keyVal2) eof = 1; break; case SQLITE_INDEX_CONSTRAINT_LT: if (pk >= cursor->keyVal2) eof = 1; break; case SQLITE_INDEX_CONSTRAINT_LE: if (pk > cursor->keyVal2) eof = 1; break; }; if (eof) { cursor->eof = 1; return; } if (sqlite3_column_type (stmt, 1) == SQLITE_BLOB) { xmlDocPtr xml_doc; int xml_len; unsigned char *xml; const unsigned char *blob = sqlite3_column_blob (stmt, 1); int size = sqlite3_column_bytes (stmt, 1); gaiaXmlFromBlob (blob, size, -1, &xml, &xml_len); if (!xml) continue; xml_doc = xmlReadMemory ((const char *) xml, xml_len, "noname.xml", NULL, 0); if (xml_doc != NULL) { xmlXPathContextPtr xpathCtx; xmlXPathObjectPtr xpathObj; if (vxpath_eval_expr (cursor->pVtab->p_cache, xml_doc, cursor->xpathExpr, &xpathCtx, &xpathObj)) { free (xml); if (cursor->xpathObj) xmlXPathFreeObject (cursor->xpathObj); if (cursor->xpathContext) xmlXPathFreeContext (cursor->xpathContext); if (cursor->xmlDoc) xmlFreeDoc (cursor->xmlDoc); cursor->xmlDoc = xml_doc; cursor->xpathContext = xpathCtx; cursor->xpathObj = xpathObj; cursor->xpathIdx = 0; break; } free (xml); xmlFreeDoc (xml_doc); } } } else { /* an error occurred */ cursor->eof = 1; return; } } cursor->eof = 0; cursor->current_row = pk; }
statement& statement::bind(int idx, int64_t value) { THROW_ERR(sqlite3_bind_int64(stmt_, idx, value)); return *this; }
int statement::bind(int idx, sqlite3_int64 value) { return sqlite3_bind_int64(stmt_, idx, value); }
static int pkg_repo_binary_register_conflicts(const char *origin, char **conflicts, int conflicts_num, sqlite3 *sqlite) { const char clean_conflicts_sql[] = "" "DELETE FROM pkg_conflicts " "WHERE package_id = ?1;"; const char select_id_sql[] = "" "SELECT id FROM packages " "WHERE origin = ?1;"; const char insert_conflict_sql[] = "" "INSERT INTO pkg_conflicts " "(package_id, conflict_id) " "VALUES (?1, ?2);"; sqlite3_stmt *stmt = NULL; int ret, i; int64_t origin_id, conflict_id; pkg_debug(4, "pkgdb_repo_register_conflicts: running '%s'", select_id_sql); if (sqlite3_prepare_v2(sqlite, select_id_sql, -1, &stmt, NULL) != SQLITE_OK) { ERROR_SQLITE(sqlite, select_id_sql); return (EPKG_FATAL); } sqlite3_bind_text(stmt, 1, origin, -1, SQLITE_TRANSIENT); ret = sqlite3_step(stmt); if (ret == SQLITE_ROW) { origin_id = sqlite3_column_int64(stmt, 0); } else { ERROR_SQLITE(sqlite, select_id_sql); return (EPKG_FATAL); } sqlite3_finalize(stmt); pkg_debug(4, "pkgdb_repo_register_conflicts: running '%s'", clean_conflicts_sql); if (sqlite3_prepare_v2(sqlite, clean_conflicts_sql, -1, &stmt, NULL) != SQLITE_OK) { ERROR_SQLITE(sqlite, clean_conflicts_sql); return (EPKG_FATAL); } sqlite3_bind_int64(stmt, 1, origin_id); /* Ignore cleanup result */ (void)sqlite3_step(stmt); sqlite3_finalize(stmt); for (i = 0; i < conflicts_num; i ++) { /* Select a conflict */ pkg_debug(4, "pkgdb_repo_register_conflicts: running '%s'", select_id_sql); if (sqlite3_prepare_v2(sqlite, select_id_sql, -1, &stmt, NULL) != SQLITE_OK) { ERROR_SQLITE(sqlite, select_id_sql); return (EPKG_FATAL); } sqlite3_bind_text(stmt, 1, conflicts[i], -1, SQLITE_TRANSIENT); ret = sqlite3_step(stmt); if (ret == SQLITE_ROW) { conflict_id = sqlite3_column_int64(stmt, 0); } else { ERROR_SQLITE(sqlite, select_id_sql); return (EPKG_FATAL); } sqlite3_finalize(stmt); /* Insert a pair */ pkg_debug(4, "pkgdb_repo_register_conflicts: running '%s'", insert_conflict_sql); if (sqlite3_prepare_v2(sqlite, insert_conflict_sql, -1, &stmt, NULL) != SQLITE_OK) { ERROR_SQLITE(sqlite, insert_conflict_sql); return (EPKG_FATAL); } sqlite3_bind_int64(stmt, 1, origin_id); sqlite3_bind_int64(stmt, 2, conflict_id); ret = sqlite3_step(stmt); if (ret != SQLITE_DONE) { ERROR_SQLITE(sqlite, insert_conflict_sql); return (EPKG_FATAL); } sqlite3_finalize(stmt); } return (EPKG_OK); }
void sqlite3_command::bind(int index, long long data) { if(sqlite3_bind_int64(this->stmt, index, data)!=SQLITE_OK) throw database_error(this->con); }
int db_search_files( struct search_node *snode, struct evbuffer *buf, size_t *count ) { int err; const char *tail; sqlite3_stmt *stmt = 0; size_t i; struct { char name_term[MAX_NAME_TERM_LEN+1]; size_t name_len; uint64_t minsize; uint64_t maxsize; uint64_t srcavail; uint64_t srccomplete; uint64_t minbitrate; uint64_t minlength; struct search_node *ext_node; struct search_node *codec_node; struct search_node *type_node; } params; char query[MAX_SEARCH_QUERY_LEN+1] = " SELECT f.hash,f.name,f.size,f.type,f.ext,f.srcavail,f.srccomplete,f.rating,f.rated_count," " (SELECT sid FROM sources WHERE fid=f.fid LIMIT 1) AS sid," " f.mlength,f.mbitrate,f.mcodec " " FROM fnames n" " JOIN files f ON f.fid = n.docid" " WHERE fnames MATCH ?" ; memset(¶ms, 0, sizeof params); while ( snode ) { if ( (ST_AND <= snode->type) && (ST_NOT >= snode->type) ) { if ( !snode->left_visited ) { if ( snode->string_term ) { params.name_len++; DB_CHECK( params.name_len < sizeof params.name_term ); strcat(params.name_term, "("); } snode->left_visited = 1; snode = snode->left; continue; } else if ( !snode->right_visited ) { if ( snode->string_term ) { const char *oper = 0; switch( snode->type ) { case ST_AND: params.name_len += 5; oper = " AND "; break; case ST_OR: params.name_len += 4; oper = " OR "; break; case ST_NOT: params.name_len += 5; oper = " NOT "; break; default: DB_CHECK(0); } DB_CHECK( params.name_len < sizeof params.name_term ); strcat(params.name_term, oper); } snode->right_visited = 1; snode = snode->right; continue; } else { if ( snode->string_term ) { params.name_len++; DB_CHECK( params.name_len < sizeof params.name_term); strcat(params.name_term, ")"); } } } else { switch ( snode->type ) { case ST_STRING: params.name_len += snode->str_len; DB_CHECK( params.name_len < sizeof params.name_term ); strncat(params.name_term, snode->str_val, snode->str_len); break; case ST_EXTENSION: params.ext_node = snode; break; case ST_CODEC: params.codec_node = snode; break; case ST_MINSIZE: params.minsize = snode->int_val; break; case ST_MAXSIZE: params.maxsize = snode->int_val; break; case ST_SRCAVAIL: params.srcavail = snode->int_val; break; case ST_SRCCOMLETE: params.srccomplete = snode->int_val; break; case ST_MINBITRATE: params.minbitrate = snode->int_val; break; case ST_MINLENGTH: params.minlength = snode->int_val; break; case ST_TYPE: params.type_node = snode; break; default: DB_CHECK(0); } } snode = snode->parent; } if ( params.ext_node ) { strcat(query, " AND f.ext=?"); } if ( params.codec_node ) { strcat(query, " AND f.mcodec=?"); } if ( params.minsize ) { strcat(query, " AND f.size>?"); } if ( params.maxsize ) { strcat(query, " AND f.size<?"); } if ( params.srcavail ) { strcat(query, " AND f.srcavail>?"); } if ( params.srccomplete ) { strcat(query, " AND f.srccomplete>?"); } if ( params.minbitrate ) { strcat(query, " AND f.mbitrate>?"); } if ( params.minlength ) { strcat(query, " AND f.mlength>?"); } if ( params.type_node ) { strcat(query, " AND f.type=?"); } strcat(query, " LIMIT ?"); DB_CHECK( SQLITE_OK == sqlite3_prepare_v2(s_db, query, strlen(query)+1, &stmt, &tail) ); i=1; DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, params.name_term, params.name_len+1, SQLITE_STATIC) ); if ( params.ext_node ) { DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, params.ext_node->str_val, params.ext_node->str_len, SQLITE_STATIC) ); } if ( params.codec_node ) { DB_CHECK( SQLITE_OK == sqlite3_bind_text(stmt, i++, params.codec_node->str_val, params.codec_node->str_len, SQLITE_STATIC) ); } if ( params.minsize ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.minsize) ); } if ( params.maxsize ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.maxsize) ); } if ( params.srcavail ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.srcavail) ); } if ( params.srccomplete ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.srccomplete) ); } if ( params.minbitrate ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.minbitrate) ); } if ( params.minlength ) { DB_CHECK( SQLITE_OK == sqlite3_bind_int64(stmt, i++, params.minlength) ); } if ( params.type_node ) { uint8_t type = get_ed2k_file_type(params.type_node->str_val, params.type_node->str_len); DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, type) ); } DB_CHECK( SQLITE_OK == sqlite3_bind_int(stmt, i++, *count) ); i = 0; while ( ((err = sqlite3_step(stmt)) == SQLITE_ROW) && (i < *count) ) { struct search_file sfile; uint64_t sid; int col = 0; memset(&sfile, 0, sizeof sfile); sfile.hash = (const unsigned char*)sqlite3_column_blob(stmt, col++); sfile.name_len = sqlite3_column_bytes(stmt, col); sfile.name_len = sfile.name_len > MAX_FILENAME_LEN ? MAX_FILENAME_LEN : sfile.name_len; sfile.name = (const char*)sqlite3_column_text(stmt, col++); sfile.size = sqlite3_column_int64(stmt, col++); sfile.type = sqlite3_column_int(stmt, col++); sfile.ext_len = sqlite3_column_bytes(stmt, col); sfile.ext_len = sfile.ext_len > MAX_FILEEXT_LEN ? MAX_FILEEXT_LEN : sfile.ext_len; sfile.ext = (const char*)sqlite3_column_text(stmt, col++); sfile.srcavail = sqlite3_column_int(stmt, col++); sfile.srccomplete = sqlite3_column_int(stmt, col++); sfile.rating = sqlite3_column_int(stmt, col++); sfile.rated_count = sqlite3_column_int(stmt, col++); sid = sqlite3_column_int64(stmt, col++); sfile.client_id = GET_SID_ID(sid); sfile.client_port = GET_SID_PORT(sid); sfile.media_length = sqlite3_column_int(stmt, col++); sfile.media_bitrate = sqlite3_column_int(stmt, col++); sfile.media_codec_len = sqlite3_column_bytes(stmt, col); sfile.media_codec_len = sfile.media_codec_len > MAX_FILEEXT_LEN ? MAX_FILEEXT_LEN : sfile.media_codec_len; sfile.media_codec = (const char*)sqlite3_column_text(stmt, col++); write_search_file(buf, &sfile); ++i; } DB_CHECK( (i==*count) || (SQLITE_DONE == err) ); *count = i; return 1; failed: if ( stmt ) sqlite3_finalize(stmt); ED2KD_LOGERR("failed perform search query (%s)", sqlite3_errmsg(s_db)); return 0; }
int PinnedMediaDBManager::selectPinnedMediaItem(const std::string& object_id, u64 device_id, PinnedMediaItem& output_pinned_media_item) { int rv = 0; int rc; static const char* SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID = "SELECT path, object_id, device_id " "FROM pin_item " "WHERE object_id=? AND device_id=?"; sqlite3_stmt *stmt = NULL; CHECK_DB_HANDLE(rv, m_db, end); rc = sqlite3_prepare_v2(m_db, SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID, -1, &stmt, NULL); CHECK_PREPARE(rv, rc, SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID, m_db, end); rc = sqlite3_bind_text(stmt, 1, object_id.c_str(), -1, SQLITE_STATIC); CHECK_BIND(rv, rc, m_db, end); rc = sqlite3_bind_int64(stmt, 2, device_id); CHECK_BIND(rv, rc, m_db, end); { std::string path; std::string object_id; u64 device_id; int sql_type; rc = sqlite3_step(stmt); CHECK_STEP(rv, rc, m_db, end); CHECK_ROW_EXIST(rv, rc, m_db, end); sql_type = sqlite3_column_type(stmt, 0); if (sql_type == SQLITE_TEXT) { path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0)); } else { LOG_WARN("Bad column type index: %d", 0); rv = -1; goto end; } sql_type = sqlite3_column_type(stmt, 1); if (sql_type == SQLITE_TEXT) { object_id = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1)); } else { LOG_WARN("Bad column type index: %d", 1); rv = -1; goto end; } sql_type = sqlite3_column_type(stmt, 2); if (sql_type == SQLITE_INTEGER) { device_id = sqlite3_column_int64(stmt, 2); } else { LOG_WARN("Bad column type index: %d", 2); rv = -1; goto end; } output_pinned_media_item.path = path; output_pinned_media_item.object_id = object_id; output_pinned_media_item.device_id = device_id; } end: FINALIZE_STMT(rv, rc, m_db, stmt); return rv; }
// Bind a 64bits int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement void Statement::bind(const int aIndex, const sqlite3_int64& aValue) { int ret = sqlite3_bind_int64(mStmtPtr, aIndex, aValue); check(ret); }
int sqlite3_bind_int(sqlite3_stmt *p, int i, int iValue){ return sqlite3_bind_int64(p, i, (i64)iValue); }
/* * Find unchanged files from a specified time, w.r.t to frequency, from the DB * Input: * query_callback : query callback fuction to handle * result records from the query * for_time : Time from where the file/s are not changed * freq_write_cnt : Frequency thresold for write * freq_read_cnt : Frequency thresold for read * clear_counters : Clear counters (r/w) for all inodes in DB * */ int gf_sqlite3_find_unchanged_for_time_freq (void *db_conn, gf_query_callback_t query_callback, void *query_cbk_args, gfdb_time_t *for_time, int freq_write_cnt, int freq_read_cnt, gf_boolean_t clear_counters) { int ret = -1; char *query_str = NULL; gf_sql_connection_t *sql_conn = db_conn; sqlite3_stmt *prep_stmt = NULL; long int for_time_usec = 0; CHECK_SQL_CONN (sql_conn, out); GF_VALIDATE_OR_GOTO (GFDB_STR_SQLITE3, query_callback, out); query_str = "select GF_FILE_TB.GF_ID," " (select group_concat( GF_PID || ',' || FNAME || ','" " || FPATH || ',' || W_DEL_FLAG ||',' || LINK_UPDATE , '::')" " from GF_FLINK_TB where GF_FILE_TB.GF_ID = GF_FLINK_TB.GF_ID)" " from GF_FILE_TB where " /*First condition: For Writes * Files that have write wind time smaller than for_time * OR * File that have write wind time greater than for_time, * but write_frequency less than freq_write_cnt*/ "( ((" GF_COL_TB_WSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_WMSEC ") < ? )" " OR " "( (" GF_COL_TB_WFC " < ? ) AND" "((" GF_COL_TB_WSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_WMSEC ") >= ? ) ) )" " OR " /*Second condition: For Reads * Files that have read wind time smaller than for_time * OR * File that have read wind time greater than for_time, * but write_frequency less than freq_write_cnt*/ "( ((" GF_COL_TB_RWSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_RWMSEC ") < ? )" " OR " "( (" GF_COL_TB_RFC " < ? ) AND" "((" GF_COL_TB_RWSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_RWMSEC ") >= ? ) ) )"; for_time_usec = gfdb_time_2_usec(for_time); ret = sqlite3_prepare (sql_conn->sqlite3_db_conn, query_str, -1, &prep_stmt, 0); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed preparing delete statment %s : %s", query_str, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind write wind time*/ ret = sqlite3_bind_int64 (prep_stmt, 1, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind write frequency thresold*/ ret = sqlite3_bind_int (prep_stmt, 2, freq_write_cnt); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding freq_write_cnt %d : %s", freq_write_cnt, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind write wind time*/ ret = sqlite3_bind_int64 (prep_stmt, 3, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read wind time*/ ret = sqlite3_bind_int64 (prep_stmt, 4, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read frequency thresold*/ ret = sqlite3_bind_int (prep_stmt, 5, freq_read_cnt); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding freq_read_cnt %d : %s", freq_read_cnt, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read wind time*/ ret = sqlite3_bind_int64 (prep_stmt, 6, for_time_usec); if (ret != SQLITE_OK) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed binding for_time_usec %ld : %s", for_time_usec, sqlite3_errmsg(sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Execute the query*/ ret = gf_sql_query_function (prep_stmt, query_callback, query_cbk_args); if (ret) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed Query %s", query_str); goto out; } /*Clear counters*/ if (clear_counters) { ret = gf_sql_clear_counters (sql_conn); if (ret) { gf_log (GFDB_STR_SQLITE3, GF_LOG_ERROR, "Failed clearing counters!"); goto out; } } ret = 0; out: sqlite3_finalize(prep_stmt); return ret; }
void QgsOSMDatabase::exportSpatiaLiteNodes( const QString& tableName, const QStringList& tagKeys, const QStringList& notNullTagKeys ) { QString sqlInsertPoint = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) ); for ( int i = 0; i < tagKeys.count(); ++i ) sqlInsertPoint += QString( ",?" ); sqlInsertPoint += ", GeomFromWKB(?, 4326))"; sqlite3_stmt* stmtInsert; if ( sqlite3_prepare_v2( mDatabase, sqlInsertPoint.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK ) { mError = "Prepare SELECT FROM nodes failed."; return; } QgsOSMNodeIterator nodes = listNodes(); QgsOSMNode n; while (( n = nodes.next() ).isValid() ) { QgsOSMTags t = tags( false, n.id() ); // skip untagged nodes: probably they form a part of ways if ( t.count() == 0 ) continue; //check not null tags bool skipNull = false; for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i ) if ( !t.contains( notNullTagKeys[i] ) ) skipNull = true; if ( skipNull ) continue; QgsGeometry* geom = QgsGeometry::fromPoint( n.point() ); int col = 0; sqlite3_bind_int64( stmtInsert, ++col, n.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(), ( int ) geom->wkbSize(), SQLITE_STATIC ); int insertRes = sqlite3_step( stmtInsert ); if ( insertRes != SQLITE_DONE ) { mError = QString( "Error inserting node %1 [%2]" ).arg( n.id() ).arg( insertRes ); delete geom; break; } sqlite3_reset( stmtInsert ); sqlite3_clear_bindings( stmtInsert ); delete geom; } sqlite3_finalize( stmtInsert ); }