{{{#!wiki red
 '''DRAFT'''
}}}

<<TableOfContents(2)>>

In order to use forms in the wiki you have to define a connection to a database and specify the form's attributes and design.


= Database Connections =
Database connections are defined by adding connection attributes to dictionary pages (per default pages ending with Dict).

Connection attribute are definitions in wiki markup having the following syntax:
{{{
[<connection_name>].<attribute>:: <value>
}}}
If connection_name is omitted the attribute refers to the default connection (the connection without a name). The value may be enclosed by (`) to prevent it from being formatted as wiki text.

 Example::
 {{{
 .type:: `sqlite`
 .file:: `attachment:Recipes.db`
 }}}

The following attributes are allowed:
|| '''attribute''' || '''value''' || '''description''' ||
|| type || sqlite | odbc | mysql | oracle || Specifies the database type. ||

The other attributes are dependent of the database type used:
 SQLite Databases::
 || '''attribute''' || '''description''' ||
 || file || Specifies the location of the database. This may be a path to a file in the file system or an attachment. ||

 ODBC Databases::
 || '''attribute''' || '''description''' ||
 || connection_string || Connection string used to connect to the ODBC database. ||

 MySQL, Oracle Databases::
 || '''attribute''' || '''description''' ||
 || server || Name of the database server ||
 || port || Port of the database server (default: 3306 for MySQL and 1521 for Qracle) ||
 || database || Name of the database on the server ||
 || uid || User Id used to logon to the database server ||
 || pwd || Password ||


== Example Connection Parameters ==
=== ODBC Connections ===
Examples providing the full connection information:
{{{
 .connection_string:: `driver:{SQL Server}, server:(local), database:HRDB, Trusted_Connection:Yes`
 .connection_string:: `driver:{SQL Server}, server:SQL01, database:HRDB, uid:moinwiki, pwd:moin-odbc`
 .connection_string:: `driver:{Microsoft ODBC for Oracle}, server:ORACLE01, Persist Security Info:False, Trusted_Connection:Yes`
 .connection_string:: `driver:{Microsoft Access Driver (*.mdb)}, dbq:C:\Program Files\TSL\HR\HR.mdb`
 .connection_string:: `driver:{Microsoft Excel Driver (*.xls)}, dbq:C:\Program Files\TSL\HR\Vacation.xls`
 .connection_string:: `driver:{Microsoft Text Driver (*.txt; *.csv)}, dbq:C:\Program Files\TSL\HR\Locations.txt`
}}}

Example on using an ODBC data source name:
{{{
 .connection_string:: `dsn:HRDB, uid:moinwiki, pwd:moin-odbc`
}}}

Further information on ODBC connection strings can be found at:
 * http://pyodbc.sourceforge.net/docs.html
 * http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring.aspx
 * http://msdn2.microsoft.com/en-us/library/aa177865(SQL.80).aspx

=== MySQL and Oracle Connections ===
{{{
 .server:: `SQL01`
 .port:: `3306`
 .database:: `hrdb`
 .uid:: `moinwiki`
 .pwd:: `moin-db`
}}}
The port value is optional. If it is omitted, the default port value of the connection type is used.


= Forms =
A form is defined by it's '''attributes''' which are specified on a dictionary page (like database connections) and it's '''design''' which is build by moin markup.

== Form Attributes ==
Form attributes are definitions in wiki markup having the following syntax:
{{{
[<form_name>].<attribute>:: <value>
}}}
If form_name is omitted the attribute refers to the default form (the form without a name). The value may be enclosed by (`) to prevent it from being formatted as wiki text.

 Example::
 {{{
 .select:: `select * from Recipes order by Name`
 }}}

