protected override void LoadConstraints(TableStructure table)
{
base.LoadConstraints(table);
TableAnalyser tad = new TableAnalyser();
DataTable fks = GetDbConn().GetSchema("ForeignKeys");
foreach (DataRow row in fks.Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.colname = row.SafeString("FKEY_FROM_COLUMN");
col.dstcolname = row.SafeString("FKEY_TO_COLUMN");
col.tblname = row.SafeString("TABLE_NAME");
col.dsttblname = row.SafeString("FKEY_TO_TABLE");
if (col.dsttblname != null && col.dsttblname.StartsWith("\"") && col.dsttblname.EndsWith("\""))
{
col.dsttblname = col.dsttblname.Substring(1, col.dsttblname.Length - 2);
}
col.ordinal = row.SafeString("FKEY_FROM_ORDINAL_POSITION");
col.keyname = row.SafeString("CONSTRAINT_NAME");
col.keytype = "FOREIGN KEY";
tad.cols.Add(col);
}
tad.SaveConstraints(table, this);
}
protected override void LoadIndexes(TableStructure table)
{
base.LoadIndexes(table);
if (m_indexCols == null)
{
m_indexCols = GetDbConn().GetSchema("IndexColumns");
}
TableAnalyser tadidx = new TableAnalyser();
foreach (DataRow row in m_indexCols.Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.keytype = "INDEX";
col.keyname = row["INDEX_NAME"].ToString();
col.tblname = row["TABLE_NAME"].ToString();
col.tblschema = row["TABLE_SCHEM"].ToString();
col.ordinal = row["ORDINAL_POSITION"].ToString();
col.colname = row["COLUMN_NAME"].ToString();
col.keyisunique = (bool)row["UNIQUE_INDEX"];
if (col.keyname.StartsWith("SYS_IDX"))
{
continue;
}
if (col.tblschema.StartsWith("SYSTEM_"))
{
continue;
}
tadidx.cols.Add(col);
}
tadidx.SaveConstraints(table, this);
}
private TableAnalyser GetRefsTA()
{
var res = AnalyserCache.GetTableAnalyser("refs");
if (res != null)
{
return(res);
}
var ta = new TableAnalyser();
foreach (var row in CachedQueryRows(SqlScripts.getrefs))
{
var key = new TableAnalyser.Key();
key.keytype = "FOREIGN KEY";
key.deleterule = row.SafeString("DELETE_RULE");
key.keyname = row["FK_CONSTRAINT"].ToString();
key.tblname = row["FK_TABLE"].ToString();
key.tblschema = row["FK_OWNER"].ToString();
key.dstpkschema = row["R_OWNER"].ToString();
key.dstpkname = row["R_CONSTRAINT"].ToString();
key.dsttblschema = row["R_OWNER"].ToString();
key.dsttblname = row["R_TABLE"].ToString();
ta.keys.Add(key);
}
foreach (var row in CachedQueryRows(SqlScripts.getrefcols))
{
var col = new TableAnalyser.Col();
col.keytype = "FOREIGN KEY";
col.keyname = row["FK_CONSTRAINT"].ToString();
col.tblname = row["FK_TABLE"].ToString();
col.tblschema = row["FK_OWNER"].ToString();
col.colname = row["FK_COLUMN_NAME"].ToString();
col.dstcolname = row["R_COLUMN_NAME"].ToString();
ta.cols.Add(col);
}
AnalyserCache.PutTableAnalyser("refs", ta);
return(ta);
}
protected override void LoadIndexes(TableStructure table)
{
try
{
string tblname = table.FullName.Name;
string schema = table.FullName.Schema;
DataTable indexlist = GetCachedTableColumnsTable("indexes", LoadIndexesTable, table.FullName);
DataTable indexcols = GetCachedTableColumnsTable("indexcols", LoadIndexColsTable, table.FullName);
TableAnalyser tadidx = new TableAnalyser();
Dictionary <string, bool> dct = new Dictionary <string, bool>();
foreach (DataRow row in indexlist.Rows)
{
TableAnalyser.Key key = new TableAnalyser.Key();
key.keytype = "INDEX";
key.keyisunique = Int32.Parse(row["IsUnique"].ToString()) == 1;
key.keyname = row["Name"].ToString();
key.tblname = tblname;
key.tblschema = schema;
dct[key.keyname] = true;
tadidx.keys.Add(key);
}
foreach (DataRow row in indexcols.Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.keytype = "INDEX";
col.keyname = row["IndexName"].ToString();
col.tblname = tblname;
col.tblschema = schema;
col.ordinal = row["KeyOrder"].ToString();
col.colname = row["ColumnName"].ToString();
if (!dct.ContainsKey(col.keyname))
{
continue;
}
tadidx.cols.Add(col);
}
tadidx.SaveConstraints(table, this);
}
catch (Exception err)
{
Logging.Warning("Error loading MSSQL indexes:" + err.ToString());
}
//table.FilledMembers |= TableStructureMembers.Indexes;
}
private TableAnalyser GetIndexesTA()
{
var res = AnalyserCache.GetTableAnalyser("indexes");
if (res != null)
{
return(res);
}
var ta = new TableAnalyser();
ta.AllowDeduceFromColumns = false;
foreach (var row in CachedQueryRows(SqlScripts.getindexes))
{
var key = new TableAnalyser.Key();
key.keytype = "INDEX";
key.keyname = row["INDEX_NAME"].ToString();
key.keyschema = row["TABLE_OWNER"].ToString();
key.tblname = row["TABLE_NAME"].ToString();
key.tblschema = row["TABLE_OWNER"].ToString();
key.keyisunique = row["UNIQUENESS"].SafeToString() == "UNIQUE";
ta.keys.Add(key);
}
foreach (var row in CachedQueryRows(SqlScripts.getindexcols))
{
var col = new TableAnalyser.Col();
col.keytype = "INDEX";
col.keyschema = row["USERNAME"].ToString();
col.keyname = row["INDEX_NAME"].ToString();
col.colname = row["COLUMN_NAME"].ToString();
col.ordinal = row["COLUMN_POSITION"].ToString();
ta.cols.Add(col);
}
AnalyserCache.PutTableAnalyser("indexes", ta);
return(ta);
}
protected override void LoadConstraints(TableStructure table)
{
if (m_members.TableMembers.ContainsAny(TableStructureMembers.ConstraintsNoIndexesNoRefs))
{
TableAnalyser ta = new TableAnalyser();
foreach (DataRow row in GetCachedTableColumnsTable("oracle.constraints", LoadConstraintsTable, table.FullName).Rows)
{
TableAnalyser.Key key = new TableAnalyser.Key();
key.keytype = OracleConstraintToDatAdmin(row["CONSTRAINT_TYPE"].ToString());
key.keyname = row["CONSTRAINT_NAME"].ToString();
key.tblname = row["TABLE_NAME"].ToString();
key.tblschema = row["OWNER"].ToString();
key.checkexpr = row["SEARCH_CONDITION"].SafeToString();
ta.keys.Add(key);
}
foreach (DataRow row in GetCachedTableColumnsTable("oracle.constraintcols", LoadConstraintsColsTable, table.FullName).Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.keyname = row["CONSTRAINT_NAME"].ToString();
col.tblname = row["TABLE_NAME"].ToString();
col.tblschema = row["OWNER"].ToString();
col.ordinal = row["POSITION"].ToString();
col.colname = row["COLUMN_NAME"].ToString();
ta.cols.Add(col);
}
ta.SaveConstraints(table, this);
}
if (m_members.TableMembers.ContainsAny(TableStructureMembers.ForeignKeys | TableStructureMembers.ReferencedFrom))
{
GetRefsTA().SaveConstraints(table, this);
}
if (m_members.TableMembers.ContainsAny(TableStructureMembers.Indexes))
{
GetIndexesTA().SaveConstraints(table, this);
}
}
protected override void LoadConstraints(TableStructure table)
{
OleDbConnection c = (OleDbConnection)GetDbConn();
TableAnalyser tad = new TableAnalyser();
DataTable pks = c.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
foreach (DataRow row in pks.Rows)
{
if (row["TABLE_NAME"].ToString() != table.FullName.Name)
{
continue;
}
TableAnalyser.Col col = new TableAnalyser.Col();
col.keytype = "PRIMARY KEY";
col.ordinal = row["ORDINAL"].ToString();
col.keyname = row["PK_NAME"].ToString();
col.colname = row["COLUMN_NAME"].ToString();
col.tblname = row["TABLE_NAME"].ToString();
tad.cols.Add(col);
}
DataTable fks = c.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow row in fks.Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.keytype = "FOREIGN KEY";
col.keyname = row["FK_NAME"].ToString();
col.ordinal = row["ORDINAL"].ToString();
col.colname = row["FK_COLUMN_NAME"].ToString();
col.tblname = row["FK_TABLE_NAME"].ToString();
col.dstcolname = row["PK_COLUMN_NAME"].ToString();
col.dsttblname = row["PK_TABLE_NAME"].ToString();
tad.cols.Add(col);
}
tad.SaveConstraints(table, this);
//table.FilledMembers |= m_members.TableMembers.FilterConstraints(false);
}
protected override void LoadIndexes(TableStructure table)
{
base.LoadIndexes(table);
TableAnalyser tad = new TableAnalyser();
DataTable idxs = GetDbConn().GetSchema("Indexes");
foreach (DataRow row in idxs.Rows)
{
TableAnalyser.Key key = new TableAnalyser.Key();
if ((bool)row["PRIMARY_KEY"])
{
continue;
}
key.tblname = row["TABLE_NAME"].ToString();
key.keyname = row["INDEX_NAME"].ToString();
key.keytype = "INDEX";
tad.keys.Add(key);
}
DataTable idxcols = GetDbConn().GetSchema("IndexColumns");
foreach (DataRow row in idxcols.Rows)
{
TableAnalyser.Col col = new TableAnalyser.Col();
col.colname = row["COLUMN_NAME"].ToString();
col.tblname = row["TABLE_NAME"].ToString();
col.keyname = row["CONSTRAINT_NAME"].ToString();
col.ordinal = row["ORDINAL_POSITION"].ToString();
col.keytype = "INDEX";
tad.cols.Add(col);
}
tad.SaveConstraints(table, this);
table._Constraints.RemoveIf(cnt => cnt.Name != null && cnt.Name.StartsWith("sqlite_"));
}