Schema Scripts

Matrix42 SchemaViewer

SchemaViewer is a multi-purpose toolkit for such administrative tasks as schema and data consistency check, generating schema extensions by scripting and many other features:

 For more detail see Matrix42 Marketplace: SchemaViewer.

Manually

Some script types cannot be generated by the system and require manual coding, for instance, .bin scripts.

Manually created scripts are used in rare and complex cases when the automatically generated scripts do not cover the necessary data changes in the system or it is necessary to update or delete the system data, that cannot be changed via the application's user interface. Most often used cases like standard structured data update or schema modification is covered by the Record Customization functionality, which is available in the Administration application.

Command line: data import/export

1. Run schemaEL file from the application’s root directory in the command line, for example:
   /Matrix42 Workplace Management/bin/SchemaEL.exe

2.  Use available commands for data import/export.

User interface: data export

Schema scripts that create data (.dat) and delete data (.del) can be automatically generated from any application that has data objects listed in the grid. Data can be downloaded by users who have access to "Export" action.

Export action allows generating the following types of schema scripts:

a. XML structured file has .dat extension. Generated schema script allows creating data based on the exported items.

b. Delete statements generates .del schema scripts. Generated script exports information about the data objects that will be removed from the database when the script is run, whilst the data itself remains in the application intact.
 

Generating schema scripts: data objects export

Automatically generated schema scripts

The easiest, quickest, and less error-prone way to generate schema scripts is to do it automatically. The system allows to do so with default-available Record Customization functionality or with additionally installed Configuration Packaging extension that provides a rich user interface for the schema scripts recording and managing.

Follow these steps to automatically generate and export schema scripts via Record Customization:

1. Start Recording: in Administration application → Home page → click Start Recording:
   
2. Introduce changes via user interface: create, delete, or modify data or data structure as necessary. The system tracks all data changes and generates schema scripts of a specific type according to the data changes.
3. Export generated Schema Scripts: in Administration application → Home page → click Download distribution package:
   
    

   Split files by object checkbox defines how the data related scripts are generated. These are .post, .dat, and .del scripts automatically generated as a result of corresponding data operations (create, update, and delete data objects). Schema-related changes are always generated as stand-alone schema scripts for each schema element and each specific data operation.

    The Split files by object option affects automatic scripts generation for each type of data operation as follows:

   • by default, Split files by object checkbox is disabled. As a result, when creating several data objects during the running Record Customization the data changes are listed in a single .post script.    
   • enabled Split files by object checkbox option allows exporting all data object changes as stand-alone scripts. For instance, when adding several Navigation Items the .post script is generated for each added to the system data object. 
4. Stop Recording: download the recorded changes and click Stop Recording. You will not be able to download recorded changes afterward.
5. Manually upload scripts to the necessary application of the /Config/ directory and apply all necessary changes to the list of running scripts according to their usage and purpose (either intended for application installation or update, scripts are extending the standard Matrix42 platform or were created as a customization package of a basic platform according to a particular organization’s needs and requirements. For more detail see Schema Scripts integration section).

Record Customization generates schema scripts of appropriate type with corresponding data according to the performed in the user interface actions. Script versions are assigned automatically, version numbers are incremented automatically as well.

Basic overview

Each Schema Script is a file that has a type, a file version, a standardized format, and is designed to operate with particular data of the application's Data Layer. Schema Scripts are run in a specific order defined by the script version and type:

The table below covers the basic principles of the schema scripts, with a few reservations:

1. Scripts running sequence is applied when the scripts have identical versions.
2. Type column lists the most frequently used script types. The type is distinguished by the file extension. Other script types that are not mentioned should be considered as obsolete.
3. Script generation is applicable for those data objects and application schema which are not a part of system data and are generated by the Record Customization functionality.

Particularities

Schema Script files have the following characteristics:

• Version identifiers:
  • Major: the value of the major version should always be Major=”2”. Do not modify it manually for the newly added files;
  • Minor: minor version;
  • Build: the version of the build that can be manually modified if the automatically assigned version may lead to the data collision;
• Type: file/script type. The type defines which kind of data will be applied to the database and which type of data operation is run (create, update, delete);
• Name: unique name within the current version. The name of the file does not affect the order of the run scripts. The file name describes the gist of the file data and performed data operation;
• FilePath: indicates where the actual file is located.

Assigning version

• Automatically: Record Customization automatically generates the appropriate version for the script;
• Manually:  for the manually created script search for the latest version of the modified application's .XML file and increase the build version by 1 and assign it to the created script.

