Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Peter da Silva]

What do you mean there have been "just a few threads" in the mailing list?
I can barely keep up with it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

"NULL IS NOT FALSE" is true because NULL is not a value therefor it is not 
FALSE, because FALSE is a value.
"NULL IS TRUE" is false because NULL is not a value so it's not TRUE.

You can't compare NULL with anything. All you can do is tell if it "IS NULL" or 
"IS NOT NULL".

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, 
you have to check for it explicitly.

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

Re: [sqlite] sqliteDefaultBusyCallback and HAVE_USLEEP

[Peter Kolbus]

> On Feb 26, 2020, at 5:53 AM, Graham Holden  wrote:
> 
> Wednesday, February 26, 2020, 11:15:14 AM, Richard Hipp  
> wrote:
> 
>>> On 2/25/20, Peter Kolbus  wrote:
>>> I noticed that sqliteDefaultBusyCallback() seems to depend directly on the
>>> OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the
>>> underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and
>>> unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not
>>> defined, any time resolution limitations are already handled there. And when
>>> a custom VFS is configured, that VFS may well be able to sleep in milli or
>>> microseconds using an RTOS-specific function that is not usleep() — for
>>> example FreeRTOS has osDelay().
>>> 
>>> Is there a reason sqliteDefaultBusyCallback() has this dual implementation,
>>> and defining HAVE_USLEEP is correct to get better performance on platforms
>>> that don’t have usleep()? Or could it be simplified?
>>> 
> 
>> I don't think I understand the question.  It sounds like you are
>> asking why SQLite does not try to sleep for sub-second intervals on
>> systems that do not support usleep()?
> 
> I've not looked at the source, so I don't know whether what I think
> Peter is saying is correct or not, but what I THINK Peter is implying
> there's POSSIBLY some decision (based on HAVE_USLEEP) in the "core"
> SQLite code about what sort of timeout to ask for, before the request
> gets handed over to the VFS to implement.

Thanks Graham, this is exactly what I’m pointing out.

> 
> Presumably, in the default VFS implementation(S) this also uses
> HAVE_USLEEP to decide whether sub-seconds times are possible or not.

The UNIX VFS implementation does test HAVE_USLEEP in unixSleep(). The Windows 
VFS looks like it supports subsecond times as well but doesn’t use this flag.

> 
> However, a custom VFS may have its own way of implementing sub-second
> delays (but does not implement usleep() itself)... it could therefore
> honour a request for sub-second delay if asked. However, to be asked,
> it has to "lie" about supporting usleep() and define HAVE_USLEEP.
> 
> It may simply be a concern over semantics: i.e. whether HAVE_USLEEP
> means ("implements the function usleep()" vs. "can do short delays
> somehow") or it might be a deeper problem in that if you define
> HAVE_USLEEP (to allow a custom VFS to be asked to sleep for short
> amounts) it also causes other parts of the SQLite code to try and use
> usleep() when it isn't implemented).

My primary concern is indeed semantics, especially as not setting HAVE_USLEEP 
with a custom VFS leads to worse performance when multiple threads are 
contending for a lock. (I’m prepared to go through the exercise of defining 
this for my company’s products, but wanted to raise the question first since 
other SQLite users could well be missing out on performance).

Every other use of a HAVE_XXX define that I’ve ever seen, indicates a function 
or header file is available. The second option Graham suggests (can do short 
delays) doesn’t seem to be consistent with the Winflows VFS, as 
sqliteDefaultBusyCallback() also tests SQLITE_OS_WIN.

The second concern is probably not a concern for SQLite if the project defines 
SQLITE_OS_OTHER, but there could be problems for other software components that 
take HAVE_USLEEP to mean that usleep() is available.

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

[sqlite] sqliteDefaultBusyCallback and HAVE_USLEEP

[Peter Kolbus]

I noticed that sqliteDefaultBusyCallback() seems to depend directly on the OS 
(behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the underlying 
primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and unixSleep() 
also has a roundup to whole seconds when HAVE_USLEEP is not defined, any time 
resolution limitations are already handled there. And when a custom VFS is 
configured, that VFS may well be able to sleep in milli or microseconds using 
an RTOS-specific function that is not usleep() — for example FreeRTOS has 
osDelay().

Is there a reason sqliteDefaultBusyCallback() has this dual implementation, and 
defining HAVE_USLEEP is correct to get better performance on platforms that 
don’t have usleep()? Or could it be simplified?

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

Re: [sqlite] WITHOUT ROWID tables

[Peter da Silva]

If you have control over the schema you can put a schema version in some
table.

On Sat, Feb 15, 2020, 13:21 J. King  wrote:

> On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz 
> wrote:
> >Wouldn't be something like
> >
> >SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
> >contains "WITHOUT ROWID"
> >
> >be sufficient?
> >
> >Just being curious.
> >
> >- Original Message -
> >From: sky5w...@gmail.com 
> >To: SQLite mailing list 
> >Sent: Saturday, February 15, 2020, 18:06:47
> >Subject: [sqlite] WITHOUT ROWID tables
> >
> >Ok, not ideal. Still confusing, but I see the difference.
> >For my code, I know the schemas. I guess a SQL builder could offer up
> >query
> >options to the user browsing new databases.
> >
> >On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
> >wrote:
> >
> >> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
> >
> >> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
> >is a
> >> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> >> table.
> >> >>
> >> > Confused...What if I made an index on a ROWID table?
> >> > CREATE INDEX "Z" ON "DOC" ("n_id");
> >
> >> The parameter in index_info() is normally the name of an index.  So
> >if you
> >> create an index "Z" and ask for index_info("Z") you will get
> >information on
> >> that index.
> >
> >> If you create a WITHOUT ROWID table with name 'Y", and ask for
> >> index_info("Y") you will get information on the primary key of that
> >table.
> >
> >> If both exist, you get information about the index.
> >
> >> Simon
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> CREATE TABLE t(
> c TEXT DEFAULT 'WITHOUT ROWID'
> -- this comment mentions something about WITHOUT ROWID
> );
>
>
> Contrived, no question, but possible.
> --
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a "journal"?

[Peter da Silva]

In programming a journal is a file or other data structure containing a
series of change records but can be replayed to reconstruct an operation.

On Thu, 30 Jan 2020, 05:29 Peng Yu,  wrote:

> Hi,
>
> https://www.sqlite.org/lockingv3.html#rollback
>
> "When a process wants to change a database file (and it is not in WAL
> mode), it first records the original unchanged database content in a
> rollback journal. The rollback journal is an ordinary disk file that
> is always located in the same directory or folder as the database file
> and has the same name as the database file with the addition of a
> -journal suffix. The rollback journal also records the initial size of
> the database so that if the database file grows it can be truncated
> back to its original size on a rollback."
>
> I got confused about the above statement about the rollback journal.
> The sentence "The rollback journal ..." appears to be a definition.
> But it is not.
>
> I think that the following can be considered as a definition. Given
> the above sentence appears in a section named "4.0 The Rollback
> Journal". I think it should be changed to a definition to avoid
> confusion.
>
> https://www.sqlite.org/tempfiles.html
>
> "A rollback journal is a temporary file used to implement atomic
> commit and rollback capabilities in SQLite."
>
> Regarding the choice of the word "journal", the only relevant entry
> about "journal" in the Oxford dictionary is this. Is "journal" just a
> fancy way of saying "record file"? Thanks.
>
> """
> 2 a daily record of news and events of a personal nature; a diary.
> • Nautical a logbook.
> • (the Journals) a record of the daily proceedings in the British
> Houses of Parliament.
> • (in bookkeeping) a daily record of business transactions with a
> statement of the accounts to which each is to be debited and credited.
> """
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Peter da Silva]

Server-free sounds good. Standalone too. Integrated maybe?

On Mon, Jan 27, 2020, 17:54 Donald Shepherd 
wrote:

> On Tue, 28 Jan 2020 at 10:19 am, Richard Hipp  wrote:
>
> > daemon-less?
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
>
> In-process? Same concept but defining it by what it is rather than what it
> isn't.
>
> Regards,
> Donald Shepherd.
>
> > 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Peter da Silva]

Local?

On Mon, 27 Jan 2020, 16:19 Richard Hipp,  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression since 3.19.0

[Peter Inglesby]

Hi -- I've not heard anything more about this, and I don't see a bug listed
at https://www.sqlite.org/src/rptview?rn=1.

Will it be addressed as a bug?

I hope I'm not coming across as demanding a fix -- I just want to make sure
this hasn't fallen through the gaps!

On Mon, 6 Jan 2020 at 20:24, Peter Inglesby 
wrote:

>
> How does the performance compare with this:
>>
>> CREATE TABLE t1 (a TEXT, b TEXT);
>> CREATE TABLE t2 (a TEXT, b TEXT);
>> CREATE INDEX t1_a ON t1 (a,b);
>> CREATE INDEX t2_a ON t2 (a,b);
>>
>> SELECT *
>> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
>> WHERE t1.a='123';
>>
>
> Here are best-of-three averages for my version and your version for both
> the good and bad commits:
>
> good / mine :: <0.1s
> bad / mine :: 12.5s
> good / yours :: 0.2s
> bad / yours :: 0.2s
>
> In other words, there is no regression for your version.  But it does not
> return as quickly as my version does before the regression.
>
>
>> Run "ANALYZE;" on a database that contains actual data, then send us
>> the output of ".fullschema"
>>
>
> Here you go:
>
> sqlite> analyze;
> sqlite> .fullschema
> CREATE TABLE t1 (a TEXT, b TEXT);
> CREATE TABLE t2 (a TEXT, b TEXT);
> CREATE INDEX t1_a ON t1 (a);
> CREATE INDEX t1_b ON t1 (b);
> CREATE INDEX t2_a ON t2 (a);
> CREATE INDEX t2_b ON t2 (b);
> ANALYZE sqlite_master;
> ANALYZE sqlite_master;
> INSERT INTO sqlite_stat1 VALUES('t2','t2_b','100 1000');
> INSERT INTO sqlite_stat1 VALUES('t2','t2_a','100 1000');
> INSERT INTO sqlite_stat1 VALUES('t1','t1_b','100 1000');
> INSERT INTO sqlite_stat1 VALUES('t1','t1_a','100 1000');
> ANALYZE sqlite_master;
>
> The sqlite_stat1 values make sense, because there are 1,000,000 rows in
> each of t1 and t2, with 1,000 values for each of a and b.
>
> After running ANALYZE, the origin query returns in 2.7s, but the query
> plan is unchanged.
>
> On Mon, 6 Jan 2020 at 01:53, Richard Hipp  wrote:
>
>> How does the performance compare with this:
>>
>> CREATE TABLE t1 (a TEXT, b TEXT);
>> CREATE TABLE t2 (a TEXT, b TEXT);
>> CREATE INDEX t1_a ON t1 (a,b);
>> CREATE INDEX t2_a ON t2 (a,b);
>>
>> SELECT *
>> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
>> WHERE t1.a='123';
>>
>>
>> On 1/5/20, Peter Inglesby  wrote:
>> > Is there any more information I could provide?
>>
>> Run "ANALYZE;" on a database that contains actual data, then send us
>> the output of ".fullschema"
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] List of innocuous functions?

[Peter Kolbus]

Is there any documentation showing, or an easy way to generate, the exact list 
of SQLite-provided functions that are innocuous?

I’d like to turn on the new SQLITE_TRUSTED_SCHEMA but support a variety of 
applications and am hoping for something to guide analysis.

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

Re: [sqlite] Best way to store key,value pairs

[Peter da Silva]

Another thing to consider is that RFC-822/1036 (et seq) headers are not
inherently unique and some are repeated, especially since there's software
that treats Usenet and Mail headers interchangeably. Also, debugging may
require being able to see the exact layout of the headers as received. The
safest solution is to add columns for the specific instances of the
specific headers that you need to index, and then store the original
headers unchanged as a blob or a big text column. Converting to json and
back without potentially losing data (even if you don't think you will need
that data) takes some care.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression since 3.19.0

[Peter Inglesby]

> How does the performance compare with this:
>
> CREATE TABLE t1 (a TEXT, b TEXT);
> CREATE TABLE t2 (a TEXT, b TEXT);
> CREATE INDEX t1_a ON t1 (a,b);
> CREATE INDEX t2_a ON t2 (a,b);
>
> SELECT *
> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
> WHERE t1.a='123';
>

Here are best-of-three averages for my version and your version for both
the good and bad commits:

good / mine :: <0.1s
bad / mine :: 12.5s
good / yours :: 0.2s
bad / yours :: 0.2s

In other words, there is no regression for your version.  But it does not
return as quickly as my version does before the regression.


> Run "ANALYZE;" on a database that contains actual data, then send us
> the output of ".fullschema"
>

Here you go:

sqlite> analyze;
sqlite> .fullschema
CREATE TABLE t1 (a TEXT, b TEXT);
CREATE TABLE t2 (a TEXT, b TEXT);
CREATE INDEX t1_a ON t1 (a);
CREATE INDEX t1_b ON t1 (b);
CREATE INDEX t2_a ON t2 (a);
CREATE INDEX t2_b ON t2 (b);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('t2','t2_b','100 1000');
INSERT INTO sqlite_stat1 VALUES('t2','t2_a','100 1000');
INSERT INTO sqlite_stat1 VALUES('t1','t1_b','100 1000');
INSERT INTO sqlite_stat1 VALUES('t1','t1_a','100 1000');
ANALYZE sqlite_master;

