Posted to tcl by tomk at Tue Nov 16 02:41:33 GMT 2010view raw
- package require TclOO
- package require sqlite3
- package require tdbc::sqlite3
- ##
- ## CREATE DATABASES
- ##
- ::tdbc::sqlite3::connection create db [file normalize [file join . db.sqlite3]]
- ##
- ## JOINS
- ##
- puts ""
- set stmtHandle [db prepare {
- CREATE TABLE department (
- DepartmentID int UNIQUE,
- DepartmentName varchar(25)
- );
- CREATE TABLE employee (
- LastName varchar(25),
- DepartmentID int,
- FOREIGN KEY (DepartmentID) REFERENCES department(DepartmentID)
- );
- }]
- ${stmtHandle} execute
- set recs {
- {31 Sales}
- {33 Engineering}
- {34 Clerical}
- {35 Marketing}
- }
- set stmtHandle [db prepare {INSERT INTO department VALUES(:id,:name)}]
- foreach rec ${recs} {
- lassign ${rec} id name
- ${stmtHandle} execute
- }
- set recs {
- {Rafferty 31}
- {Jones 33}
- {Steinberg 33}
- {Robinson 34}
- {Smith 34}
- {John NULL}
- }
- set stmtHandle [db prepare {INSERT INTO employee VALUES(:name,:id)}]
- foreach rec ${recs} {
- lassign ${rec} name id
- ${stmtHandle} execute
- }
- puts "- employee -"
- puts [join [db allrows -as lists -- {SELECT * FROM employee}] \n]
- puts "- department -"
- puts [join [db allrows -as lists -- {SELECT * FROM department}] \n]
- puts "- inner join -"
- puts [join [db allrows -as lists -- {
- SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
- FROM employee INNER JOIN department
- ON employee.DepartmentID=department.DepartmentID
- }] \n]
- puts "- cross join -"
- puts [join [db allrows -as lists -- {
- SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
- FROM employee JOIN department;
- }] \n]
- ##
- ## DELETE TABLES
- ##
- set stmtHandle [db prepare {
- DROP TABLE t1;
- DROP TABLE t2;
- }]
- ##
- ## RESULT
- ##
- if 0 {
- [tom]$ tclsh main2.tcl
- - employee -
- Rafferty 31
- Jones 33
- Steinberg 33
- Robinson 34
- Smith 34
- John NULL
- - department -
- 31 Sales
- 33 Engineering
- 34 Clerical
- 35 Marketing
- - inner join -
- Rafferty 31 Sales 31
- Jones 33 Engineering 33
- Steinberg 33 Engineering 33
- Robinson 34 Clerical 34
- Smith 34 Clerical 34
- - cross join -
- Rafferty 31 Sales 31
- Rafferty 33 Engineering 33
- Rafferty 34 Clerical 34
- Rafferty 35 Marketing 35
- Jones 31 Sales 31
- Jones 33 Engineering 33
- Jones 34 Clerical 34
- Jones 35 Marketing 35
- Steinberg 31 Sales 31
- Steinberg 33 Engineering 33
- Steinberg 34 Clerical 34
- Steinberg 35 Marketing 35
- Robinson 31 Sales 31
- Robinson 33 Engineering 33
- Robinson 34 Clerical 34
- Robinson 35 Marketing 35
- Smith 31 Sales 31
- Smith 33 Engineering 33
- Smith 34 Clerical 34
- Smith 35 Marketing 35
- John 31 Sales 31
- John 33 Engineering 33
- John 34 Clerical 34
- John 35 Marketing 35
- [tom]$
- }