Re: [sqlite] sqlite-users Digest, Vol 147, Issue 13

[Balaji Ramanathan]

Thank you, thank you!

Balaji Ramanathan
-- Forwarded message --
From: Richard Hipp 
To: General Discussion of SQLite Database <
sqlite-users@mailinglists.sqlite.org>
Cc:
Bcc:
Date: Thu, 12 Mar 2020 16:17:59 -0400
Subject: [sqlite] New SQLite Forum established - this mailing list is
deprecated
I have set up an on-line forum as a replacement for this mailing list:

https://sqlite.org/forum
https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the
forum will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.  See the
second link above for more information.

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

Re: [sqlite] SQLite forum posts are about the forum, not SQLite

[Huỳnh Trần Khanh]

That should be. Also as stated in a previous email, I want to interact
with the forum via email also. I mean, I have been using web forums
for a long time and I find the UX _really,_ really lacking. GitHub
allows me to comment on issues by email, which is more convenient for
me.

On Fri, Mar 13, 2020 at 8:47 PM Dominique Devienne  wrote:
>
> I hope that's only a temporary situation... --DD
> ___
> 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] SQLite forum posts are about the forum, not SQLite

[Dominique Devienne]

I hope that's only a temporary situation... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Stefan Sperling]

On Wed, Mar 11, 2020 at 08:20:01AM -0400, Richard Hipp wrote:
> On 3/11/20, Stefan Sperling  wrote:
> >
> > Does this help?
> >
> 
> Studying the trace output makes me think this is the same problem that
> came up on the s390 hardware and was fixed here:
> https://www.sqlite.org/src/info/04885763c4cd00cb

Yes, this fixes it. Thank you!

I will get this patch merged into the OpenBSD port of sqlite.
I suppose once the next sqlite release comes around we will be able to
drop the patch again since it will already be included in the release.

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

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Richard Hipp]

On 3/11/20, Stefan Sperling  wrote:
>
> Does this help?
>

Studying the trace output makes me think this is the same problem that
came up on the s390 hardware and was fixed here:
https://www.sqlite.org/src/info/04885763c4cd00cb

Stefan:  Can you verify that the patch above fixes the problem?  If
you want, you can click on the "Tarball" link to get a complete
tarball of check-in 04885763c4cd00cb (which, by coincidence was the
first check-in *after* the 3.31.1 release), unpack the tarball, then
do "./configure && make sqlite3.c" to generate the "sqlitle3.c" and
"sqlite3.h" files.  Or, you can update the "sqlite3.c" file you are
currently using manually entering the 3-line patch shown.

Please let us know what you find.

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

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Richard Hipp]

On 3/11/20, Stefan Sperling  wrote:
>
> Does this help?
>

It does help some.  But I need a good copy of the database schema,
which I don't have.

Please bring up the database file in the "sqlite3" command-line shell,
then enter the command ".fullschema".  Save off the results and send
them to me, please.

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

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Stefan Sperling]

On Tue, Mar 10, 2020 at 06:42:18PM +0100, Stefan Sperling wrote:
> On Tue, Mar 10, 2020 at 01:21:34PM -0400, Richard Hipp wrote:
> > Set a breakpoint on the sqlite3VdbeExec() function that fires when the
> > statement that is crashing is first executed.  (You can determine that
> > it is the correct statement by looking at the value of p->zSql.)  Then
> > do:
> > 
> >  set p->db->flags = p->db->flags | ((0x060)<<32)
> > 
> > That will turn on bytecode listing and tracing, and might provide
> > further clues.  Please record and send in the trace.
> 
> I will look into this. Thanks!

Does this help?

$ egdb --args svn up<
GNU gdb (GDB) 7.12.1
Copyright (C) 2017 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "sparc64-unknown-openbsd6.6".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
.
Find the GDB manual and other documentation resources online at:
.
For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from svn...done.
(gdb) br sqlite3.c:83210 if p->zSql && strncmp(p->zSql, "INSERT OR REPLACE 
INTO", 22) == 0
No source file named sqlite3.c.
Make breakpoint pending on future shared library load? (y or [n]) y
Breakpoint 1 (sqlite3.c:83210 if p->zSql && strncmp(p->zSql, "INSERT OR REPLACE 
INTO", 22) == 0) pending.
(gdb) run
Starting program: /usr/local/bin/svn up
Updating '.':

Breakpoint 1, sqlite3Step (p=0xd69cb26608) at sqlite3.c:83210
83210   rc = sqlite3VdbeExec(p);
(gdb) set p->db->flags = p->db->flags | (((unsigned long long)0x060)<<32)
(gdb) p /x p->db->flags
$1 = 0x60e004a0e0
(gdb) c
Continuing.
SQL: [INSERT OR REPLACE INTO nodes (   wc_id, local_relpath, op_depth, 
parent_relpath, repos_id, repos_path,   revision, presence, depth, kind, 
changed_revision, changed_date,   changed_author, checksum, properties, 
translated_size, last_mod_time,   dav_cache, symlink_target, file_external, 
moved_to, moved_here,   inherited_props) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 
?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, 
?22, ?23) ]
VDBE Program Listing:
   0 Init 0  1400   00 Start at 140
   1 OpenWrite0   160 2300 root=16 iDb=0; NODES
   2 OpenWrite1   290 k(4)  00 root=29 iDb=0; I_NODES_MOVED
   3 OpenWrite2   270 k(5,) 00 root=27 iDb=0; I_NODES_PARENT
   4 OpenWrite3   170 k(3,,,)   00 root=17 iDb=0; 
sqlite_autoindex_NODES_1
   5 Variable 120 ?100 r[2]=parameter(1,?1)
   6 Variable 230 ?200 r[3]=parameter(2,?2)
   7 Variable 340 ?300 r[4]=parameter(3,?3)
   8 Variable 450 ?400 r[5]=parameter(4,?4)
   9 Variable 560 ?500 r[6]=parameter(5,?5)
  10 Variable 670 ?600 r[7]=parameter(6,?6)
  11 Variable 780 ?700 r[8]=parameter(7,?7)
  12 Variable 890 ?800 r[9]=parameter(8,?8)
  13 Variable22   100 ?22   00 r[10]=parameter(22,?22)
  14 Variable21   110 ?21   00 r[11]=parameter(21,?21)
  15 Variable10   120 ?10   00 r[12]=parameter(10,?10)
  16 Variable15   130 ?15   00 r[13]=parameter(15,?15)
  17 Variable 9   140 ?900 r[14]=parameter(9,?9)
  18 Variable14   150 ?14   00 r[15]=parameter(14,?14)
  19 Variable19   160 ?19   00 r[16]=parameter(19,?19)
  20 Variable11   170 ?11   00 r[17]=parameter(11,?11)
  21 Variable12   180 ?12   00 r[18]=parameter(12,?12)
  22 Variable13   190 ?13   00 r[19]=parameter(13,?13)
  23 Variable16   200 ?16   00 r[20]=parameter(16,?16)
  24 Variable17   210 ?17   00 r[21]=parameter(17,?17)
  25 Variable18   220 ?18   00 r[22]=parameter(18,?18)
  26 Variable20   230 ?20   00 r[23]=parameter(20,?20)
  27 Variable23   240 ?23   00 r[24]=parameter(23,?23)
  28 NewRowid 010   00 r[1]=rowid
  29 HaltIfNull129922 NODES.wc_id   01 if r[2]=null halt
  30 HaltIfNull129923 NODES.local_relpath 01 if r[3]=null halt
  31 HaltIfNull129924 NODES.op_depth 01 if r[4]=null halt
  32 HaltIfNull129929 NODES.presence 01 if r[9]=null halt
  33 HaltIfNull12992   12 N

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Den 2020-03-10 kl. 12:31, skrev Daniel Polski:

Den 2020-03-10 kl. 02:33, skrev Rowan Worth:
On Mon, 9 Mar 2020 at 23:22, Daniel Polski  
wrote:



Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to 
figure

out when the problem starts.
I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of
[3bfa9cc97d]
WARNING: SQLITE error code: 14 os_unix.c:36982: (40) 
openDirectory(/tmp) -



errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

    ELOOP  Too many symbolic links were encountered in resolving
  pathname,  or  O_NOFOLLOW  was
   specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW 
unconditionally in

openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f


/tmp/ is a directly mounted ramdisk.



Sorry I was wrong.

/tmp/ actually IS a symlink to /var/volatile/tmp

Best regards,


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

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Dominique Pellé]

Stefan Sperling wrote:

> > Does valgrind give any clues?
>
> Valgrind does not run on the OpenBSD/sparc64 platform, unfortunately.

Would the address sanitizer (i.e. gcc -fsanitize=address, or
clang -fsanitize=address) work on OpenBSD/sparc64?

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

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Stefan Sperling]

On Tue, Mar 10, 2020 at 01:21:34PM -0400, Richard Hipp wrote:
> On 3/10/20, Stefan Sperling  wrote:
> > The query being executed is "STMT_INSERT_NODE":
> > -- STMT_INSERT_NODE
> > INSERT OR REPLACE INTO nodes (
> >   wc_id, local_relpath, op_depth, parent_relpath, repos_id, repos_path,
> >   revision, presence, depth, kind, changed_revision, changed_date,
> >   changed_author, checksum, properties, translated_size, last_mod_time,
> >   dav_cache, symlink_target, file_external, moved_to, moved_here,
> >   inherited_props)
> > VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14,
> > ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)
> 
> It would be great if you could send us the database schema - or even
> the whole database if it isn't too big.
>
> Probably if I have the schema I will be able to repro the problem.

The schema is public:
https://svn.apache.org/repos/asf/subversion/branches/1.13.x/subversion/libsvn_wc/wc-metadata.sql

> Is this only happening on Sparc, and not on x64 or ARM or PPC, etc?

It looks like it is specific to sparc64 so far.
I cannot reproduce it on amd64 with the same software versions.
I do not have access to arm/ppc machines at present.

> Does valgrind give any clues?

Valgrind does not run on the OpenBSD/sparc64 platform, unfortunately.

> Something else that might be helpful, if you have time:
> 
> Set a breakpoint on the sqlite3VdbeExec() function that fires when the
> statement that is crashing is first executed.  (You can determine that
> it is the correct statement by looking at the value of p->zSql.)  Then
> do:
> 
>  set p->db->flags = p->db->flags | ((0x060)<<32)
> 
> That will turn on bytecode listing and tracing, and might provide
> further clues.  Please record and send in the trace.

I will look into this. Thanks!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Richard Hipp]

On 3/10/20, Stefan Sperling  wrote:
> The query being executed is "STMT_INSERT_NODE":
> -- STMT_INSERT_NODE
> INSERT OR REPLACE INTO nodes (
>   wc_id, local_relpath, op_depth, parent_relpath, repos_id, repos_path,
>   revision, presence, depth, kind, changed_revision, changed_date,
>   changed_author, checksum, properties, translated_size, last_mod_time,
>   dav_cache, symlink_target, file_external, moved_to, moved_here,
>   inherited_props)
> VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14,
> ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)

It would be great if you could send us the database schema - or even
the whole database if it isn't too big.  You can send to my private
email if you like.

Probably if I have the schema I will be able to repro the problem.

Is this only happening on Sparc, and not on x64 or ARM or PPC, etc?

Does valgrind give any clues?

Something else that might be helpful, if you have time:

Set a breakpoint on the sqlite3VdbeExec() function that fires when the
statement that is crashing is first executed.  (You can determine that
it is the correct statement by looking at the value of p->zSql.)  Then
do:

 set p->db->flags = p->db->flags | ((0x060)<<32)

That will turn on bytecode listing and tracing, and might provide
further clues.  Please record and send in the trace.

-- 
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] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

[Stefan Sperling]

Hi,

We have seen sqlite segfault on OpenBSD/sparc64 in the context
of running Subversion's regression test suite:
https://ci.apache.org/builders/svn-bb-openbsd/builds/498

The log files show that a simple 'svn update' triggers the problem:
https://ci.apache.org/builders/svn-bb-openbsd/builds/498/steps/Cleanup/logs/stdio
https://ci.apache.org/builders/svn-bb-openbsd/builds/498/steps/svn/logs/stdio

The trace within sqlite for this crash looks like :

