public IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new OracleConnection(connectionStr);
conn.Open();
using (conn)
{
using (var command = new OracleCommand())
{
command.Connection = conn;
command.CommandText =
@"SELECT R.TABLE_NAME, COL.COLUMN_NAME
FROM ALL_CONSTRAINTS C, ALL_CONSTRAINTS R, ALL_CONS_COLUMNS COL
WHERE C.TABLE_NAME = :TABLE_NAME
AND C.CONSTRAINT_NAME = R.R_CONSTRAINT_NAME
AND R.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
AND R.TABLE_NAME = COL.TABLE_NAME
AND R.OWNER = COL.OWNER";
command.Parameters.Add(new OracleParameter("TABLE_NAME", table.Name));
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
hasManyRelationships.Add(new HasMany {
Reference = reader.GetString(0),
ReferenceColumn = reader.GetString(1)
});
}
return hasManyRelationships;
}
}
}
public PrimaryKey DeterminePrimaryKeys(Table table)
{
var primaryKeys = table.Columns.Where(x => x.IsPrimaryKey.Equals(true)).ToList();
if (primaryKeys.Count() == 1)
{
var c = primaryKeys.First();
var key = new PrimaryKey
{
Type = PrimaryKeyType.PrimaryKey,
Columns = { c }
};
return key;
}
if (primaryKeys.Count() > 1)
{
// Composite key
var key = new PrimaryKey
{
Type = PrimaryKeyType.CompositeKey,
Columns = primaryKeys
};
return key;
}
return null;
}
public CodeGenerator(ApplicationPreferences appPrefs, Table table)
: base(appPrefs.DomainFolderPath, "Domain", appPrefs.TableName, appPrefs.NameSpace, appPrefs.AssemblyName, appPrefs.Sequence, table, appPrefs)
{
this.appPrefs = appPrefs;
language = appPrefs.Language;
Inflector.EnableInflection = appPrefs.EnableInflections;
}
public IList<ForeignKey> DetermineForeignKeyReferences(Table table)
{
var foreignKeys = table.Columns.Where(x => x.IsForeignKey).Distinct()
.Select(c => new ForeignKey
{
Name = c.Name,
References = GetForeignKeyReferenceTableName(table.Name, c.Name),
Columns = DetermineColumnsForForeignKey(table.Columns, c.ConstraintName)
}).ToList();
Table.SetUniqueNamesForForeignKeyProperties(foreignKeys);
return foreignKeys;
}
public IList<ForeignKey> DetermineForeignKeyReferences(Table table)
{
List<ForeignKey> foreignKeys = (from column in table.Columns
where column.ForeignKeyTableName != null
select new ForeignKey
{
Name = column.ForeignKeyTableName + "_" + column.ForeignKeyColumnName,
References = column.ForeignKeyTableName,
Columns = new[] { column },
UniquePropertyName = column.Name
}).ToList();
Table.SetUniqueNamesForForeignKeyProperties(foreignKeys);
return foreignKeys;
}
public IList<ForeignKey> DetermineForeignKeyReferences(Table table)
{
var foreignKeys = (from c in table.Columns
where c.IsForeignKey
group c by new { c.ConstraintName, c.ForeignKeyTableName } into g
select new ForeignKey {
Name = g.Key.ConstraintName,
References = g.Key.ForeignKeyTableName,
Columns = g.ToList(),
UniquePropertyName = g.Key.ForeignKeyTableName
}).ToList();
Table.SetUniqueNamesForForeignKeyProperties(foreignKeys);
return foreignKeys;
}
protected AbstractGenerator(string filePath, string specificFolder, string tableName, string nameSpace, string assemblyName, string sequenceName, Table table, ApplicationPreferences appPrefs)
{
this.filePath = filePath;
if(appPrefs.GenerateInFolders)
{
this.filePath = Path.Combine(filePath, specificFolder);
if(!this.filePath.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
this.filePath = this.filePath + Path.DirectorySeparatorChar;
}
}
this.tableName = tableName;
this.nameSpace = nameSpace;
this.assemblyName = assemblyName;
this.sequenceName = sequenceName;
Table = table;
Formatter = TextFormatterFactory.GetTextFormatter(appPrefs);
}
/// <summary>
/// Return all table details based on table and owner.
/// </summary>
/// <param name="table"></param>
/// <param name="owner"></param>
/// <returns></returns>
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
using (var conn = new OracleConnection(connectionStr))
{
conn.Open();
using (OracleCommand tableCommand = conn.CreateCommand())
{
tableCommand.CommandText =
@"select column_name, data_type, nullable, sum(constraint_type) constraint_type, data_length, data_precision, data_scale
from (
SELECT tc.column_name AS column_name, tc.data_type AS data_type, tc.nullable AS NULLABLE,
decode(c.constraint_type, 'P', 1, 'R', 2, 'U', 4, 'C', 8, 16) AS constraint_type,
data_length, data_precision, data_scale, column_id
from all_tab_columns tc
left outer join (
all_cons_columns cc
join all_constraints c on (
c.owner=cc.owner
and c.constraint_name = cc.constraint_name
)
) on (
tc.owner = cc.owner
and tc.table_name = cc.table_name
and tc.column_name = cc.column_name
)
where tc.table_name = :table_name and tc.owner = :owner
order by tc.table_name, cc.position nulls last, tc.column_id)
group by column_name, data_type, nullable, data_length, data_precision, data_scale, column_id
order by column_id";
tableCommand.Parameters.Add("table_name", table.Name);
tableCommand.Parameters.Add("owner", owner);
using (OracleDataReader oracleDataReader = tableCommand.ExecuteReader(CommandBehavior.Default))
{
var m = new DataTypeMapper();
while (oracleDataReader.Read())
{
var constraintType = Convert.ToInt32(oracleDataReader.GetOracleNumber(3).Value);
int? dataLength = oracleDataReader.IsDBNull(4) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(4).Value);
int? dataPrecision = oracleDataReader.IsDBNull(5) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(5).Value);
int? dataScale = oracleDataReader.IsDBNull(6) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(6).Value);
columns.Add(new Column {
Name = oracleDataReader.GetOracleString(0).Value,
DataType = oracleDataReader.GetOracleString(1).Value,
IsNullable = string.Equals(oracleDataReader.GetOracleString(2).Value, "Y", StringComparison.OrdinalIgnoreCase),
IsPrimaryKey = ConstraintTypeResolver.IsPrimaryKey(constraintType),
IsForeignKey = ConstraintTypeResolver.IsForeignKey(constraintType),
IsUnique = ConstraintTypeResolver.IsUnique(constraintType),
MappedDataType = m.MapFromDBType(ServerType.Oracle, oracleDataReader.GetOracleString(1).Value, dataLength, dataPrecision, dataScale).ToString(),
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale
});
}
table.Owner = owner;
table.Columns = columns;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns.Where(c => c.IsForeignKey))
{
var foreignInfo = GetForeignKeyReferenceTableName(table.Name, c.Name);
c.ForeignKeyTableName = foreignInfo.Item1;
c.ForeignKeyColumnName = foreignInfo.Item2;
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
conn.Close();
}
return columns;
}
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new SqlConnection(connectionStr);
conn.Open();
try {
using (conn) {
using (var command = new SqlCommand()) {
command.Connection = conn;
command.CommandText =
String.Format(
@"
SELECT DISTINCT
PK_TABLE = b.TABLE_NAME,
FK_TABLE = c.TABLE_NAME,
FK_COLUMN_NAME = d.COLUMN_NAME,
CONSTRAINT_NAME = a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA AND a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d on a.CONSTRAINT_NAME = d.CONSTRAINT_NAME
WHERE b.TABLE_NAME = '{0}'
ORDER BY 1,2",
table.Name.Replace("'","''"));
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
var constraintName = reader["CONSTRAINT_NAME"].ToString();
var fkColumnName = reader["FK_COLUMN_NAME"].ToString();
var pkTableName = reader["PK_TABLE"].ToString();
var existing = hasManyRelationships.FirstOrDefault(hm => hm.ConstraintName == constraintName);
if (existing == null) {
var newHasManyItem = new HasMany
{
ConstraintName = constraintName,
Reference = reader.GetString(1),
PKTableName = pkTableName
};
newHasManyItem.AllReferenceColumns.Add(fkColumnName);
hasManyRelationships.Add(newHasManyItem);
} else {
existing.AllReferenceColumns.Add(fkColumnName);
}
}
}
}
} finally {
conn.Close();
}
return hasManyRelationships;
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new SqlConnection(connectionStr);
conn.Open();
try {
using (conn) {
using (var tableDetailsCommand = conn.CreateCommand()) {
tableDetailsCommand.CommandText = string.Format(
@"SELECT distinct c.column_name, c.data_type, c.is_nullable, tc.constraint_type, convert(int,c.numeric_precision) numeric_precision, c.numeric_scale, c.character_maximum_length, c.table_name, c.ordinal_position, tc.constraint_name,
columnproperty(object_id(c.table_schema + '.' + c.table_name), c.column_name,'IsIdentity') IsIdentity,
(SELECT CASE WHEN count(1) = 0 THEN 0 ELSE 1 END
FROM information_schema.table_constraints x
INNER JOIN information_schema.constraint_column_usage ccux ON c.table_name = ccux.table_name and c.column_name = ccux.column_name and c.table_schema = ccux.table_schema
WHERE x.constraint_type = 'UNIQUE' and x.table_schema = ccux.table_schema and x.constraint_name = ccux.constraint_name) IsUnique
from information_schema.columns c
left outer join (
information_schema.constraint_column_usage ccu
join information_schema.table_constraints tc on (
tc.table_schema = ccu.table_schema
and tc.constraint_name = ccu.constraint_name
and NOT tc.constraint_type IN ('CHECK','UNIQUE')
)
) on (
c.table_schema = ccu.table_schema
and c.table_name = ccu.table_name
and c.column_name = ccu.column_name
)
where c.table_name = '{0}'
and c.table_schema ='{1}'
order by c.table_name, c.ordinal_position",
table.Name.Replace("'", "''"), owner);
using (var sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default)) {
while (sqlDataReader.Read()) {
string columnName = sqlDataReader.GetString(0);
string dataType = sqlDataReader.GetString(1);
bool isNullable = sqlDataReader.GetString(2).Equals("YES", StringComparison.CurrentCultureIgnoreCase);
var isIdentity = Convert.ToBoolean(sqlDataReader["IsIdentity"]);
var characterMaxLenth = sqlDataReader["character_maximum_length"] as int?;
var numericPrecision = sqlDataReader["numeric_precision"] as int?;
var numericScale = sqlDataReader["numeric_scale"] as int?;
var constraintName = sqlDataReader["constraint_name"].ToString();
var isUnique = Convert.ToBoolean(sqlDataReader["IsUnique"]);
bool isPrimaryKey = (!sqlDataReader.IsDBNull(3) && sqlDataReader.GetString(3).Equals(SqlServerConstraintType.PrimaryKey.ToString(), StringComparison.CurrentCultureIgnoreCase));
bool isForeignKey = (!sqlDataReader.IsDBNull(3) && sqlDataReader.GetString(3).Equals(SqlServerConstraintType.ForeignKey.ToString(), StringComparison.CurrentCultureIgnoreCase));
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsIdentity = isIdentity,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsUnique = isUnique,
MappedDataType = m.MapFromDBType(ServerType.SqlServer, dataType, characterMaxLenth, numericPrecision, numericScale).ToString(),
DataLength = characterMaxLenth,
DataScale = numericScale,
DataPrecision = numericPrecision,
ConstraintName = constraintName
});
table.Columns = columns;
}
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
if (c.IsForeignKey)
{
string referencedTableName;
string referencedColumnName;
GetForeignKeyReferenceDetails(c.ConstraintName, out referencedTableName, out referencedColumnName);
c.ForeignKeyTableName = referencedTableName;
c.ForeignKeyColumnName = referencedColumnName;
}
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
}
} finally {
conn.Close();
}
return columns;
}
public ContractGenerator(ApplicationPreferences appPrefs, Table table)
: base(appPrefs.FolderPath, "Contract", appPrefs.TableName, appPrefs.NameSpace, appPrefs.AssemblyName, appPrefs.Sequence, table, appPrefs)
{
this.appPrefs = appPrefs;
this.table = table;
}
protected MappingGenerator(ApplicationPreferences applicationPreferences, Table table)
: base(applicationPreferences.FolderPath, "Mapping", applicationPreferences.TableName, applicationPreferences.NameSpace, applicationPreferences.AssemblyName, applicationPreferences.Sequence, table, applicationPreferences)
{
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new OleDbConnection(_connectionStr);
conn.Open();
try
{
using (conn)
{
using (var command = new OleDbCommand())
{
command.Connection = conn;
command.CommandText =
String.Format(
@"
SELECT pt.table_name PK_TABLE
, t.Table_name FK_TABLE
, fc.column_name FK_COLUMN_NAME
, fk.role CONSTRAINT_NAME
FROM
SYSFOREIGNKEY fk
INNER JOIN systable t ON t.table_id = fk.foreign_table_id
INNER JOIN sysfkcol fcol ON fcol.foreign_table_id = fk.foreign_table_id AND fcol.foreign_key_id = fk.foreign_key_id
INNER JOIN syscolumn fc ON fc.column_id = fcol.foreign_column_id AND fc.Table_id = fcol.foreign_table_id
INNER JOIN systable pt ON pt.table_id = fk.primary_table_id
WHERE pt.table_name = '{0}'
ORDER BY 1, 2",
table.Name);
var reader = command.ExecuteReader();
while (reader.Read())
{
var constraintName = reader["CONSTRAINT_NAME"].ToString();
var fkColumnName = reader["FK_COLUMN_NAME"].ToString();
var existing = hasManyRelationships.FirstOrDefault(hm => hm.ConstraintName == constraintName);
if (existing == null)
{
var newHasManyItem = new HasMany
{
ConstraintName = constraintName,
Reference = reader.GetString(1)
};
newHasManyItem.AllReferenceColumns.Add(fkColumnName);
hasManyRelationships.Add(newHasManyItem);
}
else
{
existing.AllReferenceColumns.Add(fkColumnName);
}
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
private static CodeSnippetStatement GetIdMapCodeSnippetStatement(ApplicationPreferences appPrefs, Table table, string pkColumnName, string propertyName, string pkColumnType, ITextFormatter formatter)
{
var dataTypeMapper = new DataTypeMapper();
bool isPkTypeIntegral = (dataTypeMapper.MapFromDBType(appPrefs.ServerType, pkColumnType, null, null, null)).IsTypeIntegral();
string idGeneratorType = (isPkTypeIntegral ? "GeneratedBy.Identity()" : "GeneratedBy.Assigned()");
var fieldName = FixPropertyWithSameClassName(propertyName, table.Name);
var pkAlsoFkQty = (from fk in table.ForeignKeys.Where(fk => fk.UniquePropertyName == pkColumnName) select fk).Count();
if (pkAlsoFkQty > 0) fieldName = fieldName + "Id";
return new CodeSnippetStatement(string.Format(TABS + "Id(x => x.{0}).{1}.Column(\"{2}\");",
formatter.FormatText(fieldName),
idGeneratorType,
pkColumnName));
}
protected AbstractCodeGenerator(
string filePath, string additionalFolder, string tableName, string nameSpace, string assemblyName, string sequenceName,
Table table, ApplicationPreferences appPrefs)
: base(filePath, additionalFolder, tableName, nameSpace, assemblyName, sequenceName, table, appPrefs)
{
}
public FluentGenerator(ApplicationPreferences appPrefs, Table table)
: base(appPrefs.FolderPath, "Mapping", appPrefs.TableName, appPrefs.NameSpaceMap, appPrefs.AssemblyName, appPrefs.Sequence, table, appPrefs)
{
this.appPrefs = appPrefs;
language = this.appPrefs.Language;
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["PKTABLE_NAME"].ToString() == table.Name)
{
var newHasManyItem = new HasMany
{
Reference = dt.Rows[i]["FKTABLE_NAME"].ToString(),
ConstraintName = dt.Rows[i]["FK_NAME"].ToString(),
ReferenceColumn = dt.Rows[i]["FKCOLUMN_NAME"].ToString()
};
hasManyRelationships.Add(newHasManyItem);
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });
string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
var tableInfo = new DataTable();
adapter.FillSchema(tableInfo, SchemaType.Source);
using (var reader = new DataTableReader(tableInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
for (var k = 0; k < schemaTable.Rows.Count; k++)
{
string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
int dataPrecision = 0;
if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
{
dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
}
int dataScale = 0;
if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
{
dataScale = (int)schemaTable.Rows[k]["NumericScale"];
}
bool isForeignKey = false;
string fkTableName = "";
string constraintName = "";
for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
{
if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
{
isForeignKey = true;
fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
break;
}
}
string dataType;
using (var cmd = new CUBRIDCommand(sqlInfo, conn))
{
using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
{
CUBRIDReader.Read();
dataType = CUBRIDReader.GetColumnTypeName(k);
}
}
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsUnique = isUnique,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsIdentity = isIdentity,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
ForeignKeyTableName = fkTableName,
ConstraintName = constraintName,
MappedDataType =
m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null).ToString(),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return columns;
}
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new NpgsqlConnection(connectionStr);
conn.Open();
using (conn)
{
using (var command = new NpgsqlCommand())
{
command.Connection = conn;
command.CommandText =
String.Format(
@"
select DISTINCT
b.TABLE_NAME,
c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
where
b.TABLE_NAME = '{0}'
order by
1,2",
table.Name);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
hasManyRelationships.Add(new HasMany
{
Reference = reader.GetString(1)
});
}
return hasManyRelationships;
}
}
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new NpgsqlConnection(connectionStr);
conn.Open();
using (conn)
{
using (NpgsqlCommand tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format(@"select
c.column_name
,c.data_type
,c.is_nullable
,b.constraint_type as type
from information_schema.constraint_column_usage a
inner join information_schema.table_constraints b on a.constraint_name=b.constraint_name
inner join information_schema.columns c on a.column_name=c.column_name and a.table_name=c.table_name
where a.table_schema='{1}' and a.table_name='{0}' and b.constraint_type in ('PRIMARY KEY')
union
select
a.column_name
,a.data_type
,a.is_nullable
,b.constraint_type as type
from information_schema.columns a
inner join information_schema.table_constraints b on b.constraint_name ='{0}_'||a.column_name||'_fkey'
union
select
a.column_name
,a.data_type
,a.is_nullable
,''
from information_schema.columns a
where a.table_schema='{1}' and a.table_name='{0}' and a.column_name not in (
select
c.column_name
from information_schema.constraint_column_usage a
inner join information_schema.table_constraints b on a.constraint_name=b.constraint_name
inner join information_schema.columns c on a.column_name=c.column_name and a.table_name=c.table_name
where a.table_schema='{1}' and a.table_name='{0}' and b.constraint_type in ('PRIMARY KEY')
union
select
a.column_name
from information_schema.columns a
inner join information_schema.table_constraints b on b.constraint_name ='{0}_'||a.column_name||'_fkey')", table.Name, owner);
using (NpgsqlDataReader sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (sqlDataReader.Read())
{
string columnName = sqlDataReader.GetString(0);
string dataType = sqlDataReader.GetString(1);
bool isNullable = sqlDataReader.GetString(2).Equals("YES",
StringComparison.
CurrentCultureIgnoreCase);
bool isPrimaryKey =
(!sqlDataReader.IsDBNull(3)
? sqlDataReader.GetString(3).Equals(
NpgsqlConstraintType.PrimaryKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
bool isForeignKey =
(!sqlDataReader.IsDBNull(3)
? sqlDataReader.GetString(3).Equals(
NpgsqlConstraintType.ForeignKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey,
//IsPrimaryKey(selectedTableName.Name, columnName)
IsForeignKey = isForeignKey,
// IsFK()
MappedDataType =
m.MapFromDBType(ServerType.PostgreSQL, dataType, null, null, null).ToString(),
//DataLength = dataLength
});
table.Columns = columns;
}
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
c.ForeignKeyTableName = GetForeignKeyReferenceTableName(table.Name, c.Name);
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
}
return columns;
}
public CastleGenerator(ApplicationPreferences applicationPreferences, Table table)
: base(applicationPreferences.FolderPath, "Mapping", applicationPreferences.TableName, applicationPreferences.NameSpaceMap, applicationPreferences.AssemblyName, applicationPreferences.Sequence, table, applicationPreferences)
{
this.applicationPreferences = applicationPreferences;
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new MySqlConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
using (MySqlCommand tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format(@"DESCRIBE {0}.{1}", owner, table);
using (MySqlDataReader sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (sqlDataReader.Read())
{
string columnName = sqlDataReader.GetString(0);
string dataType = sqlDataReader.GetString(1);
bool isNullable = sqlDataReader.GetString(2).Equals("YES",
StringComparison.
CurrentCultureIgnoreCase);
bool isPrimaryKey =
(!sqlDataReader.IsDBNull(3)
? sqlDataReader.GetString(3).Equals(
MysqlConstraintType.PrimaryKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
bool isForeignKey =
(!sqlDataReader.IsDBNull(3)
? sqlDataReader.GetString(3).Equals(
MysqlConstraintType.ForeignKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey,
//IsPrimaryKey(selectedTableName.Name, columnName)
IsForeignKey = isForeignKey,
// IsFK()
MappedDataType =
m.MapFromDBType(ServerType.MySQL, dataType, null, null, null).ToString(),
//DataLength = dataLength
});
table.Columns = columns;
}
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
c.ForeignKeyTableName = GetForeignKeyReferenceTableName(table.Name, c.Name);
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
}
}
finally
{
conn.Close();
}
return columns;
}
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new MySqlConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
using (var command = new MySqlCommand())
{
command.Connection = conn;
command.CommandText =
String.Format(
@"
SELECT
ke.referenced_table_name parent,
ke.table_name child,
ke.constraint_name
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name = '{0}' and ke.constraint_schema = '{1}'
ORDER BY
ke.table_name",
table.Name, conn.Database);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
hasManyRelationships.Add(new HasMany
{
Reference = reader.GetString(1)
});
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
private static CodeSnippetStatement GetIdMapCodeSnippetStatement(PrimaryKey primaryKey, Table table, ITextFormatter formatter)
{
var keyPropertyBuilder = new StringBuilder(primaryKey.Columns.Count);
bool first = true;
foreach (Column pkColumn in primaryKey.Columns)
{
var propertyName = formatter.FormatText(pkColumn.Name);
var fieldName = FixPropertyWithSameClassName(propertyName, table.Name);
var pkAlsoFkQty = (from fk in table.ForeignKeys.Where(fk => fk.UniquePropertyName == pkColumn.Name) select fk).Count();
if (pkAlsoFkQty > 0) fieldName = fieldName + "Id";
var tmp = String.Format(".KeyProperty(x => x.{0}, \"{1}\")",fieldName, pkColumn.Name);
keyPropertyBuilder.Append(first ? tmp : "\n" + TABS + " " + tmp);
first = false;
}
return new CodeSnippetStatement(TABS + string.Format("CompositeId(){0};", keyPropertyBuilder));
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
using (var sqlCon = new OleDbConnection(_connectionStr))
{
try
{
using (var tableDetailsCommand = sqlCon.CreateCommand())
{
var sqlText = @"SELECT
[default] default_value, c.column_name columnName,
d.domain_name DataTypeName, width ColumnSize, scale columnPrecision,
convert(bit, CASE WHEN pkey = 'Y' THEN 1 ELSE 0 END) IsKey,
convert(bit, CASE WHEN c.[nulls] = 'Y' THEN 1 ELSE 0 END) AllowDBNull,
convert(bit, CASE WHEN c.[default] = 'AUTOINCREMENT' THEN 1 ELSE 0 END) IsIdentity,
convert(bit, CASE WHEN c.[default] = 'NEWID()' THEN 1 ELSE 0 END) IsGenerated,
convert(bit, CASE WHEN fcol.foreign_column_id is null THEN 0 ELSE 1 END) IsForeignKey,
ISNULL(fk.[role],'') ConstraintName
FROM sys.syscolumn c
inner join sys.systable t on t.table_id = c.table_id
inner join sys.sysdomain d on c.domain_id = d.domain_id
inner join sysobjects o on t.object_id = o.id
inner join sys.sysuser u on u.user_id = o.uid
left join sysfkcol fcol ON c.table_id = fcol.foreign_table_id and c.column_id = fcol.foreign_column_id
left join sysforeignkey fk ON fcol.foreign_table_id = fk.foreign_table_id AND fcol.foreign_key_id = fk.foreign_key_id
WHERE t.table_name = '{0}'
and u.user_name = '{1}'
ORDER BY c.Column_id";
tableDetailsCommand.CommandText =
string.Format(sqlText,table.Name, owner);
sqlCon.Open();
var dr = tableDetailsCommand.ExecuteReader(CommandBehavior.Default);
var m = new DataTypeMapper();
while (dr.Read())
{
var name = dr["columnName"].ToString();
var isNullable = (bool) dr["AllowDBNull"];
var isPrimaryKey = dr["IsKey"] as bool?;
var isForeignKey = (bool)dr["IsForeignKey"];
var dataType = dr["DataTypeName"].ToString();
var dataLength = Convert.ToInt32(dr["columnSize"]);
var dataPrecision = Convert.ToInt32(dr["columnSize"]);
var dataScale = Convert.ToInt32(dr["columnPrecision"]);
var isIdentity = dr["IsIdentity"] as bool?;
var constraintName = dr["ConstraintName"].ToString();
var isUnique = false; //(bool) dr["IsKey"];
var mappedType = m.MapFromDBType(ServerType.Sybase, dataType, dataLength, dataPrecision,
dataScale);
if (DataTypeMapper.IsNumericType(mappedType))
{
dataLength = 0;
}
else
{
dataScale = 0;
dataPrecision = 0;
}
columns.Add(
new Column
{
Name = name,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey.GetValueOrDefault(),
IsForeignKey = isForeignKey,
MappedDataType = mappedType.ToString(),
DataLength = dataLength,
DataScale = dataScale,
DataPrecision = dataPrecision,
DataType = dataType,
IsUnique = isUnique,
IsIdentity = isIdentity.GetValueOrDefault(),
ConstraintName = constraintName
});
}
dr.Close();
}
table.Owner = owner;
table.Columns = columns;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
c.ForeignKeyTableName = GetForeignKeyReferenceTableName(table.Name, c.Name);
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
sqlCon.Close();
}
}
return columns;
}
public CUBRIDMappingGenerator(ApplicationPreferences applicationPreferences, Table table)
: base(applicationPreferences, table)
{
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
using (var sqlCon = new SQLiteConnection(_connectionStr))
{
try
{
using (var tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT * FROM {0}", table.Name);
sqlCon.Open();
var dr = tableDetailsCommand.ExecuteReader(CommandBehavior.SchemaOnly);
var dt = dr.GetSchemaTable();
var m = new DataTypeMapper();
foreach (DataRow row in dt.Rows)
{
columns.Add(
new Column
{
Name = row["ColumnName"].ToString(),
IsNullable = (bool)row["AllowDBNull"],
IsPrimaryKey = (bool)row["IsKey"],
MappedDataType = m.MapFromDBType(ServerType.SQLite, row["DataTypeName"].ToString(), (int)row["ColumnSize"], null, null).ToString(),
DataLength = (int)row["ColumnSize"],
DataType = row["DataTypeName"].ToString(),
IsUnique = (bool)row["IsUnique"]
});
dr.Close();
}
}
table.Owner = owner;
table.Columns = columns;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.ForeignKeys = new List<ForeignKey>();// DetermineForeignKeyReferences(table);
table.HasManyRelationships = new List<HasMany>();// DetermineHasManyRelationships(table);
}
finally
{
sqlCon.Close();
}
}
return columns;
}
