Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Darko Volaric]

0

I roll my own.


> On Mar 16, 2018, at 4:37 PM, Richard Hipp  wrote:
> 
> This is a survey, the results of which will help us to make SQLite faster.
> 
> How many tables in your schema(s) use AUTOINCREMENT?
> 
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> 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 store as integer

[Darko Volaric]

How it's stored depends on how the messages table is defined (which type
the message column has been given), which you haven't shown, and whether
storeMessage quotes the message argument when forming the string. My advice
is to remove any column type and make sure numbers are not quoted when they
are inserted into the database.

On Wed, Dec 6, 2017 at 11:09 AM, Cecil Westerhof 
wrote:

> I have the following tcl script:
> #!/usr/bin/env tclsh
>
> ### Improvements
> # Get database from conf-file
>
>
> package require sqlite3
>
>
> proc getCPUTemp {} {
> if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
> sensors] -> temp]} {
> error {Did not get exactly a single temperature line from [exec
> sensors] output}
> }
> return ${temp}
> }
>
> proc storeCPUTemp {} {
> storeMessage cpu-temp [getCPUTemp]
> }
>
> proc storeMessage {type message} {
> db eval "
>   INSERT INTO messages
>   (type, message)
>   VALUES
>   (:type, :message)
> "
> }
>
> proc storeSwap {} {
> storeMessage swap-usage [exec swapon --noheadings --show]
> }
>
> if {$argc != 1} {
> error "Error: ${argv0} DATABASE"
> }
> sqlite db  [lindex $argv 0]
> db timeout 1
> while {true} {
> after [expr {1000 * (60 - [clock seconds] % 60)}]
> set   currentMinute [clock format [clock seconds] -format %M]
> db transaction {
> storeCPUTemp
> # At the whole hour we save swap usage
> if {${currentMinute} == "00"} {
> storeSwap
> }
> }
> }
> # Not really necessary because the above loop never ends
> # But I find this more clear and is robuster against change
> db close
>
> If I enter:
> SELECT date
> ,  message
> ,  TYPEOF(message)
> FROM   messages
> WHERE  type = 'cpu-temp'
>AND date = '2017-12-06'
>
> I see that the temperature is saved as text.
> In the past I had a script like this in Python who would save the
> temperature as real. What do I need to change to let this script save it as
> real also?
>
> --
> Cecil Westerhof
> ___
> 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Darko Volaric]

What about invalid and reused MAC addresses and devices with no MAC address at 
all?
What about time resets to the epoch which are not restored, user time changes, 
daylight saving or leap seconds?

It sounds even more probabilistic than the probabilistic methods. Does anyone 
actually use it?


> On Nov 25, 2017, at 4:54 AM, J. King  wrote:
> 
> Version 1 UUIDs only use a random number (16 bits) in the case of an 
> uninitialized clock sequence (a case which, ideally, should only occur the 
> first time a device generates a UUID). Version 1 UUIDs especially avoid using 
> random numbers; they are also not a shortening of longer input. 
> 
> In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as 
> hash functions. 
> 
> I'm not a mathematician, and it's been a while since I've read the relevant 
> RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs 
> are impossible by design until either a) the 60-bit timestamp overflows, or 
> b) the MAC address namespace is exhausted. It's not a matter of probability, 
> and it's only "a certainty" after the end of their design lifetime. 
> 
> Of course, UUIDs being of finite size, they will eventually be exhausted, and 
> a single machine may only generate 65536 identifiers in a 100-nanosecond span 
> of time. They will not, however, collide. 
> 
> On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps 
>  wrote:
>> 
>> At 23:49 24/11/2017, you wrote:
>> 
>>> On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>>> 
 At 22:38 24/11/2017, you wrote:
> One proof of the falsehood of your assertion is that we CAN fill a 
> database with some data using UIDs, and we will almost certainly not
>> 
> get a collision, while you assertion we will.
>>> 
 This is an attempt at "proof by example". Keith is perfectly right 
 --mathematically speaking-- and your "proof" doesn't hold water, I 
 mean as a formal proof.  The best proof that your "proof" isn't a 
 proof is that you feel obliged  to add "almost certainly".
>>> 
>>> DISproof by example is a perfectly valid method. If someone makes a 
>>> claim that something is ALWAYS true, ONE counter example IS a 
>>> disproof. I said almost certainly as the chance of a collision isn't 0
>> 
>>> (to be able to say with certainty) but is most defintely less than the
>> 
>>> 100% claimed.
>> 
>> You're confusing one mathematical theorem and one practical statement. 
>> The first is the _mathematical_ fact that any PRNG (using any fixed 
>> number of random bits, which is what xUIDs are) will provide an 
>> infinite number of collisions with probability 1. This is definitely 
>> true. Of course here, the number of samples is implicitely infinite.
>> 
>> Your practical statement is that you can "most certainly" ignore the 
>> possibility of collision when feeding 2^N xUIDs into a unique column 
>> without loosing sleep. That's good enough in practice. The issue with 
>> your "demonstration" is that 2^N is bounded, whatever finite N you 
>> choose. Hence you don't contradict what Keith said, you just say 
>> something different applying to restricted cases. You're speaking about
>> 
>> practice, while Keith told about math. You're both right, each from his
>> 
>> own point of view. But you can't claim to disproof a trivially true 
>> theorem this way, by changing its premices.
>> 
>> An event with probability 10^-10...000 (any finite number of 
>> zeroes) will occur at least once, provided you run enough tries. It'll 
>> occur an infinite number of times if you run an infinite number of 
>> tries. Else its probability would be zero.
>> Your "disproof" amounts to say that 10^-10...000 = 0
>> 
>> And neither Keith nor I ever said that an xUID collision will occur 
>> with probability 1 after 2^64 samples. That would be false and that's 
>> why people feel free to use xUIDs _AND_ sleep quietly.
>> 
>> JcD
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> -- 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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 create primary key from two another PK's?

[Darko Volaric]

You don't, that's not how relational databases work. You need to create a 
separate field for each foreign key (student and workpiecelist) and together  
they form the primary key for the uniqueworkpc table. See David's reply for 
details.


> On Oct 20, 2017, at 9:56 PM, csanyipal  wrote:
> 
> Hi,
> 
> I have a small and simple database MyStudents.db .
> It has three tables: *student*, *workpiecelist*, *uniqueworkpc*.
> How can I manage to get primary key (pk) automatically for *uniqueworkpc*
> table which is composed by pk of *student* table and pk of *workpiecelist*
> table like below?
> 03256789415632-2
> where
> 03256789415632
> is a pk of a student in *student* table, and
> 2
> is an id of a workpiece in *workpiecelist* table.
> 
> 
> 
> -
> Best, Pál
> --
> Sent from: http://sqlite.1065341.n5.nabble.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] vfs question: how to pass binary/blob arg to xCreate()?

[Darko Volaric]

You could try Unicode binary encoding to increase the efficiency when passing 
binary as strings: https://qntm.org/unicodings 


> On Oct 20, 2017, at 7:20 PM, Liam Staskawicz  wrote:
> 
> Hi,
> 
> I'm just getting started exploring the vfs capabilities of sqlite, and
> am interested in providing a binary blob argument  to xCreate(), as that
> blob  includes a schema description used to generate the CREATE TABLE
> statement. The existing argument interface is string based, so I'm
> wondering if there's a way to avoid doing something like base64 encoding
> the blob as a string, or something along those lines.
> Thanks for any tips.
> 
> Liam
> ___
> 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] Grouping and grabbing one item

[Darko Volaric]

select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, 
sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0;


> On Oct 18, 2017, at 7:23 PM, jose isaias cabrera  
> wrote:
> 
> 
> CREATE TABLE Tasks (
> id INTEGER PRIMARY KEY,
> Pid INTEGER,
> bd TEXT,
> ed TEXT,
> task TEXT,
> target TEXT,
> amt REAL
> );
> 
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','DOC','es-ES',100);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','DOC','it-IT',120);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','DOC','fr-FR',110);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','VAL','es-ES',70);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','VAL','fr-FR',75);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (1,'2017-09-28','2017-10-01','VAL','it-IT',80);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','DOC','es-ES',100);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','DOC','it-IT',120);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','DOC','fr-FR',110);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','VAL','es-ES',70);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','VAL','fr-FR',75);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES 
> (2,'2017-09-28','2017-10-01','VAL','it-IT',80);
> 
> I know I can do,
> 
> select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING 
> amt > 0;
> 
> and get,
> 
> 2017-10-01|es-ES|100.0
> 2017-10-01|fr-FR|185.0
> 2017-10-01|it-IT|200.0
> 
> but, I would like to add the ed of the task='QUOTE' to the beginning of the 
> list.  So, the result would look like this,
> 
> 2017-09-27|2017-10-01|es-ES|100.0
> 2017-09-27|2017-10-01|fr-FR|185.0
> 2017-09-27|2017-10-01|it-IT|200.0
> 
> I know how to select it by itself,
> 
> SELECT ed from Tasks where task = 'QUOTE' and Pid = 1;
> 
> but I need to add it to the beginning of the list with a JOIN or something. 
> Any thoughts?  Thanks.
> 
> 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] PRAGMA optimize; == no busy handler?

[Darko Volaric]

In the case of a database that ran hot all the time a better strategy would be 
to have an online backup of the database running at all time and then run 
analyze on that periodically, then update the stats table in the live database 
when that was finished. I'm not sure what the rules are for updating the stats 
table for an open database are, but that would be a very useful enhancement if 
it wasn't very complicated to implement.



> On Oct 6, 2017, at 8:26 PM, Toby Dickenson  wrote:
> 
> Thats problematic. It means I have to guess how long my analyze might take,
> and guess at a suitable time when that scan wont block other clients. Its a
> good reason to never call analyze.
> 
> Does it have to be so? It seems to me (possibly naively) that the scan
> could be performed in one long read transaction, then a separate quick
> write transaction could be used to update the stat tables. Theres no need
> for the whole operation to be atomic, right?
> 
> On 5 October 2017 at 00:37, Simon Slavin  wrote:
> 
>> 
>> 
>> On 5 Oct 2017, at 12:07am, Toby Dickenson  wrote:
>> 
>>> How does PRAGMA optimize or ANALYSE affect concurrency? Is there any
>>> risk that it might leave the database locked for the full duration of
>>> the scan?
>> 
>> ANALYZE does this every time.  It’s not possible to analyze a database
>> while it’s being changed.
>> 
>> I’d expect "PRAGMA optimize" to do the same but I’m not sure.
>> 
>> 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

Re: [sqlite] XOR operator

[Darko Volaric]

> However, '&' and '|' and '<<' and '>>' do not seem to be defined, either 
> there or in

Almost none of the operators are defined. All that section needs is a sentence 
at the start saying "The operators have the same meaning as ANSI C except as 
described below."



> On Oct 6, 2017, at 11:31 AM, Simon Slavin  wrote:
> 
> 
> 
> On 6 Oct 2017, at 9:12am, Rowan Worth  wrote:
> 
>> On 6 October 2017 at 15:42,  wrote:
>> 
>>> On Fri Oct 06, 2017 at 09:28:08AM +0200, Clemens Ladisch wrote:
 
 For boolean values, "a XOR b" = "a <> b".
>>> 
>>> Is the <> operator documented somewhere? I can't find it in either of
>>> these places:
>> 
>> <> is SQL for "not equal to" (shout out to all the BASIC fans). It’s 
>> documented here:
>> https://sqlite.org/lang_expr.html#binaryops
> 
> That page says that '<>' means 'non-equals'.  This is not the same as the 
> binary operation 'XOR' since 'non-equals' can yield only two values: true and 
> false.  Experimentation shows …
> 
> sqlite> SELECT 11 = 19;
> 0
> sqlite> SELECT 11 <> 19;
> 1
> sqlite> SELECT 11 | 19;
> 27
> sqlite> SELECT 11 & 19;
> 3
> 
> … that even for binary values the documentation is correct and that '<>' does 
> not mean 'XOR'.  However, '&' and '|' and '<<' and '>>' do not seem to be 
> defined, either there or in
> 
> 
> 
> I can’t find anywhere in SQLite’s own documentation that defines them.  
> Perhaps this should be remedied.
> 
> 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

Re: [sqlite] ADO.NET driver upgrade puzzle

[Darko Volaric]

If you're accessing a database concurrently from different processes then you 
may want to look at this: https://sqlite.org/faq.html#q5 
 as your file system may be causing you 
problems.


> On Sep 25, 2017, at 11:09 AM, Roberts, Barry (FINTL)  
> wrote:
> 
> One of the stability tests I run involves 2 processes logging to the same 
> data sets (db3) files, because we do have multi-process access onto the db3 
> files. 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADO.NET driver upgrade puzzle

[Darko Volaric]

That's not strictly true, it depends on the threading mode: 
https://sqlite.org/threadsafe.html 

It sounds like the driver is trying to manage concurrency on its own and 
failing. For the purposes of calling sqlite, if the library is used in the 
default "serialized" mode, then no locks at all should be necessary, but maybe 
the locks are required for something happening in the driver code. If you check 
which threading mode sqlite is running in you''ll either need to change the 
threading mode to serialized, and if that's already the case or changing to 
that doesn't fix it then the threading problem is in the driver code itself.


> On Sep 25, 2017, at 11:33 AM, Clemens Ladisch  wrote:
> 
> Roberts, Barry (FINTL) wrote:
>> The application is multi-threaded
>> 
>> the system sometimes locks up, or marks one or more of the db3 as malformed.
> 
> Sounds like a threading problem.
> 
> You should not have more than one thread accessing the same connection
> concurrently.
> 
>> Using (connection)
>> Open connection
> 
> What is the relationship between threads and databases?  Are you really
> re-opening the connection for every transfer?
> 
> 
> Regards,
> Clemens
> ___
> 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] [EXTERNAL] Common subexpression optimization of deterministic functions

[Darko Volaric]

If you're looking for a simple/easy/clean way of doing it, there isn't one. You 
have to modify the library to do it properly. But I still find it an 
interesting design challenge.

Maybe instead of going the eager route you can go lazy and just cache 
subexpressions which might be called again. This is messy though, a cacheable 
expression would have to be a user-defined function call that takes parameters 
that are easy to compute (since the params are always evaluated), plus an id 
param to identify the sub expression. The first time the function is called you 
calculate and store the value under the id and next time it's called you return 
the stored value that was previously computed. This has the advantage of being 
automatic and you could hand tune it, storing only the calls you know you will 
reuse. Having to use user defined function calls is pretty ugly although you 
could automatically transform the statement and generate the C source for the 
calls.

