Re: [sqlite] JDBC driver experience

[Sylvain Pointeau]

Hi,

Le mer. 18 avr. 2018 à 21:34, Richard Hipp  a écrit :

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?


I am using xerial and one from Christian Werner

>
> 2. For how long have you been using it?


since 3 years ago when I migrated to sqlite from H2

>
> 3. What issues you had with this driver?


the xerial is almost up to date with frequent release so ready to use.
(intellij uses xerial when querying a sqlite db)
however it does not give the possibility to use the SSE encryption module
and it found it impossible to compile myself.

when I though I will need to encrypt the database, I looked at the lib from
Christian Werner.
With his help I succeeded to compile it for mingw32 for windows (I then
proposed a CMake file to compile it cross plateform so easy to use the
latest sqlite release). At the end, I did not buy the SSE but I am happy
about my journey.


> 4. What advise do you have for avoiding problems in this driver?


I did not have issues with both of these driver. I trend to like more the
one from Christian Werner since I can compile it and use the most recent
sqlite version, as well as the option for the SSE (that I may buy one day)

Best regards,
Sylvain


>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] UPSERT available in pre-release

[Sylvain Pointeau]

I full agree with Petite Abeille.
This upsert is quite limited, we can only insert one row on conflict update
one row.
Even so it is a big improvement versus the insert or replace, this is far
from the merge from the SQL standard where we can insert or update multiple
rows in one query.

I am already super mega happy to think about using this new upsert for
sure, but could you implement the merge from the SQL standard?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Sun, Aug 6, 2017 at 6:34 PM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> Dear all,
>
> you can now find the code on github with a BSD3 license, as well as a
> release providing the dll for windows
>
>
forgot to add the link :-)
https://github.com/spointeau/libsequence


> I provided a cmake file, only tested for mingw (I don't have other
> compiler), I hope other will contribute to support other compilers.
>
> Again thanks for all your support.
>
> Best regards,
> Sylvain
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

Dear all,

you can now find the code on github with a BSD3 license, as well as a
release providing the dll for windows

I provided a cmake file, only tested for mingw (I don't have other
compiler), I hope other will contribute to support other compilers.

Again thanks for all your support.

Best regards,
Sylvain


On Fri, Aug 4, 2017 at 10:44 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> You're welcome.  Thanks for posting this on github.  Make sure there's a
>> license, preferably a nice and friendly one such as a BSD license, or
>> else put it in the public domain like SQLite3 is -- but it's your code,
>> so you do what you like with it.
>>
>
> I will put a BSD license and it will be on github this week end
>
> very funny, look what I found: http://docs.oracle.com/cd/
> E17076_05/html/bdb-sql/sequencesupport.html
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

>
> You're welcome.  Thanks for posting this on github.  Make sure there's a
> license, preferably a nice and friendly one such as a BSD license, or
> else put it in the public domain like SQLite3 is -- but it's your code,
> so you do what you like with it.
>

I will put a BSD license and it will be on github this week end

very funny, look what I found:
http://docs.oracle.com/cd/E17076_05/html/bdb-sql/sequencesupport.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams <n...@cryptonector.com>
wrote:

> On Fri, Aug 04, 2017 at 09:55:03PM +0200, Sylvain Pointeau wrote:
> > On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams <n...@cryptonector.com>
> wrote:
> > > In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
> > > (WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
> > > NOT EXISTS is probably good enough.
> >
> > I wonder if a memory structure would not be better?
>
> Doesn't matter.  You already have a solution.
>
> The main issue is that you can't tell when a transaction has begun or
> ended, so you can't tell when curr_val() should raise an error.  You can
> only tell that next_val() has been called on this connection, and that's
> not the same semantics as H2's.
>
>
actually the temp table is better in case of rollback.
however normally it should be executed under an autonomous transaction,
a rollback should not affect the sequence.


> I would just not bother with curr_val() for now.
>

I need it, but the current implementation should be enough.

=> I would really really like if a real implementation of the sequence
could be done in sqlite, behaving like oracle.
(very fast, reliable etc)

Thanks for all your help!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams <n...@cryptonector.com> wrote:

> On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote:
> > 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<100) 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?
>
> In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
> (WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
> NOT EXISTS is probably good enough.
>

I wonder if a memory structure would not be better?

kind of a fixed sized array where I would iterate to find the seq_name,
otherwise I insert it.
(or a real map but then c++ would be better in this case)

what do you think?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

sult_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

Re: [sqlite] sequencer

[Sylvain Pointeau]

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 6:03 PM, petern  wrote:

> About the H2 test.  That tester actually uses 10e6 is 10 x 10^6, 10 million
> rows.  When I wrote you I forgot I bumped it up to 10 million since 1
> million was only half a second on the native case.  Give that a try on H2
> and you'll see 22s goes to above 200s.
> >
> > CREATE SEQUENCE IF NOT EXISTS SEQ_TEST START WITH 1;
> > CREATE TABLE SEQ_TEST(seq_num INT UNIQUE NOT NULL);
> >
> > insert into SEQ_TEST(seq_num)
> > select seq_test.nextval from SYSTEM_RANGE(1, *100*)
> > ;
>

no, the H2 test case was 1 million. (my computer might be terribly slower
than yours)
and I also used 1 million for my sqlite test case, so I was consistent.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

Please find below the last source code, I removed the check on the table
(NOT NULL on both sql_val and seq_inc)

sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('seq1') from T;
Run Time: real 18.829 user 16.146103 sys 0.140401

I find that the performance is not that bad now.

Because of the performance (but I did not measure), I won't do the check on
the parameters, but I did put the same name for the seq_init for 2 or 3
parameters.

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
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

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?

#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;
  }

  sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
seq_val, seq_inc) values (?, ?, ?)", -1, , 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_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, , 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, , 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", 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

Re: [sqlite] sequencer

[Sylvain Pointeau]

> On Fri, Aug 4, 2017 at 7:41 AM, petern 
>> wrote:
>>
>>> 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.
>>>
>>

> I am sorry I fail to see how to improve it, I would be really interested
> to have advises from the experts!
>

HURR ! I found something !

if I remove the checks on the SP_SEQUENCE table, it is much faster

sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('seq1') from T;
Run Time: real 18.735 user 16.614106 sys 0.109201

and I have another idea to select on the rowid at the update.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> On Fri, Aug 4, 2017 at 7:41 AM, petern <peter.nichvolo...@gmail.com>
> wrote:
>
>> 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.
>>
>
> Hi Peter,
>
> I am not using the sequence as default, but I use it explicitely like
> insert into T (T_ID) values (seq_nextval('seq'))
>
> I generally create few hundred rows in my app, so I won't see any
> performance issue,
>
> Nevertheless, I am surprised to see so much difference between my UDF and
> the trigger implementation.
>
> Does anyone have an idea how to improve it? (using the same approach ->
> the sequence is in a table
>


I am sorry I fail to see how to improve it, I would be really interested to
have advises from the experts!

Furthermore, is it still useful to make a github repository since this is
slow?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> On Fri, Aug 4, 2017 at 7:41 AM, petern <peter.nichvolo...@gmail.com>
> wrote:
>
>> 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.
>>
>
>
for information, I tested it with H2

CREATE SEQUENCE IF NOT EXISTS SEQ_TEST START WITH 1;
CREATE TABLE SEQ_TEST(seq_num INT UNIQUE NOT NULL);

insert into SEQ_TEST(seq_num)
select seq_test.nextval from SYSTEM_RANGE(1, 100)
;

[2017-08-04 13:17:00] 100 rows affected in 22s 349ms
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Fri, Aug 4, 2017 at 7:41 AM, petern  wrote:

> 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.
>

Hi Peter,

I am not using the sequence as default, but I use it explicitely like
insert into T (T_ID) values (seq_nextval('seq'))

I generally create few hundred rows in my app, so I won't see any
performance issue,

Nevertheless, I am surprised to see so much difference between my UDF and
the trigger implementation.

Does anyone have an idea how to improve it? (using the same approach -> the
sequence is in a table)

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

Le ven. 4 août 2017 à 02:42, Nico Williams <n...@cryptonector.com> a écrit :

> On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote:
> > 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);
>
> If you use sqlite3_create_function*() with nArg == -1 then you can have
> just one UDF from sp_seq_init().  You'd have to check argc here though.
>

yes this is a good idea, I will do that as well as testing the arguments as
also Petern mentioned.

>
> >   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
> >   long seq_init_val = sqlite3_value_int64(argv[1]);
>
> seq_init_val could be optional and default to 0 or maybe 1.
>

good idea, defaukt to 1

>
> >   long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);
>
> I think some type checking should be done.
>

yes agree


> You could just take the argv[] values and bind them directly to the
> insert below, and use CHECK constraints on the SP_SEQUENCE table.


> Or you could use sqlite3_value_type() to check that each argument is of
> the expected value.
>
> I like the idea of using a CHECK constraint.  It simplifies the C code
> by having SQLite3 do more of the work.
>

I like also the check constraint, I am just wondering if the error, if any,
would descriptive enough. I will test it at least..


> >   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);
>
> You might want to add WITHOUT ROWID here (unless you want this to work
> with older versions of SQLite3).  You could make this conditional on a C
> pre-processor macro.