Data Definitions management

    <ClassType>4</ClassType>

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
    <BasicSchemaObjectClass>
        <ID>f0309578-b286-e911-f280-00505690f39d</ID>
        <Name>Ud_custom</Name>
        <IsSchemaObject>0</IsSchemaObject>
        <DisplayName>Custom Data Definition</DisplayName>
        <ProtectionLevel>4</ProtectionLevel>
        <ClassType>3</ClassType>
        <DisableHistory>1</DisableHistory>
    </BasicSchemaObjectClass>
</NewDataSet>

Data Definition scripts structure

    <ClassType>3</ClassType>

    <DisableHistory>1</DisableHistory>

    <ProtectionLevel>2</ProtectionLevel>

    <Description>test</Description>

    <DisplayName>test</DisplayName>

 <BasicSchemaObjectClass-CI>
      <LCID>7</LCID>
      <Description>Basis-Datendefinition für Konnektoren.</Description>
      <DisplayName>Konnektor</DisplayName>
 </BasicSchemaObjectClass-CI>

<Schema-MetaInfo>
      <Value>Name</Value>
      <Name>DisplayExpression</Name>
</Schema-MetaInfo>

  <BasicSchemaAttributeClass>
      <ID>612cce70-41f3-47de-aa97-4ddf90f92585</ID>
      <Name>StringAttribute1</Name>

<AllowNull>1</AllowNull>
<ValueType>0</ValueType>      

      <Scale>4</Scale>
      <Precision>8</Precision>      

<NewDataSet>
    <BasicSchemaObjectClass>
        <ID>c6664bf2-276a-4969-9883-1fec5a4a6735</ID>
        <Name>SPSAccountClassBase</Name>
        <ActionOnImport>update</ActionOnImport>    
       <BasicSchemaAttributeClass>
          <ID>6c28ee06-7e87-e911-fb80-005056908684</ID>
          <Name>Ud_dismissalDate</Name>
          <IsSchemaObject>0</IsSchemaObject>
          <DisplayName>Dismissal Date</DisplayName>
          <DisableHistory>1</DisableHistory>
          <ProtectionLevel>4</ProtectionLevel>
          <DataProtectionIgnored>0</DataProtectionIgnored>
          <AllowNull>1</AllowNull>
          <ValueType>0</ValueType>
          <AttributeLength>100</AttributeLength>
          <ActionOnImport>insert</ActionOnImport>
        </BasicSchemaAttributeClass>
    </BasicSchemaObjectClass>    
</NewDataSet>

<?xml version="1.0" encoding="UTF-8"?>
<NewDataSet>    
   <BasicSchemaObjectClass>          
      <!--Mandatory information to define the item to update:-->          
      <ID>317b058a-e612-483c-afa8-5909cb795dd4</ID>          
      <Name>BaseTest</Name>           
      <!-- What to do with the item on the root level; defines action on the level of the Data Definition:-->          
      <ActionOnImport>update</ActionOnImport>                
      <!--Simple update of optional information:-->         
      <BasicSchemaObjectClass-CI>              
         <LCID>1033</LCID>             
         <Description>Second</Description>               
         <!--What to do with the data of an item; mandatory element which defines action on the level of a Data Definition attribute:-->               
         <ActionOnImport>update</ActionOnImport>  
      </BasicSchemaObjectClass-CI                
      <!-- Deletion of information: -->          
      <BasicSchemaObjectClass-CI>              
         <LCID>1031</LCID>              
         <Description>First</Description>              
         <!--Mandatory element which defines the action on the sub-element level:-->              
         <ActionOnImport>delete</ActionOnImport>           
      </BasicSchemaObjectClass-CI>        
      <BasicSchemaAttributeClass>            
         <!-- Mandatory information to define the sub-item to update:-->         
         <Name>TestAttribute</Name>              
         <ID>afa48b2f-1029-43e2-a726-f1de2d82052f</ID>                
          <!--What to do with the sub-item-->            
         <ActionOnImport>update</ActionOnImport>         
         <!--Exact definition of the update action for the mandatory elements describing the attribute of the Data Definition
             The list of available ActionOnUpdate values is provided below-->        
         <ActionOnUpdate>changeAllowNull</ActionOnUpdate>           
         <AllowNull>1</AllowNull>          
      </BasicSchemaAttributeClass>      
   </BasicSchemaObjectClass>
</NewDataSet>

