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.com>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 <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] [info] 'path TEXT,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(ancestor_id,descendant_id,depth) ON CONFLICT IGNORE' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE labeltypes (' [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] 'level_id INTEGER REFERENCES levels(id),' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT UNIQUE,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'weight FLOAT,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'isPointTier BOOLEAN,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'isExternal BOOLEAN DEFAULT 0,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'file_extension TEXT UNIQUE DEFAULT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'UNIQUE (level_id, weight)' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE legallabels (' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'labeltype_id INTEGER REFERENCES labeltypes(id),' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'label TEXT,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY (labeltype_id,name,label)' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE paths (' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'file_extension TEXT NOT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'path TEXT NOT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(file_extension,path)' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE tracks (' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT UNIQUE NOT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'file_extension TEXT NOT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'path TEXT NOT NULL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(file_extension)' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE variables (' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT PRIMARY KEY ON CONFLICT REPLACE,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'definition TEXT NOT NULL' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE segments (' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'utterance_id INTEGER REFERENCES utterances(id),' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'labeltype_id INTEGER REFERENCES labeltypes(id),' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'label_id INTEGER,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'start REAL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'end REAL,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'label TEXT,' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY (utterance_id,labeltype_id,label_id)' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');' [Sat Nov 20 14:50:10 CET 2010] [bench] [info] '3.7.2 {2010-08-23 18:52:01 42537b60566f288167f1b5864a5435986838e3a3}' no such table: main.utterances while executing "db eval $sql" (procedure "install_indices" line 12) invoked from within "install_indices $dbDir " ("uplevel" body line 8) invoked from within "uplevel \#0 $opts(-pre)" (procedure "bench" line 52) invoked from within "bench -desc "EmuQuery: Test simple query {John=fourth} in one file" -pre { set dbDir [generate_benchmark_database 1] puts $dbDir set tgFiles [glob ..." (file "./emuquery.benchmark" line 17) invoked from within "source $BENCH(file)" ("foreach" body line 4) invoked from within "foreach BENCH(file) $BENCH(FILES) { if {[file exists $BENCH(file)]} { FEEDBACK [list Sourcing $BENCH(file)] source $BENCH(file) } }" invoked from within "if {$BENCH(THREADS)} { # Each file must run in it's own thread because of all the extra # header stuff they have. #set DEBUG 1 proc th..." (file "/Library/Tcl/tcllib1.11/bench/libbench.tcl" line 436) while executing "close [Process [open |[linsert $cmd end $file] r+]]" while executing "error $::errorInfo" (procedure "Invoke" line 40) invoked from within "Invoke $ip $ver {} " (procedure "::bench::run" line 74) invoked from within "::bench::run -errors 1 -iters 10 -match Emu*simple*one* [list /usr/local/bin/tclsh8.6 ] ./emuquery.benchmark " invoked from within "set b [::bench::run -errors 1 -iters 10 -match Emu*simple*one* [list /usr/local/bin/tclsh8.6 ] ./emuquery.benchmark ] " (file "run_all_benchmarks.tcl" line 11) --------------------------------------------------------------------------------------------------------------------------------------------------------- If I dump the database created though, I get only this: host-78-64-143-238:-Tmp- zak$ sqlite3 db.sqlite3 SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .dump BEGIN TRANSACTION; COMMIT; The complaint I get from the code above is from me trying to insert indices on the utterances table, which does of course not exist in the empty database then... :-/ /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