Re: [sqlite] Bug: CAST string timestamp to TIMESTAMP truncates the value

On 11/9/2016 8:53 PM, Simon Slavin wrote: On 9 Nov 2016, at 4:44pm, Jiri Bajer wrote: sqlite> select CAST('1970-01-02 03:04:00.00' AS TIMESTAMP); SQLite has no TIMESTAMP type. See the "CAST expressions" section near the end of

Re: [sqlite] Bug: CAST string timestamp to TIMESTAMP truncates the value

On 9 Nov 2016, at 4:44pm, Jiri Bajer wrote: > sqlite> select CAST('1970-01-02 03:04:00.00' AS TIMESTAMP); SQLite has no TIMESTAMP type. See the "CAST expressions" section near the end of So the mystery becomes that of what

[sqlite] Bug: CAST string timestamp to TIMESTAMP truncates the value

Hi sqlite devs, I am using SQLite wrapped by SQLAlchemy ORM and have encountered a weird behavior of CAST of a timestamp string to timestamp type as described at https://bitbucket.org/zzzeek/alembic/issues/391/batch-migration-on-sqlite-truncate : Steps to reproduce: sqlite> select

[sqlite] sqlite ext3 settings

Hi, I have sqlite database on top of ext3 (on ssd). Does 1) ext3 parameters (data=writeback|ordered,barrier=0|1, etc) 2) drive write-caching setting (hdparm -W) value 3) ??? have impact on the possibility of losing data due to power loss? If yes which parameters should be used to

Re: [sqlite] Fastest way to search json array values?

> On Nov 9, 2016, at 3:04 AM, Wout Mertens wrote: > >> Indexes (even expression indexes) require a 'real' table. >> You'd have to put the results of json_each() into a separate table that >> can then be indexed. > > I assume I can use some sort of trigger setup to

Re: [sqlite] Database is locked

You may not DETACH while a query is running. This is to prevent the DETACH from closing a database connection out from under the running query. On 11/9/16, Jean-Baptiste Gardette wrote: > On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0 > > The following tcl script craches

[sqlite] Database is locked

On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0 The following tcl script craches : package require sqlite3 sqlite3 db1 dbfile1 db1 eval "SELECT A FROM TableA" { db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'" db1 eval

Re: [sqlite] Tcl bindings doc update request

Rowan Worth writes: > On 28 October 2016 at 02:53, Rolf Ade 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

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

Re: [sqlite] Fastest way to search json array values?

On Wed, Nov 9, 2016 at 12:20 PM Clemens Ladisch wrote: > Wout Mertens wrote: > > Another approach I thought of is to assume I will only have a certain > > number of array items (let's say 5), generate an "where is not null" > > expression index for each of them, and then

Re: [sqlite] [OT] suggestion for shell script and variable interpolation

On 11/9/16, Luca Ferrari wrote: > Hi all, > this could be trivial, but assuming I need some shell script to query > SQLite3 databases with variable-interpolated queries, what can I do? I typically using "tclsh" for this. https://www.tcl-lang.org/ SQLite is really a TCL

Re: [sqlite] [OT] suggestion for shell script and variable interpolation

On 11/09/2016 02:39 PM, Luca Ferrari wrote: Hi all, this could be trivial, but assuming I need some shell script to query SQLite3 databases with variable-interpolated queries, what can I do? Of course the following does not work because ticks prevent variable interpolation: COUNT=`sqlite3 $db

Re: [sqlite] Fastest way to search json array values?

Wout Mertens wrote: > I assume I can use some sort of trigger setup to automatically update that > derived table when a row gets inserted, deleted or updated? Any pointers on > how to do that? Something like this: http://www.sqlite.org/fts5.html#external_content_tables > Another approach I

Re: [sqlite] [OT] suggestion for shell script and variable interpolation

COUNT=$(sqlite3 "$db" "SELECT COUNT(*) FROM foo WHERE baz='$BAZ'") should totally work (I quoted $BAZ as a string, don't do that if it is a number, and you should escape any ' in $BAZ). On Wed, Nov 9, 2016 at 11:40 AM Clemens Ladisch wrote: > Luca Ferrari wrote: > > this

Re: [sqlite] Fastest way to search json array values?

On Wed, Nov 9, 2016 at 11:42 AM Clemens Ladisch wrote: > Wout Mertens wrote: > > I'm using the JSON1 extension, and I want to find items by json array > > contents. > > > > This means a query like `SELECT foo.json FROM foo, json_each(foo.json) > > WHERE json_each.value =

Re: [sqlite] Fastest way to search json array values?

Wout Mertens wrote: > I'm using the JSON1 extension, and I want to find items by json array > contents. > > This means a query like `SELECT foo.json FROM foo, json_each(foo.json) > WHERE json_each.value = "bar";`, so basically scanning the entire table. > > I understand that virtual tables can not

Re: [sqlite] [OT] suggestion for shell script and variable interpolation

Luca Ferrari wrote: > this could be trivial, but assuming I need some shell script to query > SQLite3 databases with variable-interpolated queries, what can I do? > Of course the following does not work because ticks prevent variable > interpolation: > > COUNT=`sqlite3 $db 'SELECT COUNT(*) FROM

[sqlite] [OT] suggestion for shell script and variable interpolation

Hi all, this could be trivial, but assuming I need some shell script to query SQLite3 databases with variable-interpolated queries, what can I do? Of course the following does not work because ticks prevent variable interpolation: COUNT=`sqlite3 $db 'SELECT COUNT(*) FROM foo WHERE baz=$BAZ'` and