The following attributes are allowed:
|| '''attribute''' || '''value''' || '''description''' ||
|| connection || <connection_name> || specifies the name of a definition of a database connection (see section Database Connections). If this attribute is omitted, the default connection specified in the same dictionary as the form is used. ||
|| select || <query> || specifies a query for retrieving the data of the form ||
|| select_parameter || <parameter1> [, <parameter2> [...]] || specifies a list of parameters passed to the select query ||
|| all || `0 | 1` || specifies if all records are displayed or just a single record. ||
|| addonly || `0 | 1` || Specifies if the form is ''only'' used to insert data. Then no records are displayed. ||
|| add || `0 | 1` || Specifies if the form opens in insert mode. ||
|| goto_fields ||  || List of fields to check when selecting current record. ||
|| goto_values ||  || Values the record selected as current needs to match.  ||
|| <field_name>.default ||  || [Not yet implemented] Default value of the field (on insert). ||
|| <field_name>.values || list or query || [Not yet implemented] List of possible values in an option field (default: all retrieved values and empty) ||
|| insert || <query> || specifies a query used to insert new rows. ||
|| insert_parameter || <parameter1> [, <parameter2> [...]] || specifies a list of parameters passed to the insert query ||
|| update || <query> || specifies a query used when updating a record ||
|| update_parameter || <parameter1> [, <parameter2> [...]] || specifies a list of parameters passed to the update query ||
|| delete || <query> || specifies a query used to delete a record ||
|| delete_parameter || <parameter1> [, <parameter2> [...]] || specifies a list of parameters passed to the delete query ||
|| header || wiki markup with \n || The header of the form. ||
|| body || wiki markup \n || The body of the form. If the `all` attribute is set to 1, the body is repeated for each record (showing all records). ||
|| footer || wiki markup \n || The footer of the form. ||


Query parameter or goto values can be:
 * a field of the form or any sub form
 * an unbound field
 * or any of the following variables:
 || PAGE || the current page name ||
 || NOW || the momentan time stamp ||
 || TODAY || the current date ||
 || ME || the current users login name ||

This allows e.g. filtering the records for the current user or just records with todays date.


== Form Design ==
Forms are designed by putting form macro elements on a page.
{{{
<< Form( <element> [, parameters... ] )>>
}}}
 element:: is one of the elements described below.

 Example (of a simple form)::
 {{{
<<Form(Start)>>
|| Name       || <<Form(Text, Recipes/FormsDict..Name, 30)>> ||
|| Category   || <<Form(Text, .Category, 30)>>               ||
|| Servings   || <<Form(Text, .Servings, 4)>>                ||
|| Energy     || <<Form(Text, .Energy, 4)>> Cal.             ||
|| Time       || <<Form(Text, .Time, 4)>> minutes            ||
|| Difficulty || <<Form(Text, .Difficulty, 4)>>              ||
|| Procedure  || <<Form(Textarea, .Procedure, 50, 8)>>       ||
||<-2><<Form(Navigation)>>                                   ||
<<Form(End)>>
 }}}
 This displays the following form:

 {{attachment:MacroMarket/Form/Help/Tutorial/RecipesDemo.png}}


|| '''Element''' || '''Syntax''' || '''Description''' ||
|| Start || `<<Form(Start)>>` || Specifies where the starting `<Form>` tag is placed in the rendered html page. It should be the first macro call of a form. ||
|| End || `<<Form(End)>>` || Specifies where the ending `</Form>` tag is placed in the rendered html page. It should be the last macro call of a form. ||
|| Form || `<<Form(Form, <form_key>)>>` || Specifies the sub form which will be rendered on this macro call. ||
|| Next || `<<Form(Next [, <form_key>])>>` || Specifies that the next record should be selected. Allows displaying several consecutive records. ||
|| Buttons || `<<Form(Buttons [, <form_key>])>>` || Displays the default buttons of the form (the buttons displayed depend on the form, if the form is update able and if delete and insert queries are defined). ||
|| Navigation || `<<Form(Navigation [, <form_key>])>>` || Display buttons allowing to navigate through the records. ||
|| Text || `<<Form(Text, <field_key>`<<BR>>`[, <size>])>>` || Displays a text input field. ||
|| Textarea || `<<Form(Textarea, <field_key>`<<BR>>`[, <cols>, <rows>])>>` || Displays a text area field. ||
|| Value || `<<Form(Value, <field_key>)>>` || Displays only the value of the field (not editable). ||
|| Hidden || `<<Form(Hidden, <field_key>)>>` || The value is added to the form but not displayed. ||

 form_key:: `[[<page_name>][.<form_name>]]`
 Specifies a form named <form_name> defined on the page <page_name> (see section Form Attributes). If page_name is empty, the form must be defined on the current page. If the form name is empty, it refers to the default form on that page (the form without a name).

 field_key:: `[[<page_name>][.<form_name>]].<field_name>`
 Specifies field named <field_name> of form <form_name> defined on page <page_name>. Field names starting with "@" are unbound fields allowing to insert values for filtering.

The form design can also be specified by the forms header, body and footer attributes (see section Form Attributes).