The sqlite_stat1 values make sense, because there are 1,000,000 rows in
each of t1 and t2, with 1,000 values for each of a and b.

After running ANALYZE, the origin query returns in 2.7s, but the query plan
is unchanged.

On Mon, 6 Jan 2020 at 01:53, Richard Hipp  wrote:

> How does the performance compare with this:
>
> CREATE TABLE t1 (a TEXT, b TEXT);
> CREATE TABLE t2 (a TEXT, b TEXT);
> CREATE INDEX t1_a ON t1 (a,b);
> CREATE INDEX t2_a ON t2 (a,b);
>
> SELECT *
> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
> WHERE t1.a='123';
>
>
> On 1/5/20, Peter Inglesby  wrote:
> > Is there any more information I could provide?
>
> Run "ANALYZE;" on a database that contains actual data, then send us
> the output of ".fullschema"
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance regression since 3.19.0

[Peter Inglesby]

Hi,

I've encountered a performance regression that was introduced in
41c27bc0ff1d3135 (3.19.0 2017-04-18 11:20:19).  Following the guidance in
"How To Report Bugs Against SQLite" on the wiki, I'm reporting it here.

With these tables and indexes:

CREATE TABLE t1 (a TEXT, b TEXT);
CREATE TABLE t2 (a TEXT, b TEXT);
CREATE INDEX t1_a ON t1 (a);
CREATE INDEX t1_b ON t1 (b);
CREATE INDEX t2_a ON t2 (a);
CREATE INDEX t2_b ON t2 (b);

this query now returns very slowly for large amounts of data:

SELECT *
FROM (SELECT * FROM t1 WHERE a = '123') s1
LEFT OUTER JOIN (SELECT * FROM t2 WHERE a = '123') s2
ON s1.b = s2.b;

This is the query plan after the regression:

0|0|0|SEARCH TABLE t1 USING INDEX t1_a (a=?)
0|1|1|SEARCH TABLE t2 USING INDEX t2_b (b=?)

And this is the query plan before the regression:

1|0|0|SEARCH TABLE t2 USING INDEX t2_a (a=?)
0|0|0|SEARCH TABLE t1 USING INDEX t1_a (a=?)
0|1|1|SEARCH SUBQUERY 1 AS s2 USING AUTOMATIC COVERING INDEX (b=?)

I have verified that the performance problem exists in the latest
checkout.  Is there any more information I could provide?

Thanks for your work on a great tool!

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

Re: [sqlite] Causal profiling

[Peter da Silva]

Also spaghetti inheritance is a thing.

On Wed, Jan 1, 2020, 19:19 D Burgess  wrote:

> > I’ve spent too much time lately trying to figure out or debug hellacious
> C spaghetti code
>
> And I’ve spent too much time lately trying to figure out or debug
> hellacious C++ spaghetti code
>
> Someone who writes bad C,   will write even worse C++
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding the whole C89/C90 language compliance debacle ...

[Peter da Silva]

Strictly compliant code strictly doesn't need to do anything at all,
conforming to strict standards instead of what compilers actually do is an
interesting intellectual exercise but is not necessarily useful.

On Sat, Nov 23, 2019, 16:27 Dennis Clarke  wrote:

>
> I may be the only person that does this sort of testing for my own
> reasons and perhaps for sanity checking also. I tend to think that if
> something is written to be compliant with C89/C90 then I should be able
> to run the most strict compliance compiler flags in creation and be
> perfectly happy.
>
> However that is not the case here wwith gcc 9.2.0 on RHEL 7.4 :
>
> ../sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009.compile.log
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1990 -pedantic -Wpedantic
> -pedantic-errors -O0 -m64 -g -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:23:25:
>
> error: ISO C90 does not support 'long long' [-Wlong-long]
> 23 |   typedef unsigned long long int u64;
>| ^~~~
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:
>
> In function 'KeccakF1600Step':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:5:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:29:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
>
> etc etc
>
> If we look :
>
> boe13$ head -80 tool/mksourceid.c | tail
>u64 C0, C1, C2, C3, C4;
>u64 D0, D1, D2, D3, D4;
>static const u64 RC[] = {
>  0x0001ULL,  0x8082ULL,
>  0x8000808aULL,  0x800080008000ULL,
>  0x808bULL,  0x8001ULL,
>  0x800080008081ULL,  0x80008009ULL,
>  0x008aULL,  0x0088ULL,
>  0x80008009ULL,  0x800aULL,
>  0x8000808bULL,  0x808bULL,
> boe13$
>
> Here I am using some very strict flags and note the -fno-builtin :
>
> boe13$ echo $CFLAGS
> -std=iso9899:1990 -pedantic -Wpedantic -pedantic-errors -O0 -m64
>   -g -march=k8 -mtune=k8 -Wl,-rpath=/opt/bw/lib,--enable-new-dtags
>   -fno-builtin -malign-double
> boe13$
>
> So that clearly isn't going to work here.
>
> So let's try -std=iso9899:1999 and see a different type of failure :
>
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> tclsh8.7
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksqlite3h.tcl
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009
>  >sqlite3.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mkkeywordhash
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mkkeywordhash.c
> ./mkkeywordhash >keywordhash.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> lemon
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:
>
> In function 'main':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1639:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1639 | {OPT_FSTR, "d", (char*)_d_option, "Output directory.
>   Default '.'"},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1640:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1640 | {OPT_FSTR, "D", (char*)handle_D_option, "Define an %ifdef
> macro."},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1654:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1654 | {OPT_FSTR, "T", (char*)handle_T_option, "Specify a template
> file."},
>| ^
>
> 

Re: [sqlite] Question about: Adding a record to a table with select failure

[Peter da Silva]

Assuming I'm understanding what the original message was about.

Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using application_id

[Peter da Silva]

> If you stick to lower or upper case letters, could encode up to 6 chars in
> the app_id. --DD

The return of RADIX-50.

https://en.wikipedia.org/wiki/DEC_Radix-50

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

Re: [sqlite] database disk image is malformed

[Peter da Silva]

I have been bitten by this a couple of times, so now I'm super-conservative
about how I deal with this.

What I do is have any parent database setup done by having the parent spawn
a child process to do the actual database work, and return any data the
parent needs in the status or (if more than a success status is needed)
through a pipe.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Things you shouldn't assume when you store names

[Peter da Silva]

It just drew a red box and told me to fill out all fields.

Then I enter daSilva.

Worse are the ones that don't say anything and attach the 'da' somewhere
random without telling me.

Then I show up and the nurse wastes time looking me up some other way, and
tells me I'm Peterda Silva.

On Wed, 13 Nov 2019, 15:56 Jose Isaias Cabrera,  wrote:

>
> Peter da Silva, on Wednesday, November 13, 2019 04:37 PM, wrote...
> >
> > My last name has a space in it. It's been less than a month since the
> last
> > time it was rejected by a form. One of my oldest online friends has only
> > one name. Assume nothing, permit everything.
>
> We still need to know that it is your last name and not your first name,
> or 2nd name, or... :-)  By the way, what do you do when the form says,
>
> "Please fix your last name: No spaces allowed!"
>
> Do you just type daSilva?
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Things you shouldn't assume when you store names

[Peter da Silva]

My last name has a space in it. It's been less than a month since the last
time it was rejected by a form. One of my oldest online friends has only
one name. Assume nothing, permit everything.

On Wed, 13 Nov 2019, 15:23 Swithun Crowe, 
wrote:

> Hello
>
> SS> Those are all excellent examples of why you should /not/ split up a
> SS> name into components.  For artists of all sorts (including the author
> SS> I used) what you care about is their publishing name.  Plus, how do
> SS> you identify the part of a name which you would call 'surname' ?
>
> One needs domain knowledge - knowing that Arouet is the surname of the
> artist known as Voltaire. In my use cases, this is possible (the
> researchers do it, not me). And by encoding one's domain knowledge, one is
> preserving it for future users who may not have it.
>
> Of course people are most interested in the publishing name, so Voltaire
> comes under V. But that isn't the only use that people might have for the
> data. So splitting the names and adding extra logic to display them in
> different ways in different contexts is, I think, the optimal solution.
>
> SS> Why would you want to sort by surname ?  Why is it important that
> SS> "Harris' is shown below "Harrington" ?  Don't substring searches help
> SS> you more than sorted lists ?  Wouldn't you miss the 'Parkes' family
> SS> name from John Wyndham and the 'Ruiz' from Picasso if you were
> SS> searching a sorted list ?
>
> If one had a million names from many different cultures (e.g. a customer
> database), then there may be no need/possibility to maximise reuse of the
> data, and a substring search would be the only way to find the name you
> want. But for a database of 200 French playwrights, a suitably sorted list
> is what users want.
>
> Swithun.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why do these 2 updates give different results?

[Peter da Silva]

>
> This almost seems like a job for a view.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

[Peter da Silva]

Check the local time machine backups? Even if you don't have a TM backup
drive Mojave maintains on-drive backups.

On Tue, 15 Oct 2019, 16:54 Simon Slavin,  wrote:

> Sorry, I have no other ideas.  There is no reason for a table to disappear.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Peter da Silva]

I think you're conflating things. A mail server speaks SMTP for both
inbound and outbound, IMAP/POP/webmail is all part of the user interface
stack... as would be the webforum component in any mailing list/web forum
scheme.

On Mon, 14 Oct 2019, 20:45 Warren Young,  wrote:

> On Oct 14, 2019, at 3:04 PM, Keith Medcalf  wrote:
> >
> > On Monday, 14 October, 2019 14:18, Warren Young 
> wrote:
> >
> >> Fossil Forums allow you to subscribe to email notifications.  From the
> >> reader’s perspective, it’s really very little different from the current
> >> Mailman based scheme.
> >
> > The preceding paragraph is completely at odds with the following
> paragraph, and taken together, they are completely illogical and
> inconsistent.
>
> You’re conflating inbound and outbound paths.  The ability to send email
> implies but does not require the ability to receive email.
>
> ...Which is why they’re often entirely different stacks, speaking
> different protocols!  E.g. SMTP outbound via Postfix, IMAP inbound via
> Dovecot.
>
> > All it needs is to be able to "read and process" RFC-2822 formatted
> message files that are found in an "inbound for me” directory
>
> That’s certainly one way that some email servers work.  The most common
> such scheme is called Maildir.
>
> But there’s probably at least half a dozen other ways it can work: mbox,
> MySQL store, PostgreSQL store, whatever it is that MS Exchange does that’s
> incompatible with the rest of the world…
>
> There are currently four supported outbound email setups in Fossil, and a
> stub for a fifth:
>
> https://fossil-scm.org/home/doc/trunk/www/alerts.md#advanced
>
> Why would inbound be different?
>
> Fossil isn’t in a position where it can require a specific SMTP server.
> It has to run on pretty much every common desktop and server platform.  You
> have to get pretty far down the long tail of OSes before you find one that
> Fossil doesn’t get used on daily by someone.  Therefore, we have to support
> approximately everything.
>
> On top of integrating with all common SMTP stacks, drh long ago stated a
> wish to write his own SMTP server.  (The latter being why Fossil has the
> start of one included!)  This should not surprise you if you’ve followed
> his career. :)
>
> The last time I counted up the pages of RFCs you have to implement to
> speak to a large fraction of the Internet email infrastructure — which was
> one of the times this argument came up on this mailing list! — it was
> something like 500 pages of standardese.  It is not just RFC-2822.  Getting
> to something useful will take time, which comes out of the time budget for
> SQLite, Fossil, etc.
>
> There is the option of writing glue software between Fossil and whatever
> SMTP infrastructure you already have, but no one’s bothered to do that in
> the year or so that Fossil Forums have been in steady use.  To me, that
> speaks more of the desirability of inbound email submission than about its
> inherent difficulty.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Looks interesting, but if I'm reading the descriptions right I don't think
those would help: my partitioning can be on a hash of a unique text ID or
on a geographic area, and both of those seem to be based on unique rowid
ranges.

On Thu, Aug 22, 2019 at 1:23 PM Keith Medcalf  wrote:

