1
0

PGSQLUserAccountData.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325
  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.Text;
  34. using Npgsql;
  35. using log4net;
  36. using System.Reflection;
  37. namespace OpenSim.Data.PGSQL
  38. {
  39. public class PGSQLUserAccountData : PGSQLGenericTableHandler<UserAccountData>,IUserAccountData
  40. {
  41. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  42. public PGSQLUserAccountData(string connectionString, string realm) :
  43. base(connectionString, realm, "UserAccount")
  44. {
  45. }
  46. /*
  47. private string m_Realm;
  48. private List<string> m_ColumnNames = null;
  49. private PGSQLManager m_database;
  50. public PGSQLUserAccountData(string connectionString, string realm) :
  51. base(connectionString, realm, "UserAccount")
  52. {
  53. m_Realm = realm;
  54. m_ConnectionString = connectionString;
  55. m_database = new PGSQLManager(connectionString);
  56. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  57. {
  58. conn.Open();
  59. Migration m = new Migration(conn, GetType().Assembly, "UserAccount");
  60. m.Update();
  61. }
  62. }
  63. */
  64. /*
  65. public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
  66. {
  67. return null;
  68. }
  69. */
  70. /*
  71. public override UserAccountData[] Get(string[] fields, string[] keys)
  72. {
  73. UserAccountData[] retUA = base.Get(fields,keys);
  74. if (retUA.Length > 0)
  75. {
  76. Dictionary<string, string> data = retUA[0].Data;
  77. Dictionary<string, string> data2 = new Dictionary<string, string>();
  78. foreach (KeyValuePair<string,string> chave in data)
  79. {
  80. string s2 = chave.Key;
  81. data2[s2] = chave.Value;
  82. if (!m_FieldTypes.ContainsKey(chave.Key))
  83. {
  84. string tipo = "";
  85. m_FieldTypes.TryGetValue(chave.Key, out tipo);
  86. m_FieldTypes.Add(s2, tipo);
  87. }
  88. }
  89. foreach (KeyValuePair<string, string> chave in data2)
  90. {
  91. if (!retUA[0].Data.ContainsKey(chave.Key))
  92. retUA[0].Data.Add(chave.Key, chave.Value);
  93. }
  94. }
  95. return retUA;
  96. }
  97. */
  98. /*
  99. public UserAccountData Get(UUID principalID, UUID scopeID)
  100. {
  101. UserAccountData ret = new UserAccountData();
  102. ret.Data = new Dictionary<string, string>();
  103. string sql = string.Format(@"select * from {0} where ""PrincipalID"" = :principalID", m_Realm);
  104. if (scopeID != UUID.Zero)
  105. sql += @" and ""ScopeID"" = :scopeID";
  106. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  107. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  108. {
  109. cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
  110. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  111. conn.Open();
  112. using (NpgsqlDataReader result = cmd.ExecuteReader())
  113. {
  114. if (result.Read())
  115. {
  116. ret.PrincipalID = principalID;
  117. UUID scope;
  118. UUID.TryParse(result["scopeid"].ToString(), out scope);
  119. ret.ScopeID = scope;
  120. if (m_ColumnNames == null)
  121. {
  122. m_ColumnNames = new List<string>();
  123. DataTable schemaTable = result.GetSchemaTable();
  124. foreach (DataRow row in schemaTable.Rows)
  125. m_ColumnNames.Add(row["ColumnName"].ToString());
  126. }
  127. foreach (string s in m_ColumnNames)
  128. {
  129. string s2 = s;
  130. if (s2 == "uuid")
  131. continue;
  132. if (s2 == "scopeid")
  133. continue;
  134. ret.Data[s] = result[s].ToString();
  135. }
  136. return ret;
  137. }
  138. }
  139. }
  140. return null;
  141. }
  142. public override bool Store(UserAccountData data)
  143. {
  144. if (data.Data.ContainsKey("PrincipalID"))
  145. data.Data.Remove("PrincipalID");
  146. if (data.Data.ContainsKey("ScopeID"))
  147. data.Data.Remove("ScopeID");
  148. string[] fields = new List<string>(data.Data.Keys).ToArray();
  149. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  150. using (NpgsqlCommand cmd = new NpgsqlCommand())
  151. {
  152. m_log.DebugFormat("[USER]: Try to update user {0} {1}", data.FirstName, data.LastName);
  153. StringBuilder updateBuilder = new StringBuilder();
  154. updateBuilder.AppendFormat("update {0} set ", m_Realm);
  155. bool first = true;
  156. foreach (string field in fields)
  157. {
  158. if (!first)
  159. updateBuilder.Append(", ");
  160. updateBuilder.AppendFormat("\"{0}\" = :{0}", field);
  161. first = false;
  162. if (m_FieldTypes.ContainsKey(field))
  163. cmd.Parameters.Add(m_database.CreateParameter("" + field, data.Data[field], m_FieldTypes[field]));
  164. else
  165. cmd.Parameters.Add(m_database.CreateParameter("" + field, data.Data[field]));
  166. }
  167. updateBuilder.Append(" where \"PrincipalID\" = :principalID");
  168. if (data.ScopeID != UUID.Zero)
  169. updateBuilder.Append(" and \"ScopeID\" = :scopeID");
  170. cmd.CommandText = updateBuilder.ToString();
  171. cmd.Connection = conn;
  172. cmd.Parameters.Add(m_database.CreateParameter("principalID", data.PrincipalID));
  173. cmd.Parameters.Add(m_database.CreateParameter("scopeID", data.ScopeID));
  174. m_log.DebugFormat("[USER]: SQL update user {0} ", cmd.CommandText);
  175. conn.Open();
  176. m_log.DebugFormat("[USER]: CON opened update user {0} ", cmd.CommandText);
  177. int conta = 0;
  178. try
  179. {
  180. conta = cmd.ExecuteNonQuery();
  181. }
  182. catch (Exception e){
  183. m_log.ErrorFormat("[USER]: ERROR opened update user {0} ", e.Message);
  184. }
  185. if (conta < 1)
  186. {
  187. m_log.DebugFormat("[USER]: Try to insert user {0} {1}", data.FirstName, data.LastName);
  188. StringBuilder insertBuilder = new StringBuilder();
  189. insertBuilder.AppendFormat(@"insert into {0} (""PrincipalID"", ""ScopeID"", ""FirstName"", ""LastName"", """, m_Realm);
  190. insertBuilder.Append(String.Join(@""", """, fields));
  191. insertBuilder.Append(@""") values (:principalID, :scopeID, :FirstName, :LastName, :");
  192. insertBuilder.Append(String.Join(", :", fields));
  193. insertBuilder.Append(");");
  194. cmd.Parameters.Add(m_database.CreateParameter("FirstName", data.FirstName));
  195. cmd.Parameters.Add(m_database.CreateParameter("LastName", data.LastName));
  196. cmd.CommandText = insertBuilder.ToString();
  197. if (cmd.ExecuteNonQuery() < 1)
  198. {
  199. return false;
  200. }
  201. }
  202. else
  203. m_log.DebugFormat("[USER]: User {0} {1} exists", data.FirstName, data.LastName);
  204. }
  205. return true;
  206. }
  207. public bool Store(UserAccountData data, UUID principalID, string token)
  208. {
  209. return false;
  210. }
  211. public bool SetDataItem(UUID principalID, string item, string value)
  212. {
  213. string sql = string.Format(@"update {0} set {1} = :{1} where ""UUID"" = :UUID", m_Realm, item);
  214. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  215. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  216. {
  217. if (m_FieldTypes.ContainsKey(item))
  218. cmd.Parameters.Add(m_database.CreateParameter("" + item, value, m_FieldTypes[item]));
  219. else
  220. cmd.Parameters.Add(m_database.CreateParameter("" + item, value));
  221. cmd.Parameters.Add(m_database.CreateParameter("UUID", principalID));
  222. conn.Open();
  223. if (cmd.ExecuteNonQuery() > 0)
  224. return true;
  225. }
  226. return false;
  227. }
  228. */
  229. /*
  230. public UserAccountData[] Get(string[] keys, string[] vals)
  231. {
  232. return null;
  233. }
  234. */
  235. public UserAccountData[] GetUsers(UUID scopeID, string query)
  236. {
  237. string[] words = query.Split(new char[] { ' ' });
  238. for (int i = 0; i < words.Length; i++)
  239. {
  240. if (words[i].Length < 3)
  241. {
  242. if (i != words.Length - 1)
  243. Array.Copy(words, i + 1, words, i, words.Length - i - 1);
  244. Array.Resize(ref words, words.Length - 1);
  245. }
  246. }
  247. if (words.Length == 0)
  248. return new UserAccountData[0];
  249. if (words.Length > 2)
  250. return new UserAccountData[0];
  251. string sql = "";
  252. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  253. using (NpgsqlCommand cmd = new NpgsqlCommand())
  254. {
  255. if (words.Length == 1)
  256. {
  257. sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""='00000000-0000-0000-0000-000000000000') and (""FirstName"" ilike :search or ""LastName"" ilike :search)", m_Realm);
  258. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  259. cmd.Parameters.Add(m_database.CreateParameter("search", "%" + words[0] + "%"));
  260. }
  261. else
  262. {
  263. sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""='00000000-0000-0000-0000-000000000000') and (""FirstName"" ilike :searchFirst or ""LastName"" ilike :searchLast)", m_Realm);
  264. cmd.Parameters.Add(m_database.CreateParameter("searchFirst", "%" + words[0] + "%"));
  265. cmd.Parameters.Add(m_database.CreateParameter("searchLast", "%" + words[1] + "%"));
  266. cmd.Parameters.Add(m_database.CreateParameter("ScopeID", scopeID.ToString()));
  267. }
  268. cmd.Connection = conn;
  269. cmd.CommandText = sql;
  270. conn.Open();
  271. return DoQuery(cmd);
  272. }
  273. }
  274. }
  275. }