it is a good idea, however it does not seem critical


> >   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, , 0);
>
> Should this init function re-initialize sequences if called more than
> once for the same sequence name?
>

yes I did it on purpose


> Because that's what INSERT OR REPLACE will do (since on conflict it will
> delete the old row and insert a new one).  If you don't mean to
> re-initialize then use INSERT OR IGNORE.
>
> (When I want INSERT OR UPDATE I just run two statements:
>
> UPDATE .. WHERE ..;
> INSERT OR IGNORE ..;)
>
> >   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, , 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, , 0);
> >
> >   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
> 
>
> If ever SQLite3 got higher write concurrency (it almost certainly won't,
> but derivatives might), then a simple way to ensure atomicity here might
> be to do the SELECT first then an UPDATE with a WHERE seq_val =
> :the_value_just_read, and loop as necessary.
>
> do {
> current_value = ;
>   current value hasn't changed>;
> } while ();
>
> Though if one is going to have higher write concurrency then one ought
> to have SELECT .. FOR UPDATE.
>
> Anyways, no change needed

Re: [sqlite] sequencer

[Sylvain Pointeau]

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, , 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, , 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, , 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

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Thu, 3 Aug 2017 at 08:04, Hick Gunter  wrote:

> A sequence is very easily implemented as a virtual table that keeps the
> current values in a separate table my_sequences (name text primary key,
> initial integer, current integer, increment integer).
>
> (...) Or whatever else tickles your fancy. Just think table <=> class,
> virtual field <=> method
>


What do you all recommend? UDF or Virtual tables?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

ok thank you for the confirmation, I will try implementing it in a dll
using UD functions and put it on github.

Le mer. 2 août 2017 à 20:56, Richard Hipp <d...@sqlite.org> a écrit :

> On 8/2/17, Sylvain Pointeau <sylvain.point...@gmail.com> wrote:
> >
> > is it really possible to make an update into that nextval function? I
> don't
> > think so since only one statement can be run at the same time if sqlite
> is
> > in serialized mode.
>
> "serialized" means that multiple threads cannot be making updates at
> the same time.  In this case, the update would be happening in the
> same thread.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] sequencer

[Sylvain Pointeau]

if I do

insert into mytable(f)
select nextval("myseq") from T

is it really possible to make an update into that nextval function? I don't
think so since only one statement can be run at the same time if sqlite is
in serialized mode.

Le mer. 2 août 2017 à 20:25, Nico Williams <n...@cryptonector.com> a écrit :

> On Wed, Aug 02, 2017 at 06:10:52PM +0000, Sylvain Pointeau wrote:
> > for a general case, I would need to persist the counter into a table
> (for a
> > specified sequencer) and doing the nextval inside a mutex lock
> >
> > Is it possible to insert/ select from a UDF if the statements are
> > serialized? or should I use the virtual tables? (should we store the
> > sequencers in a different DB?)
>
> In SQLite3 all writes in transactions are serialized.  No locks needed
> because there's just one big lock around the entire DB.  I recommend you
> read up on SQLite3's transactions and locking.
>
> A next_serial() UDF would basically be a C-coded (or Perl, or whatever)
> function that uses the SQLite3 API to first run an UPDATE on the
> sequence then a SELECT to get the now-next value, and would return that.
>
> If you use the INSTEAD OF trigger approach, then the same applies,
> except that the triggers will be SQL-coded (which is nice, IMO).
>
> This is all perfectly safe in the current SQLite3 concurrency model
> (just one writer at a time).  I don't think SQLite3's write concurrency
> will ever get better, but I suppose one never knows!
>
> If you were using an RDBMS with higher write concurrency then you'd need
> to be more careful and arrange for synchronization.  Usually such
> RDBMSes provide builtin next_serial()-style functions anyways.
>
> As to your last question, I'd put the sequences table in the same DB,
> unless you need to attach multiple DBs and have all of them share
> sequences, in which case I'd make a DB just for the sequences, or else
> put them in the main DB.
>
> Nico
> --
> ___
> 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

Re: [sqlite] sequencer

[Sylvain Pointeau]

Thank you Nico!

for a general case, I would need to persist the counter into a table (for a
specified sequencer) and doing the nextval inside a mutex lock

Is it possible to insert/ select from a UDF if the statements are
serialized? or should I use the virtual tables? (should we store the
sequencers in a different DB?)



Le mer. 2 août 2017 à 19:46, Nico Williams  a écrit :