>
> Have you looked at the swarmvtab or unionvtab extension?
>
> https://www.sqlite.org/unionvtab.html
> https://www.sqlite.org/swarmvtab.html
>
> Which can "avoid" having to write your own unions.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Peter da Silva
> >Sent: Thursday, 22 August, 2019 11:57
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Attached databases and union view.
> >
> >Still a bit over 3x slower on queries but that's a 7x performance
> >improvement.
> >
> >On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva 
> >wrote:
> >
> >> Legit. I'll try that.
> >>
> >> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
> >
> >> wrote:
> >>
> >>> I don't know how smart the planner is, but as a thought, would
> >UNION ALL
> >>> make any improvement over just UNION? With just UNION it has to
> >>> de-duplicate all the subquery results whereas with UNION ALL it
> >would be
> >>> free to separate all the various subqueries from each other.
> >>>
> >>> Or do you actually need the UNION to de-dupe stuff?
> >>>
> >>>
> >>> -Original Message-
> >>> From: sqlite-users 
> >On
> >>> Behalf Of Peter da Silva
> >>> Sent: Thursday, August 22, 2019 11:28 AM
> >>> To: SQLite mailing list 
> >>> Subject: [sqlite] Attached databases and union view.
> >>>
> >>> Have an existing application that's pushing the limit on how fast
> >it can
> >>> read data and add it to the database, and thinking of sharding the
> >>> database
> >>> file so I can have multiple writers writing to shards of the main
> >tables.
> >>>
> >>> ATTACH DATABASE 'shard0.sqlite' as shard0;
> >>> ATTACH DATABASE 'shard1.sqlite' as shard1;
> >>> ...
> >>>
> >>> CREATE TEMPORARY VIEW sharded_main_table AS
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard0.main_table
> >>> UNION
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard1.main_table
> >>> ...;
> >>>
> >>> What's the best way to construct this union view so the query
> >optimizer
> >>> won't be horribly confused? If I run something like "SELECT
> >count(*) FROM
> >>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20
> >times slower
> >>> than the same query against the original main_table. Running the
> >query
> >>> against each shardN.main_table it's actually faster (in total time
> >for all
> >>> queries in sequence) than running it against the original table.
> >>>
> >>> Is there a better way to construct the view, or am I going to get
> >best
> >>> query performance by making my code shard-aware?
> >>>
> >>> All the original indexes on main_table have been copied to the
> >shard
> >>> databases.
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@mailinglists.sqlite.org
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@mailinglists.sqlite.org
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>>
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Still a bit over 3x slower on queries but that's a 7x performance
improvement.

On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva  wrote:

> Legit. I'll try that.
>
> On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
> wrote:
>
>> I don't know how smart the planner is, but as a thought, would UNION ALL
>> make any improvement over just UNION? With just UNION it has to
>> de-duplicate all the subquery results whereas with UNION ALL it would be
>> free to separate all the various subqueries from each other.
>>
>> Or do you actually need the UNION to de-dupe stuff?
>>
>>
>> -----Original Message-
>> From: sqlite-users  On
>> Behalf Of Peter da Silva
>> Sent: Thursday, August 22, 2019 11:28 AM
>> To: SQLite mailing list 
>> Subject: [sqlite] Attached databases and union view.
>>
>> Have an existing application that's pushing the limit on how fast it can
>> read data and add it to the database, and thinking of sharding the
>> database
>> file so I can have multiple writers writing to shards of the main tables.
>>
>> ATTACH DATABASE 'shard0.sqlite' as shard0;
>> ATTACH DATABASE 'shard1.sqlite' as shard1;
>> ...
>>
>> CREATE TEMPORARY VIEW sharded_main_table AS
>> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
>> UNION
>> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
>> ...;
>>
>> What's the best way to construct this union view so the query optimizer
>> won't be horribly confused? If I run something like "SELECT count(*) FROM
>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
>> than the same query against the original main_table. Running the query
>> against each shardN.main_table it's actually faster (in total time for all
>> queries in sequence) than running it against the original table.
>>
>> Is there a better way to construct the view, or am I going to get best
>> query performance by making my code shard-aware?
>>
>> All the original indexes on main_table have been copied to the shard
>> databases.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Originally Tcl/native Tcl binding, now a C++ extension calling the
C-binding that's a Tcl extension itself.

On Thu, Aug 22, 2019 at 11:17 AM test user 
wrote:

> What language/binding library are you using?
>
> On Thu, 22 Aug 2019 at 16:45, Peter da Silva  wrote:
>
> > Database is on tmpfs and periodically snapshotted to SSD. There are
> > bottlenecks upstream of sqlite that we can see in traces.
> >
> > On Thu, Aug 22, 2019 at 10:36 AM Warren Young 
> wrote:
> >
> > > On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> > > >
> > > > Have an existing application that's pushing the limit
> > >
> > > If the limit is in hardware, shards won’t help.
> > >
> > > For example, a SQLite DB on a 7200 RPM spinning disk is limited to
> about
> > > 60 transactions per second under the stock SQLite fsync logic, since
> each
> > > takes 2 revolutions to commit.  (One to write to the journal, and one
> to
> > > commit the journal entry.)  Writes to multiple shards only get to
> share a
> > > platter rotation if there is no seek between writes.
> > >
> > > The limits are higher for SSDs, but there’s still a low limit on the
> > > number of parallel writes.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Legit. I'll try that.

On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
wrote:

> I don't know how smart the planner is, but as a thought, would UNION ALL
> make any improvement over just UNION? With just UNION it has to
> de-duplicate all the subquery results whereas with UNION ALL it would be
> free to separate all the various subqueries from each other.
>
> Or do you actually need the UNION to de-dupe stuff?
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Peter da Silva
> Sent: Thursday, August 22, 2019 11:28 AM
> To: SQLite mailing list 
> Subject: [sqlite] Attached databases and union view.
>
> Have an existing application that's pushing the limit on how fast it can
> read data and add it to the database, and thinking of sharding the database
> file so I can have multiple writers writing to shards of the main tables.
>
> ATTACH DATABASE 'shard0.sqlite' as shard0;
> ATTACH DATABASE 'shard1.sqlite' as shard1;
> ...
>
> CREATE TEMPORARY VIEW sharded_main_table AS
> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
> UNION
> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
> ...;
>
> What's the best way to construct this union view so the query optimizer
> won't be horribly confused? If I run something like "SELECT count(*) FROM
> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
> than the same query against the original main_table. Running the query
> against each shardN.main_table it's actually faster (in total time for all
> queries in sequence) than running it against the original table.
>
> Is there a better way to construct the view, or am I going to get best
> query performance by making my code shard-aware?
>
> All the original indexes on main_table have been copied to the shard
> databases.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Database is on tmpfs and periodically snapshotted to SSD. There are
bottlenecks upstream of sqlite that we can see in traces.

On Thu, Aug 22, 2019 at 10:36 AM Warren Young  wrote:

> On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> >
> > Have an existing application that's pushing the limit
>
> If the limit is in hardware, shards won’t help.
>
> For example, a SQLite DB on a 7200 RPM spinning disk is limited to about
> 60 transactions per second under the stock SQLite fsync logic, since each
> takes 2 revolutions to commit.  (One to write to the journal, and one to
> commit the journal entry.)  Writes to multiple shards only get to share a
> platter rotation if there is no seek between writes.
>
> The limits are higher for SSDs, but there’s still a low limit on the
> number of parallel writes.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attached databases and union view.

[Peter da Silva]

Have an existing application that's pushing the limit on how fast it can
read data and add it to the database, and thinking of sharding the database
file so I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0;
ATTACH DATABASE 'shard1.sqlite' as shard1;
...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table
UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table
...;

What's the best way to construct this union view so the query optimizer
won't be horribly confused? If I run something like "SELECT count(*) FROM
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
than the same query against the original main_table. Running the query
against each shardN.main_table it's actually faster (in total time for all
queries in sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best
query performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard
databases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Peter da Silva]

If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unreachable Breaks with ICC on Windows

[Peter da Silva]

I would argue that an unreachable break should not be a warning, since
leaving out the break is just asking for some beggar to come in and make it
reachable again and summon the fallthrough fairy without noticing it. :)

On Sat, Jul 13, 2019 at 9:32 AM André Borchert <0xa...@gmail.com> wrote:

> Hello,
>
> ICC reports two unreachable "break;" statements as warnings:
>
> SQLite 3.29
>
> Windows 2019
> Visual Studio 16.2.0 Preview 3.0
> Intel Parallel Studio XE 2019 Update 4
>
>
> 1>-- Rebuild All started: Project: SQLite, Configuration: Release x64
> --
> 1>sqlite3.c
> 1>D:\Dropbox\Chaos\SQLite Test\SQLite\sqlite3.c(87093): message #111:
> statement is unreachable
> 1>break;
> 1>^
> 1>
> 1>D:\Dropbox\Chaos\SQLite Test\SQLite\sqlite3.c(87850): message #111:
> statement is unreachable
> 1>break;
> 1>^
> 1>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to set access permissions to protect a database file?

[Peter da Silva]

I don't believe you are providing any protection against editing the file,
just deleting.

On Sun, Jun 9, 2019, 4:38 PM Markos  wrote:

> Many thanks to Luuk, Adrian, Graham, James, Simon, Richard and Peter,
>
> To guarantee "some" protection to the files containing the database I
> decided to use the following strategy:
>
> I created, as root, the directory /home/reading_room
>
> And activated the "sticky bit" of the reading_room directory with the
> command:
>
> chmod +t /home/reading_room/
>
> And transferred, the files to the new directory with the following
> access permissions:
>
> reading_room.tcl  rwxr--r-x  (owner markos)
>
> reading_room.db rw-r--rw- (owner markos)
>
>
> This way other users can run the reading_room.tcl program but can't  but
> not edit it.
>
> And can't delete the files (.tcl or .db)
>
> Trying to protect against Murphy, but not Machiavelli. (As Richard said.)
>
> Thank you,
> Markos
>
> Em 26-05-2019 23:33, Adrian Ho escreveu:
> > On 27/5/19 12:43 AM, Luuk wrote:
> >> On 26-5-2019 13:52, Adrian Ho wrote:
> >>> On 26/5/19 7:49 AM, Markos wrote:
> >>>> I made a program (reading_room.tcl), with Sqlite running on Debian 9,
> >>>> to control the books of a reading room.
> >>>>
> >>>> I implemented an authentication system for common users and
> >>>> administrator users in the reading_room.tcl program.
> >>>>
> >>>> Now I want that any user logged in the Linux be able to run the
> >>>> program reading_room.tcl, which will access the database (books.db)
> >>>>
> >>>> But I want to protect the file books.db so that only the the program
> >>>> reading_room.tcl can access the books.db file (to read or write). But
> >>>> that no user could delete or write to the file books.db (only the
> >>>> program reading_room.tcl)
> >>> The standard Unix permissions/ACLs architecture doesn't support this
> use
> >>> case directly.
> >> Can you give some more information on this, because it seems to work
> >> as i excpect it to:
> >>
> >> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> >> users are in the group 'users':
> >>
> >> luuk2@opensuse1:/home/luuk/temp> whoami
> >> luuk2
> >> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> >> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> >> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> >> SQLite version 3.28.0 2019-04-16 19:49:53
> >> Enter ".help" for usage hints.
> >> sqlite> select * from test;
> >> 1
> >> 2
> >> sqlite> insert into test values(3);
> >> Error: attempt to write a readonly database
> >> sqlite> .q
> >> luuk2@opensuse1:/home/luuk/temp>
> >>
> > The OP wants *all users* to be able to update (write) the DB via the Tcl
> > script reading_room.tcl, but *not* by (say) running the SQLite shell or
> > something else. In your setup, as long as a specific user has write
> > permissions, *every program* the user runs can write to the DB.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to set access permissions to protect a database file?

[Peter da Silva]

This is what the UNIX group ID and the set-group-id capability is for.

You make the files readable (or read and write) by a group (mode 640 or
660).

You make the program that controls access to the files set-group-id to that
group.

You may need a small "C" wrapper program to run the script set-group-id
depending on the version of UNIX and security settings. Allowing scripts to
run set-group-id has at times been violently controversial, and I have not
bothered to track teh state of teh art.

On Mon, May 27, 2019 at 8:37 AM Richard Damon 
wrote:

> On 5/27/19 9:16 AM, Simon Slavin wrote:
> > On 27 May 2019, at 3:33am, Adrian Ho  wrote:
> >
> >> The OP wants *all users* to be able to update (write) the DB via the
> Tcl script reading_room.tcl, but *not* by (say) running the SQLite shell or
> something else. In your setup, as long as a specific user has write
> permissions, *every program* the user runs can write to the DB.
> > Some work in this thread has been because the file is a SQLite
> database.  But it's not really a SQLite question.  It's more about the
> access/permissions model of Debian 9.  What OP wants can be reduced to a
> simpler situation:
> >
> > "I have a text file.  It's on a computer running Debian 9.  I want to
> make sure that this text file can be read/written by multiple people, but
> that it can be read/written only using this program I wrote."
> >
> > I've never used Debian so I can't solve the problem.  But from what
> little I remember of Linux, one solution is to create a special account for
> that one operation, and set up that account in a non-standard way.
>
> Actually, It can be an SQLite question, as another way to solve the base
> problem is to do something to the database so that only 'authorized'
> applications can access/modify it. One way to do that is to use the SEE
> extension and encrypt the database. If you do that then you no longer
> have the equivalent of a 'text file', so unauthorized applications can't
> access the file.
>
> It isn't perfect protection, because someone still will have the ability
> to delete/overwrite the file, to protect from that seems to need the
> protection method you describe, but if you are only trying to protect
> against Murphy, and not Machiavelli (as the saying goes) it may be a
> viable, and portable, solution.
>
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Have SQLite handle values of my own type

