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 wrote: > 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 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 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
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 : > See > http://sqlite.mobigroup.ru/wiki?name=ext_intarray_tcl > > 09.02.2011 17:49 пользователь "Fredrik Karlsson" > написал: >> 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
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 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
[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
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
Re: [sqlite] Command line client and Tcl interface behaves differently?
On Sat, Nov 20, 2010 at 2:43 PM, Richard Hipp wrote: > On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson wrote: > >> 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 ' [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] ');
[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] 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 wrote: > Fredrik Karlsson 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
[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] 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 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 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] Aliases in subqueries?
Dear list, 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? I am writing a query builder which has to work recursively, so I need to make sure this is true. Please tell me if this is not true. /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 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
Re: [sqlite] Get a specific sequence of rows...
On Fri, Mar 26, 2010 at 12:22 PM, Simon Slavin 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
[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
[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, 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 wrote: > 2009/10/8 Fredrik Karlsson : >> Hi, >> >> >> >> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor wrote: >>> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson 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
Re: [sqlite] Datetime mystery
Hi, On Thu, Oct 8, 2009 at 12:04 AM, P Kishor wrote: > On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson 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
[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
Re: [sqlite] Protect against SQL injection inside of the database?
On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker 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
[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