public void Setup() { this.stubbedController = MockRepository.GenerateStub<SqlController>(); this._user1 = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111111", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; this._user2 = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111112", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; this._user3 = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111113", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; this._group = new GroupDAO(this._user1) { Description = "Test description", GroupID = 1, GroupTag = "TEST", Moderators = new HashSet<UserDAO>(), Name = "TEST GROUP", EnabledPlugins = new HashSet<PluginDAO>(), Users = new HashSet<UserDAO>() }; this._group.Users.Add(this._user2); this.stubbedController.Stub(x => x.RetrieveGroup(this._group.GroupTag)).Return(this._group); this.stubbedController.Stub(x => x.RetrieveUserByPhoneEmail(this._user1.PhoneEmail)).Return(this._user1); this.stubbedController.Stub(x => x.RetrieveUserByPhoneEmail(this._user2.PhoneEmail)).Return(this._user2); }
public void Setup() { _controller = new SqlController(); _owner = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111111", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; _moderator = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111112", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; _user = new UserDAO() { UserName = "******", FirstName = "TEST", LastName = "USER", PhoneNumber = "1111111113", PhoneEmail = "*****@*****.**", Carrier = PhoneCarrier.Verizon, UserLevel = UserLevel.User, IsBanned = false, IsSuppressed = false }; _controller.CreateUser(_owner, "password"); _controller.CreateUser(_moderator, "password"); _controller.CreateUser(_user, "password"); _enabledPlugin = new PluginDAO() { Name = "EnPlgn", Description = "An enabled test plugin", IsDisabled = false, VersionNum = "1.0.0", OwnerID = _user.UserID, Access = PluginAccess.STANDARD, HelpText = "Help meh, I'm an enabled plugin!" }; _disabledPlugin = new PluginDAO() { Name = "DsPlgn", Description = "A disabled test plugin", IsDisabled = true, VersionNum = "1.0.0", OwnerID = _user.UserID, Access = PluginAccess.STANDARD, HelpText = "Help meh, I'm a disabled plugin!" }; _controller.CreatePlugin(_enabledPlugin); _controller.CreatePlugin(_disabledPlugin); _group = new GroupDAO(_owner) { Name = "Test Group", Description = "A test group, for testing", GroupTag = "TEST" }; }
/// <summary> /// Retrieves basic information for a group in a GroupDAO. Does not include users or plugins for a group. /// </summary> /// <param name="groupTag">The group tag of the group to retrieve.</param> /// <returns>A new GroupDAO object with basic group data.</returns> public GroupDAO RetrieveGroupMetadata(string groupTag) { if (string.IsNullOrEmpty(groupTag)) throw new ArgumentNullException(); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT id, name, description, owner_id, grouptag "); queryBuilder.Append("FROM groups "); queryBuilder.Append("WHERE grouptag = @grouptag"); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@grouptag", groupTag); conn.Open(); SqlDataReader reader = query.ExecuteReader(); GroupDAO group = null; // If there are no records returned from the select statement, the DataReader will be empty if (reader.Read()) { int groupID = (int)reader["id"]; string name = (string)reader["name"]; string description = (string)reader["description"]; int ownerID = (int)reader["owner_id"]; string groupTagReader = (string)reader["grouptag"]; group = new GroupDAO(RetrieveUser(ownerID)); group.GroupID = groupID; group.Name = name; group.Description = description; group.GroupTag = groupTagReader; return group; } else throw new CouldNotFindException("Could not find user with groupTag: " + groupTag); } }
/// <summary> /// Deletes an existing group that matches the given GroupDAO. All users and plugin assocations with the given /// GroupDAO will be deleted, however the users and plugins will not be. /// </summary> /// <param name="user">The PluginDAO to delete from the database.</param> /// <returns>true if the plugin was successfully deleted.</returns> /// <exception cref="ArgumentNullException">If the given PluginDAO or PluginDAO.PluginID is null.</exception> public bool DeleteGroup(GroupDAO group) { if (null == group || null == group.GroupID) throw new ArgumentNullException(); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { query.CommandText = "sp_deleteGroup"; query.CommandType = CommandType.StoredProcedure; query.Parameters.AddWithValue("@groupid", group.GroupID); conn.Open(); int effectedRows = query.ExecuteNonQuery(); /* One or more records should have been deleted: * The group record itself (1), any groupmember references (>= 0), and any groupplugin references (>= 0) */ return 0 < effectedRows; } }
private void RetrieveGroupUsers(GroupDAO group) { using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT u.id, u.username, u.first_name, u.last_name, u.phone, u.email_phone, u.carrier, u.user_level, u.banned, u.suppressed, gm.group_level "); queryBuilder.Append("FROM users u "); queryBuilder.Append("INNER JOIN groupmembers gm ON u.id = gm.user_id "); queryBuilder.Append("WHERE gm.group_id = @groupid "); queryBuilder.Append("AND u.suppressed = 0;"); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@groupid", group.GroupID); conn.Open(); SqlDataReader reader = query.ExecuteReader(); // If there are no records returned from the select statement, the DataReader will be empty while (reader.Read()) { UserDAO userDAO = BuildUserDAO(reader); switch ((int)reader["group_level"]) { // Moderator case 1: group.AddModerator(userDAO); break; // Owner case 2: // The owner should have been added in RetrieveGroupMetadata(groupTag) break; //group.Owner = userDAO; // This needs to be addressed, since the GroupDAO.Owner setter is private //break; // User default: group.AddUserToGroup(userDAO); break; } } } }
private void RetrieveGroupPlugins(GroupDAO group) { using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT p.id, p.name, p.description, p.disabled, p.version_num, p.owner_id, p.plugin_access, p.help_text "); queryBuilder.Append("FROM plugins p "); queryBuilder.Append("INNER JOIN groupplugins gm ON p.id = gm.plugin_id "); queryBuilder.Append("WHERE gm.group_id = @groupid "); queryBuilder.Append("AND gm.disabled = 0 "); queryBuilder.Append("AND p.disabled = 0"); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@groupid", group.GroupID); conn.Open(); SqlDataReader reader = query.ExecuteReader(); // If there are no records returned from the select statement, the DataReader will be empty while (reader.Read()) { group.AddPlugin(BuildPluginDAO(reader)); } } }
/// <summary> /// Inserts the group metadata into the "groups" table. /// </summary> /// <param name="group">The GroupDAO to insert into the database.</param> private bool InsertGroupMetadata(GroupDAO group) { using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("INSERT INTO groups (name, description, owner_id, created_dt, grouptag) "); queryBuilder.Append("VALUES "); queryBuilder.Append("(@name, @description, @owner_id, GETDATE(), @grouptag) "); queryBuilder.Append("; SELECT SCOPE_IDENTITY() "); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@name", group.Name); query.Parameters.AddWithValue("@description", group.Description); query.Parameters.AddWithValue("@owner_id", group.Owner.UserID); query.Parameters.AddWithValue("@grouptag", group.GroupTag); conn.Open(); int newID = (int)(decimal) query.ExecuteScalar(); if (1 > newID) return false; group.GroupID = newID; return true; } }
/// <summary> /// /// </summary> /// <param name="plugin"></param> /// <param name="key"></param> /// <param name="value"></param> public void UpdatePluginKeyValue(PluginDAO plugin, String key, String value, GroupDAO forGroup = null, UserDAO forUser = null) { if (null == plugin || null == key) throw new ArgumentNullException(); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); // Using SQL Server, so this isn't supported //queryBuilder.Append("INSERT INTO pluginkeyvalue (plugin_id, key_string, value_object) "); //queryBuilder.Append("VALUES "); //queryBuilder.Append("(@pluginid, @keystring, @valueobj) "); //queryBuilder.Append("ON DUPLICATE KEY UPDATE "); //queryBuilder.Append(" value_object = @valueobj "); queryBuilder.Append("begin tran \n"); queryBuilder.Append("if exists (select * from pluginkeyvalue with (updlock,serializable) where key_string = @keystring and plugin_id = @pluginid and group_id = @groupid) \n"); queryBuilder.Append(" begin \n"); queryBuilder.Append(" update pluginkeyvalue set value_object = @valueobj \n"); queryBuilder.Append(" where key_string = @keystring and plugin_id = @pluginid and group_id = @groupid\n"); queryBuilder.Append("end \n"); queryBuilder.Append("else \n"); queryBuilder.Append("begin \n"); queryBuilder.Append(" insert pluginkeyvalue (plugin_id, user_id, key_string, value_object, group_id) \n"); queryBuilder.Append(" values (@pluginid, @userid, @keystring, @valueobj, @groupid) \n"); queryBuilder.Append("end \n"); queryBuilder.Append("commit tran \n"); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@pluginid", plugin.PluginID.Value); if (forUser == null) query.Parameters.AddWithValue("@userid", DBNull.Value); else query.Parameters.AddWithValue("@userid", forUser.UserID.Value); if (forGroup == null) query.Parameters.AddWithValue("@groupid", DBNull.Value); else query.Parameters.AddWithValue("@groupid", forGroup.GroupID.Value); query.Parameters.AddWithValue("@keystring", key); query.Parameters.AddWithValue("@valueobj", value); conn.Open(); query.ExecuteNonQuery(); } }
public bool UpdateGroupPlugins(GroupDAO group) { CheckForPluginListModifications(group.GroupID, group.EnabledPlugins); return true; }
/// <summary> /// Updates the given group's owner with the given user. On completion, updates /// the given GroupDAO's owner object with the given user. /// </summary> /// <param name="group">The group to update the owner of.</param> /// <param name="newOwner">The user to set as owner of the given group.</param> /// <returns>true if successful.</returns> public bool UpdateGroupOwner(GroupDAO group, UserDAO newOwner) { if (null == group || null == newOwner) throw new ArgumentNullException("Cannot update null group or user"); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { query.CommandText = "UPDATE groups SET owner_id = @userid WHERE id = @groupid"; query.Parameters.AddWithValue("@userid", newOwner.UserID); query.Parameters.AddWithValue("@groupid", group.GroupID); conn.Open(); int effectedRows = query.ExecuteNonQuery(); if (1 == effectedRows) { // Update the GroupDAO reference with the new UserDAO group = new GroupDAO(newOwner) { GroupID = group.GroupID, Name = group.Name, Description = group.Description, GroupTag = group.GroupTag, Moderators = group.Moderators, Users = group.Users, EnabledPlugins = group.EnabledPlugins }; return true; } return false; } }
/// <summary> /// Updates the metadata (in the groups table) only for the given group. /// </summary> /// <param name="group">The group to update</param> /// <returns>true if successful</returns> public bool UpdateGroupMetadata(GroupDAO group) { if (null == group || null == group.GroupID) throw new ArgumentNullException(); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("UPDATE groups SET "); queryBuilder.Append("name = @name, "); queryBuilder.Append("description = @description, "); queryBuilder.Append("owner_id = @owner_id, "); queryBuilder.Append("grouptag = @grouptag "); queryBuilder.Append("WHERE id = @group_id"); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@name", group.Name); query.Parameters.AddWithValue("@description", group.Description); query.Parameters.AddWithValue("@owner_id", group.Owner.UserID); query.Parameters.AddWithValue("@grouptag", group.GroupTag); query.Parameters.AddWithValue("@group_id", group.GroupID); conn.Open(); int effectedRows = query.ExecuteNonQuery(); // Only one record should have been updated return 1 == effectedRows; } }
public bool UpdateGroupMembers(GroupDAO group) { CheckForUserListModifications(group.GroupID, group.Users, GroupLevel.User); CheckForUserListModifications(group.GroupID, group.Moderators, GroupLevel.Moderator); return true; }
/// <summary> /// Updates the metadata, the list of users for the group, and all enabled plugin relationships for the given group. /// </summary> /// <param name="group">The GroupDAO to update in the database.</param> /// <returns>true if successful.</returns> public bool UpdateGroup(GroupDAO group) { if (null == group || null == group.GroupID) throw new ArgumentNullException(); return (UpdateGroupMetadata(group) && UpdateGroupMembers(group) && UpdateGroupPlugins(group)); }
/// <summary> /// Inserts the given GroupDAO object into the database, along with the different relations /// between users, permissions, and plugins. /// </summary> /// <param name="group">The GroupDAO to insert into the database</param> /// <returns>true if the group was successfully added.</returns> /// <exception cref="ArgumentNullException">If the given group is null.</exception> public bool CreateGroup(GroupDAO group) { if (null == group) throw new ArgumentNullException(); /* * In order to prevent foreign key issues, data must be inserted in a specific order: * 1) GroupDAO metadata -> groups * 2) GroupDAO members -> groupmembers * 3) GroupDAO plugins -> groupplugins */ // Make sure the group owner exists first if (null == group.Owner) throw new CouldNotFindException("Group Owner is null, cannot find in database."); if (!UserExists(group.Owner.UserName, group.Owner.PhoneEmail)) throw new CouldNotFindException("User with username: "******" needs to be created before the group can be created."); if (GroupExists(group.Name)) throw new EntryAlreadyExistsException("Group with name: " + group.Name + " already exists."); InsertGroupMetadata(group); // Add the system plugin references to the group foreach (PluginDAO plugin in RetrieveSystemPlugins()) { InsertGroupPlugin(group.GroupID, plugin.PluginID, false); } return (InsertGroupMember(group.GroupID, group.Owner.UserID, GroupLevel.Owner) && InsertGroupMembers(group.GroupID, group.Moderators, GroupLevel.Moderator) && InsertGroupMembers(group.GroupID, group.Users, GroupLevel.User) && InsertGroupPlugins(group.GroupID, group.EnabledPlugins)); }
/// <summary> /// /// </summary> /// <param name="plugin"></param> /// <param name="key"></param> /// <returns></returns> public string RetrievePluginValue(PluginDAO plugin, String key, GroupDAO forGroup = null, UserDAO forUser = null) { if (null == plugin || null == key) throw new ArgumentNullException(); using (SqlConnection conn = new SqlConnection(CONNECTION_STRING)) using (SqlCommand query = conn.CreateCommand()) { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT value_object "); queryBuilder.Append("FROM pluginkeyvalue "); queryBuilder.Append("WHERE plugin_id = @pluginid "); if (forUser == null) queryBuilder.Append(" AND user_id IS NULL "); else queryBuilder.Append(" AND user_id = @userid "); if (forGroup == null) queryBuilder.Append(" AND group_id IS NULL "); else queryBuilder.Append(" AND group_id = @groupid "); queryBuilder.Append(" AND key_string = @keystring "); query.CommandText = queryBuilder.ToString(); query.Parameters.AddWithValue("@pluginid", plugin.PluginID.Value); if (forUser != null) query.Parameters.AddWithValue("@userid", forUser.UserID.Value); if (forGroup != null) query.Parameters.AddWithValue("@groupid", forGroup.GroupID.Value); query.Parameters.AddWithValue("@keystring", key); conn.Open(); SqlDataReader reader = query.ExecuteReader(); // If there are no records returned from the select statement, the DataReader will be empty if (reader.Read()) { return (string)reader["value_object"]; } else { return null; } } }
public void TearDown() { this._user2 = null; this._user1 = null; this._group = null; this.stubbedController = null; }