Initial Data

The SQL expanders can expand initial setup data scripts, based on an XML based format which is mapped to the correct SQL syntax.

Setup

This initial data is defined as one or more files per application, in a file located in the folder [sourceBase]/applications/[applicationName]/init-data/.

Example

Application	Initial data file
database-test	[sourceBase]/applications/database-test/init-data/001-app-init-data.xml
rocketManager	[sourceBase]/applications/rocketManager/init-data/V1.1__app_init_data.xml

File structure

The XML file has a root element dataInstances, which contains an ordered collection of dataInstance elements and dataInstanceGroups. The name of the application data file is an attribute on the root element and will be used as the name of the expanded script.

<?xml version="1.0" encoding="UTF-8"?>
<dataInstances
    xmlns="https://schemas.normalizedsystems.org/xsd/sql-expanders/5/0/0/dataInstances"
    name="">
  <dataInstance dataElement="">
    ...
  </dataInstance>
  <dataInstanceGroup dataElement="">
    <dataInstance>
      ...
    </dataInstance>
  </dataInstanceGroup>
</dataInstances>

caution

Support for multiple files was added in version 5.0.0. Prior to that version, the name of the data file was fixed to the name of the application. For example [sourceBase]/applications/rocketManager/init-data/rocketManager-data.xml.

Data instances

Each data instance represents an instance of a specific DataElement. It includes a reference to the element, as well as a list of fields with the data required to initialize them. Any field that is not listed is set to NULL.

There are in total three different types of field definitions, where for each of them the name of the field in the DataElement is the name of the XML tag in the data instance:

Type	Description
Value	A value definition is marked by the attribute type="value" or has no type attribute. The value that has to be inserted into the database is the text value of the XML tag. The value will be parsed by the expanders and mapped to the correct syntax for the type of the matching ValueField.
Scripted	Similar to a value definition but marked with the attribute type="script". These definitions also apply to ValueFields. The name of a function used to generate a value for the field in the database is the text value of the XML tag. The expanders will map this function definition to the appropriate database function or generated value.
Linked	The linked definition is used for link fields and contains a set of child elements that have the name of a field in the target element and a value it has to match to. All of these constraints will be matched to a target record as a conjunction.

caution

Unlike a field that is undefined, a value definition tag that is empty (<value/>), will not be mapped to NULL. Rather, it will be inserted as an empty string.

Script field functions

Function	Description
Date::now	This function will be translated to a timestamp with or without a time included for the types DateLong and Date respectively. To generate the timestamp, the expanders will map it to an appropriate database function, which will generate the transaction timestamp on insertion. For most SQL databases, this is current_timestamp and current_date respectively.
UUID::new	This function will generate a random UUID in the script for String type fields.

Example

<?xml version="1.0" encoding="UTF-8"?>
<dataInstances
    xmlns="https://schemas.normalizedsystems.org/xsd/sql-expanders/5/0/0/dataInstances"
    name="001-app-init-data">
  <!-- account.Account -->
  <dataInstance dataElement="account::Account">
    <address>Galileilaan 15</address>
    <city>Niel</city>
    <country>BE</country>
    <email>i.love.ns@nsx.normalizedsystems.org</email>
    <fullName>Normalized Systems</fullName>
    <name>NSX</name>
    <phone>03/123.45.67</phone>
    <refId>1</refId>
    <status>Activated</status>
    <style>nsxbootstrap</style>
    <zipCode>2845</zipCode>
  </dataInstance>

  <!-- account.Profile -->
  <dataInstance dataElement="account::Account">
    <name>admin</name>
    <weight>1</weight>
  </dataInstance>

  <!-- account.User -->
  <dataInstance dataElement="account::Account">
    <account type="link">
      <name>NSX</name>
    </account>
    <disabled>no</disabled>
    <email>admin@nsx.normalizedsystems.org</email>
    <enteredAt type="script">Date::now</enteredAt>
    <firstName/>
    <fullName>Beheerder</fullName>
    <language>dutch</language>
    <lastModifiedAt type="script">Date::now</lastModifiedAt>
    <lastName/>
    <mobile/>
    <name>admin</name>
    <password>myFavoritePassword</password>
    <persNr/>
    <profile type="link">
      <name>admin</name>
    </profile>
    <timeout>3600</timeout>
  </dataInstance>
</dataInstances>

Data instance groups

Data instance groups are a mechanism to group together multiple data instances. These serve mostly a technical purpose of reducing the size of the xml file, as it requires the reference dataElement to be defined only once.