public NpgQuery DeleteWhere(string table, IWhereOp whereOp)
{
var parameters = new List<NpgsqlParameter>();
var text = string.Format("DELETE FROM {0} WHERE {1}",
Quote(table),
whereOp.Build(parameters));
return new NpgQuery(text, parameters.ToArray());
}
public NpgQuery DeleteWhere(string table, IWhereOp whereOp)
{
var parameters = new List <NpgsqlParameter>();
var text = string.Format("DELETE FROM {0} WHERE {1}",
Quote(table),
whereOp.Build(parameters));
return(new NpgQuery(text, parameters.ToArray()));
}
public NpgQuery Select(
string table, string[] columnsToReturn,
IWhereOp whereOp,
string[] columnsOfOrder,
bool asc,
int limit,
bool distinct)
{
var parameters = new List <NpgsqlParameter>();
var text = Select(parameters, table, columnsToReturn, whereOp, columnsOfOrder, asc, limit, distinct);
return(new NpgQuery(text, parameters.ToArray()));
}
public NpgQuery UpdateWhere(string table, IWhereOp whereOp, string[] columns, object[] values)
{
var setParameters = new NpgsqlParameter[columns.Length];
var set = new string[columns.Length];
for (var i = 0; i < set.Length; i++)
{
var column = columns[i];
var parameterName = SetParameterNamePrefix + column;
set[i] = Quote(column) + " = :" + parameterName;
setParameters[i] = new NpgsqlParameter(parameterName, values[i]);
}
var parameters = new List <NpgsqlParameter>();
var scriptRow = string.Format(
"UPDATE {0} SET {1} WHERE {2}",
Quote(table),
string.Join(", ", set),
whereOp.Build(parameters));
parameters.AddRange(setParameters);
return(new NpgQuery(scriptRow, parameters.ToArray()));
}
public string Select(
IList <NpgsqlParameter> parameters,
string table, string[] columnsToReturn,
IWhereOp whereOp,
string[] columnsOfOrder,
bool asc,
int limit,
bool distinct)
{
var text = new StringBuilder();
text.Append("SELECT ");
if (distinct)
{
text.Append("DISTINCT ");
}
text.AppendFormat(
"{0} FROM {1}",
string.Join(", ", columnsToReturn.Select(Quote)),
Quote(table));
if (whereOp != null)
{
text.AppendFormat(" WHERE {0}", whereOp.Build(parameters));
}
if (columnsOfOrder != null)
{
string sortDirection = asc ? " ASC" : " DESC";
text.AppendFormat(
" ORDER BY {0}",
string.Join(", ", columnsOfOrder.Select(c => Quote(c) + sortDirection)));
}
if (limit != -1)
{
text.AppendFormat(" LIMIT {0}", limit);
}
return(text.ToString());
}
public string Select(
IList<NpgsqlParameter> parameters,
string table, string[] columnsToReturn,
IWhereOp whereOp,
string[] columnsOfOrder,
bool asc,
int limit,
bool distinct)
{
var text = new StringBuilder();
text.Append("SELECT ");
if (distinct)
{
text.Append("DISTINCT ");
}
text.AppendFormat(
"{0} FROM {1}",
string.Join(", ", columnsToReturn.Select(Quote)),
Quote(table));
if (whereOp != null)
{
text.AppendFormat(" WHERE {0}", whereOp.Build(parameters));
}
if (columnsOfOrder != null)
{
var sortDirection = asc ? " ASC" : " DESC";
text.AppendFormat(
" ORDER BY {0}",
string.Join(", ", columnsOfOrder.Select(c => Quote(c) + sortDirection)));
}
if (limit != -1)
{
text.AppendFormat(" LIMIT {0}", limit);
}
return text.ToString();
}
public NpgQuery UpdateWhere(string table, IWhereOp whereOp, string[] columns, object[] values)
{
var setParameters = new NpgsqlParameter[columns.Length];
var set = new string[columns.Length];
for (var i = 0; i < set.Length; i++)
{
var column = columns[i];
var parameterName = SetParameterNamePrefix + column;
set[i] = Quote(column) + " = :" + parameterName;
setParameters[i] = new NpgsqlParameter(parameterName, values[i]);
}
var parameters = new List<NpgsqlParameter>();
var scriptRow = string.Format(
"UPDATE {0} SET {1} WHERE {2}",
Quote(table),
string.Join(", ", set),
whereOp.Build(parameters));
parameters.AddRange(setParameters);
return new NpgQuery(scriptRow, parameters.ToArray());
}
public NpgQuery Select(
string table, string[] columnsToReturn,
IWhereOp whereOp,
string[] columnsOfOrder,
bool asc,
int limit,
bool distinct)
{
var parameters = new List<NpgsqlParameter>();
var text = Select(parameters, table, columnsToReturn, whereOp, columnsOfOrder, asc, limit, distinct);
return new NpgQuery(text, parameters.ToArray());
}
private Alarm[] GetAlarms(
NpgsqlConnection connection, IWhereOp alarmsWhereOp, CriteriaType criteriaType, AlarmSampleId criteria, int maxCount)
{
IWhereOp whereOp;
bool asc;
switch (criteriaType)
{
case CriteriaType.DownFromInfinity:
whereOp = null;
asc = false;
break;
case CriteriaType.DownFrom:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Lt(DbStr.AlarmId, criteria.AlarmId)),
new Lt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = false;
break;
case CriteriaType.DownFromOrEqual:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Le(DbStr.AlarmId, criteria.AlarmId)),
new Lt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = false;
break;
case CriteriaType.UpFromInfinity:
whereOp = null;
asc = true;
break;
case CriteriaType.UpFrom:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Gt(DbStr.AlarmId, criteria.AlarmId)),
new Gt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = true;
break;
case CriteriaType.UpFromOrEqual:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Ge(DbStr.AlarmId, criteria.AlarmId)),
new Gt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = true;
break;
default:
throw new NotSupportedException("CriteriaType " + criteriaType);
}
if (alarmsWhereOp != null)
{
whereOp = whereOp == null ? alarmsWhereOp : new And(whereOp, alarmsWhereOp);
}
var query = _npgQueryHelper.Select(
_tableName, _columns, whereOp, new[] { DbStr.StartTime, DbStr.AlarmId }, asc, Math.Min(maxCount, MaxAlarmsToRetrieve));
var alarms = _npgHelper.ExecuteReader(connection, query, reader =>
new Alarm(reader.GetDateTimeFormTicks(0), reader.GetInt32(1))
{
EndTime = reader.GetNullableDateTimeFormTicks(2),
AcknowledgeTime = reader.GetNullableDateTimeFormTicks(3),
UserId = reader.GetNullableInt64(4)
});
return asc ? alarms.Reverse().ToArray() : alarms;
}
public Not(IWhereOp op)
{
_op = op;
}
public Not(IWhereOp op)
{
_op = op;
}
private Alarm[] GetAlarms(
NpgsqlConnection connection, IWhereOp alarmsWhereOp, CriteriaType criteriaType, AlarmSampleId criteria, int maxCount)
{
IWhereOp whereOp;
bool asc;
switch (criteriaType)
{
case CriteriaType.DownFromInfinity:
whereOp = null;
asc = false;
break;
case CriteriaType.DownFrom:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Lt(DbStr.AlarmId, criteria.AlarmId)),
new Lt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = false;
break;
case CriteriaType.DownFromOrEqual:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Le(DbStr.AlarmId, criteria.AlarmId)),
new Lt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = false;
break;
case CriteriaType.UpFromInfinity:
whereOp = null;
asc = true;
break;
case CriteriaType.UpFrom:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Gt(DbStr.AlarmId, criteria.AlarmId)),
new Gt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = true;
break;
case CriteriaType.UpFromOrEqual:
whereOp = new Or(
new And(new Eq(DbStr.StartTime, criteria.StartTime.Ticks), new Ge(DbStr.AlarmId, criteria.AlarmId)),
new Gt(DbStr.StartTime, criteria.StartTime.Ticks));
asc = true;
break;
default:
throw new NotSupportedException("CriteriaType " + criteriaType);
}
if (alarmsWhereOp != null)
{
whereOp = whereOp == null ? alarmsWhereOp : new And(whereOp, alarmsWhereOp);
}
var query = _npgQueryHelper.Select(
_tableName, _columns, whereOp, new[] { DbStr.StartTime, DbStr.AlarmId }, asc, Math.Min(maxCount, MaxAlarmsToRetrieve));
var alarms = _npgHelper.ExecuteReader(connection, query, reader =>
new Alarm(reader.GetDateTimeFormTicks(0), reader.GetInt32(1))
{
EndTime = reader.GetNullableDateTimeFormTicks(2),
AcknowledgeTime = reader.GetNullableDateTimeFormTicks(3),
UserId = reader.GetNullableInt64(4)
});
return(asc ? alarms.Reverse().ToArray() : alarms);
}
