I programmed the currval using a temp table, but the performance dropped slightly
sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<1000000) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s eq1') from T; Run Time: real 25.837 user 23.446950 sys 0.171601 I create the temp table only once with the help of a global variable. I believe it is safe (per session), am I right? #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 int is_temp_table_created = 0; 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, " \ " SEQ_INIT INTEGER NOT NULL, " \ " SEQ_INC INTEGER NOT NULL CHECK (SEQ_INC<>0) " \ " )", 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_init, seq_inc) values (?, ?, ?, ?)", -1, &stmt, 0); sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC); sqlite3_bind_int64(stmt, 2, seq_init_val-seq_inc_val); sqlite3_bind_int64(stmt, 3, seq_init_val); sqlite3_bind_int64(stmt, 4, 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; int update_row_count = 0; sqlite3_stmt *stmt; long nextval = 0; sqlite3 *db = sqlite3_context_db_handle(context); 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) { nextval = 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; } if( is_temp_table_created == 0 ) { rc = sqlite3_exec(db, "CREATE TEMPORARY TABLE IF NOT EXISTS TEMP_SP_SEQ_CURRVAL ( " \ " SEQ_NAME TEXT PRIMARY KEY, " \ " CURRVAL INTEGER " \ " )", 0, 0, 0); if( rc != SQLITE_OK ) { sqlite3_result_error(context, sqlite3_errmsg(db), -1); return; } is_temp_table_created = 1; } sqlite3_prepare_v2(db, "update TEMP_SP_SEQ_CURRVAL set currval = ? where seq_name = ?", -1, &stmt, 0); sqlite3_bind_int64(stmt, 1, nextval); sqlite3_bind_text(stmt, 2, 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; } update_row_count = sqlite3_changes(db); if (update_row_count == 0) { // update not done, value has to be inserted sqlite3_prepare_v2(db, "insert into TEMP_SP_SEQ_CURRVAL (seq_name, currval) values (?,?)", -1, &stmt, 0); sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC); sqlite3_bind_int64(stmt, 2, nextval); rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if (rc != SQLITE_DONE) { sqlite3_result_error(context, sqlite3_errmsg(db), -1); return; } } sqlite3_result_int64( context, nextval ); } void sp_seq_currval(sqlite3_context *context, int argc, sqlite3_value **argv) { int rc = 0; sqlite3_stmt *stmt; long currval = 0; sqlite3 *db = sqlite3_context_db_handle(context); const unsigned char* seq_name = sqlite3_value_text(argv[0]); sqlite3_prepare_v2(db, "select currval from TEMP_SP_SEQ_CURRVAL where seq_name = ?", -1, &stmt, 0); sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC); rc = sqlite3_step(stmt); if( rc == SQLITE_ROW) { currval = sqlite3_column_int64(stmt, 0); } sqlite3_finalize(stmt); if (rc != SQLITE_ROW) { sqlite3_result_error(context, "currval is not yet defined in this session for this sequence", -1); return; } sqlite3_result_int64( context, currval ); } int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi ){ SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "seq_init", 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); sqlite3_create_function(db, "seq_currval", 1, SQLITE_UTF8, 0, sp_seq_currval, 0, 0); return 0; } On Fri, Aug 4, 2017 at 8:18 PM, Nico Williams <n...@cryptonector.com> wrote: > On Fri, Aug 04, 2017 at 08:04:38PM +0200, Sylvain Pointeau wrote: > > On Fri, Aug 4, 2017 at 6:24 PM, Nico Williams <n...@cryptonector.com> > wrote: > > > On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > > > > Now I would like to implement the seq_currval: > > > > we could implement it like (in pseudo code): > > > > var currvalues = map[string,number] > > > > function seq_nextval(seq_name) { > > update sequence set val = val + inc where name = seq_name > > nextval = select val from sequence where name = seq_name > > currvalues[seq_name] = nextval > > return nextval > > } > > > > function seq_currval(seq_name) { > > if currvalues[seq_name] is not set => raise error > > return currvalues[seq_name] > > } > > Sure, but the currvalues[] map has to be store... in the DB handle. You > could use a TEMP TABLE, but you'd have to make sure to DELETE all its > rows when a transaction ends. > > > however I noticed that because I am retrieving the params as int64: > > '10a' becomes 10, > > 'abc' becomes 0 > > null becomes 0 > > > > and I think that is ok, so the SEQ_INIT INTEGER NOT NULL can never be > null > > and the SEQ_INC can never be 0 due to the check > > Sure. > > Nico > -- > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users