public DBsyncTvTableType(Tablee tab)
{
this.tab = tab;
this.Text = tab.NazovTabulky;
this.typeOfIcon = TableIcon;
this.Name = this.Text;
}
//public override List<string> alterTable(Tablee tabIn)
//{
// throw new NotImplementedException();
//}
public override List<string> addcolumn(Tablee tabIn, Columnn colIn)
{
List<string> output = new List<string>();
string nulnotnull;
if (colIn.IS_NULLABLE1 == "YES") nulnotnull = "NULL";
else nulnotnull = "NOT NULL";
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " ADD " + colIn.COULUMN_NAME1 + " " + generateType(colIn) + " " + nulnotnull);
//zistim ci existuje constrainta na povodnej tabulke
if (colIn.COULUMN_DEFAULT1.Length > 0)
{
string name = "DF_" + tabIn.NazovTabulky + "_" + colIn.COULUMN_NAME1;
foreach (Constraintt con in tabIn.Constrainty)
{
if (con.Constraint_typ == "DEFAULT")
{
if (con.Column[0] == colIn.COULUMN_NAME1)
{
//zrusim existujucu constraint
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " DROP CONSTRAINT " + con.Constraint_nam);
name = con.Constraint_nam;
}
}
}
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " ADD CONSTRAINT " + name + " DEFAULT " + colIn.COULUMN_DEFAULT1 + " FOR " + colIn.COULUMN_NAME1);
}
return output;
}
public void Init(int numTable)
{
Tablee unTable = Tablee.GetInstance(numTable);
TB_Num_Table.Text = unTable.numTablee.ToString();
TB_Nbr_Place.Text = unTable.nbPlaceTablee.ToString();
}
public override List<string> addConstraint(Tablee tabIn, Constraintt constIn)
{
List<string> output = new List<string>();
if (constIn.Constraint_typ == "CHECK")
{
output.Add("ALTER TABLE [" + tabIn.NazovTabulky + "] ADD CONSTRAINT " + constIn.Constraint_nam + " CHECK (" + constIn.Condition + ")");
}
return output;
}
private void Init()
{
List <Tablee> desTablee;
desTablee = Tablee.GetTablee();
foreach (Tablee t in desTablee)
{
if (t.numTablee != 0)
{
ListViewItem LTablee;
LTablee = new ListViewItem(new String[] { t.numTablee.ToString(), t.nbPlaceTablee.ToString() });
lv_Table.Items.Add(LTablee);
}
}
}
private void Init()
{
List <Tablee> uneTablee; // Creation d'une liste de table
uneTablee = Tablee.GetTablee(); //Récupération de toute les table de la BDD
List <Serveur> uneServeur; // Creation d'une liste de table
uneServeur = Serveur.GetServeur(); //Récupération de toute les table de la BDD
CB_NumTable.DataSource = uneTablee; // Stockages des table dans la ComboBox
CB_NumTable.ValueMember = "numTablee";
CB_NumTable.DisplayMember = "numTablee";
CB_IdServeur.DataSource = uneServeur; // Stockages des table dans la ComboBox
CB_IdServeur.ValueMember = "idServeur";
CB_IdServeur.DisplayMember = "nomServeur";
this.ControlBox = false; //Désactivation de ControlBox
}
public override List<string> addIndex(Tablee tabIn, Index indexIn)
{
List<string> output = new List<string>();
bool exist = false;
if (indexIn.IsPrmaryKey) exist = true;
string cols = "";
bool comma = false;
foreach (string s in indexIn.Columns)
{
if (comma) cols += ",";
comma = true;
cols += s;
}
if (!exist)
{
string unq = "";
if (indexIn.Unique) unq = "UNIQUE";
output.Add("CREATE " + unq + " " + indexIn.Type + " INDEX " + indexIn.Name + " ON [" + tabIn.NazovTabulky + "](" + cols + ")");
}
return output;
}
public abstract List<string> alterColumn(Tablee tabIn, Columnn colIn);
public abstract List<string> removeTrigger(Tablee tabIn, Trigger trigIn);
// nacitanie tabuliek
public override List<Tablee> ReadTables()
{
List<Tablee> vystup = new List<Tablee>();
pripojenie.Open();
SqlCommand com = pripojenie.CreateCommand();
com.CommandText = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'";
SqlDataReader red = com.ExecuteReader();
while (red.Read())
{
Tablee tab = new Tablee();
tab.NazovTabulky = red["TABLE_NAME"].ToString();
vystup.Add(tab);
}
red.Close();
//nacitam stlpce pre jednotlive tabulky
foreach (Tablee tab in vystup)
{
readColumnsForTab(tab);
readPrivileges(tab);
ReadTriggersForTab(tab);
readConstraintsForTab(tab);
readIndexesForTab(tab);
//nacitajPKPreTab(tab);
//nacitajFKPreTab(tab);
readFKForTab(tab);
readPKForTab(tab);
}
return vystup;
}
public override List<string> alterKey(Tablee tabIn, Key keyIn)
{
List<string> output = new List<string>();
output.AddRange(removeKey(tabIn, keyIn));
output.AddRange(addKey(tabIn, keyIn));
return output;
}
public abstract List<string> removeConstraint(Tablee tabIn, Constraintt constIn);
public abstract List<string> createTable(Tablee tabIn);
public abstract List<string> alterKey(Tablee tabIn, Key keyIn);
public override List<string> removeKey(Tablee tabIn, Key keyIn)
{
List<string> output = new List<string>();
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " drop CONSTRAINT " + keyIn.NameOfKey );
return output;
}
public override List<string> removeIndex(Tablee tabIn, Index indexIn)
{
List<string> output = new List<string>();
if (!indexIn.IsPrmaryKey)
{
output.Add(" ALTER TABLE " + tabIn.NazovTabulky + " DROP INDEX " + indexIn.Name);
}
return output;
}
public override List<string> removeFKonTab(Tablee tab)
{
List<string> output = new List<string>();
foreach (Key k in tab.Keys)
{
if (!k.PrimaryKey)
{
output.Add("ALTER TABLE [" + tab.NazovTabulky + "] DROP FOREIGN KEY " + k.NameOfKey);
}
}
return output;
}
public override List<string> removeConstraint(Tablee tabIn, Constraintt constIn)
{
List<string> output = new List<string>();
if (constIn.Constraint_typ != "PRIMARY KEY")
if (constIn.Constraint_typ != "FOREIGN KEY")
{
{
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " drop CONSTRAINT " + constIn.Constraint_nam);
}
}
return output;
}
public override List<string> removeColumn(Tablee tabIn, Columnn colIn)
{
List<string> output = new List<string>();
output.Add("ALTER TABLE " + tabIn.NazovTabulky + " DROP COLUMN " + colIn.COULUMN_NAME1 );
return output;
}
public abstract List<string> alterConstraint(Tablee tabIn, Constraintt constIn);
public override List<string> removeTable(Tablee tabIn)
{
List<string> output = new List<string>();
output.Add("DROP TABLE [" + tabIn.NazovTabulky+"]");
return output;
}
public abstract List<string> alterIndex(Tablee tabIn, Index indexIn);
public override List<string> alterTrigger(Tablee tabIn, Trigger trigIn)
{
List<string> output = new List<string>();
output.AddRange(removeTrigger(tabIn,trigIn));
output.AddRange(addTrigger(tabIn, trigIn));
return output;
}
public abstract List<string> alterTrigger(Tablee tabIn, Trigger trigIn);
public override List<string> alterIndex(Tablee tabIn, Index indexIn)
{
List<string> output = new List<string>();
if (!indexIn.IsPrmaryKey)
{
output.AddRange(removeIndex(tabIn,indexIn));
output.AddRange(addIndex(tabIn, indexIn));
}
return output;
}
public abstract List<string> removeColumn(Tablee tabIn, Columnn colIn);
public abstract List<string> removeKey(Tablee tabIn, Key keyIn);
public abstract List<string> removeFKonTab(Tablee tab);
public abstract List<string> removeTable(Tablee tabIn);
public override List<string> removeTrigger(Tablee tabIn, Trigger trigIn)
{
List<string> output = new List<string>();
output.Add(" DROP TRIGGER " + trigIn.Trigger_name );
return output;
}
public override List<string> createTable(Tablee tabIn)
{
List<string> output = new List<string>();
string s = "Create table [" + tabIn.NazovTabulky + "]( ";
foreach (Columnn col in tabIn.Stlpce)
{
string nulnotnull;
if (col.IS_NULLABLE1 == "YES") nulnotnull = "NULL";
else nulnotnull = "NOT NULL";
//if (col.COULUMN_DEFAULT1.Length == 0) s += col.COULUMN_NAME1 + " " + generateType(col) + " " + nulnotnull + " ," + Environment.NewLine;
s += col.COULUMN_NAME1 + " " + generateType(col) + " " + addDefault(col) +" " + nulnotnull + " ," + Environment.NewLine;
}
foreach (Constraintt con in tabIn.Constrainty)
{
if (con.Constraint_typ == "PRIMARY KEY")
{
//vyhladam index s nazvom kluca
string cluster = "";
foreach (Index ind in tabIn.Indexy)
{
if (con.Constraint_nam == ind.Name)
{
cluster = ind.Type;
}
}
s += "Constraint " + con.Constraint_nam + " " + con.Constraint_typ + " "+cluster +" (";
bool putcolumn = false;
foreach (string ss in con.Column)
{
if (putcolumn) s += ",";
putcolumn = true;
s += ss;
}
s += ")";
}
}
s += " )";
output.Add(s);
//zaklad tabulkky je vytvoreny
//vytvorim kluce
output.AddRange(addForeinKey(tabIn));
output.AddRange(addcheckConst(tabIn));
output.AddRange(addIndexes(tabIn));
output.AddRange(addTriggers(tabIn));
foreach (Privilege priv in tabIn.Privileges)
{
output.Add(createPrivilege(priv));
}
return output;
}
public abstract List<string> removeIndex(Tablee tabIn, Index indexIn);
public override List<string> alterConstraint(Tablee tabIn, Constraintt constIn)
{
List<string> output = new List<string>();
if (constIn.Constraint_typ != "PRIMARY KEY")
{
if (constIn.Constraint_typ != "FOREIGN KEY")
{
output.AddRange(removeConstraint(tabIn, constIn));
output.AddRange(addConstraint(tabIn, constIn));
}
}
return output;
}
