Updater (English)
Aus TimeAndWork
TimeAndWork uses the SQL Server Database - XML Updater to automatically synchronise the datastructure at a customer intallation to the needed structure for new programm versions.
The updater application is delivered together with an XML file which defines the needed database structure. The application compares the existing database structure with the structure as defined in the XML file and automatically adapts the database structure as needed.
Inhaltsverzeichnis |
[bearbeiten] Structure
The XML definition file consists of the following parts:
- Tables
- Functions
- Views
- Procedures
- Updates
[bearbeiten] Tables
The table definitions contain the table name, the column names, the default values, the primary and foreign keys and the indexes used.
During the update procedure all default values, keys and indexes are removed, then the structure is adapted. Before and after the structural changes the Updates-Command are executed.
Within Column the individual columns of the tables table are defined. Here one can set the name of the column, the datatype and it is defined if the value can contain null or not (Null ="yes|no"). It is also defined if the column is an identity column or not (Identity="yes|no", IdentityStart, IdentityStep).
Sample
<Column Name="id" Datatype="int" Null="no" Identity="yes" IdentityStart="1" IdentityStep="1" />
Within Defaults the default values for columsn can be defined. Each default value must be defined with a unique name and must be bound to a single column in a table.
Sample
<Default Name="DF_person_vat" Column="vat" Value="((0))" />
Primarykeys are defined with a name and the corresponding Column within the PrimaryKeys nodes.
Sample
<Primarykey Name="Person_Id" Column="id" />
Foreignkeys define the foreign key relationship between tables. They are defined by name, source column and destination table and column. Only simple foreign keys can be defined (one column pointing to one single column).
Sample
<Foreignkey Name="Person_Organization_Id" Source="organization_id" Table="organization" Column="id" />
Indices defines the indexes used in the tables. A simple name and the name of the column that needs to be indexed is sufficient.
Sample
<Index Name="Person_Organization_Id" Column="organization_id" />
[bearbeiten] Functions
During updating all functions are removed and are created as defined in the XML.
Sample
<Function Name="ComputeTime"> <![CDATA[CREATE FUNCTION [dbo].[ComputeTime] ( @time_string FLOAT ) ... ]]> </Function>
[bearbeiten] Views
All views are removed and are created as defined in the XML. When views depend on other views, the need to be ordered in the XML file in the order as they are referenced.
Sample
<View Name="TEST_VIEW"> <![CDATA[CREATE VIEW [dbo].TEST_VIEW as ... ]]> </View>
[bearbeiten] Procedures
All procedures are removed and recreated during the updating procedure. When one procedure depends on another one, the order needs to be respected in the XML file.
Sample
<Procedure Name="TEST_Procedure"> <![CDATA[CREATE PROCEDURE [dbo].[BILLREPORT_Diartis_Budget](@project_bill_id int) ... ]]> </Procedure>
[bearbeiten] Updates
In the Updates Section, SQL commands can be defined which are execute before and after the structure is adapted. The commands need to be defined so that multiple execution doesn't result in unwanted results.
Sample
<Update Name="dropprojectparentid">
<![CDATA[
IF ((Select Coalesce(Col_length('project','parent_id'),0))>0)
ALTER TABLE project DROP COLUMN parent_id
]]>
</Update>