28) $idxname = substr($idxname,0,24).rand(1000,9999); return $idxname; } function SelFilter($table, &$arr, $delfirst) { return true; } function updatefilter($table, $fld, $val) { return "nvl($fld, $val)"; } function FieldFilter(&$fld,$mode) { $uf = strtoupper($fld); switch($uf) { case 'SIZEFLD': return 'Size'; case 'GROUPFLD': return 'Group'; case 'GROUP': if ($mode == 'SELECT') $fld = '"Group"'; return 'GroupFld'; case 'SIZE': if ($mode == 'SELECT') $fld = '"Size"'; return 'SizeFld'; } return $fld; } function ParseTable(&$table, &$pkey) { $table = trim($table); if (strlen($table) == 0) return false; if (strpos($table, '#') !== false) { $at = strpos($table, '#'); $table = trim(substr($table,0,$at)); if (strlen($table) == 0) return false; } $tabarr = explode(',',$table); if (sizeof($tabarr) == 1) { $table = $tabarr[0]; $pkey = ''; echo "No primary key for $table **** ****
"; } else { $table = trim($tabarr[0]); $pkey = trim($tabarr[1]); if (strpos($pkey,' ') !== false) echo "Bad PKEY for $table $pkey
"; } return true; } global $TARR; function TableStats($rep, $table, $pkey) { global $TARR; if (empty($TARR)) $TARR = array(); $cnt = $rep->connSrc->GetOne("select count(*) from $table"); if (isset($TARR[$table])) echo "

Table $table repeated twice

"; $TARR[$table] = $cnt; if ($pkey) { $ok = $rep->connSrc->SelectLimit("select $pkey from $table",1); if (!$ok) echo "

$table: $pkey does not exist

"; } else echo "

$table: no primary key

"; } function CreateTable($rep, $table) { ## CREATE TABLE #$DB2->Execute("drop table $table"); $rep->execute = true; $ok = $rep->CopyTableStruct($table); if ($ok) echo "Table Created
\n"; else { echo "
Error: Cannot Create Table
\n"; } flush();@ob_flush(); } function CopyData($rep, $table, $pkey) { $dtable = $table; $rep->execute = true; $rep->deleteFirst = true; $secs = time(); $rows = $rep->ReplicateData($table,$dtable,array($pkey)); $secs = time() - $secs; if (!$rows || !$rows[0] || !$rows[1] || $rows[1] != $rows[2]+$rows[3]) { echo "
Error: "; var_dump($rows); echo " (secs=$secs)
\n"; } else echo date('H:i:s'),': ',$rows[1]," record(s) copied, ",$rows[2]," inserted, ",$rows[3]," updated (secs=$secs)
\n"; flush();@ob_flush(); } function MergeDataJohnTest($rep, $table, $pkey) { $rep->SwapDBs(); $dtable = $table; $rep->oracleSequence = 'LGBSEQUENCE'; # $rep->MergeSrcSetup($table, array($pkey),'UpdatedOn','CopiedFlag'); if (strpos($rep->connDest->databaseType,'mssql') !== false) { # oracle ==> mssql $ignoreflds = array($pkey); $ignoreflds[] = 'MSSQL_ID'; $set = 'MSSQL_ID=nvl($INSERT_ID,MSSQL_ID)'; $pkeyarr = array(array($pkey),false,array('MSSQL_ID'));# array('MSSQL_ID', 'ORA_ID')); } else { # mssql ==> oracle $ignoreflds = array($pkey); $ignoreflds[] = 'ORA_ID'; $set = ''; #$set = 'ORA_ID=isnull($INSERT_ID,ORA_ID)'; $pkeyarr = array(array($pkey),array('MSSQL_ID')); } $rep->execute = true; #$rep->updateFirst = false; $ok = $rep->Merge($table, $dtable, $pkeyarr, $ignoreflds, $set, 'UpdatedOn','CopiedFlag',array('Y','N','P','='), 'CopyDate'); var_dump($ok); #$rep->connSrc->Execute("update JohnTest set name='Apple' where id=4"); } $DB = ADONewConnection('odbtp'); #$ok = $DB->Connect('localhost','root','','northwind'); $ok = $DB->Connect('192.168.0.1','DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=natsoft;DATABASE=OIR;','',''); $DB->_bindInputArray = false; $DB2 = ADONewConnection('oci8'); $ok2 = $DB2->Connect('192.168.0.2','tnb','natsoft','RAPTOR',''); if (!$ok || !$ok2) die("Failed connection DB=$ok DB2=$ok2
"); $tables = " JohnTest,id "; # net* are ERMS, need last updated field from LGBnet # tblRep* are tables insert or update from Juris, need last updated field also # The rest are lookup tables, can copy all from LGBnet $tablesOrig = " SysVoltSubLevel,id # Lookup table for Restoration Details screen sysefi,ID # (not identity) sysgenkva,ID #(not identity) sysrestoredby,ID #(not identity) # Sel* table added on 24 Oct SELSGManufacturer,ID SelABCCondSizeLV,ID SelABCCondSizeMV,ID SelArchingHornSize,ID SelBallastSize,ID SelBallastType,ID SelBatteryType,ID #(not identity) SelBreakerCapacity,ID SelBreakerType,ID #(not identity) SelCBreakerManuf,ID SelCTRatio,ID #(not identity) SelCableBrand,ID SelCableSize,ID SelCableSizeLV,ID # (not identity) SelCapacitorSize,ID SelCapacitorType,ID SelColourCode,ID SelCombineSealingChamberSize,ID SelConductorBrand,ID SelConductorSize4,ID SelConductorSizeLV,ID SelConductorSizeMV,ID SelContactorSize,ID SelContractor,ID SelCoverType,ID SelCraddleSize,ID SelDeadEndClampBrand,ID SelDeadEndClampSize,ID SelDevTermination,ID SelFPManuf,ID SelFPillarRating,ID SelFalseTrue,ID SelFuseManuf,ID SelFuseType,ID SelIPCBrand,ID SelIPCSize,ID SelIgnitorSize,ID SelIgnitorType,ID SelInsulatorBrand,ID SelJoint,ID SelJointBrand,ID SelJunctionBoxBrand,ID SelLVBoardBrand,ID SelLVBoardSize,ID SelLVOHManuf,ID SelLVVoltage,ID SelLightningArresterBrand,ID SelLightningShieldwireSize,ID SelLineTapSize,ID SelLocation,ID SelMVVoltage,ID SelMidSpanConnectorsSize,ID SelMidSpanJointSize,ID SelNERManuf,ID SelNERType,ID SelNLinkSize,ID SelPVCCondSizeLV,ID SelPoleBrand,ID SelPoleConcreteSize,ID SelPoleSize,ID SelPoleSpunConcreteSize,ID SelPoleSteelSize,ID SelPoleType,ID SelPoleWoodSize,ID SelPorcelainFuseSize,ID SelRatedFaultCurrentBreaker,ID SelRatedVoltageSG,ID #(not identity) SelRelayType,ID # (not identity) SelResistanceValue,ID SelSGEquipmentType,ID # (not identity) SelSGInsulationType,ID # (not identity) SelSGManufacturer,ID SelStayInsulatorSize,ID SelSuspensionClampBrand,ID SelSuspensionClampSize,ID SelTSwitchType,ID SelTowerType,ID SelTransformerCapacity,ID SelTransformerManuf,ID SelTransformerType,ID #(not identity) SelTypeOfArchingHorn,ID SelTypeOfCable,ID #(not identity) SelTypeOfConductor,ID # (not identity) SelTypeOfInsulationCB,ID # (not identity) SelTypeOfMidSpanJoint,ID SelTypeOfSTJoint,ID SelTypeSTCable,ID SelUGVoltage,ID # (not identity) SelVoltageInOut,ID SelWireSize,ID SelWireType,ID SelWonpieceBrand,ID # # Net* tables added on 24 Oct NetArchingHorn,Idx NetBatteryBank,Idx # identity, FunctLocation Pri NetBiMetal,Idx NetBoxFuse,Idx NetCable,Idx # identity, FunctLocation Pri NetCapacitorBank,Idx # identity, FunctLocation Pri NetCircuitBreaker,Idx # identity, FunctLocation Pri NetCombineSealingChamber,Idx NetCommunication,Idx NetCompInfras,Idx NetControl,Idx NetCraddle,Idx NetDeadEndClamp,Idx NetEarthing,Idx NetFaultIndicator,Idx NetFeederPillar,Idx # identity, FunctLocation Pri NetGenCable,Idx # identity , FunctLocation Not Null NetGenerator,Idx NetGrid,Idx NetHVOverhead,Idx #identity, FunctLocation Pri NetHVUnderground,Idx #identity, FunctLocation Pri NetIPC,Idx NetInductorBank,Idx NetInsulator,Idx NetJoint,Idx NetJunctionBox,Idx NetLVDB,Idx #identity, FunctLocation Pri NetLVOverhead,Idx NetLVUnderground,Idx # identity, FunctLocation Not Null NetLightningArrester,Idx NetLineTap,Idx NetMidSpanConnectors,Idx NetMidSpanJoint,Idx NetNER,Idx # identity , FunctLocation Pri NetOilPump,Idx NetOtherComponent,Idx NetPole,Idx NetRMU,Idx # identity, FunctLocation Pri NetStreetLight,Idx NetStrucSupp,Idx NetSuspensionClamp,Idx NetSwitchGear,Idx # identity, FunctLocation Pri NetTermination,Idx NetTransition,Idx NetWonpiece,Idx # # comment1 SelMVFuseType,ID selFuseSize,ID netRelay,Idx # identity, FunctLocation Pri SysListVolt,ID sysVoltLevel,ID_SVL sysRestoration,ID_SRE sysRepairMethod,ID_SRM # (not identity) sysInterruptionType,ID_SIN netTransformer,Idx # identity, FunctLocation Pri # # sysComponent,ID_SC sysCodecibs #-- no idea, UpdatedOn(the only column is unique),Ermscode,Cibscode is unique but got null value sysCodeno,id sysProtection,ID_SP sysEquipment,ID_SEQ sysAddress #-- no idea, ID_SAD(might be auto gen No) sysWeather,ID_SW sysEnvironment,ID_SE sysPhase,ID_SPH sysFailureCause,ID_SFC sysFailureMode,ID_SFM SysSchOutageMode,ID_SSM SysOutageType,ID_SOT SysInstallation,ID_SI SysInstallationCat,ID_SIC SysInstallationType,ID_SIT SysFaultCategory,ID_SF #(not identity) SysResponsible,ID_SR SysProtectionOperation,ID_SPO #(not identity) netCodename,CodeNo #(not identity) netSubstation,Idx #identity, FunctLocation Pri netLvFeeder,Idx # identity, FunctLocation Pri # # tblReport,ReportNo tblRepRestoration,ID_RR tblRepResdetail,ID_RRD tblRepFailureMode,ID_RFM tblRepFailureCause,ID_RFC tblRepRepairMethod,ReportNo # (not identity) tblInterruptionType,ID_TIN tblProtType,ID_PT #--capital letter tblRepProtection,ID_RP tblRepComponent,ID_RC tblRepWeather,ID_RW tblRepEnvironment,ID_RE tblRepSubstation,ID_RSS tblInstallationType,ID_TIT tblInstallationCat,ID_TIC tblFailureCause,ID_TFC tblFailureMode,ID_TFM tblProtection,ID_TP tblComponent,ID_TC tblProtdetail,Id # (Id)--capital letter for I tblInstallation,ID_TI # "; $tables = explode("\n",$tables); $rep = new ADODB_Replicate($DB,$DB2); $rep->fieldFilter = 'FieldFilter'; $rep->selFilter = 'SELFILTER'; $rep->indexFilter = 'IndexFilter'; if (1) { $rep->debug = 1; $DB->debug=1; $DB2->debug=1; } # $rep->SwapDBs(); $cnt = sizeof($tables); foreach($tables as $k => $table) { $pkey = ''; if (!ParseTable($table, $pkey)) continue; ####################### $kcnt = $k+1; echo "

($kcnt/$cnt) $table -- $pkey

\n"; flush();@ob_flush(); CreateTable($rep,$table); # COPY DATA TableStats($rep, $table, $pkey); if ($table == 'JohnTest') MergeDataJohnTest($rep, $table, $pkey); else CopyData($rep, $table, $pkey); } if (!empty($TARR)) { ksort($TARR); adodb_pr($TARR); asort($TARR); adodb_pr($TARR); } echo "
",date('H:i:s'),": Done";