Example #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;
}
extern "C" int register_android_functions(sqlite3 * handle, int utf16Storage)
{
    int err;
    UErrorCode status = U_ZERO_ERROR;

    UCollator * collator = ucol_open(NULL, &status);
    if (U_FAILURE(status)) {
        return -1;
    }

    if (utf16Storage) {
        // Note that text should be stored as UTF-16
        err = sqlite3_exec(handle, "PRAGMA encoding = 'UTF-16'", 0, 0, 0);
        if (err != SQLITE_OK) {
            return err;
        }

        // Register the UNICODE collation
        err = sqlite3_create_collation_v2(handle, "UNICODE", SQLITE_UTF16, collator, collate16,
                (void(*)(void*))localized_collator_dtor);
    } else {
        err = sqlite3_create_collation_v2(handle, "UNICODE", SQLITE_UTF8, collator, collate8,
                (void(*)(void*))localized_collator_dtor);
    }

    if (err != SQLITE_OK) {
        return err;
    }

    // Register the PHONE_NUM_EQUALS function
    err = sqlite3_create_function(
        handle, "PHONE_NUMBERS_EQUAL", 2,
        SQLITE_UTF8, NULL, phone_numbers_equal, NULL, NULL);
    if (err != SQLITE_OK) {
        return err;
    }

    // Register the PHONE_NUM_EQUALS function with an additional argument "use_strict"
    err = sqlite3_create_function(
        handle, "PHONE_NUMBERS_EQUAL", 3,
        SQLITE_UTF8, NULL, phone_numbers_equal, NULL, NULL);
    if (err != SQLITE_OK) {
        return err;
    }

    // Register the _DELETE_FILE function
    err = sqlite3_create_function(handle, "_DELETE_FILE", 1, SQLITE_UTF8, NULL, delete_file, NULL, NULL);
    if (err != SQLITE_OK) {
        return err;
    }

#if ENABLE_ANDROID_LOG
    // Register the _LOG function
    err = sqlite3_create_function(handle, "_LOG", 1, SQLITE_UTF8, NULL, android_log, NULL, NULL);
    if (err != SQLITE_OK) {
        return err;
    }
#endif

    // Register the GET_PHONEBOOK_INDEX function
    err = sqlite3_create_function(handle,
        "GET_PHONEBOOK_INDEX",
        2, SQLITE_UTF8, NULL,
        get_phonebook_index,
        NULL, NULL);
    if (err != SQLITE_OK) {
        return err;
    }

    return SQLITE_OK;
}
Example #3
0
int sqlcipher_codec_ctx_migrate(codec_ctx *ctx) {
  u32 meta;
  int rc = 0;
  int command_idx = 0;
  int password_sz;
  int saved_flags;
  int saved_nChange;
  int saved_nTotalChange;
  void (*saved_xTrace)(void*,const char*);
  Db *pDb = 0;
  sqlite3 *db = ctx->pBt->db;
  const char *db_filename = sqlite3_db_filename(db, "main");
  char *migrated_db_filename = sqlite3_mprintf("%s-migrated", db_filename);
  char *pragma_hmac_off = "PRAGMA cipher_use_hmac = OFF;";
  char *pragma_4k_kdf_iter = "PRAGMA kdf_iter = 4000;";
  char *pragma_1x_and_4k;
  char *set_user_version;
  char *key;
  int key_sz;
  int user_version = 0;
  int upgrade_1x_format = 0;
  int upgrade_4k_format = 0;
  static const unsigned char aCopy[] = {
    BTREE_SCHEMA_VERSION,     1,  /* Add one to the old schema cookie */
    BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
    BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
    BTREE_USER_VERSION,       0,  /* Preserve the user version */
    BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
  };


  key_sz = ctx->read_ctx->pass_sz + 1;
  key = sqlcipher_malloc(key_sz);
  memset(key, 0, key_sz);
  memcpy(key, ctx->read_ctx->pass, ctx->read_ctx->pass_sz);

  if(db_filename){
    const char* commands[5];
    char *attach_command = sqlite3_mprintf("ATTACH DATABASE '%s-migrated' as migrate KEY '%q';",
                                            db_filename, key);

    int rc = sqlcipher_check_connection(db_filename, key, ctx->read_ctx->pass_sz, "", &user_version);
    if(rc == SQLITE_OK){
      CODEC_TRACE(("No upgrade required - exiting\n"));
      goto exit;
    }
    
    // Version 2 - check for 4k with hmac format 
    rc = sqlcipher_check_connection(db_filename, key, ctx->read_ctx->pass_sz, pragma_4k_kdf_iter, &user_version);
    if(rc == SQLITE_OK) {
      CODEC_TRACE(("Version 2 format found\n"));
      upgrade_4k_format = 1;
    }

    // Version 1 - check both no hmac and 4k together
    pragma_1x_and_4k = sqlite3_mprintf("%s%s", pragma_hmac_off,
                                             pragma_4k_kdf_iter);
    rc = sqlcipher_check_connection(db_filename, key, ctx->read_ctx->pass_sz, pragma_1x_and_4k, &user_version);
    sqlite3_free(pragma_1x_and_4k);
    if(rc == SQLITE_OK) {
      CODEC_TRACE(("Version 1 format found\n"));
      upgrade_1x_format = 1;
      upgrade_4k_format = 1;
    }

    if(upgrade_1x_format == 0 && upgrade_4k_format == 0) {
      CODEC_TRACE(("Upgrade format not determined\n"));
      goto handle_error;
    }

    set_user_version = sqlite3_mprintf("PRAGMA migrate.user_version = %d;", user_version);
    commands[0] = upgrade_4k_format == 1 ? pragma_4k_kdf_iter : "";
    commands[1] = upgrade_1x_format == 1 ? pragma_hmac_off : "";
    commands[2] = attach_command;
    commands[3] = "SELECT sqlcipher_export('migrate');";
    commands[4] = set_user_version;
      
    for(command_idx = 0; command_idx < ArraySize(commands); command_idx++){
      const char *command = commands[command_idx];
      if(strcmp(command, "") == 0){
        continue;
      }
      rc = sqlite3_exec(db, command, NULL, NULL, NULL);
      if(rc != SQLITE_OK){
        break;
      }
    }
    sqlite3_free(attach_command);
    sqlite3_free(set_user_version);
    sqlcipher_free(key, key_sz);
    
    if(rc == SQLITE_OK){
      Btree *pDest;
      Btree *pSrc;
      int i = 0;

      if( !db->autoCommit ){
        CODEC_TRACE(("cannot migrate from within a transaction"));
        goto handle_error;
      }
      if( db->nVdbeActive>1 ){
        CODEC_TRACE(("cannot migrate - SQL statements in progress"));
        goto handle_error;
      }

      /* Save the current value of the database flags so that it can be
      ** restored before returning. Then set the writable-schema flag, and
      ** disable CHECK and foreign key constraints.  */
      saved_flags = db->flags;
      saved_nChange = db->nChange;
      saved_nTotalChange = db->nTotalChange;
      saved_xTrace = db->xTrace;
      db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin;
      db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
      db->xTrace = 0;
      
      pDest = db->aDb[0].pBt;
      pDb = &(db->aDb[db->nDb-1]);
      pSrc = pDb->pBt;
      
      rc = sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL);
      rc = sqlite3BtreeBeginTrans(pSrc, 2);
      rc = sqlite3BtreeBeginTrans(pDest, 2);
      
      assert( 1==sqlite3BtreeIsInTrans(pDest) );
      assert( 1==sqlite3BtreeIsInTrans(pSrc) );

      sqlite3CodecGetKey(db, db->nDb - 1, (void**)&key, &password_sz);
      sqlite3CodecAttach(db, 0, key, password_sz);
      sqlite3pager_get_codec(pDest->pBt->pPager, (void**)&ctx);
      
      ctx->skip_read_hmac = 1;      
      for(i=0; i<ArraySize(aCopy); i+=2){
        sqlite3BtreeGetMeta(pSrc, aCopy[i], &meta);
        rc = sqlite3BtreeUpdateMeta(pDest, aCopy[i], meta+aCopy[i+1]);
        if( NEVER(rc!=SQLITE_OK) ) goto handle_error; 
      }
      rc = sqlite3BtreeCopyFile(pDest, pSrc);
      ctx->skip_read_hmac = 0;
      if( rc!=SQLITE_OK ) goto handle_error;
      rc = sqlite3BtreeCommit(pDest);

      db->flags = saved_flags;
      db->nChange = saved_nChange;
      db->nTotalChange = saved_nTotalChange;
      db->xTrace = saved_xTrace;
      db->autoCommit = 1;
      sqlite3BtreeClose(pDb->pBt);
      pDb->pBt = 0;
      pDb->pSchema = 0;
      sqlite3ResetAllSchemasOfConnection(db);
      remove(migrated_db_filename);
      sqlite3_free(migrated_db_filename);
    } else {
      CODEC_TRACE(("*** migration failure** \n\n"));
    }
    
  }
  goto exit;

 handle_error:
  CODEC_TRACE(("An error occurred attempting to migrate the database\n"));
  rc = SQLITE_ERROR;

 exit:
  return rc;
}
Example #4
0
int SQLI_cache_dbop(struct DBdesc *db, struct db_cache *cache_elem, struct insert_data *idata)
{
  char *ptr_values, *ptr_where, *ptr_mv, *ptr_set, *ptr_insert;
  int num=0, num_set=0, ret=0, have_flows=0, len=0;

  if (idata->mv.last_queue_elem) {
    ret = sqlite3_exec(db->desc, multi_values_buffer, NULL, NULL, NULL);
    Log(LOG_DEBUG, "DEBUG ( %s/%s ): %d INSERT statements sent to the SQLite database.\n",
                config.name, config.type, idata->mv.buffer_elem_num);
    if (ret) goto signal_error;
    idata->iqn++;
    idata->mv.buffer_elem_num = FALSE;
    idata->mv.buffer_offset = 0;

    return FALSE;
  }
  
  if (config.what_to_count & COUNT_FLOWS) have_flows = TRUE;

  /* constructing sql query */
  ptr_where = where_clause;
  ptr_values = values_clause; 
  ptr_set = set_clause;
  ptr_insert = insert_full_clause;
  memset(where_clause, 0, sizeof(where_clause));
  memset(values_clause, 0, sizeof(values_clause));
  memset(set_clause, 0, sizeof(set_clause));
  memset(insert_full_clause, 0, sizeof(insert_full_clause));

  for (num = 0; num < idata->num_primitives; num++)
    (*where[num].handler)(cache_elem, idata, num, &ptr_values, &ptr_where);

  if (cache_elem->flow_type == NF9_FTYPE_EVENT || cache_elem->flow_type == NF9_FTYPE_OPTION) {
    for (num_set = 0; set_event[num_set].type; num_set++)
      (*set_event[num_set].handler)(cache_elem, idata, num_set, &ptr_set, NULL);
  }
  else {
    for (num_set = 0; set[num_set].type; num_set++)
      (*set[num_set].handler)(cache_elem, idata, num_set, &ptr_set, NULL);
  }
  
  /* sending UPDATE query a) if not switched off and
     b) if we actually have something to update */
  if (!config.sql_dont_try_update && num_set) {
    strncpy(sql_data, update_clause, SPACELEFT(sql_data));
    strncat(sql_data, set_clause, SPACELEFT(sql_data));
    strncat(sql_data, where_clause, SPACELEFT(sql_data));

    ret = sqlite3_exec(db->desc, sql_data, NULL, NULL, NULL);
    if (ret) goto signal_error; 
  }

  if (config.sql_dont_try_update || !num_set || (sqlite3_changes(db->desc) == 0)) {
    /* UPDATE failed, trying with an INSERT query */ 
    if (cache_elem->flow_type == NF9_FTYPE_EVENT || cache_elem->flow_type == NF9_FTYPE_OPTION) {
      strncpy(insert_full_clause, insert_clause, SPACELEFT(insert_full_clause));
      strncat(insert_full_clause, insert_nocounters_clause, SPACELEFT(insert_full_clause));
      strncat(ptr_values, ")", SPACELEFT(values_clause));
    }
    else {
      strncpy(insert_full_clause, insert_clause, SPACELEFT(insert_full_clause));
      strncat(insert_full_clause, insert_counters_clause, SPACELEFT(insert_full_clause));
#if defined HAVE_64BIT_COUNTERS
      if (have_flows) snprintf(ptr_values, SPACELEFT(values_clause), ", %llu, %llu, %llu)", cache_elem->packet_counter, cache_elem->bytes_counter, cache_elem->flows_counter);
      else snprintf(ptr_values, SPACELEFT(values_clause), ", %llu, %llu)", cache_elem->packet_counter, cache_elem->bytes_counter);
#else
      if (have_flows) snprintf(ptr_values, SPACELEFT(values_clause), ", %lu, %lu, %lu)", cache_elem->packet_counter, cache_elem->bytes_counter, cache_elem->flows_counter);
      else snprintf(ptr_values, SPACELEFT(values_clause), ", %lu, %lu)", cache_elem->packet_counter, cache_elem->bytes_counter);
#endif
    }
    
    strncpy(sql_data, insert_full_clause, sizeof(sql_data));
    strncat(sql_data, values_clause, SPACELEFT(sql_data));

    if (config.sql_multi_values) {
      multi_values_handling:
      len = config.sql_multi_values-idata->mv.buffer_offset;
      if (strlen(values_clause) < len) {
	if (idata->mv.buffer_elem_num) {
	  strcpy(multi_values_buffer+idata->mv.buffer_offset, "; ");
	  idata->mv.buffer_offset++;
	  idata->mv.buffer_offset++;
	}
	ptr_mv = multi_values_buffer+idata->mv.buffer_offset;
	strcpy(multi_values_buffer+idata->mv.buffer_offset, sql_data); 
	idata->mv.buffer_offset += strlen(ptr_mv);
        idata->mv.buffer_elem_num++;
      }
      else {
	if (idata->mv.buffer_elem_num) {
	  ret = sqlite3_exec(db->desc, multi_values_buffer, NULL, NULL, NULL);
	  Log(LOG_DEBUG, "DEBUG ( %s/%s ): %d INSERT statements sent to the SQLite database.\n",
		config.name, config.type, idata->mv.buffer_elem_num);
	  if (ret) goto signal_error;
	  idata->iqn++;
	  idata->mv.buffer_elem_num = FALSE;
	  idata->mv.head_buffer_elem = FALSE;
	  idata->mv.buffer_offset = 0;
	  goto multi_values_handling;
	}
	else {
	  Log(LOG_ERR, "ERROR ( %s/%s ): 'sql_multi_values' is too small (%d). Try with a larger value.\n",
	  config.name, config.type, config.sql_multi_values);
	  exit_plugin(1);
	}
      }
    }
    else {
      ret = sqlite3_exec(db->desc, sql_data, NULL, NULL, NULL);
      Log(LOG_DEBUG, "( %s/%s ): %s\n\n", config.name, config.type, sql_data);
      if (ret) goto signal_error; 
      idata->iqn++;
    }
  }
  else {
    Log(LOG_DEBUG, "( %s/%s ): %s\n\n", config.name, config.type, sql_data);
    idata->uqn++;
  }

  idata->een++;
  // cache_elem->valid = FALSE; /* committed */
  
  return ret;

  signal_error:
  if (!idata->mv.buffer_elem_num) Log(LOG_DEBUG, "DEBUG ( %s/%s ): FAILED query follows:\n%s\n", config.name, config.type, sql_data); 
  else {
    if (!idata->recover || db->type != BE_TYPE_PRIMARY) {
      /* DB failure: we will rewind the multi-values buffer */
      idata->current_queue_elem = idata->mv.head_buffer_elem;
      idata->mv.buffer_elem_num = 0;
    } 
  }
  SQLI_get_errmsg(db);
  if (db->errmsg) Log(LOG_ERR, "ERROR ( %s/%s ): %s\n\n", config.name, config.type, db->errmsg);

  return ret;
}
Example #5
0
/*
** Query the database.  But instead of invoking a callback for each row,
** malloc() for space to hold the result and return the entire results
** at the conclusion of the call.
**
** The result that is written to ***pazResult is held in memory obtained
** from malloc().  But the caller cannot free this memory directly.  
** Instead, the entire table should be passed to sqlite3_free_table() when
** the calling procedure is finished using it.
*/
SQLITE_API int sqlite3_get_table(
  sqlite3 *db,                /* The database on which the SQL executes */
  const char *zSql,           /* The SQL to be executed */
  char ***pazResult,          /* Write the result table here */
  int *pnRow,                 /* Write the number of rows in the result here */
  int *pnColumn,              /* Write the number of columns of result here */
  char **pzErrMsg             /* Write error messages here */
){
  int rc;
  TabResult res;

  *pazResult = 0;
  if( pnColumn ) *pnColumn = 0;
  if( pnRow ) *pnRow = 0;
  if( pzErrMsg ) *pzErrMsg = 0;
  res.zErrMsg = 0;
  res.nRow = 0;
  res.nColumn = 0;
  res.nData = 1;
  res.nAlloc = 20;
  res.rc = SQLITE_OK;
  res.azResult = sqlite3_malloc(sizeof(char*)*res.nAlloc );
  if( res.azResult==0 ){
     db->errCode = SQLITE_NOMEM;
     return SQLITE_NOMEM;
  }
  res.azResult[0] = 0;
  rc = sqlite3_exec(db, zSql, sqlite3_get_table_cb, &res, pzErrMsg);
  assert( sizeof(res.azResult[0])>= sizeof(res.nData) );
  res.azResult[0] = SQLITE_INT_TO_PTR(res.nData);
  if( (rc&0xff)==SQLITE_ABORT ){
    sqlite3_free_table(&res.azResult[1]);
    if( res.zErrMsg ){
      if( pzErrMsg ){
        sqlite3_free(*pzErrMsg);
        *pzErrMsg = sqlite3_mprintf("%s",res.zErrMsg);
      }
      sqlite3_free(res.zErrMsg);
    }
    db->errCode = res.rc;  /* Assume 32-bit assignment is atomic */
    return res.rc;
  }
  sqlite3_free(res.zErrMsg);
  if( rc!=SQLITE_OK ){
    sqlite3_free_table(&res.azResult[1]);
    return rc;
  }
  if( res.nAlloc>res.nData ){
    char **azNew;
    azNew = sqlite3_realloc( res.azResult, sizeof(char*)*res.nData );
    if( azNew==0 ){
      sqlite3_free_table(&res.azResult[1]);
      db->errCode = SQLITE_NOMEM;
      return SQLITE_NOMEM;
    }
    res.azResult = azNew;
  }
  *pazResult = &res.azResult[1];
  if( pnColumn ) *pnColumn = res.nColumn;
  if( pnRow ) *pnRow = res.nRow;
  return rc;
}
Example #6
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);
}
Example #7
0
bool rec_sqlite_writeTable ()
{
	traceLastFunc( "rec_sqlite_writeTable()" );

	sqlite3 *rec_db;
	char sql_cmd[1024];
	char *errmsgs = NULL;
	int ret_exists;

	if ( rec_state == RECORDING_RECORD )
	{
		cheat_state_text( "Can't save while recording." );
		return false;
	}

	if ( rec_maxNum <= 0 )
	{
		cheat_state_text( "Nothing to be saved." );
		return false;
	}

	if ( sqlite3_open( REC_DB_NAME, &rec_db ) != SQLITE_OK )
	{
		Log( "SQLite - Error while connecting: %s", sqlite3_errmsg(rec_db) );
		sqlite3_close( rec_db );
		return false;
	}

	for ( int i = 0; i < 64; i++ ) // max default name
	{
		_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "route%i", i );
		ret_exists = sqliteDB_checkTableExists( rec_db, sql_cmd );
		// continue, if table already exists
		if ( ret_exists == 1 )
			continue;
		// quit function on fail
		if ( ret_exists == -1 )
		{
			sqlite3_close( rec_db );
			return false;
		}

		// create table with default name 'route..'
		_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "CREATE TABLE 'route%i'(", i );
		_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "%s 'index' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," \
			"'maxNum' INTEGER NULL DEFAULT NULL," \
			"'angle1' REAL NOT NULL,'angle2' REAL NOT NULL,'angle3' REAL NOT NULL,'angle4' REAL NOT NULL," \
			"'angle5' REAL NOT NULL,'angle6' REAL NOT NULL,"\
			"'spin1' REAL NOT NULL,'spin2' REAL NOT NULL,'spin3' REAL NOT NULL," \
			"'speed1' REAL NOT NULL,'speed2' REAL NOT NULL,'speed3' REAL NOT NULL," \
			"'pos1' REAL NOT NULL,'pos2' REAL NOT NULL,'pos3' REAL NOT NULL);"
			, sql_cmd );
		sqlite3_exec( rec_db, sql_cmd, NULL, NULL, &errmsgs );
		if ( errmsgs != NULL )
		{
			Log( "SQLite - Error (executing CREATE TABLE statement): %s", errmsgs );
			sqlite3_close( rec_db );
			return false;
		}

		// add our data into the new table
		for ( int j = 0; j < rec_maxNum && j < (REC_ARRAYSIZE-1); j++ )
		{
			if ( j != 0 )
				_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "INSERT INTO 'route%i' VALUES( null, null,", i );
			else
				_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "INSERT INTO 'route%i' VALUES( null, %i,", i, rec_maxNum );
			_snprintf_s( sql_cmd, sizeof(sql_cmd)-1, "%s %0.2f, %0.2f, %0.2f, %0.2f,"
				"%0.2f, %0.2f," \
				"%0.2f, %0.2f, %0.2f," \
				"%0.2f, %0.2f, %0.2f," \
				"%0.2f, %0.2f, %0.2f" \
				");", 
				sql_cmd, rec_angle[j][0], rec_angle[j][1], rec_angle[j][2], rec_angle[j][3], 
				rec_angle[j][4], rec_angle[j][5],
				rec_spin[j][0], rec_spin[j][1], rec_spin[j][2],
				rec_speed[j][0], rec_speed[j][1], rec_speed[j][2],
				rec_pos[j][0], rec_pos[j][1], rec_pos[j][2] );

			//Log( sql_cmd );
			sqlite3_exec( rec_db, sql_cmd, NULL, NULL, &errmsgs );
			if ( errmsgs != NULL )
			{
				Log( "SQLite - Error (executing INSERT INTO statement): %s", errmsgs );
				sqlite3_close( rec_db );
				return false;
			}
		}

		cheat_state_text( "saved to 'route%i'", i );
		break;
	}

	sqlite3_close( rec_db );
	return true;
}
Example #8
0
static int try_to_open_db(const char *filename, struct memblock *mem, struct dive_table *table)
{
	sqlite3 *handle;
	char dm4_test[] = "select count(*) from sqlite_master where type='table' and name='Dive' and sql like '%ProfileBlob%'";
	char dm5_test[] = "select count(*) from sqlite_master where type='table' and name='Dive' and sql like '%SampleBlob%'";
	char shearwater_test[] = "select count(*) from sqlite_master where type='table' and name='system' and sql like '%dbVersion%'";
	char cobalt_test[] = "select count(*) from sqlite_master where type='table' and name='TrackPoints' and sql like '%DepthPressure%'";
	char divinglog_test[] = "select count(*) from sqlite_master where type='table' and name='DBInfo' and sql like '%PrgName%'";
	int retval;

	retval = sqlite3_open(filename, &handle);

	if (retval) {
		fprintf(stderr, "Database connection failed '%s'.\n", filename);
		return 1;
	}

	/* Testing if DB schema resembles Suunto DM5 database format */
	retval = sqlite3_exec(handle, dm5_test, &db_test_func, 0, NULL);
	if (!retval) {
		retval = parse_dm5_buffer(handle, filename, mem->buffer, mem->size, table);
		sqlite3_close(handle);
		return retval;
	}

	/* Testing if DB schema resembles Suunto DM4 database format */
	retval = sqlite3_exec(handle, dm4_test, &db_test_func, 0, NULL);
	if (!retval) {
		retval = parse_dm4_buffer(handle, filename, mem->buffer, mem->size, table);
		sqlite3_close(handle);
		return retval;
	}

	/* Testing if DB schema resembles Shearwater database format */
	retval = sqlite3_exec(handle, shearwater_test, &db_test_func, 0, NULL);
	if (!retval) {
		retval = parse_shearwater_buffer(handle, filename, mem->buffer, mem->size, table);
		sqlite3_close(handle);
		return retval;
	}

	/* Testing if DB schema resembles Atomic Cobalt database format */
	retval = sqlite3_exec(handle, cobalt_test, &db_test_func, 0, NULL);
	if (!retval) {
		retval = parse_cobalt_buffer(handle, filename, mem->buffer, mem->size, table);
		sqlite3_close(handle);
		return retval;
	}

	/* Testing if DB schema resembles Divinglog database format */
	retval = sqlite3_exec(handle, divinglog_test, &db_test_func, 0, NULL);
	if (!retval) {
		retval = parse_divinglog_buffer(handle, filename, mem->buffer, mem->size, table);
		sqlite3_close(handle);
		return retval;
	}

	sqlite3_close(handle);

	return retval;
}
Example #9
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;
}
Example #10
0
// "SELECT fold, id, rev, features FROM..."
int Database::GetTuples(const std::string& q, std::vector<tuple_t>* r) const
{
	return sqlite3_exec(m_db, q.c_str(), Database::get_vector,
			static_cast<void*>(r), NULL);
}
Example #11
0
// "SELECT COUNT(*) FROM .. "
int Database::Count(const std::string& q, unsigned long *r) const
{
	return sqlite3_exec(m_db, q.c_str(), Database::set_ulong,
			static_cast<void*>(r), NULL);
}
Example #12
0
void database::add_trade(const TradeGateway::ExecutionReport *pTrade)
{
	if( !this->is_connected() )
		this->connect();

	if( pTrade->lastQty == 0 ) return;

	// gb2312 to utf8
	std::string text = siconv(pTrade->ordRejReason,"GB2312","UTF-8");

	std::ostringstream sql;
		
	sql << "BEGIN;";
	sql << "INSERT INTO dt_trades VALUES ("
		<< "'" << pTrade->accountId << "',"
		<< "'" << pTrade->ordId<< "',"
		<< "'" << pTrade->execId << "',"
		<< ""  << pTrade->type<< ","
		<< "\"" << text<< "\","
		<< ""  << pTrade->lastQty << ","
		<< ""  << pTrade->lastPx<< ","
		<< ""  << pTrade->tradeDate<< ","
		<< ""  << pTrade->transactTime<< ");";
	
	//更新委托表
	TradeGateway::OrderStatus os;
	switch( pTrade->type )
	{
	case TradeGateway::EtCanceled:
		os =  TradeGateway::Canceled;
		sql << "UPDATE dt_orders SET "
			<< "ordStatus =" << os << ","
			<< "cumQty=(ordQty-" << abs((int)pTrade->lastQty) << "),"
			<< "text=\"" << text << "\","
			<< "leavesQty="  <<  0 << " "
			<< "WHERE (ordId='" <<  pTrade->ordId << "') "
			<< "AND (accountId='" << pTrade->accountId << "') "
			<< "AND (date=" << pTrade->tradeDate << ");";
		break;
	case TradeGateway::EtRejected:
		os =  TradeGateway::Rejected;
		sql << "UPDATE dt_orders SET "
			<< "ordStatus =" << os << ","
			<< "cumQty=(ordQty-" << abs((int)pTrade->lastQty) << "),"
			<< "text=\"" << text << "\","
			<< "leavesQty="  <<  0 << " "
			<< "WHERE (ordId='" <<  pTrade->ordId << "') "
			<< "AND (accountId='" << pTrade->accountId << "') "
			<< "AND (date=" << pTrade->tradeDate << ");";
		break;
	case TradeGateway::EtStopped:
		os =  TradeGateway::Stopped;
		sql << "UPDATE dt_orders SET "
			<< "ordStatus =" << os << ","
			<< "cumQty=(ordQty-" << abs((int)pTrade->lastQty) << "),"
			<< "text=\"" << text << "\","
			<< "leavesQty="  <<  0 << " "
			<< "WHERE (ordId='" <<  pTrade->ordId << "') "
			<< "AND (accountId='" << pTrade->accountId << "') "
			<< "AND (date=" << pTrade->tradeDate << ");";
		break;
	case TradeGateway::EtTrade:
		sql << "UPDATE dt_orders SET "
			<< "avgPx=(cumQty*avgPx+" <<pTrade->lastQty*pTrade->lastPx<< ")/(cumQty+"<< pTrade->lastQty << "),"
			<< "cumQty=(cumQty+" << pTrade->lastQty << "),"
			<< "leavesQty=leavesQty-"  <<  pTrade->lastQty << ","
			<< "ordStatus = CASE (leavesQty-"<<pTrade->lastQty <<") WHEN 0 then " << TradeGateway::Filled << " ELSE "<<TradeGateway::Working <<" END "
			<< "WHERE (ordId='" <<  pTrade->ordId << "') "
			<< "AND (accountId='" << pTrade->accountId << "') "
			<< "AND (date=" << pTrade->tradeDate << ");";
		break;
	}

	sql << "COMMIT;";
	char *zErrMsg = 0;

	int rc = sqlite3_exec(pdb, sql.str().c_str(), NULL, 0, &zErrMsg);
	if( rc!=SQLITE_OK )
	{
		std::ostringstream err ;
		err << "SQL error: " << zErrMsg;
		sqlite3_free(zErrMsg);
		throw err.str();
	}
}
Example #13
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;
}
Example #14
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;
}
Example #15
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
}
Example #16
0
File: main.c Project: 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;
}
Example #17
0
/* Adds the query to the list of queries for which we will build and cache a reply */
static int
cache_daap_query_add(struct cache_command *cmd)
{
#define Q_TMPL "INSERT OR REPLACE INTO queries (user_agent, query, msec, timestamp) VALUES ('%q', '%q', %d, %" PRIi64 ");"
#define Q_CLEANUP "DELETE FROM queries WHERE id NOT IN (SELECT id FROM queries ORDER BY timestamp DESC LIMIT 20);"
  char *query;
  char *errmsg;
  int ret;

  if (!cmd->arg.ua)
    {
      DPRINTF(E_LOG, L_CACHE, "Couldn't add slow query to cache, unknown user-agent\n");

      goto error_add;
    }

  // Currently we are only able to pre-build and cache these reply types
  if ( (strncmp(cmd->arg.query, "/databases/1/containers/", strlen("/databases/1/containers/")) != 0) &&
       (strncmp(cmd->arg.query, "/databases/1/groups?", strlen("/databases/1/groups?")) != 0) &&
       (strncmp(cmd->arg.query, "/databases/1/items?", strlen("/databases/1/items?")) != 0) &&
       (strncmp(cmd->arg.query, "/databases/1/browse/", strlen("/databases/1/browse/")) != 0) )
    goto error_add;

  remove_tag(cmd->arg.query, "session-id");
  remove_tag(cmd->arg.query, "revision-number");

  query = sqlite3_mprintf(Q_TMPL, cmd->arg.ua, cmd->arg.query, cmd->arg.msec, (int64_t)time(NULL));
  if (!query)
    {
      DPRINTF(E_LOG, L_CACHE, "Out of memory making query string.\n");

      goto error_add;
    }

  ret = sqlite3_exec(g_db_hdl, query, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_LOG, L_CACHE, "Error adding query to query list: %s\n", errmsg);

      sqlite3_free(query);
      sqlite3_free(errmsg);
      goto error_add;
    }

  sqlite3_free(query);

  DPRINTF(E_INFO, L_CACHE, "Slow query (%d ms) added to cache: '%s' (user-agent: '%s')\n", cmd->arg.msec, cmd->arg.query, cmd->arg.ua);

  free(cmd->arg.ua);
  free(cmd->arg.query);

  // Limits the size of the cache to only contain replies for 20 most recent queries
  ret = sqlite3_exec(g_db_hdl, Q_CLEANUP, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_LOG, L_CACHE, "Error cleaning up query list before update: %s\n", errmsg);
      sqlite3_free(errmsg);
      return -1;
    }

  cache_daap_trigger();

  return 0;

 error_add:
  if (cmd->arg.ua)
    free(cmd->arg.ua);

  if (cmd->arg.query)
    free(cmd->arg.query);

  return -1;
