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