int main (int argc, char **argv) { sqlite3 *db; // Definimos un puntero a la base de datos char *errMsg = 0; // Variable para el mensaje de error int rc; // Variable para el retorno de la sentencia sqlite3_stmt *result; // Puntero a la respuesta de la consulta // Abro la conexión con la base de datos rc = sqlite3_open("BDPrueba.sqlite", &db); // Compruebo que no hay error if (rc != SQLITE_OK) { fprintf(stderr, "No se puede acceder a la base de datos: %s.\n", sqlite3_errmsg(db)); sqlite3_close(db); return(1); } // Borro la tabla si no existe rc = sqlite3_exec(db, "DROP TABLE IF EXISTS Empresa", NULL, NULL, &errMsg); if (rc != SQLITE_OK) { fprintf(stderr, "Error al borrar la tabla: %s.\n", errMsg); sqlite3_free(errMsg); sqlite3_close(db); return(2); } // Creo la tabla Empresa rc = sqlite3_exec(db, "CREATE TABLE Empresa (IdEmpresa INTEGER PRIMARY KEY, Nombre CHAR[250])", NULL, NULL, &errMsg); if (rc != SQLITE_OK) { fprintf(stderr, "Error al crear la tabla: %s.\n", errMsg); sqlite3_free(errMsg); sqlite3_close(db); return(2); } // Inserto un par de registros rc = sqlite3_exec(db, "INSERT INTO Empresa VALUES( 1, 'Empresa A')", NULL, NULL, &errMsg); if (rc != SQLITE_OK) { fprintf(stderr, "Error al crear el primer registro: %s.\n", errMsg); sqlite3_free(errMsg); sqlite3_close(db); return(2); } rc = sqlite3_exec(db, "INSERT INTO Empresa VALUES( 2, 'Empresa B')", NULL, NULL, &errMsg); if (rc != SQLITE_OK) { fprintf(stderr, "Error al crear el segundo registro: %s.\n", errMsg); sqlite3_free(errMsg); sqlite3_close(db); return(2); } // Consulta a realizar sobre la tabla. // En este caso quiero los campos idEmpresa y Nombre de la tabla Empresa rc = sqlite3_prepare(db, "SELECT idEmpresa,Nombre FROM Empresa", -1, &result, NULL); // Compruebo que no hay error if (rc != SQLITE_OK) { fprintf(stderr, "Error en la consulta: %s.\n", sqlite3_errmsg(db)); sqlite3_close(db); return(3); } // Bucle de presentación en pantalla del resultado de la consulta while ( sqlite3_step(result) == SQLITE_ROW) { fprintf(stderr, "El Id y nombre de la empresa son: %i - %s.\n", sqlite3_column_int(result, 0) , sqlite3_column_text(result, 1)); } // Cierro la conexión sqlite3_close(db); return 0; }
DatabaseResult Database::exec(const String sqlStmt) { DatabaseColumn dbCol; DatabaseRow dbRow; DatabaseResult dbRes; if(sqliteStatus != SQLITE_OK) throw DatabaseNotOpenException() << DebugInfo(TRACE(), DebugInfo::Error); sqliteStatus = sqlite3_prepare_v2(db, sqlStmt.data(), -1, &dbStmt, NULL); if(sqliteStatus != SQLITE_OK) throw DatabaseStatementInvalidException() << DebugInfo(String(sqlStmt) << "\n\"" << sqlite3_errmsg(db) << "\"\n" << TRACE(), DebugInfo::Error); int iCol = 0, colType = -1; int colCount = sqlite3_column_count(dbStmt); int stepRet = sqlite3_step(dbStmt); bool isNoResult = true; if(stepRet == SQLITE_ROW) isNoResult = false; while(stepRet == SQLITE_ROW) { for(iCol=0; iCol<colCount; ++iCol) { colType = sqlite3_column_type(dbStmt, iCol); if(colType == SQLITE_INTEGER) { dbCol = DatabaseColumn(sqlite3_column_int(dbStmt, iCol)); dbCol.setInt64Data(sqlite3_column_int64(dbStmt, iCol)); } else if(colType == SQLITE_FLOAT) { dbCol = DatabaseColumn(sqlite3_column_double(dbStmt, iCol)); } else if(colType == SQLITE_TEXT) { dbCol = DatabaseColumn(String(sqlite3_column_text(dbStmt, iCol))); } else if(colType == SQLITE_BLOB) { dbCol = DatabaseColumn((unsigned char*)sqlite3_column_blob(dbStmt, iCol)); } else if(colType == SQLITE_NULL) { dbCol = DatabaseColumn(true); } dbRow.addColumn(dbCol); } dbRes.addRowData(dbRow); dbRow = DatabaseRow(); stepRet = sqlite3_step(dbStmt); } sqlite3_finalize(dbStmt); if(stepRet != SQLITE_DONE) throw DatabaseResultNotDoneException() << DebugInfo(sqlite3_errmsg(db) << TRACE(), DebugInfo::Error); if(isNoResult == true) dbRes.setRowChanged(sqlite3_changes(db)); return dbRes; }
static int create_spatial_index(sqlite3 *db, const char *db_name, const char *table_name, const char *geometry_column_name, const char *id_column_name, errorstream_t *error) { int result = SQLITE_OK; char *index_table_name = NULL; int exists = 0; index_table_name = sqlite3_mprintf("rtree_%s_%s", table_name, geometry_column_name); if (index_table_name == NULL) { result = SQLITE_NOMEM; goto exit; } // Check if the target table exists exists = 0; result = sql_check_table_exists(db, db_name, index_table_name, &exists); if (result != SQLITE_OK) { error_append(error, "Could not check if index table %s.%s exists: %s", db_name, index_table_name, sqlite3_errmsg(db)); goto exit; } if (exists) { result = SQLITE_OK; goto exit; } // Check if the target table exists exists = 0; result = sql_check_table_exists(db, db_name, table_name, &exists); if (result != SQLITE_OK) { error_append(error, "Could not check if table %s.%s exists: %s", db_name, table_name, sqlite3_errmsg(db)); goto exit; } if (!exists) { error_append(error, "Table %s.%s does not exist", db_name, table_name); goto exit; } int geom_col_count = 0; result = sql_exec_for_int(db, &geom_col_count, "SELECT count(*) FROM \"%w\".gpkg_geometry_columns WHERE table_name LIKE %Q AND column_name LIKE %Q", db_name, table_name, geometry_column_name); if (result != SQLITE_OK) { error_append(error, "Could not check if column %s.%s.%s exists in %s.gpkg_geometry_columns: %s", db_name, table_name, geometry_column_name, db_name, sqlite3_errmsg(db)); goto exit; } if (geom_col_count == 0) { error_append(error, "Column %s.%s.%s is not registered in %s.gpkg_geometry_columns", db_name, table_name, geometry_column_name, db_name); goto exit; } result = sql_exec(db, "CREATE VIRTUAL TABLE \"%w\".\"%w\" USING rtree(id, minx, maxx, miny, maxy)", db_name, index_table_name); if (result != SQLITE_OK) { error_append(error, "Could not create rtree table %s.%s: %s", db_name, index_table_name, sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_insert\" AFTER INSERT ON \"%w\"\n" " WHEN (NEW.\"%w\" NOTNULL AND NOT ST_IsEmpty(NEW.\"%w\"))\n" "BEGIN\n" " INSERT OR REPLACE INTO \"%w\" VALUES (\n" " NEW.\"%w\",\n" " ST_MinX(NEW.\"%w\"), ST_MaxX(NEW.\"%w\"),\n" " ST_MinY(NEW.\"%w\"), ST_MaxY(NEW.\"%w\")\n" " );\n" "END;", db_name, table_name, geometry_column_name, table_name, geometry_column_name, geometry_column_name, index_table_name, id_column_name, geometry_column_name, geometry_column_name, geometry_column_name, geometry_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree insert trigger: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_update1\" AFTER UPDATE OF \"%w\" ON \"%w\"\n" " WHEN OLD.\"%w\" = NEW.\"%w\" AND\n" " (NEW.\"%w\" NOTNULL AND NOT ST_IsEmpty(NEW.\"%w\"))\n" "BEGIN\n" " INSERT OR REPLACE INTO \"%w\" VALUES (\n" " NEW.\"%w\",\n" " ST_MinX(NEW.\"%w\"), ST_MaxX(NEW.\"%w\"),\n" " ST_MinY(NEW.\"%w\"), ST_MaxY(NEW.\"%w\")\n" " );\n" "END;", db_name, table_name, geometry_column_name, geometry_column_name, table_name, id_column_name, id_column_name, geometry_column_name, geometry_column_name, index_table_name, id_column_name, geometry_column_name, geometry_column_name, geometry_column_name, geometry_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree update trigger 1: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_update2\" AFTER UPDATE OF \"%w\" ON \"%w\"\n" " WHEN OLD.\"%w\" = NEW.\"%w\" AND\n" " (NEW.\"%w\" ISNULL OR ST_IsEmpty(NEW.\"%w\"))\n" "BEGIN\n" " DELETE FROM \"%w\" WHERE id = OLD.\"%w\";\n" "END;", db_name, table_name, geometry_column_name, geometry_column_name, table_name, id_column_name, id_column_name, geometry_column_name, geometry_column_name, index_table_name, id_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree update trigger 2: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_update3\" AFTER UPDATE ON \"%w\"\n" " WHEN OLD.\"%w\" != NEW.\"%w\" AND\n" " (NEW.\"%w\" NOTNULL AND NOT ST_IsEmpty(NEW.\"%w\"))\n" "BEGIN\n" " DELETE FROM \"%w\" WHERE id = OLD.\"%w\";\n" " INSERT OR REPLACE INTO \"%w\" VALUES (\n" " NEW.\"%w\",\n" " ST_MinX(NEW.\"%w\"), ST_MaxX(NEW.\"%w\"),\n" " ST_MinY(NEW.\"%w\"), ST_MaxY(NEW.\"%w\")\n" " );\n" "END;", db_name, table_name, geometry_column_name, table_name, id_column_name, id_column_name, geometry_column_name, geometry_column_name, index_table_name, id_column_name, index_table_name, id_column_name, geometry_column_name, geometry_column_name, geometry_column_name, geometry_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree update trigger 3: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_update4\" AFTER UPDATE ON \"%w\"\n" " WHEN OLD.\"%w\" != NEW.\"%w\" AND\n" " (NEW.\"%w\" ISNULL OR ST_IsEmpty(NEW.\"%w\"))\n" "BEGIN\n" " DELETE FROM \"%w\" WHERE id IN (OLD.\"%w\", NEW.\"%w\");\n" "END;", db_name, table_name, geometry_column_name, table_name, id_column_name, id_column_name, geometry_column_name, geometry_column_name, index_table_name, id_column_name, id_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree update trigger 4: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "CREATE TRIGGER \"%w\".\"rtree_%w_%w_delete\" AFTER DELETE ON \"%w\"\n" "BEGIN\n" " DELETE FROM \"%w\" WHERE id = OLD.\"%w\";\n" "END;", db_name, table_name, geometry_column_name, table_name, index_table_name, id_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not create rtree delete trigger: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "INSERT OR REPLACE INTO \"%w\".\"%w\" (id, minx, maxx, miny, maxy) " " SELECT \"%w\", ST_MinX(\"%w\"), ST_MaxX(\"%w\"), ST_MinY(\"%w\"), ST_MaxY(\"%w\") FROM \"%w\".\"%w\"" " WHERE \"%w\" NOTNULL AND NOT ST_IsEmpty(\"%w\")", db_name, index_table_name, id_column_name, geometry_column_name, geometry_column_name, geometry_column_name, geometry_column_name, db_name, table_name, geometry_column_name, geometry_column_name ); if (result != SQLITE_OK) { error_append(error, "Could not populate rtree: %s", sqlite3_errmsg(db)); goto exit; } result = sql_exec( db, "INSERT OR REPLACE INTO \"%w\".\"gpkg_extensions\" (table_name, column_name, extension_name, definition, scope) VALUES (\"%w\", \"%w\", \"%w\", \"%w\", \"%w\")", db_name, table_name, geometry_column_name, "gpkg_rtree_index", "GeoPackage 1.0 Specification Annex L", "write-only" ); if (result != SQLITE_OK) { error_append(error, "Could not register rtree usage in gpkg_extensions: %s", sqlite3_errmsg(db)); goto exit; } exit: sqlite3_free(index_table_name); return result; }
int sqlite3_stepx (sqlite3 *db, sqlite3_stmt **stmtp, sqlite3_stepx_callback callback, void *cookie, char **errmsg, const char *sql, ...) { int rc; int err = 0; sqlite3_stmt *stmt = NULL; va_list va; int args; enum sqlite_arg_type t; int i; int cols; /* Names of the columns. We initialize this lazily to avoid the overhead in case the query doesn't return any results. */ const char **azColName = 0; int callback_initialized = 0; const char **azVals = 0; callback_initialized = 0; if (stmtp && *stmtp) { stmt = *stmtp; /* Make sure this statement is associated with the supplied db. */ assert (db == sqlite3_db_handle (stmt)); #if DEBUG_TOFU_CACHE prepares_saved ++; #endif } else { const char *tail = NULL; rc = sqlite3_prepare_v2 (db, sql, -1, &stmt, &tail); if (rc) log_fatal ("failed to prepare SQL: %s", sql); /* We can only process a single statement. */ if (tail) { while (*tail == ' ' || *tail == ';' || *tail == '\n') tail ++; if (*tail) log_fatal ("sqlite3_stepx can only process a single SQL statement." " Second statement starts with: '%s'\n", tail); } if (stmtp) *stmtp = stmt; } #if DEBUG_TOFU_CACHE queries ++; #endif args = sqlite3_bind_parameter_count (stmt); va_start (va, sql); if (args) { for (i = 1; i <= args; i ++) { t = va_arg (va, enum sqlite_arg_type); switch (t) { case SQLITE_ARG_INT: { int value = va_arg (va, int); err = sqlite3_bind_int (stmt, i, value); break; } case SQLITE_ARG_LONG_LONG: { long long value = va_arg (va, long long); err = sqlite3_bind_int64 (stmt, i, value); break; } case SQLITE_ARG_STRING: { char *text = va_arg (va, char *); err = sqlite3_bind_text (stmt, i, text, -1, SQLITE_STATIC); break; } case SQLITE_ARG_BLOB: { char *blob = va_arg (va, void *); long long length = va_arg (va, long long); err = sqlite3_bind_blob (stmt, i, blob, length, SQLITE_STATIC); break; } default: /* Internal error. Likely corruption. */ log_fatal ("Bad value for parameter type %d.\n", t); } if (err) { log_fatal ("Error binding parameter %d\n", i); goto out; } } } t = va_arg (va, enum sqlite_arg_type); assert (t == SQLITE_ARG_END); va_end (va); for (;;) { rc = sqlite3_step (stmt); if (rc != SQLITE_ROW) /* No more data (SQLITE_DONE) or an error occurred. */ break; if (! callback) continue; if (! callback_initialized) { cols = sqlite3_column_count (stmt); azColName = xmalloc (2 * cols * sizeof (const char *) + 1); for (i = 0; i < cols; i ++) azColName[i] = sqlite3_column_name (stmt, i); callback_initialized = 1; } azVals = &azColName[cols]; for (i = 0; i < cols; i ++) { azVals[i] = sqlite3_column_text (stmt, i); if (! azVals[i] && sqlite3_column_type (stmt, i) != SQLITE_NULL) /* Out of memory. */ { err = SQLITE_NOMEM; break; } } if (callback (cookie, cols, (char **) azVals, (char **) azColName, stmt)) /* A non-zero result means to abort. */ { err = SQLITE_ABORT; break; } } out: xfree (azColName); if (stmtp) rc = sqlite3_reset (stmt); else rc = sqlite3_finalize (stmt); if (rc == SQLITE_OK && err) /* Local error. */ { rc = err; if (errmsg) { const char *e = sqlite3_errstr (err); size_t l = strlen (e) + 1; *errmsg = sqlite3_malloc (l); if (! *errmsg) log_fatal ("Out of memory.\n"); memcpy (*errmsg, e, l); } } else if (rc != SQLITE_OK && errmsg) /* Error reported by sqlite. */ { const char * e = sqlite3_errmsg (db); size_t l = strlen (e) + 1; *errmsg = sqlite3_malloc (l); if (! *errmsg) log_fatal ("Out of memory.\n"); memcpy (*errmsg, e, l); } return rc; }
int sql_query ( dbref player, char *q_string, char *buff, char **bufc, const Delim *row_delim, const Delim *field_delim ) { sqlite3 *sqlite; const unsigned char *col_data; int num_rows, got_rows, got_fields; int i, j; int retries; int retval; sqlite3_stmt *stmt; const char *rest; /* * If we have no connection, and we don't have auto-reconnect on (or * we try to auto-reconnect and we fail), this is an error generating * a #-1. Notify the player, too, and set the return code. */ sqlite = sqlite3_struct; if ( ( !sqlite ) && ( mod_db_sql_config.reconnect != 0 ) ) { /* * Try to reconnect. */ retries = 0; while ( ( retries < SQLITE_RETRY_TIMES ) && !sqlite ) { sleep ( 1 ); sql_init ( 0, 0, NULL, NULL ); sqlite = sqlite3_struct; retries++; } } if ( !sqlite ) { notify_quiet ( player, "No SQL database connection." ); if ( buff ) safe_str ( "#-1", buff, bufc ); return -1; } if ( !q_string || !*q_string ) return 0; /* * Prepare the query. */ retval = sqlite3_prepare_v2 ( sqlite, q_string, -1, &stmt, &rest ); if ( retval != SQLITE_OK ) { notify_quiet ( player, sqlite3_errmsg ( sqlite ) ); if ( buff ) safe_str ( "#-1", buff, bufc ); sqlite3_finalize ( stmt ); return -1; } /* * Construct properly-delimited data. */ if ( buff ) { i = 0; while ( sqlite3_step ( stmt ) == SQLITE_ROW ) { if ( i++ > 0 ) { print_sep ( row_delim, buff, bufc ); } got_fields = sqlite3_column_count ( stmt ); if ( got_fields ) { for ( j = 0; j < got_fields; j++ ) { col_data = sqlite3_column_text ( stmt, j ); if ( j > 0 ) { print_sep ( field_delim, buff, bufc ); } if ( col_data && *col_data ) safe_str ( ( char * ) col_data, buff, bufc ); } } } } else { i = 0; while ( sqlite3_step ( stmt ) == SQLITE_ROW ) { if ( i++ > 0 ) { print_sep ( row_delim, buff, bufc ); } got_fields = sqlite3_column_count ( stmt ); if ( got_fields ) { for ( j = 0; j < got_fields; j++ ) { col_data = sqlite3_column_text ( stmt, j ); if ( j > 0 ) { notify_check ( player, player, MSG_PUP_ALWAYS | MSG_ME_ALL | MSG_F_DOWN, "Row %d, Field %d: %s", i, j + 1, col_data ); } if ( col_data && *col_data ) { notify_check ( player, player, MSG_PUP_ALWAYS | MSG_ME_ALL | MSG_F_DOWN, "Row %d, Field %d: NULL", i, j + 1 ); } } } } } if ( i == 0 ) { num_rows = sqlite3_changes ( sqlite ); if ( num_rows > 0 ) { notify_check ( player, player, MSG_PUP_ALWAYS | MSG_ME_ALL | MSG_F_DOWN, "SQL query touched %d %s.", num_rows, ( num_rows == 1 ) ? "row" : "rows" ); } } sqlite3_finalize ( stmt ); return 0; }
result_t SQLite::execute(const char *sql, int sLen, obj_ptr<DBResult_base> &retVal) { if (!m_db) return CHECK_ERROR(CALL_E_INVALID_CALL); sqlite3_stmt *stmt = 0; const char *pStr1; if (sqlite3_prepare_v2(m_db, sql, sLen, &stmt, &pStr1)) { result_t hr = CHECK_ERROR(Runtime::setError(sqlite3_errmsg(m_db))); if (stmt) sqlite3_finalize(stmt); return hr; } if (!stmt) return CHECK_ERROR(Runtime::setError("Query was empty")); int columns = sqlite3_column_count(stmt); obj_ptr<DBResult> res; if (columns > 0) { int i; res = new DBResult(columns); for (i = 0; i < columns; i++) { std::string s = sqlite3_column_name(stmt, i); res->setField(i, s); } while (true) { int r = sqlite3_step_sleep(stmt, SQLITE_SLEEP_TIME); if (r == SQLITE_ROW) { res->beginRow(); for (i = 0; i < columns; i++) { Variant v; switch (sqlite3_column_type(stmt, i)) { case SQLITE_NULL: break; case SQLITE_INTEGER: v = (int64_t) sqlite3_column_int64(stmt, i); break; case SQLITE_FLOAT: v = sqlite3_column_double(stmt, i); break; default: const char *type = sqlite3_column_decltype(stmt, i); if (type && (!qstricmp(type, "blob") || !qstricmp(type, "tinyblob") || !qstricmp(type, "mediumblob") || !qstricmp(type, "longblob"))) { const char *data = (const char *) sqlite3_column_blob(stmt, i); int size = sqlite3_column_bytes(stmt, i); v = new Buffer(std::string(data, size)); } else if (type && (!qstricmp(type, "datetime") || !qstricmp(type, "date") || !qstricmp(type, "time"))) { const char *data = (const char *) sqlite3_column_text(stmt, i); int size = sqlite3_column_bytes(stmt, i); v.parseDate(data, size); } else { const char *data = (const char *) sqlite3_column_text(stmt, i); int size = sqlite3_column_bytes(stmt, i); v = std::string(data, size); } break; } res->rowValue(i, v); } res->endRow(); } else if (r == SQLITE_DONE) break; else { sqlite3_finalize(stmt); return CHECK_ERROR(Runtime::setError(sqlite3_errmsg(m_db))); } } } else { int r = sqlite3_step_sleep(stmt, SQLITE_SLEEP_TIME); if (r == SQLITE_DONE) res = new DBResult(sqlite3_changes(m_db), sqlite3_last_insert_rowid(m_db)); else { sqlite3_finalize(stmt); return CHECK_ERROR(Runtime::setError(sqlite3_errmsg(m_db))); } } sqlite3_finalize(stmt); retVal = res; return 0; }
static int cache_create(void) { #define Q_PRAGMA_CACHE_SIZE "PRAGMA cache_size=%d;" #define Q_PRAGMA_JOURNAL_MODE "PRAGMA journal_mode=%s;" #define Q_PRAGMA_SYNCHRONOUS "PRAGMA synchronous=%d;" char *errmsg; int ret; int cache_size; char *journal_mode; int synchronous; char *query; // Open db ret = sqlite3_open(g_db_path, &g_db_hdl); if (ret != SQLITE_OK) { DPRINTF(E_FATAL, L_CACHE, "Could not open database: %s\n", sqlite3_errmsg(g_db_hdl)); sqlite3_close(g_db_hdl); return -1; } // Check cache version ret = cache_check_version(); if (ret < 0) { DPRINTF(E_FATAL, L_CACHE, "Could not check cache database version\n"); sqlite3_close(g_db_hdl); return -1; } else if (ret > 0) { ret = cache_create_tables(); if (ret < 0) { DPRINTF(E_FATAL, L_CACHE, "Could not create database tables\n"); sqlite3_close(g_db_hdl); return -1; } } // Set page cache size in number of pages cache_size = cfg_getint(cfg_getsec(cfg, "sqlite"), "pragma_cache_size_cache"); if (cache_size > -1) { query = sqlite3_mprintf(Q_PRAGMA_CACHE_SIZE, cache_size); ret = sqlite3_exec(g_db_hdl, query, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { DPRINTF(E_FATAL, L_CACHE, "Error creating query index: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(g_db_hdl); return -1; } } // Set journal mode journal_mode = cfg_getstr(cfg_getsec(cfg, "sqlite"), "pragma_journal_mode"); if (journal_mode) { query = sqlite3_mprintf(Q_PRAGMA_JOURNAL_MODE, journal_mode); ret = sqlite3_exec(g_db_hdl, query, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { DPRINTF(E_FATAL, L_CACHE, "Error creating query index: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(g_db_hdl); return -1; } } // Set synchronous flag synchronous = cfg_getint(cfg_getsec(cfg, "sqlite"), "pragma_synchronous"); if (synchronous > -1) { query = sqlite3_mprintf(Q_PRAGMA_SYNCHRONOUS, synchronous); ret = sqlite3_exec(g_db_hdl, query, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { DPRINTF(E_FATAL, L_CACHE, "Error creating query index: %s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(g_db_hdl); return -1; } } DPRINTF(E_DBG, L_CACHE, "Cache created\n"); return 0; #undef Q_PRAGMA_CACHE_SIZE #undef Q_PRAGMA_JOURNAL_MODE #undef Q_PRAGMA_SYNCHRONOUS }
void raise_sqlite_error(::sqlite3* db) { std::string s = "SQLite3 Error: "; s += sqlite3_errmsg(db); throw exception(s.c_str()); }
int database::qry_working_orders(const std::string &investorId, std::string &seqnum, long maxrec,int date, TradeGateway::OrderReportSeq &list) { int retnum =0; if( !this->is_connected() ) this->connect(); std::string seq(seqnum); if( seq.length() == 0 ) { seq = "0"; } std::ostringstream sql; sql << "SELECT *,rowid FROM dt_orders WHERE (accountId='" << investorId << "') AND (date=" << date << ") AND (rowid>" << seq << ") AND ( ordStatus < " << TradeGateway::PendingCancel <<") order by rowid" <<" LIMIT "<< maxrec; sqlite3_stmt *pstmt = NULL; const char * pzTail = NULL; int rc = sqlite3_prepare_v2(pdb,sql.str().c_str(),sql.str().length(),&pstmt,&pzTail); if (rc !=SQLITE_OK) { std::ostringstream err ; err << "Can't prepare query: " << sqlite3_errmsg(pdb); throw err.str(); } else { while( 1 ) { rc = sqlite3_step(pstmt); if (rc == SQLITE_ERROR) { std::ostringstream err ; err << "Can't step record: " << sqlite3_errmsg(pdb); sqlite3_finalize( pstmt ); throw err.str(); } else if( rc ==SQLITE_MISUSE || rc == SQLITE_DONE ) break; else if( rc == SQLITE_BUSY ) { continue; } else if( rc == SQLITE_ROW ) { TradeGateway::OrderReport ord; ord.base.accountId=(const char*)sqlite3_column_text(pstmt,0); ord.base.inst.securityID = (const char*)sqlite3_column_text(pstmt,1); ord.base.inst.securityIDSource = (TradeGateway::MarketIdSource)sqlite3_column_int(pstmt,2); ord.base.s = (TradeGateway::Side)sqlite3_column_int(pstmt,3); ord.base.posEfct = (TradeGateway::PositionEffect)sqlite3_column_int(pstmt,4); ord.base.type = (TradeGateway::OrdType)sqlite3_column_int(pstmt,5); ord.base.lmtPrice = sqlite3_column_double(pstmt,6); ord.base.ordQty = sqlite3_column_int(pstmt,7); ord.base.ordId = (const char*)sqlite3_column_text(pstmt,8); if( ord.base.ordId.length()== 0 ) ord.base.ordId = (const char*)sqlite3_column_text(pstmt,16); ord.ordStatus = (TradeGateway::OrderStatus)sqlite3_column_int(pstmt,9); ord.base.date = sqlite3_column_int(pstmt,10); ord.base.time = sqlite3_column_int(pstmt,11); ord.cumQty = sqlite3_column_int(pstmt,12); ord.avgPx = sqlite3_column_double(pstmt,13); ord.leavesQty = sqlite3_column_int(pstmt,14); ord.text = (const char*)sqlite3_column_text(pstmt,15); ord.seq = (const char*)sqlite3_column_text(pstmt,16); seqnum=ord.seq; list.push_back(ord); retnum++; } } sqlite3_finalize( pstmt ); } return retnum; }
int database::qry_order_by_ordid(const std::string &investorId,const std::string &id,int date, TradeGateway::OrderReport &ord) { int retnum=0; if( !this->is_connected() ) this->connect(); std::ostringstream sql; sql << "SELECT *,rowid FROM dt_orders WHERE (accountId='" << investorId << "') AND (date=" << date << ") AND (((ordId='') AND (rowid='" << id << "')) OR (ordId='" << id << "'));"; sqlite3_stmt *pstmt = NULL; const char * pzTail = NULL; int rc = sqlite3_prepare_v2(pdb,sql.str().c_str(),sql.str().length(),&pstmt,&pzTail); if (rc !=SQLITE_OK) { std::ostringstream err ; err << "Can't prepare query: " << sqlite3_errmsg(pdb); throw err.str(); } else { while( 1 ) { rc = sqlite3_step(pstmt); if (rc == SQLITE_ERROR) { std::ostringstream err ; err << "Can't step record: " << sqlite3_errmsg(pdb); sqlite3_finalize( pstmt ); throw err.str(); } else if( rc ==SQLITE_MISUSE || rc == SQLITE_DONE ) break; else if( rc == SQLITE_BUSY ) { continue; } else if( rc == SQLITE_ROW ) { ord.base.accountId=(const char*)sqlite3_column_text(pstmt,0); ord.base.inst.securityID = (const char*)sqlite3_column_text(pstmt,1); ord.base.inst.securityIDSource = (TradeGateway::MarketIdSource)sqlite3_column_int(pstmt,2); ord.base.s = (TradeGateway::Side)sqlite3_column_int(pstmt,3); ord.base.posEfct = (TradeGateway::PositionEffect)sqlite3_column_int(pstmt,4); ord.base.type = (TradeGateway::OrdType)sqlite3_column_int(pstmt,5); ord.base.lmtPrice = sqlite3_column_double(pstmt,6); ord.base.ordQty = sqlite3_column_int(pstmt,7); ord.base.ordId = (const char*)sqlite3_column_text(pstmt,8); if( ord.base.ordId.length()== 0 ) ord.base.ordId = (const char*)sqlite3_column_text(pstmt,16); ord.ordStatus = (TradeGateway::OrderStatus)sqlite3_column_int(pstmt,9); ord.base.date = sqlite3_column_int(pstmt,10); ord.base.time = sqlite3_column_int(pstmt,11); ord.cumQty = sqlite3_column_int(pstmt,12); ord.avgPx = sqlite3_column_double(pstmt,13); ord.leavesQty = sqlite3_column_int(pstmt,14); ord.text =siconv((const char*)sqlite3_column_text(pstmt,15),"UTF-8","GB2312"); ord.seq = (const char*)sqlite3_column_text(pstmt,16); retnum++; } } sqlite3_finalize( pstmt ); } return retnum; }
OGRLayer * OGRSQLiteExecuteSQL( GDALDataset* poDS, const char *pszStatement, OGRGeometry *poSpatialFilter, CPL_UNUSED const char *pszDialect ) { char* pszTmpDBName = (char*) CPLMalloc(256); sprintf(pszTmpDBName, "/vsimem/ogr2sqlite/temp_%p.db", pszTmpDBName); OGRSQLiteDataSource* poSQLiteDS = NULL; int nRet; int bSpatialiteDB = FALSE; CPLString osOldVal; const char* pszOldVal = CPLGetConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", NULL); if( pszOldVal != NULL ) { osOldVal = pszOldVal; pszOldVal = osOldVal.c_str(); } /* -------------------------------------------------------------------- */ /* Create in-memory sqlite/spatialite DB */ /* -------------------------------------------------------------------- */ #ifdef HAVE_SPATIALITE /* -------------------------------------------------------------------- */ /* Creating an empty spatialite DB (with spatial_ref_sys populated */ /* has a non-neglectable cost. So at the first attempt, let's make */ /* one and cache it for later use. */ /* -------------------------------------------------------------------- */ #if 1 static vsi_l_offset nEmptyDBSize = 0; static GByte* pabyEmptyDB = NULL; { static CPLMutex* hMutex = NULL; CPLMutexHolder oMutexHolder(&hMutex); static int bTried = FALSE; if( !bTried && CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) ) { bTried = TRUE; char* pszCachedFilename = (char*) CPLMalloc(256); sprintf(pszCachedFilename, "/vsimem/ogr2sqlite/reference_%p.db",pszCachedFilename); char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES"); OGRSQLiteDataSource* poCachedDS = new OGRSQLiteDataSource(); nRet = poCachedDS->Create( pszCachedFilename, papszOptions ); CSLDestroy(papszOptions); papszOptions = NULL; delete poCachedDS; if( nRet ) /* Note: the reference file keeps the ownership of the data, so that */ /* it gets released with VSICleanupFileManager() */ pabyEmptyDB = VSIGetMemFileBuffer( pszCachedFilename, &nEmptyDBSize, FALSE ); CPLFree( pszCachedFilename ); } } /* The following configuration option is useful mostly for debugging/testing */ if( pabyEmptyDB != NULL && CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) ) { GByte* pabyEmptyDBClone = (GByte*)VSIMalloc(nEmptyDBSize); if( pabyEmptyDBClone == NULL ) { CPLFree(pszTmpDBName); return NULL; } memcpy(pabyEmptyDBClone, pabyEmptyDB, nEmptyDBSize); VSIFCloseL(VSIFileFromMemBuffer( pszTmpDBName, pabyEmptyDBClone, nEmptyDBSize, TRUE )); poSQLiteDS = new OGRSQLiteDataSource(); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO"); nRet = poSQLiteDS->Open( pszTmpDBName, TRUE, NULL ); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal); if( !nRet ) { /* should not happen really ! */ delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } bSpatialiteDB = TRUE; } #else /* No caching version */ poSQLiteDS = new OGRSQLiteDataSource(); char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES"); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO"); nRet = poSQLiteDS->Create( pszTmpDBName, papszOptions ); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal); CSLDestroy(papszOptions); papszOptions = NULL; if( nRet ) { bSpatialiteDB = TRUE; } #endif else { delete poSQLiteDS; poSQLiteDS = NULL; #else // HAVE_SPATIALITE if( TRUE ) { #endif // HAVE_SPATIALITE poSQLiteDS = new OGRSQLiteDataSource(); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO"); nRet = poSQLiteDS->Create( pszTmpDBName, NULL ); CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal); if( !nRet ) { delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } } /* -------------------------------------------------------------------- */ /* Attach the Virtual Table OGR2SQLITE module to it. */ /* -------------------------------------------------------------------- */ OGR2SQLITEModule* poModule = OGR2SQLITE_Setup(poDS, poSQLiteDS); sqlite3* hDB = poSQLiteDS->GetDB(); /* -------------------------------------------------------------------- */ /* Analysze the statement to determine which tables will be used. */ /* -------------------------------------------------------------------- */ std::set<LayerDesc> oSetLayers; std::set<CPLString> oSetSpatialIndex; CPLString osModifiedSQL; OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers, oSetSpatialIndex, osModifiedSQL); std::set<LayerDesc>::iterator oIter = oSetLayers.begin(); if( strcmp(pszStatement, osModifiedSQL.c_str()) != 0 ) CPLDebug("OGR", "Modified SQL: %s", osModifiedSQL.c_str()); pszStatement = osModifiedSQL.c_str(); /* do not use it anymore */ int bFoundOGRStyle = ( osModifiedSQL.ifind("OGR_STYLE") != std::string::npos ); /* -------------------------------------------------------------------- */ /* For each of those tables, create a Virtual Table. */ /* -------------------------------------------------------------------- */ for(; oIter != oSetLayers.end(); ++oIter) { const LayerDesc& oLayerDesc = *oIter; /*CPLDebug("OGR", "Layer desc : %s, %s, %s, %s", oLayerDesc.osOriginalStr.c_str(), oLayerDesc.osSubstitutedName.c_str(), oLayerDesc.osDSName.c_str(), oLayerDesc.osLayerName.c_str());*/ CPLString osSQL; OGRLayer* poLayer = NULL; CPLString osTableName; int nExtraDS; if( oLayerDesc.osDSName.size() == 0 ) { poLayer = poDS->GetLayerByName(oLayerDesc.osLayerName); /* Might be a false positive (unlikely) */ if( poLayer == NULL ) continue; osTableName = oLayerDesc.osLayerName; nExtraDS = -1; } else { OGRDataSource* poOtherDS = (OGRDataSource* ) OGROpen(oLayerDesc.osDSName, FALSE, NULL); if( poOtherDS == NULL ) { CPLError(CE_Failure, CPLE_AppDefined, "Cannot open datasource '%s'", oLayerDesc.osDSName.c_str() ); delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } poLayer = poOtherDS->GetLayerByName(oLayerDesc.osLayerName); if( poLayer == NULL ) { CPLError(CE_Failure, CPLE_AppDefined, "Cannot find layer '%s' in '%s'", oLayerDesc.osLayerName.c_str(), oLayerDesc.osDSName.c_str() ); delete poOtherDS; delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } osTableName = oLayerDesc.osSubstitutedName; nExtraDS = OGR2SQLITE_AddExtraDS(poModule, poOtherDS); } osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d)", OGRSQLiteEscapeName(osTableName).c_str(), nExtraDS, OGRSQLiteEscape(oLayerDesc.osLayerName).c_str(), bFoundOGRStyle); char* pszErrMsg = NULL; int rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, &pszErrMsg ); if( rc != SQLITE_OK ) { CPLError(CE_Failure, CPLE_AppDefined, "Cannot create virtual table for layer '%s' : %s", osTableName.c_str(), pszErrMsg); sqlite3_free(pszErrMsg); continue; } for(int i=0; i<poLayer->GetLayerDefn()->GetGeomFieldCount(); i++) { OGR2SQLITEDealWithSpatialColumn(poLayer, i, oLayerDesc, osTableName, poSQLiteDS, hDB, bSpatialiteDB, oSetLayers, oSetSpatialIndex); } } /* -------------------------------------------------------------------- */ /* Reload, so that virtual tables are recognized */ /* -------------------------------------------------------------------- */ poSQLiteDS->ReloadLayers(); /* -------------------------------------------------------------------- */ /* Prepare the statement. */ /* -------------------------------------------------------------------- */ /* This will speed-up layer creation */ /* ORDER BY are costly to evaluate and are not necessary to establish */ /* the layer definition. */ int bUseStatementForGetNextFeature = TRUE; int bEmptyLayer = FALSE; sqlite3_stmt *hSQLStmt = NULL; int rc = sqlite3_prepare( hDB, pszStatement, strlen(pszStatement), &hSQLStmt, NULL ); if( rc != SQLITE_OK ) { CPLError( CE_Failure, CPLE_AppDefined, "In ExecuteSQL(): sqlite3_prepare(%s):\n %s", pszStatement, sqlite3_errmsg(hDB) ); if( hSQLStmt != NULL ) { sqlite3_finalize( hSQLStmt ); } delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } /* -------------------------------------------------------------------- */ /* Do we get a resultset? */ /* -------------------------------------------------------------------- */ rc = sqlite3_step( hSQLStmt ); if( rc != SQLITE_ROW ) { if ( rc != SQLITE_DONE ) { CPLError( CE_Failure, CPLE_AppDefined, "In ExecuteSQL(): sqlite3_step(%s):\n %s", pszStatement, sqlite3_errmsg(hDB) ); sqlite3_finalize( hSQLStmt ); delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } if( !EQUALN(pszStatement, "SELECT ", 7) ) { sqlite3_finalize( hSQLStmt ); delete poSQLiteDS; VSIUnlink(pszTmpDBName); CPLFree(pszTmpDBName); return NULL; } bUseStatementForGetNextFeature = FALSE; bEmptyLayer = TRUE; } /* -------------------------------------------------------------------- */ /* Create layer. */ /* -------------------------------------------------------------------- */ OGRSQLiteSelectLayer *poLayer = NULL; poLayer = new OGRSQLiteExecuteSQLLayer( pszTmpDBName, poSQLiteDS, pszStatement, hSQLStmt, bUseStatementForGetNextFeature, bEmptyLayer ); if( poSpatialFilter != NULL ) poLayer->SetSpatialFilter( 0, poSpatialFilter ); return poLayer; } /************************************************************************/ /* OGRSQLiteGetReferencedLayers() */ /************************************************************************/ std::set<LayerDesc> OGRSQLiteGetReferencedLayers(const char* pszStatement) { /* -------------------------------------------------------------------- */ /* Analysze the statement to determine which tables will be used. */ /* -------------------------------------------------------------------- */ std::set<LayerDesc> oSetLayers; std::set<CPLString> oSetSpatialIndex; CPLString osModifiedSQL; OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers, oSetSpatialIndex, osModifiedSQL); return oSetLayers; }
static int OGR2SQLITEDealWithSpatialColumn(OGRLayer* poLayer, int iGeomCol, const LayerDesc& oLayerDesc, const CPLString& osTableName, OGRSQLiteDataSource* poSQLiteDS, sqlite3* hDB, int bSpatialiteDB, const std::set<LayerDesc>& oSetLayers, const std::set<CPLString>& oSetSpatialIndex ) { int rc; OGRGeomFieldDefn* poGeomField = poLayer->GetLayerDefn()->GetGeomFieldDefn(iGeomCol); CPLString osGeomColRaw; if( iGeomCol == 0 ) osGeomColRaw = OGR2SQLITE_GetNameForGeometryColumn(poLayer); else osGeomColRaw = poGeomField->GetNameRef(); const char* pszGeomColRaw = osGeomColRaw.c_str(); CPLString osGeomColEscaped(OGRSQLiteEscape(pszGeomColRaw)); const char* pszGeomColEscaped = osGeomColEscaped.c_str(); CPLString osLayerNameEscaped(OGRSQLiteEscape(osTableName)); const char* pszLayerNameEscaped = osLayerNameEscaped.c_str(); CPLString osIdxNameRaw(CPLSPrintf("idx_%s_%s", oLayerDesc.osLayerName.c_str(), pszGeomColRaw)); CPLString osIdxNameEscaped(OGRSQLiteEscapeName(osIdxNameRaw)); /* Make sure that the SRS is injected in spatial_ref_sys */ OGRSpatialReference* poSRS = poGeomField->GetSpatialRef(); if( iGeomCol == 0 && poSRS == NULL ) poSRS = poLayer->GetSpatialRef(); int nSRSId = poSQLiteDS->GetUndefinedSRID(); if( poSRS != NULL ) nSRSId = poSQLiteDS->FetchSRSId(poSRS); CPLString osSQL; int bCreateSpatialIndex = FALSE; if( !bSpatialiteDB ) { osSQL.Printf("INSERT INTO geometry_columns (f_table_name, " "f_geometry_column, geometry_format, geometry_type, " "coord_dimension, srid) " "VALUES ('%s','%s','SpatiaLite',%d,%d,%d)", pszLayerNameEscaped, pszGeomColEscaped, (int) wkbFlatten(poLayer->GetGeomType()), wkbHasZ( poLayer->GetGeomType() ) ? 3 : 2, nSRSId); } #ifdef HAVE_SPATIALITE else { /* We detect the need for creating a spatial index by 2 means : */ /* 1) if there's an explicit reference to a 'idx_layername_geometrycolumn' */ /* table in the SQL --> old/traditionnal way of requesting spatial indices */ /* with spatialite. */ std::set<LayerDesc>::const_iterator oIter2 = oSetLayers.begin(); for(; oIter2 != oSetLayers.end(); ++oIter2) { const LayerDesc& oLayerDescIter = *oIter2; if( EQUAL(oLayerDescIter.osLayerName, osIdxNameRaw) ) { bCreateSpatialIndex = TRUE; break; } } /* 2) or if there's a SELECT FROM SpatialIndex WHERE f_table_name = 'layername' */ if( !bCreateSpatialIndex ) { std::set<CPLString>::const_iterator oIter3 = oSetSpatialIndex.begin(); for(; oIter3 != oSetSpatialIndex.end(); ++oIter3) { const CPLString& osNameIter = *oIter3; if( EQUAL(osNameIter, oLayerDesc.osLayerName) ) { bCreateSpatialIndex = TRUE; break; } } } if( poSQLiteDS->HasSpatialite4Layout() ) { int nGeomType = poLayer->GetGeomType(); int nCoordDimension = 2; if( wkbHasZ((OGRwkbGeometryType)nGeomType) ) { nGeomType += 1000; nCoordDimension = 3; } osSQL.Printf("INSERT INTO geometry_columns (f_table_name, " "f_geometry_column, geometry_type, coord_dimension, " "srid, spatial_index_enabled) " "VALUES ('%s',Lower('%s'),%d ,%d ,%d, %d)", pszLayerNameEscaped, pszGeomColEscaped, nGeomType, nCoordDimension, nSRSId, bCreateSpatialIndex ); } else { const char *pszGeometryType = OGRToOGCGeomType(poLayer->GetGeomType()); if (pszGeometryType[0] == '\0') pszGeometryType = "GEOMETRY"; osSQL.Printf("INSERT INTO geometry_columns (f_table_name, " "f_geometry_column, type, coord_dimension, " "srid, spatial_index_enabled) " "VALUES ('%s','%s','%s','%s',%d, %d)", pszLayerNameEscaped, pszGeomColEscaped, pszGeometryType, wkbHasZ( poLayer->GetGeomType() ) ? "XYZ" : "XY", nSRSId, bCreateSpatialIndex ); } } #endif // HAVE_SPATIALITE rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL ); #ifdef HAVE_SPATIALITE /* -------------------------------------------------------------------- */ /* Should we create a spatial index ?. */ /* -------------------------------------------------------------------- */ if( !bSpatialiteDB || !bCreateSpatialIndex ) return rc == SQLITE_OK; CPLDebug("SQLITE", "Create spatial index %s", osIdxNameRaw.c_str()); /* ENABLE_VIRTUAL_OGR_SPATIAL_INDEX is not defined */ #ifdef ENABLE_VIRTUAL_OGR_SPATIAL_INDEX osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING " "VirtualOGRSpatialIndex(%d, '%s', pkid, xmin, xmax, ymin, ymax)", osIdxNameEscaped.c_str(), nExtraDS, OGRSQLiteEscape(oLayerDesc.osLayerName).c_str()); rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL ); if( rc != SQLITE_OK ) { CPLDebug("SQLITE", "Error occured during spatial index creation : %s", sqlite3_errmsg(hDB)); } #else // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, NULL ); osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" " "USING rtree(pkid, xmin, xmax, ymin, ymax)", osIdxNameEscaped.c_str()); if( rc == SQLITE_OK ) rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL ); sqlite3_stmt *hStmt = NULL; if( rc == SQLITE_OK ) { const char* pszInsertInto = CPLSPrintf( "INSERT INTO \"%s\" (pkid, xmin, xmax, ymin, ymax) " "VALUES (?,?,?,?,?)", osIdxNameEscaped.c_str()); rc = sqlite3_prepare(hDB, pszInsertInto, -1, &hStmt, NULL); } OGRFeature* poFeature; OGREnvelope sEnvelope; OGR2SQLITE_IgnoreAllFieldsExceptGeometry(poLayer); poLayer->ResetReading(); while( rc == SQLITE_OK && (poFeature = poLayer->GetNextFeature()) != NULL ) { OGRGeometry* poGeom = poFeature->GetGeometryRef(); if( poGeom != NULL && !poGeom->IsEmpty() ) { poGeom->getEnvelope(&sEnvelope); sqlite3_bind_int64(hStmt, 1, (sqlite3_int64) poFeature->GetFID() ); sqlite3_bind_double(hStmt, 2, sEnvelope.MinX); sqlite3_bind_double(hStmt, 3, sEnvelope.MaxX); sqlite3_bind_double(hStmt, 4, sEnvelope.MinY); sqlite3_bind_double(hStmt, 5, sEnvelope.MaxY); rc = sqlite3_step(hStmt); if( rc == SQLITE_OK || rc == SQLITE_DONE ) rc = sqlite3_reset(hStmt); } delete poFeature; } poLayer->SetIgnoredFields(NULL); sqlite3_finalize(hStmt); if( rc == SQLITE_OK ) rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, NULL ); else { CPLDebug("SQLITE", "Error occured during spatial index creation : %s", sqlite3_errmsg(hDB)); rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, NULL ); } #endif // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX #endif // HAVE_SPATIALITE return rc == SQLITE_OK; }
std::string Query::GetError() { if (odb) return sqlite3_errmsg(odb -> db); return ""; }
/* ** Execute SQL code. Return one of the SQLITE_ success/failure ** codes. Also write an error message into memory obtained from ** malloc() and make *pzErrMsg point to that message. ** ** If the SQL is a query, then for each row in the query result ** the xCallback() function is called. pArg becomes the first ** argument to xCallback(). If xCallback=NULL then no callback ** is invoked, even for queries. */ int sqlite3_exec( sqlite3 *db, /* The database on which the SQL executes */ const char *zSql, /* The SQL to be executed */ sqlite3_callback xCallback, /* Invoke this callback routine */ void *pArg, /* First argument to xCallback() */ char **pzErrMsg /* Write error messages here */ ){ int rc = SQLITE_OK; /* Return code */ const char *zLeftover; /* Tail of unprocessed SQL */ sqlite3_stmt *pStmt = 0; /* The current SQL statement */ char **azCols = 0; /* Names of result columns */ int nRetry = 0; /* Number of retry attempts */ int callbackIsInit; /* True if callback data is initialized */ if( zSql==0 ) zSql = ""; sqlite3_mutex_enter(db->mutex); sqlite3Error(db, SQLITE_OK, 0); while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){ int nCol; char **azVals = 0; pStmt = 0; rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover); assert( rc==SQLITE_OK || pStmt==0 ); if( rc!=SQLITE_OK ){ continue; } if( !pStmt ){ /* this happens for a comment or white-space */ zSql = zLeftover; continue; } callbackIsInit = 0; nCol = sqlite3_column_count(pStmt); while( 1 ){ int i; rc = sqlite3_step(pStmt); /* Invoke the callback function if required */ if( xCallback && (SQLITE_ROW==rc || (SQLITE_DONE==rc && !callbackIsInit && db->flags&SQLITE_NullCallback)) ){ if( !callbackIsInit ){ azCols = sqlite3DbMallocZero(db, 2*nCol*sizeof(const char*) + 1); if( azCols==0 ){ goto exec_out; } for(i=0; i<nCol; i++){ azCols[i] = (char *)sqlite3_column_name(pStmt, i); /* sqlite3VdbeSetColName() installs column names as UTF8 ** strings so there is no way for sqlite3_column_name() to fail. */ assert( azCols[i]!=0 ); } callbackIsInit = 1; } if( rc==SQLITE_ROW ){ azVals = &azCols[nCol]; for(i=0; i<nCol; i++){ azVals[i] = (char *)sqlite3_column_text(pStmt, i); if( !azVals[i] && sqlite3_column_type(pStmt, i)!=SQLITE_NULL ){ db->mallocFailed = 1; goto exec_out; } } } if( xCallback(pArg, nCol, azVals, azCols) ){ rc = SQLITE_ABORT; sqlite3VdbeFinalize((Vdbe *)pStmt); pStmt = 0; sqlite3Error(db, SQLITE_ABORT, 0); goto exec_out; } } if( rc!=SQLITE_ROW ){ rc = sqlite3VdbeFinalize((Vdbe *)pStmt); pStmt = 0; if( rc!=SQLITE_SCHEMA ){ nRetry = 0; zSql = zLeftover; while( sqlite3Isspace(zSql[0]) ) zSql++; } break; } } sqlite3DbFree(db, azCols); azCols = 0; } exec_out: if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt); sqlite3DbFree(db, azCols); rc = sqlite3ApiExit(db, rc); if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){ int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db)); *pzErrMsg = sqlite3Malloc(nErrMsg); if( *pzErrMsg ){ memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg); }else{ rc = SQLITE_NOMEM; sqlite3Error(db, SQLITE_NOMEM, 0); } }else if( pzErrMsg ){ *pzErrMsg = 0; } assert( (rc&db->errMask)==rc ); sqlite3_mutex_leave(db->mutex); return rc; }
/* * Find recently changed files with a specific frequency from the DB * Input: * db_conn : db connection object * query_callback : query callback fuction to handle * result records from the query * from_time : Time to define what is recent * 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_recently_changed_files_freq (void *db_conn, gf_query_callback_t query_callback, void *query_cbk_args, gfdb_time_t *from_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; uint64_t from_time_usec = 0; char *base_query_str = NULL; CHECK_SQL_CONN (sql_conn, out); GF_VALIDATE_OR_GOTO(GFDB_STR_SQLITE3, query_callback, out); ret = gf_get_basic_query_stmt (&base_query_str); if (ret <= 0) { goto out; } ret = gf_asprintf (&query_str, "%s AND " /*First condition: For Writes*/ "( ( ((" GF_COL_TB_WSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_WMSEC ") >= ? )" " AND "" (" GF_COL_TB_WFC " >= ? ) )" " OR " /*Second condition: For Reads */ "( ((" GF_COL_TB_RWSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_RWMSEC ") >= ?)" " AND "" (" GF_COL_TB_RFC " >= ? ) ) )", base_query_str); if (ret < 0) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_QUERY_FAILED, "Failed to create query statement"); query_str = NULL; goto out; } from_time_usec = gfdb_time_2_usec (from_time); ret = sqlite3_prepare (sql_conn->sqlite3_db_conn, query_str, -1, &prep_stmt, 0); if (ret != SQLITE_OK) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_PREPARE_FAILED, "Failed to prepare 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, from_time_usec); if (ret != SQLITE_OK) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind from_time_usec " "%"PRIu64" : %s", from_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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind freq_write_cnt " "%d : %s", freq_write_cnt, sqlite3_errmsg (sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read wind time*/ ret = sqlite3_bind_int64 (prep_stmt, 3, from_time_usec); if (ret != SQLITE_OK) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind from_time_usec " "%"PRIu64" : %s", from_time_usec, sqlite3_errmsg (sql_conn->sqlite3_db_conn)); ret = -1; goto out; } /*Bind read frequency thresold*/ ret = sqlite3_bind_int (prep_stmt, 4, freq_read_cnt); if (ret != SQLITE_OK) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind freq_read_cnt " "%d : %s", freq_read_cnt, 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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_QUERY_FAILED, "Failed Query %s", query_str); goto out; } /*Clear counters*/ if (clear_counters) { ret = gf_sql_clear_counters (sql_conn); if (ret) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_CLEAR_COUNTER_FAILED, "Failed to clear" " counters!"); goto out; } } ret = 0; out: sqlite3_finalize (prep_stmt); GF_FREE (base_query_str); GF_FREE (query_str); return ret; }
int database::qry_trades(const std::string &investorId, const std::string &seqnum, long maxrec,int date, TradeGateway::ExecutionReportSeq &list) { int retnum =0; if( !this->is_connected() ) this->connect(); std::ostringstream sql; std::string seq = seqnum; if( seq.length() == 0 ) { seq = "0"; } sql << "SELECT *,rowid FROM dt_trades WHERE (accountId='" << investorId << "') AND (tradeDate=" << date << ") AND (rowid>" << seq << ") order by rowid LIMIT "<<maxrec; sqlite3_stmt *pstmt = NULL; const char * pzTail = NULL; int rc = sqlite3_prepare_v2(pdb,sql.str().c_str(),sql.str().length(),&pstmt,&pzTail); if (rc !=SQLITE_OK) { std::ostringstream err ; err << "Can't prepare query: " << sqlite3_errmsg(pdb); throw err.str(); } else { while( 1 ) { rc = sqlite3_step(pstmt); if (rc == SQLITE_ERROR) { std::ostringstream err ; err << "Can't step record: " << sqlite3_errmsg(pdb); sqlite3_finalize( pstmt ); throw err.str(); } else if( rc ==SQLITE_MISUSE || rc == SQLITE_DONE ) break; else if( rc == SQLITE_BUSY ) { continue; } else if( rc == SQLITE_ROW ) { TradeGateway::ExecutionReport er; er.accountId = (const char*)sqlite3_column_text(pstmt,0); er.ordId = (const char*)sqlite3_column_text(pstmt,1); er.execId = (const char*)sqlite3_column_text(pstmt,2); er.type = (TradeGateway::ExecType)sqlite3_column_int(pstmt,3); er.ordRejReason = siconv((const char*)sqlite3_column_text(pstmt,4),"UTF-8","GB2312"); er.lastQty = sqlite3_column_int(pstmt,5); er.lastPx=sqlite3_column_double(pstmt,6); er.tradeDate =sqlite3_column_int(pstmt,7); er.transactTime =sqlite3_column_int(pstmt,8); er.seq = (const char*)sqlite3_column_text(pstmt,9); list.push_back(er); retnum++; } } sqlite3_finalize( pstmt ); } return retnum; }
bool exec(const std::string& sql, const bindings_type& bindings, rows_type* rows) { boost::mutex::scoped_lock lock(guard_); auto it = stmt_ptr_map_.find(sql); sqlite3_stmt* stmt; if(it == stmt_ptr_map_.end()) { sqlite3_stmt* new_stmt; int ret = sqlite3_prepare_v2(db_ptr_.get(), sql.c_str(), sql.size(), &new_stmt, NULL); if(new_stmt == NULL ) { std::cerr << "Failed to execute statement: " << sql << std::endl; return false; } if(ret != SQLITE_OK) { std::cerr << "Failed to execute statement: " << sql << ", " << ret << std::endl; sqlite3_finalize(new_stmt); return false; } stmt_ptr_map_[sql] = boost::shared_ptr<sqlite3_stmt>(new_stmt, statement_finalise()); stmt = new_stmt; } else { stmt = it->second.get(); // Reset the prepared statement, which is more efficient than re-creating // as preparing a statement is expensive. sqlite3_reset(stmt); sqlite3_clear_bindings(stmt); } #ifdef BOOST_NO_CXX11_RANGE_BASED_FOR BOOST_FOREACH(auto bit, bindings) { #else for(auto bit : bindings) { #endif int ndx = 0; if(bit.first.type() == json_spirit::int_type) { ndx = bit.first.get_int(); } else if(bit.first.type() == json_spirit::str_type) { ndx = sqlite3_bind_parameter_index(stmt, bit.first.get_str().c_str()); } else { ASSERT_LOG(false, "parameter for binding index must be int or string: " << bit.first.type()); } ASSERT_LOG(ndx != 0, "Bad index value: 0"); switch(bit.second.type()) { case json_spirit::obj_type: { json_spirit::mObject& obj = bit.second.get_obj(); std::string s = json_spirit::write(obj); sqlite3_bind_blob(stmt, ndx, static_cast<const void*>(s.c_str()), s.size(), SQLITE_TRANSIENT); break; } case json_spirit::array_type: { json_spirit::mArray& ary = bit.second.get_array(); std::string s = json_spirit::write(ary); sqlite3_bind_blob(stmt, ndx, static_cast<const void*>(s.c_str()), s.size(), SQLITE_TRANSIENT); break; } case json_spirit::str_type: { std::string s = bit.second.get_str(); sqlite3_bind_text(stmt, ndx, s.c_str(), s.size(), SQLITE_TRANSIENT); break; } case json_spirit::bool_type: { sqlite3_bind_int(stmt, ndx, bit.second.get_bool()); break; } case json_spirit::int_type: { sqlite3_bind_int(stmt, ndx, bit.second.get_int()); break; } case json_spirit::real_type: { sqlite3_bind_double(stmt, ndx, bit.second.get_real()); break; } case json_spirit::null_type: { sqlite3_bind_null(stmt, ndx); break; } } } std::vector<json_spirit::mValue> ret_rows; // Step through the statement bool stepping = true; while(stepping) { int ret = sqlite3_step(stmt); if(ret == SQLITE_ROW) { int col_count = sqlite3_column_count(stmt); for(int n = 0; n != col_count; ++n) { int type = sqlite3_column_type(stmt, n); switch(type) { case SQLITE_INTEGER: { int val = sqlite3_column_int(stmt, n); ret_rows.push_back(json_spirit::mValue(val)); break; } case SQLITE_FLOAT: { double val = sqlite3_column_double(stmt, n); ret_rows.push_back(json_spirit::mValue(val)); break; } case SQLITE_BLOB: { const char* blob = reinterpret_cast<const char *>(sqlite3_column_blob(stmt, n)); int len = sqlite3_column_bytes(stmt, n); json_spirit::mValue value; json_spirit::read(std::string(blob, blob + len), value); ret_rows.push_back(value); break; } case SQLITE_NULL: { ret_rows.push_back(json_spirit::mValue()); break; } case SQLITE_TEXT: { const uint8_t* us = sqlite3_column_text(stmt, n); int len = sqlite3_column_bytes(stmt, n); std::string s(us, us + len); ret_rows.push_back(json_spirit::mValue(s)); break; } } } } else if(ret == SQLITE_DONE) { stepping = false; } else { std::cerr << "Error stepping through statement: " << sql << ", " << ret << " : " << sqlite3_errmsg(db_ptr_.get()) << std::endl; return false; } } ASSERT_LOG(rows != NULL || ret_rows.empty() != false, "There was data to return but no place to put it."); rows->swap(ret_rows); return true; } private:
static gboolean rspamd_sqlite3_wait (rspamd_mempool_t *pool, const gchar *lock) { gint fd; struct timespec sleep_ts = { .tv_sec = 0, .tv_nsec = 1000000 }; fd = open (lock, O_RDONLY); if (fd == -1) { msg_err_pool ("cannot open lock file %s: %s", lock, strerror (errno)); return FALSE; } while (!rspamd_file_lock (fd, TRUE)) { if (nanosleep (&sleep_ts, NULL) == -1 && errno != EINTR) { close (fd); msg_err_pool ("cannot sleep open lock file %s: %s", lock, strerror (errno)); return FALSE; } } rspamd_file_unlock (fd, FALSE); close (fd); return TRUE; } sqlite3 * rspamd_sqlite3_open_or_create (rspamd_mempool_t *pool, const gchar *path, const gchar *create_sql, GError **err) { sqlite3 *sqlite; gint rc, flags, lock_fd; gchar lock_path[PATH_MAX], dbdir[PATH_MAX], *pdir; static const char sqlite_wal[] = "PRAGMA journal_mode=\"wal\";", exclusive_lock_sql[] = "PRAGMA locking_mode=\"exclusive\";", fsync_sql[] = "PRAGMA synchronous=1;", foreign_keys[] = "PRAGMA foreign_keys=\"ON\";", enable_mmap[] = "PRAGMA mmap_size=268435456;"; gboolean create = FALSE, has_lock = FALSE; flags = SQLITE_OPEN_READWRITE; #ifdef SQLITE_OPEN_SHAREDCACHE flags |= SQLITE_OPEN_SHAREDCACHE; #endif #ifdef SQLITE_OPEN_WAL flags |= SQLITE_OPEN_WAL; #endif rspamd_strlcpy (dbdir, path, sizeof (dbdir)); pdir = dirname (dbdir); if (access (pdir, W_OK) == -1) { g_set_error (err, rspamd_sqlite3_quark (), errno, "cannot open sqlite directory %s: %s", pdir, strerror (errno)); return NULL; } rspamd_snprintf (lock_path, sizeof (lock_path), "%s.lock", path); if (access (path, R_OK) == -1 && create_sql != NULL) { flags |= SQLITE_OPEN_CREATE; create = TRUE; } rspamd_snprintf (lock_path, sizeof (lock_path), "%s.lock", path); lock_fd = open (lock_path, O_WRONLY|O_CREAT|O_EXCL, 00600); if (lock_fd == -1 && (errno == EEXIST || errno == EBUSY)) { msg_debug_pool ("checking %s to wait for db being initialized", lock_path); if (!rspamd_sqlite3_wait (pool, lock_path)) { g_set_error (err, rspamd_sqlite3_quark (), errno, "cannot create sqlite file %s: %s", path, strerror (errno)); return NULL; } /* At this point we have database created */ create = FALSE; has_lock = FALSE; } else { msg_debug_pool ("locking %s to block other processes", lock_path); g_assert (rspamd_file_lock (lock_fd, FALSE)); has_lock = TRUE; } if ((rc = sqlite3_open_v2 (path, &sqlite, flags, NULL)) != SQLITE_OK) { #if SQLITE_VERSION_NUMBER >= 3008000 g_set_error (err, rspamd_sqlite3_quark (), rc, "cannot open sqlite db %s: %s", path, sqlite3_errstr (rc)); #else g_set_error (err, rspamd_sqlite3_quark (), rc, "cannot open sqlite db %s: %d", path, rc); #endif return NULL; } if (create) { if (sqlite3_exec (sqlite, sqlite_wal, NULL, NULL, NULL) != SQLITE_OK) { msg_warn_pool ("WAL mode is not supported (%s), locking issues might occur", sqlite3_errmsg (sqlite)); } if (sqlite3_exec (sqlite, exclusive_lock_sql, NULL, NULL, NULL) != SQLITE_OK) { msg_warn_pool ("cannot exclusively lock database to create schema: %s", sqlite3_errmsg (sqlite)); } if (sqlite3_exec (sqlite, create_sql, NULL, NULL, NULL) != SQLITE_OK) { g_set_error (err, rspamd_sqlite3_quark (), -1, "cannot execute create sql `%s`: %s", create_sql, sqlite3_errmsg (sqlite)); sqlite3_close (sqlite); rspamd_file_unlock (lock_fd, FALSE); unlink (lock_path); close (lock_fd); return NULL; } sqlite3_close (sqlite); /* Reopen in normal mode */ msg_debug_pool ("reopening %s in normal mode", path); flags &= ~SQLITE_OPEN_CREATE; if ((rc = sqlite3_open_v2 (path, &sqlite, flags, NULL)) != SQLITE_OK) { #if SQLITE_VERSION_NUMBER >= 3008000 g_set_error (err, rspamd_sqlite3_quark (), rc, "cannot open sqlite db after creation %s: %s", path, sqlite3_errstr (rc)); #else g_set_error (err, rspamd_sqlite3_quark (), rc, "cannot open sqlite db after creation %s: %d", path, rc); #endif rspamd_file_unlock (lock_fd, FALSE); unlink (lock_path); close (lock_fd); return NULL; } } if (sqlite3_exec (sqlite, sqlite_wal, NULL, NULL, NULL) != SQLITE_OK) { msg_warn_pool ("WAL mode is not supported (%s), locking issues might occur", sqlite3_errmsg (sqlite)); } if (sqlite3_exec (sqlite, fsync_sql, NULL, NULL, NULL) != SQLITE_OK) { msg_warn_pool ("cannot set synchronous: %s", sqlite3_errmsg (sqlite)); } if ((rc = sqlite3_exec (sqlite, foreign_keys, NULL, NULL, NULL)) != SQLITE_OK) { msg_warn_pool ("cannot enable foreign keys: %s", sqlite3_errmsg (sqlite)); } if (sizeof (gpointer) >= 8 && (rc = sqlite3_exec (sqlite, enable_mmap, NULL, NULL, NULL)) != SQLITE_OK) { msg_warn_pool ("cannot enable mmap: %s", sqlite3_errmsg (sqlite)); } if (has_lock) { msg_debug_pool ("removing lock from %s", lock_path); rspamd_file_unlock (lock_fd, FALSE); unlink (lock_path); close (lock_fd); } return sqlite; }
/* * Get the cached artwork image for the given persistentid and maximum width/height * * If there is a cached entry for the given id and width/height, the parameter cached is set to 1. * In this case format and data contain the cached values. * * @param cmd->arg.persistentid persistent songalbumid or songartistid * @param cmd->arg.max_w maximum image width * @param cmd->arg.max_h maximum image height * @param cmd->arg.cached set by this function to 0 if no cache entry exists, otherwise 1 * @param cmd->arg.format set by this function to the format of the cache entry * @param cmd->arg.evbuf event buffer filled by this function with the scaled image * @return 0 if successful, -1 if an error occurred */ static int cache_artwork_get_impl(struct cache_command *cmd) { #define Q_TMPL "SELECT a.format, a.data FROM artwork a WHERE a.persistentid = '%" PRIi64 "' AND a.max_w = %d AND a.max_h = %d;" sqlite3_stmt *stmt; char *query; int datalen; int ret; query = sqlite3_mprintf(Q_TMPL, cmd->arg.peristentid, cmd->arg.max_w, cmd->arg.max_h); if (!query) { DPRINTF(E_LOG, L_CACHE, "Out of memory for query string\n"); return -1; } DPRINTF(E_DBG, L_CACHE, "Running query '%s'\n", query); ret = sqlite3_prepare_v2(g_db_hdl, query, -1, &stmt, 0); if (ret != SQLITE_OK) { DPRINTF(E_LOG, L_CACHE, "Could not prepare statement: %s\n", sqlite3_errmsg(g_db_hdl)); ret = -1; goto error_get; } ret = sqlite3_step(stmt); if (ret != SQLITE_ROW) { cmd->arg.cached = 0; if (ret == SQLITE_DONE) { ret = 0; DPRINTF(E_DBG, L_CACHE, "No results\n"); } else { ret = -1; DPRINTF(E_LOG, L_CACHE, "Could not step: %s\n", sqlite3_errmsg(g_db_hdl)); } goto error_get; } cmd->arg.format = sqlite3_column_int(stmt, 0); datalen = sqlite3_column_bytes(stmt, 1); if (!cmd->arg.evbuf) { DPRINTF(E_LOG, L_CACHE, "Error: Artwork evbuffer is NULL\n"); goto error_get; } ret = evbuffer_add(cmd->arg.evbuf, sqlite3_column_blob(stmt, 1), datalen); if (ret < 0) { DPRINTF(E_LOG, L_CACHE, "Out of memory for artwork evbuffer\n"); goto error_get; } cmd->arg.cached = 1; ret = sqlite3_finalize(stmt); if (ret != SQLITE_OK) DPRINTF(E_LOG, L_CACHE, "Error finalizing query for getting cache: %s\n", sqlite3_errmsg(g_db_hdl)); DPRINTF(E_DBG, L_CACHE, "Cache hit: %s\n", query); return 0; error_get: sqlite3_finalize(stmt); return -1; #undef Q_TMPL }
int rspamd_sqlite3_run_prstmt (rspamd_mempool_t *pool, sqlite3 *db, GArray *stmts, gint idx, ...) { gint retcode; va_list ap; sqlite3_stmt *stmt; gint i, rowid, nargs, j; gint64 len; gpointer p; struct rspamd_sqlite3_prstmt *nst; const char *argtypes; if (idx < 0 || idx >= (gint)stmts->len) { return -1; } nst = &g_array_index (stmts, struct rspamd_sqlite3_prstmt, idx); stmt = nst->stmt; g_assert (nst != NULL); msg_debug_pool ("executing `%s`", nst->sql); argtypes = nst->args; sqlite3_reset (stmt); va_start (ap, idx); nargs = 1; for (i = 0, rowid = 1; argtypes[i] != '\0'; i ++) { switch (argtypes[i]) { case 'T': for (j = 0; j < nargs; j ++, rowid ++) { sqlite3_bind_text (stmt, rowid, va_arg (ap, const char*), -1, SQLITE_STATIC); } nargs = 1; break; case 'V': case 'B': for (j = 0; j < nargs; j ++, rowid ++) { len = va_arg (ap, gint64); sqlite3_bind_text (stmt, rowid, va_arg (ap, const char*), len, SQLITE_STATIC); } nargs = 1; break; case 'I': for (j = 0; j < nargs; j ++, rowid ++) { sqlite3_bind_int64 (stmt, rowid, va_arg (ap, gint64)); } nargs = 1; break; case 'S': for (j = 0; j < nargs; j ++, rowid ++) { sqlite3_bind_int (stmt, rowid, va_arg (ap, gint)); } nargs = 1; break; case '*': nargs = va_arg (ap, gint); break; } } va_end (ap); retcode = sqlite3_step (stmt); if (retcode == nst->result) { argtypes = nst->ret; for (i = 0; argtypes != NULL && argtypes[i] != '\0'; i ++) { switch (argtypes[i]) { case 'T': *va_arg (ap, char**) = g_strdup (sqlite3_column_text (stmt, i)); break; case 'I': *va_arg (ap, gint64*) = sqlite3_column_int64 (stmt, i); break; case 'S': *va_arg (ap, int*) = sqlite3_column_int (stmt, i); break; case 'L': *va_arg (ap, gint64*) = sqlite3_last_insert_rowid (db); break; case 'B': len = sqlite3_column_bytes (stmt, i); g_assert (len >= 0); p = g_malloc (len); memcpy (p, sqlite3_column_blob (stmt, i), len); *va_arg (ap, gint64*) = len; *va_arg (ap, gpointer*) = p; break; } } if (!(nst->flags & RSPAMD_SQLITE3_STMT_MULTIPLE)) { sqlite3_reset (stmt); } return SQLITE_OK; } else if (retcode != SQLITE_DONE) { msg_debug_pool ("failed to execute query %s: %d, %s", nst->sql, retcode, sqlite3_errmsg (db)); } if (!(nst->flags & RSPAMD_SQLITE3_STMT_MULTIPLE)) { sqlite3_reset (stmt); } return retcode; }
/* Gets a reply from the cache */ static int cache_daap_query_get(struct cache_command *cmd) { #define Q_TMPL "SELECT reply FROM replies WHERE query = ?;" sqlite3_stmt *stmt; char *query; int datalen; int ret; query = cmd->arg.query; remove_tag(query, "session-id"); remove_tag(query, "revision-number"); // Look in the DB ret = sqlite3_prepare_v2(g_db_hdl, Q_TMPL, -1, &stmt, 0); if (ret != SQLITE_OK) { DPRINTF(E_LOG, L_CACHE, "Error preparing query for cache update: %s\n", sqlite3_errmsg(g_db_hdl)); free(query); return -1; } sqlite3_bind_text(stmt, 1, query, -1, SQLITE_STATIC); ret = sqlite3_step(stmt); if (ret != SQLITE_ROW) { if (ret != SQLITE_DONE) DPRINTF(E_LOG, L_CACHE, "Error stepping query for cache update: %s\n", sqlite3_errmsg(g_db_hdl)); goto error_get; } datalen = sqlite3_column_bytes(stmt, 0); if (!cmd->arg.evbuf) { DPRINTF(E_LOG, L_CACHE, "Error: DAAP reply evbuffer is NULL\n"); goto error_get; } ret = evbuffer_add(cmd->arg.evbuf, sqlite3_column_blob(stmt, 0), datalen); if (ret < 0) { DPRINTF(E_LOG, L_CACHE, "Out of memory for DAAP reply evbuffer\n"); goto error_get; } ret = sqlite3_finalize(stmt); if (ret != SQLITE_OK) DPRINTF(E_LOG, L_CACHE, "Error finalizing query for getting cache: %s\n", sqlite3_errmsg(g_db_hdl)); DPRINTF(E_INFO, L_CACHE, "Cache hit: %s\n", query); free(query); return 0; error_get: sqlite3_finalize(stmt); free(query); return -1; #undef Q_TMPL }
int main(void) { int lfd; int cfd; int sfd; int rdy; struct sockaddr_in sin; struct sockaddr_in cin; int client[FD_SETSIZE]; /* 客户端连接的套接字描述符数组 */ int maxi; int maxfd; /* 最大连接数 */ fd_set rset; fd_set allset; socklen_t addr_len; /* 地址结构长度 */ int i; int n; int len; int opt = 1; /* 套接字选项 */ char addr_p[20]; sqlite3 *db = NULL; char *err_msg = NULL; msg_t msg; time_t ptime; char pestime[100] = {0}; /* 对server_addr_in 结构进行赋值 */ bzero(&sin,sizeof(struct sockaddr_in)); /* 先清零 */ sin.sin_family=AF_INET; sin.sin_addr.s_addr=htonl(INADDR_ANY); //表示接受任何ip地址 将ip地址转换成网络字节序 sin.sin_port=htons(PORT); //将端口号转换成网络字节序 /* 调用socket函数创建一个TCP协议套接口 */ if((lfd=socket(AF_INET,SOCK_STREAM,0))==-1) // AF_INET:IPV4;SOCK_STREAM:TCP { fprintf(stderr,"Socket error:%s\n\a",strerror(errno)); exit(1); } /*设置套接字选项 使用默认选项*/ setsockopt(lfd, SOL_SOCKET, SO_REUSEADDR, &opt, sizeof(opt)); /* 调用bind函数 将serer_addr结构绑定到sockfd上 */ if(bind(lfd,(struct sockaddr *)(&sin),sizeof(struct sockaddr))==-1) { fprintf(stderr,"Bind error:%s\n\a",strerror(errno)); exit(1); } /* 开始监听端口 等待客户的请求 */ if(listen(lfd,20)==-1) { fprintf(stderr,"Listen error:%s\n\a",strerror(errno)); exit(1); } printf("Accepting connections .......\n"); maxfd = lfd; /*对最大文件描述符进行初始化*/ maxi = -1; /*初始化客户端连接描述符集合*/ for(i = 0;i < FD_SETSIZE;i++) { client[i] = -1; } FD_ZERO(&allset); /* 清空文件描述符集合 */ FD_SET(lfd,&allset); /* 将监听字设置在集合内 */ int rc = sqlite3_open("chat_room.db",&db); if(rc != SQLITE_OK) { fprintf(stderr,"open database failed %s\n",sqlite3_errmsg(db)); } char sql_create_user_info[256] = {0}; //保存用户信息 sprintf(sql_create_user_info,"create table user_info(id INTEGER,name TEXT,password TEXT,primary key(id));"); sqlite3_exec(db,sql_create_user_info,NULL,0,&err_msg); char sql_create_log_info[256] = {0}; //保存已登录用户 sprintf(sql_create_log_info,"create table log_info(id INTEGER,name TEXT,connectfd INTEGER,primary key(id));"); sqlite3_exec(db,sql_create_log_info,NULL,0,&err_msg); char sql_create_record[256] = {0}; //保存服务器运行记录 sprintf(sql_create_record,"create table record(id INTEGER,name TEXT,size TEXT,target TEXT,msg TEXT,time TEXT,primary key(id));"); sqlite3_exec(db,sql_create_record,NULL,0,&err_msg); /* 开始服务程序的死循环 */ while(1) { rset = allset; /*得到当前可以读的文件描述符数*/ rdy = select(maxfd + 1, &rset, NULL, NULL, NULL); if(FD_ISSET(lfd, &rset)) { addr_len = sizeof(sin); /* 接受客户端的请求 */ if((cfd=accept(lfd,(struct sockaddr *)(&cin),&addr_len))==-1) { fprintf(stderr,"Accept error:%s\n\a",strerror(errno)); exit(1); } /*查找一个空闲位置*/ for(i = 0; i<FD_SETSIZE; i++) { //printf("%d\t",client[i]); if(client[i] <= 0) { client[i] = cfd; /* 将处理该客户端的连接套接字设置到该位置 */ break; } } /* 太多的客户端连接 服务器拒绝俄请求 跳出循环 */ if(i == FD_SETSIZE) { printf("too many clients"); exit(1); } FD_SET(cfd, &allset); /* 设置连接集合 */ if(cfd > maxfd) /* 新的连接描述符 */ { maxfd = cfd; } if(i > maxi) { maxi = i; } if(--rdy <= 0) /* 减少一个连接描述符 */ { continue; } } /* 对每一个连接描述符做处理 */ for(i = 0;i< FD_SETSIZE;i++) { if((sfd = client[i]) < 0) { continue; } if(FD_ISSET(sfd, &rset)) { n = read(sfd,&msg,sizeof(msg_t)); if(n == 0) //客户端异常退出 { printf("the other side has been closed. \n"); char sql_quit[256]; //删除登录状态 sprintf(sql_quit, "delete from log_info where connectfd = %d;", sfd); rc = sqlite3_exec(db,sql_quit,NULL,0,&err_msg); if(rc != SQLITE_OK) { fprintf(stderr,"%s",err_msg); } time(&ptime); //保存记录 strcpy(pestime,ctime(&ptime)); rc = sqlite3_exec(db, sql_quit, NULL, 0, &err_msg); sprintf(sql_quit, "insert into record (id,name,size,time) values(%d,'%s','%s','%s');",msg.id,msg.name,"下线",pestime); rc = sqlite3_exec(db, sql_quit, NULL, 0, &err_msg); memset(&msg,0,sizeof(msg_t)); fflush(stdout); /* 刷新 输出终端 */ close(sfd); FD_CLR(sfd, &allset); /*清空连接描述符数组*/ client[i] = -1; } else { msg_handle(&msg,sfd); /* 谐函数出错 */ if(n == 1) { exit(1); } } /*如果没有可以读的套接字 退出循环*/ if(--rdy <= 0) { break; } } } } close(lfd); /* 关闭链接套接字 */ return 0; }
int sql_init ( dbref player, dbref cause, char *buff, char **bufc ) { sqlite3 *sqlite; int retval; /* * Make sure we have valid config options. No need to check sql_host, * only the db. */ if ( !mod_db_sql_config.db || !*mod_db_sql_config.db ) return -1; /* * If we are already connected, drop and retry the connection, in * case for some reason the server went away. */ if ( sqlite3_struct ) sql_shutdown ( 0, 0, NULL, NULL ); retval = sqlite3_open ( mod_db_sql_config.db, &sqlite ); if ( retval != SQLITE_OK ) { log_write ( LOG_ALWAYS, "SQL", "CONN", "Failed to open %s: %s", mod_db_sql_config.db, sqlite3_errmsg ( sqlite ) ); return -1; } log_write ( LOG_ALWAYS, "SQL", "CONN", "Opened SQLite3 file %s", mod_db_sql_config.db ); sqlite3_struct = sqlite; mod_db_sql_config.socket = -1; return 1; }
const char* SQLiteDatabase::lastErrorMsg() { if (m_db) return sqlite3_errmsg(m_db); return m_openErrorMessage.isNull() ? notOpenErrorMessage : m_openErrorMessage.data(); }
static const char *dict_sqlite_lookup(DICT *dict, const char *name) { const char *myname = "dict_sqlite_lookup"; DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict; sqlite3_stmt *sql_stmt; const char *query_remainder; static VSTRING *query; static VSTRING *result; const char *retval; int expansion = 0; int status; int domain_rc; /* * In case of return without lookup (skipped key, etc.). */ dict->error = 0; /* * Don't frustrate future attempts to make Postfix UTF-8 transparent. */ if (!valid_utf_8(name, strlen(name))) { if (msg_verbose) msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'", myname, dict_sqlite->parser->name, name); return (0); } /* * Optionally fold the key. Folding may be enabled on on-the-fly. */ if (dict->flags & DICT_FLAG_FOLD_FIX) { if (dict->fold_buf == 0) dict->fold_buf = vstring_alloc(100); vstring_strcpy(dict->fold_buf, name); name = lowercase(vstring_str(dict->fold_buf)); } /* * Apply the optional domain filter for email address lookups. */ if ((domain_rc = db_common_check_domain(dict_sqlite->ctx, name)) == 0) { if (msg_verbose) msg_info("%s: %s: Skipping lookup of '%s'", myname, dict_sqlite->parser->name, name); return (0); } if (domain_rc < 0) DICT_ERR_VAL_RETURN(dict, domain_rc, (char *) 0); /* * Expand the query and query the database. */ #define INIT_VSTR(buf, len) do { \ if (buf == 0) \ buf = vstring_alloc(len); \ VSTRING_RESET(buf); \ VSTRING_TERMINATE(buf); \ } while (0) INIT_VSTR(query, 10); if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query, name, 0, query, dict_sqlite_quote)) return (0); if (msg_verbose) msg_info("%s: %s: Searching with query %s", myname, dict_sqlite->parser->name, vstring_str(query)); if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1, &sql_stmt, &query_remainder) != SQLITE_OK) msg_fatal("%s: %s: SQL prepare failed: %s\n", myname, dict_sqlite->parser->name, sqlite3_errmsg(dict_sqlite->db)); if (*query_remainder && msg_verbose) msg_info("%s: %s: Ignoring text at end of query: %s", myname, dict_sqlite->parser->name, query_remainder); /* * Retrieve and expand the result(s). */ INIT_VSTR(result, 10); while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) { if (status == SQLITE_ROW) { if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format, (char *) sqlite3_column_text(sql_stmt, 0), name, result, 0) && dict_sqlite->expansion_limit > 0 && ++expansion > dict_sqlite->expansion_limit) { msg_warn("%s: %s: Expansion limit exceeded for key '%s'", myname, dict_sqlite->parser->name, name); dict->error = DICT_ERR_RETRY; break; } } /* Fix 20100616 */ else { msg_warn("%s: %s: SQL step failed for query '%s': %s\n", myname, dict_sqlite->parser->name, vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); dict->error = DICT_ERR_RETRY; break; } } /* * Clean up. */ if (sqlite3_finalize(sql_stmt)) msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n", myname, dict_sqlite->parser->name, vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); return ((dict->error == 0 && *(retval = vstring_str(result)) != 0) ? retval : 0); }
bool SQLite3Backend::getBuddies(long id, std::list<BuddyInfo> &roster) { // SELECT id, uin, subscription, nickname, groups, flags FROM " + m_prefix + "buddies WHERE user_id=? ORDER BY id ASC BEGIN(m_getBuddies); BIND_INT(m_getBuddies, id); // "SELECT buddy_id, type, var, value FROM " + m_prefix + "buddies_settings WHERE user_id=? ORDER BY buddy_id ASC" BEGIN(m_getBuddiesSettings); BIND_INT(m_getBuddiesSettings, id); SettingVariableInfo var; long buddy_id = -1; std::string key; int ret; int ret2 = -10; while((ret = sqlite3_step(m_getBuddies)) == SQLITE_ROW) { BuddyInfo b; RESET_GET_COUNTER(m_getBuddies); b.id = GET_INT(m_getBuddies); b.legacyName = GET_STR(m_getBuddies); b.subscription = GET_STR(m_getBuddies); b.alias = GET_STR(m_getBuddies); std::string groups = GET_STR(m_getBuddies); b.groups = StorageBackend::deserializeGroups(groups); b.flags = GET_INT(m_getBuddies); if (buddy_id == b.id) { std::cout << "Adding buddy info " << key << "\n"; b.settings[key] = var; buddy_id = -1; } while(buddy_id == -1 && ret2 != SQLITE_DONE && ret2 != SQLITE_ERROR && (ret2 = sqlite3_step(m_getBuddiesSettings)) == SQLITE_ROW) { RESET_GET_COUNTER(m_getBuddiesSettings); buddy_id = GET_INT(m_getBuddiesSettings); var.type = GET_INT(m_getBuddiesSettings); key = GET_STR(m_getBuddiesSettings); std::string val = GET_STR(m_getBuddiesSettings); switch (var.type) { case TYPE_BOOLEAN: var.b = atoi(val.c_str()); break; case TYPE_STRING: var.s = val; break; default: if (buddy_id == b.id) { buddy_id = -1; } continue; break; } if (buddy_id == b.id) { std::cout << "Adding buddy info " << key << "=" << val << "\n"; b.settings[key] = var; buddy_id = -1; } } // if (ret != SQLITE_DONE) { // LOG4CXX_ERROR(logger, "getBuddiesSettings query"<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db))); // return false; // } roster.push_back(b); } if (ret != SQLITE_DONE) { LOG4CXX_ERROR(logger, "getBuddies query "<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db))); return false; } if (ret2 != SQLITE_DONE) { if (ret2 == SQLITE_ERROR) { LOG4CXX_ERROR(logger, "getBuddiesSettings query "<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db))); return false; } while((ret2 = sqlite3_step(m_getBuddiesSettings)) == SQLITE_ROW) { } if (ret2 != SQLITE_DONE) { LOG4CXX_ERROR(logger, "getBuddiesSettings query "<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db))); return false; } } return true; }
static int add_geometry_column(sqlite3 *db, const char *db_name, const char *table_name, const char *column_name, const char *geom_type, int srs_id, int z, int m, errorstream_t *error) { int result; const char *normalized_geom_type; result = geom_normalized_type_name(geom_type, &normalized_geom_type); if (result != SQLITE_OK) { error_append(error, "Invalid geometry type: %s", geom_type); return result; } if (z < 0 || z > 2) { error_append(error, "Invalid Z flag value: %d", z); return result; } if (m < 0 || m > 2) { error_append(error, "Invalid M flag value: %d", z); return result; } // Check if the target table exists int exists = 0; result = sql_check_table_exists(db, db_name, table_name, &exists); if (result != SQLITE_OK) { error_append(error, "Could not check if table %s.%s exists", db_name, table_name); return result; } if (!exists) { error_append(error, "Table %s.%s does not exist", db_name, table_name); return SQLITE_OK; } if (error_count(error) > 0) { return SQLITE_OK; } // Check if the SRID is defined int count = 0; result = sql_exec_for_int(db, &count, "SELECT count(*) FROM gpkg_spatial_ref_sys WHERE srs_id = %d", srs_id); if (result != SQLITE_OK) { return result; } if (count == 0) { error_append(error, "SRS %d does not exist", srs_id); return SQLITE_OK; } result = sql_exec(db, "ALTER TABLE \"%w\".\"%w\" ADD COLUMN \"%w\" %s", db_name, table_name, column_name, normalized_geom_type); if (result != SQLITE_OK) { error_append(error, sqlite3_errmsg(db)); return result; } result = sql_exec(db, "INSERT INTO \"%w\".\"%w\" (table_name, column_name, geometry_type_name, srs_id, z, m) VALUES (%Q, %Q, %Q, %d, %d, %d)", db_name, "gpkg_geometry_columns", table_name, column_name, normalized_geom_type, srs_id, z, m); if (result != SQLITE_OK) { error_append(error, sqlite3_errmsg(db)); return result; } return SQLITE_OK; }
/* * 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; uint64_t for_time_usec = 0; char *base_query_str = NULL; CHECK_SQL_CONN (sql_conn, out); GF_VALIDATE_OR_GOTO(GFDB_STR_SQLITE3, query_callback, out); ret = gf_get_basic_query_stmt (&base_query_str); if (ret <= 0) { goto out; } ret = gf_asprintf (&query_str, "%s AND " /*First condition: For writes*/ "( ((" GF_COL_TB_WSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_WMSEC ") <= ? )" " AND " /*Second condition: For reads*/ "((" GF_COL_TB_RWSEC " * " TOSTRING(GFDB_MICROSEC) " + " GF_COL_TB_RWMSEC ") <= ?) )", base_query_str); if (ret < 0) { gf_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_QUERY_FAILED, "Failed to create query statement"); query_str = NULL; goto out; } 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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_PREPARE_FAILED, "Failed to prepare 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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind for_time_usec " "%"PRIu64" : %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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_BINDING_FAILED, "Failed to bind for_time_usec " "%"PRIu64" : %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_msg (GFDB_STR_SQLITE3, GF_LOG_ERROR, 0, LG_MSG_QUERY_FAILED, "Failed Query %s", query_str); goto out; } ret = 0; out: sqlite3_finalize (prep_stmt); GF_FREE (base_query_str); GF_FREE (query_str); return ret; }
int main(int argc,char* argv[]){ sqlite3 *db; char *zErrMsg=0; char file[100]; char *sql; int i; sqlite3_stmt *prepared_insert,*prepared_delete,*prepared_replace,*prepared_source_redirect; listNode* root=calloc(1,sizeof(listNode)); strcat(file,getenv("HOME")); strncat(file,"/freshen.db",13); int rc; rc=sqlite3_open(file,&db); if(rc){ fprintf(stderr,"Can't open database:%s\n",sqlite3_errmsg(db)); exit(0); }else{ fprintf(stderr,"database opened successfully\n"); } sql=CREATE_TABLE; rc=sqlite3_exec(db,sql,callback,0,&zErrMsg); if(rc!=SQLITE_OK){ fprintf(stderr,"SQL Error:%s\n",zErrMsg); sqlite3_free(zErrMsg); }else{ fprintf(stderr,"Table created sucessfully\n"); } rc=sqlite3_prepare_v2(db,"insert into FILES (DESTINATION,SOURCE,DANGEROUS) values (?,?,?);",-1,&prepared_insert,NULL); if(rc!=SQLITE_OK){ //write this sometime } rc=sqlite3_prepare_v2(db,"delete from FILES where DESTINATION = ?",-1,&prepared_delete,NULL); if(rc!=SQLITE_OK){ //do something here sometime. } rc=sqlite3_prepare_v2(db,"update FILES set SOURCE = ? where DESTINATION = ?",-1,&prepared_replace,NULL); if(rc!=SQLITE_OK){ //Needs to be written } rc=sqlite3_prepare_v2(db,"UPDATE FILES SET SOURCE = ? WHERE SOURCE = ?",-1,&prepared_source_redirect,NULL); if(rc!=SQLITE_OK){ //This sort of thing might not need to actually be written, after all, they //should compile to the same thing each time. fprintf(stderr,"Something broke!\n"); } for(i=1;i<argc;i++){ if(strcmp(argv[i],"-insert")==0){ char *dest=argv[i+1]; char *src=argv[i+2]; char destpath[PATH_MAX+1], srcpath[PATH_MAX+1]; if(!isArgFile(dest)){ fprintf(stderr,"-insert requires two arguments <destination file> <source file> [-dangerous]?\n%s is not visible to this as a file\n",dest); exit(1); } if(!isArgFile(src)){ fprintf(stderr,"-insert requires two arguments <destination file> <source file> [-dangerous]?\n%s is not visible to this as a file\n",src); exit(1); } if(!fileExists(src)) { fprintf(stderr,"%s does not exist, source files must exist\n",src); exit(1); } realpath(dest,destpath); realpath(src,srcpath); i+=2; short dangerous=0; if(argc>=i+2){ if(strcmp(argv[i+1],"-dangerous")==0) { dangerous=1; i++; } } if(sqlite3_bind_text(prepared_insert,1,destpath,-1,SQLITE_STATIC)!=SQLITE_OK) fprintf(stderr,"Failed to bind destination\n"); if(sqlite3_bind_text(prepared_insert,2,srcpath,-1,SQLITE_STATIC)!=SQLITE_OK) fprintf(stderr,"Failed to bind source\n"); if(sqlite3_bind_int(prepared_insert,3,dangerous)!=SQLITE_OK) fprintf(stderr,"Failed to bind dangerous\n"); rc=sqlite3_step(prepared_insert); if(rc!=SQLITE_DONE){ fprintf(stderr,"Didn't run right: %s\n",sqlite3_errstr(rc)); } sqlite3_reset(prepared_insert);//Reset prepared statement }else if(strcmp(argv[i],"-freshen")==0){ sqlite3_exec(db,"select * from FILES;",freshen,(void*)root,&zErrMsg); listNode* r=root; struct stat srcbuf,dstbuf; short destination_exists=1,skip_danger=0; char can_replace=1; struct utimbuf replacement_time; if(argc>i+1&&strcmp(argv[i+1],"-safe-only")){ skip_danger=1; i++; } while(r){ rc=stat(r->destination,&dstbuf); if(rc==-1){ if(errno!=ENOENT){ fprintf(stderr,"It seems that the destination is inaccessible for some reason\n"); exit(1); }else destination_exists=0; }else destination_exists=1; rc=stat(r->source,&srcbuf); if(rc==-1){ fprintf(stderr,"It seems that the source file is inaccessible for some reason\n"); exit(1); } if(r->dangerous) { if(skip_danger) { r=r->next; continue; } printf("%s is marked as being dangerous, is %s ready to be used?\n",r->destination,r->source); scanf("%c",&can_replace); }else can_replace=1; if((can_replace!=0&&can_replace!='n')||!destination_exists){ printf(srcbuf.st_mtime>dstbuf.st_mtime? "Replacing %s with %s\n":"%s up to date with %s\n",r->destination,r->source); cp(r->destination,r->source); replacement_time.modtime=srcbuf.st_mtim.tv_sec; replacement_time.actime=srcbuf.st_atim.tv_sec; utime(r->destination,&replacement_time);//replace with the source file's time } r=r->next; } freeList(root); root=calloc(1,sizeof(listNode)); }else if (strcmp(argv[i],"-remove")==0){//Remove destination file from database, doesn't really matter if it succeeds. char rpath[PATH_MAX+1]; realpath(argv[i+1],rpath); i++; sqlite3_bind_text(prepared_delete,1,rpath,-1,SQLITE_STATIC); sqlite3_step(prepared_delete); sqlite3_reset(prepared_delete); }else if(strcmp(argv[i],"-replace")==0){ char srcpath[PATH_MAX+1],dstpath[PATH_MAX+1]; realpath(argv[i+1],dstpath); realpath(argv[i+2],srcpath); sqlite3_bind_text(prepared_replace,1,srcpath,-1,SQLITE_STATIC); sqlite3_bind_text(prepared_replace,2,dstpath,-1,SQLITE_STATIC); sqlite3_step(prepared_replace); sqlite3_reset(prepared_replace); }else if(strcmp(argv[i],"-redirect")==0){ char srcpath[PATH_MAX+1],newpath[PATH_MAX+1]; if(argc<=i+2){ fprintf(stderr,"The proper invocation of -redirect requires two arguments <original source> <new source>\n"); exit(1); } realpath(argv[i+1],srcpath);//Get the full path of the file. realpath(argv[i+2],newpath); printf("%s\n",srcpath); sqlite3_bind_text(prepared_source_redirect,1,newpath,-1,SQLITE_STATIC); sqlite3_bind_text(prepared_source_redirect,2,srcpath,-1,SQLITE_STATIC); rc=sqlite3_step(prepared_source_redirect); if(rc!=SQLITE_DONE){ fprintf(stderr,"%s\n",sqlite3_errmsg(db)); } sqlite3_reset(prepared_source_redirect); i+=2; } } sqlite3_close(db); }
SWITCH_DECLARE(const char *) switch_core_db_errmsg(switch_core_db_t *db) { return sqlite3_errmsg(db); }