Any number of DB data sources can be added as page sources to the design of a page and then used within it. Whether a DB page source is editable or not is defined at the time the page source is added. Specify a DB page source to be uneditable if its data is required only for presentation. Make the page source editable if you want to allow clients to modify DB data.
When a DB source is added, a data tree is generated (see screenshot below and the section Tree structure below). Each DB table row corresponds to a Row element; the table's columns are added as attributes of the Row element. If the page source is used on multiple pages, then a single tree structure can be shared across all instances of the page source. The option to share a tree structure is available each time a page source is added that is used on another page. When a shared structure is modified, you are offered the option of modifying the shared page source in its multiple instances across pages; alternatively, the shared page source is modified only in the instance in which it is modified..
|Note:||If SQL statements are stored in a page source, they might trigger firewall rules while the design is executed on a client device. To prevent this, it is recommended that you do one of the following: (i) set the page source property Keep data on to Server only; (ii) use SSL for client connections; (iii) assemble the SQL statement on the server when required..|
After you have created a design that uses a DB page source, you can switch the page source to another DB that has the same structure and continue to use the original design. To switch the DBs of a page source, right-click the $DB root node of the tree, select Choose DB Data Source, and continue with the DB connection process.
Two DBs are considered to have the same structure if they have the same table names, same column names, and same column definitions. If the new structure is different in any way, although the connection to the DB will be made, the page source will not be updated with data from the new DB. If the DB switch is aborted, then the page source will continue to use the original DB.
|Note:||If the DBs involved in the switch have different case-sensitivities, then you will have to modify SQL statements, XPath expressions, and any other constructs that use the non-matching names.|
Every DB page source has the following structure:
$DBX (the root node)
|--DB (the root element)
| |--RowSet (a container element for the rows of the DB table)
| | |
| | |--Row (the rows of the DB table)
| | | |
| | | |--<Attributes> (the columns of the DB table)
The nodes in the tree can be addressed using XPath expressions. If a node is set as the XPath context node for the page (via the node's context menu), then XPath expressions can be built relative to the XPath context node. Otherwise nodes can be addressed using absolute paths starting at the root node: $DBX/DB/RowSet/Row/MyAttribute.
You can also use XQuery expressions to retrieve or manipulate data in the DB tree. See the section on primary keys below for an example.
In order for data to be edited and saved, the tree of the page source must also include an OriginalRowSet element, which is a copy of the RowSet element. The original data is saved in the OriginalRowSet element, while edited data is saved in the RowSet element. When the page source is saved, the difference between the two trees, OriginalRowSet and RowSet, is calculated, and the page source is updated on the basis of the difference. If the modification is successful, then the modified data is copied to OriginalRowSet so that OriginalRowSet contains the newly saved DB data, and the modification process can be repeated.
Note the following points:
•The OriginalRowSet element is not created by default in the tree of the DB page source. To create it, right-click the root node of the DB page source and toggle on the command Create OriginalRowSet.
•The Create OriginalRowSet.command is enabled for database type ($DB) root nodes. It is a toggle command that creates/removes an OriginalRowSet data structure that contains the original data of the page source.
•Till the time modified data is saved to the DB, the original DB data is retained in the OriginalRowSet structure. This ensures that the original DB data is still available in the tree.
•To retrieve the original data of a DB row that has been modified but not yet saved, use the XPath function mt-db-original-row.
Primary keys in DBs typically are auto-incrementing. When this is the case and a new row is added to a table, the primary key column of the added row is automatically incremented. In MobileTogether Designer, when a table is retrieved the primary key and auto-increment information is automatically retrieved and displayed in the Page Sources Pane (see screenshot below).
If auto-retrieval of this information was not successful, the context menu of tree nodes contains toggle commands that enable you to correctly annotate nodes (see screenshot below).
If the primary key column is not auto-incrementing, new primary key values for appended rows must be automatically generated using an XQuery expression. This is because primary key columns cannot be edited. The XQuery expression is inserted by using the primary-key node's context menu command, Ensure Exists before Page Load (XPath Value). In the example below, a new value is generated for the primary key @id by using the following XQuery expression:
let $all := $DB1/DB/RowSet/Row/@id
let $ids := remove($all, index-of($all, ""))
let $id := if (empty($ids)) then 1 else max($ids) + 1