Another approach I've been thinking about implementing, which is a bit 
speculative to say the least, is to compile the SQLite byte codes to something 
like WebAssembly byte codes, then running it through the BinaryEn which 
optimizes it, then into native code. That would implement not only CSE but a 
host of other optimizations and might be useful where the statement and query 
plan will not change much, or it's worth spending compute time optimizing it 
thus. Typically SQLite will be IO bound, but for some applications (like mine) 
it might make sense.



> On Sep 14, 2017, at 6:37 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 14, 2017, at 8:38 AM, Warren Young  wrote:
>> 
>> All the examples I’ve seen attempting to support the value of this feature 
>> are simple enough that even a naive text compression algorithm could find 
>> the similarities and “hoist” the copies so the value is computed only once.  
>> That means the *human* can also see the CSE and hoist it manually.  
> 
> Fine; **can someone please tell me how to hoist/factor out the subexpression 
> manually then**? My SQL queries are generated procedurally and I can easily 
> change my code to do this refactoring, if I know the trick.
> 
> I've tried using a "WITH" clause, but it doesn't help; it results in the same 
> number of calls to the native function. (See previous post in this thread for 
> an actual example.)
> 
> I need something that doesn't modify the database, so generating a new table 
> with the function results is right out. Even a temporary table wouldn't help 
> because it would probably be more overhead than it's worth (the functions I 
> want to factor out aren't _that_ expensive.)
> 
> —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] [EXTERNAL] Common subexpression optimization of deterministic functions

[Darko Volaric]

OK, in trying to clear up one misunderstanding I've created another, so let me 
be clear: I don't support automatic CSE, I think it should be done manually. 
But to support that Richard, can you answer this question: is there a way of 
ensuring that a particular expression (or just function call) will be 
guaranteed to be evaluated before any other in a particular statement?



> On Sep 14, 2017, at 4:13 PM, Richard Hipp <d...@sqlite.org> wrote:
> 
> On 9/14/17, Darko Volaric <li...@darko.org> wrote:
>> I think people are missing the point, probably becuase it's not a great
>> example. Consider the following statement:
>> 
>> SELECT funca(slow(10)), funkb(slow(10))
>> 
>> and lets say slow(10) takes an hour to compute, and funka and funkb take
>> almost no time to execute. With common subexpression optimization the
>> statement would take one hour, instead of two, to compute becuase the value
>> of slow(10) would only be calculated once.
> 
> I fully understand the benefits of CSE.  My point is that constructs
> such as the above are very rarely used in SQLite - so much so that the
> amount of extra time spent inside of sqlite3_prepare() in order to
> deal with them is not worth the effort.
> 
> CSE in the example you cite above is relatively easy.  A harder example is 
> this:
> 
>  SELECT coalesce(x, slow(10)), coalesce(y, slow(10)) FROM tab;
> 
> -- 
> 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] [EXTERNAL] Common subexpression optimization of deterministic functions

[Darko Volaric]

I think people are missing the point, probably becuase it's not a great 
example. Consider the following statement:

SELECT funca(slow(10)), funkb(slow(10))

and lets say slow(10) takes an hour to compute, and funka and funkb take almost 
no time to execute. With common subexpression optimization the statement would 
take one hour, instead of two, to compute becuase the value of slow(10) would 
only be calculated once.


> On Sep 14, 2017, at 8:31 AM, Wout Mertens  wrote:
> 
> Isn't that what cross join is for? Do a select on a virtual table to
> calculate the value and then use that value in the real where clause?
> 
> On Wed, Sep 13, 2017, 9:10 AM Hick Gunter  wrote:
> 
>> Try fl_value(...) IN ()
>> 
>> -Ursprüngliche Nachricht-
>> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> Im Auftrag von Jens Alfke
>> Gesendet: Dienstag, 12. September 2017 19:26
>> An: SQLite mailing list 
>> Betreff: [EXTERNAL] [sqlite] Common subexpression optimization of
>> deterministic functions
>> 
>> SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic
>> function. For example, in this query, where `fl_value` is a function I’ve
>> registered as SQLITE_DETERMINISTIC:
>> 
>> SELECT key FROM kv_default
>> WHERE fl_value(body, 'contact.address.state') = 'CA'
>>   OR fl_value(body, 'contact.address.state') = 'WA'
>> 
>> fl_value gets called twice per row in the table, with the same inputs both
>> times of course. As fl_value is not a cheap function — it’s similar to
>> json_value — it would be a noticeable speedup if it were evaluated only
>> once per row.
>> 
>> Is there a way I can restructure these (automatically generated) queries
>> to do the refactoring explicitly? Sort of like assigning to a temporary
>> variable in an imperative language? It looks like a WITH clause lets me do
>> this syntactically, but I'm not sure if it'll make a difference at runtime.
>> 
>> —Jens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> ___
>> Gunter Hick
>> Software Engineer
>> Scientific Games International GmbH
>> FN 157284 a, HG Wien
>> Klitschgasse 2-4, A-1130 Vienna, Austria
>> Tel: +43 1 80100 0
>> E-Mail: h...@scigames.at
>> 
>> This communication (including any attachments) is intended for the use of
>> the intended recipient(s) only and may contain information that is
>> confidential, privileged or legally protected. Any unauthorized use or
>> dissemination of this communication is strictly prohibited. If you have
>> received this communication in error, please immediately notify the sender
>> by return e-mail message and delete all copies of the original
>> communication. Thank you for your cooperation.
>> 
>> 
>> ___
>> 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] SQLite list spam from raypoker79

[Darko Volaric]

Is it possible that the spammer got a hold of the subscriber list?


> On Sep 13, 2017, at 1:55 AM, Richard Hipp  wrote:
> 
> Thanks for reporting this, Darren.  I didn't see it because those
> emails went straight into my spam folder.
> 
> I don't know how they got through, because raypoke...@yahoo.com is not
> a subscriber, and new subscription requests are moderated.
> 
> If anybody else is seeing these emails, and/or has advice on how I can
> stop them, please send me private email.
> 
> On 9/12/17, Darren Duncan  wrote:
>> Richard, in case you didn't notice yet, there's an insane-or-troll-sounding
>> list
>> subscriber raypoker79 who has sent 3 messages in the last 90 minutes, as
>> replies
>> to normal threads, asking to be unsubscribed and talking about credit cards
>> etc;
> 
> 
> -- 
> 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] Common subexpression optimization of deterministic functions

[Darko Volaric]

Yeah that is a tricky bit, especially since the query optimizer might evaluate 
join expressions in an arbitrary order. A possible approach to this is to work 
out how to always get a particular expression evaluated first (that may well 
just be the left-most expression in the WHERE clause) then create a trivial 
function that takes any number of parameters and always returns true and pass 
all the Set functions as parameters in left to right dependency order. This 
should work becuase I believe function parameters are evaluated left to right 
before the function is called.


> On Sep 12, 2017, at 11:27 PM, Jens Alfke <j...@mooseyard.com> wrote:
> 
> 
>> On Sep 12, 2017, at 1:41 PM, Darko Volaric <li...@darko.org 
>> <mailto:li...@darko.org>> wrote:
>> 
>> You can implement this by using user defined functions to implement row 
>> "local variables" or "registers". They're single assignment storage that 
>> keeps intermediate results, namely the common subexpressions.
> 
> Thanks! That's a very interesting technique.
> 
>> Note that you would need to order these so they are evaluated in dependency 
>> order, i.e. ensure each name is set before it is got.
> 
> What's a good way to do that? Since SQL is a non-imperative language there 
> isn't much notion of order of operations. I can imagine using a 
> short-circuiting AND operator, but can I be guaranteed that this will always 
> work?
> 
> —Jens

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Darko Volaric]

You can implement this by using user defined functions to implement row "local 
variables" or "registers". They're single assignment storage that keeps 
intermediate results, namely the common subexpressions.

You'd define two functions, something like Get(rowid, name) and Set(rowid, 
name, value). You call Set with the subexpressions as the last parameter. It 
doesn't return any value and just stores the value. The Get function returns a 
previously set value with the given name and is used in the expressions where 
that subexpression would otherwise appear. Note that you would need to order 
these so they are evaluated in dependency order, i.e. ensure each name is set 
before it is got. The rowid parameter is used to detect when the row changes 
and the local variables are all cleared in readiness for the next row.



> On Sep 12, 2017, at 9:22 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 12, 2017, at 12:02 PM, Darren Duncan  wrote:
>> 
>> Practically speaking any optimization to reduce actual calls to the 
>> deterministic function would have to be at compile time to rewrite the query 
>> to explicitly keep the result of the function and use it several times,
> 
> Exactly.
> 
>> which is someone users can also do by writing the query differently.
> 
> Great — any advice on how to do it? I'm totally willing to do this :) but I'm 
> not sure how. As I said, a WITH clause looks promising, but I don't know if 
> that is purely syntactic sugar, like a macro. (And changing my query 
> generator to factor common calls into WITH clauses would be a nontrivial 
> amount of work, so I would like to get some assurance that it might help, 
> before I try it.)
> 
> The CSE optimization has long been standard in traditional compilers, even 
> though the programmer could get the same result by changing their code. (The 
> same is true of many other optimizations.) The benefit is that it lets the 
> developer write simpler, clearer code with less effort. 
> 
> I realize SQLite doesn't have the kind of industrial-strength query 
> optimizers that other SQL databases have, but (from an outside perspective) 
> this seems like a fairly straightforward optimization. SQLite is already 
> doing some similar tricks to recognize matching sub-expressions when it 
> applies an expression-based index to a query, for example.
> 
> —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] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

[Darko Volaric]

If you're preparing that statement more than once then you are wasting time, 
there's no reason whatsoever to do it. You're also wasting time if you make a 
bind call to set any column that hasn't changed since the last time you 
executed the statement.

The entire row is rewritten when updating so the most efficient way to do it is 
to call sqlite3_prepare_v2 once, then bind any columns that have changed, call 
sqlite3_step to execute the statement, then call sqlite3_reset to reuse the 
statement, then go back to binding any columns that have changed and repeat the 
other steps. sqlite3_reset does not clear any column bindings.



> On Sep 10, 2017, at 7:08 AM, ghalwasi  wrote:
> 
 Are you using a single prepared statement and binding values (in which
> case, how do you know what values to bind for the "non-updated" columns?) or
> are you creating query strings? 
> 
> I am not too sure, if i get it completely. My current code has a lot of
> update statements like.
> 
> "update records set name=:name, type=:type, class=:class, ttl=:ttl where
> rr_id=:rr_id;"
> every time we do prepare the statetment again (sqlite3_prepare_v2) and call
> sqlite_bind_* for each of these columns (name, type, class, ttl) and then
> execute.
> 
> Now here intent was/is to just update "ttl" column but i see that we are
> unnecessarily updating 4 fields. And my original question was in this
> context where i want to figure out whether it could make some performance
> improvement if we change the above statement to 
> "update records set ttl=:ttl where rr_id=:rr_id;"
> 
> 
> 
> 
> --
> Sent from: http://sqlite.1065341.n5.nabble.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] Amalgamation compilation with SQLITE_THREADSAFE=0

[Darko Volaric]

Maybe you misunderstood that sentence:  "auto_vacuum=FULL" mode will reduce the 
file size but so will a "VACUUM" command, independent of the auto_vacuum 
setting. It makes that crystal clear by detailing how it works later on that 
page: 

> The VACUUM command works by copying the contents of the database into a 
> temporary database file and then overwriting the original with the contents 
> of the temporary file.


So it will by definition use the minimum possible amount of space for the 
resulting database file.



> On Sep 1, 2017, at 6:53 PM, Jacky Lam  wrote:
> 
> What I am understood from the answer is explicit code must be used during
> creating db, for example, auto_vacuum=FULL.
> If no, the file size will not reduce even deleting a number of records and
> this is normal.
> 
> 
> On Sat, Sep 2, 2017 at 12:47 AM, Stephen Chrzanowski 
> wrote:
> 
>> You'll want to vacuum the database.
>> 
>> https://sqlite.org/lang_vacuum.html
>> 
>> Deleting records from a SQLite database only changes the pages that already
>> exist within the file.  It doesn't prune anything automatically.  It can do
>> so, though, if you set the appropriate pragma.
>> 
>> On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam  wrote:
>> 
>>> Hi All,
>>> While using my own implemented file system, the db file size will only
>>> expand and not prune even remove record from the db.
>>> Could anyone advise me that what I am missing in order to pruning the db
>>> size when removing a number of records?
>>> Jacky
>>> 
>>> On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin 
>>> wrote:
>>> 
 
 
 On 9 Aug 2017, at 3:31am, Jacky Lam  wrote:
 
> 1. Can I call sqlite3_open more than one times before calling
 sqlite3_close
> and sqlite3_free?
 
 Call sqlite3_open() for each database you want to open.  You can have
>> any
 number of databases open at the same time.  Call sqlite3_close() for
>> each
 database you have open when you no longer need it.  After closing the
>>> last
 connection call sqlite3_shutdown() as described in
 
 
 
 (The above ignores use of SQL's ATTACH command.)
 
 You are not expected to ever call sqlite3_free() unless you are using
 SQLite to do other memory-handing tasks for you.  Most people who use
 SQLite never call sqlite3_free().
 
> 2. If the above mentioned devices change to mutli-thread setting but
>> no
> thread safe functions such as mutex, is this setting still fine?
 
 You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
 arrange that only one thread will be doing SQLite calls at once.  As
>> long
 as you can ensure this, SQLite will function correctly.
 
>If not, how can I make it thread safe with lack of mutex support
>> in
> the system?
 
 Do any of the following:
 
 A) Implement your own mutex system.
 
 B) Use SQLite’s mutex system ( >> mutex_alloc.html>
 )
 
 C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections
>> using
 sqlite3_open_v2(), as described in >> 
>>> .
 
 Please note that the above is a top-of-the-head answer and I have not
 personally tries each of the options to make sure it works.
 
 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] hex and char functions

[Darko Volaric]

Actually a maximum of 4 bytes are required to encode a single valid code-point 
in UTF-8.


