NAME
Catalyst::Plugin::AutoCRUD - Instant AJAX web front-end for DBIx::Class
VERSION
This document refers to version 0.68 of Catalyst::Plugin::AutoCRUD
PURPOSE
You have a database, and wish to have a basic web interface supporting
Create, Retrieve, Update, Delete and Search, with little effort. This
module is able to create such interfaces on the fly. They are a bit
whizzy and all Web 2.0-ish.
SYNOPSIS
If you already have a Catalyst app with DBIx::Class models configured:
use Catalyst qw(AutoCRUD); # <-- add the plugin name here in MyApp.pm
Now load your app in a web browser, but add "/autocrud" to the URL path.
Alternatively, to connect to an external database if you have the
DBIX::Class schema available, use the "ConfigLoader" plugin with the
following config:
<Model::AutoCRUD::DBIC>
schema_class My::Database::Schema
connect_info dbi:Pg:dbname=mydbname;host=mydbhost.example.com;
connect_info username
connect_info password
<connect_info>
AutoCommit 1
</connect_info>
</Model::AutoCRUD::DBIC>
If you don't have the DBIx::Class schema available, just omit the
"schema_class" option (and have DBIx::Class::Schema::Loader installed).
DESCRIPTION
This module contains an application which will automatically construct a
web interface for a database on the fly. The web interface supports
Create, Retrieve, Update, Delete and Search operations.
The interface is not written to static files on your system, and uses
AJAX to act upon the database without reloading your web page (much like
other Web 2.0 appliactions, for example Google Mail).
Almost all the information required by the plugin is retrieved from the
DBIx::Class ORM frontend to your database, which it is expected that you
have already set up (although see "USAGE", below). This means that any
change in database schema ought to be reflected immediately in the web
interface after a page refresh.
USAGE
Read Me First
If you created your "DBIx::Class" Schema some time ago, perhaps using an
older version of "DBIx::Class::Schema::Loader", then it might well be
lacking some configuration which is required to get the best results
from this plugin.
Common omissions in column configurations include "is_foreign_key",
"join_type", "is_nullable", and "is_auto_increment". Of course it's also
good practice to have your "DBIx::Class" Schema closely reflect the
database schema anyway.
To automatically bring things up to date, download the latest version of
DBIx::Class::Schema::Loader from CPAN (which may be 0.05 or a
pre-release), and use the output from that. If you don't yet have a
Schema, continue reading and Scenario 2, below, will cover the steps
required.
Scenario 1: Plugin to an existing Catalyst App
This mode is for when you have written your Catalyst application, but
the Views are catering for the users and as an admin you'd like a more
direct, secondary web interface to the database.
package AutoCRUDUser;
use Catalyst qw(AutoCRUD);
__PACKAGE__->setup;
1;
Adding "Catalyst::Plugin::AutoCRUD" as a plugin to your Catalyst
application, as above, causes it to scan your existing Models. If any of
them are built using Catalyst::Model::DBIC::Schema, they are
automatically loaded.
This mode of operation works even if you have more than one database.
You will be offered a Home screen to select the database, and then
another menu to select the table within that.
Remember that the pages available from this plugin will be located under
the "/autocrud" path of your application. Use the "basepath" option if
you want to override this.
Scenario 2: Frontend for an existing "DBIx::Class::Schema" based class
In this mode, "Catalyst::Plugin::AutoCRUD" is running standalone, in a
sense as the Catalyst application itself. Your main application file
looks almost the same as in Scenario 1, except you'll need the
"ConfigLoader" plugin:
package AutoCRUDUser;
use Catalyst qw(ConfigLoader AutoCRUD);
__PACKAGE__->setup;
1;
For the configuration, you need to tell AutoCRUD which package contains
the "DBIx::Class" schema, and also provide database connection
parameters.
<Model::AutoCRUD::DBIC>
schema_class My::Database::Schema
connect_info dbi:Pg:dbname=mydbname;host=mydbhost.example.com;
connect_info username
connect_info password
<connect_info>
AutoCommit 1
</connect_info>
</Model::AutoCRUD::DBIC>
The "Model::AutoCRUD::DBIC" section must look (and be named) exactly
like that above, except you should of course change the "schema_class"
value and the values within "connect_info".
Remember that the pages available from this plugin will be located under
the "/autocrud" path if your application. Use the "basepath" option if
you want to override this.
"DBIx::Class" setup
You will of course need the "DBIx::Class" schema to be created and
installed on your system. The recommended way to do this quickly is to
use the excellent DBIx::Class::Schema::Loader module which connects to
your database and writes "DBIx::Class" Perl modules for it.
Pick a suitable namespace for your schema, which is not related to this
application. For example "DBIC::Database::Foo::Schema" for the "Foo"
database (in the configuration example above we used
"My::Database::Schema"). Then use the following command-line
incantation:
perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:. -e \
'make_schema_at("DBIC::Database::Foo::Schema", { debug => 1 }, \
["dbi:Pg:dbname=foodb;host=mydbhost.example.com","user","pass" ])'
This will create a directory (such as "DBIC") which you need to move
into your Perl Include path (one of the paths shown at the end of "perl
-V").
Scenario 3: Lazy loading a "DBIx::Class" schema
If you're in such a hurry that you can't create the "DBIx::Class"
schema, as shown in the previous section, then
"Catalyst::Plugin::AutoCRUD" is able to do this on the fly, but it will
slow the application's startup just a little.
The application file and configuration are very similar to those in
Scenario two, above, except that you omit the "schema_class"
configuration option because you want AutoCRUD to generate that on the
fly (rather than reading an existing one from disk).
package AutoCRUDUser;
use Catalyst qw(ConfigLoader AutoCRUD);
__PACKAGE__->setup;
1;
<Model::AutoCRUD::DBIC>
connect_info dbi:Pg:dbname=mydbname;host=mydbhost.example.com;
connect_info username
connect_info password
<connect_info>
AutoCommit 1
</connect_info>
</Model::AutoCRUD::DBIC>
When AutoCRUD loads it will connect to the database and use the
DBIx::Class::Schema::Loader module to reverse engineer its schema. To
work properly you'll need the very latest version of that module (at
least 0.05, or the most recent development release from CPAN).
The other drawback to this scenario (other than the slower operation) is
that you have no ability to customize how foreign, related records are
shown. A related record will simply be represented as something
approximating the name of the foreign table, the names of the primary
keys, and associated values (e.g. id(5)).
TIPS AND TRICKS
Update your "DBIx::Class" Result Classes
If you created your "DBIx::Class" Schema some time ago, perhaps using an
older version of "DBIx::Class::Schema::Loader", then it might well be
lacking some configuration which is required to get the best results
from this plugin.
Common omissions in column configurations include "is_foreign_key",
"join_type", "is_nullable", and "is_auto_increment". Of course it's also
good practice to have your "DBIx::Class" Schema closely reflect the
database schema anyway.
To automatically bring things up to date, download the latest version of
DBIx::Class::Schema::Loader from CPAN (which may be 0.05 or a
pre-release), and use the output from that.
More detail is given in the "TROUBLESHOOTING" section, below.
Representing related records
When the web interface wants to display a column which references
another table, you can make things look much better by adding a custom
render method to your "DBIx::Class" Result Classes (i.e. the class files
for each table).
First, the plugin will look for a method called "display_name" and use
that. Here is an example which could be added to your Result Class files
below the line which reads "DO NOT MODIFY THIS OR ANYTHING ABOVE", and
in this case returns the data from the "title" column:
sub display_name {
my $self = shift;
return $self->title || '';
}
Failing the existence of a "display_name" method, the plugin attempts to
stringify the row object. Using stringification is not recommended,
although some people like it. Here is an example of a stringification
handler:
use overload '""' => sub {
my $self = shift;
return $self->title || '';
}, fallback => 1;
If all else fails the plugin prints the best hint it can to describe the
foreign row. This is something approximating the name of the foreign
table, the names of the primary keys, and associated values. It's better
than stringifying the object the way Perl does, anyway.
Textfields and Textareas
When the plugin creates a web form for adding or editing, it has to
choose whether to show a Textfield or Textarea for text-type fields. If
you have set a "size" option in add_columns() within the Schema, and
this is less than or equal to 40, a Textfield is used. Otherwise, if the
"size" option is larger than 40 or not set, then an auto-expanding,
scrollable Textarea is used.
Column names with spaces
The plugin will handle most tricky names, but you should remember to
pass some required extra quoting hints to DBIx::Class when it makes a
connection to your database:
# most databases:
{ quote_char => q{`}, name_sep => q{.} }
# SQL Server:
{ quote_char => [qw/[ ]/], name_sep => q{.} }
For more information see the DBIx::Class::Storage::DBI manual page or
ask on the DBIx::Class mail list.
Database IO filters
Buried within one of the modules in this application are some filters
which are applied to data of certain types as it enters or leaves the
database. If you find a particular data type is not being rendered
correctly, please drop the author a line at the email address below,
explaining what you'd like to see instead.
Relocating AutoCRUD to another URL path
If you want to use this application as a plugin with another Catalyst
system, it should work fine, but you probably want to serve pages under
a different path on your web site. To that end, the plugin by default
places its pages under a path part of ".../autocrud/". You can change
this by adding the following option to your configuration file:
<Plugin::AutoCRUD>
basepath admin
</Plugin::AutoCRUD>
In the above example, the path ".../admin/" will contain the AutoCRUD
application, and all generated links in AutoCRUD will also make use of
that path. Remember this is added to the "base" of your Cataylst
application which, depending on your web server configuration, might
also have a leading path.
To have the links based at the root of your application (which was the
default behaviour of "CatalystX::ListFramework::Builder", set this
variable to an empty string in your configuration:
<Plugin::AutoCRUD>
basepath ""
</Plugin::AutoCRUD>
Using your own ExtJS libraries
The plugin will use copies of the ExtJS libraries hosted in the CacheFly
content delivery network out there on the Internet. Under some
circumstances you'll want to use your own hosted copy, for instance if
you are serving HTTPS (because browsers will warn about mixed HTTP and
HTTPS content).
In which case, you'll need to download the ExtJS Javascript Library
(version 2.2+ recommended), from this web page:
<http://extjs.com/products/extjs/download.php>.
Install it to your web server in a location that it is able to serve as
static content. Make a note of the path used in a URL to retrieve this
content, as it will be needed in the application configuration file,
like so:
<Plugin::AutoCRUD>
extjs2 /static/javascript/extjs-2
</Plugin::AutoCRUD>
Use the "extjs2" option as shown above to specify the URL path to the
libraries. This will be used in the templates in some way like this:
<script type="text/javascript" src="[% c.config.extjs2 %]/ext-all.js" />
Simple read-only non-JavaScript Frontend
All table views will default to the full-featured ExtJS based frontend.
If you would prefer to see a simple read-only non-JavaScript interface,
then append "/browse" to your URL.
This simpler frontend uses HTTP GET only, supports paging and sorting,
and will obey any column filtering and renaming as set in your "SITES
CONFIGURATION" file.
Overriding built-in Templates
The whole site is built from Perl Template Toolkit templates, and it is
possible to override these shipped templates with your own files. This
goes for both general files (CSS, top-level TT wrapper) as well as the
site files mentioned in the next section.
To add these override paths, include the following directive in your
configuration file:
<Plugin::AutoCRUD>
tt_path /path/to/my/local/templates
</Plugin::AutoCRUD>
This "tt_path" directive can be included multiple times to set a list of
override paths, which will be processed in the order given.
If you want to override any of the CSS used in the app, copy the
"wrapper.tt" template from whichever "site" you are using, edit, and
install in a local "tt_path" set with this directive.
SITES CONFIGURATION
Another feature borrowed from the original CatalystX::ListFramework is
the ability to have multiple views of your data, tailored in various
ways. For example you might choose to hide some tables, or columns
within tables, rename headings of columns, or disable updates or
deletes.
Altering the Default Site
When using this plugin out of the box you're already running within the
default site, which unsurprisingly is called "default". To override
settings in this, create the following configuration stub, and fill it
in with any of the options listed below:
<Plugin::AutoCRUD>
<sites>
<default>
# override settings here
</default>
</sites>
</Plugin::AutoCRUD>
Configuration Options for Sites
Notes
In the discussion below, the term *schema* is used to mean the database,
and *source* is used to mean table. These are just more accurate terms
for the purposes of this plugin.
In general, when you apply a setting to something at a higher level
(say, a schema), it *percolates* down to the child sections (i.e. the
sources). For example, setting "delete_allowed no" on a schema will
prevent records from any source within that from being deleted.
Some of the options are *global* for a site, others apply to the schema
or sources within it. To specify an option for one or the other, use the
schema and source names *as they appear in the URL path*:
<Plugin::AutoCRUD>
<sites>
<default>
# global settings for the site, here
<myschema>
# override settings here
<somesource>
# and/or override settings here
</somesource
</myschema>
</default>
</sites>
</Plugin::AutoCRUD>
Options
update_allowed [ yes* | no ]
This can be applied to either a schema or a source; if applied to a
schema it percolates to all the sources, unless the source has a
different setting.
The default is to allow updates to be made to existing records. Set
this to a value of "no" to prevent this operation from being
permitted. Widgets will also be removed from the user interface so
as not to confuse users.
<Plugin::AutoCRUD>
<sites>
<default>
update_allowed no
</default>
</sites>
</Plugin::AutoCRUD>
Although this is a separate option from "create_allowed", below, *be
warned!* Both creation and editing of records are in fact "update"
actions, so the *only* way to secure the application from creation
and editing of records is to set this option to "no".
create_allowed [ yes* | no ]
This can be applied to either a schema or a source; if applied to a
schema it percolates to all the sources, unless the source has a
different setting.
The default is to allow new records to be created. Set this to a
value of "no" to prevent this operation from being allowed. Widgets
will also be removed from the user interface so as not to confuse
users.
<Plugin::AutoCRUD>
<sites>
<default>
create_allowed no
</default>
</sites>
</Plugin::AutoCRUD>
*Important Note:* this is purely a cosmetic action, and it is still
possible for a user to call the AJAX API published by this
application in order to create a new record. The only way truely to
prevent record creation is also to set the "update_allowed" option
to "no", as shown above.
delete_allowed [ yes* | no ]
This can be applied to either a schema or a source; if applied to a
schema it percolates to all the sources, unless the source has a
different setting.
The default is to allow deletions of records in the sources. Set
this to a value of "no" to prevent deletions from being allowed.
Widgets will also be removed from the user interface so as not to
confuse users.
<Plugin::AutoCRUD>
<sites>
<default>
delete_allowed no
</default>
</sites>
</Plugin::AutoCRUD>
columns \@column_names
This option achieves two purposes. First, you can re-order the set
of columns as they are displayed to the user. Second, by omitting
columns from this list you can hide them from the main table views.
Provide a list of the column names (as the data source knows them)
to this setting. This option must appear at the source level of your
site config hierarchy. In "Config::General" format, this would look
something like:
<Plugin::AutoCRUD>
<sites>
<default>
<myschema>
<thesource>
columns id
columns title
columns length
</thesource>
</myschema>
</default>
</sites>
</Plugin::AutoCRUD>
Any columns existing in the source, but not mentioned there, will
not be displayed in the main table. They'll still appear in the
record edit form, as some fields are required by the database schema
so cannot be hidden. Columns will be displayed in the same order
that you list them in the configuration.
headings { col => title, ... }
You can alter the title given to any column in the user interface,
by providing a hash mapping of column names (as the data source
knows them) to titles you wish displayed to the user. This option
must appear at the source level of your site config hierarchy. In
"Config::General" format, this would look something like:
<Plugin::AutoCRUD>
<sites>
<default>
<myschema>
<thesource>
<headings>
id Key
title Name
length Time
</headings>
</thesource>
</myschema>
</default>
</sites>
</Plugin::AutoCRUD>
Any columns not included in the hash mapping will use the default
title (i.e. what the plugin works out for itself). To hide a column
from view, use the "columns" option, described above.
list_returns [ \@columns | { col => title, ... } ]
This configuration option is *DEPRECATED*. Please see "columns" to
control which columns are displayed to users and, independently,
"headings" to alter the titles displayed for any columns. The plugin
still respects a "list_returns" configuration setting but will emit
a warning to your log that you need to migrate to the new, more
flexible, alternatives.
hidden [ yes | no* ]
If you don't want a schema to be offered to the user, or likewise a
particular source, then set this option to "yes". By default, all
schema and sources are shown in the user interface.
<Plugin::AutoCRUD>
<sites>
<default>
<myschema>
<secretsource>
hidden yes
</secretsource>
</myschema>
</default>
</sites>
</Plugin::AutoCRUD>
This can be applied to either a schema or source; if applied to a
schema it overrides all child sources, even if a source has a
different setting.
frontend [ full-fat | skinny | ... ]
With this option you can swap out the set of templates used to
generate the web front-end, and completely change its look and feel.
Currently you have two choices: either "full-fat" which is the
default and provides the standard full-featured ExtJS frontend, or
"skinny" which is a read-only non-JavaScript alternative supporting
listing, paging and sorting only.
Set the frontend in your site config at its top level. Note that you
cannot set the frontend on a per-schema or per-source basis, only
per-site:
<Plugin::AutoCRUD>
<sites>
<default>
frontend skinny
</default>
</sites>
</Plugin::AutoCRUD>
Be aware that setting the frontend to "skinny" does not restrict
create or update access to your database via the AJAX API. For that,
you still should set the *_allowed options listed above, as
required.
Creating a New Site
You can create a new site by adding it to the "sites" section of your
configuration:
<Plugin::AutoCRUD>
<sites>
<mysite>
# local settings here
</mysite>
</sites>
</Plugin::AutoCRUD>
You'll notice that a non-default site is active because the path in your
URLs changes to a more RPC-like verbose form, mentioning the site,
schema and source:
from this:
.../autocrud/myschema/thesource # (i.e. site == default)
to this:
.../autocrud/site/mysite/schema/myschema/source/thesource
So let's say you've created a dumbed down site for your users which is
read-only (i.e. "update_allowed no" and "delete_allowed no"), and called
the site "simplesite" in your configuration. You need to give the
following URL to users:
.../autocrud/site/simplesite
You could also then place an access control on this path part in your
web server (e.g. Apache) which is different from the default site
itself.
TROUBLESHOOTING
Foreign keys should be configured with "is_foreign_key"
Any column in your result classes which contains the primary key of
another table should have the "is_foreign_key => 1" option added to its
configuration.
Not doing this will cause the affected column to appear twice in your
table, with a "(REF)" suffix on the heading, rather than once with an
"(FK)" suffix. The technical reason for this, if you are interested, is
that "is_foreign_key" is used by this plugin to differentiate between
"has_one" and "belongs_to" type relations. The plugin will emit an error
level log message if it detects this problem.
If using "DBIx::Class::Schema::Loader" to generate your Schema, use at
least version 0.05 or the most recent development release from CPAN to
have this automatically configured for you.
Make sure "belongs_to" follows "add_columns"
Whenver you use "belongs_to()" in a result class, it must come after any
calls to "add_column()" which affect the foreign key. A situation where
this may not be the case is if you add additional column options in a
second call to "add_column()", after the "DO NOT MODIFY THIS OR ANYTHING
ABOVE" line.
If you do not follow this guideline, then you won't see any related data
in the views generated by this plugin. Furthermore, you'll be losing
much of the advantage of "DBIx::Class", so this plugin will emit an
error log level message if it detects this problem.
A better solution is to re-generate your result class using a recent
version of "DBIx::Class::Schema::Loader" from the CPAN (which may be
0.05 or a pre-release).
Optional "belongs_to" relations must have a "join_type"
If you have any "belongs_to" type relations where the column containing
the foreign key can be NULL, it's *strongly recommended* that you add a
"join_type" parameter to the end of the relevant options to
"add_columns()", like so:
# in a Book class, the book optionally has an Owner
__PACKAGE__->belongs_to(
'my_owner', # accessor name
'My::DBIC::Schema::Owner', # related class
'owner_id', # our FK column (or join condition)
{ join_type => 'LEFT OUTER' } # attributes
);
If you don't do this, some database records will be missing! The plugin
will emit an error level log message if it detects this problem. The
technical reason for this, if you are interested, is that "DBIx::Class"
defaults to an INNER join for the "belongs_to()" relation, but if the
column can be null (that is, "is_nullable") then you most likely want a
LEFT OUTER join.
If using "DBIx::Class::Schema::Loader" to generate your Schema, use at
least version 0.05 or the most recent development release from CPAN to
have this automatically configured for you.
Columns with auto-increment data types
For those columns where your database uses an auto-incremented value,
add the "is_auto_increment => 1" parameter to the options list in
"add_columns()". This will let the plugin know you don't need to supply
a value for new or updated records. The interface will look much better
as a result.
If using "DBIx::Class::Schema::Loader" to generate your Schema, use at
least version 0.05 or the most recent development release from CPAN to
have this automatically configured for you.
EXAMPLES
The code examples given above in this manual are also supplied in the
form of a sample application. You'll find the application itself in the
"examples/app/" directory of this distribution, and the SQLite3 data
source in the "examples/sql/" directory.
INSTANT DEMO APPLICATION
If you want to run an instant demo of this module, with minimal
configuration, then a simple application for that is shipped with this
distribution. For this to work, you must have:
* The very latest version of DBIx::Class::Schema::Loader installed on
your system (at least 0.05, or the most recent developemnt release
from CPAN).
* SQLite3 and the accompanying DBD module, if you want to use the
shipped demo database.
Go to the "examples/sql/" directory of this distribution and run the
"bootstrap_sqlite.pl" perl script. This will create an SQLite file.
Now change to the "examples/demo/" directory and start the demo
application like so:
demo> perl ./server.pl
Although the instruction at the end of the output says to visit
(something like) "http://localhost:3000", you *must* instead visit
"http://localhost:3000/autocrud" (i.e. add "/autocrud" to the end). Et
voila!
To use your own database rather than the SQLite demo, edit
"examples/demo/demo.conf" so that it contains the correct "dsn",
username, and password for your database. Upon restarting the
application you should see your own data source instead.
LIMITATIONS
Single column primary key
There's no support for multiple column primary keys
(composite/compound keys). This has saved a lot of time in
development because it greatly simplifies the Catalyst and
DBIx::Class code.
No two columns in a given table may have the same FK constraint
If you have two columns which both have foreign key constraints to
the same table, it's very likely AutoCRUD will not work. Again this
is a simplification which speeded the initial development.
Time Zone settings are lost during SELECT/UPDATE
Database fields of types such as (PostgreSQL) "timestamp with time
zone" will be displayed with a date and time picker, but you'll lose
the time zone's UTC offset value, sorry. This could be fixed and
retained through an improved UI widget, for example.
For the issues above, if you're desperate that the feature be
implemented soon, please drop me a line at the address below, because
you might be able to buy some of my time for the development.
REQUIREMENTS
* Catalyst::Runtime >= 5.70
* Catalyst::Model::DBIC::Schema
* Catalyst::View::JSON
* Catalyst::View::TT
* Catalyst::Action::RenderView
* MRO::Compat
SEE ALSO
CatalystX::CRUD and CatalystX::CRUD:YUI are two distributions which
allow you to create something similar but with full customization, and
the ability to add more features. So, you trade effort for flexibility
and power.
CatalystX::ListFramework is similar but has no dependency on Javascript
(though it can use it for fancy auto-complete searches), and it also
allows you to control which columns are rendered in the display.
ACKNOWLEDGEMENTS
Without the initial work on "CatalystX::ListFramework" by Andrew Payne
and Peter Edwards this package would not exist. If you are looking for
something like this module but without the dependency on Javascript,
please do check out CatalystX::ListFramework.
AUTHOR
Oliver Gorwits "<oliver.gorwits@oucs.ox.ac.uk>"
COPYRIGHT & LICENSE
Bundled images are Copyright (c) 2006 Mark James, and are from
<http://www.famfamfam.com/lab/icons/silk/>.
This distribution ships with the Ext.ux.form.DateTime Extension Class
for Ext 2.x Library, Copyright (c) 2008, Ing. Jozef Sakalos, and
released under the LGPL 3.0 license (library version 289, 2008-06-12
21:08:08).
The rest is Copyright (c) Oliver Gorwits 2009.
This library is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.