|Syntactic Confectionery Delight|
Spreadsheet CGIby jeffa (Bishop)
|on Nov 10, 2001 at 22:04 UTC||Need Help??|
DBIx::XHTML_Table Cookbook receipe #42
The Spreadsheet From Hell(or more silly things you can do with this module)
Turns out that DBIx::XTHML_Table can be used to create a 'spreadsheet' application via CGI. The map_cell() method can be used to interpolate the current cell value inside an <input> tag - and that's the reason I added a callback mechanism in the first place, for others to find new and creative things to do with it. But, how do you name the individual names of each <input> tag? They have to be unique.
If you wanted to use a naming convention like a conventional spreadsheet (A1 - A9, B1 - B9, etc.) then you could try something like so:
After the form has been submitted, you can pick apart the rows from the cols by running each element from CGI.pm's param() method through a regex. The _ at the beginning of each name is used to seperate the 'spreadsheet' <input>'s from the other form elements. Not necessarily the best way, but it works.
At the time the idea of creating a spreadsheet out of my module was presented to me by Jim Cromie, I thought my above solution was pretty slick - for about 3 seconds. Then I realized that I don't have a corresponding B52 or A1 in my database, and hard coding some kind of mapping between database rows and columns to spreadsheet rows and columns is rather tedious and well, silly.
In order to put the proper information into the name, that is, in order to be able to know which row to update with what, I added two new methods that are meant to be used inside the map_cell() callback:
DBIx::XHTML_Table version 0.95 prints out every column you select, and displaying primary keys so prominently (and allowing the user to CHANGE them!!) just isn't good. Alas, time to add another method, but this new method can do one of two things:
The new method is set_pk(), and you send it the name of your primary key column (the next version will accept no arg and default to 'id'). By doing so, you mark that column as the primary key and it is not displayed with the other columns. The cool part is that each row can be tagged with the primary key value, which means we can now know which database row our spreadsheet row corresponds with.
The result is the following script - replace <INSERT_TABLE_NAME> with the desired database table name, replace <INSERT_PK_NAME> and supply the proper DB connection credentials and it should work without a hitch. Don't forget the 'shebang' line if you are on *nix.
Receipe #42 Code
The first thing is to connect to your database via DBI (not DBIx::XHTML_Table) so you can re-use the handle. (and yes, Apache::DBI would be much better!).
Next is a simple check to see if the form was submitted. If not, then a new DBIx::XHTML_Table object is created, modified, and displayed. Nothing new here, except the naming convention i chose for each input - an _ to sererate it from the other form controls, and an = sign to delimit $row from $col so i can split it later. It's the 'was submitted' part that's tricky:
The foreach loop munges the params into a data structure. To paint a picture: (and please forgive my blantent abuse of the 2nd normal form, by the way)
...which is what the while loop uses to actually create the SQL update statement. Using bind vars and ? is good for portability among different databases, by the way. (big thanks to maverick for reminding me)
The next big thing that this puppy needs is CACHING!! Every row is updated, even the ones that didn't change - if the user only changes one cell, then every row that was selected will be updated. Ughh. I'll leave caching as an 'exercise for the reader' (aka 'pass the buck'). I also did not address the issue of joins, but in this case I was aiming for simplicity. Besides, that's receipe #43 ...
ConclusionIs this the next best thing since slice bread? Is this the be-all end-all way to to make a spreadsheet app? Of course not - it's just another Cool Use For Perl. ;)