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
