MSSQLUserAccountData.cs 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections;
  29. using System.Collections.Generic;
  30. using System.Data;
  31. using OpenMetaverse;
  32. using OpenSim.Framework;
  33. using System.Data.SqlClient;
  34. using System.Text;
  35. namespace OpenSim.Data.MSSQL
  36. {
  37. public class MSSQLUserAccountData : MSSQLGenericTableHandler<UserAccountData>,IUserAccountData
  38. {
  39. public MSSQLUserAccountData(string connectionString, string realm) :
  40. base(connectionString, realm, "UserAccount")
  41. {
  42. }
  43. //private string m_Realm;
  44. //private List<string> m_ColumnNames = null;
  45. //private MSSQLManager m_database;
  46. //public MSSQLUserAccountData(string connectionString, string realm)
  47. //{
  48. // m_Realm = realm;
  49. // m_ConnectionString = connectionString;
  50. // m_database = new MSSQLManager(connectionString);
  51. // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  52. // {
  53. // conn.Open();
  54. // Migration m = new Migration(conn, GetType().Assembly, "UserStore");
  55. // m.Update();
  56. // }
  57. //}
  58. //public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
  59. //{
  60. // return null;
  61. //}
  62. //public UserAccountData Get(UUID principalID, UUID scopeID)
  63. //{
  64. // UserAccountData ret = new UserAccountData();
  65. // ret.Data = new Dictionary<string, string>();
  66. // string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
  67. // if (scopeID != UUID.Zero)
  68. // sql += " and ScopeID = @scopeID";
  69. // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  70. // using (SqlCommand cmd = new SqlCommand(sql, conn))
  71. // {
  72. // cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
  73. // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  74. // conn.Open();
  75. // using (SqlDataReader result = cmd.ExecuteReader())
  76. // {
  77. // if (result.Read())
  78. // {
  79. // ret.PrincipalID = principalID;
  80. // UUID scope;
  81. // UUID.TryParse(result["ScopeID"].ToString(), out scope);
  82. // ret.ScopeID = scope;
  83. // if (m_ColumnNames == null)
  84. // {
  85. // m_ColumnNames = new List<string>();
  86. // DataTable schemaTable = result.GetSchemaTable();
  87. // foreach (DataRow row in schemaTable.Rows)
  88. // m_ColumnNames.Add(row["ColumnName"].ToString());
  89. // }
  90. // foreach (string s in m_ColumnNames)
  91. // {
  92. // if (s == "UUID")
  93. // continue;
  94. // if (s == "ScopeID")
  95. // continue;
  96. // ret.Data[s] = result[s].ToString();
  97. // }
  98. // return ret;
  99. // }
  100. // }
  101. // }
  102. // return null;
  103. //}
  104. //public bool Store(UserAccountData data)
  105. //{
  106. // if (data.Data.ContainsKey("UUID"))
  107. // data.Data.Remove("UUID");
  108. // if (data.Data.ContainsKey("ScopeID"))
  109. // data.Data.Remove("ScopeID");
  110. // string[] fields = new List<string>(data.Data.Keys).ToArray();
  111. // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  112. // using (SqlCommand cmd = new SqlCommand())
  113. // {
  114. // StringBuilder updateBuilder = new StringBuilder();
  115. // updateBuilder.AppendFormat("update {0} set ", m_Realm);
  116. // bool first = true;
  117. // foreach (string field in fields)
  118. // {
  119. // if (!first)
  120. // updateBuilder.Append(", ");
  121. // updateBuilder.AppendFormat("{0} = @{0}", field);
  122. // first = false;
  123. // cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
  124. // }
  125. // updateBuilder.Append(" where UUID = @principalID");
  126. // if (data.ScopeID != UUID.Zero)
  127. // updateBuilder.Append(" and ScopeID = @scopeID");
  128. // cmd.CommandText = updateBuilder.ToString();
  129. // cmd.Connection = conn;
  130. // cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
  131. // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
  132. // conn.Open();
  133. // if (cmd.ExecuteNonQuery() < 1)
  134. // {
  135. // StringBuilder insertBuilder = new StringBuilder();
  136. // insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm);
  137. // insertBuilder.Append(String.Join(", ", fields));
  138. // insertBuilder.Append(") values (@principalID, @scopeID, @");
  139. // insertBuilder.Append(String.Join(", @", fields));
  140. // insertBuilder.Append(")");
  141. // cmd.CommandText = insertBuilder.ToString();
  142. // if (cmd.ExecuteNonQuery() < 1)
  143. // {
  144. // return false;
  145. // }
  146. // }
  147. // }
  148. // return true;
  149. //}
  150. //public bool Store(UserAccountData data, UUID principalID, string token)
  151. //{
  152. // return false;
  153. //}
  154. //public bool SetDataItem(UUID principalID, string item, string value)
  155. //{
  156. // string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
  157. // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  158. // using (SqlCommand cmd = new SqlCommand(sql, conn))
  159. // {
  160. // cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
  161. // cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
  162. // conn.Open();
  163. // if (cmd.ExecuteNonQuery() > 0)
  164. // return true;
  165. // }
  166. // return false;
  167. //}
  168. //public UserAccountData[] Get(string[] keys, string[] vals)
  169. //{
  170. // return null;
  171. //}
  172. public UserAccountData[] GetUsers(UUID scopeID, string query)
  173. {
  174. string[] words = query.Split(new char[] { ' ' });
  175. for (int i = 0; i < words.Length; i++)
  176. {
  177. if (words[i].Length < 3)
  178. {
  179. if (i != words.Length - 1)
  180. Array.Copy(words, i + 1, words, i, words.Length - i - 1);
  181. Array.Resize(ref words, words.Length - 1);
  182. }
  183. }
  184. if (words.Length == 0)
  185. return new UserAccountData[0];
  186. if (words.Length > 2)
  187. return new UserAccountData[0];
  188. string sql = "";
  189. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  190. using (SqlCommand cmd = new SqlCommand())
  191. {
  192. if (words.Length == 1)
  193. {
  194. sql = 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);
  195. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  196. cmd.Parameters.Add(m_database.CreateParameter("@search", "%" + words[0] + "%"));
  197. }
  198. else
  199. {
  200. sql = 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);
  201. cmd.Parameters.Add(m_database.CreateParameter("@searchFirst", "%" + words[0] + "%"));
  202. cmd.Parameters.Add(m_database.CreateParameter("@searchLast", "%" + words[1] + "%"));
  203. cmd.Parameters.Add(m_database.CreateParameter("@ScopeID", scopeID.ToString()));
  204. }
  205. cmd.Connection = conn;
  206. cmd.CommandText = sql;
  207. conn.Open();
  208. return DoQuery(cmd);
  209. }
  210. }
  211. }
  212. }