Re: [sqlite] Surprising result from changes() after constraint violation

[Simon Slavin]

On 21 Jul 2017, at 10:50pm, Steven Friedman  wrote:

> sqlite> insert into t2 (c1) values (1);
> Error: foreign key mismatch - "t2" referencing "t1"
> sqlite> select changes();

"changes()" is returning the value of the last /successfull/ command which 
makes changes.  Since the INSERT command changed, the value returned by 
"changes()" was not updated.

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

Re: [sqlite] Trigger firing order

[Jean-Luc Hainaut]

On 21/07/2017 19:00, Simon Slavin wrote:


I’m minded to leave things as they are, with the order undefined.  If you 
really want to trigger a number of different operations in a specific order, 
put those operations all in one trigger, one after another.


Yes, possible now with the reminder of Peter: inserts can be conditional 
as well.



Alternatively, instead of having
...
have

Operation A
Trigger A1 on operation A performs operation B
Trigger B1 on operation B performs operation C
Trigger A3 on operation A performs operation D

That way you’ll know that you’ll get either ABCD or ABDC, but either way C will 
be executed after B.


Right, but not applicable in my special case: B is a global timestamp 
server that delivers unique timestamp Id's that are further used by 
several operations (e.g., closing the current state then creating a new 
current state). So trigger B1 does not know which operation will be 
performed next. Only triggers of type A1 know.



I have a question.  Would you expect to see depth-first or width-first 
recursion, and why ?


I suppose you refer to the order cascading triggers must be fired, as in 
the architecture mentioned above. Am I right?
Quite difficult question, to which I have no general answer (probably, 
there is none). In MY case (automatic management of transaction time 
temporal DB), I would say that a depth-first execution would be the most 
natural.
 If action A triggers actions B1 and B2, and action B1 triggers actions 
C11 and C12, I think that the final state of the data should be easier 
to understand if it results from sequence A.B1.C11.C12.B2, even if B2 
modifies the result of A.B1.C11.C12. I can imagine that the result of 
A.B1.B2.C11.C12 could lead, in some cases, to unexpected final states.
It seems that depth-first order is applied by Oracle (I don't know for 
others), though the protocol is fairly complicated by their "mutating 
table" concept, which often leads to convoluted programming.
The documentation is unclear but it seems to suggest that the 
"follows/precedes" clauses apply to select the next sibling trigger 
after the child triggers have completed. But I may be wrong!


Thanks for the suggestions and discussion

JL

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

[sqlite] Surprising result from changes() after constraint violation

[Steven Friedman]

Running Sqlite 3.19.3, an insert failed because of a constraint violation and 
the return value from changes() was not what I would have expected.  Why did 
"select changes()" return "3" on the second call, even though nothing was 
inserted into the db?

sqlite> pragma foreign_keys=1;
sqlite> create table t1(c1 int);
sqlite> insert into t1 (c1) values (1), (2), (3);
sqlite> select changes();
3
sqlite> select count(1) from t1;
3
sqlite> create table t2 (c1 int references t1(c1) on delete restrict);
sqlite> insert into t2 (c1) values (1);
Error: foreign key mismatch - "t2" referencing "t1"
sqlite> select changes();
3
sqlite> select count(1) from t2;
0

Thank you,

Steve Friedman

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

[sqlite] Evaluation of SQLite SEE

[Adrian Wright]

Hello,

I see that a license is required to download SQLite SEE. Is it possible to
get a temporary license to evaluate? We would like to test the performance
impact of encryption on our application.

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

Re: [sqlite] Trigger firing order

[Jean-Luc Hainaut]

On 21/07/2017 18:13, petern wrote:

a programming pattern that is missing in SQLite trigger body: "if (new.C1

<> old.C1) then ".  It can be simulated for updates ("update
... where ... and new.C1 <> old.C1") but not for inserts ("insert" has no
"where" clause).

Maybe so.  But, INSERT can accept data rows from a SELECT statement which
both does have a WHERE clause and is aware of the trigger body variables.


Quite right! Didn't think of this.  Now, all invidual triggers have been 
gathered into a single one.


Thanks

JL


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

Re: [sqlite] Database version

[Stephen Chrzanowski]

I have a routine that reads the first chunk of any file to see if it
contains the SQLite header.  That way I can be sure that what I'm opening
is at least a SQLite database, I can skip on a Malformed Database error
when trying to open a renamed BMP, and I have the security knowing that if
files are renamed, I can still poke and prod at them.  But whether or not
the application is SUPPOSED to have its fingers on a valid database is a
completely different story.

The only thing I can think of when wanting to know what the file-format ID
is would be to troubleshoot older databases when run against a newer
library.  It could be used as an internal verification of what actual
version of the library was used to write the database, and reference code
they've written to see why something is working or not working in a
particular way.

