// 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;
}
/// <summary>
/// 生成 delete from t where pk1 = PT.type1 and pk2 = ....
/// </summary>
/// <param name="t"></param>
/// <param name="sq"></param>
/// <returns></returns>
public static MyQuery Delete( DbTable t, MyQuery sq = null )
{
var q = new MyQuery( "delete from ", t );
if( sq == null )
{
if( t.primaryKeys.Count == 0 ) throw new Exception( "无主键表无法生成删除条件" );
for( int i = 0; i < t.primaryKeys.Count; ++i )
{
q.Append( ( i == 0 ? " where " : " and " ), t.primaryKeys[ i ], " = ", t.primaryKeys[ i ].GetParmType() );
}
}
else if( !sq._empty ) q.Append( sq );
return q;
}
/// <summary>
/// 生成 ( select 1 from xxxx limit 1 )
/// </summary>
public static MyQuery Exists( DbTable t, MyQuery sq = null )
{
var q = new MyQuery( "select 1 from ", t );
if( sq != null ) q.Append( sq );
return q;
}
/// <summary>
/// 生成 update t set c1 = PT.type1, c2 = ... where pk1 = PT.type1 and pk2 = ....
/// </summary>
/// <param name="t">表</param>
/// <param name="updateCols">要更新的字段列表。如果不传则为除了自增和Timespan的所有字段</param>
/// <param name="sq">where部分的子查询。如果为 null 则按主键生成，如果 IsEmpty 将不生成 where 部分</param>
/// <returns>查询</returns>
public static MyQuery Update( DbTable t, IEnumerable<DbColumn> updateCols = null, MyQuery sq = null )
{
var q = new MyQuery( "update ", t, " set " );
if( updateCols == null ) updateCols = t.columns;
int i = 0;
foreach( var c in updateCols ) // 跳过 AutoIncrease 字段
{
if( c.autoIncrement ) continue;
if( i++ > 0 ) q.Append( ", " );
q.Append( c, " = ", c.GetParmType() );
}
if( sq == null )
{
if( t.primaryKeys.Count == 0 ) throw new Exception( "无主键表无法生成更新条件" );
i = 0;
for( ; i < t.primaryKeys.Count; ++i )
{
q.Append( ( i == 0 ? " where " : " and " ), t.primaryKeys[ i ], " = ", t.primaryKeys[ i ].GetParmType() );
}
}
else if( !sq._empty ) q.Append( sq );
return q;
}
/// <summary>
/// 生成 select c1, c2, ... from t
/// 如果传入的子查询为 null，则追加生成 where pk1 = PT.type1 and pk2 = PT.type2 ...（有主键的话）
/// 否则原样追加子查询 字串（希望 阻止生成 where 主键, 可传入 new MyQuery() ）
/// </summary>
/// <param name="calcFoundRows">如果使用了 limit 并希望同时返回 符合查找条件的总记录数，须传入 true</param>
/// <param name="t">表</param>
/// <param name="selectCols">字段列表。如果为空表示所有字段</param>
/// <param name="sq">可追加在 from xxx 后面的查询语句</param>
/// <param name="limit">追加最尾部的行数限制语句</param>
/// <returns>查询</returns>
public static MyQuery Select( bool calcFoundRows, DbTable t, IEnumerable<DbColumn> selectCols = null, MyQuery sq = null, Nullable<KeyValuePair<int, int>> limit = null )
{
var q = new MyQuery( "select " + ( calcFoundRows ? "SQL_CALC_FOUND_ROWS " : "" ) );
if( selectCols == null ) selectCols = t.columns;
foreach( var c in selectCols ) q.Append( c );
q.Append( " from ", t );
if( sq == null )
{
if( t.primaryKeys.Count > 0 )
{
for( int i = 0; i < t.primaryKeys.Count; ++i )
{
q.Append( ( i == 0 ? " where " : " and " ), t.primaryKeys[ i ], " = ", t.primaryKeys[ i ].GetParmType() );
}
}
}
else if( !sq._empty ) q.Append( sq );
if( limit != null )
{
q.Append( " limit ", limit.Value.Key, ", ", limit.Value.Value );
if( calcFoundRows )
{
q.Append( "; select FOUND_ROWS();" );
}
}
return q;
}
/// <summary>
/// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ), ( PT.typeN, ... ), ...
/// </summary>
/// <param name="replace">是否为替换模式</param>
/// <param name="t">表</param>
/// <param name="insertCols">要插入的字段列表（不能包含自增字段）。如果为空表示所有字段（跳过只读字段）</param>
/// <returns>查询</returns>
public static MyQuery Inserts( bool replace, DbTable t, int rowCount, IEnumerable<DbColumn> insertCols = null )
{
var q = new MyQuery( ( replace ? "replace" : "insert" ) + " into ", t, " ( " );
if( insertCols == null ) insertCols = t.columns;
foreach( var c in insertCols )
{
if( c.isReadonly ) continue; // 跳过 AutoIncrease 字段
q.AppendExceptTableName( c );
}
q.Append( " ) values " );
for( int i = 0; i < rowCount; ++i )
{
if( i > 0 ) q.Append( ", " );
q.Append( "( " );
foreach( var c in insertCols )
{
if( c.isReadonly ) continue; // 跳过 AutoIncrease 字段
q.Append( c.GetParmType() );
}
q.Append( " )" );
}
return q;
}
/// <summary>
/// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ); select c1, c2, ... 回读，自增主键将使用 LAST_INSERT_ID() 作为条件
/// </summary>
/// <param name="replace">是否为替换模式</param>
/// <param name="t">表</param>
/// <param name="insertCols">要插入的字段列表（不能包含自增字段）。如果为空表示所有字段（跳过只读字段）</param>
/// <param name="selectCols">回读字段列表。传入 null, 则不回读，不生成 ; select 这部分语句</param>
/// <returns>查询</returns>
public static MyQuery Insert( bool replace, DbTable t, IEnumerable<DbColumn> insertCols = null, IEnumerable<DbColumn> selectCols = null )
{
var q = new MyQuery( ( replace ? "replace" : "insert" ) + " into ", t, " ( " );
var sq = new MyQuery( " ) values ( " );
if( insertCols == null ) insertCols = t.columns;
foreach( var c in insertCols ) // 跳过 AutoIncrease 字段（指定要插入哪些字段，将无视数据库默认值，除了自增不应该无视）
{
if( c.isReadonly ) continue; //throw new Exception( "只读字段无法插入" );
q.AppendExceptTableName( c );
sq.Append( c.GetParmType() );
}
q.Append( sq );
q.Append( " )" );
if( selectCols != null )
{
if( t.primaryKeys.Count == 0 ) throw new Exception( "无主键的表无法于 insert 后 select 回插入行的数据" );
q.Append( ";" );
if( t.primaryKeys.Count( a => a.autoIncrement ) == 0 ) // 如果主键中不含自增字段，可直接使用 Select 的拼接物来做读回
{
q.Append( Select( false, t, selectCols ) );
}
else
{
sq.Clear();
if( t.primaryKeys.Count > 0 )
{
for( int i = 0; i < t.primaryKeys.Count; ++i )
{
var pk = t.primaryKeys[ i ];
sq.Append( ( i == 0 ? " where " : " and " ), pk, " = " );
if( pk.autoIncrement ) sq.Append( "LAST_INSERT_ID()" );
else sq.Append( pk.GetParmType() );
}
}
q.Append( Select( false, t, selectCols, sq ) );
}
}
return q;
}
// 将 SchemaTable 的数据转为 DbTable 的字段声明，创建 DbTable, 填充 _columns 并返回
public static DbTable NewDbTable( DataTable t )
{
if( t == null ) return null;
// 还可能关注 ColumnOrdinal, ColumnSize. ColumnOrdinal 是以 1 为起始的字段位置
var dt = new DbTable();
var column_ColumnName = t.Columns[ "ColumnName" ];
var column_DataType = t.Columns[ "DataType" ];
var column_AllowDBNull = t.Columns[ "AllowDBNull" ];
var column_ColumnSize = t.Columns[ "ColumnSize" ];
var column_ColumnOrdinal = t.Columns[ "ColumnOrdinal" ];
foreach( DataRow row in t.Rows )
{
var cn = (string)row[ column_ColumnName ];
var ca = (bool)row[ column_AllowDBNull ];
var ct = (Type)row[ column_DataType ];
var idx = (int)row[ column_ColumnOrdinal ] - 1;
if( ct.Name == "UInt64" && (int)row[ column_ColumnSize ] == 1 )
{
dt.AddColumn( new DbColumn { name = cn, dataType = DbDataTypes.Boolean, nullable = ca, columnIndex = idx } );
}
else
{
dt.AddColumn( new DbColumn { name = cn, dataType = GetDbType( ct ), nullable = ca, columnIndex = idx } );
}
}
return dt;
}
// 填充 rows
public static void FillDbTable( DbTable dt, MySqlDataReader r )
{
while( r.Read() )
{
var dr = dt.AddRow();
for( int i = 0; i < r.FieldCount; ++i )
{
if( dt.columns[ i ].nullable && r.IsDBNull( i ) )
{
dr[ i ].SetNull();
}
else
{
FillValue( dr, r, i );
}
}
}
}