Re: [sqlite] SQLite 3.20.0 postponed
> 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] Database version
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] Trigger firing order
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
[sqlite] Evaluation of SQLite SEE
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
[sqlite] Surprising result from changes() after constraint violation
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
Re: [sqlite] Trigger firing order
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
Re: [sqlite] Surprising result from changes() after constraint violation
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