Posted to tcl by tomk at Tue Nov 16 02:41:33 GMT 2010view raw

  1. package require TclOO
  2. package require sqlite3
  3. package require tdbc::sqlite3
  4.  
  5. ##
  6. ## CREATE DATABASES
  7. ##
  8. ::tdbc::sqlite3::connection create db [file normalize [file join . db.sqlite3]]
  9.  
  10. ##
  11. ## JOINS
  12. ##
  13. puts ""
  14. set stmtHandle [db prepare {
  15. CREATE TABLE department (
  16. DepartmentID int UNIQUE,
  17. DepartmentName varchar(25)
  18. );
  19. CREATE TABLE employee (
  20. LastName varchar(25),
  21. DepartmentID int,
  22. FOREIGN KEY (DepartmentID) REFERENCES department(DepartmentID)
  23. );
  24. }]
  25. ${stmtHandle} execute
  26. set recs {
  27. {31 Sales}
  28. {33 Engineering}
  29. {34 Clerical}
  30. {35 Marketing}
  31. }
  32. set stmtHandle [db prepare {INSERT INTO department VALUES(:id,:name)}]
  33. foreach rec ${recs} {
  34. lassign ${rec} id name
  35. ${stmtHandle} execute
  36. }
  37. set recs {
  38. {Rafferty 31}
  39. {Jones 33}
  40. {Steinberg 33}
  41. {Robinson 34}
  42. {Smith 34}
  43. {John NULL}
  44. }
  45. set stmtHandle [db prepare {INSERT INTO employee VALUES(:name,:id)}]
  46. foreach rec ${recs} {
  47. lassign ${rec} name id
  48. ${stmtHandle} execute
  49. }
  50. puts "- employee -"
  51. puts [join [db allrows -as lists -- {SELECT * FROM employee}] \n]
  52. puts "- department -"
  53. puts [join [db allrows -as lists -- {SELECT * FROM department}] \n]
  54. puts "- inner join -"
  55. puts [join [db allrows -as lists -- {
  56. SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
  57. FROM employee INNER JOIN department
  58. ON employee.DepartmentID=department.DepartmentID
  59. }] \n]
  60. puts "- cross join -"
  61. puts [join [db allrows -as lists -- {
  62. SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
  63. FROM employee JOIN department;
  64. }] \n]
  65.  
  66. ##
  67. ## DELETE TABLES
  68. ##
  69. set stmtHandle [db prepare {
  70. DROP TABLE t1;
  71. DROP TABLE t2;
  72. }]
  73.  
  74. ##
  75. ## RESULT
  76. ##
  77. if 0 {
  78. [tom]$ tclsh main2.tcl
  79.  
  80. - employee -
  81. Rafferty 31
  82. Jones 33
  83. Steinberg 33
  84. Robinson 34
  85. Smith 34
  86. John NULL
  87. - department -
  88. 31 Sales
  89. 33 Engineering
  90. 34 Clerical
  91. 35 Marketing
  92. - inner join -
  93. Rafferty 31 Sales 31
  94. Jones 33 Engineering 33
  95. Steinberg 33 Engineering 33
  96. Robinson 34 Clerical 34
  97. Smith 34 Clerical 34
  98. - cross join -
  99. Rafferty 31 Sales 31
  100. Rafferty 33 Engineering 33
  101. Rafferty 34 Clerical 34
  102. Rafferty 35 Marketing 35
  103. Jones 31 Sales 31
  104. Jones 33 Engineering 33
  105. Jones 34 Clerical 34
  106. Jones 35 Marketing 35
  107. Steinberg 31 Sales 31
  108. Steinberg 33 Engineering 33
  109. Steinberg 34 Clerical 34
  110. Steinberg 35 Marketing 35
  111. Robinson 31 Sales 31
  112. Robinson 33 Engineering 33
  113. Robinson 34 Clerical 34
  114. Robinson 35 Marketing 35
  115. Smith 31 Sales 31
  116. Smith 33 Engineering 33
  117. Smith 34 Clerical 34
  118. Smith 35 Marketing 35
  119. John 31 Sales 31
  120. John 33 Engineering 33
  121. John 34 Clerical 34
  122. John 35 Marketing 35
  123. [tom]$
  124.  
  125. }