Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
Perl - database access - DBD, DBI and DBIx modules

How Perl DBD, DBI and DBIx fit inPerl's DBI module provides an excellent link from SQL databases to Perl, and the assorted DBD modules that fit underneath it allow it to operate well for a whole lot of different databases. But they are only excellent as far as they go - for the SQL statements have to be generated in your application code, rather than keeping the database instructions clear of your code. Enter the DBIx module, which provides an interface from Perl language variables to database tables. Using DBIx (which calls DBI then DBD internally), you can extract the SQL from the application and generate it, encapsulated, within the DBIx classes.

The diagram shows - in the top line - how you need to code both your application and your SQL to application translation if you do not use DBIx. In contrast, the bottom line shows how you only need to code your application if you use DBIx.

I've started with a "Control" example - showing direct access to an SQLite database through DBI and DBD - source code [here] (and there's an older version using MySQL [here]). You'll note if you look at this code that it's quite short, but contains all sorts of SQL statements.

My second example uses DBIx. Look at this:
  my $schema = D2::Main->connect('dbi:SQLite:d2_test.sqlite');
  my @teamadditions = ( ["Tom", 5], ["Jerry", 6]);
  $schema->populate('People', [ [qw/name pid/], @teamadditions ]);

Those three lines of application code ... which contain no SQL ... add two rows to an SQL table using DBIx. The source code (with two use statements to complete it!) may be found [here].

The "magic" of DBIx is in one of those uses - that pulls in separate datatype definitions:
  package D2::Main;
  use base qw/DBIx::Class::Schema/;

Which in turn pull in the table definition:
  package D2::Main::Result::People;
  use base qw/DBIx::Class::Core/;
  __PACKAGE__->add_columns(qw/ pid name /);

(You can download these two files [here] and [here])

The final file for this application - which sets up the database and some initial test data - is [here].

You'll find sample outputs from the programs, and details of how to run it, in the first file - and it's a straightforward and complete DBIx example; I'll go so far as to say it's the simplest I've seen around as the examples in the official documentation show off a lot more facilities - but set the hurdle far higher for you when you first want to try DBIx.

I have allowed myself to add further complexity (generating selects with joined tables, sorting my output, etc) in my third example. Once you've tried the example above, have a look at this set of files:

SQL to set up the tables - [here].
The main application, showing two examples of data extraction - [here]. INCLUDES SAMPLE RUN and DOCUMENTATION
The model loader - [here].
Defining a table of computers (and how it relates to a people table) - [here].
Defining a table of people (and how it relates to a computers table) - [here].

Once you get the hand of DBIx, it's a nice way to help keep the model layer in its own compartment, though you still need to know SQL subjects such as database normalization to come up with good designs. We're covering Perl to Database interfaces on this week's Perl for Larger Projects course, and we also cover it briefly on the more introductory Perl Programming Course. The DBD and DBI modules are usable by people who are quite new to Perl; some of the DBIx data structures, though, are rather ambitious for the first week - easy to use once you know them, but perhaps not too easy to learn.
(written 2010-12-22, updated 2010-12-24)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
P308 - Using SQL Databases from Perl
  [2561] The future of MySQL - (2010-01-03)
  [2381] Checking the database connection manually - (2009-08-28)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1224] Object Relation Mapping (ORM) - (2007-06-09)
  [975] Answering ALL the delegate's Perl questions - (2006-12-09)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [515] MySQL - an FAQ - (2005-12-03)

S156 - Interfacing Applications to MySQL Databases
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

S200 - MySQL - SQLite
  [4024] SQL databases from Python - an SQLite example - (2013-03-02)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [3269] Files or Databases? MySQL, SQLite, or Oracle? - (2011-04-28)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)

Back to
Learning Object Orientation in Perl through bananas and perhaps Moose
Previous and next
Horse's mouth home
Forward to
Looking ahead and behind in Regular Expressions - double matching
Some other Articles
Thank you - and Happy Christmas
AND and OR operators - what is the difference between logical and bitwise varieties?
The week before Christmas
Looking ahead and behind in Regular Expressions - double matching
Perl - database access - DBD, DBI and DBIx modules
Learning Object Orientation in Perl through bananas and perhaps Moose
Making Perl class definitions more conventional and shorter
Contrast in pictures
The Christmas Season has arrived
Setting your user_agent in PHP - telling back servers who you are
4759 posts, page by page
Link to page ... 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, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/3099_Per ... dules.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat