public List<string> generateDataScriptsForDB2(DbSyncTableDiff tab)
{
List<string> output = new List<string>();
if (!tab.isDifferent())
{
int havePrimaryKey = 0;
bool simplePK = false;
Key kk = null;
foreach (DbSyncKeyDiff k in tab.KeysDifList())
{
if (k.getKeyA().PrimaryKey)
{
havePrimaryKey++;
if (k.getKeyA().NameOfColumns.Count == 1)
{
simplePK = true;
kk = k.getKeyA();
}
}
}
if ((havePrimaryKey == 1) && simplePK)
{
if (this.settings.ComparisonMethod == ComparatorSettings.LeftRight)
{
DbDataReader red1 = db1.prip.getReaderOfTable(tab.getTableName(),"","");
while (red1.Read())
{
bool differentExist = false;
bool differentNotExist = false;
List<ColVal> cvlist = new List<ColVal>();
string key = red1[kk.NameOfColumns[0]].ToString();
DbDataReader red2 = db2.prip.getReaderOfTable(tab.getTableName(), kk.NameOfColumns[0], key);
if (red2.Read())
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1,red1[col.COULUMN_NAME1].ToString()));
if (red1[col.COULUMN_NAME1].ToString() != red2[col.COULUMN_NAME1].ToString())
{
differentExist = true;
}
}
}
else differentNotExist = true;
red2.Close();
if (differentExist)
{
output.Add(db2.prip.updateRow(tab.getTableName(), cvlist, new ColVal(kk.NameOfColumns[0], key)));
}
if (differentNotExist)
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1,red1[col.COULUMN_NAME1].ToString()));
}
output.Add(db2.prip.insertRow(tab.getTableName(), cvlist));
}
}
red1.Close();
}
if (this.settings.ComparisonMethod == ComparatorSettings.LeftRightDel)
{
DbDataReader red1 = db1.prip.getReaderOfTable(tab.getTableName(), "", "");
while (red1.Read())
{
bool differentExist = false;
bool differentNotExist = false;
List<ColVal> cvlist = new List<ColVal>();
string key = red1[kk.NameOfColumns[0]].ToString();
DbDataReader red2 = db2.prip.getReaderOfTable(tab.getTableName(), kk.NameOfColumns[0], key);
if (red2.Read())
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
if (red1[col.COULUMN_NAME1].ToString() != red2[col.COULUMN_NAME1].ToString())
{
differentExist = true;
}
}
}
else differentNotExist = true;
red2.Close();
if (differentExist)
{
output.Add(db2.prip.updateRow(tab.getTableName(), cvlist, new ColVal(kk.NameOfColumns[0], key)));
}
if (differentNotExist)
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
}
output.Add(db2.prip.insertRow(tab.getTableName(), cvlist));
}
}
red1.Close();
red1 = db2.prip.getReaderOfTable(tab.getTableName(), "", "");
while (red1.Read())
{
bool differentExist = false;
bool differentNotExist = false;
List<ColVal> cvlist = new List<ColVal>();
string key = red1[kk.NameOfColumns[0]].ToString();
DbDataReader red2 = db1.prip.getReaderOfTable(tab.getTableName(), kk.NameOfColumns[0], key);
if (red2.Read())
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
if (red1[col.COULUMN_NAME1].ToString() != red2[col.COULUMN_NAME1].ToString())
{
differentExist = true;
}
}
}
else differentNotExist = true;
red2.Close();
//if (differentExist)
//{
// output.Add(db2.prip.updateRow(tab.getTableName(), cvlist, new ColVal(key, red1[key].ToString())));
//}
if (differentNotExist)
{
output.Add(db2.prip.deleteRow(tab.getTableName(), new ColVal(kk.NameOfColumns[0], key)));
}
}
red1.Close();
//teraz vymazem tie, co tam nemaju byt
}
if (this.settings.ComparisonMethod == ComparatorSettings.TwoWay)
{
DbDataReader red1 = db2.prip.getReaderOfTable(tab.getTableName(), "", "");
while (red1.Read())
{
bool differentExist = false;
bool differentNotExist = false;
List<ColVal> cvlist1 = new List<ColVal>();
List<ColVal> cvlist2 = new List<ColVal>();
string key = red1[kk.NameOfColumns[0]].ToString();
DbDataReader red2 = db1.prip.getReaderOfTable(tab.getTableName(), kk.NameOfColumns[0], key);
if (red2.Read())
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist1.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
cvlist2.Add(new ColVal(col.COULUMN_NAME1, red2[col.COULUMN_NAME1].ToString()));
if (red1[col.COULUMN_NAME1].ToString() != red2[col.COULUMN_NAME1].ToString())
{
differentExist = true;
}
}
}
else differentNotExist = true;
red2.Close();
if (differentExist)
{
if (this.settings.IsDbAPriority)
{
output.Add(db2.prip.updateRow(tab.getTableName(), cvlist2, new ColVal(kk.NameOfColumns[0], key)));
}
}
if (differentNotExist)
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
// cvlist1.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
}
// output.Add(db1.prip.insertRow(tab.getTableName(), cvlist1));
}
}
red1.Close();
red1 = db1.prip.getReaderOfTable(tab.getTableName(), "", "");
while (red1.Read())
{
bool differentExist = false;
bool differentNotExist = false;
List<ColVal> cvlist = new List<ColVal>();
string key = red1[kk.NameOfColumns[0]].ToString();
DbDataReader red2 = db2.prip.getReaderOfTable(tab.getTableName(), kk.NameOfColumns[0], key);
if (red2.Read())
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
if (red1[col.COULUMN_NAME1].ToString() != red2[col.COULUMN_NAME1].ToString())
{
differentExist = true;
}
}
}
else differentNotExist = true;
red2.Close();
//if (differentExist)
//{
// output.Add(db2.prip.updateRow(tab.getTableName(), cvlist, new ColVal(key, red1[key].ToString())));
//}
if (differentNotExist)
{
foreach (Columnn col in tab.getTabA().Stlpce)
{
cvlist.Add(new ColVal(col.COULUMN_NAME1, red1[col.COULUMN_NAME1].ToString()));
}
output.Add(db2.prip.insertRow(tab.getTableName(), cvlist));
}
}
red1.Close();
}
}
else throw new Exception("Cannot synchronize data on this table, because there is no primary key or composite primary key");
}
else
throw new Exception("Cannot synchronize data on non synchronized tables, synchronize schema first please");
return output;
//return null;
}
public List<string> syncTable(DbSyncTableDiff tabIn)
{
List<string> output = new List<string>();
if (settings.ComparisonMethod == ComparatorSettings.LeftRight )
{
//missing objects
foreach (DbSyncColumnDiff col in tabIn.ColumnsDifferent)
{
output.AddRange(db1.prip.alterColumn(tabIn.getTabB(), col.getColumnA()));
}
foreach (Trigger trg in tabIn.TriggersMissingDb2)
{
output.AddRange(db1.prip.addTrigger(tabIn.getTabB(), trg));
}
foreach (Privilege priv in tabIn.GrantsMissingDb2)
{
output.Add(db1.prip.createPrivilege(priv));
}
foreach (Index ind in tabIn.IndexesMissingDb2)
{
output.AddRange(db1.prip.addIndex(tabIn.getTabB(), ind));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb2)
{
output.AddRange(db1.prip.addConstraint(tabIn.getTabB(), con));
}
foreach (Key keyy in tabIn.KeysMissingDb2)
{
output.AddRange(db1.prip.addKey(tabIn.getTabB(), keyy));
}
//objects to rewrite
foreach (DbSyncIndexDiff ind in tabIn.IndexesDifferent)
{
output.AddRange(db1.prip.alterIndex(tabIn.getTabB(), ind.getIndexA()));
}
foreach (DbSyncPrivilegeDiff priv in tabIn.GrantsDifferent)
{
output.AddRange(db1.prip.alterPrivilege(priv.getPrivB()));
}
foreach (DbSyncTriggerDiff trg in tabIn.TriggersDifferent)
{
output.AddRange(db1.prip.alterTrigger(tabIn.getTabB(), trg.getTrigA()));
}
foreach (DbSyncKeyDiff key in tabIn.KeysDifferent)
{
output.AddRange(db1.prip.alterKey(tabIn.getTabB(), key.getKeyA()));
}
foreach (DbSyncConstraintDiff con in tabIn.ConstraintsDifferent)
{
output.AddRange(db1.prip.alterConstraint(tabIn.getTabB(), con.getConstA()));
}
}
if (settings.ComparisonMethod == ComparatorSettings.LeftRightDel)
{
//missing objects
foreach (DbSyncColumnDiff col in tabIn.ColumnsDifferent)
{
output.AddRange(db1.prip.alterColumn(tabIn.getTabB(), col.getColumnA()));
}
foreach (Trigger trg in tabIn.TriggersMissingDb2)
{
output.AddRange(db1.prip.addTrigger(tabIn.getTabB(), trg));
}
foreach (Privilege priv in tabIn.GrantsMissingDb2)
{
output.Add(db1.prip.createPrivilege(priv));
}
foreach (Index ind in tabIn.IndexesMissingDb2)
{
output.AddRange(db1.prip.addIndex(tabIn.getTabB(), ind));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb2)
{
output.AddRange(db1.prip.addConstraint(tabIn.getTabB(), con));
}
foreach (Key keyy in tabIn.KeysMissingDb2)
{
output.AddRange(db1.prip.addKey(tabIn.getTabB(), keyy));
}
//objects to delete
foreach (Index ind in tabIn.IndexesMissingDb1)
{
output.AddRange(db1.prip.removeIndex(tabIn.getTabB(), ind));
}
foreach (Trigger trg in tabIn.TriggersMissingDb1)
{
output.AddRange(db1.prip.removeTrigger(tabIn.getTabB(), trg));
}
foreach (Key keyy in tabIn.KeysMissingDb1)
{
output.AddRange(db1.prip.removeKey(tabIn.getTabB(), keyy));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb1)
{
output.AddRange(db1.prip.removeConstraint(tabIn.getTabB(), con));
}
foreach (Privilege priv in tabIn.GrantsMissingDb1)
{
output.AddRange(db1.prip.removePrivilege(priv));
}
foreach (Columnn col in tabIn.ColumnsMissingDb1)
{
output.AddRange(db1.prip.removeColumn(tabIn.getTabB(), col));
}
//objects to rewrite
foreach (DbSyncIndexDiff ind in tabIn.IndexesDifferent)
{
output.AddRange(db1.prip.alterIndex(tabIn.getTabB(), ind.getIndexA()));
}
foreach (DbSyncPrivilegeDiff priv in tabIn.GrantsDifferent)
{
output.AddRange(db1.prip.alterPrivilege(priv.getPrivA()));
}
foreach (DbSyncTriggerDiff trg in tabIn.TriggersDifferent)
{
output.AddRange(db1.prip.alterTrigger(tabIn.getTabB(), trg.getTrigA()));
}
foreach (DbSyncKeyDiff key in tabIn.KeysDifferent)
{
output.AddRange(db1.prip.alterKey(tabIn.getTabB(), key.getKeyA()));
}
foreach (DbSyncConstraintDiff con in tabIn.ConstraintsDifferent)
{
output.AddRange(db1.prip.alterConstraint(tabIn.getTabB(), con.getConstA()));
}
}
if (settings.ComparisonMethod == ComparatorSettings.RightLeft )
{
//missing objects
foreach (DbSyncColumnDiff col in tabIn.ColumnsDifferent)
{
output.AddRange(db1.prip.alterColumn(tabIn.getTabA(), col.getColumnB()));
}
foreach (Trigger trg in tabIn.TriggersMissingDb1)
{
output.AddRange(db1.prip.addTrigger(tabIn.getTabA(), trg));
}
foreach (Privilege priv in tabIn.GrantsMissingDb1)
{
output.Add(db1.prip.createPrivilege(priv));
}
foreach (Index ind in tabIn.IndexesMissingDb1)
{
output.AddRange(db1.prip.addIndex(tabIn.getTabA(), ind));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb1)
{
output.AddRange(db1.prip.addConstraint(tabIn.getTabA(), con));
}
foreach (Key keyy in tabIn.KeysMissingDb1)
{
output.AddRange(db1.prip.addKey(tabIn.getTabA(), keyy));
}
//objects to rewrite
foreach (DbSyncIndexDiff ind in tabIn.IndexesDifferent)
{
output.AddRange(db1.prip.alterIndex(tabIn.getTabA(), ind.getIndexB()));
}
foreach (DbSyncPrivilegeDiff priv in tabIn.GrantsDifferent)
{
output.AddRange(db1.prip.alterPrivilege(priv.getPrivA()));
}
foreach (DbSyncTriggerDiff trg in tabIn.TriggersDifferent)
{
output.AddRange(db1.prip.alterTrigger(tabIn.getTabA(), trg.getTrigB()));
}
foreach (DbSyncKeyDiff key in tabIn.KeysDifferent)
{
output.AddRange(db1.prip.alterKey(tabIn.getTabA(), key.getKeyB()));
}
foreach (DbSyncConstraintDiff con in tabIn.ConstraintsDifferent)
{
output.AddRange(db1.prip.alterConstraint(tabIn.getTabA(), con.getConstB()));
}
}
if (settings.ComparisonMethod == ComparatorSettings.RightLeftDel)
{
//missing objects
foreach (DbSyncColumnDiff col in tabIn.ColumnsDifferent)
{
output.AddRange(db1.prip.alterColumn(tabIn.getTabA(), col.getColumnB()));
}
foreach (Trigger trg in tabIn.TriggersMissingDb1)
{
output.AddRange(db1.prip.addTrigger(tabIn.getTabA(), trg));
}
foreach (Privilege priv in tabIn.GrantsMissingDb1)
{
output.Add(db1.prip.createPrivilege(priv));
}
foreach (Index ind in tabIn.IndexesMissingDb1)
{
output.AddRange(db1.prip.addIndex(tabIn.getTabA(), ind));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb1)
{
output.AddRange(db1.prip.addConstraint(tabIn.getTabA(), con));
}
foreach (Key keyy in tabIn.KeysMissingDb1)
{
output.AddRange(db1.prip.addKey(tabIn.getTabA(), keyy));
}
//objects to delete
foreach (Index ind in tabIn.IndexesMissingDb2)
{
output.AddRange(db1.prip.removeIndex(tabIn.getTabA(), ind));
}
foreach (Trigger trg in tabIn.TriggersMissingDb2)
{
output.AddRange(db1.prip.removeTrigger(tabIn.getTabA(), trg));
}
foreach (Key keyy in tabIn.KeysMissingDb2)
{
output.AddRange(db1.prip.removeKey(tabIn.getTabA(), keyy));
}
foreach (Constraintt con in tabIn.ConstraintsMissingDb2)
{
output.AddRange(db1.prip.removeConstraint(tabIn.getTabA(), con));
}
foreach (Privilege priv in tabIn.GrantsMissingDb2)
{
output.AddRange(db1.prip.removePrivilege(priv));
}
foreach (Columnn col in tabIn.ColumnsMissingDb2)
{
output.AddRange(db1.prip.removeColumn(tabIn.getTabA(), col));
}
//objects to rewrite
foreach (DbSyncIndexDiff ind in tabIn.IndexesDifferent)
{
output.AddRange(db1.prip.alterIndex(tabIn.getTabA(), ind.getIndexB()));
}
foreach (DbSyncPrivilegeDiff priv in tabIn.GrantsDifferent)
{
output.AddRange(db1.prip.alterPrivilege(priv.getPrivB()));
}
foreach (DbSyncTriggerDiff trg in tabIn.TriggersDifferent)
{
output.AddRange(db1.prip.alterTrigger(tabIn.getTabA(), trg.getTrigB()));
}
foreach (DbSyncKeyDiff key in tabIn.KeysDifferent)
{
output.AddRange(db1.prip.alterKey(tabIn.getTabA(), key.getKeyB()));
}
foreach (DbSyncConstraintDiff con in tabIn.ConstraintsDifferent)
{
output.AddRange(db1.prip.alterConstraint(tabIn.getTabA(), con.getConstB()));
}
}
return output;
}
