Move DB objects to another layer
From Axaptapedia
... without losing data
If you want to transfer DB objects e.g. from USR to CUS layer, the database fields are deleted and recreated, data is lost. To prevent that, we can:
- Export data, move the object and import data
- Export the objects with IDs
- Fake IDs for the target layer
I created jobs to "correct" the IDs:
The first job LayerMove_USRCUS has to be executed before Export of USR-Layer. It fakes the tableIds and fieldIds. The second job AfterLayerMove has to be executed after the Import into the CUS-Layer. It will correct the tableIds again (necessary).
So the sequence is:
- Backup DB and Application
- Job LayerMove_USRCUS
- export USR
- delete USR Layer
- import CUS
- Job AfterLayerMove
- reimport CUS
- synchronize database
static void LayerMove_USRCUS(Args _args) { //if other layers needed, only the macro values need to be changed #define.layerText("USR to CUS") // from - to layer #define.minUSRid(50001) // minimum id of the from-layer #define.maxUSRid(59999) // maximum id of the from-layer #define.minCUSid(40001) // minimum id of the to-layer #define.maxCUSid(49999) // maximum id of the to-layer #define.defCUSid(40001) // default id to start with, mostly = #minCUSid SysLastValue sysLastValue; SQLDictionary sqlTables; SQLDictionary sqlDictionary, sqlDictionary2; TableId tableId, maxCUStableId; int currentId, recentFieldId; str fieldList; boolean yesToAll = false, moveFields; int answer; ; setPrefix("Transfer tables and fields from " + #layerText); //look for the highest table id in the destination layer select firstOnly sqlDictionary order by tabId desc where sqlDictionary.fieldId == 0 && sqlDictionary.tabId >= #minCUSid && sqlDictionary.tabId <= #maxCUSid; maxCUStableId = sqlDictionary.tabId ? sqlDictionary.tabId : #defCUSid - 1; //loop thru all tables while select sqlTables order by name where sqlTables.fieldId == 0 { tableId = sqlTables.tabId; //if the table or some fields are located in the source layer... moveFields = (tableId >= #minUSRid && tableId <= #maxUSRid); if (!moveFields) { select firstOnly sqlDictionary order by fieldId desc where sqlDictionary.tabId == tableId && sqlDictionary.fieldId >= #minUSRid && sqlDictionary.fieldId <= #maxUSRid; if (sqlDictionary) moveFields = true; } if (moveFields) { setPrefix(strFmt("Table %1 (Id %2)", sqlTables.name, sqlTables.tabId)); fieldList = ""; while select sqlDictionary order by fieldId asc where sqlDictionary.TabId == tableId && sqlDictionary.array == 1 && sqlDictionary.fieldId >= #minUSRid && sqlDictionary.fieldId <= #maxUSRid { fieldList += (fieldList ? ", " : "") + sqlDictionary.name; } //ask for permission to change if (!yesToAll) { answer = Box::yesYesAllNoCancel("ok to move" + ((tableId >= #minUSRid && tableId <= #maxUSRid) ? " table " + sqlTables.name : "") + (fieldList ? " fields " + fieldList : "") + " from " + #layerText + "?", DialogButton::No); yesToAll = (answer == DialogButton::YesToAll); if (answer == DialogButton::Cancel) break; } if (yesToAll || answer == DialogButton::Yes) { ttsbegin; //if table is located in the source layer... if (tableId >= #minUSRid && tableId <= #maxUSRid) { // move table to CUS: look if already exist in lower layer select firstOnly forUpdate sqlDictionary where sqlDictionary.tabId == sqlTables.tabId && sqlDictionary.fieldId == 0; select firstOnly sqlDictionary2 order by tabId desc where sqlDictionary2.name == sqlTables.name && sqlDictionary2.tabId != sqlTables.tabId && sqlDictionary2.fieldId == 0; if (sqlDictionary2) { tableId = sqlDictionary2.tabId; sqlDictionary.delete(); //delete also dataAreaId, recId delete_from sqlDictionary where sqlDictionary.TabId == sqlTables.tabId && sqlDictionary.fieldId > 0 && (sqlDictionary.fieldId < #minUSRid || sqlDictionary.fieldId > #maxUSRid); } else { maxCUStableId++; tableId = maxCUStableId; sqlDictionary.tabId = tableId; sqlDictionary.update(); //change dataAreaId, recId and some other fields while select forUpdate sqlDictionary order by fieldId asc, array asc where sqlDictionary.TabId == sqlTables.tabId && sqlDictionary.fieldId > 0 && (sqlDictionary.fieldId < #minUSRid || sqlDictionary.fieldId > #maxUSRid) { sqlDictionary.tabId = tableId; sqlDictionary.update(); } } delete_from sysLastValue where sysLastValue.elementName == strFmt("T:%1", sqlTables.tabId); info(strFmt("table id changed from %1 to %2", sqlTables.tabId, tableId)); } //look for the highest field id of that table in destination layer select firstOnly sqlDictionary order by fieldId desc where sqlDictionary.tabId == sqlTables.tabId && sqlDictionary.fieldId >= #minCUSid && sqlDictionary.fieldId <= #maxCUSid; currentId = sqlDictionary.FieldId ? sqlDictionary.FieldId : #defCUSid - 1; recentFieldId = 0; //search for all fields in the source layer while select forUpdate sqlDictionary order by fieldId asc, array asc where sqlDictionary.TabId == sqlTables.tabId && sqlDictionary.fieldId >= #minUSRid && sqlDictionary.fieldId <= #maxUSRid { select forUpdate sqlDictionary2 order by fieldId desc where sqlDictionary2.TabId == tableId && sqlDictionary2.fieldId != sqlDictionary.fieldId && sqlDictionary2.array == sqlDictionary.array && sqlDictionary2.name == sqlDictionary.name; if (sqlDictionary2) { if (sqlDictionary2.fieldId >= #minCUSid && sqlDictionary2.fieldId <= #maxCUSid) { currentId = sqlDictionary2.fieldId; sqlDictionary2.delete(); } } else { // array fields have the same fieldId if (recentFieldId != sqlDictionary.fieldId) { currentId++; recentFieldId = sqlDictionary.fieldId; } } info(strFmt("fieldName: %1, id changed from %2 to %3", sqlDictionary.name, sqlDictionary.fieldId, currentId)); sqlDictionary.tabId = tableId; sqlDictionary.fieldId = currentId; sqlDictionary.update(); } ttscommit; } } } setPrefix("Transfer"); info("Transfer completed"); }
static void AfterLayerMove(Args _args) { //if other layer needed, only the macro values need to be changed #define.layerText("CUS") // from - to layer #define.minCUSid(40001) // minimum id #define.maxCUSid(49999) // maximum id SQLDictionary sqlTables; SQLDictionary sqlDictionary; Dictionary dictionary = new Dictionary(); TableId tableId; ; setPrefix("Checking tables of layer " + #layerText); //loop thru all tables while select sqlTables where sqlTables.fieldId == 0 && sqlTables.tabId >= #minCUSid && sqlTables.tabId <= #maxCUSid { tableId = dictionary.tableName2Id(sqlTables.name); if (tableId != sqlTables.tabId && tableId && sqlTables.tabId) { ttsbegin; while select forUpdate sqlDictionary where sqlDictionary.tabId == sqlTables.tabid { sqlDictionary.tabId = tableId; sqlDictionary.update(); } info(strFmt("table %1 id changed from %2 to %3", sqlTables.name, sqlTables.tabId, tableId)); ttscommit; } } setPrefix("Transfer"); info("Transfer completed"); }
Any feedback welcome.
Regards, harald (--Hghrp 03:00, 20 March 2006 (PST))
