sqlite_master

The SQLite database holds the database schema in the table sqlite_master.

Table definition

The table definition is as follows:

• Type : Type is Index or Table.
• name : Index or table name
• tbl_name : Name of the table
• rootpage : rootpage, internal to SQLite
• sql : The SQL statement, which would create this table. Example:

     CREATE TABLE [table2] ([t2c1] VARCHAR, [t2c2] VARCHAR, CONSTRAINT "p_key" PRIMARY KEY ([t2c1], [t2c2]) ON CONFLICT ABORT)

find defined tables

The following procedure returns a list of defined tables.

Its functionality is similar to the tclODBC command db tables.

proc GetTables {{NameStart ""}} {
    return [db eval "select tbl_name from sqlite_master\
        where (type = \"table\") and (tbl_name like \"${NameStart}%\")"]
}

PRAGMA functions to query information of the schema (e.g. sqlite_master)

find defined columns of a table (from KBK)

The functionality is similar to the tclODBC command db columns.

TABLE_INFO calls the attached function once for each column with the following array elements set:

• cid: Column ID: 0,1,2,...
• name: column name
• type: type. Example: varchar (16)
• notnull: 0/1
• dflt_value: Default value
• pk: Primary key: 0/1

proc cols {db table} {
    set result {}
    $db eval "PRAGMA TABLE_INFO($table)" row { lappend result $row(name) }
    return $result
}

find information about keys

Get primary key columns:

proc pk {db table} {
    set result {}
    $db eval "PRAGMA TABLE_INFO($table)" row {
        if {$row(pk)} {
            lappend result $row(name)
        }
    }
    return $result
}

Get a matrix of keys by enumerating the unique keys:

The primary key is missing in the list, if it is on a column of type integer.

INDEX_LIST returns:

• seq : 0,...
• name: Index name
• unique: 0/1

INDEX_INFO returns:

• seqno: 0,1,...
• cid: Column id
• name: column name

proc keys {db table} {
    set result {}
    $db eval "PRAGMA INDEX_LIST($table)" row {
        if {$row(unique)} {
            set keycols {}
            $db eval "PRAGMA INDEX_INFO($row(name))" detail {
                lappend keycols $detail(name)
            }
            lappend result $keycols
        }
    }
    return $result
}

See also

• SQLite
• tdbc