Table based DB cookbook development
Develop, agile style, a recipe that allows the use of data in PmWiki tables in PmWiki pages to be included with SQL like selection.
While it keeps SQL in mind as the basis for manipulation it is intended as a easy to use recipe keeping wikiness in mind. By this I mean that it uses existing constructs, concepts, look, and feel, of PmWiki. Actually I see it as being like an (:include :) for tables on steroids. This is not intended to be a DB based front end, it is envisaged as an entirely PmWiki based recipe.
Likely major steps:
- the basics of structuring and hooking the recipe into PmWiki.
- the extraction of a table or tables data from a wiki page into an array.
- the selection (and manipulation) of data from the array.
- the insertion of data back into a wiki page (perhaps with some reformatting, eg dates yyyy-mm-dd to dd mon).
- select a row or rows from a single PmWiki table (from)
- by date value in column (where)
- next (ie single row where date column is closest to today's date, but in the future)
- future (ie all rows where date column is in the future)
- may assume (if necessary) that rows are in ascending or descending date order
- date format IS0 8601 yyyy-mm-dd
- by keyword in column
- by date value in column (where)
- identify table by (from)
- PageName (ie page in local group)
- FullName (ie page in specified group)
- table within page by anchor (anchor to be before table, table to be first one encountered after anchor)
- to work with input from both simple and advanced tables
- columns identified by (select, where, order)
- order, eg column1, column2, column3
- select by single criteria (where)
- SQL like selection should use SQL keywords and rules
It is suggested that table formatting be handled as follows:
- within table directives - ignores
- captions - ignored
- headings - ignore first row if heading found in any cell, ignore cell contents if found after first row
- rowspan - spanned cells should be treated as empty
- colspan - spanned cells should be treated as empty
- output to be rows from a table
- table heading to be an option (default yes)
- in advanced table format (to allow maximum formatting possibilities)
- simply output full row in same order as input
- are three any synergies with pagelist or page list templates?
(:tabledb from="ClubNight" where="column1=next" head=no:)
(:tabledb from="TTC/FixtureCard" where="column3='family'" head=no:)
- select by date (where)
- past (ie all rows where date is in the past)
- may not assume rows are in ascending date order
- columns identified by heading (select)
- heading is taken to be content of first row
- join tables (join%)
- based on column
- source from multiple page
- multiple tables on same page
- select by multiple criteria (where, between)
- order by
- select named columns only ()
Future future requirements
- from wildcards (from FixtureCard/*)
- allow date formats mm-dd where yyyy is assumed to be current year
- extend date format supported, eg dd mon, dd month, dd mon yyyy, dd month yyyy (NOT intended to (ever) support 2 digit years)
See for example
The requirement is to replace a legacy perl script that performs background processing on the webserver, and a process that includes loading an updated file up to the server using FTP, with a website page that can be edited on the website in a similar way to all other pages.
This replacement will extend the ability of the website to not only show the next club night on the home page, but, say, the next family trip, ski trip, wednesday trip, etc.
In other words we will have a more flexible alternative that will allow the information (club night and fixture card) already on the website to be excerpted for us on the club's home page, and elsewhere on the website (rather than relying on hidden files behind the scene).