Introduction

Often it is nice to throw data into a wiki in tabular form so that it can be used as a reference. It is a logical progression that it would be nice to search, filter and present the stored information in other forms. SQL presents a fairly clear industry standard for querying and modifying data so I have used this as a rough outline for a MoinMoin macro.

• Disclaimer! I am new to SQL so don't expect anything too advanced!

Installation

1. strptime is missing from Python, (more details). You need to put this file in your Python path somewhere.

2. If you are running Python 2.3.x, you do not need the above fix. You will have to make the following changes to the /SourceCode. Comment out lines 40 - 43. Change line 118 (self.timestruct = wdbtime.strptime(str, format)) to self.timestruct = time.strptime(str, format). That should do it. JosYule.

3. Copy the following file into your plugins macro directory: /SourceCode

Usage

Build a wiki page containing some data. Note:

• It's best if the table is the only information on the page.

• The table should have no indent (preceeding spaces). (This one here has to make this page look nice ).

  fruit	size	colour
  banana	20	yellow
  apple	5	green
  pear	6	green

Create a query on another wiki page. Read about SQL for more info on the syntax. Each query is in its own section e.g. (please note the double curly braces should be triple curly braces but I can't add them for obvious reasons).

• == queryname1 ==
   {{ query
   }}
  == queryname2 ==
   {{ query
   }}

Query has the form :-

• select || $(fruit) || $(size) ||
  titles ||<#eeeeee> '''Fruit name''' ||<#eeeeee> '''Size of fruit''' ||
  from WikiPage
  where "$(colour)"=="green" and $(size)<100

This will give you:

• Fruit name	Size of fruit
  apple	green
  pear	green

Notes :-

• The select statement is what will be placed in each row of the resulting table.

• The titles statement (not SQL!) is the first row of the results table.

• from reads a wiki page containing a table.

• where filters information from a table.

  • The $(column_name) variable is substituted for the value per row and the test executed. If the test passes the row is added to the selection set.

  • Note, in the example we are comparing a text value and a numeric value so we put inverted comma, as usual round text values, and not round numeric values. It's just Python code after all - so beware!

  • Imagine someone doing where "blah"*1000000000. This is why the macro is open to abuse.

• There are functions, of the form $(function:args). Maybe some kind soul can document these. I leave it as an exercise to the user of this macro to understand the code and use these. It should be easy to add your own functions.

Functions

Functions are can be used to create virtual columns, where the data is created via the function. Lets use an example to illustrate. Here is the table, lets say its on a page named ProjectDataTable:

• project	client	workers	due
  BanzaiProject	ClientBig	JosYule NickTrout	24-jan-04
  ZooperProject	ClientSmall	JosYule	25-jan-04

Very important to note the date format. it is <day>-<abbr. month>-<2 digit year>.

Now lets setup a query. Lets say this query is on a page called ProjectQueryData (please note the double curly braces should be triple curly braces but I can't add them for obvious reasons).

• == daysuntildue ==
  {{
  select || $(project) || $(days_until:due) ||
  titles ||<#eeeeee> '''Project''' ||<#eeeeee> '''Days till Due''' ||
  from ProjectDataTable
  where "1" == "1"
  }} 

Now, on the page that you want to see the output of this query, you would add the following line...

• WikiDb(!ProjectQueryData,daysuntildue,)

Note that you have to have a trailing comma in the args list for the macro.

The output of this query would be something like this, assuming that the current date is the 1st of January:

Functions can also be used in the where part of a query. Here is an example which would show projects due in the next 24 days:

• == projectsduequery ==
  select || $(project) || $(workers) ||
  titles ||<#eeeeee> '''Project''' ||<#eeeeee> '''Employees''' || 
  from ProjectDataTable
  where $(days_until:due) < 25
  }}

Note that in the line where $(days_until:due) < 25, we are not using quotes around the function ($(days_until:due)). This is because we want to do a numerical comparison. With the quotes, it would treat the values as a string.

This should output a table like so:

List of Functions that are available:

• day_of_week - takes a date and returns the day of week
• days_until - takes a date and returns the number of days till that date from the current date
• working_days_until - takes a date and returns the number of working (week) days untill that date from the current date

Arguments to WikiDb Macro

Another interesting thing you can do with the querys is to have arguments in the query which are replaced by data given in the WikiDb macro call.

Here is an example of a query, assuming the page naming scheme from the functions example.

== argreplacement ==
{{
select || $(project) || $(client) ||
titles ||<#eeeeee> '''Project''' ||<#eeeeee> '''Client''' ||
from ProjectDataTable
where "$(1)" in "$(workers)"
}}

Now, on the page that you want to display the results of the query, you could have something like...

• WikiDb(!ProjectQueryData,argreplacement,!JosYule)

Note the 3rd argument, JosYule. This gets filled in to the "$(1)" in the query. The output of this query would be:

You could also simply change the macro to show projects that have a different user involved:

• WikiDb(!ProjectQueryData,argreplacement,!NickTrout )

Would result in:

See Also

• WikiFeatures:DatabaseCapabilities

Discusssion

• The name WikiDb is mighty redundant, DataQuery or something similar seems better to me. --jh

  • I intend to add data insertion as a function of the macro and so Dataquery is too specific. --ndt

  Please use MoinMoin.widget.browser.DataBrowserWidget for the display of results, which will eventually mean that sorting by columns, displaying in chunks and other stuff will come automagically. Example: userform.py at the bottom.

  • I would very much like for the macro to be more integrated with Moin. Features which I would like to try and implement:
    • Insertion of data into tables through the use of forms.
    • Sorting of data. Clicking on column headings to sort by column would be a very welcome feature.
    • Stats on data, e.g. sum, mean, min, max etc.
    • Caching of page table data for fast access.

  This looks like Zope's DTML iterator and TinyTable module that allows to put data in a table by using an ugly format instead of text boxes. The iterator supports min, max, sum, etc. and can be used to access the content of any array. Instead of TinyTable, ZopeGadfly supports SQL.

  Instead of looking at another WikiPage for the data it would be nice to be able have the database be attachments to the query page. Another option would be to pass the real file name path to the database (definitely not for a public wiki). Maybe integration with Gadfly? http://gadfly.sourceforge.net/

  • The idea here was that you might use the same query several times, with different parameters. If you refine the query it will change all instances that use it. If you attach the query it will only change for the page the uses it. The other issue here is people can modify the query as they don't use ACLS. If you put the query on a page you can restrict who edits it. There may be some security issues with letting anyone write a query. -- NickTrout

  Did someone recognize the solution from AlexanderFischer, who wrote a processor (old style, 1.2-release) and a parser (new style, 1.3-release) to import simple mysql-content? -- KlausHeinisch

  • I had a quick look at the SQL parser and its an SQL interface. What I want is something more integrated with MM (i.e. data is stored in pages, so you can also hand edit them, depending on ACL). I haven't touched this work in a while because of lack of time. What I was thinking of doing was modifying MM so that any marked tables on pages are parsed and saved into a an intermediate (quickly accessed) file. This might be a pickle of a list or dictionary containing all the information in the table. I'm not interested in storing that much information anyway, pages might be a few hundred kB, but not MB. This is just a convenience so you might seach whose Birthday it is this month etc, not for storing customer databases. Besides, if I were going the backend database route I would use ZODB with a simple query language in front. I don't think SQL is for the general wiki population, it's quite technical, but would be good for very large databases. There is room for both approaches. -- NickTrout

Do not forget to look at virtual pages. It is a plugin system that allows you to serve wiki pages e.g. from a DB. See MoinMoinPatch and http://gauss.ffii.org for an example.