<NewDataSet>
    <BasicSchemaObjectClass>
        <ID>c6664bf2-276a-4969-9883-1fec5a4a6735</ID>
        <Name>SPSAccountClassBase</Name>
        <ActionOnImport>update</ActionOnImport> 
 
        <BasicSchemaAttributeClass>
            <ID>6c28ee06-7e87-e911-fb80-005056908684</ID>
            <Name>Ud_dismissalDate</Name>
            <ActionOnImport>delete</ActionOnImport>
        </BasicSchemaAttributeClass>
    </BasicSchemaObjectClass>    
</NewDataSet>    

<NewDataSet>
    <BasicSchemaObjectClass>
        <ID>c6664bf2-276a-4969-9883-1fec5a4a6735</ID>
        <Name>SPSAccountClassBase</Name>
        <ActionOnImport>delte</ActionOnImport>
    </BasicSchemaObjectClass>    
</NewDataSet>

      <ActionOnUpdate>rename</ActionOnUpdate>
      <NewName>Description</NewName>

      <ActionOnUpdate>resize</ActionOnUpdate>
      <NewAttributeLength>200</NewAttributeLength>

     <ActionOnUpdate>changeAllowNull</ActionOnUpdate>
     <AllowNull>1</AllowNull>

     <ActionOnUpdate>resizeDecimal</ActionOnUpdate>
     <NewAttributeScale>4</NewAttributeScale>
     <NewAttributePrecision>12</NewAttributePrecision>

      <ActionOnUpdate>changeToCurrency</ActionOnUpdate>

      <ActionOnUpdate>changeToText</ActionOnUpdate>

     <ActionOnUpdate>languageDependent</ActionOnUpdate>
     <LanguageDependent>1</LanguageDependent>

Relation

Defines and creates relations of the Data Definition attributes.

Type

Defines and creates the Configuration Item.

Configuration Item contains Data Definitions, so the .type script should be run after the .class, containing the description of the attributes:

<NewDataSet>
    <BasicSchemaObjectType>
        <ID>e8582113-d486-e911-f280-00505690f39d</ID>
        <Name>Ud_</Name>
        <IsSchemaObject>0</IsSchemaObject>
        <Schema-MetaInfo>
            <Name>ArchivingEnabled</Name>
            <Value>False</Value>
        </Schema-MetaInfo>
        <Schema-MetaInfo>
            <Name>AddActionPosition</Name>
            <Value>0</Value>
        </Schema-MetaInfo>
        <Schema-MetaInfo>
            <Name>UseUserCurrency</Name>
            <Value>False</Value>
        </Schema-MetaInfo>
        <Schema-MetaInfo>
            <Name>StateGroup</Name>
            <Value>0</Value>
        </Schema-MetaInfo>
        <Schema-MetaInfo>
            <Name>UUXColor</Name>
            <Value>#a30b35</Value>
        </Schema-MetaInfo>
        <Schema-MetaInfo>
            <Name>DisplayExpression</Name>
            <Value>name</Value>
        </Schema-MetaInfo>
        <DisplayName>New CI</DisplayName>
        <ProtectionLevel>4</ProtectionLevel>
        <BasedOnTypeClass>184e1215-b1fe-4bcd-9fc6-70035a33a4f3</BasedOnTypeClass>
        <BasicSchemaObjectTypeToClass>
            <RelatedSchemaObjectClass>856166c0-1e29-e311-3782-001f81000830</RelatedSchemaObjectClass>
            <Cardinality>0</Cardinality>
            <ProtectionLevel>0</ProtectionLevel>
        </BasicSchemaObjectTypeToClass>
    </BasicSchemaObjectType>
</NewDataSet>

Configuration items are represented in the database by related tables that have a common foreign key. They have a name, ID, and contain a collection of data definitions composing the CI. The DDs that are part of the composition have a defined cardinality in the CI; this cardinality is part of the CI definition. The ID and name are always mandatory if you want to create or change a CI.

<NewDataSet>
    <BasicSchemaObjectType>
        <ID>9226486b-d7f0-4fd0-b667-da14a2a11487</ID>
        <Name>TestType</Name>

  <ProtectionLevel>2</ProtectionLevel>

    <Description>test</Description>
    <DisplayName>test</DisplayName>    

  <BasicSchemaObjectType-CI>
      <LCID>7</LCID>
      <Description>CI für Konnektoren.</Description>
      <DisplayName>Konnektor</DisplayName>
   </BasicSchemaObjectType-CI>

<Schema-MetaInfo>
      <Value>Name</Value>
      <Name>DisplayExpression</Name>
 </Schema-MetaInfo>

<BasicSchemaObjectTypeToClass>
      <RelatedSchemaObjectClass>9226486b-d7f0-4fd0-b667-da14a2a11487</RelatedSchemaObjectClass>
      <Cardinality>3</Cardinality>
