public static string LoadIdByField(this IDBConnection connection, ColumnSpec column, string value)
{
List<string> ids = connection.LoadIdsByConditions(
column.table,
new ComparisonCondition(
column,
ComparisonType.EQUAL,
value
),
Diapasone.unlimited
);
if(ids.Count > 1) {
throw new CriticalException("not unique");
} else if(ids.Count == 1) {
return ids[0];
} else {
throw new NotFoundInDBException(column, value);
}
}
private List<string> _LoadIdsByConditions(DbCommand command, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using(var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits);
string queryConditions = "";
if(conditionsCompiled.Key != "") queryConditions = "WHERE " + conditionsCompiled.Key;
ParamsHolder paramsHolder = conditionsCompiled.Value;
StringBuilder queryJoins = new StringBuilder();
{
foreach(var join in joins) {
queryJoins.Append(" JOIN ");
queryJoins.Append(join.additionalTableRaw.compile(this.traits));
queryJoins.Append(" ");
queryJoins.Append(join.additionalTable.compile(this.traits));
queryJoins.Append(" ON ");
queryJoins.Append(join.mainColumn.compile(this.traits));
queryJoins.Append(" = ");
queryJoins.Append(join.additionalTable.getIdSpec().compile(this.traits));
}
}
string querySorts = "";
if(sorts.Length > 0) {
List<string> sortParts = new List<string>();
foreach(SortSpec sortSpec in sorts) {
if(sortSpec.ascending) {
sortParts.Add(sortSpec.column.compile(this.traits) + " ASC");
} else {
sortParts.Add(sortSpec.column.compile(this.traits) + " DESC");
}
}
querySorts = "ORDER BY " + string.Join(", ", sortParts.ToArray());
}
string queryMain = "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions;
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
if(!diapasone.total.HasValue) {
command.CommandText = logger.commandText = "SELECT COUNT(*) " + queryMain;
object rawCount;
//try {
rawCount = command.ExecuteScalar();
//} catch(Npgsql.NpgsqlException e) {
//throw new FLocalException("Error while trying to execute " + command.CommandText + ": " + e.Message);
//}
long count = (long)rawCount;
if(count < 1) {
diapasone.total = 0;
} else {
diapasone.total = count;
}
}
if(diapasone.total.Value < 1) {
return new List<string>();
} else {
if(diapasone.total.Value > 1000 && diapasone.count < 0 && !allowHugeLists) {
throw new CriticalException("huge list");
}
string queryLimits = "";
if(diapasone.count >= 0) {
queryLimits = "LIMIT " + diapasone.count + " OFFSET " + diapasone.start;
}
command.CommandText = logger.commandText = "SELECT " + idSpec.compile(this.traits) + " " + queryMain + " " + querySorts + " " + queryLimits;
List<string> result = new List<string>();
using(DbDataReader reader = command.ExecuteReader()) {
while(reader.Read()) {
result.Add(reader.GetValue(0).ToString());
}
}
return result;
}
}
}
public List<string> LoadIdsByConditions(ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using(DbConnection connection = this.createConnection()) {
using(DbCommand command = connection.CreateCommand()) {
return this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, idSpec, allowHugeLists);
}
}
}
public SortSpec(ColumnSpec column, bool ascending)
{
this.column = column;
this.ascending = ascending;
}
public static ColumnOrValue createColumn(ColumnSpec column)
{
return new ColumnOrValue{_isColumn = true, _column = column};
}
public JoinSpec(ColumnSpec mainColumn, ITableSpec additionalTable, string alias)
{
this.mainColumn = mainColumn;
this.additionalTableRaw = additionalTable;
this.additionalTable = new TableSpec(additionalTable, alias);
}
public NotFoundInDBException(ColumnSpec columnSpec, string value)
: base("Object " + columnSpec.table.name + "[" + columnSpec.name + "=" + value + "] not found in db")
{
}
public static List<string> LoadIdsByConditions(this IDBConnection connection, ITableSpec table, AbstractCondition conditions, Diapasone diapasone, ColumnSpec idSpec, params SortSpec[] sorts)
{
return connection.LoadIdsByConditions(table, conditions, diapasone, new JoinSpec[0], sorts, idSpec, false);
}