DBIx::CGITables 0.001 - Easy DB access from a CGI
use DBIx::CGITables;
my %parameters=();
my $query=DBIx::CGITables->new(\%parameters));
$query->search_execute_and_do_everything_even_parse_the_template();
This module is under development - and this version is not tested very well. The documentation might not be completely in sync with the latest changes, and the documentation is not optimized for easy understanding at the moment. Sorry.
DBIx::CGITables is made for making database access through CGIs really easy.
It's completely template-oriented. The templates are in HTML::Template format. Some templates might be set up quickly by using DBIx::CGITables::MakeTemplates; see the doc for this module. Some web designer should fix the templates a bit.
The template approach might make the system a bit "static". Gerald Richter has another approach based upon HTML::Embperl (not published at the time I write this), if you'd better like a more dynamic system. Anyway, I find it quite important not to mix HTML and code - the HTML documents should be easy to manipulate by non-technical web editors.
Ideally, HTML and (language-dependent) content should be splitted. I think such things might be done better by using Zope than cgis.
The database handling is done by DBIx::Recordset - this module gets its parametres first from a CGI query, then it might be overridden or completed by a parameter file, and the caller (your .cgi script or whatever) is also free to modify or add parameters.
I'm hoping that anybody should get a working (though, probably ugly) CGI interface to any kind of database simply:
1. Run DBIx::CGITables::MakeTemplates (see the pod)
2. Create the script given above at SYNOPSIS
3. Set up the webserver correct.
4. If you're not satisfied with the look, try to edit the html templates.
5. If you're not satisfied with the functionality, try reading the rest of this documentation, and the DBIx::Recordset documentation.
6. If you're still not satisfied with the functionality and/or you find bugs, hack the code and submit the patches to the mailinglist and/or me and/or (if DBIx::Recordset is affected) Gerhard Richter. If you're not a perl hacker, or if you don't have time, send a mail about what's wrong and/or what's missing to the mailinglist anyway. Or privately to me if you don't want to write to a mailinglist.
Firstly, parameters are taken from the query.
Then, parameters on are taken from the parameter file. This file is either located in the same folder as the templates, or in a folder as specified in the parameters. The parameter file contains options to DBIx::CGITables and to DBIx::Recordsets. I'm more or less trying to follow up the parameter style DBIx::Recordset is using - with one special character in front of the parameter suggesting what kind of parameter it is. It is quite a bit kludgy, but it works. The parameters should be at the key=value form, i.e.:
!Table=tablename !DataSource=... !NoRGV !NoT
CGITables will recognize a key starting with =, so it's possible to put up '=execute=1' at a line. The default value will be 1, so '=execute' should be equivalent.
If the line contains more than one equal sign which is not at the start of the line, the other equal signs will be threated as a part of the value.
Eventually conflicts will appear as the param keys are duplicated in the query and in the param file. The default is that the param file overrides the query. This might be changed by a special code inserted in front of the key=value-pair to override this behaviour. (I think too many special codes might be a bit hairy ... but I hope this will work out anyway). Those codes start with '%' since this character is not used by DBIx::Recordset, and they're separated from the key=value couple by one space. In addition to suggesting how collitions should be
%= or %! Always override options set other places
%+, Add new stuff to a comma separated list. The comma might be replaced by any other character, and to \t and \n, or simply removed. Use '\ ' for a blank and '\\' for a backslash.
%^, Prepend, separate from existing value with a comma (same rules as above)
%? Yield - use this with default values that should only be set if no other values are set.
%!() Override or ignore - that is, if the key exists, overwrite, if not, ignore.
%() Ignore option (but keep it in template outputs)
%T Template option. The key=value is given the Template Class.
%RGV Recordset Global Variable, most important ones are PreserveCase and Debug.
For =execute parameters (see DBIx::Recordset), %= or %! will `override' other =execute by deleting those. %+ and %^ will execute things in order. The default is to use the priority set by DBIx::Recordset.
In my older system, I had something called dependent and independent subqueries. A `dependent subquery' is a link in the DBIx::Recordset terminology. An `independent subquery' would be an independent DBIx::Recordset object, i.e. for fetching data for a HTML select box. I also had an option to only `resolve' links when the select returned only one row - not to waste time fetching too much data when a long list was fetched. I guess Recordset handles this more or less automagically.
`dependent subqueries' (or links, if you'd like) might be handed over like this:
!Links/-street/!Table=street !Links/-street/!LinkedField=id !Links/-street/!MainField=street_id
Drop the first `!Links' to create an independent subquery. The primary subquery has the tag "default" without a starting minus. The tags should better start with minus, to avoid inprobable though potential clashes with other output template substitutions.
My earlier systems had some weird syntax for creating misc lists from the parameters. DBIx::Recordset uses string splitting.
The first character in a parameter key or a line in the parameter file is often special. It's a bit messy, but I think it's the easiest way to do it, anyway - if the rules are obeyed there shouldn't be any ambiguisities. Here's a complete list of the first characters:
% - reserved for a `special handling' code putted in front of the real key/param. This special code is usually describing how to handle parameter collitions, but also to tell that the key/param should be ignored, or belongs somewhere else (i.e. the PreserveCase option is a global variable that might need modification) / - reserved for extra named Recordset objects. ! - reserved for Recordset initialization and important parameters to CGITables. - - reserved for the name of a named Recordset object.
See the DBIx::Recordset manual for those: ' - reserved for a DB column key = value that needs quoting # - ...... numeric value \ - ...... value that should not be quoted (i.e. SQL function) + - ...... value with multiple fields * - ...... Operator for DB column key
$ - misc options to Recordset and CGITables. = - execute commands to Recordset ? - extra output (typically ?id and ?count) ; - If you need extra parameters for containing state, start the variable name with one of those "comment" signs to avoid possible clashes.
Supported parameters:
!!Filename - defaults to $ENV{PATH_TRANSLATED}
!!ParamFileDir - Default directory for finding ParamFile
!!ParamFile - See below for default. Ignores ParamFileDir.
!!QueryClass - defaults to 'CGI', but I'm intending to head for CGI::Fast
!!Query - defaults to new !!QueryClass
!TemplateClass - defaults to 'HTML::Template'
!RecordsetClass - defaults to 'DBIx::Recordset'
!DoNothing
!SearchForm specifies that a search form should be printed. No searching is done, and the default loop is returned with one empty element. This is typically useful when viewing the data in a looped form, then this one will create an empty form.
The parameters starting with '!!' must be set before the query and parameter file is parsed.
!Mod2 should give an output variable ?mod2 which indicates whether the row count is an odd or not. This is a very popular feature demand, though not possible to implement in a nice way in this scheme. I don't think it belongs to the template engine, but it would be quite ugly putting it elsewhere, I think. I've putted a hack into the sub faenskopiering which really shouldn't be called in the first place. This hack ignores !Mod2 and inserts ?mod2 into the tables.
$substring_search=column[,column..] will enable substring search for the selected attribute. This means *column will be set to " LIKE " and the value will be set to "%value%" in searches. This should be used with care, as it might strain on most databases. Anyway, mysql handles it well. Case sensitivity might be an issue, searches in mysql are always case insensitive.
Possible future parameters: !IncludeParamfile !ParamMacro !Ooups !OtherTemplate !SetSelected
For !Links: $fetch_always (default) $fetch_when_found_one $fetch_when_found_more $fetch_when_found_none
Unfortunately I don't have the time writing more docs due to deadlines.
?count (at the top level) The number of elements returned. Nice to use in constructs like <TMPL_UNLESS name="?count">Sorry, didn't find anything</TMPL_UNLESS> ?mod2 (in all recordset loops) 0 for even, 1 for odd (0 beeing "odd") any parameter key (at the top level) will give the corresponding value. a loop called cgi_query with the variables key and value will give the query - nice for forwarding a query in hidden input tags. Each database query have a loop with _only_ the database values (except ?mod2 which really is a hack). It would have helped a lot to have access to the outer scope variables in the inner loop, but it seems like the author of the template engine in use disagrees.
The templates should have an extention matching /\.(\w+)$/ - typically sth like mydatabase.CGITables or mydatabase.db or mydatabase.db_html. The script will then search for the param file mydatabase.param.$1, i.e. mydatabase.param.db. It might also use another template if found, mydatabase.$status.$1, where $status might be one of (in prioritied order):
error update_ok delete_ok add_ok found_$n found_more
(this is not completely implemented yet)
Probably a lot of important features are missing. It might also be slow as it's costly to build Recordsets, and they're discarded after each call to the cgi; I'm intending to solve this by optimizing for CGI::Fast and storing the frequently used recordsets in a cache. Eventually such a cache should be kept in shared memory. I haven't looked at it yet, but Sam Tregar has announced some general module for keeping caches in shared memory.
The code contains this line several places: die "stub!";
When this code is executed with the warning flag, there might be some warnings popping up from the DBIx::Recordset module. Those will eventually disappear in newer versions of DBIx::Recordset.
This is UNDER DEVELOPMENT and ABSOLUTELY NOT GOOD ENOUGH TESTED. The number of unknown bugs is probably high.
Version 0.
This started as some template cgi system that needed database access. To allow better flexibility, I made some hacks to allow SQL code to be inserted into the template. This was ... ugly, hairy and hacky. I expanded it, so the SQL code could be in separate files. It still was ugly, hairy and hacky.
Version 1.
I started more or less from scratch, and made a new system, where SQL code and other parameters to the script could be inserted into a special parameter file. The script would "automagically" generate SQL to select, update, insert and delete rows from tables. It started out a lot better than version 0, but it was still hairy, and it certainly only became worse and worse as more and more features was to be added.
Version 2.
I started from scratch again, this time with object oriented modules - DBIx::Access and DBIx::CGIAccess. This time I aimed for cleanliness. But I think it has grown more messy as more features was to be crammed in. I'm currently merging from the Solid database to MySQL - and I'm a bit horrified because MySQL is case sensitive, and Solid wasn't - which might mean that I will have to redesign some of the parameter syntax (I've chosen UPPERCASE for database column names, and lowercase for misc options).
Version 3.
I registered at CPAN and got a "go" for DBIx::Tables and DBIx::CGITables.
I scratched my head in a week. Then I started from scratch again, discarding DBIx::Tables for DBIx::Recordset.
Some of the uglyness from earlier versions remain - a quite "ugly" symbol usage in the param file/query, like "%() !Table=foo". Maybe I would have tried doing it in a better way if it wasn't for Recordset already having parameters prepended by a special character.
Feel free to submit patches, but also normal feedback, bugfixes and wishlists.
It's up to you to provide proper security. I think the Right Way to do it is to let the .cgi do the authentication (i.e. by SSL certificates or transmitting the DB login and password encrypted) and then let the DBMS control what privilegies the user should have.
Another way is to override all potentially harmful parameters to the DBIx::Recordset, either by the param file or by a hash to sub search_execute_and_do_everything_even_parse_the_template
Tobias Brox <tobiasb@funcom.com>
Feedback is appreciated - even flames. I will eventually put up a mailing list if I notice any interesst about this module.
This module was written partly in my worktime. Shameless plug for my employer:
Check our upcoming MMORPG at http://www.anarchy-online.com/ - Linux client will be available.
Play multiplayer Spades, Backgammon, Poker and more for free at http://www.funcom.com/ (Java)
To install DBIx::CGITables, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::CGITables
CPAN shell
perl -MCPAN -e shell install DBIx::CGITables
For more information on module installation, please visit the detailed CPAN module installation guide.