[Peter da Silva]

Windows TCP/IP userland used to be a port of the BSD networking tools (I
think via Lachman Associates) so that's not surprising. Allegedly they
reimplemented it at some point.

On Thu, May 23, 2019 at 3:57 PM Keith Medcalf  wrote:

> On Thursday, 23 May, 2019 08:35, Dominique Devienne 
> wrote:
>
> >On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
> > wrote:
> >
> >> I have been working network for a long time, and I have never seen
> >> any application that takes "zeroed left-filled" IP addresses.  Just
> >> sharing...
> >> Thanks.
>
> > Works for me with a .001 at least, as shown below. But that wasn't
> > really the point I was making, FWIW. --DD
>
> Well, actually, that depends on how the code in the network stack converts
> from presentation format to network format.  Windows 10 1903 (at least)
> treats each component as an input number which means that a number that
> starts with a 0 is an octal number ... (ie, the BSD way)
>
> >ping 172.217.14.227
>
> Pinging 172.217.14.227 with 32 bytes of data:
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
>
> >ping 172.217.014.227
>
> Pinging 172.217.12.227 with 32 bytes of data:
> Reply from 172.217.12.227: bytes=32 time=73ms TTL=45
> Reply from 172.217.12.227: bytes=32 time=72ms TTL=45
>
> Other parsers may see the input as invalid:
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.014.227'));
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.14.227'));
> 172.217.14.227
>
> So really, what you get depends on who wrote the code that is doing the
> translation.  Since the code that I used to implement ipblobaddr and
> ipaddrblob is taken from the ISC DNS Bind code based on code written by
> Paul Vixie in 1996, it is quite possible that many things will see leading
> 0's as invalid input.  Some things (for example Cisco IOS) may also choose
> to just ignore the extra 0's.  Other things may take it as an indicator
> that the value is base-8 rather than base-10.
>
> See
> https://tools.ietf.org/html/draft-main-ipaddr-text-rep-00
> https://en.wikipedia.org/wiki/Dot-decimal_notation
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] althttpd.c check-in: efdc1b8e66

[Peter da Silva]

> A middle ground is Docker for Windows.  I hesitate to speculate on its speed 
> compared to the other three, but I think there’s reason to hope it could be a 
> good option.

I would expect that running a Linux image inside Microsoft's hypervisor 
environment and running a Linux image in Docker's or other third party's 
hypervisor environment would provide roughly similar performance... the 
technology is well understood and mature. The main reason to pick one over 
another is the VM management environment you prefer.

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

Re: [sqlite] Use cases for sqlite3_value_frombind()?

[Peter da Silva]

I don't think I would use this as a security indicator, whether it's a
bound parameter or a constant in the query string doesn't tell you if it's
from a trusted source or not. This is more an indicator that this value is
likely to change in subsequent queries.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

Oy. I've worked on safety-critical systems with hard real-time constraints
too. For the most part they didn't *have* file systems or the file systems
were basically read-only in production. Sticking a relational database any
closer than the SCADA monitoring node would not be a thing that happens,
let alone using a compressing file system to hold that database. But
there's a whole spectrum of embedded systems between that and arcade games.

On Wed, Apr 10, 2019 at 5:17 PM Keith Medcalf  wrote:

