Please find below the last source code, I removed the check on the table (NOT NULL on both sql_val and seq_inc)
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('seq1') from T; Run Time: real 18.829 user 16.146103 sys 0.140401 I find that the performance is not that bad now. Because of the performance (but I did not measure), I won't do the check on the parameters, but I did put the same name for the seq_init for 2 or 3 parameters. Now I would like to implement the seq_currval: Oracle raises an exception because seq_nextval must be called at least one time for the session H2 returns init minus seq (making nextval returning the init value) => this is also the trick I used so the update is simpler so more efficient do you agree that we must use a variable mapping sequenceName => last nextval should I use a very simple structure (fixed sized array and O(n) access)? or use a map? but then I would rather prefer to switch to C++, unless you have a better idea? #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 PRIMARY KEY, " \ " SEQ_VAL INTEGER, " \ " SEQ_INC INTEGER " \ " )", 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-seq_inc_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", 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