Reading symbols from svn...(no debugging symbols found)...done.
[New process 486437]
Core was generated by `svn'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0  sqlite3VdbeRecordUnpack (pKeyInfo=0x4868586788, nKey=-538976289, 
pKey=0xdfdfdfdfdfdfdfdf, p=0x48d0b05008) at sqlite3.c:81298
81298 idx = getVarint32(aKey, szHdr);
(gdb) bt
#0  sqlite3VdbeRecordUnpack (pKeyInfo=0x4868586788, nKey=-538976289, 
pKey=0xdfdfdfdfdfdfdfdf, p=0x48d0b05008) at sqlite3.c:81298
#1  0x004951fca230 in sqlite3VdbeExec (p=0x48a9ed9208) at sqlite3.c:89382
#2  0x004951fcda40 in sqlite3Step (p=) at sqlite3.c:83210
#3  sqlite3_step (pStmt=0x48a9ed9208) at sqlite3.c:17739


Note that on OpenBSD 0xdfdfdfdfdfdfdfdf is written to memory which
has been freed.

With sqlite compiled with debugging enabled the problem triggers a
few lines f code earlier and manifests itself as an assertion failure:

#1  0x00e27e089f48 in *_libc___assert2 (file=0xe2eb9d90e0 "sqlite3.c",
line=89376, func=0xe2eb9ec9d8 <__func__.59513> "sqlite3VdbeExec",
failedexpr=0xe2eb9ee570 "pIn3->flags & MEM_Blob")
at /usr/src/lib/libc/gen/assert.c:52
#2  0x00e2eb72d42c in sqlite3VdbeExec (p=0xe2a3d0a408) at sqlite3.c:89376


I have been trying to isolate the problem for a couple of hours and
don't believe I'll get much further without help.

What's going on in Subversion is that a 'base node' row is being
inserted into the NODES table in the working copy database.
The query being executed is "STMT_INSERT_NODE":
-- STMT_INSERT_NODE
INSERT OR REPLACE INTO nodes (
  wc_id, local_relpath, op_depth, parent_relpath, repos_id, repos_path,
  revision, presence, depth, kind, changed_revision, changed_date,
  changed_author, checksum, properties, translated_size, last_mod_time,
  dav_cache, symlink_target, file_external, moved_to, moved_here,
  inherited_props)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14,
?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)

The input values bound to this query by Subversion all seem fine.

I also know that Subversion was working fine before I upgraded this
sparc64 machine to a newer OpenBSD -current snapshot on March 8, at
which point among many unrelated updates the sqlite package on the
system was upgraded from 3.30.1 to 3.31.1. As you can see on this page,
builds were green before I upgraded the system:
https://ci.apache.org/builders/svn-bb-openbsd

Below is a full back trace with debug symbols, compiled from the
sqlite-autoconf-3310100 release. At the end I've included values from
some data structures that seem relevant.

Is this enough information? I can reproduce the problem reliably,
so if you need more information just let me know what you want to see.

Cheers,
Stefan


This trace is from 'svn up' in a working copy of svn.apache.org's
"repos/asf/apr/trunk" folder, which I happened to have available.

(gdb) bt
#0  *_libc_abort () at /usr/src/lib/libc/stdlib/abort.c:57
#1  0x00e27e089f48 in *_libc___assert2 (file=0xe2eb9d90e0 "sqlite3.c",
line=89376, func=0xe2eb9ec9d8 <__func__.59513> "sqlite3VdbeExec",
failedexpr=0xe2eb9ee570 "pIn3->flags & MEM_Blob")
at /usr/src/lib/libc/gen/assert.c:52
#2  0x00e2eb72d42c in sqlite3VdbeExec (p=0xe2a3d0a408) at sqlite3.c:89376
#3  0x00e2eb71a370 in sqlite3Step (p=0xe2a3d0a408) at sqlite3.c:83210
#4  0x00e2eb71a894 in sqlite3_step (pStmt=0xe2a3d0a408) at sqlite3.c:83275
#5  0x00e30cfff5fc in svn_sqlite__step (got_row=0xfffeb734,
stmt=0xe332deb910) at subversion/libsvn_subr/sqlite.c:347
#6  0x00e30cfff6d8 in svn_sqlite__insert (row_id=0x0, stmt=0xe332deb910)
at subversion/libsvn_subr/sqlite.c:371
#7  0x00e2896d9034 in insert_base_node (pibb=0xfffeb9a0,
wcroot=0xe2754a2d60, local_relpath=0xe27a8481a1 "file_io/os2",
scratch_pool=0xe27a848028) at subversion/libsvn_wc/wc_db.c:812
#8  0x00e2896dd1a4 in svn_wc__db_base_add_directory (db=,
local_abspath=0xe27a848188 "/home/stsp/src/apr-trunk/file_io/os2",
wri_abspath=,
repos_relpath=0xe27a8482d8 "apr/apr/trunk/file_io/os2",
repos_root_url=0xe2754a7628 "https://svn.apache.org/repos/asf";,
repos_uuid=0xe2754a7650 "13f79535-47bb-0310-9956-ffa450edef68",
revision=1875042, props=0xe27a848890, changed_rev=1866019,
changed_date=1566976038342098, changed_author=0xe27a848ba8 "jorton",
children=0x0, depth=svn_depth_empty, dav_cache=0x0, update_actual_props=1,
new_actual_props=0xe27a848988, new_iprops=0x0, conflict=0x0,
work_items=0x0, scratch_p

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Den 2020-03-10 kl. 02:33, skrev Rowan Worth:

On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:


Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to figure
out when the problem starts.
I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of
[3bfa9cc97d]
WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -


errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

ELOOP  Too many symbolic links were encountered in resolving
  pathname,  or  O_NOFOLLOW  was
   specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f


/tmp/ is a directly mounted ramdisk.


Probably a bug? The changelog for sqlite 3.31.0 include this which is
likely related:

- * Add the SQLITE_OPEN_NOFOLLOW
 option to
sqlite3_open_v2()  that prevents
SQLite from opening symbolic links.


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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Graham Holden]

Tuesday, March 10, 2020, 1:33:13 AM, Rowan Worth  wrote:

> On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

>> Updated to 3.31.1 but my application started spitting out an error when
>> opening the database, so I tested some earlier sqlite versions to figure
>> out when the problem starts.
>> I don't get the message in versions <= 3.30.1.
>>
>> (from the applications log)
>> SQLite Version: 3.31.0
>> INFO: Database opened: /tmp/database.sqlite
>> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
>> [3bfa9cc97d]
>> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>>

> errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

>ELOOP  Too many symbolic links were encountered in resolving
>  pathname,  or  O_NOFOLLOW  was
>   specified but pathname was a symbolic link.

> Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
> openDirectory() since this checkin:

> https://www.sqlite.org/src/info/6a64fb6a2da6c98f

> Probably a bug? The changelog for sqlite 3.31.0 include this which is
> likely related:

> - * Add the SQLITE_OPEN_NOFOLLOW
>  option to
> sqlite3_open_v2()  that prevents
> SQLite from opening symbolic links.

Assuming /tmp IS a symlink, then since it's the parent directory of
the SQLite file that openDirectory fails on, I would hazard a wild
guess that this has something to do with SQLite's handling of journal
files, about which that above check-in comment notes "O_NOFOLLOW is
always included in open() system calls for journal files".

Graham Holden

(There were an earlier pair of emails to the list on 13th Feb this
year where use of O_NOFOLLOW was triggering ELOOP, but I suspect that
wasn't directly related as the problem there was "protection" code in
SQLite that was opening /dev/null was failing because /dev/null under
Solaris was itself a symbolic link (see
https://www.sqlite.org/src/timeline?c=0c683c43a62fe25c)


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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Rowan Worth]

On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

> Updated to 3.31.1 but my application started spitting out an error when
> opening the database, so I tested some earlier sqlite versions to figure
> out when the problem starts.
> I don't get the message in versions <= 3.30.1.
>
> (from the applications log)
> SQLite Version: 3.31.0
> INFO: Database opened: /tmp/database.sqlite
> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
> [3bfa9cc97d]
> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>

errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

   ELOOP  Too many symbolic links were encountered in resolving
 pathname,  or  O_NOFOLLOW  was
  specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f

Probably a bug? The changelog for sqlite 3.31.0 include this which is
likely related:

- * Add the SQLITE_OPEN_NOFOLLOW
 option to
sqlite3_open_v2()  that prevents
SQLite from opening symbolic links.

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

[sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Updated to 3.31.1 but my application started spitting out an error when 
opening the database, so I tested some earlier sqlite versions to figure 
out when the problem starts.

I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of 
[3bfa9cc97d]

WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -

It seems related to opening the WAL file. Anything I should change when 
using sqlite >= 3.31.0 ?


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

[sqlite] sqlite CLI .dbinfo not working

[Holm, Thomas (Integration and Application Centers)]

Hi,

I have downloaded the amalgamation source code and are trying to build the CLI 
but I can´t get the .dbinfo to work.

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .dbinfo
the ".dbinfo" command requires the -DSQLITE_ENABLE_DBPAGE_VTAB compile-time 
options
sqlite>

I have tried to add option DSQLITE_ENABLE_DBPAGE_VTAB and rebuilt, but with no 
success.
Also I have downloaded the Precompiled Binaries for Windows, but the CLI 
included gives the same result.

Anyone who knows how to get the .dbinfo command working?

Regards
Thomas Holm

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

Re: [sqlite] sqlite-users Digest, Vol 146, Issue 27

[Balaji Ramanathan]

On Thu, Feb 27, 2020 at 6:00 AM <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

>   18. Cannot export 'sqlite3' file to CSV (Rachael Courts)
> From: Rachael Courts 
> To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
> Cc:
> Bcc:
> Date: Thu, 27 Feb 2020 01:05:50 +
> Subject: [sqlite] Cannot export 'sqlite3' file to CSV
> Hi All,
>
> I'm a couple of months into my PhD, studying bioacoustics. I am using
> SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files
> to import into MATLAB for further analyses.
>
> Currently I have attempted the data export function, selecting which
> database and table I would like to export, however no option for 'CSV'
> exportation is available. The only options that appear are 'JSON, SQL,
> HTML, and PDF' which can't be read into excel.
>
> I have also attempted the queries tab in SQLiteStudio, in an attempt to
> write code for exportation. This did not work.
>
> I also attempted opening the command window on my computer, to change the
> '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have
> a '.db' attached, and therefore will not open in the command window for
> editing.
>
> I'm seeking ANY advice on how to export these files into CSV, so they can
> be read in excel, and easily imported into MATLAB.
>
> I've attached an example file, if this helps.
>
> I hope to hear from someone soon,
> Rachael
>

I am not sure exactly what you are trying to do, but SQLiteStudio does
provide you the ability to export as CSV.  Are you trying to export just
the table or the table + table indexes + table triggers?  CSV export is
supported under Data ->Export only for table export (not for table +
indexes or table + triggers, etc.).

If you are trying to export the whole database, it is best to think of
a database with multiple tables as a spreadsheet with multiple tabs.  You
can export each tab as a CSV, but you can't export an entire multi-tab
spreadsheet as one CSV.  Same thing applies to databases.  You can either
export each table as a CSV, then import them into multiple tabs of a
spreadsheet and use vlookups to relate the data into one bigger dataset
with more columns - or better yet, write some SQL to "join" the tables and
export the results of that query as a CSV for import into a spreadsheet.

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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Stefan Brüns]

On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
> 
> On armv7l, there is another failure in the fuzztests, with and without the
> patch:
> sessionfuzz-data1.db: sessionfuzz: ./sqlite3.c:57249: pager_open_journal:
> Assertion `rc!=SQLITE_OK || isOpen(pPager->jfd)' failed.

I had previously overlooked this, but the fuzz check also fails on ppc32be, 
but passes on i586 (and all tried 64bit archs).

Kind regards,

Stefan

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Stefan Brüns]

On Mittwoch, 29. Januar 2020 13:40:44 CET Richard Hipp wrote:
> Please retry using this check-in:
> https://www.sqlite.org/src/info/b20503aaf5b6595a

The failings test now pass on all architectures:
- ix86/x86_64
- armv7hl, aarch64
- ppc32be, ppc64be, ppc64le
- s390x

Kind regards,

Stefan

> On 1/28/20, Brüns, Stefan  wrote:
> > On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
> >> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
> >> > On 1/27/20, Ondrej Dubaj  wrote:
> >> > > Hi,
> >> > > 
> >> > > I came across a problem during mate test, where fuzzcheck ends with
> >> > > segfault.
> >> > > The problem appears to be only on [s390x]. Other architectures are
> >> > > working fine.
> >> > 
> >> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
> >> > 
> >> > Thanks for the temporary SSH login!
> >> 
> >> We were seeing the problem also on other ppc64BE:
> >> 
> >> ppc64 (big endian):
> >> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
> >> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db
> >> (sqlid=7726,dbid=1): segfault
> >> 
> >> The issue is cured with the fix, but we still see 3 failing tests with
> >> fts4/
> >> fts5:
> >> 
> >> ! fts5matchinfo-15.1 expected: [X'0200']
> >> ! fts5matchinfo-15.1 got:  [X'0002']
> >> ! fts5matchinfo-15.2 expected: [X'0200']
> >> ! fts5matchinfo-15.2 got:  [X'0002']
> >> ! fts4aa-6.10 expected:
> >> [X'02000E000E0001000100010001
> >> 00' ] ! fts4aa-6.10 got:
> >> [X'0002000E000E00010001000100
> >> 01' ]
[...]
> > 
> > This is 3.31.1, btw.
> > 

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Richard Hipp]

Please retry using this check-in:
https://www.sqlite.org/src/info/b20503aaf5b6595a

On 1/28/20, Brüns, Stefan  wrote:
> On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
>> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
>> > On 1/27/20, Ondrej Dubaj  wrote:
>> > > Hi,
>> > >
>> > > I came across a problem during mate test, where fuzzcheck ends with
>> > > segfault.
>> > > The problem appears to be only on [s390x]. Other architectures are
>> > > working fine.
>> >
>> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
>> >
>> > Thanks for the temporary SSH login!
>>
>> We were seeing the problem also on other ppc64BE:
>>
>> ppc64 (big endian):
>> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
>> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db
>> (sqlid=7726,dbid=1): segfault
>>
>> The issue is cured with the fix, but we still see 3 failing tests with
>> fts4/
>> fts5:
>>
>> ! fts5matchinfo-15.1 expected: [X'0200']
>> ! fts5matchinfo-15.1 got:  [X'0002']
>> ! fts5matchinfo-15.2 expected: [X'0200']
>> ! fts5matchinfo-15.2 got:  [X'0002']
>> ! fts4aa-6.10 expected:
>> [X'02000E000E000100010001000100'
>> ] ! fts4aa-6.10 got:
>> [X'0002000E000E0001000100010001'
>> ]
>
> Seems to be an endianess problem, all three affected archs are big endian
> (ppc32, ppc64be, s390x):
>
>> ! fts4aa-6.10 expected:
>> [X'0200   0E00 0E00 0100 0100 0100
>>
> 0100'
>> ] ! fts4aa-6.10 got:
>> [X'0002   000E 000E 0001 0001 0001
>>
> 0001'
>> ]
>
> This is 3.31.1, btw.
>
> Kind regards, Stefan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Brüns , Stefan]

On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
> > On 1/27/20, Ondrej Dubaj  wrote:
> > > Hi,
> > > 
> > > I came across a problem during mate test, where fuzzcheck ends with
> > > segfault.
> > > The problem appears to be only on [s390x]. Other architectures are
> > > working fine.
> > 
> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
> > 
> > Thanks for the temporary SSH login!
> 
> We were seeing the problem also on other ppc64BE:
> 
> ppc64 (big endian):
> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db
> (sqlid=7726,dbid=1): segfault
> 
> The issue is cured with the fix, but we still see 3 failing tests with fts4/
> fts5:
> 
> ! fts5matchinfo-15.1 expected: [X'0200']
> ! fts5matchinfo-15.1 got:  [X'0002']
> ! fts5matchinfo-15.2 expected: [X'0200']
> ! fts5matchinfo-15.2 got:  [X'0002']
> ! fts4aa-6.10 expected:
> [X'02000E000E000100010001000100'
> ] ! fts4aa-6.10 got:
> [X'0002000E000E0001000100010001'
> ]

