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;
}
