| public SelectCountFrom ( string table ) : string | ||
| table | string | The table. |
| Résultat | string | |
private bool FileExists(DbTransaction transaction, string name)
{
ICommandBuilder builder = _sqlStorageProviderUtility.GetCommandBuilder2();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("SearchIndex");
query = queryBuilder.Where(query, "Wiki", WhereOperator.Equals, "Wiki");
query = queryBuilder.AndWhere(query, "Name", WhereOperator.Equals, "Name");
List <Parameter> parameters = new List <Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Wiki", _wiki));
parameters.Add(new Parameter(ParameterType.String, "Name", name));
DbCommand command = builder.GetCommand(transaction, query, parameters);
int count = _sqlStorageProviderUtility.ExecuteScalar <int>(command, -1, false);
return(count == 1);
}
/// <summary>
/// Determines whether a file exists.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="fullName">The file full name, for example "/file.txt" or "/directory/file.txt".</param>
/// <returns><c>true</c> if the file exists, <c>false</c> otherwise.</returns>
private bool FileExists(DbTransaction transaction, string fullName)
{
string directory, file;
SplitFileFullName(fullName, out directory, out file);
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("File");
query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");
query = queryBuilder.AndWhere(query, "Directory", WhereOperator.Equals, "Directory");
List<Parameter> parameters = new List<Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Name", file));
parameters.Add(new Parameter(ParameterType.String, "Directory", directory));
DbCommand command = builder.GetCommand(transaction, query, parameters);
int count = ExecuteScalar<int>(command, -1, false);
return count == 1;
}
/// <summary>
/// Determines whether a directory exists.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="directory">The directory, for example "/my/directory".</param>
/// <returns><c>true</c> if the directory exists, <c>false</c> otherwise.</returns>
/// <remarks>The root directory always exists.</remarks>
private bool DirectoryExists(DbTransaction transaction, string directory)
{
directory = PrepareDirectory(directory);
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("Directory");
query = queryBuilder.Where(query, "FullPath", WhereOperator.Equals, "FullPath");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "FullPath", directory));
DbCommand command = builder.GetCommand(transaction, query, parameters);
int count = ExecuteScalar<int>(command, -1, false);
return count == 1;
}
/// <summary>
/// Determines whether a page attachment exists.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="page">The page.</param>
/// <param name="name">The attachment.</param>
/// <returns><c>true</c> if the attachment exists, <c>false</c> otherwise.</returns>
private bool AttachmentExists(DbTransaction transaction, PageInfo page, string name)
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("Attachment");
query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");
query = queryBuilder.AndWhere(query, "Page", WhereOperator.Equals, "Page");
List<Parameter> parameters = new List<Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Name", name));
parameters.Add(new Parameter(ParameterType.String, "Page", page.FullName));
DbCommand command = builder.GetCommand(transaction, query, parameters);
int count = ExecuteScalar<int>(command, -1, false);
return count == 1;
}
/// <summary>
/// Gets the approximate size, in bytes, of the search engine index.
/// </summary>
private long GetSize()
{
// 1. Size of documents: 8 + 2*20 + 2*30 + 2*1 + 8 = 118 bytes
// 2. Size of words: 8 + 2*8 = 24 bytes
// 3. Size of mappings: 8 + 8 + 2 + 2 + 1 = 21 bytes
// 4. Size = Size * 2
ICommandBuilder builder = GetCommandBuilder();
DbConnection connection = builder.GetConnection(connString);
QueryBuilder queryBuilder = new QueryBuilder(builder);
long size = 0;
string query = queryBuilder.SelectCountFrom("IndexDocument");
DbCommand command = builder.GetCommand(connection, query, new List<Parameter>());
int rows = ExecuteScalar<int>(command, -1, false);
if(rows == -1) return 0;
size += rows * 118;
query = queryBuilder.SelectCountFrom("IndexWord");
command = builder.GetCommand(connection, query, new List<Parameter>());
rows = ExecuteScalar<int>(command, -1, false);
if(rows == -1) return 0;
size += rows * 24;
query = queryBuilder.SelectCountFrom("IndexWordMapping");
command = builder.GetCommand(connection, query, new List<Parameter>());
rows = ExecuteScalar<int>(command, -1, false);
if(rows == -1) return 0;
size += rows * 21;
CloseConnection(connection);
return size * 2;
}
/// <summary>
/// Gets the total number of Messages in a Page Discussion.
/// </summary>
/// <param name="page">The Page.</param>
/// <returns>The number of messages.</returns>
/// <exception cref="ArgumentNullException">If <paramref name="page"/> is <c>null</c>.</exception>
public int GetMessageCount(PageInfo page)
{
if(page == null) throw new ArgumentNullException("page");
ICommandBuilder builder = GetCommandBuilder();
DbConnection connection = builder.GetConnection(connString);
if(GetPage(connection, page.FullName) == null) {
CloseConnection(connection);
return -1;
}
string nspace, name;
NameTools.ExpandFullName(page.FullName, out nspace, out name);
if(nspace == null) nspace = "";
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("Message");
query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
query = queryBuilder.AndWhere(query, "Namespace", WhereOperator.Equals, "Namespace");
List<Parameter> parameters = new List<Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Page", name));
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));
DbCommand command = builder.GetCommand(connection, query, parameters);
int count = ExecuteScalar<int>(command, 0);
return count;
}
/// <summary>
/// Gets the number of elements in the index.
/// </summary>
/// <param name="element">The type of elements.</param>
/// <returns>The number of elements.</returns>
private int GetCount(IndexElementType element)
{
ICommandBuilder builder = GetCommandBuilder();
DbConnection connection = builder.GetConnection(connString);
QueryBuilder queryBuilder = new QueryBuilder(builder);
int count = 0;
string elemName = "";
if(element == IndexElementType.Documents) elemName = "IndexDocument";
else if(element == IndexElementType.Words) elemName = "IndexWord";
else if(element == IndexElementType.Occurrences) elemName = "IndexWordMapping";
else throw new NotSupportedException("Unsupported element type");
string query = queryBuilder.SelectCountFrom(elemName);
DbCommand command = builder.GetCommand(connection, query, new List<Parameter>());
count = ExecuteScalar<int>(command, -1, true);
return count;
}
/// <summary>
/// Prepares the plugin status row, if necessary.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="typeName">The Type name of the plugin.</param>
private void PreparePluginStatusRow(DbTransaction transaction, string typeName)
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("PluginStatus");
query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Name", typeName));
DbCommand command = builder.GetCommand(transaction, query, parameters);
int rows = ExecuteScalar<int>(command, -1, false);
if(rows == -1) return;
if(rows == 0) {
// Insert a neutral row (enabled, empty config)
query = queryBuilder.InsertInto("PluginStatus", new string[] { "Name", "Enabled", "Configuration" }, new string[] { "Name", "Enabled", "Configuration" });
parameters = new List<Parameter>(3);
parameters.Add(new Parameter(ParameterType.String, "Name", typeName));
parameters.Add(new Parameter(ParameterType.Boolean, "Enabled", true));
parameters.Add(new Parameter(ParameterType.String, "Configuration", ""));
command = builder.GetCommand(transaction, query, parameters);
ExecuteNonQuery(command, false);
}
}
/// <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);
}
}
}
/// <summary>
/// Verifies that a user exists.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="username">The username.</param>
/// <returns><c>true</c> if the user exists, <c>false</c> otherwise.</returns>
private bool UserExists(DbConnection connection, string username)
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectCountFrom("User");
query = queryBuilder.Where(query, "Username", WhereOperator.Equals, "Username");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Username", username));
DbCommand command = builder.GetCommand(connection, query, parameters);
int count = ExecuteScalar<int>(command, -1, false);
return count == 1;
}
