Cruddy! Official Web Site
CRUD now, code later.

SourceForge

Introduction

While your technical teams battle over which web/database application framework to implement ( STRUTS, Ruby on Rails, Catalyst, Zope, WaveMaker, Coldfusion, etc) your systems administrator can have Cruddy! up within an hour and your team can start being productive today. Similar to phpMyAdmin in the plug-and-play manner but much more user-friendly for your staff/clients with access restrictions and also supports Oracle RDBMS. Unlike phpMyAdmin, Cruddy!'s underlying CGI::CRUD is a full object-oriented webapp development framework.

Cruddy! is an application of the CGI::CRUD framework that provides an instant web front-end CRUD interface to your database. Currently, Oracle and MySQL RDBMS are supported. Cruddy! is stable and used in several production sites around the world including ValueClick, Inc.

With Cruddy!, you can get a basic CRUD web interface up and running in minutes without coding. At the same time, it allows a great deal of flexibility for customization by engineers (application functionality/business logic) and non-engineers (presentation and style) alike.
See also SmartCruddy! for a sister Business Intelligence application.

CRUD now, code later.

CRUD (Create, Read/Report, Update, Delete) are the four basic data manipulation commands of a data source; e.g. enabling management of configuration data in an administrative interface to your application.

A unique and powerful advantage with this CRUD abstraction is that it can be tied closely with a database schema. Each group of fields in the form can represent a database table (or view) and the table/column properties and constraints are automagically discovered so your DBA can make DDL changes that will be immediately reflected in the HTML forms (no duplication of the data dictionary in your code).

All user/operator input is checked tightly against database constraints and there is built-in magic to provide convenient select lists, etc, and to enforce a discreet set of valid values against unique/primary keys in lookup tables. This means referential integrity even for MySQL. Metadata in MySQL's SET and ENUM types are also supported. This gives the operator a chance to correct mistakes with helpful hints instead of just getting a meaningless db error code.

Another advantage this abstraction provides is the separation of presentation and style using style sheets and having human-friendly presentation attributes and access controls stored in a database table that can be managed by non-engineers.

Screenshots

Demo

Use guest/guest as the username/passwd for the demo.

Quick-Start and Setup Instructions

Prerequisites

Cruddy! is an implementation of the all perl CGI::CRUD module (available from CPAN). It currently has drivers for MySQL and Oracle RDBMS's. So besides perl and a webserver:
  1. RDBMS: Presumably you have such a database setup with tables already, otherwise why would you be here? A schema is provided in the tutorial below if you want to test the waters on some sample data.
  2. Install CGI::CRUD.
  3. Make sure the DBD driver for your RDBMS is installed and working properly (DBD::mysql or DBD::Oracle).

Get Cruddy!

Download and unpack the latest Cruddy! distribution.

Install webserver resources

You'll find the following resources in the distribution.

Place cruddy.css in your webserver document root, e.g.
/var/www/html/cruddy.css
Place cruddy.tpl close to your webserver root path, e.g.
/var/www/tpl/cruddy.tpl

Quick-start (cgi-bin install)

Place cruddy.pl in your cgi-bin path or otherwise allow it to be executed within your webserver, e.g. /var/www/cgi-bin/cruddy.pl

Set the following environment variables to connect to your RDBMS via DBI:

DBI_DSN
DBI_USER
DBI_PASS

Optionally set the following environment variables if your template path differs from above (where you put cruddy.tpl)

CRUDDY_FAST_TEMPLATE_PATH
CRUDDY_FAST_TEMPLATE_MAIN
E.g. for apache httpd.conf:
SetEnv DBI_DSN dbi:Oracle:pdev
SetEnv DBI_USER cruddy
SetEnv DBI_PASS cruddy

# SetEnv CRUDDY_FAST_TEMPLATE_PATH /usr/local/apache/tpl
# SetEnv CRUDDY_FAST_TEMPLATE_MAIN cruddy.tpl
For Oracle you'll probably want the following as well (of course making sure they are set before starting apache):
PassEnv LD_LIBRARY_PATH
PassEnv ORACLE_HOME
(Re)start your webserver and navigate the URL to execute the script, e.g.
http://localhost/cgi-bin/cruddy.pl

CRUD your heart out.

mod_perl setup

For a busy production environment, you'll probably want (already have) mod_perl. Cruddy! supports both mod_perl v1 and v2 with minor differences when noted below.

Prerequisites

In addition to mod_perl, you'll need the following perl modules (available from CPAN) depending on the version of mod_perl you're using (most likely v2 for current systems). If you use RPM's (RedHat, etc) this will be easier if you install both mod_perl and mod_perl-devel packages.

