[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