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