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