We continue our case study about the imaginary firm NewHorizons, see Example: New Horizons and New Horizons Requirements. In section Example: New Horizons you started development by creating the file
franks/Frank2Manual/configurations/NewHorizons/Configuration.xml. You will extend this configuration by adding files to your directory
The Frank!Framework can take care of database initialization. Database initialization should happen when an enterprise application starts for the first time. When an enterprise application is restarted later, database initialization should be omitted because data in the database should be persistent.
The Frank!Framework internally uses Liquibase, see http://www.liquibase.org/, to initialize the database. Please switch on Liquibase as follows:
StageSpecifics_LOC.propertiesand give it the following contents:
You can use many different property files to configure properties. When you choose
StageSpecifics_LOC.properties, your settings will not be applied when your configuration is deployed on production. If you would choose
DeploymentSpecifics.properties, you settings would also be used in production, provided they are not overruled. More details are in section Properties, but before reading that section please finish this chapter first.
We illustrate database initialization here for H2 databases, because this database is embedded within the Frank!Framework and does not require a process external to it. More information about databases is available in section The Database. Liquibase expects a so-called changelog, an XML file that defines the data model and the initial data.
Please create file
DatabaseChangelog.xmland add XML to initialize the database described in the previous section New Horizons Requirements. Here is the XML to add:
<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd"> <changeSet id="1" author="martijn"> <sql> CREATE TABLE booking ( id INT PRIMARY KEY NOT NULL, travelerId INT NOT NULL, price DECIMAL NOT NULL, fee DECIMAL NOT NULL ) </sql> </changeSet> <changeSet id="2" author="martijn"> <sql> CREATE TABLE visit ( bookingId INT NOT NULL, seq INT NOT NULL, hostId INT NOT NULL, productId INT NOT NULL, startDate date NOT NULL, endDate date NOT NULL, price DECIMAL NOT NULL, PRIMARY KEY (bookingId, seq) ); ALTER TABLE visit ADD FOREIGN KEY (bookingId) REFERENCES booking(id) </sql> </changeSet> </databaseChangeLog>
For clarity we chose to use SQL statements in the changelog. As a consequence, it is not database independent as would be the case if it were pure XML. The shown changelog is specific for H2 databases.
Test your database¶
You can test your work by querying the tables you created, “booking” and “visit”. Please continue as follows:
Click “JDBC” (number 1 in the figure below). This link will expand.
Click “Execute Query” (number 2). The following screen appears:
You see you are in the JDBC Execute Query screen (number 1). Select “Datasource” “jdbc/frank2manual” (number 2).
For more information, see section The Database.
You can choose to have comma-separated (csv) output instead of XML (number 3).
SELECT * FROM booking(number 4).
Press “Send” (number 5). You will see the result
"ID","TRAVELERID","PRICE","FEE"(number 6). You have verified that the “booking” table exists.
Verify that table “visit” exists by executing the query
SELECT * FROM visit. Check that the result of this query is
Please do not modify existing change sets. When you have new requirements for initial data, please add new change sets. On start-up, the Frank!Framework checks which change sets have been executed and which change sets are new. Only new change sets are executed. This only works when existing change sets never change.
If you are developing on the changelog within your own project, you will probably make some errors. In this situation, you want to remove all database tables to rerun all change sets within your changelog. You can do this using the query
DROP ALL OBJECTS. After running it, restart the Frank!Framework.