Ejemplo n.º 1
2
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;
}
Ejemplo n.º 2
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;
}
Ejemplo n.º 3
0
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;
}
Ejemplo n.º 4
0
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;
}
Ejemplo n.º 5
0
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;
}
Ejemplo n.º 6
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;
}
Ejemplo n.º 7
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
}
Ejemplo n.º 8
0
void raise_sqlite_error(::sqlite3* db)
{
    std::string s = "SQLite3 Error: ";
    s += sqlite3_errmsg(db);
    throw exception(s.c_str());
}
Ejemplo n.º 9
0
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;
}
Ejemplo n.º 10
0
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;
}
Ejemplo n.º 11
0
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;
}
Ejemplo n.º 12
0
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;
}
Ejemplo n.º 13
0
std::string Query::GetError()
{
	if (odb)
		return sqlite3_errmsg(odb -> db);
	return "";
}
Ejemplo n.º 14
0
/*
** 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;
}
Ejemplo n.º 15
0
/*
 * 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;
}
Ejemplo n.º 16
0
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;
}
Ejemplo n.º 17
0
		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:
Ejemplo n.º 18
0
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;
}
Ejemplo n.º 19
0
/*
 * 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
}
Ejemplo n.º 20
0
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;
}
Ejemplo n.º 21
0
/* 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
}
Ejemplo n.º 22
0
Archivo: main.c Proyecto: lr6666/duan
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;
}
Ejemplo n.º 23
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;
}
Ejemplo n.º 24
0
const char* SQLiteDatabase::lastErrorMsg()
{
    if (m_db)
        return sqlite3_errmsg(m_db);
    return m_openErrorMessage.isNull() ? notOpenErrorMessage : m_openErrorMessage.data();
}
Ejemplo n.º 25
0
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);
}
Ejemplo n.º 26
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;
}
Ejemplo n.º 27
0
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;
}
Ejemplo n.º 28
0
/*
 * 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;
}
Ejemplo n.º 29
0
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);
}
Ejemplo n.º 30
0
SWITCH_DECLARE(const char *) switch_core_db_errmsg(switch_core_db_t *db)
{
    return sqlite3_errmsg(db);
}