1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
#!perl -w
use strict;
use DBI;
my $dbfile="test.db";
my $dsn = "dbi:SQLite:dbname=$dbfile";
my $username = "";
my $password = "";
my %attr = ( RaiseError => 1, AutoCommit => 0 );
my $dbh = DBI->connect($dsn, $username, $password,\%attr ) ;
# FK
$dbh->do("pragma foreign_keys=ON;");
#artist
$dbh->do("DROP TABLE IF EXISTS artist");
$dbh->do("DROP TABLE IF EXISTS track");
$dbh->do("CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
)");
$dbh->do("INSERT INTO artist VALUES(1, 'Dean Martin')");
$dbh->do("INSERT INTO artist VALUES(2, 'Frank Sinatra')");
#track
$dbh->do("CREATE TABLE track(
trackid INTEGER PRIMARY KEY,
trackname TEXT,
artist INTEGER NOT NULL,
FOREIGN KEY(artist) REFERENCES artist(artistid)
)");
$dbh->do("INSERT INTO track VALUES(1, 'That''s Amore', 1 )");
$dbh->do("INSERT INTO track VALUES(2, 'Christmas Blues', 1 )");
$dbh->do("INSERT INTO track VALUES(3, 'My Way', 2 )");
# TEST 1
$dbh->do("INSERT INTO track VALUES(14, 'Mr. Bojangles', 3)");
$dbh->do("DELETE FROM artist WHERE artistid=2");
$dbh->commit;
$dbh->disconnect; |
Partager