> On Aug 8, 2017, at 2:44 AM, Jens Alfke  wrote:
> 
> 
>> On Aug 7, 2017, at 8:29 AM, x  wrote:
>> 
>> I thought I had learned enough about this string lunacy to get by but 
>> finding out that the UTF8 code for the UTF16 code \u0085 is in fact \uc285 
>> has tipped me over the edge. I assumed they both used the same codes but 
>> UTF16 allowed some characters UTF8 didn’t have.
> 
> UTF-8 is backwards-compatible with ASCII. All 7-bit bytes (00-7f) represent 
> the same characters as their ASCII equivalents. Beyond that, UTF-8 uses a 
> sequence of two to five bytes in the range 80-ff to encode a single Unicode 
> character/code-point. (You can sort of think of this as every byte holding 7 
> bits of the actual character number, with its MSB set to 1. It’s not exactly 
> like that, but close.)
> 
> IMHO UTF-8 is the best general purpose text encoding. Code that works with 
> ASCII (real 7-bit ASCII, not the nonstandard “extended” stuff) will generally 
> work with UTF-8; the main thing to watch out for tends to be breaking or 
> trimming strings, because you don’t want to cut part of a multibyte sequence. 
> UTF-8 is also quite compact for Roman languages (although not non-Roman ones.)
> 
> 16-bit encodings used to seem like a good idea back when Unicode has fewer 
> than 65,536 characters, so you could assume that one unichar = one character. 
> Those days are long gone. Now dealing with UTF-16 has all the same problems 
> of dealing with UTF-8 (i.e. multi-word sequences) without the benefits of 
> compactness or ASCII compatibility.
> 
> 32-bit encodings are just silly, unless for some reason you really really 
> have to optimize for speed over size (and even then the added size may well 
> blow out your CPU caches and negate the speed boost.)
> 
> —Jens
> 
> PS: Apparently C++11 allows Unicode string literals by putting a letter U in 
> front of the initial quote. The result will be a string of wchar_t.
> ___
> 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] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

[Darko Volaric]

There is no sqlite3_result_subtype() becuase there is no sqlite3_bind_subtype() 
becuase the subtype of a value cannot be stored in the database. That is my 
point. I don't know what you mean by your second paragraph, but subtype here is 
just an attribute of a value, similar to it's type (in SQLite each stored value 
can have its own type). Given SQLite is a database, it seems natural to be able 
to store a value's subtype along with the other attributes of a value, but for 
whatever reason the SQLite devs did not think that was nessessary.


> On Jul 24, 2017, at 7:57 AM, petern <peter.nichvolo...@gmail.com> wrote:
> 
> Users of a sqlite3_bind_subtype() wouldn't expect those bits to be
> persisted to disk considering that a function returned column value
> presented to INSERT or UPDATE doesn't have sqlite3_result_subtype() bits
> recorded.   Does that make a difference to your answer?
> 
> https://www.sqlite.org/c3ref/result_subtype.html
> 
> Subtypes as currently implemented are only useful for coordinating type
> safety among the application's transient objects.  I certainly wouldn't
> expect to read back hidden subtype bits from the disk to initialize/deduce
> a transient object's type.  In the interest of readability and transparency
> the design of the ordinarily visible part of table name and columns
> themselves would always fully encode the type of transient objects based on
> that table's rows.  Encoding stuff in persisted hidden column bits works
> against the "correct by inspection" objective of good design.
> 
> 
> 
> 
> 
> On Sun, Jul 23, 2017 at 9:13 PM, Darko Volaric <li...@darko.org> wrote:
> 
>> You can't have sqlite3_bind_subtype() because it would require a change to
>> the database file format - there would be no real performance penalty
>> besides increased record size. There is no existing place to store a
>> per-value subtype in the file format as it stands, although I did have a
>> design that would hide a few bits for the subtype at the end of the record
>> header where no-one is looking. It is a hack though and I gave up becuase
>> of having to also implement passing around the subtype internally, which
>> was then solved with the above functions, but by that time I had moved on
>> to a different solution. If you're really interested I can probably
>> describe the hack as I originally envisaged it.
>> 
>> Anyway, that's one theory, the other is that Richard isn't updating the
>> file format just to spite me for rudely arguing the need for subtypes a
>> couple of years ago. That or he just values the stability of the file
>> format.
>> 
>> 
>>> On Jul 24, 2017, at 2:40 AM, petern <peter.nichvolo...@gmail.com> wrote:
>>> 
>>> 2. These functions convey only the lower 8 bits of information and there
>>> there is no corresponding sqlite3_bind_subtype() but there is mention of
>>> expanding the number of bits in future SQLite versions. I take it the
>>> present 8 bits API can't be extended to a new sqlite3_bind_subtype()
>>> function for performance reasons?  Even 8 bits could hash a modest cache
>> of
>>> application pointers.  Just asking.
>>> ___
>>> 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] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

[Darko Volaric]

You can't have sqlite3_bind_subtype() because it would require a change to the 
database file format - there would be no real performance penalty besides 
increased record size. There is no existing place to store a per-value subtype 
in the file format as it stands, although I did have a design that would hide a 
few bits for the subtype at the end of the record header where no-one is 
looking. It is a hack though and I gave up becuase of having to also implement 
passing around the subtype internally, which was then solved with the above 
functions, but by that time I had moved on to a different solution. If you're 
really interested I can probably describe the hack as I originally envisaged it.

Anyway, that's one theory, the other is that Richard isn't updating the file 
format just to spite me for rudely arguing the need for subtypes a couple of 
years ago. That or he just values the stability of the file format.


> On Jul 24, 2017, at 2:40 AM, petern  wrote:
> 
> 2. These functions convey only the lower 8 bits of information and there
> there is no corresponding sqlite3_bind_subtype() but there is mention of
> expanding the number of bits in future SQLite versions. I take it the
> present 8 bits API can't be extended to a new sqlite3_bind_subtype()
> function for performance reasons?  Even 8 bits could hash a modest cache of
> application pointers.  Just asking.
> ___
> 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] Article: UUID or GUID as Primary Keys? Be Careful!

[Darko Volaric]

And between centrally assigned "consecutive integer keys" and elaborate, 
probabilistic UUIDs are centrally allocated number ranges of a natively 
supported integer, eg 64 bit on SQLite. Problem solved.


> On Jun 10, 2017, at 6:27 AM, Jens Alfke  wrote:
> 
> 
>> On Jun 9, 2017, at 3:05 PM, Simon Slavin  wrote:
>> 
>> Tangential to SQLite, but there’s little on the list at the moment so 
>> perhaps some of you might like this.
>> >  
>> >
> 
> He makes some questionable points, like saying that an ASCII string of hex 
> has a “9x cost in size” compared to a binary representation, or that hex 
> strings would somehow get larger when converted from ISO-8859-1 to UTF-8.
> 
>> Several of his points don’t apply to SQLite, which works differently from 
>> most SQL engines, but it’s interesting reading nevertheless.
> 
> Most of what he says is extremely RDBMS-centric, even though he never says 
> so. I have the feeling he’s never used a NoSQL database. To me the whole 
> thing comes off as pretty parochial — I have to laugh at his assertion that 
> UUIDs aren’t scalable, since in systems like Couchbase Server*, which handles 
> ridiculously huge data sets, it’s extremely common to use them as keys. 
> 
> From a highly-scalable perspective, having a single global counter in the 
> database to assign consecutive integer keys is a horrifying bottleneck!
> 
>> (I find it ironic that the URLs for his posts are composed of two elements: 
>> an assigned piece of content and an arbitrary long number coded as 
>> hexadecimal.)
> 
> Blame that on Medium, which is hosting his blog posts :)
> 
> —Jens
> 
> * Disclaimer: I work for Couchbase (but on mobile software, not on servers.)
> ___
> 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] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Darko Volaric]

If you really wanted to have stored procedures and did not mind calling them 
using a function syntax, you could write your own stored procedure extension. 
You'd store them in their own table, write a custom function that evaluates 
them and call them something like this: sp("name", param1, param2,...). A 
simple recursive-decent parser would likely do the trick to parse and evaluate 
the procedures. Depending on your needs, the "stored procedures" could be as 
simple as a series of SQL statements to execute, with parameter substitutions, 
which would be almost trivial to write. If you were so inclined.

There are good reasons to have stored procedures other than reducing connection 
latency - developers like to encapsulate logic that is associated entirely with 
the database in the database, use them to do extended checking, to populate 
denormalized or derived data, or to provide a level of abstraction, for 
instance. Although this code could be put in the client side you may want to be 
able to maintain the database independently of the application or you may have 
multiple client applications and want to avoid duplicating code in multiple 
code bases, for instance.



> On Apr 15, 2017, at 3:57 PM, Manoj Sengottuvel  wrote:
> 
> Hi Richard,
> 
> Is it possible to create the Stored Procedure (SP) in Sqlite?
> 
> if not , is there any alternate way for SP?
> 
> 
> regards
> Manoj
> ___
> 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 schema in memory?

[Darko Volaric]

Is it time to break out an API for schema lookup? That would seem to be the 
least work for the developers and would give people the chance to implement 
whatever strategy they need to manage large schemas, including storing them in 
the database in a structured manager, or a compressed in-memory representation.



> On Mar 16, 2017, at 11:57 PM, Richard Hipp  wrote:
> 
> On 3/16/17, Bob Friesenhahn  wrote:
>> 
>> I just checked and the total character count for the trigger and index
>> names themselves is only 23k, which is not even a tiny dent in 1.58MB.
>> Is there a muliplying factor somewhere which would make this worth
>> doing?
> 
> I did say it was a "small step"  :-)  Great journeys begin with a single step.
> 
>> 
>> Storing original SQL text such as SQL keywords surely consumes a lot
>> of space (I am assuming this is what is done).  If SQL command and
>> verb text is converted into a more concise specification for internal
>> use, then less memory should be consumed.
> 
> The schema is stored as a parse tree.  But it still needs to store the
> names of objects (triggers, indexes, tables, columns) in order to look
> them up by name in response to various SQL commands.
> -- 
> 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] Incremental BLOB IO

[Darko Volaric]

I haven't got an example but how about inserting the record and then
updating the blob in question?

On Wed, Mar 15, 2017 at 12:33 AM, Mike King  wrote:

> Hi,
>
> I'm trying to understand incremental BLOB IO using the latest
> System.Data.Sqlite and C#.
>
> I've got some test code working where I can execute a query and using a
> data reader get a SQLiteBlob object and read the blob back. However, I'm
> not clear as to how I can use incremental IO if I'm doing an Insert of a
> new record.
>
> I appreciate it's a really cheeky thing to ask, but does anybody have an
> example of this?
>
> Cheers,
>
> Mike
> ___
> 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 is this so much more efficient?

[Darko Volaric]

You can actually index functions or expression:
https://www.sqlite.org/expridx.html

On Thu, Feb 16, 2017 at 9:32 PM, Cecil Westerhof 
wrote:

> 2017-02-16 21:10 GMT+01:00 Dominique Pellé :
>
> > Cecil Westerhof  wrote:
> >
> > > I have a table vmstat that I use to store vmstat info. ;-)
> > > At the moment it has more as 661 thousand records.
> > >
> > > In principle the values of usertime, systemtime, idletime, waittime and
> > > stolentime should add up to 100. I just wanted to check it. Of-course
> > there
> > > could be a rounding error, so I wrote the following query:
> > > SELECT date
> > > ,  time
> > > ,  usertime
> > > ,  systemtime
> > > ,  idletime
> > > ,  waittime
> > > ,  stolentime
> > > ,  (usertime + systemtime + idletime + waittime + stolentime) AS
> > > totaltime
> > > FROM   vmstat
> > > WHERE  totaltime  < 99 OR totaltime > 101
> > >
> > > I did not like that, so I rewrote the WHERE to:
> > > WHERE  ABS(100 - totaltime) > 1
> > >
> > > The funny thing the second WHERE is more efficient as the first, where
> I
> > > would have expected it to be the other way around.
> > > The first takes around 1.050 milliseconds.
> > > The second takes around  950 milliseconds.
> > > So the second is around 10% more efficient. Why is this?
> > >
> > > In case it is important: I did this in sqlitebrowser 3.7.0, which uses
> > > SQLite 3.8.10.2.
> >
> >
> > I suspect  that when you use "WHERE ABS(100 - totaltime) > 1"
> > SQLite cannot use an index since it's an expression, so it does a
> > full table scan, whereas the other solution which does
> > "WHERE  totaltime  < 99 OR totaltime > 101"
> > may use an index on totaltime (assuming that there is an index).
> >
> > In general using an index is good.  But if most of the records
> > satisfy the condition "ABS(100 - totaltime) > 1" then an index
> > can be more harmful than useful.   And that could explain
> > why using "WHERE ABS(100 - totaltime) > 1" is a bit faster.
> > You could try "EXPLAIN QUERY PLAN" on your queries to
> > see if they use an index or if they do a full table scan.
> >
>
> ​Totaltime is calculated, so it cannot have an index. ;-)
> Besides from the almost 700.000 records only two satisfy the condition.
>
> I should look into EXPLAIN QUERY PLAN.
>
> --
> Cecil Westerhof
> ___
> 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] Storing a INTEGER in a TEXT field

[Darko Volaric]

The problem is that you're giving your column a type when you don't want it
to have. If the second last line was "message NOT NULL" you'd get exactly
what you're asking for.

On Wed, Feb 15, 2017 at 1:22 AM, Cecil Westerhof 
wrote:

> I have the following table:
> CREATE  TABLE messages(
> messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
> dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> typeTEXT NOT NULL,
> message TEXT NOT NULL
> );
>
> But for some data the field message is filled with an integer. An integer
> takes less room as its text representation and it sorts differently also.
> Is there a way to store an INTEGER in a TEXT field? Not very important,
> more nice to have.
>
> I just created the following view:
> CREATE VIEW downloadCount AS
> SELECT   date AS Date
> ,time AS Time
> ,CAST(message AS INTEGER) AS DownloadCount
> FROM messages
> WHEREtype = 'download-count'
> ;
>
> --
> Cecil Westerhof
> ___
> 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] View column data type affinity?

[Darko Volaric]

Yes, by giving the column a type, that determines its affinity.

On Sun, Jan 15, 2017 at 8:08 PM, Joe Seeley  wrote:

> Is there a way when creating a view to specify the data type affinity for
> each column?
> ___
> 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 this query plan?

[Darko Volaric]

Actually all that would happen is a massive number of hidden bugs would be
revealed. He would be doing the world a favour.

On Thu, Jan 12, 2017 at 5:13 PM, Jean-Christophe Deschamps  wrote:

