/// <summary>
/// Gets the table schema.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="tableType">Type of the table.</param>
/// <returns></returns>
public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
{
//return base.GetTableSchema(tableName, tableType);
MySqlConnection conn = new MySqlConnection(DefaultConnectionString);
if (dsColumns.Tables[Name] == null)
{
lock (_lockColumns)
{
QueryCommand cmdColumns = new QueryCommand(ALL_TABLE_COLUMNS_SQL, Name);
cmdColumns.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdColumns));
dsColumns.Tables.Add(dt);
}
}
DataRow[] drColumns = dsColumns.Tables[Name].Select("TableName ='" + tableName + "'", "OrdinalPosition ASC");
if (drColumns.Length == 0)
{
return(null);
}
TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this);
tbl.ForeignKeys = new TableSchema.ForeignKeyTableCollection();
for (int i = 0; i < drColumns.Length; i++)
{
string nativeDataType = drColumns[i][SqlSchemaVariable.DATA_TYPE].ToString().ToLower();
TableSchema.TableColumn column = new TableSchema.TableColumn(tbl);
column.ColumnName = drColumns[i][SqlSchemaVariable.COLUMN_NAME].ToString();
column.DataType = GetDbType(nativeDataType);
if (SetPropertyDefaultsFromDatabase && drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT] != DBNull.Value &&
drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString() != "\0")
{
column.DefaultSetting = drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString().Trim();
}
//thanks rauchy!
bool autoIncrement;
bool successfullyParsed = bool.TryParse(drColumns[i][SqlSchemaVariable.IS_IDENTITY].ToString(), out autoIncrement);
if (!successfullyParsed)
{
autoIncrement = Convert.ToBoolean(drColumns[i][SqlSchemaVariable.IS_IDENTITY]);
}
column.AutoIncrement = autoIncrement;
int maxLength;
int.TryParse(drColumns[i][SqlSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
column.MaxLength = maxLength;
column.IsNullable = (drColumns[i][SqlSchemaVariable.IS_NULLABLE].ToString() == "YES");
//column.IsReadOnly = (nativeDataType == "timestamp");
columns.Add(column);
}
if (dsIndex.Tables[Name] == null)
{
lock (_lockIndex)
{
QueryCommand cmdIndex = new QueryCommand(ALL_TABLE_INDEXES_SQL, Name);
cmdIndex.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdIndex));
dsIndex.Tables.Add(dt);
}
}
DataRow[] drIndexes = dsIndex.Tables[Name].Select("TableName = '" + tableName + "'");
for (int i = 0; i < drIndexes.Length; i++)
{
string colName = drIndexes[i][SqlSchemaVariable.COLUMN_NAME].ToString();
string constraintType = drIndexes[i][SqlSchemaVariable.CONSTRAINT_TYPE].ToString();
TableSchema.TableColumn column = columns.GetColumn(colName);
if (Utility.IsMatch(constraintType, SqlSchemaVariable.PRIMARY_KEY))
{
column.IsPrimaryKey = true;
}
else if (Utility.IsMatch(constraintType, SqlSchemaVariable.FOREIGN_KEY))
{
column.IsForeignKey = true;
}
//HACK: Allow second pass naming adjust based on whether a column is keyed
column.ColumnName = column.ColumnName;
}
if (dsPK.Tables[Name] == null)
{
lock (_lockPK)
{
QueryCommand cmdPk = new QueryCommand(ALL_TABLE_PRIMARY_TABLES, Name);
cmdPk.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdPk));
dsPK.Tables.Add(dt);
}
}
DataRow[] drPK = dsPK.Tables[Name].Select("PK_Table ='" + tableName + "'");
for (int i = 0; i < drPK.Length; i++)
{
string colName = drPK[i]["FK_Column"].ToString();
string fkName = drPK[i]["FK_Table"].ToString();
TableSchema.PrimaryKeyTable pkTable = new TableSchema.PrimaryKeyTable(this);
pkTable.ColumnName = colName;
pkTable.TableName = fkName;
tbl.PrimaryKeyTables.Add(pkTable);
}
if (dsFK.Tables[Name] == null)
{
lock (_lockFK)
{
QueryCommand cmdFK = new QueryCommand(ALL_TABLE_FOREIGN_TABLES, Name);
cmdFK.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdFK));
dsFK.Tables.Add(dt);
}
}
DataRow[] drFK = dsFK.Tables[Name].Select("FK_Table ='" + tableName + "'");
ArrayList usedConstraints = new ArrayList();
for (int i = 0; i < drFK.Length; i++)
{
string constraintName = drFK[i]["Constraint_Name"].ToString();
if (!usedConstraints.Contains(constraintName))
{
usedConstraints.Add(constraintName);
string colName = drFK[i]["FK_Column"].ToString();
string fkName = CorrectTableCasing(drFK[i]["PK_Table"].ToString(), conn.Database);
TableSchema.TableColumn column = columns.GetColumn(colName);
if (column != null)
{
column.ForeignKeyTableName = fkName;
}
else
{
continue;
}
TableSchema.ForeignKeyTable fkTable = new TableSchema.ForeignKeyTable(this);
fkTable.ColumnName = colName;
fkTable.TableName = fkName;
tbl.ForeignKeys.Add(fkTable);
}
}
if (dsManyToManyCheck.Tables[Name] == null)
{
lock (_lockManyToManyCheck)
{
QueryCommand cmdM2M = new QueryCommand(MANY_TO_MANY_CHECK_ALL, Name);
cmdM2M.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdM2M));
dsManyToManyCheck.Tables.Add(dt);
}
}
DataRow[] drs = dsManyToManyCheck.Tables[Name].Select("PK_Table = '" + tableName + "'");
if (drs.Length > 0)
{
for (int count = 0; count < drs.Length; count++)
{
string mapTable = drs[count]["FK_Table"].ToString();
string localKey = drs[count]["FK_Column"].ToString();
if (dsManyToManyMap.Tables[Name] == null)
{
lock (_lockManyToManyMap)
{
QueryCommand cmdM2MMap = new QueryCommand(MANY_TO_MANY_FOREIGN_MAP_ALL, Name);
cmdM2MMap.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdM2MMap));
dsManyToManyMap.Tables.Add(dt);
}
}
DataRow[] drMap = dsManyToManyMap.Tables[Name].Select("FK_Table = '" + mapTable + "' AND PK_Table <> '" + tableName + "'");
for (int i = 0; i < drMap.Length; i++)
{
TableSchema.ManyToManyRelationship m = new TableSchema.ManyToManyRelationship(mapTable, tbl.Provider);
m.ForeignTableName = drMap[i]["PK_Table"].ToString();
m.ForeignPrimaryKey = drMap[i]["PK_Column"].ToString();
m.MapTableLocalTableKeyColumn = localKey;
m.MapTableForeignTableKeyColumn = drMap[i]["FK_Column"].ToString();
tbl.ManyToManys.Add(m);
}
}
}
tbl.Columns = columns;
return(tbl);
}
/// <summary>
/// Gets the table schema.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="tableType">Type of the table.</param>
/// <returns></returns>
public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
{
TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
TableSchema.Table table = new TableSchema.Table(tableName, tableType, this);
table.Name = tableName;
string sql = " DESCRIBE `" + tableName + "`";
using (MySqlCommand cmd = new MySqlCommand(sql))
{
//get information about both the table and it's columns
MySqlConnection conn = new MySqlConnection(DefaultConnectionString);
cmd.Connection = conn;
conn.Open();
try
{
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
TableSchema.TableColumn column = new TableSchema.TableColumn(table);
column.IsPrimaryKey = rdr["Key"].ToString() == "PRI";
column.IsForeignKey = rdr["Key"].ToString() == "MUL";
column.ColumnName = rdr["Field"].ToString();
string sType = rdr["Type"].ToString();
string sSize;
if (sType.IndexOf("(") > 0)
{
sSize = sType.Substring(sType.IndexOf("("), sType.Length - sType.IndexOf("(")).Replace(")", String.Empty).Replace("(", String.Empty);
sType = sType.Substring(0, sType.IndexOf("("));
}
else
{
sSize = "0";
}
int size;
int.TryParse(sSize, out size);
column.MaxLength = size;
column.DataType = GetDbType(sType);
//column.DataType = sType.Substring(0,sType.IndexOf("("));
column.AutoIncrement = rdr["Extra"].ToString() == "auto_increment";
//string nullable = rdr["Null"].ToString();
column.IsNullable = (rdr["Null"].ToString().ToLower() == "yes");
column.IsReadOnly = false;
columns.Add(column);
}
rdr.Close();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch
{
//there's no table here
//let fall through to return a null
}
}
if (columns.Count > 0)
{
table.Columns = columns;
return(table);
}
return(null);
}
/// <summary>
/// Gets the table schema.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="tableType">Type of the table.</param>
/// <returns></returns>
public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
{
TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this);
//tbl.ClassName = Convention.ClassName(tableName);
//string sql = TABLE_COLUMN_SQL;
QueryCommand cmd = new QueryCommand(TABLE_COLUMN_SQL, Name);
cmd.AddParameter(TABLE_NAME_PARAMETER, tableName, DbType.AnsiString);
TableSchema.TableColumn column;
using (IDataReader rdr = DataService.GetReader(cmd))
{
//get information about both the table and it's columns
while (rdr.Read())
{
tbl.SchemaName = rdr["USER"].ToString();
column = new TableSchema.TableColumn(tbl);
column.ColumnName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString();
string scale = rdr[OracleSchemaVariable.NUMBER_SCALE].ToString();
string precision = rdr[OracleSchemaVariable.NUMBER_PRECISION].ToString();
column.NumberScale = 0;
column.NumberPrecision = 0;
if (!String.IsNullOrEmpty(scale) && scale != "0")
{
column.NumberScale = int.Parse(scale);
}
if (!String.IsNullOrEmpty(precision) && precision != "0")
{
column.NumberPrecision = int.Parse(precision);
}
column.DataType = GetDbType(rdr[OracleSchemaVariable.DATA_TYPE].ToString().ToLower());
column.AutoIncrement = false;
int maxLength;
int.TryParse(rdr[OracleSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
column.MaxLength = maxLength;
column.IsNullable = Utility.IsMatch(rdr[OracleSchemaVariable.IS_NULLABLE].ToString(), "Y");
column.IsReadOnly = false;
columns.Add(column);
}
}
cmd.CommandSql = INDEX_SQL;
//cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);
using (IDataReader rdr = DataService.GetReader(cmd))
{
while (rdr.Read())
{
string colName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString();
string constraintType = rdr[OracleSchemaVariable.CONSTRAINT_TYPE].ToString();
column = columns.GetColumn(colName);
if (constraintType == SqlSchemaVariable.PRIMARY_KEY)
{
column.IsPrimaryKey = true;
}
else if (constraintType == SqlSchemaVariable.FOREIGN_KEY)
{
column.IsForeignKey = true;
}
//HACK: Allow second pass naming adjust based on whether a column is keyed
column.ColumnName = column.ColumnName;
}
rdr.Close();
}
if (columns.Count > 0)
{
tbl.Columns = columns;
return(tbl);
}
return(null);
}
/// <summary>
/// Gets the table schema.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="tableType">Type of the table.</param>
/// <returns></returns>
public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
{
//return base.GetTableSchema(tableName, tableType);
MySqlConnection conn = new MySqlConnection(DefaultConnectionString);
if(dsColumns.Tables[Name] == null)
{
lock(_lockColumns)
{
QueryCommand cmdColumns = new QueryCommand(ALL_TABLE_COLUMNS_SQL, Name);
cmdColumns.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdColumns));
dsColumns.Tables.Add(dt);
}
}
DataRow[] drColumns = dsColumns.Tables[Name].Select("TableName ='" + tableName + "'", "OrdinalPosition ASC");
if(drColumns.Length == 0)
return null;
TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this);
tbl.ForeignKeys = new TableSchema.ForeignKeyTableCollection();
for(int i = 0; i < drColumns.Length; i++)
{
string nativeDataType = drColumns[i][SqlSchemaVariable.DATA_TYPE].ToString().ToLower();
TableSchema.TableColumn column = new TableSchema.TableColumn(tbl);
column.ColumnName = drColumns[i][SqlSchemaVariable.COLUMN_NAME].ToString();
column.DataType = GetDbType(nativeDataType);
if(SetPropertyDefaultsFromDatabase && drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT] != DBNull.Value &&
drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString() != "\0")
column.DefaultSetting = drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString().Trim();
//thanks rauchy!
bool autoIncrement;
bool successfullyParsed = bool.TryParse(drColumns[i][SqlSchemaVariable.IS_IDENTITY].ToString(), out autoIncrement);
if(!successfullyParsed)
autoIncrement = Convert.ToBoolean(drColumns[i][SqlSchemaVariable.IS_IDENTITY]);
column.AutoIncrement = autoIncrement;
int maxLength;
int.TryParse(drColumns[i][SqlSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
column.MaxLength = maxLength;
column.IsNullable = (drColumns[i][SqlSchemaVariable.IS_NULLABLE].ToString() == "YES");
//column.IsReadOnly = (nativeDataType == "timestamp");
columns.Add(column);
}
if(dsIndex.Tables[Name] == null)
{
lock(_lockIndex)
{
QueryCommand cmdIndex = new QueryCommand(ALL_TABLE_INDEXES_SQL, Name);
cmdIndex.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdIndex));
dsIndex.Tables.Add(dt);
}
}
DataRow[] drIndexes = dsIndex.Tables[Name].Select("TableName = '" + tableName + "'");
for(int i = 0; i < drIndexes.Length; i++)
{
string colName = drIndexes[i][SqlSchemaVariable.COLUMN_NAME].ToString();
string constraintType = drIndexes[i][SqlSchemaVariable.CONSTRAINT_TYPE].ToString();
TableSchema.TableColumn column = columns.GetColumn(colName);
if(Utility.IsMatch(constraintType, SqlSchemaVariable.PRIMARY_KEY))
column.IsPrimaryKey = true;
else if(Utility.IsMatch(constraintType, SqlSchemaVariable.FOREIGN_KEY))
column.IsForeignKey = true;
//HACK: Allow second pass naming adjust based on whether a column is keyed
column.ColumnName = column.ColumnName;
}
if(dsPK.Tables[Name] == null)
{
lock(_lockPK)
{
QueryCommand cmdPk = new QueryCommand(ALL_TABLE_PRIMARY_TABLES, Name);
cmdPk.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdPk));
dsPK.Tables.Add(dt);
}
}
DataRow[] drPK = dsPK.Tables[Name].Select("PK_Table ='" + tableName + "'");
for(int i = 0; i < drPK.Length; i++)
{
string colName = drPK[i]["FK_Column"].ToString();
string fkName = drPK[i]["FK_Table"].ToString();
TableSchema.PrimaryKeyTable pkTable = new TableSchema.PrimaryKeyTable(this);
pkTable.ColumnName = colName;
pkTable.TableName = fkName;
tbl.PrimaryKeyTables.Add(pkTable);
}
if(dsFK.Tables[Name] == null)
{
lock(_lockFK)
{
QueryCommand cmdFK = new QueryCommand(ALL_TABLE_FOREIGN_TABLES, Name);
cmdFK.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdFK));
dsFK.Tables.Add(dt);
}
}
DataRow[] drFK = dsFK.Tables[Name].Select("FK_Table ='" + tableName + "'");
ArrayList usedConstraints = new ArrayList();
for(int i = 0; i < drFK.Length; i++)
{
string constraintName = drFK[i]["Constraint_Name"].ToString();
if(!usedConstraints.Contains(constraintName))
{
usedConstraints.Add(constraintName);
string colName = drFK[i]["FK_Column"].ToString();
string fkName = CorrectTableCasing(drFK[i]["PK_Table"].ToString(), conn.Database);
TableSchema.TableColumn column = columns.GetColumn(colName);
if(column != null)
column.ForeignKeyTableName = fkName;
else
continue;
TableSchema.ForeignKeyTable fkTable = new TableSchema.ForeignKeyTable(this);
fkTable.ColumnName = colName;
fkTable.TableName = fkName;
tbl.ForeignKeys.Add(fkTable);
}
}
if(dsManyToManyCheck.Tables[Name] == null)
{
lock(_lockManyToManyCheck)
{
QueryCommand cmdM2M = new QueryCommand(MANY_TO_MANY_CHECK_ALL, Name);
cmdM2M.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdM2M));
dsManyToManyCheck.Tables.Add(dt);
}
}
DataRow[] drs = dsManyToManyCheck.Tables[Name].Select("PK_Table = '" + tableName + "'");
if(drs.Length > 0)
{
for(int count = 0; count < drs.Length; count++)
{
string mapTable = drs[count]["FK_Table"].ToString();
string localKey = drs[count]["FK_Column"].ToString();
if(dsManyToManyMap.Tables[Name] == null)
{
lock(_lockManyToManyMap)
{
QueryCommand cmdM2MMap = new QueryCommand(MANY_TO_MANY_FOREIGN_MAP_ALL, Name);
cmdM2MMap.Parameters.Add("?DatabaseName", conn.Database, DbType.AnsiString);
DataTable dt = new DataTable(Name);
dt.Load(GetReader(cmdM2MMap));
dsManyToManyMap.Tables.Add(dt);
}
}
DataRow[] drMap = dsManyToManyMap.Tables[Name].Select("FK_Table = '" + mapTable + "' AND PK_Table <> '" + tableName + "'");
for(int i = 0; i < drMap.Length; i++)
{
TableSchema.ManyToManyRelationship m = new TableSchema.ManyToManyRelationship(mapTable, tbl.Provider);
m.ForeignTableName = drMap[i]["PK_Table"].ToString();
m.ForeignPrimaryKey = drMap[i]["PK_Column"].ToString();
m.MapTableLocalTableKeyColumn = localKey;
m.MapTableForeignTableKeyColumn = drMap[i]["FK_Column"].ToString();
tbl.ManyToManys.Add(m);
}
}
}
tbl.Columns = columns;
return tbl;
}
