Re: [sqlite] Tcl API docs - clarification
Hi, I think you should have a look here: http://www.sqlite.org/uri.html Hope that helps! Fredrik On Tue, Oct 23, 2012 at 10:48 AM, John Gillespie rjkgilles...@gmail.comwrote: Just spent a while trying to work out how to access sqlite in read-only mode from Tcl. There seems to be nothing in the API doc : http://www.sqlite.org/tclsqlite.html I eventually got the information from an error message: wrong # args: should be sqlite3 HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? Would it be possible for you to add these options for the 'sqlite3' command to the Tcl API document. Thanks John Gillespie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (UML) Visualisation of a SQLite schema ?
Hi, Just thought I'd tell you that I went into solving this issue myself. The resulting script is here: https://github.com/dargosch/sqlite2dot * Requires Tcl and the sqlite3 package for Tcl. It generates a .dot file for use with Graphviz. Very simple script, but it solves the issue I wanted to solve :-) Please suggest enhancements. /Fredrik On Fri, Apr 15, 2011 at 12:40 PM, Gary_Gabriel gabrielrobert.qu...@googlemail.com wrote: Hi Fredrik, I'm looking for a tool that would generate a visualisation (UML?) of the SQLite database schema I'm using, with table constraints and references between tables if possible. Is there such a tool (for Mac)? If you are interested in doing something yourself- then this might be a gentle reminder of a thread on the mailing list. Subject: [sqlite] Creating directed graphs and simple examples To: sqlite-users@sqlite.org From: Gary Briggs chu...@icculus.org Date: 15, Mar 2011 Archive: http://www.mail-archive.com/sqlite-users@sqlite.org/msg59602.html Graphviz: http://www.graphviz.org/ As you probably know Graphviz integrates into Tcl/Tk with TclDot which is available for Posix. Good luck with your search- Gary Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (UML) Visualisation of a SQLite schema ?
Hi, I'm looking for a tool that would generate a visualisation (UML?) of the SQLite database schema I'm using, with table constraints and references between tables if possible. Is there such a tool (for Mac)? I've looked into SQL::Translator (produces reasonable output, but does not cover the entire SQLite syntax) and SchemaSpy (the javasqlite has pointer problems on my mac). Any suggestion would be very helpful. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN
On Wed, Feb 9, 2011 at 6:15 PM, Eric Smith eas@gmail.com wrote:
Fredrik Karlsson wrote:
package require sqlite3
sqlite3 db :memory:
db eval {create table a (id INTEGER);}
db eval {insert into a values (1);}
db eval {insert into a values (2);}
db eval {select * from a where id in (1,3);} vals {parray vals}
vals(*) = id
vals(id) = 1
set alist [list 1 3]
1 3
db eval {select * from a where id in $alist;} vals {parray vals}
near $alist: syntax error
--
This implies that the manner in which $alist gets expanded should be
sensitive to the SQL context in which the expansion happens (and also,
for the purposes of backward compatibility, to the value of the
variable iteslf).
Unless I'm mistaken that would require pushing the expansion logic down
much further into sqlite, and probably would still fail in a number of
cases.
So I doubt you'll get much traction there, especially since this can be
pretty easily done from your application.
Here's an option off the top of my head:
proc qSqlList {alistname} {
set magic_array_name _qSql_${alistname}_arr
upvar $alistname alist $magic_array_name alist_arr
#assert {[string is list $alist]} ;# or whatever your infra provides
array unset alist_arr
set i 0
set out [list]
foreach item $alist {
set alist_arr($i) $item
lappend out \$${magic_array_name}($i)
incr i
}
return ([join $out ,])
}
So your call becomes:
db eval select * from a where id in [qSqlList alist] vals {parray vals}
SQLite does the expansion on the underlying list values with the proper
sqlite3_bind_* calls etc.
The proc isn't 100% ideal because:
1. it creates this magic array in the caller's scope (not the prettiest
thing in the world), and
2. for that reason it disallows dumb copies of the return value to float
around. You need to make the sqlite call close to where you do the
quoting.
Still, it might be good enough for your purposes. Or maybe you can
expand on the idea, wrap it up into a cleaner interface, and go from there.
Hi Eric,
Thank you for that! I could definitelly wrap that proc up to at least
get the array to be in a separate, hidden, namespace or something like
that.
Thanks!
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN
Hi,
A very nice extension - I'll look into that one for my integer-only
lists, for sure.
Thank you!
/Fredrik
2011/2/10 Alexey Pechnikov pechni...@mobigroup.ru:
See
http://sqlite.mobigroup.ru/wiki?name=ext_intarray_tcl
09.02.2011 17:49 пользователь Fredrik Karlsson dargo...@gmail.com
написал:
Dear list,
I find the IN operator quite useful for selecting a set number of things.
However, I often have a Tcl list with the things I want to match
already when I get to the stage there I should issue a SELECT on the
database.
I then paste all the elements of the list together with ',' or just ,
chars and supply that inside a () to the sqlite3 database command.
Would it not be a nice, and safer, addition to the interface to be
able to do something like this (i.e. sot that there would be no syntax
error at the end):
--
package require sqlite3
sqlite3 db :memory:
db eval {create table a (id INTEGER);}
db eval {insert into a values (1);}
db eval {insert into a values (2);}
db eval {select * from a where id in (1,3);} vals {parray vals}
vals(*) = id
vals(id) = 1
set alist [list 1 3]
1 3
db eval {select * from a where id in $alist;} vals {parray vals}
near $alist: syntax error
--
Also seems much safer to have a proper binding of values here..
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN
Dear list,
I find the IN operator quite useful for selecting a set number of things.
However, I often have a Tcl list with the things I want to match
already when I get to the stage there I should issue a SELECT on the
database.
I then paste all the elements of the list together with ',' or just ,
chars and supply that inside a () to the sqlite3 database command.
Would it not be a nice, and safer, addition to the interface to be
able to do something like this (i.e. sot that there would be no syntax
error at the end):
--
package require sqlite3
sqlite3 db :memory:
db eval {create table a (id INTEGER);}
db eval {insert into a values (1);}
db eval {insert into a values (2);}
db eval {select * from a where id in (1,3);} vals {parray vals}
vals(*) = id
vals(id) = 1
set alist [list 1 3]
1 3
db eval {select * from a where id in $alist;} vals {parray vals}
near $alist: syntax error
--
Also seems much safer to have a proper binding of values here..
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl interface to the int sqlite3_open_v2 funtion's SQLITE_OPEN_READONLY flag?
Hi, I notice that the int sqlite3_open_v2 C function has a SQLITE_OPEN_READONLY which allows opening a database in read only mode. Would it be possible to have this possibility in the Tcl interface too? Perhaps similar to the way the open command of Tcl specifies flags. This feature would be very useful as I have parts of my package that should NEVER be able to modify the underlying database. Right now I am jumping through lots and lots of Tcl loops in order to avoid any possibility of writes in not trusted areas in the code, and I cant help feeling that a read only database handle would be much more sensible. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Command line client and Tcl interface behaves differently?
Dear list,
I am having a silly problem, and need your expertise. I just want to
initiate a SQLite database using a schema file in Tcl, but I just get
an empty database whatever I do. I asked this question on the Tcl
list, and got a reply which I interpret to mean that this is not a
problem in my Tcl knowledge - but in my SQLite implementation.
Anyway, loading from the same SQL file within the sqlite3 command
line client is no problem
Here is what I am doing:
proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
set dbFile [file join [file normalize $dbDir] $dbFile ]
if { $force == 1 [file exists $dbFile]} {
file delete $dbFile
}
sqlite3 db $dbFile
set schemaFile [file normalize [file join .. setup 1_schema.sql] ]
if {! [file exists $schemaFile] } {
return -code error Unable to open schema file $schemaFile
}
set inf [open $schemaFile r]
set sql [read $inf]
close $inf
db eval $sql
db close
return [file normalize $dbFile ]
}
Does the Tcl interface and the sqlite3 binary behave differently
when parsing instructions from a file?
The SQL code in the 1_schema.sql file is just table definitions and
comments using the /* */ syntax which loads ok in the command like
client, so what could be wrong?
Thankful for all the help I could get on this.
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Command line client and Tcl interface behaves differently?
On Sat, Nov 20, 2010 at 2:43 PM, Richard Hipp d...@sqlite.org wrote:
On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson dargo...@gmail.comwrote:
Dear list,
I am having a silly problem, and need your expertise. I just want to
initiate a SQLite database using a schema file in Tcl, but I just get
an empty database whatever I do. I asked this question on the Tcl
list, and got a reply which I interpret to mean that this is not a
problem in my Tcl knowledge - but in my SQLite implementation.
Anyway, loading from the same SQL file within the sqlite3 command
line client is no problem
Here is what I am doing:
proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
set dbFile [file join [file normalize $dbDir] $dbFile ]
if { $force == 1 [file exists $dbFile]} {
file delete $dbFile
}
sqlite3 db $dbFile
set schemaFile [file normalize [file join .. setup 1_schema.sql] ]
if {! [file exists $schemaFile] } {
return -code error Unable to open schema file $schemaFile
}
set inf [open $schemaFile r]
set sql [read $inf]
close $inf
Add here: puts $sql
Let's see what file you are really loading
db eval $sql
And here: puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
db close
return [file normalize $dbFile ]
}
Does the Tcl interface and the sqlite3 binary behave differently
when parsing instructions from a file?
The SQL code in the 1_schema.sql file is just table definitions and
comments using the /* */ syntax which loads ok in the command like
client, so what could be wrong?
Thankful for all the help I could get on this.
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Thank you Rickard for the quick response. This is really code that is
part of a benchmarking framework (which used to work, oddly enough) so
the output is a bit verbose.
-
$ tclsh8.6 run_all_benchmarks.tcl
[Sat Nov 20 14:50:09 CET 2010] [bench] [debug] '::bench::run -errors 1
-iters 10 -match Emu*simple*one* /usr/local/bin/tclsh8.6
./emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Benchmark
/usr/local/bin/tclsh8.6'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Sourcing ./emuquery.benchmark'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Running EmuQuery: Test
simple query {John=fourth} in one file'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Copied 1 files to
/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info]
'/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE utterances ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'filelength REAL,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'updated_at TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum_algorithm TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'UNIQUE(name) ON CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE levels ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT UNIQUE ON
CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ',weight FLOAT UNIQUE'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'parent_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'child_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(parent_id,child_id)'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE tc_level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'ancestor_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'descendant_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'depth INTEGER,'
[Sat Nov 20 14:50:10 CET 2010] [bench
Re: [sqlite] Command line client and Tcl interface behaves differently?
Hi,
Sorry, never mind this post. I haven't found the problem actually yet,
but a bare minimum example fed directly to the interpreter through
the command line works correctly:
% package require sqlite3
3.7.2
% set inf [open 1_schema.sql r]
file6
% set sql [read $inf]
[... the contents of the read file is dumped ..]
% close $inf
% sqlite3 db :memory:
% db eval $sql
% puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
3.7.2 {2010-08-23 18:52:01 42537b60566f288167f1b5864a5435986838e3a3}
% db eval {select * from SQLITE_MASTER} values {parray values}
[... data concerning all tables are dumped... ]
So, the error is somewhere else in the code. Sorry about that. :-/
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Resursive trigger not doing full recursion...
Dear list, I'm probably doing something really stupid here, but I feel I need to ask you anyway to see id there is something that I've missed. Is it not possible for a trigger to trigger itself? I get triggers that trigger triggers, but so far not triggers that trigger themselves... (yes, many triggers.. :-) ) This is what I am doing: I need to compute the transitive closure of a graph: Tables I am using are: --- This table stores the connections--- CREATE TABLE level_level ( parent_id INTEGER REFERENCES levels(id), child_id INTEGER REFERENCES levels(id), PRIMARY KEY(parent_id,child_id) ); --- This table stores the TC CREATE TABLE tc_level_level ( ancestor_id INTEGER REFERENCES levels(id), descendant_id INTEGER REFERENCES levels(id), depth INTEGER, path TEXT, PRIMARY KEY(ancestor_id,descendant_id,depth) ON CONFLICT IGNORE ); Now, I have two triggers: --- This trigger inserts the simple connections into the TC table CREATE TRIGGER ll_tcll_insert after insert on level_level for each row begin delete from tc_level_level where depth 1; insert into tc_level_level select NEW.parent_id ancestor_id,NEW.child_id descendant_id,1 depth,NEW.parent_id || ',' ||NEW.child_id path from level_level; end; --- This table stores the TC CREATE TRIGGER tcll_tcll_insert after insert on tc_level_level for each row begin insert into tc_level_level select tc1.ancestor_id, tc2.descendant_id, tc1.depth + tc2.depth,tc1.path || substr(tc2.path, length(tc2.ancestor_id)+1) from tc_level_level tc1, tc_level_level tc2 where tc1.descendant_id = tc2.ancestor_id; end; A sample run looks like this: delete from level_level; delete from tc_level_level; INSERT INTO level_level VALUES(1,2); INSERT INTO level_level VALUES(1,3); INSERT INTO level_level VALUES(3,4); INSERT INTO level_level VALUES(4,5); INSERT INTO level_level VALUES(2,5); select * from level_level; parent_id child_id -- -- 1 2 1 3 3 4 4 5 2 5 select * from tc_level_level; ancestor_id descendant_id depth path --- - -- -- 12 1 1,2 13 1 1,3 34 1 3,4 45 1 4,5 25 1 2,5 15 2 1,2,5 14 2 1,3,4 35 2 3,4,5 So, why is there not a 15 3 1,3,4,5 row? Second question is, of course, can I depend on this behaviour? Or, is it just because of the version of SQLite I am currently using (3.6.16)? Thanks! /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resursive trigger not doing full recursion...
Hi Igor, Thank you for the qick response. Of course, this PRAGMA solved the entire issue, once I got a never version of SQLite installed. Thank you! /Fredrik On Tue, Nov 16, 2010 at 10:58 PM, Igor Tandetnik itandet...@mvps.org wrote: Fredrik Karlsson dargo...@gmail.com wrote: I'm probably doing something really stupid here, but I feel I need to ask you anyway to see id there is something that I've missed. Is it not possible for a trigger to trigger itself? Recursive triggers have to be explicitly turned on: http://sqlite.org/pragma.html#pragma_recursive_triggers Second question is, of course, can I depend on this behaviour? Or, is it just because of the version of SQLite I am currently using (3.6.16)? Recursive triggers are supported beginning with 3.6.18. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
Hi, Have you had a look at the EXCEPT statement? http://www.sqlite.org/lang_select.html (bottom of page) EXCEPT takes the result of left SELECT after removing the results of the right SELECT. Does this make sense to you? /Fredrik On Thu, May 6, 2010 at 1:03 PM, Matt Young youngsan...@gmail.com wrote: # I am doing a simulation of distinct insert into seriesid (series_id,pointer) select series_id,ROWID from seriesdata as s where s.series_id not in( select series_id from seriesid ); # compile the table seriesid, then in the subquery I interrodate the table at its current state # trying to include only members not already in. No run time erros. Is this correct, shouyld this work? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aliases in subqueries?
On Fri, Apr 2, 2010 at 1:59 PM, Igor Tandetnik itandet...@mvps.org wrote: Fredrik Karlsson wrote: Sorry for asking this SQL question, but I just have to make sure: - Aliases in subqueries - are the always, local to the scope of the subquery? That is, if I have this query (this example makes _no_ sense, I know. Just an illustration.): select target.* from ( select target.* from (select m.id, n. name from mytable m, myothertable, n where m.id = n.id) target, (select m.id, n. name from mytable m, myothertable, n where m.id = n.id) comp1, where target.id = comp1.id ) target, mytable t where t.id = target.id; will the aliases at the different levels be safe inside of their subquery so that e.g. target in the outer query will be intact once the subquery has been evaluated? Yes. Apart from a misplaced comma here and there, this statement should work. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hi, Excellent! Thank you for having a look at this. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Get a specific sequence of rows...
Hi, I have a list of id:s stored in a field. I would now like to get some information from a table by these id:s, but exactly in this order. So, if I have a table 1 One 2 Two 3 Three and the sequence 3,1,2 stored somewhere, how do I get a neat list like Three One Two ? I I can see a solution where I split the string outside of sqlite and then construct a query consisting of lots of UNIONs, like (not a full example, just an illustration) (select name from mytab where id = 3) UNION ALL (select name from mytab where id = 1) UNION ALL (select name from mytab where id = 2) ... ... but maybe there is a better option somewhere? I would be thankful for any help I can get. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get a specific sequence of rows...
On Fri, Mar 26, 2010 at 12:22 PM, Simon Slavin slav...@bigfraud.org wrote: On 26 Mar 2010, at 9:00am, Fredrik Karlsson wrote: I have a list of id:s stored in a field. I would now like to get some information from a table by these id:s, but exactly in this order. So, if I have a table 1 One 2 Two 3 Three and the sequence 3,1,2 stored somewhere, how do I get a neat list like Three One Two I can't find a function which does what you want. The SQL-like solution to it is to have your '3,1,2' written to a table as three records, then use a JOIN or a sub-SELECT to make SQL return the right records in the right order. Simon. Hi, This is of course another solution. I guess, coming from R, I was looking for a substitute for th %in% operator (or the MySQL IN operator as it turns out, now that I've Googled this some more) but a temp table would also do the trick I guess. Thank you! /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get a specific sequence of rows...
On Fri, Mar 26, 2010 at 1:25 PM, Igor Tandetnik itandet...@mvps.org wrote: Fredrik Karlsson wrote: This is of course another solution. I guess, coming from R, I was looking for a substitute for th %in% operator (or the MySQL IN operator as it turns out, now that I've Googled this some more) but a temp table would also do the trick I guess. Well, you can generate a statement of the form select name from mytab where id in (3, 1, 2); I don't believe either SQLite or MySQL guarantees that the rows will always come out in the order in which IDs are listed in the IN clause. But I won't be surprised if they do happen to come out this way, as an implementation detail. I'm not sure I'd be comfortable relying on such behavior though. -- Igor Tandetnik Oh, ok. I did not think of that. Indeed, this is the behaviour of R %in% too, so I should really have thought of this. Since this is a calculated path in a directed graph, order is important, and so I will go with a temporary (in memory?) table. Thank you for your great help! /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] All ancestors / descendents of a vertex in sqlite?
Dear list, I have a (small) directed graph which I would be able to fins all ancestors or descendents of a certain vertex (transitive closure?). So, using this graph: CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, UNIQUE(parent_id, child_id)); INSERT INTO levels_levels VALUES(6,7); INSERT INTO levels_levels VALUES(6,8); INSERT INTO levels_levels VALUES(8,9); INSERT INTO levels_levels VALUES(7,10); INSERT INTO levels_levels VALUES(9,10); which would look like this: 6 7 8 | 9 10 I would like to make it possible to find 10 as a descendent of 8 and 6 as a parent of 9 (for instance). I have found a couple of procedural solutions using procedural calls in sql server or postgresql, but is there a solution that I could get into sqlite? The graph will never be very big, updating efficiency is not an important factor. It will be queried a lot though, so search efficiency is important. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] When should you move to R*Tree?
Dear list, I am writing small program for large-ish databases involving multiple (nested) comparisons of time references (REAL) against time intervals (also REAL) stored as max and min points (i.e. is this time point within the intervals I have stored in table X?) At what point, in terms of database size or otherwise, would it be sensible (if at all) to move into using the R*Tree module for the time reference comparisons? I have tried generating som data to do comparisons, but I get lots of warning messages about constrain violations, so I am not so sure that the index is working correctly. Anyway, with 30 000 rows and my possibly faulty index it seems that merging negates any positive effects of R*Tree. This is my setup: CREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX,); CREATE TABLE maintab (id INTEGER PRIMARY KEY AUTOINCREMENT, lab TEXT); CREATE TABLE merged (id INTEGER PRIMARY KEY, lab TEXT, minX REAL, maxX REAL); -- Data inserted into the database by a bunch of insert into maintab values ( 1 ,'mmm'); insert into demo_index values ( 1 , (random()+0.0)/(random()/1000), (random()+0.0)/(random()/1000)); . (30 000 of them) Typically select * from maintab m, demo_index d where d.maxX = 100.0 and d.minX = 100.0 and d.id = m.id; runns in CPU Time: user 0.074044 sys 0.035214 and (where merged is a table with everything in it) select * from merged where maxX = 100.0 and minX = 100.0; in CPU Time: user 0.053880 sys 0.031010 Does this seem reasonable? Am I doing something stupid? /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datetime mystery
Hi,
On Thu, Oct 8, 2009 at 12:04 AM, P Kishor punk.k...@gmail.com wrote:
On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson dargo...@gmail.com wrote:
Dear list,
I am sorry if I am asking a FAQ, but what is differnent with
datetime() and time()?
date # This is the correct time on the system
Ons 7 Okt 2009 23:56:36 CEST
sqlite3 temp.sqlite SELECT datetime();
2009-10-07 21:56:58
sqlite3 temp.sqlite SELECT datetime('now);
SQL error: unrecognized token: 'now);
sqlite3 temp.sqlite SELECT datetime('now');
2009-10-07 21:57:13
sqlite3 temp.sqlite SELECT time('now');
21:59:05
What happened here? How come the time functions are off 2 hours?
(I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
time zones. The sqlite returned times, by default, are UTC.
Yes, that would have been my guess too, but I am on CET, which I
understand is UTC+1. So, I am still getting one hour less than I
should from SQLite. Or, am I doing something stupid?
/Fredrik
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datetime mystery
Hi,
Yes! That's it! Sorry about the stupid question then..
select datetime('now','localtime'); seems to do what I want.
/Fredrik
On Thu, Oct 8, 2009 at 9:39 AM, Simon Davies
simon.james.dav...@googlemail.com wrote:
2009/10/8 Fredrik Karlsson dargo...@gmail.com:
Hi,
On Thu, Oct 8, 2009 at 12:04 AM, P Kishor punk.k...@gmail.com wrote:
On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson dargo...@gmail.com wrote:
Dear list,
I am sorry if I am asking a FAQ, but what is differnent with
datetime() and time()?
date # This is the correct time on the system
Ons 7 Okt 2009 23:56:36 CEST
sqlite3 temp.sqlite SELECT datetime();
2009-10-07 21:56:58
sqlite3 temp.sqlite SELECT datetime('now);
SQL error: unrecognized token: 'now);
sqlite3 temp.sqlite SELECT datetime('now');
2009-10-07 21:57:13
sqlite3 temp.sqlite SELECT time('now');
21:59:05
What happened here? How come the time functions are off 2 hours?
(I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
time zones. The sqlite returned times, by default, are UTC.
Yes, that would have been my guess too, but I am on CET, which I
understand is UTC+1. So, I am still getting one hour less than I
should from SQLite. Or, am I doing something stupid?
From above, you are currently on CEST, which is UTC+2
/Fredrik
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Datetime mystery
Dear list,
I am sorry if I am asking a FAQ, but what is differnent with
datetime() and time()?
date # This is the correct time on the system
Ons 7 Okt 2009 23:56:36 CEST
sqlite3 temp.sqlite SELECT datetime();
2009-10-07 21:56:58
sqlite3 temp.sqlite SELECT datetime('now);
SQL error: unrecognized token: 'now);
sqlite3 temp.sqlite SELECT datetime('now');
2009-10-07 21:57:13
sqlite3 temp.sqlite SELECT time('now');
21:59:05
What happened here? How come the time functions are off 2 hours?
(I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Protect against SQL injection inside of the database?
Dear list,
Sorry for jumping onto the list mainly to ask a question, but it is an
imporant one, and I have failed to find the answer on Google.
I am developing a prototype of an application in Tcl using sqlite as
the backend database. Now, I know that I will be dealing with quite
naïve users, who will not think that !' and simialar characters are
evil and potentially dangerous in a SQL database context. So, now I
need to make sure that I am taking all the precautions I can to
protect the database from evil / naïve users, and since parts of the
application may be ported to C for speed later, I would prefer as much
of it to happen in the SQL queries themselves, in order to make sure
that the behaviour stays constant when porting.
My currrent strategy is to use a combination of quote() and trim() (as
blank space at the ends of a string is not important in my
application). So, for each string value I get from the user, I do
something similar to
set out [format {select * from X where label == quote(trim(%s)) and
id %d } $myStringValue $compId ]
(Please ignore the Tcl part if you are not familiar with it.. format
is basically (almost) sprintf in a new name )
So, my questions are now:
1) Can I feel safe that the string value is now safe (to some
degree) regarding SQL injection?
2) Have I done something that will prevent me from matching values I
really want to match by altering the original string value?
3) Is the integer value reasonably secure, or shouls something be done
for that too (and then, what?)
Sorry for these questions, but I would rather dot all the i:s before
moving on in the application development. I have seen before how
creative naïve users can be when it comes to making applications crash
due to unforseen actions. :-)
Of course, any input in this would be greatly appreciated.
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenkerm...@contact.de wrote:
Your working far too hard. The sqlite Tcl binding already does all thats
needed.
This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id
$compId}]
But you MUST use {} to quote your query and not , so sqlite gets to do the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.
Michael
Hi Michael,
Ok, I can see how this would be the easiest solution, but what I am
doing is basically a query builder (maping of comands in a specialized
language to pattern subselects in SQL queries). Since the statements
can be nested in many different ways, I cannot expect to be able to
construct the query and keeping track of variable names to be used in
the final substitution, so that I can make use of the built in binding
feature of sqlite It is much to much hard work.
Instead, I think I need to make each part of the query return a
complete (not to be evaluated further outside of sqlite) SQL query
subselect statement, which is why I think I need to make sure that the
values I insert is safe inside an SQL statement myself.
Or, do you know of a Tcl command to make strings SQL safe? (Sorry
for making this into a Tcl question now..)
/Fredrik
--
Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