> At 15:13 12/01/2017, you wrote:
>
>> Re: "I read this as a provocative joke."
>>
>> I didn't read it as just a joke.
>>
>> The analogy with random fonts, etc. breaks down, I think, because
>> randomizing the ordering would be an attempt to *improve* sqlite's
>> usability -- not some pedantic punishment.
>>
>
> I read this, as well as Hick previous reply. I'm well aware of the issue,
> which is in no way specific to SQLite.
>
> Yet, providing some new SQLite build (source, amalgamation binaries)
> someday where the result order would be willingly random or different from
> the current behavior (call it natural or naively expectable or intuitive or
> whatelse) will break uncountable uses where the app isn't open to change.
> Remember that in many situations SQLite is being used as a loadable
> component either because the original code was designed so or because the
> language used can't statically link.
>
> So it could be an improvement for *-future-* SQLite apps, or rather a good
> reminder aimed towards developpers, but that would potentially break
> gazillions legacy uses or at the very least cause a huge lot of unnecessary
> inconveniences. Expect a tsunami of disapprovals.
>
> If a user has problems with her sqlite output early in the process, leading
>> to the discovery of a missing "ORDER BY" clause, the argument is that she
>> has been dealt a favor.  It's vastly worse for her to encounter a
>> mysterious bug when the sqlite version is updated years from now to one
>> which (perfectly correctly) returns a different ordering for that same
>> query.
>>
>> Further, Dr. Hipp and his team won't have to deal with howls of "it's
>> broken" when such a version is released.
>>
>
> I also have to repeatedly point out in the community where I offer support
> that SQL deals with unordered sets and to the consequence, that issuing the
> very same SELECT twice in a row could rightfully return results in
> different orders when no ORDER BY clause is specified.  But I bet such an
> uncalled change (as salutary as it may be from a rational point of view)
> would result in a long term continuous higher saturation of this list and
> other support channels with posts from questionning/angry/disappointed
> 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] Why this query plan?

[Darko Volaric]

Your example is entirely wrong. Spreadsheet apps explicitly define the
behavior, and provide functionality, for defaulting the attributes for
unused cells.

A better example is this: looking at your paper mail and asking "why didn't
mail posted on the same day from the same sender arrive on the same day?"

The order of an unordered result is unspecified. There is a good reason for
that: it's too complex to describe, or it's just not possible, for example
when probabilistic optimisation is used. It's not an arbitrary restriction.

Anyone asking why the order is what it is is not a valid question,
regardless of their curiosity. Maybe if they really, really want to know
they should read the code.

On Thu, Jan 12, 2017 at 9:11 AM, Jean-Christophe Deschamps  wrote:

> Richard,
>
> At 02:00 12/01/2017, you wrote:
>
> The "PRAGMA reverse_unordered_selects=ON" statement has long been
>> available to do this.  But it is an optional feature that has to be
>> turned on.  And I don't think anybody ever turns it on.  My proposal
>> is to make it random.
>>
>> Maybe it would be sufficient to initialize the
>> reverse_unordered_selects setting to a random value (on or off) inside
>> of sqlite3_open().
>>
>
> I read this as a provocative joke.
>
> While I agree with you that way too many users and applications blindly
> (naively?) rely on the current behavior, willfully making the order more or
> less random by default would be similar, say for a spreadsheet app, to
> choose random font, size, centering, coloring and formatting of any cell
> where those attributes have not been explicitely set.
>
> Ask yourself, but I for one wouldn't make much use of such a spreadsheet
> app, even if some standard says it's legitimate behavior.
>
> If you ask somebody to enumerate strictly positive integers less than 6,
> 99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 4, 1,
> 3 is a perfectly valid answer, anyone would ask "Why this funny order?".
>
>
> ___
> 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 3.16.0 enters testing

[Darko Volaric]

What are you basing that theory on?

On Thu, Dec 29, 2016 at 10:29 PM, Dominique Pellé  wrote:

> Richard Hipp  wrote:
>
> > On 12/29/16, Bob Friesenhahn  wrote:
> >> Is there a way to know how well cachegrind CPU
> >> cycles map to real-world CPU usage?
> >
> > Not that I know of.  If you have any suggestions, please speak up.
>
> The 'stat' command of Linux perf tool would be a good
> candidate to replace cachgrind stats. It's much faster
> than cachegrind. I suppose that it also gives more realistic
> results. See:
>
> https://perf.wiki.kernel.org/index.php/Tutorial#Counting_with_perf_stat
>
> Dominique
> ___
> 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] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

[Darko Volaric]

Or use a collation instead, although "collate" is an operator it's not
treated as a function:

select 'abc' n union select 'ABC' n order by n collate nocase

On Tue, Dec 27, 2016 at 1:34 AM, Jean-Christophe Deschamps  wrote:

> At 00:45 27/12/2016, you wrote:
>
> The work arounds is using a WITH clause or putting the upper function
>> expression in the output of each select.
>>
>
> Another way to rewrite is to wrap the compound select inside a simple
> outer select:
>
> select n
> from
> (
>   select 'Abc' n
>   union
>   select 'aaa' n
> )
> order by upper(n)
>
>
> ___
> 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] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

[Darko Volaric]

It's not a bug, it's a documented restriction, see the last point below.
The work arounds is using a WITH clause or putting the upper function
expression in the output of each select.

From http://www.sqlite.org/lang_select.html :

Each ORDER BY expression is processed as follows:

   1.

   If the ORDER BY expression is a constant integer K then the expression
   is considered an alias for the K-th column of the result set (columns are
   numbered from left to right starting with 1).
   2.

   If the ORDER BY expression is an identifier that corresponds to the
   alias of one of the output columns, then the expression is considered an
   alias for that column.
   3.

   Otherwise, if the ORDER BY expression is any other expression, it is
   evaluated and the returned value used to order the output rows. If the
   SELECT statement is a simple SELECT, then an ORDER BY may contain any
   arbitrary expressions. However, if the SELECT is a compound SELECT, then
   ORDER BY expressions that are not aliases to output columns must be exactly
   the same as an expression used as an output column.


On Wed, Dec 21, 2016 at 1:27 PM,  wrote:

> > After content filtering, the message was empty
>
> Ok. I try to send plain text only with no attachments...
>
>
> SQLite 3.8.6 2014-08-15 11:46:33
> SQLite 3.11.0
> Android ver. 5.0.2
>
> Dear Sirs,
>
> I have problem with ORDER BY UPPER(...) in conjunction with UNION.
> The following query returns an error  "1st ORDER BY term does not match
> any column in the result set".
>
> SELECT
>  0 as TableType,
>  GroupId as RecordId,
>  Name as Name
>  FROM ProductGroup
> UNION
> SELECT
>  1 as TableType,
>  ProductId as RecordId,
>  Name as Name
>  FROM Product
>  ORDER BY UPPER(Name)
>
> When I replace the UPPER (Name) by Name - everything works correctly.
> Below I attached the database on which the error occurs.
>
> On the bug list, I found only simillar bug:
> http://www.sqlite.org/src/tktview?name=d06a25c844
>
>
> Lukasz Stela
> INSOFT sp. z o.o.
> ___
> 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] Missing source code in 3.14 release

[Darko Volaric]

Though clearly a man committed to open source, it's a bit shocking that Dr
Hipp has not released the source code for that pie.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not true. SQLite doesn't have to discard that information after
performing the substitutions. It can use it later for optimizations.

I know that SQLite allows it. I'm not complaining about anything. Have a
look at my original question, which is, restated:

What is the rationale behind disallowing using aliases in the body of
statements?

I think it's an important question since it's obviously easier (trivial) to
do common sub-expression elimination with aliases and it's clearly easier
for humans to write correct code using them. I'm wondering what the evils
of aliases in bodies are, because I can't think of any.

Maybe its because SQL(ite) always wants to reevaluate expressions wherever
they occur, and the implied semantics of body aliases run counter to this.
If that's the case, my next question would be why that behavior is
desirable.

On Sat, Sep 5, 2015 at 11:55 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 7:07pm, Darko Volaric  wrote:
>
> > That's not a valid reason since it's trivial for SQLite to transform
> > aliases by substituting their names with their definitions. It could be
> > handled in the parser code.
>
> And by doing that you would not get optimization, since SQLite would have
> to work out the value once for the WHERE clause and once for the selected
> value.  Which is what SQLite does now.  Which is what you are complaining
> about in this thread.
>
> Remember: SQLite /does/ do this, even though it's not required by the SQL
> standard.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not a valid reason since it's trivial for SQLite to transform
aliases by substituting their names with their definitions. It could be
handled in the parser code.

Meanwhile it's much harder for a human to do the opposite.

So my question remains: why not have this user friendly feature? What are
the motivations for not having it?

On Sat, Sep 5, 2015 at 9:23 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 5:07pm, Darko Volaric  wrote:
>
> > That's not what I said, why don't you read/quote the whole sentence:
> >
> > "Besides being part of the standard (I assume), what's the rationale for
> > this restriction?"
> >
> > I'm asking why the SQL standard restricts the use of aliases in this way
> > and what the benefit of this restriction is.
>
> I'm sorry.  I did not understand your grammar.  Please put it down to me
> being more used to British English than American English.  I would have
> expected "Apart from being part of the standard ...".
>
> The reason is that the SQL engine has to select the correct rows before it
> has to work out the value of each column in the row.  Consider these
> statements:
>
> SELECT s FROM myTable WHERE l/z < 456;
> SELECT * FROM myTable WHERE l/z < 456;
> SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456;
>
> There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the
> whole table if only two or three are going to satisfy b < 456.  So it does
> the WHERE clause first.  Only once it has picked the right rows does it
> need to pay attention to the bit between SELECT and FROM.  Maybe no rows
> will satisfy the WHERE clause and it won't have to bother at all.  For a
> column alias to be useful in both parts of the clause the syntax might be
> more like
>
> SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) <
> 456;
>
> which is, of course, not valid SQL.
>
> You might also be interested to see whether this works:
>
> SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z
> < 456;
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not what I said, why don't you read/quote the whole sentence:

"Besides being part of the standard (I assume), what's the rationale for
this restriction?"

I'm asking why the SQL standard restricts the use of aliases in this way
and what the benefit of this restriction is.

On Sat, Sep 5, 2015 at 8:49 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 3:19pm, Darko Volaric  wrote:
>
> > Besides being part of the standard (I assume)
>
> You assume incorrectly.  In the classic SQL model, aliases to column names
> are assigned after the results have been returned.  In other words, aliases
> cannot be used in the WHERE clause.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

Besides being part of the standard (I assume), what's the rationale for
this restriction?

It would seem that standard SQL is being willfully less efficient and more
error prone by making the user rewrite expressions.

Isn't this in the same category as manifest typing, where a more liberal
approach is an improvement?


On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp  wrote:

> On 9/4/15, Domingo Alvarez Duarte  wrote:
> >
> > Would be nice to sqlite be able to recognize aliases and also do not call
> > column expressions multiple times.
> >
> > print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
> val
> > from json_tbl  where val = 'the_value_1';"));
> >
>
> This is not valid SQL, actually.  For clarity, here is the (invalid)
> SQL reformatted:
>
>SELECT a+b AS x FROM t1 WHERE x=99;
>
> You are not suppose to be able to access the "x" alias within the WHERE
> clause.
>
> Yes, I know that SQLite allows this.  But it does so grudgingly, for
> historical reasons.  It is technically not valid.  Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
>
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
>
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps.  I'd do so if I could.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Password protection to sqlite3 db file

[Darko Volaric]

Yes. There are free and paid options for this. For instance:

https://www.sqlite.org/see/doc/trunk/www/index.wiki

https://www.zetetic.net/sqlcipher/

On Wed, Sep 2, 2015 at 9:36 AM, techi eth  wrote:

> Hi,
>
> Is it possible to protect sqlite3 db file with password protection in
> Linux.
>
> Techi
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] why I don't get an error ?

[Darko Volaric]

SQLite records have fields that are variable sized and encode type and
length information for each field and no table constraint changes this. The
table constraints only change how some values are interpreted.


On Thu, Aug 27, 2015 at 7:06 AM, Nicolas J?ger 
wrote:

> Hi Darko, Igor and others.
>
>   so the only reason to define datatype in sqlite is for the size on
>   the disk ?
>
>   so why not just only using `BLOB` (excepted for `INTEGER PRIMARY
>   KEY`) ?
>
>   being less persmissive wouldn't make querries run faster ?
>   for example, the comparisons would not have to try to attempt to
>   convert or even check and determine the type of the values.
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] why I don't get an error ?

[Darko Volaric]

Columns do not have a fixed type and will accept any type. It's not a bug,
it's a feature:

http://sqlite.org/datatype3.html

On Wed, Aug 26, 2015 at 8:51 PM, Nicolas J?ger 
wrote:

> Hi,
> I have a table built by:
>
> CREATE TABLE IF NOT EXISTS TAGS (ID INTEGER PRIMARY KEY AUTOINCREMENT,
> NAME TEXT NOT NULL, COUNT INTEGER NOT NULL);
>
> where `COUNT` is an `INTEGER`. I wanted to increment `COUNT` with that
> command :
>
> UPDATE TAGS SET COUNT = 'COUNT + 1' WHERE ID = '666';
>
> but when I looked where `ID` = 666, in the `COUNT` cell I read :
>
> COUNT + 1
>
> my error is obvious, but why sqlite doesn't return an error ? as I
> specified I want an integer, I should not be granted to set the value
> by a string. Or there is some reason for sqlite being permissive ?
>
>
> regards,
>
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Lua inside SQLite

[Darko Volaric]

Wow, what a great feature. This saves me so much hacking!

I planned on using virtual tables but per-query instance creation and
parameter passing was going to be such a mess.

On Sun, Aug 23, 2015 at 1:55 PM, Richard Hipp  wrote:

> On 8/23/15, Abilio Marques  wrote:
> >
> > 1. sqlite3 CLI doesn't seem to "load" the .so easily... The generated so
> is
> > called "lua.ext". If I run "*.load lua.ext*" it claims that there is no
> .so
> > available. I went into renaming the file to lua.so, and ran *.load
> lua.so*
> > ... Got: *Error: Shared object "lua.so.so " not found,
> > required by "sqlite3"* ... then tried with *.load lua* ... I guessed that
> > would work... but got *Error: Shared object "lua.so" not found, required
> by
> > "sqlite3*", even when the file was present at that directory... But then
> I
> > ran *.load ./lua.so* , and even *.load ./lua* ... both loaded without
> > problems... Is that the correct behavior?
>
> Yes. You each have to specify a pathname or else the library must be
> on LD_LIBRARY_PATH.  I think this is a security restriction.  Maybe
> somebody else can shed more light on the matter.
>
> >
> > 2. Which is the convention for the file extension of a loadable module?
> .so
> > and .dll?, or is there any other name used regularly among sqlite3 users?
>
> .so for linux.  .dylib for mac.  .dll for windows.
>
> You can just do ".load ./lua" without the suffix and SQLite will
> supply the correct suffix for your platform.
>
>
> >
> > 3. Lua can return arrays. Also, Lua can return multiple values. Tried to
> > take advantage of those facts, but while reading the SQLite API
> > documentation, found no function where I could map multiple values as the
> > return of a function. Am I wrong? For example:
> >
> > select * from table t where fieldA in (lua('return 1,2,3'));
> >
> > Could be a useful feat in real scripts with real code.
> >
>
> Regular SQL functions always return scalars in SQLite.
>
> See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an
> example of how to implement table-valued functions.  This is a new
> feature so there is no documentation on it yet.  But the example is
> well-commented.  This capability will be in the next release, so
> you'll have to compile from trunk if you want to use it right away -
> it is not found in 3.8.11.1.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Tables and Columns of Database of Whatsapp