> On Wed, Aug 02, 2017 at 05:41:38PM +0100, Simon Slavin wrote:
> > On 2 Aug 2017, at 5:35pm, Nico Williams  wrote:
> > > On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
> > >> Can someone explain ?
> > >
> > > They make it easy to have N tables with the same rowid namespace, for
> > > example.  So you could do something like:
> > >
> > >  ...
> >
> > Okay, I understand that.  Thanks, Nico.
> >
> > So the reason I didn’t understand the need for sequences is that
> > someone who had been using SQLite for a long time would never come up
> > with the concept.  It’s just not needed.  You’d just create individual
> > tables, each with their own AUTOINC key as normal, then key into them
> > using a VIEW or FOREIGN KEY.  The problem solved by SEQUENCEs never
> > arises.
>
> Sometimes you have external constraints on your schema and so don't have
> the freedom to do what you propose.  I guess it can happen that you've
> never had to experience that, but I have.
>
> > This gives us a problem with Peter’s original question, because it
> > seems unlike that implmenting sequences with SQLite is common enough
> > that we have a ready solution.
>
> Well, if no one's done the UDF thing, maybe OP can do it and maybe open
> source it.  I mean, it's pretty simple.  Alternatively OP can use
> triggers as discussed.
>
> Another thing I often do with SQLite3 is to create VIEWs with INSTEAD OF
> triggers that do the right thing.  Something like:
>
>   CREATE TABLE sequences (...); -- see previous post
>   CREATE TABLE real_thing (...);
>   CREATE VIEW  thing AS
>   SELECT * FROM real_thing;
>   CREATE TRIGGER thing_ins INSTEAD OF INSERT ON thing
>   FOR EACH ROW
>   BEGIN
> UPDATE sequences
> SET last = last + 1
> WHERE name = 'foo';
> INSERT INTO real_thing (...)
> SELECT (SELECT last FROM sequences WHERE name = 'foo'),
>NEW.column_1, ...;
>   END;
>   CREATE TRIGGER thing_upd INSTEAD OF UPDATE ON thing
>   FOR EACH ROW
>   BEGIN
> UPDATE real_thing
> SET ...
> WHERE ...;
>   END;
>   CREATE TRIGGER thing_del INSTEAD OF DELETE ON thing
>   FOR EACH ROW
>   BEGIN
> DELETE FROM real_thing
> WHERE ...;
>   END;
>
> I often basically use VIEWs in SQLite3 as a form of SQL-coded stored
> procedures, with NEW.* / OLD.* being the "function"'s arguments.
>
> This can get very verbose and somewhat tedious though.  I've used SQL to
> generate all of these VIEWs and INSTEAD OF TRIGGERs in order to reduce
> the amount of code to hand-maintain.  (I also do similar things with PG:
> https://github.com/twosigma/postgresql-contrib .)
>
> Since it's not possible to have an INSERT output a result, nor can you
> have a SELECT on such a VIEW have a side-effect, it's not possible to
> write a sequences VIEW that you can then use like this:
>
>   -- Doesn't work because SELECT on VIEWs can't have side-effects (not
>   -- without side-effect-having UDFs in the VIEW's definition):
>   INSERT INTO real_thing (...)
>   SELECT (SELECT next FROM seq WHERE name = 'foo'), ...;
>
> Without a next_serial() UDF one has to resort to the triggers discussed
> earlier.  So there's a limit to what one can do with this technique if
> you also appreciate and want elegance (which I do).
>
> An actual next_serial()-type function would be very nice.
>
> In short, SQLite3 basically has stored procedures.  It's just missing
> syntactic sugar for them.
>
> It really does help to not be afraid of using SQL as a programming
> language.  Many are allergic to SQL as a programming language -- those
> poor souls often end up using ORMs and pay the price for it later.
>
> Nico
> --
> ___
> 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

Re: [sqlite] sequencer

[Sylvain Pointeau]

your solution is only for one row as opposed to my example creating many
rows

On Wed, 2 Aug 2017 at 18:27, Peter Da Silva <peter.dasi...@flightaware.com>
wrote:

> By “the same thing” I mean:
>
> BEGIN;
> something like the stuff I had in my original post where it’s incrementing
> the sequence;
> your statement where you’re using the sequence, except using something
> like (SELECT value FROM super_sequences WHERE id=’SEQ_1’);
> COMMIT;
>
> On 8/2/17, 11:20 AM, "sqlite-users on behalf of Sylvain Pointeau" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> sylvain.point...@gmail.com> wrote:
>
> On Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>
> > Can’t you do the same basic logic then use (SELECT value FROM
> > super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
> >
> >
> > insert into mytable (MY_NO, MY_INFO)
> > SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> > ;
> >
> >
> no because nextval also increment the sequence, as opposed to the
> (SELECT
> value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the
> value
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
>
> insert into mytable (MY_NO, MY_INFO)
> SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> ;
>
>
no because nextval also increment the sequence, as opposed to the (SELECT
value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the value
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Wed, Aug 2, 2017 at 5:56 PM, Simon Slavin  wrote:

>
>
> On 2 Aug 2017, at 4:54pm, Peter Da Silva 
> wrote:
>
> > Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
> Actually, I don’t understand how sequences are superior to normal use of
> an AUTOINC column.  Can someone explain ?
>
>
Yes I can.

I need to use special number that I specified the start and increment.

for instance I have a entity with a lower number and upper number, I create
then a sequence like

CREATE SEQUENCE IF NOT EXISTS SEQ_RANGE START WITH 100 INCREMENT BY 100 ;

then I create many entities like:

insert into MYENTITY(LowerNumber,UpperNumber)
select SEQ_RANGE.currval+1, SEQ_RANGE.nextval
from MY_REF_TABLE
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Wed, Aug 2, 2017 at 5:43 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Hence the suggestion to script a transaction. For example, in pseudocode:
>
> BEGIN;
> SELECT value, increment from super_sequences where table = :table and
> column = :column;
> INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,
> values);
> UPDATE super_sequences set value = :(value + increment) WHERE table =
> :table and column = :column;
> COMMIT;
>

ok but it does not work for insert like

insert into mytable (MY_NO, MY_INFO)
SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Wed, Aug 2, 2017 at 5:27 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Have a look at https://sqlite.org/autoinc.html
>

Yes I am aware of autoinc but this is not what I can use, because I need to
specify exactly the sequence (as start number and increment). Additionally
I can have tables having 2 or 3 fields needing a specified sequence number.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sequencer

[Sylvain Pointeau]

Dear all,

I am currently using H2 and I use sequencers like:

CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')

I would like to move to sqlite, but what would be your advice for the
sequencer values?
Is it possible at all?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

[Sylvain Pointeau]

I wonder how SQLITE_PREPARE_PERSISTENT can be used in a jdbc driver. Do you
have any idea?
(seems like there is no possibility to know about it from the jdbc API)

is it better to always set this flag or to never do it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

> OK yes I agree, where can we discuss about it?
>
>
For information, we continued this discussion privately.

I succeed to build this library and I will use it in my project.
Additionally I would be glad contribute if Christian needs my help.
Furthermore, I will assess my need to have SSE, and if yes, I will buy it
and integrate it (quite easily).

I will also blog on how to compile it and how to use it, sooner or later,
it might help other people having the same need.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

>
>
> IMO it is no good idea to discuss specific issues of an only indirectly
> SQLite related
> library on this mailing list


OK yes I agree, where can we discuss about it?

>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

On Sun, Mar 19, 2017 at 5:38 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> On Sun, Mar 19, 2017 at 4:57 PM, Sylvain Pointeau <
> sylvain.point...@gmail.com> wrote:
>
>> Why don't you provide an up-to-date version? this lib seems dead when we
>>> look at the website, also why don't you put the sources on git it would be
>>> easier to contribute or raise a bug if any...
>>>
>>
>> I tried to compile the latest version of sqlite 3.17 with the latest
>> sources of sqlitejava  (http://www.ch-werner.de/javasqlite) with VS2015
>> Express Edition
>> I downloaded the latest jdk-8u121.
>>
>> I have the following error: SQLite\JDBCDriver.java:9: error: JDBCDriver
>> is not abstract and does not override abstract method getParentLogger() in
>> Driver
>>
>
> I just tried with jdk-6u45-x86:
>
> SQLite\JDBC2x\JDBCConnection.java:17: SQLite.JDBC2x.JDBCConnection is not
> abstract and does not override abstract method
> createStruct(java.lang.String,java.lang.Object[]) in java.sql.Connection
> public class JDBCConnection
>^
> .\SQLite\JDBC2x\JDBCDatabaseMetaData.java:10: 
> SQLite.JDBC2x.JDBCDatabaseMetaData
> is not abstract and does not override abstract method
> getFunctionColumns(java.lang.String,java.lang.String,java.lang.String,java.lang.String)
> in java.sql.DatabaseMetaData
> public class JDBCDatabaseMetaData implements DatabaseMetaData {
>^
>

it works with jdk-1_5_0_22

Please could we move this library to compile with the newest JDK and VS2015
without error and warning?

I am ready to help for windows.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

On Sun, Mar 19, 2017 at 4:57 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> Why don't you provide an up-to-date version? this lib seems dead when we
>> look at the website, also why don't you put the sources on git it would be
>> easier to contribute or raise a bug if any...
>>
>
> I tried to compile the latest version of sqlite 3.17 with the latest
> sources of sqlitejava  (http://www.ch-werner.de/javasqlite) with VS2015
> Express Edition
> I downloaded the latest jdk-8u121.
>
> I have the following error: SQLite\JDBCDriver.java:9: error: JDBCDriver is
> not abstract and does not override abstract method getParentLogger() in
> Driver
>

I just tried with jdk-6u45-x86:

SQLite\JDBC2x\JDBCConnection.java:17: SQLite.JDBC2x.JDBCConnection is not
abstract and does not override abstract method
createStruct(java.lang.String,java.lang.Object[]) in java.sql.Connection
public class JDBCConnection
   ^
.\SQLite\JDBC2x\JDBCDatabaseMetaData.java:10:
SQLite.JDBC2x.JDBCDatabaseMetaData is not abstract and does not override
abstract method
getFunctionColumns(java.lang.String,java.lang.String,java.lang.String,java.lang.String)
in java.sql.DatabaseMetaData
public class JDBCDatabaseMetaData implements DatabaseMetaData {
   ^
.\SQLite\JDBC2x\JDBCResultSet.java:12: SQLite.JDBC2x.JDBCResultSet is not
abstract and does not override abstract method
updateNClob(java.lang.String,java.io.Reader) in java.sql.ResultSet
public class JDBCResultSet implements java.sql.ResultSet {
   ^
.\SQLite\JDBC2x\JDBCStatement.java:6: SQLite.JDBC2x.JDBCStatement is not
abstract and does not override abstract method isPoolable() in
java.sql.Statement
public class JDBCStatement implements java.sql.Statement {
   ^
.\SQLite\JDBC2x\JDBCResultSetMetaData.java:5:
SQLite.JDBC2x.JDBCResultSetMetaData is not abstract and does not override
abstract method isWrapperFor(java.lang.Class) in java.sql.Wrapper
public class JDBCResultSetMetaData implements java.sql.ResultSetMetaData {
   ^
.\SQLite\JDBC2x\JDBCPreparedStatement.java:17:
SQLite.JDBC2x.JDBCPreparedStatement is not abstract and does not override
abstract method setNClob(int,java.io.Reader) in java.sql.PreparedStatement
public class JDBCPreparedStatement extends JDBCStatement
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

>
> Why don't you provide an up-to-date version? this lib seems dead when we
> look at the website, also why don't you put the sources on git it would be
> easier to contribute or raise a bug if any...
>

I tried to compile the latest version of sqlite 3.17 with the latest
sources of sqlitejava  (http://www.ch-werner.de/javasqlite) with VS2015
Express Edition
I downloaded the latest jdk-8u121.

I have the following error: SQLite\JDBCDriver.java:9: error: JDBCDriver is
not abstract and does not override abstract method getParentLogger() in
Driver
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

> Now I'm a little bit confused. You asked for an SEE enabled SQLite Java
> binding. This requires you to 1. obtain your SEE license from DRH's company
> and 2. to use the SEE sources and compile/link them to
> {lib,}sqlite_jni.{dll,so}
> for your target platform.
>

Don't be confused, I know all that, but before to buy the SSE (it is
2'000), I need:
1- to choose a sqlite jdbc driver allowing SSE (done)
2- to test the java driver to see if I can migrate successfully from H2 to
sqlite (I will do that in the next coming week(s))
3- to really assess if I need still need encryption in my project (after
some recent changes)

For now I tested your driver (quicky) and it seems I can use it, so 1 is
done, I need to do 2.


>
> So in any case involving SEE you have to rebuild things from source code
> and deal with deployment on the target environment.
>
>
Yes and I have a compiler on another machine, but for a matter of testing
quickly, I would not like go through the compile cycle.
Anyway, for now I tested with the outdated version (3.7.7.1)

Why don't you provide an up-to-date version? this lib seems dead when we
look at the website, also why don't you put the sources on git it would be
easier to contribute or raise a bug if any...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

On Sat, Mar 18, 2017 at 1:04 PM, Christian Werner <
christian.wer...@t-online.de> wrote:

> Could it be updated to the latest version of sqlite, seems it is pretty
>> outdated now.
>>
>
> Totally outdated but still compiling even with most recent SQLite 3 (and 2)
> versions and having autoconf OOTB support for SEE. Must be steampunk then
> ;-)
>

I am on a computer without compilers, it would have been good if I could
test with the latest version.

Could it be also improved? not needing to set the path of the native
>> library? like xerial made it, is it possible?
>>
>
> If properly installed there should be no need to explicitly set a path.
>

You mean in the JDK (or JRE)? (if yes -> this is not always possible)

another question, do you confirm that sqlite3 is included into
sqlite_jni.dll?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

Le sam. 18 mars 2017 à 09:53, Christian Werner <christian.wer...@t-online.de>
a écrit :

> On 03/18/2017 06:21 AM, Sylvain Pointeau wrote:
> > I don't really want to use odbc in Java. I was thinking of
> > https://github.com/xerial/sqlite-jdbc
> > but how to use SSE in this case? seems the build is not so easy.
>
> You could use http://www.ch-werner.de/javasqlite which can be built with
> SEE.


OK I can give it a try.

Could it be updated to the latest version of sqlite, seems it is pretty
outdated now.
Could it be also improved? not needing to set the path of the native
library? like xerial made it, is it possible?

does this jdbc driver give the metadata?

Best regards,
Sylvain


>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite with Java

[Sylvain Pointeau]

I don't really want to use odbc in Java. I was thinking of
https://github.com/xerial/sqlite-jdbc
but how to use SSE in this case? seems the build is not so easy.

with the sqlite odbc module
>
> http://www.ch-werner.de/sqliteodbc
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite with Java

[Sylvain Pointeau]

Dear all,

I would like to use sqlite from Java, but I am also looking to buy SSE.
however which library would you recommend? how to integrate SSE?

ps: it would be splendid if you could provide the java libs, similar to the
.net version.

Best regard,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import 5000 xml files in a sqlite database file

[Sylvain Pointeau]

hello,

I am not sure if Oxygen or another XML specialized software could do it,
however it would be easy done using C or C++ or Java. Advantage is that it
is then easy to automatize in a batch mode. I can propose you my services
if you are interested.

Best regards,
Sylvain

Le samedi 22 octobre 2016,  a écrit :

> Hi,
>
> I have more than 5000 xml files. All files have the same xml-structure.
>
> Each file has different values (timestamps, numbers and strings). I would
> like to put all these values in a sqlite database tabke, all in one table.
> => Import the data values into a sqlite database table.
>
> Can you please tell me a software program, that can do this quickly?
>
> Thank you for your answers.
>
> Best regards
>
> Bob
> ___
> 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

[sqlite] user defined function returning a result set

[Sylvain Pointeau]

>
> To do this, you'd have to create a virtual table that actually gets
> instantiated per CSV file:
>
>  CREATE VIRTUAL TABLE temp.file1 USING
> csvFileReader('/path/to/my/file.csv');
>  SELECT * FROM file1;
>  DROP TABLE file1;
>
> In the above, the xCreate method of the virtual table implementation
> could peek at the CSV file to see how many columns it has before
> declaring how many rows it intends to return.  Which is what you need.
> --
> D. Richard Hipp
> drh at sqlite.org
>
>
Excellent thank you very much, I could find a example and I will take it
from there.
Thanks again :-)

[sqlite] user defined function returning a result set

[Sylvain Pointeau]

Hello,

I think I have read on this mailing list that sqlite now has functions able
to return rows. (but cannot find it anymore)

I am interested about this new functionality. Would it be possible to see a
very basic sample of it?

Do you think we can implement a kind of CSV reader with this new function?
kind of: select * from CSVRead('/path/to/my/file.csv')

Please let me know.

Best regards,
Sylvain

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :

>
> On 30 Jul 2015, at 9:57pm, Sylvain Pointeau  <javascript:;>> wrote:
>
> > no it does not work double clicking on the csv to open it in excel, I am
> > 100% sure (I just tried again), you have to go through the data->import
> and
> > set up the columns as text for it to work
>
> Yes, as I wrote, this bad behaviour (which you could reasonably call a
> bug) is documented.  That's how Microsoft wrote Excel and that's the way
> they want it to work, and that's how it will continue to work.
>
> Simon.
>
>
There is a workaround for the leading 0, but you cannot have multi-line
when doing this. It is one or the other, too bad! I though generating xml
for excel instead of csv, but I didn't have time to try yet.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 10:52 PM, Simon Slavin  wrote:

>
> On 30 Jul 2015, at 9:50pm, Sylvain Pointeau 
> wrote:
>
> > leading
> > 0 are removed when opening a csv file by double clicking on it to open it
> > in excel.
>
> This is documented behaviour in Excel, which assumes that all cells
> contain numbers, and therefore that leading zeros can be removed.  If you
> don't want them removed you have to quote the value, e.g. "0123".
>
> Simon.


no it does not work double clicking on the csv to open it in excel, I am
100% sure (I just tried again), you have to go through the data->import and
set up the columns as text for it to work

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 9:00 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/30/2015 10:58 AM, Sylvain Pointeau wrote:
> > is it possible? in a lot of cases, I cannot use sqlite (executable)
> > because of the lack of a good CSV import. It would really great if
> > this could be addressed.
>
> Use the APSW shell:
>
>   http://rogerbinns.github.io/apsw/shell.html
>
> Behind the scenes it uses the Python CSV libraries which support a
> number of dialects including excel (default) and excel-tab.
>
> The APSW shell also supports an .autoimport command.  It automatically
> works out dialects, separators and data types.  Here is the extended help:
>
>  8< 
> sqlite> .help autoimport
>
> .autoimport FILENAME ?TABLE?  Imports filename creating a table
>   and automatically working out
>   separators and data types
>   (alternative to .import command)
>
> 
>
> Care is taken to ensure that columns looking like numbers are
> only treated as numbers if they do not have unnecessary leading
> zeroes or plus signs.  This is to avoid treating phone numbers
> and similar number like strings as integers.


I know your shell, unfortunately it is more difficult to install Python and
APSW than just bare sqlite3. Additionally it works well (see my previous
email, I was wrong, the CSV import works just fine)

however your auto import reminds me just what I faced few days ago, leading
0 are removed when opening a csv file by double clicking on it to open it
in excel.
I had to do import -> data then to go through the wizard until I setup all
columns as text, to keep the leading 0. unfortunately it led to another
issue, cell with multiple lines where badly imported. what I did, I removed
the line feed on some columns before generating the csv. not easy to have
all we need :-/

[sqlite] CSV excel import

[Sylvain Pointeau]

>
>
> ?I am replying to your original message rather that later ones because I'm
> curious about the CSV file which is giving you a problem. Using the sqlite3
> command on Linux Fedora 22 (64 bit), I get the following (transcript):
>
> $sqlite3
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test(name1 text, name2 text, name3 text);
> sqlite> .mode csv
> sqlite> .import ./test.csv test
> sqlite> .mode lines
> sqlite> select * from test;
> name1 = name1
> name2 = name2
> name3 = name3
>
> name1 = line1a
> name2 = line1b
> name3 = line1c
>
> name1 = line2"a
> name2 = line2b
> name3 = 'line2b'
> sqlite> .quit
> joarmc at mckown5 2015-07-30T14:43:21 ~/junk
> $cat test.csv
> name1,name2,name3
> "line1a",line1b,line1c
> "line2""a",line2b,'line2b'
> joarmc at mckown5 2015-07-30T14:43:25 ~/junk


Well ... I am glad that you are right.

When I saw your message, I wondered why it didn't work for me.

I have taken back my samples:

$ cat test.csv
A,B,C
T,TI,TIT
"A,B",C,D
"1st line
2nd line",E,F

sqlite> .mode csv
sqlite> .import test.csv T
sqlite> select * from T;
T,TI,TIT
"A,B",C,D
"1st line
2nd line",E,F

I think this is where my mistake comes from, and when I did browse on
internet, I saw some replies showing how to do an update to remove the
quotes...

now I should have done:

sqlite> .mode csv
sqlite> .import test.csv T
sqlite> .mode list
sqlite> select * from T;
T,TI,TIT
A,B,C,D
1st line
2nd line,E,F
0003,002,01

so I was obviously wrong, sqlite import the csv very well, and I am glad to
have been wrong!

apologies for my mistake.

Best regards,
Sylvain

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, John McKown  a
?crit :

> On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau <
> sylvain.pointeau at gmail.com <javascript:;>> wrote:
>
> > On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  <javascript:;>> wrote:
> >
> > > Actually there exists an open source tool that convert Excel data into
> > > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library
> (
> > > http://www.gdal.org/).  You do need a version of GDAL built with the
> > > SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> > > SpatiaLite into the mix, you can make your spreadsheets show up a
> virtual
> > > tables using the VirtualOGR module.
> > > Peter
> >
> >
> > really cool, but I would like to have a solution directly in the sqlite3
> > executable, so it would be available on my mac and on my windows at work
> as
> > well as everywhere where sqlite can be installed.
> >
> >
> ?I see and understand your desire. But I, personally, don't like the idea.
> I really don't want Dr. Hipp and the other developers to be using up their
> time trying to put in something that is MS specific. And then trying to
> keep it up to date with future, incompatible version of the Excel file
> format. I don't use Excel.?
>
> ?I normally use LibreOffice. And sometimes even Gnumeric.
>
> ?What might be of some, more generic, help would be if the SQLite
> executable could do an IMPORT operation from an ODBC source. This could
> address your problem because Excel, at least on Windows, supports being
> used as an ODBC target. I don't know about the Mac. The plus of this would
> be that would open up a standard interface to SQLite which could use many
> other sources such as Oracle, PostgreSQL, MariaDB (MySQL), and anything
> else which implements an ODBC source interface.?
>
>
My answer saying that I would like to have it in sqlite implied a correct
csv import. In a previous email, I stated that it would be unreasonable to
ask sqlite to have an xml parser etc.
I just need a import of csv that is working well, not more.

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :

>
> On 30 Jul 2015, at 7:48pm, Sylvain Pointeau  <javascript:;>> wrote:
>
> > really cool, but I would like to have a solution directly in the sqlite3
> > executable
>
> If you're talking about the SQLite shell tool then Excel import will never
> be integrated into it.  There's no way to know when Microsoft is going to
> change or add to their file specification for Excel, and it would oblige
> the SQLite development team to update the shell tool on an schedule only
> Microsoft could predict.
>
> By the way, an earlier premise of this thread is incorrect.  CSV is
> perfectly standard and perfectly documented:
>
> <https://tools.ietf.org/html/rfc4180>
>
> The CSV importing part of the SQLite shell tool implements this very well.
>
> Simon.
>


No it does not implement the rule 5 correctly:

 Each field may or may not be enclosed in double quotes (however
   some programs, such as Microsoft Excel, do not use double quotes
   at all).  If fields are not enclosed with double quotes, then
   double quotes may not appear inside the fields.


Why sqlite keeps the quoted string in the database? Except of this, it
would work well I would say

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Scott Doctor  a ?crit :

>
> A trick that works great most of the time with ODS is when exporting to
> CSV select the option to quote all fields. One problem with CSV is that
> many exports quote strings but not numbers. If everything is quoted then it
> is much simpler to process. But would need at least several options on the
> import:
>
> 1)  what is the separator token (i.e. is it a comma, or a period, or a
> semicolon,...)
>
> 2)  what is the decimal token (i.e. is it a period, comma, other,...)
>
> 3)  Should quoted strings keep the quotes or strip the quote characters
> during processing
>
> 4)  What is the escape sequence for embedding a quote character within a
> quoted string
>
> 5)  using single or double quote character as the quote token.
>
> As you can see the number of permutations grows very fast to accommodate
> the wide variety of ways common programs handle CSV exports.
>
> On 7/30/2015 11:28 AM, Bernardo Sulzbach wrote:
>
> > I can remember two times when my life would have been easier if I
> > could throw big .ods into sqlite3 dbs.
>

I would like something that works, actually I never could use the import
csv from sqlite, I had to build my own, like probably many others. Why is
it not possible to have a solution directly in sqlite?

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  wrote:

> Actually there exists an open source tool that convert Excel data into
> SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (
> http://www.gdal.org/).  You do need a version of GDAL built with the
> SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> SpatiaLite into the mix, you can make your spreadsheets show up a virtual
> tables using the VirtualOGR module.
> Peter


really cool, but I would like to have a solution directly in the sqlite3
executable, so it would be available on my mac and on my windows at work as
well as everywhere where sqlite can be installed.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:43 PM, Adam Devita  wrote:

> Instead of trying to conform to MS-Excel's csv format, wouldn't it be
> better to write an import from .xls (or .ods if that is an open
> standard) directly?
>
> That way each cell's value can be bound to a position holder in a
> query.  No more fussing with "In this country we use this symbol to
> denote decimals", "my data has special characters or line feeds inside
> a cell" etc.
>
>
The level of effort is just not the same, you have to deal with an XML
parser and all.
CSV works also well, I had actually no problem at all handling CSV with H2,
I am just asking the same for sqlite.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:32 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> > My point is that I have seen so many emails regarding this incorrect csv
> import, that it would be so easy for us if it just simply works in the CLI
> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
>

JSON is not an option when we are working with business people. Excel is
their only  tool to review and modify data so we need to import and export
CSV. Honestly direct excel import/export would be even better but CSV is
fine too and largely simpler.

for instance, H2 worked wonderfully well for all excel import. why is it
unreasonable to ask for the same in sqlite?

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:17 PM, Luuk  wrote:

> On 30-7-2015 20:07, Richard Hipp wrote:
>
>> On 7/30/15, Sylvain Pointeau  wrote:
>>
>>> I understood from the mailing list, that CSV is not a defined format,
>>> then
>>> let's propose another format, well defined, the Excel one (which is in my
>>> experience a format to is good every time I had to exchange CSV files).
>>>
>>> Then why don't you propose an import of CSV from Excel (or similar)?
>>> csv(excel)
>>>
>>> is it possible? in a lot of cases, I cannot use sqlite (executable)
>>> because
>>> of the lack of a good CSV import. It would really great if this could be
>>> addressed.
>>>
>>>
>> An Excel-to-SQLite converter utility sounds like it would be a great
>> open-source project.  Why don't you start it up?
>>
>>
> +1
>
> Except for the fact that "CSV is not a defined format"..
>
> It's not a defined format because:
> 1) CSV is an acornym for 'Comma Separated Values'
>
> 2) There are countries in the world which use a comma ',' as a decimal
> separator
>
> 3) Excel (or Microsoft) decided to use the ';' as a separator in case the
> decimal separator is a ','
>
>
for instance, in H2, the CSV reader works wonderfully well.
we can define in H2, but also in sqlite the column separator.

Well as I said, CSV might not be a defined format, but it is probably not
the excuse to not import it correctly. For now in sqlite, quoted text stays
quoted after the import. How do you want us to work correctly with this
result? honestly.

Best regards,
Sylvain

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:07 PM, Richard Hipp  wrote:

> > Then why don't you propose an import of CSV from Excel (or similar)?
> > csv(excel)



>

An Excel-to-SQLite converter utility sounds like it would be a great
> open-source project.  Why don't you start it up?
> --
> D. Richard Hipp
> drh at sqlite.org


I have made a lot of utilities for sqlite, and one of them is a csv2db for
sqlite.

it is not so practical because:
- it needs to be recompile for the platform (windows in my case and I
didn't have any compiler)
- the program is external to the sqlite3 CLI, therefore we need to mix sql
script and bash scripts.

but I can give you the source code if you want, it is however written in
c++.
actually it was available in gitorious but it closed. I have to find time
to make it again available on github.

My point is that I have seen so many emails regarding this incorrect csv
import, that it would be so easy for us if it just simply works in the CLI
and delivered in standard in the sqlite3 executable.

Best regards,
Sylvain

[sqlite] CSV excel import

[Sylvain Pointeau]

I understood from the mailing list, that CSV is not a defined format, then
let's propose another format, well defined, the Excel one (which is in my
experience a format to is good every time I had to exchange CSV files).

Then why don't you propose an import of CSV from Excel (or similar)?
csv(excel)

is it possible? in a lot of cases, I cannot use sqlite (executable) because
of the lack of a good CSV import. It would really great if this could be
addressed.

Best regards,
Sylvain

[sqlite] index for OR clause

[Sylvain Pointeau]

On Mon, Jul 27, 2015 at 8:27 PM, R.Smith  wrote:

>
>
> On 2015-07-27 08:09 PM, Simon Slavin wrote:
>
>> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau 
>> wrote:
>>
>>  create table TEST (
>>> a TEXT NOT NULL,
>>> a2 TEXT NOT NULL,
>>> b TEXT NOT NULL,
>>> c TEXT NOT NULL
>>> );
>>>
>>> create index IDX_TEST_1 on TEST(a,a2,b,c);
>>>
>>> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
>>> insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
>>> insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
>>> insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
>>>
>>> sqlite> explain query plan select * from TEST where (a = '123' or
>>> a2='1234') and b = '456' and c='PP';
>>>
>>> 0|0|0|SCAN TABLE TEST
>>>
>>> whereas
>>>
>>> sqlite> explain query plan select * from TEST where a = '123' and b =
>>> '456'
>>> and c='PP';
>>>
>>> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
>>>
>>> how to make this query using an index? is this possible?
>>>
>> That is a good job of investigation you have done there.  What you didn't
>> take into account is that the order of columns in an index matters.
>>
>> Your example has known precise values for b and c, and sometimes but not
>> always knows a and a2.  So b and c should be up front in your index:
>>
>> create index IDX_TEST_2 on TEST(b,c,a,a2);
>>
>
> Quite, but there is also another consideration: Cardinality.  Now it might
> be that this data extract is not representative of the whole table at all,
> but from the looks of it, those columns b and c offer severely low
> cardinality, perhaps in the order of more than 30% of the table in
> duplications.
>
> If this is the case, an index on c or b will amount to pretty much a
> table-scan still. You really want to hit those high-cardinality columns in
> your leading indexer (which is why Richard rightly suggested the double
> index).
>
> I'd almost think having the less-expensive set of these:
> create index IDX_TEST_1 on TEST(a);
> create index IDX_TEST_2 on TEST(a2);
> would produce very near the same speeds for that query if the cardinality
> of columns b and c remain low for populated data.
>
> If however the cardinality scales up pretty well, then Richard's
> suggestion is much better, and if the cardinality will be even higher in b
> and c than in the a's, then Simon's suggestion is best.
>

in my case, c cardinality was low, (a,b) and (a2,b) almost unique

I suppose that in my case an index on c and b would give good result :-)

[sqlite] index for OR clause

[Sylvain Pointeau]

Yes, having:

create table TEST (
 a TEXT NOT NULL,
 a2 TEXT NULL,
 b TEXT NOT NULL,
 c TEXT NOT NULL
);

create index IDX_TEST_1 on TEST(c,b,a,a2);
create index IDX_TEST_2 on TEST(c,b,a);
create index IDX_TEST_3 on TEST(c,b,a2);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (c=? AND b=?)


but isn't the 2 indices giving best best response time?


create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?)

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_3 (a2=? AND b=? AND c=?)

I guess you will say that it depends on the data set?
but in a general case, I think the 2 indices version is faster, do you
agree?

[sqlite] index for OR clause

[Sylvain Pointeau]

ha yes thank you

create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?)

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_3 (a2=? AND b=? AND c=?)

