[Milan-pm] Creating a SQLite DB and loading a schema
marcos rebelo
oleber at gmail.com
Thu Oct 8 05:36:51 PDT 2009
Hi all
I need to define and test a SQLite Data Base in disk and in memory.
- I need to load the schema in the __DATA__ area into the data base.
- I need to create a foreign key in user.organization_id to
organization.organization_id
How do I solve this?
use strict;
use warnings;
use v5.10;
use Data::Dumper;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=db.sqlite", "", "");
$/ = undef;
$dbh->do(<DATA>) or die $dbh->errstr;
say 'END';
__DATA__
DROP TABLE IF EXISTS organization;
CREATE TABLE IF NOT EXISTS organization (
organization_id VARCHAR PRIMARY KEY NOT NULL,
email VARCHAR NOT NULL
);
--
DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (
email VARCHAR PRIMARY KEY NOT NULL,
organization_id VARCHAR NOT NULL -- Foreign key
);
--
DROP TABLE IF EXISTS organization_document_type;
CREATE TABLE IF NOT EXISTS organization_document_type (
organization_id VARCHAR PRIMARY KEY NOT NULL,
document_type VARCHAR NOT NULL,
letter_color VARCHAR NOT NULL,
letter_print_both_sides INTEGER NOT NULL,
letter_type VARCHAR NOT NULL,
email_form VARCHAR NOT NULL,
email_subject VARCHAR NOT NULL,
email_body VARCHAR NOT NULL
);
CREATE TRIGGER insert__organization_document_type__letter_color
BEFORE INSERT ON organization_document_type
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'organization_document_type.letter_color invalid')
WHERE NEW.letter_color NOT IN ( 'BW', 'COLOR' );
END;
CREATE TRIGGER update__organization_document_type__letter_color
BEFORE UPDATE OF letter_color ON organization_document_type
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'organization_document_type.letter_color invalid')
WHERE NEW.letter_color NOT IN ( 'BW', 'COLOR' );
END;
Best Regards
Marcos Rebelo
--
Marcos Rebelo
http://oleber.freehostia.com
Milan Perl Mongers leader http://milan.pm.org
--
Marcos Rebelo
http://oleber.freehostia.com
Milan Perl Mongers leader http://milan.pm.org
More information about the Milan-pm
mailing list