</BasicSchemaObjectTypeToClass>

Dat

Data added to the Data Definition. The file creates new records in the Data Definition class.

Is executed after the .class file, the Data Definition where it should be stored in.

The structural elements include the table name, column names and the created data placed inside the corresponding elements (e.g. ID of the created record and all necessary field values that should be added to the database under this ID).

Mandatory elements of the .dat file for new records of the database:

• Data Definition name: name of the table where the record will be inserted;
• Record ID: unique identifier of the record in the database table.

ID of the record must be unique and an insert of a new record with same ID but other data for the Data Definition fields is ignored:

1. CreateData.dat script creates a record with a specific ID in the database.
2. Further CreateNewData.dat script attempts to create a new record in the same class with the same ID but with the new data.
3. Ignore data insert: new data for existing record ID is ignored, the application install/update process does not result in an error and ignored data can be traced back in the application setup log files.

.dat script example for creating a new user role:

<NewDataSet>
  <SPSSecurityTypeRole>
    <ID>320a6d83-6f05-ce76-1f0b-08d6e8f6ab05</ID>
  </SPSSecurityTypeRole>
  <SPSSecurityClassRole>
    <ID>4b52c4d5-d286-e911-f280-00505690f39d</ID>
    <Name>HR</Name>
    <UsedInTypeSPSSecurityTypeRole>320a6d83-6f05-ce76-1f0b-08d6e8f6ab05</UsedInTypeSPSSecurityTypeRole>
    <MembershipMaintenance>0</MembershipMaintenance>
    <ShowInForwardAction>0</ShowInForwardAction>
    <MailContentType>1</MailContentType>
  </SPSSecurityClassRole>
  <SPSCommonClassBase>
    <ID>4c52c4d5-d286-e911-f280-00505690f39d</ID>
    <UsedInType>320a6d83-6f05-ce76-1f0b-08d6e8f6ab05</UsedInType>
    <TypeID>f64d3d1d-e2ab-4a43-a453-52caff9b0ccd</TypeID>
    <CostCenter>816361e3-13e6-4b44-96a9-fb44c63a8e36</CostCenter>
    <Location>cf060d4d-5c47-462f-b5d1-7df6c865fd91</Location>
    <OU>4df735f2-b4bc-4ce8-92ee-60f40fcf5653</OU>
    <Security-OU>4df735f2-b4bc-4ce8-92ee-60f40fcf5653</Security-OU>
    <Security-Location>cf060d4d-5c47-462f-b5d1-7df6c865fd91</Security-Location>
    <Security-CostCenter>816361e3-13e6-4b44-96a9-fb44c63a8e36</Security-CostCenter>
    <Category>3</Category>
  </SPSCommonClassBase>
  <SPSScRoleClassBase>
    <ID>4d52c4d5-d286-e911-f280-00505690f39d</ID>
    <UsedInTypeSPSSecurityTypeRole>320a6d83-6f05-ce76-1f0b-08d6e8f6ab05</UsedInTypeSPSSecurityTypeRole>
  </SPSScRoleClassBase>
</NewDataSet>

Del

Allows deleting data objects from the database.

• Automatically created scripts are generated from the Record Customization functionality, Configuration Packaging extension, or can be exported.
• Manually created script can delete the system data that cannot be removed via user interface due to the system restrictions, for instance, deleting a default user role.

Mandatory elements of the .del file:

• ObjectId ID: ID of the record that will be removed by the script;
• TypeID: ID of a Configuration Item the record is a part of:

<?xml version="1.0" encoding="utf-8"?>
<DeleteObjectRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Items>
        <DeleteObjectRequestItem>
            <ObjectId>e91a214f-0386-e911-f280-00505690f39d</ObjectId>
            <TypeId>0566eada-859e-4e17-afbf-c8a98cfe0d27</TypeId>
        </DeleteObjectRequestItem>
    </Items>
</DeleteObjectRequest>

Bin

When the required System updates cannot be implemented with the standard schema types, the System allows executing the compiled code run by .bin scripts. This approach has no limitations and can implement absolutely any change of the System that does not align with the cases covered by the basic schema scripts.

The program class which defines the Update script has to implement the interface  Matrix42.Schema.Installer.UpdateScript.

The Example of the Binary script definition:

<NativeUpdateRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Assembly>Matrix42.Maintenance.BinaryScripts.dll</Assembly>
    <Type>Matrix42.Maintenance.BinaryScripts.ADDomainMigrationScript</Type>