thank you again

On Mon, Jul 27, 2015 at 8:05 PM, Richard Hipp  wrote:

> On 7/27/15, Sylvain Pointeau  wrote:
> > Hello,
> >
> > I would like to know if this is possible to use an index in the following
> > case:
> >
> > select * from TEST where (a = '123' or a2='1234') and b = '456' and
> c='PP';
> >
> >
> > given the table:
> >
> > create table TEST (
> >  a TEXT NOT NULL,
> >  a2 TEXT NOT NULL,
> >  b TEXT NOT NULL,
> >  c TEXT NOT NULL
> > );
> >
> > create index IDX_TEST_1 on TEST(a,a2,b,c);
>
> You want the following two indexes:
>
>create index IDX_TEST_1 on TEST(a,b,c);
>create index IDX_TEST_2 on TEST(a2,b,c);
>
> >
> > insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
> > insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
> > insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
> > insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
> >
> >
> > sqlite> explain query plan select * from TEST where (a = '123' or
> > a2='1234') and b = '456' and c='PP';
> >
> > 0|0|0|SCAN TABLE TEST
> >
> >
> > whereas
> >
> > sqlite> explain query plan select * from TEST where a = '123' and b =
> '456'
> > and c='PP';
> >
> > 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
> >
> > how to make this query using an index? is this possible?
> >
> > Best regards,
> > Sylvain
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] index for OR clause

