SQWiki

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

• You can store records, which are nothing more than wiki files with property tables
• You can display these records in table form.

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:

• a short summary,
• priority, and
• a long description.

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:

ID	Summary	Priority
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

ID	Summary
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
   

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