DocTables

TBWiki includes a feature for very easy creation and editing of simple databases. These are referred to as "tables".

Contents:

Table Data Formats [edit section]

Table data is stored in regular tbwiki pages. [FIXTHIS - maybe change this to a database directory?], or it can be parsed from an external source. For data in tbwiki pages, you can edit the data in a table via the normal tbwiki page editor, or using table forms.

There are several types of tables, which vary depending on their storage format:

MoinMoin Tables [edit section]

MoinMoin tables are stored in a single file. The records are stored one per line, with the field names in the first row of the table, and the record_ids in the first column of the table.

Sample source spec: source_spec=PhoneDatabase 

||Name||Phone||
||Tim ||555-1212||
||Nadine||444-2323||

Attribute files [edit section]

Attribute files are a collection of files, each containing a single record, where the fields for each record are stored as name-value pairs.

Sample source spec: source_spec=Book.*

Any file starting with "Book" would be considered a record in the table.

Attributes are listed in the file as name=value pairs. Values can be single-line or multi-line. Multi-line values are

The first attribute of the record must the name: 'record', which should correspond to a unique identifier for records in this table. Internally, this is referred to as the record_id.

The names in the records are NOT required to be perfectly consistent between records. That is, one record may have a 'phone' attribute, while others do not. For purposes of display and editing, the set of fields for the tables is the union of all fields encountered in all records in the set of files in the database.

Inline Tables [edit section]

Inline tables are stored inline on a regular tbwiki page. They currently only support moinmoin table syntax, and can be either named or unnamed.

An inline table is embedded inside a #!Table processor block, and each line of the table has fields separated by double vertical bars.

Here's an example: 


{{{#!Table
||Name||Job||
||Tim||engineer||
||Fred||pilot||
}} }

Only one unnamed table may be used on a single wiki page.

Multiple named tables may be used on a wiki page. A named table uses a block with a name. 

{{{BlockName
<data>
}} }

Also, the table processor declaration can include a block name: 

{{{#!Table:BlockName
<data>
}} }

Parsed data Tables [edit section]

Using the scan_data module, you can parse data from multiple sources.

These can be tbwiki pages, local files, or web pages.

Parsing data from external web pages (scraping them) may introduce long delays in displaying a table.

This produces a read-only database.

Here is a sample table declaration for the FIXTHIS list for the tbwiki software: 

{{{#!Table
source_spec=/home/tbird/work/tbwiki/cgi-bin/.*[.]py$:/home/tbird/work/tbwiki/cgi-bin/plugins/.*[.]py$

match_spec="""
record_start=.*FIXTHIS
description=.*FIXTHIS[ -]*(.*)$
file=%(basename)s
line_no=%(line_no)s
"""

cols=file:line_no:description
sortby=file:alpha,line_no:int
}} }

For more information, see DocParsedTables

TBWikiDB Tables [edit section]

This reads a database from regular tbwiki page files in the current wiki.

The source_spec for such a table should be a page name prefix, followed by a wildcard (.*). And the match_spec for the table definition should be the exact string "<tbwikidb>". (see source_spec below for more details) 

{{{#!Table
source_spec=Bug_.*
match_spec=<tbwikidb>
}} }

DB fields are parsed from each page, with a definition creating a single-line value and a level-1 section creating a multi-line value.

Here's an example format: 

; Summary: summary of item
; Responsible: Tim

 = Description =
This is a description of the
first item, and can have:
 * regular tbwiki markup

To add records, you need to make a "template" page, from which new pages will be created. This is a regular tbwiki page, with a name that ends in the word 'template'. When a user selects "Add Row", a new file will be created using the filename prefix and the wildcard replaced with a record id, with the initial contents being from the template. In the example provided above the template would be named "Bug_template".

Table type specifiers [edit section]

The following fields, that are part of the table configuration, determine how the table is parsed:

source_spec [edit section]

For the source_spec, if there is a leading slash, the spec indicates local files (in the server file system). (This is a huge security issue!)

If there is no leading slash, the spec indicates pages in the tbwiki data area. This spec can contain wildcards. (At least, trailing wildcards.) If the spec contains a colon, ':', then the first part is the page name and the second part is a block name. If the table name is '#', then the current page is used as the page name.

If the string starts with "http", then it indicates a URL for a web page from which the data is loaded.

