public IEnumerable <Punishment> getPunishments(Diapasone diapasone)
{
return(Punishment.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
Punishment.TableSpec.instance,
new ComplexCondition(
ConditionsJoinType.AND,
new ComparisonCondition(
Punishment.TableSpec.instance.getColumnSpec(Punishment.TableSpec.FIELD_OWNERID),
ComparisonType.EQUAL,
this.id.ToString()
),
new ComparisonCondition(
Punishment.TableSpec.instance.getColumnSpec(Punishment.TableSpec.FIELD_EXPIRES),
ComparisonType.GREATEROREQUAL,
DateTime.Now.ToUTCString()
),
new ComparisonCondition(
Punishment.TableSpec.instance.getColumnSpec(Punishment.TableSpec.FIELD_ISWITHDRAWED),
ComparisonType.EQUAL,
"0"
)
),
diapasone
) select int.Parse(stringId)
));
}
public IEnumerable <PMMessage> getMessages(Diapasone diapasone, UserContext context, bool isAscending)
{
diapasone.total = this.totalMessages;
return(PMMessage.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
PMMessage.TableSpec.instance,
new ComplexCondition(
ConditionsJoinType.AND,
new ComparisonCondition(
PMMessage.TableSpec.instance.getColumnSpec(PMMessage.TableSpec.FIELD_OWNERID),
ComparisonType.EQUAL,
this.ownerId.ToString()
),
new ComparisonCondition(
PMMessage.TableSpec.instance.getColumnSpec(PMMessage.TableSpec.FIELD_INTERLOCUTORID),
ComparisonType.EQUAL,
this.interlocutorId.ToString()
)
),
diapasone,
new JoinSpec[0],
new SortSpec[] {
new SortSpec(
PMMessage.TableSpec.instance.getIdSpec(),
isAscending
),
}
) select int.Parse(stringId)
));
}
public IEnumerable <Post> getMentions(Diapasone diapasone, bool isAscending)
{
return(Post.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
Mention.TableSpec.instance,
new ComparisonCondition(
Mention.TableSpec.instance.getColumnSpec(Mention.TableSpec.FIELD_MENTIONEDUSERID),
ComparisonType.EQUAL,
this.id.ToString()
),
diapasone,
Mention.TableSpec.instance.getColumnSpec(Mention.TableSpec.FIELD_POSTID),
new SortSpec(Mention.TableSpec.instance.getIdSpec(), isAscending)
) select int.Parse(stringId)
));
}
public IEnumerable <Thread> getThreads(Diapasone diapasone, bool isAscending)
{
diapasone.total = this.totalThreadsExcludingSubboards;
return(this.getThreads(
diapasone,
new SortSpec[] {
new SortSpec(
Thread.TableSpec.instance.getColumnSpec(Thread.TableSpec.FIELD_ISANNOUNCEMENT),
false
),
new SortSpec(
Thread.TableSpec.instance.getColumnSpec(Thread.TableSpec.FIELD_LASTPOSTID),
isAscending
),
}
));
}
public IEnumerable <Thread> getThreads(Diapasone diapasone, SortSpec[] sortBy)
{
diapasone.total = this.totalThreadsExcludingSubboards;
return(Thread.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
Thread.TableSpec.instance,
new ComparisonCondition(
Thread.TableSpec.instance.getColumnSpec(Thread.TableSpec.FIELD_BOARDID),
ComparisonType.EQUAL,
this.id.ToString()
),
diapasone,
new JoinSpec[0],
sortBy
) select int.Parse(stringId)
));
}
public static IEnumerable <User> getUsers(Diapasone diapasone, bool isAscending)
{
return(User.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
User.TableSpec.instance,
new EmptyCondition(),
diapasone,
new JoinSpec[0],
new SortSpec[] {
new SortSpec(
User.TableSpec.instance.getIdSpec(),
isAscending
),
}
) select int.Parse(stringId)
));
}
public static IEnumerable <PMConversation> getConversations(Account owner, Diapasone diapasone, bool isAscending)
{
return(LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
TableSpec.instance,
new ComparisonCondition(
TableSpec.instance.getColumnSpec(TableSpec.FIELD_OWNERID),
ComparisonType.EQUAL,
owner.id.ToString()
),
diapasone,
new JoinSpec[0],
new SortSpec[] {
new SortSpec(
TableSpec.instance.getColumnSpec(TableSpec.FIELD_LASTMESSAGEID),
isAscending
)
}
) select int.Parse(stringId)
));
}
public IEnumerable <Thread> getThreads(Diapasone diapasone, bool isAscending)
{
return(Thread.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
Thread.TableSpec.instance,
new ComparisonCondition(
Thread.TableSpec.instance.getColumnSpec(Thread.TableSpec.FIELD_TOPICSTARTERID),
ComparisonType.EQUAL,
this.id.ToString()
),
diapasone,
new JoinSpec[0],
new SortSpec[] {
new SortSpec(
Thread.TableSpec.instance.getColumnSpec(Thread.TableSpec.FIELD_LASTPOSTID),
isAscending
),
}
) select int.Parse(stringId)
));
}
public IEnumerable <Post> getPosts(Diapasone diapasone, bool isAscending)
{
diapasone.total = this.totalPosts;
return(Post.LoadByIds(
from stringId in Config.instance.mainConnection.LoadIdsByConditions(
Post.TableSpec.instance,
new ComparisonCondition(
Post.TableSpec.instance.getColumnSpec(Post.TableSpec.FIELD_THREADID),
ComparisonType.EQUAL,
this.id.ToString()
),
diapasone,
new JoinSpec[0],
new SortSpec[] {
new SortSpec(
Post.TableSpec.instance.getIdSpec(),
isAscending
),
}
) select int.Parse(stringId)
));
}
public List <string> LoadIdsByConditions(Web.Core.DB.Transaction _transaction, ITableSpec table, Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, bool allowHugeLists)
{
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
command.Transaction = transaction.sqltransaction;
return(this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, table.getIdSpec(), allowHugeLists));
}
}
}
public List <string> LoadIdsByConditions(ITableSpec table, 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));
}
}
}
private List <string> _LoadIdsByConditions(DbCommand command, ITableSpec table, 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);
}
}
}
