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

[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 <http://gnu.org/licenses/gpl.html>
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:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
<http://www.gnu.org/software/gdb/documentation/>.
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
 

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

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

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 21:50:18 CET Simon Slavin wrote:
> On 16 Feb 2020, at 8:44pm, Stefan Brüns  
wrote:
> > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
> >>> One use case I am aware of (although this targets places.sqlite, not
> >>> cookies.sqlite) is reading the history, bookmarks and tags.>> 
> >> These things can be done using the bookmarks API, WebExtensions API, and
> >> other methods.  Reading the SQLite database is actually more difficult.> 
> > AFAIK this only works while FF is running ...
> 
> That is the problem that started this thread: that the database file could
> not be opened while FF was running.

The database being inaccessible while FF is running does not equate to FF is 
always running. Both cases (with and without FF running) have to be covered.

And having to write two different access methods (direct access and through 
bookmarks API) is obviously the worst of all variants.

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] Is it possible to dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:

> > One use case I am aware of (although this targets places.sqlite, not 
> > cookies.sqlite) is reading the history, bookmarks and tags.
> These things can be done using the bookmarks API, WebExtensions API, and
> other methods.  Reading the SQLite database is actually more difficult.

AFAIK this only works while FF is running ...

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] Is it possible to dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 18:36:15 CET Simon Slavin wrote:
> On 16 Feb 2020, at 5:15pm, Peng Yu  wrote:
> > Why the database can not be read by another sqlite3 session when the
> > corresponding -wal file exists? Thanks.
> 
> This is done on purpose by the developers of Firefox to prevent a security
> vulnerability which I will not describe in public.

Will this stop anyone from just copying the DB without the -wal file? 
Afterwards, the DB can be read, as there is no longer any associated log.

> One of the Mozilla developers involved in the decision reads this list.  If
> you have a good reason why you want to open the SQLite database while
> Firefox is running, you could post it here.  You might be able to persuade
> them to reconsider the decision.

One use case I am aware of (although this targets places.sqlite, not 
cookies.sqlite) is reading the history, bookmarks and tags.

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

[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

[sqlite] Truncation/rounding error in strftime(x, 'unixepoch')

[Stefan Brüns]

Hi,

some time ago there was an error reported when running the testsuite on ix86, 
in the date.test/date-2.2c-*.

The error happens as a string like 1237962480.003 gets parsed and rounded to 
1237962480.002999... and is later truncated to 1237962480.002.

The rounding error happend in date.c:parseModifier(...):
p->iJD = (sqlite3_int64)r;

i.e. the double r is truncated by casting it to int.

Doing the following change fixes the error on ix86, and lets the testsuites
pass on i586, x86_64, aarch64, ppc64le, ...

- p->iJD = (sqlite3_int64)r;
+ p->iJD = (sqlite3_int64)(r + 0.5);

Also compare with date.c:setRawDateNumber(...), where rounding is already 
applied:

p->iJD = (sqlite3_int64)(r*8640.0 + 0.5);

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] oserror-2.1.x fails when run on an BTRFS volume - directories may have any st_size.

[Stefan Brüns]

On Donnerstag, 26. Dezember 2019 02:01:36 CET Richard Hipp wrote:
> Tnx for the report.  Should be fixed as of
> https://sqlite.org/src/info/c8c6dd0e6582ec91
> 
> Please do us the favor of trying this out on both Btrfs and XFS and
> making sure it works correctly on both filesystems.  Tnx.

Thanks for the quick fix. I did a quick test on BTRFS and it works, XFS is 
pending.

The fix requires a small amendment though, there are other types than 
directories which have an st_size of 0, sockets, pipes, ...

The size check probably should only be done for regular files - symlinks can 
never have a size of 0, and all others are implementation defined.

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

[sqlite] oserror-2.1.x fails when run on an BTRFS volume - directories may have any st_size.

[Stefan Brüns]

The oserror-2.1.1 test fails, as a exisiting test.db-wal is silently ignored.

Running this small example on e.g. XFS or tmpfs yields the following:
$> mkdir test.db-wal
$> strace -efile sqlite3 test.db
...
sqlite> .databases
openat(AT_FDCWD, "test.db", O_RDONLY)   = -1 ENOENT (Datei oder Verzeichnis 
nicht gefunden)
lstat("test.db", 0x7ffc38d3b8c0)= -1 ENOENT (Datei oder Verzeichnis 
nicht gefunden)
getcwd("/home/stefan", 511)     = 13
openat(AT_FDCWD, "/home/stefan/test.db", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 3
stat("/home/stefan/test.db", {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
stat("/home/stefan/test.db-journal", 0x7ffc38d3a730) = -1 ENOENT (Datei oder 
Verzeichnis nicht gefunden)
stat("/home/stefan/test.db-wal", {st_mode=S_IFDIR|0755, st_size=6, ...}) = 0
unlink("/home/stefan/test.db-wal")  = -1 EISDIR (Ist ein Verzeichnis)
Error: disk I/O error

Doing the same on an btrfs volume:
$> mkdir test.db-wal
$> strace -efile sqlite3 test.db
...
sqlite> .databases
stat("/var/tmp/t/test.db-journal", 0x7fff01578140) = -1 ENOENT (Datei oder 
Verzeichnis nicht gefunden)
stat("/var/tmp/t/test.db-wal", {st_mode=S_IFDIR|0755, st_size=0, ...}) = 0
stat("/var/tmp/t/test.db-journal", 0x7fff015790e0) = -1 ENOENT (Datei oder 
Verzeichnis nicht gefunden)
stat("/var/tmp/t/test.db-wal", {st_mode=S_IFDIR|0755, st_size=0, ...}) = 0
main: /var/tmp/t/test.db
sqlite> 

The culprit is the st_size check in os_unix.c/unixAccess:
...
  if( flags==SQLITE_ACCESS_EXISTS ){
struct stat buf;
*pResOut = (0==osStat(zPath, ) && buf.st_size>0);
  }else{
...

Changing it to ".. && ((buf.st_size>0) || (buf.st_mode & S_IFMT == 
S_IFDIR)));" fixes the problem.


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

[sqlite] Misleading comment in various test/fts*.test files

[Stefan Brüns]

In the various test files, there (in most cases) is a misleading comment.

In i.e. fts3expr.test, the comment is inverted, in fts3fault2 it is correct:


test/fts3expr.test:# If SQLITE_ENABLE_FTS3 is defined, omit this file.
test/fts3expr.test-ifcapable !fts3 {
--
test/fts3fault2.test:# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
test/fts3fault2.test-ifcapable !fts3 { finish_test ; return }

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

[sqlite] fts3corrupt4-33.0 depends on SQLITE_ENABLE_ICU

[Stefan Brüns]

The test added in https://www.sqlite.org/src/info/e01fdbf9f700e1bd

requires icu to run successfully. Otherwise it errors out with:

! fts3corrupt4-33.0 expected: [1 {database disk image is malformed}]
! fts3corrupt4-33.0 got:  [1 {unknown tokenizer: icu}]

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] SQL Murder Mystery

[Stefan Evert]

> On 16 Dec 2019, at 04:59, Simon Slavin  wrote:
> 
> A little light relief:
> 
> 
> 
> Chosen SQL variation is SQLite !


Nice, but the crime is so easy to solve …

SELECT CAST(x'4A6572656D7920426F77657273' AS TEXT);

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

Re: [sqlite] Regarding CoC

[Stefan Evert]

> On 23 Oct 2018, at 07:04, Paul  wrote:
> 
> If my opinion has any value, even though being atheist, I prefer this CoC 100 
> times over
> the CoC that is being currently pushed onto the many open-source communities, 
> that was
> created by some purple-headed feminist with political motives. This one does 
> not have 
> any hidden intentions (at least, it seems so to me, knowing that you're 
> honest person).

Exactly my feelings.

– Stefan

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

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

Hi Simon,

thanks for the hint. I won't do that.
--
Stefan


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

[sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

I'm working on a database application that is shut down quite often. At each 
shutdown, only a small amount of data is written to the DB, which is in WAL 
mode. Due to HW restrictions, I had to reduce the total amount of data written 
to disk, which is why I have activated SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE.

This works fine as long as TRUNCATE checkpoints are issued from within my code. 
However, since the amount of data that is written into the DB at each shutdown 
varies, it is suboptimal to just trigger the checkpoint at each n-th shutdown. 
To get a better trade-off between the amount of written data and WAL size, it 
would be nice to trigger the checkpoints based on the size of the WAL. It seems 
that PRAGMA journal_size_limit doesn't help here: the WAL file grows infinitely 
when no checkpoints are issued from within my code. Is this the expected 
behavior (I guess so) or a configuration problem?

Note that PRAGMA wal_autocheckpoint also doesn't help, but this is pretty 
obvious because it only triggers a PASSIVE checkpoint. I would rather not 
access the WAL file directly (bypassing SQLite) for checking it's size. Does 
anybody have a proper solution for this problem?

P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is 
called) to delete/truncate the WAL when journal_size_limit is reached. I had to 
change only two lines, but of course I am not sure that my change doesn't break 
anything.
--
Stefan Kniep

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

[sqlite] SQLite used in our network engine (SLikeNet)

[Stefan Hett]

Hi,

I'd just like to quickly reach out to let you know that we released a new (open 
source) network engine in which we also utilize SQLite and want to thank you 
for the work you put into SQLite.

SQLite is used as the backend db for the logging mechanism utilized in the 
network engine.

In order to not have this acknowledgement mail be misinterpreted as an 
advertisement mail from our side, I refrained from adding links to the project 
pages. If you are interested in the project, feel free to send me a mail and 
I'll give you the links to the project pages.

Please note that I'm not subscribed to the mailing list and would appreciate if 
you could CC me directly if you chose to reply to this mail.

-- 
Regards,
Stefan Hett, CEO SLikeSoft

SLikeSoft UG (haftungsbeschränkt), Dresdener Str. 8, 52068 Aachen, Germany
Tel: +49 157 74050303
Geschäftsführer: Stefan Hett, Handelsregister Aachen HRB 20706

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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Stefan Evert]

> On 6 Sep 2016, at 10:19, Stephan Beal  wrote:
> 
> A counter-opinion, though apparently in the small minority: i _absolutely
> despise_ fixed-width web site layouts.

+1, including layouts with fixed maximum width.


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

[sqlite] Unexpected behavior in LEMON parser generator?

[Kraus, Stefan]

Hello,

i have a question regarding the LEMON parser generator:

I am writing an expression grammar. The token LT means "<", while GT means ">".
When i use this grammar, everything works as expected:

%nonassoc  LT GT .
ex ::= ex LT ex .
ex ::= ex GT ex .

There are no conflicts, and the non-associativity is handled correctly, e.g. 
the expression "2 < 3 < 4" is reported as a syntax error.

However, changing the grammar to

%nonassoc  COMPARISON .
ex ::= ex LT ex . [COMPARISON]
ex ::= ex GT ex . [COMPARISON]

does not work as i expected. First, the expression "2 < 3 < 4" is not reported 
as a syntax error, but is processed instead. Moreover, parsing conflicts are 
reported between LT and GT. I thought that both versions should be equivalent?

Do i make a mistake or is that a bug? I have tried the above example in the 
LIME parser generator for PHP, which is described as ported from LEMON, and 
there it worked as expected (using the %prec directive). I will need this 
feature (setting precedence in a rule) for a domain specific language with a 
rather complex expression grammar.

Greetings + Thanks for Helping
Stefan

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

[sqlite] Sqlite incompatibility with Postgres

[Stefan Evert]

> On 18 May 2016, at 02:41, dandl  wrote:
> 
> Then you are mistaken. 
> 1. Consider the following set S of integers: 1,3,5,7,42,99,83,11.
> 2. Divide it into two subsets such that S1 is of size 3 and all members of
> S1 are larger than those in S2.
> 
> A sort is unnecessary -- there are many algorithms that can do that purely
> based on set logic, the ability to compare members and the ability to
> determine the cardinality of a set.

I think the point is that your query may not have a solution if there are ties, 
i.e. the ordering is not complete:

Consider this set of integers: 1,3,5,7,42,99,83,11,83,83

In this case, there is no subset S1 of size 3 that satisfies your criterion.  
In an SQL query, the set returned by LIMIT 3 would not be defined uniquely.

Best,
Stefan Evert

[sqlite] Possible error using length on UTF-8 characters

[Meinlschmidt Stefan]

> No.  I think you are confused about how UTF8 works.
> 
> The hexadecimal shown above is a 6-character string as follows:
> 
>  "\u00c2\u00ac\u00c2\u00ac\u00c2\u00ac"
> 
> You are wanting this three-character string:
> 
>  "\uc2ac\uc283\uc283"
> 
> The hex encoding of the string you want would be:
> 
>  "EC8AACEC8AACEC8AAC"

I suspect that someone confused Code Points (=characters, letters) with
encoded bytes in memory, leading to double encode. The canonical
recommended read for that topic would be
http://www.joelonsoftware.com/articles/Unicode.html

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Possible error using length on UTF-8 characters

[Meinlschmidt Stefan]

> sqlite> select hex(id) from operators;
> C382C2ACC382C2ACC382C2AC

This is hex utf8 for "??", i.e. 3? 0xc2 0xac, which again is utf8
for "?" (0xac)

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Meinlschmidt Stefan]

> Is this on windows?

The original problem was on QNX 6.5 employing a QNX6 filesystem on mNAND
flash.

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Meinlschmidt Stefan]

> ===
> Like the user reading ?saving OK? and throwing away the
> Post-It with the original information
> ===
> 
> This is exactly my concern.
> The user throwing away the Post-It is entirely reasonable if he sees a
> message like that.
> 
> Do you happen to know if Linux/Debian (which I think uses a journalling
> filesystem) carries this risk?

