From 2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf Mon Sep 17 00:00:00 2001 From: StrawberryFride Date: Wed, 24 Feb 2010 16:42:39 +0000 Subject: MSSQL Additions for Presence Refactor branch. Most functionality tested and works, some outstanding issues around login location and border crossings on y axis. Signed-off-by: Melanie --- OpenSim/Data/MSSQL/MSSQLUserAccountData.cs | 319 ++++++++++++++++------------- 1 file changed, 179 insertions(+), 140 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLUserAccountData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs index 01c64dc..9f18e5e 100644 --- a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs +++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs @@ -36,168 +36,207 @@ using System.Text; namespace OpenSim.Data.MSSQL { - public class MSSQLUserAccountData : IUserAccountData + public class MSSQLUserAccountData : MSSQLGenericTableHandler,IUserAccountData { - private string m_Realm; - private List m_ColumnNames = null; - private string m_ConnectionString; - private MSSQLManager m_database; - - public MSSQLUserAccountData(string connectionString, string realm) - { - m_Realm = realm; - m_ConnectionString = connectionString; - m_database = new MSSQLManager(connectionString); - - using (SqlConnection conn = new SqlConnection(m_ConnectionString)) - { - conn.Open(); - Migration m = new Migration(conn, GetType().Assembly, "UserStore"); - m.Update(); - } - } - - public List Query(UUID principalID, UUID scopeID, string query) + public MSSQLUserAccountData(string connectionString, string realm) : + base(connectionString, realm, "UserAccount") { - return null; } + //private string m_Realm; + //private List m_ColumnNames = null; + //private MSSQLManager m_database; + + //public MSSQLUserAccountData(string connectionString, string realm) + //{ + // m_Realm = realm; + // m_ConnectionString = connectionString; + // m_database = new MSSQLManager(connectionString); + + // using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + // { + // conn.Open(); + // Migration m = new Migration(conn, GetType().Assembly, "UserStore"); + // m.Update(); + // } + //} + + //public List Query(UUID principalID, UUID scopeID, string query) + //{ + // return null; + //} + + //public UserAccountData Get(UUID principalID, UUID scopeID) + //{ + // UserAccountData ret = new UserAccountData(); + // ret.Data = new Dictionary(); + + // string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm); + // if (scopeID != UUID.Zero) + // sql += " and ScopeID = @scopeID"; + + // using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + // using (SqlCommand cmd = new SqlCommand(sql, conn)) + // { + // cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); + // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); + + // conn.Open(); + // using (SqlDataReader result = cmd.ExecuteReader()) + // { + // if (result.Read()) + // { + // ret.PrincipalID = principalID; + // UUID scope; + // UUID.TryParse(result["ScopeID"].ToString(), out scope); + // ret.ScopeID = scope; + + // if (m_ColumnNames == null) + // { + // m_ColumnNames = new List(); + + // DataTable schemaTable = result.GetSchemaTable(); + // foreach (DataRow row in schemaTable.Rows) + // m_ColumnNames.Add(row["ColumnName"].ToString()); + // } + + // foreach (string s in m_ColumnNames) + // { + // if (s == "UUID") + // continue; + // if (s == "ScopeID") + // continue; + + // ret.Data[s] = result[s].ToString(); + // } + // return ret; + // } + // } + // } + // return null; + //} + + //public bool Store(UserAccountData data) + //{ + // if (data.Data.ContainsKey("UUID")) + // data.Data.Remove("UUID"); + // if (data.Data.ContainsKey("ScopeID")) + // data.Data.Remove("ScopeID"); + + // string[] fields = new List(data.Data.Keys).ToArray(); + + // using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + // using (SqlCommand cmd = new SqlCommand()) + // { + // StringBuilder updateBuilder = new StringBuilder(); + // updateBuilder.AppendFormat("update {0} set ", m_Realm); + // bool first = true; + // foreach (string field in fields) + // { + // if (!first) + // updateBuilder.Append(", "); + // updateBuilder.AppendFormat("{0} = @{0}", field); + + // first = false; + // cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field])); + // } + + // updateBuilder.Append(" where UUID = @principalID"); + + // if (data.ScopeID != UUID.Zero) + // updateBuilder.Append(" and ScopeID = @scopeID"); + + // cmd.CommandText = updateBuilder.ToString(); + // cmd.Connection = conn; + // cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID)); + // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID)); + // conn.Open(); + + // if (cmd.ExecuteNonQuery() < 1) + // { + // StringBuilder insertBuilder = new StringBuilder(); + // insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm); + // insertBuilder.Append(String.Join(", ", fields)); + // insertBuilder.Append(") values (@principalID, @scopeID, @"); + // insertBuilder.Append(String.Join(", @", fields)); + // insertBuilder.Append(")"); + + // cmd.CommandText = insertBuilder.ToString(); + + // if (cmd.ExecuteNonQuery() < 1) + // { + // return false; + // } + // } + // } + // return true; + //} + + //public bool Store(UserAccountData data, UUID principalID, string token) + //{ + // return false; + //} + + //public bool SetDataItem(UUID principalID, string item, string value) + //{ + // string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item); + // using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + // using (SqlCommand cmd = new SqlCommand(sql, conn)) + // { + // cmd.Parameters.Add(m_database.CreateParameter("@" + item, value)); + // cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID)); + + // conn.Open(); + + // if (cmd.ExecuteNonQuery() > 0) + // return true; + // } + // return false; + //} + + //public UserAccountData[] Get(string[] keys, string[] vals) + //{ + // return null; + //} - public UserAccountData Get(UUID principalID, UUID scopeID) + public UserAccountData[] GetUsers(UUID scopeID, string query) { - UserAccountData ret = new UserAccountData(); - ret.Data = new Dictionary(); + string[] words = query.Split(new char[] { ' ' }); - string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm); - if (scopeID != UUID.Zero) - sql += " and ScopeID = @scopeID"; - - using (SqlConnection conn = new SqlConnection(m_ConnectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) + for (int i = 0; i < words.Length; i++) { - cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); - cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); - - conn.Open(); - using (SqlDataReader result = cmd.ExecuteReader()) + if (words[i].Length < 3) { - if (result.Read()) - { - ret.PrincipalID = principalID; - UUID scope; - UUID.TryParse(result["ScopeID"].ToString(), out scope); - ret.ScopeID = scope; - - if (m_ColumnNames == null) - { - m_ColumnNames = new List(); - - DataTable schemaTable = result.GetSchemaTable(); - foreach (DataRow row in schemaTable.Rows) - m_ColumnNames.Add(row["ColumnName"].ToString()); - } - - foreach (string s in m_ColumnNames) - { - if (s == "UUID") - continue; - if (s == "ScopeID") - continue; - - ret.Data[s] = result[s].ToString(); - } - return ret; - } + if (i != words.Length - 1) + Array.Copy(words, i + 1, words, i, words.Length - i - 1); + Array.Resize(ref words, words.Length - 1); } } - return null; - } - public bool Store(UserAccountData data) - { - if (data.Data.ContainsKey("UUID")) - data.Data.Remove("UUID"); - if (data.Data.ContainsKey("ScopeID")) - data.Data.Remove("ScopeID"); + if (words.Length == 0) + return new UserAccountData[0]; - string[] fields = new List(data.Data.Keys).ToArray(); + if (words.Length > 2) + return new UserAccountData[0]; using (SqlConnection conn = new SqlConnection(m_ConnectionString)) using (SqlCommand cmd = new SqlCommand()) { - StringBuilder updateBuilder = new StringBuilder(); - updateBuilder.AppendFormat("update {0} set ", m_Realm); - bool first = true; - foreach (string field in fields) + if (words.Length == 1) { - if (!first) - updateBuilder.Append(", "); - updateBuilder.AppendFormat("{0} = @{0}", field); - - first = false; - cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field])); + cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @search or [LastName] like @search)", m_Realm); + cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); + cmd.Parameters.Add(m_database.CreateParameter("@search", "%" + words[0] + "%")); } - - updateBuilder.Append(" where UUID = @principalID"); - - if (data.ScopeID != UUID.Zero) - updateBuilder.Append(" and ScopeID = @scopeID"); - - cmd.CommandText = updateBuilder.ToString(); - cmd.Connection = conn; - cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID)); - cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID)); - conn.Open(); - - if (cmd.ExecuteNonQuery() < 1) + else { - StringBuilder insertBuilder = new StringBuilder(); - insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm); - insertBuilder.Append(String.Join(", ", fields)); - insertBuilder.Append(") values (@principalID, @scopeID, @"); - insertBuilder.Append(String.Join(", @", fields)); - insertBuilder.Append(")"); - - cmd.CommandText = insertBuilder.ToString(); - - if (cmd.ExecuteNonQuery() < 1) - { - return false; - } + cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @searchFirst or [LastName] like @searchLast)", m_Realm); + cmd.Parameters.Add(m_database.CreateParameter("@searchFirst", "%" + words[0] + "%")); + cmd.Parameters.Add(m_database.CreateParameter("@searchLast", "%" + words[1] + "%")); + cmd.Parameters.Add(m_database.CreateParameter("@ScopeID", scopeID.ToString())); } - } - return true; - } - - public bool Store(UserAccountData data, UUID principalID, string token) - { - return false; - } - - public bool SetDataItem(UUID principalID, string item, string value) - { - string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item); - using (SqlConnection conn = new SqlConnection(m_ConnectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(m_database.CreateParameter("@" + item, value)); - cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID)); - - conn.Open(); - if (cmd.ExecuteNonQuery() > 0) - return true; + return DoQuery(cmd); } - return false; - } - - public UserAccountData[] Get(string[] keys, string[] vals) - { - return null; - } - - public UserAccountData[] GetUsers(UUID scopeID, string query) - { - return null; } } } -- cgit v1.1