[Sylvain Pointeau]

Hello,

I would like to know if this is possible to use an index in the following
case:

select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';


given the table:

create table TEST (
 a TEXT NOT NULL,
 a2 TEXT NOT NULL,
 b TEXT NOT NULL,
 c TEXT NOT NULL
);

create index IDX_TEST_1 on TEST(a,a2,b,c);

insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');


sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SCAN TABLE TEST


whereas

sqlite> explain query plan select * from TEST where a = '123' and b = '456'
and c='PP';

0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)

how to make this query using an index? is this possible?

Best regards,
Sylvain

Re: [sqlite] Variable values in Views

[Sylvain Pointeau]

It is called parameterized view in sqlserver.
Actually it is extremely useful in order to have a good reusability in the
code.
I was actually missing it in Oracle, although I found a workaround of using
the pipelined functions.

Unfortunately, it is missing in sqlite, as well as the merge statement,
also very useful (insert or replace has just to be avoided)

What I used to make is to use a shell script, and to use sed to replace my
variable before executing the script... Far from ideal but it worked ok, it
is just annoying because we have to prepare the file first.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lemon - %wildcar

[Sylvain Pointeau]

Hello,

Le samedi 26 juillet 2014, Richard Hipp  a écrit :
>
> Historical note:  I wrote Lemon back in the 80s (on a Sun3, IIRC)


