opensim – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 eva 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  
28 using System;
29 using System.Collections;
30 using System.Collections.Generic;
31 using System.Data;
32 using OpenMetaverse;
33 using OpenSim.Framework;
34 using System.Text;
35 using Npgsql;
36 using log4net;
37 using System.Reflection;
38  
39 namespace 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  
302 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
303 using (NpgsqlCommand cmd = new NpgsqlCommand())
304 {
305 if (words.Length == 1)
306 {
307 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);
308 cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
309 cmd.Parameters.Add(m_database.CreateParameter("search", "%" + words[0] + "%"));
310 }
311 else
312 {
313 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);
314 cmd.Parameters.Add(m_database.CreateParameter("searchFirst", "%" + words[0] + "%"));
315 cmd.Parameters.Add(m_database.CreateParameter("searchLast", "%" + words[1] + "%"));
316 cmd.Parameters.Add(m_database.CreateParameter("ScopeID", scopeID.ToString()));
317 }
318 cmd.Connection = conn;
319 cmd.CommandText = sql;
320 conn.Open();
321 return DoQuery(cmd);
322 }
323 }
324 }
325 }