Sylvain, are you happy with the performance? Maybe you are using it differently but, from my tests, the DEFAULT clause is ignored for PRIMARY KEY columns. I had to use an ordinary column with UNIQUE constraint to test your extension. Below is a tester for 1 million rows which completes in about 186 seconds. The same million row test with PRIMARY KEY column (and ignored DEFAULT) completes in about 5 seconds.
----------------- sqlite> .load sqlite-ext/libseqvalue.so sqlite> DROP TABLE IF EXISTS sp_sequence;SELECT seq_init_inc("seqtest",1,2);DROP TABLE IF EXISTS seqtest;CREATE TABLE seqtest(rowid INT DEFAULT(seq_nextval('seqtest')) UNIQUE NOT NULL,payload INT); "seq_init_inc(""seqtest"",1,2)" 1 Run Time: real 0.394 user 0.168000 sys 0.044000 sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<10e6) INSERT INTO seqtest(payload) SELECT i from T; Run Time: real 190.775 user 186.208000 sys 0.616000 sqlite> select * from seqtest limit 10; rowid,payload 3,1 5,2 7,3 9,4 11,5 13,6 15,7 17,8 19,9 21,10 Run Time: real 0.001 user 0.000000 sys 0.000000 Your idea peaked my interest because I used H2 in the past. H2 has many features but is very slow compared to SQLite for large tables. There is also a learning curve to move from thinking in H2 to thinking in SQLite. For example, in this case, unless you can get a PRIMARY KEY column to work with your sequence function, INSERTs will be a bottleneck. FYI. below is a reference implementation using INSTEAD OF TRIGGER to compute next rowid for the same million row test. Compare 15 seconds in user time with 186 seconds using DEFAULT sequence function. sqlite> DROP TABLE IF EXISTS seqtest;CREATE TABLE seqtest(rowid INTEGER PRIMARY KEY,payload int);DROP TRIGGER IF EXISTS seqtest;CREATE VIEW seqtest_v AS SELECT * FROM seqtest;CREATE TRIGGER seqtest INSTEAD OF INSERT ON seqtest_v BEGIN INSERT INTO seqtest(rowid,payload) VALUES ((SELECT ifnull(max(rowid)+2,1) FROM seqtest),NEW.payload); END; Run Time: real 0.189 user 0.000000 sys 0.000000 sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<10e6) INSERT INTO seqtest_v(payload) SELECT i from T;Run Time: real 23.461 user 15.012000 sys 0.380000 sqlite> select * from seqtest limit 10; rowid,payload 1,1 3,2 5,3 7,4 9,5 11,6 13,7 15,8 17,9 19,10 Run Time: real 0.000 user 0.000000 sys 0.000000 On Thu, Aug 3, 2017 at 3:35 PM, Sylvain Pointeau <sylvain.point...@gmail.com > wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users