You were a visionary, Lemon is probably the only viable option for a parser
generator in C/C++ at this moment.

because
> the $1,$2,$3 notation of Yacc wasn't working for me and also because Yacc
> wanted the parser to call the lexer, not the other way around, and because
> it was difficult to stop memory leaks in Yacc when there was a syntax
> error.  There was a companion LL(1) parser generator called "Lime" which
> has been lost.   Lemon was originally a bunch of separate source files,
> many of which were automatically generated.  But at some point I
> concatenated them all together into the single "lemon.c" file.  All that
> happened prior to the beginning of SQLite.  Anyhow, the current lemon.c
> code is a mess, for which I apologize.

But it does mostly work, so I've
> never gotten around to rewriting it from scratch, which is what it
> desperately needs.


Lemon is a master piece, it deserves it.
I propose my help, I am interested in the domain, but I might not be
capable enough. However I would be highly motivated if I am guided by
someone like you.


>
> The %wildcard token matches any token that cannot be matched by another
> rule in the current context.
>
>
It is exactly what I needed, I tried it and it is wonderful. I try to parse
some plsql code, but I only need to be superficial, then everything between
begin / end will be eaten by the wildcar


> I had to add the %wildcard feature to the parser generator when we added
> virtual tables to SQLite back in 2006 - in order to support the full text
> search extension.  The virtual table syntax allows one put any arbitrary
> sequence of tokens as the argument, as long as parentheses match up.
> Keywords, literals, operators - any tokens are allowed.  And the most
> memory-efficient way to do that was to enhance the parser generate with
> some special features to support it.  Hence:  "%wildcard".
>
>
I am lucky ;-)



> The lack of documentation is probably just because we never got around to
> it


> > if yes, could it be added in the documentation?
> >
>
> Do you have a patch :-)
>
>
Your documentation is among the best, I can help also there I think, how do
you want the patch to be done, I just need a little bit of explanation to
start. (I am on MacOS X)


>
> >
> > ps: I am pretty impressed by lemon, I am really having fun.
> >
> > Best regards,
> > Sylvain
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org 
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] lemon - %wildcar

[Sylvain Pointeau]

Hello,

I would like to know if %wildcard is an undocumented feature on purpose, is
this safe to use it?
if yes, could it be added in the documentation?

ps: I am pretty impressed by lemon, I am really having fun.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE & INSERT vs. REPLACE

[Sylvain Pointeau]

I second "Petite Abeille", the merge into (SQL 2003) would be more than
useful.
REPLACE should be avoided.


On Wed, May 28, 2014 at 5:08 PM, Simon Slavin  wrote:

>
> On 28 May 2014, at 3:55pm, Drago, William @ MWG - NARDAEAST
>  wrote:
>
> > Can someone tell me what an appropriate use for REPLACE is?
>
> REPLACE in SQlite is just a way of writing
>
> INSERT OR REPLACE ...
>
> It's best use is when you are not sure whether a record already exists or
> not.  And the definition of 'exists' is that the new row has data which
> clashes with an existing row by at least one UNIQUE constraint, including
> the rule that primary keys must be UNIQUE.
>
> So an example is if you have a big table of equipment, and a smaller table
> listing all equipment which is on loan.  One row for each item on loan, and
> everything not mentioned in this smaller table should be in the stock room.
>  The smaller table would have a UNIQUE key on the equipment number to
> prevent it from listing one item being on loan to two different people.
>
> If you discover that item number 515 is on loan to Barry now you need to
> make sure that Barry is listed in that table.  But you don't know whether
> you're creating a new row or replacing a row that said that the equipment
> was on loan to Amanda last week.  So you use INSERT OR REPLACE and SQLite
> works out whether it has to delete an existing row before it can insert the
> new one.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Sylvain Pointeau]

I agree with David, CTE is just wonderful, a big help to avoid re-typing
many times the same sub-query and a performance improvement as well. Yes we
can workaround it but it is ugly and leads to un-maintainable code. I am
using it every days in my job and I can just tell that it has been proven
to be really useful.

... as well as merge ...

Yes I also have the feeling that you are ditching all new SQL feature
requests, saying that it is useless because it can be done in another way.
At least I would have liked to see a little interest and understanding on
how it could be useful to developers (us!).

I know SQLite is lite, but it should look forward to the SQL 2003,
developers are using all 2003 features in their daily job (oracle or sql
server), we would like to see it as well (not all but a part of it, even
with some limitations) in our sqlite wonderful app or scripts.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL 2003 in sqlite

[Sylvain Pointeau]

Hi,

> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2
> Do you have specific requirements for the UPDATE features of MERGE ?

the issue with "insert or replace" is that it will match on primary keys,
additionally (I am not 100% sure) it deletes first the row to replace it.
we cannot update then only 1 column.

furthermore, the merge can delete as well, it does the join on the fields
we want (not only PK), it is really wonderful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL 2003 in sqlite

[Sylvain Pointeau]

Hello,

The merge statement is really missing in sqlite...
Is there any plan to integrate this SQL 2003 syntax in sqlite?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Sylvain Pointeau]

Hello,

If I would have one wish, it would not be the row level locking but the
merge syntax, so usefulf to update, insert or update in 1 command (no
insert or replace is not an equivalent), and in general it would be good to
implement the sql 2003.

Just a wish.

Best regards,
Sylvain

Le lundi 11 novembre 2013, Raheel Gupta a écrit :

