| public OrderBy ( string query, string columns, Ordering orderings ) : string | ||
| query | string | The query. |
| columns | string | The columns to order by. |
| orderings | Ordering | The ordering directions for each column. |
| return | string | |
/// <summary>
/// Gets all the Log Entries, sorted by date/time (oldest to newest).
/// </summary>
/// <returns>The Log Entries.</returns>
public LogEntry[] GetLogEntries()
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Log", new string[] { "DateTime", "EntryType", "User", "Message", "Wiki" });
query = queryBuilder.OrderBy(query, new string[] { "DateTime" }, new Ordering[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connString, query, new List <Parameter>());
DbDataReader reader = ExecuteReader(command);
if (reader != null)
{
List <LogEntry> result = new List <LogEntry>(100);
while (reader.Read())
{
result.Add(new LogEntry(EntryTypeFromChar((reader["EntryType"] as string)[0]),
new DateTime(((DateTime)reader["DateTime"]).Ticks, DateTimeKind.Utc), reader["Message"] as string, reader["User"] as string, reader["Wiki"] as string));
}
CloseReader(command, reader);
return(result.ToArray());
}
else
{
return(null);
}
}
public override string[] List()
{
ICommandBuilder builder = _sqlStorageProviderUtility.GetCommandBuilder2();
DbConnection connection = builder.GetConnection(_connString);
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("SearchIndex", new string[] { "Name" });
query = queryBuilder.Where(query, "Wiki", WhereOperator.Equals, "Wiki");
query = queryBuilder.OrderBy(query, new[] { "Name" }, new[] { Ordering.Asc });
List <Parameter> parameters = new List <Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Wiki", _wiki));
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = _sqlStorageProviderUtility.ExecuteReader(command);
List <string> result = new List <string>(20);
if (reader != null)
{
while (reader.Read())
{
result.Add(reader["Name"] as string);
}
_sqlStorageProviderUtility.CloseReader(command, reader);
}
return(result.ToArray());
}
/// <summary>
/// Gets all the sub-namespaces.
/// </summary>
/// <returns>The sub-namespaces, sorted by name.</returns>
public NamespaceInfo[] GetNamespaces()
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
// select ... from Namespace left join Page on Namespace.DefaultPage = Page.Name where Namespace.Name <> '' and (Namespace.DefaultPage is null or Page.Namespace <> '')
string query = queryBuilder.SelectFrom("Namespace", "Page", "DefaultPage", "Name", Join.LeftJoin, new string[] { "Name", "DefaultPage" }, new string[] { "CreationDateTime" });
query = queryBuilder.Where(query, "Namespace", "Name", WhereOperator.NotEquals, "Empty1");
query = queryBuilder.AndWhere(query, "Namespace", "DefaultPage", WhereOperator.IsNull, null, true, false);
query = queryBuilder.OrWhere(query, "Page", "Namespace", WhereOperator.NotEquals, "Empty2", false, true);
query = queryBuilder.OrderBy(query, new[] { "Namespace_Name" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Empty1", ""));
parameters.Add(new Parameter(ParameterType.String, "Empty2", ""));
DbCommand command = builder.GetCommand(connString, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<NamespaceInfo> result = new List<NamespaceInfo>(10);
while(reader.Read()) {
string realName = reader["Namespace_Name"] as string;
string page = GetNullableColumn<string>(reader, "Namespace_DefaultPage", null);
PageInfo defaultPage = string.IsNullOrEmpty(page) ? null :
new PageInfo(NameTools.GetFullName(realName, page), this, (DateTime)reader["Page_CreationDateTime"]);
// The query returns duplicate entries if the main page of two or more namespaces have the same name
if(result.Find(n => { return n.Name.Equals(realName); }) == null) {
result.Add(new NamespaceInfo(realName, this, defaultPage));
}
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets all the categories of a page.
/// </summary>
/// <param name="page">The page.</param>
/// <returns>The categories, sorted by name.</returns>
/// <exception cref="ArgumentNullException">If <paramref name="page"/> is <c>null</c>.</exception>
public CategoryInfo[] GetCategoriesForPage(PageInfo page)
{
if(page == null) throw new ArgumentNullException("page");
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string nspace, pageName;
NameTools.ExpandFullName(page.FullName, out nspace, out pageName);
if(nspace == null) nspace = "";
string query = queryBuilder.SelectFrom("Category", "CategoryBinding", new string[] { "Name", "Namespace" }, new string[] { "Category", "Namespace" }, Join.LeftJoin,
new string[] { "Name", "Namespace" }, new string[] { "Page" });
query = queryBuilder.Where(query, "CategoryBinding", "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.AndWhere(query, "CategoryBinding", "Page", WhereOperator.Equals, "Page");
query = queryBuilder.OrderBy(query, new[] { "Category_Name" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(2);
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));
parameters.Add(new Parameter(ParameterType.String, "Page", pageName));
DbCommand command = builder.GetCommand(connString, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<CategoryInfo> result = new List<CategoryInfo>(20);
List<string> pages = new List<string>(50);
string prevName = "|||";
string name = null;
while(reader.Read()) {
name = reader["Category_Name"] as string;
if(name != prevName) {
if(prevName != "|||") {
result[result.Count - 1].Pages = pages.ToArray();
pages.Clear();
}
result.Add(new CategoryInfo(NameTools.GetFullName(reader["Category_Namespace"] as string, name), this));
}
prevName = name;
if(!IsDBNull(reader, "CategoryBinding_Page")) {
pages.Add(NameTools.GetFullName(reader["Category_Namespace"] as string, reader["CategoryBinding_Page"] as string));
}
}
CloseReader(command, reader);
if(result.Count > 0) result[result.Count - 1].Pages = pages.ToArray();
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets all the Pages in a namespace.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="nspace">The namespace (<c>null</c> for the root).</param>
/// <returns>All the Pages in the namespace. The array is not sorted.</returns>
private PageInfo[] GetPages(DbConnection connection, NamespaceInfo nspace)
{
string nspaceName = nspace != null ? nspace.Name : "";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Page");
query = queryBuilder.Where(query, "Namespace", WhereOperator.Equals, "Namespace");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspaceName));
query = queryBuilder.OrderBy(query, new[] { "Name" }, new[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<PageInfo> result = new List<PageInfo>(100);
while(reader.Read()) {
result.Add(new PageInfo(NameTools.GetFullName(reader["Namespace"] as string, reader["Name"] as string),
this, (DateTime)reader["CreationDateTime"]));
}
CloseReader(reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets the Page Messages.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="page">The Page.</param>
/// <returns>The list of the <b>first-level</b> Messages, containing the replies properly nested, sorted by date/time.</returns>
private Message[] GetMessages(DbConnection connection, PageInfo page)
{
if(GetPage(connection, page.FullName) == null) return null;
// 1. Load all messages in memory in a dictionary id->message
// 2. Build tree using ParentID
string nspace, name;
NameTools.ExpandFullName(page.FullName, out nspace, out name);
if(nspace == null) nspace = "";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Message", new string[] { "Id", "Parent", "Username", "Subject", "DateTime", "Body" });
query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
query = queryBuilder.AndWhere(query, "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.OrderBy(query, new string[] { "DateTime", "Id" }, new Ordering[] { Ordering.Asc, Ordering.Asc });
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);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
Dictionary<short, Message> allMessages = new Dictionary<short, Message>(50);
List<short> ids = new List<short>(50);
List<short?> parents = new List<short?>(50);
while(reader.Read()) {
Message msg = new Message((short)reader["Id"], reader["Username"] as string, reader["Subject"] as string,
(DateTime)reader["DateTime"], reader["Body"] as string);
ids.Add((short)msg.ID);
// Import from V2: parent = -1, otherwise null
if(!IsDBNull(reader, "Parent")) {
short par = (short)reader["Parent"];
if(par >= 0) parents.Add(par);
else parents.Add(null);
}
else parents.Add(null);
allMessages.Add((short)msg.ID, msg);
}
CloseReader(reader);
// Add messages to their parents and build the top-level messages list
List<Message> result = new List<Message>(20);
for(int i = 0; i < ids.Count; i++) {
short? currentParent = parents[i];
short currentId = ids[i];
if(currentParent.HasValue) {
List<Message> replies = new List<Message>(allMessages[currentParent.Value].Replies);
replies.Add(allMessages[currentId]);
allMessages[currentParent.Value].Replies = replies.ToArray();
}
else result.Add(allMessages[currentId]);
}
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets the user groups of a user.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="username">The username.</param>
/// <returns>The groups.</returns>
private string[] GetUserGroups(DbTransaction transaction, string username)
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("UserGroupMembership", new string[] { "UserGroup" });
query = queryBuilder.Where(query, "User", WhereOperator.Equals, "Username");
queryBuilder.OrderBy(query, new[] { "UserGroup_Name" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Username", username));
DbCommand command = builder.GetCommand(transaction, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(5);
while(reader.Read()) {
result.Add(reader["UserGroup"] as string);
}
CloseReader(reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// The the names of the pages with attachments.
/// </summary>
/// <returns>The names of the pages with attachments.</returns>
public string[] GetPagesWithAttachments()
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Attachment", new string[] { "Page" });
query = queryBuilder.GroupBy(query, new[] { "Page" });
query = queryBuilder.OrderBy(query, new[] { "Page" }, new[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connString, query, new List<Parameter>());
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(100);
while(reader.Read()) {
result.Add(reader["Page"] as string);
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Lists the Directories in the specified directory.
/// </summary>
/// <param name="transaction">A database transaction.</param>
/// <param name="directory">The full directory name, for example "/my/directory". Null, empty or "/" for the root directory.</param>
/// <returns>The list of Directories in the Directory.</returns>
private string[] ListDirectories(DbTransaction transaction, string directory)
{
directory = PrepareDirectory(directory);
ICommandBuilder builder = GetCommandBuilder();
if(!DirectoryExists(transaction, directory)) {
RollbackTransaction(transaction);
throw new ArgumentException("Directory does not exist", "directory");
}
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Directory", new string[] { "FullPath" });
query = queryBuilder.Where(query, "Parent", WhereOperator.Equals, "Parent");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Parent", directory));
query = queryBuilder.OrderBy(query, new[] { "FullPath" }, new[] { Ordering.Asc });
DbCommand command = builder.GetCommand(transaction, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(20);
while(reader.Read()) {
result.Add(reader["FullPath"] as string);
}
CloseReader(reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Adds a new Message to a Page.
/// </summary>
/// <param name="page">The Page.</param>
/// <param name="username">The Username.</param>
/// <param name="subject">The Subject.</param>
/// <param name="dateTime">The Date/Time.</param>
/// <param name="body">The Body.</param>
/// <param name="parent">The Parent Message ID, or -1.</param>
/// <returns>True if the Message is added successfully.</returns>
/// <exception cref="ArgumentNullException">If <paramref name="page"/>, <paramref name="username"/>, <paramref name="subject"/> or <paramref name="body"/> are <c>null</c>.</exception>
/// <exception cref="ArgumentException">If <paramref name="username"/> or <paramref name="subject"/> are empty.</exception>
/// <exception cref="ArgumentOutOfRangeException">If <paramref name="parent"/> is less than -1.</exception>
public bool AddMessage(PageInfo page, string username, string subject, DateTime dateTime, string body, int parent)
{
if(page == null) throw new ArgumentNullException("page");
if(username == null) throw new ArgumentNullException("username");
if(username.Length == 0) throw new ArgumentException("Username cannot be empty", "username");
if(subject == null) throw new ArgumentNullException("subject");
if(subject.Length == 0) throw new ArgumentException("Subject cannot be empty", "subject");
if(body == null) throw new ArgumentNullException("body"); // body can be empty
if(parent < -1) throw new ArgumentOutOfRangeException("parent", "Invalid Parent Message ID");
string nspace, name;
NameTools.ExpandFullName(page.FullName, out nspace, out name);
if(nspace == null) nspace = "";
ICommandBuilder builder = GetCommandBuilder();
DbConnection connection = builder.GetConnection(connString);
DbTransaction transaction = BeginTransaction(connection);
if(parent != -1 && FindMessage(GetMessages(transaction, page), parent) == null) {
RollbackTransaction(transaction);
return false;
}
QueryBuilder queryBuilder = new QueryBuilder(builder);
short freeId = -1;
string query = queryBuilder.SelectFrom("Message", new string[] { "Id" });
query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
query = queryBuilder.AndWhere(query, "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.OrderBy(query, new string[] { "Id" }, new Ordering[] { Ordering.Desc });
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(transaction, query, parameters);
freeId = ExecuteScalar<short>(command, -1, false);
if(freeId == -1) freeId = 0;
else freeId++;
query = queryBuilder.InsertInto("Message", new string[] { "Page", "Namespace", "Id", "Parent", "Username", "Subject", "DateTime", "Body" },
new string[] { "Page", "Namespace", "Id", "Parent", "Username", "Subject", "DateTime", "Body" });
parameters = new List<Parameter>(8);
parameters.Add(new Parameter(ParameterType.String, "Page", name));
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));
parameters.Add(new Parameter(ParameterType.Int16, "Id", freeId));
if(parent != -1) parameters.Add(new Parameter(ParameterType.Int16, "Parent", parent));
else parameters.Add(new Parameter(ParameterType.Int16, "Parent", DBNull.Value));
parameters.Add(new Parameter(ParameterType.String, "Username", username));
parameters.Add(new Parameter(ParameterType.String, "Subject", subject));
parameters.Add(new Parameter(ParameterType.DateTime, "DateTime", dateTime));
parameters.Add(new Parameter(ParameterType.String, "Body", body));
command = builder.GetCommand(transaction, query, parameters);
int rows = ExecuteNonQuery(command, false);
if(rows == 1) {
IndexMessage(page, freeId, subject, dateTime, body, transaction);
CommitTransaction(transaction);
return true;
}
else {
RollbackTransaction(transaction);
return false;
}
}
/// <summary>
/// Gets the recent changes of the Wiki.
/// </summary>
/// <returns>The recent Changes, oldest to newest.</returns>
public RecentChange[] GetRecentChanges()
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("RecentChange", new string[] { "Page", "Title", "MessageSubject", "DateTime", "User", "Change", "Description" });
query = queryBuilder.OrderBy(query, new string[] { "DateTime" }, new Ordering[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connString, query, new List<Parameter>());
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<RecentChange> result = new List<RecentChange>(100);
while(reader.Read()) {
result.Add(new RecentChange(reader["Page"] as string, reader["Title"] as string,
GetNullableColumn<string>(reader, "MessageSubject", ""),
(DateTime)reader["DateTime"], reader["User"] as string, RecentChangeFromChar(((string)reader["Change"])[0]),
GetNullableColumn<string>(reader, "Description", "")));
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets the outgoing links of a page.
/// </summary>
/// <param name="page">The full name of the page.</param>
/// <returns>The outgoing links.</returns>
/// <exception cref="ArgumentNullException">If <b>page</b> is <c>null</c>.</exception>
/// <exception cref="ArgumentException">If <b>page</b> is empty.</exception>
public string[] GetOutgoingLinks(string page)
{
if(page == null) throw new ArgumentNullException("page");
if(page.Length == 0) throw new ArgumentException("Page cannot be empty", "page");
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("OutgoingLink", new string[] { "Destination" });
query = queryBuilder.Where(query, "Source", WhereOperator.Equals, "Source");
query = queryBuilder.OrderBy(query, new[] { "Destination" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Source", page));
DbCommand command = builder.GetCommand(connString, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(20);
while(reader.Read()) {
result.Add(reader["Destination"] as string);
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets all the Log Entries, sorted by date/time (oldest to newest).
/// </summary>
/// <returns>The Log Entries.</returns>
public LogEntry[] GetLogEntries()
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Log", new string[] { "DateTime", "EntryType", "User", "Message" });
query = queryBuilder.OrderBy(query, new string[] { "DateTime" }, new Ordering[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connString, query, new List<Parameter>());
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<LogEntry> result = new List<LogEntry>(100);
while(reader.Read()) {
result.Add(new LogEntry(EntryTypeFromChar((reader["EntryType"] as string)[0]),
(DateTime)reader["DateTime"], reader["Message"] as string, reader["User"] as string));
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <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>
/// Gets all the Navigation Paths in a Namespace.
/// </summary>
/// <param name="nspace">The Namespace.</param>
/// <returns>All the Navigation Paths, sorted by name.</returns>
public NavigationPath[] GetNavigationPaths(NamespaceInfo nspace)
{
string nspaceName = nspace != null ? nspace.Name : "";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("NavigationPath", new string[] { "Name", "Namespace", "Page" });
query = queryBuilder.Where(query, "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.OrderBy(query, new string[] { "Namespace", "Name", "Number" }, new Ordering[] { Ordering.Asc, Ordering.Asc, Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspaceName));
DbCommand command = builder.GetCommand(connString, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<NavigationPath> result = new List<NavigationPath>(10);
string prevName = "|||";
string name;
string actualNamespace = "";
List<string> pages = new List<string>(10);
while(reader.Read()) {
name = reader["Name"] as string;
if(name != prevName) {
actualNamespace = reader["Namespace"] as string;
if(prevName != "|||") {
result[result.Count - 1].Pages = pages.ToArray();
pages.Clear();
}
result.Add(new NavigationPath(NameTools.GetFullName(actualNamespace, name), this));
}
prevName = name;
pages.Add(NameTools.GetFullName(actualNamespace, reader["Page"] as string));
}
if(result.Count > 0) {
result[result.Count - 1].Pages = pages.ToArray();
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets the Backup/Revision numbers of a Page.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="page">The Page to get the Backups of.</param>
/// <returns>The Backup/Revision numbers.</returns>
private int[] GetBackups(DbConnection connection, PageInfo page)
{
if(GetPage(connection, page.FullName) == null) {
return null;
}
string name, nspace;
NameTools.ExpandFullName(page.FullName, out nspace, out name);
if(nspace == null) nspace = "";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("PageContent", new string[] { "Revision" });
query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
query = queryBuilder.AndWhere(query, "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.AndWhere(query, "Revision", WhereOperator.GreaterThanOrEqualTo, "Revision");
query = queryBuilder.OrderBy(query, new[] { "Revision" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(3);
parameters.Add(new Parameter(ParameterType.String, "Page", name));
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));
parameters.Add(new Parameter(ParameterType.Int16, "Revision", FirstRevision));
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<int> result = new List<int>(100);
while(reader.Read()) {
result.Add((short)reader["Revision"]);
}
CloseReader(reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets all the Categories in a namespace.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="nspace">The namespace.</param>
/// <returns>All the Categories in the namespace. The array is not sorted.</returns>
private CategoryInfo[] GetCategories(DbConnection connection, NamespaceInfo nspace)
{
string nspaceName = nspace != null ? nspace.Name : "";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Category", "CategoryBinding", new string[] { "Name", "Namespace" }, new string[] { "Category", "Namespace" }, Join.LeftJoin,
new string[] { "Name", "Namespace" }, new string[] { "Page" });
query = queryBuilder.Where(query, "Category", "Namespace", WhereOperator.Equals, "Namespace");
query = queryBuilder.OrderBy(query, new string[] { "Category_Name", "CategoryBinding_Page" }, new Ordering[] { Ordering.Asc, Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Namespace", nspaceName));
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<CategoryInfo> result = new List<CategoryInfo>(20);
List<string> pages = new List<string>(50);
string prevName = "|||";
string name = null;
while(reader.Read()) {
name = reader["Category_Name"] as string;
if(name != prevName) {
if(prevName != "|||") {
result[result.Count - 1].Pages = pages.ToArray();
pages.Clear();
}
result.Add(new CategoryInfo(NameTools.GetFullName(reader["Category_Namespace"] as string, name), this));
}
prevName = name;
if(!IsDBNull(reader, "CategoryBinding_Page")) {
pages.Add(NameTools.GetFullName(reader["Category_Namespace"] as string, reader["CategoryBinding_Page"] as string));
}
}
CloseReader(reader);
if(result.Count > 0) result[result.Count - 1].Pages = pages.ToArray();
return result.ToArray();
}
else return null;
}
/// <summary>
/// Returns the names of the Attachments of a Page.
/// </summary>
/// <param name="connection">A database connection.</param>
/// <param name="pageInfo">The Page Info object that owns the Attachments.</param>
/// <returns>The names, or an empty list.</returns>
private string[] ListPageAttachments(DbConnection connection, PageInfo pageInfo)
{
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("Attachment", new string[] { "Name" });
query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Page", pageInfo.FullName));
query = queryBuilder.OrderBy(query, new[] { "Name" }, new[] { Ordering.Asc });
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(10);
while(reader.Read()) {
result.Add(reader["Name"] as string);
}
CloseReader(reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets a free element ID from the database.
/// </summary>
/// <param name="element">The element type.</param>
/// <param name="transaction">The current database transaction.</param>
/// <returns>The free element ID.</returns>
private uint GetFreeElementId(IndexElementType element, DbTransaction transaction)
{
if(element == IndexElementType.Occurrences) throw new ArgumentException("Element cannot be Occurrences", "element");
string table = element == IndexElementType.Documents ? "IndexDocument" : "IndexWord";
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom(table, new string[] { "Id" });
query = queryBuilder.OrderBy(query, new string[] { "Id" }, new Ordering[] { Ordering.Desc });
DbCommand command = builder.GetCommand(transaction, query, new List<Parameter>());
int id = ExecuteScalar<int>(command, -1, false);
if(id == -1) return 0;
else return (uint)id + 1;
}
/// <summary>
/// Lists the Files in the specified Directory.
/// </summary>
/// <param name="directory">The full directory name, for example "/my/directory". Null, empty or "/" for the root directory.</param>
/// <returns>The list of Files in the directory.</returns>
/// <exception cref="ArgumentException">If <paramref name="directory"/> does not exist.</exception>
public string[] ListFiles(string directory)
{
directory = PrepareDirectory(directory);
ICommandBuilder builder = GetCommandBuilder();
DbConnection connection = builder.GetConnection(connString);
if(!DirectoryExists(connection, directory)) {
CloseConnection(connection);
throw new ArgumentException("Directory does not exist", "directory");
}
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("File", new string[] { "Name" });
query = queryBuilder.Where(query, "Directory", WhereOperator.Equals, "Directory");
query = queryBuilder.OrderBy(query, new [] { "Name" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Directory", directory));
DbCommand command = builder.GetCommand(connection, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
List<string> result = new List<string>(20);
while(reader.Read()) {
result.Add(directory + reader["Name"] as string);
}
CloseReader(command, reader);
return result.ToArray();
}
else return null;
}
/// <summary>
/// Gets all the users that have the specified element in their data.
/// </summary>
/// <param name="key">The key of the data.</param>
/// <returns>The users and the data.</returns>
/// <exception cref="ArgumentNullException">If <b>key</b> is <c>null</c>.</exception>
/// <exception cref="ArgumentException">If <b>key</b> is empty.</exception>
public IDictionary<UserInfo, string> GetUsersWithData(string key)
{
if(key == null) throw new ArgumentNullException("key");
if(key.Length == 0) throw new ArgumentException("Key cannot be empty", "key");
ICommandBuilder builder = GetCommandBuilder();
QueryBuilder queryBuilder = new QueryBuilder(builder);
string query = queryBuilder.SelectFrom("User", "UserData", "Username", "User", Join.RightJoin,
new string[] { "Username", "DisplayName", "Email", "Active", "DateTime" }, new string[] { "Data" },
"UserGroupMembership", "User", Join.LeftJoin, new string[] { "UserGroup" });
query = queryBuilder.Where(query, "UserData", "Key", WhereOperator.Equals, "Key");
query = queryBuilder.OrderBy(query, new[] { "User_Username" }, new[] { Ordering.Asc });
List<Parameter> parameters = new List<Parameter>(1);
parameters.Add(new Parameter(ParameterType.String, "Key", key));
DbCommand command = builder.GetCommand(connString, query, parameters);
DbDataReader reader = ExecuteReader(command);
if(reader != null) {
Dictionary<UserInfo, string> result = new Dictionary<UserInfo, string>(100);
string prevUsername = "******";
UserInfo prevUser = null;
string username = null;
string displayName;
string email;
bool active;
DateTime dateTime;
string data;
List<string> groups = new List<string>(5);
while(reader.Read()) {
username = reader["User_Username"] as string;
if(username != prevUsername) {
// Set previous user's groups
if(prevUsername != "|||") {
prevUser.Groups = groups.ToArray();
groups.Clear();
}
// Read new data
displayName = GetNullableColumn<string>(reader, "User_DisplayName", null);
email = reader["User_Email"] as string;
active = (bool)reader["User_Active"];
dateTime = (DateTime)reader["User_DateTime"];
data = reader["UserData_Data"] as string;
// Append new user
prevUser = new UserInfo(username, displayName, email, active, dateTime, this);
result.Add(prevUser, data);
}
// Keep reading groups
prevUsername = username;
if(!IsDBNull(reader, "UserGroupMembership_UserGroup")) {
groups.Add(reader["UserGroupMembership_UserGroup"] as string);
}
}
if(prevUser != null) {
prevUser.Groups = groups.ToArray();
}
CloseReader(command, reader);
return result;
}
else return null;
}
