/// <summary>
/// Downloads all asset bundles from the specified URL.
/// </summary>
/// <returns>
/// Info class instance or null, if an error occured.
/// </returns>
/// <param name='urls'>
/// URLs to the same files from different places (mirrors).
/// </param>
public static IndexInfo DownloadAll(params string[] urls)
{
//Debug.Log("DownloadAll - Started");
IndexInfo ret = IndexInfo.GetInstance(urls);
//Debug.Log("DownloadAll - Finished");
return(ret);
}
protected override void fillFromDatabase(List <TableInfo> tables, bool fillRowCount, int maxRecurseDepth)
{
if (tables.Exists(t => {
return(t.TableName.ToUpper().Trim() == TableName.ToUpper().Trim());
}))
{
// this table has already been added and loaded.
return;
}
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
// first determine options for the table itself
DataTable dtTable = null;
try {
dtTable = dm.Read(String.Format("show create table {0}", FullTableName));
} catch (Exception ex) {
if (ex.Message.ToLower().Contains(" doesn't exist"))
{
// this table isn't defined -- a FK is pointing at a non-existent table.
// just jump out and continue processing.
return;
}
}
if (dtTable.Rows.Count > 0)
{
string createTable = dtTable.Rows[0]["Create Table"].ToString();
Regex reEngine = new Regex(@"ENGINE=([^\s]*)", RegexOptions.Multiline);
Match m = reEngine.Match(createTable);
if (m.Success)
{
Engine = m.Groups[1].Value;
}
Regex reCharset = new Regex(@"CHARSET=([^\s]*)", RegexOptions.Multiline);
m = reCharset.Match(createTable);
if (m.Success)
{
CharacterSet = m.Groups[1].Value;
}
Regex reCollate = new Regex(@"COLLATE=([^\s]*)", RegexOptions.Multiline);
m = reCollate.Match(createTable);
if (m.Success)
{
Collation = m.Groups[1].Value;
}
Regex rePkType = new Regex(@"USING ([^\s]*) ", RegexOptions.Multiline);
m = rePkType.Match(createTable);
if (m.Success)
{
PrimaryKeySortType = m.Groups[1].Value;
}
}
// now fill in the fields
DataTable dt = dm.Read(String.Format("describe {0};", FullTableName));
Fields = new List <FieldInfo>();
foreach (DataRow dr in dt.Rows)
{
// create a field object based on the datarow information
FieldInfo fi = null;
Match m = Regex.Match(dr["Type"].ToString(), @"([^(]*)(?:\(([0-9]*),?([0-9]*)?\))?");
int minlength = 0;
int maxlength = 0;
int scale = 0;
int precision = 0;
string fieldType = null;
if (m.Success)
{
fieldType = m.Groups[1].Value.ToString().ToLower();
precision = maxlength = Toolkit.ToInt32(m.Groups[2].Value.ToString(), 0);
scale = Toolkit.ToInt32(m.Groups[3].Value.ToString(), 0);
}
string fieldName = dr["Field"].ToString().ToUpper();
bool nullable = dr["Null"].ToString().ToLower().Trim() != "no";
bool primaryKey = dr["Key"].ToString().ToLower().Trim() == "pri";
// TODO: Mysql's describe statement does not kick out character set info!
// How are we going to get that for fields that have different char set
// than the default for its table?
bool unsigned = dr["Type"].ToString().ToLower().Contains("unsigned");
bool zerofill = dr["Type"].ToString().ToLower().Contains("zerofill");
bool autoIncrement = dr["Extra"].ToString().ToLower().Contains("auto_increment");
bool createdDate = fieldName == "CREATED" || fieldName == "CREATED_AT" || fieldName == "CREATED_DATE";
bool createdBy = fieldName == "CREATED_BY";
bool modifiedDate = fieldName == "MODIFIED" || fieldName == "MODIFIED_AT" || fieldName == "MODIFIED_DATE";
bool modifiedBy = fieldName == "MODIFIED_BY";
bool ownedDate = fieldName == "OWNED" || fieldName == "OWNED_AT" || fieldName == "OWNED_DATE";
bool ownedBy = fieldName == "OWNED_BY";
fi = new SqliteFieldInfo(this);
switch (fieldType)
{
case "int":
fi.DataType = typeof(int);
break;
case "bigint":
case "long":
fi.DataType = typeof(long);
break;
case "varchar":
case "nvarchar":
fi.DataType = typeof(string);
break;
case "char":
case "nchar":
minlength = maxlength;
fi.DataType = typeof(string);
break;
case "datetime2":
case "datetime":
fi.DataType = typeof(DateTime);
break;
case "float":
fi.DataType = typeof(float);
break;
case "decimal":
fi.DataType = typeof(Decimal);
break;
case "double":
fi.DataType = typeof(Double);
break;
default:
throw new InvalidOperationException(getDisplayMember("fillFromDatabase{default}", "Fields of type '{0}' are not supported.\nYou must change the type for {1}.{2} before continuing or add the code to support that type to GrinGlobal.DatabaseInspector.\nSupported types are:\nint\nvarchar\nchar\nnvarchar\nnchar\ndatetime\nfloat\ndouble\ndecimal\n", fieldType, TableName, fieldName));
}
fi.AddOptions("name", dr["Field"].ToString(),
"nullable", nullable,
"minlength", minlength,
"maxlength", maxlength,
"scale", scale,
"precision", precision,
"primarykey", primaryKey,
"unsigned", unsigned,
"zerofill", zerofill,
"autoincrement", autoIncrement,
"createdby", createdBy,
"createdat", createdDate,
"modifiedby", modifiedBy,
"modifiedat", modifiedDate,
"ownedby", ownedBy,
"ownedat", ownedDate);
object defaultVal = dr["default"];
if (defaultVal != null)
{
if (defaultVal == DBNull.Value)
{
fi.AddOptions("defaultvalue", "{DBNull.Value}");
}
else if (defaultVal.GetType().ToString().ToLower() == "system.string")
{
fi.AddOptions("defaultvalue", @"""" + defaultVal + @"""");
}
else
{
fi.AddOptions("defaultvalue", defaultVal);
}
}
Fields.Add(fi);
}
// now get index information
DataTable dtTemp = dm.Read(String.Format("SHOW INDEX FROM {0}", FullTableName));
string prevKeyName = null;
IndexInfo idx = null;
foreach (DataRow drTemp in dtTemp.Rows)
{
bool unique = drTemp["non_unique"].ToString().Trim() == "0";
string keyName = drTemp["key_name"].ToString();
string typeName = drTemp["index_type"].ToString();
// int sequenceInIndex = Toolkit.ToInt32(drTemp["seq_in_index"].ToString(), 0);
if (keyName == "PRIMARY")
{
// skip. primary keys are built as part of create table.
}
else
{
if (keyName != prevKeyName)
{
// new index.
idx = IndexInfo.GetInstance(this);
idx.TableName = TableName;
idx.IndexName = keyName;
if (unique)
{
idx.IndexKind = "UNIQUE";
}
idx.IndexType = typeName;
Indexes.Add(idx);
prevKeyName = keyName;
}
FieldInfo fi = Fields.Find(fi2 => {
return(fi2.Name.ToLower() == drTemp["column_name"].ToString().ToLower());
});
if (fi != null)
{
idx.Fields.Add(fi);
}
}
}
// we've defined the table as well as all its fields and indexes.
// add it to the list BEFORE resolving constraints.
// this lets us sidestep recursion black holes due to circular referential integrity constraints
// (aka self-referential or t1 -> t2 -> t1, or t1 -> t2 -> t3 -> t1
if (!tables.Exists(t => {
return(this.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(this);
}
// now get constraint information
// (this is already in the dtTable from CREATE TABLE, just parse it here)
if (dtTable.Rows.Count > 0)
{
string createTable = dtTable.Rows[0]["Create Table"].ToString();
// CONSTRAINT `FK_ACC_PI` FOREIGN KEY (`PIVOL`) REFERENCES `pi` (`PIVOL`),
// name src fields tgt tgt fields
Regex reConstraint = new Regex(@"CONSTRAINT[\s]*([^\s]*) FOREIGN KEY \(([^\s,]*)*\) REFERENCES ([^\s]*) \(([^\s,]*)*\)", RegexOptions.Multiline);
MatchCollection mc = reConstraint.Matches(createTable);
if (mc.Count > 0)
{
foreach (Match m in mc)
{
ConstraintInfo ci = new SqliteConstraintInfo(this);
ci.ConstraintName = m.Groups[1].Value.Replace("`", "").ToUpper();
ci.TableName = this.TableName;
ci.ReferencesTableName = m.Groups[3].Value.Replace("`", "").ToUpper();
// determine which field(s) this foreign key points at
TableInfo tiRef = null;
if (this.TableName.ToUpper().Trim() == ci.ReferencesTableName.ToUpper().Trim())
{
// self-referential.
tiRef = this;
}
else
{
// see if it's pointing at a table we've already resolved
foreach (TableInfo ti in tables)
{
if (ti.TableName.ToUpper().Trim() == ci.ReferencesTableName.ToUpper().Trim())
{
tiRef = ti;
break;
}
}
// see if
}
if (tiRef == null)
{
// the current table references one that isn't loaded yet.
// load it (and all of its referenced tables)
tiRef = new SqliteTableInfo(DataConnectionSpec);
tiRef.Fill(SchemaName, ci.ReferencesTableName, tables, fillRowCount, maxRecurseDepth - 1);
if (!tables.Exists(te => {
return(te.TableName.ToUpper().Trim() == tiRef.TableName.ToUpper().Trim());
}))
{
tables.Add(tiRef);
}
}
foreach (Capture cap2 in m.Groups[4].Captures)
{
if (cap2.Length > 0)
{
string refFieldName = cap2.Value.Replace("`", "").ToUpper();
foreach (FieldInfo fi2 in tiRef.Fields)
{
if (fi2.Name.ToUpper().Trim() == refFieldName.ToUpper().Trim())
{
ci.ReferencesFields.Add(fi2);
break;
}
}
}
}
// tell each field in our table if they're a foreign key elsewhere
for (int i = 0; i < m.Groups[2].Captures.Count; i++)
{
Capture cap = m.Groups[2].Captures[i];
if (cap.Length > 0)
{
string srcFieldName = cap.Value.Replace("`", "").ToUpper();
foreach (FieldInfo fi in this.Fields)
{
if (fi.Name.ToUpper().Trim() == srcFieldName.ToUpper().Trim())
{
fi.IsForeignKey = true;
fi.ForeignKeyTableName = ci.ReferencesTableName;
if (ci.ReferencesFields.Count > i)
{
fi.ForeignKeyFieldName = ci.ReferencesFields[i].Name;
}
ci.SourceFields.Add(fi);
break;
}
}
}
}
Constraints.Add(ci);
}
}
}
// fill in rowcount
if (fillRowCount)
{
RowCount = Toolkit.ToInt32(dm.ReadValue(String.Format("select count(1) from {0}", FullTableName)), 0);
}
}
}
protected override void fillFromDatabase(List <TableInfo> tables, bool fillRowCount, int maxRecurseDepth)
{
if (tables.Exists(t => {
return(t.TableName.ToUpper().Trim() == TableName.ToUpper().Trim());
}))
{
// this table has already been added and loaded.
return;
}
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
// now fill in the fields
DataTable dt = dm.Read("select * from information_schema.columns where table_name = '" + TableName + "' order by ORDINAL_POSITION");
Fields = new List <FieldInfo>();
foreach (DataRow dr in dt.Rows)
{
// create a field object based on the datarow information
FieldInfo fi = FieldInfo.GetInstance(this);
fi.TableName = dr["table_name"].ToString();
fi.Name = dr["column_name"].ToString();
string fieldname = fi.Name.ToUpper().Trim();
string datatype = dr["data_type"].ToString().ToUpper().Trim();
// fi.CharacterSet = dr["character_set_name"].ToString();
switch (datatype)
{
case "INT":
fi.DataType = typeof(int);
break;
case "NVARCHAR":
case "NCHAR":
case "VARCHAR":
case "CHAR":
fi.DataType = typeof(string);
break;
case "DECIMAL":
case "NUMERIC":
fi.DataType = typeof(decimal);
break;
case "DATETIME":
case "DATETIME2":
fi.DataType = typeof(DateTime);
break;
case "BIGINT":
fi.DataType = typeof(long);
break;
default:
throw new NotImplementedException(getDisplayMember("fillFromDatabase{default}", "Datatype='{0}' not implemented in SqlServerTableInfo.fillFromDatabase.", datatype));
}
//fi.DbType = null;
object defaultVal = dr["column_default"];
if (defaultVal != null)
{
if (defaultVal == DBNull.Value || defaultVal.ToString() == "(NULL)")
{
fi.DefaultValue = "{DBNull.Value}";
//} else if (defaultVal.GetType().ToString().ToLower() == "system.string") {
// fi.DefaultValue = @"""" + defaultVal.ToString().Replace("((", "").Replace("))", ""); +@"""";
}
else
{
fi.DefaultValue = defaultVal.ToString().Replace("((", "").Replace("))", "");
fi.DefaultValue = fi.DefaultValue.ToString().Replace("('", "").Replace("')", "");
}
}
int ret = Toolkit.ToInt32(dm.ReadValue("SELECT COLUMNPROPERTY( OBJECT_ID(:tablename),:columnname,'IsIdentity')", new DataParameters(":tablename", fi.TableName, ":columnname", fi.Name)), 0);
fi.IsAutoIncrement = ret == 1;
//fi.FieldType = null;
//fi.ForeignKeyFieldName = null;
//fi.ForeignKeyTableName = null;
//fi.IsAutoIncrement = null;
fi.IsCreatedDate = fieldname == "CREATED" || fieldname == "CREATED_AT" || fieldname == "CREATED_DATE";
fi.IsCreatedBy = fieldname == "CREATED_BY";
//fi.IsForeignKey = null;
fi.IsModifiedDate = fieldname == "MODIFIED" || fieldname == "MODIFIED_AT" || fieldname == "MODIFIED_DATE";
fi.IsModifiedBy = fieldname == "MODIFIED_BY";
fi.IsNullable = dr["IS_NULLABLE"].ToString().ToUpper().Trim() == "YES";
fi.IsOwnedDate = fieldname == "OWNED" || fieldname == "OWNED_AT" || fieldname == "OWNED_DATE";
fi.IsOwnedBy = fieldname == "OWNED_BY";
fi.IsPrimaryKey = 0 < Toolkit.ToInt32(dm.ReadValue(@"
select count(1)
from
information_schema.table_constraints tc
inner join information_schema.constraint_column_usage ccu
on tc.constraint_name = ccu.constraint_name
where
tc.table_name = :tableName
and tc.constraint_type = 'PRIMARY KEY'
and ccu.column_name = :fieldName
", new DataParameters(":tableName", fi.TableName, ":fieldName", fi.Name)), 0);
//fi.IsUnsigned = null;
//fi.IsZeroFill = null;
fi.MaxLength = Toolkit.ToInt32(dr["character_maximum_length"], 0);
//fi.MinLength = null;
fi.Precision = Toolkit.ToInt32(dr["numeric_precision"], 0);
//fi.Purpose = null;
fi.Scale = Toolkit.ToInt32(dr["numeric_scale"], 0);
//fi.Value = null;
Fields.Add(fi);
}
if (dt.Rows.Count > 0)
{
// now get index information
DataSet dsHelpInfo = new DataSet();
dsHelpInfo = dm.Read("sp_help [" + TableName + "]", dsHelpInfo, "schema", null);
DataTable dtIndex = null;
// index table isn't always spit out int he same order (if no idexes, no table)
// so we have to probe for it by column names.
foreach (DataTable dtHelp in dsHelpInfo.Tables)
{
if (dtHelp.Columns.Contains("index_name") && dtHelp.Columns.Contains("index_description"))
{
dtIndex = dtHelp;
break;
}
}
if (dtIndex != null)
{
foreach (DataRow drIndex in dtIndex.Rows)
{
string indexName = drIndex["index_name"].ToString();
string desc = drIndex["index_description"].ToString().ToUpper().Trim();
string[] fieldNames = drIndex["index_keys"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
// int sequenceInIndex = Toolkit.ToInt32(drTemp["seq_in_index"].ToString(), 0);
if (indexName.ToUpper().Trim().StartsWith("PK"))
{
// skip. primary keys are built as part of create table.
}
else
{
// new index.
IndexInfo idx = IndexInfo.GetInstance(this);
idx.TableName = TableName;
idx.IndexName = indexName;
if (desc.Contains("UNIQUE"))
{
idx.IndexKind = "UNIQUE";
}
idx.IndexType = null;
Indexes.Add(idx);
foreach (string fn in fieldNames)
{
string fn1 = fn.ToUpper().Trim();
foreach (FieldInfo fi2 in Fields)
{
string fn2 = fi2.Name.ToUpper().Trim();
if (fn2 == fn1)
{
idx.Fields.Add(fi2);
}
}
}
}
}
}
// fill in rowcount
if (fillRowCount)
{
RowCount = Toolkit.ToInt32(dm.ReadValue("select count(1) from [" + TableName + "]"), 0);
}
// we've defined the table as well as all its fields and indexes.
// add it to the list BEFORE resolving constraints.
// this lets us sidestep recursion black holes due to circular referential integrity constraints
// (aka self-referential or t1 -> t2 -> t1, or t1 -> t2 -> t3 -> t1
if (!tables.Exists(t => {
return(this.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(this);
}
DataTable dtConstraints = dm.Read(@"
select
tc.TABLE_NAME as source_table,
tc.CONSTRAINT_NAME as source_constraint,
tc2.TABLE_NAME as refers_to_table,
rc.UNIQUE_CONSTRAINT_NAME as refers_to_constraint
from
information_schema.table_constraints tc
inner join information_schema.referential_constraints rc
on tc.constraint_name = rc.constraint_name
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2
on rc.UNIQUE_CONSTRAINT_NAME = tc2.CONSTRAINT_NAME
where
tc.table_name = :tableName
order by
tc.constraint_name" , new DataParameters(":tableName", TableName));
// pull source info
foreach (DataRow drC in dtConstraints.Rows)
{
ConstraintInfo ci = new SqlServerConstraintInfo(this);
ci.ConstraintName = drC["source_constraint"].ToString();
ci.TableName = TableName;
ci.Table = this;
ci.ReferencesTableName = drC["refers_to_table"].ToString();
// needed for lazy evaluation
ci.TableList = tables;
// determine which field(s) this foreign key points at
TableInfo tiRef = null;
if (this.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
// self-referential.
tiRef = this;
}
else
{
// see if it's pointing at a table we've already resolved
foreach (TableInfo ti in tables)
{
if (ti.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
tiRef = ti;
break;
}
}
}
if (tiRef == null)
{
// if (maxRecurseDepth > 0) {
// the current table references one that isn't loaded yet.
// load it (and all of its referenced tables)
if (!tables.Exists(te => {
return(te.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim());
}))
{
tiRef = new SqlServerTableInfo(DataConnectionSpec);
tiRef.Fill(SchemaName, ci.ReferencesTableName, tables, fillRowCount, maxRecurseDepth - 1);
if (!tables.Exists(t => {
return(tiRef.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(tiRef);
}
}
//} else {
// // there's more FK relationships, but they specified not to follow them.
// // we already filled in the ci.ReferencesTableName so we can do lazy evaluation on it later if need be.
// // the lazy evaluation will essentially redo everything we did above, but get past this step to fill the constraint info.
// Constraints.Add(ci);
// continue;
//}
}
ci.ReferencesTable = tiRef;
DataTable dtRefersTo = dm.Read(@"
select
*
from
information_schema.constraint_column_usage
where
constraint_name = :constraintName
order by
column_name
", new DataParameters(":constraintName", drC["refers_to_constraint"].ToString()));
foreach (DataRow drRef in dtRefersTo.Rows)
{
string refFieldName = drRef["COLUMN_NAME"].ToString().ToLower().Trim();
foreach (FieldInfo fiRef in tiRef.Fields)
{
if (fiRef.Name.ToLower().Trim() == refFieldName)
{
ci.ReferencesFields.Add(fiRef);
break;
}
}
}
DataTable dtSource = dm.Read(@"
select
*
from
information_schema.constraint_column_usage
where
constraint_name = :constraintName
order by
column_name
", new DataParameters(":constraintName", drC["source_constraint"].ToString()));
foreach (DataRow drSrc in dtSource.Rows)
{
string srcFieldName = drSrc["COLUMN_NAME"].ToString().ToUpper().Trim();
foreach (FieldInfo fi in this.Fields)
{
if (fi.Name.ToUpper().Trim() == srcFieldName)
{
ci.SourceFields.Add(fi);
fi.IsForeignKey = true;
fi.ForeignKeyConstraintName = ci.ConstraintName;
fi.ForeignKeyTableName = ci.ReferencesTableName;
fi.ForeignKeyTable = ci.ReferencesTable;
if (ci.ReferencesFields.Count > 0)
{
fi.ForeignKeyFieldName = ci.ReferencesFields[0].Name;
}
break;
}
}
}
Constraints.Add(ci);
}
}
}
}
protected override void fillFromDatabase(List <TableInfo> tables, bool fillRowCount, int maxRecurseDepth)
{
if (tables.Exists(t => {
return(t.TableName.ToUpper().Trim() == TableName.ToUpper().Trim());
}))
{
// this table has already been added and loaded.
return;
}
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
// now fill in the fields
DataTable dt = dm.Read("select * from information_schema.columns where table_name = '" + TableName + "' order by ORDINAL_POSITION");
Fields = new List <FieldInfo>();
foreach (DataRow dr in dt.Rows)
{
// create a field object based on the datarow information
FieldInfo fi = FieldInfo.GetInstance(this);
fi.TableName = dr["table_name"].ToString();
fi.Name = dr["column_name"].ToString();
string fieldname = fi.Name.ToUpper().Trim();
string datatype = dr["data_type"].ToString().ToUpper().Trim();
// fi.CharacterSet = dr["character_set_name"].ToString();
switch (datatype)
{
case "INTEGER":
fi.DataType = typeof(int);
break;
case "CHARACTER":
case "CHARACTER VARYING":
case "TEXT":
fi.DataType = typeof(string);
break;
case "DECIMAL":
case "NUMERIC":
fi.DataType = typeof(decimal);
break;
case "DATETIME":
case "DATETIME2":
case "TIMESTAMP WITHOUT TIME ZONE":
fi.DataType = typeof(DateTime);
break;
case "BIGINT":
fi.DataType = typeof(long);
break;
default:
throw new NotImplementedException(getDisplayMember("fillFromDatabase", "Datatype='{0}' not implemented in PostgreSqlTableInfo.fillFromDatabase.", datatype));
}
//fi.DbType = null;
object defaultVal = dr["column_default"];
if (defaultVal != null)
{
if (defaultVal == DBNull.Value || defaultVal.ToString() == "(NULL)")
{
fi.DefaultValue = "{DBNull.Value}";
//} else if (defaultVal.GetType().ToString().ToLower() == "system.string") {
// fi.DefaultValue = @"""" + defaultVal.ToString().Replace("((", "").Replace("))", ""); +@"""";
}
else
{
fi.DefaultValue = defaultVal.ToString().Replace("((", "").Replace("))", "");
fi.DefaultValue = fi.DefaultValue.ToString().Replace("('", "").Replace("')", "");
}
}
fi.IsAutoIncrement = dr["column_default"].ToString().ToLower().Contains("nextval(");
//fi.FieldType = null;
//fi.ForeignKeyFieldName = null;
//fi.ForeignKeyTableName = null;
//fi.IsAutoIncrement = null;
fi.IsCreatedDate = fieldname == "CREATED" || fieldname == "CREATED_AT" || fieldname == "CREATED_DATE";
fi.IsCreatedBy = fieldname == "CREATED_BY";
//fi.IsForeignKey = null;
fi.IsModifiedDate = fieldname == "MODIFIED" || fieldname == "MODIFIED_AT" || fieldname == "MODIFIED_DATE";
fi.IsModifiedBy = fieldname == "MODIFIED_BY";
fi.IsNullable = dr["IS_NULLABLE"].ToString().ToUpper().Trim() == "YES";
fi.IsOwnedDate = fieldname == "OWNED" || fieldname == "OWNED_AT" || fieldname == "OWNED_DATE";
fi.IsOwnedBy = fieldname == "OWNED_BY";
fi.IsPrimaryKey = 0 < Toolkit.ToInt32(dm.ReadValue(@"
select count(1)
from
information_schema.table_constraints tc
inner join information_schema.constraint_column_usage ccu
on tc.constraint_name = ccu.constraint_name
where
tc.table_name = :tableName
and tc.constraint_type = 'PRIMARY KEY'
and ccu.column_name = :fieldName
", new DataParameters(":tableName", fi.TableName, ":fieldName", fi.Name)), 0);
//fi.IsUnsigned = null;
//fi.IsZeroFill = null;
fi.MaxLength = Toolkit.ToInt32(dr["character_maximum_length"], 0);
//fi.MinLength = null;
fi.Precision = Toolkit.ToInt32(dr["numeric_precision"], 0);
//fi.Purpose = null;
fi.Scale = Toolkit.ToInt32(dr["numeric_scale"], 0);
//fi.Value = null;
Fields.Add(fi);
}
if (dt.Rows.Count > 0)
{
// now get index information. See http://www.alberton.info/postgresql_meta_info.html
var dtIndex = dm.Read(@"
select
indexname,
indexdef
from
pg_indexes
where
tablename = :tableName
", new DataParameters(":tableName", TableName, DbType.String));
if (dtIndex != null)
{
foreach (DataRow drIndex in dtIndex.Rows)
{
string desc = drIndex["indexdef"].ToString().ToUpper().Trim();
if (!desc.Contains(TableName.ToUpper() + "_PKEY"))
{
string indexName = drIndex["indexname"].ToString();
var idx = IndexInfo.GetInstance(this);
idx.TableName = TableName;
idx.IndexName = indexName;
if (desc.Contains("CREATE UNIQUE INDEX"))
{
idx.IndexKind = "UNIQUE";
}
var dtIndexFieldOrdinals = dm.Read(@"
SELECT
indkey as field_ordinals
FROM
pg_class,
pg_index
WHERE
pg_class.oid = pg_index.indexrelid
AND pg_class.oid IN (
SELECT
indexrelid
FROM
pg_index, pg_class
WHERE
pg_class.relname=:tableName
AND pg_class.oid=pg_index.indrelid
-- AND indisunique != 't'
AND indisprimary != 't'
)
AND relname = :indexName
", new DataParameters(":tableName", TableName, DbType.String, ":indexName", indexName, DbType.String));
if (dtIndexFieldOrdinals.Rows.Count > 0)
{
var ords = Toolkit.ToIntList(dtIndexFieldOrdinals.Rows[0]["field_ordinals"].ToString());
foreach (var ord in ords)
{
var dtFieldName = dm.Read(@"
SELECT
a.attname as field_name
FROM
pg_index c
INNER JOIN pg_class i
on c.indexrelid = i.oid
LEFT JOIN pg_class t
ON c.indrelid = t.oid
LEFT JOIN pg_attribute a
ON a.attrelid = t.oid
AND a.attnum = ANY(indkey)
WHERE
t.relname = :tableName
AND i.relname = :indexName
AND a.attnum = :ord
", new DataParameters(":tableName", TableName, DbType.String, ":indexName", indexName, DbType.String, ":ord", ord, DbType.Int32));
foreach (DataRow drFieldName in dtFieldName.Rows)
{
var fn = drFieldName["field_name"].ToString().ToUpper().Trim();
foreach (FieldInfo fi2 in Fields)
{
if (fi2.Name.ToUpper().Trim() == fn)
{
idx.Fields.Add(fi2);
break;
}
}
}
}
}
Indexes.Add(idx);
}
//string[] fieldNames = drIndex["index_keys"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
//// int sequenceInIndex = Toolkit.ToInt32(drTemp["seq_in_index"].ToString(), 0);
//if (indexName.ToUpper().Trim().StartsWith("PK")) {
// // skip. primary keys are built as part of create table.
//} else {
// // new index.
// IndexInfo idx = IndexInfo.GetInstance(this);
// idx.TableName = TableName;
// idx.IndexName = indexName;
// if (desc.Contains("UNIQUE")) {
// idx.IndexKind = "UNIQUE";
// }
// idx.IndexType = null;
// Indexes.Add(idx);
// foreach (string fn in fieldNames) {
// string fn1 = fn.ToUpper().Trim();
// foreach (FieldInfo fi2 in Fields) {
// string fn2 = fi2.Name.ToUpper().Trim();
// if (fn2 == fn1) {
// idx.Fields.Add(fi2);
// }
// }
// }
//}
}
}
// fill in rowcount
if (fillRowCount)
{
RowCount = Toolkit.ToInt32(dm.ReadValue("select count(1) from [" + TableName + "]"), 0);
}
// we've defined the table as well as all its fields and indexes.
// add it to the list BEFORE resolving constraints.
// this lets us sidestep recursion black holes due to circular referential integrity constraints
// (aka self-referential or t1 -> t2 -> t1, or t1 -> t2 -> t3 -> t1
if (!tables.Exists(t => {
return(this.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(this);
}
DataTable dtConstraints = dm.Read(@"
select
tc.TABLE_NAME as source_table,
tc.CONSTRAINT_NAME as source_constraint,
tc2.TABLE_NAME as refers_to_table,
rc.UNIQUE_CONSTRAINT_NAME as refers_to_constraint
from
information_schema.table_constraints tc
inner join information_schema.referential_constraints rc
on tc.constraint_name = rc.constraint_name
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2
on rc.UNIQUE_CONSTRAINT_NAME = tc2.CONSTRAINT_NAME
where
tc.table_name = :tableName
order by
tc.constraint_name" , new DataParameters(":tableName", TableName));
// pull source info
foreach (DataRow drC in dtConstraints.Rows)
{
ConstraintInfo ci = new PostgreSqlConstraintInfo(this);
ci.ConstraintName = drC["source_constraint"].ToString();
ci.TableName = TableName;
ci.Table = this;
ci.ReferencesTableName = drC["refers_to_table"].ToString();
// needed for lazy evaluation
ci.TableList = tables;
// determine which field(s) this foreign key points at
TableInfo tiRef = null;
if (this.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
// self-referential.
tiRef = this;
}
else
{
// see if it's pointing at a table we've already resolved
foreach (TableInfo ti in tables)
{
if (ti.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
tiRef = ti;
break;
}
}
}
if (tiRef == null)
{
// if (maxRecurseDepth > 0) {
// the current table references one that isn't loaded yet.
// load it (and all of its referenced tables)
if (!tables.Exists(te => {
return(te.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim());
}))
{
tiRef = new PostgreSqlTableInfo(DataConnectionSpec);
tiRef.Fill(SchemaName, ci.ReferencesTableName, tables, fillRowCount, maxRecurseDepth - 1);
if (!tables.Exists(t => {
return(tiRef.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(tiRef);
}
}
//} else {
// // there's more FK relationships, but they specified not to follow them.
// // we already filled in the ci.ReferencesTableName so we can do lazy evaluation on it later if need be.
// // the lazy evaluation will essentially redo everything we did above, but get past this step to fill the constraint info.
// Constraints.Add(ci);
// continue;
//}
}
ci.ReferencesTable = tiRef;
DataTable dtRefersTo = dm.Read(@"
select
*
from
information_schema.constraint_column_usage
where
constraint_name = :constraintName
order by
column_name
", new DataParameters(":constraintName", drC["refers_to_constraint"].ToString()));
foreach (DataRow drRef in dtRefersTo.Rows)
{
string refFieldName = drRef["COLUMN_NAME"].ToString().ToLower().Trim();
foreach (FieldInfo fiRef in tiRef.Fields)
{
if (fiRef.Name.ToLower().Trim() == refFieldName)
{
ci.ReferencesFields.Add(fiRef);
break;
}
}
}
DataTable dtSource = dm.Read(@"
select
*
from
information_schema.key_column_usage
where
constraint_name = :constraintName
order by
column_name
", new DataParameters(":constraintName", drC["source_constraint"].ToString()));
foreach (DataRow drSrc in dtSource.Rows)
{
string srcFieldName = drSrc["COLUMN_NAME"].ToString().ToUpper().Trim();
foreach (FieldInfo fi in this.Fields)
{
if (fi.Name.ToUpper().Trim() == srcFieldName)
{
ci.SourceFields.Add(fi);
fi.IsForeignKey = true;
fi.ForeignKeyConstraintName = ci.ConstraintName;
fi.ForeignKeyTableName = ci.ReferencesTableName;
fi.ForeignKeyTable = ci.ReferencesTable;
if (ci.ReferencesFields.Count > 0)
{
fi.ForeignKeyFieldName = ci.ReferencesFields[0].Name;
}
break;
}
}
}
Constraints.Add(ci);
}
}
}
}
protected override void fillFromDatabase(List <TableInfo> tables, bool fillRowCount, int maxRecurseDepth)
{
if (tables.Exists(t => {
return(t.TableName.ToUpper().Trim() == TableName.ToUpper().Trim());
}))
{
// this table has already been added and loaded.
return;
}
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
// load column info
DataTable dtCols = dm.Read(@"
SELECT
*
FROM
ALL_TAB_COLUMNS
WHERE
OWNER = :owner
AND TABLE_NAME = :tablename
ORDER BY
COLUMN_ID
",
new DataParameters(":owner", SchemaName.ToUpper(), ":tablename", this.TableName.ToUpper()));
foreach (DataRow drCol in dtCols.Rows)
{
string columnName = drCol["COLUMN_NAME"].ToString();
string dataType = drCol["DATA_TYPE"].ToString();
int length = Toolkit.ToInt32(drCol["DATA_LENGTH"], 0);
int? precision = Toolkit.ToInt32(drCol["DATA_PRECISION"], null);
int? scale = Toolkit.ToInt32(drCol["DATA_SCALE"], null);
bool nullable = drCol["NULLABLE"].ToString() == "Y";
var defaultValue = drCol["DATA_DEFAULT"];
switch (dataType.ToUpper())
{
case "VARCHAR":
case "VARCHAR2":
case "CHAR":
case "CLOB":
// for string fields, we should use the char_length value instead of data_length
// because data_length measures bytes instead of chars (unicode is typically 2 bytes / char)
length = Toolkit.ToInt32(drCol["CHAR_LENGTH"], 0);
break;
}
FieldInfo fi = FieldInfo.GetInstance(this);
fi.Name = columnName;
fi.TableName = this.TableName;
fi.IsPrimaryKey = 0 < Toolkit.ToInt32(dm.ReadValue(@"
SELECT
COUNT(1) as COUNT
FROM
ALL_CONSTRAINTS AC INNER JOIN ALL_CONS_COLUMNS ACC
ON AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
WHERE
AC.TABLE_NAME = :tableName AND AC.CONSTRAINT_TYPE = 'P'
AND ACC.COLUMN_NAME = :fieldName
", new DataParameters(":tableName", fi.TableName.ToUpper(), ":fieldName", fi.Name.ToUpper())), 0);
// NOTE: assumes existence of a sequence for this table implies the primary key is an auto increment
if (fi.IsPrimaryKey)
{
fi.IsAutoIncrement = 0 < Toolkit.ToInt32(dm.ReadValue(@"
SELECT
COUNT(1) AS COUNT
FROM
ALL_SEQUENCES
WHERE
SEQUENCE_NAME = :seqName
", new DataParameters(":seqName", "SQ_" + fi.TableName.ToUpper())), 0);
}
fi.Precision = (precision == null ? 0 : (int)precision);
fi.Scale = (scale == null ? 0 : (int)scale);
fi.DefaultValue = defaultValue;
fi.MaxLength = length;
fi.IsNullable = nullable;
fi.IsCreatedDate = columnName == "CREATED_DATE";
fi.IsCreatedBy = columnName == "CREATED_BY";
fi.IsModifiedDate = columnName == "MODIFIED_DATE";
fi.IsModifiedBy = columnName == "MODIFIED_BY";
fi.IsOwnedBy = columnName == "OWNED_BY";
fi.IsOwnedDate = columnName == "OWNED_DATE";
switch (dataType)
{
case "DATE":
fi.DataType = typeof(DateTime);
fi.MaxLength = 0;
fi.MinLength = 0;
break;
case "VARCHAR2":
case "CHAR":
fi.DataType = typeof(string);
break;
case "CLOB":
fi.MaxLength = -1;
fi.DataType = typeof(string);
break;
case "NUMBER":
if (precision == null)
{
if (scale == null)
{
// assume a built-in precision and scale
fi.DataType = typeof(float);
}
else
{
// assume an integer, as we have scale but no precision
fi.DataType = typeof(int);
fi.Precision = 10;
fi.Scale = (int)scale;
fi.MaxLength = 0;
}
}
else
{
if (scale == null)
{
// assume a whole number, as we have precision but no scale
fi.Precision = (int)precision;
fi.Scale = 0;
fi.MaxLength = 0;
if (fi.Precision > 10)
{
fi.DataType = typeof(decimal);
}
else
{
fi.DataType = typeof(int);
}
}
else
{
if (scale == 0)
{
// assume an integer
fi.Precision = (int)precision;
fi.Scale = 0;
fi.MaxLength = 0;
if (fi.Precision > 10)
{
fi.DataType = typeof(decimal);
}
else
{
fi.DataType = typeof(int);
}
}
else
{
// assume a decimal
fi.DataType = typeof(decimal);
fi.Precision = (int)precision;
fi.Scale = (int)scale;
fi.MaxLength = 0;
fi.MinLength = 0;
}
}
}
// throw new InvalidOperationException("precision/scale=" + precision + "/" + scale + " not interpretted for oracle type of 'NUMBER'");
break;
default:
throw new NotImplementedException(getDisplayMember("fillFromDatabase", "Datatype='{0}' not implemented in OracleTableInfo.fillFromDatabase.", dataType));
}
this.Fields.Add(fi);
}
// load index info
var dtIndexes = dm.Read(@"
SELECT
*
FROM
ALL_INDEXES
WHERE
OWNER = :owner
AND TABLE_NAME = :tablename
ORDER BY
INDEX_NAME" ,
new DataParameters(":owner", SchemaName.ToUpper(), ":tablename", this.TableName.ToUpper()));
foreach (DataRow drIndex in dtIndexes.Rows)
{
var indexName = drIndex["INDEX_NAME"].ToString();
var tableName = drIndex["TABLE_NAME"].ToString();
var isUnique = drIndex["UNIQUENESS"].ToString() == "UNIQUE";
if (!indexName.ToUpper().StartsWith("PK"))
{
var ii = IndexInfo.GetInstance(this);
ii.TableName = this.TableName;
ii.IndexName = indexName;
if (isUnique)
{
ii.IndexKind = "UNIQUE";
}
var dtIndexFields = dm.Read(@"
SELECT
*
FROM
ALL_IND_COLUMNS
WHERE
INDEX_OWNER = :owner
AND INDEX_NAME = :indexname
ORDER BY
COLUMN_POSITION" ,
new DataParameters(":owner", SchemaName.ToUpper(), ":indexname", indexName.ToUpper()));
foreach (DataRow drIndexField in dtIndexFields.Rows)
{
FieldInfo fiIndex = Fields.Find(f => {
return(f.Name.ToUpper() == drIndexField["COLUMN_NAME"].ToString().ToUpper());
});
if (fiIndex != null)
{
ii.Fields.Add(fiIndex);
}
}
Indexes.Add(ii);
}
}
// we've defined the table as well as all its fields and indexes.
// add it to the list BEFORE resolving constraints.
// this lets us sidestep recursion black holes due to circular referential integrity constraints
// (aka self-referential or t1 -> t2 -> t1, or t1 -> t2 -> t3 -> t1
if (!tables.Exists(t => {
return(this.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(this);
}
// load constraint info
var dtConstraints = dm.Read(@"
SELECT
*
FROM
ALL_CONSTRAINTS
WHERE
OWNER = :owner
AND CONSTRAINT_TYPE = 'R'
AND TABLE_NAME = :tablename
ORDER BY
CONSTRAINT_NAME
",
new DataParameters(":owner", SchemaName.ToUpper(), ":tablename", this.TableName.ToUpper()));
foreach (DataRow drConstraint in dtConstraints.Rows)
{
var ci = ConstraintInfo.GetInstance(this);
ci.ConstraintName = drConstraint["CONSTRAINT_NAME"].ToString();
ci.TableName = this.TableName;
ci.Table = this;
ci.TableList = tables;
var refersConstraintName = drConstraint["R_CONSTRAINT_NAME"].ToString();
var dtRefTable = dm.Read(@"
SELECT
*
FROM
ALL_CONSTRAINTS
WHERE
OWNER = :owner
AND CONSTRAINT_NAME = :referConstraintName
ORDER BY
CONSTRAINT_NAME
",
new DataParameters(":owner", SchemaName.ToUpper(), ":referConstraintName", refersConstraintName.ToUpper()));
if (dtRefTable.Rows.Count > 0)
{
ci.ReferencesTableName = dtRefTable.Rows[0]["TABLE_NAME"].ToString().ToUpper();
// determine which field(s) this foreign key points at
TableInfo tiRef = null;
if (this.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
// self-referential.
tiRef = this;
}
else
{
// see if it's pointing at a table we've already resolved
foreach (TableInfo ti in tables)
{
if (ti.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim())
{
tiRef = ti;
break;
}
}
}
// we now know both the source table and the references table.
// look up fields that are involved in the constraint on both ends.
if (tiRef == null)
{
// if (maxRecurseDepth > 0) {
// the current table references one that isn't loaded yet.
// load it (and all of its referenced tables)
if (!tables.Exists(te => {
return(te.TableName.ToLower().Trim() == ci.ReferencesTableName.ToLower().Trim());
}))
{
tiRef = new OracleTableInfo(DataConnectionSpec);
tiRef.Fill(SchemaName.ToUpper(), ci.ReferencesTableName.ToUpper(), tables, fillRowCount, maxRecurseDepth - 1);
if (!tables.Exists(t => {
return(tiRef.TableName.ToUpper().Trim() == t.TableName.ToUpper().Trim());
}))
{
tables.Add(tiRef);
}
}
//} else {
// // there's more FK relationships, but they specified not to follow them.
// // we already filled in the ci.ReferencesTableName so we can do lazy evaluation on it later if need be.
// // the lazy evaluation will essentially redo everything we did above, but get past this step to fill the constraint info.
// Constraints.Add(ci);
// continue;
//}
}
ci.ReferencesTable = tiRef;
DataTable dtRefersTo = dm.Read(@"
SELECT
*
FROM
ALL_CONS_COLUMNS
WHERE
OWNER = :owner
AND CONSTRAINT_NAME = :refersToConstraint
ORDER BY
POSITION
", new DataParameters(":owner", SchemaName.ToUpper(), ":refersToConstraint", refersConstraintName.ToUpper()));
foreach (DataRow drRef in dtRefersTo.Rows)
{
string refFieldName = drRef["COLUMN_NAME"].ToString().ToLower().Trim();
foreach (FieldInfo fiRef in tiRef.Fields)
{
if (fiRef.Name.ToLower().Trim() == refFieldName)
{
ci.ReferencesFields.Add(fiRef);
break;
}
}
}
// look up source table fields
var dtSourceFields = dm.Read(@"
SELECT
*
FROM
ALL_CONS_COLUMNS
WHERE
OWNER = :owner
AND CONSTRAINT_NAME = :constraintName
ORDER BY
POSITION
",
new DataParameters(":owner", SchemaName.ToUpper(), ":constraintName", ci.ConstraintName.ToUpper()));
foreach (DataRow drSourceField in dtSourceFields.Rows)
{
var name = drSourceField["COLUMN_NAME"].ToString().ToLower().Trim();
var fiSource = this.Fields.Find(f => { return(f.Name.ToLower().Trim() == name); });
if (fiSource != null)
{
ci.SourceFields.Add(fiSource);
ci.SourceFieldNames.Add(name);
fiSource.IsForeignKey = true;
fiSource.ForeignKeyConstraintName = ci.ConstraintName;
fiSource.ForeignKeyTableName = ci.ReferencesTableName;
fiSource.ForeignKeyTable = ci.ReferencesTable;
if (ci.ReferencesFields.Count > 0)
{
fiSource.ForeignKeyFieldName = ci.ReferencesFields[0].Name;
}
}
}
Constraints.Add(ci);
}
}
}
}