// 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 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;
}
}
/// <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;
}
public MyQuery Clone()
{
var rtv = new MyQuery();
rtv._content.Append( _content );
foreach( var p in _parms )
{
rtv._parms.Add( new MyParameter
{
_type = p._type,
_pos = p._pos,
_content = p._content
} );
}
return rtv;
}
/// <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>
/// 生成 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;
}
/// <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;
}