I do not know Linux' current behaviour, especially with recent
filesystems. I seem to remember from last century that Linux by default
automatically issues a sync every 30s. So every filesystem that is
writable and uses a write-back-cache (i.e. that doesn't right writeaway)
would have a 0s?30s amnesy window. (V)FAT would be a candidate, but SD
cards usually are parameterized at mount to actually write through, i.e.
are less efficient but have no problem. I have no clue what journalling
file systems do here.

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Meinlschmidt Stefan]

>> Using the standard defaults (which avoid WAL), is there any
>> possibility whatsoever of that last SQL transaction being lost?
> 
> I have an unusual answer: Yes, and it doesn't matter.  

While I agree in principle, your answer depends on some assumptions that
need not hold.

> Let's suppose, as you did, that the application got back "Commit
> Successful" followed quickly by a power failure.  You want to know,
> could the transaction be lost anyway?  But I ask you, what action could
> the application possibly take, in that subsecond interval, that it
> matters?

Under the QNX OS using a QNX6 filesystem with default configuration,
that ?subsecond interval? is actually up to 10s. For any non-journalling
filesystem (SD cards, anyone?) mounted without immediate write-through
(for efficiency) on Linux the interval can be, IIRC, up to 30s. So how
much can happen in this period is very sensitive to details not
necessarily under control of or even available to the SQLite user.

The application could for example write to some non-SQLite storage
(other file system, raw flash, physical journal printout, ?) and try to
guarantee global consistency by waiting for the SQLite transaction to
complete. Like the user reading ?saving OK? and throwing away the
Post-It with the original information. Or (what we did) it could shut
off device power.

> There is no God's-eye view of application state.  The important service
> provided by the DBMS is not "what's committed is definitely saved", but
> rather that "what's committed is definitely *consistent*".

So when your application requires consistency of some broader scope, you
need the DBMS give you enough rope^h^h^h^h^h^h^h^h^h^h^hthe tools to
implement that yourself. Without a durability guarantee you're screwed.

The more frequent simpler usecases of course are not susceptible to that
and then indeed it doesn't matter.

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Meinlschmidt Stefan]

Hi Rowan!

>> Shutting down power right after a successfully committed
>> transaction rolls back that transaction on next startup.
> 
> nitpick: This is sqlite behaving as advertised. See
> https://www.sqlite.org/lockingv3.html section 5.0 step 6, and
> https://www.sqlite.org/atomiccommit.html section 3.11 which explain that
> the presence of a [well-formed] journal file is the mechanism via which
> sqlite discriminates between a committed and in-progress transaction.
> 
> ie. according to sqlite a transaction is *not* successfully committed if
> the journal file is still present, so its well within its rights to
> rollback in this scenario.

Yes, a hot journal file means an incomplete transaction and should
absolutly roll back. What actually bugs me is that this happens after
COMMIT has returned without an error.

> That said, syncing the directory doesn't sound like a terrible idea. But
> it's not clear to me that the cost of another sync every transaction is
> worth a marginal reduction in the power-failure-leads-to-rollback window.

The actual costs are not clear to me either. I hope that someone on the
list with more experience in that field knows more.

> That's if it even reduces the window; it wouldn't surprise me to find that
> the journal is removed from disk after the same delay both with and without
> dirsync, the difference being that the dirsync prevents sqlite from
> returning control to your code until its certain the commit has persisted.

The filesystem used (QNX6) is advertised as performing a full sync on
every fsync and automatically some delay time after each write (10s),
and it looks like it is really doing so. If it wouldn't sync on fsync,
or if fsync would just wait until the automatic sync is through, I would
surely open a ticket with QNX and have some stern words with their resident.

That said I would expect the dirsync to shorten the window of
unnecessary rollback by an average of 5s in our case, which may or may
not be relevant to other users. On other filesystems with longer times
until it syncs anyway (I remember 30s from my first Linux box) the
reduction might be more substantial.

> There's certainly a surprising result here:
> 
> if (sqlite3_exec("COMMIT") == SQLITE_OK) {
> /* post-commit logic */
> }
> 
> Logically, the code in the if block can reasonably assume that the
> transaction will not rollback.

And that's where the potential for a tradeoff lies. Should SQLite
guarantee that in post-commit the transaction is done, durably, even for
the cost of the additional? Or should it not guarantee durability an be
a bit (or much, I don't know) faster?

Personally I tend to assume a database is made for guaranteeing ACID
properties, except when the user explicitly decides otherwise. So in my
eyes an fsync plus possibly an #ifdef for explicitly disabling it would
be the cleanest thing. OTOH it might surprise users that are used to the
higher performance/lower load and don't care for durability, so an
#ifdef for explicitly enabling the fsync instead might still be a good
compromise.

> But as you have discovered this is not
> always true with JOURNAL_MODE=DELETE unless the dirsync is performed. To be
> fair I don't think there are many power-failure scenarios where the
> post-commit logic would have a chance to do anything significant, so the
> incorrect assumption will usually be moot.

Yes, in a normal power failure you ?only? win more time, a few seconds
or more, depending on the filesystem's details.

BTW., as you have mentioned JOURNAL_MODE=DELETE?I have not examined what
happens in the other journal modes, i.e. I'm not sure whether
invalidating the journal by other means has the same lack of sync or
not. A proper patch might need to involve the other modes, too.

> In your case it sounds like a controlled shutdown - is there a reason you
> don't do a full disk sync before that?

Yes, it is a controlled shutdown, so in my case the /* post-commit logic
*/ basically pulls the plug.

Trouble is that I only control the database, not the shutdown procedure
(this is a commercial product with several hundred people working on
different aspects of the system). So while I can try to ask the shutdown
crew to sync like any real computer would do, I ultimately have no
saying in that but still need to ensure survival of data.

Without the patch (which I do have applied locally, of course), me and
everyone with a similar usecase get into trouble for relying on the
quite excellent reputation of SQLite. Actually I first fingerpointed to
the flash hardware or its driver, because ?SQLite is well-tested and
doesn't have this kind of bugs? :-)

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Meinlschmidt Stefan]

ardbook/test/uncommit.sqlite-journal,No error
> 3252313,1,,UNLINK,/mnt/boardbook/test/uncommit.sqlite-journal,No error
> 3252313,1,,OPEN,/mnt/boardbook/test,No error,r
> 3252313,1,,FSYNC,/mnt/boardbook/test,No error
> 3252313,1,,CLOSE,/mnt/boardbook/test,No error
> 3252313,1,,LOCK,/mnt/boardbook/test/uncommit.sqlite,No error,
> 3252313,1,,LOCK,/mnt/boardbook/test/uncommit.sqlite,No error,
> 3252313,1,,LOCK,/mnt/boardbook/test/uncommit.sqlite,No error,
> 3252313,1,,STAT,/mnt/boardbook/test/uncommit.sqlite,No error,file
> 3252313,1,,OPEN,/mnt/boardbook/test/uncommit.sqlite,No error,
> 3252313,1,,STAT,/mnt/boardbook/test/uncommit.sqlite,No error,file
> 3252313,1,,CLOSE,/mnt/boardbook/test/uncommit.sqlite,No error
> 3252313,1,,CLOSE,/mnt/boardbook/test/uncommit.sqlite,No error
> POWER OFF NOW!

Yank, reboot,

> # ls -l
> total 20
> drwxr-xr-x  2 root  root   4096 Jan 01 00:01 .
> drwxrwxr-x  4 root  root   4096 Jan 01 00:02 ..
> -rw-r--r--  1 root  root   2048 Jan 01 00:01 uncommit.sqlite

No zombie journal file this time, as expected, as unlinking the journal
is followed by fsyncing the directory. The same patch also fixes our
original problem.

I found some old email exchange about seemingly the same topic (see
http://sqlite.1065341.n5.nabble.com/Journal-deletion-no-directory-fsync-td37700.html),
but no conclusion. Therefore I'm not sure whether this is a genuine bug
or merely a tradeoff ?it's more efficient that way and when power fails
a slight bit earlier the transaction is interrupted anyway?.

Please consider adding the fix to the code, at least as -D option.
SQLite journal mode doesn't guarantee durability otherwise.

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531
-- next part --
An embedded and charset-unspecified text was scrubbed...
Name: Makefile
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160119/defdfb22/attachment.ksh>

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Stefan Keller]

As of SQL:2003, arrays are standard SQL types.
Examples of standard syntax:
  BIGINT ARRAY
  BIGINT ARRAY[100]
  BIGINT ARRAY[100] ARRAY[200]

It's fully implemented e.g. in PostgreSQL...

-S.


2015-04-27 18:48 GMT+02:00 Drago, William @ CSG - NARDA-MITEQ
:
> All,
>
> I've been enjoying this discussion and have learned a thing or two from all 
> the suggestions.
>
> My particular problem is indeed solved. Adding a simple blob to my original 
> table is the best solution in this case. If you think about the trace data as 
> simply a screenshot of the analyzer display then I think it is easier to 
> understand why I don't want to bother with more complex solutions. Even 
> though they may be technically more correct, they are overkill for what I'm 
> trying to accomplish. I'm just saving a screenshot, minus the graticule and 
> all the other decorations on the display. There's no need to make it any more 
> complicated than that. I don't even have to save the X-axis elements as those 
> are derived from other parameters that are already known. Someone asked if 
> the trace data was time stamped. It is not. Date and time of the test are 
> stored along with all the other relevant information.
>
> If I ever have to deal with complex numbers as my core data I have some 
> pretty good ideas on how to handle them now. BTW, the languages I use most, 
> VEE & C#, both support complex data types. Given the amount of use database 
> programs see in the scientific world I'm surprised there is no native complex 
> data type (I can appreciate the difficulty in implementing such a thing). 
> Array support for each type would be nice too. We have this in VEE and it is 
> so easy to use. But for now every programmer working in science or 
> engineering has to re-invent the wheel.
>
> For the curious here are the analyzers I'm working with:
> http://www.keysight.com/en/pc-101745%3Aepsg%3Apgr/pna-network-analyzers-300-khz-to-11-thz?cc=US=eng
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
>> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
>> Sent: Sunday, April 26, 2015 9:56 PM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] SQLite and Scientific Computing: Arrays and Complex
>> Numbers
>>
>> The original thread asking about an array of complex numbers has been
>> marked as "solved." The requester has decided to serialize the complex
>> numbers and store them in a blob. Earlier, Keith had suggested storing
>> complex numbers as a pair of real numbers and a separate box table. I
>> extended Keith's suggestion with two or three tables, elements, arrays
>> and optionally coordinates.
>>
>> There is some literature on storing arrays in SQL databases. In
>> addition complex numbers seem to be the orphan stepchild of programming
>> languages (let alone databases). Although FORTRAN IV had complex
>> numbers they were not added to the C standard until C99.
>>
>> Language / Standard / Library
>> 
>> C / C99/ complex.h
>> http://en.wikibooks.org/wiki/C_Programming/C_Reference/complex.h
>> C# / 4.0 / System.Numerics.Complex
>> https://msdn.microsoft.com/en-
>> us/library/system.numerics.complex(v=vs.110).aspx
>>
>> Java /?/ Apache Commons
>> Python/2.6.5/ cmath
>> https://docs.python.org/2/library/cmath.html
>>
>> http://en.wikipedia.org/wiki/Complex_data_type
>>
>> So, I suppose if a company wanted to sponsor it, complex numbers could
>> be supported through an addin library similar to FTS3 and FTS4 for full
>> text searches.
>> http://sqlite.org/fts3.html
>>
>> Here for example, is a discussion on IBM DeveloperWorks concerning the
>> Informix database.
>> https://www.ibm.com/developerworks/community/blogs/gbowerman/entry/sql_
>> and_the_complex_plane?lang=en
>>
>> Some databases have Abstract Defined Type (Oracle) or User Defined
>> Types (Microsoft SQL Server) that could be used for complex numbers.
>>
>> Ironically, the scientific data format NetCDF did not have provision
>> for complex numbers (it was designed for weather data).
>> https://www.unidata.ucar.edu/mailing_lists/archives/netcdfgroup/2011/ms
>> g00027.html
>>
>> There are some discusssions of scientific versions of SQL (such as
>> SciQL):
>>
>> "A Query Language for Multidimensional Arrays:
>> Design, Implementation, and Optimization Techniques"
>> http://homepages.inf.ed.ac.uk/libkin/papers/sigmod96a.pdf
>>
>> "Requirements for Science Data Bases and SciDB"
>> http://www-db.cs.wisc.edu/cidr/cidr2009/Paper_26.pdf
>>
>> "SciQL, A Query Language for Science Applications"
>> http://homepages.cwi.nl/~zhang/papers/arraydb11.pdf
>>
>> Jim
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> 

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

[Stefan Keller]

Hi Navin

I've compared with PostgreSQL. It's twice as as "fast" as SQLite with
100 mio. records on my old laptop - but still too slow using count().

So, as Eduardo suggested, you have to solve this problem with a
separate table and triggers, like shown below.

Yours, S.

-- Create test table
CREATE TABLE test (id INTEGER PRIMARY KEY, r REAL, t TEXT );

-- Fill test table with test data...!
INSERT INTO test (r,t)
  WITH RECURSIVE
cte(r,t) AS (
   SELECT random(), hex(random()*1000)
   UNION ALL
   SELECT random(), hex(random()*1000)
 FROM cte
 LIMIT 1000 -- 10 mio.
  )
  SELECT * FROM cte;

-- Create auxiliary table
CREATE TABLE count_statistics (
  table_name TEXT not NULL,
  row_count INTEGER
);

CREATE TRIGGER count_statistics_delete_test
BEFORE DELETE ON 'test'
FOR EACH ROW BEGIN
  UPDATE count_statistics SET row_count=row_count-1 WHERE table_name='test';
END;

CREATE TRIGGER count_statistics_insert_test
BEFORE INSERT ON 'test'
FOR EACH ROW BEGIN
  UPDATE count_statistics SET row_count=row_count+1 WHERE table_name='test';
END;