[Darko Volaric]

It should be kept in mind that lawyers routinely put things in these
"click-through agreements" that is unenforceable or illegal.

It may be perfectly legal to reverse engineer, for example: ( from
https://www.eff.org/issues/coders/reverse-engineering-faq )

"Courts have found that reverse engineering for interoperability, for
example, can be a fair use."

If it's being used for teaching it can also be fair use: ( from
http://www.teachingcopyright.org/handout/fair-use-faq )

"the court found that the biographer's use was fair, in part because the
biographer's purpose was to educate and inform the public"

Of course this reflects US law but it most likely also applies in Germany,
and is probably even more favorable for fair use there.


On Mon, Jun 29, 2015 at 6:42 AM, John McKown 
wrote:

> On Sun, Jun 28, 2015 at 7:24 AM,  wrote:
>
> > Hi,
> >
> > I teach pupils SQL in school.
> >
> > I want to create exercises about the SQLite database of Whatsapp.
> >
> > Can you tell me the names of tables and the names of columns?
> >
> > For the tables, I'll think of data.
> >
> > Thank you,
> >
> > Bob
> >
>
> I'm going to go a bit sideways on this, I hope it is not objectionable.
> Have you contacted Whatsapp about this? I ask because on their web site at
> https://www.whatsapp.com/legal/, it specifically has legalese saying "
> (iii) you will not attempt to reverse engineer, alter or modify any part of
> the Service;" I am not any kind of a lawyer. But it _might_ be argued
> (similar to Oracle vs. Google on the Java API) that the schema of the
> SQLite data base is "part of the Service" and that, especially by using it
> for teaching purposes, you are "reverse engineering" it. Yes, likely a
> extreme position. But IP lawyers can be sharks. Just myself, personally,
> I'd contact Whatsapp and simply ask permission, perhaps explaining what you
> want to do and why you thought that their DB would be a good teaching
> scenario for your students.
>
> Again, I'm just trying urge caution in today's litigious society. I don't
> mean to imply that you are doing anything illegal or immoral (or fattening
> ).
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> My sister opened a computer store in Hawaii. She sells C shells down by the
> seashore.
> If someone tell you that nothing is impossible:
> Ask him to dribble a football.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Will SQLite break a join query?

[Darko Volaric]

You can get SQLite to show exactly what it's doing using "explain", see
http://sqlite.org/opcode.html

On Thu, Jun 25, 2015 at 10:16 PM, Sairam Gaddam 
wrote:

> I know that SQLite will perform some internal select statements when
> executing a join query but will it break a join query and execute in parts
> ???
>
> For example if I join 10 tables, will it perform join of 8 and 2 tables at
> once and in turn join the result of those two joins?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] A story of breaking the encryption of a SQLite database

[Darko Volaric]

That has nothing to do with SQLite or encryption. It's basically saying "I
broke into Fort Knox - I found the key under the mat."

On Sun, Jun 21, 2015 at 10:26 AM, Simon Slavin  wrote:

> For those of you who might be interested in a high-tech attempt at busting
> SQLCipher encryption:
>
>  >
>
> Please note that the breaking of the encryption was not done by examining
> the database itself.  SQLiteCipher is not part of SQLite, it's just one of
> a number of encryption systems available.  This article should not be taken
> as indicating that SQLite encryption, /per se/, is poor or easy to break.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Mozilla wiki 'avoid SQLite'

[Darko Volaric]

The irony of your comment (which I entirely agree with) is that because
SQLite (and similar) does so much incredibly important stuff for you, it
ends up being difficult for programmers to use it, especially the lower
level the programming is. They don't understand the model of how the system
works, and how its meant to be used. Quite often they think they can put a
value in a field, and all will be well, because that's what SQL promises.
They don't think about transactions, normalization, referential integrity
or anything else, because they don't need those features. But unfortunately
if you don't then SQL will perform poorly (or incorrectly) because it isn't
a simple system to store data.

For example, I remember encountering a system where the programmers tracked
a trade though various states. Each time it changed states the system would
move (ie, insert then delete) the record from one table, into a nearly
identical table (without a transaction). They essentially used the table to
denote the state. Might make sense in an imperative model, where buffers
are common, but not at all in SQL.

All they're saying here is stop misusing SQLite: if a child cuts itself
with a knife, take away the knife.

On Sun, Jun 14, 2015 at 1:19 PM, Richard Hipp  wrote:

> On 6/14/15, Scott Hess  wrote:
> > SQLite essentially gives you a set of
> > b-trees with syntactic sugar over them,
>
> SQL (and I speak in general terms here, not just of SQLite) provides
> way more than syntactic sugar over b-trees.  The syntactic sugar over
> b-trees part is the *least* of the many advantages of SQL.  Other more
> important features include:
>
> (1) Transactions.  Atomic updates with the option to ROLLBACK at any time.
>
> (2) Data abstraction.  The schema concisely and accurately describes
> the data format in a way that is portable across systems and across
> time.  Content stored in an SQL database is far, far more likely to be
> accessible 25 years in the future.  It is also far more likely to be
> accessible today by other applications written in different
> programming languages or implemented on obscure and/or experimental
> hardware or operating systems.
>
> (3) Declarative Programming.  With SQL, the programmer asks the
> machine a question and lets the query planner figure out an
> appropriate algorithm.  A few lines of query text replace hundreds or
> thousands of lines of procedural code needed to implement that query.
> If performance problems are encountered, they can usually be remedied
> by CREATE INDEX, and without changing a single line of code - the
> query procedures devised by the query planner shift automatically.
>
> To be sure, many programmers end up using SQL as a glorified key-value
> store.  You can actually find programs that do "SELECT * FROM table
> WHERE pk=?" on two or three tables and then implement a join as
> procedural code in the application.  Maybe this is what they were
> taught.  Or maybe they never really understood the concept of a join.
> I dunno.  But for whatever reason, you do see a lot of people treating
> SQL as little more than an interface or wire protocol for talking to a
> btree, and thereby bypassing 95% of the goodness of SQL.   But just
> because a lot of programmers do this, does not mean it is right.
>
> Alexander Lloyd (on the Google Spanner team) says that "NoSQL
> databases ... enforce a broadly applied premature optimization on the
> entire system."  What Alexander means is that there are some
> specialized cases where NoSQL-type solutions are required for
> performance, but that those situations are the exception rather than
> the rule.   It is better to use the powerful abstractions provided by
> SQL to build a reliable system, then go back and optimize any
> performance bottlenecks on a case-by-case basis.  Alexander's remarks
> implicitly but clearly reference Don Knuth's assertion that premature
> optimization is the root of all evil.
>
> Another way of thinking about the difference between SQL and NoSQL is
> by analogy to C versus assembly language.  There was a huge debate in
> the software industry in the 1980s about whether applications should
> be coded in assembly or C.  Hand-coded assembly is theoretically
> faster than machine-code generated by a C compiler (or at least it was
> in the 80s - that point is debatable now).  But in practice, programs
> written in C tended to perform better than those written in assembly.
> The reason is that C being higher level enabled programmers to spend
> less time fiddling with bits and more time developing better
> algorithms.  C enables a programmer to spend more time "heads-up"
> thinking about solving the users problem and less them "heads-down"
> worrying about the details of the implementation.  The SQL vs. NoSQL
> situation is quite similar.  You can, in theory, always run faster
> with NoSQL storage.  But in practice, SQL is so much more powerful
> that applications coded using SQL tend to perform better, be more
> 

[sqlite] Optimizer and redundant joins

[Darko Volaric]

I think without your actual schema and query plan it might be hard to tell,
but generally the optimizer is not a panacea, it's limited in what it can
divine from your queries.

On Tue, Jun 9, 2015 at 10:52 AM, giles burgess  wrote:

> Hi, I have a question about the SQLite query optimizer.
>
> I have a SQLite embedded database with a star-like schema, with the wrinkle
> that each record in the fact table can map to a range of records in each
> dimension table. This is represented in the fact table as min and max
> rowids for each dimension.
>
> This is not a particularly friendly physical schema to query directly, so I
> have a view that dereferences the rowid foreign keys and also expands the
> dimension ranges into separate rows. The multiplication factor for all
> dimensions can get quite large, with a single physical fact record mapping
> to thousands of records in the view.
>
> Looking at the query plan, it appears that a select * on this view gets
> executed as nested scans over all the dimension tables.
>
> Obviously this isn't very fast, but I would expect that if I perform a
> projection on this view that contains only a subset of the dimension field
> values, eg:
>
> select distinct dimension1_value from friendly_view;
>
> the query optimizer ought to be able to avoid the joins with the tables for
> dimension2, dimension3 etc since they don't contribute to the result. But
> according to the planner it's scanning over all dimension tables
> regardless.
>
> Is this expected? Is there some way to define the view so that the
> optimizer can avoid these redundant joins? Or do I need to always
> explicitly limit the joins to only the tables that are needed?
>
>
> Thanks,
>
> Giles Burgess
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

Yes, the relational model is the key, that is my point. The SQL language is
an entirely arbitrary syntax applied to it. You don't need it to work a
relational database, just like you don't have to program in C to write a
program for a typical processor.

I don't care about how many applications have a performance problem with
SQL generation. *My* application has a problem with it.

I also don't care about Richard's query. *My* queries are much easier to
form without contorting them into a SQL query.

This is the entire point of my changes: avoid arbitrary bottlenecks and
conventions that get in the way of performance and ease of use. It's not
useful for your average person or typical application, but it's extremely
useful for people who want to get the benefit of the database engine
encapsulated within it.


On Fri, Jun 5, 2015 at 2:10 PM, James K. Lowden 
wrote:

> On Thu, 4 Jun 2015 15:11:55 -0700
> Darko Volaric  wrote:
>
> > Are you seriously saying that that SQL syntax is friendly? How can you
> > defend SQL syntax other than on grounds of history or
> > standardization?
>
> The first and best defense of SQL is that it has at least some
> basis in the relational model.  It expresses relational project,
> select, join, union, and intersection directly, and with contortions
> relational division.  Like Algol-60, it's an improvement on its
> predecessors and on many of its successors.
>
> > If you're more comfortable and familiar with JSON
> > the yes it is easier and you can avoid an unnecessary conversion step.
>
> I wonder how many applications you've profiled for which SQL generation
> and parsing were a significant share of the run time.  Usually once the
> data are of any appreciable size I/O becomes the most important
> component.  That's why modern DBMSs have so many features to minimize
> I/O.
>
> While you're working on your new syntax, I hope you'll keep Richard's
> query in mind.  SQL, verbose as it is, is pretty clean compared to
> most ORM syntaxes I've seen.  A syntax that can express his query more
> succinctly and is also "better" along the lines you describe would be
> an achievement.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

There's a bit of confusion as to what I'm actually proposing. I can't reply
to everyone so I'll just post the APIs and/or patches when they're done and
we can argue those on their merits.

On Thu, Jun 4, 2015 at 5:03 PM, Darko Volaric  wrote:

> Well, I've been using SQL for about 30 years so I'm unlikely to change my
> view, but I think you bring up a much more important point: instead of
> arguing online I should get back to work!
>
>
> On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp  wrote:
>
>> On 6/4/15, Darko Volaric  wrote:
>> >
>> > What is motivating this for me is that I generate many unique queries
>> in my
>> > code for almost any operation. Converting those to SQL is error prone
>> and
>> > uses a lot of memory compared to the operation involved. The database
>> > engine is so fast and efficient yet I'm wasting resources making SQL!
>> >
>>
>> You are welcomed to go off and try to come up with a new and better
>> interface.  That's the beauty of open-source.  Maybe you will come up
>> with some new and innovative ideas that will change the industry!
>> It's happened before!
>>
>> I just want to ensure that if, after working on your new approach for
>> a while, you eventually decide that SQL isn't quite as bad a language
>> as you originally thought it was, that you don't come back and say I
>> didn't warn you.
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] User-defined types

[Darko Volaric]

Well, I've been using SQL for about 30 years so I'm unlikely to change my
view, but I think you bring up a much more important point: instead of
arguing online I should get back to work!


On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp  wrote:

> On 6/4/15, Darko Volaric  wrote:
> >
> > What is motivating this for me is that I generate many unique queries in
> my
> > code for almost any operation. Converting those to SQL is error prone and
> > uses a lot of memory compared to the operation involved. The database
> > engine is so fast and efficient yet I'm wasting resources making SQL!
> >
>
> You are welcomed to go off and try to come up with a new and better
> interface.  That's the beauty of open-source.  Maybe you will come up
> with some new and innovative ideas that will change the industry!
> It's happened before!
>
> I just want to ensure that if, after working on your new approach for
> a while, you eventually decide that SQL isn't quite as bad a language
> as you originally thought it was, that you don't come back and say I
> didn't warn you.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

I now regret using JSON as an example since everyone wants me to convert
SQL to JSON for them now, but my point isn't any particular notation, I
want an API of sorts instead of a notation or syntax. Then you can adapt
anything you like and make it efficient with the platform you're using. So
for example you send a native, binary JavaScript object (or record,
whatever its called) as your query instead of SQL text.

What is motivating this for me is that I generate many unique queries in my
code for almost any operation. Converting those to SQL is error prone and
uses a lot of memory compared to the operation involved. The database
engine is so fast and efficient yet I'm wasting resources making SQL!

I just want to skip that SQL bottleneck, because it has no technical
justification other than "standardization" and pass my query straight
through.


On Thu, Jun 4, 2015 at 3:13 PM, R.Smith  wrote:

