• View
• Edit
• History
• Attach
• Print
• Backlinks

DataPlates

Questions answered by this recipe

How can I quickly and painlessly create a PmWiki front-end for an existing database table or query, without having to write forms and pagelist templates by hand?

Description

DataPlates automatically creates Fox or ZAP forms and pagelist templates for the groups of pages configured in DataQuery, which can then be customized and saved as wiki pages. By default it uses the structure described in EditDataPages so that records are displayed as wiki pages and can be edited by clicking the Edit link, but this behavior can be changed. The forms automatically employ client-side (JavaScript) validation to encourage entry of valid data into your database.

Full documentation and demonstration of all the features of this recipe are available at its demo site, http://workscited.net/dataplates .

• what's working
• what's not working yet
• known bugs
• 2008-03-02: Here is a beta version: dataplates2.phpΔ which lets you use either Fox or ZAP as a form processor. You need also to install DataQuery and Fox or ZAP.
• 2007-10-08: Here is the latest beta: dataplates.phpΔ Since it is a brand new recipe, it requires recent versions of DataQuery, ZAP, and PmWiki

To install it, include the file in your config.php. Any configuration of the $DQglobals variable should come before inclusion of the PHP files. If using ZAP install both ZAP files.

Here is an example config.php, showing the configuration and inclusion of all three recipes.

$Databases['demo'] = array(
  'driver' => 'mysql',
  'hostname' => 'db.workscited.net',
  'database' => 'demo',
  'username' => 'workscited',
  'password' => '********');
$DQglobals['scriptfile'] = "$PubDirUrl/dFilter.js";
include "$FarmD/cookbook/dataquery.php";
include "$FarmD/cookbook/dataplates.php";
include "$FarmD/cookbook/zap.php";
include "$FarmD/cookbook/zaptoolbox.php";

Here is an example config entry which uses Fox and the latest dataplates2.phpΔ and dataquery2.phpΔ.
Some notes:

• You could alternatively use ZAP.
• If you install Fox or ZAP before the dataplates script it will recognise which form processor is installed.
• Alternatively use $DPFormProcessor = 'fox'; or $DPFormProcessor = 'zap'; before including dataplates2.php.
• Also, if you use Fox, you most likely will want the Textarea markup, cause Fox uses it.

# add any Fox configuration variables here....
include_once("$FarmD/cookbook/fox/fox.php");

$Databases['demo'] = array(
  'driver' => 'mysql',
  'hostname' => 'db.workscited.net',
  'database' => 'demo',
  'username' => 'workscited',
  'password' => '********');
$DQglobals['scriptfile'] = "$PubDirUrl/dFilter.js";
include "$FarmD/cookbook/dataquery2.php";
include "$FarmD/cookbook/dataplates2.php";

DataPlates attempts to use an external JavaScript for input masking. If you are running a testing server without a connection to the Internet, you will want to save a local copy of the JavaScript file from http://workscited.net/dFilter.js to your /pmwiki/pub/ directory and change the $DQglobals['scriptfile'] variable as shown above; otherwise your computer will attempt to access the Internet every time you load a page. If you do not want to use the JavaScript at all, simply set this variable to "" and it will not be used.

Notes

If you are running on a slow server, you may wish to enable DataPlates only long enough to generate the forms and templates you wish to use, customize those pages and save your changes, and then disable DataPlates again before opening the new part of your wiki to the public. The main utility of DataPlates is in helping you create your database front-end. Once you have customized your templates, it is not necessary to leave DataPlates installed.

Features that are working as of 2007-06-10:

• When DataPlates is installed, the DataQuery configuration pages utilize ZAP forms for a more user-friendly interface meant to resemble that of Microsoft Access. These forms allow easy access to some of the more obscure query-configuration options (such as linking tables to copies of themselves) that are not evident without the forms.
• Two pagelist templates are created for each query, one for listing multiple records and one for displaying a single record. By default these templates are generated at $Group.Templates, the list appears at $Group.HomePage, and the individual records are displayed via $Group.GroupHeader, but these defaults can be changed.
• A ZAP edit form for editing the primary table of a query is created at $Group.EditForm (by default). The form is based on the table structure: text inputs will only accept as many characters as the fields they submit to; dates, timestamps, and numeric fields have input masks to prevent entry of non-numeric characters; enumerated fields produce select or radio-button inputs; set fields produce a select-multiple input or a set of checkboxes; and fields from joined tables are presented as drop-down menus so that only valid values are saved to joined fields.
• A customized search form is generated at $Group.Search (by default) to allow you to search within a specific field of the query rather than the full text of every record. Inequalities and ranges of values can be specified for each field, along with wildcards and equalities.

Features that are not working yet

• Support for different date and timestamp formats in input masks and ZAP validation -- currently only YYYY-MM-DD HH:MM:SS is supported, though of course the forms may be customized to whatever format you require.
• More flexible input masks -- only numeric masks work at present, and they fill from left to right, which is not appropriate for data types with fixed decimal places such as currency.
• File uploads to BLOB (binary data) fields. Not sure there's demand for this. The most likely use would be to store photos in database records.
• A full To-Do list is maintained on the demo site.

Known bugs

• Textareas are post-processed by ZAP for storage as page-text variables (it's a feature, not a bug) and may not be in the desired format for database storage. This is totally fixable, it will just take some tweaking. Suggestions welcome.
• the templates and forms created by DataPlates are broken by PmWiki's WikiWords feature -- it mistakes their class names (which contain capital letters) for WikiWords. You may turn off WikiWords with the following line in your config.php:

   $EnableWikiWords = 0;
  

  I realize this is a core feature of PmWiki and it's my responsibility to fix my recipe to work with it, but it's not a high priority for me right now, so if you come up with a fix for it, send me the revised script and I'll incorporate your changes into the next release.
• Currently you cannot configure all the options of a query when it is disabled. You have to enable the query on the DataQuery.DataQuery configuration page (by checking its box) and submit the form, then return to the query's configuration page and the full form will appear.
• Since DataQuery's (:data:) markup does not support values that are different from the labels displayed, the auto-generated selects and radio buttons and checkboxes may not return values that are valid for the fields.

The most helpful thing beta testers can do at this point is to use DataPlates to create drafts of templates and forms, customize these pages to work the way you want, and send me the customized wiki code, so that I can tweak DataPlates to create the desired code automatically. Bug reports are also appreciated, though I can't promise to get to them right away!

I am no longer available to work on this project for free, but I am accepting bounties. Contact me at Profiles.Ben Stallings.

Release Notes

If the recipe has multiple releases, then release notes can be placed here. Note that it's often easier for people to work with "release dates" instead of "version numbers".

• 2007-06-11, 16:00 CDT: minor bug fix.
• 2007-06-10, 18:00 CDT: version 0.3 beta (search forms)
• 2007-05-20, 14:00 CDT: sort lists by columns
• 2007-04-08, 16:45 CDT: additional information on status screen
• 2007-03-28, 15:30 CDT: version 0.1 beta

Comments

As stated at the dataplates demo site it is mainly thought to get data into a database. If you want to use a numerical index as a first array in your database table (e.g.: ID|Titel|Text|Author), you can easily achieve this by using powertools.php and with the following wiki-link [[{(serialname MYQUERY)}?action=edit|New Entry in Database]] whereas MYQUERY is the query defined at the site DataQuery.DataQuery.
Peter Lutz, 10th of february 2009

See Also

• DataQuery
• Fox
• ZAP
• SQLite

Contributors

• Ben Stallings -- development sponsored by Prairie Star District UUA
• HansB - (Fox integration)

* (+) Optional positive comment. Name, date
* (-) Optional negative comment. Name, date