#undef Q_CLEANUP
#undef Q_TMPL
}
Example #18
0
void CurrentStatic::Update()
{
	char sql[256];
	sprintf(sql, "UPDATE CurrentStatic SET x=%.f, y=%.f, locationId=%d WHERE id=%d;", x, y, currentLocation->id, id);
	sqlite3_exec(Game::instance->db, sql, NULL, NULL, NULL);
}
Example #19
0
/*
** Attempt to read the database schema and initialize internal
** data structures for a single database file.  The index of the
** database file is given by iDb.  iDb==0 is used for the main
** database.  iDb==1 should never be used.  iDb>=2 is used for
** auxiliary databases.  Return one of the SQLITE_ error codes to
** indicate success or failure.
*/
static int sqlite3InitOne(sqlite3 *db, int iDb, char **pzErrMsg){
  int rc;
  BtCursor *curMain;
  int size;
  Table *pTab;
  char const *azArg[5];
  char zDbNum[30];
  int meta[10];
  InitData initData;
  char const *zMasterSchema;
  char const *zMasterName = SCHEMA_TABLE(iDb);

  /*
  ** The master database table has a structure like this
  */
  static const char master_schema[] = 
     "CREATE TABLE sqlite_master(\n"
     "  type text,\n"
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;
#ifndef SQLITE_OMIT_TEMPDB
  static const char temp_master_schema[] = 
     "CREATE TEMP TABLE sqlite_temp_master(\n"
     "  type text,\n"
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;
#else
  #define temp_master_schema 0
#endif

  assert( iDb>=0 && iDb<db->nDb );

  /* zMasterSchema and zInitScript are set to point at the master schema
  ** and initialisation script appropriate for the database being
  ** initialised. zMasterName is the name of the master table.
  */
  if( !OMIT_TEMPDB && iDb==1 ){
    zMasterSchema = temp_master_schema;
  }else{
    zMasterSchema = master_schema;
  }
  zMasterName = SCHEMA_TABLE(iDb);

  /* Construct the schema tables.  */
  sqlite3SafetyOff(db);
  azArg[0] = zMasterName;
  azArg[1] = "1";
  azArg[2] = zMasterSchema;
  sprintf(zDbNum, "%d", iDb);
  azArg[3] = zDbNum;
  azArg[4] = 0;
  initData.db = db;
  initData.pzErrMsg = pzErrMsg;
  rc = sqlite3InitCallback(&initData, 4, (char **)azArg, 0);
  if( rc!=SQLITE_OK ){
    sqlite3SafetyOn(db);
    return rc;
  }
  pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);
  if( pTab ){
    pTab->readOnly = 1;
  }
  sqlite3SafetyOn(db);

  /* Create a cursor to hold the database open
  */
  if( db->aDb[iDb].pBt==0 ){
    if( !OMIT_TEMPDB && iDb==1 ) DbSetProperty(db, 1, DB_SchemaLoaded);
    return SQLITE_OK;
  }
  rc = sqlite3BtreeCursor(db->aDb[iDb].pBt, MASTER_ROOT, 0, 0, 0, &curMain);
  if( rc!=SQLITE_OK && rc!=SQLITE_EMPTY ){
    sqlite3SetString(pzErrMsg, sqlite3ErrStr(rc), (char*)0);
    return rc;
  }

  /* Get the database meta information.
  **
  ** Meta values are as follows:
  **    meta[0]   Schema cookie.  Changes with each schema change.
  **    meta[1]   File format of schema layer.
  **    meta[2]   Size of the page cache.
  **    meta[3]   Use freelist if 0.  Autovacuum if greater than zero.
  **    meta[4]   Db text encoding. 1:UTF-8 3:UTF-16 LE 4:UTF-16 BE
  **    meta[5]   The user cookie. Used by the application.
  **    meta[6]   
  **    meta[7]
  **    meta[8]
  **    meta[9]
  **
  ** Note: The hash defined SQLITE_UTF* symbols in sqliteInt.h correspond to
  ** the possible values of meta[4].
  */
  if( rc==SQLITE_OK ){
    int i;
    for(i=0; rc==SQLITE_OK && i<sizeof(meta)/sizeof(meta[0]); i++){
      rc = sqlite3BtreeGetMeta(db->aDb[iDb].pBt, i+1, (u32 *)&meta[i]);
    }
    if( rc ){
      sqlite3SetString(pzErrMsg, sqlite3ErrStr(rc), (char*)0);
      sqlite3BtreeCloseCursor(curMain);
      return rc;
    }
  }else{
    memset(meta, 0, sizeof(meta));
  }
  db->aDb[iDb].schema_cookie = meta[0];

  /* If opening a non-empty database, check the text encoding. For the
  ** main database, set sqlite3.enc to the encoding of the main database.
  ** For an attached db, it is an error if the encoding is not the same
  ** as sqlite3.enc.
  */
  if( meta[4] ){  /* text encoding */
    if( iDb==0 ){
      /* If opening the main database, set db->enc. */
      db->enc = (u8)meta[4];
      db->pDfltColl = sqlite3FindCollSeq(db, db->enc, "BINARY", 6, 0);
    }else{
      /* If opening an attached database, the encoding much match db->enc */
      if( meta[4]!=db->enc ){
        sqlite3BtreeCloseCursor(curMain);
        sqlite3SetString(pzErrMsg, "attached databases must use the same"
            " text encoding as main database", (char*)0);
        return SQLITE_ERROR;
      }
    }
  }

  size = meta[2];
  if( size==0 ){ size = MAX_PAGES; }
  db->aDb[iDb].cache_size = size;

  if( iDb==0 ){
    db->file_format = meta[1];
    if( db->file_format==0 ){
      /* This happens if the database was initially empty */
      db->file_format = 1;
    }

    if( db->file_format==2 || db->file_format==3 ){
      /* File format 2 is treated exactly as file format 1. New 
      ** databases are created with file format 1.
      */ 
      db->file_format = 1;
    }
  }

  /*
  ** file_format==1    Version 3.0.0.
  ** file_format==2    Version 3.1.3.
  ** file_format==3    Version 3.1.4.
  **
  ** Version 3.0 can only use files with file_format==1. Version 3.1.3
  ** can read and write files with file_format==1 or file_format==2.
  ** Version 3.1.4 can read and write file formats 1, 2 and 3.
  */
  if( meta[1]>3 ){
    sqlite3BtreeCloseCursor(curMain);
    sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0);
    return SQLITE_ERROR;
  }

  sqlite3BtreeSetCacheSize(db->aDb[iDb].pBt, db->aDb[iDb].cache_size);

  /* Read the schema information out of the schema tables
  */
  assert( db->init.busy );
  if( rc==SQLITE_EMPTY ){
    /* For an empty database, there is nothing to read */
    rc = SQLITE_OK;
  }else{
    char *zSql;
    zSql = sqlite3MPrintf(
        "SELECT name, rootpage, sql, '%s' FROM '%q'.%s",
        zDbNum, db->aDb[iDb].zName, zMasterName);
    sqlite3SafetyOff(db);
    rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0);
    sqlite3SafetyOn(db);
    sqliteFree(zSql);
    sqlite3BtreeCloseCursor(curMain);
  }
  if( sqlite3_malloc_failed ){
    sqlite3SetString(pzErrMsg, "out of memory", (char*)0);
    rc = SQLITE_NOMEM;
    sqlite3ResetInternalSchema(db, 0);
  }
  if( rc==SQLITE_OK ){
    DbSetProperty(db, iDb, DB_SchemaLoaded);
  }else{
    sqlite3ResetInternalSchema(db, iDb);
  }
  return rc;
}
Example #20
0
int main(int argc, char** args)
{
    // Create an int variable for storing the return code for each call
    int retval;
    
    // The number of queries to be handled,size of each query and pointer
    int q_cnt = 5,q_size = 150,ind = 0;
    char **queries = malloc(sizeof(char) * q_cnt * q_size);
        
    // A prepered statement for fetching tables
    sqlite3_stmt *stmt;
    
    // Create a handle for database connection, create a pointer to sqlite3
    sqlite3 *handle;
    
    // try to create the database. If it doesnt exist, it would be created
    // pass a pointer to the pointer to sqlite3, in short sqlite3**
    retval = sqlite3_open("sampledb.sqlite3",&handle);
    // If connection failed, handle returns NULL
    if(retval)
    {
        printf("Database connection failed\n");
        return -1;
    }
    printf("Connection successful\n");
    
    // Create the SQL query for creating a table
    char create_table[100] = "CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)";
    
    // Execute the query for creating the table
    retval = sqlite3_exec(handle,create_table,0,0,0);
    
    // Insert first row and second row
    queries[ind++] = "INSERT INTO users VALUES('manish','mani',1)";
    retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
    queries[ind++] = "INSERT INTO users VALUES('mehul','pulsar',0)";
    retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
    
    // select those rows from the table
    queries[ind++] = "SELECT * from users";
    retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
    if(retval)
    {
        printf("Selecting data from DB Failed\n");
        return -1;
    }
    
    // Read the number of rows fetched
    int cols = sqlite3_column_count(stmt);
        
    while(1)
    {
        // fetch a row's status
        retval = sqlite3_step(stmt);
        
        if(retval == SQLITE_ROW)
        {
            // SQLITE_ROW means fetched a row
            
            // sqlite3_column_text returns a const void* , typecast it to const char*
            for(int col=0 ; col<cols;col++)
            {
                const char *val = (const char*)sqlite3_column_text(stmt,col);
                printf("%s = %s\t",sqlite3_column_name(stmt,col),val);
            }
            printf("\n");
        }
        else if(retval == SQLITE_DONE)
        {
            // All rows finished
            printf("All rows fetched\n");
            break;
        }
        else
        {
            // Some error encountered
            printf("Some error encountered\n");
            return -1;
        }
    }
    
    // Close the handle to free memory
    sqlite3_close(handle);
    return 0;
}
int main(int argc, char **argv) {
  sqlite3 *db;
  int rc;
  int cache_sz = -1;
  int page_sz = -1;
  char flag;
  char *inf, *outf, *key;
  char *sql;
  
  while ((flag = getopt(argc, argv, "i:o:k:c:p:")) != -1) {
    switch(flag) {
      case 'i':
        if((inf = (char *)calloc((size_t) strlen(optarg) + 1, sizeof(char))) == NULL) {
           ERROR(("no memory"));
           exit(1);
        }
        strcpy(inf, optarg);
        break;
      case 'o':
        if((outf = (char *)calloc((size_t) strlen(optarg) + 1, sizeof(char))) == NULL) {
           ERROR(("no memory"));
           exit(1);
        }
        strcpy(outf, optarg);
        break;
      case 'k':
        if((key = (char *) calloc((size_t) strlen(optarg) + 1, sizeof(char))) == NULL) {
           ERROR(("no memory"));
           exit(1);
        }
        strcpy(key, optarg);
        break;
      case 'c':
        cache_sz = atoi(optarg);
        break;
      case 'p':
        page_sz = atoi(optarg);
        break;
      case '?':
      default:
        break; 
    }
  }
  argc -= optind;
  argv += optind;

  if(inf == NULL || outf == NULL || key == NULL) {
    ERROR(("usage: exportencrypt -i <input file> -o <output file> -k <key> [-c cache_size] [-p page_size]\n"));
    exit(1);
  }

  if ((rc = sqlite3_open(inf, &db)) != SQLITE_OK) {
     ERROR(("sqlite3_open failed for %s: %d, %s\n", inf, rc, sqlite3_errmsg(db)))
  }

  if(cache_sz != -1) {
    sql = sqlite3_mprintf("PRAGMA cache_size = %d;", cache_sz); 
    rc = (sql == NULL) ? SQLITE_NOMEM : sqlite3_exec(db, sql, NULL, 0, NULL); 
    INFO(("%s\n", sql));
    if( rc!=SQLITE_OK ) goto end_of_export;
    sqlite3_free(sql);
  }

  sql = sqlite3_mprintf("ATTACH DATABASE %Q AS enc KEY %Q;", outf, key); 
  rc = (sql == NULL) ? SQLITE_NOMEM : sqlite3_exec(db, sql, NULL, 0, NULL); 
  INFO(("%s\n", sql));
  if( rc!=SQLITE_OK ) goto end_of_export;
  sqlite3_free(sql);

  if(page_sz != -1) {
    sql = sqlite3_mprintf("PRAGMA enc.cipher_page_size = %d;", page_sz); 
    rc = (sql == NULL) ? SQLITE_NOMEM : sqlite3_exec(db, sql, NULL, 0, NULL); 
    INFO(("%s\n", sql));
    if( rc!=SQLITE_OK ) goto end_of_export;
    sqlite3_free(sql);
  }
  
  sql = "SELECT sqlcipher_export('enc');";
  rc = sqlite3_exec(db, sql, NULL, 0, NULL); 
  INFO(("%s\n", sql));
  if( rc!=SQLITE_OK ) goto end_of_export;

  sql = "SELECT sqlcipher_export('enc');";
  rc = sqlite3_exec(db, sql, NULL, 0, NULL); 
  INFO(("%s\n", sql));
  if( rc!=SQLITE_OK ) goto end_of_export;

  sql = "DETACH DATABASE enc;";
  rc = sqlite3_exec(db, sql, NULL, 0, NULL); 
  INFO(("%s\n", sql));
  if( rc!=SQLITE_OK ) goto end_of_export;

  sql = NULL;

end_of_export:

  if(rc != SQLITE_OK) {
    ERROR(("error %d: %s\n", rc, sqlite3_errmsg(db)))
  }
  sqlite3_close(db);
  exit(0);
}
Example #22
0
void batch_add_e3ds(char *file_name){

    /** public function - see header */

    FILE* file;

    if((file=fopen(file_name, "r"))==NULL){

        log_event(EVENT_ERROR, "file [%s] not found", file_name);
        stop_server();
    }

    char line[160]="";
    int line_counter=0;

    log_event(EVENT_INITIALISATION, "\nAdding e3ds specified in file [%s]", file_name);
    fprintf(stderr, "\nAdding e3ds specified in file [%s]\n", file_name);

    //check database is open and table exists
    check_db_open(GET_CALL_INFO);
    check_table_exists("E3D_TABLE", GET_CALL_INFO);

    sqlite3_stmt *stmt;
    char *sErrMsg = 0;

    char *sql="INSERT INTO E3D_TABLE("  \
    "E3D_ID," \
    "E3D_FILENAME,"  \
    "OBJECT_ID" \
    ") VALUES(?, ?, ?)";

    prepare_query(sql, &stmt, GET_CALL_INFO);

    int rc=sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
    if(rc!=SQLITE_OK){

        log_event(EVENT_ERROR, "sqlite3_exec failed", GET_CALL_INFO);
        log_text(EVENT_ERROR, "return code [%i] message [%s] sql [%s]", rc, *&sErrMsg, sql);
    }

    while (fgets(line, sizeof(line), file)) {

        line_counter++;

        sscanf(line, "%*s");

        char output[5][MAX_LST_LINE_LEN];
        memset(&output, 0, sizeof(output));
        parse_line(line, output);

        sqlite3_bind_int(stmt, 1, atoi(output[0]));                 //e3d id
        sqlite3_bind_text(stmt, 2, output[1], -1, SQLITE_STATIC);   //e3d file name
        sqlite3_bind_int(stmt, 3, atoi(output[2]));                 //object id

        step_query(sql, &stmt, GET_CALL_INFO);

        sqlite3_clear_bindings(stmt);
        sqlite3_reset(stmt);

        fprintf(stderr, "e3d [%s] added successfully\n", output[1]);
        log_event(EVENT_SESSION, "Added e3d [%s] to E3D_TABLE", output[1]);
    }

    rc=sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
    if (rc!=SQLITE_OK) {

        log_event(EVENT_ERROR, "sqlite3_exec failed", GET_CALL_INFO);
        log_text(EVENT_ERROR, "return code [%i] message [%s] sql [%s]", rc, *sErrMsg, sql);
    }

    destroy_query(sql, &stmt, GET_CALL_INFO);

    fclose(file);

    //load data so it can be used by other functions
    load_db_e3ds();

    e3ds.data_loaded=true;
}
Example #23
0
void SQLI_Unlock(struct BE_descs *bed)
{
  if (bed->p->connected) sqlite3_exec(bed->p->desc, unlock_clause, NULL, NULL, NULL);
  if (bed->b->connected) sqlite3_exec(bed->b->desc, unlock_clause, NULL, NULL, NULL);
}
Example #24
0
DBOP *
dbop3_open(const char *path, int mode, int perm, int flags) {
	int rc, rw = 0;
	char *errmsg = 0;
	DBOP *dbop;
	sqlite3 *db3;
	const char *tblname;
	int cache_size = 0;
	STRBUF *sql = strbuf_open_tempbuf();
	char buf[1024];

	/*
	 * When the path is NULL string and private, temporary file is used.
	 * The database will be removed when the session is closed.
	 */
	if (path == NULL) {
		path = "";
		tblname = "temp";
	} else {
		/*
		 * In case of creation.
		 */
		if (mode == 1)
			(void)truncate(path, 0);
		tblname = "db";
	}
	/*
	 * setup arguments.
	 */
	switch (mode) {
	case 0:
		rw = SQLITE_OPEN_READONLY;
		break;
	case 1:
		rw = SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE;
		break;
	case 2:
		rw = SQLITE_OPEN_READWRITE;
		break;
	default:
		assert(0);
	}
	/*
	 * When the forth argument is NULL, sqlite3_vfs is used.
	 */
	rc = sqlite3_open_v2(path, &db3, rw, NULL);
	if (rc != SQLITE_OK)
		die("sqlite3_open_v2 failed. (rc = %d)", rc);
	dbop = (DBOP *)check_calloc(sizeof(DBOP), 1);
	strlimcpy(dbop->dbname, path, sizeof(dbop->dbname));
	dbop->sb        = strbuf_open(0);
	dbop->db3       = db3;
	dbop->openflags	= flags;
	dbop->perm	= (mode == 1) ? perm : 0;
	dbop->mode      = mode;
	dbop->lastdat	= NULL;
	dbop->lastflag	= NULL;
	dbop->lastsize	= 0;
	dbop->sortout	= NULL;
	dbop->sortin	= NULL;
	dbop->stmt      = NULL;
	dbop->tblname   = check_strdup(tblname);
	/*
	 * Maximum file size is DBOP_PAGESIZE * 2147483646.
	 * if DBOP_PAGESIZE == 8192 then maximum file size is 17592186028032 (17T).
	 */
	snprintf(buf, sizeof(buf), "pragma page_size=%d", DBOP_PAGESIZE);
	rc = sqlite3_exec(dbop->db3, buf,  NULL, NULL, &errmsg);
	if (rc != SQLITE_OK)
		die("pragma page_size error: %s", errmsg);
	/*
	 * create table (GTAGS, GRTAGS, GSYMS, GPATH).
	 */
	if (mode == 1) {
		/* drop table */
		strbuf_clear(sql);
		strbuf_puts(sql, "drop table ");
		strbuf_puts(sql, dbop->tblname);
		rc = sqlite3_exec(dbop->db3, strbuf_value(sql), NULL, NULL, &errmsg);
        	if (rc != SQLITE_OK) {
			/* ignore */
		}
		/* create table */
		strbuf_clear(sql);
		strbuf_puts(sql, "create table ");
		strbuf_puts(sql, dbop->tblname);
		strbuf_puts(sql, " (key text, dat text, extra text");
		if (!(flags & DBOP_DUP))
			strbuf_puts(sql, ", primary key(key)");
		strbuf_putc(sql, ')');
		rc = sqlite3_exec(dbop->db3, strbuf_value(sql), NULL, NULL, &errmsg);
        	if (rc != SQLITE_OK)
			die("create table error: %s", errmsg);
	}
	/*
	rc = sqlite3_exec(dbop->db3, "pragma synchronous=off", NULL, NULL, &errmsg);
       	if (rc != SQLITE_OK)
		die("pragma synchronous=off error: %s", errmsg);
	*/
	/*
         * Decide cache size.
         * See libutil/gparam.h for the details.
         */
	cache_size = GTAGSCACHE;
	if (getenv("GTAGSCACHE") != NULL)
		cache_size = atoi(getenv("GTAGSCACHE"));
	if (cache_size < GTAGSMINCACHE)
		cache_size = GTAGSMINCACHE;
	cache_size = (cache_size + DBOP_PAGESIZE - 1) / DBOP_PAGESIZE;
	snprintf(buf, sizeof(buf), "pragma cache_size=%d", cache_size);
	rc = sqlite3_exec(dbop->db3, buf,  NULL, NULL, &errmsg);
       	if (rc != SQLITE_OK)
		die("pragma cache_size error: %s", errmsg);
	sqlite3_exec(dbop->db3, "pragma journal_mode=memory", NULL, NULL, &errmsg);
       	if (rc != SQLITE_OK)
		die("pragma journal_mode=memory error: %s", errmsg);
	sqlite3_exec(dbop->db3, "pragma synchronous=off", NULL, NULL, &errmsg);
       	if (rc != SQLITE_OK)
		die("pragma synchronous=off error: %s", errmsg);
	rc = sqlite3_exec(dbop->db3, "begin transaction", NULL, NULL, &errmsg);
       	if (rc != SQLITE_OK)
		die("begin transaction error: %s", errmsg);
	strbuf_release_tempbuf(sql);
	return dbop;
}
int main (int argc, char *argv[])
{
    sqlite3 *db_handle = NULL;
    char *sql_statement;
    int ret;
    char *err_msg = NULL;
    int i;
    char **results;
    int rows;
    int columns;

    spatialite_init (0);

    ret = sqlite3_open_v2 (":memory:", &db_handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "cannot open in-memory db: %s\n", sqlite3_errmsg (db_handle));
	sqlite3_close (db_handle);
	db_handle = NULL;
	return -1;
    }
    
    ret = sqlite3_exec (db_handle, "create VIRTUAL TABLE dbftest USING VirtualDBF(\"shp/merano-3d/roads.dbf\", 'CP1252');", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "VirtualDBF error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -2;
    }
    
    ret = sqlite3_exec (db_handle, "DROP TABLE dbftest;", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "DROP TABLE error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -3;
    }

    ret = sqlite3_exec (db_handle, "create VIRTUAL TABLE dbftest USING VirtualDBF('shp/merano-3d/roads.dbf', \"CP1252\");", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "VirtualDBF error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -4;
    }
    
    ret = sqlite3_exec (db_handle, "DROP TABLE dbftest;", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "DROP TABLE error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -5;
    }

    ret = sqlite3_exec (db_handle, "create VIRTUAL TABLE dbftest USING VirtualDBF('shp/merano-3d/roads.dbf', CP1252);", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "VirtualDBF error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -6;
    }
    
    for (i = 0; steps[i].sql; ++i) {
	ret = sqlite3_get_table (db_handle, steps[i].sql, &results, &rows, &columns, &err_msg);
	if (ret != SQLITE_OK) {
	    fprintf (stderr, "Error: %s\n", err_msg);
	    sqlite3_free (err_msg);
	    return -7;
	}
	if (rows != steps[i].num_rows) {
	    fprintf (stderr, "Unexpected num of rows for test %i: %i.\n", i, rows);
	    return  -8;
	}
	sqlite3_free_table (results);
    }

    ret = sqlite3_exec (db_handle, "DROP TABLE dbftest;", NULL, NULL, &err_msg);
    if (ret != SQLITE_OK) {
	fprintf (stderr, "DROP TABLE error: %s\n", err_msg);
	sqlite3_free (err_msg);
	return -9;
    }

    sqlite3_close (db_handle);
    spatialite_cleanup();
    sqlite3_reset_auto_extension();
    
    return 0;
}
Example #26
0
static int
cache_create_tables(void)
{
#define T_REPLIES						\
  "CREATE TABLE IF NOT EXISTS replies ("			\
  "   id                 INTEGER PRIMARY KEY NOT NULL,"		\
  "   query              VARCHAR(4096) NOT NULL,"		\
  "   reply              BLOB"					\
  ");"
#define T_QUERIES						\
  "CREATE TABLE IF NOT EXISTS queries ("			\
  "   id                 INTEGER PRIMARY KEY NOT NULL,"		\
  "   query              VARCHAR(4096) UNIQUE NOT NULL,"	\
  "   user_agent         VARCHAR(1024),"			\
  "   msec               INTEGER DEFAULT 0,"			\
  "   timestamp          INTEGER DEFAULT 0"			\
  ");"
#define I_QUERY							\
  "CREATE INDEX IF NOT EXISTS idx_query ON replies (query);"
#define T_ARTWORK					\
  "CREATE TABLE IF NOT EXISTS artwork ("		\
  "   id                  INTEGER PRIMARY KEY NOT NULL,"\
  "   persistentid        INTEGER NOT NULL,"		\
  "   max_w               INTEGER NOT NULL,"		\
  "   max_h               INTEGER NOT NULL,"		\
  "   format              INTEGER NOT NULL,"		\
  "   filepath            VARCHAR(4096) NOT NULL,"	\
  "   db_timestamp        INTEGER DEFAULT 0,"		\
  "   data                BLOB"				\
  ");"
#define I_ARTWORK_ID				\
  "CREATE INDEX IF NOT EXISTS idx_persistentidwh ON artwork(persistentid, max_w, max_h);"
#define I_ARTWORK_PATH				\
  "CREATE INDEX IF NOT EXISTS idx_pathtime ON artwork(filepath, db_timestamp);"
#define T_ADMIN_CACHE	\
  "CREATE TABLE IF NOT EXISTS admin_cache("	\
  " key VARCHAR(32) PRIMARY KEY NOT NULL,"	\
  " value VARCHAR(32) NOT NULL"	\
  ");"
#define Q_CACHE_VERSION	\
  "INSERT INTO admin_cache (key, value) VALUES ('cache_version', '%d');"

  char *query;
  char *errmsg;
  int ret;


  // Create reply cache table
  ret = sqlite3_exec(g_db_hdl, T_REPLIES, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating reply cache table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Create query table (the queries for which we will generate and cache replies)
  ret = sqlite3_exec(g_db_hdl, T_QUERIES, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating query table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Create index
  ret = sqlite3_exec(g_db_hdl, I_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;
    }

  // Create artwork table
  ret = sqlite3_exec(g_db_hdl, T_ARTWORK, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating artwork table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Create index
  ret = sqlite3_exec(g_db_hdl, I_ARTWORK_ID, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating artwork index: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }
  ret = sqlite3_exec(g_db_hdl, I_ARTWORK_PATH, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating artwork index: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Create admin cache table
  ret = sqlite3_exec(g_db_hdl, T_ADMIN_CACHE, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating admin cache table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }
  query = sqlite3_mprintf(Q_CACHE_VERSION, CACHE_VERSION);
  ret = sqlite3_exec(g_db_hdl, query, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error creating admin cache table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  sqlite3_free(query);

  DPRINTF(E_DBG, L_CACHE, "Cache tables created\n");

  return 0;
#undef T_REPLIES
#undef T_QUERIES
#undef I_QUERY
#undef T_ARTWORK
#undef I_ARTWORK_ID
#undef I_ARTWORK_PATH
#undef T_ADMIN_CACHE
#undef Q_CACHE_VERSION
}
/* set locale in the android_metadata table, install localized collators, and rebuild indexes */
static void native_setLocale(JNIEnv* env, jobject object, jstring localeString, jint flags)
{
    if ((flags & NO_LOCALIZED_COLLATORS)) return;

    int err;
    char const* locale8 = env->GetStringUTFChars(localeString, NULL);
    sqlite3 * handle = (sqlite3 *)env->GetIntField(object, offset_db_handle);
    sqlite3_stmt* stmt = NULL;
    char** meta = NULL;
    int rowCount, colCount;
    char* dbLocale = NULL;

    // create the table, if necessary and possible
    if (!(flags & OPEN_READONLY)) {
        static const char *createSql ="CREATE TABLE IF NOT EXISTS " ANDROID_TABLE " (locale TEXT)";
        err = sqlite3_exec(handle, createSql, NULL, NULL, NULL);
        if (err != SQLITE_OK) {
            LOGE("CREATE TABLE " ANDROID_TABLE " failed\n");
            throw_sqlite3_exception(env, handle);
            goto done;
        }
    }

    // try to read from the table
    static const char *selectSql = "SELECT locale FROM " ANDROID_TABLE " LIMIT 1";
    err = sqlite3_get_table(handle, selectSql, &meta, &rowCount, &colCount, NULL);
    if (err != SQLITE_OK) {
        LOGE("SELECT locale FROM " ANDROID_TABLE " failed\n");
        throw_sqlite3_exception(env, handle);
        goto done;
    }

    dbLocale = (rowCount >= 1) ? meta[colCount] : NULL;

    if (dbLocale != NULL && !strcmp(dbLocale, locale8)) {
        // database locale is the same as the desired locale; set up the collators and go
        err = register_localized_collators(handle, locale8, UTF16_STORAGE);
        if (err != SQLITE_OK) throw_sqlite3_exception(env, handle);
        goto done;   // no database changes needed
    }

    if ((flags & OPEN_READONLY)) {
        // read-only database, so we're going to have to put up with whatever we got
        // For registering new index. Not for modifing the read-only database.
        err = register_localized_collators(handle, locale8, UTF16_STORAGE);
        if (err != SQLITE_OK) throw_sqlite3_exception(env, handle);
        goto done;
    }

    // need to update android_metadata and indexes atomically, so use a transaction...
    err = sqlite3_exec(handle, "BEGIN TRANSACTION", NULL, NULL, NULL);
    if (err != SQLITE_OK) {
        LOGE("BEGIN TRANSACTION failed setting locale\n");
        throw_sqlite3_exception(env, handle);
        goto done;
    }

    err = register_localized_collators(handle, locale8, UTF16_STORAGE);
    if (err != SQLITE_OK) {
        LOGE("register_localized_collators() failed setting locale\n");
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    err = sqlite3_exec(handle, "DELETE FROM " ANDROID_TABLE, NULL, NULL, NULL);
    if (err != SQLITE_OK) {
        LOGE("DELETE failed setting locale\n");
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    static const char *sql = "INSERT INTO " ANDROID_TABLE " (locale) VALUES(?);";
    err = sqlite3_prepare_v2(handle, sql, -1, &stmt, NULL);
    if (err != SQLITE_OK) {
        LOGE("sqlite3_prepare_v2(\"%s\") failed\n", sql);
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    err = sqlite3_bind_text(stmt, 1, locale8, -1, SQLITE_TRANSIENT);
    if (err != SQLITE_OK) {
        LOGE("sqlite3_bind_text() failed setting locale\n");
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    err = sqlite3_step(stmt);
    if (err != SQLITE_OK && err != SQLITE_DONE) {
        LOGE("sqlite3_step(\"%s\") failed setting locale\n", sql);
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    err = sqlite3_exec(handle, "REINDEX LOCALIZED", NULL, NULL, NULL);
    if (err != SQLITE_OK) {
        LOGE("REINDEX LOCALIZED failed\n");
        throw_sqlite3_exception(env, handle);
        goto rollback;
    }

    // all done, yay!
    err = sqlite3_exec(handle, "COMMIT TRANSACTION", NULL, NULL, NULL);
    if (err != SQLITE_OK) {
        LOGE("COMMIT TRANSACTION failed setting locale\n");
        throw_sqlite3_exception(env, handle);
        goto done;
    }

rollback:
    if (err != SQLITE_OK) {
        sqlite3_exec(handle, "ROLLBACK TRANSACTION", NULL, NULL, NULL);
    }

done:
    if (locale8 != NULL) env->ReleaseStringUTFChars(localeString, locale8);
    if (stmt != NULL) sqlite3_finalize(stmt);
    if (meta != NULL) sqlite3_free_table(meta);
}
Example #28
0
static int
cache_drop_tables(void)
{
#define D_REPLIES	"DROP TABLE IF EXISTS replies;"
#define D_QUERIES	"DROP TABLE IF EXISTS queries;"
#define D_QUERY		"DROP INDEX IF EXISTS idx_query;"
#define D_ARTWORK	"DROP TABLE IF EXISTS artwork;"
#define D_ARTWORK_ID	"DROP INDEX IF EXISTS idx_persistentidwh;"
#define D_ARTWORK_PATH	"DROP INDEX IF EXISTS idx_pathtime;"
#define D_ADMIN_CACHE	"DROP TABLE IF EXISTS admin_cache;"
#define Q_VACUUM	"VACUUM;"

  char *errmsg;
  int ret;


  // Drop reply cache table
  ret = sqlite3_exec(g_db_hdl, D_REPLIES, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping reply cache table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Drop query table
  ret = sqlite3_exec(g_db_hdl, D_QUERIES, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping query table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Drop index
  ret = sqlite3_exec(g_db_hdl, D_QUERY, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping query index: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Drop artwork table
  ret = sqlite3_exec(g_db_hdl, D_ARTWORK, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping artwork table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Drop index
  ret = sqlite3_exec(g_db_hdl, D_ARTWORK_ID, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping artwork index: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }
  ret = sqlite3_exec(g_db_hdl, D_ARTWORK_PATH, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping artwork index: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Drop admin cache table
  ret = sqlite3_exec(g_db_hdl, D_ADMIN_CACHE, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error dropping admin cache table: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  // Vacuum
  ret = sqlite3_exec(g_db_hdl, Q_VACUUM, NULL, NULL, &errmsg);
  if (ret != SQLITE_OK)
    {
      DPRINTF(E_FATAL, L_CACHE, "Error vacuum cache database: %s\n", errmsg);

      sqlite3_free(errmsg);
      sqlite3_close(g_db_hdl);
      return -1;
    }

  DPRINTF(E_DBG, L_CACHE, "Cache tables dropped\n");

  return 0;
#undef D_REPLIES
#undef D_QUERIES
#undef D_QUERY
#undef D_ARTWORK
#undef D_ARTWORK_ID
#undef D_ARTWORK_PATH
#undef D_ADMIN_CACHE
#undef Q_VACUUM
}
Example #29
0
int main(int argc, char ** argv) {
	char * sep;
	sqlite3 * db = NULL;
	struct Package current = blank_package;
	char baseurl[256] = "";
	char line[sizeof(current.homepage)]; /* No line will be larger than the largest field */
	int code;
	int chained_call = 0;
	char * db_path = NULL;
	/* NOTE: If Package ever contains varible fields, this must be changed */
	char sql[sizeof(current) + 8*3*sizeof(char) + 137*sizeof(char) + 256*sizeof(char)];

	while((code = getopt(argc, argv, "schd:")) != -1) {
		switch(code) {
			case 's':
				if(db != NULL) {
					fputs("-d and -s are mutually exclusive\n", stderr);
					exit(EXIT_FAILURE);
				}
				print_sql = 1;
				break;
			case 'c':
				chained_call = 1;
				break;
			case 'h':
				help();
				break;
			case 'd':
				if(print_sql) {
					fputs("-d and -s are mutually exclusive\n", stderr);
					exit(EXIT_FAILURE);
				}
				if(sqlite3_open(optarg, &db) != 0) {
					fprintf(stderr, "%s\n", sqlite3_errmsg(db));
					exit(EXIT_FAILURE);
				}
				break;
			default:
				help();
		}
	}

	/* Open database */
	if(!print_sql && db == NULL && (db_path = get_db_path()) && sqlite3_open(db_path, &db) != 0) {
		fprintf(stderr, "%s\n", sqlite3_errmsg(db));
		exit(EXIT_FAILURE);
	}
	if(db_path) {
		free(db_path);
	}

	/* Do everything as one transaction. Many times faster */
	safe_execute(db, "BEGIN TRANSACTION;");

	/* Create tables if they do not exist */
	safe_execute(db, "CREATE TABLE IF NOT EXISTS packages " \
	                 "(package TEXT PRIMARY KEY, name TEXT, version TEXT," \
	                 "maintainer TEXT, installed_size INTEGER, size INTEGER," \
	                 "homepage TEXT, section TEXT, category TEXT, baseurl TEXT,"\
	                 "path TEXT, md5 TEXT, description TEXT, user_rating INTEGER,"\
	                 "user_owns TEXT, status INTEGER, rating INTEGER, price INTEGER);" \
	                 "CREATE TABLE IF NOT EXISTS virtual_packages (package TEXT PRIMARY KEY, is_really TEXT);" \
	                 "CREATE TABLE IF NOT EXISTS depends (package TEXT, depend TEXT, version TEXT);"
	            );

	if(!chained_call) {
		safe_execute(db, "DELETE FROM virtual_packages;");
		safe_execute(db, "DELETE FROM depends;");
	}

	/* Loop over lines from stream */
	code = 0;
	while(fgets(line, sizeof(line), stdin)) {
		if(line[0] == '#') {
			/* Chomp */
			if((sep = strchr(line, '\n'))) {
				*sep = '\0';
			}
			strncpy(baseurl, line + 1, sizeof(baseurl)-1);
		/* Blank line means end of this package definition */
		} else if(line[0] == '\n') {
			current.baseurl = baseurl;
			if(current.package[0] != '\0') {
				package_insert_sql(&current, sql, sizeof(sql));

				if(print_sql) {
					puts(sql);
				} else {
					if((code = sqlite3_exec(db, sql, NULL, NULL, NULL)) != 0) {
						if(code == SQLITE_CONSTRAINT) {
							package_update_sql(&current, sql, sizeof(sql));
							safe_execute(db, sql);
						} else {
							fprintf(stderr, "%s\n", sqlite3_errmsg(db));
							exit(EXIT_FAILURE);
						}
					}
				}
			}

			/* Reset things */
			code = 0;
			current = blank_package;
		} else {
			/* Chomp */
			if((sep = strchr(line, '\n'))) {
				*sep = '\0';
			}
			/* Description spans multiple lines at the end, concat stuff */
			if(code) {
				strncat(current.description, "\n", sizeof(current.description)-1);
				strncat(current.description, line, sizeof(current.description)-1);
			} else {
				/* Split on colon */
				if((sep = strchr(line, ':'))) {
					*sep = '\0';
					/* Skip over the space too */
					sep = sep + 2;
					/* If we haven't seen the field yet, do a string compare to see if
					 * this is it. Copy remainder of line into struct */
					if(       current.package[0]      == '\0' && strcmp(line, "Package")        == 0) {
						strncpy(current.package,     sep, sizeof(current.package)-1);
					} else if(current.name[0]         == '\0' && strcmp(line, "Name")           == 0) {
						strncpy(current.name,        sep, sizeof(current.name)-1);
					} else if(current.category[0]     == '\0' && strcmp(line, "Category")       == 0) {
						strncpy(current.category,    sep, sizeof(current.category)-1);
					} else if(current.version[0]      == '\0' && strcmp(line, "Version")        == 0) {
						strncpy(current.version,     sep, sizeof(current.version)-1);
					} else if(current.user_owns[0]    == '\0' && strcmp(line, "UserOwns")       == 0) {
						strncpy(current.user_owns,   sep, sizeof(current.user_owns)-1);
					} else if(current.section[0]      == '\0' && strcmp(line, "Section")        == 0) {
						strncpy(current.section,     sep, sizeof(current.section)-1);
					} else if(current.md5[0]          == '\0' && strcmp(line, "MD5sum")         == 0) {
						strncpy(current.md5,         sep, sizeof(current.md5)-1);
					} else if(current.maintainer[0]   == '\0' && strcmp(line, "Maintainer")     == 0) {
						strncpy(current.maintainer,  sep, sizeof(current.maintainer)-1);
					} else if(current.path[0]         == '\0' && strcmp(line, "Filename")       == 0) {
						strncat(current.path, sep,     sizeof(current.path)-1);
					} else if(current.homepage        == '\0' && strcmp(line, "Homepage")       == 0) {
						strncpy(current.homepage,    sep, sizeof(current.homepage)-1);
					} else if(current.rating          ==   0  && strcmp(line, "Rating")         == 0) {
						current.rating = atoi(sep);
					} else if(current.user_rating     ==   0  && strcmp(line, "UserRating")     == 0) {
						current.user_rating = atoi(sep);
					} else if(current.price          ==   0  && strcmp(line, "Price")           == 0) {
						current.price = atoi(sep);
					} else if(current.installed_size  ==   0  && strcmp(line, "Installed-Size") == 0) {
						current.installed_size = atoi(sep);
					} else if(current.size            ==   0  && strcmp(line, "Size")           == 0) {
						current.size = atoi(sep);
					} else if(                                   strcmp(line, "Provides")       == 0) {
						sep = strtok(sep, ", ");
						sql[0] = '\0';
						strncpy(sql, "INSERT INTO virtual_packages (package, is_really) VALUES(", sizeof(sql));
						quotecat(sql, sep, sizeof(sql), 1);
						quotecat(sql, current.package, sizeof(sql), 0);
						strncat(sql, ");", sizeof(sql));
						safe_execute(db, sql);
						while((sep = strtok(NULL, ", ")) != NULL) {
							sql[0] = '\0';
							strncpy(sql, "INSERT INTO virtual_packages (package, is_really) VALUES(", sizeof(sql));
							quotecat(sql, sep, sizeof(sql), 1);
							quotecat(sql, current.package, sizeof(sql), 0);
							strncat(sql, ");", sizeof(sql));
							safe_execute(db, sql);
						}
					} else if(                                   strcmp(line, "Depends")        == 0) {
						parse_depends(db, current.package, sep);
					} else if(                                   strcmp(line, "Description")    == 0) {
						strncpy(current.description, sep, sizeof(current.description)-1);
						code = 1;
					}
				}
			} /* if code */
		} /* if line[0] == '\n' */
	} /* while */

	/* End the transaction only when all data has been inserted */
	safe_execute(db, "END TRANSACTION;");

	/* Clean up disk space */
	if(!chained_call) {
		safe_execute(db, "DELETE FROM packages WHERE package='';");
		safe_execute(db, "VACUUM;");
	}

	/* Close database */
	if(db != NULL && sqlite3_close(db) != 0) {
		fprintf(stderr, "%s\n", sqlite3_errmsg(db));
		exit(EXIT_FAILURE);
	}

	exit(EXIT_SUCCESS);
}
Example #30
0
gboolean gc_db_init(gboolean disable_database_)
{

#ifdef USE_SQLITE
  disable_database = disable_database_;
  SUPPORT_OR_RETURN(FALSE);
  gboolean creation = FALSE;
  char *zErrMsg;
  char **result;
  int rc;
  int nrow;
  int ncolumn;

  GcomprisProperties	*properties = gc_prop_get();

  if (!g_file_test(properties->database, G_FILE_TEST_EXISTS))
    creation = TRUE;

  rc = sqlite3_open(properties->database, &gcompris_db);
  if( rc ){
    g_message("Can't open database %s : %s\n", properties->database,
	      sqlite3_errmsg(gcompris_db));
    sqlite3_close(gcompris_db);
    disable_database = TRUE;
    return FALSE;
  }

  g_message("Database %s opened", properties->database);

  if (creation){
    _create_db();
  } else {
    if ( ! _check_db_integrity() ||
	 _gc_boards_count() == 0 )
      {
	// We failed to load the database, let's
	// backup it and re create it.
	sqlite3_close(gcompris_db);
	gchar *backup = g_strdup_printf("%s.broken", properties->database);
	if ( g_rename(properties->database, backup) < 0 )
	  {
	    // Obviously, we cannot write the backup file either
	    g_message("Failed to write the backup database %s", backup);
	    disable_database = TRUE;
	    return FALSE;
	  }
	else
	  g_message("Database is broken, it is copyed in %s", backup);
	g_free(backup);

	rc = sqlite3_open(properties->database, &gcompris_db);
	if( rc ){
	  g_message("Can't open database %s : %s\n", properties->database,
		    sqlite3_errmsg(gcompris_db));
	  sqlite3_close(gcompris_db);
	  disable_database = TRUE;
	  return FALSE;
	}
	_create_db();

	if ( ! _check_db_integrity() )
	  {
	    disable_database = TRUE;
	    return FALSE;
	  }
      }

    g_message("Database Integrity ok");

    rc = sqlite3_get_table(gcompris_db,
			   CHECK_VERSION,
			   &result,
			   &nrow,
			   &ncolumn,
			   &zErrMsg
			   );
    if( rc!=SQLITE_OK ){
      g_error("SQL error: %s\n", zErrMsg);
    }

    if (strcmp(result[1],VERSION)!=0)
      g_message("Running GCompris is %s, but database version is %s", VERSION, result[1]);
    sqlite3_free_table(result);

    /* Schema upgrade */
    rc = sqlite3_get_table(gcompris_db,
			   PRAGMA_SCHEMA_VERSION,
			   &result,
			   &nrow,
			   &ncolumn,
			   &zErrMsg
			   );
    if( rc!=SQLITE_OK ){
      g_error("SQL error: %s\n", zErrMsg);
    }

    int version = atoi(result[1]);
    sqlite3_free_table(result);
    if(version <= 16)
      {
	g_message("Upgrading from <16 schema version\n");
	rc = sqlite3_exec(gcompris_db,CREATE_TABLE_LOGS, NULL,  0, &zErrMsg);
	if( rc!=SQLITE_OK ) {
	  g_error("SQL error: %s\n", zErrMsg);
	}
      }
    if ( _get_user_version() == 0)
      {
	g_message("Upgrading schema based on user version = 0\n");
	rc = sqlite3_exec(gcompris_db,"DROP TABLE boards;", NULL,  0, &zErrMsg);
	if( rc!=SQLITE_OK ) {
	  g_error("SQL error: %s\n", zErrMsg);
	}
	rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS, NULL,  0, &zErrMsg);
	if( rc!=SQLITE_OK ) {
	  g_error("SQL error: %s\n", zErrMsg);
	}
	// We just dropped the boards table, force a reread
	properties->reread_menu = TRUE;
	_set_user_version(1);
      }
  }

  return TRUE;
#else
  return FALSE;
#endif
}