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 <[email protected]
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
