Re: [sqlite] Boolean values in variable, tclsqlite3
On 19 Jul 2019, at 9:15pm, Gilles Pérez wrote: > Is it possible in Tcl to specify I want a boolean? You don't want a boolean. SQLite doesn't understand booleans. You want integers. Do this Constants: DB_FALSE = 0, DB_TRUE = 1 I would suggest you don't use names like SQLITE_FALSE because the assumption would be that those are defined in a SQLITE library, but you can use other names. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean values in variable, tclsqlite3
2019-07-19 19:29 +02:00, d...@sqlite.org : > On 7/19/19, Gilles Pérez wrote: > > set tvalue true > > This statement sets the TCL variable "tvalue" to the four-character > string "true", not to a boolean true. Is it possible in Tcl to specify I want a boolean? For now, I “filter” true/false values and I mix $ or : notation with literal I build, but it's not that practical. Thank you, Gilles. -- Envoyé de mon téléphone. Excusez la brièveté. Sendita per poŝtelefono, pardonu do la koncizon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Well, yes and no. I see that as more of a generic question of "why is some rogue process accessing and changing your database?" rather than a problem specific to SQLite. If your data needs foreign keys, or some extension like FTS, R-Tree, etc. then you're going to be controlling what's accessing and changing your database and make sure it knows what's in there. If some other process is bludgeoning its way through your data without respect, then that's another whole issue that would be there no matter how you chose to store your data. -Original Message- From: sqlite-users On Behalf Of Richard Damon Sent: Friday, July 19, 2019 2:46 PM To: SQLite mailing list Subject: Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... One big issue is that in general (as I remember right) pragmas generally affect the connection, not the database itself, so shouldn’t change how the schema is interpreted, or another connection (or before issuing the pragma) might interpret things differently and possibly see the database as somehow corrupt or be able to corrupt the database. > On Jul 19, 2019, at 1:44 PM, Thomas Kurz wrote: > > Imho it would be helpful (especially for newbies that don't know the full > history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all > kinds of historical bugs. They might be relevant for existing applications > but in no way for newly created ones. Among the things to consider should be: > > - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) > - enable FOREIGN KEY constraints (I know there is already a pragma, but imho > it should be included) > - strict type enforcement > - disable the use of double quotes for strings > - default to WITHOUT ROWID > > ...and probably many more I don't know about ;-) > > ___ 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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
One big issue is that in general (as I remember right) pragmas generally affect the connection, not the database itself, so shouldn’t change how the schema is interpreted, or another connection (or before issuing the pragma) might interpret things differently and possibly see the database as somehow corrupt or be able to corrupt the database. > On Jul 19, 2019, at 1:44 PM, Thomas Kurz wrote: > > Imho it would be helpful (especially for newbies that don't know the full > history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all > kinds of historical bugs. They might be relevant for existing applications > but in no way for newly created ones. Among the things to consider should be: > > - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) > - enable FOREIGN KEY constraints (I know there is already a pragma, but imho > it should be included) > - strict type enforcement > - disable the use of double quotes for strings > - default to WITHOUT ROWID > > ...and probably many more I don't know about ;-) > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Imho it would be helpful (especially for newbies that don't know the full history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all kinds of historical bugs. They might be relevant for existing applications but in no way for newly created ones. Among the things to consider should be: - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) - enable FOREIGN KEY constraints (I know there is already a pragma, but imho it should be included) - strict type enforcement - disable the use of double quotes for strings - default to WITHOUT ROWID ...and probably many more I don't know about ;-) - Original Message - From: Dominique Devienne To: SQLite mailing list Sent: Friday, July 19, 2019, 10:25:17 Subject: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf wrote: > Except in SQLite where as a documented behavioural anomaly maintained for > backwards compatibility it simply means "UNIQUE" (for ROWID tables). And > UNIQUE indexes may have NULL components. This is because despite your > wishing that your primary key is the primary key, it is not the primary key. > However when WITHOUT ROWID tables were introduced there was no backwards > compatibility issues (they were new after all) then PRIMARY KEY could be > implemented as UNIQUE NOT NULL ... > https://sqlite.org/nulls.html > https://sqlite.org/rowidtable.html > https://sqlite.org/withoutrowid.html > See especially 2 sub 4 in the latter. This whole thread is good material for the new(ish) quirks page IMHO. As Keith points out, most of the material exists in the doc already, but IMHO the quirks page should be the one-stop page to learn about all those historical or by-design particularities of SQLite, with a short explanation and pointers to other doc places with more details. Just my $0.02 of course :) --DD ___ 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] Boolean values in variable, tclsqlite3
On 7/19/19, Gilles Pérez wrote: > set tvalue true This statement sets the TCL variable "tvalue" to the four-character string "true", not to a boolean true. -- 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
Re: [sqlite] Boolean values in variable, tclsqlite3
As the script got removed, here it is:
package require sqlite3
proc main {} {
puts "sqlite3 version: [
exec sqlite3 -version
]"
sqlite3 db :memory:
puts "libsqlite3 version: [
db version
]"
db eval {
CREATE TABLE test (
id INTEGER PRIMARY KEY,
b BOOLEAN NOT NULL DEFAULT false
);
}
db2 eval {
INSERT INTO test (b) VALUES (true);
}
display
db2 eval {
INSERT INTO test (b) VALUES (:tvalue);
}
display
db2 eval "
INSERT INTO test (b) VALUES (false);
"
display
db2 eval {UPDATE test SET b = :fvalue WHERE id = 2}
display
db2 eval {UPDATE test SET b = false WHERE id = 2}
display
}
proc display {} {
db eval {
SELECT * FROM test;
} values {
array unset values {\*}
parray values
puts ""
}
puts ""
}
proc db2 {action req} {
set tvalue true
set fvalue false
puts [string trim $req]
db $action $req
}
main
2019-07-19 17:02 +02:00, sql...@octidi.net :
>
> Hello,
>
> If I run the attached script (results I see in attached text), one can see a
> problem with true and false in variables. If I write true or false directly,
> no problem, sqlite store a 1 or a 0.
>
> But if a put them in a variable, sqlite always store them as strings "true"
> or "false".
>
> How to achieve a correct result with a $value or :value without prefiltering
> values “true” or “false”?
>
> Thank you,
>
> Gilles.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> *Pièces jointes:*
> * results.txt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Boolean values in variable, tclsqlite3
Hello, If I run the attached script (results I see in attached text), one can see a problem with true and false in variables. If I write true or false directly, no problem, sqlite store a 1 or a 0. But if a put them in a variable, sqlite always store them as strings "true" or "false". How to achieve a correct result with a $value or :value without prefiltering values “true” or “false”? Thank you, Gilles. sqlite3 version: 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199aalt1 libsqlite3 version: 3.25.3 INSERT INTO test (b) VALUES (true); values(b) = 1 values(id) = 1 INSERT INTO test (b) VALUES (:tvalue); values(b) = 1 values(id) = 1 values(b) = true values(id) = 2 INSERT INTO test (b) VALUES (false); values(b) = 1 values(id) = 1 values(b) = true values(id) = 2 values(b) = 0 values(id) = 3 UPDATE test SET b = :fvalue WHERE id = 2 values(b) = 1 values(id) = 1 values(b) = false values(id) = 2 values(b) = 0 values(id) = 3 UPDATE test SET b = false WHERE id = 2 values(b) = 1 values(id) = 1 values(b) = 0 values(id) = 2 values(b) = 0 values(id) = 3 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query planner regression with FTS4: slower path is chosen
I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason the planner decides to search non-FTS table first then scan the whole FTS table. Version 3.22.0 is the last unaffected, while issue is still present in HEAD. Probably it has something to do with a fact that, according to EXPLAIN, new version of planner ignores LEFT join and considers it just a JOIN. At least it feels that way, anyway. Test case: CREATE VIRTUAL TABLE search USING FTS4(text); WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 2000 ) INSERT INTO search(docid, text) SELECT x, 'test' || x FROM cnt; CREATE TABLE foo(s_docid integer primary key, bar integer); WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 2000 ) INSERT INTO foo(s_docid, bar) SELECT x, 1 FROM cnt; .timer on -- Fast SELECT COUNT() FROM search LEFT JOIN foo ON s_docid = docid WHERE bar = 1 AND search MATCH 'test*'; -- Fast SELECT COUNT() FROM foo WHERE bar = 1 AND s_docid IN ( SELECT docid FROM search WHERE search MATCH 'test*' ); -- Create index, as some real-life queries use searches by `bar` CREATE INDEX foo_bar_idx ON foo (bar); -- Slow SELECT COUNT() FROM search LEFT JOIN foo ON s_docid = docid WHERE bar = 1 AND search MATCH 'test*'; -- As fast as before (current workaround) SELECT COUNT() FROM foo WHERE bar = 1 AND s_docid IN ( SELECT docid FROM search WHERE search MATCH 'test*' ); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Thank you all for your answers. I have made the changes necessary. Alex -Message d'origine- De : sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] De la part de Keith Medcalf Envoyé : jeudi 18 juillet 2019 21:11 À : SQLite mailing list Objet : Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... Except in SQLite where as a documented behavioural anomaly maintained for backwards compatibility it simply means "UNIQUE" (for ROWID tables). And UNIQUE indexes may have NULL components. This is because despite your wishing that your primary key is the primary key, it is not the primary key. However when WITHOUT ROWID tables were introduced there was no backwards compatibility issues (they were new after all) then PRIMARY KEY could be implemented as UNIQUE NOT NULL ... https://sqlite.org/nulls.html https://sqlite.org/rowidtable.html https://sqlite.org/withoutrowid.html See especially 2 sub 4 in the latter. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz >Sent: Thursday, 18 July, 2019 12:33 >To: SQLite mailing list >Subject: Re: [sqlite] I can insert multiple rows with the same primary >key when one of the value of the PK is NULL ... > >> You might prefer adding an explicit NOT NULL on both "client" and >"salesman" columns. >> There is an historical reason why SQLite accepts NULL for primary >key column(s). > >Ok, thanks for the hint, I didn't know that either. But it is a very >odd behavior, because PRIMARY KEY per definition doesn't mean anything >else than UNIQUE NOT NULL. > >___ >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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf wrote: > Except in SQLite where as a documented behavioural anomaly maintained for > backwards compatibility it simply means "UNIQUE" (for ROWID tables). And > UNIQUE indexes may have NULL components. This is because despite your > wishing that your primary key is the primary key, it is not the primary key. > > However when WITHOUT ROWID tables were introduced there was no backwards > compatibility issues (they were new after all) then PRIMARY KEY could be > implemented as UNIQUE NOT NULL ... > > https://sqlite.org/nulls.html > https://sqlite.org/rowidtable.html > https://sqlite.org/withoutrowid.html > > See especially 2 sub 4 in the latter. > This whole thread is good material for the new(ish) quirks page IMHO. As Keith points out, most of the material exists in the doc already, but IMHO the quirks page should be the one-stop page to learn about all those historical or by-design particularities of SQLite, with a short explanation and pointers to other doc places with more details. Just my $0.02 of course :) --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