Seems to be an endianess problem, all three affected archs are big endian 
(ppc32, ppc64be, s390x):

> ! fts4aa-6.10 expected:
> [X'0200   0E00 0E00 0100 0100 0100 
0100'
> ] ! fts4aa-6.10 got:
> [X'0002   000E 000E 0001 0001 0001 
0001'
> ]

This is 3.31.1, btw.

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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Brüns , Stefan]

On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
> On 1/27/20, Ondrej Dubaj  wrote:
> > Hi,
> > 
> > I came across a problem during mate test, where fuzzcheck ends with
> > segfault.
> > The problem appears to be only on [s390x]. Other architectures are
> > working fine.
> 
> Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
> 
> Thanks for the temporary SSH login!

We were seeing the problem also on other ppc64BE:

ppc64 (big endian):
fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck /home/abuild/rpmbuild/
BUILD/sqlite-src-3310100/test/fuzzdata1.db (sqlid=7726,dbid=1): segfault

The issue is cured with the fix, but we still see 3 failing tests with fts4/
fts5:

! fts5matchinfo-15.1 expected: [X'0200']
! fts5matchinfo-15.1 got:  [X'0002']
! fts5matchinfo-15.2 expected: [X'0200']
! fts5matchinfo-15.2 got:  [X'0002']
! fts4aa-6.10 expected: 
[X'02000E000E000100010001000100']
! fts4aa-6.10 got:  
[X'0002000E000E0001000100010001']

SQLite 2020-01-27 19:55:54 
3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
3 errors out of 294433 tests on obs-power8-05 Linux 64-bit big-endian
!Failures on these tests: fts5matchinfo-15.1 fts5matchinfo-15.2 fts4aa-6.10

SQLite 2020-01-27 19:55:54 
3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
3 errors out of 294121 tests on obs-power8-05 Linux 32-bit big-endian
!Failures on these tests: fts5matchinfo-15.1 fts5matchinfo-15.2 fts4aa-6.10

SQLite 2020-01-27 19:55:54 
3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
3 errors out of 294433 tests on s390zp23 Linux 64-bit big-endian
!Failures on these tests: fts5matchinfo-15.1 fts5matchinfo-15.2 fts4aa-6.10


