SQWiki

Any subdirectory of the Wiki folder can be treated as a database table. What does it mean?

Creating a Table

There is no special step to create a table. The moment you add the first record or a template, you have a table.

Suppose you want to organize a to do list. The table will be called ToDo, and the records in the table will contain:

It's a good idea to start with a template for your records. It's a wiki file called template.wiki that will be used as a template for your new records. To create a template in ToDo, create a link
 [[ToDo:template]]
ToDo:template

and click on it. The first time you click, both the directory ToDo and the file template.wiki in it will be created. You will also be able to edit this file. Here's how the file might look like:

 | Summary | |
 | Priority | |
 == Description
This is your template for a table record. Notice that a record is just a two-column wiki table. You interpret the first column as containing property names and the second property values. Other than that, the record may contain any additional text formatted using wiki (or HTML) markup. Moreover, after the table, the record can continue with any free-form wiki -- in this case we start a chapter named Description.

Creating a Record

Since a record is nothing else but a wiki file with a two-column table, you can create it as any other wiki file, by starting a wiki link that specifies the directory (table name) followed by record name. For instance, you can create a link:

 [[ToDo:Trip]]
When you click on the link, a new wiki file, Trip.wiki, in the directory ToDo, will be created for you. If the directory contains a template, it will be used to initialize your new file. The file will be opened in the editor and you may start editing it immediately, filling the blanks and adding anything else you wish. Here's an example:

ToDo:Trip

You can also create a record using a SQWiki command. All SQWiki commands start on a new line with a question mark. Their syntax is very similar to SQL commands. A new record is created using an INSERT INTO command. For instance

 ? INSERT INTO ToDo (Priority = High, Summary="Buy Code Co-op")
This type of insertion is useful when you want to use wiki forms. Note that records created using this method have numbers for their names. These numbers serve as unique IDs for records and are very useful, for instance, as bug IDs in a wiki bug database.

Listing Records


The advantage of storing data in the form of records (in particular, embedding the two-column tables) is that you can easily list them. For instance, to list all records from the ToDo table, and display the Summary and Priority for each, you use the SELECT statement

 ?SELECT Summary, Priority FROM ToDo
Here's what this statement expands to:

IDSummaryPriority
ToDo:Trip

Trip to Hawaii

High

ToDo:1

Buy Code Co-op

High

ToDo:2

Review ListView

Low

Note that the first column contains links to the individual records. You can click on any of them to view the complete record.

Optionally, you can specify a pseudo property DELETE (all caps) in your SELECT statement. The listing will then have an additional column filled with delete links. By clicking on a delete link you delete a database record (however, if the record contains any links, the linked pages or images will not be deleted!). Here's an example of such a query:

 ?SELECT Summary, Priority, DELETE FROM ToDo
Records are always sorted by modification date--the last modified, first.

You can also restrict your listing by specifying the WHERE clause. For instance, to list only high priority items, use:

 ?SELECT Summary FROM ToDo WHERE Priority = high

IDSummary
ToDo:Trip

Trip to Hawaii

ToDo:1

Buy Code Co-op

The WHERE clause can combine simple conditions:

property = value equality
property <> value non-equality
property = NOT value alternative for <>

with the use of Boolean operators AND, OR, NOT, and parentheses. Value (in-) equality is tested using case insensitive string comparison.

Listing Images

You can also list the special Image directory using the command

 ? FROM Image
This is the result:

  1. sunset.jpg
    sunset.jpg
Note: Normally you also see "delete" links when listing images. When you click a "delete" link, the image gets deleted from the project.