>
> On Wednesday, 10 April, 2019 14:21, Peter da Silva 
> wrote:
>
> >On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf 
> wrote:
>
> >> Why would anyone fart about with added complication and the
> >> concomittant increased unreliability when storage is so damn cheap?
>
> >Embedded systems and mobile devices.
>
> You mean "play things", for the most part.
>
> By their very definitions "play things" do not require reliability and as
> such the added complication and inherent increase in unreliability due to
> that increased complexity is of no real effect.
>
> I am used to dealing with "important shit".  That means that if it stops
> working, even for a minute, it might entail costs of millions of dollars
> and perhaps a few deaths or cripplings as well.
>
> There is a very great difference between the "streaming media crap" not
> working for a bit and you have to (heavens forbid) read a book, or the mail
> server going down for a day or two, which are really nothing more than
> minor inconveniences by comparison.  The streaming box screws up?  Throw it
> out and buy another.  In the "play things" world adding complexity to
> increase unreliability and save a few pennies is often a reasonable
> trade-off.  After all, nothing of any real significance will be lost -- it
> is merely a bit of inconvenience to suffer through with no real lasting
> impact.
>
> On the other hand if the consequence of failure is certain death of 10
> people, then I would much rather be spending more money on reliable
> hardware to maintain the designed level of reliability than to save a few
> shekels by tossing "compression" into the mix thereby reducing reliability
> and increasing the probability (through an increase in unpredictable
> failure modes) of those 10 people dying.  I think if you were one of those
> 10 people with your life at risk you would see things the same way.
>
> >But of course those probably don't apply here. :)
>
> It is all a matter of perspective.  Lets imaging that the problem with the
> 747MAX was not that the new control system was designed by an idiot and
> that insufficient training on the detection and correction of the "we know
> this is going to be a problem" so intruduced were not the issue.  Lets say
> instead that the files were merely a bit too big for the hard drives they
> decided to use.  They have the option of (a) spending an additional $100
> and getting larger storage and not changing the failure scenario's at all;
> or, (b) not spending any money and instead adding yet another layer of
> software to perform "compression" instead (thus changing the failure
> scenario's because now you have a whole whack of new failure modes).
>
> The "Play Things" people consider that the crash of the airliner and the
> loss of equipment and all life aboard is merely an "inconvenience" and will
> choose option (b) because hey, the software always works, right?  The
> "Important Shit" people will consider that the *possible* increase in risk
> of loss of equipment and life due to the addition of yet more complexity
> cannot be tolerated and will chose option (a) because it is far more cost
> effective than the analysis that will be required to *prove* option (a) has
> not increased the risk.
>
> I simply happen to fall into the "Important Shit" category of people by
> default.  I am somewhat risk-adverse as they say.  If the risk associated
> with a thing is significant, then spend as much as required to reduce that
> risk to an acceptable level.  If the risk associated with a thing is
> negligible, then get the cheapest shit available and when it "breaks" throw
> it out and get another.
>
> This does not mean that the "Play Things" outlook is incorrect.  It merely
> depends on the garden in which you are playing and in to which category the
> product falls.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

> Why would anyone fart about with added complication and the concomittant
> increased unreliability when storage is so damn cheap?
>

Embedded systems and mobile devices.

But of course those probably don't apply here. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

A database trades off space for performance and functionality. It is
expected that a database with indexes that you can randomly access is going
to take more space than the raw data, let alone a compressed version of the
raw data.

On Wed, Apr 10, 2019 at 12:39 AM Peng Yu  wrote:

> I have some TSV table in .gz format of only 278MB. But the
> corresponding sqlite3 database exceeds 1.58GB (without any index). Is
> there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation fault running a query

[Peter Hardman]

OK. I've anonymised the database and cut it down so that only the records 
extracted by the query are present. I have this feeling I've done something 
stupid with the data - but all the other queries I run against this database 
work fine.


It still gives a segfault when I run the query.

The SQLite3 version is SQLite 3.27.1 2019-02-08 13:17:39, built with zlib 
version 1.2.11 and gcc-8.2.1 20181127.


I've attached the database file (300K).



Dan Kennedy wrote on 06/03/2019 12:07:


On 6/3/62 16:37, Peter Hardman wrote:

So, I forgot I had emails from the list turned off

Integrity check shows no results.

The query fails from the CLI

The schema (of a very much cut down database) is attached.



Can you post the results of running the ".fullschema" command in the shell tool?

Thanks,

Dan.







And if it does still crash, please provide the database schema to help with 
debugging.



-Original Message-
From: sqlite-users [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On 
Behalf Of Simon Slavin

Sent: Tuesday, March 05, 2019 3:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Segmentation fault running a query

On 5 Mar 2019, at 8:06pm, Peter Hardman  
wrote:

> If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter 
or from sqlitebrowser I get a segmentation fault.


Please find the SQLite command-line tool on your computer.  If you don't 
already have one you can download one for your platform in the section 
"Precompiled Binaries" on


<https://sqlite.org/download.html>

Using that program, please run

PRAGMA integrity_check;

If that reports no problems (should return no results) then please run your 
SELECT query in that program just to verify that it gets the same result your 
own code does.


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

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


--

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

[sqlite] Segmentation fault running a query

[Peter Hardman]

So, I forgot I had emails from the list turned off

Integrity check shows no results.

The query fails from the CLI

The schema (of a very much cut down database) is attached.



And if it does still crash, please provide the database schema to help with 
debugging.



-Original Message-
From: sqlite-users [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On 
Behalf Of Simon Slavin

Sent: Tuesday, March 05, 2019 3:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Segmentation fault running a query

On 5 Mar 2019, at 8:06pm, Peter Hardman  
wrote:

> If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter 
or from sqlitebrowser I get a segmentation fault.


Please find the SQLite command-line tool on your computer.  If you don't already 
have one you can download one for your platform in the section "Precompiled 
Binaries" on


<https://sqlite.org/download.html>

Using that program, please run

PRAGMA integrity_check;

If that reports no problems (should return no results) then please run your 
SELECT query in that program just to verify that it gets the same result your 
own code does.

--

Peter Hardman
('CREATE TABLE version (\nmajor_version INTEGER NOT NULL PRIMARY 
KEY,\nminor_version INTEGER NOT NULL,\nparadox_upload TEXT,\nconverted 
TIMESTAMP NOT NULL,\ncreated TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_class (\nclass_name VARCHAR(16) NOT NULL PRIMARY 
KEY,\ndescription VARCHAR(32) NOT NULL UNIQUE,\nbase_subscription INTEGER NOT 
NULL,\ndiscount INTEGER NOT NULL,\nrenewal_period INTEGER NOT 
NULL,\njoining_fee INTEGER NOT NULL,\nvotes INTEGER NOT NULL,\nrenewal_notice 
INTEGER NOT NULL CHECK(renewal_notice in (0, 1)),\nis_active INTEGER NOT NULL 
CHECK(is_active in (0, 1)),\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed 
TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_dates (\nrenewal_date DATE NOT NULL PRIMARY 
KEY,\ndiscount_end_date DATE NOT NULL,\nmembership_ceases DATE NOT 
NULL,\njoiner_rollfwd_date DATE NOT NULL,\nlast_renewal_date DATE NOT 
NULL,\nnext_renewal_date DATE NOT NULL,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE pmt_method (\npmt_method VARCHAR(16) NOT NULL PRIMARY 
KEY,\nmethod_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_source (\nsource VARCHAR(32) NOT NULL PRIMARY 
KEY,\nsource_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE nonren_reason (\nreason VARCHAR(64) NOT NULL PRIMARY 
KEY,\nreason_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE phone_type (\nphone_type VARCHAR(16) NOT NULL PRIMARY 
KEY,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE role_type (\nrole_type VARCHAR(32) NOT NULL PRIMARY 
KEY,\nrole_code VARCHAR(1) NOT NULL,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE county (\ncounty VARCHAR(32) NOT NULL PRIMARY KEY,\ncounty_code 
VARCHAR(2) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed 
TIMESTAMP NOT NULL)',)
('CREATE TABLE adj_county (\nrec_id INTEGER NOT NULL PRIMARY KEY 
AUTOINCREMENT,\ncounty VARCHAR(32) NOT NULL REFERENCES county (county) ON 
UPDATE RESTRICT ON DELETE RESTRICT,\nadj_county VARCHAR(32) NOT NULL REFERENCES 
county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,\nchange_reason 
VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL,\nCONSTRAINT 
adj_county_key UNIQUE (county, adj_county))',)
('CREATE TABLE sqlite_sequence(name,seq)',)
("CREATE TABLE country (\ncountry VARCHAR(32) NOT NULL PRIMARY 
KEY,\ncountry_code VARCHAR(2) NOT NULL UNIQUE,\ncalling_code VARCHAR(4) NOT 
NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)",)
('CREATE TABLE region (\nregion VARCHAR(32) NOT NULL PRIMARY KEY,\nregion_code 
VARCHAR(2) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed 
TIMESTAMP NOT NULL)',)
('CREATE TABLE in_region (\nrec_id INTEGER NOT NULL PRIMARY KEY 
AUTOINCREMENT,\nregion VARCHAR(32) NOT NULL REFERENCES region (region) ON 
UPDATE RESTRICT ON DELETE RESTRICT,\ncounty VARCHAR(32) NOT NULL REFERENCES 
county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,\ncountry VARCHAR(32) NOT 
NULL REFERENCES country (country) ON UPDATE RESTRICT ON DELETE 
RESTRICT,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT 
NULL,\nCONSTRAINT in_region_key UNIQUE (region, county, country))',)
('CREATE TABLE post_area (\noutward_code VARCHAR(4) NOT NULL PRIMARY 
KEY,\narea_name VARCHAR(32) NOT NULL UNIQUE,\nlatitude FLOAT NOT 
NULL,\nlongitude FLOAT NOT NULL,\neasting INTEGER NOT NULL,\nnorthing INTEGER 
NOT NULL,\ngrid_ref CHARACTER(8) NOT NULL,\nchange_reason VARCHAR(32) NOT 
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CRE

[sqlite] Segmentation fault running a query

[Peter Hardman]

If i run the following query from Python 3.7 using the sqlite3 DBAPI 
adapter or from sqlitebrowser I get a segmentation fault.


The query runs error free on PostgreSQL 9.6

The query fails if the 'in' list has more than two entries.

Any ideas? I could create a test database but it will take a while as it 
has personal information in which I would have to obfuscate to avoid 
falling foul of the GDPR.


Query:

select  m.member_no, p.title, p.initials, p.forename, p.surname,
m.address_1, m.address_2, m.address_3, m.post_town, m.county,
m.post_code, m.country, s.class_name, m.joined, s.renewed,
s.expires, s.pmt_method, m.non_renewal, m.share_info,
m.last_changed, p.person_id, r.region
from person p join mem_pers mp on p.person_id = mp.person_id
join member m on mp.member_no = m.member_no
join current_member_status s on m.member_no = s.member_no
left join in_region r on m.county = r.county
 join mem_dates d on s.expires > d.renewal_date where m.non_renewal = ''
and p.person_id in 
(200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077) 


 order by m.country, m.county, p.surname, p.initials

Core dump (the interesting bit only):

[peter@system04 ppd-memrpt2]$ coredumpctl info 10267
   PID: 10267 (sqlitebrowser)
   UID: 1000 (peter)
   GID: 1000 (peter)
Signal: 11 (SEGV)
 Timestamp: Tue 2019-03-05 18:55:38 GMT (55min ago)
  Command Line: sqlitebrowser
Executable: /usr/bin/sqlitebrowser
 Control Group: /user.slice/user-1000.slice/session-1.scope
  Unit: session-1.scope
 Slice: user-1000.slice
   Session: 1
 Owner UID: 1000 (peter)
   Boot ID: f27026461d4944c8a24ca6524def7c0c
Machine ID: 47f95fceb9d84436be17de2a1639655e
  Hostname: system04
   Storage: 
/var/lib/systemd/coredump/core.sqlitebrowser.1000.f27026461d4944c8a24ca6524def7c0c.10267.1551812138>

   Message: Process 10267 (sqlitebrowser) of user 1000 dumped core.

Stack trace of thread 10267:
#0  0x7ffaf1357223 sqlite3VdbeFindCompare 
(libsqlite3.so.0)
#1  0x7ffaf12ba157 sqlite3BtreeMovetoUnpacked 
(libsqlite3.so.0)

#2  0x7ffaf134db86 sqlite3VdbeExec (libsqlite3.so.0)
#3  0x7ffaf1352920 sqlite3_step (libsqlite3.so.0)
#4  0x556bb8b1b09e _ZN10MainWindow12executeQueryEv 
(sqlitebrowser)

#5  0x556bb8c1da2f n/a (sqlitebrowser)
#6  0x7ffaf0a4187c 
_ZN11QMetaObject8activateEP7QObjectiiPPv (libQt5Core.so.5)
#7  0x7ffaf16f5633 _ZN7QAction9triggeredEb 
(libQt5Widgets.so.5)
#8  0x7ffaf16f7d1a 
_ZN7QAction8activateENS_11ActionEventE (libQt5Widgets.so.5)

#9  0x7ffaf17ea45e n/a (libQt5Widgets.so.5)
#10 0x7ffaf17ea696 
_ZN15QAbstractButton17mouseReleaseEventEP11QMouseEvent (libQt5Widgets.so.5)
#11 0x7ffaf18da04b 
_ZN11QToolButton17mouseReleaseEventEP11QMouseEvent (libQt5Widgets.so.5)
#12 0x7ffaf173cb68 _ZN7QWidget5eventEP6QEvent 
(libQt5Widgets.so.5)
#13 0x7ffaf18da0f4 _ZN11QToolButton5eventEP6QEvent 
(libQt5Widgets.so.5)
#14 0x7ffaf16fbe24 
_ZN19QApplicationPrivate13notify_helperEP7QObjectP6QEvent 
(libQt5Widgets.so.5)
#15 0x7ffaf1703929 
_ZN12QApplication6notifyEP7QObjectP6QEvent (libQt5Widgets.so.5)
#16 0x7ffaf0a16e99 
_ZN16QCoreApplication15notifyInternal2EP7QObjectP6QEvent (libQt5Core.so.5)
#17 0x7ffaf1702c08 
_ZN19QApplicationPrivate14sendMouseEventEP7QWidgetP11QMouseEventS1_S1_PS1_R8QPoi>

lines


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

Re: [sqlite] Confused with type mismatch in Tcl interface

[Peter da Silva]

One nasty thing you might be able to do in the interim is to shimmer the
object to the type you want by doing something like [set x [expr {double
$x}]] to make it double.

On Wed, Feb 27, 2019 at 9:31 AM Richard Hipp  wrote:

> On 2/27/19, Jean-Baptiste Gardette  wrote:
> > have you any explanation why the typof() output of
> > "SELECT DerefDict_SQL('A'), typeof(DerefDict_SQL('A'))"
> > gives "text" in Sequence A and "real" in Sequence B ?
> >
>
> No.
>
> The code that determines the return type of a TCL UDF is here:
> https://www.sqlite.org/src/artifact/6b19e7562195aaf8?ln=993-1018
>
> As you can see, the interface is looking at the Tcl_Obj.typePtr value
> to try to guess an appropriate return type.  This is considered bad
> form in TCL since TCL makes no guarantees about the value of typePtr
> for a Tcl_Obj.  But the technique has worked well for nearly two
> decades.
>
> What if we were to add a new option to the "db function" method of the
> TCL interface that let you specify the return type of the
> TCL-implemented UDF?  If the optional argument is omitted, then the
> current behavior of trying to guess the return type based on typePtr
> is used. But if the "-returntype TYPE" argument is present, it
> determines which return type to use regardless of the value in the
> typePtr.  Would that help your situation?
>
> Note that my tests were run after compiling both TCL and SQLite from
> sources.  There is no telling what non-standard changes may have been
> added by people who assembled your pre-compiled binaries.
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to `this` table?

[Peter da Silva]

As an aside, this schema seems to be violating 
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
 particularly rule 21.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: table-valued functions for PRAGMA ignore database names

[Peter Gunold]

Hello,

I found a bug in sqlite when using table-values pragma functions with
attached databases when using the sqlite3.exe for windows.
In my tests I found out, that it is not possible to use table-valued pragma
functions on attached databases e.g. to read user_version or schema_version.

So the Query:

SELECT a.user_version as 'main_version', b.user_version as
'attached_version' FROM
  main.pragma_user_version() as 'a',
   test_db.pragma_user_version() as 'b';

Should display the user_version of main-database and attached database as
wel, but both values display always the version of the main database.

I tested this with 2 szenarios, first with attaching a in-memory database
and second with attaching a physical database.
Results are identical for both.




Infos about test envoirement

OS:Windows XP Professional Service Pack 3 (32-Bit)
Sqlite-Version:3.27.1 2019-02-08 13:17:39
0eca3dd3d38b31c92b49ca2d311128b74584714d9e7de895b1a6286ef959a1dd
Test-Command:sqlite3 -echo bug_test.db < pragma_bug-testcase.sql



To make it easy to reconstruct this Bug I added example outputs and my
inputs-commands (as sql file) for sqlite3.exe.

Simply run
"sqlite3 -echo bug_test.db < pragma_bug-testcase.sql"

on any test-databse and compare results to my "test-ouputs" for szenario 1
and szenario 2.
-- switch display mode
.mode column
.header on


-- show my sqlite-version
SELECT sqlite_version();
sqlite_version()

3.27.1  


-- TEST SZENARIO 1: attach an in-memory test-database
ATTACH DATABASE ':memory:' AS test_db;


-- TEST SZENARIO 2: attach another psysical database



-- lets check if attached database is here
SELECT * FROM pragma_database_list();
seq namefile

--  --  

0   mainJ:\bug_test.db
2   test_db 





SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
0 0 



-- Change user Version of main-db and attached db
PRAGMAmain.user_version=123;
PRAGMA test_db.user_version=456;





-- WRONG-BEHAVOIR: both cols show the values for main-db
-- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values 
from attached database 'test_db' like 'PRAGMA test_db.user_version;'

SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
123   123 



-- This shows correct values
PRAGMA main.user_version;
user_version

123 
PRAGMA test_db.user_version;
user_version

456 



-- Show that database-name prefix is completly ignored:
-- Expected behavoir: 'Error: unknown database not_existing_database' - because 
no database name 'not_existing_database' exists
-- Actual behavoir: no error, it shows the value for pragma user_version from 
main-db

SELECT * FROM  not_existing_database.pragma_user_version();
user_version

123 



.quit
-- switch display mode
.mode column
.header on


-- show my sqlite-version
SELECT sqlite_version();
sqlite_version()

3.27.1  


-- TEST SZENARIO 1: attach an in-memory test-database
-- ATTACH DATABASE ':memory:' AS test_db;


-- TEST SZENARIO 2: attach another psysical database
ATTACH DATABASE './physical.db' AS test_db;



-- lets check if attached database is here
SELECT * FROM pragma_database_list();
seq namefile

--  --  

0   mainJ:\bug_test.db
2   test_db J:\physical.db  




SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
0 0 



-- Change user Version of main-db and attached db
PRAGMAmain.user_version=123;
PRAGMA test_db.user_version=456;





-- WRONG-BEHAVOIR: both cols show the values for main-db
-- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values 
from attached database 'test_db' like 'PRAGMA test_db.user_version;'

SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
123   123  

Re: [sqlite] sqlite 3.37.1: void function returns value

[Peter da Silva]

I am pretty sure that the code is not legal C because it's using the return
value of a void function, as well as returning a value from a void
function. Compilers that "do what I mean" and accept it are in error. It's
certainly possible that some obscure clause in some C standard blesses it
but I can't imagine why they would.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying hierarchical structure

[Peter Johnson]

some relevant links:

http://dwhoman.com/blog/sql-transitive-closure.html

http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/

On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert  Working on an Android app and part of that is storing SQL in a virtual
> folder system in SQLite. For this I want to use a so-called closure table
> as explained nicely here:
>
> http://technobytz.com/closure_table_store_hierarchical_data.html
>
> I have a table holder the folder details:
>
> ID PARENT_ID Folder
>
> -
> 1  0   Folder1
> 2  1   Folder2
> 3  1   Folder3
> 4  1   Folder4
> 5  2   Folder5
> 6  2   Folder6
>
>
> And then the closure table:
>
> PARENT_ID CHILD_ID DEPTH
>
> ---
> 1   10
> 2   20
> 3   30
> 4   40
> 5   50
> 6   60
> 1   21
> 1   31
> 1   41
> 2   51
> 1   52
> 2   61
> 1   62
>
> What should the SQL be to display the folders like this:
>
> FolderPARENT_ID
> Folder1   0
> Folder2   1
> Folder5   2
> Folder6   2
> Folder3   1
> Folder4   1
>
>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Peter da Silva]

On Tue, Jan 29, 2019, 7:46 PM Igor Korot  You can install mySQL/MariaDB for free and use it for your needs.
> I believe that if you pay to Oracle/MariaDB Foundation, it will be
> just for support. (I may be wrong though).
>

Or better, PostgreSQL. We have a system that uses SQLite as an optional
cache for PostgreSQL and takes advantage of the similarity of their SQL
variants to get the best of both worlds. I modified the Pgtcl library to
even get the variable binding for PostgreSQL to match SQLite syntax and
semantics.

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

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Peter da Silva]

The sensible permissions in this case would probably be rw-rw-r-- with the
same group as the service and owned by the service group. That is how group
permissions were designed to work.


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

Re: [sqlite] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Peter da Silva]

Sounds like something is using fork when it should be using vfork?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Peter da Silva]

That wasn't "endian" argument, this is an arithmetic operation question,
avoiding extra operations in the common case of small (mag < 2^56)
operands. Since I posted that I've figured out some other optimizations
that work better the way they laid it out, and it makes more sense now.



On Thu, Jan 3, 2019 at 5:54 AM Gary R. Schmidt  wrote:

> On 03/01/2019 22:22, Peter da Silva wrote:
> > Why is the exponent in the low bits, since it forces unnecessary shifts
> for
> > integer operations?
> >
> That's easy, because the high bits are closer to the barrel shifter, so
> it takes less time for the electron to get there![1][2]
>
> Cheers,
> GaryB-)
>
> 1 - Oh ghod, bit-order arguments, worse than the shell wars and the
> editor wars combined!!!
>
> 2 - Sarcasm intended, for those who may not be certain.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Peter da Silva]

Why is the exponent in the low bits, since it forces unnecessary shifts for
integer operations?

On Thu., 3 Jan. 2019, 03:34 Pavlos Christoforou  Happy new year all,
>
> Have not followed the full thread, in case it has not been mentioned
> already:
>
> http://dec64.com/
>
>
> Cheers
>
>
> On Tue, 18 Dec 2018 at 02:42, Keith Medcalf  wrote:
>
> > >This thread is getting out of hand. Firstly there is no such binary
> > >representation ( in this universe ) for a trivial decimal number such
> > >as one tenth ( 0.10 ) and really folks should refer to the text book
> > >recently published ( 2nd Edition actually ) where all this is covered
> > >:
> > > Handbook of Floating-Point Arithmetic
> > > Authors: Muller, J.-M., Brunie, N., de Dinechin, F.,
> > >  Jeannerod, C.-P., Joldes, M., Lefèvre, V.,
> > >  Melquiond, G., Revol, N., Torres, S.
> > >
> > > This handbook is a definitive guide to the effective use of
> > > modern floating-point arithmetic, which has considerably
> > > evolved, from the frequently inconsistent floating-point number
> > > systems of early computing to the recent IEEE 754-2008 standard.
> >
> >
> >
> https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdf
> >
> > While it is true there is no exact representation of 1/10th in binary
> > floating point, at double precision the epsilon is 1.3877787807814457e-17
> > which means that for all intents and purposes 1/10th is exact to 16.9
> > decimal places.  Which is pretty damn good for a format that is only
> > purported to be accurate to 15 decimal digits.
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Pavlos Christoforou
>
> Point Nine Limited
> Mobile: +357 99 160960
>
> pavlos.christofo...@p9ft.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] i Know i should use 'AS', but ....

[Peter Johnson]

The headers are present in all three queries you pasted.

The first result shows two rows, the top row is the header.

The other two results show 4 rows each, the top row of each is the header
row.

-P

On Tue, 25 Dec 2018, 3:42 AM Luuk  sqlite> .version
> SQLite 3.26.0
>
> sqlite> .headers on
>
> sqlite> select 1 as X,date();
> X|date()
> 1|2018-12-24
> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
> union all select 2 union all select 3);
> x|row_number() over (order by 1 desc)
> 3|1
> 2|2
> 1|3
>
> Why are the headers missing in above query?
>
>
>
> sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1
> as x union all select 2 union all select 3);
> x|Y
> 3|1
> 2|2
> 1|3
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

Programs that were written defensively from the start are more likely to
survive attack. Programs that have successfully been exposed to attack can
be treated as more secure. Sqlite is explicitly not designed to be secure
against untrusted input or corrupt .

This is OK. It's not a program that would be expected to be designed to be
secure against these kinds of attacks. It just means that you don't expose
it to untrusted input, like it's a shell or compiler or something.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

Fuzz testing would be extremely unlikely to have caught the original
attack. Nor would fuzz testing on input be likely to hit all corrupt
database attacks. Fuzz testing using fuzzed corrupted databases might.

On Thu., 20 Dec. 2018, 11:26 Jens Alfke 
>
> > On Dec 19, 2018, at 4:03 PM, Peter da Silva  wrote:
> >
> > sqlite is not immune to wandering through bad pointers, because code
> > coverage tests don't test for malicious data..
>
> Fuzz testing does, though [implicitly].
>
> https://www.sqlite.org/testing.html#sql_fuzz_using_the_american_fuzzy_lop_fuzzer
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

sqlite is not immune to wandering through bad pointers, because code
coverage tests don't test for malicious data... I found a null pointer
crash in sqlite earlier this year. I could see Mallory crafting a database
that had carefully corrupted structures in it that smashed the stack.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

On Tue, Dec 18, 2018 at 3:49 PM Nathan Green  wrote:

> Except the problem isn't just in Chrome. Apparently, any system that allows
> SQL injection is vulnerable.
>

That's kind of a tautology isn't it? Isn't there some kind of Godwin's Law
variant for XKCD 327?

I notice that the 12 points on https://www.sqlite.org/appfileformat.html
don't include "secure".

I mean, sure, we used to distribute software on Usenet as shell scripts
(look up "shar archive") but it's not 1984 any more.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

Javascript was designed from the start to safely execute malicious code.
That doesn't mean it is safe, it just means it might be. There have been
all kinds of javascript-based exploits, after all.

But an interpreter that was not originally designed to be safe in the face
of malicious code? I can't understand the confusion in the mind that would
lead one to expect miracles of it. This is not a criticism of sqlite, by
any means. Safe languages are rare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

I have to say I'm pretty boggled that Chrome allows hostile users to feed
code directly into an SQL interpreter that wasn't written from the ground
up to be secure. Secure interpreters are *hard* even when you're designing
them from scratch (see also, the whole history of web-based
vulnerabilities). That seems to be dancing with the screwup fairy to me.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] Non-EXCLUSIVE modes in dot-file locking strategy

[Peter da Silva]

The only way I can think of to have a large counter in a lock is to lock
access to a file containing a counter and I don't know if not-a-file-system
semantics support that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Peter da Silva]

Apple uses Sqlite in a number of applications, including Apple Mail, so
they have to have some kind of accommodation for saving sqlite databases.

The Time Machine patent does not describe using file system snapshots:


*"An algorithm or other monitoring can be used to detect changes that occur
during the backup operation in order to maintain consistency between
related data in the backup. The back up can be performed again for related
data that was modified during prior backup operation. *

*"In general, in one aspect, a method is provided. A backup operation of
data including a plurality of related items is initiated. Modifications to
one or more items of the plurality of related items are monitored for
during the backup operation. The backup operation is completed. If a
modification occurred to one or more items, a second backup operation is
performed for the modified items."*

This does not seem to authoritatively state that multiple files will be
backed up consistently.

On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf  wrote:

>
> I know nothing about "Time Machine", but does it copy the entire
> filesystem in (at least) "crash consistent" state?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Daniel Alm
> >Sent: Tuesday, 11 December, 2018 05:02
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Mac: Users receive "database disk image is
> >malformed" errors after restoring database from Time Machine backup
> >
> >Hi,
> >
> >For the past half year we’ve been receiving reports from users who
> >had restored their SQLite-based databases from a Time Machine backup.
> >Afterwards, they would receive "database disk image is malformed”
> >errors. The app also backs up the user’s data “manually” to a ZIP
> >file every week; those backups seem to be working fine. We also
> >haven’t received reports from other backup tools causing issues. I
> >have also suspected a bug in Time Machine, but it is striking that
> >the issues did seem to start occurring after an update to the app
> >(luckily, in fact, with the same update that also introduced the
> >“manual” backups).
> >
> >Changes that we made to our setup in the update that coincided with
> >the errors occurring:
> >- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1
> >in another update; no improvement).
> >- Used memory mapping for read accesses via “PRAGMA mmap_size =
> >1073741824;” (we have since reverted to “PRAGMA mmap_size = 0;” after
> >reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-
> >and-PRAGMA-fullfsync-on-macOS-td95366.html
> > >PRAGMA-fullfsync-on-macOS-td95366.html>; no improvement).
> >- Using a secondary database via [ATTACH
> >DATABASE](https://www.sqlite.org/lang_attach.html
> >) (although this also seems
> >to occur for users without such a database).
> >
> >At this point, I am at a loss, especially given that SQLite should be
> >fairly robust against database corruption. While our app is running
> >in the background all the time, it is not very write-heavy (~ one
> >transaction per minute taking just a few milliseconds). Also, the app
> >had been running fine before the update for a long time without any
> >reports of this issue. I might be doing something wrong or have
> >changed anything else, but I don’t know what; if you have any ideas,
> >let me know.
> >
> >Any suggestions on what could be the culprit or what else I could try
> >besides downgrading all the way to SQLite 3.21 would be appreciated.
> >
> >Thanks,
> >Daniel Alm
> >
> >P.S.: Our database currently uses the following PRAGMAs:
> >
> >PRAGMA mmap_size = 0;
> >PRAGMA page_size = 4096;
> >PRAGMA cache_size = -10240;
> >PRAGMA foreign_keys = ON;
> >PRAGMA journal_size_limit = 8388608;
> >PRAGMA checkpoint_fullfsync = 1;
> >PRAGMA wal_autocheckpoint = 2048;
> >PRAGMA journal_mode = WAL;
> >
> >Happy to provide any more details as needed.
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Peter da Silva]

