|
From: <tmy...@us...> - 2006-05-20 07:15:58
|
Revision: 36 Author: tmyroadctfig Date: 2006-05-20 00:15:35 -0700 (Sat, 20 May 2006) ViewCVS: http://svn.sourceforge.net/nmailserver/?rev=36&view=rev Log Message: ----------- Further work on local store. Modified Paths: -------------- NMail/branches/luke-dev/NMail/ILocalStore.cs NMail/branches/luke-dev/NMail/ILocalStoreData.cs NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs Modified: NMail/branches/luke-dev/NMail/ILocalStore.cs =================================================================== --- NMail/branches/luke-dev/NMail/ILocalStore.cs 2006-05-20 01:44:04 UTC (rev 35) +++ NMail/branches/luke-dev/NMail/ILocalStore.cs 2006-05-20 07:15:35 UTC (rev 36) @@ -473,14 +473,33 @@ /// <returns>The result of the attempt to delete a group.</returns> LocalStoreGroupResult DeleteGroup(IAuthenticationToken authToken, int groupId); - /// <summary> - /// Updates the any changes to the group into the local store. - /// </summary> - /// <param name="authToken">The authentication credentials.</param> - /// <param name="group">The group details to update.</param> - /// <returns>The result of the attempt to update the group.</returns> - LocalStoreGroupResult UpdateGroup(IAuthenticationToken authToken, LocalStoreGroup group); + /// <summary> + /// Renames the group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The Id of the group to delete.</param> + /// <param name="newName">The new name for the group.</param> + /// <returns>The result of the attempt to rename the group.</returns> + LocalStoreGroupResult RenameGroup(IAuthenticationToken authToken, LocalStoreGroup group); + /// <summary> + /// Adds a child group to the given group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The child group to add.</param> + /// <param name="parentId">The group to add the child to.</param> + /// <returns>The result of the attempt to add.</returns> + LocalStoreGroupResult AddGroupToGroup(IAuthenticationToken authToken, int parentId, int groupId); + + /// <summary> + /// Removes a child group from the given group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The child group to remove.</param> + /// <param name="parentId">The group to remove the child from.</param> + /// <returns>The result of the attempt to remove.</returns> + LocalStoreGroupResult RemoveGroupFromGroup(IAuthenticationToken authToken, int parentId, int groupId); + /// <summary> /// Adds a group to the given mail domain. /// </summary> @@ -557,29 +576,29 @@ /// <summary> /// The operation was completed successfully. /// </summary> - OkSuccessful, + OkSuccessful = 0, /// <summary> /// The create or rename failed because a folder with the same name already existed. /// </summary> - AlreadyExists, + AlreadyExists = 1, /// <summary> /// The folder was not deleted because it contained child folders. /// </summary> - HasChildren, + HasChildren = 2, /// <summary> /// The operation failed because the source folder was not found on the server. /// If the operation is on a subfolder that the user can't see due to permissions /// this will be returned. /// </summary> - NonExistent, + NonExistent = 3, /// <summary> /// The user is not permitted to perform the operation. /// </summary> - NotPermitted + NotPermitted = 4 } /// <summary> @@ -614,7 +633,17 @@ /// <summary> /// The user is not permitted to perform the operation. /// </summary> - NotPermitted = 5 + NotPermitted = 5, + + /// <summary> + /// The user cannot be created because there is existing folders with the same details. + /// </summary> + FoldersAlreadyExist = 6, + + /// <summary> + /// An unknown error occurred trying to perform the operation. + /// </summary> + UnkownError = 7 } /// <summary> Modified: NMail/branches/luke-dev/NMail/ILocalStoreData.cs =================================================================== --- NMail/branches/luke-dev/NMail/ILocalStoreData.cs 2006-05-20 01:44:04 UTC (rev 35) +++ NMail/branches/luke-dev/NMail/ILocalStoreData.cs 2006-05-20 07:15:35 UTC (rev 36) @@ -317,7 +317,7 @@ /// </summary> /// <param name="username">The username to lookup.</param> /// <returns>The user Id or null if non is found.</returns> - int GetUserId(string username); + int? GetUserId(string username); /// <summary> /// Creates a new user in the local store and creates an initial folder. @@ -393,24 +393,43 @@ LocalStoreGroupResult DeleteGroup(int groupId); /// <summary> - /// Updates the any changes to the group into the local store. - /// </summary> - /// <param name="group">The group details to update.</param> - /// <returns>The result of the attempt to update the group.</returns> - LocalStoreGroupResult UpdateGroup(LocalStoreGroup group); + /// Renames the group. + /// </summary> + /// <param name="groupId">The Id of the group to delete.</param> + /// <param name="newName">The new name for the group.</param> + /// <returns>The result of the attempt to rename the group.</returns> + LocalStoreGroupResult RenameGroup(int groupId, string newName); + + /// <summary> + /// Adds a child group to the given group. + /// </summary> + /// <param name="groupId">The child group to add.</param> + /// <param name="parentId">The group to add the child to.</param> + /// <returns>The result of the attempt to add.</returns> + LocalStoreGroupResult AddGroupToGroup(int parentId, int groupId); + + /// <summary> + /// Removes a child group from the given group. + /// </summary> + /// <param name="groupId">The child group to remove.</param> + /// <param name="parentId">The group to remove the child from.</param> + /// <returns>The result of the attempt to remove.</returns> + LocalStoreGroupResult RemoveGroupFromGroup(int parentId, int groupId); /// <summary> /// Adds a group to the given mail domain. /// </summary> /// <param name="groupId">The Id of the group to add.</param> /// <param name="mailDomainId">The Id of the mail domain to add the group to.</param> + /// <returns>The result of the attempt to add.</returns> LocalStoreGroupResult AddGroupToMailDomain(int groupId, int mailDomainId); /// <summary> /// Removes group from the given mail domain. /// </summary> /// <param name="groupId">The Id of the group to remove.</param> - /// <param name="mailDomainId">The Id of the mail domain to revome the group from.</param> + /// <param name="mailDomainId">The Id of the mail domain to revome the group from.</param> + /// <returns>The result of the attempt to remove.</returns> LocalStoreGroupResult RemoveGroupFromMailDomain(int groupId, int mailDomainId); #endregion Modified: NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs =================================================================== --- NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs 2006-05-20 01:44:04 UTC (rev 35) +++ NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs 2006-05-20 07:15:35 UTC (rev 36) @@ -1017,20 +1017,43 @@ /// <param name="authToken">The authentication credentials.</param> /// <param name="groupId">The Id of the group to delete.</param> /// <returns>The result of the attempt to delete a group.</returns> - public LocalStoreGroupResult DeleteGroup(IAuthenticationToken authToken, int groupId) { - return LocalStoreData.DeleteGroup(groupId); - } + public LocalStoreGroupResult DeleteGroup(IAuthenticationToken authToken, int groupId) { + return LocalStoreData.DeleteGroup(groupId); + } - /// <summary> - /// Updates the any changes to the group into the local store. - /// </summary> - /// <param name="authToken">The authentication credentials.</param> - /// <param name="group">The group details to update.</param> - /// <returns>The result of the attempt to update the group.</returns> - public LocalStoreGroupResult UpdateGroup(IAuthenticationToken authToken, LocalStoreGroup group) { - return LocalStoreData.UpdateGroup(group); - } + /// <summary> + /// Renames the group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The Id of the group to delete.</param> + /// <param name="newName">The new name for the group.</param> + /// <returns>The result of the attempt to rename the group.</returns> + public LocalStoreGroupResult RenameGroup(IAuthenticationToken authToken, LocalStoreGroup group) { + return LocalStoreData.RenameGroup(group.GroupId, group.Name); + } + /// <summary> + /// Adds a child group to the given group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The child group to add.</param> + /// <param name="parentId">The group to add the child to.</param> + /// <returns>The result of the attempt to add.</returns> + public LocalStoreGroupResult AddGroupToGroup(IAuthenticationToken authToken, int parentId, int groupId) { + return LocalStoreData.AddGroupToGroup(parentId, groupId); + } + + /// <summary> + /// Removes a child group from the given group. + /// </summary> + /// <param name="authToken">The authentication credentials.</param> + /// <param name="groupId">The child group to remove.</param> + /// <param name="parentId">The group to remove the child from.</param> + /// <returns>The result of the attempt to remove.</returns> + public LocalStoreGroupResult RemoveGroupFromGroup(IAuthenticationToken authToken, int parentId, int groupId) { + return LocalStoreData.RemoveGroupFromGroup(parentId, groupId); + } + /// <summary> /// Adds a group to the given mail domain. /// </summary> Modified: NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql =================================================================== --- NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql 2006-05-20 01:44:04 UTC (rev 35) +++ NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql 2006-05-20 07:15:35 UTC (rev 36) @@ -73,6 +73,28 @@ PRIMARY KEY (MailDomainId) ) TYPE=InnoDb; +DROP TABLE IF EXISTS UserMailDomainMap; +CREATE TABLE UserMailDomainMap ( + UserId INT NOT NULL, + MailDomainId INT NOT NULL, + INDEX UserId (UserId), + FOREIGN KEY (UserId) REFERENCES `User` (UserId) ON DELETE CASCADE, + INDEX MailDomainId (MailDomainId), + FOREIGN KEY (MailDomainId) REFERENCES MailDomain (MailDomainId) ON DELETE CASCADE, + PRIMARY KEY(UserId, MailDomainId) +) TYPE=InnoDb; + +DROP TABLE IF EXISTS GroupMailDomainMap; +CREATE TABLE GroupMailDomainMap ( + GroupId INT NOT NULL, + MailDomainId INT NOT NULL, + INDEX GroupId (GroupId), + FOREIGN KEY (GroupId) REFERENCES `Group` (GroupId) ON DELETE CASCADE, + INDEX ParentId (MailDomainId), + FOREIGN KEY (MailDomainId) REFERENCES MailDomain (MailDomainId) ON DELETE CASCADE, + PRIMARY KEY(GroupId, MailDomainId) +) TYPE=InnoDb; + DROP TABLE IF EXISTS MailboxMapping; CREATE TABLE MailboxMapping ( MailboxMappingId INT AUTO_INCREMENT NOT NULL, @@ -167,28 +189,305 @@ FOREIGN KEY (FolderId) REFERENCES Folder (FolderId) ON DELETE CASCADE ) TYPE=InnoDB; -DROP TABLE IF EXISTS MailDomain; -CREATE TABLE MailDomain ( - MailDomainId INT AUTO_INCREMENT NOT NULL, - PrimaryHost VARCHAR(256) NOT NULL, - AdditionalHosts LONGBLOB, - MailboxMappings LONGBLOB, - AllowedActions LONGBLOB, - DefaultActions LONGBLOB, - AllowedValidators LONGBLOB, - PRIMARY KEY(MailDomainId) -) TYPE=InnoDB; +delimiter // + -- ----------------------------------------------------------------------- -- --- Stored procedures +-- Folder related stored procedures -- -- ----------------------------------------------------------------------- -delimiter // +DROP PROCEDURE IF EXISTS CreateFolder // +CREATE PROCEDURE CreateFolder +( + ParentId INT, + NamespaceId INT, + Name VARCHAR(1000), + UserId INT, + OUT FolderId INT, + OUT Result INT +) +BEGIN + + START TRANSACTION; + + /* Check if the parameters are valid. */ + IF EXISTS (SELECT f.FolderId FROM Folder f WHERE f.FolderId = ParentId) THEN + + /* Check for duplicate folders. */ + IF NOT EXISTS (SELECT f.FolderId FROM Folder f WHERE f.Name LIKE Name) THEN + + INSERT INTO Folder (ParentFolderId, NamespaceId, Name, OwnerUserId, NextMessageId) VALUES (ParentId, NamespaceId, Name, UserId, 1); + SELECT LAST_INSERT_ID() INTO FolderId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* Folder already exists. */ + SELECT 1 INTO Result; + END IF; + + ELSE + /* No such parent. */ + SELECT 3 INTO Result; + END IF; + + COMMIT; +END +// +-- ----------------------------------------------------------------------- +-- +-- User related stored procedures +-- +-- ----------------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS GetUserFromName // +CREATE PROCEDURE GetUserFromName +( + Name VARCHAR(100), + OUT UserId INT, + OUT QuotaHardLimit INT, + OUT QuotaWarnLimit INT, + OUT UserFolderId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + IF EXISTS (SELECT u.UserId FROM `User` u WHERE u.Name LIKE Name) THEN + + SELECT + u.UserId, u.QuotaHardLimit, u.QuotaWarnLimit, u.UserFolderId + INTO + UserId, QuotaHardLimit, QuotaWarnLimit, UserFolderId + FROM + `User` u + WHERE + u.Name LIKE Name; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + +DROP PROCEDURE IF EXISTS GetUserFromId // +CREATE PROCEDURE GetUserFromId +( + UserId INT, + OUT Name VARCHAR(100), + OUT QuotaHardLimit INT, + OUT QuotaWarnLimit INT, + OUT UserFolderId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + IF EXISTS (SELECT u.UserId FROM `User` u WHERE u.UserId = UserId) THEN + + SELECT + u.Name, u.QuotaHardLimit, u.QuotaWarnLimit, u.UserFolderId + INTO + Name, QuotaHardLimit, QuotaWarnLimit, UserFolderId + FROM + `User` u + WHERE + u.UserId = UserId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + +DROP PROCEDURE IF EXISTS GetUserIds // +CREATE PROCEDURE GetUserIds +() +BEGIN + + SELECT u.UserId FROM `User` u; + +END +// + +DROP PROCEDURE IF EXISTS CreateUser // +CREATE PROCEDURE CreateUser +( + Name VARCHAR(100), + QuotaHardLimit INT, + QuotaWarnLimit INT, + OUT UserId INT, + OUT Result INT +) +BEGIN + DECLARE NamespaceId INT; + DECLARE InboxName VARCHAR(1000); + DECLARE UserFolderId INT; + DECLARE UserInboxId INT; + DECLARE CreateResult INT; + + SET NamespaceId = 1; + + START TRANSACTION; + + /* Check if the name is valid. */ + IF NOT EXISTS (SELECT u.UserId FROM User u WHERE u.Name LIKE Name) THEN + + SET InboxName = CONCAT_WS(".", Name, "INBOX"); + + /* Check if the user's folders exist */ + IF NOT EXISTS (SELECT * FROM Folder f WHERE f.Name LIKE Name and f.NamespaceId = NamespaceId) AND + NOT EXISTS (SELECT * FROM Folder f WHERE f.Name LIKE InboxName and f.NamespaceId = NamespaceId) THEN + + INSERT INTO User (Name, QuotaHardLimit, QuotaWarnLimit) VALUES (Name, QuotaHardLimit, QuotaWarnLimit); + SELECT LAST_INSERT_ID() INTO UserId; + + /* Create the user's base folder. */ + CALL CreateFolder(NULL, NamespaceId, Name, UserId, UserFolderId, CreateResult); + + IF CreateResult = 0 THEN + + /* Create an INBOX for the user. */ + CALL CreateFolder(UserFolderId, NamespaceId, InboxName, UserId, UserInboxId, CreateResult); + + IF CreateResult = 0 THEN + + /* Update the user to have the correct folder. */ + UPDATE User u SET u.UserFolderId = UserFolderId WHERE u.UserId = UserId; + + /* Ok successful. */ + SELECT 0 INTO Result; + COMMIT; + + ELSE + /* Error creating folder. Return unknown error. */ + SELECT 7 INTO Result; + ROLLBACK; + END IF; + + ELSE + /* Error creating folder. Return unknown error. */ + SELECT 7 INTO Result; + ROLLBACK; + END IF; + + ELSE + /* Folder's already exist. */ + SELECT 6 INTO Result; + ROLLBACK; + END IF; + + ELSE + /* Already exists. */ + SELECT 1 INTO Result; + COMMIT; + END IF; +END +// + +DROP PROCEDURE IF EXISTS DeleteUser // +CREATE PROCEDURE DeleteUser +( + UserId INT + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the user Id is valid. */ + IF EXISTS (SELECT u.UserId FROM `User` u WHERE u.UserId = UserId) THEN + + /* Check if the user still has folders. */ + IF NOT EXISTS (SELECT * FROM Folder WHERE OwnerUserId = UserId) THEN + + DELETE FROM `User` WHERE UserId = UserId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* User still has folders. */ + SELECT 4 INTO Result; + END IF; + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + +DROP PROCEDURE IF EXISTS UpdateUser // +CREATE PROCEDURE UpdateUser +( + UserId INT, + Name VARCHAR(100), + QuotaHardLimit INT, + QuotaWarnLimit INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the user Id is valid. */ + IF EXISTS (SELECT u.UserId FROM `User` u WHERE u.UserId = UserId) THEN + + /* Check if the new name is valid. */ + IF EXISTS (SELECT u.Name FROM `User` u WHERE u.UserId = UserId AND u.Name LIKE Name) OR + NOT EXISTS (SELECT U.Name FROM `User` u WHERE u.Name LIKE Name) THEN + + UPDATE + `User` u + SET + u.Name = Name, + u.QuotaHardLimit = QuotaHardLimit, + u.QuotaWarnLimit = QuotaWarnLimit + WHERE + u.UserId = UserId; + + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* Already exists. */ + SELECT 1 INTO Result; + END IF; + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + + +-- ----------------------------------------------------------------------- +-- +-- Group related stored procedures +-- +-- ----------------------------------------------------------------------- + DROP PROCEDURE IF EXISTS GetGroupFromName // CREATE PROCEDURE GetGroupFromName ( @@ -222,7 +521,7 @@ SELECT 0 INTO Result; ELSE - /* No such user. */ + /* No such group. */ SELECT 2 INTO Result; END IF; @@ -263,7 +562,7 @@ SELECT 1 INTO Result; ELSE - /* No such user. */ + /* No such group. */ SELECT 2 INTO Result; END IF; @@ -291,12 +590,12 @@ BEGIN START TRANSACTION; - /* Check if the Id is valid. */ - IF NOT EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) THEN + /* Check if the name is valid. */ + IF NOT EXISTS (SELECT g.GroupId FROM `Group` g WHERE g.Name LIKE Name) THEN INSERT INTO `Group` (Name) VALUES (Name); - SELECT g.GroupId INTO GroupId FROM `Group` g WHERE g.GroupId = LAST_INSERT_ID(); + SELECT LAST_INSERT_ID() INTO GroupId; /* Ok successful. */ SELECT 0 INTO Result; @@ -336,8 +635,8 @@ END // -DROP PROCEDURE IF EXISTS UpdateGroup // -CREATE PROCEDURE UpdateGroupName +DROP PROCEDURE IF EXISTS RenameGroup // +CREATE PROCEDURE RenameGroup ( GroupId INT, Name VARCHAR(100), @@ -350,12 +649,12 @@ IF EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) THEN /* Check if the name has changed. */ - IF NOT EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId AND g.Name = Name) THEN + IF NOT EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId AND g.Name LIKE Name) THEN /* Check if the name already exists. */ IF NOT EXISTS (SELECT g.Name FROM `Group` g WHERE g.Name LIKE Name) THEN - UPDATE `Group` g SET g.Name = Name; + UPDATE `Group` g SET g.Name = Name WHERE g.GroupId = GroupId; /* Ok successful. */ SELECT 0 INTO Result; @@ -378,6 +677,70 @@ END // +DROP PROCEDURE IF EXISTS AddGroupToGroup // +CREATE PROCEDURE AddGroupToGroup +( + ParentId INT, + GroupId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the Ids is valid. */ + IF EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) AND + EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = ParentId) THEN + + /* Check if an entry already exists */ + IF NOT EXISTS (SELECT * FROM GroupGroupMap WHERE g.GroupId = GroupId AND g.ParentId = ParentId) THEN + + INSERT INTO GroupGroupMap (GroupId, ParentId) VALUES (GroupId, ParentId); + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* Already exists. */ + SELECT 1 INTO Result; + END IF; + + ELSE + /* No such group. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + +DROP PROCEDURE IF EXISTS RemoveGroupFromGroup // +CREATE PROCEDURE RemoveGroupFromGroup +( + ParentId INT, + GroupId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the Ids is valid. */ + IF EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) AND + EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = ParentId) THEN + + DELETE FROM GroupGroupMap WHERE GroupId = GroupId AND ParentId = ParentId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* No such group. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + delimiter ; Modified: NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs =================================================================== --- NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs 2006-05-20 01:44:04 UTC (rev 35) +++ NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs 2006-05-20 07:15:35 UTC (rev 36) @@ -33,7 +33,6 @@ using NMail.LocalStoreData.MySql.Configuration; // TODO: implement quotas -// TODO: case insensitive folder names? // TODO: password expiry // TODO: account lockout if too many bad attempts @@ -60,7 +59,7 @@ userIds.Add(3); LocalStoreGroup g = m.GetGroup("Administrators"); g.UserIds = userIds.ToArray(); - m.UpdateGroup(g); + //m.UpdateGroup(g); g = m.GetGroup("Administrators"); } @@ -234,7 +233,7 @@ private int? getStoreFolderId(Folder folder, MySqlConnection cnn, MySqlTransaction transaction) { using (MySqlCommand cmd = cnn.CreateCommand()) { cmd.Transaction = transaction; - cmd.CommandText = "SELECT f.FolderId FROM Namespace n, Folder f WHERE n.NamespaceId = f.NamespaceId AND n.Name = ?Namespace AND f.Name = ?FolderName"; + cmd.CommandText = "SELECT f.FolderId FROM Namespace n, Folder f WHERE n.NamespaceId = f.NamespaceId AND n.Name = ?Namespace AND f.Name LIKE ?FolderName"; cmd.Parameters.Add("FolderName", folder.FolderName); cmd.Parameters.Add("Namespace", folder.NameSpace); @@ -325,8 +324,9 @@ try { trans = cnn.BeginTransaction(); - int userId = getUserId(userName, cnn, trans); - StoreFolder result = createFolder(userId, parent, newFolder, cnn, null); + int? userId = getUserId(userName, cnn, trans); + // TODO: handle invalid userId properly + StoreFolder result = createFolder(userId.Value, parent, newFolder, cnn, null); trans.Commit(); return result; @@ -884,8 +884,12 @@ } #endregion - #region Load and Save Object - + #region Load and Save Object + /// <summary> + /// Gets a serialized object that can be used by either an validation or a delivery action. + /// </summary> + /// <param name="key">A key to identify which object to get.</param> + /// <returns>The object.</returns> public object LoadObject(string key) { using (MySqlConnection cnn = GetConnection()) { using (MySqlCommand cmd = cnn.CreateCommand()) { @@ -896,8 +900,12 @@ return SerializationHelper.Deserialize(data); } } - } + } + /// <summary> + /// Serializes an object that can be used later by either an validation or a delivery action. + /// </summary> + /// <param name="key">A key to identify the object at load time.</param> public void SaveObject(object o, string key) { using (MySqlConnection cnn = GetConnection()) { MySqlTransaction trans = cnn.BeginTransaction(); @@ -926,35 +934,67 @@ #region User Management #region Get User - public LocalStoreUser GetUser(string username) { - using (MySqlConnection cnn = GetConnection()) { - using (MySqlCommand cmd = cnn.CreateCommand()) { - cmd.CommandText = "SELECT u.UserId, u.QuotaHardLimit, u.QuotaWarnLimit, u.UserFolderId FROM User u WHERE u.Username = ?Username"; - cmd.Parameters.Add("Username", username); + /// <summary> + /// Gets the user for the given username. + /// </summary> + /// <param name="username">The username to look up.</param> + /// <returns>The matching user or null if non is found.</returns> + public LocalStoreUser GetUser(string name) { + using (MySqlConnection cnn = GetConnection()) { + return getUser(name, cnn, null); + } + } - using (MySqlDataReader reader = cmd.ExecuteReader()) { - if (reader.Read()) { - int userId = (int)reader["UserId"]; - object quotaHardLimit = reader["QuotaHardLimit"]; - object quotaWarnLimit = reader["QuotaWarnLimit"]; - int userFolderId = (int)reader["UserFolderId"]; + private LocalStoreUser getUser(string name, MySqlConnection cnn, MySqlTransaction transaction) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.Transaction = transaction; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetUserFromName"; + cmd.Parameters.Add("Name", name); + cmd.Parameters.Add("UserId", MySqlDbType.Int32); + cmd.Parameters.Add("QuotaHardLimit", MySqlDbType.Int32); + cmd.Parameters.Add("QuotaWarnLimit", MySqlDbType.Int32); + cmd.Parameters.Add("UserFolderId", MySqlDbType.Int32); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["UserId"].Direction = ParameterDirection.Output; + cmd.Parameters["QuotaHardLimit"].Direction = ParameterDirection.Output; + cmd.Parameters["QuotaWarnLimit"].Direction = ParameterDirection.Output; + cmd.Parameters["UserFolderId"].Direction = ParameterDirection.Output; + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - int? quotaHardLimitVal = (quotaHardLimit == DBNull.Value) ? null : (int?)quotaHardLimit; - int? quotaWarnLimitVal = (quotaWarnLimit == DBNull.Value) ? null : (int?)quotaWarnLimit; + cmd.ExecuteNonQuery(); + + int resultValue = (int) cmd.Parameters["Result"].Value; + LocalStoreUserResult result = (LocalStoreUserResult) resultValue; - return new LocalStoreUser(username, - userId, - userFolderId, - quotaWarnLimitVal, - quotaHardLimitVal); - } + // Check if the lookup succeeded + if (result != LocalStoreUserResult.OkSuccessful) { + return null; + } + + // Parse out all the returned values + int userId = (int) cmd.Parameters["UserId"].Value; + object quotaHardLimit = (int) cmd.Parameters["QuotaHardLimit"].Value; + object quotaWarnLimit = (int) cmd.Parameters["QuotaWarnLimit"].Value; + int userFolderId = (int) cmd.Parameters["UserFolderId"].Value; - return null; - } - } - } - } + int? quotaHardLimitVal = (quotaHardLimit == DBNull.Value) ? null : (int?) quotaHardLimit; + int? quotaWarnLimitVal = (quotaWarnLimit == DBNull.Value) ? null : (int?) quotaWarnLimit; + // Return the user object + return new LocalStoreUser(name, + userId, + userFolderId, + quotaWarnLimitVal, + quotaHardLimitVal); + } + } + + /// <summary> + /// Gets the user for the given user Id. + /// </summary> + /// <param name="userId">The Id of the user to look up.</param> + /// <returns>The matching user or null if non is found.</returns> public LocalStoreUser GetUser(int userId) { using (MySqlConnection cnn = GetConnection()) { return getUser(userId, cnn, null); @@ -963,68 +1003,98 @@ public LocalStoreUser getUser(int userId, MySqlConnection cnn, MySqlTransaction transaction) { using (MySqlCommand cmd = cnn.CreateCommand()) { - cmd.Transaction = transaction; - cmd.CommandText = "SELECT u.Username, u.QuotaHardLimit, u.QuotaWarnLimit, u.UserFolderId FROM User u WHERE u.UserId = ?UserId"; - cmd.Parameters.Add("UserId", userId); + cmd.Transaction = transaction; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetUserFromId"; + cmd.Parameters.Add("UserId", userId); + cmd.Parameters.Add("Name", MySqlDbType.VarChar); + cmd.Parameters.Add("QuotaHardLimit", MySqlDbType.Int32); + cmd.Parameters.Add("QuotaWarnLimit", MySqlDbType.Int32); + cmd.Parameters.Add("UserFolderId", MySqlDbType.Int32); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Name"].Direction = ParameterDirection.Output; + cmd.Parameters["QuotaHardLimit"].Direction = ParameterDirection.Output; + cmd.Parameters["QuotaWarnLimit"].Direction = ParameterDirection.Output; + cmd.Parameters["UserFolderId"].Direction = ParameterDirection.Output; + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - using (MySqlDataReader reader = cmd.ExecuteReader()) { - if (reader.Read()) { - string username = (string)reader["Username"]; - object quotaHardLimit = reader["QuotaHardLimit"]; - object quotaWarnLimit = reader["QuotaWarnLimit"]; - int userFolderId = (int)reader["UserFolderId"]; + cmd.ExecuteNonQuery(); - int? quotaHardLimitVal = (quotaHardLimit == DBNull.Value) ? null : (int?)quotaHardLimit; - int? quotaWarnLimitVal = (quotaWarnLimit == DBNull.Value) ? null : (int?)quotaWarnLimit; + int resultValue = (int) cmd.Parameters["Result"].Value; + LocalStoreUserResult result = (LocalStoreUserResult) resultValue; - return new LocalStoreUser(username, - userId, - userFolderId, - quotaWarnLimitVal, - quotaHardLimitVal); - } + // Check if the lookup succeeded + if (result != LocalStoreUserResult.OkSuccessful) { + return null; + } - return null; - } + // Parse out all the returned values + string name = (string) cmd.Parameters["Name"].Value; + object quotaHardLimit = (int) cmd.Parameters["QuotaHardLimit"].Value; + object quotaWarnLimit = (int) cmd.Parameters["QuotaWarnLimit"].Value; + int userFolderId = (int) cmd.Parameters["UserFolderId"].Value; + + int? quotaHardLimitVal = (quotaHardLimit == DBNull.Value) ? null : (int?) quotaHardLimit; + int? quotaWarnLimitVal = (quotaWarnLimit == DBNull.Value) ? null : (int?) quotaWarnLimit; + + // Return the user object + return new LocalStoreUser(name, + userId, + userFolderId, + quotaWarnLimitVal, + quotaHardLimitVal); } } #endregion #region Get Users - public LocalStoreUser[] GetUsers() { + /// <summary> + /// Gets the list of users that currently exist in the local store. + /// </summary> + /// <returns>The list of users.</returns> + public LocalStoreUser[] GetUsers() { using (MySqlConnection cnn = GetConnection()) { - using (MySqlCommand cmd = cnn.CreateCommand()) { - cmd.CommandText = "SELECT u.Username, u.UserId, u.QuotaHardLimit, u.QuotaWarnLimit, u.UserFolderId FROM User u"; + MySqlTransaction transaction = cnn.BeginTransaction(); - using (MySqlDataReader reader = cmd.ExecuteReader()) { - List<LocalStoreUser> users = new List<LocalStoreUser>(); + try { + MySqlCommand cmd = cnn.CreateCommand(); + cmd.Transaction = transaction; - while (reader.Read()) { - string username = (string)reader["Username"]; - int userId = (int)reader["UserId"]; - object quotaHardLimit = reader["QuotaHardLimit"]; - object quotaWarnLimit = reader["QuotaWarnLimit"]; - int userFolderId = (int)reader["UserFolderId"]; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetUserIds"; + MySqlDataReader reader = cmd.ExecuteReader(); + List<int> userIds = new List<int>(); - int? quotaHardLimitVal = (quotaHardLimit == DBNull.Value) ? null : (int?)quotaHardLimit; - int? quotaWarnLimitVal = (quotaWarnLimit == DBNull.Value) ? null : (int?)quotaWarnLimit; + while (reader.NextResult()) { + int userId = (int) reader["UserId"]; + } - users.Add(new LocalStoreUser(username, - userId, - userFolderId, - quotaWarnLimitVal, - quotaHardLimitVal)); - } + List<LocalStoreUser> users = new List<LocalStoreUser>(); + foreach (int userId in userIds) { + users.Add(getUser(userId, cnn, transaction)); + } - return users.ToArray(); + transaction.Commit(); + return users.ToArray(); + + } catch (Exception ex) { + if (transaction != null) { + transaction.Rollback(); } + + throw ex; } } } #endregion #region Get Username - public string GetUsername(int userId) { + /// <summary> + /// Gets the username associated with the given Id. + /// </summary> + /// <param name="userId">The user Id to lookup.</param> + /// <returns>The username or null if non is found.</returns> + public string GetUsername(int userId) { using (MySqlConnection cnn = GetConnection()) { using (MySqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT u.Username FROM User u WHERE UserId = ?UserId"; @@ -1042,13 +1112,18 @@ #endregion #region Get User Id - public int GetUserId(string username) { + /// <summary> + /// Gets the user Id for the associated username. + /// </summary> + /// <param name="username">The username to lookup.</param> + /// <returns>The user Id or null if non is found.</returns> + public int? GetUserId(string username) { using (MySqlConnection cnn = GetConnection()) { return getUserId(username, cnn, null); } } - private int getUserId(string username, MySqlConnection cnn, MySqlTransaction trans) { + private int? getUserId(string username, MySqlConnection cnn, MySqlTransaction trans) { using (MySqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT u.UserId FROM User u WHERE Username = ?Username"; cmd.Parameters.Add("Username", username); @@ -1056,192 +1131,116 @@ object o = cmd.ExecuteScalar(); if (o == null || o == DBNull.Value) { - return -1; + return null; } else { return (int)o; } } } #endregion - - /// <summary> - /// Adds a user to the given mail domain. - /// </summary> - /// <param name="userId">The Id of the user to add.</param> - /// <param name="mailDomainId">The Id of the mail domain to add the user to.</param> - public LocalStoreUserResult AddUserToMailDomain(int userId, int mailDomainId) { - throw new NotImplementedException(); - } - /// <summary> - /// Removes user from the given mail domain. - /// </summary> - /// <param name="userId">The Id of the user to remove.</param> - /// <param name="mailDomainId">The Id of the mail domain to revome the user from.</param> - public LocalStoreUserResult RemoveUserFromMailDomain(int userId, int mailDomainId) { - throw new NotImplementedException(); - } - - public LocalStoreUserResult CreateUser(string username, int? warnQuota, int? hardQuota) { + #region Create User + /// <summary> + /// Creates a new user in the local store and creates an initial folder. + /// </summary> + /// <param name="userName">The name of the new user.</param> + /// <param name="warnQuota">The warning quota level.</param> + /// <param name="hardQuota">The hard quota.</param> + /// <returns>The result of the attemp to create a new user.</returns> + public LocalStoreUserResult CreateUser(string username, int? warnQuota, int? hardQuota) { using (MySqlConnection cnn = GetConnection()) { - MySqlTransaction transaction = null; - - try { - transaction = cnn.BeginTransaction(); - - MySqlCommand cmd = cnn.CreateCommand(); - cmd.Transaction = transaction; - cmd.Connection = cnn; - - //TODO: make adjustable - int namespaceId = 1; - string namespaceStr = "LocalMail"; - - // Check if the user has a folder - cmd.CommandText = "SELECT COUNT(*) FROM Folder WHERE Name = ?Name and NamespaceId = ?NamespaceId"; + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "CreateUser"; cmd.Parameters.Add("Name", username); - cmd.Parameters.Add("NamespaceId", namespaceId); - long count = (long) cmd.ExecuteScalar(); + cmd.Parameters.Add("QuotaHardLimit", hardQuota); + cmd.Parameters.Add("QuotaWarnLimit", warnQuota); + cmd.Parameters.Add("UserId", MySqlDbType.Int32); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["UserId"].Direction = ParameterDirection.Output; + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - if (count != 0) { - throw new InvalidOperationException("Folder with the same username already exists."); - } + cmd.ExecuteNonQuery(); - // Check if the user has an inbox folder - cmd.CommandText = "SELECT COUNT(*) FROM Folder WHERE Name = ?Name and NamespaceId = ?NamespaceId"; - cmd.Parameters.Add("Name", username + ".INBOX"); - cmd.Parameters.Add("NamespaceId", namespaceId); - count = (long) cmd.ExecuteScalar(); + int result = (int) cmd.Parameters["Result"].Value; - if (count != 0) { - throw new InvalidOperationException("User's inbox already exists."); - } - - // Check if the user exists - cmd.CommandText = "SELECT COUNT(*) FROM User WHERE Username = ?Username"; - cmd.Parameters.Add("Username", username); - count = (long) cmd.ExecuteScalar(); - - if (count != 0) { - transaction.Commit(); - return LocalStoreUserResult.AlreadyExists; - } - - // Add the user - cmd.CommandText = "INSERT INTO User (Username, QuotaHardLimit, QuotaWarnLimit, Password) VALUES (?Username, ?QuotaHardLimit, ?QuotaWarnLimit, NULL)"; - cmd.Parameters.Add("Username", username); - cmd.Parameters.Add("QuotaHardLimit", hardQuota); - cmd.Parameters.Add("QuotaWarnLimit", warnQuota); - if (cmd.ExecuteNonQuery() != 1) { - throw new System.Data.DataException("Error inserting user."); - } - - int userId = getUserId(username, cnn, transaction); - - // Add the user's folder - Folder newFolder = new Folder(namespaceStr, username); - StoreFolder baseFolder = createFolder(userId, namespaceId, null, newFolder, cnn, transaction); - - // Add the user's inbox - Folder inboxFolder = new Folder(baseFolder, "INBOX"); - StoreFolder createdFolder = createFolder(userId, namespaceId, baseFolder.FolderId, inboxFolder, cnn, transaction); - - // Give the new user full privileges to their folders - setStoreFolderAce(baseFolder, new StoreFolderAce(username, StoreFolderPrivilegeHelper.AllPrivileges, AcePrivilegeType.Allow), cnn, transaction); - setStoreFolderAce(createdFolder, new StoreFolderAce(username, StoreFolderPrivilegeHelper.AllPrivileges, AcePrivilegeType.Allow), cnn, transaction); - - // Update the user's entry to include the folder Id - cmd.CommandText = "UPDATE User SET UserFolderId = ?UserFolderId WHERE UserId = ?UserId"; - cmd.Parameters.Add("UserFolderId", baseFolder.FolderId); - cmd.Parameters.Add("UserId", userId); - - if (cmd.ExecuteNonQuery() != 1) { - throw new System.Data.DataException("Error updating user's folder Id."); - } - - transaction.Commit(); - return LocalStoreUserResult.OkSuccessful; - - } catch (Exception e) { - if (transaction != null) { - transaction.Rollback(); - } - - throw e; + return (LocalStoreUserResult) result; } } - } + } + #endregion - #region Delete User - public LocalStoreUserResult DeleteUser(int userId) { + #region Delete User + /// <summary> + /// Removes a user from the local store. + /// </summary> + /// <param name="userId">The Id of the user to delete.</param> + /// <returns>The result of the attempt to delete a user.</returns> + public LocalStoreUserResult DeleteUser(int userId) { using (MySqlConnection cnn = GetConnection()) { - MySqlTransaction transaction = null; + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "DeleteUser"; + cmd.Parameters.Add("UserId", userId); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - try { - transaction = cnn.BeginTransaction(); + cmd.ExecuteNonQuery(); - // Check if the user exists - LocalStoreUser user = getUser(userId, cnn, transaction); - if (user == null) { - transaction.Commit(); - return LocalStoreUserResult.NoSuchUser; - } + int result = (int) cmd.Parameters["Result"].Value; - using (MySqlCommand cmd = cnn.CreateCommand()) { - // Check if the user still owns any folders - cmd.Transaction = transaction; - cmd.CommandText = "SELECT COUNT(*) FROM Folder WHERE OwnerUserId = ?UserId"; - cmd.Parameters.Add("UserId", userId); - long folders = (long)cmd.ExecuteScalar(); - - if (folders != 0) { - transaction.Commit(); - return LocalStoreUserResult.UserStillHasFolders; - } - - // Delete the user - cmd.CommandText = "DELETE FROM User WHERE UserId = ?UserId"; - cmd.Parameters.Add("UserId", userId); - - if (cmd.ExecuteNonQuery() != 1) { - throw new System.Data.DataException("Error deleting user."); - } - - transaction.Commit(); - return LocalStoreUserResult.OkSuccessful; - } - } catch (Exception ex) { - if (transaction != null) { - transaction.Rollback(); - } - - throw ex; - } + return (LocalStoreUserResult) result; + } } } #endregion #region Update User + /// <summary> + /// Updates the any changes to the user into the local store. + /// </summary> + /// <param name="user">The user details to update.</param> + /// <returns>The result of the attempt to update the user.</returns> public LocalStoreUserResult UpdateUser(LocalStoreUser user) { using (MySqlConnection cnn = GetConnection()) { using (MySqlCommand cmd = cnn.CreateCommand()) { - cmd.CommandText = "UPDATE User SET Username = ?Username, UserFolderId = ?UserFolderId, QuotaHardLimit = ?QuotaHardLimit, QuotaWarnLimit = ?QuotaWarnLimit WHERE UserId = ?UserId"; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "UpdateUser"; cmd.Parameters.Add("UserId", user.UserId); - cmd.Parameters.Add("Username", user.Username); - cmd.Parameters.Add("UserFolderId", user.UserFolderId); + cmd.Parameters.Add("Name", user.Username); cmd.Parameters.Add("QuotaHardLimit", user.QuotaHardLimit); cmd.Parameters.Add("QuotaWarnLimit", user.QuotaWarnLimit); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - if (cmd.ExecuteNonQuery() != 1) { - throw new System.Data.DataException("Error updating user."); - } + cmd.ExecuteNonQuery(); - return LocalStoreUserResult.OkSuccessful; + int result = (int) cmd.Parameters["Result"].Value; + + return (LocalStoreUserResult) result; } } } + #endregion + + /// <summary> + /// Adds a user to the given mail domain. + /// </summary> + /// <param name="userId">The Id of the user to add.</param> + /// <param name="mailDomainId">The Id of the mail domain to add the user to.</param> + public LocalStoreUserResult AddUserToMailDomain(int userId, int mailDomainId) { + throw new NotImplementedException(); + } + + /// <summary> + /// Removes user from the given mail domain. + /// </summary> + /// <param name="userId">The Id of the user to remove.</param> + /// <param name="mailDomainId">The Id of the mail domain to revome the user from.</param> + public LocalStoreUserResult RemoveUserFromMailDomain(int userId, int mailDomainId) { + throw new NotImplementedException(); + } #endregion - #endregion #region Group Management #region GetGroup @@ -1432,7 +1431,7 @@ using (MySqlConnection cnn = GetConnection()) { using (MySqlCommand cmd = cnn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; - cmd.CommandText = "SELECT DeleteGroup(?GroupId)"; + cmd.CommandText = "DeleteGroup"; cmd.Parameters.Add("GroupId", groupId); cmd.Parameters.Add("Result", MySqlDbType.Int32); cmd.Parameters["Result"].Direction = ParameterDirection.Output; @@ -1447,99 +1446,106 @@ } #endregion - #region Update Group - /// <summary> - /// Updates the any changes to the group into the local store. - /// </summary> - /// <param name="group">The group details to update.</param> - /// <returns>The result of the attempt to update the group.</returns> - public LocalStoreGroupResult UpdateGroup(LocalStoreGroup group) { + #region Rename Group + /// <summary> + /// Renames the group. + /// </summary> + /// <param name="groupId">The Id of the group to delete.</param> + /// <param name="newName">The new name for the group.</param> + /// <returns>The result of the attempt to rename the group.</returns> + public LocalStoreGroupResult RenameGroup(int groupId, string newName) { using (MySqlConnection cnn = GetConnection()) { - MySqlTransaction transaction = cnn.BeginTransaction(); - - try { - MySqlCommand cmd = cnn.CreateCommand(); - cmd.Transaction = transaction; - + using (MySqlCommand cmd = cnn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; - cmd.CommandText = "UpdateGroupName"; - cmd.Parameters.Add("GroupId", group.GroupId); - cmd.Parameters.Add("Name", group.Name); + cmd.CommandText = "RenameGroup"; + cmd.Parameters.Add("GroupId", groupId); + cmd.Parameters.Add("Name", newName); cmd.Parameters.Add("Result", MySqlDbType.Int32); cmd.Parameters["Result"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); - int resultValue = (int) cmd.Parameters["Result"].Value; - LocalStoreGroupResult result = (LocalStoreGroupResult) resultValue; + int result = (int) cmd.Parameters["Result"].Value; - // Check for any errors updating the group name - if (result != LocalStoreGroupResult.OkSuccessful) { - transaction.Rollback(); - return result; - } + return (LocalStoreGroupResult) result; + } + } + } + #endregion - // Delete and re-add all the group's users - cmd = cnn.CreateCommand(); - cmd.Transaction = transaction; - cmd.CommandText = "DELETE FROM UserGroupMap WHERE GroupId = ?GroupId"; - cmd.Parameters.Add("GroupId", group.GroupId); - cmd.ExecuteNonQuery(); + #region Add Group to Group + /// <summary> + /// Adds a child group to the given group. + /// </summary> + /// <param name="groupId">The child group to add.</param> + /// <param name="parentId">The group to add the child to.</param> + /// <returns>The result of the attempt to add.</returns> + public LocalStoreGroupResult AddGroupToGroup(int parentId, int groupId) { + using (MySqlConnection cnn = GetConnection()) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "AddGroupToGroup"; + cmd.Parameters.Add("ParentId", parentId); + cmd.Parameters.Add("GroupId", groupId); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - cmd.CommandText = "INSERT INTO UserGroupMap (UserId, GroupId) VALUES (?UserId, ?GroupId)"; - cmd.Parameters.Add("GroupId", group.GroupId); - cmd.Parameters.Add("UserId", -1); - foreach (int userId in group.UserIds) { - cmd.Parameters["UserId"].Value = userId; - cmd.ExecuteNonQuery(); - } + cmd.ExecuteNonQuery(); - // Delete and re-add all the group's sub-groups - cmd = cnn.CreateCommand(); - cmd.Transaction = transaction; - cmd.CommandText = "DELETE FROM GroupGroupMap WHERE ParentId = ?ParentId"; - cmd.Parameters.Add("ParentId", group.GroupId); - cmd.ExecuteNonQuery(); + int result = (int) cmd.Parameters["Result"].Value; - cmd.CommandText = "INSERT INTO GroupGroupMap (ParentId, GroupId) VALUES (?ParentId, ?GroupId)"; - cmd.Parameters.Add("ParentId", group.GroupId); - cmd.Parameters.Add("GroupId", -1); - foreach (int groupId in group.SubGroupIds) { - cmd.Parameters["GroupId"].Value = groupId; - cmd.ExecuteNonQuery(); - } + return (LocalStoreGroupResult) result; + } + } + } + #endregion - transaction.Commit(); - return LocalStoreGroupResult.OkSuccessful; + #region Remove Group from Group + /// <summary> + /// Removes a child group from the given group. + /// </summary> + /// <param name="groupId">The child group to remove.</param> + /// <param name="parentId">The group to remove the child from.</param> + /// <returns>The result of the attempt to remove.</returns> + public LocalStoreGroupResult RemoveGroupFromGroup(int parentId, int groupId) { + using (MySqlConnection cnn = GetConnection()) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "RemoveGroupFromGroup"; + cmd.Parameters.Add("ParentId", parentId); + cmd.Parameters.Add("GroupId", groupId); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - } catch (Exception ex) { - if (transaction != null) { - transaction.Rollback(); - } + cmd.ExecuteNonQuery(); - throw ex; - } - } - } - #endregion + int result = (int) cmd.Parameters["Result"].Value; - /// <summary> - /// Adds a group to the given mail domain. - /// </summary> - /// <param name="groupId">The Id of the group to add.</param> - /// <param name="mailDomainId">The Id of the mail domain to add the group to.</param> - public LocalStoreGroupResult AddGroupToMailDomain(int groupId, int mailDomainId) { - throw new NotImplementedException(); - } + return (LocalStoreGroupResult) result; + } + } + } + #endregion - /// <summary> - /// Removes group from the given mail domain. - /// </summary> - /// <param name="groupId">The Id of the group to remove.</param> - /// <param name="mailDomainId">The Id of the mail domain to revome the group from.</param> - public LocalStoreGroupResult RemoveGroupFromMailDomain(int groupId, int mailDomainId) { - throw new NotImplementedException(); - } + /// <summary> + /// Adds a group to the given mail domain. + /// </summary> + /// <param name="groupId">The Id of the group to add.</param> + /// <param name="mailDomainId">The Id of the mail domain to add the group to.</param> + /// <returns>The result of the attempt to add.</returns> + public LocalStoreGroupResult AddGroupToMailDomain(int groupId, int mailDomainId) { + throw new NotImplementedException(); + } + + /// <summary> + /// Removes group from the given mail domain. + /// </summary> + /// <param name="groupId">The Id of the group to remove.</param> + /// <param name="mailDomainId">The Id of the mail domain to revome the group from.</param> + /// <returns>The result of the attempt to remove.</returns> + public LocalStoreGroupResult RemoveGroupFromMailDomain(int groupId, int mailDomainId) { + throw new NotImplementedException(); + } #endregion #region ACLs This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
