Re: [sqlite] loading extension csv.c
> On 2/7/20, chiahui chen wrote: >> >> I tried the suggested solution. sqlite3 was >> downloaded and compiled. >> Then ran : gcc -g -l. -fPIC -dynamiclib ./ext/csv.c -o csv.dylib ^^^ Is this a typo? This should read gcc -I. >> the same errors occurred again. >> >> Is there any suggestion? Just in case if my Mac operating system provides >> some info (macOS High Sierra version 10.13.6) >> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl binding: Quirk with Tcl variable reference in eval method
While being able to use Tcl variable references inside db eval SQL
statements (as in
set name "foo'bar"
db eval {SELECT * FROM sometable WHERE somecolumn = $name}
) this does work only for "simple" Tcl variable references.
This script shows this:
package require sqlite3
sqlite3 db ":memory:"
puts [db version]
db eval {
CREATE TABLE test(name text);
INSERT INTO test(name) VALUES('one');
}
set array(a) "one"
set key "a"
puts "\$array(\$key) has the value: '$array($key)'"
puts "Same as \$array(a): '$array(a)'"
db eval {
DELETE FROM test WHERE name = $array($key)
}
puts "State after first DELETE (using \$array(\$key)); nothing deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]
db eval {
DELETE FROM test WHERE name = $array(a)
}
puts "State after second DELETE (using (\$array(a)); now the row is deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]
While $array($key) is a perfect variable reference in Tcl scripts
(depending of course of the values of array and key) it isn't inside a
SQL script provided to the eval method.
I naively expected the sqlite3 SQL parser to revert to Tcl_ParseVar()
(or Tcl_ParseVarName()) to resolve Tcl variable references inside the
SQL - which would happily resolve $array($key) or even more complicated
constructs as $x([expr {$index + 1}]) - but it is obviously done in
another, simpler way.
I suspect there are good reasons for this "limitation" and this is no
big deal, the feature (with its current capabilities) is still very
helpful. Maybe I'm the only one being so bold to have expected
$array($key) to work inside the SQL statement. But perhaps a word of
warning in https://www.sqlite.org/tclsqlite.html#eval would prevent
others to have similar expectations.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Regression in json()
The fix http://www.sqlite.org/cgi/src/info/4f1b5229a3bbc9d4 (Escape
control characters in JSON) introduced a regression.
> ./sqlite3
SQLite version 3.19.0 2017-04-12 17:50:12
[...]
sqlite> select json('"ä"');
Error: malformed JSON
I'd expect: "ä"
The reason is
http://www.sqlite.org/cgi/src/artifact/18d80526c34e3eab?ln=804
The variable c is declared as char
(http://www.sqlite.org/cgi/src/artifact/18d80526c34e3eab?ln=736)
If I'm not mistaken that should be:
unsigned char c;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] json() number value parsing
Am 04/09/2017 10:34 AM, Olivier Mascia wrote:
Le 9 avr. 2017 à 03:08, Jens Alfke <j...@mooseyard.com> a écrit :
On Apr 7, 2017, at 5:26 PM, Rolf Ade <r...@pointsman.de> wrote:
./sqlite3
SQLite version 3.19.0 2017-04-07 20:20:08
[...]
sqlite> select json(' { "this" : 000.23 } ');
{"this":000.23}
If I read RFC 7159 (http://www.rfc-editor.org/rfc/rfc7159.txt) correct
this should return: "Error: malformed JSON".
In this case I would go with Postel’s Law, paraphrased as “Be
strict in what you write, but lenient in what you read.” I don’t see
a point in disallowing something as trivial as redundant leading
zeroes.
Mr. Hipp has already fixed this:
https://www.sqlite.org/src/info/204e72f0080e8f08
If you think, that Postel's law should applied here, then Olivier
already pointed out rightfully:
If you'd go with Postal's Law, you would make it so:
sqlite> select json(' { "this" : 000.23 } '); // be lenient in what you
read
{"this":0.23} // be strict in what you write
I think, you do your users no good on the long run, if you accept not
recommendation compliant input (without explict request to do that by
the user). After all, JSON isn't a very complex standard and the
specification does not let much room (if ever) to argue if a certain
input string is valid or not. And JSON isn't an internet protocol, but
a data interchange format.
That all said I'm far from being religious about this. Even a "won't
fix" or a "works as designed" would have been OK with me.
This even wasn't a case I god bitten by this in the wild. For another
project I'm currently writing my 'own' JSON parser. To do that, I took
a look at the sqlite JSON parser implementation (just because I knew
it's on my hard disk and Mr. Hipps code is high quality). I stumbled
over this just by studying the sqlite json1.c code and wanted to make
sure, this implemenation detail is known and decided deliberately.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json() number value parsing
> ./sqlite3
SQLite version 3.19.0 2017-04-07 20:20:08
[...]
sqlite> select json(' { "this" : 000.23 } ');
{"this":000.23}
If I read RFC 7159 (http://www.rfc-editor.org/rfc/rfc7159.txt) correct
this should return: "Error: malformed JSON".
http://sqlite.org/json1.html say, the json1 support "is disabled by
default". If I build from check-out just with ./configure it is
available, I think.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings doc update request
I certainly apologzize, to rise this a third time; I will stop to do it
again.
Still, I think the documentation of the busy method of the Tcl interface
to sqlite
http://sqlite.org/tclsqlite.html#busy
lacks the information, that the callback procedure will be called with
one argument.
Rolf Ade <r...@pointsman.de> writes:
> I apologzize to rise this again, it may have fallen under the radar for
> some reason. It is minor, too.
>
> The documentation of the busy method of the Tcl interface to sqlite
>
> http://sqlite.org/tclsqlite.html#busy
>
> doesn't tell, that the callback procedure is called with one arg, the
> "number of times that the busy handler has been invoked previously for
> the same locking event" (as the documentation sqlite3_busy_handler() at
> https://www.sqlite.org/c3ref/busy_handler.html words it).
>
> The documentation also doesn't note, how the timeout and busy methods
> interfere.
>
> The appended script illustrates the current implementation.
>
> rolf
>
> package require sqlite3
>
> sqlite3 one tmp.db
> one eval {
> CREATE TABLE IF NOT EXISTS some(value text);
> INSERT INTO some VALUES('foo');
> BEGIN IMMEDIATE TRANSACTION
> }
>
> proc busyhandler {args} {
> global counter
> puts "args: '$args'"
> incr counter
> if {$counter > 3} {
> return 1
> }
> return 0
> }
>
> sqlite3 two tmp.db
> two busy busyhandler
>
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
> two timeout 500
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
> two busy busyhandler
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
>
>
>
> Rolf Ade <r...@pointsman.de> writes:
>> The documentation of the busy method at
>>
>> http://sqlite.org/tclsqlite.html#busy
>>
>> should be more specific, with regards of the arguments of the Tcl
>> callback procedure.
>>
>> The documentation currently reads:
>>
>> The "busy" method, like "timeout", only comes into play when the
>> database is locked. But the "busy" method gives the programmer much
>> more control over what action to take. The "busy" method specifies a
>> callback Tcl procedure that is invoked whenever SQLite tries to open
>> a locked database. This callback can do whatever is desired.
>> Presumably, the callback will do some other useful work for a short
>> while (such as service GUI events) then return so that the lock can
>> be tried again. The callback procedure should return "0" if it wants
>> SQLite to try again to open the database and should return "1" if it
>> wants SQLite to abandon the current operation.
>>
>> This doesn't specify, what arguments the Tcl callback procedure must
>> expect. Turns out, that the proc is called with one argument (the number
>> of how much the busy callback was already called for the current lock
>> situation, it seems). That should be explictly written in the
>> documentation, since it doesn't seem clearly obvious.
>>
>> What must be obvious is, that English is a foreign language to me.
>> Therefor, I'm shy to propose a phrase.
>>
>> Another plea, since I'm already writing: It isn't immediate and without
>> any doubt clear, how the "timeout" and the "busy" methods play together,
>> if both are used. I suspect, the timeout, if given, determines, how long
>> it lasts until the busy callback is called (and that for every round, if
>> the busy callback returned "0") but if it is this way (or not) isn't
>> said somewhere, if I see right.
>>
>> rolf
>>
>> ___
>> 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] Tcl bindings doc update request
Rowan Worth <row...@dug.com> writes:
> On 28 October 2016 at 02:53, Rolf Ade <r...@pointsman.de> wrote:
>
>> Another plea, since I'm already writing: It isn't immediate and without
>> any doubt clear, how the "timeout" and the "busy" methods play together,
>> if both are used. I suspect, the timeout, if given, determines, how long
>> it lasts until the busy callback is called (and that for every round, if
>> the busy callback returned "0") but if it is this way (or not) isn't
>> said somewhere, if I see right.
>>
>
> I'm not familiar with the TCL side of things, but in the C API "timeout"
> installs its own "busy" handler (which sleeps for a moment and then returns
> 0, or 1 if the timeout has been exceeded).
>
> There can only be one busy handler, so my guess is that the most recent
> call to "timeout" or "busy" will determine whether your busy handler or
> sqlite's timeout handler gets called.
It is in fact, as you suspected from knowing the C side.
This script illustrates this:
package require sqlite3
sqlite3 one tmp.db
one eval {
CREATE TABLE IF NOT EXISTS some(value text);
INSERT INTO some VALUES('foo');
BEGIN IMMEDIATE TRANSACTION
}
proc busyhandler {args} {
puts "busyhandler called"
}
sqlite3 two tmp.db
two busy busyhandler
two timeout 500
catch {two eval { DELETE FROM some }} errMsg
puts $errMsg
two busy busyhandler
catch {two eval { DELETE FROM some }} errMsg
puts $errMsg
rolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings doc update request
I apologzize to rise this again, it may have fallen under the radar for
some reason. It is minor, too.
The documentation of the busy method of the Tcl interface to sqlite
http://sqlite.org/tclsqlite.html#busy
doesn't tell, that the callback procedure is called with one arg, the
"number of times that the busy handler has been invoked previously for
the same locking event" (as the documentation sqlite3_busy_handler() at
https://www.sqlite.org/c3ref/busy_handler.html words it).
The documentation also doesn't note, how the timeout and busy methods
interfere.
The appended script illustrates the current implementation.
rolf
package require sqlite3
sqlite3 one tmp.db
one eval {
CREATE TABLE IF NOT EXISTS some(value text);
INSERT INTO some VALUES('foo');
BEGIN IMMEDIATE TRANSACTION
}
proc busyhandler {args} {
global counter
puts "args: '$args'"
incr counter
if {$counter > 3} {
return 1
}
return 0
}
sqlite3 two tmp.db
two busy busyhandler
catch {two eval { DELETE FROM some }} errMsg
puts $errMsg
two timeout 500
catch {two eval { DELETE FROM some }} errMsg
puts $errMsg
two busy busyhandler
catch {two eval { DELETE FROM some }} errMsg
puts $errMsg
Rolf Ade <r...@pointsman.de> writes:
> The documentation of the busy method at
>
> http://sqlite.org/tclsqlite.html#busy
>
> should be more specific, with regards of the arguments of the Tcl
> callback procedure.
>
> The documentation currently reads:
>
> The "busy" method, like "timeout", only comes into play when the
> database is locked. But the "busy" method gives the programmer much
> more control over what action to take. The "busy" method specifies a
> callback Tcl procedure that is invoked whenever SQLite tries to open
> a locked database. This callback can do whatever is desired.
> Presumably, the callback will do some other useful work for a short
> while (such as service GUI events) then return so that the lock can
> be tried again. The callback procedure should return "0" if it wants
> SQLite to try again to open the database and should return "1" if it
> wants SQLite to abandon the current operation.
>
> This doesn't specify, what arguments the Tcl callback procedure must
> expect. Turns out, that the proc is called with one argument (the number
> of how much the busy callback was already called for the current lock
> situation, it seems). That should be explictly written in the
> documentation, since it doesn't seem clearly obvious.
>
> What must be obvious is, that English is a foreign language to me.
> Therefor, I'm shy to propose a phrase.
>
> Another plea, since I'm already writing: It isn't immediate and without
> any doubt clear, how the "timeout" and the "busy" methods play together,
> if both are used. I suspect, the timeout, if given, determines, how long
> it lasts until the busy callback is called (and that for every round, if
> the busy callback returned "0") but if it is this way (or not) isn't
> said somewhere, if I see right.
>
> rolf
>
> ___
> 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] Tcl bindings doc update request
The documentation of the busy method at http://sqlite.org/tclsqlite.html#busy should be more specific, with regards of the arguments of the Tcl callback procedure. The documentation currently reads: The "busy" method, like "timeout", only comes into play when the database is locked. But the "busy" method gives the programmer much more control over what action to take. The "busy" method specifies a callback Tcl procedure that is invoked whenever SQLite tries to open a locked database. This callback can do whatever is desired. Presumably, the callback will do some other useful work for a short while (such as service GUI events) then return so that the lock can be tried again. The callback procedure should return "0" if it wants SQLite to try again to open the database and should return "1" if it wants SQLite to abandon the current operation. This doesn't specify, what arguments the Tcl callback procedure must expect. Turns out, that the proc is called with one argument (the number of how much the busy callback was already called for the current lock situation, it seems). That should be explictly written in the documentation, since it doesn't seem clearly obvious. What must be obvious is, that English is a foreign language to me. Therefor, I'm shy to propose a phrase. Another plea, since I'm already writing: It isn't immediate and without any doubt clear, how the "timeout" and the "busy" methods play together, if both are used. I suspect, the timeout, if given, determines, how long it lasts until the busy callback is called (and that for every round, if the busy callback returned "0") but if it is this way (or not) isn't said somewhere, if I see right. rolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typical suffixes of sqlite database files
Hello, I'm asking this for further discussion of a deficiencies of emacs sql-mode w/ sqlite databases (https://debbugs.gnu.org/cgi/bugreport.cgi?bug=23566). What suffixes to sqlite database files do you use or see in the wild? There are for sure .db (e.g. firefox) .sqlite[23]? .fossil (Fossil) .fslchout (Fossil) What else do you see? Ideally with source (application / framework) Thanks. rolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?
Hello, I'm asking this for further discussion of a deficiencies of emacs sql-mode w/ sqlite databases (https://debbugs.gnu.org/cgi/bugreport.cgi?bug=23566). If the cmd line tool sqlite3 is used, is it possible to damage a file, given as sqlite database file argument to sqlite3? Just sqlite3 existing.file with any (non sqlite database file) file works for me and present me the ordinary sqlite3 cmd line prompt. I'm able to make selects but as far as I do something, that needs the database file (reading data or altering it) I get the error: Error: file is encrypted or is not a database After exiting sqlite3, I find some.file unmodified. While this is fine (and sane) (and expected) this is just some anecdotal data by me. It would help me in the above mentioned discussion, if I could point to a more trusted source, that the sqlite developer activly care about (by magic bytes et. al.) not mangling non sqlite database files erroneously given as sqlite database file argument to sqlite3. rolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Version 3.13.0 coming soon
Richard Hipp writes: > A change summary for 3.13.0 is at > https://www.sqlite.org/draft/releaselog/3_13_0.html Change the temporary directory search algorithm on Unix to allow directories read and execute permission, but without read permission, to serve as temporary directories. .. "write and execute permission, but without read permission" ?
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
Am 11/17/2015 11:32 PM, Richard Hipp wrote: > On 11/17/15, Yuri wrote: >> This message always leaves the user wondering: "Which constraint?" >> >> How hard is it to add this information to the message? Is this a matter >> of memorizing the ID of the constraint, and then printing its name in >> the message? >> > > It is a substantial change (basically a complete rewrite of the entire > foreign key constraint mechanism) which would negatively impact both > space and performance. > > The current foreign key constraint mechanism uses a single counter. > As constraints are violated, the counter increments, and as > constraints are resolved the counter decrements. At the end, if the > counter is greater than zero then a "foreign key constraint" error is > issued. > > To provide information about which constraint(s) failed, it would be > necessary to have a bag (a list or hash table or an associative array) > of all the constraints that have been violated and then remove > elements from the bag as constraints are resolved. > > A bag takes more run-time memory than a single counter. (Maybe a lot > more, depending on how many elements it holds.) Adding an element to > a bag takes more time than incrementing a counter. (In particular, > adding an element to a bag probably involves one or more calls to > malloc().) Removing an element from a bag takes more time than > decrementing a counter. A good explanation. It's faster and need lesser memory. Although, the plea stays. Obviously, the name of the constraint would add value to the message. You made crystal clear, that we can't have both at once: fast / less memory and helpful constraint failed message. The way out may be ly in the words "at once". In case of a 'FOREIGN KEY constraint failed' I'm temporarily fine with a not so fast and memory efficent sqlite, as long, as it helps me, to understand the situation more quickly.