> @simon
>
> I guess a Row level locking could be difficult but a Page Level locking
> could be not that difficult.
>
> ATM "db level locking" :
> If DB locked throw busy error
> In not locked lock db, let the writer do its thing
>
> For Page level locking (I think you could allow something like) :
> Let writer write / modify pages IF not locked
> ONLY If writer comes across a locked page wait for it to be released
>
> In this way, multiple threads could do writes. Again I am not an expert but
> from my little understanding this might not remove the leaness. You are the
> expert.
>
> And even I agree that Sqlite must be "lite"
>
>
>
> On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin 
> >
> wrote:
>
> >
> > On 10 Nov 2013, at 12:05pm, Raheel Gupta >
> wrote:
> >
> > >>> I can't think of any other single feature that would remove the
> "lite"
> > >
> > > I am not a database expert. If you say so, it must be the case.
> > > But if there is a way to implement concurrent writers in SQLite
> > maintaining
> > > the "lite" in SQLite, I would be the most happiest person here :)
> >
> > The main reason you seem to prefer SQLite to other databases is that it's
> > faster.  Adding row-level locking to SQLite would slow it down a lot.
>  As a
> > very simplified explanation, for one SELECT instead of
> >
> > try to lock the database
> > check to see that the lock on the database is yours
> > FOR EACH ROW:
> > figure out where the row's data is
> > read the data
> > unlock the database
> >
> > you have to do
> >
> > FOR EACH ROW:
> > figure out where the row's data is
> > try to lock the row
> > check to see that the lock on the row is yours
> > read the data
> > release the row
> >
> > If your SELECT returns 10 rows you end up doing 50 operations instead of
> > 23.  Which would mean that SQLite was half the speed, and no longer had
> any
> > advantages for you, so you would use something else.
> >
> > Locking is the single hardest thing to get right when writing a DBMS.
> >  SQLite gets a lot of its tininess and speed by implementing the simplest
> > fastest method of locking possible.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org 
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Sylvain Pointeau]

It will not be possible with SQLite but it would be possible using H2.
http://www.h2database.com/html/advanced.html#file_system

maybe you could convert the sqlite to H2 somewhere in your process?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Xcode warning on 3.7.10

[Sylvain Pointeau]

On Mon, Jan 23, 2012 at 2:50 PM, Simon Slavin  wrote:

>
> On 23 Jan 2012, at 5:49am, Tommy wrote:
>
> > After bringing the SQLite amalgamation into my library and compiling,
> > Apple Xcode produced the following warning:
> >
> > sqlite3.c:27620:32:{27620:32-27620:45}{27620:30-27620:31}: warning:
> > implicit conversion from 'long long' to 'long' changes value from
> > 9223372036854775807 to -1 [-Wconstant-conversion,3]
> >
>
> Which version of Xcode.  Which version of the OS ?
> Which target platform (by which I mean hardware) do you have the project
> set for ?  32-bit or 64-bit ?
> Are you compiling on an Intel Mac ? (just in case you're still using PPC)
>
>
Are you using Clang?
is SQLite compatible with Clang?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

[Sylvain Pointeau]

On Tue, Oct 18, 2011 at 8:30 AM, Nick Gammon <n...@gammon.com.au> wrote:

>
> On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote:
>
> > Is it normal that fromuid of the table exits is STRING ?
> > I think it should be TEXT to be surely processed as text and not float
>
> That was an error. However it shouldn't take SQLite 2.5 seconds to handle
> *any* numeric literal. Especially as it was quoted. For example, in C you
> don't expect:
>
> x = "123E45678942";
>
> ... to go through any sort of numeric conversion. Now I know this isn't C,
> but the "let's see if we can turn a string into a number, and take two to
> three seconds to do so" is not right, IMHO.
>
>
it has something to do with type affinity.
I don't think you would have had this slow down if your type was TEXT,
because (I think) sqlite would have not tried to convert it.
Anyway seems to be improved now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

[Sylvain Pointeau]

Is it normal that fromuid of the table exits is STRING ?
I think it should be TEXT to be surely processed as text and not float
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing CSV data on command-line?

[Sylvain Pointeau]

>
> I have got to stop working until 3:00am.  Your suggestion points to what I
> did not verify.  The original CSV is much richer with double quoting some
> values.  The following shows that setting the separator as I was within the
> shell does not do what I was expecting:
>
>
I made one that I am using since 3 years now.
it is a command line tool that (should) work(s) everywhere, tested on
windows and macosx.
https://gitorious.org/sylisa-csv2db

I hope it helps.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Java

[Sylvain Pointeau]

> my program language is Java. And the www.sqlite.org not supply API for
Java. What should I do?

I think this one is the best
http://code.google.com/p/sqlite-jdbc/

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems on importing quoted csv files

[Sylvain Pointeau]

I made one, as a command line tool.

the announcement:
http://spointeau.blogspot.com/2011/03/sylisa-csv2db-is-born.html

the sources:
https://gitorious.org/sylisa-csv2db/sylisa-csv2db


Another option, you can also use the shell (in python) apsw
http://apidoc.apsw.googlecode.com/hg/index.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ Sample Code

[Sylvain Pointeau]

I wrote one wrapper, that can be compiled, like sqlite3, in your C++
program.
It doesn't rely on any third library except sqlite3.
The update of sqlite3 is just done by replacing the amalgamation files.

https://gitorious.org/sylisa-dblite/sylisa-dblite

It uses the syntax borrowed from soci++, very convenient for the code
readability.

best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

[Sylvain Pointeau]

Hello,

I just tried APSW on max os x,
but how to get the auto-completion to work?
it does not work for me...

Best regards,
Sylvain

On Sat, Apr 9, 2011 at 11:50 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/09/2011 02:03 PM, Edzard Pasma wrote:
> > I just made an alias in the shell (Unix-) for this tool:
> >
> > alias apsw='python -c "import apsw;apsw.main()"'
>
> I keep trying to make it so you can do "python -m apsw " and the code
> is
> structured to allow that, but in my investigations it seems like Python
> internals will only do it for a module written in Python and not one
> written
> in C.
>
> > Also reported an issue
>
> Anyone who is interested can follow along at the following link.  The APSW
> shell supports a superset of SQLite shell's invocation and hence the
> behaviours are a little inconsistent:
>
>  http://code.google.com/p/apsw/issues/detail?id=115
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk2g1JgACgkQmOOfHg372QTjRQCcDVMaLLR27OQWCVI/4R7yTVZ3
> j/sAoK8RD+R6VextcxboId7GI9O1jNRY
> =QjF4
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.7.5

[Sylvain Pointeau]

Any news on System.Data.SQLite.org ?

It was a so great news!
When are the releases planned?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Sylvain Pointeau]

I agree with this change because it doesn't change all well written
programs.
It just tries to correct all incorrectly written ones, which is a good thing
for the end-users.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Sylvain Pointeau]

I think you can hide those details,
you can perfectly retrieve all rows (or bunch of rows) and then do not make
any network access, but still simulate the sqlite API.

I would keep the connection open, it doesn't block other clients to connect
as well, just the server has to be multi-process.

the idea behind is to keep the existing code and make your application
working with a database located on a server. If you want to go to a real
database server, why not going directly to PostgreSQL?

Best regards,
Sylvain

On Wed, Dec 22, 2010 at 11:52 PM, Doug <pa...@poweradmin.com> wrote:

> This is where I think the problems will crop up.  Can you imagine making a
> network round trip for each row fetch,
> and then for each column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and bring it
> back to the client.  And if the response is
> large, will you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)
>
> Doug
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
> Sent: Wednesday, December 22, 2010 7:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite server
>
> Why not doing it with DCOM or Corba or what ever even the sockets?
> but hidden behind the same API of SQLite. The "real" sqlite lib will be on
> the server.
> is it called "remote procedure call"?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Sylvain Pointeau]

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The "real" sqlite lib will be on
the server.
is it called "remote procedure call"?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Sylvain Pointeau]

and what about using a DCOM like technology to open a distant database?
the sqlite API will stay the same but behind the scene, it will access your
server using a DCOM like technology?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Sylvain Pointeau]

What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Sylvain Pointeau]

... and if you use the "unix-dotfile" as the VFS name in your open call.
Does it work?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Sylvain Pointeau]

sorry yes I didn't compile with SQLITE_DEBUG
I just did it, and I have the same error:

$ /usr/local/bin/sqlite3 tst.db3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ./testSchema.sql
sqlite> .read ./TestValues.sql
sqlite> .read ./TestQuery.sql
Assertion failed: (memIsValid([i])), function sqlite3VdbeExec,
file sqlite3.c, line 64507.
Abort trap


On Sat, Dec 4, 2010 at 11:20 AM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> I used:
> CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64'
> ./configure --disable-dependency-tracking
>
> my compiler is:
> $ gcc --version
> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
> Copyright (C) 2007 Free Software Foundation, Inc.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Sylvain Pointeau]

I used:
CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64'
./configure --disable-dependency-tracking

my compiler is:
$ gcc --version
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
Copyright (C) 2007 Free Software Foundation, Inc.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Sylvain Pointeau]

Hi,

I am on macosx sqlite 3.7.3

$ /usr/local/bin/sqlite3 test.db3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ./testSchema.sql
sqlite> .read ./TestValues.sql
sqlite> .read ./TestQuery.sql
Otterbourne Golf Course|Jane Doe|Assumed Name

