Add New Books
In order to add new books, we can use the Editing Page to fill in author and book data. This page uses the \$EditBook page source, which selects and displays the data of one book: The author ID and book ID of the book to select are obtained from the \$PERSISTENT tree. So now, if we want to use this subpage as our form for filling data about a new book and display a page with empty entry fields, then we must not select any existing record from the DB and present empty fields for data entry. We can do this by setting the IDs in the \$PERSISTENT tree to the empty string, respectively, before opening the subpage.
However, we must make sure that we use unique IDs for the primary keys of the new author record and the new book record. We can do this by incrementing each of these values by one over the largest existing ID value in the respective tables.
The text fields and image field of the \$EditBook page source can be edited in the same way as when modifying the values. When \$EditBook is saved, the new data will be saved to a new author record and a new book record in the Authors and Books tables, respectively. Both new records will have not only new unique IDs but also a correct foreign key in the Books table—one that links it to the correct author.
The mechanism described above is implemented through the actions of (i) the Add New Book button on the Main Page, and (ii) the OnPageLoad actions of the Editing Page. These two sets of actions are described below.
Add New Book actions (Main Page)
The actions for going to the Editing Page subpage are set on the Add New Book button (see screenshot below).
The actions are the same as for the actions for editing a book in that the Edit Author Action Group is called. Note, however, that before the call to this Action Group, the data in the \$PERSISTENT tree is reinitialized—with Genre being set to All.
In the call to the Edit Author Action Group (see screenshot above), the parameters \$AuthorID and \$BookID are set to the empty string. In the Edit Author Action Group, the EditAuthorID and EditBookID nodes of the \$PERSISTENT tree are updated with these values (see screenshot below). This is important because the \$EditBook page source of the Editing Page subpage selects the book to display according to the corresponding values in the \$PERSISTENT tree. Since these values are the empty string, there is no record in the DB that will be selected. Consequently, the nodes of the \$EditBook page source would be empty—as will the entry fields on the page.
However, there are three DB fields that must not be empty. These are the primary keys of the new Authors record and new Books record, as well as the foreign key of the Books record that relates this Books record to the new Authors record. The values of these keys are specified as actions of the subpage's OnPageLoad event, as described below
OnPageLoad actions (Editing Page)
The actions of the OnPageLoad event do the following:
•Ensure that the \$EditBook page source has the expected structure of the Authors parent, including with the Books child element. This is done with the mt-get-page-source-structure XPath extension function (see screenshot below).
•Find out the greatest integer value from among the author IDs. Update three nodes with this value incremented by 1: (i) \$PERSISTENT/Root/EditAuthorID; (ii) the node corresponding to the primary key of the Authors table (Row/@Author_ID); (iii) the node corresponding to the foreign key of the Books table (Row/Books/Row/@Author_ID).
•Find out the greatest integer value from among the book IDs. Update two nodes with this value incremented by 1: (i) \$PERSISTENT/Root/EditBookID; (ii) the node corresponding to the primary key of the Books table (Row/Books/Row/@Book_ID).
|The Author_ID and Book_ID nodes are the primary keys of the Authors and Books tables, respectively. Each ID must therefore be unique, and this is the reason a new ID is made 1 larger than the greatest of the existing IDs. However, this is not an ideal approach in some situations, and an alternative approach is indicated in the next section below, Auto-incrementing Primary Keys.
Note the following points:
•The first IF expression checks whether the ID nodes in the \$PERSISTENT tree are both empty. We have set them to be empty when the user chooses to add a new record (see Add New Book Actions above).
•The second IF expression checks whether any record has been imported from the DB. Since there will be none, the page source structure is appended.
•The first of the two DB Execute actions (highlighted blue) queries the DB for the greatest integer value from among the author IDs and saves the value as an attribute named @pk in the \$MT_DBExecute_Result dynamic variable. (This variable is an XML tree; it can be serialized with the XPath function serialize if you want to see its structure)
•The first set of Update Node actions is used to update the author ID nodes in the \$PERSISTENT tree and the \$EditBook page source.
•The second of the two DB Execute actions is similar to the first. It queries the DB for the greatest integer value from among the book IDs and saves the value as an attribute named @pk in the \$MT_DBExecute_Result dynamic variable.
•The second set of Update Node actions is used to update the book ID nodes in the \$PERSISTENT tree and the \$EditBook page source.
The record now has its important ID fields filled with the relevant unique IDs, and it is now displayed for data entry in the Editing Page subpage. The data that is entered is saved in the \$EditBook page source. On saving the page source, the new data is added as an Authors record and a Books record to the DB, related to each other by the Books record's foreign key. The saving and canceling actions are the same as those used when editing a record.
Auto-incrementing Primary Keys
In our example above, we have calculated the value of a new record's primary key in the following way: We find the greatest integer among all the IDs of the respective table, add 1 to this value, and assign the resulting value as the ID of the new record.
However, this approach would not be ideal if, while one user is adding a new record and has not yet saved it, a second user starts to add a new record. In this case, both new records would have the same ID, the uniqueness criterion would not be fulfilled, and, consequently, one of the records would be rejected by the DB.
An alternative approach would be to define the primary key fields of the DB to auto-increment when a new record is added to the DB. There would then be no need to enter a value for the primary key field, let alone a unique value. This is because a unique value will be entered automatically by the DB when the record is entered.
You can set a DB field to auto-increment in one of the following ways:
•In the DB, define the field to be auto-incrementing. When a new record is saved from the solution to the DB, the primary key field will be automatically incremented with a unique value.
•In the Page Sources Pane of your design, right-click the page source field you want to auto-increment and select DB Field | Is Auto Increment. When the record is saved to the DB, the field will be auto-incremented.