-- initialize/update/reset count_statistics
UPDATE count_statistics SET row_count=(
  SELECT count(*) FROM test
) WHERE table_name='test';


.timer on
SELECT max(id) FROM test;
SELECT count(*) FROM test; -- slow!

-- This is the count replacement:
SELECT row_count FROM count_statistics WHERE table_name='test'; -- fast!

-- Done.

2015-01-25 20:05 GMT+01:00 Navin S Parakkal <nav...@hp.com>:
>
> On Saturday 24 January 2015 03:15 PM, Stefan Keller wrote:
>>
>> I think it's time for a serious simple benchmark with sqlite and say
>> PostgreSQL.
>> PostgeSQL also had performance problems time ago but this has been
>> resolved.
>> Can you describe the hp_table1 schema (CREATE TABLE statement...) and
>> some data (INSERTs)?
>>
>>
> Yes though a avid fan of sqlite, have to reconsider it for scalability
> issues. Everything about the table is present in the archives and thread .
>
>
>
> On Sunday 25 January 2015 06:17 AM, Eduardo Morras wrote:
>>
>> You can use PostgreSQL, using part of Oracle licence cost for better
>> hardware and a dinner for the team.
>>
> Yes that is an option we have to consider seriously. I thought we were doing
> something wrong like creating the index or maybe tuning page size.
>
>
> I'm still stuck.
>
>
>
>
> I maybe completely wrong but i'm guessing your B-tree index is static.
> Something in that doesn't support dynamic updation or some bug causing it
> have linear update .
>
>
> http://pastebin.com/davqKcF8
>
>
>
> Reposting below if you have missed it.
>
>
> My process.csv is around 27G. I've gzipped it and put
> atftp://navinps:sqlit...@h2.usa.hp.com  as process.csv.gz
>
> There is only 1 file there.
> md5sum process.csv.gz
> e77a322744a26d4c8a1ad4d61a84ee72  process.csv.gz
>
>  [root@centosnavin sqlite-autoconf-3080801]# cat sqlite3commands.txt
> CREATE TABLE [hp_table1] ( InstanceId INTEGER, LogTime INTEGER, ArrivalTime
> INTEGER, CollectionTime INTEGER, [dml_PROC_TIME] TIME, [dml_PROC_INTERVAL]
> INTEGER, [dml_PROC_INTEREST] TEXT, [dml_PROC_TOP_CPU_INDEX] INTEGER,
> [dml_PROC_TOP_DISK_INDEX] INTEGER, [dml_PROC_STATE_FLAG] INTEGER,
> [dml_PROC_RUN_TIME] REAL, [dml_PROC_STOP_REASON_FLAG] INTEGER,
> [dml_PROC_INTERVAL_ALIVE] INTEGER, [dml_PROC_STOP_REASON] TEXT,
> [dml_PROC_STATE] TEXT, [dml_PROC_PRI] INTEGER, [dml_PROC_NICE_PRI] INTEGER,
> [dml_PROC_CPU_LAST_USED] INTEGER, [dml_PROC_CPU_SWITCHES] INTEGER,
> [dml_PROC_IO_BYTE] REAL, [dml_PROC_VOLUNTARY_CSWITCH] INTEGER,
> [dml_PROC_FORCED_CSWITCH] INTEGER, [dml_PROC_IO_BYTE_RATE] REAL,
> [dml_PROC_CPU_TOTAL_UTIL] REAL, [dml_PROC_CPU_TOTAL_TIME] REAL,
> [dml_PROC_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYS_MODE_TIME] REAL,
> [dml_PROC_CPU_USER_MODE_UTIL] REAL, [dml_PROC_CPU_USER_MODE_TIME] REAL,
> [dml_PROC_THREAD_COUNT] INTEGER, [dml_PROC_CPU_ALIVE_TOTAL_UTIL] REAL ,
> [dml_PROC_CPU_ALIVE_USER_MODE_UTIL]  REAL,
> [dml_PROC_CPU_ALIVE_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYSCALL_UTIL] REAL,
> [dml_PROC_CPU_SYSCALL_TIME] REAL, [dml_PROC_CHILD_CPU_USER_MODE_UTIL] REAL,
> [dml_PROC_CHILD_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CHILD_CPU_TOTAL_UTIL]
> REAL, [dml_PROC_DISK_PHYS_READ] INTEGER, [dml_PROC_DISK_PHYS_READ_RATE]
> REAL, [dml_PROC_DISK_PHYS_WRITE] INTEGER, [dml_PROC_DISK_PHYS_WRITE_RATE]
> REAL, [dml_PROC_DISK_PHYS_IO_RATE] REAL, [dml_PROC_MEM_RES] REAL,
> [dml_PROC_MEM_SHARED_RES] REAL, [dml_PROC_MEM_VIRT] REAL,
> [dml_PROC_MEM_DATA_VIRT] REAL, [dml_PROC_MEM_STACK_VIRT] REAL,
> [dml_PROC_PAGEFAULT] INTEGER, [dml_PROC_PAGEFAULT_RATE] REAL,
> [dml_PROC_MINOR_FAULT] INTEGER, [dml_PROC_MAJOR_FAULT] INTEGER,
> [dml_PROC_MEM_LOCKED] REAL, [dml_PROC_DISK_

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

[Stefan Keller]

Hi,

Relying on sequence will not work (and is a wrong hack) since the use
case includes deleting rows explicitly.

I think it's time for a serious simple benchmark with sqlite and say PostgreSQL.
PostgeSQL also had performance problems time ago but this has been resolved.
Can you describe the hp_table1 schema (CREATE TABLE statement...) and
some data (INSERTs)?

Yours, S.


2015-01-24 10:33 GMT+01:00 Clemens Ladisch :
> Jim Wilcoxson wrote:
>> If you have a table where rows are inserted but never deleted, and you
>> have a rowid column, you can use this:
>>
>> select seq from sqlite_sequence where name = 'tablename'
>
> This works only for an AUTOINCREMENT column.
>
>> This will return instantly, without scanning any rows or indexes, and
>> is much faster than max(rowid) for huge tables.
>
> Max(rowid) has a special optimization and looks only at the last entry
> in the index.  It is what SQLite uses internally for tables without
> AUTOINCREMENT, and is actually faster than looking up the sequence value
> in a separate table.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Whish List for 2015

[Stefan Keller]

Hi,

Adding JSON to SQLite (like in PostgreSQL) ultimately needs a
JavaScript parser - and that seems to be against the goal of SQLite
being slim.

But what about adding a data type "hash table", i.e. set of keyvalue
pairs (also known as hstore, dictionary or associative array)?
Would'nt this still serve your use case?

Hash tables are already used now internally in the SQLite parser (see hash.c).
If not as part of the core it could be first implemented as an extension.
Here's the syntax of hstore in PostgreSQL [1].

Yours, S.

[1] http://www.postgresql.org/docs/current/static/hstore.html


2015-01-18 22:16 GMT+01:00 Simon Slavin :
>
> On 18 Jan 2015, at 8:29pm, David Barrett  wrote:
>
>>SELECT createNewJSONCollationFunction( "FooCollationSequence", "foo" );
>>SELECT createNewJSONCollationFunction( "BarCollationSequence", "bar" );
>
> Those two might be okay depending on what the quoted values are meant to be.
>
>>CREATE INDEX ON testDataFoo ( "json" FooCollationSequence );
>>CREATE INDEX ON testDataBar ( "json" BarCollationSequence );
>
> Syntax wrong.
>
> CREATE INDEX indexname ON tablename (columnname COLLATE collatefunction)
>
> Indexes need a name each.  Inside the brackets goes the name of the column 
> you're indexing on, then COLLATE, then the collation function to use (which 
> can be your own).  You cannot make the  collation function take a parameter 
> at this point.
>
>> Before giving that a shot, however, I'm hoping someone here can give me a
>> sense of whether that'll work?  Specifically, if I'm generating these
>> dynamic collation sequences on the fly, how does sqlite store the index on
>> disk?
>
> SQLite uses the simplest most obvious way to do it: the values returned by 
> your COLLATE function are stored in the index when a new row is added to a 
> table.  If your COLLATE function changes after rows have been added to the 
> index, SQLite does not understand the problem and can corrupt the index as it 
> tries to change it assuming that the old values are correct.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type?

[Stefan Keller]

Hi,

Thank you all for the answers.

Petite Abeille writ

2014-03-18 19:22 GMT+01:00 Petite Abeille <petite.abei...@gmail.com>:
> If, for some reasons, you cannot even accomplish first normal form [1], i.e. 
> one and only one value per column,
> well, then, maybe, a relational database is not the right tool for the task 
> at hand.

Some answers already suggested to normalize key-values.

While I know this EAV pattern I'm still open for implementations which
are able to process KVP as a data type.
And, actually, as you may have realized, PostgreSQL proved that even
(post-)relational databases can handle KVP efficiently.

So, I'm considering to follow-up Tiago's proposal either
1. to create function similar to PostgreSQL (with the problem that
SQLite has no types and thus can't optimize the index) -
2. or to write at least a casting function which takes the key-value
string syntax and converts it into rows (pivot)??

-S.


2014-03-18 19:22 GMT+01:00 Petite Abeille <petite.abei...@gmail.com>:
>
> On Mar 18, 2014, at 2:46 AM, Stefan Keller <sfkel...@gmail.com> wrote:
>
>> Any suggestions on how to query this most efficiently (like [select
>> value from "some_key"])?
>
> As mentioned, turn this construct into a regular relational table structure.
>
> If, for some reasons, you cannot even accomplish first normal form [1], i.e. 
> one and only one value per column, well, then, maybe, a relational database 
> is not the right tool for the task at hand.
>
> [1] http://en.wikipedia.org/wiki/First_normal_form
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type?

[Stefan Keller]

Hi,

I have a column which contains a string structure taken from the
PostgreSQL HSTORE key-value-pairs type. This is an example of one
column value:

"operator"=>"police","name"=>"Zurich","some_key"=>"some_value"

Any suggestions on how to query this most efficiently (like [select
value from "some_key"])?

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

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

Dear Keith

> I think your definition is a little off. (...)

Nice thoughts about the general term of a system - but unfortunately not
state-of-the-art. If you don't trust in G* pls. read the first chapters of
any university course or well-known or books about Database Systems, like
the book "Fundamentals..." from Elmasri

Yours, Stefan



2013/9/2 Keith Medcalf <kmedc...@dessus.com>

>
> > To me (as a university teacher :->), here exists no single common
> > definition what a (R)DBMS is! That comes close:
>
> > A database management system (DBMS) is software that controls the
> > storage, organization, and retrieval of data. A DBMS provides
> > various functions like data security, data integrity, data sharing,
> > data concurrence, data independence, data recovery etc. And an RDBMS
> > is a DBMS based on the relational model.
>
> I think your definition is a little off.  A DBMS (data base management
> system) is a "system" for the management of a collection (base) of data.
>  The terms themselves indicate no requirement for the "base" or the "data",
> nor in particular the type of "system" involved -- certainly no requirement
> for "software" running on a "computer".  DNA (and RNA) are DBMSes and are
> most assuredly Systems for the Management of a Base of Data.
>
> It is unfortunate that so many wear blinders so much of the time that they
> cannot comprehend the plain and clear meaning of words without falling
> afoul of their own prejudices.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

I understand now.

To me (as a university teacher :->), here exists no single common
definition what a (R)DBMS is! That comes close:

A database management system (DBMS) is software that controls the storage,
organization, and retrieval of data. A DBMS provides various functions like
data security, data integrity, data sharing, data concurrence, data
independence, data recovery etc. And an RDBMS is a DBMS based on the
relational model.

To me, SQLite clearly is a RDBMS - with pros and cons like any product.
There is no one-size-fits-all database btw and that's why NoSQL databases
came up.

I see no evidence, why SQLite would'nt be a RDBMS, or if ot would be less
than a RDBMS like e.g. Oracle or PostgreSQL. If someone want's to argue
based on single properties, this could be of help:
http://database-management-systems.findthebest.com/compare/24-36-43-53/Access-vs-Oracle-vs-PostgreSQL-vs-SQLite



2013/9/1 kimtiago 

> Hi people,
>
> I`m really sorry if you think i`m a noob. Maybe I am.
>
> I`m brazilian and my english its not very good.
>
> I googled SQLite and i read a lot of information before post in this forum.
>
> I`m writing by nabble yes.
>
> Now please tell me. Is this a forum only for experts? I just asked a
> question.
>
> Now let me explain why i have this doubt. I did a exam and I miss this
> question, so i`m seeking for information to counter attack my teacher and
> i`m confuse beacause the information i`m reading in the web says diferent
> things. Because thia i came here.
>
> In wikipedia says that he is DBMS, but in the official web site says
> "SQLite
> is an embedded SQL database engine."
>
> I prefer the official information. Now please, someone can explain me whats
> the difference?
>
> Thank you for undestand me
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868p70874.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

Hi,

Wikipedia answers with yes and why (= because it's ACID and SQL compliant)
within the first three sentences!
http://en.wikipedia.org/wiki/SQLite

Yours, S.


2013/9/1 kimtiago 

> Hi,
>
> I need to know if SQLite is a DBMS and why.
>
> Please its urgent.
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Hi Régis

I'd wish to give you a solution but I'm sorry to have new news about that issue.

I'd be happy if there are any SQlite devs around to give you a
solution in order to make this database more usable (an more SQL
compatible).

Yours, Stefan


2013/6/11 regish <regis.haubo...@eau-adour-garonne.fr>:
> Hi all,
> I'm starting to use SQLITE in GIS use cases. I'm facing this view typing
> column issue, which prevent my favourite client from interpreting correctly
> numeric data types.  I'm using QGIS, so I won't be able to map numeric
> values (they fall back as text values).
> Is there anything new since 2010?  If not, should I suggest Qgis Devs to
> hack the field type detection for views?
> Régis
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS malformed MATCH expression: [( "a*" OR b*)]

[Stefan Rogin]

Forget the last question.
I've managed to get passed it :), I didn't open the whole solution file,
just the  SQLite.Interop.2010 project,

I've seen that it's set on .Net 4
How can I set it to 3.5 and are there any drawbacks?

Anyway, thanks for the quick reply. +1 for the support

On Fri, Feb 3, 2012 at 9:41 PM, Stefan Rogin <stefan.rogin2...@gmail.com>wrote:

> Hi,
>
> I tried to build with all default and it gave me this:
>
> Error 5 error LNK1181: cannot open input file
> '\SQLite.Interop\..\bin\2010\ReleaseModule\bin\System.Data.SQLite.netmodule'
>  \SQLite.Interop\LINK SQLite.Interop.2010
>
> On Fri, Feb 3, 2012 at 5:32 PM, Stefan Rogin 
> <stefan.rogin2...@gmail.com>wrote:
>
>> Thank you,
>>
>> I'll try to recompile.
>> I looked in the Navicat folder and it had 2 sqlite dlls(sqlite and
>> sqlite3), could those be of any good?
>> În data de 03.02.2012 16:52, "Joe Mistachkin" <sql...@mistachkin.com> a
>> scris:
>>
>>
>>> The native SQLite code bundled with System.Data.SQLite is not compiled
>>> with the SQLITE_ENABLE_FTS3_PARENTHESIS option, which is required for
>>> the expression you are asking about to work properly.
>>>
>>> Of course, it can always be recompiled if you have access to Visual C++,
>>> by editing one of the following files and recompiling the SQLite.Interop
>>> project:
>>>
>>>/SQLite.Interop/props/sqlite3.vsprops (for VC++ 2008)
>>>/SQLite.Interop/props/sqlite3.props (for VC++ 2010)
>>>
>>> Where  is the root of the source tree for System.Data.SQLite.
>>>
>>> --
>>> Joe Mistachkin
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>
>
> --
>
> *Stefan Rogin*
> Webdesigner
> Tel: +40.769.622.178
>
>


-- 

*Stefan Rogin*
Webdesigner
Tel: +40.769.622.178
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS malformed MATCH expression: [( "a*" OR b*)]

[Stefan Rogin]

Hi,

I tried to build with all default and it gave me this:

Error 5 error LNK1181: cannot open input file
'\SQLite.Interop\..\bin\2010\ReleaseModule\bin\System.Data.SQLite.netmodule'
 \SQLite.Interop\LINK SQLite.Interop.2010

On Fri, Feb 3, 2012 at 5:32 PM, Stefan Rogin <stefan.rogin2...@gmail.com>wrote:

> Thank you,
>
> I'll try to recompile.
> I looked in the Navicat folder and it had 2 sqlite dlls(sqlite and
> sqlite3), could those be of any good?
> În data de 03.02.2012 16:52, "Joe Mistachkin" <sql...@mistachkin.com> a
> scris:
>
>
>> The native SQLite code bundled with System.Data.SQLite is not compiled
>> with the SQLITE_ENABLE_FTS3_PARENTHESIS option, which is required for
>> the expression you are asking about to work properly.
>>
>> Of course, it can always be recompiled if you have access to Visual C++,
>> by editing one of the following files and recompiling the SQLite.Interop
>> project:
>>
>>/SQLite.Interop/props/sqlite3.vsprops (for VC++ 2008)
>>/SQLite.Interop/props/sqlite3.props (for VC++ 2010)
>>
>> Where  is the root of the source tree for System.Data.SQLite.
>>
>> --
>> Joe Mistachkin
>>
>> _______
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>


-- 

*Stefan Rogin*
Webdesigner
Tel: +40.769.622.178
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS malformed MATCH expression: [( "a*" OR b*)]

[Stefan Rogin]

Thank you,

I'll try to recompile.
I looked in the Navicat folder and it had 2 sqlite dlls(sqlite and
sqlite3), could those be of any good?
În data de 03.02.2012 16:52, "Joe Mistachkin"  a
scris:

>
> The native SQLite code bundled with System.Data.SQLite is not compiled
> with the SQLITE_ENABLE_FTS3_PARENTHESIS option, which is required for
> the expression you are asking about to work properly.
>
> Of course, it can always be recompiled if you have access to Visual C++,
> by editing one of the following files and recompiling the SQLite.Interop
> project:
>
>/SQLite.Interop/props/sqlite3.vsprops (for VC++ 2008)
>/SQLite.Interop/props/sqlite3.props (for VC++ 2010)
>
> Where  is the root of the source tree for System.Data.SQLite.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS malformed MATCH expression: [( "a*" OR b*)]

[Stefan Rogin]

Hi,

I've encountered some issues when using *FTS4 MATCH* on a database, like
the one mentioned in the subject (*malformed MATCH expression: [( "a*" OR
b*)]*).
>From what I discovered it is triggered by using a quote at the beginning of
a parenthesis, if I write *(b* OR "a*")* it works just fine.
What I found interesting is that if I use Navicat with the SQLite database,
the query above works just fine.
Also there is support for " NOT " opposed to " - " supported
by System.Data.SQLite.

*this is the syntax I used to get the data:*

DataTable dt = new DataTable();

using (SQLiteCommand cmd = mySQLiteConn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
SQLiteDataAdapter sqlDa = new SQLiteDataAdapter(cmd);
sqlDa.Fill(dt);
}

*exception detail:*

System.Data.SQLite.SQLiteException was unhandled
  Message=SQLite error
malformed MATCH expression: [("a*" OR b*)]
  Source=System.Data.SQLite
  ErrorCode=-2147467259
  StackTrace:
   at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
   at
System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior
behavior)
   at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables,
Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior
behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SQLite_Take2.Form1.ftsSearch() in D:\c#\SQLite Take2\Main.cs:line
607
   at SQLite_Take2.Form1.cmdSearch_Click(Object sender, EventArgs e) in
D:\c#\SQLite Take2\Main.cs:line 340
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&
m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
msg)
   at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
   at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
   at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at SQLite_Take2.Program.Main() in D:\c#\SQLite Take2\Program.cs:line
18
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[]
args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


*I use:*
Windows 7 , 64bit
Visual Studio 2010
SQLite Server version : 3.7.7.1
Connection string: "Data Source=.\temp.db;UTF8Encoding=True;Version=3"
System.Data.SQLite.dll version : 1.0.74.0
-- 

*Stefan Rogin*
Webdesigner
Tel: +40.769.622.178
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'integer'

[Stefan Keller]

Again a disclaimer: I use SQlite often e.g. for continuous testing and
in Desktop Apps. - and I like it as it is except for it's homepage
declaration and (more formally) for it kind of weak typing (meaning
'weak' compared to the information schema).

Weak typing in databases assigns the house keeping of consistency to
the writer - who is often unknown in database uses cases. And views
which are'nt able to keep and disclose their domain descriptor is a
'no-no' in database technology.

Can someone give me use cases on how weak typing is used?

Simon wrote:
> Stefan, please read this:
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Thank you for the weblink.
Here are some citations from it which could be of interest to you too
(see below).
I don't think SQLite supports that.
'Nough said?

Roger wrote:
> In other news people complained about those new fangled cars not behaving
> exactly like the horses they were used to.  Eventually they got over it and
> realised it was a wonderful improvement most of the time.

The citations of the SQL standard gives evidence that it's the other
way round: *You* are riding the horses.
But that's Ok to me as long as those riders declare that :->

Yours, Stefan




Excerpts from "ISO/IEC 9075:1992, Database Language SQL - July 30,
1992 (Second Informal Review Draft)",
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :

4.1 Data Types
"SQL () defines distinct data types named by the following s: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC,
DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME,
TIMESTAMP, and INTERVAL."

"(...) A data type descriptor includes an identification of the data
type and all information needed to characterize an instance of that
data type."

4.7 Domains
"A domain is described by a domain descriptor. (...)"

4.8  Columns
"(...) All values of the same column are of the same data type or
domain and are values in the same table.

4.12 Catalogs
"(...) An SQL-environment contains zero or more catalogs. A catalog
contains one or more schemas, but always contains a schema named
INFORMATION_SCHEMA that contains the views and domains of the
Information Schema."




2011/4/18 Simon Slavin <slav...@bigfraud.org>:
>
> On 18 Apr 2011, at 1:53am, Roger Binns wrote:
>
>> Your complaints are really that SQLite doesn't function exactly how you are
>> used to.  Note that it does do exactly what you tell it - the behaviour is
>> not random.
>
> It's probably worth Stefan reading the SQL specification.  Stefan, please 
> read this:
>
> <http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>
>
> which is about as close to a spec as there is.  Then you can survey all the 
> well known implementations of SQL and find that none of them really get at 
> all close to it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'integer'

[Stefan Keller]

Sorry for those brutally honest words - and yes, datastore is not well defined.

But look at how homepage introduces SQLite: a "serverless, ... SQL
database engine". As said, I believe SQLite could avoid traps by
declaring more clearly what it is and what it is *not*. Wikipedia
says: it is "an ACID-compliant embedded relational database management
system (...)" Then finally: "(...) SQLite uses a dynamically and
weakly typed SQL syntax that does not guarantee the domain integrity".
I clearly prefer 'weakly typed' (or even type-less) over "dynamically
typed" (mentioned in http://www.sqlite.org/datatype3.html ).

To me, SQLite is an embedded and weakly typed (or type-less)
relational datastore library. It is a light-weight, hybrid
in-memory/on-disk system and follows closely SQL syntax and ACID
principles.

SQlite to me can into a completely "typeless" database like with
randomly user defined data type names and typeless views (see below).
Consistency is being ignored (often silently!). As a data reader you
have to expect any type in every column value. Look at the examples
below...

This is why to me it's more like a relational data store than a RDBMS.
This has some advantages and but also some - often unexpected -
disadvantages.

Yours, Stefan

P.S. Disclaimer: Pls. take into account that I'm actually a promotor
of SQLite/Spatialite as a 'desktop format' to easily exchange
geospatial data.


EXAMPLES SHOWING HOW SQLITE IGNORES DATA TYPES SCHEMA INFORMATION
-

sqlite>

-- Create a table called 'atable':
CREATE TABLE atable (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  anint INTEGER NOT NULL,
  anumeric NUMERIC,
  acolumn i_dont_care);
=> look at datatype name 'i_dont_care'

-- insert a valid and an invalid tuple
INSERT INTO atable (anint, anumeric, acolumn) VALUES (2, 3, 4);

INSERT INTO atable (id, anint) VALUES (1, 2);
=> Error: PRIMARY KEY must be unique

-- show schema information:
PRAGMA table_info(atable);
0|id|INTEGER|0||1
1|anint|INTEGER|1||0
2|anumeric|INTEGER|0||0
3|acolumn|i_dont_care|0||0
=> Schema says id (pk) column is NULLable (notnull=0)?!

-- insert another invalid primary key value
sqlite> INSERT INTO atable (id, anint, anumeric, acolumn)
   VALUES (NULL, 2, 3, 4);
=> No error although NULL value for id (=primary key)?!

SELECT * FROM atable;
1|2|3|4
2|2|3|4
=> Ignored NULL value for id and replaced it with autoincrement.

-- insert another tuple
INSERT INTO atable (anint, anumeric) VALUES ('2', '3');
=> no error?

SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric),
TypeOf(acolumn) FROM atable;
1|integer|integer|integer|integer
2|integer|integer|integer|integer
3|integer|integer|integer|null
=> Implicit casts from text to integer/numeric have been made.

-- insert another tuple
INSERT INTO atable (anint, anumeric, acolumn) VALUES ('2.2', '3.3', 4.4);

SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric),
TypeOf(acolumn) FROM atable;
1|integer|integer|integer|integer
2|integer|integer|integer|integer
3|integer|integer|integer|null
4|integer|real|real|real
=> No cast is made to integer but to real...!?

PRAGMA table_info(atable);
=> Schema still pretends anint is integer and anumeric is numeric.

-- insert another tuple
INSERT INTO atable (anint, acolumn) VALUES (2, 'whateveryouwant');

INSERT INTO atable (anint, anumeric, acolumn) VALUES ('whocares?',
'whocares?', 'whocares?');
=> Inserts chars everywhere...!

SELECT * FROM atable;
1|2|3|4
2|2|3|4
3|2|3|
4|2.2|3.3|4.4
5|2||whateveryouwant
6|whocares?|whocares?|whocares?

SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric),
TypeOf(acolumn) FROM atable;
1|integer|integer|integer|integer
2|integer|integer|integer|integer
3|integer|integer|integer|null
4|integer|real|real|real
5|integer|integer|null|text
6|integer|text|text|text
=>

-- Create view with cast:
CREATE VIEW aview AS
  SELECT id, CAST(anint AS INTEGER) "anint2"
  FROM atable;

SELECT ROWID, TypeOf(id), TypeOf(anint2) FROM aview;
=> Scans through all tuples!
=> anint2 contains all integers as promised
=> ROWID ignored; no ROWID anymore!?

PRAGMA table_info(aview);
=> View def. casted anint2 to be integer but reports 'unkown' type in schema!?

-- Partial solution:
CREATE TEMP TABLE atable_tmp AS
  SELECT id, CAST(anint AS INTEGER) "anint", anumeric, acolumn
  FROM atable;

SELECT ROWID, * FROM atable_tmp;
...
SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric),
TypeOf(acolumn) FROM atable_tmp;
...

2011/4/17 Pavel Ivanov <paiva...@gmail.com>:
> Stefan,
>
>> SQLite should'nt be called a "SQL database" (as e.g. touted on its homepage).
>> Instead it should clearly declare itself as an "In-memory SQL
>> Datastore" or a "Data container with SQL capabilities".
>
> This is quite serious allegations. Making them you sho

Re: [sqlite] 'integer'

[Stefan Keller]

Michael and Jay are right about the subtleties on how SQlite
interprets what is a data type, a primary key and a database schema
and it's ACID implementation in general.

To me, the main reason - and remedy - of this FAQ is that SQlite
should'nt be called a "SQL database" (as e.g. touted on its homepage).
Instead it should clearly declare itself as an "In-memory SQL
Datastore" or a "Data container with SQL capabilities".

Yours, S.


2011/4/17 Black, Michael (IS) :
> Seems to behave OK for me on 3.7.5 on Windows.  What version are you using on 
> what OS with what compile flags?
>
> You also "said" it didn't work but you didnt' actually what what you did.
>
> Like this...
>
>
>
> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>   ...>         OtherColumn INTEGER);
> sqlite>
> sqlite> INSERT INTO Tg (TgConfigId) VALUES (1);
> sqlite> SELECT * FROM Tg WHERE TgConfigId = 1;
> 1|
> sqlite>
> sqlite> SELECT * FROM Tg;
> 1|
> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1');
> Error: PRIMARY KEY must be unique
> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2');
> sqlite> SELECT * FROM Tg;
> 1|
> 2|
> sqlite> SELECT * FROM Tg WHERE TgConfigId = 2;
> 2|
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Tobias Vesterlund [tobias.vesterl...@ericsson.com]
> Sent: Saturday, April 16, 2011 12:40 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] 'integer'
>
> Hi,
>
> I ran into something I don't understand, maybe someone here can shed some 
> light on it for me.
>
> I have a table named Tg which is created (with tcl) by:
>
> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>        OtherColumn INTEGER);
>
> If I do:
> INSERT INTO Tg (TgConfigId) VALUES (1);
>
> The following select works:
> SELECT * FROM Tg WHERE TgConfigId = 1;
>
> But if I insert '1' instead I have to select on '1', 1 no longer works. That 
> makes some sense, but not entirely, as what I inserted isn't an integer any 
> longer but a string. Why would I be allowed to insert values with '' in a 
> column that is specified to be an integer?
>
> Regards,
> Tobias
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Right, I don't want to lessen scalability of my application. But I
also don't want to introcude redundancy just because some columns on
the the view lack a type.

I assume SQLite wants to adhere to the relational model which states:
The result of a select statement is another relation. And "A view is
just a relation (a table), but stores a definition, rather than a set
of tuples." (from chapter fundamentals of "Database Management
Systems" by Ramakrishnan & Gehrke, 2002).

So, for the "consumer" a view should behave like a table. This has the
following advances:
* Decoupling: Rename physical column names without breaking code which
reads views.
* Security: One can grant read permission on a view without granting
any permission to the underlying table.
* Simplicity: It's easier to write queries.
* Helps to avoud redundancy: Views can have calculated columns, like
age (from birthdate) or tax or rebate.

So its pretty clear to me that views should have the option to return
types. CAST could be a solution.
Determintation of the return type of a calculation could be another
step. That's from the basics of computer languages.

-S.

2010/5/7 Pavel Ivanov <paiva...@gmail.com>:
>> To Pavel: My application reads the column types out in order to pretty
>> print the values - as mentioned by Tom - but also to generate a dialog
>> for entering new data (of course combined with INSTEAD OF TRIGGERs).
>
> So as I see it: you have some universal code for displaying and
> inputing data. And you don't want to lessen scalability of your
> application by hard-coding the relation between column names and their
> data types. So you can create additional table that will contain this
> information. And I think this solution is better than just relying on
> declared type of columns - more straightforward and more
> understandable by somebody coming to your project in the future.
>
>
> Pavel
>
> On Thu, May 6, 2010 at 7:11 PM, Stefan Keller <sfkel...@gmail.com> wrote:
>> Thank you, Tom and Dan, for your constructive answers.
>>
>> To Pavel: My application reads the column types out in order to pretty
>> print the values - as mentioned by Tom - but also to generate a dialog
>> for entering new data (of course combined with INSTEAD OF TRIGGERs).
>>
>> I understand that it's difficult to implement result-types for
>> expressions in general. To me it would be enough if there would be at
>> least one way to assign result-types with a CAST "wrapper" as I have
>> mentioned before.
>>
>> Does anybody know whom to present this proposal in order remedy this
>> current inconsistency in SQLite? sqlite-dev?
>>
>> In the meantime I thought of a hack and to assign the result-type by
>> hand in the data dictionary after having created the VIEW.
>> Would this be a feasible work around?
>>
>> Yours, S.
>>
>> 2010/5/6 BareFeetWare <list@tandb.com.au>:
>>> On 06/05/2010, at 2:51 PM, Dan Bishop wrote:
>>>
>>>> BareFeetWare wrote:
>>>>>
>>>
>>>>> I've had the same issue. In the end I had to parse my view functions in 
>>>>> my own code and look for functions that give a particular type of result. 
>>>>> So, for instance, round() gives an integer, round(..., 2) gives a real, 
>>>>> concat() gives text. I also look for a cast(... as type) to use that 
>>>>> declared type. It's fiddly and I would have hoped SQLite would have at 
>>>>> least declared the type if a cast was present, but it seems not.
>>>>>
>>>
>>>> A CAST expression could be assigned a declared type
>>>
>>> Yes, that should be fairly easy to do in SQLite, simpler than my adding it 
>>> in my own code (which then requires my own parser - not a trivial exercise).
>>>
>>>> but it would be difficult to implement it for expressions in general.  Not 
>>>> only would you have to declare a type for every function
>>>
>>> Yes, probably half the functions don't have a consistently returned type 
>>> (eg length, concat). The ones that don't (eg coalesce) should just be left 
>>> to return a null (unknown) type. The schema developer can simply wrap those 
>>> expressions in a cast if they want a predetermined output type.
>>>
>>>> but you'd have to do it for operators as well, and what type should A * B 
>>>> be if A is "SMALLINT" and B is "UINT"?
>>>
>>> I hadn't thought of operators. As far as I know, mathematical operators (* 
>>> / + -) only work with integers and reals, Perhaps the result 

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Thank you, Tom and Dan, for your constructive answers.

To Pavel: My application reads the column types out in order to pretty
print the values - as mentioned by Tom - but also to generate a dialog
for entering new data (of course combined with INSTEAD OF TRIGGERs).

I understand that it's difficult to implement result-types for
expressions in general. To me it would be enough if there would be at
least one way to assign result-types with a CAST "wrapper" as I have
mentioned before.

Does anybody know whom to present this proposal in order remedy this
current inconsistency in SQLite? sqlite-dev?

In the meantime I thought of a hack and to assign the result-type by
hand in the data dictionary after having created the VIEW.
Would this be a feasible work around?

Yours, S.

2010/5/6 BareFeetWare :
> On 06/05/2010, at 2:51 PM, Dan Bishop wrote:
>
>> BareFeetWare wrote:
>>>
>
>>> I've had the same issue. In the end I had to parse my view functions in my 
>>> own code and look for functions that give a particular type of result. So, 
>>> for instance, round() gives an integer, round(..., 2) gives a real, 
>>> concat() gives text. I also look for a cast(... as type) to use that 
>>> declared type. It's fiddly and I would have hoped SQLite would have at 
>>> least declared the type if a cast was present, but it seems not.
>>>
>
>> A CAST expression could be assigned a declared type
>
> Yes, that should be fairly easy to do in SQLite, simpler than my adding it in 
> my own code (which then requires my own parser - not a trivial exercise).
>
>> but it would be difficult to implement it for expressions in general.  Not 
>> only would you have to declare a type for every function
>
> Yes, probably half the functions don't have a consistently returned type (eg 
> length, concat). The ones that don't (eg coalesce) should just be left to 
> return a null (unknown) type. The schema developer can simply wrap those 
> expressions in a cast if they want a predetermined output type.
>
>> but you'd have to do it for operators as well, and what type should A * B be 
>> if A is "SMALLINT" and B is "UINT"?
>
> I hadn't thought of operators. As far as I know, mathematical operators (* / 
> + -) only work with integers and reals, Perhaps the result type could be set 
> to "numeric" or some other superclass. Or, to get real fancy, if the two 
> operands are declared integers, then the result is an integer; if one is real 
> then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite 
> as integer, so the result would be an integer.
>
>> Should relational operators have a declared type of BOOLEAN?
>
> Good idea.
>
> Tom
> BareFeetWare
>
>  --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

I interpret the silence on the lis that anyone agrees that SQLite has a bug
because there seems to be no way to get VIEWS returning the column type if the
column is calculated or a function. This also breaks compatibility as
mentioned in
http://www.sqlite.org/datatype3.html ("SQL statement that work on
statically typed
databases should work the same way in SQLite.").
=> Time for a ticket?

-S.

2010/5/3 Stefan Keller <sfkel...@gmail.com>:
> Unfortunately the application which reads from this view needs that
> all columns are typed - even if the values types deviate from it - and
> I think this is a logical assumption. So, I fear I do have only one
> chance and SQLite doesn't let me do it:
>
> CREATE VIEW myview AS
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>
> To me it seems like an inconsistency (or bug) when TABLES allow
> declaration of types and VIEWS only eventually.
>
> -S.
>
> 2010/5/3 Simon Slavin <slav...@bigfraud.org>:
>>
>> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>>
>>> But in SQLite if a view column comes from a function result or some
>>> computation, then the column type is NULL...!? It's not taking the
>>> result-type as mentioned in the manual
>>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>>> CAST in the projection clause, like this:
>>>
>>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>>
>>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>>> => Is there a way to give such columns a type anyway?
>>
>> You get two chances to CAST, one when you define the VIEW, and another when 
>> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
>> evaluation you want, the other probably will.  As you say, VIEW columns 
>> don't have types at all.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Unfortunately the application which reads from this view needs that
all columns are typed - even if the values types deviate from it - and
I think this is a logical assumption. So, I fear I do have only one
chance and SQLite doesn't let me do it:

CREATE VIEW myview AS
  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

To me it seems like an inconsistency (or bug) when TABLES allow
declaration of types and VIEWS only eventually.

-S.

2010/5/3 Simon Slavin <slav...@bigfraud.org>:
>
> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>
>> But in SQLite if a view column comes from a function result or some
>> computation, then the column type is NULL...!? It's not taking the
>> result-type as mentioned in the manual
>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>> CAST in the projection clause, like this:
>>
>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>
>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>> => Is there a way to give such columns a type anyway?
>
> You get two chances to CAST, one when you define the VIEW, and another when 
> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
> evaluation you want, the other probably will.  As you say, VIEW columns don't 
> have types at all.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

I have a question regarding VIEWs in SQLite:

It looks like if SQLite simply copies the column type from the
original table into the corresponding view column. And I know  SQLite
implements some 'loose column typing'. That's ok so far.

But in SQLite if a view column comes from a function result or some
computation, then the column type is NULL...!? It's not taking the
result-type as mentioned in the manual
(http://www.sqlite.org/lang_select.html) - even when I try to do a
CAST in the projection clause, like this:

  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

I mean that a VIEW should behave like a (read only) TABLE in any case.
=> Is there a way to give such columns a type anyway?

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

[sqlite] SQLite crashes when trying to INSERT into table with wrong default value

[stefan]

Hi all,

i've got a table:
CREATE TABLE [usr_user] ([id] INTEGER PRIMARY KEY AUTOINCREMENT,  
[name] TEXT, [email] TEXT, [password] TEXT, [createDate] DATETIME  
DEFAULT (NOW()))

see the problem? i figured out myself that "NOW()" as a default value  
doesn't work (i'm new with sqlite, coming from mysql - so it didn't  
occur to me right away).

now, when i tried to insert a row into this table:
INSERT INTO usr_user ('asdf', 'a...@asdf.com', 'somemd5hash')

it crashed sqliteman, sqlitestudio and apache with php5/pdo. in php i  
was able to step through the code, crashing occured when preparing  
that statement.

for reference, the error reported in apaches error log file is:
[Wed Sep 30 08:24:51 2009] [notice] Parent: child process exited with  
status 3221225477 -- Restarting.
i remember the status code to be a different one when trying it from home.

i've tried to replicate it on 3 different systems, all with the same result:
* winXP 32b
* win7 64b
* Linux 2.6.27.7-grsec #25 SMP Mon Feb 16 16:37:38 CET 2009 i686

sqlite info:
PECL Module version: (bundled) 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.4  
2008/12/31 11:17:42 sebastian Exp $
SQLite Library:  3.3.7

now, this error is easily fixed by just removing/replacing the  
offending default value, but shouldn't it be handelt differently than  
just crashing?

thanks, stefan


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

[sqlite] incorrect where clause does not throw error

[Stefan Finzel]

Porting an application to sqlite3.6.13 on Linux i made a mistake 
creating a illegal query on a character field:


select * from Test where Remark = NULL

select * from Test where Remark <> NULL

I was confused as there were neither data nor an error. Shouldn't this 
cause at least an error message?



With the correct queries

select * from Test where Remark is NULL

select * from Test where Remark not NULL

everything is fine


--
_
    Stefan G. R. Finzel, Dipl. Phys. (Univ.)
  Ochsenkopfstr. 5, D-95478 Kemnath, +49 (0)9642 704448
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN - DBD::SQLite version 1.20

[Stefan Evert]

Thanks a lot, Darren!

This version also fixes the problems I've been having with segfaults  
in the create_function test that I've been worried about.  I've only  
tried version 1.22_02, but according to the change log, the bug was  
fixed before the public release of 1.20.

Best,
Stefan

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

Re: [sqlite] speeding up row by row lookup in a large db

[Stefan Evert]

>> Just wanted to say publicly that DBD::SQLite is the greatest thing
>> since, well, SQLite. Thanks for making our lives easy.
>
> +1

$count++;

from me, too.  We talk so much about speed and versions only because  
we use DBD::SQLite so heavily.

BTW, I've switched to the amalgamation package, with SQLite updated to  
3.6.11 manually.  Works very well so far, though I haven't tried  
callbacks on the Mac so far (where the test suite segfaults).

Best,
Stefan

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

Re: [sqlite] speeding up row by row lookup in a large db

[Stefan Evert]

On 22 Mar 2009, at 00:27, P Kishor wrote:

> Stefan, you were right about "Depends on how much data" part, but it
> applied to the weather data, not the lookup tables for lc (or
> landcover) or dist (or disturbance).
>
> Also, "Even for Perl/DBI" makes it seem there is something wrong with
> Perl/DBI... no, no... Perl/DBI is an industry-tested, extremely
> capable interface. I wouldn't trade it for anything. The problem was
> with my problem, with my data size, with my approach. I have to
> correct that. DBD::SQLite/DBD::SQLite::Amalgamation/Perl DBI are the
> greatest things since SQLite.

I didn't mean to criticise DBI specifically.  I'm doing most of my  
database work through DBI (with either SQLite or MySQL as a backend)  
and I'm very happy with its ease of use and stability.

However, Perl does introduce a lot of overhead, so it may not be the  
best choice for high-performance applications.  In my simplistic  
benchmarks, Perl did not achieve more than approx. 400,000 subroutine  
calls per second (MacBook Pro, 2.5 GHz).  If you're reading your 7,300  
result rows in a loop with fetchrow_* method calls (or if similar  
subroutines are called internally when using fetchall_* or  
selectall_*), then you're already limited to less than 60 queries/s by  
the function call overhead.

If you don't need to access the rows separately, you could try to pack  
the complete data for each met_id with Storable.pm and store it in a  
single database blob.  I haven't tried that myself, though, so I don't  
know whether that would be any faster.

>> Another thing to keep in mind is that the SQLite version included  
>> in the
>> DBD::SQLite distribution is fairly old (3.4.0 on my Mac -- I doubt  
>> there's a
>> more recent version of DBD::SQLite around), and AFAIK there have  
>> been some
>> speed improvements in SQLite recently.
>
> I am using DBD::SQLite::Amalgamation with SQLite 3.6.11. I am not sure
> there is anything wrong with DBD::SQLite 1.1.4 at all (other than its
> use of the older SQLite code, which is easily corrected). One might
> get better bang by ensuring that the latest version of DBI is
> installed, which Tim Bunce and company are probably always
> fine-tuning.

DBD::SQLite is fine and works very well for me, but hasn't seen any  
updates to a newer SQLite version in quite a long time.  My  
understanding is that it's not entirely trivial to replace the SQLite  
source code (without understanding all the XS interfacing code), and  
that it would be better and easier to use the amalgamation anyway.

I'd love to switch to DBD::SQLite::Amalgamation, but I need to  
register my own callback functions, and this package fails the  
callback tests with a bus error.

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

Re: [sqlite] speeding up row by row lookup in a large db

[Stefan Evert]

On 21 Mar 2009, at 15:31, P Kishor wrote:

> I did some benchmarking with the above schema using Perl DBI, and I
> get about 30 transactions per second as long as I returning the data
> to memory.

Even for Perl/DBI, that seems pretty slow.  Depends on how much data  
each of these transactions returns, though -- if there are thousands  
of rows in lc or dist for each cell_id, then you can't expect much  
better performance.  Even though DBI and DBD::SQLite are written in C,  
they have to allocate fairly complex data structures to return the  
data (in the best case, an anonymous array with 40 to 60 entries for  
each data row in the result set), and these operations are relatively  
expensive in Perl (I know because I've written some XS code recently  
that does this kind of thing).

Another thing to keep in mind is that the SQLite version included in  
the DBD::SQLite distribution is fairly old (3.4.0 on my Mac -- I doubt  
there's a more recent version of DBD::SQLite around), and AFAIK there  
have been some speed improvements in SQLite recently.

(Darren, any news from the maintainer of DBD::SQLite?  I would be very  
delighted and grateful to be able to use an up-to-date SQLite version  
in my Perl scripts.)

>
> [1] First retrieve all data from cell table
> SELECT * FROM cell WHERE cell_id = :cell_id
>
> [2] Now retrieve the related lc, dist and met
> SELECT lc.*
> FROM lc l JOIN cell_lc c on l.lc_id = c.lc_id
> WHERE c.cell_id = :cell_id

Just a short in the dark, but people on this list have occasionally  
pointed out that SQLite isn't very good at optimising JOIN queries, so  
it's often better to use a sub-select clause.  (SQLite isn't very good  
at optimisation in general, at least the version 3.4.0 that comes with  
Perl, and I've found that sometimes it helps a lot to give a few  
subtle hints to the optimiser ...)

Have you tried rephrasing queries [2] and [3] in this form?

> SELECT * FROM lc WHERE lc_id IN (SELECT lc_id FROM cell_lc WHERE  
> cell_id = :cell_id)

I've had good experiences with this approach, although my definition  
of good performance is rather along the lines of "completes within  
less than 5 seconds". :-)


Best regards,
Stefan Evert

[ stefan.ev...@uos.de | http://purl.org/stefan.evert ]



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

Re: [sqlite] Any advantage to pre-sorting data before loading and indexing it?

[Stefan Evert]

On 17 Feb 2009, at 15:34, pyt...@bdurham.com wrote:

> Is there any advantage to having data pre-sorted (in index order)
> before loading it into SQLite and indexing it? Or does indexing
> work best if the index values are randomly ordered at data load
> time?

 From my own experience, there's a big performance gain both at the  
indexing stage and in queries that have to scan relatively large parts  
of the database in index order (at least if you're working with a  
database that doesn't fit in RAM).

Of course, if you're indexing multiple columns, you can only pre-sort  
data for one of them, so on average there will be little gain.




Best regards,
Stefan Evert

[ stefan.ev...@uos.de | http://purl.org/stefan.evert ]



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

Re: [sqlite] request to become co-maintainer of DBD::SQLite

[Stefan Evert]

>>

Dear Duncan,

thanks for taking on this job!  I have recently started using SQLite  
quite heavily from Perl scripts -- it is astonishingly efficient, even  
for simple queries on a 70 GB database (Google's Web 1T 5-gram  
database, in case someone's curious) with Perl callback functions --  
so I'd be more than happy to see an up-to-date version of DBD::SQLite.

Since I'm lazy enough to rely on OS-provided SQLite installations on  
various computers, I'm using at least three different old versions of  
SQLite in parallel, DBD::SQLite being the oldest of all ... (no  
compatibility problems at all, though, so kudos to all SQLite  
developers!).

>> I have been stuck back at 3.4 for various issues.
>>
>> I do Perl and C and offer some help.

Same here.  I feel reasonably at home both in C and Perl, and I've  
written some simple XS code.  I don't have any experience with DBI,  
which seems to have its own method of compiling C extensions for DBD  
modules (from a quick look at the DBD::SQLite sources).

Just let us know how/whether we can help you!




Best regards,
Stefan Evert

[ stefan.ev...@uos.de | http://purl.org/stefan.evert ]



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

Re: [sqlite] Journal files

[Stefan Evert]

> I tried that on sqlite3 version 3.6.6 and the return value was "OFF"  
> but the
> journals are still being created. Any reason why this wouldn't work?

Did you set

PRAGMA journal_mode = OFF;

? The way I read the documentation (on a second or third close  
reading, I think), this only sets the default value for new databases  
to be attached, but doesn't affect your main connection and any  
databases that have already been attached.  So what I do is

PRAGMA main.journal_mode = OFF;

IIRC, I needed this line to actually turn off the journals (tested  
with 3.4.x and 3.5.x versions of SQLite only, though).

Cheers,
Stefan

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

Re: [sqlite] multiple inserts

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Kodok Márton
> Sent: Monday, August 11, 2008 2:21 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] multiple inserts
> 
> Hi,
> 
> Does SQLite accepts multiple insert?
> insert into table (col1,col2) values (val1,val2), 
> (val3,val4), (val5,val6)

No.

> If not, how can I speed up large inserts?
> eg: 1000 rows
> 


Here is a quick shot: Just use a transaction surrounding your INSERTs, and use 
prepared statements and parameter bindings to prevent multiple parsing.

-

char* errmsg = NULL;
sqlite3* db = NULL;

db = open( )


// begin transaction
sqlite3_exec( db, "BEGIN TRANSACTION", NULL, NULL, );

// prepare for multiple inserts
sqlite3_stmt* stmt = sqlite3_prepare_v2(  "INSERT INTO T1 (C1, C2, C3) VALUES 
(?, ?, ?)")

-for-each-row
// read doc for sqlite3_bind_* carefully!!
sqlite3_bind_int( stmt, 1,  someIntVariable);
sqlite3_bind_text( stmt, 2,  -1, "hello", SQLITE_STATIC);
sqlite3_bind_text( stmt, 3,  -1, pzSomeString, SQLITE_TRANSIENT);
sqlite3_step(stmt);   // executes the INSERT
sqlite3_reset(stmt);  // important! Clears the old values, makes the statement 
accept new parameters.
-end-for-each

sqlite3_finalize(stmt); // clean up prepared statement
 
// begin transaction
sqlite3_exec( db, "COMMIT", NULL, NULL, );  // COMMIT all dirty pages at 
once
-

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

Re: [sqlite] Loading a existing database 100% into memory

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel
> Sent: Thursday, August 07, 2008 12:27 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Loading a existing database 100% into memory
> 
> Good afternoon list,
> 
> I would like to load my current database file completely into 
> memory, mostly as an experiment to check SQLite's maximum 
> memory footprint, however searching through the documentation 
> I can only find references about how to create new databases 
> that are completely memory resident.
> Is there a way to do this?  I'm currently using the SQlite 
> console application for my testing if that makes a difference.
> 

What do you mean by loading it into memory?
If you want to dump the on-disk tables into memory and also want to
check the memory footprint used for caching try this:

Open the :memory: database and attach the on-disk database with
ATTACH filename.db AS filename

Then do a 
CREATE TABLE tableName AS SELECT * FROM filename.tableName
On each table in the file, thus creating an in-memory copy of the DB and
having done a select on each table (i.e. you'll see how much cache in
memory will be used, etc.)

You can enumerate all tables in a your on-disk-file in the mentioned
scenario by doing a "SELECT tbl_name FROM filename.sqlite_master WHERE
type = 'table'".

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

Re: [sqlite] General help, a bit OT

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
> Sent: Tuesday, July 15, 2008 2:28 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] General help, a bit OT
> 
> Oh, sorry, the example was just an example.  The calculations 
> I need to make on on ColC cannot be done within SQLite.
>
> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
> >

AFAIK, the current .NET provider for SQLITE supports custom functions
implemented in .NET.
Maybe this will work?

SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;

You might also want to implement the function in C for speed
improvements.

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

Re: [sqlite] General help, a bit OT

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
> Sent: Tuesday, July 15, 2008 2:03 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] General help, a bit OT
> 
> A bit off topic, but perhaps someone could direct me.
> 
> I'm in vb.net
> 
> I have to read thousands of tables and operate on each one in 
> sequence.  I need to do this fast.
> 
> I want to read the data from 6 stored columns and many rows 
> from each table.
> 
> Once the table is read, I need to create derived columns 
> (e.g. (new) Column C = column A + ColumnB).


Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?

Greetings,
Stefan Szomraky.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about PRAGMA journal_mode

[Stefan Arentz]

On Wed, Jun 18, 2008 at 10:39 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Jun 18, 2008, at 10:25 AM, Stefan Arentz wrote:
>
>> The documentation has the following about the OFF setting of
>> JOURNAL_MODE:
>>
>> "The OFF journaling mode disables the rollback journal completely. No
>> rollback journal is ever created and hence there is never a rollback
>> journal to delete. The OFF journaling mode disables the atomic commit
>> and rollback capabilities of SQLite. If a crash or power failure
>> occurs in the middle of a transaction when the OFF journaling mode is
>> set, then the database file will very likely go corrupt."
>>
>> I wonder what 'corrupt' means here.
>>
>> Does it mean corrupt as in 'the internal low-level database structure
>> will likely go corrupt' or does it mean 'the data in the database will
>> likely go inconsistent'.
>>
>
> Both.  Your database will likely be unusable.  Do not set
> journal_mode=OFF on any database that you cannot recreate in its
> entirety from secondary data following a program crash.

Thanks Richard, that is exactly the answer that I needed :-)

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

[sqlite] The APress SQLite Book

[Stefan Arentz]

Sorry if this is a little offtopic but I am curious what other people thing.

Is it just me, or is the APress book 'The definitive guide to SQLIte'
not very high quality?

First, the index is completely unusable. The index is grouped by
subject, so if you want to for example lookup where the book talks
about sqlite3_reset you first have to know that it is grouped under
the 'Prepared Query'. This is completely useless for looking up things
and I keep for a full index scan (heh) to find things. My copy of the
book mentions 'Find it faster at http://superindex.apress.com !' but
that site gives a 404.

Also, the book seems to be inaccurate in a bunch of places. For
example, when I did find the section about sqlite_reset, I read the
following:

"If you want to reuse the statement, you can do so using
sqlite3_reset(). It will keep the compiled SQL statement (and any
bound parameters), but commits any changes related to the current
statement to the database."

Maybe the terminology used here is simply very confusing but I'm
pretty sure that sqlite3_reset does not commit anything. It only
resets the state of the prepared statement so that it can be used
again.

The terminology used is also very inconsistent. For example the books
talks about 'Compiled SQL Queries', 'Prepared Queries', 'Prepared
Statements' and 'Compiled SQL Statements' instead of using simply
'Prepared Statement' as the rest of the database world does.

Some sections are pretty good and give a lot of insight, but in
general I am very disappointed and I think I will simply toss it away
and keep using the documentation on the SQLite web site.

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

[sqlite] Question about PRAGMA journal_mode

[Stefan Arentz]

The documentation has the following about the OFF setting of JOURNAL_MODE:

"The OFF journaling mode disables the rollback journal completely. No
rollback journal is ever created and hence there is never a rollback
journal to delete. The OFF journaling mode disables the atomic commit
and rollback capabilities of SQLite. If a crash or power failure
occurs in the middle of a transaction when the OFF journaling mode is
set, then the database file will very likely go corrupt."

I wonder what 'corrupt' means here.

Does it mean corrupt as in 'the internal low-level database structure
will likely go corrupt' or does it mean 'the data in the database will
likely go inconsistent'.

The difference is subtle, but important for my application.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speeding up index creation?

[Stefan Arentz]

On Fri, May 23, 2008 at 6:37 PM, Nemanja Čorlija <[EMAIL PROTECTED]> wrote:
> On Fri, May 23, 2008 at 4:20 PM, Stefan Arentz <[EMAIL PROTECTED]> wrote:
>> I have an interesting problem. I need to generate a large table
>> periodically. The table contains a unique SHA1 hash code and 6 integer
>> values and has about 6 million rows. Generating this table is fast. I
>> can set it up in less than 90 seconds on a slow iMac (2.16 Ghz and
>> slow disk). The thing that takes a (relatively) long time is the index
>> creation on the unique hash code .. 720 seconds.
>>
>> (I'm importing within transactions with chunks of 25.000 records)
>>
>> The question is, is there any way to speed up the indexing proces?
>>
>> Couple of things on my mind:
>>
>> Doing this all on an in-memory database takes about 150 seconds in
>> total. Is it possible to build a database in memory and then dump it
>> to disk?
>>
>> Would it make sense to sort the records that i import? Could that
>> result in a quicker index operation? This certainly helps for Berkeley
>> DB. But it is lower level and you can use it's internal hashing
>> functions on your data to pre-sort it. Maybe SQLite has something
>> similar?
>>
>
> It does make sense to presort records before inserting into on-disk
> db. You should insert into memory db first and then insert sorted
> records into disk db from there. You can batch this into chunks of 25K
> rows as you're doing now. But if you have enough memory, it will
> probably be better to increase number of rows.
> You should also play with increasing cache_size and page_size values.
> Pseudo code:
>
> OPEN :MEMORY:
> ATTACH DISK_DB;
>
> DO
>  BEGIN;
>  INSERT INTO MEMORY_TABLE VALUES(...); x 25.000
>  INSERT INTO DISK_TABLE SELECT * FROM MEMORY_TABLE ORDER BY sha1_hash;
>  COMMIT;
> WHILE has_more_rows

This is a great tip. I implemented the above and it works very well.
The load speed on Ubuntu Hardy went down from an hour+ to 6 minutes.

I'm now experimenting with the pragmas to see if this can go even more faster.

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

[sqlite] Speeding up index creation?

[Stefan Arentz]

I have an interesting problem. I need to generate a large table
periodically. The table contains a unique SHA1 hash code and 6 integer
values and has about 6 million rows. Generating this table is fast. I
can set it up in less than 90 seconds on a slow iMac (2.16 Ghz and
slow disk). The thing that takes a (relatively) long time is the index
creation on the unique hash code .. 720 seconds.

(I'm importing within transactions with chunks of 25.000 records)

The question is, is there any way to speed up the indexing proces?

Couple of things on my mind:

Doing this all on an in-memory database takes about 150 seconds in
total. Is it possible to build a database in memory and then dump it
to disk?

Would it make sense to sort the records that i import? Could that
result in a quicker index operation? This certainly helps for Berkeley
DB. But it is lower level and you can use it's internal hashing
functions on your data to pre-sort it. Maybe SQLite has something
similar?

Are there any other tunable options that can improve indexing speed?

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

[sqlite] sqlite3_callback called even for empty tables

[Stefan Kuhr]

Hello everyone,

I hope this is not an FAQ...

In my code I call sqlite3_exec with a sqlite3_callback. I noticed that when
I do a select statement, then the callback is invoked once even if the table
is empty, but with all argv strings passed to the callback being NULL
(however with the correct argc value). Is this expected behaviour, so I
always have to check in my callback if the string parameters being passed
are non-NULL to guard against a select statement on an empty table, or is
there a more elegant solution to the problem?

Thanks for any help,

-- 
Stefan Kuhr

-- 
View this message in context: 
http://www.nabble.com/sqlite3_callback-called-even-for-empty-tables-tf4111001.html#a11689130
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Calling sqlite3_exec from within another sqlite3_exec callback?

[Stefan Kuhr]

Hello everyone,

this might be a real dumb question, but I am relatively new to database
programming and in particular to sqlite, so anyway, here it goes:

Suppose I have a call to sqlite3_exec in my code and it calls into the
callback function passed as the third parameter to this sqlite3_exec call.
Is it possible to create another sqlite3 object with a sqlite3_open to the
same database file from within this callback? Or in other words: Can I fire
a query from within a callback of a query, ore more or less nested queries?
Is this supported or even a common pattern?

TIA,

-- 
Stefan

-- 
View this message in context: 
http://www.nabble.com/Calling-sqlite3_exec-from-within-another-sqlite3_exec-callback--tf3958849.html#a11233481
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] MingW32 help wanted

[Stefan de Konink]

Scott Hess wrote:

I took Makefile.linux-gcc, and made the obvious changes (there are
mingw lines all over in there).  EXE = .exe, SO =dll, SHPREFIX = ,
[that was nothing for that setting], and TCC, AR, and RANLIB set to
the path to the appropriate commands from mingw.

I should warn that I haven't actually built using mingw for maybe 9
months!  This is just going off of the Makefile that I still have
lying around.


Thanks Scott!

With a little bit of creativity I managed to get it all going! :)


Stefan


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] MingW32 help wanted

[Stefan de Konink]

Mike Frysinger wrote:

On Tuesday 13 March 2007, Stefan de Konink wrote:

My target is to get sqlite compiled on my host platform i686 and my
target platform mingw32. Could someone get a step by step manual to get
the 'precious' dll/.a?


well, this could be autotoolized and force people to do
./configure --host=mingw32
i do that now with a library of my own ... but i cross-compile everything from 
my Linux host ;)

-mike


Ok that doesn't work. Because:

lemon needs to run on the host system. os_unix doesn't compile because 
it has undefined 'locks'. removing os_unix leads to unresolved symbols 
in a lot of files when making the dll.


So a step by step tutorial would be nice :)


So what I did.

./configure --prefix=/opt/gtk
Adapted Makefile and replaced TCC and added a --tag to libtool (because 
it otherwise complains).

make
everything is ok until os_unix...


Stefan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] MingW32 help wanted

[Stefan de Konink]

Hello,


I have found this post:

http://marc.10east.com/?l=sqlite-users=115689163614925=2


My target is to get sqlite compiled on my host platform i686 and my 
target platform mingw32. Could someone get a step by step manual to get 
the 'precious' dll/.a?


When removing the os_unix.c from the Makefile I have everything 
compiled. But running mkdll.sh doesn't work for me :(




Stefan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] announce: libsqlfs

[Stefan de Konink]

> Currently it has been tested on 32-bit x86 and Strong Arm processors,
> with SQLite 2.8.16 and FUSE 2.3.5.  It can be built both as an
> application library or as an FUSE module.

Are you, your company, going to implement a /etc-in-one-file? Or isn't
saving settings and small rich-text-files the main target?



Stefan de Konink


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Open sqb database in php?

[Stefan de Konink]

On Thu, 6 Jul 2006, Albert van Bergen wrote:

> Is it possible to open a sqb database in php?

Natively you can open SQLite 2 databases.


There is a 'package' avaiable to open SQLite 3 database, but I never tried
that.


Stefan

Re: [sqlite] SQlite

[Stefan de Konink]

On Mon, 5 Jun 2006, Cesar David Rodas Maldonado wrote:

> I am developing something for that.
>
> where can i send you my script? (is done in C)

I think more people will be interested in it. So if it is not too big send
it to the list, otherwise put in on a website :) (or mail me privately)



Stefan

Re: [sqlite] SQlite

[Stefan de Konink]

On Mon, 5 Jun 2006, Cesar David Rodas Maldonado wrote:

> i am interesting to do something for sqlite, and i was thinking to try to do
> a multi threading for read and write at the same time, like MySQL, or i
> would like to do something for fulltext search.
>
> Please people answer me giving me ideas!!!

Fulltextsearch would be sweet :) Currently my database is too big to do a
'like' in reasonable time.


Stefan

[sqlite] Bugreport (minor)

[Stefan de Konink]

SQLite version 2.8.16
Enter ".help" for instructions
sqlite> \;
Segmentation fault

Probably some minor thing.


Stefan

Re: [sqlite] Database locks up on AMD64/PHP

[Stefan de Konink]

On Tue, 7 Mar 2006, Firman Wandayandi wrote:

> On 3/7/06, Stefan de Konink <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> >
> > We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs
> > our TV Newspaper System. It was running on a PIII-1GHz but due some
> > shifts for rendering performance increase (Inkscape SVG -> PNG) we moved
> > the complete application to an AMD64.
> >
> > The last weeks it seems the database got locked very often, and made the
> > apache/php system 'hang', not on query but on insert. Restarting Apache
> > solves the problem making me wonder what I can do to visualize the
> > problem or even fix it.
> >
> > As temporary fix I already tried to decrease the database size (now at
> > 7MB). But this only increase speed (for now).
> >
>
> What's this meant?

What part isn't clear?

Stefan

[sqlite] Database locks up on AMD64/PHP

[Stefan de Konink]

Hello,


We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs 
our TV Newspaper System. It was running on a PIII-1GHz but due some 
shifts for rendering performance increase (Inkscape SVG -> PNG) we moved 
the complete application to an AMD64.


The last weeks it seems the database got locked very often, and made the 
apache/php system 'hang', not on query but on insert. Restarting Apache 
solves the problem making me wonder what I can do to visualize the 
problem or even fix it.


As temporary fix I already tried to decrease the database size (now at 
7MB). But this only increase speed (for now).



Yours Sincerely,

Stefan de Konink

[sqlite] no download link to tclsqlite-3_3_0.zip

[Stefan Finzel]

Hi,

there is no link on the SQLite download page for "*Precompiled Binaries 
For Windows" to download tclsqlite-3_3_0.zip (sqlitedll with TCL bindings).


Please can anyone provide the community / me with **tclsqlite-3_3_0.zip 
as ** I have no access to a Microsoft OS/Compiler?


TIA

Stefan
*

--
____
  Stefan G. R. Finzel, Dipl. Phys. (Univ.)
   Ochsenkopfstr. 5, D-95478 Kemnath, 09642/704448

[sqlite] Announcement: ARMed SQLite database library

[Stefan Ruppert]

Hello everybody,

below is our announcement about the availibility of an ARM instrumented
SQLite database library. In the last month we instrumented the SQLite
source to operate with the ARM 4.0 OpenGroup measurement standard. You
can find detailed information about ARM on the OpenGroup web-site (See
reference section below).

We would like to provide the instrumentation to the sqlite community and
want to integrate it into the cvs repository if you think thats useful.
For any questions or feedback please feel free to contact us.

Regards,
Stefan

--
Stefan Ruppert <[EMAIL PROTECTED]>
Web: http://www.ruppert-it.de/
 http://www.myarm.de/


Announcement: ARMed SQLite database library
===

SQLite goes ARM 4.0
---

The popular database engine SQLite [1] now exists in an ARM [2]
instrumented version.  Therefore any application using the SQLite
database can now measure database transaction using ARM 4.0 standard
just by replacing the SQLite shared library or by linking against the
instrumented archive.

The SQLite instrumentation
--

Each SQLite database query is measured through the ARM 4.0
instrumentation. Special properties are attached to the transaction
measurement like database name and SQL query string. Currently we
support SQLite version 2.8.16 used by Qt and the latest main release
3.2.2.For a detailed description please read our SQLite
instrumentation [3] web section.

For more information or support to the SQLite ARM instrumentation
please contact us.

Contact
---

MyARM GbR
Neue Str. 4
63571 Gelnhausen-Roth
Germany
email: [EMAIL PROTECTED]
Web:   http://www.myarm.de/

References
--

[1] SQLite web site:
http://www.sqlite.org/
[2] OpenGroup ARM web site:
http://www.opengroup.org/management/arm/
[3] SQLite instrumentation page:
http://www.myarm.de/solutions/sqlite.html

[sqlite] Yatt 0.0.7 and Weblite

[Stefan Radig]

Hi,

I have released a new version (0.0.7) of my freeware bug tracker yatt.  
Most significant changes are the addition of hook scripts which can be  
used for various purposes. E.g. - at least on unix systems - a mail can be  
automatically sent when an entry is added or edited. Additionally, user  
defined policies can be implemented using the hook script.

For download and details see

http://www.yatt.de

As usually it's available for linux, windows and linux on arm processors.

Additionally I uploaded a new program called weblite. This is a very  
lightweight http server with included sqlite database and a C-like  
scripting language. It's an alpha version, so it's not announced on the  
webpage. Download here:


http://www.yatt.de/weblite.zip

Basically it's a stripped down yatt with added support for cgi scripts. It  
can be used to make very lightweight web based database applications. It's  
only one executable containing everything.
There is not much documentation, yet, and it's currently only available  
for windows. If there is interest, I will add documentation and versions  
for other operating systems. It's freeware.


Please tell me, if you think it's useful and/or have suggestions for  
improvements.


Stefan

Re: [sqlite] Re: philosophy behind public domain?

[Stefan Finzel]

As a german citizen I'll try you explain my understanding of my 
countries law. The basic concept should be similar within central Europe 
(Austria, France, Italy, Spain ... but not Great Britain) as most 
countries laws evolved from the Roman law . Sorry i am not a lawyer, 
just a programmer concerned with this question while living from his 
work but also giving parts of this work back to the community.


We have two different parts. One is called  'Urheberrecht' (right of 
author) and the other "Entscheidungs-" and/or "Verwertungsrecht" (right 
to decide  of usage and right to use) .


First one just handles the mental ownership of  a piece of work. This 
can not be  given to another party.  In many cases this is worth nothing 
as is just bundles your name with your work. Very often this right is 
incorrectly translated as Copyright even in European countries.


Second one handles the commercial and economical aspects. Of cause this 
is something total different. If you get paid for your work,  you 
sometimes loose this rights  to your  customer or employer immediatly.
If you still have this rights by your own  you are able to  
give/license/sell them like  every material thing.


As I unterstand  the american way the customer or employer get the 
unrestricted usage rights under almost all circumstances. Additionally 
the author seems to have no right to be mentioned at all.


Do not worry in casse an author tells you he gives you the right to use, 
to decide how to use AND(!!!) the right to modify it. You have all 
neccessary rights, except to remove the authors name (if it was there 
before!!!). Although this is just for the authors reputation/prestige 
even big companies have been accuessed to put back the name, to pay for 
unauthorized removal or stop usage immediatly.
Once again note, in Germany the right to modify code does not include 
removing the authors name.


Now most germans seems to accept the common GPL and BSD like copyrights. 
But I have problems understanding  many  restrictions/variatons of 
proprietary copyrights and just do not accept and use them.
In cases german citizens accepted a foreign license model ot contribute 
software, it would be nearly impossible to involve a German court 
whether for license nor for warranty aspects.


Stefan Finzel

D. Richard Hipp wrote:


On Fri, 2005-06-03 at 21:01 +0200, Andreas Rottmann wrote:
 


There is
no such thing as "disclaiming copyright" in Europe (or at least
Germany and Austria).

Rotty
   



This would be a problem for any citizen of Germany or Austria
that wanted to contribute code to the SQLite project.  I cannot
see that this would ever be a problem for an SQLite users.

Can citizens of Germany and Austria assign their copyright interest
to third parties?  If so, then if you want to contribute code to
SQLite, just assign the copyright to me and I will then dedicate
the code to the public domain, which I can do since I am not a
citizen of Austria or Germany.  If citizens of Germany and Austria
are not allowed to assign copyright, then you will not be allowed
to contribute code to SQLite regardless of what license SQLite uses.
Either way, the fact that SQLite has been dedicated to the public
domain seems unimportant.
 

Re: [sqlite] 50MB Size Limit?

[Stefan Finzel]

What about the os shells limit? Look at commands limit/ulimit/unlimit
G. Roderick Singleton wrote:
On Mon, 2005-04-11 at 12:05 -0400, Jonathan Zdziarski wrote:
 

D. Richard Hipp wrote:
   

Are you sure your users are not, in fact, filling up their disk
drives?
 

nope, plenty of free space on the drives. The 50MB limit seems to be 
very exact as well...exactly 51,200,000 bytes. I'm stumped too.
   

quotas?
 

Re: [sqlite] NULL representation/empty value in query results?

[Stefan Finzel]

Although Tcl does not know NULL at all, sqlite does. From my current 
unterstanding there are five different  fundamental internal datatypes. 
One of them is  SQLITE_NULL. But SQLITE_NULL is not used at all while 
iterating over the results of

dbcmd eval "SELECT ..."
From my current unterstanding the result of the query is still a C 
array during this iteration within DbObjCmd and than gets converted to 
Tcl objects. So it should be simple to extend to use SQLITE_NULL  and 
return a predefined value e.g. if a special PRAGMA is set.  In the 
moment i am to tired to figure out of whether there is an already usable 
relation to  combine it with the sqlite shells .nullvalue command.

It's the first time i've looked in the sqlite code base. Maybe i am wrong.
Is there any experience?
TIA

Re: [sqlite] NULL representation/empty value in query results?

[Stefan Finzel]

Hello Jay,
Yes, that is the expected behaviour. But that is not my problem.
Within sqlite console there is a a command
.nullvalue NULL
All I want is to know how I can set and use this mechanismen from my Tcl 
interpreter too.

Of cause it would be possible to misuse the default setting to get the 
required NULL. But there are plenty thousands of lines of codes and 
backups of dynamic tables containing NULLs all over the world. And some 
of them just need to differ NULL and empty value while iterating of 
datas containing NULLs in several columns. To get really paranoic there 
are even columns containing NULL and the string 'NULL'.

Now this seems to be the last step porting from mSQL to SQLite. For my 
code it is no problem to use the default option, it could be done in a 
few minutes. But nor the world wide code base neither the backups can't 
be changed. And so far, we already used a mechanismen setting the NULL 
string and it worked well for more than ten years and therefore is 
established.

Ok , can this be done regularly or do I have to make a customized 
version of SQLite?

Regards

Re: [sqlite] NULL representation/empty value in query results?

[Stefan Finzel]

Yeah! I've missed the default option. But is this SQL standard?
And it does not seem to work for me at all. I still can't differ empty 
and NULL;

create table deftest (k integer primary key,
 i integer default 'NULL',
 s char default 'NULL');
sqlite> insert into deftest (i, s) values (NULL,'');
sqlite> select * from deftest;
1||
sqlite>
So we are able to create empty values and NULL by an INSERT. Why 
shouldn't we a simple way to get it back by an SELECT too?
AlthoughTcl itself has no NULL value it would be nice to define a string 
that is given back by sqlite itself in cases where NULL values exist.

I am still hoping there is something usable and/or configurable like
PRAGMA null_string='NULL';
or
PRAGMA null_string='NAN';
which can be set before executing a select statement.
Regards
Kurt Welgehausen wrote:
Is there a way to change the NULL representation ... ?
   

No. Tcl has no null value. Usually this is not a problem,
but if you really need to distinguish between a missing
value and an empty string, you can use default values.
sqlite> create table deftest (k integer primary key,
  ...>   i integer default '?',
  ...>   s char default '??');
sqlite> insert into deftest (i) values (11);
sqlite> insert into deftest (i, s) values (22, '');
sqlite> select * from deftest;
k   i   s 
--  --  --
1   11  ??
2   22

Regards
 

[sqlite] NULL representation/empty value in query results?

[Stefan Finzel]

Hi,
maybe this is also a too simple question but I miss it "once again? ;-) "
I am using tclsqlite 2.8.16/3.1.x/3.2.0 to query for results containing 
NULL and/or empty integer/float/text/blob.

Is there a way to change the NULL representation to get a specified 
string (NULL, NAN) for NULL values and an empty output to differ between 
NULL and empty values in the same record?

Is there a way to define this representation at least for each new 
tclcommand (dbcmd)?

TIA
Stefan

[sqlite] How to do NULL Handling in SELECT Statement?

[Stefan Finzel]

Hi,
what is the correct way to query for NULL-values? I use  SQLite version 
3.2.0

create table t1(a int, b char);
insert into t1 values(1, '2');
insert into t1 values(3,NULL);
insert into t1 values(NULL,'4');
select * from t1  where b=NULL;
-- this gives no result at all
select * from t1  where b='';
-- this also gives no result
select * from t1  where b<>'';
-- this gives the expected result (but i wanted the reverse data set)
1|2
|4
-- so i tried
select * from t1  where not b<>'';
-- and still i get not the result i was looking for.
TIA
Stefan

Re: [sqlite] thoughts on a web-based front end to sqlite3 db?

[Stefan Finzel]

Tcl is really fine for web interfaces. Porting an almost 10 year old web 
application to sqlite2 and sqlite3 was a charme. It is supporting Linux, 
SunOS,Windows and maybe HP-UX so far.  If you are interested in an 
generic example using SQLite 3.1.3 (but also supporting 2.1.16) look at 
http://softguard.dyndns.org:8015  or https://softguard.dyndns.org:8016  
user is 'testIt' (uppercase i) password is '$4SBS' for unlimited access. 
Please clean up any changes you make after testing.

As SQLite needs no further administration creating databases and tables 
on the fly is as simple as using the command line. You only need a 
standard tcl distribution, tclhttpd3.5.1 and sqlite/tclsqlite itself to 
run it.

PS.: This site is only for demonstration and in work. 
Internationalization does only support English and German. Also cloning 
and copying records does not work so far. The API is still limited and 
mostly directed to msql2/3.

Andrew Piskorski wrote:
On Mon, Mar 07, 2005 at 04:22:50PM -0500, Eli Burke wrote:
 

I qualify as opinionated, so:  Tcl.  The fact that Dr. Hipp supports
Tcl directly for SQLite is yet another bonus.
 

running apache although I'm open to alternatives. The app itself uses
   

AOLserver.  Among other things, it goes very nicely with Tcl.
 

Re: [sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?

[Stefan Radig]

Hi,
I have released a new version of my task tracking software Yatt. It's  
based on SQLite.
The new version adds a bunch of new features including user management  
from the
html interface. You can find it on www.yatt.de. It's freeware.

It's currently using SQLite 2.8.x. I am thinking about upgrading to 3.1.x.  
Is there
an automated tool for converting a database from 2.8 to 3.1, which I could  
give to
users for simplifying the upgrade? I mean a tool, which opens the old  
database, reads
the scheme, creates a new database and copies all entries. Or do I have to  
build
something myself? I know this can be done from the commandline, but that's  
not an
option for normal users - especially on windows, I think.

Thanks,
Stefan

Re: [sqlite] how to get result of eval as list of lists?

[Stefan Finzel]

Kurt Welgehausen wrote:
proc lpartition {recsize data} {
 set ret {}
 set datalen [llength $data]
 for {set i 0; set j [expr {$recsize-1}]} \
 {$i < $datalen} {incr i $recsize; incr j $recsize} {
   lappend ret [lrange $data $i $j]
 }
 set ret}
So is there another way to determine the number of columns/or results of 
a query to calculate recsize?
A query can be quite complicate like combined SELECTs or something like 
{SELECT*,rowid from...}

db eval {SELECT*,rowid from t1} data {set columns $data(*); lappend 
records }]
set recsize [llength ${columns}]
set recordslist [lpartition ${recsize} ${records}]

This still requires the eval script and has the drawback duplicating the 
used memory.

There seems to be only two simple and fast solutions for me.
- First one is creating another db subcommand (e.g.: db evallist ...}
- Second one would be easier (but slower?) creating another element (a 
pedant to data(*)) holding exactly one row of the result array

 data(*)= a b rowid
 data(a)= 3
 data(b)= howdy!
 data(rowid)= 3
 data(typeof:a) = text
 data(typeof:b) = text
 data(typeof:rowid) = INTEGER
proposed:
 data(_)  = {3 howdy! 3}
TIA
Stefan

[sqlite] Mozilla XPCOM

[Stefan Neumann]

Hi,

I'm using my own little sqlite-based program to manage my personal
information chaos. It needs some browser interaction so it's making
me smiling brightly, when thinking on the things I could do porting this
to the mozilla-platform.
Unfortunately this mozilla-thing still lacks an easy-to-use database
support. It should run without having to install MySQL. Beside: It's
nice not to care about field-sizes :)
I think sqlite could be ideal for this purpose. Does anybody know
of any intentions to make something like an XPCOM-wrapper for
sqlite? Could this mean a lot of work and is there any project that
could serve as a starting-point?

Stefan

[sqlite] newbe questions for sqlite 2.8.15 used by tcl

[Finzel Stefan]

Hello!

after a long time i came back to have a look at sqlite again. As a sql
database running on Linux and Windows without additional installation
required (just packaging the other tcl stuff and running out of the box)
it is totally fullfilling my needs.

I assume it is running at SunOS, HP-UX and BSD systems too, isn't it?

Are there any performance issues comparing 2.8.15 and 3.0.4?

By the way i get an communication error downloading
tclsqlite-3.0.4.so.gz, and there seems to be no tcl binding at
tclsqlite-3.0.3.so.gz.

---

question 1: is there a way to handle read and write access differently? 

note: using msql2/3 so far, i want to create demo versions of my
applications using sqlite instead. Many of this applications have
generic sql interfaces. They are used very often. Using this interface
there are two different rights:

1. read access: selection of data is allowed
2. read/write access: changing/removing of data is allowed additionally,
creating/deleting/indexing tables 

At msql2/3 there was a simple way to achieve this: msqlsel was only able
to get data and msqlexec is able to execute arbitrary sql statements

Is there a way to do this similar using "$sqllite_handle eval args" like
a switch -readonly or -readwrite

---

question 2: the eval method allows to execute several comma seperated
sql statements at once. Are there any performance issues? How many bulks
are optimal for different operating systems?

note: i tested this just once comparing 6 single statements and a
bulk insert of 6 statements. The later took about twice in time.

---

question 2: sqlite requires unique table/index name per database? Is
this standard? 

example: second table can not be created as index has the same name

CREATE TABLE sg (
  loc CHAR(20)
) ;

CREATE INDEX sg_LBD ON sg (
loc
) ;


CREATE TABLE sg_LBD (
  loc CHAR(20)
) ;

CREATE INDEX sg_LBD ON sg_LBD (
loc
) ;

stderr: there is already an index named sg_LBD

and 

stderr: index sg_LBD already exists

fix: simple; change definitions at runtime, e.g. use tablename as prefix
and a unique id


---

question 4: how to esacpe ' characters? 
  (thought sql required \\ and \' or is this msql1/2/3
specific?)

example: 

INSERT INTO sg  VALUES ('two quotes '' are recognized');

INSERT INTO sg  VALUES ('this doesn\'t work');

stderr: near "s": syntax error

fix: simple; insert layer to handle escape per database system, convert
data at change

---

question 5:  table fields can not be named 'Group' or 'check'?
   (as used in msql1/2/3 so far)

fix: hard; rename/restructure existing data fields in database and
applications

--

TIA

Stefan
 
______

  Stefan G. R. Finzel  Dipl. Phys. (Univ.)
  Ochsenkopfstraße 5   Phone +49 (0)9642 704448
  D - 95478 Kemnath[EMAIL PROTECTED]
  Germany, Europe  http://www.osn.de/user/finzel
__