tcl sqlite examples

nscerqueira Some examples of tcl and sqlite usage:

NOTE: the name of the database is opendb

Retrieve the names of the tables stored in a sqlite database:

package require sqlite3

sqlite3 opendb "database.db" ; # Open up an existing database or create a new empty one.
set tableNames [opendb eval {SELECT tbl_name FROM sqlite_master}]
puts $tableNames

Retrieve the names of the columns stored in a sqlite database(variable columnName):

opendb eval "SELECT * FROM $tableName LIMIT 1" x {set columnName $x(*)} 
puts $columnName

APE Note that this does not work if table is empty.

jnc You can also use the PRAGMA statement table_info(table_name) to retrieve column information. See http://sqlite.org/pragma.html for a list of all PRAGMA statements.

set columns [opendb eval "PRAGMA table_info($tableName)"]
puts $columns

Retrieve the row data from a table

opendb eval "Select * FROM $i" values {
   set columnNames $values(*)
   set rowList {}
   foreach column $columnNames {
      lappend rowList $values($column)
   }
   puts $rowList
}

KWizzz: Above example executes set columnNames $values(*) for each row. You might want to use this instead:

proc columnNames {db query} {
    # execute query with no actions in order to get column names
    $db eval $query columns break
    set h $columns(*)
    return $h
}

proc columnValues {columnNames {sep ";"}} {
    set r {}
    foreach name $columnNames {
        upvar $name value
        set value [string map {\" \"\"} $value]
        lappend r \"$value\"
    }
    set values [join $r $sep]
    return $values
}

set sql "Select * FROM $i"
set h [columnNames opendb $sql]
puts $h
opendb eval $sql {
    puts [columnValues $h
}

JOB: Anyone interested in some date arithmetics - the sqlite database can do it all! The classical days between date function the SQL way goes like this:

-- note: sqlite doesn't need select ... from dual
SELECT julianday ( current_date ) - julianday( DATE('....-09-25'));

escargo 2012-01-31: Anybody have a clue on how to decipher the SQLite tables used by Firefox 3 bookmarks? I thought it might be easier to read the database directly rather than export HTML and then parse the result, but the structure is not documented anywhere I can find, and there seems to be no "how to" on how to navigate through all the tables.

Category Example

Category SQLite