it works for me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and multiple writers?

[Sylvain Pointeau]

Are you sure that you are not keeping a transaction in the message sender?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compile SQlite 32 and 64 bits universal with ICU support on MacOSX

[Sylvain Pointeau]

Hello,

I met today a difficult situation where I wanted to have SQLite 32 and 64
bits with the ICU support,
Unfortunately ICU does not support the universal 32 and 64 bits...
but using lipo and a bit of manual work, we have it working!

I wrote the detailed procedure on my blog
http://spointeau.blogspot.com/2010/08/sqlite-with-icu-on-macosx-for-32-and-64.html

Best regards,
Sylvain (happy!)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

I learned something today :-)
I added /usr/local/lib to my /etc/ld.so.conf
then run "sudo ldconfig"
then I could use the latest sqlite3 in my /usr/local/ directory
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

... my fault, the programs are taking the lib from /usr/lib before
/usr/local/lib
it is not doing this way on my macosx, I am searching now how to specify to
take first the /usr/local/lib when executing, even for
/usr/local/bin/sqlite3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

I just installed the sources (not the amalgamation ones) and it works fine.
what's wrong with the amalgamation package?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

I don't know if it is important, but I am running ubuntu 10.04 64 bit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

I made a small c program for testing the libs...

#include 
#include 


int main()
{
printf("version %s\n",sqlite3_libversion());
return 0;
}


it returns 3.6.22

why do I compile and install a previous version?
(is it linked to ubuntu?)

best regards,
Sylvain





>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

[Sylvain Pointeau]

Hi,

I downloaded, compiled and installed the sqlite3 3.7.01 from the sources
(amalgamation).
however I have the sqlite3 version 3.6.22 installed in /usr/local/bin when I
do "make install"

>.libs/sqlite3 --version
3.6.22

however something strange, it creates a sqlite3 on the folder (same level as
./configure) which has the right version 3.7.01

what should I do then?
it seems that something is not correct, I don't know if it is in the
compilation or in the install script.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing an sqlite db from two different programs

[Sylvain Pointeau]

to not block all users with updates.

On Tue, Jun 29, 2010 at 6:54 PM, Greg Burd <greg.b...@oracle.com> wrote:

> Why "the smallest update possible"?
>
> -grge
>
> > -----Original Message-
> > From: Sylvain Pointeau [mailto:sylvain.point...@gmail.com]
> > Sent: Monday, June 28, 2010 6:36 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Accessing an sqlite db from two different
> > programs
> >
> > absolutely no problem with sqlite.
> > ensure you are doing smallest update as possible.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing an sqlite db from two different programs

[Sylvain Pointeau]

absolutely no problem with sqlite.
ensure you are doing smallest update as possible.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Qt

[Sylvain Pointeau]

On Wed, Jun 16, 2010 at 3:42 PM, Sam Carleton 
>
> The error was between the keyboard and the chair...  Once the SQLite
> DLL was moved into the Qt bin directory, all worked as expected.
>
> :-)


> Thus the answer to my question would seem to be:  Qt will simply use
> the SQLite3.lib you give it, if it is to link to a DLL, you need to
> make sure Qt can find the DLL.
>
> sure, I am happy that your problem is solved.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking issue on NFS filesystem

[Sylvain Pointeau]

> int sqlite3_open_v2(
>  const char *filename,   /* Database filename (UTF-8) */
>  sqlite3 **ppDb, /* OUT: SQLite db handle */
>  int flags,  /* Flags */
>  const char *zVfs/* Name of VFS module to use */
> );
>
> With the last argument "unix-dotfile".
>
>
Does it work?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Qt

[Sylvain Pointeau]

Hi,

What is the error?
did you make a sample project that you could share?

Best regards,
Sylvain

On Tue, Jun 15, 2010 at 1:11 AM, Sam Carleton <scarle...@miltonstreet.com>wrote:

> Sylvain,
>
> I have given it a try and it isn't working.  First a quick questions:
> Is qsqlite.dll statically linked to the sqlite code or dynamically
> linked?  The sqlite3.lib I am trying to link to is the for dynamically
> linking, aka requiring the sqlite3.dll.  I am guessing that is the
> issue, but since it will take me a while to spin up a statically
> linked version of the lib, I thought I might ask before running down
> that rat whole;)
>
> Sam
>
> On Wed, Jun 9, 2010 at 4:20 AM, Sylvain Pointeau
> <sylvain.point...@gmail.com> wrote:
> >
> > build your qt sqlite as a plugin
> > then recompile this plugin with the version you want.
> >
> >
> http://doc.trolltech.com/4.6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above
> >
> > best regards,
> > Sylvain
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Qt

[Sylvain Pointeau]

build your qt sqlite as a plugin
then recompile this plugin with the version you want.

http://doc.trolltech.com/4.6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above

best
regards,
Sylvain

On Wed, Jun 9, 2010 at 4:32 AM, Sam Carleton  wrote:

> On Tue, Jun 8, 2010 at 10:25 PM, Bill King  wrote:
>
> >
> > >
> > >1. How do I control the version of SQLite used with Qt?
> > >
> > ./configure -system-sqlite will use the sqlite compiled for your system.
> >
>
> No, not how do I compile SQLite into Qt, I have done that.  I want to
> control the VERSION.  I don't know if the distro of Qt is using the latest
> version of SQLite or not.  Also, if I opt to NOT upgrade the Apache server,
> I would like Qt to use the same version of SQLite that the Apache server is
> using ;)
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] issue with sqlite + user function + load dll implemented with Qt

[Sylvain Pointeau]

a little more details on how I use the extension with sqlite3:

In my shell script, I am running sqlite3 on all files of a folder:

find "./scripts" -iname "*.sql" | while read sqlfile
do
echo " -- ${sqlfile}"
/usr/local/bin/sqlite3 mydb.db3 < $sqlfile;
done

and in my sql script, I am loading the dll extension made with Qt:

.load ../../libsqliteextensions.dylib

The compilation of the dll goes like:

g++ -c -pipe -g -gdwarf-2 -arch x86_64 -Xarch_x86_64
-mmacosx-version-min=10.5 -Wall -W -fPIC -DQT_CORE_LIB -DQT_SHARED
-I/usr/local/Trolltech/Qt-4.7.0/mkspecs/macx-g++ -I.
-I/usr/local/Trolltech/Qt-4.7.0/lib/QtCore.framework/Versions/4/Headers
-I/usr/local/Trolltech/Qt-4.7.0/include/QtCore
-I/usr/local/Trolltech/Qt-4.7.0/include -Icompil/moc
-F/usr/local/Trolltech/Qt-4.7.0/lib -o compil/obj/sqliteextensions.o
src/sqliteextensions.cpp

g++ -headerpad_max_install_names -arch x86_64 -Xarch_x86_64
-mmacosx-version-min=10.5 -single_module -dynamiclib -compatibility_version 1.0
-current_version 1.0.0 -install_name libsqliteextensions.1.dylib -o
libsqliteextensions.1.0.0.dylib compil/obj/sqliteextensions.o
 -F/usr/local/Trolltech/Qt-4.7.0/lib -L/usr/local/Trolltech/Qt-4.7.0/lib
-L/usr/local/lib/ -framework QtCore -L/usr/local/Trolltech/Qt-4.7.0/lib

I hope that it makes thing clearer.

Best regards,
Sylvain


On Tue, May 18, 2010 at 7:40 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> hello,
>
> I am facing a very bizarre issue.
> I am on mac os x 64 bits.
>
> I needed a uuid() function that generates GUID.
> I created then a dll with Qt like:
>
> #include "/usr/local/include/sqlite3ext.h"
> SQLITE_EXTENSION_INIT1
>
> #include 
> #include 
>
> extern "C" void sqlite3_uuid(sqlite3_context *context, int argc,
> sqlite3_value **argv){
>  QUuid uuid = QUuid::createUuid();
>  QString s = uuid;
>  s.remove(0,1);
>  s.remove(s.size()-1,1);
>  QByteArray to_utf8 = s.toAscii();
>  sqlite3_result_text( context, to_utf8.constData(), -1, SQLITE_TRANSIENT
> );
> }
>
> extern "C" int sqlite3_extension_init(
>sqlite3 *db,
>char **pzErrMsg,
>const sqlite3_api_routines *pApi
> ){
> SQLITE_EXTENSION_INIT2(pApi)
>  sqlite3_create_function(db, "uuid", 0, SQLITE_UTF8, 0, sqlite3_uuid, 0,
> 0);
>  return 0;
> }
>
> I compiled Qt with cocoa
>
> then after when I run a script, only in a shell, when I first try to insert
> a GUID, it says that the primary key already exists, which is obviously
> wrong.
> However, this error occurs only in a shell script, not in the command line
> (unbelievable!).
>
> I could only fallback to use uuid from boost and it works perfectly.
>
> Is it safe to use Qt to implement a dll for creating user functions?
> Do you have any idea on what happens here? (any hints?)
>
> I have no idea how to narrow down the problem,
> is someone interested to investigate? if yes I can build a minimal project
> (and try to reproduce the same issue)
>
> Any hints are warmly welcome.
>
> Best regards,
> Sylvain
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users