/// <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; }