axStart: table & field ID conflicts

[Blog bot]

Источник: http://axstart.spaces.live.com/Blog/...C0A0!343.entry
==============

<div>
table & field ID conflicts.
Some months ago, I posted an article about SQLDictionary ID conflicts. It was a x++ job that scans and solves at once. Last month I have to do this in a use live environment. The customer preferred detailed information about the ID conflicts and solving it was the next step. The keyword in this process is: Don’t lose customer data on the Database!
Let’s look at next example
Tablename
AOT ID
SQL ID
A
50003
40001
B
40025
40013
C
40026
40025
 
So I created a script that checks:

• Difference in Table ID
• Difference in Field ID
• Difference in String size
  Changed field properties

Check if documents in document management are attached to tables with an ID conflict. (or amy other table).
From the results I created small jobs that solved the ID conflicts on a structural way.

• Solve any difference in the field Properties In your AOT (string size, Created date, etc)
  Solve the Table ID conflicts Process the tables in an order that the change to a non existing ID in the sqldicttable. So we have to solve first table C next table B. We will not fix the Field ID issues yet.

static void repairSQLDictTableConflicts(Args _args)
{
SQLDIctionary dictionary;
void doTable(int aotTable, int sqlTable)
{
ttsbegin;
while select forupdate dictionary where dictionary.tabId == sqlTable
{
 dictionary.tabId = aotTable;
 dictionary.update();
}
ttscommit;
}
;
ttsbegin;
 doTable( tablenum(a), 40001 ) ;
 doTable( tablenum(c), 40031 ) ;
 doTable( tablenum(b), 40025 ) ;
ttscommit;
}
 

• Solve the Field ID conflicts. I used the next job for solving the Field ID conflicts

static void repairSQLDictFieldConflicts(Args _args)
{
  SQLDIctionary dictionary;
  void doField(int aotTtable, int aotField, int sqlField)
  {
    ttsbegin;
    while select forupdate dictionary where dictionary.tabId == aotTtable &&      dictionary.fieldId == sqlField
    {
       dictionary.fieldId = aotField;
      dictionary.update();
    }
  ttscommit;
 }
  ;
  ttsbegin;
  dofield(tablenum(A),fieldnum( A,field),4001);
  dofield(tablenum(B),fieldnum( B,otherfield),4002);
  ttscommit;
}
 
For analyzing the ID conflicts I used this script, It can also be used for checking if the ID conflicts are solved.
static void checkSQLDictionary(Args _args)
{
  SQLDictionary dictionary,dictionaryMissing;
  str tableName;
  int AOTTableID, AOTFieldID;
  TreeNodetreeNode;
  boolean doTable;
  boolean skip;
  sysdictFieldsdf;
  SysDictTablesdt;
  str propertyName;
  int sdtFields,sdtFieldsTotal;
  ;
<font color="#000000"><font face=Arial><font size=1>  while select dictionary order by tabid,fieldId where dictionary.fieldId