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); } } }