Here are some examples:

match_spec [edit section]

A match_spec is used to describe the regular expressions which are used to gather the record fields and values from the data.

The match_spec is a specified with a set of lines, one-per regular expression, enclosed in triple-quotes (three double-quotes in a row).

To identify the beginning of each record, the "record_start=" special variable is used.

Named fields are parsed according to the regular expressions given in the match_spec.

By default, each record will have the named fields, and those will be displayed in the resulting table. Each record also has a 'record_id' field, which will have the same value as the first field listed in the match_spec.

See DocWebSed for details about pattern matching and regular expressions that are supported by the data_scan module.

basename and

line_no are special values which hold the name of the file being read and the line_no in the data where the record_start is found. These can be assigned to arbitrary field names for the record, and used in the table.

Declaring a Table [edit section]

Table data is stored in one or more files (or parsed from some other source). To show a view of a table on a page, you create a table declaration, usually with a processor block:

Macro Syntax [edit section]

Here is a sample macro referencing a simple table: 
{{Table(Table1Data)}}

Processor Syntax (with examples) [edit section]

Here is a sample processor syntax for a simple table (with data specified in an external file (another page): 

{{{#!Table
source_spec=Table1Data
}} }

Here is the sample processor syntax for a multi-file table: 

{{{#!Table
source_spec=bug_.*
}} }

You can also specify data in a named block on either the current page or another page: 

{{{#!Table
source_spec=#:DataBlock1
}} }


{{{#!Table
source_spec=DataPage:DataBlock1
}} }

Table configuration [edit section]

Table configuration is specified as a list of attributes in name/value pairs, using standard tbwiki configuration syntax (use triple-quotes for multi-line values).

Here are some of the attributes you can use with the Table processor:

field attributes [edit section]

You can specify the attributes of individual fields, so that TBWiki can process them properly (generate appropriate default forms, and display the values properly).

field attributes are:

dynamic default values [edit section]

There are some special default values that allow you to automatically populate a field differently for each new record created. To use a dynamic default value, use one of the strings below:

color specifiers [edit section]

The table configuration can provide a list of color specifiers that indicate what the background color of a particular cell should be, based on the value of the field in that cell

field attribute samples [edit section]

Field types and possible values: 
{{{SampleDataConf
field=Summary
type=Text
field=Description
type=Textarea
field=Done
type=Select
possible_values="""Yes
No"""
field=Happy
type=Checkbox
field=OS
type=Radio
possible_values="""Linux
BeOs
DOS
Windows"""
default_value=Linux
}} }

Color Expressions: 

{{{#!Table
source_spec=#:ReactorTable
field=reactor_temperature
colors="""(int(value)<10):ffffd0
(int(value)>=10 and int(value)<50):ffff80
(int(value)>500):ffd0d0"""
}} }

Configuration blocks [edit section]

You can specify several "configuration" attributes of a table, and use them repeatedly (i.e. from different processor blocks), using the "conf=" attribute

Configuration blocks are used to describe any of the attributes of the table.

Query blocks [edit section]

You can specify a "query" block, which has a set of table attributes (usually including a row_filter) to show a subset of the data. These can be referenced via a URL, to show different views of the same data.

Views [edit section]

To show a table on a page, you use the table processor, which has the format: 
{{{#!Table
<source spec>
}} }
You can also specify view a

Custom forms and views [edit section]

You can create custom views of table data, by creating a view file.

View [edit section]

To create a custom view for a record, ...

Maybe need to use "Value" instead of "Field". Field is used for edit forms. With a different keyword, you could have a mix of read-only and editable fields. This is desirable.

Example: 

add_form="""Name: %(nameValue)s<br>
Phone: %(phoneField)s<br>
"""

Add Form [edit section]

To create a custom form to add a record, use the "add_form" attribute in the configuration for a database. This is (almost always) a multi-line attribute in the table configuration, and consists of the HTML used to display the edit fields for the record.

Edit fields are placed in the HTML wherever a named python format string occurs, of the type: "%(<field-name>Field)s" or "%(<field-name>File<field-type>)s"

Ex: To create a custom add form for the Book.* database, add the following attribute in the DBBookConf file, along with the other config: In this file, put HTML (FIXTHIS - should allow tbwiki markup also) to display the form. Whereever you want the form input element for a field of the record, you can place a python named string format item.

Here is an example showing fields in an HTML table. 

add_form="""<table>
<tr>
<td>User name:</td><td> %(record_idField) s</td>
</tr><tr>
<td>Phone number:</td><td> %(phoneField) s</td>
</tr><tr>
<td>Description:</td><td> %(descriptionFieldTextarea) s</td>
</tr><tr>
<td>%(ok_flagFieldCheckbox) s Is OK?</td>
</tr>
</table>
%(SaveButton) s %(CancelButton) s<br>
"""

Note that any default values specified in the database config will be pre-filled in, in the form. Any values that don't have defaults in the configuration will start blank.

Edit form [edit section]

An edit form is specified the same way, but using the configuration attribute "edit_form". Note that the field values in the form will be pre-filled in with values from the record edited.

Field format strings for edit forms [edit section]

Field format strings are python named format strings. The name of the python variable consist of the field name, followed by "Field", followed by the type of form element to display.

If no type is specified, (either in the field format string, or in the database configuration) a single-line text entry box is used (that is, an HTML INPUT form element).

Possible types are:

If something besides one of these types is specified, it is used as the INPUT field type in the HTML file element.

Special form elements [edit section]

The following are special form elements that can be placed anywhere in the form specification:

Spreadsheets [edit section]

Tables may contain formulas which are calculated at runtime to produce a final value for a "cell".

In order for the table processor to process formulas, it needs to know that a table is to be interpreted as a spreadsheet. To do this, add the attribute is_spreadsheet=1 to the table configuration (usually this is the first line after the #!Table processor directive at the top of the block).

formulas [edit section]

Formulas can consist of simple arithmetic, or functions. Formulas operate on data obtained from other cells, including individual cells and ranges, and on literals.

A formula always starts with an equal sign ('='). Here is a very simple formula: '=2+2'

Currently supported functions are:

FIXTHIS - add support for COUNTIF

cell addressing [edit section]

To unlock the power of formulas, you will want to use values from other cells. Currently, the spreadsheet only does a single pass over the data in the table, going row-by-row, in the order of records in the table database internally. formulas may refer to rows and columns that precede the formula in the table.

cells are looked up by row name and column name, with special syntax to allow for referring to the current row and current column. A cell reference starts with a dollar sign ('$'), and then has the row identifier followed by the column identifier.

Let's say we had the following table:
item  ^ count  ^ price  ^ total  ^
paper 5 .07 =$paper.count * paper.price
pencil 2 .50 =$pencil.count * $pencil.price
Query

The row identifier is the name specified as the record_id, or first cell in a row. The column identifiers is a name specified in the first row of the cells.

You can omit the row identifier OR column identifier, but not both. Thus, the formula in the last cell in the above table (the one that contains "=$pencil.count * $pencil.price") could be written more shortly by referring to the current row. That is, you could omit the current row name (pencil) and re-write that cell as: "=$.count * $.price", with the same meaning.

An empty row identifier means "the current row", and an empty column identifier means "the current column". Notice that this allows the same formula to be used in multiple rows. Also since formulas use names, they don't have to be adjusted if more rows or columns are added to the table. They do have to be adjusted, however, if the names that are referenced are changed. (That is, if you changed the column name of "price" to "cost", you would need to adjust any formulas that used that column name.)

You may also refer to cells by absolute or relative position in the table. The absolute position depends on the row_output_list and col_output_list. That is, it may depend on your query. Rows and columns are numbered from 0. Row absolute references must be exactly 5 digits long, with leading zeros.

So $00003.5 would refer to the cell in the fourth row and sixth column in the table. Usually, the first row is the header, and the first column is the record key. You can think of the numbering as being 1-based, within the actual data portion of the table. In the sample table above, the address $00001.1 and $paper.count refer to the same cell (with value of 5).

You can refer to cells with relative references as well. Use '-' or '+' as the prefix to indicate a backward reference or forward reference, respectively. Here are some examples of relative references:

Functions often operate on ranges. A range is just two cell references, separated by a colon. Like so: 

  =SUM($paper.total:$pencil.total)
Currently, ranges are evaluated left-to-right and top-to-bottom.

examples [edit section]

See TestSpreadsheetStuff for examples.

Things to Describe [edit section]

not implemented yet [edit section]

Support generated default values for fields: