1<?php 2include_once('../adodb.inc.php'); 3include_once('adodb-replicate.inc.php'); 4 5set_time_limit(0); 6 7function IndexFilter($dtable, $idxname,$flds,$options) 8{ 9 if (strlen($idxname) > 28) $idxname = substr($idxname,0,24).rand(1000,9999); 10 return $idxname; 11} 12 13function SelFilter($table, &$arr, $delfirst) 14{ 15 return true; 16} 17 18function updatefilter($table, $fld, $val) 19{ 20 return "nvl($fld, $val)"; 21} 22 23 24function FieldFilter(&$fld,$mode) 25{ 26 $uf = strtoupper($fld); 27 switch($uf) { 28 case 'SIZEFLD': 29 return 'Size'; 30 31 case 'GROUPFLD': 32 return 'Group'; 33 34 case 'GROUP': 35 if ($mode == 'SELECT') $fld = '"Group"'; 36 return 'GroupFld'; 37 case 'SIZE': 38 if ($mode == 'SELECT') $fld = '"Size"'; 39 return 'SizeFld'; 40 } 41 return $fld; 42} 43 44function ParseTable(&$table, &$pkey) 45{ 46 $table = trim($table); 47 if (strlen($table) == 0) return false; 48 if (strpos($table, '#') !== false) { 49 $at = strpos($table, '#'); 50 $table = trim(substr($table,0,$at)); 51 if (strlen($table) == 0) return false; 52 } 53 54 $tabarr = explode(',',$table); 55 if (sizeof($tabarr) == 1) { 56 $table = $tabarr[0]; 57 $pkey = ''; 58 echo "No primary key for $table **** **** <br>"; 59 } else { 60 $table = trim($tabarr[0]); 61 $pkey = trim($tabarr[1]); 62 if (strpos($pkey,' ') !== false) echo "Bad PKEY for $table $pkey<br>"; 63 } 64 65 return true; 66} 67 68global $TARR; 69 70function TableStats($rep, $table, $pkey) 71{ 72global $TARR; 73 74 if (empty($TARR)) $TARR = array(); 75 $cnt = $rep->connSrc->GetOne("select count(*) from $table"); 76 if (isset($TARR[$table])) echo "<h1>Table $table repeated twice</h1>"; 77 $TARR[$table] = $cnt; 78 79 if ($pkey) { 80 $ok = $rep->connSrc->SelectLimit("select $pkey from $table",1); 81 if (!$ok) echo "<h1>$table: $pkey does not exist</h1>"; 82 } else 83 echo "<h1>$table: no primary key</h1>"; 84} 85 86function CreateTable($rep, $table) 87{ 88## CREATE TABLE 89 #$DB2->Execute("drop table $table"); 90 91 $rep->execute = true; 92 $ok = $rep->CopyTableStruct($table); 93 if ($ok) echo "Table Created<br>\n"; 94 else { 95 echo "<hr>Error: Cannot Create Table<hr>\n"; 96 } 97 flush();@ob_flush(); 98} 99 100function CopyData($rep, $table, $pkey) 101{ 102 $dtable = $table; 103 104 $rep->execute = true; 105 $rep->deleteFirst = true; 106 107 $secs = time(); 108 $rows = $rep->ReplicateData($table,$dtable,array($pkey)); 109 $secs = time() - $secs; 110 if (!$rows || !$rows[0] || !$rows[1] || $rows[1] != $rows[2]+$rows[3]) { 111 echo "<hr>Error: "; var_dump($rows); echo " (secs=$secs) <hr>\n"; 112 } else 113 echo date('H:i:s'),': ',$rows[1]," record(s) copied, ",$rows[2]," inserted, ",$rows[3]," updated (secs=$secs)<br>\n"; 114 flush();@ob_flush(); 115} 116 117function MergeDataJohnTest($rep, $table, $pkey) 118{ 119 $rep->SwapDBs(); 120 121 $dtable = $table; 122 $rep->oracleSequence = 'LGBSEQUENCE'; 123 124# $rep->MergeSrcSetup($table, array($pkey),'UpdatedOn','CopiedFlag'); 125 if (strpos($rep->connDest->databaseType,'mssql') !== false) { # oracle ==> mssql 126 $ignoreflds = array($pkey); 127 $ignoreflds[] = 'MSSQL_ID'; 128 $set = 'MSSQL_ID=nvl($INSERT_ID,MSSQL_ID)'; 129 $pkeyarr = array(array($pkey),false,array('MSSQL_ID'));# array('MSSQL_ID', 'ORA_ID')); 130 } else { # mssql ==> oracle 131 $ignoreflds = array($pkey); 132 $ignoreflds[] = 'ORA_ID'; 133 $set = ''; 134 #$set = 'ORA_ID=isnull($INSERT_ID,ORA_ID)'; 135 $pkeyarr = array(array($pkey),array('MSSQL_ID')); 136 } 137 $rep->execute = true; 138 #$rep->updateFirst = false; 139 $ok = $rep->Merge($table, $dtable, $pkeyarr, $ignoreflds, $set, 'UpdatedOn','CopiedFlag',array('Y','N','P','='), 'CopyDate'); 140 var_dump($ok); 141 142 #$rep->connSrc->Execute("update JohnTest set name='Apple' where id=4"); 143} 144 145$DB = ADONewConnection('odbtp'); 146#$ok = $DB->Connect('localhost','root','','northwind'); 147$ok = $DB->Connect('192.168.0.1','DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=natsoft;DATABASE=OIR;','',''); 148$DB->_bindInputArray = false; 149 150$DB2 = ADONewConnection('oci8'); 151$ok2 = $DB2->Connect('192.168.0.2','tnb','natsoft','RAPTOR',''); 152 153if (!$ok || !$ok2) die("Failed connection DB=$ok DB2=$ok2<br>"); 154 155$tables = 156" 157JohnTest,id 158"; 159 160# net* are ERMS, need last updated field from LGBnet 161# tblRep* are tables insert or update from Juris, need last updated field also 162# The rest are lookup tables, can copy all from LGBnet 163 164$tablesOrig = 165" 166SysVoltSubLevel,id 167# Lookup table for Restoration Details screen 168sysefi,ID # (not identity) 169sysgenkva,ID #(not identity) 170sysrestoredby,ID #(not identity) 171# Sel* table added on 24 Oct 172SELSGManufacturer,ID 173SelABCCondSizeLV,ID 174SelABCCondSizeMV,ID 175SelArchingHornSize,ID 176SelBallastSize,ID 177SelBallastType,ID 178SelBatteryType,ID #(not identity) 179SelBreakerCapacity,ID 180SelBreakerType,ID #(not identity) 181SelCBreakerManuf,ID 182SelCTRatio,ID #(not identity) 183SelCableBrand,ID 184SelCableSize,ID 185SelCableSizeLV,ID # (not identity) 186SelCapacitorSize,ID 187SelCapacitorType,ID 188SelColourCode,ID 189SelCombineSealingChamberSize,ID 190SelConductorBrand,ID 191SelConductorSize4,ID 192SelConductorSizeLV,ID 193SelConductorSizeMV,ID 194SelContactorSize,ID 195SelContractor,ID 196SelCoverType,ID 197SelCraddleSize,ID 198SelDeadEndClampBrand,ID 199SelDeadEndClampSize,ID 200SelDevTermination,ID 201SelFPManuf,ID 202SelFPillarRating,ID 203SelFalseTrue,ID 204SelFuseManuf,ID 205SelFuseType,ID 206SelIPCBrand,ID 207SelIPCSize,ID 208SelIgnitorSize,ID 209SelIgnitorType,ID 210SelInsulatorBrand,ID 211SelJoint,ID 212SelJointBrand,ID 213SelJunctionBoxBrand,ID 214SelLVBoardBrand,ID 215SelLVBoardSize,ID 216SelLVOHManuf,ID 217SelLVVoltage,ID 218SelLightningArresterBrand,ID 219SelLightningShieldwireSize,ID 220SelLineTapSize,ID 221SelLocation,ID 222SelMVVoltage,ID 223SelMidSpanConnectorsSize,ID 224SelMidSpanJointSize,ID 225SelNERManuf,ID 226SelNERType,ID 227SelNLinkSize,ID 228SelPVCCondSizeLV,ID 229SelPoleBrand,ID 230SelPoleConcreteSize,ID 231SelPoleSize,ID 232SelPoleSpunConcreteSize,ID 233SelPoleSteelSize,ID 234SelPoleType,ID 235SelPoleWoodSize,ID 236SelPorcelainFuseSize,ID 237SelRatedFaultCurrentBreaker,ID 238SelRatedVoltageSG,ID #(not identity) 239SelRelayType,ID # (not identity) 240SelResistanceValue,ID 241SelSGEquipmentType,ID # (not identity) 242SelSGInsulationType,ID # (not identity) 243SelSGManufacturer,ID 244SelStayInsulatorSize,ID 245SelSuspensionClampBrand,ID 246SelSuspensionClampSize,ID 247SelTSwitchType,ID 248SelTowerType,ID 249SelTransformerCapacity,ID 250SelTransformerManuf,ID 251SelTransformerType,ID #(not identity) 252SelTypeOfArchingHorn,ID 253SelTypeOfCable,ID #(not identity) 254SelTypeOfConductor,ID # (not identity) 255SelTypeOfInsulationCB,ID # (not identity) 256SelTypeOfMidSpanJoint,ID 257SelTypeOfSTJoint,ID 258SelTypeSTCable,ID 259SelUGVoltage,ID # (not identity) 260SelVoltageInOut,ID 261SelWireSize,ID 262SelWireType,ID 263SelWonpieceBrand,ID 264# 265# Net* tables added on 24 Oct 266NetArchingHorn,Idx 267NetBatteryBank,Idx # identity, FunctLocation Pri 268NetBiMetal,Idx 269NetBoxFuse,Idx 270NetCable,Idx # identity, FunctLocation Pri 271NetCapacitorBank,Idx # identity, FunctLocation Pri 272NetCircuitBreaker,Idx # identity, FunctLocation Pri 273NetCombineSealingChamber,Idx 274NetCommunication,Idx 275NetCompInfras,Idx 276NetControl,Idx 277NetCraddle,Idx 278NetDeadEndClamp,Idx 279NetEarthing,Idx 280NetFaultIndicator,Idx 281NetFeederPillar,Idx # identity, FunctLocation Pri 282NetGenCable,Idx # identity , FunctLocation Not Null 283NetGenerator,Idx 284NetGrid,Idx 285NetHVOverhead,Idx #identity, FunctLocation Pri 286NetHVUnderground,Idx #identity, FunctLocation Pri 287NetIPC,Idx 288NetInductorBank,Idx 289NetInsulator,Idx 290NetJoint,Idx 291NetJunctionBox,Idx 292NetLVDB,Idx #identity, FunctLocation Pri 293NetLVOverhead,Idx 294NetLVUnderground,Idx # identity, FunctLocation Not Null 295NetLightningArrester,Idx 296NetLineTap,Idx 297NetMidSpanConnectors,Idx 298NetMidSpanJoint,Idx 299NetNER,Idx # identity , FunctLocation Pri 300NetOilPump,Idx 301NetOtherComponent,Idx 302NetPole,Idx 303NetRMU,Idx # identity, FunctLocation Pri 304NetStreetLight,Idx 305NetStrucSupp,Idx 306NetSuspensionClamp,Idx 307NetSwitchGear,Idx # identity, FunctLocation Pri 308NetTermination,Idx 309NetTransition,Idx 310NetWonpiece,Idx 311# 312# comment1 313SelMVFuseType,ID 314selFuseSize,ID 315netRelay,Idx # identity, FunctLocation Pri 316SysListVolt,ID 317sysVoltLevel,ID_SVL 318sysRestoration,ID_SRE 319sysRepairMethod,ID_SRM # (not identity) 320 321sysInterruptionType,ID_SIN 322netTransformer,Idx # identity, FunctLocation Pri 323# 324# 325sysComponent,ID_SC 326sysCodecibs #-- no idea, UpdatedOn(the only column is unique),Ermscode,Cibscode is unique but got null value 327sysCodeno,id 328sysProtection,ID_SP 329sysEquipment,ID_SEQ 330sysAddress #-- no idea, ID_SAD(might be auto gen No) 331sysWeather,ID_SW 332sysEnvironment,ID_SE 333sysPhase,ID_SPH 334sysFailureCause,ID_SFC 335sysFailureMode,ID_SFM 336SysSchOutageMode,ID_SSM 337SysOutageType,ID_SOT 338SysInstallation,ID_SI 339SysInstallationCat,ID_SIC 340SysInstallationType,ID_SIT 341SysFaultCategory,ID_SF #(not identity) 342SysResponsible,ID_SR 343SysProtectionOperation,ID_SPO #(not identity) 344netCodename,CodeNo #(not identity) 345netSubstation,Idx #identity, FunctLocation Pri 346netLvFeeder,Idx # identity, FunctLocation Pri 347# 348# 349tblReport,ReportNo 350tblRepRestoration,ID_RR 351tblRepResdetail,ID_RRD 352tblRepFailureMode,ID_RFM 353tblRepFailureCause,ID_RFC 354tblRepRepairMethod,ReportNo # (not identity) 355tblInterruptionType,ID_TIN 356tblProtType,ID_PT #--capital letter 357tblRepProtection,ID_RP 358tblRepComponent,ID_RC 359tblRepWeather,ID_RW 360tblRepEnvironment,ID_RE 361tblRepSubstation,ID_RSS 362tblInstallationType,ID_TIT 363tblInstallationCat,ID_TIC 364tblFailureCause,ID_TFC 365tblFailureMode,ID_TFM 366tblProtection,ID_TP 367tblComponent,ID_TC 368tblProtdetail,Id # (Id)--capital letter for I 369tblInstallation,ID_TI 370# 371"; 372 373 374$tables = explode("\n",$tables); 375 376$rep = new ADODB_Replicate($DB,$DB2); 377$rep->fieldFilter = 'FieldFilter'; 378$rep->selFilter = 'SELFILTER'; 379$rep->indexFilter = 'IndexFilter'; 380 381if (1) { 382 $rep->debug = 1; 383 $DB->debug=1; 384 $DB2->debug=1; 385} 386 387# $rep->SwapDBs(); 388 389$cnt = sizeof($tables); 390foreach($tables as $k => $table) { 391 $pkey = ''; 392 if (!ParseTable($table, $pkey)) continue; 393 394 ####################### 395 396 $kcnt = $k+1; 397 echo "<h1>($kcnt/$cnt) $table -- $pkey</h1>\n"; 398 flush();@ob_flush(); 399 400 CreateTable($rep,$table); 401 402 403 # COPY DATA 404 405 406 TableStats($rep, $table, $pkey); 407 408 if ($table == 'JohnTest') MergeDataJohnTest($rep, $table, $pkey); 409 else CopyData($rep, $table, $pkey); 410 411} 412 413 414if (!empty($TARR)) { 415 ksort($TARR); 416 adodb_pr($TARR); 417 asort($TARR); 418 adodb_pr($TARR); 419} 420 421echo "<hr>",date('H:i:s'),": Done</hr>"; 422