| t | TBWiki includes a feature for very easy creation and editing of simple databases. These are referred to as "tables". | t | |
| <p> | | |
| | | |
| <table align="right"><tr><td><div class="toc"> | | |
| Contents: | | |
| <ul> | | |
| <li><a href="#Table_Data_Formats">Table Data Formats</a></li> | | |
| <ul> | | |
| <li><a href="#MoinMoin_Tables">MoinMoin Tables</a></li> | | |
| <li><a href="#Attribute_files">Attribute files</a></li> | | |
| <li><a href="#Inline_Tables">Inline Tables</a></li> | | |
| <li><a href="#Parsed_data_Tables">Parsed data Tables</a></li> | | |
| <li><a href="#TBWikiDB_Tables">TBWikiDB Tables</a></li> | | |
| </ul> | | |
| <li><a href="#Table_type_specifiers">Table type specifiers</a></li> | | |
| <ul> | | |
| <li><a href="#source_spec">source_spec</a></li> | | |
| <li><a href="#match_spec">match_spec</a></li> | | |
| </ul> | | |
| <li><a href="#Declaring_a_Table">Declaring a Table</a></li> | | |
| <ul> | | |
| <li><a href="#Macro_Syntax">Macro Syntax</a></li> | | |
| <li><a href="#Processor_Syntax_(with_examples)">Processor Syntax (with examples)</a></li> | | |
| </ul> | | |
| <li><a href="#Table_configuration">Table configuration</a></li> | | |
| <ul> | | |
| <li><a href="#field_attributes">field attributes</a></li> | | |
| <ul> | | |
| <li><a href="#dynamic_default_values">dynamic default values</a></li> | | |
| <li><a href="#color_specifiers">color specifiers</a></li> | | |
| <ul> | | |
| <li><a href="#field_attribute_samples">field attribute samples</a></li> | | |
| </ul> | | |
| </ul> | | |
| <li><a href="#Configuration_blocks">Configuration blocks</a></li> | | |
| <li><a href="#Query_blocks">Query blocks</a></li> | | |
| </ul> | | |
| <li><a href="#Views">Views</a></li> | | |
| <ul> | | |
| <li><a href="#Custom_forms_and_views">Custom forms and views</a></li> | | |
| <ul> | | |
| <li><a href="#View">View</a></li> | | |
| <li><a href="#Add_Form">Add Form</a></li> | | |
| <li><a href="#Edit_form">Edit form</a></li> | | |
| </ul> | | |
| <li><a href="#Field_format_strings_for_edit_forms">Field format strings for edit forms</a></li> | | |
| <li><a href="#Special_form_elements">Special form elements</a></li> | | |
| </ul> | | |
| <li><a href="#Spreadsheets">Spreadsheets</a></li> | | |
| <ul> | | |
| <li><a href="#formulas">formulas</a></li> | | |
| <li><a href="#cell_addressing">cell addressing</a></li> | | |
| <li><a href="#examples">examples</a></li> | | |
| </ul> | | |
| <li><a href="#Things_to_Describe">Things to Describe</a></li> | | |
| <ul> | | |
| <li><a href="#not_implemented_yet">not implemented yet</a></li> | | |
| </ul> | | |
| </ul> | | |
| </div></td></tr></table> | | |
| <p> | | |
| <h1><a name="Table_Data_Formats">Table Data Formats</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Table_Data_Formats">edit section</a>]</font></span> | | |
| </h1> | | |
| 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. | | |
| <p> | | |
| There are several types of tables, which vary depending on their storage format: | | |
| <ul><li><b>MoinMoin</b> - tb_type="moinmoin" | | |
| <ul><li>data is in a separate page (or block on a page), and is specified in MoinMoin syntax (using double vertical bars to delimit fields) | | |
| </ul><li><b>attribute files</b> - tb_type="attr_files" | | |
| <ul><li>multiple files, with one table record per file | | |
| <li>data values for the record (field values) are listed as name-value pairs | | |
| <li>at least one attribute file must exist | | |
| <li>attribute files can list different fields (that is, they can be sparse) | | |
| <ul><li>HOWEVER - every attribute file must have a 'record' field, which corresponds to 'record_id' in other places. | | |
| </ul></ul><li><b>inline</b> - tb_type="inline" | | |
| <ul><li>places the data in moin-moin format inline in the same processor block | | |
| used to declare the table | | |
| </ul><li><b>parsed data files</b> - data tb_type="parsed" | | |
| <ul><li>data is parsed from an external source using using the scanning information provided in a "match_spec" | | |
| <li>this uses the <i>data_scan</i> module | | |
| <li>this is currently a read-only data source (no record editing allowed) | | |
| </ul><li><b>tbwikidb files</b> - data db_type="tbwikidb" | | |
| <ul><li>data is parsed from tbwiki pages specified using a source_spec | | |
| <li>set match_spec="<tbwikidb>" | | |
| <li>must have a a page named "<base_name>template" to use to add new pages | | |
| <ul><li>example: _bug_template | | |
| </ul></ul><li><b>test data</b> - db_type="test_data" | | |
| <ul><li>this is a special set of test data internal to the table.py module. | | |
| It is used for testing only. | | |
| </ul> | | |
| </ul> | | |
| <p> | | |
| <h2><a name="MoinMoin_Tables">MoinMoin Tables</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=MoinMoin_Tables">edit section</a>]</font></span> | | |
| </h2> | | |
| 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. | | |
| <p> | | |
| Sample source spec: source_spec=PhoneDatabase | | |
| <pre> | | |
| ||Name||Phone|| | | |
| ||Tim ||555-1212|| | | |
| ||Nadine||444-2323|| | | |
| </pre> | | |
| <p> | | |
| <h2><a name="Attribute_files">Attribute files</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Attribute_files">edit section</a>]</font></span> | | |
| </h2> | | |
| Attribute files are a collection of files, each containing a single record, | | |
| where the fields for each record are stored as name-value pairs. | | |
| <p> | | |
| Sample source spec: source_spec=Book.* | | |
| <p> | | |
| Any file starting with "Book" would be considered a record in the table. | | |
| <p> | | |
| Attributes are listed in the file as name=value pairs. | | |
| Values can be single-line or multi-line. Multi-line values are | | |
| <p> | | |
| 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. | | |
| <p> | | |
| 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. | | |
| <p> | | |
| <h2><a name="Inline_Tables">Inline Tables</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Inline_Tables">edit section</a>]</font></span> | | |
| </h2> | | |
| Inline tables are stored inline on a regular tbwiki page. They currently only | | |
| support moinmoin table syntax, and can be either named or unnamed. | | |
| <p> | | |
| An inline table is embedded inside a #!Table processor block, | | |
| and each line of the table has fields separated by double vertical bars. | | |
| <p> | | |
| Here's an example: | | |
| <pre> | | |
| | | |
| {{{#!Table | | |
| ||Name||Job|| | | |
| ||Tim||engineer|| | | |
| ||Fred||pilot|| | | |
| }} } | | |
| | | |
| </pre> | | |
| <p> | | |
| Only one unnamed table may be used on a single wiki page. | | |
| <p> | | |
| Multiple named tables may be used on a wiki page. | | |
| A named table uses a block with a name. | | |
| <p> | | |
| <pre> | | |
| {{{BlockName | | |
| <data> | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| Also, the table processor declaration can include a block name: | | |
| <pre> | | |
| {{{#!Table:BlockName | | |
| <data> | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| <h2><a name="Parsed_data_Tables">Parsed data Tables</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Parsed_data_Tables">edit section</a>]</font></span> | | |
| </h2> | | |
| Using the <i>scan_data</i> module, you can parse data from multiple sources. | | |
| <p> | | |
| These can be tbwiki pages, local files, or web pages. | | |
| <p> | | |
| Parsing data from external web pages (scraping them) may introduce long | | |
| delays in displaying a table. | | |
| <p> | | |
| This produces a read-only database. | | |
| <p> | | |
| Here is a sample table declaration for the FIXTHIS list for the tbwiki software: | | |
| <p> | | |
| <pre> | | |
| {{{#!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 | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| For more information, see <a href="/tbwiki/DocParsedTables">DocParsedTables</a> | | |
| <p> | | |
| <h2><a name="TBWikiDB_Tables">TBWikiDB Tables</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=TBWikiDB_Tables">edit section</a>]</font></span> | | |
| </h2> | | |
| This reads a database from regular tbwiki page files in the current wiki. | | |
| <p> | | |
| 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) | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=Bug_.* | | |
| match_spec=<tbwikidb> | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| 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. | | |
| <p> | | |
| Here's an example format: | | |
| <pre> | | |
| ; Summary: summary of item | | |
| ; Responsible: Tim | | |
| | | |
| = Description = | | |
| This is a description of the | | |
| first item, and can have: | | |
| * regular tbwiki markup | | |
| </pre> | | |
| <p> | | |
| 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". | | |
| <p> | | |
| <h1><a name="Table_type_specifiers">Table type specifiers</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Table_type_specifiers">edit section</a>]</font></span> | | |
| </h1> | | |
| The following fields, that are part of the table configuration, determine how the table is parsed: | | |
| <p> | | |
| <h2><a name="source_spec">source_spec</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=source_spec">edit section</a>]</font></span> | | |
| </h2> | | |
| 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!) | | |
| <p> | | |
| 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. | | |
| <p> | | |
| If the string starts with "http", then it indicates a URL for a web page from | | |
| which the data is loaded. | | |
| <p> | | |
| Here are some examples: | | |
| <ul><li>source_spec=/home/user/big/security/hole/file - read table data from the indicated file in the host filesystem | | |
| <li>source_spec=page_name - read table data from page "page_name" in the set of tbwiki pages | | |
| <li>source_spec=page_name:table_block_name - read table data from the block named "table_block_name" in the page "page_name" (from the tbwiki pages). | | |
| <li>source_spec=page_name_.* - read table data from multiple pages - from all tbwiki pages that start with "page_name_" | | |
| <li>source_spec=<a href="http://domain.com/foo.html">http://domain.com/foo.html</a> - read table data from the indicated web page (this is usually used with a match_spec to parse the data on the page into records). | | |
| <li>source_spec=/tmp/file1:/tmp/file2.* - read table data from multiple files in the local filesystem (usually used with a match_spec to parse the data in the files into records) | | |
| </ul> | | |
| <p> | | |
| <h2><a name="match_spec">match_spec</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=match_spec">edit section</a>]</font></span> | | |
| </h2> | | |
| A match_spec is used to describe the regular expressions which are used to gather the record fields and values from the data. | | |
| <p> | | |
| 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). | | |
| <p> | | |
| To identify the beginning of each record, the | | |
| "record_start=" special variable is used. | | |
| <p> | | |
| Named fields are parsed according to the regular expressions given in the match_spec. | | |
| <p> | | |
| 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. | | |
| <p> | | |
| See <a href="/tbwiki/DocWebSed">DocWebSed</a> for details about pattern matching and regular expressions | | |
| that are supported by the <i>data_scan</i> module. | | |
| <p> | | |
| <p> | | |
| <i>basename</i> and | | |
| <p> | | |
| <i>line_no</i> 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. | | |
| <p> | | |
| <h1><a name="Declaring_a_Table">Declaring a Table</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Declaring_a_Table">edit section</a>]</font></span> | | |
| </h1> | | |
| <p> | | |
| 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: | | |
| <p> | | |
| <h2><a name="Macro_Syntax">Macro Syntax</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Macro_Syntax">edit section</a>]</font></span> | | |
| </h2> | | |
| Here is a sample macro referencing a simple table: | | |
| <pre> | | |
| {{Table(Table1Data)}} | | |
| </pre> | | |
| <p> | | |
| <h2><a name="Processor_Syntax_(with_examples)">Processor Syntax (with examples)</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Processor_Syntax_(with_examples)">edit section</a>]</font></span> | | |
| </h2> | | |
| Here is a sample processor syntax for a simple table (with data | | |
| specified in an external file (another page): | | |
| <p> | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=Table1Data | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| Here is the sample processor syntax for a multi-file table: | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=bug_.* | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| You can also specify data in a named block on either the current | | |
| page or another page: | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=#:DataBlock1 | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=DataPage:DataBlock1 | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| <h1><a name="Table_configuration">Table configuration</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Table_configuration">edit section</a>]</font></span> | | |
| </h1> | | |
| 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). | | |
| <p> | | |
| Here are some of the attributes you can use with the Table processor: | | |
| <ul><li><b>source_spec</b> - specifies the source of the data | | |
| <li><b>match_spec</b> - used for parsed-data and tbwikidb tables | | |
| <ul><li>see below | | |
| </ul><li><b>cols</b> - specify the columns of the table to output (and their order) | | |
| <ul><li>e.g. cols=field2:field1:field4 | | |
| </ul><li><b>sortby</b> - specify the sort order for displayed records | | |
| <ul><li>multiple fields can be specified, along with their sort type | | |
| <li>e.g. sortby=age:int,name:alpha | | |
| <li>sort types are: | | |
| <ul><li><b>alpha</b> - alphanumeric order (default) | | |
| <li><b>alphai</b> - alphanumeric, case-insensitive | | |
| <li><b>rev_alpha</b> (reverse alpha) | | |
| <li><b>rev_alphai</b> | | |
| <li><b>int</b> - integer order | | |
| <li><b>rev_int</b> | | |
| <li><b>month</b> - by month name | | |
| <li><b>rev_month</b> | | |
| <li><b>possible_values</b> - by order in possible_values list | | |
| <li><b>rev_possible_values</b> | | |
| </ul></ul><li><b>field</b> - specify field type information (multiple declarations allowed) | | |
| <ul><li>these are specified as one or more "field=" statements, using standard attribute syntax | | |
| <li>each "field=" line begins a new field type declaration | | |
| <li>see below for details | | |
| </ul><li><b>row_filter</b> - specify an expression used to filter records in the table | | |
| <ul><li>you can test for string equality, numeric inequality or a regular expression match | | |
| <li>the format of the row_filter is: row_filter=<expression>, where <expression> consists of <field_name><operator><value> | | |
| <ul><li>No extraneous spaces are allowed between the word 'row_filter=' and the expression, or between the field_name, operator or value | | |
| <ul><li>this allows for testing values with leading spaces | | |
| </ul><li>the operator can be one of: =, ==, !=. <, >, <=, >=, *= | | |
| <li>'=' and '==' are used for string equality tests: | | |
| <ul><li>ex: row_filter=name=Tim | | |
| </ul><li>either != or <> can be used for string inequality tests: | | |
| <ul><li>ex: row_filter=status!=done | | |
| </ul><li><, >, <=, >=, <> are used for numeric inequality tests | | |
| <ul><li>ex: row_filter=age>=30 | | |
| <li>ex: row_filter=pressure<>10 | | |
| </ul><li>*= is used for regular expression matches | | |
| <ul><li>ex: row_filter=status*=closed|fixed | | |
| <li>ex: row_filter=uses_linux*=[Yy]es | | |
| <li>ex: row_filter=uses_linux*=.*es | | |
| </ul><li>if the value has both a leading and trailing double-quote, they are removed | | |
| <ul><li>ex: row_filter=name=="spaces at end " | | |
| </ul> | | |
| </ul> | | |
| </ul> | | |
| </ul> | | |
| <p> | | |
| <ul><li><b>add_form</b> - specify a form used for adding a new record to the table | | |
| <ul><li>see <a style="color:red;" href="/tbwiki/TestTableUser">TestTableUser</a> | | |
| </ul><li><b>edit_form</b> - specify a form used for editing an existing record in the table | | |
| <li><b>conf</b> - specify the configuration in a separate block | | |
| <ul><li>see below for details | | |
| </ul><li><b>query</b> - specify a block containing query attributes | | |
| <ul><li>Usually, this specifies a block of configuration information, along with a row-filter, to generate a particular view of the data | | |
| <li>see below for details | | |
| </ul><li><b>is_spreadsheet</b> - set to 1 to indicate that the table contains formulas that should be evaluated | | |
| <li><b>show_sort_links</b> - set to 0 to indicate that sort-links should NOT be shown in the table header. This refers to the small caret that links to a version of the table sorted by that column. | | |
| <li><b>show_edit_links</b> - set to 0 to indicate that edit links should not be shown as part of table output. This refers to the links underneath the table that allow someone to edit items in the table. This is mostly useful for inline tables that are for presentation only. That is, that are to be edited as part of wiki text, and not with a table form. | | |
| <li><b>show_as_markup</b> - set to 0 to show data in each cell of the table as plain text. Defaults to 1, which indicates that the table data is processed as tbwiki markup. | | |
| <li><b>show_template</b> - set to 1 to show the template file in a tbwikidb (so it appears in the table). By default the template file is not shown in the table. | | |
| </ul> | | |
| <p> | | |
| <h2><a name="field_attributes">field attributes</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=field_attributes">edit section</a>]</font></span> | | |
| </h2> | | |
| You can specify the attributes of individual fields, so that TBWiki can | | |
| process them properly (generate appropriate default forms, and display | | |
| the values properly). | | |
| <p> | | |
| field attributes are: | | |
| <ul><li><b>field</b> - specify the name of the field for which attributes are being declared | | |
| <li><b>type</b> - specify the type of the field | | |
| <ul><li>possible types are: Text, Textarea, Select, Checkbox, Radio | | |
| </ul><li><b>default_value</b> - specify a value to pre-populate in the field in an Add form | | |
| <li><b>possible_values</b> - list of possible values for the field | | |
| <ul><li>specified with a multi-line value, one value per line | | |
| </ul><li><b>colors</b> - list of specifiers used to define the background color for a field, based on the field's current value | | |
| <ul><li>see below for definitions and syntax | | |
| </ul> | | |
| </ul> | | |
| <p> | | |
| <h3><a name="dynamic_default_values">dynamic default values</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=dynamic_default_values">edit section</a>]</font></span> | | |
| </h3> | | |
| 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: | | |
| <ul><li>%(timestamp)s - yields a string in the format: yyyy-mm-dd_HH:MM:SS.SS | | |
| <li>%(next_int)s - yields a value one higher than all other integer values in this field in the table already | | |
| <li>%(next_page_num)s = yields a number one higher than all other page numbers already used for this table (for attrdb and tbwikidb databases only). This number is used in the filename to create a new file. | | |
| </ul> | | |
| <p> | | |
| <h3><a name="color_specifiers">color specifiers</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=color_specifiers">edit section</a>]</font></span> | | |
| </h3> | | |
| 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 | | |
| <ul><li>the 'colors' field attribute is used to provide a list of tests and colors | | |
| <ul><li>each line in the list is of the form: | | |
| <ul><li>test:color | | |
| </ul><li>these list of tests are matched against the field value, and if a match is found, the background color of the cell for that item is shaded in the specified color | | |
| </ul><li>a tests consists of either a word or phrase, or a python expression enclosed in parenthesis, using the term 'value'a colon | | |
| <ul><li>an expression can be a single word or phrase, in which case if the field value matches the word or phrase exactly, the test passes (and the color is used) | | |
| <ul><li>ex: | | |
| <pre> | | |
| colors="""good:green | | |
| bad:red | | |
| more than one word:yellow""" | | |
| </pre> | | |
| </ul><li>colors can be color names or hex triples | | |
| <li>expressions can be any valid python expression | | |
| <ul><li>expressions can include boolean operators (not, and, or) | | |
| <li>expressions can test for equality or inequality | | |
| </ul><li>ex: (int(value)<20):blue - show a blue cell for items with value<20 | | |
| <li>ex: show "good" cells in green and "bad" cells in red | | |
| <pre> | | |
| colors="""(value=="good"):green | | |
| (value=="bad"):ff0000""" | | |
| </pre> | | |
| <li>ex: show particular values in yellow | | |
| <pre> | | |
| colors="""(int(value)>5 and int(value)<10):00ffff""" | | |
| </pre> | | |
| <li>ex: show some words in orange | | |
| <pre> | | |
| colors="""(value in ["lemon", "banana"]):orange | | |
| (value in ["apple", "cherry", "stawberry", "tomato"]):red""" | | |
| </pre> | | |
| </ul> | | |
| </ul> | | |
| <p> | | |
| <h4><a name="field_attribute_samples">field attribute samples</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=field_attribute_samples">edit section</a>]</font></span> | | |
| </h4> | | |
| Field types and possible values: | | |
| <pre> | | |
| {{{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 | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| Color Expressions: | | |
| <pre> | | |
| {{{#!Table | | |
| source_spec=#:ReactorTable | | |
| field=reactor_temperature | | |
| colors="""(int(value)<10):ffffd0 | | |
| (int(value)>=10 and int(value)<50):ffff80 | | |
| (int(value)>500):ffd0d0""" | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| <h2><a name="Configuration_blocks">Configuration blocks</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Configuration_blocks">edit section</a>]</font></span> | | |
| </h2> | | |
| You can specify several "configuration" attributes of a table, | | |
| and use them repeatedly (i.e. from different processor blocks), using | | |
| the "conf=" attribute | | |
| <p> | | |
| Configuration blocks are used to describe any of the attributes of the | | |
| table. | | |
| <p> | | |
| <h2><a name="Query_blocks">Query blocks</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Query_blocks">edit section</a>]</font></span> | | |
| </h2> | | |
| 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. | | |
| <p> | | |
| <h1><a name="Views">Views</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Views">edit section</a>]</font></span> | | |
| </h1> | | |
| To show a table on a page, you use the table processor, which has the format: | | |
| <pre> | | |
| {{{#!Table | | |
| <source spec> | | |
| }} } | | |
| </pre> | | |
| <p> | | |
| You can also specify view a table that references a query configuration block, | | |
| to show a different view of the table. | | |
| <p> | | |
| <h2><a name="Custom_forms_and_views">Custom forms and views</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Custom_forms_and_views">edit section</a>]</font></span> | | |
| </h2> | | |
| You can create custom views of table data, by creating a view file. | | |
| <h3><a name="View">View</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=View">edit section</a>]</font></span> | | |
| </h3> | | |
| To create a custom view for a record, ... | | |
| <p> | | |
| <ul><li>line_edit_form_spec, get_default_line_edit_form_spec | | |
| </ul> | | |
| <p> | | |
| <ul><li>there is no way to do a custom view right now. | | |
| </ul> | | |
| <p> | | |
| <ul><li>see def html_string() | | |
| <ul><li>should support non-table views? | | |
| <ul><li>need view_header, view_record, view_footer definitions in config | | |
| <li>view_record needs could have: | | |
| <pre> | | |
| view_record_form=""" | | |
| <tr><td>%(nameField)s</td><td>custom text...%(phoneField)s</td></tr> | | |
| <tr><td>next line:%(addr1Field)s</td><td>%(addr2Field)s</td/</tr> | | |
| """ | | |
| </pre> | | |
| </ul> | | |
| </ul> | | |
| </ul> | | |
| <p> | | |
| <ul><li>or, not in a table format: | | |
| <pre> | | |
| view_record_form="""Name: %(nameField)s<br> | | |
| Phone: %(phoneField)s<br> | | |
| Address: %(addr1Field)<br>%addr2Field) | | |
| <HR> | | |
| """ | | |
| </pre> | | |
| </ul> | | |
| <p> | | |
| 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. | | |
| <p> | | |
| Example: | | |
| <pre> | | |
| add_form="""Name: %(nameValue)s<br> | | |
| Phone: %(phoneField)s<br> | | |
| """ | | |
| </pre> | | |
| <p> | | |
| <h3><a name="Add_Form">Add Form</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Add_Form">edit section</a>]</font></span> | | |
| </h3> | | |
| 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. | | |
| <p> | | |
| 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" | | |
| <p> | | |
| 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. | | |
| <p> | | |
| Here is an example showing fields in an HTML table. | | |
| <pre> | | |
| 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> | | |
| """ | | |
| </pre> | | |
| <p> | | |
| 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. | | |
| <p> | | |
| <h3><a name="Edit_form">Edit form</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Edit_form">edit section</a>]</font></span> | | |
| </h3> | | |
| 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. | | |
| <p> | | |
| <h2><a name="Field_format_strings_for_edit_forms">Field format strings for edit forms</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Field_format_strings_for_edit_forms">edit section</a>]</font></span> | | |
| </h2> | | |
| 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. | | |
| <p> | | |
| 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). | | |
| <p> | | |
| Possible types are: | | |
| <ul><li>Text | | |
| <li>TextArea | | |
| <li>Checkbox | | |
| <li>Select | | |
| <li>Radio | | |
| </ul> | | |
| <p> | | |
| If something besides one of these types is specified, it is used | | |
| as the INPUT field type in the HTML file element. | | |
| <p> | | |
| <h2><a name="Special_form_elements">Special form elements</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Special_form_elements">edit section</a>]</font></span> | | |
| </h2> | | |
| The following are special form elements that can be placed anywhere in the | | |
| form specification: | | |
| <ul><li>SaveButton - this shows the button to submit the form data | | |
| <li>CancelButton - this cancels the operation and doesn't save the form data | | |
| </ul> | | |
| <p> | | |
| <h1><a name="Spreadsheets">Spreadsheets</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Spreadsheets">edit section</a>]</font></span> | | |
| </h1> | | |
| Tables may contain formulas which are calculated at runtime to produce a final value for a "cell". | | |
| <p> | | |
| 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). | | |
| <p> | | |
| <h2><a name="formulas">formulas</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=formulas">edit section</a>]</font></span> | | |
| </h2> | | |
| Formulas can consist of simple arithmetic, or functions. Formulas | | |
| operate on data obtained from other cells, including individual cells and | | |
| ranges, and on literals. | | |
| <p> | | |
| A formula always starts with an equal sign ('='). Here is a very simple | | |
| formula: '=2+2' | | |
| <p> | | |
| Currently supported functions are: | | |
| <ul><li>SUM(<range>) - calculates the sum of the numbers in the indicated range of cells | | |
| <li>COUNT(<range>) - show the number of cells in a range that have numeric values | | |
| <li>COUNTA(<range>) - show the number of non-empty cells in a range | | |
| <li>AVERAGE(<range>) - show the average for cells containing numeric values in a range of cells | | |
| <li>AVERAGEA(<range>) - show the average for cells containing non-empty values in a range of cells (TRUE is calculated as 1, any other non-numeric value is 0, but counts towards the count of cells) | | |
| <li>MAX(<list>) or MAX(<range>) - show the maximum value from a list of values or a from a given range | | |
| <li>MIN(<list>) or MAX(<range>) - show the minimum value from a list of values or from a given range | | |
| </ul> | | |
| <p> | | |
| FIXTHIS - add support for COUNTIF | | |
| <p> | | |
| <h2><a name="cell_addressing">cell addressing</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=cell_addressing">edit section</a>]</font></span> | | |
| </h2> | | |
| 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. | | |
| <p> | | |
| 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. | | |
| <p> | | |
| Let's say we had the following table: | | |
| <table border=1> | | |
| <tr bgcolor="#d0d0ff"> | | |
| <td align="center"><b>item</b> <a href="/tbwiki/DocTables?sortby=item">^</a></td> | | |
| <td align="center"><b>count</b> <a href="/tbwiki/DocTables?sortby=count">^</a></td> | | |
| <td align="center"><b>price</b> <a href="/tbwiki/DocTables?sortby=price">^</a></td> | | |
| <td align="center"><b>total</b> <a href="/tbwiki/DocTables?sortby=total">^</a></td> | | |
| </tr> | | |
| <tr> | | |
| <td>paper</td> | | |
| <td>5</td> | | |
| <td>.07</td> | | |
| <td>=$paper.count * paper.price</td> | | |
| </tr> | | |
| <tr> | | |
| <td>pencil</td> | | |
| <td>2</td> | | |
| <td>.50</td> | | |
| <td>=$pencil.count * $pencil.price</td> | | |
| </tr> | | |
| </table> | | |
| <a href="/tbwiki/DocTables?action=Table.query_form&source_spec=DocTables%3Aexample1&conf=&table_block=DocTables%3Aexample1"> | | |
| <font size=-1>Query</font></a> | | |
| <br> | | |
| | | |
| <p> | | |
| 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. | | |
| <p> | | |
| 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. | | |
| <p> | | |
| 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.) | | |
| <p> | | |
| 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. | | |
| <p> | | |
| 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). | | |
| <p> | | |
| 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: | | |
| <ul><li>'$-1.' indicates the cell one row previous (the cell just above the current one) | | |
| <li>'$.-1' indicates the cell one column previous (the cell just to the left of the current one) | | |
| <li>'$+2.-1' indicates a cell 2 rows down and one column back from the current cell. | | |
| </ul> | | |
| <p> | | |
| Functions often operate on ranges. A range is just two cell references, | | |
| separated by a colon. Like so: | | |
| <pre> | | |
| =SUM($paper.total:$pencil.total) | | |
| </pre> | | |
| Currently, ranges are evaluated left-to-right and top-to-bottom. | | |
| <p> | | |
| <h2><a name="examples">examples</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=examples">edit section</a>]</font></span> | | |
| </h2> | | |
| See <a style="color:red;" href="/tbwiki/TestSpreadsheetStuff">TestSpreadsheetStuff</a> for examples. | | |
| <p> | | |
| <h1><a name="Things_to_Describe">Things to Describe</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=Things_to_Describe">edit section</a>]</font></span> | | |
| </h1> | | |
| <ul><li>sequencing of records | | |
| <li>filtering | | |
| <li>sorting | | |
| <li>views | | |
| <li>default values | | |
| <li>spreadsheets | | |
| <li>match_spec conf description and examples | | |
| </ul> | | |
| <p> | | |
| <h2><a name="not_implemented_yet">not implemented yet</a> | | |
| <span align=right class="section_edit_link">[<a href="/tbwiki/DocTables?action=edit§ion=not_implemented_yet">edit section</a>]</font></span> | | |
| </h2> | | |
| Support generated default values for fields: | | |
| <ul><li>%(cur_date)s | | |
| <li>%(next_int)s | | |
| </ul> | | |
| <p> | | |