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!
(I very much prefer strong typing, though for EAV schemas it's very
nice to have a duck-typed V column. But if CHECK constraints kill
performance, then strong typing via CHECK constraints is not viable.
I know, CHECK constraints don't get you strong typing in VIEWs and
queries in general, but it's good enough for my purposes.)
> I find that the performance is not that bad now.
Excellent.
> 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".
> 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?
> 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.
> #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.
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).
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