On 2018-10-19, at 09:02, Winfried  wrote:
> Before I build a GUI, is there a Windows SQLite database manager that can do
> this?
> 
> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none seems
> to support this feature.

SQLite's closest thing to a native scripting language is Tcl, with a Tk 
graphical toolkit. If you write your app in Tcl/Tk it will run on Windows, Mac, 
and UNIX.

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

Re: [sqlite] forming sqlite3_statements using stored sql

[Peter da Silva]

I don't care for the sql variable syntax they're using, since it appears to
conflict with the native Tcl bindings for sqlite3.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] curious discovery about geopoly module

[Peter da Silva]

On 2018-11-01, at 02:51, Graham Hardman  wrote:
> I am pleased about this, although still puzzled by the fact that the windows 
> LoadLibrary call returned a non null pointer and windows help file implies 
> that it is ok for multiple copies of same name libraries to be loaded 
> together.

This has been a problem in Windows for years. Microsoft themselves used to 
include version numbers in library base names (eg, MSVCRT40.DLL) and other 
vendors use similar tricks to keep "DLL hell" at bay. I suspect that your 
solution of using a different library name is actually best practice. :(
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying row count

[Peter da Silva]

If you're going to change the sqlite3 command shell, add a ".count" and
maybe ".changes" display option.

On Wed, Oct 31, 2018 at 11:23 AM Don V Nielsen 
wrote:

> I really enjoy using JetBrains DataGrip. It connects to everything and has
> great intellisense, find and replace tools, sql templates, all the goodies
> a big IDE brings to the table.
>
> On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne 
> wrote:
>
> > On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch 
> > wrote:
> >
> > > David Fletcher wrote:> Hi all,
> > > > Is there a mode in the sqlite shell, or some fancy extension, that
> will
> > > display a row
> > > > number when outputting results?
> > >
> > > No.  You'd have to modify the shell, or add the row_number() window
> > > function to the query.
> >
> >
> > Unless you need to use bleeding edge new features, use some GUI SQLite
> tool
> > instead.
> >
> > I use SQliteSpy myself for that. Gives me the time to process the query,
> > how many rows,
> > how many VM steps, Sort steps, that sort of thing. And shows me the
> result
> > in tabular fashion,
> > with color-coding based on value types (useful for a ducktyping DB like
> > SQLite).
> >
> > Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
> > once in a while.
> >
> > I typically keep several queries I'm playing with, and CTRL-F9 the
> selected
> > one to run it.
> >
> > Don't get me wrong, the CLI shell is great, but for many rows, or wide
> > rows,
> > or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
> > --DD
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] union + window functions = sqlite crash (version 3.25.2)

[Peter Ďurica]

Table with sample data:
*create table t(a int, b int);*
*insert into t values(1,11);*
*insert into t values(2,12);*

now query using any window function (row_number, rank, ) after UNION or
UNION ALL will cause sqlite.exe crash (no regular error)

for example:
*select a, rank() over(order by b) from t *
*union all *
*select a, rank() over(order by b desc) from t;*
WILL CRASH

but single statement is fine

*select a, rank() over(order by b desc) from t;  *

and also window function just before union is fine
*select a, rank() over(order by b) from t *
*union all *
*select a, b from t;*


when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
my c# app, I got error below
Exception thrown at 0x7FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
0xC005: Access violation reading location 0x0008.

I believe it's a bug in core sqlite, which should be fixed

Please let me know how it goes

Thanks in advacne,
Peter Ďurica
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Peter da Silva]

You would have had more luck with "Be excellent to each other".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] segmentation fault in sqlite api call

[Peter da Silva]

You're passing a char * to a routine that expects a char **, and then
immediately trying to indirect through it, which means it's taking the
text, treating it as a pointer, and passing the random data it's pointing
to as a string to sqlite.

On Sun., 21 Oct. 2018, 11:55 Ratheendran R,  wrote:

> Hi,
>
> I am a embedded engineer and new to sqlite,we want to use sqlite for our
> local storage instead of file i/o.
>
> I have created a table with key and value records of char type,now if I try
> store a value with string length more than 50 char I get segmentation
> fault,please see the code below and let me know if I can improve it.
>
> sqlite3 *mod_init() {
>
> /* Open database */
> //rc = sqlite3_open("test.db", >db);
> lastError = sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL);
> if( lastError ) {
> fprintf(stderr, "Can't open database: %s\n",
> sqlite3_errmsg(dbObj->db));
> free(query);
> return(0);
> } else {
> fprintf(stdout, "Opened database successfully\n");
> }
> memset(query,0,200);
> strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db("  \
> "key   TEXT PRIMARY KEY   NOT NULL," \
> "valueVARCHAR(100));");
>
> /* Execute SQL statement */
> lastError = sqlite3_exec(db, query, 0, 0, );
>
>
> if( lastError != SQLITE_OK ){
> fprintf(stderr, "SQL error: %s\n", zErrMsg);
> sqlite3_free(zErrMsg);
> } else {
> fprintf(stdout, "Table created successfully\n");
> }
> return db;
> }
>
>
>
> int mydef_set(cf_db_t *dbObj,char *key, char **value)
> {
> char *zErrMsg = 0;
> int rc;
> sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values
> ('%s', '%s');",key,*value);
> /* Execute SQL statement */
> lastError = sqlite3_exec(db, query, 0, 0, );
> if( lastError != SQLITE_OK ) {
>   fprintf(stderr, "SQL error: %s\n", zErrMsg);
>   sqlite3_free(zErrMsg);
>} else {
>   fprintf(stdout, "Update done successfully\n");
>}
> return lastError;
> }
>
>
> int main()
> {
> sqlite3 *db;
> db=mod_init();
> mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn
> bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg");
> }
>
> Thanks,
> Ratheendran
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Peter da Silva]

