Re: [sqlite] Understanding the WITH clause

[Luuk]

On 15-6-2019 18:24, Sam Carleton wrote:

Pax vobiscum,
Sam Carleton


I had to pull up a dictionary to know what Pax (=Peace) you are talking 
about..


(https://en.wikipedia.org/wiki/Pax_(liturgy))

"In Christian liturgy 
, "the /*Pax*/" is an 
abbreviation of the Latin 
salutations "/pax 
vobis/" ("peace to you") or "/pax vobiscum/" ("peace with you"), which 
are used in the Catholic 
Mass 
and Lutheran 
Divine Service 
."


I do think (without any expressed opinion here) that there's is olny a 
small percentage of people who did know withoutout searching the 
internet (of the dictionary at home).


Greetings from Holland,
"*Holland*is a region and former province on the western coast of the 
Netherlands . The name 
/Holland/is also frequently used informally to refer to the whole of the 
country of the Netherlands. This usage is commonly accepted in other 
countries,^[2]  
and sometimes employed by the Dutch themselves.^[2] 
 However, some 
in the Netherlands, particularly those from regions outside Holland, may 
find it undesirable^[2] 
 or 
misrepresentative to use the term for the whole country." 
(https://en.wikipedia.org/wiki/Holland)


Yes, i'm one of the people who do find Holland misrepresentative for 
'the Netherlands' (https://en.wikipedia.org/wiki/Netherlands)


But, since drifting away from topic,

Houdoe (https://en.wikipedia.org/wiki/Houdoe)





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

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

[Simon Slavin]

On 15 Jun 2019, at 2:42pm, Dan Kaminsky  wrote:

[about the 32676 hard limit on the number of columns in a table]

> I spent quite a bit of time hacking large column support into a working
> Python pipeline, and I'd prefer never to run that in production.
> Converting this compile time variable into a runtime knob would be
> appreciated.

Something you should know about SQLite is that if it needs to find the 2001st 
column of a row it has to read the entire row from storage and walk through all 
2000 columns before the one it wants.  So both storing and recalling data in 
wide tables is very inefficient.

To compensate for this problem, which occurs in many SQL engines, you can turn 
your wide table into a thin table (key/value pairs) by adding the column name 
to the key.  SQLite is extremely good at handling tall thin tables.

If you think about what you're really doing with your data you're find that 
although it's classically drawn out as a huge 2D grid, the data is closer to an 
Entity–attribute–value model, and more suited to a tall table with a long key.

There's no reason why your library should have to know how SQLite is used to 
store data.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding the WITH clause

[Sam Carleton]

I am working with a nested set tree and reading through Joe Celko's trees
and hierarchies in sql source book to refresh the old brain.  In section
4.7.1 of the book he has an example that uses a do while statement.  It
looks like this concept can be implemented in SQLite by using the WITH
clause, but I don't fully understand it.  Might someone be able to help me
translate this into SQLite code?  Here is the beginnings of it:

while exists (select * from frammis where wgt = 0)
do update frammis
  set wgt =  ...
end while;

The ... is the following case statement, unless I am mistaken this is
pretty straight forward to convert, my big question is the code above.

case
when 0 < all ( select c.wgt
   from frammis as c
   left outer join frammis as b on b.lft = (select max(S.lft)
from frammis as s where c.lft > s.lft and c.lft < s.rgt)
   where b.part = frammis.part )
then ( select coalesce (sum(c.wgt*c.qty), b.wgt)
from frammis as c
left outer join frammis as b on b.lft = (select max(S.lft) from
frammis as s where c.lft > s.lft and c.lft < s.rgt)
  where b.part =  frammis.part )
else frammis.wgt
end


Pax vobiscum,
Sam Carleton
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

[Dan Kaminsky]

Sqlite3 has something of a normative declaration in its source code:

*
** This is the maximum number of
**
*** Columns in a table
*** Columns in an index
*** Columns in a view
*** Terms in the SET clause of an UPDATE statement
*** Terms in the result set of a SELECT statement
*** Terms in the GROUP BY or ORDER BY clauses of a SELECT statement.
*** Terms in the VALUES clause of an INSERT statement
**
** The hard upper limit here is 32676.  Most database people will
** tell you that in a well-normalized database, you usually should
** not have more than a dozen or so columns in any table.  And if
** that is the case, there is no point in having more than a few
** dozen values in any of the other situations described above.
*/
#ifndef SQLITE_MAX_COLUMN
# define SQLITE_MAX_COLUMN 2000
#endif

All software has constraints fundamental to its problem set and particular
implementation, and I would not mail you simply for deciding an
architecture on behalf of your users.  You do that every day.

However, conditions have changed since (I expect) this design was
specified.  One of the more useful and usable packages for Natural Language
Processing, Magnitude[1], leverages SQLite to efficiently handle the real
valued but entirely abstract collections of numbers -- vector spaces --
that modern machine learning depends on.

Traditionally, word2vec and other language vector approaches have fit their
models into tens or hundreds of dimensions.  New work is acquiring the
context of each word -- "Huntington", preceeded by "Mr.", vs. "Huntington"
preceeded by "Street" or followed by Disease.  These phrase or sentence
embeddings (as they're called) can take quite a bit more space to represent
necessary context.  They may take 3072 columns (in the case of Magnitude's
ELMo records[2]) or even 4096 columns (in the case of InferSent's sentence
embeddings[3]).

That is exceeding SQLite's limits.

These spaces, though abstract, have become the most powerful way we know to
not merely represent, but actually discover relationships.  This is simply
a new data domain that is the *input* to what eventually becomes the
familiarly normalizable relational domain.  It's different, but it's not
"wrong".  If it's behavior you can support -- as your 32K hard limit
implies -- it would certainly be helpful for these user scenarios.

I spent quite a bit of time hacking large column support into a working
Python pipeline, and I'd prefer never to run that in production.
Converting this compile time variable into a runtime knob would be
appreciated.

--Dan

[1] https://github.com/plasticityai/magnitude
[2] https://github.com/plasticityai/magnitude/blob/master/ELMo.md
[3] https://github.com/plasticityai/magnitude/blob/master/ELMo.md
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding the WITH clause

[Sam Carleton]

I have kept reading and the next section of the book does away with the
update and simply creates a recursive function.  The goal of the function
is to determine the 'weight' of a part by adding up all the subassemblies *
qty.  At first I thought this might be easier to convert into SQLite, but
upon deeper reading of the SQLite doc's, I am getting the impression this
is NOT the type of thing the WITH statement can do.  Below is the stored
proc from the book, followed by the SQL to create the table and populate
it.

Can this be converted, if so, how?

CREATE FUNCTION WgtCalc(IN MY_PART CHAR(2))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
-- RECURSIVE FUNCTION
RETURN
(select coalesce(sum(Subassemblies.qty *
case when Subassemblies.lft + 1 = Subassemblies.rgt
 then subassemblies.wgt
 else WgtCalc(Subassemblies.part)
 end), max(Assemblies.wgt))
 from Frammis as Assemblies
left join Frammis as Subassemblies on
Assemblies.lft < Subassemblies.lft and
Assemblies.rgt > Subassemblies.rgt and
not exists (
select *
from frammis
where lft < Subassemblies.lft and
  lft > Assemblies.lft and
  rgt > Subassemblies.rgt and
  rgt < Assemblies.rgt)
where Assemblies.part = MY_PART);

-- --

create table frammis
(
part char,
qty  int,
wgt  int,
lft  int,
rgt  int
);

create unique index frammis_part_uindex
on frammis (part);

INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('a', 1,  0,  1, 28);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('b', 1,  0,  2,  5);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('c', 2,  0,  6, 19);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('d', 2,  0, 20, 27);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('e', 2, 12,  3,  4);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('f', 5,  0,  7, 16);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('g', 2,  6, 17, 18);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('h', 3,  0, 21, 26);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('I', 4,  8,  8,  9);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('j', 1,  0, 10, 15);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('k', 5,  3, 22, 23);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('l', 1,  4, 24, 25);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('m', 2,  7, 11, 12);
INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('n', 3,  2, 13, 14);

Pax vobiscum,
Sam Carleton


On Sat, Jun 15, 2019 at 12:29 PM Sam Carleton  wrote:

> I am working with a nested set tree and reading through Joe Celko's trees
> and hierarchies in sql source book to refresh the old brain.  In section
> 4.7.1 of the book he has an example that uses a do while statement.  It
> looks like this concept can be implemented in SQLite by using the WITH
> clause, but I don't fully understand it.  Might someone be able to help me
> translate this into SQLite code?  Here is the beginnings of it:
>
> while exists (select * from frammis where wgt = 0)
> do update frammis
>   set wgt =  ...
> end while;
>
> The ... is the following case statement, unless I am mistaken this is
> pretty straight forward to convert, my big question is the code above.
>
> case
> when 0 < all ( select c.wgt
>from frammis as c
>left outer join frammis as b on b.lft = (select max(S.lft)
> from frammis as s where c.lft > s.lft and c.lft < s.rgt)
>where b.part = frammis.part )
> then ( select coalesce (sum(c.wgt*c.qty), b.wgt)
> from frammis as c
> left outer join frammis as b on b.lft = (select max(S.lft) from
> frammis as s where c.lft > s.lft and c.lft < s.rgt)
>   where b.part =  frammis.part )
> else frammis.wgt
> end
>
> Pax vobiscum,
> Sam Carleton
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pointer-passing interface, and testing if pointer is correct 'type'....

[dave]

Folks;
 
I have a v-table using the pointer-passing interface, and binding 'null' is
a valid use-case.  However, to wit there is no way to tell on the v-table
implementation side if the sqlite3_value_pointer() returns 'null' because
that is what the user explicitly and validly chose, or because they
specified the wrong 'pointer type string'.  As it is, I can't emit useful
error messages to developers in that case.
 
If there is a method of differentiating 'null because you want it', and
'null because you can't have it', please advise.
 
Outside of that, I would suggest as a future enhancement possibly:
*  int sqlite_isvalid_pointer(sqlite3_value*, const char*)
  returns a 'boolean' indicating it was bound validly or not
or if doing two validations (one for the test, one for the value retrieval)
is unappealing, maybe:
*  void *sqlite3_value_pointer_v2(sqlite3_value*, const char*, int*);
  gets pointer as per usual, and if final parameter is non-null, provide a
'boolean' indicating that it was validly bound.

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

[sqlite] Understanding the WITH clause

[Sam Carleton]

I am working with a nested set tree and reading through Joe Celko's trees
and hierarchies in sql source book to refresh the old brain.  In section
4.7.1 of the book he has an example that uses a do while statement.  It
looks like this concept can be implemented in SQLite by using the WITH
clause, but I don't fully understand it.  Might someone be able to help me
translate this into SQLite code?  Here is the beginnings of it:

while exists (select * from frammis where wgt = 0)
do update frammis
  set wgt =  ...
end while;

The ... is the following case statement, unless I am mistaken this is
pretty straight forward to convert, my big question is the code above.

case
when 0 < all ( select c.wgt
   from frammis as c
   left outer join frammis as b on b.lft = (select max(S.lft)
from frammis as s where c.lft > s.lft and c.lft < s.rgt)
   where b.part = frammis.part )
then ( select coalesce (sum(c.wgt*c.qty), b.wgt)
from frammis as c
left outer join frammis as b on b.lft = (select max(S.lft) from
frammis as s where c.lft > s.lft and c.lft < s.rgt)
  where b.part =  frammis.part )
else frammis.wgt
end

Pax vobiscum,
Sam Carleton
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] demoRead() function in test_demovfs.c not consistent with documentation

[Dan Kennedy]

On 15/6/62 20:08, Dominique Pellé wrote:

Hi

SQLite page https://sqlite.org/c3ref/io_methods.html says
at the bottom:

=== BEGIN QUOTE ===
If xRead() returns SQLITE_IOERR_SHORT_READ it must
also fill in the unread portions of the buffer with zeros. A VFS
that fails to zero-fill short reads might seem to work.
However, failure to zero-fill short reads will eventually lead
to database corruption.
=== END QUOTE ===

Yet, I see that function demoRead() in the demo
VFS example at https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c
does not zero-fill the buffer in case of SQLITE_IOERR_SHORT_READ.

It looks like a bug in the demo code, or is the
SQLite documentation incorrect?



Hi Dominique

I think it's a bug in the demo code. Fixed, along with a problem in the 
xFileControl method, here:


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

I'm not sure that breaking this rule can actually lead to database 
corruption, except in the trivial case where the database is 0 bytes in 
size at the start of the transaction. But it's hard to be 100% sure of 
that, and quite impossible to be sure that SQLite won't change to take 
advantage of this part of the specification in the future. So I guess 
new VFS implementations should zero memory when returning 
SQLITE_IOERR_SHORT_READ.


Regards,

Dan.




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

[sqlite] demoRead() function in test_demovfs.c not consistent with documentation

[Dominique Pellé]

Hi

SQLite page https://sqlite.org/c3ref/io_methods.html says
at the bottom:

=== BEGIN QUOTE ===
If xRead() returns SQLITE_IOERR_SHORT_READ it must
also fill in the unread portions of the buffer with zeros. A VFS
that fails to zero-fill short reads might seem to work.
However, failure to zero-fill short reads will eventually lead
to database corruption.
=== END QUOTE ===

Yet, I see that function demoRead() in the demo
VFS example at https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c
does not zero-fill the buffer in case of SQLITE_IOERR_SHORT_READ.

It looks like a bug in the demo code, or is the
SQLite documentation incorrect?

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

Re: [sqlite] select within transaction

[Roman Fleysher]

Thank you,  Adrian. I think this is reason changes() exist.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Adrian Ho 
Date: 6/15/19 12:25 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API 
(https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Ftclsqlite.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3Dreserved=0),
 then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Jean-Marie CUAZ]

Maybe the core pb under the question asked here is this prior question :

from the standard, is it possible to consider 0.0 as an imprecise 
representation of 0 ?


(if yes, signing 0.0 seems logical to me)

Regards,

-jm


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

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

Re: [sqlite] json path escaping with double quote

[gwenn]

Ok,
I tried to patch this line:

diff --git a/ext/misc/json1.c b/ext/misc/json1.c
index d99d360b2..0bb4e1cee 100644
--- a/ext/misc/json1.c
+++ b/ext/misc/json1.c
@@ -1123,7 +1123,7 @@ static JsonNode *jsonLookupStep(
   u32 iStart, iLabel;
   JsonNode *pNode;
   iStart = jsonParseAddNode(pParse, JSON_OBJECT, 2, 0);
-  iLabel = jsonParseAddNode(pParse, JSON_STRING, i, zPath);
+  iLabel = jsonParseAddNode(pParse, JSON_STRING, nKey, zKey);
   zPath += i;
   pNode = jsonLookupAppend(pParse, zPath, pApnd, pzErr);
   if( pParse->oom ) return 0;

Regards.

On Thu, Jun 13, 2019 at 6:45 PM gwenn  wrote:
>
> Hello,
> With the json1 extension, we can escape special characters like '['
> from being interpreted as an array index by wrapping the path in
> double quotes. But sometimes, it does not work:
>
> sqlite> CREATE TABLE test (data TEXT);
> sqlite> INSERT INTO test (data) VALUES ('{}');
> sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> json(3
> 2));
> sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
> "equity_spot[at_andr]"|32
> sqlite> -- KO: expected equity_spot[at_andr]|32 but got
> "equity_spot[at_andr]"|32
> sqlite> DELETE FROM test;
> sqlite> INSERT INTO test (data) VALUES ('{"equity_spot[at_andr]":34.3}');
> sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> json(3
> 2));
> sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
> equity_spot[at_andr]|32
> sqlite> -- OK: no double quote
>
> I use json_patch as a workaround.
> Is this the expected behaviour ?
> Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Darren Duncan]

On 2019-06-12 6:35 a.m., Richard Hipp wrote:

IEEE754 floating point numbers have separate representations for +0.0
and -0.0.  As currently implemented, SQLite always display both
quantities as just "0.0".

Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
would that create unnecessary confusion?


I would say, either you support IEEE754 floats fully to the standard, or you 
don't pretend to support them and just say you have floats without mentioning 
IEEE754.  Also I say that distinguishing -0.0 and 0.0 is good for those that 
need to know and harmless to those that don't. -- Darren Duncan

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