SQLite has had wrong answers before, to which have been fixed, so if that
ID of the past can be mapped internally to whatever code they have, it'd be
a better foundation to either correct their code, or, go back to a
particular SQLite library version to validate code changes, or what have
you.

On Fri, Jul 21, 2017 at 12:11 PM, Jens Alfke  wrote:

>
> But why should your user care? As a developer I’ve been working with
> SQLite since 2004 and I’ve never needed to know the internal file-format ID
> in a database.
>
> And as a user, I probably use at least 50 apps (on desktop and mobile)
> that use SQLite internally, and it doesn’t matter to me what version they
> use.
>
> —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] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 22 juil. 2017 à 08:14, Gwendal Roué  a écrit :
> 
> Still, I feel that static strings are a weird way to define keys. For 
> example, value subtypes in SQLite have the same requirement of needing 
> "unique subtype identifiers", and those subtypes are, today, ints. Not 
> strings compared with strcmp(). Is there anything special with pointer 
> functions that have them require something different than ints for 
> identifying pointers?

Answering my own question: with int keys, it would be very easy to implement 
"interfaces that provide access to pointers of any type the user wants", that 
Richard does not want to support. Static strings indeed are a way to lock the 
API to "narrowly defined purposes".

OK, I'm good :-) Thanks a lot for your explanations, Richard!

Gwendal Roué

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

Re: [sqlite] UPDATE database using parameters

[Keith Medcalf]

On Friday, 21 July, 2017 20:05, Jens Alfke  said:
>> On Jul 21, 2017, at 1:01 PM, Keith Medcalf 
>> wrote:

>> Just using a web browser has your machine executing god only knows
>> what code generated by god only knows who doing god only knows what
>> to your computer.  Unless you have disabled that, of course.  But
>> that makes the web almost completely unuseable

>Well, JavaScript is sandboxed. And I think most people would take
>issue with the assertion that the web is unusable.

Not very well.  How do you think "drive by downloads" work?  Javascript in 
browsers is the most dangerous thing ever invented!

So, if you have disabled Javascript entirely the Web is *not* almost completely 
unuseable?  You must only visit websites run by the competent, because 90% of 
the web sites visited will be completely blank if Javascript is disabled.  
Google even displays the message "Once you remove all the malware, nothing 
remains".

>> And people who use squirrily quotes should fix their email client …

>“These”?  They're true quotation marks. The straight kind was only
>invented later, for typewriters, just to save a key; they’ve never
>been acceptable in anything but typewritten documents. (The early
>typewriters also didn’t have a “1” because you could use a lowercase
>“l”, or a “0” because you could use an “O”. That got remedied later,
>but they kept the ugly quotes.)

Nonetheless.  If you permit your email client to use squirrily quotes then 
anything that you type in a message cannot be cut and pasted into any "normal" 
software, command prompt, client, or programming language source file without 
having to have the quotes all fixed up.  Same with replacing -- with an em-dash.

And no, the squirrily quotes are typeset quotes.  They are meant for typeset 
publications.  Computer code is not a typesetting application.




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

Re: [sqlite] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 21 juil. 2017 à 18:50, Richard Hipp  a écrit :
> 
> On 7/21/17, Gwendal Roué  wrote:
>> 
>> First, this strcmp() give a lot of work to languages that wrap SQLite and
>> lack support for "static strings".
> 
> But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
> invoked from those languages.  The _pointer() routines are invoked
> from C, and C does easily support string literals that are static
> strings.
> 
> A C-language wrapper around sqlite3_result_pointer() and
> sqlite3_bind_pointer() that interfaces to the non-static-string
> language can simply insert the required static string.
> 
> We do not want the static string to be a parameter to a generic
> higher-level interface.  That defeats the purpose of the static
> string.  Remember, the string is a "pointer type".  We do not want to
> support interfaces that provide access to pointers of any type the
> user wants.  We are not trying to recreate C++ templates or other
> interfaces that work with arbitrary types.  Each use of _pointer() is
> intended to be used for a single narrowly defined purpose.

If I understand you correctly, no wrapper library in a foreign language should 
ever claim "New! Support for pointer functions introduced in SQLite 3.20.0".

Instead, it could claim: "New! Support for SQLite 3.32.0, including the new 
FTS6 full-text engine". The support for this hypothetical FTS6 engine would 
imply the use of pointer functions by the wrapper, but not by its clients. FTS6 
would have its own "narrowly defined purposes" for function pointers.

I see the point, and the fact that you don't want "to support interfaces that 
provide access to pointers of any type the user wants". I don't want to discuss 
that.

Still, I feel that static strings are a weird way to define keys. For example, 
value subtypes in SQLite have the same requirement of needing "unique subtype 
identifiers", and those subtypes are, today, ints. Not strings compared with 
strcmp(). Is there anything special with pointer functions that have them 
require something different than ints for identifying pointers?

Gwendal Roué

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