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

2016-11-09 Thread Igor Tandetnik 

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



So the mystery becomes that of what happens if you write "CAST (11.5 AS FRED)". 
 The documentation doesn't seem to mention this.


It is in fact documented, between 
https://www.sqlite.org/lang_expr.html#castexpr and 
https://www.sqlite.org/datatype3.html#affname . CAST(expr AS name) 
determines affinity from `name`, then coerces the value of `expr` 
according to that affinity. The default affinity, when no specific rules 
apply, is NUMERIC.

--
Igor Tandetnik

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

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

2016-11-09 Thread Simon Slavin 

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 happens if you write "CAST (11.5 AS FRED)". 
 The documentation doesn't seem to mention this.

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

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

2016-11-09 Thread Jiri Bajer 

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 CAST('1970-01-02 03:04:00.00' AS TIMESTAMP);

Expected result:
1970-01-02 03:04:00.00

Actual result:
1970

Tested versions:
SQLite version 3.7.17 2013-05-20 00:56:22
SQLite version 3.11.0 2016-02-15 17:29:24

Is this "as designed" and I have missed the part of SQLite documentation 
describing this surprising behavior -- or is it a bug?


Thank you!
Jiri Bajer
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite ext3 settings

2016-11-09 Thread Andrii Motsok 
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 achieve maximum safety against loosing of the data 
due to power loss during writing to WAL database?

  3) Are there any other settings (system, sqlite, etc) which could increase 
protection against data corruption after power lost or system crash?


Regards,

Andrii

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

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

2016-11-09 Thread Jens Alfke 

> 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 automatically update that
> derived table when a row gets inserted, deleted or updated? Any pointers on
> how to do that?

I’ve done similar things, in implementing map/reduce on top of SQLite, but I 
don’t think the approach I used will help you because it relied on some 
specifics of the schema I was using, and it also wasn’t as fast as it could 
have been. I need to revisit it soon, as I’m rewriting that same code.

The trigger approach looks promising. One complication, that doesn’t show up in 
that snippet on the FTS5 page, is that a row in the source table can produce 
multiple rows in the derived table. So the derived table would need a foreign 
key referencing the rowid in the source table, so that when a source row is 
updated or deleted all the derived rows can be replaced.

(If you get this working, I’d be interested to see the SQL code for it.)

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

Re: [sqlite] Database is locked

2016-11-09 Thread Richard Hipp 
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 :
>
>  package require sqlite3
>
>  sqlite3 db1 dbfile1
>
>  db1 eval "SELECT A FROM TableA" {
>
>  db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'"
>
>  db1 eval "SELECT B FROM dbfile2.TableB"
>
>  db1 eval "DETACH DATABASE dbfile2"
>  }
>
> Error message :
> database dbfile2 is locked
>  while executing
> "db1 eval "DETACH DATABASE dbfile2""
>
> Please, if anyone could explain to me what's wrong.
>
> Thanks
> Jean-Baptiste
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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

[sqlite] Database is locked

2016-11-09 Thread Jean-Baptiste Gardette 

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 "SELECT B FROM dbfile2.TableB"

db1 eval "DETACH DATABASE dbfile2"
}

Error message :
database dbfile2 is locked
while executing
"db1 eval "DETACH DATABASE dbfile2""

Please, if anyone could explain to me what's wrong.

Thanks
Jean-Baptiste
___
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

2016-11-09 Thread Rolf Ade 

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 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

2016-11-09 Thread Rolf Ade 

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  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

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

2016-11-09 Thread Wout Mertens 
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 query them with 5 OR clauses.
> > Would that use more or less resources?
>
> Check with EXPLAIN QUERY PLAN whether the indexes are used at all.
> It's possible you'll have to use UNION instead.


No:
sqlite> create table t(json JSON);
sqlite> create index foo on t(json);
sqlite> explain query plan select * from t where json = '5';
0|0|0|SEARCH TABLE t USING COVERING INDEX foo (json=?)
sqlite> explain query plan select * from t where json like '5';
0|0|0|SCAN TABLE t

Thanks for the FTS5 link!

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

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

2016-11-09 Thread Richard Hipp 
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 extension that escaped into the wild, so it
works remarkably well with TCL.  And TCL was originally conceived as a
kind of shell language.

-- 
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] [OT] suggestion for shell script and variable interpolation

2016-11-09 Thread ravi.shan...@cellworksgroup.com 

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 'SELECT COUNT(*) FROM foo WHERE baz=$BAZ'`

and the only ugly solution I thought is to use a temp file to write
down the query:

echo "SELECT COUNT(*) FROM foo WHERE baz=$BAZ">  $$.sql
COUNT=`sqlite3 $db<  $$.sql`

but I'm sure there's a better and much more elegant approach.

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

This will work and i have tried in my system.

query="SELECT COUNT(*) FROM foo WHERE baz='$BAZ'"
result=$( sqlite3 dbpath "$query" )

source : 
http://stackoverflow.com/questions/15314441/what-is-the-proper-quoting-to-assign-an-interpolated-sqlite3-query-to-a-variable

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

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

2016-11-09 Thread Clemens Ladisch 
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 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 query them with 5 OR clauses.
> Would that use more or less resources?

Check with EXPLAIN QUERY PLAN whether the indexes are used at all.
It's possible you'll have to use UNION instead.


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

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

2016-11-09 Thread Wout Mertens 
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 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'`
>
> You did not mention the shell, but double quotes should work.
>
>
> Regards,
> Clemens
> ___
> 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] Fastest way to search json array values?

2016-11-09 Thread Wout Mertens 
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 = "bar";`, so basically scanning the entire table.
> >
> > I understand that virtual tables can not be indexed, so what other
> > approaches would be possible here to prevent such a slow operation?
>
> 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 automatically update that
derived table when a row gets inserted, deleted or updated? Any pointers on
how to do that?

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 query them with 5 OR clauses.
Would that use more or less resources?

Finally, I suppose I could use LIKE on the json field to see if it includes
the string, and then further filter from there. However, creating an index
on the json field does not seem to help with LIKE.

Or extend the JSON1 extension to do this behind the scenes.


I'm going to leave that option on the shelf for now :)

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

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

2016-11-09 Thread Clemens Ladisch 
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 be indexed, so what other
> approaches would be possible here to prevent such a slow operation?

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.

Or extend the JSON1 extension to do this behind the scenes.


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

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

2016-11-09 Thread Clemens Ladisch 
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 foo WHERE baz=$BAZ'`

You did not mention the shell, but double quotes should work.


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

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

2016-11-09 Thread Luca Ferrari 
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 the only ugly solution I thought is to use a temp file to write
down the query:

echo "SELECT COUNT(*) FROM foo WHERE baz=$BAZ" > $$.sql
COUNT=`sqlite3 $db < $$.sql`

but I'm sure there's a better and much more elegant approach.

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