Don't put raw user input where code is expected. Match strongs are code.
You need to encapsulate (eg escape) or filter (delete bad characters) match
strings outside sqlite.

On Thu., 18 Oct. 2018, 13:50 Maziar Parsijani, 
wrote:

> It will crash or exit the program.
>
> On Thu, Oct 18, 2018 at 2:27 PM Jens Alfke  wrote:
>
> >
> >
> > > On Oct 18, 2018, at 11:17 AM, Maziar Parsijani <
> > maziar.parsij...@gmail.com> wrote:
> > >
> > > I just search for words an alphabets in different languages with python
> > and
> > > my database is sqlite but I need to do something to not getting error
> > when
> > > user input a wrong character like the ones that I told before.I can ban
> > > user to not input these characters but I am curious to find a way on
> > sqlite.
> >
> > What’s wrong with getting an error? If the error code or message is
> > specific enough, you can detect it and tell the user they’ve entered
> > invalid characters.
> >
> > —Jens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_complete and comments

[Peter da Silva]

Comments have no effect on the completeness of a statement, you will have
to look for leading '--' explicitly.

On Sun., 14 Oct. 2018, 13:38 Roger Binns,  wrote:

> I use sqlite3_complete in my shell in order to determine when a complete
> statement has been input and can be run.  (Otherwise a continuation
> "sqlite> " prompt is shown.)
>
> If the line entered is:
>
>-- hello
>
> Then the sqlite shell does not issue a continuation and "executes" the
> text.  However sqlite3_complete does not say that line is complete so
> the command line shell has extra logic to figure this out.  What is the
> right way of considering line comment complete as the SQLite shell?
>
> These lines get True from sqlite3_complete:
>
>   select 3; --
>   select 3 /* */ ;
>
> And these get False:
>
>   select 3
>   --
>   -- ;
>
>
>
> Roger
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Peter da Silva]

There's a couple of Tcl/Tk SQLITE database managers that could be more easily 
turned into something like the Access GUI than starting from scratch. And they 
wouldn't be limited to Windows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A SQL statement reformatter

[Peter da Silva]

Oh, nice!

On Fri, Sep 28, 2018 at 9:50 AM Chris Brody  wrote:

> I found https://github.com/mjibson/sqlfmt through their about page. No
> license though, just raised
> https://github.com/mjibson/sqlfmt/issues/33.
>
> GitHub fork-me ribbon would also be nice I think.
>
> Someone should have make this tool 20-30 years ago!
> On Fri, Sep 28, 2018 at 10:44 AM Peter da Silva  wrote:
> >
> > Pity they aren't making the code available. Not sure I want to send
> company
> > SQL to a random site.
> >
> > On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen 
> wrote:
> >
> > > Slick. That line width slider feature is something we don't get using
> Poor
> > > Man's Sql Formatter.
> > >
> > > Thanks!
> > >
> > > On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin 
> wrote:
> > >
> > > > For those times when you have to understand a poorly-formatted SQL
> > > > statement:
> > > >
> > > > <https://sqlfum.pt>
> > > >
> > > > I seem to prefer 'full' mode.
> > > >
> > > > Simon.
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A SQL statement reformatter

[Peter da Silva]

Pity they aren't making the code available. Not sure I want to send company
SQL to a random site.

On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen  wrote:

> Slick. That line width slider feature is something we don't get using Poor
> Man's Sql Formatter.
>
> Thanks!
>
> On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:
>
> > For those times when you have to understand a poorly-formatted SQL
> > statement:
> >
> > 
> >
> > I seem to prefer 'full' mode.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Core dump in sqlite 3.23 through 3.24 (at least)

[Peter Da Silva]

In tclsqlite.c, function DbMain()... somewhere between 3.19 and 3.23 there was 
a re-write of the argument parsing code for the sqlite command, and following 
code was removed and not completely replaced with a new equivalent:


  if( objc<3 || (objc&1)!=1 ){

Tcl_WrongNumArgs(interp, 1, objv,

  "HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"

  " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"

#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)

  " ?-key CODECKEY?"

#endif

);

return TCL_ERROR;

  }

The result is that if you use the sqlite command with no arguments it core 
dumps on the code following, where it tries to use objv[1], instead of 
returning an error.


$ tclsh

% package require sqlite3

3.24.0

% sqlite3

Segmentation fault (core dumped)

