|
From: <tmy...@us...> - 2006-05-20 01:44:32
|
Revision: 35 Author: tmyroadctfig Date: 2006-05-19 18:44:04 -0700 (Fri, 19 May 2006) ViewCVS: http://svn.sourceforge.net/nmailserver/?rev=35&view=rev Log Message: ----------- Work on local store. Modified Paths: -------------- NMail/branches/luke-dev/NMail/DataTypes/LocalStoreGroup.cs NMail/branches/luke-dev/NMail/ILocalStore.cs NMail/branches/luke-dev/NMail/ILocalStoreData.cs NMail/branches/luke-dev/NMail.Administration.Console/NMail.Administration.Console.csproj 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 Added Paths: ----------- NMail/branches/luke-dev/NMail.Administration.Console/Command/AddUserToMailDomainCommand.cs NMail/branches/luke-dev/NMail.Administration.Console/Command/RemoveUserFromMailDomainCommand.cs Modified: NMail/branches/luke-dev/NMail/DataTypes/LocalStoreGroup.cs =================================================================== --- NMail/branches/luke-dev/NMail/DataTypes/LocalStoreGroup.cs 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail/DataTypes/LocalStoreGroup.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -35,6 +35,8 @@ public LocalStoreGroup(string name, int groupId, int[] userIds, int[] groupIds) { this.name = name; this.groupId = groupId; + this.userIds = userIds; + this.groupIds = groupIds; } private string name; Modified: NMail/branches/luke-dev/NMail/ILocalStore.cs =================================================================== --- NMail/branches/luke-dev/NMail/ILocalStore.cs 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail/ILocalStore.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -420,7 +420,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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> - void AddUserToMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId); + /// <returns>The result of the attempt to add the user to the mail domain.</returns> + LocalStoreUserResult AddUserToMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId); /// <summary> /// Removes user from the given mail domain. @@ -428,7 +429,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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> - void RemoveUserFromMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId); + /// <returns>The result of the attempt to remove the user from the mail domain.</returns> + LocalStoreUserResult RemoveUserFromMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId); #endregion #region Group Management @@ -485,7 +487,7 @@ /// <param name="authToken">The authentication credentials.</param> /// <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> - void AddGroupToMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId); + LocalStoreGroupResult AddGroupToMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId); /// <summary> /// Removes group from the given mail domain. @@ -493,7 +495,7 @@ /// <param name="authToken">The authentication credentials.</param> /// <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> - void RemoveGroupFromMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId); + LocalStoreGroupResult RemoveGroupFromMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId); #endregion #region Mail Domain Management @@ -587,27 +589,32 @@ /// <summary> /// The operation completed successfully. /// </summary> - OkSuccessful, + OkSuccessful = 0, /// <summary> /// A user with the same name already exists. /// </summary> - AlreadyExists, + AlreadyExists = 1, /// <summary> /// No matching user exists in the local store. /// </summary> - NoSuchUser, + NoSuchUser = 2, /// <summary> + /// Cannot add or remove the user from the mail domain because it doens't exist. + /// </summary> + NoSuchMailDomain = 3, + + /// <summary> /// The user cannot be deleted because they still own folders. /// </summary> - UserStillHasFolders, + UserStillHasFolders = 4, /// <summary> /// The user is not permitted to perform the operation. /// </summary> - NotPermitted + NotPermitted = 5 } /// <summary> @@ -617,22 +624,27 @@ /// <summary> /// The operation completed successfully. /// </summary> - OkSuccessful, + OkSuccessful = 0, /// <summary> /// A group with the same name already exists. /// </summary> - AlreadyExists, + AlreadyExists = 1, /// <summary> /// No matching group exists in the local store. /// </summary> - NoSuchUser, + NoSuchGroup = 2, /// <summary> + /// Cannot add or remove the group from the mail domain because it doens't exist. + /// </summary> + NoSuchMailDomain = 3, + + /// <summary> /// The user is not permitted to perform the operation. /// </summary> - NotPermitted + NotPermitted = 4 } /// <summary> Modified: NMail/branches/luke-dev/NMail/ILocalStoreData.cs =================================================================== --- NMail/branches/luke-dev/NMail/ILocalStoreData.cs 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail/ILocalStoreData.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -347,14 +347,14 @@ /// </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> - void AddUserToMailDomain(int userId, int mailDomainId); + LocalStoreUserResult AddUserToMailDomain(int userId, int mailDomainId); /// <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> - void RemoveUserFromMailDomain(int userId, int mailDomainId); + LocalStoreUserResult RemoveUserFromMailDomain(int userId, int mailDomainId); #endregion #region Group Management @@ -404,14 +404,14 @@ /// </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> - void AddGroupToMailDomain(int groupId, int mailDomainId); + 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> - void RemoveGroupFromMailDomain(int groupId, int mailDomainId); + LocalStoreGroupResult RemoveGroupFromMailDomain(int groupId, int mailDomainId); #endregion #region Mail Domain Management Added: NMail/branches/luke-dev/NMail.Administration.Console/Command/AddUserToMailDomainCommand.cs =================================================================== --- NMail/branches/luke-dev/NMail.Administration.Console/Command/AddUserToMailDomainCommand.cs (rev 0) +++ NMail/branches/luke-dev/NMail.Administration.Console/Command/AddUserToMailDomainCommand.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -0,0 +1,99 @@ +/* + * Copyright 2004-2006 Luke Quinane. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + */ + +using System; +using System.Collections.Generic; +using System.Text; + +using NMail; +using NMail.Authentication; +using NMail.DataTypes; + +namespace NMail.Administration.Console.Command { + class AddUserToMailDomainCommand : IConsoleCommand { + + private ILocalStore localStore; + + private IAuthenticationToken authToken; + + internal AddUserToMailDomainCommand(ILocalStore localStore, IAuthenticationToken authToken) { + this.localStore = localStore; + this.authToken = authToken; + } + + #region IConsoleCommand Members + + public string Description { + get { return "Adds an existing user to a mail domain."; } + } + + public string[] Usage { + get { + List<string> usage = new List<string>(); + + usage.Add("addusertomaildomain Username MailDomainId"); + + return usage.ToArray(); + } + } + + public string Remarks { + get { + return null; + } + } + + public ConsoleExample[] Examples { + get { + List<ConsoleExample> examples = new List<ConsoleExample>(); + + examples.Add(new ConsoleExample("addusertomaildomain jsmith 1", "Add the user with the username \"jsmith\" to the mail domain with Id 1.")); + + return examples.ToArray(); + } + } + + public void Process(string[] commandTokens) { + string username; + int mailDomainId; + + if (commandTokens.Length != 2) { + System.Console.WriteLine("A username and mail domain Id are required."); + return; + } + username = commandTokens[0]; + try { + mailDomainId = Int32.Parse(commandTokens[1]); + } catch (Exception) { + System.Console.WriteLine("Error parsing mail domain Id."); + return; + } + + LocalStoreUser user = this.localStore.GetUser(this.authToken, username); + if (user == null) { + System.Console.WriteLine("No such user."); + return; + } + + this.localStore.AddUserToMailDomain(this.authToken, user.UserId, mailDomainId); + System.Console.WriteLine("Added user to mail domain."); + System.Console.WriteLine(); + } + + #endregion + } +} Added: NMail/branches/luke-dev/NMail.Administration.Console/Command/RemoveUserFromMailDomainCommand.cs =================================================================== --- NMail/branches/luke-dev/NMail.Administration.Console/Command/RemoveUserFromMailDomainCommand.cs (rev 0) +++ NMail/branches/luke-dev/NMail.Administration.Console/Command/RemoveUserFromMailDomainCommand.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -0,0 +1,103 @@ +/* + * Copyright 2004-2006 Luke Quinane. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + */ + +using System; +using System.Collections.Generic; +using System.Text; + +using NMail; +using NMail.Authentication; +using NMail.DataTypes; + +namespace NMail.Administration.Console.Command { + class RemoveUserFromMailDomainCommand : IConsoleCommand { + + private ILocalStore localStore; + + private IAuthenticationToken authToken; + + internal RemoveUserFromMailDomainCommand(ILocalStore localStore, IAuthenticationToken authToken) { + this.localStore = localStore; + this.authToken = authToken; + } + + #region IConsoleCommand Members + + public string Description { + get { return "Removes a user from a mail domain."; } + } + + public string[] Usage { + get { + List<string> usage = new List<string>(); + + usage.Add("removeuserfrommaildomain Username MailDomainId"); + + return usage.ToArray(); + } + } + + public string Remarks { + get { + return null; + } + } + + public ConsoleExample[] Examples { + get { + List<ConsoleExample> examples = new List<ConsoleExample>(); + + examples.Add(new ConsoleExample("removeuserfrommaildomain jsmith 1", "Removes the user with the username \"jsmith\" from the mail domain with Id 1.")); + + return examples.ToArray(); + } + } + + public void Process(string[] commandTokens) { + string username; + int mailDomainId; + + if (commandTokens.Length != 2) { + System.Console.WriteLine("A username and mail domain Id are required."); + return; + } + username = commandTokens[0]; + try { + mailDomainId = Int32.Parse(commandTokens[1]); + } catch (Exception) { + System.Console.WriteLine("Error parsing mail domain Id."); + return; + } + + LocalStoreUser user = this.localStore.GetUser(this.authToken, username); + if (user == null) { + System.Console.WriteLine("No such user."); + return; + } + + LocalStoreUserResult result = this.localStore.RemoveUserFromMailDomain(this.authToken, user.UserId, mailDomainId); + if (result == LocalStoreUserResult.OkSuccessful) { + System.Console.WriteLine("Removed user from mail domain."); + } else { + System.Console.WriteLine("An error occurred removing the user from the mail domain."); + } + System.Console.WriteLine(); + } + + #endregion + } +} Modified: NMail/branches/luke-dev/NMail.Administration.Console/NMail.Administration.Console.csproj =================================================================== --- NMail/branches/luke-dev/NMail.Administration.Console/NMail.Administration.Console.csproj 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail.Administration.Console/NMail.Administration.Console.csproj 2006-05-20 01:44:04 UTC (rev 35) @@ -35,6 +35,8 @@ </ItemGroup> <ItemGroup> <Compile Include="Command\AddUserCommand.cs" /> + <Compile Include="Command\AddUserToMailDomainCommand.cs" /> + <Compile Include="Command\RemoveUserFromMailDomainCommand.cs" /> <Compile Include="Command\ListFoldersCommand.cs" /> <Compile Include="Command\DeleteUserCommand.cs" /> <Compile Include="Command\ListMailDomainsCommand.cs" /> Modified: NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs =================================================================== --- NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail.LocalStore/LocalStore.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -954,8 +954,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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 void AddUserToMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId) { - this.LocalStoreData.AddUserToMailDomain(userId, mailDomainId); + public LocalStoreUserResult AddUserToMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId) { + return this.LocalStoreData.AddUserToMailDomain(userId, mailDomainId); } /// <summary> @@ -964,8 +964,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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 void RemoveUserFromMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId) { - this.LocalStoreData.RemoveUserFromMailDomain(userId, mailDomainId); + public LocalStoreUserResult RemoveUserFromMailDomain(IAuthenticationToken authToken, int userId, int mailDomainId) { + return this.LocalStoreData.RemoveUserFromMailDomain(userId, mailDomainId); } #endregion @@ -1037,8 +1037,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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 void AddGroupToMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId) { - LocalStoreData.AddGroupToMailDomain(groupId, mailDomainId); + public LocalStoreGroupResult AddGroupToMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId) { + return LocalStoreData.AddGroupToMailDomain(groupId, mailDomainId); } /// <summary> @@ -1047,8 +1047,8 @@ /// <param name="authToken">The authentication credentials.</param> /// <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 void RemoveGroupFromMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId) { - LocalStoreData.RemoveGroupFromMailDomain(groupId, mailDomainId); + public LocalStoreGroupResult RemoveGroupFromMailDomain(IAuthenticationToken authToken, int groupId, int mailDomainId) { + return LocalStoreData.RemoveGroupFromMailDomain(groupId, mailDomainId); } #endregion Modified: NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql =================================================================== --- NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStore.sql 2006-05-20 01:44:04 UTC (rev 35) @@ -39,34 +39,40 @@ DROP TABLE IF EXISTS `Group`; CREATE TABLE `Group` ( GroupId INT AUTO_INCREMENT NOT NULL, - GroupName VARCHAR(100) NOT NULL, + Name VARCHAR(100) NOT NULL, PRIMARY KEY(GroupId) ) TYPE=InnoDb; DROP TABLE IF EXISTS UserGroupMap; CREATE TABLE UserGroupMap ( - UserGroupMapId INT AUTO_INCREMENT NOT NULL, UserId INT NOT NULL, GroupId INT NOT NULL, INDEX UserId (UserId), FOREIGN KEY (UserId) REFERENCES `User` (UserId) ON DELETE CASCADE, INDEX GroupId (GroupId), FOREIGN KEY (GroupId) REFERENCES `Group` (GroupId) ON DELETE CASCADE, - PRIMARY KEY(UserGroupMapId) + PRIMARY KEY(UserId, GroupId) ) TYPE=InnoDb; DROP TABLE IF EXISTS GroupGroupMap; CREATE TABLE GroupGroupMap ( - GroupGroupMapId INT AUTO_INCREMENT NOT NULL, ParentId INT NOT NULL, GroupId INT NOT NULL, INDEX ParentId (ParentId), FOREIGN KEY (ParentId) REFERENCES `Group` (GroupId) ON DELETE CASCADE, INDEX GroupId (GroupId), FOREIGN KEY (GroupId) REFERENCES `Group` (GroupId) ON DELETE CASCADE, - PRIMARY KEY(GroupGroupMapId) + PRIMARY KEY(ParentId, GroupId) ) TYPE=InnoDb; +DROP TABLE IF EXISTS MailDomain; +CREATE TABLE MailDomain ( + MailDomainId INT AUTO_INCREMENT NOT NULL, + PrimaryHost VARCHAR(255) NOT NULL, + ObjectData LONGBLOB NOT NULL, + PRIMARY KEY (MailDomainId) +) TYPE=InnoDb; + DROP TABLE IF EXISTS MailboxMapping; CREATE TABLE MailboxMapping ( MailboxMappingId INT AUTO_INCREMENT NOT NULL, @@ -184,38 +190,194 @@ DROP PROCEDURE IF EXISTS GetGroupFromName // -CREATE PROCEDURE GetGroup +CREATE PROCEDURE GetGroupFromName ( Name VARCHAR(100), - OUT GroupId INT + OUT GroupId INT, + OUT Result INT ) BEGIN - START TRANSACTION; - SELECT g.GroupId INTO GroupId FROM `Group` g WHERE g.Name = Name; + IF EXISTS (SELECT g.GroupId FROM `Group` g WHERE g.Name LIKE Name) THEN + + SELECT g.GroupId INTO GroupId FROM `Group` g WHERE g.Name LIKE Name; + SELECT + gm.GroupId + FROM + GroupGroupMap gm + WHERE + gm.ParentId = GroupId; - SELECT - gm.GroupId - FROM - GroupGroupMap gm - WHERE - gm.ParentId = GroupId; + SELECT + um.UserId + FROM + UserGroupMap um + WHERE + um.GroupId = GroupId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// - SELECT - um.UserId - FROM - UserGroupMap um - WHERE - um.GroupId = GroupId; +DROP PROCEDURE IF EXISTS GetGroupFromId // +CREATE PROCEDURE GetGroupFromId +( + GroupId INT, + OUT Name VARCHAR(100), + OUT Result INT +) +BEGIN + START TRANSACTION; + + IF EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) THEN + + SELECT g.Name INTO Name FROM `Group` g WHERE g.GroupId = GroupId; + + SELECT + gm.GroupId + FROM + GroupGroupMap gm + WHERE + gm.ParentId = GroupId; + + + SELECT + um.UserId + FROM + UserGroupMap um + WHERE + um.GroupId = GroupId; + + /* Ok successful. */ + SELECT 1 INTO Result; + + ELSE + /* No such user. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + +DROP PROCEDURE IF EXISTS GetGroupIds // +CREATE PROCEDURE GetGroupIds +() +BEGIN + + SELECT g.GroupId FROM `Group` g; + +END +// + +DROP PROCEDURE IF EXISTS CreateGroup // +CREATE PROCEDURE CreateGroup +( + Name VARCHAR(100), + OUT GroupId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the Id is valid. */ + IF NOT EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) THEN + INSERT INTO `Group` (Name) VALUES (Name); + + SELECT g.GroupId INTO GroupId FROM `Group` g WHERE g.GroupId = LAST_INSERT_ID(); + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* Already exists. */ + SELECT 1 INTO Result; + END IF; + COMMIT; +END +// +DROP PROCEDURE IF EXISTS DeleteGroup // +CREATE PROCEDURE DeleteGroup +( + GroupId INT, + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the Id is valid. */ + IF EXISTS (SELECT g.Name FROM `Group` g WHERE g.GroupId = GroupId) THEN + + DELETE FROM `Group` WHERE GroupId = GroupId; + + /* Ok successful. */ + SELECT 0 INTO Result; + + ELSE + /* No such group. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; END // +DROP PROCEDURE IF EXISTS UpdateGroup // +CREATE PROCEDURE UpdateGroupName +( + GroupId INT, + Name VARCHAR(100), + OUT Result INT +) +BEGIN + START TRANSACTION; + + /* Check if the Id is valid. */ + 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 + + /* 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; + + /* Ok successful. */ + SELECT 0 INTO Result; + ELSE + /* Already exists. */ + SELECT 1 INTO Result; + END IF; + + ELSE + /* No name change needed. Return "Ok successful". */ + SELECT 0 INTO Result; + END IF; + + ELSE + /* No such group. */ + SELECT 2 INTO Result; + END IF; + + COMMIT; +END +// + delimiter ; @@ -231,4 +393,23 @@ INSERT INTO Folder (NamespaceId, Name, OwnerUserId, NextMessageId, ParentFolderId) VALUES (1, "Administrator.INBOX", 1, 1, 1); INSERT INTO FolderAcl (FolderId, Identifier, Allow, Privilege) VALUES (1, "Administrator", 1, 0xffffff); -- All privs INSERT INTO FolderAcl (FolderId, Identifier, Allow, Privilege) VALUES (2, "Administrator", 1, 0xffffff); -- All privs -INSERT INTO Subscription (UserId, FolderId) VALUES (1, 2); \ No newline at end of file +INSERT INTO Subscription (UserId, FolderId) VALUES (1, 2); +INSERT INTO `Group` (Name) VALUES ("Administrators"); +INSERT INTO UserGroupMap (GroupId, UserId) VALUES (1, 1); + + +-- TODO: remove, for testing only +INSERT INTO User (UserName, Password, UserFolderId) VALUES ("Testing1", "changeme", 2); +INSERT INTO User (UserName, Password, UserFolderId) VALUES ("Testing2", "changeme", 2); +INSERT INTO User (UserName, Password, UserFolderId) VALUES ("Testing3", "changeme", 2); + +INSERT INTO `Group` (Name) VALUES ("Test1"); +INSERT INTO `Group` (Name) VALUES ("Test2"); +INSERT INTO `Group` (Name) VALUES ("Test3"); + +INSERT INTO UserGroupMap (UserId, GroupId) VALUES (2, 1); +INSERT INTO UserGroupMap (UserId, GroupId) VALUES (2, 2); +INSERT INTO UserGroupMap (UserId, GroupId) VALUES (3, 3); + + +INSERT INTO GroupGroupMap (ParentId, GroupId) VALUES (3, 2); \ No newline at end of file Modified: NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs =================================================================== --- NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs 2006-05-14 08:18:20 UTC (rev 34) +++ NMail/branches/luke-dev/NMail.LocalStoreData.MySql/MySqlLocalStoreData.cs 2006-05-20 01:44:04 UTC (rev 35) @@ -51,7 +51,17 @@ // TODO: remove (for testing only) static MySqlLocalStoreData() { MySqlLocalStoreData m = new MySqlLocalStoreData(); - m.GetGroup("Administrators"); + //m.CreateGroup("Test123"); + //m.GetGroup("aaaAdministrators"); + //m.CreateUser("Test123", 100, 100); + + List<int> userIds = new List<int>(); + userIds.Add(2); + userIds.Add(3); + LocalStoreGroup g = m.GetGroup("Administrators"); + g.UserIds = userIds.ToArray(); + m.UpdateGroup(g); + g = m.GetGroup("Administrators"); } #region ILocalStoreData Members @@ -1052,13 +1062,14 @@ } } } + #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 void AddUserToMailDomain(int userId, int mailDomainId) { + public LocalStoreUserResult AddUserToMailDomain(int userId, int mailDomainId) { throw new NotImplementedException(); } @@ -1067,10 +1078,9 @@ /// </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 void RemoveUserFromMailDomain(int userId, int mailDomainId) { + public LocalStoreUserResult RemoveUserFromMailDomain(int userId, int mailDomainId) { throw new NotImplementedException(); } - #endregion public LocalStoreUserResult CreateUser(string username, int? warnQuota, int? hardQuota) { using (MySqlConnection cnn = GetConnection()) { @@ -1234,6 +1244,7 @@ #endregion #region Group Management + #region GetGroup /// <summary> /// Gets the group for the given name. /// </summary> @@ -1241,20 +1252,50 @@ /// <returns>The matching group or null if non is found.</returns> public LocalStoreGroup GetGroup(string name) { using (MySqlConnection cnn = GetConnection()) { + return getGroup(name, cnn, null); + } + } - using (MySqlCommand cmd = cnn.CreateCommand()) { - cmd.CommandType = CommandType.StoredProcedure; - cmd.CommandText = "GetGroup()"; - cmd.Parameters.Add("Name", name); + private LocalStoreGroup getGroup(string name, MySqlConnection cnn, MySqlTransaction transaction) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.Transaction = transaction; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetGroupFromName"; + cmd.Parameters.Add("Name", name); + cmd.Parameters.Add("GroupId", MySqlDbType.Int32); + cmd.Parameters["GroupId"].Direction = ParameterDirection.Output; + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; - DataSet groupData = new DataSet(); - MySqlDataAdapter da = new MySqlDataAdapter(cmd); - da.Fill(groupData); + DataSet groupData = new DataSet(); + MySqlDataAdapter da = new MySqlDataAdapter(cmd); + da.Fill(groupData); - int groupId = (int) cmd.Parameters["GroupId"].Value; + int resultValue = (int) cmd.Parameters["Result"].Value; + LocalStoreUserResult result = (LocalStoreUserResult) resultValue; + // Check if the lookup succeeded + if (result != LocalStoreUserResult.OkSuccessful) { return null; } + + int groupId = (int) cmd.Parameters["GroupId"].Value; + + // Get the group's sub-group Ids + List<int> groupIds = new List<int>(); + DataTable groupGroupTable = groupData.Tables[0]; + foreach (DataRow row in groupGroupTable.Rows) { + groupIds.Add((int) row[0]); + } + + // The the group's user Ids + List<int> userIds = new List<int>(); + DataTable userGroupTable = groupData.Tables[1]; + foreach (DataRow row in userGroupTable.Rows) { + userIds.Add((int) row[0]); + } + + return new LocalStoreGroup(name, groupId, userIds.ToArray(), groupIds.ToArray()); } } @@ -1264,50 +1305,230 @@ /// <param name="groupId">The Id of the group to look up.</param> /// <returns>The matching group or null if non is found.</returns> public LocalStoreGroup GetGroup(int groupId) { - throw new NotImplementedException(); + using (MySqlConnection cnn = GetConnection()) { + return getGroup(groupId, cnn, null); + } } + private LocalStoreGroup getGroup(int groupId, MySqlConnection cnn, MySqlTransaction transaction) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.Transaction = transaction; + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetGroupFromId"; + cmd.Parameters.Add("GroupId", groupId); + cmd.Parameters.Add("Name", MySqlDbType.VarChar); + cmd.Parameters["Name"].Direction = ParameterDirection.Output; + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; + + DataSet groupData = new DataSet(); + MySqlDataAdapter da = new MySqlDataAdapter(cmd); + da.Fill(groupData); + + int resultValue = (int) cmd.Parameters["Result"].Value; + LocalStoreUserResult result = (LocalStoreUserResult) resultValue; + + // Check if the lookup succeeded + if (result != LocalStoreUserResult.OkSuccessful) { + return null; + } + + string name = (string) cmd.Parameters["Name"].Value; + + // Get the group's sub-group Ids + List<int> groupIds = new List<int>(); + DataTable groupGroupTable = groupData.Tables[0]; + foreach (DataRow row in groupGroupTable.Rows) { + groupIds.Add((int) row[0]); + } + + // The the group's user Ids + List<int> userIds = new List<int>(); + DataTable userGroupTable = groupData.Tables[1]; + foreach (DataRow row in userGroupTable.Rows) { + userIds.Add((int) row[0]); + } + + return new LocalStoreGroup(name, groupId, userIds.ToArray(), groupIds.ToArray()); + } + } + #endregion + + #region GetGroups /// <summary> /// Gets the list of groups that currently exist in the local store. /// </summary> /// <returns>The list of groups.</returns> public LocalStoreGroup[] GetGroups() { - throw new NotImplementedException(); + using (MySqlConnection cnn = GetConnection()) { + MySqlTransaction transaction = cnn.BeginTransaction(); + + try { + MySqlCommand cmd = cnn.CreateCommand(); + cmd.Transaction = transaction; + + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "GetGroupIds"; + MySqlDataReader reader = cmd.ExecuteReader(); + List<int> groupIds = new List<int>(); + + while (reader.NextResult()) { + int groupId = (int) reader["GroupId"]; + } + + List<LocalStoreGroup> groups = new List<LocalStoreGroup>(); + foreach (int groupId in groupIds) { + groups.Add(getGroup(groupId, cnn, transaction)); + } + + transaction.Commit(); + return groups.ToArray(); + + } catch (Exception ex) { + if (transaction != null) { + transaction.Rollback(); + } + + throw ex; + } + } } + #endregion + #region CreateGroup /// <summary> /// Creates a new group in the local store. /// </summary> /// <param name="name">The name of the new group.</param> /// <returns>The result of the attemp to create a new group.</returns> public LocalStoreGroupResult CreateGroup(string name) { - throw new NotImplementedException(); + using (MySqlConnection cnn = GetConnection()) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "CreateGroup"; + cmd.Parameters.Add("Name", name); + cmd.Parameters.Add("GroupId", MySqlDbType.Int32); + cmd.Parameters["GroupId"].Direction = ParameterDirection.Output; + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; + + cmd.ExecuteNonQuery(); + + int result = (int) cmd.Parameters["Result"].Value; + + return (LocalStoreGroupResult) result; + } + } } + #endregion + #region Delete Group /// <summary> /// Removes a group from the local store. /// </summary> /// <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(int groupId) { - throw new NotImplementedException(); + using (MySqlConnection cnn = GetConnection()) { + using (MySqlCommand cmd = cnn.CreateCommand()) { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "SELECT DeleteGroup(?GroupId)"; + cmd.Parameters.Add("GroupId", groupId); + cmd.Parameters.Add("Result", MySqlDbType.Int32); + cmd.Parameters["Result"].Direction = ParameterDirection.Output; + + cmd.ExecuteNonQuery(); + + int result = (int) cmd.Parameters["Result"].Value; + + return (LocalStoreGroupResult) result; + } + } } + #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) { - throw new NotImplementedException(); + using (MySqlConnection cnn = GetConnection()) { + MySqlTransaction transaction = cnn.BeginTransaction(); + + try { + MySqlCommand cmd = cnn.CreateCommand(); + cmd.Transaction = transaction; + + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "UpdateGroupName"; + cmd.Parameters.Add("GroupId", group.GroupId); + cmd.Parameters.Add("Name", group.Name); + 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; + + // Check for any errors updating the group name + if (result != LocalStoreGroupResult.OkSuccessful) { + transaction.Rollback(); + return result; + } + + // 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(); + + 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(); + } + + // 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(); + + 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(); + } + + transaction.Commit(); + return LocalStoreGroupResult.OkSuccessful; + + } catch (Exception ex) { + if (transaction != null) { + transaction.Rollback(); + } + + throw ex; + } + } } - + #endregion + /// <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 void AddGroupToMailDomain(int groupId, int mailDomainId) { + public LocalStoreGroupResult AddGroupToMailDomain(int groupId, int mailDomainId) { throw new NotImplementedException(); } @@ -1316,7 +1537,7 @@ /// </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 void RemoveGroupFromMailDomain(int groupId, int mailDomainId) { + public LocalStoreGroupResult RemoveGroupFromMailDomain(int groupId, int mailDomainId) { throw new NotImplementedException(); } #endregion This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
