public WhereIn ( string query, string column, string parameters ) : string | ||
query | string | The query. |
column | string | The column subject of the WHERE clause. |
parameters | string | The names of the parameters in the IN set. |
return | string |
/// <summary> /// Cuts the recent changes if necessary. /// </summary> private void CutRecentChangesIfNecessary() { ICommandBuilder builder = GetCommandBuilder(); DbConnection connection = builder.GetConnection(connString); DbTransaction transaction = BeginTransaction(connection); QueryBuilder queryBuilder = new QueryBuilder(builder); string query = queryBuilder.SelectCountFrom("RecentChange"); DbCommand command = builder.GetCommand(transaction, query, new List<Parameter>()); int rows = ExecuteScalar<int>(command, -1, false); int maxChanges = int.Parse(host.GetSettingValue(SettingName.MaxRecentChanges)); if(rows > maxChanges) { // Remove 10% of old changes to avoid 1-by-1 deletion every time a change is made int entriesToDelete = maxChanges / 10; if(entriesToDelete > rows) entriesToDelete = rows; //entriesToDelete += entriesToDelete / 10; // This code is not optimized, but it surely works in most DBMS query = queryBuilder.SelectFrom("RecentChange", new string[] { "Id" }); query = queryBuilder.OrderBy(query, new string[] { "Id" }, new Ordering[] { Ordering.Asc }); command = builder.GetCommand(transaction, query, new List<Parameter>()); DbDataReader reader = ExecuteReader(command); List<int> ids = new List<int>(entriesToDelete); if(reader != null) { while(reader.Read() && ids.Count < entriesToDelete) { ids.Add((int)reader["Id"]); } CloseReader(reader); } if(ids.Count > 0) { // Given that the IDs to delete can be many, the query is split in many chunks, each one deleting 50 items // This works-around the problem of too many parameters in a RPC call of SQL Server // See also CutLog for(int chunk = 0; chunk <= ids.Count / MaxParametersInQuery; chunk++) { query = queryBuilder.DeleteFrom("RecentChange"); List<string> parms = new List<string>(MaxParametersInQuery); List<Parameter> parameters = new List<Parameter>(MaxParametersInQuery); for(int i = chunk * MaxParametersInQuery; i < Math.Min(ids.Count, (chunk + 1) * MaxParametersInQuery); i++) { parms.Add("P" + i.ToString()); parameters.Add(new Parameter(ParameterType.Int32, parms[parms.Count - 1], ids[i])); } query = queryBuilder.WhereIn(query, "Id", parms.ToArray()); command = builder.GetCommand(transaction, query, parameters); if(ExecuteNonQuery(command, false) < 0) { RollbackTransaction(transaction); return; } } } } CommitTransaction(transaction); }
/// <summary> /// Reduces the size of the Log to the specified size (or less). /// </summary> /// <param name="size">The size to shrink the log to (in bytes).</param> private void CutLog(int size) { ICommandBuilder builder = GetCommandBuilder(); DbConnection connection = builder.GetConnection(connString); DbTransaction transaction = BeginTransaction(connection); QueryBuilder queryBuilder = new QueryBuilder(builder); string query = queryBuilder.SelectCountFrom("Log"); DbCommand command = builder.GetCommand(transaction, query, new List <Parameter>()); int rows = ExecuteScalar <int>(command, -1, false); if (rows == -1) { RollbackTransaction(transaction); return; } int estimatedSize = rows * EstimatedLogEntrySize; if (size < estimatedSize) { int difference = estimatedSize - size; int entriesToDelete = difference / EstimatedLogEntrySize; // Add 10% to avoid 1-by-1 deletion when adding new entries entriesToDelete += entriesToDelete / 10; if (entriesToDelete > 0) { // This code is not optimized, but it surely works in most DBMS query = queryBuilder.SelectFrom("Log", new string[] { "Id" }); query = queryBuilder.OrderBy(query, new string[] { "Id" }, new Ordering[] { Ordering.Asc }); command = builder.GetCommand(transaction, query, new List <Parameter>()); DbDataReader reader = ExecuteReader(command); List <int> ids = new List <int>(entriesToDelete); if (reader != null) { while (reader.Read() && ids.Count < entriesToDelete) { ids.Add((int)reader["Id"]); } CloseReader(reader); } if (ids.Count > 0) { // Given that the IDs to delete can be many, the query is split in many chunks, each one deleting 50 items // This works-around the problem of too many parameters in a RPC call of SQL Server // See also CutRecentChangesIfNecessary for (int chunk = 0; chunk <= ids.Count / MaxParametersInQuery; chunk++) { query = queryBuilder.DeleteFrom("Log"); List <string> parms = new List <string>(MaxParametersInQuery); List <Parameter> parameters = new List <Parameter>(MaxParametersInQuery); for (int i = chunk * MaxParametersInQuery; i < Math.Min(ids.Count, (chunk + 1) * MaxParametersInQuery); i++) { parms.Add("P" + i.ToString()); parameters.Add(new Parameter(ParameterType.Int32, parms[parms.Count - 1], ids[i])); } query = queryBuilder.WhereIn(query, "Id", parms.ToArray()); command = builder.GetCommand(transaction, query, parameters); if (ExecuteNonQuery(command, false) < 0) { RollbackTransaction(transaction); return; } } } CommitTransaction(transaction); } } }