Re: [sqlite] Foreign keys & TEMPORARY tables.

[Richard Hipp]

On 11/7/16, Smith, Randall  wrote:
> It's quite common to create some kind of large database table or
> constellation of tables that will be used for intermediate work product, and
> which will be deleted when an operation, or the database session, is
> complete.  This kind of thing is an obvious candidate for TEMPORARY tables
> in SQLite, not only because they will be reliably cleaned up, but because
> they can be located in RAM for faster operation.
>
> However, my understanding is that TEMPORARY tables are effectively in their
> own database and (thus?) cannot have foreign key relationships to other
> databases (in this case, the database holding the persistent data).  This
> seems unfortunate since it prevents using TEMPORARY tables if you need or
> want the consistency checking provided by foreign keys, and also seems
> unnecessary since foreign key relationships pointing from a TEMP table to a
> persistent table will never outlive the session, so the argument against
> allowing against foreign keys between databases doesn't (I claim) really
> apply.
>
> Am I getting the situation right?  Is there any way to allow foreign keys
> from TEMP tables to persistent tables, or can this be considered a feature
> request?

Suppose there are two different processes talking to the database,
process A and process B.  The database has some table "xyz" with
primary key "id".

Process A creates a temporary table that REFERENCES the "xyz" table
and inserts some content.  But then process B (who has no way of
seeing the temp table in A or even of knowing that the temp table
exists) does "DELETE FROM xyz".  This breaks the FK constraints in
process A.  But process B has no way of knowing that. And so there is
is no way to enforce an FK constraint between a TEMP table and
ordinary table.

-- 
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] Foreign keys & TEMPORARY tables.

[Smith, Randall]

It's quite common to create some kind of large database table or constellation 
of tables that will be used for intermediate work product, and which will be 
deleted when an operation, or the database session, is complete.  This kind of 
thing is an obvious candidate for TEMPORARY tables in SQLite, not only because 
they will be reliably cleaned up, but because they can be located in RAM for 
faster operation.

However, my understanding is that TEMPORARY tables are effectively in their own 
database and (thus?) cannot have foreign key relationships to other databases 
(in this case, the database holding the persistent data).  This seems 
unfortunate since it prevents using TEMPORARY tables if you need or want the 
consistency checking provided by foreign keys, and also seems unnecessary since 
foreign key relationships pointing from a TEMP table to a persistent table will 
never outlive the session, so the argument against allowing against foreign 
keys between databases doesn't (I claim) really apply.

Am I getting the situation right?  Is there any way to allow foreign keys from 
TEMP tables to persistent tables, or can this be considered a feature request?

Randall Smith
Senior Staff Engineer
Qualcomm, Inc.



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

Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Drago, William @ CSG - NARDA-MITEQ]

I skimmed through this entire thread and didn't see any mention of 
System.Data.SQLite which is the ADO.NET provider for SQLite and 
written/maintained by the authors of SQLite.

http://system.data.sqlite.org/

Perhaps I missed it or perhaps it's not really what you are looking for. I 
thought I should at least mention it.

I use System.Data.SQLite with VEE and C#, and I use xerial/sqlite-jdbc with 
Java.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Thursday, January 07, 2016 7:23 PM
> To: SQLite mailing list 
> Subject: [sqlite] Wish List for 2016: High Level API for Object Oriented
> Interactive Languages
>
> At the command line interface (CLI) in SQLite (and most SQL
> implementations) is an interpreted set at a time language with implicit loops.
>
> Efficient low level languages (such as C) process data a record at a time and
> the existing API is appropriate for them.
>
> Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View or
> a Query all at once as a data set.
> I would count among the OOIL languages: R, Python, Julia Scala,
> MatLab/Octave and APL. In a slightly different category would be Java and C#
> which are object oriented and arguably interpreted, but are not intended to
> be used interactively at a command line with a Read-Evaluate-Print-Loop
> (REPL).
>
> The intent of the higher level API is to improve the reliability of the
> interfaces. The existing SQLite APIs are correct, but hard to use in the sense
> that creating an interface from an OOIL language is more involved than just
> "wrapping" one by one a set of functions. What I am proposing is a second
> set of APIs that when trivially wrapped for use in an OOIL language would
> result in a function that makes sense to an OOIL programmer and interprets
> the SQL statements in a manner consistent with the SQLite CLI (perhaps it
> could even borrow code from the CLI).
>
> I believe R has remarkably good interface packages for SQLite, but that is not
> necessarily the norm across the other OOIL languages.
>
> I am assuming that the higher level API would be hard to use in C because its
> up to the programmer to write the low level code while maintaining a
> complex abstraction in their head (because C is better suited for creating
> abstractions than using them). Header files (.h) would help some but they
> would inflate the size of the code and still be hard for the C programmer to
> keep track of. So, that's why I see the need for a second higher API that
> might be written in C, but would certainly not be used in C!
>
> I am undecided as to whether the higher level API would be useful in Java or
> C#.  Java and C# programmers might not be used to implicit loops and find
> them not worth the trouble; whereas R, Python or Julia programer would
> expect to get an entire table, view or query all at once.
>
> The higher level API would have to be optional, since it would not be
> desirable for a programmer or organization that needs SQLite to run with the
> smallest possible footprint on a phone, tablet or Internet of things
> (IOT) device.
>
> Just a wishlist idea. No rush for me because I am happy in R and will probably
> be moving from SQLite to client server SQL database before I move from R to
> Python, Julia or Java.
>
> Jim Callahan
> Orlando, FL
>
>
>  email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> This
> email has been sent from a virus-free computer protected by Avast.
> www.avast.com
>  email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Re: [sqlite] Backslash problems with json() and json_valid()

[Dominique Devienne]

On Mon, Nov 7, 2016 at 6:49 AM, Øyvind A. Holm  wrote:

> It seems as json() and json_valid() misbehave about unescaped
> backslashes. json() creates invalid JSON, and json_valid() happily
> accepts it:
>
>   $ sqlite3
>   SQLite version 3.15.1 2016-11-04 12:08:49
>   Enter ".help" for usage hints.
>   Connected to a transient in-memory database.
>   Use ".open FILENAME" to reopen on a persistent database.
>   sqlite> select json('" \ "'); -- Creates invalid JSON
>   " \ "
>   sqlite> select json('" \\ "');
>   " \\ "
>   sqlite> select json_valid('" \ "'); -- Accepts invalid JSON
>   1
>   sqlite> select json_quote('\'); -- Works correctly
>   "\\"
>   sqlite>
>
>   $ sqlite3 :memory: "select json('\" \\ \"');"
>   " \ "
>   $ sqlite3 :memory: "select json('\" \\ \"');" | json_verify
>   lexical error: inside a string, '\' occurs before a character which it
>   may not.
>   " \ "
>(right here) --^
>   JSON is invalid
>   $
>
> It's been like this since JSON arriveed in v3.9.0.
>

Just a couple precisions, to help read Øyvind's post:

1) SQLite doesn't do C-style escapes.

From https://www.sqlite.org/lang_expr.html :
  "C-style escapes using the backslash character are not supported because
they are not standard SQL."

2) The last two commands are hard to parse because of the need to do
shell-escaping.

But otherwise I believe he's correct. Only the following chars can follow a
backslash in JSON: u " \ / b f n r t

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