public void AddColumn(string tableName, DbColumnInfo columnInfo)
{
#region QUERY
const string query = "ALTER TABLE {0} ADD {1}";
#endregion
StringBuilder inserting = new StringBuilder("");
inserting.Append(columnInfo.Name);
inserting.Append(" ");
inserting.Append(columnInfo.Type);
inserting.Append(" ");
if (!columnInfo.Nullable)
{
inserting.Append("NOT NULL ");
}
if (columnInfo.PrimaryKey)
{
inserting.Append("PRIMARY KEY ");
}
if (columnInfo.Reference != null && columnInfo.Reference != "")
{
DbColumnReference reference = DbColumnReference.TryParse(columnInfo.Reference);
inserting.Append(string.Format("REFERENCES {0}({1})", reference.TableName, reference.ColumnName));
inserting.Append(" ");
}
while (inserting.Length > 0 && inserting[inserting.Length - 1] == ' ')
{
inserting.Remove(inserting.Length - 1, 1);
}
ExecuteCommandReader(string.Format(query, tableName, inserting.ToString()));
}
public DbColumnReference GetColumnReference(DbColumnReference column)
{
#region QUERY
const string query = "SELECT c.owner, c_pk.table_name r_table_name, b.column_name r_column_name " +
"FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner " +
"AND c.r_constraint_name = c_pk.constraint_name " +
"JOIN user_cons_columns b ON C_PK.owner = b.owner AND C_PK.CONSTRAINT_NAME = b.constraint_name " +
"WHERE c.constraint_type = 'R' AND c.owner = \'{0}\' AND a.table_name = \'{1}\' AND a.column_name = \'{2}\'";
#endregion
using (var cmd = _databaseConnection.CreateCommand())
{
cmd.CommandText = String.Format(query, column.Owner.ToUpper(), column.TableName.ToUpper(), column.ColumnName.ToUpper());
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
DbColumnReference res = new DbColumnReference();
res.Owner = reader.GetString(0);
res.TableName = reader.GetString(1);
res.ColumnName = reader.GetString(2);
return(res);
}
}
}
public string CreateString()
{
StringBuilder res = new StringBuilder("");
res.Append(Name);
res.Append(" ");
res.Append(Type);
res.Append(" ");
if (Reference != "" && Reference != null)
{
DbColumnReference reference = DbColumnReference.TryParse(Reference);
res.Append(string.Format("REFERENCES {0}({1})", reference.TableName, reference.ColumnName));
res.Append(" ");
}
if (!Nullable)
{
res.Append("NOT NULL ");
}
return(res.ToString());
}
public IDictionary <string, DbColumnReference> GetTableReferences(string tableName)
{
if (tableName == "" || tableName == null)
{
return(new Dictionary <string, DbColumnReference>());
}
#region QUERY
const string query = "SELECT c.owner, c_pk.table_name r_table_name, b.column_name r_column_name " +
"FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner " +
"AND c.r_constraint_name = c_pk.constraint_name " +
"JOIN user_cons_columns b ON C_PK.owner = b.owner AND C_PK.CONSTRAINT_NAME = b.constraint_name " +
"WHERE c.constraint_type = 'R' AND a.table_name = \'{0}\'";
#endregion
IDictionary <string, DbColumnReference> res = new Dictionary <string, DbColumnReference>();
using (var cmd = _databaseConnection.CreateCommand())
{
cmd.CommandText = String.Format(query, tableName.ToUpper());
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader == null)
{
return(null);
}
while (reader.Read())
{
DbColumnReference cur = new DbColumnReference();
cur.Owner = reader.GetString(0);
cur.TableName = reader.GetString(1);
cur.ColumnName = reader.GetString(2);
res[cur.ColumnName] = cur;
}
return(res);
}
}
return(res);
}
private static void SetDbForeignKeys(DbModel dbModel, DatabaseType type, DbConnection conn, int connectionId, string databaseName)
{
var columnLookup = dbModel.Tables.SelectMany(t => t.Columns)
.Select(c => c as Models.DbColumn)
.ToDictionary(c => new DbColumnReference(c.Table.Schema, c.Table.Name, c.Name));
DbCommand cmd = null;
switch (type)
{
case DatabaseType.MySQL:
{
string sql = "SELECT NULL AS CHILD_TABLE_SCHEMA, "
+ " C.TABLE_NAME AS CHILD_TABLE_NAME, "
+ " C.COLUMN_NAME AS CHILD_COLUMN_NAME, "
+ " NULL AS PARENT_TABLE_SCHEMA, "
+ " P.TABLE_NAME AS PARENT_TABLE_NAME, "
+ " P.COLUMN_NAME AS PARENT_COLUMN_NAME "
+ "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C "
+ " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG "
+ " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA "
+ " AND C.TABLE_NAME = RC.TABLE_NAME "
+ " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P "
+ " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG "
+ " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA "
+ " AND P.TABLE_NAME = RC.REFERENCED_TABLE_NAME "
+ " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME "
+ " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION "
+ "WHERE C.TABLE_SCHEMA = @schema;";
cmd = CreateCommand(type, conn, sql);
cmd.Parameters.Add(new MySqlParameter("@schema", databaseName));
}
break;
case DatabaseType.PostgreSQL:
{
string sql = "SELECT C.TABLE_SCHEMA AS CHILD_TABLE_SCHEMA, "
+ " C.TABLE_NAME AS CHILD_TABLE_NAME, "
+ " C.COLUMN_NAME AS CHILD_COLUMN_NAME, "
+ " P.TABLE_SCHEMA AS PARENT_TABLE_SCHEMA, "
+ " P.TABLE_NAME AS PARENT_TABLE_NAME, "
+ " P.COLUMN_NAME AS PARENT_COLUMN_NAME "
+ "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C "
+ " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG "
+ " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA "
+ " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P "
+ " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG "
+ " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA "
+ " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME "
+ " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION "
+ "WHERE C.TABLE_SCHEMA <> 'sys' AND P.TABLE_SCHEMA <> 'sys';";
cmd = CreateCommand(type, conn, sql);
}
break;
case DatabaseType.SQLServer:
{
string sql = "SELECT C.TABLE_SCHEMA AS CHILD_TABLE_SCHEMA, "
+ " C.TABLE_NAME AS CHILD_TABLE_NAME, "
+ " C.COLUMN_NAME AS CHILD_COLUMN_NAME, "
+ " P.TABLE_SCHEMA AS PARENT_TABLE_SCHEMA, "
+ " P.TABLE_NAME AS PARENT_TABLE_NAME, "
+ " P.COLUMN_NAME AS PARENT_COLUMN_NAME "
+ "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C "
+ " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG "
+ " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA "
+ " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P "
+ " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG "
+ " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA "
+ " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME "
+ " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION "
+ "WHERE C.TABLE_SCHEMA <> 'sys' AND P.TABLE_SCHEMA <> 'sys';";
cmd = CreateCommand(type, conn, sql);
}
break;
}
var fkData = new List <Tuple <DbColumnReference, DbColumnReference> >();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var child = new DbColumnReference(reader.IsDBNull(0) ? (string)null : reader.GetString(0), reader.IsDBNull(1) ? (string)null : reader.GetString(1), reader.IsDBNull(2) ? (string)null : reader.GetString(2));
var parent = new DbColumnReference(reader.IsDBNull(3) ? (string)null : reader.GetString(3), reader.IsDBNull(4) ? (string)null : reader.GetString(4), reader.IsDBNull(5) ? (string)null : reader.GetString(5));
fkData.Add(new Tuple <DbColumnReference, DbColumnReference>(child, parent));
}
}
foreach (var fk in fkData)
{
if (columnLookup.ContainsKey(fk.Item1) && columnLookup.ContainsKey(fk.Item2))
{
var childColumn = columnLookup[fk.Item1];
childColumn.Parent = columnLookup[fk.Item2];
}
}
foreach (var table in dbModel.Tables)
{
foreach (var column in table.Columns.Select(c => c as Models.DbColumn))
{
DbTable dbTable = null;
if (column.IsPrimaryKey == false && column.Parent == null)
{
dbTable = dbModel.Tables.FirstOrDefault(t =>
t.Schema == table.Schema &&
((t.Name.ToLower() + "_id") == column.Name.ToLower()) ||
((t.Name.ToLower() + "id") == column.Name.ToLower()));
if (dbTable != null)
{
column.Parent = dbTable.Columns[0] as Models.DbColumn;
}
}
}
}
}
