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:
> > Please find below the last source code, I removed the check on the table
> > (NOT NULL on both sql_val and seq_inc)
>
> Yeah, I saw. I think this is another argument for SQLite3 needing a
> strict-type mode!
>
> > 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
>
> I'm not sure how to implement this. Maybe with the sessions extension.
>
> But I don't think this is an important semantic in the context of
> SQLite3 -- after all, it's "lite".
>
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]
}
>
> > 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
>
> I don't follow. What's H2?
>
>
H2 is a java embedded database, similar to sqlite. I recently moved most of
my projects (in java/kotlin) to sqlite, one is still using H2, mainly due
to the sequence.
> > 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?
>
> I don't understand this.
>
see the pseudo code above, how to implement the map[string,number] in C? a
fixed sized array?
>
> > #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;
> > }
>
> You could still check the types here... It won't kill performance --
> after all, this is only at sequence init time.
>
> Also, you could keep the CHECK constraints on the name and increment
> columns, and not on the current columns. Not sure it's worth it.
>
>
ok I did it
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);
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
The rest looks good.
>
> Also, I saw D. R. Hipp's discussion of increased write concurrency.
> Ooops! (Also, great news!) Maybe you should change the code for the
> next value to a loop like I described earlier (get the current value,
> update to add 1 where the value hasn't changed, repeat until the update
> statement updates one row, not zero).
>
I did not really understand your logic here.
Best regards,
Sylvain
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
