Posted to tcl by patthoyts at Wed Oct 31 15:45:17 GMT 2007view raw
- # If the database tables are not present - create them. Note we are
- # using SQL compatible types which will also work with Perl's libdbi.
- # (sqlite3 doesn't really care about type).
- proc wxdspider::InitDB {} {
- variable App
- set App(db) [DIO::handle $App(db:type) [namespace current]::db \
- -user $App(db:user) -pass $App(db:pass) \
- -host $App(db:host) -db $App(database)]
- # Check if the table is present already and create if not.
- set r [$App(db) exec {SELECT COUNT(filename),COUNT(scantype) FROM files;}]
- if {[$r errorcode] != 0} {
- set create_files {CREATE TABLE files
- (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- filename VARCHAR(255),mtime INT,path TEXT,title TEXT,
- description TEXT,user VARCHAR(64),creator VARCHAR(64),
- status TEXT,errorcode INT,started DATETIME,stopped DATETIME,
- laser VARCHAR(64),grating VARCHAR(64),beampath VARCHAR(16),
- wirever VARCHAR(128),mmtver VARCHAR(16),ismap INT,
- isseries INT,size INT,collected INT,derived INT,
- linefocus INT,maptype VARCHAR(32),focustrack INT,
- scantype VARCHAR(64),exposure INT,
- serial_camera VARCHAR(6),serial_instrument VARCHAR(6));
- }
- set create_errors {CREATE TABLE errors
- (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- filename VARCHAR(255),time DATETIME,path TEXT,
- message TEXT,trace TEXT);
- }
- foreach table [list files errors] {
- set r [$App(db) exec [set create_$table]]
- if {[$r errorcode] != 0} {
- Error "failed to create '$table' table: [$r errorinfo]"
- }
- }
- foreach col [list user creator errorcode maptype laser linefocus] {
- set sql "CREATE INDEX idx_$col ON files($col);"
- set r [$App(db) exec $sql]
- if {[$r errorcode] != 0} {
- Error "failed to create index '$col': [$r errorinfo]"
- }
- }
- set r [$App(db) exec {CREATE VIEW v_stats AS
- SELECT COUNT(filename), SUM(size),MIN(size),MAX(size) FROM files;}]
- if {[$r errorcode] != 0} {
- Error "failed to create stats view"
- }
- }
- }