/// <summary>
/// Returns the complete schemma of a table.
/// In this version it assumes that a primary key of type integer is autoincrementable.
/// </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.Table table = new TableSchema.Table(tableName, tableType, this);
table.Name = tableName;
table.Columns = new TableSchema.TableColumnCollection();
table.ForeignKeys = new TableSchema.ForeignKeyTableCollection();
table.SchemaName = Catalog;
DataTable dtcols = AllColumns;
DataTable fks = AllForeignKeys;
//Add all the columns
foreach (DataRow row in dtcols.Select(string.Format("TABLE_NAME = '{0}'", tableName)))
{
TableSchema.TableColumn column = new TableSchema.TableColumn(table);
column.ColumnName = row["COLUMN_NAME"].ToString();
column.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
column.IsForeignKey = (fks.Select(string.Format("TABLE_NAME = '{0}' AND FKEY_FROM_COLUMN = '{1}'", tableName, column.ColumnName)).Length > 0);
column.DataType = GetDbType(row["DATA_TYPE"].ToString());
if (column.DataType == DbType.Guid || column.DataType == DbType.DateTime)
{
column.MaxLength = (column.DataType == DbType.Guid) ? 64 : 48;
}
else
{
column.MaxLength = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
}
column.AutoIncrement = Convert.ToBoolean(row["PRIMARY_KEY"]) && GetDbType(row["DATA_TYPE"].ToString()) == DbType.Int64;
column.IsNullable = Convert.ToBoolean(row["IS_NULLABLE"]);
column.IsReadOnly = false;
table.Columns.Add(column);
}
//Add all the foreigh keys from other tables that have foreign keys to this table's primary key
foreach (DataRow row in fks.Select(string.Format("FKEY_TO_TABLE = '{0}'", tableName)))
{
TableSchema.TableColumn column = table.Columns.GetColumn(row["FKEY_TO_COLUMN"].ToString());
if (!column.IsForeignKey)
{
TableSchema.PrimaryKeyTable pkTable = new TableSchema.PrimaryKeyTable(this);
pkTable.ColumnName = row["FKEY_FROM_COLUMN"].ToString();
pkTable.TableName = row["TABLE_NAME"].ToString();
table.PrimaryKeyTables.Add(pkTable);
}
}
//Add all the foreign keys
foreach (DataRow row in fks.Select(string.Format("TABLE_NAME = '{0}'", tableName)))
{
TableSchema.ForeignKeyTable fk = new TableSchema.ForeignKeyTable(this);
fk.ColumnName = row["FKEY_FROM_COLUMN"].ToString();
fk.TableName = row["FKEY_TO_TABLE"].ToString();
table.ForeignKeys.Add(fk);
}
if (table.Columns.Count > 0)
{
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)
{
//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)
{
//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;
}
