Prüfen und Korrigieren des SOG ERP (VACOS) Datenbankmodells
Nach Änderung des Datenbankmodells muss die Datenbank auf dem Datenbankserver entsprechend aktualisiert werden.
Dazu kann direkt das Kommando SOGERPConsole verwendet werden.
Es gibt aber auch vorbereitete SQL-Kommandos, die für die beiden Aktualisierungsarten aufgerufen werden sollten, wenn bezüglich der Verwendung der Optionen Unsicherheit besteht:
sogsql sql\zcview2.sql
Aktualisiert Veränderungen der Tabellenstrukturen über die komplette Datenbank inklusive der Zugriffsrechte auf Tabellen und Views.
Dabei werden unbekannte Tabellen, Views oder Indices vorerst nur aufgezeigt und noch nicht entfernt, damit für diese Elemente eine Klärung zur Integration in das Datenbankmodell erfolgen kann. Elemente, die langfristig als berechtigter Bestandteil der Datenbank erhalten bleiben sollen, müssen also unbedingt in das Datenbankmodell integriert werden.
sogsql sql\zcview3.sql
Aktualisiert die Zugriffsrechte der Benutzer auf Tabellen und Views.
Veränderung des Datenbankmodells
Mithilfe von Konfigurationsdateien kann das Zieldatenbankmodell Ihren individuellen Anforderungen angepasst werden. Dabei wurde insbesondere darauf Rücksicht genommen, die verwendeten Datenbank-Indices zu optimieren.
Die Konfigurationsdateien werden über die Umgebungsvariable "FRMCFGPATH" gesucht, und haben folgende Namen:
Database_base.xml
Database_proj.xml
Database_vorort.xml
Database_${HHENVDOMAIN}_${hhproj}.xml
Database_${HHENVDOMAIN}_${hhproj}_${hhfirm}.xml
Database_test.xml
Die Konfigurationen werden nacheinander verarbeitet. Spätere Definitionen haben Vorrang vor früheren.
Database_base.xml wird dabei verwendet, um eine Optimierungsbasis aus der SOG-Entwicklung festzulegen.
Database_proj.xml kann im Hause SOG für die Festlegung von projektindividuellen Änderungen des Datenbankmodells gegenüber der SOG ERP (VACOS)-Standard-Datenbank genutzt werden.
Database_vorort.xml können Sie verwenden, um das Datenbankmodell Ihren persönlichen Anforderungen anzupassen, und gewonnene Erkenntnisse aus einer Datenbank-Performance-Analyse umzusetzen.
Database_test kann nur in der Testumgebung der SOG Entwicklung verwendet werden.
Aufbau der Konfigurationsdateien
Die Konfigurationsdateien haben folgenden Aufbau:
<Config>
<Database IgnoreDDIndexes="false" Collation="Latin1_General_cs_as" SSASCollation="Latin1_General_ci_ai" >
</Database>
</Config>
Bedeutung der einzelnen Parameter:
Wird dieser Schalter auf "true" gesetzt, werden alle "einfachen" Indices, die über das "SOG Data Dictionary" auf Einzelfelder festgelegt wurden, verworfen.
Veränderung einzelner Tabellen
<Config>
<Database>
<Table Name="f010" DumpIndex="true" EnableIndexNames="true">
<Field Name="hhidentity" Enabled="true" DbType="DbBigInt" Identity="true" />
<Index Name="idx_f010_hhlock" Enabled="false" />
<Index Name="idx_f010__primary" Enabled="true" Fields="hhidentity" />
<Index Name="idx_f010_key_1" Enabled="true" Fields="key_1" Uniq="true"/>
<Index Name="idx_f010_indi1" Enabled="true" Fields="firma,sa" Includes="name1,name2,konto" Uniq="false"/>
<Index Name="idx_f010_konto" Enabled="false" />
<Index Name="idx_f010_nur_fuer_kunden" Enabled="true" Fields="vnr,v_konto" Where="sa = 1" />
</Table>
Bedeutung der einzelnen Parameter:
Alle erzeugten Indices werden in der Ausgabe aufgelistet. Auch wenn diese nicht verändert werden müssen.
Für die Tabelle wird eingeschaltet, dass benannte Indices verwendet werden sollen.
Durch diesen Schalter wird eine neue, einheitliche Namensvergabe für alle verwendeten Indices aktiviert.
Dadurch werden bei erster Einführung der Systematik alle Indices einmal neu erzeugt. Nachfolgend können alle Indices über ihren Namen eindeutig identifiziert, ein- und ausgeschaltet und modifiziert werden.
Bitte Beachten Sie: Bei Verwendung dieser Option wird die veraltete Systematik zur Festlegung von Datenbankindices über die Dateien "sql\zcindex.sql", "sql\zcindex_local.sql" und "sql\zcindex_vorort.sql" deaktiviert. Dort festgelegte Indices sollten nach Prüfung ihrer Notwendigkeit in die neue Systematik überführt werden.
Mit einer Fieldanweisung können weitere Datenfelder angelegt werden, die in die Datenbank aufgenommen werden.
In diesem Beispiel wird das Datenfeld "hhidentity" erzeugt, das später für die Festlegung des Primärschlüssels verwendet wird.
Setzen Sie "Enabled" auf "true" damit ein Feld oder Index erzeugt wird. Mit "Enabled" = "false" können Sie einzelne Felder oder Indices deaktivieren.
Festlegung des Datenbanktyp des Datenfeldes.
Folgende Datenbanktypen sind unterstützt: DbChar, DbVarChar, DbInt, DbBigInt, DbSmallInt, DbDecimal, DbDate, DbBlob, DbFloat
true. Das Datenfeld bekommt ein "identity" Attribut. Dies sorgt dafür, dass bei jedem Insert eine eindeutige, aufsteigende Nummer in diesem Datenfeld gespeichert wird.
true: In Diesem Datenfeld sind "null" Werte erlaubt. Achtung: Sollte für SOG ERP (VACOS) Tabellen nicht verwendet werden!
Länge des Datenwertes.
Anzahl Nachkommastellen (Nur bei DbDecimal)
Initialisierungswert.
Möglichkeit zur Festlegung der Sortierreihenfolge einer einzelnen Datenspalte. Mögliche Angabe z.B: Latin1_General_ci_ai (Nicht Case-Sensitiv) oder Latin1_General_cs_as (Case Sensitiv). Wird die "Collation" auf Feldebene angegeben, muss auch die Collation der Datenbank gesetzt werden. Die Collation auf Datenbankebene stellt einen Initialwert für alle Datenfelder aller Tabellen der Datenbank dar. Da eine gemeinsame Einstellung für MSSMaxChar und MSSMaxCharUnicode gefunden werden muss, können nur Collations verwendet werden, die in dieser Beziehung kompatibel sind. Verwenden Sie z.B. "Latin1_General_ci_ai" und "Latin1_General_cs_as" und stellen Sie dann "MssMaxChar" auf 142 sowie "MssMaxCharUnicode" auf "381".
Verwenden Sie den SQL sql\sog\ShowMaxChar.sql um die möglichen Werte für MssMaxChar und MssMaxCharUnicode für die aktuellen Einstellungen des Datenfeldes "txt1" der Tabelle "dbcheck" anzuzeigen. "txt1" von "dbcheck" sollte immer die restriktivste, genutzte Collation aufweisen. (Also normalerweise: Latin1_General_cs_as).
Sortierreihenfolge für CUBE Tabellen (Funktion XmlInfoToDbJob)
Konfiguration eines Indexs. Beziehen Sie sich auf den Namen eines bekannte Index um diesen zu verändern, oder legen Sie einen neuen Index-Namen fest, um einen neuen Index anzulegen.
true. Es handelt sich um den Primär-Index (clustered uniq)
true: Der Index soll nur eindeutige Werte zulassen.
true: Der Index soll als Gruppierter Index (Clustered Index) angelegt werden. Damit werden die eigentlichen Daten der Tabelle in die gewünschte Reihenfolge gebracht, es wird als kein zusätzlicher Speicher für den Index benötigt. Pro Tabelle kann es nur einen Clusterd Index geben. Im Allgemeinen ist dies der Primary Index. Die Option kann also nur für Tabellen verwendet werden, die keinen Primary Index haben.
Kommagetrennte Liste von Feldnamen, die in den Index einbezogen werden sollen.
Kommagetrennte Liste von weiteren Feldnamen, die als "include" in den Index aufgenommen werden. (Covered Index)
Angabe einer optionalen Where-Bedingung um die Menge an Daten, die indiziert werden soll, einzuschränken. Dies bweirkt aber, dass der Index nur dann genutzt wird, wenn die Where-Bedingung des Sql zu der des Indexes passt.
Tabellenoption. True: Eine für einzelne Datenfelder angegebene "Collation" wird auch dann durchgesetzt, wenn das Datenbankattribut "Collation" nicht eingestellt wurde. Diese Funktion wird für Tabellen verwendet, in denen SOG ERP (VACOS) zwingend mit Case-Sensitivität arbeiten muss. (z.B. automatische Übersetzungen)
true: Die Trigger der Tabelle werden so erzeugt, das bei Einfügen eines Datensatzes in die Tabelle, der entsprechende Datensatz aus der f006 gelöscht wird.
true: Keine Änderung des Datentyp, selbst wenn die Datenbank auf Unicode umgeschaltet wird. false (Initialwert): Bei Umschaltung der Datenbank auf Unicode werden "char" Felder zu "nchar" und "varchar" zu "nvarchar".
Kann für eine Tabelle angegeben werden, um den Füllfaktor aller Index der Tabelle festzulegen. Kann für einen Index angegeben werden, um den Füllfaktor eines einzelnen Indexes festzulegen.
Für die Tabelle werden keine automatischen Trigger erzeugt.
Keine Warnung, dass die Tabelle keinen PrimaryKey besitzt.
<Config>
<Database >
<Table Name="dbrelease" EnableIndexNames="true">
<Field Name="key_1" DbType="DbChar" Len="22" />
<Field Name="relnr" DbType="DbInt" />
<Field Name="label" DbType="DbChar" Len="30" />
<Field Name="cmdnr" DbType="DbInt" />
<Field Name="delwatch" DbType="DbSmallInt" />
<Index Name="idx_dbrelease__primary" Fields="key_1" Primary="true" Uniq="true"/>
</Table>
Über die "Table" Struktur können vollständige Tabellen in der SOG ERP (VACOS)-Datenbank festgelegt werden.
Beachten Sie, dass dies nur in Ausnahmefällen verwendet werden sollte.
Normalerweise sollten SOG ERP (VACOS)-Datenbanken nicht mit fremden Tabellen belegt werden.
Eine erlaubte Erweiterungen der SOG ERP (VACOS)-Datenstrukturen stellen Meta-Datentabellen dar. Diese werden über die SOG ERP (VACOS)-Funktion p500 - Metatabellenpflege (metasu) definiert.
<View Name="f531" ReadOnly="true">
<SqlText>
create view ${HHDBO}.f531 (status, key_1, firma, sa, art, lg, lgp, verfb, dispo_b, e_best, schw_ges, resv_b1, resv_b2, am_rw_k,
zm_rw_k) as select status, key_1, firma, sa, art, lg, lgp, (b_menge+zm_lg_m+zm_lg_f-resv_b1-resv_b2-gesperrt),
(b_menge+zm_lg_m+zm_lg_f-resv_b1-resv_b2-gesperrt+best_b), e_best, (anf_menge_s+zm_s_m+zm_s_f), resv_b1, resv_b2,
am_rw_k, zm_rw_k
from f031
</SqlText>
<Field Name="lgp" DbType="DbChar" Len="10" />
<Field Name="verfb" DbType="DbDecimal" Len="17" nk="4" />
<Field Name="dispo_b" DbType="DbDecimal" Len="18" nk="4" />
<Field Name="schw_ges" DbType="DbDecimal" Len="14" nk="4" />
</View>
In diesem Beispiel wurde die Datenbank-View "f531" festgelegt.
Bei der Definition von Views muss der SQLText zum Erstellen der Views festgelegt werden.
Gibt es zu der View keine SOG ERP (VACOS)-Definition (DataDictionairy) oder weicht die Datenbank-View von der im DD festgelegten Struktur ab, so können über "Field" Anweisungen die SOG ERP (VACOS)-Definitionen korrigiert werden.
Festlegung von views mit Feldmapping über das SOG ERP (VACOS)-DD
<View Name="gewvol" ddmap="true">
<Field Name="key_1" DbType="DbChar" Len="84" />
</View>
Hier wird die Datenbank-View "gewvol" definiert. Dabei wird mit der Option "ddmap" angegeben, dass die Datenfelder über das DataDictionairy "gewvol" gemappt werden. Da der "key_1" nicht mit der DD-Definition übereinstimmt, muss dieser hier manuell angepasst werden.
Festlegung von views mit Feldmapping einzelner Datenfelder
Um die Definition von views zu vereinfachen, ist auch folgende Syntax möglich:
<View Name="AxibKunden" ReadOnly="true" >
<SqlText>
create view #NAME# (#FIELDNAMES#) as select #FIELDVALUES# from f010 where sa = 1;
</SqlText>
<Field Name="firma" Like="f010.firma" />
<Field Name="Konto" Like="f010.konto" />
<Field Name="Status" Like="f010.status" />
<Field Name="Liefersperre" Like="f010.lf_sp" />
</View>
Im SQLText können die folgenden Platzhalter verwendet werden:
"#NAME#" für den Namen der view (hier: AxibKunden),
"#FIELDNAMES#" für die Liste der über "Field" Anweisungen festgelegten Felder (hier: firma,Konto,Status,Liefersperre) sowie
"#FIELDVALUES#" für die Liste der über "Field" Anweisungen festgelegten Feldinhalte (hier: firma,konto,status,lf_sp).
Wird dabei ein Feldwert nicht aus einem über ein DataDictionairy festgelegten Datenfeld ermittelt, kann an Stelle der "Like" Anweisung auch eine vollständig Datenbankfeld-Formatfestlegung erfolgen (DbType, Len, etc.) und der Quelltext der View-Definition über das Attribut "ViewSource" festgelegt werden. (Beispiel: Berechnete Datenfelder).
Wird die Eigenschaft "UseLikeAsViewSource" auf "true" gesetzt, wird für alle Felder angenommen, dass der Inhalt des Like-Attributes (sofern vorhanden) gleichzeitig den Quelltext für die Definition der View-Eigenschaft festlegt. Dies wird benötigt, wenn die View aus mehreren Tabellen zusammengesetzt wird, und die Datenfelder dabei nicht eindeutig über ihren Namen identifizierbar sind.
Festlegung von Datenfeldern, die immer aus dem aktuellen Projekt stammen sollen
Bei dem Abgleich der SOG ERP (VACOS)-Datenbank mit einer Datenbank-Strukturdatei (z.B. während einer Datenbankaktualisierung), kann festgelegt werden, dass bestimmte Datenfelder in bestimmten Tabellen nicht in dem Format der Datenbankstrukturdatei, sondern in dem über das aktuelle SOG ERP (VACOS)-Projekt festgelegte Format verarbeitet werden sollen.
<UpdateStrcuturFile>
<Table Name="f030" >
<Field Name="mc1" />
<Field Name="mc2" />
<Field Name="mc3" />
</Table>
</UpdateStrcuturFile>
Legen Sie dafür über "UpdateStrcuturFile" fest, welche Datenfelder in welchen Tabellen immer aus dem aktuellen SOG ERP (VACOS)-Projekt festgelegt werden sollen.
<Trigger Name="f090status">
<SqlText>
create trigger ${HHDBO}.f090status
on ${HHDBO}.f090
for UPDATE
AS
begin
SET NOCOUNT ON
...
</SqlText>
</Trigger>
Über einen "Trigger" Eintrag, können Datenbank Trigger festgelegt werden.
<Function Name="vacos_hhupd" >
<SqlText>
create function dbo.vacos_hhupd() returns char(14) as
begin
declare @time as datetime = sysutcdatetime()
declare @hhupd as char(14) = ( select CONVERT(varchar(8), @time, 112)
+ REPLACE( CONVERT(varchar(8), @time, 108), ':', '' ) )
return @hhupd
end;
</SqlText>
</Function>
Über eine "Funktion" Eintrag, können TSQL-Funktionen festgelegt werden.
Mit dem Attribut TableValued="True" müssen Funktionen markiert werden, die Tabellen als Ergebnis zurückliefern.
<Ignore Name="hlpcache" Ignore="true"/>
Mit einem "Ignore" Eintrag wird festgelegt, dass die angegebene Tabelle oder View nicht über das SOG ERP (VACOS)-Datenbankmodell überwacht werden soll.
<Config>
<Database >
<DbUser Name="tst1" WinUser="false" />
<DbUser Name="${USERDOMAIN}\SogErpUser" WinUser="true" />
Über den Konfigurationseintrag "DbUser" können weitere Benutzernamen festgelegt werden, die für die Steuerung von Zugriffsrechten Verwendung finden sollen.
Durch Angabe von "WinUser=true" wird eingestellt, dass es sich bei dem Benutzer um einen Windows-Login (Windows-Benutzer oder Windows-Gruppe) handelt.
Windows-Logins könne für die Zuweisung von Rechten auf Tabellenebene verwendet werden.
Bei der Festlegung von Benutzernamen können auch Umgebungsvariable verwendet werden. z.B. "USERDOMAIN".
Ist der Benutzer dem Datenbankserver noch nicht als Login bekannt, erfolgt automatisch eine entsprechende Zuordnung. Ein Aautomatisches entfernen von einmal zugewiesenen Benutzern erfolgt nicht, auch wenn sich das Datenbankmodell entsprechend ändern sollte.
<DbUser Name="tst1" Roles="db_datareader" />
Über eine "Roles" Angabe können dem Benutzer Datenbankrollen zugeordnet werden. Geben Sie Dabei eine kommaseparierte Liste von Datenbankrollen an, die dem Benutzer zugeordnet werden sollen. Stellt das System fest, dass der angegebene Benutzer noch weiteren Rollen zugeordnet ist, werden ihm diese Zuordnungen automatisch entzogen. Die am häufigsten verwendeten Rollen in diesem Zusammenhang sind: db_datareader, db_datawriter und db_owner.
Festlegen von Benutzerrechten von zusätzlichen Benutzern auf Tabellen- oder Viewebene
<Table Name="f000" EnableIndexNames="true">
<User Names="tst1" Access="ReadOnly" />
<User Names="${USERDOMAIN}\SogErpUser" Access="ReadOnly" />
</Table>
Über den Konfigurationseintrag "User" unterhalb eines "Table" oder "View" Eintrags können zusätzliche Benutzerrechte für Tabellen und Views konfiguriert werden. Über Names kann eine kommagetrennte Liste von Benutzernamen angegeben werden, die Rechte für die Tabelle oder View bekommen sollen. Über "Access" kann die Art des möglichen Zugriffs festgelegt werden.
Mögliche Zugriffsarten:
Nur lesender Zugriff erlaubt.
Lesen und Aktualisieren erlaubt.
Lesen, Aktualisieren, Einfügen und Löschen erlaubt.
Zugriff auf Tabellen in fremden Datenbanken oder auf fremden Datenbankservern
Das SOG ERP (VACOS)-Datenbankmodell bietet eine allgemeine Möglichkeit um auf Tabellen zuzugreifen, die in fremden Datenbanken oder auf fremden Datenbankservern liegen.
Dazu wird ggf. das SQL-Server Feature sp_addlinkedserver verwendet.
Um auf eine externe Tabelle zuzugreifen, muss ggf. unabhängig vom SOG ERP (VACOS) Datenbankmodell ein "Linked Server" eingerichtet werden. Verwenden Sie dazu z.B. folgende SQL Anweisung:
sp_addlinkedserver 'sogdb1', N'SQL Server';
Beachten Sie die SQL-Server Dokumentation um die weiteren Möglichkeiten dieser Funktion zu erfahren.
Um im SOG ERP (VACOS) Datenbankmodell festzulegen, dass eine Tabelle auf einem Remote-System liegt, wird folgende Syntax eingesetzt:
<Config>
<Database>
<Table Name="f010" HandleAsExternalView="[sogdb1].[dbsname].[dbo].f010">
</Table>
</Database>
</Config>
Das Datenbankmodell legt nun an Stelle einer Tabelle eine 1:1 View auf die externe Datenquelle fest.
Anschließend kann dann wie gewohnt über den Tabellennamen auf die externe Datenquelle zugegriffen werden.
Erweitern der automatisch generierten Trigger um eigene Bedingungen
Zur Prüfung der Datenkonsistenz auf Satzebene wird automatisch pro SOG ERP (VACOS)-Tabelle ein Trigger erzeugt der sicherstellt, dass Einzelfelder von Datenbank-Schlüsseln im Satz mit ihren Gruppenfeldern übereinstimmen, dass bei einer Änderung hhupd aktualisiert wird, der die Löschtabelle f006 lädt und der die Veränderungstabelle dbchglog aktualisiert.
Über das Datenbankmodell können für jede Tabelle weitere, einfache Bedingungen formuliert werden, um diese Trigger zu erweitern.
Damit kann z.B. sichergestellt werden, dass der Anfang von key_1 mit dem Anfang von key_2 übereinstimmt, wenn dies für eine Tabelle so definiert ist.
Triggererweiterungen werden wie folgt festgelegt:
<Config>
<Database>
<Table Name="f010" EnableIndexNames="true">
<TriggerCondition Name="key_2" Condition="substring(key_1,1,3) != substring(key_2,1,3)" Message="key_1/key_2 fehlerhaft!" Fields="key_2"/>
</Table>
</Database>
</Config>
Legt den Namen der Triggererweiterung fest. Damit kann in Folgekonfigurationen eine Trigger-Condition überschrieben oder abgeschaltet werden.
true: TriggerCondition ist aktiv
Einfache SQL Bedingung
Meldung, die im Fehlerfall angezeigt werden soll.
Datenfelder, die zusätzlich zu key_1 bei der Daten-Konsistenzprüfung angezeigt werden sollen, wenn Fehler gefunden wurden.
Sind innerhalb der Datenbank kundenindividuelle Datenbankobjekte angelegt, so können diese über eine Ignore-Anweisung "bekannt" gegeben werden. Elemente für die eine Ignore-Anweisung gegeben wurde, werden vom Datenbank-Modell vollkommen unbehandelt gelassen.
<Config>
<Database>
<Ignore Name="IndividuellesObject" />