mod_perl v1 The perl XS libapreq
Verify the following have been installed:

Apache::Request
mod_perl v2 The perl XS libapreq2
Verify the following have been installed:
Apache2::Request
Apache2::RequestRec
Apache2::RequestIO
Apache2::RequestUtil
Recommended:
Apache::DBI

You may need to run /sbin/ldconfig -v as root after installing libapreq(2).

Configuration

You'll need the following directives added to your httpd.conf. Typically, these exist in a separate file Include'd by httpd.conf e.g. /etc/httpd/conf.d/perl.conf for RedHat or similar systems.
# Define here if using Apache::DBI->connect_on_init() in mod_perl_startup.pl
# Otherwise, or if there are conflicts, can set inside /cruddy Location directive below
PerlSetEnv DBI_DSN dbi:mysql:cruddy
PerlSetEnv DBI_USER cruddy
PerlSetEnv DBI_PASS cruddy

PerlRequire conf/mod_perl_startup.pl

PerlModule CGI::CRUD::Skipper
<Location /cruddy>
    # For mod_perl v1 use the latter directive
    SetHandler modperl
    #SetHandler perl-script

    # For mod_perl v1 use the latter directive
    PerlResponseHandler CGI::CRUD::Skipper
    #PerlHandler CGI::CRUD::Skipper

    # Optional if these differ from the defaults
    # PerlSetEnv CRUDDY_FAST_TEMPLATE_PATH /usr/local/apache/tpl
    # PerlSetEnv CRUDDY_FAST_TEMPLATE_MAIN cruddy.tpl

    # Don't set in production environments, only useful for mod_perl (not cgi-bin)
    # PerlSetEnv CRUDDY_PARADUMP_DEBUG /tmp/cruddy_debug_params.pl

    Order deny,allow
    Allow from all
</Location>
NOTES:

As explained in the cgi-bin install, you'll need the environment set (DBI_DSN, etc), I recommend PerlSetEnv for mod_perl.

CRUDDY_PARADUMP_DEBUG will enable you to use mod_perl_debug.pl to debug your application (details). Do NOT use in a production environment.

The PerlRequire directive is a recommended way to pre-load modules to save memory. This script will also identify the mod_perl version and load the appropriate modules. The script (mod_perl_startup.pl) is included in the "contrib/" directory and the important parts are included below:

BEGIN
{

    if ($ENV{MOD_PERL_API_VERSION} && $ENV{MOD_PERL_API_VERSION} >= 2)
    {
        require Apache2::Request;
        require Apache2::RequestRec;
        require Apache2::RequestIO;
        require Apache2::RequestUtil;
        $::MOD_PERL_REQ_CLASS = 'Apache2::Request';
    }
    else
    {
        require Apache::Request;
        $::MOD_PERL_REQ_CLASS = 'Apache::Request';
    }

    use Apache::DBI ();

    # For debugging connections
    # $Apache::DBI::DEBUG = 2;

    # May be redundant in your environment
    use DBI;

    # Pre-load the driver depending on which DBD you're using (recommended)
    DBI->install_driver("Oracle");
    DBI->install_driver("mysql");

    use DBIx::IO::Table;
    use DBIx::IO::Search;
    use DBIx::IO::Mask;
    use DBIx::IO;
    use DBIx::IO::GenLib ();

    # Pre-load the DBIx driver you'll need (otherwise is lazy-loaded)
    use DBIx::IO::OracleIO;
    use DBIx::IO::OracleLib;
    use DBIx::IO::mysqlIO;
    use DBIx::IO::mysqlLib;

    use CGI::AutoForm;
    use CGI::CRUD::Table;
    use CGI::CRUD::TableIO;
    use CGI::FastTemplate;
    use CGI::CRUD::Output;
    use CGI::CRUD::SkipperOutput;
    use CGI::CRUD::ApacheOutputFastTemplate;
    use CGI::CRUD::Skipper;
    use CGI::Enurl ();
}

# I also like to initialize the database connections for each child,
# use as you see fit.
Apache::DBI->connect_on_init
(
    undef,undef,undef,
    {
        PrintError => 1,
        RaiseError => 0,
        AutoCommit => 1,
    }
);
You'll want to adapt this to your system depending on which RDBMS you're using, etc. For mod_perl v1 I've found newer versions of Apache::DBI are not compatible.

Navigate to the Location URL you created:
http://localhost/cruddy

Cruddy! caches DDL, keep this in mind when making structural changes to your database - when using mod_perl, Apache must be restarted for such changes to be reflected in the UI.

