private void GetColumnData( TableMap map )
{
string sql = String.Format( selectColumns, map.TableId );
SqlResult sr = broker.Execute( sql );
for( int i = 0; i < sr.Rows.Count; i++ )
{
string column = sr.GetString( i, "column_name" );
FieldMap fm = map.GetFieldMapFromColumn( column );
bool isNullable = sr.GetString( i, "nulls" ).Equals( "Y" );
if( fm != null )
{
fm.ColumnId = sr.GetInt( i, "column_id" );
fm.SetDbType( sr.GetString( i, "domain_name" ), false );
fm.SetIsNullable( isNullable );
fm.SetSize( sr.GetInt( i, "width" ) );
}
else // raise an error if we've detected a database/type mismatch
{
// TODO disabled due to code restructuring
// Check.Verify( isNullable, Error.NoPropertyForNotNullColumn, column, map.Type );
}
}
}
private void UpdateTableMapWithColumnInformation( TableMap map )
{
SqlResult sr = broker.Execute( String.Format( selectColumns, map.TableName ), null, null );
// process result set using columns: cid, name, type, notnull, dflt_value, pk
for( int i=0; i<sr.RowsContained; i++ )
{
string columnName = sr.GetString( i, "name" );
FieldMap fm = map.GetFieldMapFromColumn( columnName );
if( fm == null )
{
fm = new FieldMap( map, columnName );
map.Fields.Add( fm );
}
// get basic column information
fm.SetDbType( sr.GetString( i, "type" ), false );
fm.SetIsNullable( ! sr.GetBoolean( i, "notnull" ) );
fm.SetIsPrimaryKey( sr.GetBoolean( i, "pk" ) );
fm.SetIsAutoGenerated( fm.IsPrimaryKey && (fm.Type == typeof(int) || fm.Type == typeof(long)) );
}
}
private void GetColumnData( TableMap map )
{
string sql = String.Format( selectColumns, map.TableId );
SqlResult sr = broker.Execute( sql );
for( int i = 0; i < sr.Rows.Count; i++ )
{
string column = sr.GetString( i, "column_name" );
FieldMap fm = map.GetFieldMapFromColumn( column );
if( fm == null )
{
fm = new FieldMap( map, column );
map.Fields.Add( fm );
}
bool isNullable = sr.GetString( i, "nulls" ).Equals( "Y" );
bool isAutoGenerated = sr.GetString( i, "default" ).ToLower() == "autoincrement";
fm.ColumnId = sr.GetInt( i, "column_id" );
fm.SetDbType( sr.GetString( i, "domain_name" ), false );
fm.SetIsNullable( isNullable );
fm.SetIsAutoGenerated( isAutoGenerated );
fm.SetSize( sr.GetInt( i, "width" ) );
}
}
private void GetColumnData( TableMap map )
{
string sql = String.Format( selectColumns, map.TableName );
SqlStatement stmt = broker.GetStatement( sql );
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
for( int i = 0; i < sr.Rows.Count; i++ )
{
// returns columns: Field, Type, Null, Key, Default, Extra
string columnName = sr.GetString( i, "Field" );
FieldMap fm = map.GetFieldMapFromColumn( columnName );
if( fm == null )
{
fm = new FieldMap( map, columnName );
map.Fields.Add( fm );
}
if( fm != null )
{
string typeinfo = sr.GetString( i, "Type" );
bool isUnsigned;
fm.SetDbType( ExtractType( typeinfo, out isUnsigned ), isUnsigned );
if( fm.DbType == (long) MySqlDbType.Enum )
{
fm.HandleEnumAsString = true;
}
fm.SetSize( ExtractSize( typeinfo ) );
fm.SetIsNullable( sr.GetString( i, "Null" ).Equals( "YES" ) );
fm.SetIsPrimaryKey( sr.GetString( i, "Key" ).Equals( "PRI" ) );
if( fm.IsPrimaryKey )
{
fm.SetIsAutoGenerated( sr.GetString( i, "Extra" ).Equals( "auto_increment" ) );
}
}
else // raise an error if we've detected a database/type mismatch
{
bool hasDefault = sr.GetObject( i, "Default" ) != null;
// TODO disabled due to code restructuring
// Check.Verify( isNullable || hasDefault, Error.NoPropertyForNotNullColumn, column, map.Type );
}
}
}
private void GetConstraintData( TableMap map )
{
SqlStatement stmt = broker.GetStatement( String.Format( selectConstraints, map.TableName ) );
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
if( sr.Rows.Count == 1 )
{
// returns columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length,
// Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Create_options,
// Comment (as "InnoDB free: 3072 kB; (ListId) REFER test/List(ListId)"
string comment = sr.GetString( 0, 1 ); // column 1 is either "Create table" or "Create view"
if( comment != null && comment.Length > 5 )
{
string[] comments = comment.Split( ',' );
foreach( string cmt in comments )
{
string tmp = cmt.Trim();
// samples:
// "(Column) REFER database/Table(Column)"
// "(`Column`) REFER `database/Table`(`Column`)"
string pattern = @"\(`?(?<column>\w+)`?\) REFER .*/(?<fkTable>\w+)[`\s]+\(`?(?<fkColumn>\w+)`?\)";
Regex regex = new Regex( pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled );
Match m = regex.Match( tmp );
if( m.Success )
{
FieldMap fm = map.GetFieldMapFromColumn( m.Groups[ "column" ].Value );
if( fm != null )
{
fm.SetForeignKeyTableName( m.Groups[ "fkTable" ].Value );
fm.SetForeignKeyColumnName( m.Groups[ "fkColumn" ].Value );
}
}
else
{
//CONSTRAINT `fk_employee_type_employee` FOREIGN KEY (`employee_type_id`) REFERENCES `employee_type` (`employee_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
pattern = @"[\s\w]FOREIGN KEY\s\(`?(?<column>\w+)`?\) REFERENCES `?(?<fkTable>\w+)`? \(`?(?<fkColumn>\w+)`?\)[\s\w]+";
Regex regexNew = new Regex( pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled );
Match mNew = regexNew.Match( tmp );
if( mNew.Success )
{
// string constraintPart = mNew.Groups["constraint"].Value;
FieldMap fm = map.GetFieldMapFromColumn( mNew.Groups[ "column" ].Value );
if( fm != null )
{
fm.SetForeignKeyTableName( mNew.Groups[ "fkTable" ].Value );
fm.SetForeignKeyColumnName( mNew.Groups[ "fkColumn" ].Value );
}
}
else if( tmp != null )
{
int index = tmp.IndexOf( "REFER" );
if( index > 0 )
{
string columnName = ExtractColumn( tmp.Substring( 0, index - 1 ) );
tmp = tmp.Substring( index + 5, tmp.Length - index - 5 ).Trim();
index = tmp.IndexOf( "/" );
int start = tmp.IndexOf( "(" );
int end = tmp.IndexOf( ")" );
if( index > 0 && start > 0 && end > start )
{
string foreignTable = tmp.Substring( index + 1, start - index - 1 );
string foreignColumn = tmp.Substring( start + 1, end - start - 1 );
FieldMap fm = map.GetFieldMapFromColumn( columnName );
fm.SetForeignKeyTableName( foreignTable );
fm.SetForeignKeyColumnName( foreignColumn );
}
}
}
}
}
}
}
}
private void GetColumnData( TableMap map )
{
string sql = String.Format( selectColumns, map.TableName );
SqlStatement stmt = broker.GetStatement( sql );
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
for( int i = 0; i < sr.Rows.Count; i++ )
{
// returns columns: Field, Type, TypeSize, FieldSize, NotNull, HasDefault, Default
string columnName = sr.GetString( i, "field" );
// get or create FieldMap for column
FieldMap fm = map.GetFieldMapFromColumn( columnName );
if( fm == null )
{
fm = new FieldMap( map, columnName );
map.Fields.Add( fm );
}
bool isNullable = sr.GetString( i, "notnull" ).Trim().ToLower().StartsWith( "f" );
if( fm != null )
{
bool hasDefault = sr.GetBoolean( i, "hasdefault" );
fm.SetDbType( sr.GetString( i, "type" ), false );
int size = ExtractSize( sr.GetInt( i, "typesize" ), sr.GetInt( i, "fieldsize" ) );
fm.SetSize( size );
fm.SetIsNullable( isNullable );
//fm.SetIsPrimaryKey( sr.GetString( i, "Key" ).Equals( "PRI" ) );
// fm.SetIsForeignKey( sr.GetString( i, "Key" ).Equals( "FOR" ) );
//if( fm.IsPrimaryKey )
// fm.SetIsAutoGenerated( sr.GetString( i, "Key" ).Equals( "auto_increment" ) );
//if( sr.GetString( i, "HasDefault" ).Equals( "t" ) )
// fm.SetMagicValue( sr.GetObject( i, "Default" ) );
}
}
}
/// <summary>
/// This method fills the TableMap with information on table columns.
/// </summary>
private void GetColumnData( TableMap map )
{
DataTable dt = GetColumns( map.TableName );
foreach( DataRow row in dt.Rows )
{
// result row contains:
// COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_HASDEFAULT, COLUMN_DEFAULT,
// COLUMN_FLAGS, IS_NULLABLE, NUMERIC_PRECISION, NUMERIC_SCALE,
// CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
string columnName = (string) row[ "COLUMN_NAME" ];
FieldMap fm = map.GetFieldMapFromColumn( columnName );
if( fm == null )
{
fm = new FieldMap( map, columnName );
map.Fields.Add( fm );
}
bool isNullable = Convert.ToBoolean( row[ "IS_NULLABLE" ] );
if( fm != null )
{
OleDbType dbType = (OleDbType) row[ "DATA_TYPE" ];
fm.SetDbType( (long) dbType );
// set numeric scale for DBTYPE_DECIMAL, DBTYPE_NUMERIC, DBTYPE_VARNUMERIC
if( dbType == OleDbType.Decimal || dbType == OleDbType.Numeric || dbType == OleDbType.VarNumeric )
{
fm.SetSize( Convert.ToInt32( row[ "NUMERIC_PRECISION" ] ) );
}
if( dbType == OleDbType.LongVarBinary || dbType == OleDbType.LongVarChar ||
dbType == OleDbType.LongVarWChar || dbType == OleDbType.VarBinary ||
dbType == OleDbType.VarChar || dbType == OleDbType.VarWChar ||
dbType == OleDbType.WChar || dbType == OleDbType.Char || dbType == OleDbType.BSTR ||
dbType == OleDbType.Binary )
{
fm.SetSize( Convert.ToInt32( row[ "CHARACTER_MAXIMUM_LENGTH" ] ) );
}
fm.SetIsNullable( isNullable );
int columnFlags = Convert.ToInt32( row[ "COLUMN_FLAGS" ] );
// BROKEN (expected value does not match IS_NULLABLE set above)
// BROKEN set whether column can contain NULL values
int flags = (int) DBCOLUMNFLAGS.ISNULLABLE + (int) DBCOLUMNFLAGS.MAYBENULL;
bool isNullableFlag = (columnFlags & flags) != 0;
//fm.SetIsNullable( isNullableFlag && fm.IsNullable );
// set whether column is updatable
flags = (int) DBCOLUMNFLAGS.WRITE + (int) DBCOLUMNFLAGS.WRITEUNKNOWN;
bool isReadOnly = (columnFlags & flags) == 0;
fm.IsReadOnly = isReadOnly;
// BROKEN (expected bitmask value is never set)
// set whether column is auto-generated
//flags = (int) DBCOLUMNFLAGS.ISROWID;
//bool isAutoGenerated = (columnFlags & flags) != 0;
//fm.SetIsAutoGenerated( isAutoGenerated );
}
else // raise an error if we've detected a database/type mismatch
{
bool hasDefault = Convert.ToBoolean( row[ "COLUMN_HASDEFAULT" ] );
// TODO disabled due to code restructuring
Check.Verify( isNullable || hasDefault, Error.NoPropertyForNotNullColumn, columnName, map.TableName );
}
}
}
