Questions answered by this recipe
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?
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.
- 2008-03-02: Use dataquery2.phpΔ if you use DataPlates with Fox as a form processor
- 2007-10-08: Here's the latest: dataquery.phpΔ .
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 "
There is now a Demo Site at http://workscited.net/dataquery that fully documents all features of the recipe. Check it out!
- 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.
- 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 discussion at DataQuery-Talk