tdbc::sqlite3

Artifact 771ca772f53bf1fd9026c7a0c5b6679196c6a67e7f0cce95d1b5a5bd92d8f259:

Attachment "test.tcl" to ticket [8d015d929f] added by anonymous 2019-08-31 15:07:47.

---

package require tdbc::sqlite3
set fnm test.db
if {[file exists $fnm]} {
    file delete $fnm
}
set db [tdbc::sqlite3::connection new $fnm]

$db allrows {CREATE TABLE teams (
			       tea_id INTEGER PRIMARY KEY AUTOINCREMENT,
			       tea_name CHAR
			       )}
set stmt [$db prepare {
    INSERT INTO teams (tea_name) VALUES (:name)
}]
foreach team {
    "Arsenal"
    "Aston Villa"
    "Bournmouth and Boscombe United"
    "Brighton and Hove Albion"
    "Burnley"
    "Chelsea"
    "Crystal Palace"
    "Everton"
    "Leicester City"
    "Liverpool"
    "Manchester City"
    "Manchester United"
    "Newcastle United"
    "Norwich City"
    "Sheffield United"
    "Southampton"
    "Tottenham Hotspur"
    "Watford"
    "West Ham United"
    "Wolverhampton Wanderers"
} {
    $stmt execute [dict create name $team]
}
$stmt close

# puts stdout [join [$db allrows -as lists {SELECT * FROM teams}] \n]
puts stdout [join [lmap row [$db allrows -as lists {SELECT * FROM teams}] {
    format "%2d %s" {*}$row
}] \n]
puts stdout ""

$db allrows {CREATE TABLE fixtures (
				    fix_id INTEGER PRIMARY KEY AUTOINCREMENT,
				    fix_date CHAR,
				    fix_home INTEGER REFERENCES teams (tea_name),
				    fix_away INTEGER REFERENCES teams (tea_away)
				    )
}

set stmt [$db prepare {
    INSERT INTO fixtures (fix_date, fix_home, fix_away)
    VALUES (:date, :home, :away)
}]
foreach {date home away} {
    2019-08-31 16 12
    2019-08-31  6 15
    2019-08-31  7  2
    2019-08-31  9  3
    2019-08-31 11  4
    2019-08-31 13 18
    2019-08-31 19 14
    2019-08-31  5 10
    2019-09-01  8 20
    2019-09-01  1 17
} {
    $stmt execute [dict create date $date home $home away $away]
}

puts stdout [join [$db allrows -as lists {SELECT * FROM fixtures}] \n]
puts stdout ""

puts stdout [join [$db allrows -as lists {
    SELECT
        fix_date,
        home.tea_name,
        away.tea_name
    FROM
    fixtures
    JOIN
    teams AS home ON home.tea_id = fix_home
    JOIN
    teams AS away ON away.tea_id = fix_away
}] \n]