It looks like there is an attempt to handle missing arguments at the top of the 
“for(i=2; ihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Docs typo JSON1 @ 4.13

[Peter Johnson]

Hi,

The JSON1 docs at https://www.sqlite.org/json1.html have a minor typo:

Section 4.13. The json_each() and json_tree() table-valued functions

atom ANY, -- value for primitive types, null for array & object
> id INTEGER -- integer ID for this element
> parent INTEGER, -- integer ID for the parent of this element


The "id INTEGER" column definition is missing a trailing comma.

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

Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

This is also a problem on FreeBSD, I just haven’t noticed it because the ports 
guys are on top of keeping their sqlite3 build up to date, and they do the 
smart thing and build --with-system-sqlite.

If you’re building sqlite-autoconf-3xx/tea, then why would the “system 
sqlite” ever be anything but current? Why would you install the Tcl extension 
but not the library itself?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

> You may be building on a system where they’ve still got SQLite 3.7.mumble 
> installed

In this case there are not two implementations present. Both libsqlite3.24.0.so 
and libsqlite3.so.0.8.6 were built from sqlite-autoconf-324. This is the 
only copy of libsqlite3 anywhere on the system.

> You may have noticed the continual pressure on this mailing list to be using 
> recent versions of SQLite.  One way to ensure that is to always statically 
> link.

That’s not an option, Tcl extensions are inherently dynamically loaded. If you 
use two Tcl extensions that call sqlite you need to ensure that both 
dynamically link to the same copy of the library. Not just the same version, 
the same copy, because they need to be operating on the same memory allocation 
arena and other globals.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

So I was doing some work with another Tcl extension (Pgtcl) that was linking to 
libsqlite3. It worked fine on FreeBSD but on Linux (Ubuntu 4.13.0-27 generic) 
it crashed. We traced that down to it having two copies of libsqlite linked in, 
the one in the sqlite3 extension (libsqlite3.24.0.so) and the system sqlite 
that pgtcl found in /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6...

Forcing it to make libsqlite3.24.0.so just the Tcl glue to libsqlite3.so.0.8.6 
with ./configure --with-system-sqlite did the trick, but I’m just wondering why 
that wouldn’t be the normal way to do things.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_prepare_v2 with an empty statement.

[Peter Da Silva]

In the documentation for sqlite3_prepare_v2, it says:

*ppStmt is left pointing to a compiled prepared 
statement that can be executed using 
sqlite3_step(). If there is an error, 
*ppStmt is set to NULL. If the input text contains no SQL (if the input is an 
empty string or a comment) then *ppStmt is set to NULL. The calling procedure 
is responsible for deleting the compiled SQL statement using 
sqlite3_finalize() after it has 
finished with it. ppStmt may not be NULL.

Is an empty SQL statement always an error, i.e. is it possible for 
sqlite3_prepare_v2 to return SQLITE_OK while leaving *ppStmt NULL? I have been 
assuming not, but I’m tracking down a hard-to-find bug and double-checking all 
my assumptions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CASE and NULL

[Peter Johnson]

Can't you just use IFNULL to assign a default value?

CASE IFNULL( x, -999 )
  WHEN 1 THEN 11
  WHEN 2 THEN 22
  WHEN 3 THEN 33
  WHEN 4 THEN 44
  WHEN -999 THEN 55
  ELSE 66
END

On 5 July 2018 at 11:35, R Smith  wrote:

> On 2018/07/05 8:44 AM, Simon Slavin wrote:
>
>> On 5 Jul 2018, at 7:30am, Clemens Ladisch  wrote:
>>
>> The expression "x = x" will fail for NULL, but succeed for everything
>>> else.  So you can use that to implement a "not-NULL ELSE"
>>>
>> Wow.  That has to be the most counter-intuitive feature of SQLite.  I
>> understand why it works, but I still don't like it.  Thanks for posting it.
>>
>
> That's how it works everywhere, not just in SQLite. NULL has special
> handling in that any expression or function that gets touched by a NULL
> value immediately returns NULL (except for some aggregates that sometimes
> have NULL values among their input populations, which they simply ignore).
>
> What the OP essentially wants is to test for NULL values, which is
> possible using "IS" but not in an equality test (since the expression [ a =
> x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL, as
> they should), so it cannot use equality testing in the usual way a CASE
> executes.
>
> My typical way to do this is:
>
> CASE
> WHEN x IS NULL THEN ...
> WHEN x < 1 THEN ...
> WHEN x < 3 THEN ...
> WHEN x < 5 THEN ...
> ELSE ...
> END;
>
> But I feel like the equality check option can easily be enhanced in SQLite
> to have this work:
>
> CASE x
> WHEN IS NULL THEN 
> WHEN  1 THEN ...
> WHEN  3 THEN ...
> WHEN  5 THEN ...
> END;
>
> but then it's so little difference from the example above it that I have
> never yearned for it - in fact, I never use this latter version due to its
> shortcomings in testing anything that is not an equality check (but since
> my preference is no measure of its utility, perhaps it's worth considering).
>
>
> Cheers,
> Ryan
>
> PS: Here is a version of the 1st example working:
>
> WITH C(x) AS (
>  SELECT NULL
>  UNION ALL
>  SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL
> )
> SELECT x, CASE
> WHEN x IS NULL THEN 'None'
> WHEN x < 1 THEN 'Zero'
> WHEN x < 3 THEN 'Small'
> WHEN x < 6 THEN 'Medium'
> ELSE 'Large'
> END AS size
>   FROM C
> ;
>
>
>   -- x| size
>   --  | --
>   -- NULL | None
>   -- 1| Small
>   -- 2| Small
>   -- 3| Medium
>   -- 4| Medium
>   -- 5| Medium
>   -- 6| Large
>   -- 7| Large
>   -- 8| Large
>   -- 9| Large
>   -- 10   | Large
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 content table

[Peter Johnson]

Wording looks great, thanks Dan.

FWIW I believe the same docs issue applies to the FTS5 module.

Docs are hard, I often find myself re-reading paragraphs and then figuring
out that I'm just a big dummy and didn't 'get it' the first time.

The FTS docs are actually pretty great, they introduce some new concepts
that are unique to that module and so those nonconanical patterns cause
people to make their own assumptions regarding how they are implemented,
which was the case here.

Thanks again.
- Peter

On Mon, 2 Jul 2018, 10:02 AM Dan Kennedy,  wrote:

> On 07/01/2018 05:20 PM, Peter Johnson wrote:
> > I recall you must still enumerate the column names of the fields you wish
> > to link to the contentless table when creating the virtual table.
> >
> > If I remember correctly, failing to do so will result in 'no such column'
> > errors.
> >
> > Last time I looked at the docs I got caught with the same gotcha, the
> docs
> > show an example query of how the contentless content is loaded but
> > (possibly?) don't make it 100% clear whether you need to specify those
> > columns in the vtable definition in order to allow them to be returned in
> > the result rows.
>
> Fair enough. Added a sentence here to try to make things clearer:
>
>https://sqlite.org/docsrc/info/ea0f0b4c4af09bf9
>
> Dan.
>
>
>
> >
> > -P
> >
> > On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy,  wrote:
> >
> >> On 06/30/2018 10:24 PM, Dudu Markovitz wrote:
> >>> Hi
> >>>
> >>> according to the documentation -
> >>> 6.2.2. External Content FTS4 Tables
> >>>
> >>> An "external content" FTS4 table is similar to a contentless table,
> >> except
> >>> that if evaluation of a query requires the value of a column other than
> >>> docid, FTS4 attempts to retrieve that value from a table (or view, or
> >>> virtual table) nominated by the user (hereafter referred to as the
> >> "content
> >>> table").
> >>>
> >>> However I see no example for a query on the fts table that implicitly
> >>> retrieves values from the content table, nor could I find any in other
> >>> place.
> >>> All my attempts to write such a query ended up with "Error: no such
> >>> column".
> >>> Am I missing something or is it a problem in the documentation?
> >> I don't think I understand the question. What do you want it to do?
> >>
> >> The SELECT query in the second block of code in that section:
> >>
> >> https://sqlite.org/fts3.html#_external_content_fts4_tables_
> >>
> >> is:
> >>
> >> SELECT * FROM t3 WHERE t3 MATCH 'k'
> >>
> >> t3 is the FTS4 table. The query does a lookup on the FTS4 index to find
> >> matches for token 'k', then automatically retrieves values from the
> >> content table (t2) for each matched row. The first instance of the
> >> SELECT in the block of code shows everything working as expected, the
> >> subsequent two show the counter-intuitive effects of allowing the FTS
> >> index to get out of sync with the content table.
> >>
> >> Dan.
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 content table

[Peter Johnson]

I recall you must still enumerate the column names of the fields you wish
to link to the contentless table when creating the virtual table.

If I remember correctly, failing to do so will result in 'no such column'
errors.

Last time I looked at the docs I got caught with the same gotcha, the docs
show an example query of how the contentless content is loaded but
(possibly?) don't make it 100% clear whether you need to specify those
columns in the vtable definition in order to allow them to be returned in
the result rows.

-P

On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy,  wrote:

> On 06/30/2018 10:24 PM, Dudu Markovitz wrote:
> > Hi
> >
> > according to the documentation -
> > 6.2.2. External Content FTS4 Tables
> >
> > An "external content" FTS4 table is similar to a contentless table,
> except
> > that if evaluation of a query requires the value of a column other than
> > docid, FTS4 attempts to retrieve that value from a table (or view, or
> > virtual table) nominated by the user (hereafter referred to as the
> "content
> > table").
> >
> > However I see no example for a query on the fts table that implicitly
> > retrieves values from the content table, nor could I find any in other
> > place.
> > All my attempts to write such a query ended up with "Error: no such
> > column".
> > Am I missing something or is it a problem in the documentation?
>
> I don't think I understand the question. What do you want it to do?
>
> The SELECT query in the second block of code in that section:
>
>https://sqlite.org/fts3.html#_external_content_fts4_tables_
>
> is:
>
>SELECT * FROM t3 WHERE t3 MATCH 'k'
>
> t3 is the FTS4 table. The query does a lookup on the FTS4 index to find
> matches for token 'k', then automatically retrieves values from the
> content table (t2) for each matched row. The first instance of the
> SELECT in the block of code shows everything working as expected, the
> subsequent two show the counter-intuitive effects of allowing the FTS
> index to get out of sync with the content table.
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Peter Johnson]

Is it possible to create a trigger on sqlite_master which calls a
user-defined function AFTER INSERT?

That would avoid having to poll, but it'd still allow the application to be
notified when the schema changed.

On 19 June 2018 at 20:56, Richard Hipp  wrote:

> On 6/19/18, Igor Korot  wrote:
> > Hi, Wout,
> >
> > On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens 
> > wrote:
> >> you can query the table with
> >> https://www.sqlite.org/pragma.html#pragma_table_info
> >
> > Let me give you a scenario:
> >
> > 1. My application connects to the database and performs some
> > operations (using C API).
> > 2. During the application run, someone started sqlite3, connects to
> > the database and creates a
> > brand new table.
> > 3. My application will need to pick up the newly created table and
> continue.
> >
> > Is it easily possible?
> >
> > There is a sqlite3_*_hook() family of functions, but it looks like
> > they won't help with sqlite_master.
> >
> > Is there a different way?
>
> Poll the PRAGMA schema_version value and watch for changes.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with node-sqlite3

[Peter Johnson]

Hi Omer,

Unfortunately what you are trying to do it not possible.

You are trying to run the npm package `node-sqlite3` in an Angular project.

Angular is a front-end, browser-based framework.

The npm package manager contains javascript modules which can sometimes be
used either in the browser or on the command line via nodejs.

In this case, the node-sqlite3 will only work when installed and run in
nodejs on the command line, it will not work in the browser.

The messages relating to the lack of crypto, http, and https are because
those modules are part of nodejs and are not available in the browser.

The dependency on make and python are due to node-sqlite3 shipping a
version of sqlite with the module, it requires those tools
to compile sqlite.

I hope that helps, this is, unfortunately, the wrong place to report this
bug.

Please open an issue here for more help:
https://github.com/mapbox/node-sqlite3

-P

On 13 June 2018 at 12:50, Space Pixel  wrote:

> Hi Ryan,
>
> I can see you didn't quite understand me. The project I am attempting to
> install sqlite3 on is an Angular 6 project. Now, in the package sqlite3
> there is a CS file that spits out a warning "you need an appropriate loader
> to handle this file type". Also, some of the packages sqlite3 requires are
> very ancient and deprecated: crypto, aws-sdk, http, https, and some more.
> Now, about getting the latest version through the website- again, the
> "sqlite3" that I'm talking about is a NPM package, and there is nothing to
> do with finding the latest version: I already have it. Plus, when I tried
> to install it without any Python or build tools, it fell back to the build
> and then the build immediately FAILED. After I installed Windows Build
> Tools and Python (and added it to PATH), I got it to build successfully,
> but then I face the problems that I mentioned in the first mail.
>
> Hope you understand now,
>
> Omer Shamai.
>
> On Wed, Jun 13, 2018, 12:52 PM R Smith  wrote:
>
> > On 2018/06/13 10:26 AM, Space Pixel wrote:
> > > Hello sqlite community,
> > >
> > > I am having serious trouble with the Node.js module "sqlite3". This
> > package
> > > is supposed to connect JavaScript or TypeScript code with a sqlite3
> > > database (file or online database). When I try to use the sqlite file
> > (the
> > > command I am using is "const db = new sqlite3.Database("file name
> > > here");"), it spits out SO many errors regarding packages that were
> > > declared as deprecated and out of date so long ago. I'm gonna give a
> log
> > > below. Notice the warnings: A .cs file spits out a warning, in which it
> > > says: "You need an appropriate loader to handle this file". I tried to
> > find
> > > a solution for a whole month, to no avail. Please help at once.
> >
> > Hi Omer,
> >
> > The reason your messages are not being answered quick and precisely is
> > that this is really not an SQLite issue, it's a CS problem and people on
> > a CS forum are much more likely able to help out.  It's also possible
> > that some CS user here might read it and offer assistance, but so far it
> > didn't attract much attention from such Samaritans.
> >
> > The SQLite devs do not make deprecated systems, the stuff can be
> > downloaded from the SQLite site is all up-to-date and working, so that
> > means that either CS itself or the wrapper or module you use in CS, to
> > use SQLite DB capabilities, is out-of-date or deprecated. We can show
> > you the newest and updated sources for SQLite itself, but we cannot
> > magic it into your CS project - hence me saying you are far more likely
> > to get help from a CS forum since someone there is bound to have
> > discovered and (hopefully) already solved this same problem.
> >
> > Once you get the SQLite to be understood by your CS and start using it
> > and then have any problem with how it works, understands SQL or handles
> > files, then we can (and would love to) fully assist.
> >
> > Good luck,
> > Ryan
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Peter Nacken]

Dear all,

Thanks for your help. It works !

Peter

> Am 11.06.2018 um 11:14 schrieb Tim Streater :
> 
>> On 11 Jun 2018, at 09:07, Peter Nacken  wrote:
>> 
>> I try to insert email addresses into a table and get an error with addresses
>> they have a single quotation mark ( na'm...@domain.ltd ). 
>> 
>> Sorry I'm facing this problem for weeks, I can't find a solution.
>> 
>> Is there a known workaround for it ?
> 
> See:
> 
> <https://www.sqlite.org/faq.html>
> 
> and look at question 14 and its answer.
> 
> 
> 
> -- 
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Insert with an '

[Peter Nacken]

Sorry I forgot I‘m using C#

> Am 11.06.2018 um 10:07 schrieb Peter Nacken :
> 
> Hi,
> 
> I'm new in SQLite. 
> 
> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ). 
> 
> Sorry I'm facing this problem for weeks, I can't find a solution.
> 
> Is there a known workaround for it ?
> 
> Thanks for help
> 
> Peter
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Insert with an '

[Peter Nacken]

Hi,

I'm new in SQLite. 

I try to insert email addresses into a table and get an error with addresses 
they have a single quotation mark ( na'm...@domain.ltd ). 

Sorry I'm facing this problem for weeks, I can't find a solution.

Is there a known workaround for it ?

Thanks for help

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

[sqlite] query planning - covering indices

[Peter Johnson]

I noticed that the query planner favours the primary index when a covering
index exists which can satisfy the same query.

My understanding is that covering indices are more efficient, as the table
itself does not need to be scanned when all the required columns exist in
the covering index?

Is it correct to say that example 1 is more efficient than the other two
examples, particularly when there are many columns, all of which are
covered by the covering index?

*example 1 - using covering index*

CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER);
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING COVERING INDEX idx_covering (a=? AND b=?)

*example2 - using primary key & covering index*

CREATE TABLE example (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING INTEGER PRIMARY KEY (rowid=?)

*example3 - using composite primary key & covering index*

CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY(a, b));
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (a=? AND
b=?)

I also noticed that when using > or < instead of = that the covering index
is used instead of the primary index.

Could someone please help me to understand why it works like this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/15/18, 1:25 AM, "Howard Chu" <h...@symas.com> wrote:

Peter Da Silva wrote:
> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of h...@symas.com> 
wrote:
> 
>  SQLightning
> 
> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.

There's been a couple of changes in syntax recently (UPSERT and IS TRUE/IS 
FALSE) that you might want to pick up for compatibility. They make it easier to 
have the same program switch between an SQLITE and PGSQL back end, which we're 
wont to do.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/14/18, 9:17 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

Apologies if I muddled the waters here.  I read the "SQLightning" response 
below as SQLitening.  I didn't know there was a similarly named project out 
there.  I also can't see the beginning of this discussion to have context on 
what was originally asked, so I don't know which project was actually intended.

Ah, OK. Here's more context, don't know if it'll help: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2018-May/079224.html

Clemens Ladisch wrote:
> Techno Magos wrote:
>> So, memory sqlite is not really usable with multiple threads (readers).
>> While one might expect  that multiple readers of *memory *content could
>> scale even better than with file content.
> 
> Concurrent accesses to the same in-memory data structures must be
> serialized.  In shared-cache mode, the connections share the cache, while
> on-disk connections each have their own cache.
> 
>> Is there some special mode possible to achieve scaling up throughput with
>> multiple threads for memory sqlite content?
> 
> Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
> and journal_mode settings), and rely on the OS file cache.

Or just use SQLightning, which has no scalability limits for readers.
 

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
different names for the same project? It doesn't seem so. Which was intended?

https://github.com/LMDB/sqlightning

On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

This is the latest:

http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

I contributed to the last SQLitening update.  No one has reported any 
issues that need fixing or updating since that update.  It seems to be working 
quite well/stable. 



> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

> SQLightning

> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

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


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

Re: [sqlite] shell edit quoting

[Peter Da Silva]

I think you're in "csv mode":

sqlite> select sql from sql_procs where name = 'a';
select * from "mytable" ;

sqlite> .header on
sqlite> .mode csv
sqlite> select sql from sql_procs where name = 'a';
sql
"select * from ""mytable"" ;
"

On 5/13/18, 7:04 PM, "sqlite-users on behalf of David Burgess" 
 wrote:

> And it works for me:

I'm pleased for you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

SQLightning

Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

Nicely retro-feel website too:

https://droptablecompanies.co.uk/

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

On 5/7/18, 2:14 AM, "sqlite-users on behalf of Scott Robison" 
 wrote:
It could just indicate someone with a sense of humor who crafted a
name that looks like an injection attack for their company.

Most likely, or else it's part of an honor system exploit.

http://humorix.org/10277

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

Re: [sqlite] Fossil Delta Compression in SqLite

[Peter Da Silva]

On 5/6/18, 11:23 AM, "sqlite-users on behalf of Philip Bennefall" 
 
wrote:
Only the requirement for attribution in binaries. That can be 
significant in certain use cases.

One line of text in the documentation provided with the distribution doesn't 
seem burdensome. It's not like the advertising clause in the original BSD 
license... is that what you're thinking of?
 

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:42 PM, "sqlite-users on behalf of R Smith" 
 wrote:
My point is that CSV was not necessarily "meant" to be what you say. Who 
exactly "meant" for it to be that? Because the official stuff makes no 
such claim or mention.

Bah. Existential shenanigans. There's probably some pseudo-Latin or German term 
for what I meant.

Your point is however more towards the WHY question, and no, I am not 
sure. But, you know, it's Microsoft - after they made "locales" they 
probably wanted someone to use it, so they bullied Excel division into 
it. :)

From what I've heard the Excel division is the least bulliable part of the 
company. Didn't they maintain their own C compiler for a while to cut down on 
dependencies on the rest of Microsoft? 

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:15 PM, "sqlite-users on behalf of R Smith" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
ryansmit...@gmail.com> wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva<peter.dasi...@flightaware.com>  
wrote:

> CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

That is a very bold and idealistic claim.

I'm not claiming what you think I'm claiming. All I'm saying is that CSV is 
meant to communicate with software, not humans. Locales are there for humans. 
Using "the locale says list separates are semicolons" as a justification for 
sometimes using semicolons in an interchange format instead of commas doesn't 
make sense. Do you actually have a reference for that being the reason for 
Excel using semicolons?

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

To another post hating on Excel - Excel has many flaws, but this is not 
one of them, it's a fault of the list-separator setting in the Windows 
OS on which the Excel runs.

CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

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