Purpose

You have a database. You want to turn it into a website with as little effort as possible. And it has to be Web 2.2 compliant: when people choose a date, they point and click on a little calendar rather than using HTML pulldown menus. Users can page through long lists of data without reloading a web page for each windowful of displayed information. That sort of thing. Today we will start from scratch and use available free perl modules to quickly build a fully-functioning web interface to your existing relational database that's all pimped out with JavaScript, and CSS, and AJAX and all that fancy stuff.

This is a "bare metal" web application that gives you a clear window into your database and its structure of tables and relationships. After its generated you can examine it to learn how to build your own application.

Caveat Lector

This "tutorial" was started by a relative beginner, based on available documentation, experimentation, and answers provided by experts to specific questions. We don't know if this is the right way to do things, but this is what worked for us. Positive contributions to this page are encouraged.

Start From Scratch

First we needed a hosting provider. The author searched the internet for virtual machine hosting, and purchased a virtual FreeBSD 8.0 machine. This tutorial should work with any functional unix platform. We installed the webserver and database, perl, Catalyst and everything else we needed, including:

  • Rose::DB will be our Object-Relational-Mapper (ORM)
  • Rose::HTML::Form is our HTML form solution.
  • Rose::DBx::Garden connects the above two items to each other
  • Rose::DBx::Garden::Catalyst will connect the Garden to the Catalyst framework.
  • The user-interface is provided by CatalystX::CRUD::YUI. YUI stands for YavaScript User Interface. You can read all about it here.

Realistically we spent a bunch of time installing other perl modules. See your error messages for information on which ones you're missing.

Which Database?

Since we're doing this on the cheap (and since we don't trust closed source code running on our machine) our two choices for database engine are PostgreSQL and MySQL. We chose Postgres because we want to be able to extract the most meta-information about our database as possible. Your results may vary.

Then we built our database, being certain than all primary, unique, and foreign keys to be correct. We normalized our database in conformance to the Third Normal Form. We also made sure our database conformed to the default conventions of Rose::DB::Object::ConventionManager .

Other database things to watch out for:

  • All your foreign key constraints must be correct.
  • Things seem to go better in tables that have a single-column surrogate integer primary key, but that may or may not be strictly necessary.
  • Don't use deprecated data types in Postgres, such as 'money.' The type will not be recognized and its form field length will be zero.
  • Remember that 'character', and 'character varying' types have lengths. The PostgreSQL server won't complain if it's missing, but your form fields will come out wrong.
  • The application called pgAdmin provides a graphical interface allowing complete control over a PostgreSQL server, including creation and modification of the database schema. Competence with PostgreSQL is required to use this program effectively. pgadmin.org

Speed Web Development

Here's the amazingly fast creation of a database-driven website. Type these commands at your favorite shell prompt:

catalyst.pl MyApp
cd MyApp
perl Makefile.PL

Now you're in your new application folder. You'll see the folder called lib/ where all the perl code will go. The next step is to create a way for your application to connect to your database. Here's the file we created in the lib/ folder. We called it lib/MyDB.pm.

package MyDB;
use strict;
use warnings;
use base 'Rose::DB';
    
MyDB->register_db (
    database => 'mydatabase',
    driver   => 'Pg',
    username => 'myuser'
    );
1;

(Warning: there is no password in this example. It's your own responsibility to secure your database server.)

Next we need a script to automagically create our application website. It only does three things: (1) it uses Rose::DBx::Garden::Catalyst, (2) it configures said module, and (3) it does the deed, calling the method that creates almost all the files you need in order to have a working website. We called this perl script mk_garden.pl.

#!/path/to/perl
use MyDB;
use Rose::DBx::Garden::Catalyst;
    
my $garden = Rose::DBx::Garden::Catalyst->new(
    garden_prefix               => 'RDBO',
    catalyst_prefix             => 'MyDatabase',
    controler_prefix            => 'MyController'
    db                          => MyDB->new,
    debug                       => 1,
    perltidy_opts               => '-pbp -nst -nse',                                      
    tt                          => 1,                 
    include_autoinc_form_fields => 0,
    column_to_label             =>
        sub {
            my ($garden_obj$col_name) = @_;
           return join(' 'map { ucfirst } split(/_/$col_name));
        },
    );
$garden->plant('lib');

That last line of code, the one calling the method plant(), that's where the magic is. That calls the method that creates almost all the files of your new application website (besides the ones you created using `catalyst.pl).

Next you must set the default view for Catalyst. Edit myapp.conf and add one line:

default_view RDGC

Install The HTML Templates and Stylesheets

Find your system perl libraries, and make yourself copies of all the distributed template files to your root/ folder so you can change them for your own purposes: The command from your application directory is something like:

cp -vRr /path/to/perl5/CatalystX/CRUD/YUI/TT/* root

Then your template files are in root/crud, and your stylesheets are in root/css. (You might have to make the files writable in order to edit them.)

Remove the line from your crud/tt_config.tt that indicates that it is a default configuration file:

ThisIsDefTTConfig = 1;

You now have a working site. Next, you customize it until it's the way you want it.

Configure Your Menus

All good applications have a pulldown menubar. Yours is no exception. Edit your file called root/crud/schema_menu.tt to configure your menu options and their actions.

Installing the JavaScript Libraries on Your Server

You'll want to serve your own copies of the JavaScript libraries.

Read the installation instructions that come with those libraries. After installing them. update your tt_config.tt file to so reflect.

And We're Done!

At least this is as far as the authors have gotten. If you've done this and have gotten further, please add to this wiki page.

My tags:
 
Popular tags:
 
Powered by Catalyst
Powered by MojoMojo Hosted by Shadowcat - Managed by Nordaaker