public HashSet <DBIndex> Load(Tabloid tabloid)
{
HashSet <DBIndex> result = new HashSet <DBIndex>();
using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_index(_db, tabloid.Name)))
{
while (reader.Read())
{
result.Add(new DBIndex(_db)
{
Table = (DBTable)tabloid,
Name = (string)reader["name"],
isUnique = Convert.ToBoolean(reader["is_unique"])
});
}
}
foreach (DBIndex index in result)
{
using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_COLUMNS_index(_db, tabloid.Name, index.Name)))
{
while (reader.Read())
{
index.Columns.Add(tabloid.Columns.SingleOrDefault(c => c.Name == (string)reader["ColumnName"]).Name);
}
}
}
return(result);
}
public ListJson <DBItem> ExecuteRead(IDbCommand command, Tabloid tabloid)
{
using (DBReader reader = ExecuteCommand(command))
{
ListJson <DBItem> result = Read(reader, tabloid);
return(result);
}
}
public DBItem(DBConnection db, Tabloid tabloid = null, Dictionary <string, object> dict = null)
{
_db = db;
_tabloid = tabloid;
_properties = new Dictionary <string, object>();
_foreignKeys = new Dictionary <string, object>();
if (dict != null)
{
foreach (var pair in dict)
{
this[pair.Key] = pair.Value;
}
}
}
public HashSet <DBColumn> Load(Tabloid Tabloid)
{
// in cache
string tabloidRealName = _db.CommandSet.ToRealTableName(_db.Application, Tabloid.Name);
if (Cache.ContainsKey(tabloidRealName))
{
return(Cache[tabloidRealName]);
}
HashSet <DBColumn> result = new HashSet <DBColumn>();
using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_column(_db, Tabloid.Name)))
{
while (reader.Read())
{
string defaultValue = null;
if (reader["default"] != DBNull.Value)
{
defaultValue = Convert.ToString(reader["default"]).Trim(new char[] { '(', ')' }); // removes ('value')
if ((!defaultValue.StartsWith("'") || !defaultValue.EndsWith("'")) && defaultValue.ToUpper() != "NULL")
{
defaultValue = $"'{defaultValue}'";
}
}
result.Add(new DBColumn(_db)
{
Tabloid = Tabloid,
Name = (string)reader["name"],
Type = DataType.FromDBName((string)reader["typeName"], _db.Type),
MaxLength = reader["max_length"] != DBNull.Value ? Convert.ToInt32(reader["max_length"]) : -1,
IsNullable = Convert.ToBoolean(reader["is_nullable"]),
IsUnique = Convert.ToBoolean(reader["is_unique"] != DBNull.Value ? reader["is_unique"] : false) || (string)reader["name"] == DBCommandSet.PrimaryKey,
DefaultValue = defaultValue
//,Scale = Convert.ToInt32(reader["scale"])
});
}
}
Cache.Add(tabloidRealName, result);
return(result);
}
public ListJson <DBItem> Read(DBReader reader, Tabloid tabloid)
{
ListJson <DBItem> items = new ListJson <DBItem>();
while (reader.Read())
{
DBItem newItem = new DBItem(this, tabloid);
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
newItem[columnName] = reader[columnName];
}
items.Add(newItem);
}
return(items);
}
public virtual IDbCommand SELECT_count(DBConnection db, Tabloid table, Manager <Condition> conditions = null, Manager <Join> joins = null, GroupBy groupBy = null)
{
IDbCommand command = Command;
string conditionString = conditions != null && conditions.Any()
? $"WHERE {conditions.ToSql(this, command)}"
: null;
string groupByString = groupBy != null
? ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, table.Name, groupBy.Columns), false, true)
: null;
string tableString = groupBy != null
? $"(SELECT {groupByString} FROM {ToRealTableName(db.Application, table.Name)} GROUP BY {groupByString}) _table1"
: ToRealTableName(db.Application, table.Name);
command.CommandText = groupBy != null
? $"SELECT Count(*) count FROM (SELECT {groupByString} FROM {ToRealTableName(db.Application, table.Name)} {joins.ToSql(this, command)} {conditionString} GROUP BY {groupByString}) a"
: $"SELECT Count(*) count FROM {ToRealTableName(db.Application, table.Name)} {joins.ToSql(this, command)} {conditionString}";
return(command);
}
public HashSet <DBForeignKey> Load(Tabloid tabloid)
{
HashSet <DBForeignKey> result = new HashSet <DBForeignKey>();
using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_foreignKey(_db, tabloid.Name, true, false)))
{
while (reader.Read())
{
result.Add(new DBForeignKey(_db)
{
SourceTable = (tabloid as DBTable),
SourceColumn = (string)reader["sourceColumn"],
TargetTable = _db.Table(_db.CommandSet.FromRealTableName(_db.Application, (string)reader["targetTable"])),
TargetColumn = (string)reader["targetColumn"],
OnDelete = (string)reader["onDelete"],
OnUpdate = (string)reader["onUpdate"]
});
}
}
return(result);
}
public override IDbCommand SELECT(DBConnection db, Tabloid tabloid, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null)
{
MySqlCommand command = new MySqlCommand();
List <string> withTable = new List <string>();
/// get columns to select
List <Tuple <string, string> > columns = new List <Tuple <string, string> >();
// column are set
if (columnNames != null && columnNames.Any())
{
columns.AddRange(ColumnsToTuple(db.Application, tabloid.Name, columnNames));
}
// all columns
else
{
// origin table
columns.AddRange(tabloid.Columns.Select(c => new Tuple <string, string>(tabloid.Name, c.Name)));
// table not found - every table have some column
if (columns.Count == 0)
{
throw new Exception($"Tabulka/View [{tabloid.Name}]({tabloid.RealName}) nenalezena!");
}
// joined tables
foreach (Join join in joins)
{
columns.AddRange(new Tabloid(db)
{
Name = join.joinTableName
}.Columns.Select(c => new Tuple <string, string>(join.joinTableName, c.Name)));
}
}
List <Tuple <string, string> > whereColumns = new List <Tuple <string, string> >(columns);
foreach (Condition condition in conditions)
{
Tuple <string, string> tuple = ColumnsToTuple(db.Application, tabloid.Name, new List <string> {
condition.column
}).First();
if (!whereColumns.Any(wc => wc.Item1.ToLower() == tuple.Item1.ToLower() && wc.Item2.ToLower() == tuple.Item2.ToLower()))
{
whereColumns.Add(tuple);
}
}
/// SELECT FROM, JOIN
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, whereColumns)} FROM {ToRealTableName(db.Application, tabloid.Name)} {joins.ToSql(this, command)}";
/// WHERE, GROUP, HAVING
string conditionString = conditions.ToSql(this, command);
if (!string.IsNullOrEmpty(conditionString) || groupBy != null)
{
withTable.Add($"__table1 as ({command.CommandText})");
// WHERE
if (!string.IsNullOrEmpty(conditionString))
{
conditionString =
$"WHERE {conditionString}";
if (groupBy == null)
{
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString}";
}
}
// GROUP BY
if (groupBy != null)
{
string havingString = groupBy.Having.ToSql(this, command);
if (!string.IsNullOrEmpty(havingString))
{
havingString = $"HAVING {havingString}";
}
if (groupBy.Function == ESqlFunction.none)
{
columns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, columns, true, false)} {havingString}";
}
// first, last -> inner query
else if (groupBy.Function.NeedsInnerQuery())
{
command.CommandText =
$"SELECT * FROM __table1 WHERE {conditionString} {(string.IsNullOrEmpty(conditionString) ? "" : "AND")} {FullPrimaryKey(tabloid.Name)} IN (SELECT {(groupBy.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM __table1 GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString})";
}
// other functions
else
{
// get only numeric columns
if (groupBy.Function.RequireNumeric())
{
List <Tuple <string, string> > currentColumns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);
foreach (string tabloidName in columns.Select(c => c.Item1).Distinct())
{
Tabloid currentTabloid = tabloidName == tabloid.Name
? tabloid
: new Tabloid(db)
{
Name = tabloidName
};
currentColumns = currentColumns.Concat(ColumnsToTuple(db.Application, currentTabloid.Name, currentTabloid.Columns.Where(c => columns.Contains(new Tuple <string, string>(currentTabloid.Name, c.Name)) && DataType.IsNumeric(c.Type)).Select(c => c.Name))).ToList();
}
columns = currentColumns;
}
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false, groupBy.Function.ToString(), groupBy.Columns)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString}";
}
}
}
/// Drop step
if (dropStep != null)
{
if (dropStep.Order == null)
{
throw new ArgumentException("Order needs to be filled");
}
withTable.Add($"__table2 as ({command.CommandText})");
string rowNum = $"FLOOR((@__ROWNUM:=@__ROWNUM+1)*{dropStep.FinalCount}/(SELECT Count(*) FROM __table2)) _rowNum";
if (dropStep.Function == ESqlFunction.none)
{
throw new ArgumentException("DropStep with no function return no data!");
}
// first, last -> inner query
else if (dropStep.Function.NeedsInnerQuery())
{
command.CommandText =
$"SELECT * FROM __table2 WHERE {FullPrimaryKey(tabloid.Name)} IN (SELECT {(dropStep.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum)";
}
// other functions
else
{
command.CommandText =
$"SELECT {ColumnTuplesToString(db.Application, columns, true, false, dropStep.Function.ToString())} FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum";
}
}
string limitString = page != null
? $"LIMIT {page.RowsPerPage} OFFSET {page.PageIndex * page.RowsPerPage}"
: limit != null
? $"LIMIT {limit}"
: "";
command.CommandText =
$"{(withTable.Any() ? "with " : "")}{string.Join(",", withTable)} {command.CommandText} {order?.ToSql(this, command)} {limitString}";
return(command);
}
public EntityManager(DBConnection db, Tabloid tabloid)
{
_db = db;
_tabloid = tabloid;
}
public abstract IDbCommand SELECT(DBConnection db, Tabloid table, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null);