Search:

Tararua Tramping Club

Celebrating 100 years of tramping

Webteam Table DB

Search Webteam

(:template each:)

{=$Name}? {=$Summary}

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).

Basic requirements

Input

  • 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
  • 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

  • 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?

For example

(:tabledb from="ClubNight" where="column1=next" head=no:)
(:tabledb from="TTC/FixtureCard" where="column3='family'" head=no:)

Future requirements

  • 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/*)
  • like
  • in
  • 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

Notes

Background

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).

Page last modified on 2013 Oct 06 19:47

Edit - History - Recent changes - Wiki help - Search     About TTC     Contact us     About the website     Site map     email page as link -> mailto:?Subject=TTC: Table DB&Body=From the TTC website: Table DB (https://ttc [period] org [period] nz/pmwiki/pmwiki [period] php/Webteam/TableDB) Technical requirements for event lists.