DataQuery

Summary: Retrieve records and query results from any database supported by ADOdb (such as MySQL) and portray them as wiki pages, allowing them to be processed by other wiki functions such as pagelists, includes, page text variables, and ZAP.
Version: 0.6 beta
Prerequisites: PmWiki 2.2 (beta) required for page text variables; DatabaseStandard; a database supported by ADOdb.
Status: beta
Maintainer: Ben Stallings
Discussion: DataQuery-Talk

Questions answered by this recipe

How can I use my PmWiki as a flexible and powerful front-end for a database, without all that tedious mucking about with SelectQuery and UpdateForm?

How can I give my database the user-friendly functionality of PmWiki, including password-protection of individual tables and records and the ability to track and restore changes that have been made to each record over time?

How can I add database functionality to the core PmWiki functions and to Cookbook recipes such as Fox or ZAP?

Description

The DataQuery demo site has been taken down due to lack of interest. If you would like to host the demo site yourself, please contact Ben Stallings. Be sure to check out the demo site to see DataQuery in action, and for full documentation of all its features. The below is just a summary.

  • DataQuery causes database records to act just like wiki pages. The admin specifies a set of queries that correspond to wiki groups. If the user or any wiki function attempts to access a page in one of these groups, DataQuery will display (or edit, or delete) the record whose key field matches the page name.
  • Records are made available to the wiki as a series of page text variables that can be displayed in a ZAP form or in a template (for example in a GroupHeader). Records can be created, edited, searched, pagelisted, and deleted, just as if they were ordinary wiki pages.
  • Additional search options are available to allow you to search for a value or range of values within a specific field of your query, rather than the full text.
  • If you add a handful of additional fields to your tables, you can view and restore past changes (using PmWiki's History command), password-protect individual records (using ?action=attr), and sort pagelists by when records were last modified (order=time) or by whom (order=author). None of these features are available in traditional databases; they are wiki features applied to the database by DataQuery.
  • The DataQuery.DataQuery page shows the queries currently in use by the program and allows the admin to easily list all records in a query. Errors are logged at DataQuery.ErrorLog.
  • A configuration page at DataQuery.Group allows a wiki admin to easily alter the query used at Group. For example, a query can pull data from two or more joined tables, and include additional fields that are calculated by SQL functions such as CONCAT or REPLACE. The key field to use with a query defaults to the primary key of the table, but can be changed in this configuration page as well. Searching, browsing, and editing records works just as if the fields came from a single table -- similar functionality to a saved query in Microsoft Access or a "view" in MySQL 5.1.
  • Drop-down menus ("select inputs"), sets of radio buttons, or checkboxes can be automatically generated with simple markup to allow easier entry of values from joined tables. This is done without the overhead of pagelists, and without compromising the security of your query configuration.

Notes

Put it in your cookbook directory along with the Database Standard recipe and add the following lines to your local/config.php, changing them to match your database configuration:

 $Databases['connection_name'] = array(
  'driver' => 'mysql',
  'hostname' => 'db.example.net',
  'database' => 'database_name',
  'username' => 'user_name',
  'password' => 'example_password');
 include "$FarmD/cookbook/dataquery.php";

There is now a Demo Site at http://workscited.net/dataquery that fully documents all features of the recipe. Check it out!

Release Notes

  • 2007-06-11, 16:00 CDT: minor bug fix.
  • 2007-06-10, 18:00 CDT: version 0.6 beta (Drop-down menus for linked fields)
  • 2007-05-20, 14:00 CDT: PmWiki 2.2.0 beta 46 compatibility.
  • 2007-04-18, 18:45 CDT: version 0.5 beta (Acme support, numerous minor bug fixes)
  • 2007-03-28, 15:30 CDT: DataPlates support.
  • 2007-03-23, 15:00 CDT: compatibility with ZAP page shortcuts
  • 2007-03-17, 19:00 CDT: ability to join tables to copies of themselves
  • 2007-03-16, 18:00 CDT: added a GroupHeader to the configuration pages
  • 2007-03-16, 15:45 CDT: version 0.4 beta uploaded (numerous major bug fixes, removal of the "match" and "calc" configuration variables -- see demo site for new documentation).
  • 2007-01-19, 10:20 CST: MS SQL Server compatibility.
  • 2007-01-17, 19:00 CST: SQLite compatibility.
  • 2007-01-07, 14:45 CST: version 0.3 beta uploaded (record deletion, query names with underscores and mixed caps).
  • 2006-12-22, 18:45 EDT: version 0.2 beta uploaded (query customization).
  • 2006-12-14, 14:00 CST: version 0.1 beta uploaded (form interface spun off into DataPlates recipe).
  • 2006-11-09, 18:20 CDT: editing records.
  • 2006-10-17, 11:30 CDT: configuration constants replaced by global $Database.
  • 2006-10-15, 15:30 CDT: $DQcache introduced, drastically reducing the number of queries necessary to do a pagelist.
  • 2006-10-10, 19:30 CDT: includes and page text variables fixed.
  • 2006-10-10, 15:30 CDT: First pre-alpha uploaded.

Known bugs

  • The (:data:) markup does not allow for labels that are different than the values recorded, so it may produce values that are not valid for the field you're trying to save the data in.

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

See Also

SelectQuery, UpdateForm, ZAP, DataPlates, DQReport, SQLite

Contributors

Ben Stallings - development largely sponsored by Prairie Star District UUA

Comments

See discussion at DataQuery-Talk

User notes? : If you use, used or reviewed this recipe, you can add your name. These statistics appear in the Cookbook listings and will help newcomers browsing through the wiki.