On armv7l, there is another failure in the fuzztests, with and without the 
patch:
sessionfuzz-data1.db: sessionfuzz: ./sqlite3.c:57249: pager_open_journal: 
Assertion `rc!=SQLITE_OK || isOpen(pPager->jfd)' failed.


Kind regards,

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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Richard Hipp]

On 1/27/20, Ondrej Dubaj  wrote:
> Hi,
>
> I came across a problem during mate test, where fuzzcheck ends with
> segfault.
> The problem appears to be only on [s390x]. Other architectures are
> working fine.

Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb

Thanks for the temporary SSH login!

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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Richard Hipp]

On 1/27/20, Ondrej Dubaj  wrote:
> The problem appears to be only on this arches.

That probably means it is an EBCDIC problem.

We have no way of replicating or debugging this problem as we have no
access to an s390 machine.  Can RedHat perhaps provide one of the
SQLite developers with a temporary ssh account into an s390 machine so
that we can investigate and fix the problem?

-- 
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] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Ondrej Dubaj]

Hi,

I came across a problem during mate test, where fuzzcheck ends with
segfault.
The problem appears to be only on this arches. Other architectures are
working fine.

Build here:

https://koji.fedoraproject.org/koji/taskinfo?taskID=40950404

Log:

./fuzzcheck /builddir/build/BUILD/sqlite-src-331/test/fuzzdata1.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata2.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata3.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata4.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata5.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata6.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata7.db
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata8.db
fuzzdata1.db: SQL fuzz
fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
/builddir/build/BUILD/sqlite-src-331/test/fuzzdata1.db
(sqlid=7726,dbid=1): segfault
make: *** [Makefile:1242: fuzztest] Error 1

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

[sqlite] SQLite: A couple minor doc bugs

[Sigmund, Andrew]

To whom it may concern,

  A couple minor SQLite documentation bugs:

https://sqlite.org/testing.html  Section 7.1, in paragraph beginning "Branch 
coverage is more strict": Paragraph ends saying "three test cases", but only 
two are listed (first appears garbled).

  https://sqlite.org/deterministic.html  Section 3, at the end of 
the first paragraph: "... where only deterministic functions are allows [sic]." 
 "allows" -> "allowed".

  (Reply directly to me, if necessary; I'm not subscribed to the 
list.)

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

Re: [sqlite] Sqlite 3.31.0 breaks firefox and thunderbird

[Ferdinand]

Thank you for the suggestion, Warren.

Bisect first pointed to commit df51ae19c1aa4c26, but sometime after that this 
segfault seemed to be fixed, a second bisect blamed commit 2ae77bd233570834:

https://www.sqlite.org/src/info/2ae77bd233570834

I've attached a backtrace from a crash at this commit.

Cheers,
Ferdinand
#0  0x7fffef25af20 in  () at /home/fw/nightly-latest/libxul.so
#1  0x7fffef2539f7 in  () at /home/fw/nightly-latest/libxul.so
#2  0x7fffea64478a in sqlite3OsOpen
(pVfs=0x7fffdb83c920, zPath=0x7fffd424dc18 
"/home/fw/.mozilla/firefox/s1jb1nj0.ephemeral/storage/permanent/chrome/idb/3870112724rsegmnoittet-es.sqlite-wal",
 pFile=0x7fffd43e49d0, flags=524294, pFlagsOut=0x7fffd3cdd63c) at 
sqlite3.c:22915
#3  0x7fffea6695cd in sqlite3WalOpen
(pVfs=0x7fffdb83c920, pDbFd=0x7fffd424d980, zWalName=0x7fffd424dc18 
"/home/fw/.mozilla/firefox/s1jb1nj0.ephemeral/storage/permanent/chrome/idb/3870112724rsegmnoittet-es.sqlite-wal",
 bNoShm=0, mxWalSize=-1, ppWal=0x7fffd424d928)
at sqlite3.c:60512
#4  0x7fffea667b4f in pagerOpenWal (pPager=0x7fffd424d800) at 
sqlite3.c:58926
#5  0x7fffea667cce in sqlite3PagerOpenWal (pPager=0x7fffd424d800, 
pbOpen=0x7fffd3cdd6d0) at sqlite3.c:58970
#6  0x7fffea674b3c in lockBtree (pBt=0x7fffd4f7d670) at sqlite3.c:67141
#7  0x7fffea67555b in sqlite3BtreeBeginTrans (p=0x7fffd425ff10, wrflag=0, 
pSchemaVersion=0x0) at sqlite3.c:67463
#8  0x7fffea6f0da8 in sqlite3InitOne (db=0x7fffd42e2c00, iDb=0, 
pzErrMsg=0x7fffd3cde9f8, mFlags=0)
at sqlite3.c:127048
#9  0x7fffea6f137d in sqlite3Init (db=0x7fffd42e2c00, 
pzErrMsg=0x7fffd3cde9f8) at sqlite3.c:127233
#10 0x7fffea6f14d5 in sqlite3ReadSchema (pParse=0x7fffd3cde9f0) at 
sqlite3.c:127259
#11 0x7fffea6eb165 in sqlite3Pragma
(pParse=0x7fffd3cde9f0, pId1=0x7fffd3cde038, pId2=0x7fffd3cde050, 
pValue=0x7fffd3cde080, minusFlag=1)
at sqlite3.c:124693
#12 0x7fffea72d1e7 in yy_reduce
(yypParser=0x7fffd3cddff0, yyruleno=242, yyLookahead=1, 
yyLookaheadToken=..., pParse=0x7fffd3cde9f0)
at sqlite3.c:156497
--Type  for more, q to quit, c to continue without paging--
#13 0x7fffea72e89a in sqlite3Parser (yyp=0x7fffd3cddff0, yymajor=1, 
yyminor=...) at sqlite3.c:157054
#14 0x7fffea72fda1 in sqlite3RunParser (pParse=0x7fffd3cde9f0, 
zSql=0x7fffd5add8d9 "", pzErrMsg=0x7fffd3cdeb98)
at sqlite3.c:158328
#15 0x7fffea6f1c44 in sqlite3Prepare
(db=0x7fffd42e2c00, zSql=0x7fffd5add888 "/* 
/home/fw/nightly/storage/mozStorageConnection.cpp */ PRAGMA cache_size = 
-2048", nBytes=-1, prepFlags=128, pReprepare=0x0, ppStmt=0x7fffd3cdecd8, 
pzTail=0x7fffd3cdece0) at sqlite3.c:127460
#16 0x7fffea6f1ebf in sqlite3LockAndPrepare
(db=0x7fffd42e2c00, zSql=0x7fffd5add888 "/* 
/home/fw/nightly/storage/mozStorageConnection.cpp */ PRAGMA cache_size = 
-2048", nBytes=-1, prepFlags=128, pOld=0x0, ppStmt=0x7fffd3cdecd8, 
pzTail=0x7fffd3cdece0) at sqlite3.c:127532
#17 0x7fffea6f2214 in sqlite3_prepare_v2
(db=0x7fffd42e2c00, zSql=0x7fffd5add888 "/* 
/home/fw/nightly/storage/mozStorageConnection.cpp */ PRAGMA cache_size = 
-2048", nBytes=-1, ppStmt=0x7fffd3cdecd8, pzTail=0x7fffd3cdece0) at 
sqlite3.c:127616
#18 0x7fffea6e957f in sqlite3_exec
(db=0x7fffd42e2c00, zSql=0x7fffd5add888 "/* 
/home/fw/nightly/storage/mozStorageConnection.cpp */ PRAGMA cache_size = 
-2048", xCallback=0x0, pArg=0x0, pzErrMsg=0x0) at sqlite3.c:121960
#19 0x7fffef24d86f in  () at /home/fw/nightly-latest/libxul.so

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

Re: [sqlite] Sqlite 3.31.0 breaks firefox and thunderbird

[Warren Young]

On Jan 23, 2020, at 8:33 AM, Bernhard Rosenkraenzer  wrote:
> 
> The Debian guys have also observed this:
> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=949644
> (and also don't have a fix yet).
> 
> Any ideas?

Can you bisect SQLite to narrow the range here?  This release had an unusually 
long period to cook, so without a bisect, you’re kind of asking for someone to 
remember what they changed months ago.

Method:

1. Check out SQLite source from Fossil: https://sqlite.org/src/

2. fossil bisect reset ; fossil bisect bad   (marks tip-of-trunk as “bad”)

3. fossil bisect good version-3.30.1   (or whatever version you last tested as 
“good”)

At that point, the source tree will contain a version halfway between 
tip-of-trunk and the version you marked “good” with the third command.  Build 
it, test it, then say either “fossil bisect bad” or “fossil bisect good” 
depending on whether it crashes again.

By my count, there have been 551 checkins between those two releases, so a 
bisect should take roughly 9 tries to find the culprit:

$ fossil timeline after version-3.30.1 -t ci -n 0 | grep -c '^[0-9]'
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite 3.31.0 breaks firefox and thunderbird

[Richard Hipp]

On 1/23/20, Bernhard Rosenkraenzer  wrote:
> Hi,
> after updating sqlite to 3.31.0, both firefox and thunderbird crash on
> startup (rebuilding them against the newer sqlite doesn't help).

Is this related to https://bugzilla.mozilla.org/show_bug.cgi?id=1607902

> Backtrace:
> (gdb) bt
> #0  0x71b9fe20 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #1  0x71b993d2 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #2  0x7fffef3afdf5 in pagerOpenWal () from /usr/lib64/libsqlite3.so.0
> #3  0x7fffef398e76 in sqlite3BtreeBeginTrans () from
> /usr/lib64/libsqlite3.so.0
> #4  0x7fffef3ed074 in sqlite3InitOne () from /usr/lib64/libsqlite3.so.0
> #5  0x7fffef3f105e in sqlite3Pragma () from /usr/lib64/libsqlite3.so.0
> #6  0x7fffef3b44ea in yy_reduce () from /usr/lib64/libsqlite3.so.0
> #7  0x7fffef397df0 in sqlite3RunParser () from
> /usr/lib64/libsqlite3.so.0
> #8  0x7fffef3968a9 in sqlite3Prepare () from /usr/lib64/libsqlite3.so.0
> #9  0x7fffef396171 in sqlite3LockAndPrepare () from
> /usr/lib64/libsqlite3.so.0
> #10 0x7fffef3881cf in sqlite3_exec () from /usr/lib64/libsqlite3.so.0
> #11 0x71b93ddc in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #12 0x71b934a4 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #13 0x71b938eb in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #14 0x71ba2be2 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #15 0x71626619 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #16 0x716347b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #17 0x71530f1f in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #18 0x71533006 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #19 0x718a5dca in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #20 0x718757b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #21 0x7152f0b5 in ?? () from /usr/lib64/firefox-71.0/libxul.so
> #22 0x777f6979 in ?? () from /lib64/libnspr4.so
> #23 0x77f8a031 in start_thread () from /lib64/libpthread.so.0
> #24 0x77b6f4df in clone () from /lib64/libc.so.6
>
> The Debian guys have also observed this:
> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=949644
> (and also don't have a fix yet).
>
> Any ideas?
>
> Best regards
> bero
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Sqlite 3.31.0 breaks firefox and thunderbird

[Bernhard Rosenkraenzer]

Hi,
after updating sqlite to 3.31.0, both firefox and thunderbird crash on startup 
(rebuilding them against the newer sqlite doesn't help).
Backtrace:
(gdb) bt
#0  0x71b9fe20 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#1  0x71b993d2 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#2  0x7fffef3afdf5 in pagerOpenWal () from /usr/lib64/libsqlite3.so.0
#3  0x7fffef398e76 in sqlite3BtreeBeginTrans () from 
/usr/lib64/libsqlite3.so.0
#4  0x7fffef3ed074 in sqlite3InitOne () from /usr/lib64/libsqlite3.so.0
#5  0x7fffef3f105e in sqlite3Pragma () from /usr/lib64/libsqlite3.so.0
#6  0x7fffef3b44ea in yy_reduce () from /usr/lib64/libsqlite3.so.0
#7  0x7fffef397df0 in sqlite3RunParser () from /usr/lib64/libsqlite3.so.0
#8  0x7fffef3968a9 in sqlite3Prepare () from /usr/lib64/libsqlite3.so.0
#9  0x7fffef396171 in sqlite3LockAndPrepare () from 
/usr/lib64/libsqlite3.so.0
#10 0x7fffef3881cf in sqlite3_exec () from /usr/lib64/libsqlite3.so.0
#11 0x71b93ddc in ?? () from /usr/lib64/firefox-71.0/libxul.so
#12 0x71b934a4 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#13 0x71b938eb in ?? () from /usr/lib64/firefox-71.0/libxul.so
#14 0x71ba2be2 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#15 0x71626619 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#16 0x716347b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#17 0x71530f1f in ?? () from /usr/lib64/firefox-71.0/libxul.so
#18 0x71533006 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#19 0x718a5dca in ?? () from /usr/lib64/firefox-71.0/libxul.so
#20 0x718757b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#21 0x7152f0b5 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#22 0x777f6979 in ?? () from /lib64/libnspr4.so
#23 0x77f8a031 in start_thread () from /lib64/libpthread.so.0
#24 0x77b6f4df in clone () from /lib64/libc.so.6

The Debian guys have also observed this:
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=949644
(and also don't have a fix yet).

Any ideas?

Best regards
bero

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

Re: [sqlite] SQLite 3.31.0 enters pre-release testing

[Jose Isaias Cabrera]

Richard Hipp, on Friday, January 17, 2020 07:56 AM, wrote...

> A terse summary of changes can be seen at
> https://www.sqlite.org/draft/releaselog/current.html
No gain in speed? :-)  I love it when I see, "2% faster..."  Thanks.

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

[sqlite] SQLite 3.31.0 enters pre-release testing

[Richard Hipp]

Our intent is that there will be no more changes to SQLite, other than
documentation updates and bug fixes, prior to the 3.31.0 release.
Consider the "Prerelease Snapshot" at https://sqlite.org/download.html
to be a beta-release.  Please test that snapshot and report any
problems.

A terse summary of changes can be seen at
https://www.sqlite.org/draft/releaselog/current.html

A release checklist has been set up at
https://www.sqlite.org/src/ext/checklist/top/index

As I write these words, the checklist is all gray.  Items will turn
green as they are checked off (or other colors if there are issues).
The 3.31.0 release will occur when the checklist goes all-green.  We
hope to achieve this on or before 2020-01-31 - two weeks from today.

You can see the full sequence of main-branch check-ins since the
previous release at
https://www.sqlite.org/src/timeline?from=release&to=trunk

For a diff showing all changes since the previous release, edit the
prior URL by replacing "/timeline" with "/vdiff".  You can also click
on any two nodes (the circles connected by arrows) on the timeline
graph to see a diff between the two selected check-ins.  Or, manually
edit the URL to change the "from" and "to" attributes to a check-in
hash prefix, branch name, of tag.

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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

[Simon Slavin]

On 5 Jan 2020, at 5:01pm, Amer Neely  wrote:

> But the question still remains, why the different results?

The optimizer gets improved from time to time in SQLite versions.  SQLite 
solved the problem faster by breaking down your query differently: deciding 
whether to do one scan or use two indexes, which table to search first, etc..

Your query was appears to violate the SQL standard, by failing to define the 
JOIN.  It should probably have resulted in a syntax error, but it didn't.  And 
it just happened that one version of SQLite interpreted the query one way, 
another interpreted it another way.

I advise you change your software to use one of the queries DRH supplied:

>   SELECT * FROM a, (b JOIN c USING(id));
> 
>   SELECT * FROM (a,b) JOIN c USING (id);

This will remove the ambiguity, meaning that however SQLite interprets the 
query in the future, or even if you switch to a different SQL engine, you get 
the result you wanted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

[Amer Neely]

> On Saturday, 4 January, 2020 18:31, Amer Neely 
> wrote:
> 
>> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>> and web-based environment for several years. So far I'm happy and
>> impressed with SQLite, but I recently noticed some odd behaviour with
>> one of my queries.
>> Using the command-line in a shell (Mac High Sierra) I get a particular
>> result from a query. The exact same query in a Perl script gives me a
>> different result. To my mind it is a simple query, getting the 5 latest
>> additions to my music library.
>> Command-line:
>> select artists.artist, artists.artistid, cds.title, cds.artistid,
>> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>> cds using (artistid) group by artists.artistid order by cds.id desc
>> limit 5;
>> gives me the correct result. However, in a Perl script it gives me a
>> different result. How is that possible? Could it be a Perl::DBI issue?
>> Many thanks for anyone able to shed some light on this.
> 
> Your select does not constrain artists so the result is non-deterministic
> in that the result will depend on how the query planner decides to
> execute the query.  That is, you have not specified any join constraints
> on artists.
> 
> SELECT * FROM A, B JOIN C USING (D);
> 
> means
> 
> SELECT *
>  FROM A, B, C
> WHERE B.D == C.D;
> 
> if you thought it meant
> 
> SELECT *
>  FROM A, B, C
> WHERE A.D == B.D
>   AND B.D == C.D;
> 
> then that is likely the reason for the discrepancy.
> 
> 
Thank you for your time and consideration. I have managed to 'solve'
this problem by constraining on artists.artistid by grouping on that.
But the question still remains, why the different results?


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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Igor Korot)

[Amer Neely]

-- > What is your version of Perl and the SQLite module?

This is perl 5, version 18, subversion 2 (v5.18.2) built for
darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2013, Larry Wall

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.

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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result

[Keith Medcalf]

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

On Saturday, 4 January, 2020 18:31, Amer Neely  wrote:

>I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>and web-based environment for several years. So far I'm happy and
>impressed with SQLite, but I recently noticed some odd behaviour with
>one of my queries.
>Using the command-line in a shell (Mac High Sierra) I get a particular
>result from a query. The exact same query in a Perl script gives me a
>different result. To my mind it is a simple query, getting the 5 latest
>additions to my music library.
>Command-line:
>select artists.artist, artists.artistid, cds.title, cds.artistid,
>cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>cds using (artistid) group by artists.artistid order by cds.id desc
>limit 5;
>gives me the correct result. However, in a Perl script it gives me a
>different result. How is that possible? Could it be a Perl::DBI issue?
>Many thanks for anyone able to shed some light on this.

Your select does not constrain artists so the result is non-deterministic in 
that the result will depend on how the query planner decides to execute the 
query.  That is, you have not specified any join constraints on artists.

SELECT * FROM A, B JOIN C USING (D);

means

SELECT * 
  FROM A, B, C
 WHERE B.D == C.D;

if you thought it meant

SELECT *
  FROM A, B, C
 WHERE A.D == B.D
   AND B.D == C.D;

then that is likely the reason for the discrepancy.




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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Sat, Jan 4, 2020 at 7:31 PM Amer Neely  wrote:
>
> Hello all,
> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
> and web-based environment for several years. So far I'm happy and
> impressed with SQLite, but I recently noticed some odd behaviour with
> one of my queries.
> Using the command-line in a shell (Mac High Sierra) I get a particular
> result from a query. The exact same query in a Perl script gives me a
> different result. To my mind it is a simple query, getting the 5 latest
> additions to my music library.
> Command-line:
> select artists.artist, artists.artistid, cds.title, cds.artistid,
> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
> cds using (artistid) group by artists.artistid order by cds.id desc
> limit 5;
> gives me the correct result. However, in a Perl script it gives me a
> different result. How is that possible? Could it be a Perl::DBI issue?
> Many thanks for anyone able to shed some light on this.

What is your version of Perl and the SQLite module?

Thank you.

> --
> Amer Neely
> ___
> 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] SQLite command-line result is different from Perl DBI::Sqlite result

[Amer Neely]

Hello all,
I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
and web-based environment for several years. So far I'm happy and
impressed with SQLite, but I recently noticed some odd behaviour with
one of my queries.
Using the command-line in a shell (Mac High Sierra) I get a particular
result from a query. The exact same query in a Perl script gives me a
different result. To my mind it is a simple query, getting the 5 latest
additions to my music library.
Command-line:
select artists.artist, artists.artistid, cds.title, cds.artistid,
cds.cdid, genres.genre, genres.artistid from artists, genres inner join
cds using (artistid) group by artists.artistid order by cds.id desc
limit 5;
gives me the correct result. However, in a Perl script it gives me a
different result. How is that possible? Could it be a Perl::DBI issue?
Many thanks for anyone able to shed some light on this.
-- 
Amer Neely
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite database gets corrupted

[Simon Slavin]

On 20 Dec 2019, at 12:11pm, Syed Ahmad  wrote:

> Any way to recover it.

This mailing list strips attachments.

When's the last time you could use this file ?  Did it have any form of 
encryption ?

Was it usable with your own software ?  Could you open it with the SQLite 
command-line tool ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite database gets corrupted

[Syed Ahmad]

Hi,

Operating system: Embedded Linux.

Version : 3.14.2 2016-09-12

sqlite3 bad.sqlite "PRAGMA integrity_check"
Error: file is encrypted or is not a database


I have attached the file.


Any way to recover it. Is any there at fixes in latest sqlite version 3.x.
which will fix this issue.


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

[sqlite] SQLite overload help

[Pedro Batista]

I have created a custom function (aux), I'm able to load it via
load_extension() on shell.
Now I'm trying to overload a function for a Virtual Table (fts3 or fts5)

These what I know:
need to hit sqlite3_overload_function
which uses the xFindFunction()
that goes back to `fts3FindFunctionMethod`

I am not sure how I can hit that method to overload with my custom function
already loaded
There's exact SQL queries to accomplish that or I need to code some more C?

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

Re: [sqlite] sqlite sync over network

[Jens Alfke]

> On Dec 9, 2019, at 7:43 AM, George  wrote:
> 
> (litesync - SQLite Replication and Synchronization) 
> litesync seems to be a version of sqlite made by Ramos Bernardo from Brazil.

Interesting, but I don't see how they can make those kinds of claims without 
running into conflicts on primary keys. They say you can't use AUTOINCREMENT, 
for obvious reasons, but don't describe how else primary keys would be assigned.

The whole thing appears to be closed-source only, and from a developer I've 
never heard of, so I'd be cautious and build some software to try to evaluate 
it before you plunk down money.

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

Re: [sqlite] sqlite sync over network

[George]

thank you Jens,I have just found litesync 
(litesync - SQLite Replication and Synchronization) 
litesync seems to be a version of sqlite made by Ramos Bernardo from Brazil.

| 
| 
|  | 
litesync - SQLite Replication and Synchronization

litesync makes easy to replicate and synchronize SQLite databases. All nodes 
can write to the database, even whe...
 |

 |

 |


I'm testing this now. For windows OS its easy but still dont know how to use it 
on linux and what about Qt integration, ... how to add it on my application. 
It is working in a simple demo on 3 machines (winOS) but I'm wandering if 
anybody tested in a real life situation.

George C.


Hello from GxG,
gxg_...@yahoo.com
 

On Friday, December 6, 2019, 2:32:53 AM GMT+2, Jens Alfke 
 wrote:  
 
 

> On Dec 5, 2019, at 8:05 AM, George  wrote:
> 
> Changes -> bidirectional. All terminals can save to db. Amount of collected 
> data it's not big and frequency between readings will be minimum 2sec. When 
> we push more readings. Data itself it's very small. Like reading temperature 
> from a probe.

The product I work on — Couchbase Mobile* — does this, but it may not be 
exactly what you want because it uses SQLite internally on the client side, and 
not at all on the server. So it's not something you can plug your existing 
SQLite-based code into. (Our data model is not relational but JSON-based.)

Data sync is hard. I've been working in this area since 2011 so I think I have 
some expertise here :)
Problems you're likely to run into:

1. You can't use regular integer primary keys, because different clients will 
end up creating rows with the same keys and cause collisions when they sync. 
You either have to use UUIDs as keys, or else somehow partition the key space 
in a deterministic way, like prefixing a fixed client ID to a key.

2. If multiple entities can update the db, there will likely be conflicts. 
Conflicts are hard to manage, and how you do it is entirely dependent on your 
high-level schema. In the worst case, conflicts require human intervention.

3. You need a message-oriented protocol. It's best to keep a continuous 
bidirectional connection open. WebSockets is a good protocol for this. You'll 
have to deal with errors establishing the connection, and unexpected 
disconnects, by periodic retries.

4. Schema upgrades in a distributed system are a mess. If your system is 
centralized enough you can take it down and upgrade every peer's database, then 
bring it back up, but of course that doesn't work in a decentralized system. 
(One of the main reasons Couchbase is schema-less.)

5. Keep in mind there is no central source of truth. Data takes finite time to 
propagate, and transient errors greatly increase that time. Even if you have a 
central server, it will be behind the clients that create the data, so it 
doesn't have the latest info. No one does.

—Jens

* https://www.couchbase.com/products/mobile
___
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] sqlite sync over network

[Jens Alfke]

> On Dec 5, 2019, at 8:05 AM, George  wrote:
> 
> Changes -> bidirectional. All terminals can save to db. Amount of collected 
> data it's not big and frequency between readings will be minimum 2sec. When 
> we push more readings. Data itself it's very small. Like reading temperature 
> from a probe.

The product I work on — Couchbase Mobile* — does this, but it may not be 
exactly what you want because it uses SQLite internally on the client side, and 
not at all on the server. So it's not something you can plug your existing 
SQLite-based code into. (Our data model is not relational but JSON-based.)

Data sync is hard. I've been working in this area since 2011 so I think I have 
some expertise here :)
Problems you're likely to run into:

1. You can't use regular integer primary keys, because different clients will 
end up creating rows with the same keys and cause collisions when they sync. 
You either have to use UUIDs as keys, or else somehow partition the key space 
in a deterministic way, like prefixing a fixed client ID to a key.

2. If multiple entities can update the db, there will likely be conflicts. 
Conflicts are hard to manage, and how you do it is entirely dependent on your 
high-level schema. In the worst case, conflicts require human intervention.

3. You need a message-oriented protocol. It's best to keep a continuous 
bidirectional connection open. WebSockets is a good protocol for this. You'll 
have to deal with errors establishing the connection, and unexpected 
disconnects, by periodic retries.

4. Schema upgrades in a distributed system are a mess. If your system is 
centralized enough you can take it down and upgrade every peer's database, then 
bring it back up, but of course that doesn't work in a decentralized system. 
(One of the main reasons Couchbase is schema-less.)

5. Keep in mind there is no central source of truth. Data takes finite time to 
propagate, and transient errors greatly increase that time. Even if you have a 
central server, it will be behind the clients that create the data, so it 
doesn't have the latest info. No one does.

—Jens

* https://www.couchbase.com/products/mobile
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite sync over network

[Simon Slavin]

On 5 Dec 2019, at 4:48pm, George  wrote:

> We have an app already done in Qt on all terminals. Sqlite it's already used 
> by this app. 

When a terminal makes up a SQL INSERT command to add new data to the database, 
have it also save this command to a file.  That's the file you send to the 
master computer so that the master database of all transactions can be updated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite sync over network

[Reid Thompson]

On Thu, 2019-12-05 at 12:54 +, George wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> Hi,
> I need to sync sqlite tables over network between few devices.
> Please help me with an opinion with what will be the best approach ?
> thanks,
> George
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM&r=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8&m=ZUgwTMbyka9IuNZaXY0epvffHzRxcqMMJu6gFDi2cWQ&s=bFUXvciZ4M0weWnWL70V53tgL3oicufmaLQb3BWoGmI&e=

this may suit your needs, based on sqlite.

Bedrock was built by Expensify, and is a networking and distributed transaction 
layer built atop SQLite, the fastest, most reliable, and most widely 
distributed database in the world.

https://bedrockdb.com/


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

Re: [sqlite] sqlite sync over network

[George]

We have an app already done in Qt on all terminals. Sqlite it's already used by 
this app. The only missing part it's our sync to have data on PC for reports 
and also on screen reports on terminals if operator will need to check.We need 
sqlite because help us to manage data on terminal and display it wit Qt 
tableview,  sortfilterproxymodel.
We also don't have PC app, now working on it. And ofcourse still working on 
terminal app to improve.Terminals use a touch display, it's a Samsung board, 
and user can check status of all system
 

Sent from Yahoo Mail on Android 
 
  On Thu, Dec 5, 2019 at 18:24, Simon Slavin wrote:   
Okay.  Do you really need all of the terminals to have up-to-date data from all 
the terminals ?

If not, it's a simple problem: each terminal sends data to the master, which 
puts all the incoming data into the same database.  Terminals don't need a SQL 
dataase at all.  They simply report changes to the master, as often as they 
need to.

Information send from each terminal can be in the form of a text file of new 
data in CSV format.  Incoming text files pile up in a directory on the master.  
It is important that the master does not try to process data into the SQLite 
database immediately, since this would introduce a delay and perhaps problems 
with locking.  It's faster just to accept the data and store it in a file on 
disk, and worry about processing it separately.

A program on the master server continually reads any file available, adds its 
data to the central database, then deletes the file.  If there are no files 
waiting to be processed it can idle for a second or two.  Another program, 
which can run at the same time, gives you access to this database so you can 
read the data and do your maintenance.

Does that work for you ?
___
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] sqlite sync over network

[Simon Slavin]

Okay.  Do you really need all of the terminals to have up-to-date data from all 
the terminals ?

If not, it's a simple problem: each terminal sends data to the master, which 
puts all the incoming data into the same database.  Terminals don't need a SQL 
dataase at all.  They simply report changes to the master, as often as they 
need to.

Information send from each terminal can be in the form of a text file of new 
data in CSV format.  Incoming text files pile up in a directory on the master.  
It is important that the master does not try to process data into the SQLite 
database immediately, since this would introduce a delay and perhaps problems 
with locking.  It's faster just to accept the data and store it in a file on 
disk, and worry about processing it separately.

A program on the master server continually reads any file available, adds its 
data to the central database, then deletes the file.  If there are no files 
waiting to be processed it can idle for a second or two.  Another program, 
which can run at the same time, gives you access to this database so you can 
read the data and do your maintenance.

Does that work for you ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite sync over network

[George]

Hi Simon,
I miss to talk about sync trigger. Will be great to be after post trigger. I 
mean we read a probe on terminal T1, save data and on post trigger also put a 
job for sync and leave it on other thread to do this. If some sync jobs fail 
will must to try again to sync or on timeout  stay in a sync log table.
If it's not possible than we have to check what we can do else. It's not really 
time critical so also here we can be flexible.
George C.

Sent from Yahoo Mail on Android 
 
  On Thu, Dec 5, 2019 at 17:14, Simon Slavin wrote:   On 
5 Dec 2019, at 12:54pm, George  wrote:

> I need to sync sqlite tables over network between few devices. 
> Please help me with an opinion with what will be the best approach ?

Unfortunately this is a subject which has no simple solution.  The things you 
have to do are not simple.  To help us advise you, you might answer these 
questions.

Do all your devices make changes to the database, or are changes made by just 
one of them ?

What governs when to synchronise ?  Should it be done at regular intervales ?  
If so, how often.  Otherwise what should trigger synchronisation ?

Are all your devices permanently on and connected to the internet, or might 
some of them be turned off or out of contact some of the time ?

Are your devices all proper computers or do you have to worry about battery-use 
and bandwidth on some of them ?
___
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] sqlite sync over network

[George]

Hi Simon,
Thx for helping me. I'm trying to explain our setup next:

Changes -> bidirectional. All terminals can save to db. Amount of collected 
data it's not big and frequency between readings will be minimum 2sec. When we 
push more readings. Data itself it's very small. Like reading temperature from 
a probe.
Terminals-> ARM  with Debian LinuxPC-> this is like master. Kind of server. OS 
is Ubuntu or Windows. Now it's Ubuntu. Here we study all data and sometime do 
some changes to database also. But it's maintenance like deleting old data. 
Change names and settings of terminals.
No internet connection. We run this in our private LAN. All it's local and high 
speed.
No problem with energy consumption, batteries etc. All working on backup UPS.

George C.

-gXg

Sent from Yahoo Mail on Android 
 
  On Thu, Dec 5, 2019 at 17:14, Simon Slavin wrote:   On 
5 Dec 2019, at 12:54pm, George  wrote:

> I need to sync sqlite tables over network between few devices. 
> Please help me with an opinion with what will be the best approach ?

Unfortunately this is a subject which has no simple solution.  The things you 
have to do are not simple.  To help us advise you, you might answer these 
questions.

Do all your devices make changes to the database, or are changes made by just 
one of them ?

What governs when to synchronise ?  Should it be done at regular intervales ?  
If so, how often.  Otherwise what should trigger synchronisation ?

Are all your devices permanently on and connected to the internet, or might 
some of them be turned off or out of contact some of the time ?

Are your devices all proper computers or do you have to worry about battery-use 
and bandwidth on some of them ?
___
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] sqlite sync over network

[Simon Slavin]

On 5 Dec 2019, at 12:54pm, George  wrote:

> I need to sync sqlite tables over network between few devices. 
> Please help me with an opinion with what will be the best approach ?

Unfortunately this is a subject which has no simple solution.  The things you 
have to do are not simple.  To help us advise you, you might answer these 
questions.

Do all your devices make changes to the database, or are changes made by just 
one of them ?

What governs when to synchronise ?  Should it be done at regular intervales ?  
If so, how often.  Otherwise what should trigger synchronisation ?

Are all your devices permanently on and connected to the internet, or might 
some of them be turned off or out of contact some of the time ?

Are your devices all proper computers or do you have to worry about battery-use 
and bandwidth on some of them ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite sync over network

[George]

Hi,
I need to sync sqlite tables over network between few devices. 
Please help me with an opinion with what will be the best approach ?
thanks, 
George
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite syntax auto suggest

[Laurent Dhont]

Dear Richard & others,


Now it is clear but how do I let the user make for example an UPDATE 
query without the OR.


I have UPDATE on position 0, then I want OR, OF, tableName. This works 
but SET is only available on position 4-5. I want this also on position 2.



What I want to do is get a list of all words that can be used in the 
next word the user is going to type.



I hope I am making sense.


Thanks!


Kind regards,


Laurent


On 29.11.19 14:09, Richard Hipp wrote:

On 11/29/19, Laurent  wrote:

Could you perhaps explain what pos means and what the [isTerminal] = 0
rows mean.


New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.


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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Dominique !

Here are two views that can bring that info to a query:

==

CREATE VIEW rule_list_view AS
SELECT
    a.ruleid,
    b.name,
    a.lhs
FROM rule AS a
LEFT JOIN symbol AS b ON a.lhs = b.id;

CREATE VIEW rulerhs_list_view AS
SELECT
    a.ruleid,
    b.name as symbol_name,
    c.name as rule_name,
    a.pos,
    a.sym
FROM rulerhs AS a
LEFT JOIN symbol AS b ON a.sym = b.id
LEFT JOIN rule_list_view AS c ON a.ruleid = c.ruleid;

==

Maybe others can contribute with other views to make the info there 
easier to use !


Cheers !

On 29/11/19 14:56, Dominique Devienne wrote:

On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp  wrote:


On 11/29/19, Laurent  wrote:

Could you perhaps explain what pos means and what the [isTerminal] =

0 rows mean.

New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.


Hi. Why not add a column and write it to the SQL insert?

Rather than as a comment in the SQL "dump"? The first thing I'd do with
that new output file,
would be to run it to generate the DB, then look at the DB in a graphical
client, to side and dice
the data. As it stands, those useful comments would be lost in that
scenario. My $0.02. --DD
___
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] sqlite syntax auto suggest

[Dominique Devienne]

On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp  wrote:

> On 11/29/19, Laurent  wrote:
> >
> > Could you perhaps explain what pos means and what the [isTerminal] =
> 0 rows mean.
>
> New check-in enhances the output to include a comment in the SQL
> before the encoding of each production rule.  This should help make it
> clear what the SQL is trying to represent.
>

Hi. Why not add a column and write it to the SQL insert?

Rather than as a comment in the SQL "dump"? The first thing I'd do with
that new output file,
would be to run it to generate the DB, then look at the DB in a graphical
client, to side and dice
the data. As it stands, those useful comments would be lost in that
scenario. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite syntax auto suggest

[Richard Hipp]

On 11/29/19, Laurent  wrote:
>
> Could you perhaps explain what pos means and what the [isTerminal] = 0
> rows mean.
>

New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.

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

Re: [sqlite] sqlite syntax auto suggest

[Laurent]

Dear Richard,


This has been very helpful, I have the parse.sql file. I feel very 
flattered that you took the time to answer my question. My boss says you 
are the one that should win a nobel prize.


This has been much appreciated!!


I completely understand if you won't answer anymore but I can try.

From what I can understand:

 * the symbol table lists all keywords that can be typed in a query
   where [isTerminal] = 1.
 * The other rows where [isTerminal] = 0 are some kind of
   commands/placeholders. This I do not really know.
 * There is never a direct connection from an [isTerminal] = 1 row to
   an [isTerminal] = 0 row.
 * I think but I am not sure that [pos] means position. But maybe I am
   wrong because if I look at for example the FROM keyword I see [pos]
   = 0 with lowwercase 'from' and from what I understand you cannot use
   the FROM keyword in the first position.


Could you perhaps explain what pos means and what the [isTerminal] = 0 
rows mean.


If you have not time for these things I completely understand, thanks 
anyway!



Laurent

On 28.11.19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  Seehttps://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

Sorry by split my reply in so many small ones !

When trying to use the generate "parse.sql" I'm getting this:



sqlite3 parse.db < parse.sql
Error: near line 10: FOREIGN KEY constraint failed
Error: near line 11: FOREIGN KEY constraint failed
Error: near line 12: FOREIGN KEY constraint failed
Error: near line 13: FOREIGN KEY constraint failed



I have "foreign key" ON by default and added "BEGIN; ... COMMIT;" around 
the generated SQL.




BEGIN;
CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);
INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(0,'$',TRUE,NULL);

...

INSERT INTO rule(ruleid,lhs)VALUES(384,261);
COMMIT;



Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

Again after rereading your reply and trying to use the "parse.sql" I 
noticed that it doesn't have a "begin;../commit;" wrapper as it's 
recommended to not having "fsync" calls on each insert.


Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

My bad after writing the last reply I found that "EXPLAIN" and "explain" 
are different symbols there.


Could a small comment be generated at the begin of the generated 
"parse.sql" ?


Something like: (dummy example)



---

-- This is a generated output from lemon parse generator

-- The symbol table can hold terminals and no terminals denoted by ...

-- ...



Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

I just looked at it and I have some doubts about the generated 
parse.sql, as I see it there is this table:




CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,59);

--...

INSERT INTO 
symbol(id,name,isTerminal,fallback)VALUES(185,'explain',FALSE,NULL);




Wouldn't it be better to have:



CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO 
symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,FALSE);




This way we can search for any combination of individual letter cases 
and find a unique match ?


Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Richard Hipp]

On 11/28/19, Laurent Dhont  wrote:
> is there an API to
> get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.
-- 
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] sqlite syntax auto suggest

[Laurent Dhont]

Hi all,


I am trying to create a fully automated auto suggestion feature for my 
web application for sqlite.

Currently this is going well, but I realized there are to many features 
in sqlite to hardcode this, so it will be near impossible and take a lot 
off time. The diagrams on the site of sqlite, for example the select 
statement https://sqlite.org/syntax/select-stmt.html, is there an API to 
get this information in a format that is not an image?

I really want to make the auto suggestions very specific:

A very basic SELECT statement without all the complicated stuff: SELECT 
column, column, ... FROM table_name WHERE clause, ...

After "SELECT" is typed I only show columns in the auto suggestions, if 
a "," is typed I again show only column names, if there is no column I 
show only "FROM", after "FROM" I show only table_names, 


Please do not feel obligated to answer this question, if you do this is 
much appreciated!


Thanks in advance.


Kind regards,


Laurent Dhont


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

Re: [sqlite] SQLite Message Passing Interface

[Simon Slavin]

On 26 Nov 2019, at 10:54pm, Enzo Madda  wrote:

> Id appreciate constructive feedback or questions, thanks!

Your project is a feeder library to a SQL engine.  So you might want to add to 
your documentation something about how your users can avoid SQL injection 
vulnerabilities.  Or how your library makes them impossible.  Or what you've 
done to help reduce them.  Whatever.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Message Passing Interface

[Enzo Madda]

Hello,

Ive been working on something called the SQLite Message Passing Interface,
or SMPI for short.

It allows you to use SQLite from Javascript code in React Native apps.

There is an introduction at https://sqlitempi.com/.

Id appreciate constructive feedback or questions, thanks!

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

[sqlite] SQLite 2019-11-25 04:15:33 says 0 errors out of 250197 tests

[Dennis Clarke]

I felt it was time to change the subject line to something more useful.

Also everything works flawlessly here on Red Hat Enterprise Linux 7.4 :

.
.
.
SQLite 2019-11-25 04:15:33 
b0b655625cf491c832a259d29a67660b8d5943c201617900a83d0660b2673377

0 errors out of 250197 tests on boe13.genunix.com Linux 64-bit little-endian
All memory allocations freed - no leaks
Maximum memory usage: 9267208 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls

Excellent.


--
Dennis Clarke
RISC-V/SPARC/PPC/ARM/CISC
UNIX and Linux spoken
GreyBeard and suspenders optional



 Forwarded Message 
Subject: Re: [sqlite] What is the C language standard to which sqlite 
conforms ?

Date: Mon, 25 Nov 2019 11:17:32 +0700
From: Dan Kennedy <.>
Reply-To: SQLite mailing list 
To: sqlite-users@mailinglists.sqlite.org


On 24/11/62 06:18, Dennis Clarke wrote:

On 11/23/19 4:46 PM, Dan Kennedy wrote:




Some follow up and thank you all for looking at this.

Using this mornings trunk/current/head I do see the tests running well
 with these little exceptions :


boe13$ pwd
/opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.006

... build clean as usual :-)

tests run nicely now until ...


Can you run:

   ./testfixture test/journal3.test

and post the output?



It would be my pleasure to get some light tossed on this ... so here is
a very clean compile ( no -std in CFLAGS at all on gcc 9.2.0 ) and the
tests look like so :


This is a test script error. Should now be fixed here:

https://sqlite.org/src/info/b0b655625cf491c8

.
.
.

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

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Dan Kennedy]

On 8/11/62 00:15, Dennis Clarke wrote:

On 2019-11-07 11:44, Shawn Wagner wrote:
... Just don't use strict c99 mode when compiling with gcc? Drop the 
-std

argument from your CFLAGS to use the default (gnu11 since gcc 5) or
explicitly use gnu99, which gives you that version of the C standard 
+ gcc

extensions.

(Not that they have anything to do with the problem, but compiling 
with -O0

and -fno-builtin are strange unless you're planning on spending some
quality time in a debugger stepping through code, and -malign-double is
already the default on x86-64 so kind of pointless)



Debugger .. yes. That will happen and I build on a multitude of
platforms.

OKay so the code fails on Solaris sparc with c99 whereas in the recent
past it all builds fine :

libtool: compile:  /opt/developerstudio12.6/bin/c99 
-I/usr/local/include -D_TS_ERRNO -D_POSIX_PTHREAD_SEMANTICS 
-D_LARGEFILE64_SOURCE -Xc -m64 -xarch=sparc -g -errfmt=error 
-errshort=full -xstrconst -xildoff -xmemalign=8s -xnolibmil 
-xcode=pic32 -xregs=no%appl -xlibmieee -mc -ftrap=%none 
-xbuiltin=%none -xunroll=1 -xs -xdebugformat=dwarf -errtags=yes 
-errwarn=%none -erroff=%none -DSQLITE_OS_UNIX=1 -I. 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/rtree 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/icu 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/fts3 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/async 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/session 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/userauth 
-D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/local/include 
-DSQLITE_THREADSAFE=1 -DSQLITE_HAVE_ZLIB=1 -DUSE_TCL_STUBS=1 -c 
/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c 
 -KPIC -DPIC -o .libs/tclsqlite.o
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2346: error: undefined symbol: SQLITE_DBCONFIG_ENABLE_VIEW
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2346: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2351: error: undefined symbol: SQLITE_DBCONFIG_TRIGGER_EQP
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2351: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2352: error: undefined symbol: SQLITE_DBCONFIG_RESET_DATABASE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2352: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2353: error: undefined symbol: SQLITE_DBCONFIG_DEFENSIVE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2353: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2354: error: undefined symbol: SQLITE_DBCONFIG_WRITABLE_SCHEMA
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2354: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2355: error: undefined symbol: SQLITE_DBCONFIG_LEGACY_ALTER_TABLE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2355: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2356: error: undefined symbol: SQLITE_DBCONFIG_DQS_DML
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2356: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2357: error: undefined symbol: SQLITE_DBCONFIG_DQS_DDL
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2357: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2855: error: undefined symbol: SQLITE_DIRECTONLY
c99: acomp failed for 
/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c

gmake: *** [Makefile:1029: tclsqlite.lo] Error 1



On Red Hat Enterprise Linux 7.4 the code actually does compile and then
core dumps with a segfault from with that same source file :

Time: walshared.test 24 ms
# WARNING: This next test takes around 12 seconds
gmake: *** [Makefile:1256: tcltest] Segmentation fault (core dumped)



This is almost certainly an issue with the test scripts, not the 
library. Can you post the last 100 lines or so of the file 
"test-out.txt" that was created in the cwd by the [make quicktest] or 
whatever you ran to get this?


Thanks,

Dan.



___
sqli

Re: [sqlite] SQLite with branching

[Robert M. Münch]

On 4 Nov 2019, at 22:25, Jens Alfke wrote:

>> On Nov 4, 2019, at 4:57 AM, Simon Slavin  wrote:
>>
>> That's one of the reasons that the source code for SQLite is public: so that 
>> people can add the features they want.
>
> Totally agree. However, when you go off the mainline of SQLite you lose some 
> things, like easy updating to new SQLite releases — you now have to deal with 
> merging the new official SQLite into the forked SQLite, or waiting for the 
> fork maintainer to do it.

For such fundamental and big changes, that’s exactly the problem.

For the mainline SQLite we know that the code-base is regularly maintained and 
updated and that the official extensions work. Hence, I can base a product on 
it.

For such a big new feature/method, which would be at the core of my product, I 
either have to maintain it myself (heavy) or hope that it’s not a dead-end… the 
risk profile is just not good.

Viele Grüsse.

-- 

Robert M. Münch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Bernardo Ramos]

I included WAL mode and mmap on the LiteTree simple benchmark.

It turns out that WAL mode is as fast as LiteTree on Linux (with a hard 
disk) for writes and a little slower on reads.


On MacBook Pro (with SSD) LiteTree is faster on both writing and 
reading.


SQLite's mmap make it slightly faster than just with WAL. It is faster 
than LiteTree on reads (no page data copy on both cases). But this 
depends on the benchmark code. Sometimes it is slower than using just 
WAL mode (apparently with small dbs).


Sometimes mmap is way faster than all others (on a virtual machine: 
Windows hosting Linux).


But honestly, I do not know the reason of these differences.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Jens Alfke]

> On Nov 7, 2019, at 9:02 AM, Bernardo Ramos  wrote:
> 
> If you are interested in just the performance without the branching feature, 
> there are at least 3 options:
> 
> 1. SQLigthning: I was thinking in updating it to the last version of SQLite

That would be awesome! I have looked at it a few times, but it's based on such 
an old version that it's useless to me in its current state.

> 2. Modified version of LiteTree, without branches

I'm curious how performance of LiteTree (w/o branching) compares to 
SQLightning, i.e. whether storing rows or pages is more efficient. Have you 
measured?

> 3. SQLite with mmap

IIRC, this is not nearly as fast as either of the LMDB-based approaches. I 
don't know why; presumably SQLite doesn't make as efficient use of 
memory-mapping.

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

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Another option (Which the devs might consider) is to replace use of the asm
keyword with __asm__, which is a form of the extension understood even in
strict mode.

(I have no idea why gcc doesn't offer intrinsics for bit rotation, as it's
a common task and shouldn't require hacks like this to reliably do
efficiently).

On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke  wrote:

> On 2019-11-07 11:15, Shawn Wagner wrote:
> > Does that toolchain use gcc, or a different compiler? If gcc, are you
> using
> > the same CFLAGS as on the redhat box (you're turning on a bunch of extra
> > non-default options there)?
> >
>
> I don't see how --with-threads can be a concern. The other options do
> not qualify as a "bunch" at all.
>
> Regardless it looks like the codebase does strange GNU extensions.
>
> There must be a way to switch all that off.
>
> Dennis
>
> ps : don't top post.
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Dennis Clarke]

On 2019-11-07 11:44, Shawn Wagner wrote:

... Just don't use strict c99 mode when compiling with gcc? Drop the -std
argument from your CFLAGS to use the default (gnu11 since gcc 5) or
explicitly use gnu99, which gives you that version of the C standard + gcc
extensions.

(Not that they have anything to do with the problem, but compiling with -O0
and -fno-builtin are strange unless you're planning on spending some
quality time in a debugger stepping through code, and -malign-double is
already the default on x86-64 so kind of pointless)



Debugger .. yes. That will happen and I build on a multitude of
platforms.

OKay so the code fails on Solaris sparc with c99 whereas in the recent
past it all builds fine :

libtool: compile:  /opt/developerstudio12.6/bin/c99 -I/usr/local/include 
-D_TS_ERRNO -D_POSIX_PTHREAD_SEMANTICS -D_LARGEFILE64_SOURCE -Xc -m64 
-xarch=sparc -g -errfmt=error -errshort=full -xstrconst -xildoff 
-xmemalign=8s -xnolibmil -xcode=pic32 -xregs=no%appl -xlibmieee -mc 
-ftrap=%none -xbuiltin=%none -xunroll=1 -xs -xdebugformat=dwarf 
-errtags=yes -errwarn=%none -erroff=%none -DSQLITE_OS_UNIX=1 -I. 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/rtree 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/icu 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/fts3 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/async 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/session 
-I/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/ext/userauth 
-D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/local/include 
-DSQLITE_THREADSAFE=1 -DSQLITE_HAVE_ZLIB=1 -DUSE_TCL_STUBS=1 -c 
/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c 
 -KPIC -DPIC -o .libs/tclsqlite.o
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2346: error: undefined symbol: SQLITE_DBCONFIG_ENABLE_VIEW
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2346: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2351: error: undefined symbol: SQLITE_DBCONFIG_TRIGGER_EQP
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2351: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2352: error: undefined symbol: SQLITE_DBCONFIG_RESET_DATABASE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2352: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2353: error: undefined symbol: SQLITE_DBCONFIG_DEFENSIVE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2353: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2354: error: undefined symbol: SQLITE_DBCONFIG_WRITABLE_SCHEMA
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2354: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2355: error: undefined symbol: SQLITE_DBCONFIG_LEGACY_ALTER_TABLE
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2355: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2356: error: undefined symbol: SQLITE_DBCONFIG_DQS_DML
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2356: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2357: error: undefined symbol: SQLITE_DBCONFIG_DQS_DDL
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2357: error: non-constant initializer: op "NAME"
"/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c", 
line 2855: error: undefined symbol: SQLITE_DIRECTONLY
c99: acomp failed for 
/usr/local/build/sqlite-src-3300100_Oracle_sparc64vii+.001/src/tclsqlite.c

gmake: *** [Makefile:1029: tclsqlite.lo] Error 1



On Red Hat Enterprise Linux 7.4 the code actually does compile and then
core dumps with a segfault from with that same source file :

Time: walshared.test 24 ms
# WARNING: This next test takes around 12 seconds
gmake: *** [Makefile:1256: tcltest] Segmentation fault (core dumped)
boe13$ pwd
/opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001
boe13$ find . | grep -i 'core'
./testdir/core.43494
boe13$
boe13$ file ./testdir/core.43494
./testdir/core.43494: ELF 64-bit LSB core file x86-64, version 1 (SYSV), 
SVR4-style, from './testfixture 
/opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.00', rea

Re: [sqlite] SQLite with branching

[Bernardo Ramos]

Hi!

I am the creator of LiteTree (also LiteReplica, LiteSync and 3 new 
products that will be released soon).


When I was planning to add branching I discovered many ways to implement 
it. I selected the one that satisfied performance over disk usage. It 
can also be implemented the other way around, with low disk space and 
slower. Having both high performance and low disk usage is really hard.


The performance comes from using LMDB and from fine-tuning it to reach 
this goal (in a safe way).


But it comes with a disadvantage: it uses a lot of disk space when 
compared with a normal SQLite db file.


The reason: all past states of the database must be stored if we want to 
be able to create new branches from any place, as well as to navigate 
the database at any previous point in time.


It does not store the entire db state at each point-in-time, just the 
modified pages compared to the previous point.


It also requires a considerable amount of virtual memory space, as (I 
guess) in any memory mapped solution.


If you are interested in just the performance without the branching 
feature, there are at least 3 options:


1. SQLigthning: I was thinking in updating it to the last version of 
SQLite

2. Modified version of LiteTree, without branches
3. SQLite with mmap

I confess that I have not tried SQLite with mmap yet. So maybe it is as 
fast as LiteTree, or even faster. IDK


Do not forget that all these 3 options use memory mapping. Consider this 
on IoT devices and 32-bit processors.


Options 1 and 2 were in my list, but now I have more important products 
being implemented. And option 3 may solve the requirement anyway.



Now let me uncover some differences here:

SQLigthning: Stores SQLite db's rows on LMDB

LiteTree: Stores SQLite db's entire pages on LMDB


Or, showing by SQLite interface level:

SQLigthning: B-tree level

LiteTree: Pager/WAL level


You may wonder how storing an entire db page on another db could be 
fast... one trick is to use the SQLite's reserved space feature on each 
page, matching the size of the header for overflow pages on LMDB. In 
this way a SQLite db page is stored using exactly 4096 bytes on LMDB! 
(not counting the required b-tree index)


The same trick could be applied for another WAL file format in a way 
that each db page would be stored exactly at disk sector boundaries, off 
course having the WAL header using an entire page. The reserved space on 
this case would be the same size of a WAL page header. This would not 
change the write speed but could make the read of random pages on WAL a 
little faster.


This also comes with a disadvantage of using a little more disk space 
than normal, and it is not compatible with existing dbs (a new file with 
reserved space on each page should be created). So it could only exist 
as an option (extension?) or separate product.


In some of my projects I modified the WAL module so the interface is 
pluggable like VFS.


But yeah, I do not know whether the results would compensate the effort 
on the main trunk.


In some cases it is better to implement a virtual table instead.

Anyway, all of these modifications and derived products are only 
possible due to the spectacular work of Richard, as well as Dan and 
Mistachkin.


Thank you so much!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

... Just don't use strict c99 mode when compiling with gcc? Drop the -std
argument from your CFLAGS to use the default (gnu11 since gcc 5) or
explicitly use gnu99, which gives you that version of the C standard + gcc
extensions.

(Not that they have anything to do with the problem, but compiling with -O0
and -fno-builtin are strange unless you're planning on spending some
quality time in a debugger stepping through code, and -malign-double is
already the default on x86-64 so kind of pointless)

On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke  wrote:

> On 2019-11-07 11:15, Shawn Wagner wrote:
> > Does that toolchain use gcc, or a different compiler? If gcc, are you
> using
> > the same CFLAGS as on the redhat box (you're turning on a bunch of extra
> > non-default options there)?
> >
>
> I don't see how --with-threads can be a concern. The other options do
> not qualify as a "bunch" at all.
>
> Regardless it looks like the codebase does strange GNU extensions.
>
> There must be a way to switch all that off.
>
> Dennis
>
> ps : don't top post.
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Dennis Clarke]

On 2019-11-07 11:15, Shawn Wagner wrote:

Does that toolchain use gcc, or a different compiler? If gcc, are you using
the same CFLAGS as on the redhat box (you're turning on a bunch of extra
non-default options there)?



I don't see how --with-threads can be a concern. The other options do
not qualify as a "bunch" at all.

Regardless it looks like the codebase does strange GNU extensions.

There must be a way to switch all that off.

Dennis

ps : don't top post.

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

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

For that matter, is the Solaris box x86 or sparc? If the latter, this
particular code path obviously wouldn't ever be used on it.

On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke  wrote:

> On 2019-11-07 11:01, Shawn Wagner wrote:
> > Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
> > gcc extensions like inline asm.
>
> OKay but I have no issues doing a compile on a Solaris server using the
> Oracle Studio 12.6 tools and strict C99.  So how is C99 the issue?
>
> Dennis
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Does that toolchain use gcc, or a different compiler? If gcc, are you using
the same CFLAGS as on the redhat box (you're turning on a bunch of extra
non-default options there)?

On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke  wrote:

> On 2019-11-07 11:01, Shawn Wagner wrote:
> > Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
> > gcc extensions like inline asm.
>
> OKay but I have no issues doing a compile on a Solaris server using the
> Oracle Studio 12.6 tools and strict C99.  So how is C99 the issue?
>
> Dennis
>
> ___
> 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] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Dennis Clarke]

On 2019-11-07 11:01, Shawn Wagner wrote:

Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
gcc extensions like inline asm.


OKay but I have no issues doing a compile on a Solaris server using the
Oracle Studio 12.6 tools and strict C99.  So how is C99 the issue?

Dennis

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

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Shawn Wagner]

Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables
gcc extensions like inline asm.

On Thu, Nov 7, 2019, 7:57 AM Dennis Clarke  wrote:

>
> While building sqlite from the zip file sqlite-src-3300100.zip I saw
> what seems to be a normal configure and then madness :
>
>
> boe13$ pwd
> /opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001
> boe13$ ./configure --prefix=/opt/bw --enable-shared --enable-static
> --enable-readline --enable-threadsafe 2>&1 | tee
> ../sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001.config.log
> checking build system type... x86_64-pc-linux-gnu
> checking host system type... x86_64-pc-linux-gnu
> checking for gcc... /opt/bw/gcc9/bin/gcc
> checking whether the C compiler works... yes
> checking for C compiler default output file name... a.out
> checking for suffix of executables...
> checking whether we are cross compiling... no
> checking for suffix of object files... o
> checking whether we are using the GNU C compiler... yes
> checking whether /opt/bw/gcc9/bin/gcc accepts -g... yes
> checking for /opt/bw/gcc9/bin/gcc option to accept ISO C89... none needed
> checking for a sed that does not truncate output... /usr/bin/sed
> checking for grep that handles long lines and -e... /usr/bin/grep
> checking for egrep... /usr/bin/grep -E
> checking for fgrep... /usr/bin/grep -F
> checking for ld used by /opt/bw/gcc9/bin/gcc... /usr/bin/ld
> checking if the linker (/usr/bin/ld) is GNU ld... yes
> checking for BSD- or MS-compatible name lister (nm)...
> /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p
> checking the name lister (/opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm
> -p) interface... BSD nm
> checking whether ln -s works... yes
> checking the maximum length of command line arguments... 1572864
> checking whether the shell understands some XSI constructs... yes
> checking whether the shell understands "+="... yes
> checking for /usr/bin/ld option to reload object files... -r
> checking for objdump... /usr/bin/objdump
> checking how to recognize dependent libraries... pass_all
> checking for ar... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ar
> checking for strip... /usr/bin/strip
> checking for ranlib... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ranlib
> checking command to parse /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p
> output from /opt/bw/gcc9/bin/gcc object... ok
> checking how to run the C preprocessor... /opt/bw/gcc9/bin/gcc -E
> checking for ANSI C header files... yes
> checking for sys/types.h... yes
> checking for sys/stat.h... yes
> checking for stdlib.h... yes
> checking for string.h... yes
> checking for memory.h... yes
> checking for strings.h... yes
> checking for inttypes.h... yes
> checking for stdint.h... yes
> checking for unistd.h... yes
> checking for dlfcn.h... yes
> checking for objdir... .libs
> checking if /opt/bw/gcc9/bin/gcc supports -fno-rtti -fno-exceptions... no
> checking for /opt/bw/gcc9/bin/gcc option to produce PIC... -fPIC -DPIC
> checking if /opt/bw/gcc9/bin/gcc PIC flag -fPIC -DPIC works... yes
> checking if /opt/bw/gcc9/bin/gcc static flag -static works... no
> checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... yes
> checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... (cached) yes
> checking whether the /opt/bw/gcc9/bin/gcc linker (/usr/bin/ld -m
> elf_x86_64) supports shared libraries... yes
> checking whether -lc should be explicitly linked in... no
> checking dynamic linker characteristics... GNU/Linux ld.so
> checking how to hardcode library paths into programs... immediate
> checking whether stripping libraries is possible... yes
> checking if libtool supports shared libraries... yes
> checking whether to build shared libraries... yes
> checking whether to build static libraries... yes
> checking for a BSD-compatible install... /usr/bin/install -c
> checking for special C compiler options needed for large files... no
> checking for _FILE_OFFSET_BITS value needed for large files... no
> checking for int8_t... yes
> checking for int16_t... yes
> checking for int32_t... yes
> checking for int64_t... yes
> checking for intptr_t... yes
> checking for uint8_t... yes
> checking for uint16_t... yes
> checking for uint32_t... yes
> checking for uint64_t... yes
> checking for uintptr_t... yes
> checking for sys/types.h... (cached) yes
> checking for stdlib.h... (cached) yes
> checking for stdint.h... (cached) yes
> checking for inttypes.h... (cached) yes
> checking malloc.h usability... yes
> checking malloc.h presence... yes
> checking for malloc.h... yes
> checking for fdatasync... yes
> checking for gmtime_r... yes
> checking for isnan... yes
> checking for localtime_r... yes
> checking for localtime_s... no
> checking for malloc_usable_size... yes
> checking for strchrnul... yes
> checking for usleep... yes
> checking for utime... yes
> checking for pread... yes
> checking for pread64... yes
> checking for pwrite... yes
> checking for pwrite64... yes
> checking for tclsh8.7... tclsh8.7
> configu

[sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

[Dennis Clarke]

While building sqlite from the zip file sqlite-src-3300100.zip I saw
what seems to be a normal configure and then madness :


boe13$ pwd
/opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001
boe13$ ./configure --prefix=/opt/bw --enable-shared --enable-static 
--enable-readline --enable-threadsafe 2>&1 | tee 
../sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001.config.log

checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking for gcc... /opt/bw/gcc9/bin/gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether /opt/bw/gcc9/bin/gcc accepts -g... yes
checking for /opt/bw/gcc9/bin/gcc option to accept ISO C89... none needed
checking for a sed that does not truncate output... /usr/bin/sed
checking for grep that handles long lines and -e... /usr/bin/grep
checking for egrep... /usr/bin/grep -E
checking for fgrep... /usr/bin/grep -F
checking for ld used by /opt/bw/gcc9/bin/gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... 
/opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p
checking the name lister (/opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm 
-p) interface... BSD nm

checking whether ln -s works... yes
checking the maximum length of command line arguments... 1572864
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking for /usr/bin/ld option to reload object files... -r
checking for objdump... /usr/bin/objdump
checking how to recognize dependent libraries... pass_all
checking for ar... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ar
checking for strip... /usr/bin/strip
checking for ranlib... /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-ranlib
checking command to parse /opt/bw/gcc9/bin/x86_64-redhat-linux-gcc-nm -p 
output from /opt/bw/gcc9/bin/gcc object... ok

checking how to run the C preprocessor... /opt/bw/gcc9/bin/gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if /opt/bw/gcc9/bin/gcc supports -fno-rtti -fno-exceptions... no
checking for /opt/bw/gcc9/bin/gcc option to produce PIC... -fPIC -DPIC
checking if /opt/bw/gcc9/bin/gcc PIC flag -fPIC -DPIC works... yes
checking if /opt/bw/gcc9/bin/gcc static flag -static works... no
checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... yes
checking if /opt/bw/gcc9/bin/gcc supports -c -o file.o... (cached) yes
checking whether the /opt/bw/gcc9/bin/gcc linker (/usr/bin/ld -m 
elf_x86_64) supports shared libraries... yes

checking whether -lc should be explicitly linked in... no
checking dynamic linker characteristics... GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for a BSD-compatible install... /usr/bin/install -c
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for int8_t... yes
checking for int16_t... yes
checking for int32_t... yes
checking for int64_t... yes
checking for intptr_t... yes
checking for uint8_t... yes
checking for uint16_t... yes
checking for uint32_t... yes
checking for uint64_t... yes
checking for uintptr_t... yes
checking for sys/types.h... (cached) yes
checking for stdlib.h... (cached) yes
checking for stdint.h... (cached) yes
checking for inttypes.h... (cached) yes
checking malloc.h usability... yes
checking malloc.h presence... yes
checking for malloc.h... yes
checking for fdatasync... yes
checking for gmtime_r... yes
checking for isnan... yes
checking for localtime_r... yes
checking for localtime_s... no
checking for malloc_usable_size... yes
checking for strchrnul... yes
checking for usleep... yes
checking for utime... yes
checking for pread... yes
checking for pread64... yes
checking for pwrite... yes
checking for pwrite64... yes
checking for tclsh8.7... tclsh8.7
configure: Version set to 3.30
configure: Release set to 3.30.1
configure: Version number set to 3030001
checking whether to support threadsafe operation... yes
checking for library containing pthread_create... -lpthread
checking for library containing pthread_mutexattr_init... none required
checking whether to support shared library linked as release mode or 
n

Re: [sqlite] SQLite with branching

[Jens Alfke]

> On Nov 5, 2019, at 1:27 AM, Dominique Devienne  wrote:
> 
> AFAIK, that was one of the goals of SQLite4 [1], to change the backend to LSM.

LMDB (LiteTree's back-end) doesn't use LSM; it's a B-tree manager. The speedup 
appears to come from a combination of techniques like eliminating caching 
through memory-mapping, and eliminating locking through MVCC. There's a paper 
describing it in somewhat more detail[1].

I know there is memory-mapping support in SQLite, but it doesn't seem to result 
in as much speedup, and it has some nasty data-corruption bugs on macOS 
(possibly iOS too?)

LSM and LMDB would seem to have differing goals — LSM is best for applications 
with high write throughput, while LMDB is optimized more for read performance. 
I would guess that the latter is closer to the majority of SQLite use cases, 
since small/embedded systems tend to take in less data than big servers do.

—Jens

[1]: http://www.lmdb.tech/media/20120829-LinuxCon-MDB-txt.pdf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Dominique Devienne]

On Tue, Nov 5, 2019 at 10:01 AM Wout Mertens  wrote:

> On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke  wrote:
>
> > I don't have a practical use for the branching features, though they're
> cool, but I'm salivating at the thought of a 2x speedup.
> > With all the work that's put into eking out small performance increases
> in SQLite, I'd imagine the devs would be interested in
> > something that made that big of a difference...
>
> What I would like to know is how such a performance increase is
> achieved, and why regular SQLite can't do the same?
>

AFAIK, that was one of the goals of SQLite4 [1], to change the backend to
LSM.
We know now SQLite4 is basically abandoned, but LSM was refactored as an
SQLite3 extension [2].
Here's an article that goes into more depth on the subject [3]. Hope this
helps. --DD

[1] https://sqlite.org/src4/doc/trunk/www/index.wiki
[2] https://www.sqlite.org/src/dir?ci=5710845b6314f924&name=ext/lsm1
[3] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Wout Mertens]

On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke  wrote:

> I don't have a practical use for the branching features, though they're cool, 
> but I'm salivating at the thought of a 2x speedup.
> With all the work that's put into eking out small performance increases in 
> SQLite, I'd imagine the devs would be interested in
> something that made that big of a difference...

What I would like to know is how such a performance increase is
achieved, and why regular SQLite can't do the same?

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

Re: [sqlite] SQLite with branching

[Jens Alfke]

> On Nov 4, 2019, at 4:57 AM, Simon Slavin  wrote:
> 
> That's one of the reasons that the source code for SQLite is public: so that 
> people can add the features they want.

Totally agree. However, when you go off the mainline of SQLite you lose some 
things, like easy updating to new SQLite releases — you now have to deal with 
merging the new official SQLite into the forked SQLite, or waiting for the fork 
maintainer to do it.

In the case of LiteTree, I suspect the merge would be pretty difficult because 
of the extensive changes — it must be replacing the whole B-tree layer to be 
using LMDB as storage. (There was an earlier project called SQLightning that 
did the same thing. I was tempted by it, but it was based on an old version 
like 3.9 and the author made it clear he had zero interest in updating.)

I don't have a practical use for the branching features, though they're cool, 
but I'm salivating at the thought of a 2x speedup. With all the work that's put 
into eking out small performance increases in SQLite, I'd imagine the devs 
would be interested in something that made that big of a difference...

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

Re: [sqlite] SQLite with branching

[Simon Slavin]

The post you quoted points to exactly that: a version of SQLite that handles 
branches.  Check it out.

That's one of the reasons that the source code for SQLite is public: so that 
people can add the features they want.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Robert M. Münch]

@SQLite Guys: Do you have something like branching on your roadmap? I really 
like this feature and see a lot of use-cases beside the blockchain topic. And, 
of course if this works with your encryption extension that would be awesome.

* simple versioning of a database: Useful when you want to keep different app 
states (like complex calculations) and be able to go back and forth in time to 
see what data was used, what changed, what is the impact of that change.

* simple implementation of alternatives: create a branch and let the user do 
whatever the want. Later they can keep it or go back. I think using changesets 
can be a bit more challenging for such a use-case.

* I see such a feature as a natural companion to the session extension. Where 
versioning/branching works on the whole database.

Viele Grüsse. Robert M. Münch


On 29 Aug 2018, at 14:28, Simon Slavin wrote:

> I have no connection with the following project.
>
> 
>
> Described poorly on the web site so here's my own description:
>
> This is an extension of SQLite which allows branched versions, each new 
> branch creating one dataset which existed before the new branch and a new 
> dataset, initially a copy of the old dataset at some historical point, which 
> can be further modified.  Both the old and new branches can be further 
> branched.
>
> LiteTree is implemented storing the SQLite db pages on LMDB making it more 
> than twice as fast as normal SQLite on Linux and MacOSX, and also runs on 
> Windows.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Error 25: column index out of range when binding for a DATETIME

[Jeffrey Walton]

On Thu, Oct 31, 2019 at 9:52 AM Keith Medcalf  wrote:
> On Thursday, 31 October, 2019 07:17, Jeffrey Walton  
> wrote:
> ...
> >/* negative for days in the past */
> >int days = 120;
> >days = -days;
>
> >const char DELETE_STMT[] = "DELETE from blacklist " \
> >"WHERE dtime < datetime('now', '? days');";
>
> This statement contains no parameter.  You have a string constant with a ? 
> character inside the string.  Parameters go outside of constants, not inside 
> them.  Perhaps try something like this (which will work only if days is 
> negative):
>
> const char DELETE_STMT[] = "DELETE from blacklist " \
> "WHERE dtime < datetime('now', ? || ' days');";

Perfect, thanks.

That information may make good reading at
https://www.sqlite.org/lang_datefunc.html . I would never have figured
out the syntax on my own.

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

Re: [sqlite] [sqlite-dev] Error 25: column index out of range when binding for a DATETIME

[Keith Medcalf]

On Thursday, 31 October, 2019 07:17, Jeffrey Walton  wrote:

First, your question should go to the sqlite-users mailing list, not the 
developers mailing list.

>I'm having trouble binding a parameter for a DELETE. I am trying to
>delete records older than 120 days. Below, I have a table with a
>column dtime of type DATETIME. days is a dirty parameter specified by
>the user.

See https://sqlite.org/datatype3.html

>/* negative for days in the past */
>int days = 120;
>days = -days;

>const char DELETE_STMT[] = "DELETE from blacklist " \
>"WHERE dtime < datetime('now', '? days');";

This statement contains no parameter.  You have a string constant with a ? 
character inside the string.  Parameters go outside of constants, not inside 
them.  Perhaps try something like this (which will work only if days is 
negative):

const char DELETE_STMT[] = "DELETE from blacklist " \
"WHERE dtime < datetime('now', ? || ' days');";

You would not expect this to work would you:

 int days = -120;
 printf("Days = days\n");

>rc = sqlite3_prepare_v2(conn, DELETE_STMT, -1, &stmt, NULL);
>if (!SQLITE_SUCCESS(rc))
>{
>log_error("Failed to prepare query, Error (%d): %s\n",
>rc, sqlite3_errmsg(conn));
>errs++; goto finish;
>}
>
>rc = sqlite3_bind_int(stmt, 1, days);
>if (!SQLITE_SUCCESS(rc))
>{
>log_error("Failed to bind days, Error (%d): %s\n",
>rc, sqlite3_errmsg(conn));
>errs++; goto finish;
>}
>
-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] sqlite - AIX memory fault coredupm when using .output command [WARNING! - EXTERNAL]

[Warren Young]

On Oct 29, 2019, at 2:56 PM, Dawson, Jeff G  wrote:
> 
> SQLite version 3.7.14.1 2012-10-04 19:37:12

I infer that you’re migrating a legacy system.  There are two good alternatives 
to your current method that should avoid the symptom entirely:

1. Build a current version of SQLite for the old AIX system and try the dump 
with that instead.  It should read that old file just fine.

2. Copy the SQLite DB to the new system and work with it there.  If you’re 
moving away from both AIX and SQLite, you can do the dump on the modern system 
with a current version of SQLite, which should work much better.  If you’re 
sticking with SQLite on the new system, you don’t need the dump at all; just 
use the copied file on the new system.

If it happens that either path also crashes, you’re far more likely to get a 
fix for it than for this greatly outdated version of SQLite.

If you get another core dump with either alternative conversion path, please 
include the backtrace.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite - AIX memory fault coredupm when using .output command [WARNING! - EXTERNAL]

[Dawson, Jeff G]

Hello,

We are running sqlite version below on AX 7.2 TL 1.  We have a database around 
430MB and was trying to export the contents to a file shown below, done this 
with other databases but it seems there is some type of limitation possibly 
with the version of sqlite we are running?  We did open a ticket with IBM which 
looked at the core dump and said it was on the application side, sqlite.   We 
ended up doing 3 select statements using a unique number to pull files of about 
500k of rows to finally clear the table.  Wanted to see if anyone has come 
across this type of issue before.

SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from EpicADT;
1457871
sqlite> .output finalAllHospTable.txt
sqlite> select * from EpicADT ORDER BY acctNum;
Memory fault(coredump)


iscax_testcis02@hci (/cistest/cis6.2/integrator/sqlLiteDbs/JGD)
/>ls -altr
total 1378760
-rw-r--r--1 hci  staff 436788224 Oct 29 15:46 EpicADT_PROD1TBL.db
drwxrwxr-x   20 hci  staff  4096 Oct 29 15:48 ..
-rw-rw-r--1 hci  staff 0 Oct 29 15:48 finalAllHospTable.txt
drwxrwxr-x2 hci  staff   256 Oct 29 15:49 .
-rw-rw-r--1 hci  staff 269121503 Oct 29 15:49 core


Table schema

sqlite> .schema
CREATE TABLE EpicADT (msgDT TEXT, msgID TEXT, hosp TEXT, FirstName TEXT, MI 
TEXT, LastName TEXT, DOB TEXT, Gender TEXT, pt


AIX Power 8 + server 16 cpu's running

/>ulimit -a
time(seconds)unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes)unlimited
memory(kbytes)   unlimited
coredump(blocks) unlimited
nofiles(descriptors) 2
threads(per process) unlimited
processes(per user)  unlimited

Thanks,
Jeff

WARNING! – This email is from an external sender.  Do not click links or open 
attachments unless you know the sender.  Never give out your username and 
password.


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

Re: [sqlite] sqlite segfault bug report

[Simon Slavin]

On 28 Oct 2019, at 11:07am, Richard Hipp  wrote:

> Bisect:

Just to explain to the Korean students: you spotted a real bug, but very 
recently the bug was fixed.

However, your work is useful.  Please download the current version of SQLite 
from



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

Re: [sqlite] sqlite segfault bug report

[Richard Hipp]

On 10/28/19, Graham Holden  wrote:
> Monday, October 28, 2019, 5:46:55 AM, ???  wrote:
>
>> Code:
>
>> BEGIN;
>> CREATE TABLE t1(a);
>> ALTER TABLE t1 ADD c CHECK (b>c);
>> PRAGMA writable_schema=on;
>> INSERT INTO t1 VALUES(2,3);
>
>> Version: 3.29.0, 3.22.0
>
> On "SQLite version 3.30.1 2019-10-10 20:19:45" this returns an
> error after the third line:
>
> Error: no such column: b

Bisect:

https://sqlite.org/src/timeline?bid=ya1e1ba9145nfc82b73eaan596ac2a4ean361eb2f682yffd4c30620n32fba11ab7y75775c5ab4y238e083571y5d76dbc5b0y31e85fbbc4


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

Re: [sqlite] sqlite segfault bug report

[Graham Holden]

Monday, October 28, 2019, 5:46:55 AM, ???  wrote:

> Code:
 
> BEGIN;
> CREATE TABLE t1(a);
> ALTER TABLE t1 ADD c CHECK (b>c);
> PRAGMA writable_schema=on;
> INSERT INTO t1 VALUES(2,3);
 
> Version: 3.29.0, 3.22.0

On "SQLite version 3.30.1 2019-10-10 20:19:45" this returns an
error after the third line:

Error: no such column: b


Regards,
Graham Holden


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

[sqlite] sqlite segfault bug report

[우병수]

Code:
 
BEGIN;
CREATE TABLE t1(a);
ALTER TABLE t1 ADD c CHECK (b>c);
PRAGMA writable_schema=on;
INSERT INTO t1 VALUES(2,3);
 
Version: 3.29.0, 3.22.0 
 
Hello, we are students from Korea.
We learned about fuzzing and tried to fuzz on sqlite3, and we found a piece of 
code which makes crash. 
 
When we try to insert values, segmentation fault occured.
 
So, we tried to analyze the reason and we found that the program consider b as 
tk_id, not a column.
Thus, when we try to insert the value, the program try to access 
"pEList->nExpr"(sqlite3.c:101266) whose value is NULL.
 
If we change b>c into c>b in the third line, b is considered as column. So, we 
think there is a problem when unknown variable appears first in constraint 
statement.
 
Please check about this. Thank you! 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Jose Isaias Cabrera]

Roman Fleysher, on Thursday, October 10, 2019 04:31 PM, wrote...

> Imagine a vehicle which has wheels to drive on a road. And wings to fly and
> a hull to float. How nice and happy world would be !? True? Of course true.
>
> But, this vehicle would be bad as a car, bad as an airplane and bad a boat.
> Why? Because it is too hard to cram all the features and keep it small and
> light. But if you have to have one, you can.
>
> The goal of SQLite is to be light. It is much easier to use and versatile
> thanks to its size.

Yeah... We're completely off the subject. Sorry.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Warren Young]

On Oct 10, 2019, at 2:03 PM, Jose Isaias Cabrera  wrote:
> 
> 2. The normal SQlite snapshots plus a series of libraries and functions that 
> can easily be compiled with the original light SQLite.

This is basically the model for FOSS OS distros: a bunch of people get together 
and work on a coherent full-fat alternative to the scattered individual 
features.

Sometimes these people band together under the aegis of a company (e.g. Red 
Hat) and other times it’s a largely volunteer effort (e.g. FreeBSD).  Either 
way, it’s a lot of work, and there’s a pretty high failure rate in terms of the 
number of projects started vs those that end up being big.

I think if SQLite could support such a thing, we’d have seen it emerge already, 
probably from underneath one of the GUI front end projects as a freemium 
come-on for the company’s paid offering.

But hey, go prove me wrong and pull a Red Hat atop SQLite.
_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Roman Fleysher]

It's ok that you don't believe in it, but the last statement, you know is not 
true.  I have been in this list since 2003 or so, and constantly there is one 
or two request to Dr. Hipp and the owners, per month, to add "stuff" to it.  
Or, things like, "I would love to have SQLite do...", etc.  First of all, there 
are already folks that have written pieces of code that they use themselves for 
their projects.  Imagine if these become available to the rest of the world, it 
would be a nice happy programming place. :-) But, I accept your preclusion of 
being the maintainer. :-)

Imagine a vehicle which has wheels to drive on a road. And wings to fly and a 
hull to float. How nice and happy world would be !? True? Of course true.

But, this vehicle would be bad as a car, bad as an airplane and bad a boat. 
Why? Because it is too hard to cram all the features and keep it small and 
light. But if you have to have one, you can.

The goal of SQLite is to be light. It is much easier to use and versatile 
thanks to its size.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Jose Isaias Cabrera]

Roman Fleysher, on Thursday, October 10, 2019 04:17 PM, wrote...
>
> With your brain excluded, who is "we"?
>
> The beauty of SQLite is that SQL was distilled to the smallest and most 
> reproducible
> on many platforms set. Over time, I see how developers expand functionality 
> to make it
> more convenient while maintaining reproducibility across platforms. Time, 
> date, math
> operations are extremely hard to make reproducible. SQLite is small in SQL 
> already: It
> has LEFT JOIN and does not have RIGHT JOIN. Why? Because RIGHT can be made 
> out of LEFT
> by swapping order of tables. That is how small it is. The new functionality 
> should be
> more substantial than swapping the order of tables or adding extra SQL line 
> of code for
> date manipulation.

Nothing is going to change about this. That will continue to be for 50 years, 
or so.

> More over, SQLIte already has extension mechanism and many libraries have 
> been written:
>
> https://www.sqlite.org/contrib/

Thanks. I didn't know this.

> The "we" can add to it date manipulation routines and many other.

This is what I am talking about.  But now, more purposely driven.

> SQLite is a beauty. Learn to see it!
I saw it, and I loved it.

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

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Thomas Kurz]

> It has LEFT JOIN and does not have RIGHT JOIN. Why? Because RIGHT can be made 
> out of LEFT by swapping order of tables.

The paradigma of SQL is to let the user describe what he wants to do, not to 
think about how to describe the problem so that the database system does 
understand. The lack of RIGHT JOIN, FULL INNER/OUTER JOIN, (full) ALTER TABLE, 
missing geospatial functions, etc. is a huge disadvantage, not an advantage!

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