>
>
> On 2015-06-04 11:16 PM, Darko Volaric wrote:
>
>> My point about JSON, etc is that there is no reason not to use that as a
>> query language if that makes it easier. If your system is efficient with
>> JSON, why not accept a query that is formatted as JSON? It's not
>> semantically different to SQL syntax. Here's an example (with a roughly
>> JSON notation):
>>
>> {
>>operation: "insert"
>>table: "blah"
>>columns: ["a", "b", "c"]
>>values: [1.3, 2.0, 3.1]
>>on-conflict: "replace"
>> }
>>
>
> It's an interesting idea and I for one am willing to entertain the
> thought, but I'm having difficulty seeing the "simpler" and "easier" things
> you claim, or the memory saving for that matter.
>
> Just take the above JSON query and consider that in SQL that would simply
> look like:
>
> REPLACE INTO blah (a,b,c) VALUES (1.3, 2.0, 3.1);
>
> If we have to open a pole on which version seems simpler or use less
> memory, the result would probably be indecisive if not plainly favouring
> the latter.
>
> I am willing to learn though, for instance, how do you see this next query
> represented in the JSON way?:
>
> INSERT INTO blah (1,b,c) VALUES
> (1.1, 2.2, 3.3),
> (3.1, 3.2, 3.4),
> (5.1, 4.2, 3.5),
> (7.1, 5.2, 3.6);
>
>
> Or maybe this one:
>
> SELECT MAX(A.Code), MAX(A.Name), B.Age, MAX(B.LastEditedDate) AS LastDT
>   FROM CodeNames AS A
>   LEFT JOIN Codehist AS B ON A.Code = B.Code AND B.Age > 30
>  WHERE A.Name LIKE 'SomeVal%'
>  GROUP BY B.Age
>  ORDER BY LastDT DESC
>  LIMIT 50;
>
> (I'll forgo the "Having" clause for simplicity).
>
> I'm finding it difficult to imagine a better layout for that query in a
> JSON (or any other Markup-based) document - but I am quite willing (and
> even interested) to be shown a way that makes more sense and satisfies the
> claims of simplicity and memory efficiency.
>
> Once a layout is found that works, I imagine it would be a whole other can
> of spaghetti to make any program author the syntax sensibly, but that is a
> worthy bridge to cross once the first question is answered well.
>
> Alternatively, you might be able to show how the other notation might ease
> the query-planner's work, or how it might help any other SQL process work
> better or faster. Some significant improvement in functionality or
> efficiency will make a much stronger case than "It's easier to compose".
>
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

I'm saying that SQL is alien to the platform it's being used on and native
is better. I'm trying to make a general point (in vain it seems), I don't
use JSON.

On Thu, Jun 4, 2015 at 2:46 PM, Simon Slavin  wrote:

>
> On 4 Jun 2015, at 10:16pm, Darko Volaric  wrote:
>
> > Here's an example (with a roughly
> > JSON notation):
> >
> > {
> >  operation: "insert"
> >  table: "blah"
> >  columns: ["a", "b", "c"]
> >  values: [1.3, 2.0, 3.1]
> >  on-conflict: "replace"
> > }
> >
> > That is equivalent to an INSERT SQL statement, but why form that SQL
> > string, possibly using memory and time, when your system can spit out
> JSON
> > (or whatever) effortlessly?
>
> Why invent a new nonstandard notation for database operations when you
> have SQL ?
>
> Given your JSON expression above it's easy to write code which turns the
> JSON into a SQL command.  So just do that (either outside SQLite or by
> creating a loadable external function for SQLite) and then you can use
> SQLite exactly as it is without having to keep modifying your project every
> time the developer releases a bug-fix.
>
> The hard work in creating a fork is not in the initial work but in the
> maintenance every time the main project gets updated.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

Yes, you can do that but I'm trying to remove steps and conversions, not
add more. My point is that a more native interface is better than SQL.

Yes it's basic, but as I said, a first step. I'm making changes to one
part, making changes to another part makes no difference. The fork has been
forked, the die is cast.

Actually, I'm focusing on my own work which require all these things one
way or another.

On Thu, Jun 4, 2015 at 2:30 PM, Nico Williams  wrote:

> On Thu, Jun 04, 2015 at 02:16:22PM -0700, Darko Volaric wrote:
> > {
> >   operation: "insert"
> >   table: "blah"
> >   columns: ["a", "b", "c"]
> >   values: [1.3, 2.0, 3.1]
> >   on-conflict: "replace"
> > }
>
> I do this all the time.  It's trivial enough to generate SQL from that
> sort of thing.  If you have an AST then you can trivially map the
> AST<->a JSON/XML/ASN.1/whatever schema.
>
> But I don't think ease of alternative representation is the winning
> argument for wanting the engine core to use an AST.  It's only
> convenient.
>
> The winning argument is that working with an AST makes some tasks easy
> that are otherwise hard (e.g., common sub-expression elimination).
>
> >  [...]  Why are people who come from the websphere
> > learning SQL syntax? [...]
>
> Because it's standard.
>
> > The feature I'm working on now, as a first step, basically feeds the
> parser
> > tokens so I don't have to generate a query string. [...]
>
> That seems rather basic, not really good enough.  It must save some
> allocations.  But is it worth forking SQLite3 for this?!  Whatever you
> do with a fork, it's got to be worth it.  Forking is quite hard, so
> every change has got to be worth the effort.
>
> Switching to an AST is going to require more allocations (and much more
> developer effort), that's for sure.  But then, this is in statement
> compilation, which should not be the most critical component.
>
> Anyways, this is all very far afield from UDTs.  If you want to fork to
> add UDTs, focus on that first.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

Are you seriously saying that that SQL syntax is friendly? How can you
defend SQL syntax other than on grounds of history or standardization? If
you're more comfortable and familiar with JSON the yes it is easier and you
can avoid an unnecessary conversion step.

If you're using JavaScript you'd send JS objects (ie binary equivalent of
JSON) to the parser rather than SQL since it's frictionless and binary to
binary. Doing that is safer and more efficient, not to mention the savings
in programming and bugs from the SQL generation, which is the biggest win
of all.

On Thu, Jun 4, 2015 at 2:26 PM, Richard Hipp  wrote:

> On 6/4/15, Darko Volaric  wrote:
> > My point about JSON, etc is that there is no reason not to use that as a
> > query language if that makes it easier. If your system is efficient with
> > JSON, why not accept a query that is formatted as JSON? It's not
> > semantically different to SQL syntax. Here's an example (with a roughly
> > JSON notation):
> >
> > {
> >   operation: "insert"
> >   table: "blah"
> >   columns: ["a", "b", "c"]
> >   values: [1.3, 2.0, 3.1]
> >   on-conflict: "replace"
> > }
> >
> > That is equivalent to an INSERT SQL statement, but why form that SQL
> > string, possibly using memory and time, when your system can spit out
> JSON
> > (or whatever) effortlessly?
>
> What is the JSON equivalent to the query shown below?  Can you
> honestly say that the JSON equivalent (whatever it looks like) is
> somehow easier to generate, read, parse, and/or debug than the SQL?
>
> SELECT
>   sp.name, st.bug_name,
>   (SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),
>   (SELECT debian_cve.bug FROM debian_cve
> WHERE debian_cve.bug_name = st.bug_name
> ORDER BY debian_cve.bug),
>   sp.release,
>   sp.subrelease,
>   sp.version,
>   (SELECT pn.fixed_version FROM package_notes AS pn
> WHERE pn.bug_name = st.bug_name
>   AND pn.package = sp.name
>   AND(pn.release = sp.release OR (pn.release = '' AND
> fixed_version != ''))),
>   st.vulnerable,
>   st.urgency,
>   (SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),
>   (SELECT comment FROM package_notes_nodsa AS nd
> WHERE nd.package = sp.name AND nd.release = sp.release
>   AND nd.bug_name = st.bug_name) AS nodsa
> FROM
>source_package_status AS st,
>source_packages AS sp, bugs
> WHERE
>sp.rowid = st.package
>AND st.bug_name = bugs.name
>AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
>AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
> 'jessie'
>   OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
> ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

My point about JSON, etc is that there is no reason not to use that as a
query language if that makes it easier. If your system is efficient with
JSON, why not accept a query that is formatted as JSON? It's not
semantically different to SQL syntax. Here's an example (with a roughly
JSON notation):

{
  operation: "insert"
  table: "blah"
  columns: ["a", "b", "c"]
  values: [1.3, 2.0, 3.1]
  on-conflict: "replace"
}

That is equivalent to an INSERT SQL statement, but why form that SQL
string, possibly using memory and time, when your system can spit out JSON
(or whatever) effortlessly? Why are people who come from the websphere
learning SQL syntax? It has no magic, the magic is in what it means, which
anyone can understand (tables, columns, joins, search criteria). The syntax
is completely arbitrary, from the 70's or 80's and probably ultimately
inspired by COBOL. There is of course a lot existing information based
around SQL syntax, but most people want to insert some data and do fairly
straight forward queries on it. SQL is probably mostly confusing to them.

The feature I'm working on now, as a first step, basically feeds the parser
tokens so I don't have to generate a query string. Even that gives me a big
saving (mostly in memory), without changing the syntax or introducing
subtle bugs. The next step is "rationalize" the syntax progressively so
that the sequence of tokens I need to pass is closer to the representation
I use internally (in my code). This is the least impact approach I think.

You could insert yourself into any point in the SQL to bytecodes process.
If you wanted to fully support XML queries or whatever that could be a part
of the process, whereby appropriate function calls are generated (say into
your XML query library), or virtual table instance are created or the query
is transformed appropriately.



On Thu, Jun 4, 2015 at 12:05 PM, Nico Williams 
wrote:

> On Thu, Jun 04, 2015 at 11:45:28AM -0700, Darko Volaric wrote:
> > Which sort of leads me to my next feature, which is bypassing the SQL
> > language. [...]
>
> I like SQL, but sure, if the compiler worked by first parsing into an
> AST, and if the AST were enough of an interface (versioned, though not
> necessarily backward-compatible between versions), then one could:
>
>  - write different front-end languages (though an AST isn't needed for
>this: you can always generate SQL)
>
>  - write powerful macro languages
>
>  - write alternative/additional optimizers (and linters, syntax
>highlighters, ...) that work at the AST level
>
> If the VDBE bytecode were also a versioned interface then one could
> write peep-hole optimizers as well.
>
> One might even want to generate IR code for LLVM, or use a JIT-er,
> though for SQL I don't think that would pay off.  I suspect that most of
> the CPU cycles go to data-intensive tasks such as I/O, cache thrashing,
> and encoding/decoding.  I'd be much more interested in SQLite4 being
> finished than an LLVM backend for SQLite3, and I'd be very interested in
> seeing if word-optimized variable-length encoding would have better
> performance than byte-optimized variable-length encoding.  The point
> though is that using an AST would make the system more modular.
>
> >[...]. Why use that crusty old syntax when it's equally expressible in
> > JSON, XML or something else. Again I see it just as an API, [...]
>
> Now I'm confused.  JSON and XML are not query languages.  There exist
> query languages for them (e.g., XPath/XSLT for XML).
>
> I suppose you might have meant that SQL itself is a data representation
> language like JSON and XML are, and it is (data being expressed as
> INSERT .. VALUES ..; statements).
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

Which sort of leads me to my next feature, which is bypassing the SQL
language. Why use that crusty old syntax when it's equally expressible in
JSON, XML or something else. Again I see it just as an API, callable by
whatever parser you want, or none at all, if your code generates queries
directly.

I'll definitely release some patches when it's done. Maybe I'll call it
"ite" - SQLite without the SQL.

On Thu, Jun 4, 2015 at 11:23 AM, Nico Williams 
wrote:

