// todo: Min, Max
// todo: Exists
/// <summary>
/// 生成 select count( distinct colName ) from xxxx where .....
/// </summary>
/// <param name="t"></param>
/// <param name="col"></param>
/// <param name="distinct"></param>
/// <param name="sq"></param>
/// <returns></returns>
public static MyQuery Count( DbTable t, DbColumn col = null, bool distinct = false, MyQuery sq = null )
{
if( col != null && col.parent != t ) throw new Exception( "参数2: 字段 不属于 参数1: 表" );
var q = new MyQuery( "select count(" + ( distinct ? " distinct" : "" ) + @" " + ( col == null ? "*" : col.name ) + @" ) from ", t );
if( sq != null ) q.Append( sq );
return q;
}
public DbTable AddColumn( DbColumn c )
{
columns.Add( c );
nullflags.Add( new List<bool>() );
switch( c.dataType )
{
case DbDataTypes.Boolean:
columnDatas.Add( new List<bool>() ); break;
case DbDataTypes.Int8:
columnDatas.Add( new List<sbyte>() ); break;
case DbDataTypes.Int16:
columnDatas.Add( new List<short>() ); break;
case DbDataTypes.Int32:
columnDatas.Add( new List<int>() ); break;
case DbDataTypes.Int64:
columnDatas.Add( new List<long>() ); break;
case DbDataTypes.UInt8:
columnDatas.Add( new List<byte>() ); break;
case DbDataTypes.UInt16:
columnDatas.Add( new List<ushort>() ); break;
case DbDataTypes.UInt32:
columnDatas.Add( new List<uint>() ); break;
case DbDataTypes.UInt64:
columnDatas.Add( new List<ulong>() ); break;
case DbDataTypes.Float:
columnDatas.Add( new List<float>() ); break;
case DbDataTypes.Double:
columnDatas.Add( new List<double>() ); break;
case DbDataTypes.DateTime:
columnDatas.Add( new List<DateTime>() ); break;
case DbDataTypes.String:
columnDatas.Add( new List<string>() ); break;
case DbDataTypes.Bytes:
columnDatas.Add( new List<byte[]>() ); break;
default:
break;
}
return this;
}
/// <summary>
/// 生成 select min( colName ) from xxxx where .....
/// </summary>
public static MyQuery Min( DbColumn col, MyQuery sq = null )
{
var q = new MyQuery( "select min( ", col.name, " ) from ", col.parent );
if( sq != null ) q.Append( sq );
return q;
}
// 取 dr 中某字段的数据并返回
public static object GetValue( DbColumn col, MySqlDataReader r, int idx )
{
switch( col.dataType )
{
case DbDataTypes.Boolean:
return r.GetBoolean( idx );
case DbDataTypes.Int8:
return r.GetSByte( idx );
case DbDataTypes.Int16:
return r.GetInt16( idx );
case DbDataTypes.Int32:
return r.GetInt32( idx );
case DbDataTypes.Int64:
return r.GetInt64( idx );
case DbDataTypes.UInt8:
return r.GetByte( idx );
case DbDataTypes.UInt16:
return r.GetUInt16( idx );
case DbDataTypes.UInt32:
return r.GetUInt32( idx );
case DbDataTypes.UInt64:
return r.GetUInt64( idx );
case DbDataTypes.Float:
return r.GetFloat( idx );
case DbDataTypes.Double:
return r.GetDouble( idx );
case DbDataTypes.DateTime:
return r.GetDateTime( idx );
case DbDataTypes.String:
return r.GetString( idx );
case DbDataTypes.Bytes:
var len = (int)r.GetBytes( idx, 0, null, 0, 0 );
var buf = new byte[ len ];
r.GetBytes( idx, 0, buf, 0, len );
return buf;
default:
throw new Exception( "unsupported DbType" );
}
}
public static DbQueryResult GetInfo()
{
using( var conn = new MySqlConnection( connStr ) )
{
conn.Open();
var result = new DbQueryResult();
var _tables = result.tables;
result.affectedRows = 0;
result.schema = cfg.database;
var q = new MyQuery( "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = ", MyParameterDataTypes.String );
q[ 0 ].Assign( cfg.database );
var dt = MyUtils.ExecuteDbTable( conn, q.ToSql() );
for( int i = 0; i < dt.rowCount; ++i )
{
var dr = dt[ i ];
_tables.Add( new DbTable
{
name = dr[ "TABLE_NAME" ].ToString(),
comment = dr[ "TABLE_COMMENT" ].ToString()
} );
}
q.Assign( "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ", MyParameterDataTypes.String, " AND TABLE_NAME = ", MyParameterDataTypes.String );
q[ 0 ].Assign( cfg.database );
foreach( var t in _tables )
{
q[ 1 ].Assign( t.name );
dt = MyUtils.ExecuteDbTable( conn, q.ToSql() );
for( int i = 0; i < dt.rowCount; ++i )
{
var dr = dt[ i ];
var c = new DbColumn
{
name = (string)dr[ "COLUMN_NAME" ].ToString(),
columnIndex = i,
dataType = GetDataType( (string)dr[ "DATA_TYPE" ], (string)dr[ "COLUMN_TYPE" ] ),
nullable = (string)dr[ "IS_NULLABLE" ] == "YES",
comment = (string)dr[ "COLUMN_COMMENT" ],
autoIncrement = (string)dr[ "EXTRA" ] == "auto_increment",
timestamp = (string)dr[ "DATA_TYPE" ] == "timestamp" && (string)dr[ "COLUMN_DEFAULT" ] == "CURRENT_TIMESTAMP",
primaryKey = (string)dr[ "COLUMN_KEY" ] == "PRI",
parent = t
};
t.AddColumn( c );
}
}
return result;
}
}
