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
