Posted to tcl by patthoyts at Wed Oct 31 15:45:17 GMT 2007view raw

  1. # If the database tables are not present - create them. Note we are
  2. # using SQL compatible types which will also work with Perl's libdbi.
  3. # (sqlite3 doesn't really care about type).
  4. proc wxdspider::InitDB {} {
  5. variable App
  6. set App(db) [DIO::handle $App(db:type) [namespace current]::db \
  7. -user $App(db:user) -pass $App(db:pass) \
  8. -host $App(db:host) -db $App(database)]
  9.  
  10. # Check if the table is present already and create if not.
  11. set r [$App(db) exec {SELECT COUNT(filename),COUNT(scantype) FROM files;}]
  12. if {[$r errorcode] != 0} {
  13. set create_files {CREATE TABLE files
  14. (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  15. filename VARCHAR(255),mtime INT,path TEXT,title TEXT,
  16. description TEXT,user VARCHAR(64),creator VARCHAR(64),
  17. status TEXT,errorcode INT,started DATETIME,stopped DATETIME,
  18. laser VARCHAR(64),grating VARCHAR(64),beampath VARCHAR(16),
  19. wirever VARCHAR(128),mmtver VARCHAR(16),ismap INT,
  20. isseries INT,size INT,collected INT,derived INT,
  21. linefocus INT,maptype VARCHAR(32),focustrack INT,
  22. scantype VARCHAR(64),exposure INT,
  23. serial_camera VARCHAR(6),serial_instrument VARCHAR(6));
  24. }
  25. set create_errors {CREATE TABLE errors
  26. (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  27. filename VARCHAR(255),time DATETIME,path TEXT,
  28. message TEXT,trace TEXT);
  29. }
  30.  
  31. foreach table [list files errors] {
  32. set r [$App(db) exec [set create_$table]]
  33. if {[$r errorcode] != 0} {
  34. Error "failed to create '$table' table: [$r errorinfo]"
  35. }
  36. }
  37.  
  38. foreach col [list user creator errorcode maptype laser linefocus] {
  39. set sql "CREATE INDEX idx_$col ON files($col);"
  40. set r [$App(db) exec $sql]
  41. if {[$r errorcode] != 0} {
  42. Error "failed to create index '$col': [$r errorinfo]"
  43. }
  44. }
  45. set r [$App(db) exec {CREATE VIEW v_stats AS
  46. SELECT COUNT(filename), SUM(size),MIN(size),MAX(size) FROM files;}]
  47. if {[$r errorcode] != 0} {
  48. Error "failed to create stats view"
  49. }
  50. }
  51. }