Catalyst::Plugin::AutoCRUD - Instant AJAX web front-end for DBIx::Class
This document refers to version 0.44 of Catalyst::Plugin::AutoCRUD
This is an ALPHA RELEASE. I'd really appreciate any bug reports; you can use the CPAN RT bug tracking system, or email me (Oliver) directly at the address at the bottom of this page. Please also be aware that the configuration file content has changed from previous releases of the module.
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.
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).
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.
If you're upgrading from CatalystX::ListFramework::Builder, please note the following important changes:
/autocrud if you don't set it. Override this with the basepath option.extjs2 option from your config, as this plugin can now pull the files from CacheFly.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 if your application. Use the basepath option if you want to override this.
DBIx::Class::Schema based classIn 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 setupYou 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).
DBIx::Class schemaIf 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 (0.05 or greater).
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)).
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.
For those columns where your database uses an auto-incremented value, add the is_auto_increment => 1, option to the relevant hash 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.
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.
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>
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" />
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.
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>
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>
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, and also updates to be made to existing records. Set this to a value of no to prevent these operations from being allowed. 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>
Important note: this setting applies to both the creation of new records in your source, as well as the updating of existing records. There's no separate create_allowed option.
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>
To restrict the set of columns displayed, provide a list of the column names (as the data source knows them) to this setting. In Config::General format, this would look something like:
<Plugin::AutoCRUD>
<sites>
<default>
list_returns id
list_returns title
list_returns length
</default>
</sites>
</Plugin::AutoCRUD>
So 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, but that might be fixed in a future version of this plugin. If using this feauture, you probably want to also use update_allowed no to preserve your users' sanity.
At the same time, you can alter the titles given to some columns in the user interface, by changing this option from a list to a hash form:
<Plugin::AutoCRUD>
<sites>
<default>
<list_returns>
id Key
title Name
length Time
</list_returns>
</default>
</sites>
</Plugin::AutoCRUD>
Here, the columns are still restricted, and their titles are changed to the values on the right hand side. To use the default value for a column (i.e. what the Plugin works out for itself), just omit the value on the right hand side.
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.
This option isn't fully implemented. It allows you to swap out the set of templates used to generate the web front-end. At the moment, only the full-fat option is implemented, and it's the ExtJS front-end you'll be familiar with.
The skinny front-end is just a stub (you can try if it you like), and I'm hoping someone will write HTML/CSS with a more lightweight JS library such as JQuery or YUI, and submit that.
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.
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.
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:
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.
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.
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.
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.
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.
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.
Oliver Gorwits <oliver.gorwits@oucs.ox.ac.uk>
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.
