Re: [sqlite] sequencer

[Sylvain Pointeau]

Hello,

please find below my implementation of a sequence, I am open for any critic!

Best regards,
Sylvain

-------------------------------------------------------------------

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv) {
  int rc = 0;
  sqlite3_stmt *stmt;
  sqlite3 *db = sqlite3_context_db_handle(context);

  const unsigned char* seq_name = sqlite3_value_text(argv[0]);
  long seq_init_val = sqlite3_value_int64(argv[1]);
  long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);

  rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
  " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
  " SEQ_VAL INTEGER NOT NULL, " \
  " SEQ_INC INTEGER NOT NULL " \
  " )", 0, 0, 0);

  if( rc != SQLITE_OK ) {
    sqlite3_result_error(context, sqlite3_errmsg(db), -1);
    return;
  }


  sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
seq_val, seq_inc) values (?, ?, ?)", -1, &stmt, 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
  sqlite3_bind_int64(stmt, 2, seq_init_val);
  sqlite3_bind_int64(stmt, 3, seq_inc_val);

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    sqlite3_result_error(context, sqlite3_errmsg(db), -1);
    return;
  }

  sqlite3_result_int64( context, seq_init_val );
}

void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int rc = 0;
  sqlite3_stmt *stmt;
  sqlite3 *db = sqlite3_context_db_handle(context);
  long seq_val = 0;

  const unsigned char* seq_name = sqlite3_value_text(argv[0]);

  sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
seq_inc where seq_name = ?", -1, &stmt, 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    sqlite3_result_error(context, sqlite3_errmsg(db), -1);
    return;
  }

  sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name =
?", -1, &stmt, 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);

  rc = sqlite3_step(stmt);

  if( rc == SQLITE_ROW) {
    seq_val = sqlite3_column_int64(stmt, 0);
  }

  sqlite3_finalize(stmt);

  if (rc != SQLITE_ROW) {
    if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
does not exist", -1);
    else sqlite3_result_error(context, sqlite3_errmsg(db), -1);
    return;
  }

  sqlite3_result_int64( context, seq_val );
}


int sqlite3_extension_init(
      sqlite3 *db,
      char **pzErrMsg,
      const sqlite3_api_routines *pApi
){
 SQLITE_EXTENSION_INIT2(pApi)
 sqlite3_create_function(db, "seq_init_inc", 3, SQLITE_UTF8, 0,
sp_seq_init, 0, 0);
 sqlite3_create_function(db, "seq_init", 2, SQLITE_UTF8, 0, sp_seq_init, 0,
0);
 sqlite3_create_function(db, "seq_nextval", 1, SQLITE_UTF8, 0,
sp_seq_nextval, 0, 0);
 return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users