> On Wed, Jun 03, 2015 at 06:04:29PM -0700, Darko Volaric wrote:
> > Yep, references a another one. Just like the functions, you have to join
> on
> > the user type information, add it to constraints, etc.
>
> Once you're extending SQLite3 proper the referential integrity problem
> goes away (being no different than the "problem" SQLite3 has of tracking
> value types internally).
>
> > In my case I'm already modifying and maintaining my own version of
> SQLite.
> > My project is basically a database with a lot of extensions. Submitting
> > patches is not an issue. The last time I brought these ideas up I was
> > practically chased off by a mob waving pitchforks and torches. Apparently
> > almost no-one thinks user defined types is a good idea so there is no
> point
> > sharing it. I don't expect anyone to help me maintain the code. The
> > critical parts of SQLite (like the record read/write) are very stable and
> > updates hardly ever affect me.
>
> I'm not at all sure that UDTs are a bad idea.  I do want JSON support,
> preferably using jq for expressing transformations (or merely addressing
> specific values in a JSON text) of JSON texts.  And I'd like bigint (and
> bigfloat?) support.  Those two types should be enough for easily dealing
> with a great many needs for UDTs without necessarily having support for
> arbitrary UDTs.  A bitstring type would also be convenient for things
> like IP addresses and CIDR.
>
> Given that SQLite3 is in the public domain, you're not obliged to share
> your development.  But who knows, in spite of all the reasons that using
> your "fork" would not be advisable, you might succeed in forming a
> community of users.  So unless you have reasons not to share your work,
> I'd encourage you to share it.  If you do share it, you'll want to give
> it a distinct name (it wouldn't be SQLite3, would it), and you'll want
> to very careful whose patches you accept: if you want to ever be able to
> contribute your changes back into the mainline, you'll need to be able
> to show that each contribution is permitted by the contributor's
> employer and so on.
>
> I have a long wishlist of features or changes that I don't think the
> SQLite3 developers are going to be very interested in at this time.  You
> might not be either, but if there was a community of external developers
> that could pool its resources to make contributions that might be
> welcomed by the SQLite3 developers...
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

That's an entirely valid point, but didn't come up in the discussions if
memory serves. It was the "you don't know what you're doing and don't
understand databases" which I thought was an odd response, but that's all
irrelevant.

I agree that feature bloat is not a good idea (hello, PgSQL!) and I don't
want to add features but rather programming interfaces. I don't make
extensive changes to the code at all. I basically try to provide hooks in
the code that don't actually change any functionality.

For instance my UDTs design just adds a few bits to the record header that
associates a small, unsigned integer with a stored field, nothing more.
These bits have no impact on any code that is not aware of them. It was the
most minimal design I could think of.

Similarly with the VM. I don't add opcodes nor do I change the form or
semantics of the existing codes, but I do sneak "links" into unused opcode
parameters which are interpreted by my own code, kind of like running a VM
of my own alongside.

In other places I just use private functions as if they were public APIs.

I think this is the sort of approach is beneficial to SQLite, especially
since it's an embedded database and it's very natural to want to extend it
and intertwine it with your own code, if you need more than the stock
functionality. I also understand that it makes the job of the developers
harder and that they have no reason to put time into hooks or APIs used by
a relative minority.

I suspect most people who need extensions are like me and want something
very particular and mix in their own proprietary code. For instance, I
think programming in C is like a visiting a dentist who doesn't use
lidocaine, so my actual functionality is in a completely different language.

On Wed, Jun 3, 2015 at 11:52 PM, Dominique Devienne 
wrote:

> On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric  wrote:
>
> > In my case I'm already modifying and maintaining my own version of
> SQLite.
> > [...]. The last time I brought these ideas up I was
> > practically chased off by a mob waving pitchforks and torches. Apparently
> > almost no-one thinks user defined types is a good idea so there is no
> point
> > sharing it. I don't expect anyone to help me maintain the code.
>
>
> FWIW,  I think UDTs are a great idea. But also
> - optional static typing of columns;
> - checksums of blocks;-
> - blob two-tier storage (a la Oracle);
> - native indexing of virtual table;
> - native JSON support;
> - etc...
>
> Yes, the community, just like the authors, of SQLite have a strong bias
> against changes and to keep SQLite "lite".
>
> And can be brutal in how they say it (or ignore it) when someone rants
> about his pet-peeves, or try to push forward his wish list (including me
> above).
>
> But remember that SQLite didn't have FKs for a long time. Didn't have CTE.
> Both of which are major enhancements. So there's hope long term IMHO :).
>
> Now unlike most (including me again), you go further and actually code it
> up apparently. That's great. But it's hard to fork SQLite and get any
> traction given the fast-paced refactoring/optimization the main code goes
> through. And also UDTs can have widespread side effects within SQLite, hard
> to gauge w/o having the whole code-base and design in ones head like DRH.
> Might be good enough for you, but not for the high quality standards which
> is a hallmark of SQLite IMHO. All I can suggest is continue communicating
> and perhaps also OSS your changes on GitHub or similar, and you may get
> help somehow.
>
> I suspect (hope really) first-class UDTs in SQLite (as Nico calls them)
> haven't been dismissed, and it's more a question of finding the time and
> funding to do them right, i.e. in a "lite" way that doesn't adversely
> affect SQLite if you don't use them, and thoroughly tested as usual. My
> $0.02. --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SourceForge seems to have grabbed the "sqlite" project there

[Darko Volaric]

Sourceforge is rapidly digging its own grave with its awful behavior. It's
hardly "taken control" of the project, it's just another fork, essentially.

SQLite isn't even copyrighted and has no licence, so no problem there.
Using the SQLite trademark might be a problem though.

On Wed, Jun 3, 2015 at 5:12 PM, Stephen Chrzanowski 
wrote:

> I can see why they're doing it as well.  Some people have different
> preferences to grab the repo.  I have a script running on one of my linux
> boxes that checks periodically (Once a week?  Once a month?  I can't
> remember) for all links on the SQLite download page.  If I don't have the
> URL or file sitting locally, I download it.
>
> "Eventually" I'm going to automate a process to
> - Automatically upload the amalgamation source code into my VCS (Which no
> one uses anymore, I can't get hold of the original developers to see if I
> can have the source code, and the VCS is 100% Win32)
> - compile the amalgamation and upload the DLL to the VCS
>
> I'm sure there are services out there that has ties directly into github
> and/or sourceforge that'll do certain requests to do automatic "stuff" with
> whatever has changed.  I think Jenkins has some kind of tie in, but I've
> never used it directly.
>
> On Wed, Jun 3, 2015 at 4:52 PM, Simon Slavin  wrote:
>
> >
> > Don't worry about it.  As the page says, the SourceForge page on SQLite
> is
> > just a mirror rather than being the real page to coordinate and
> distribute
> > SQLite.  You can see the laziness involved in their mirror, since
> although
> > the page has the URL you supplied and says just 'SQLite' for its title,
> the
> > default download is
> >
> > sqlite-dll-win32-x86-3081002.zip
> >
> > which is by no means the entirety of SQLite.  I'm guessing it has
> > auto-configured to show the file most often downloaded.
> >
> > They're doing something the license for SQLite says they can do.  But
> they
> > don't pretend to be the proper SQLite page and they do link to SQLite's
> > proper site.  I don't see them doing much harm.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

Yep, references a another one. Just like the functions, you have to join on
the user type information, add it to constraints, etc.

In my case I'm already modifying and maintaining my own version of SQLite.
My project is basically a database with a lot of extensions. Submitting
patches is not an issue. The last time I brought these ideas up I was
practically chased off by a mob waving pitchforks and torches. Apparently
almost no-one thinks user defined types is a good idea so there is no point
sharing it. I don't expect anyone to help me maintain the code. The
critical parts of SQLite (like the record read/write) are very stable and
updates hardly ever affect me.

Regarding PgSQL, an advantage of encoding your own binary types is that you
can copy them straight into your code and execute with them directly - I
use the same encoding/data structures throughout and they serve my code and
requirements instead of the database's or its API.

PgSQL is also a poor fit for me because it's huge and assumes a (huge)
server, I'm running on small nodes with relatively little memory.

On Wed, Jun 3, 2015 at 4:26 PM, Nico Williams  wrote:

> On Wed, Jun 03, 2015 at 03:55:04PM -0700, Darko Volaric wrote:
> > I've tackled this problem from a couple of different angles. My goal was
> to
> > allow arbitrary user defined types, based on the builtin types
> (essentially
> > subtypes of the existing types), with a minimum of work and minimum
> > disruption of the normal/existing use of the database and API.
> >
> > The approaches I considered were:
>
> A UDT can't be first-class without modifying SQLite3.  Without
> first-class UDT support the application has to be responsible to some
> degree for adhering to whatever encoding conventions it chooses to use.
>
> User-defined functions, collations, and virtual tables can be used to
> move some of the burden from the application to the UDFs and VTs, but
> you can't move all of it (e.g., because whatever SQLite3 type you use to
> encode UDTs will often have other uses, leading to an aliasing problem
> that requires the application to avoid it).
>
> > - encoding the user type codes for each data column in a separate column
> > dedicated to the purpose. This is a low impact but cumbersome, for
> instance
> > using a function that interprets the user type would have to have the
> user
> > type passed in for each argument, along with the actual data.
>
> It's cumbersome because it creates a referential integrity problem.
>
> > - modifying the data file format to carry user type information. There is
> > space in the record/row header where you can encode this information in a
> > backwards compatible way, but the source code for data record access is
> not
> > friendly, basically a dense blob of code with a lot of integer literals
> > which are all very important, but it's hard to be sure what they entail
> and
> > that you haven't introduced a subtle bug and ultimately data corruption.
> > Additionally the user type would have to be passed around internally -
> for
> > example in the sqlite3_value object - and tracking down all of those
> > reliably is a bit of work.
>
> Right, you'd basically be talking about adding new first-class types to
> SQLite3.  That's quite an understaking and not for the faint of heart.
> Even if you tackle this, chances are it'd be very difficult to get the
> SQLite3 dev team to accept the changes -- one would be forking SQLite3,
> and that requires serious (read: lots of experienced software engineer
> time) effort to develop and maintain.
>
> > - using blobs. Although using text representation is friendly when
> looking
> > at the data with standard tools, it's slower and takes up more memory in
> > various places. I found that encoding some user types as blobs with a
> type
> > marker at their start (a single byte with extensions) and interpreting
> them
> > was a simple and low impact approach. [...]
>
> Encoding as text or blobs is about your only realistic option.  Enums
> can be encoded as numbers too, as can small bitsets.
>
> > The first option isn't very practical. The second option is the fastest
> and
> > most robust solution and my long term approach which I will be going back
> > to after development has progressed a bit more. Currently I'm using the
> > third approach as an interim measure. I'm supporting arbitrary prec ints
> > and reals, arrays and tuples and other types this way.
>
> At that point why not just switch to Postgres?
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Recursive CTE optimization

[Darko Volaric]

I think the difficulty here is that the optimizer is oriented toward the
"low level" and mainly concerned with choosing indexes, processing order
etc  (see https://www.sqlite.org/optoverview.html ) and has sort of a
narrow view of the task.

Approaching it from the other end and breaking down the SQL statement into
a set of constraints over a set of relations linked by those constraints
you can use ideas from constraint programming, such as your optimisation
which is constraint propagation: adding redundant constraints that are
logically implied by the existing constraints; the redundant constraints
don't change the meaning of the query but serve to "prune" the query space
thus reducing the work.

This sort of approach could be implemented as a SQL to SQL transformation
and would complement the existing query planner. Maybe there's a tool out
there already which processes standard SQL in this way?

On Wed, May 27, 2015 at 3:31 AM, Matthias-Christian Ott 
wrote:

> I have the following (simplified and generalized) schema and query:
>
> CREATE TABLE nodes (
>   id INTEGER NOT NULL PRIMARY KEY
> );
>
> CREATE TABLE edges (
>   a INTEGER NOT NULL REFERENCES nodes (id),
>   b INTEGER NOT NULL REFERENCES nodes (id),
>   PRIMARY KEY (a, b)
> );
>
> WITH reachable (a, b) AS (
>   SELECT a, b FROM edges
>   UNION ALL
>   SELECT reachable.a AS a, edges.b AS b
>   FROM reachable
>   INNER JOIN edges ON reachable.b = edges.a
> )
> SELECT nodes.id, reachable.b
> FROM nodes
> INNER JOIN reachable ON a = id
> WHERE id = 1;
>
> The query plan for the query looks like this:
>
> 2|0|0|SCAN TABLE edges
> 3|0|0|SCAN TABLE reachable
> 3|1|1|SEARCH TABLE edges USING COVERING INDEX sqlite_autoindex_edges_1
> (a=?)
> 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)
> 0|0|0|SEARCH TABLE nodes USING INTEGER PRIMARY KEY (rowid=?)
> 0|1|1|SCAN SUBQUERY 1
>
> The query first computes the reachable relation for all nodes and then
> filters out the reachable nodes of node 1 which is clearly inefficient.
>
> If I'm not mistaken the query could be optimized to:
>
> WITH reachable (a, b) AS (
>   SELECT a, b FROM edges WHERE a = 1
>   UNION ALL
>   SELECT reachable.a AS a, edges.b AS b
>   FROM reachable
>   INNER JOIN edges ON reachable.b = edges.a
> )
> SELECT nodes.id, reachable.b
> FROM nodes
> INNER JOIN reachable ON a = id
> WHERE id = 1;
>
> The query plan for the optimized query looks like this:
>
> 2|0|0|SEARCH TABLE edges USING COVERING INDEX sqlite_autoindex_edges_1
> (a=?)
> 3|0|0|SCAN TABLE reachable
> 3|1|1|SEARCH TABLE edges USING COVERING INDEX sqlite_autoindex_edges_1
> (a=?)
> 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)
> 0|0|0|SEARCH TABLE nodes USING INTEGER PRIMARY KEY (rowid=?)
> 0|1|1|SCAN SUBQUERY 1
>
> It looks like the optimization can be generalized because you can pull
> the selection into the initial select query of the CTE. Is there a
> chance that SQLite could perform this optimization in one of the next
> releases?
>
> I could tolerate with repeating the parameter but I want to create a
> view out of it, so it's not really an option. Are there any alternatives
> if the optimization will not be automated?
>
> Moreover, "COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)" looks
> wrong, shouldn't it read "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE
> (UNION)"?
>
> - Matthias-Christian
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] User-defined types

[Darko Volaric]

I've tackled this problem from a couple of different angles. My goal was to
allow arbitrary user defined types, based on the builtin types (essentially
subtypes of the existing types), with a minimum of work and minimum
disruption of the normal/existing use of the database and API.

The approaches I considered were:

- encoding the user type codes for each data column in a separate column
dedicated to the purpose. This is a low impact but cumbersome, for instance
using a function that interprets the user type would have to have the user
type passed in for each argument, along with the actual data.

- modifying the data file format to carry user type information. There is
space in the record/row header where you can encode this information in a
backwards compatible way, but the source code for data record access is not
friendly, basically a dense blob of code with a lot of integer literals
which are all very important, but it's hard to be sure what they entail and
that you haven't introduced a subtle bug and ultimately data corruption.
Additionally the user type would have to be passed around internally - for
example in the sqlite3_value object - and tracking down all of those
reliably is a bit of work.

- using blobs. Although using text representation is friendly when looking
at the data with standard tools, it's slower and takes up more memory in
various places. I found that encoding some user types as blobs with a type
marker at their start (a single byte with extensions) and interpreting them
was a simple and low impact approach. I also split the standard integer
type four ways (negative and positive, odd and even) to get the scalar user
types I needed. User defined functions and collations need to be defined
for interpreting these user types of course.

The first option isn't very practical. The second option is the fastest and
most robust solution and my long term approach which I will be going back
to after development has progressed a bit more. Currently I'm using the
third approach as an interim measure. I'm supporting arbitrary prec ints
and reals, arrays and tuples and other types this way.



On Wed, May 27, 2015 at 3:48 AM, Matthias-Christian Ott 
wrote:

> I want to define user-defined types, i.e. types not SQLite has not
> built-in and make sure that I didn't overlook something. Is it correct
> that values of user-defined types should be stored as text and have a
> collation defined if there is an order relation for the type if the type
> cannot be represented as a subset of integer or float?
>
> Example:
> Suppose I want to store arbitrary precision integers in SQLite. I would
> create a column with text affinity, (uniquely) serialize and deserialize
> the integers to text (e.g. by converting them into decimal
> representation) and define and declare a collation that deserializes the
> texts to arbitrary integers and compares the integers.
>
> Is there another way to define user-defined types despite this method
> and virtual tables?
>
> - Matthias-Christian
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Download Image from Internet and add directly to Database

[Darko Volaric]

Use incremental blob I/O, see

http://sqlite.org/c3ref/blob_open.html

Note that you'll need to use INSERT or UPDATE SQL to allocate or expand
space for the blob in the database field.


On Thu, May 21, 2015 at 7:17 PM, reddy ykk  wrote:

> Hi All,
>
> I am facing a problem in downloading the image files (from internet) and
> writing the same to SQLite Database (blob Data).
>
> 1. I dont want to encode the binary data. I just want to write as it is. So
> that i can fetch the image data later and draw on my screen.
> 2. The image data has '00' after 4 bytes. So, only first 4 bytes are being
> written to Database as blob data.
>
> Please help me in overcoming these problems.
> I am using VC++ as the coding language. I am using SQLite 3 methods for the
> same.
>
> Any sample code is highly apreciated
>
> Thanks and Regards,
> Kishor Reddy
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I think that IR would be something like first order predicate logic, to
which SQL and the relational calculus is closely related. Now that we have
WITH and recursive queries, you've basically got a bottom-up evaluation of
the declarative subset of Prolog (if you ignore issues relating to logic
variables).

On Wed, Feb 18, 2015 at 1:13 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 02/18/2015 11:43 AM, Richard Hipp wrote:
> > but I think the truth is we are probably stuck with SQL for a while
> > yet.
>
> In theory there could be an intermediate representation form (like
> compilers do) that is publicly available, with the (now optional) SQL
> part producing IR, as well as any other query language
> implementations.  The LLVM project is an example of doing a design
> like this.
>
> In practise the result wouldn't be very Lite, and would constrain
> future development.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlTlAGsACgkQmOOfHg372QRP+ACgqoP3Ss8ZgvO95M8IVHhLRDbo
> itEAoMhJKWIKiiYjsAqNUGl/cpv/e+fp
> =Z+np
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I agree with you, and am not suggesting getting rid of it, but rather
making it "pluggable" like many parts of the back end.

