//public static hierarchy get_hierarchy(string table)
//{
// if (hierarchies[conn].ContainsKey(table) /*&& hierarchies[conn][table].childs.Count > 0*/) return hierarchies[conn][table];
// DataRowCollection drc = DBHelper.query(conn, @"
// WITH hierarchy (TABLE_NAME, column_name, table_in, column_in, path)
// AS
// (
// SELECT kcu.TABLE_NAME, kcu.column_name, ccuin.TABLE_NAME, ccuin.COLUMN_NAME, cast(kcu.TABLE_NAME as nvarchar(max))
// FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
// inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcin on rcin.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG and rcin.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rcin.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
// inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccuin on rcin.UNIQUE_CONSTRAINT_CATALOG = ccuin.CONSTRAINT_CATALOG and rcin.UNIQUE_CONSTRAINT_SCHEMA = ccuin.CONSTRAINT_SCHEMA and rcin.UNIQUE_CONSTRAINT_NAME = ccuin.CONSTRAINT_NAME
// WHERE kcu.TABLE_NAME = '" + table + @"'
// UNION ALL
// SELECT kcu.TABLE_NAME, kcu.column_name, ccuin.TABLE_NAME, ccuin.COLUMN_NAME, path + '/' + kcu.TABLE_NAME
// from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
// inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcin on rcin.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG and rcin.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rcin.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
// inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccuin on rcin.UNIQUE_CONSTRAINT_CATALOG = ccuin.CONSTRAINT_CATALOG and rcin.UNIQUE_CONSTRAINT_SCHEMA = ccuin.CONSTRAINT_SCHEMA and rcin.UNIQUE_CONSTRAINT_NAME = ccuin.CONSTRAINT_NAME
// inner join hierarchy h on h.table_in = kcu.TABLE_NAME and h.TABLE_NAME <> kcu.TABLE_NAME
// )
// SELECT *
// FROM hierarchy
// --order by path
// ");
// foreach (DataRow dr in drc)
// {
// if (!hierarchies[conn].ContainsKey(dr["TABLE_NAME"].ToString())) hierarchies[conn].Add(dr["TABLE_NAME"].ToString(), new hierarchy() { name = dr["TABLE_NAME"].ToString() });
// if (!hierarchies[conn].ContainsKey(dr["table_in"].ToString())) hierarchies[conn].Add(dr["table_in"].ToString(), new hierarchy() { name = dr["table_in"].ToString() });
// if (!hierarchies[conn][dr["TABLE_NAME"].ToString()].childs.ContainsKey(dr["column_name"].ToString())) hierarchies[conn][dr["TABLE_NAME"].ToString()].childs.Add(dr["column_name"].ToString(), hierarchies[conn][dr["table_in"].ToString()]);
// //hierarchies[conn][dr["table_in"].ToString()].pk = dr["column_in"].ToString(); // предпологаем что все ссылки идут на pk
// //if (!hierarchies[conn][dr["table_in"].ToString()].refs.ContainsKey(dr["column_in"].ToString())) hierarchies[conn][dr["table_in"].ToString()].refs.Add(dr["column_in"].ToString(), hierarchies[conn][dr["TABLE_NAME"].ToString()]);
// }
// return hierarchies[conn][table];
//}
public static Dictionary <string, List <string> > get_query_elements(string table)
{
table_info ti = get_table(table);
Dictionary <string, List <string> > res = new Dictionary <string, List <string> >()
{
{ "select", new List <string>()
{
"[" + table + "].*"
} }, { "left join", new List <string>() }
};
foreach (KeyValuePair <string, List <string> > l in ti.links_out) /*if(table != l.Key)*/ foreach {
(string c in l.Value) {
table_info ti_ref = get_table(l.Key);
List <string> names_ref = new List <string>();
List <column_info> cols = ti_ref.names.Count > 0 ? ti_ref.names : ti_ref.columns.Values.ToList();
foreach (column_info ci in cols)
{
if (ci.tbl_out == null)
{
if (ci.data_type.Contains("char") || ci.data_type == "text")
{
names_ref.Add("[" + l.Key + "_" + c + "].[" + ci.name + "]");
}
else if (!ci.is_identity || (ci.is_identity && ci.is_name))
{
names_ref.Add("cast([" + l.Key + "_" + c + "].[" + ci.name + "] as nvarchar(max))");
}
}
}
res["select"].Add("isnull(" + string.Join(", '') + ' ' + isnull(", names_ref.ToArray()) + ", '') as [" + c + "_ref]");
res["left join"].Add("[" + l.Key + "] as [" + l.Key + "_" + c + "]" + " on [" + l.Key + "_" + c + "].[" + ti_ref.pk + "] = [" + table + "].[" + c + "]");
}
}
public static bool IsAutoupdate(libobj obj)
{
Type type = obj.GetType();
string name = type.Name;
table_info t;
if (!_tables.ContainsKey(name))
{
AutoUpdateClassAttribute[] attr = (AutoUpdateClassAttribute[])
type.GetCustomAttributes(typeof(AutoUpdateClassAttribute), false);
t = new table_info();
if (attr.Length > 0) //class labeled as autoupde-able
{
PropertyInfo[] props = type.GetProperties(BindingFlags.NonPublic |
BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.Instance);
AutoUpdatePropAttribute[] attrs;
List <PropertyInfo> updateable_props = new List <PropertyInfo>();
System.Array.ForEach <PropertyInfo>(props, (p) =>
{
attrs = (AutoUpdatePropAttribute[])p.GetCustomAttributes(
typeof(AutoUpdatePropAttribute), false);
if (attrs.Length > 0)
{
updateable_props.Add(p);
}
});
// if no property is labeled to be updated to a specific field (i.e. updateable_prop.count == 0)
// then there is nothing to update; thus only populate table_info if necc.
if (!(updateable_props.Count == 0))
{
t.ClassAttr = attr[0];
t.PropertyInfos = updateable_props.ToArray();
}
} //end if (attr.Length > 0) //class labeled as autoupde-able
_tables.Add(name, t);
}
else //if (!_tables.ContainsKey(name))
{
_tables.TryGetValue(name, out t);
} //end if (!_tables.ContainsKey(name))
return(t.ClassAttr != null);
}
public static table_info get_table(string table)
{
if (!tables.ContainsKey(conn))
{
tables.Add(conn, new Dictionary <string, table_info>());
}
if (tables[conn].ContainsKey(table))
{
return(tables[conn][table]);
}
table_info ti = new table_info();
ti.name = table;
DataRowCollection columns = DBHelper.query(conn, @"
SELECT c.column_name, ccu.column_name as fk_column, ccu.table_name as fk_table, ccuin.TABLE_NAME as in_table, ccuin.COLUMN_NAME as in_column, CONSTRAINT_TYPE, DATA_TYPE, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(c.TABLE_NAME), c.column_name, 'IsIdentity') as isidentity, iname
FROM INFORMATION_SCHEMA.COLUMNS c
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.TABLE_SCHEMA = c.TABLE_SCHEMA and kcu.TABLE_CATALOG = c.TABLE_CATALOG and kcu.TABLE_NAME = c.TABLE_NAME and kcu.COLUMN_NAME = c.COLUMN_NAME
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG and rc.UNIQUE_CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rc.UNIQUE_CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG and rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA and rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME and tc.TABLE_SCHEMA = c.TABLE_SCHEMA and tc.TABLE_CATALOG = c.TABLE_CATALOG and tc.TABLE_NAME = c.TABLE_NAME
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcin on rcin.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG and rcin.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rcin.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccuin on rcin.UNIQUE_CONSTRAINT_CATALOG = ccuin.CONSTRAINT_CATALOG and rcin.UNIQUE_CONSTRAINT_SCHEMA = ccuin.CONSTRAINT_SCHEMA and rcin.UNIQUE_CONSTRAINT_NAME = ccuin.CONSTRAINT_NAME
left join (
select syst.name, sysi.name as iname, sysc.name as cname
from sys.tables syst
inner join sys.indexes sysi on sysi.object_id = syst.object_id and sysi.is_unique = 1 and sysi.type <> 1
inner join sys.index_columns sysic on sysi.object_id = sysic.object_id and sysi.index_id = sysic.index_id
inner join sys.columns sysc on sysic.object_id = sysc.object_id and sysic.column_id = sysc.column_id
) unicidx on unicidx.name = c.TABLE_NAME and unicidx.cname = c.COLUMN_NAME
WHERE c.TABLE_NAME = '" + table + "' order by c.ORDINAL_POSITION;"
);
foreach (DataRow row in columns)
{
if (!ti.columns.ContainsKey(row["column_name"].ToString()))
{
column_info fk = new column_info()
{
name = row["column_name"].ToString(),
data_type = row["DATA_TYPE"].ToString(),
char_length = String.IsNullOrEmpty(row["CHARACTER_MAXIMUM_LENGTH"].ToString()) ? 0 : int.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString()),
table_info = ti
};
if (!String.IsNullOrEmpty(row["CONSTRAINT_TYPE"].ToString()))
{
fk.constraint_type = row["CONSTRAINT_TYPE"].ToString();
if (row["CONSTRAINT_TYPE"].ToString() == "PRIMARY KEY")
{
ti.pk = row["column_name"].ToString();
}
}
if (!String.IsNullOrEmpty(row["fk_column"].ToString()) && !String.IsNullOrEmpty(row["fk_table"].ToString()))
{
ti.links_in.Add(row["fk_table"].ToString(), new List <string>()
{
row["fk_column"].ToString()
}); //fk.links_in.Add(row["fk_column"].ToString(), new List<string>() { row["fk_table"].ToString() });
}
if (!String.IsNullOrEmpty(row["in_column"].ToString()) && !String.IsNullOrEmpty(row["in_table"].ToString()))
{
fk.tbl_out = row["in_table"].ToString();
fk.col_out = row["in_column"].ToString();
if (!ti.links_out.ContainsKey(row["in_table"].ToString()))
{
ti.links_out.Add(row["in_table"].ToString(), new List <string>());
}
ti.links_out[row["in_table"].ToString()].Add(row["column_name"].ToString());
}
if (row["IS_NULLABLE"].ToString() == "YES")
{
fk.is_nullable = true;
}
if (row["isidentity"].ToString() == "1")
{
fk.is_identity = true;
}
if (!row.IsNull("iname"))
{
fk.is_name = true;
ti.names.Add(fk);
}
ti.columns.Add(row["column_name"].ToString(), fk);
}
else if (!ti.links_in.ContainsKey(row["fk_table"].ToString()))
{
ti.links_in.Add(row["fk_table"].ToString(), new List <string>()
{
row["fk_column"].ToString()
});
}
else if (!ti.links_in[row["fk_table"].ToString()].Contains(row["fk_column"].ToString()))
{
ti.links_in[row["fk_table"].ToString()].Add(row["fk_column"].ToString());
}
if (row["in_table"].ToString() == table)
{
ti.tree = row["column_name"].ToString(); // пока так
}
}
if (!tables[conn].ContainsKey(table))
{
tables[conn].Add(table, ti); //хз
}
return(tables[conn][table]);
}