Tutorial - Next Steps and Customization

By now, you're satisfying your basic CRUD pleasures and you may be asking is it really that easy? Well yes, but things may not look so pretty or integrated into your existing look and feel. Some of the simplest and most obvious next steps for "skinning" the UI is modifying cruddy.css and perhaps cruddy.tpl.

Next, you'll probably want to dress up the form controls and identifiers (field names, etc), read the documents (man CGI::CRUD, man CGI::AutoForm, man DBIx::IO).
(much time has passed)
OK, so now you've read the docs very carefully... or not; here's a quick tutorial that should get you started.

CGI::AutoForm provides much of the underlying magic to coordinate your data dictionary with form creation. It can optionally use a table (UI_TABLE_COLUMN) to store presentation elements such as field names, access controls (what fields can be updated, used in queries, etc) and metadata relationships (e.g. what does artist_id = 144022 refer to?).

Below, we'll use an example schema that manages a music collection (as seen in the demo) - look in the "demo/" and "contrib/" directories for the SQL files.

Create the demo tables

demo_schema_{mysql,oracle}.sql

Populate the demo tables with sample records

demo_populate.sql

For mysql, you'll want to use the -f command option because the first (Oracle) statement will fail:

mysql -u cruddy -p'pass' -v -v -v -f cruddy <demo_populate.sql

Point your DSN at the new schema and you'll see a basic CRUD interface to the new tables.

Next, to dress things up a little we'll use UI_TABLE_COLUMN:

Create the tables and metadata:

AutoForm_schema_{mysql,oracle}.sql
AutoForm_metadata.sql

Populate UI_TABLE_COLUMN for the demo tables:

demo_AutoForm_populate.sql

You should now see a better looking UI; notice that you have a great deal of control over your web front-end simply by managing records in UI_TABLE_COLUMN. To fully understand what's going on, you'll want to inspect demo_AutoForm_populate.sql and have a look at the documentation Have a look around and you'll notice some of the convenient features such as auto-ID generation and select-list masking. Enjoy!

Documentation

The perl modules Cruddy! implements are fully documented. The following are a good place to start:
CGI::CRUD
CGI::AutoForm
DBIx::IO


mod_perl Debugging Tool

mod_perl_debug.pl is included in the "contrib/" directory. This is handy for stepping through your code by simulating the mod_perl environment. You'll find this useful not only for Cruddy!, but for any mod_perl application.

First thing to do is set the CRUDDY_PARADUMP_DEBUG environment variable to a filename that is writable by apache (see example configuration above). This will capture request parameters that you will then feed to mod_perl_debug.pl. Set the environment (restart apache) and navigate to the page that is giving you problems.

Then inspect the dump file and feed it to mod_perl_debug.pl after setting your local environment to match mod_perl's (e.g. make sure to set DBI_DSN, DBI_USER, DBI_PASS). You'll need to set a break point where the module handling the request is require'd by the script and step into those statements. Example:

[rsandberg@sf-rsandberg contrib]$ export DBI_DSN=dbi:Oracle:mpdev
[rsandberg@sf-rsandberg contrib]$ export DBI_USER=cruddy
[rsandberg@sf-rsandberg contrib]$ export DBI_PASS=cruddy
[rsandberg@sf-rsandberg contrib]$ ./mod_perl_debug.pl -p /tmp/cruddy_debug_params.pl DBIx::Knowledge::SmartCruddy

Loading DB routines from perl5db.pl version 1.28
Editor support available.

Enter h or `h h' for help, or `man perldebug' for more help.

main::(./mod_perl_debug.pl:16): ($program = __FILE__) =~ s#.*/##;
  DB<1> b 38
  DB<2> c
main::(./mod_perl_debug.pl:38): eval("${mod}::$handler$args");
  DB<2> s
main::((eval 17)[./mod_perl_debug.pl:38]:1):
1:      DBIx::Knowledge::SmartCruddy::handler([ \@ARGV, $opt_user ])
  DB<2> s
DBIx::Knowledge::SmartCruddy::handler(/home/rsandberg/work/boxit/perlext/DBIx/Knowledge/SmartCruddy.pm:27):
27:         my $r = new CGI::CRUD::SkipperOutput(@_) or return OK;
  DB<2> n
DBIx::Knowledge::SmartCruddy::handler(/home/rsandberg/work/boxit/perlext/DBIx/Knowledge/SmartCruddy.pm:29):
29:         my $query = $r->query();
  DB<2> 

Cruddy! sponsored by The SMB Exchange, INC
Copyright © 2008 Reed Sandberg