Right now, roughly speaking, I'm doing:  logical form -> SQL -> execution
of logical form, and SQL seems to me to just be an arbitrary hoop that I
have to jump through, complicating things along the way.

Obviously this is not terribly mainstream, but I think would greatly
improve the usefulness and structure of SQLite.

Just my 2 cents on the subject...

On Wed, Feb 18, 2015 at 11:43 AM, Richard Hipp  wrote:

> On 2/18/15, Darko Volaric  wrote:
> > The only thing I'd change about SQLite is the SQL bit.
>
> Most people agree that the SQL language is a bit of a mess.  But so is
> the Qwerty keyboard layout.  The problem is that the improvement you
> get by moving to something else is less than the pain of making the
> move.  Everybody agrees that Dvorak is a better keyboard layout, and
> yet nobody uses Dvorak even though it has been widely available on
> computer keyboards since the Apple-II,  Not intending to rain on your
> parade, but I think the truth is we are probably stuck with SQL for a
> while yet.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I second this notion. I think SQLite is uniquely suited to server based
applications of all kinds. Its light footprint and the fact that it's a
library rather than a full system gives it a flexibility and raw
performance that other systems cannot. We use it at the core of each node
in a distributed and parallel system.

When using SQLite the architecture of your database system is not
preordained by designers who could not foresee novel designs and
approaches. SQLite is like a systems programing language: It's lean and
mean and a powerful tool that gives full control to the systems designer
and programmer.

The only thing I'd change about SQLite is the SQL bit. To me it's an
anachronism and a mess and needs to be factored further out of the SQLite
core, with a more rigorous formalism providing an interface (with an
exposed and supported API) to the database engine, but at a higher level
than say the virtual machine.

On Wed, Feb 18, 2015 at 9:12 AM, Marcus Grimm 
wrote:

> We use sqlite as the db engine inside a server application
> with a number of clients that connect to the server.
> Sqlite works just beatiful here and I wish these statements
> "sqlite shall not be used for client/server things" would be
> worded less generally. In fact when we mention sqlite as our
> db engine customer point to this restriction and we run into
> an excuse sort of arguments.
> On the bottom line: Sqlite CAN very well serve as the DB
> engine for client/server applications, it just depend how
> the api is used.
>
> Marcus
>
> Am 2015-02-18 15:34, schrieb Richard Hipp:
>
>> In a feeble effort to do "marketing", I have revised the "Appropriate
>> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
>> Things" and "Edge of the Network" above the break.  See:
>>
>> https://www.sqlite.org/whentouse.html
>>
>> Please be my "focus group", and provide feedback, comments,
>> suggestions, and/or criticism about the revised document.   Send your
>> remarks back to this mailing list, or directly to me at the email in
>> the signature.
>>
>> Thank you for your help.
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] ordinary CTE containing sum()

[Darko Volaric]

I'm interested in this too (since I rely on it working). According to the
documentation this should be perfectly legal and seems like an arbitrary
limitation (or a bug). It says:

"An ordinary common table expression works as if it were a view that exists
for the duration of a single statement."

But it's not the case here.

On Sun, Feb 8, 2015 at 6:57 AM, Doug Currie  wrote:

> >
> > > In response to this SO question:
> > >
> > >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> > >
> > > I tried to formulate a query without temp tables using an ordinary
> > > CTE, but received an error "misuse of aggregate: sum()".
> >
>
> tonypdmtr  on SO posted
> a
> CTE solution; it is something like this, which works for me:
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id
>union values (NULL, 0))
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s where S_id is not NULL;
>
> But my question remains, why is the UNION necessary in the  CTE?
>
> why doesn't this work? ...
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id)
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s;
>
> e
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[Darko Volaric]

You wrote:

"From the tone of your last post (if I am reading correctly) I understand
that you have your mind set on finding a way that you have thought about a
lot lying in your bed late at night, you have rolled it around in your head
and you just "feel" this should be doable and will be so elegant a
solution. "

That references only me, in a condescending way, without referencing the
actual issue. That's ad-hominem. You've made it clear by your posts you
don't understand the SQLite issue I'm talking about so I'm not discussing
that further, as I have already said. If you want to address anything to do
with me, then email me directly at take it off list, so as to stop wasting
other people's time.

On Wed, Nov 26, 2014 at 8:20 AM, RSmith <rsm...@rsweb.co.za> wrote:

>
> On 2014/11/26 15:58, Darko Volaric wrote:
>
>> I'm not looking for confirmation of ideas, on the contrary, people seem
>> to want to push their own ideas about a database should be used and how I'm
>> not using it correctly, when that is irrlevent to the issue I'm discussing.
>> Maybe more focus on the technical facts and less on divining what I think
>> at night in bed and try framing your arguments based on those technical
>> facts rather than ad-hominem attacks.
>>
>
> I am sincerely sorry if you construed my reply as anything remotely ad
> hominem - it surely wasn't, and it's not pushing ideas on you, it's giving
> advice or alternates because your ideas are short-sighted and hard to
> implement. We are however very nice on here since sharing knowledge is a
> passion, and that's why we say nice things like we understand how it feels
> to have ideas and then offer some advice... not because we spend our days
> divining about your life.  You throw everyone's advice back in their faces
> and are arrogant about it - well, even to that we are still nice and
> willing to answer the questions - kindly accept it in that spirit.
>
>
>> But I don't intend to argue this point any further since I'm merely
>> looking for advice about how the database engine is implemented, not about
>> how it's used or how I'm using it.
>>
>
> Ok, sticking to the facts, the database engine is implemented in a way
> that makes your original suggested options pan out like this:
>1 - very easy for the engine, very work intensive for you.
>2 - still easy for the engine, although it will lose most RDBMS
> querying value, and still cumbersome for you (Maybe best to use Virtual
> tables to implement this), and
>3 - impossible without a dedicated fork, and even then very difficult.
>
> I wouldn't personally pick any of those, but if those were the only
> options in life and I had to pick one, knowing SQLite, I'd probably lean
> more towards option 2 than the others.
>
> SQLite is "loosely typed", but it is still typed, and the typing mechanism
> is not open to the API and every one of the hundreds of core functions in
> SQLite are specifically coded to dance with those few primary types.
> Adding/Altering it must always be the very last option on any list.
>
>
> Good luck,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[Darko Volaric]

The person I replied to cross posted, not I, and I didn't realise this
before I replied to his cross post and the I couldn't change it then, so
maybe take that up with him.

I'm not confusing anything. You, and the other posters, are confusing the
representation of values and the concrete value type with the logical types
declared for column domains. I understand this might be a subtle
distinction to some.

But I don't intend to argue this point any further since I'm merely looking
for advice about how the database engine is implemented, not about how it's
used or how I'm using it.

I'm not looking for confirmation of ideas, on the contrary, people seem to
want to push their own ideas about a database should be used and how I'm
not using it correctly, when that is irrlevent to the issue I'm discussing.
Maybe more focus on the technical facts and less on divining what I think
at night in bed and try framing your arguments based on those technical
facts rather than ad-hominem attacks.



On Wed, Nov 26, 2014 at 4:53 AM, RSmith <rsm...@rsweb.co.za> wrote:

> Hi Darko,
>
> Firstly, kindly keep this to the sqlite-users forum and not on the dev
> forum (the devs read this too, the difference being simply that this one
> exists to help you, the other one is to discuss development stuff, not to
> help anyone).
>
> Secondly, you are confusing two things. You are arguing about what /CAN/
> be done while James tried to explain what /Should/ be done. Nobody can
> argue with the possibility, but from experience we know that you are going
> down a road that will bite you later.
>
> That said, the best way to achieve what you wish to do is by adding
> columns that explain the domain of a type on a per-row basis. The fact that
> inside of SQLite an Integer can be stored in different ways is simply a
> code/data/space optimisation for SQLite, it is transparent to the user and
> transparent to the SQL - it is in no way intended as a data-feature or
> extension of the SQL Language. If you try to build on top of that (or maybe
> not on top of it, but in the same way) more arbitrary sub-types, yes of
> course it is possible, but it may leave you in a World of hurt in terms of
> using the DB as a relational database system for other normal activities.
>
> In contrast, if this is intended more as an application file-format than
> RDBMS, sure, make it better but, as others pointed out, still try to do so
> in a way that does not require maintaining a software fork. This is good
> advice and in no way forbidding or prescribing, just letting you know what
> will make life easier for you in the long run.
>
> From the tone of your last post (if I am reading correctly) I understand
> that you have your mind set on finding a way that you have thought about a
> lot lying in your bed late at night, you have rolled it around in your head
> and you just "feel" this should be doable and will be so elegant a
> solution. We all go through that. You probably came here looking for
> confirmation of your ideas rather than advice, but many of the people here
> have already done what you are trying now, this is why they know and this
> is why this list is useful.
>
> Y'know, use it, don't use it, etc. :)
>
>
> On 2014/11/26 13:22, Darko Volaric wrote:
>
>> That's not true. A 64 bit floating point number and an 64 bit integer can
>> be represented by the exact same 64 bit pattern, and no-one would suggest
>> they're the same value. You can have those two differently typed although
>> identical bit values in the same SQLite column. The data identifying the
>> representation of those datums is integral to that value and doesn't
>> belong
>> in a different column as you say. Other SQL based systems also allow
>> differently represented ("typed") values to appear in the same column.
>>
>> The domain of a column can logically incorporate these different kinds of
>> values by introducing the concept of subtype. For instance in SQLite there
>> are something like 6 different representations of integer of between 1 and
>> 64 bits. Each one of those is a different "type" in the sense of having a
>> different representation due to the number of bits they use and being
>> limited to a different set of numbers. A 1 bit integer is a subtype of a
>> 64
>> bit integer since the set of numbers allowed by the 1 bit integer is {0,
>> 1}
>> are contained with the set of numbers allowed by 64 bit integers, ie
>> {0..2^64-1}. If the column has a domain of integer then all these values
>> are valid since they're all integers. There is no logical or formal reason
>> why this can't be extended further to allow arbitrary subtypes according
>> to
>> the user's wishes.
>

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[Darko Volaric]

That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented ("typed") values to appear in the same column.

The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different "type" in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.

You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.

There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.

On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden <jklow...@schemamania.org>
wrote:

> On Tue, 25 Nov 2014 04:41:51 -0800
> Darko Volaric <li...@darko.org> wrote:
>
> > I have a need to implement per-value custom typing in SQLite. In my
> > case I may have the integer value 1234 appear in two rows in the same
> > column, but they are different in that they have completely different
> > meanings because they have different types although they have the
> > same representation in the database.
>
> A column is a type.  Two values of the same type *must* represent the
> same thing.  That's how SQL works.
>
> When you say 1234 may represent two different things in the same
> column, what you really mean is that 1234 doesn't stand for the whole
> thing, that the column isn't the whole type (as you conceive it).
> That's fine; you need another column to discriminate between them, to
> capture that "whole type".  Each column-component of that type is itself
> a type, just as a street name is part of a postal address.
>
> As a practical example of what that's true, consider this list:
>
> anything
> 
> cat
> green
> jogging
>
> We can sort that "anything" column as *strings*, but what if each one is
> 1234 in the database?  Even if they are different values, how do you
> compare green to jogging?  Which one comes first?  How should a join
> work?
>
> If that doesn't convince you, please understand I'm not expressing an
> opinion.  I'm pointing out a basic tenet of the relational model.  I
> can recommend good references on the subject.
>
> If you represent your things, whatever they are, in the model according
> to its rules, you will find you don't need to extend the type system.
> Nothing good awaits you if you attempt to extend it without first
> understanding it.
>
> HTH.
>
> --jkl
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

[Darko Volaric]

There's nothing vague about select statements, they're logical formulas
involving the data in your database and as exact as any other programming
language, albeit in a very different domain. Relational databases are based
on first order predicate logic and have operations that are are rigorously
defined.

You're obviously thinking about the limitations of the database's optimizer
and the opaque way it does its work, but just as a real time or embedded
programmer may have qualms about (the uncertainty of) the code produced by
a compiler and prefer hand rolled assembler, you want more control and
certainty over what the database does. The problem in both cases is that
the programmer assumes they can do better than the machine, which is highly
questionable given the current state of the art in both cases.

You can give the optimiser tips about how to go about its work and that can
help improve efficiency, but you are better off trusting the optimiser
rather than trying to fight it. If there is an actual problem with the
efficiency of a well written select, it's almost always rooted in the
design of the database schema, especially to much or too little
de-normalization.

On Sat, Nov 8, 2014 at 7:45 AM, Tristan Van Berkom  wrote:

> On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote:
> > On Sat, 08 Nov 2014 22:55:46 +0900
> > Tristan Van Berkom  wrote:
> >
> > > So I would have to say, the "right way to do it" is the most efficient
> > > way, the one which provides SQLite with the best indications of how
> > > to plot an efficient query plan.
> >
> > Keith is suggesting that the right way to do it is neither "any way that
> > works" nor necessarily "whatever is fastest" but "the clearest
> > formulation of the query".  Clarity has the salutary property of being
> > most likely to be correct (because understood by the human) and stands
> > a better than fair chance of being executed efficiently (because it
> > translates easily to a good query plan).
> >
> > Most of time -- not every time, but most of the time -- indexes
> > and table design matter much more to efficient execution than query
> > syntax. When a clearly expressed query is not executed efficiently in
> > the presence of useful indexes, and especially when a slightly different
> > one does, that's usually considered a defect of the query planner.
>
> I see what you're saying, as I mentioned in the initial email I do
> consider myself to be a relative newbie, and the majority of work
> I've been doing with SQL (until this year) has been working on
> optimizing existing schemas/queries for embedded use.
>
> While I do understand SQL as a functional language, most functional
> programming I've done still has rather explicit syntax/rules, so I get
> particularly uncomfortable with writing vague statements, such as
> JOIN tableA, tableB WHERE ... without being exactly specific on the
> heart/nature of the join which is happening.
>
> Also what I've found in my limited experience is that nesting SELECT
> statements, at least in SQLite, completely throws off the planner,
> as in it has no opportunity to make a good guess and has to deal
> with the result set of a nested SELECT as an opaque table, which
> is generally not an indexed table (or perhaps it is, but I wouldnt
> know because those indexes don't seem to be declarative in any way).
>
> So indeed, I am not comfortable with 'leaving it up to chance',
> and if there is a way to get higher specificity, I try to achieve that.
>
> In any case, I do have queries which work well at this point, but
> posted this question to the list in the hope I could find the right
> specificity for the given query - it's not a very big deal, I'll
> probably just stick with what works and try to fix it in the places
> where profiling reveals that I'm doing something wrong.
>
> Cheers,
> -Tristan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users