DB Table Actions: Scenarios
Altova website: 
MapForce Video Demos
This chapter discusses some of the possible scenarios of using database table actions. All the scenarios use a hierarchical database called BookCatalog.sqlite. This database has three tables: Authors (parent), Books (child), TrackingInfo (not connected to any other tables). The Authors and Books tables have a foreign-key relationship. It is important to note that there is autoincrement set for the primary keys in the Authors and Books tables. The structure of the database is described in the script below:
CREATE TABLE
"main"."Authors" (
"AuthorID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"Author" TEXT,
"Country" TEXT,
"Website" TEXT
);
CREATE TABLE
"main"."Books" (
"BookID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"Title" TEXT,
"AuthorID" INTEGER,
"ISBN" TEXT,
"Publisher" TEXT,
"NumPages" INTEGER,
"Year" INTEGER,
"Genre" TEXT,
"Price" DECIMAL,
FOREIGN KEY ("AuthorID") REFERENCES "Authors" ("AuthorID") ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE
"main"."TrackingInfo" (
"MappedOn" DATETIME
);
Scenario 1: Delete all DB data and insert all source data
In the first scenario, we want to delete all the data from the BookCatalog.sqlite database and populate the database with all the data from the source file. Our mapping looks as follows:
Even though the database has three tables, only Authors and Books are included in the database component. Since nothing is mapped to the TrackingInfo table, this table is absent from the component. Since autoincrement is set for the Authors and Books tables, we do not need to connect anything to the AuthorID and BookID columns: these IDs will be generated automatically by the database.
DB table actions for Authors
The table actions for the Authors table (screenshot below) have been configured in the following way:
•In the SQL statement to execute before first record section, we have set up the DELETE action that will delete all the records from the database, including all the child records.
•In the Actions to execute for each record section, we have set up the Insert All action.
•Authors' IDs will be generated automatically by the database (the DB-generated option in the Insert All action).
•The other values will be mapped from the source file.
DB table actions for Books
For the Books table, we have set up the same Insert All action (screenshot below). Book IDs will be generated by the database. The AuthorID column references the primary key in the Authors table. The values for this column will be supplied automatically. All the other values will be mapped from the source file.
Output
The code listing below shows an extract of the output:
DELETE FROM "Books"
DELETE FROM "Authors"
INSERT INTO "Authors" ("Author", "Country", "Website") VALUES ('Stephen King', 'US', 'www.stephenking.com')
SELECT "AuthorID" FROM "Authors" WHERE "AuthorID" = last_insert_rowid()
-- >>> %AuthorID1%
INSERT INTO "Books" ("AuthorID", "Title", "ISBN", "Publisher", "NumPages", "Year", "Genre", "Price") VALUES ('%AuthorID1%', 'Misery', '1501143107', 'Scribner', 368, 2016, 'Horror', 11.99)
INSERT INTO "Books" ("AuthorID", "Title", "ISBN", "Publisher", "NumPages", "Year", "Genre", "Price") VALUES ('%AuthorID1%', 'Outsider', '1501180983', 'Scribner', 576, 2018, 'Horror', 12.79)
Note that the SQL statements in the output are for information purposes only. To execute the SQL statements, open the Output pane and run the Run SQL/NoSQL-Script toolbar command. For more information, see SQL Statements in the Output.
Scenario 2: Update authors and books, insert rest, insert tracking info
In real-life situations, the database is constantly changing, and several people may be working on the same database. Therefore, deleting all the database records may not be desirable. In the second scenario, we have the following goals:
•To update authors and their books that exist both in the source file and the database
•To insert source records that do not exist in the database
•To supply tracking information about the date and time of the mapping
Our mapping has the same components and connections as in the first scenario. However, the database table actions have been configured differently (see below).
DB table actions for Authors
The screenshot below shows the actions defined for the Authors table. In the Update If condition, we have set up the equal value on the Author column. This means that if there are authors with the same names in the source and in the database, only these authors' records will be updated in the database. Authors that exist only in the source file will simply be inserted into the database. Authors that exist only in the database will remain unchanged.
DB table actions for Books
We have the same combination of actions for the Books table (see below). This time, the equal value is set on the Title column. This means that if there are the same books in the source and the database, these book records will be updated in the database. Books that exist only in the source will be inserted into the database. Books that exist only in the database will remain unchanged.
No duplicate records
The major advantage of the Update If condition is that it prevents us from getting duplicate records in the database.
Output
The code listing below shows an extract of the output:
UPDATE "Authors" SET "Country" = 'US', "Website" = 'www.stephenking.com' WHERE ("Authors"."Author" = 'Stephen King')
SELECT "AuthorID" FROM "Authors" WHERE ("Author" = 'Stephen King')
-- >>> %AuthorID1%
UPDATE "Books" SET "ISBN" = '1501143107', "Publisher" = 'Scribner', "NumPages" = 368, "Year" = 2016, "Genre" = 'Horror', "Price" = 11.99 WHERE ("Books"."AuthorID" = '%AuthorID1%') AND ("Books"."Title" = 'Misery')
UPDATE "Books" SET "ISBN" = '1501180983', "Publisher" = 'Scribner', "NumPages" = 576, "Year" = 2018, "Genre" = 'Horror', "Price" = 12.79 WHERE ("Books"."AuthorID" = '%AuthorID1%') AND ("Books"."Title" = 'Outsider')
Alternative solution
To avoid duplicate records in the Books table, you can also choose to delete child records (see below) and set the Insert All condition in the Books table. The actions for the Authors table remain unchanged. In this setup, all the Book records whose authors exist in the source and the database will be deleted from the database. With the Insert All condition set in the Books table, the following types of source Book records will be inserted:
•Book records whose authors exist in the source and the database
•Book records of new authors that exist only in the source
If there is a source Book record without a parent, this Book record will also be mapped to the database, and its parent record will be created in the Authors table. The new Author record will receive a new ID (primary key), and all the other fields will receive NULL values. This is possible only if all the fields in the Authors table, except for the primary key, are nullable. If the fields are not nullable, you will get an error message saying that the NOT NULL constraint has failed.
Importantly, the results of the main scenario and the alternative solution may diverge. For example, if an author in the database has five book records, and if the same author has only three records in the source, all the five database records will be deleted and will be replaced with three records from the source.
Insert tracking info
When several people are working on the same database, it might be a good idea to know when the mapping was last executed. There could be different possibilities: e.g., you can use various datetime functions; you can also supply a custom SQL statement in the Database Table Actions dialog. In our example, we will add the following SQL statement to the Actions to execute for each record section (Authors table):
INSERT INTO TrackingInfo VALUES (DATETIME())
When you run the SQL script, this SQL statement will be executed first, before any statements for database records.
Some other possible scenarios
Instead of the Update If condition, you can also set the Delete If action. In this case, the Author and Book records that exist in the source and the database will be deleted, and new records will be inserted into the database (Insert Rest action). You can also choose to ignore the same records (Ignore If condition) and insert new records into the database (Insert Rest action).