This is the first article of series to explore the Perl DBIx class.
I am using Cygwin, and my work dir is at "/cygdrive/c/mynb/mybookshop" or c:/mynb/mybookshop
Step 1) Let us start with a simple DDL using SQLite3:
CREATE TABLE books (
id INTEGER PRIMARY KEY,
name TEXT,
description TEXT,
isbn TEXT,
price DOUBLE,
fk_category_id INT REFERENCES categories,
fk_author_id INT REFERENCES authors
);
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY,
name TEXT
);
and save those as books.sql......
Step 2)
The next step is to create the Database....and simply type this in the command line:
cat books.sql | sqlite3 books.db
now in C:/mynb/mybookshop, I have both books.sql & books.db
Step 3)
we will start creating the corresponding classes...we will use the DBIx::Class:Schema::Loader=make_schema_at
here is the script that I have used in the command line:
perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir=./dbix -e 'make_schema_at("BookDB::Schema::DB", {debug => 1}, ["dbi:SQLite:dbname:books.db","",""])'
pay attention to (a) the dump_to_dir parm "./dbix" --> this will create dbix directory
under the current one
and (b) the "BookDB::Schema::DB" --> would put the Schema classes (Books,
Authors, and Categories) under the directory path of BookDB/Schema/DB
under the dbix.
the resulting directory structs will be as follows:
Verify that under BookDB/SChema directory, you should see the "DB.pm" Perl module that contains the following:
package BookDB::Schema::DB;
use strict;
use warnings;
use base 'DBIx::Class::Schema';
__PACKAGE__->load_classes;
.....
1;
and under BookDB/Schema/DB directory, you have 3 modules: Authors.pm, Books.pm, and Categories.pm
I have attached snippets of the Authors.pm as follows:
Notice the has_many relationships that indicates that a book may have more than one author.
4) now that everything is in place, let us start with how to insert the data into the DB through the dbix classes
For starters I went to the Barnesandnobles.com and got some samples data as follows:
To Try Mens Souls by Newt Gingrich
Half Broke Horses by Jeannette Walls
Jane Austen by Jane Austen
Dog Days by Jeff Kinney
No Less Than Victory by Jeff Shaara
The Book of Genesis Illustrated by R Crumb
Homecoming by Patricia Briggs
Diary of a Wimpy Kid Four Book Set by Jeff Kinney
The Lovely Bones by Alice Sebold
The Girl with the Dragon Tattoo by Stieg Larsson
The Elegance of the Hedgehog by Muriel Barbery
Marvel Encyclopedia 2009 by DK Publishing
The Coliseum Con by Geronimo Stilton
The Guernsey Literary and Potato Peel Pie Society by Mary Ann Shaffer
....
I save the data as "books.txt" and now it is a matter of
using Regex to create tables for the Authors and the Books.
Once the authors are in place, now it is time to start populating the Books table
now we have got some data...it is time for some exploration :)
use BookDB::Schema::DB;
use BookDB::Schema::DB::Books;
use Data::Dumper;
my $bookdb = BookDB::Schema::DB->connect ("dbi:SQLite:books.db",
'', '', {RaiseError=>1});
open (BOOKS,"books.txt");
my @books_array;
my $books = $bookdb->resultset("Books");
my $authors = $bookdb->resultset("Authors");
my $book = $books->search_like({name => 'Naruto%'})->next();
print $book->name, $book->fk_author_id->name;
...which in short is trying to find a book whose title has "Naruto" and retrieve the author's name as well.
..if everything is fine up to this point, then congrats....you are on your way to explore further what DBIx can do to your project....
if you are into Web development, you could leverage DBIx model within the spirit of MVC (Model View Controller) such as with Catalyst framework...which could look like this:
I will cover the integration of your DBIx model within the Catalyst MVC frameworks in the next blog :)....
Let me know if you have any comments or suggestions.