</NativeUpdateRequest>

Post

Plain SQL storing the changes applied to the existing data created by .dat schema script type.

.post scripts store SQL data update statements:

--DataUpdateAccounts
UPDATE [PDRNavigationItemClassBase]
SET     [Title] = 'User Accounts'
WHERE ID = '9f4bca46-d308-e711-e79b-bc5ff41a70c0'

Basic principles

XML attributes

<Script Major="02" Minor="81" Build="0028" Type="dat" Name="ServiceManagementFilters" 
FilePath="Pandora\02-81-0028 ServiceManagementFilters.dat" DisableConstraints="true" />

<Script Major="02" Minor="81" Build="0028" Type="dat" Name="ServiceManagementStructures" 
FilePath="Pandora\02-81-0028 ServiceManagementStructures.dat" />

<Script Major="02" Minor="81" Build="0028" Type="post" Name="UpdateServiceManagementStructuresAndFilters"
 FilePath="Pandora\02-81-0028 UpdateServiceManagementStructuresAndFilters.post" />

<?xml version="1.0" encoding="utf-8"?>
<SchemaScripts>
    <Script Major="2" Minor="1" Build="1" Type="class" Name="Fleet Managment_Ud_Car_create"
     Mode="Merge" FilePath="install\02-01-0001 Ud_Car_create.class" />
    <Script Major="2" Minor="1" Build="1" Type="relation" Name="Fleet Managment_FK_SPSUserClassBaseUd_Car_Ud_CarsOwner_create"
     Mode="Merge" FilePath="install\02-01-0001 FK_SPSUserClassBaseUd_Car_Ud_CarsOwner_create.relation" />
    <Script Major="2" Minor="1" Build="2" Type="dat" Name="Fleet Managment_SPSSecurityTypeRole" 
     Mode="Merge" FilePath="install\02-01-0002 SPSSecurityTypeRole.dat" />
</SchemaScripts>

<?xml version="1.0" encoding="utf-8"?>
<SchemaScripts>
    <Script Major="2" Minor="1" Build="1" Type="class" Name="Fleet Managment_Ud_Car_create"
     Mode="Merge" Register="false" FilePath="install\02-01-0001 Ud_Car_create.class" />
</SchemaScripts>

<?xml version="1.0" encoding="utf-8"?>
<SchemaScripts>
    <Script Major="2" Minor="1" Build="2147483644" Type="del" 
    Name="test_PDRDataQueryType_uninstall" PackageVersion="1.0" Mode="Merge" Register="false" 
    FilePath="uninstall\02-01-2147483644 PDRDataQueryType_uninstall.del" />
    <Script Major="2" Minor="1" Build="2147483642" Type="del" 
    Name="test_PDRContentWidgetTypeObjectDetails_test Preview_uninstall" PackageVersion="2.1" Register="false" 
    FilePath="uninstall\02-01-2147483642 PDRContentWidgetTypeObjectDetails_test Preview_uninstall.del" />
</SchemaScripts>

Install process

Before running the scripts the system installation process includes collecting and sorting all schema scripts from the /Config/ directory by their version and type:

1. Run basic scripts from /ReferenceDataBaseScripts/ : db_data.xml and db_schema.sql create basic DB tables and fills them with data (are not considered as the part of the schema scripts, but use the same approach);
2. Custom.xml: includes reference to the basic schema scripts for adding application schema and fill out the schema with data, according to the contents of the script. All scripts from all reference files are combined in one block, sorted by version and type, and executed in the sorted sequence.  
3. Run the rest of the scripts from the /Config/ Directory.
4. Install-Only.xml: run scripts containing demo data that is installed once and is not a part of a system data, so that it can be deleted later on, for example, demo services, default demo catalog, etc.
5. Keep track of the executed scripts in the SchemaScript database table: store version identifiers, type, file name and path, for example:

Update process

During the system update process all schema scripts are collected and sorted according to the version and type from the following sources:

1.  /Config/Update/Updates.xml;
2.  Add files from Custom.xml.  This file also includes the reference to the customized schema script packages;
3. Scripts from /Config/Dev/ (if present): take into account schema scripts created additionally for application development purpose;
4. Run those scripts that were not executed previously (during the application setup or the latest update) and store them to SchemaScript database table.

The update process is the same for all scripts that were created as a result of standard Matrix42 platform development or customization of the standard functionality.

Install Package

The System allows installing schema scripts partially (outside of the Setup process). The tool <AppFolder>/bin/update.exe installs the scripts referenced by the specified XML:

update -schema "..\Config\Migration.xml" "..\Config"