opensim-development – Blame information for rev 1
?pathlinks?
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.Generic; |
||
30 | using System.Data; |
||
31 | using System.Reflection; |
||
32 | using log4net; |
||
33 | using OpenMetaverse; |
||
34 | using OpenSim.Framework; |
||
35 | using OpenSim.Region.Framework.Interfaces; |
||
36 | using System.Text; |
||
37 | using Npgsql; |
||
38 | |||
39 | namespace OpenSim.Data.PGSQL |
||
40 | { |
||
41 | public class PGSQLGenericTableHandler<T> : PGSqlFramework where T : class, new() |
||
42 | { |
||
43 | private static readonly ILog m_log = |
||
44 | LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); |
||
45 | |||
46 | protected string m_ConnectionString; |
||
47 | protected PGSQLManager m_database; //used for parameter type translation |
||
48 | protected Dictionary<string, FieldInfo> m_Fields = |
||
49 | new Dictionary<string, FieldInfo>(); |
||
50 | |||
51 | protected Dictionary<string, string> m_FieldTypes = new Dictionary<string, string>(); |
||
52 | |||
53 | protected List<string> m_ColumnNames = null; |
||
54 | protected string m_Realm; |
||
55 | protected FieldInfo m_DataField = null; |
||
56 | |||
57 | protected virtual Assembly Assembly |
||
58 | { |
||
59 | get { return GetType().Assembly; } |
||
60 | } |
||
61 | |||
62 | public PGSQLGenericTableHandler(string connectionString, |
||
63 | string realm, string storeName) |
||
64 | : base(connectionString) |
||
65 | { |
||
66 | m_Realm = realm; |
||
67 | |||
68 | m_ConnectionString = connectionString; |
||
69 | |||
70 | if (storeName != String.Empty) |
||
71 | { |
||
72 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
73 | { |
||
74 | conn.Open(); |
||
75 | Migration m = new Migration(conn, GetType().Assembly, storeName); |
||
76 | m.Update(); |
||
77 | } |
||
78 | |||
79 | } |
||
80 | m_database = new PGSQLManager(m_ConnectionString); |
||
81 | |||
82 | Type t = typeof(T); |
||
83 | FieldInfo[] fields = t.GetFields(BindingFlags.Public | |
||
84 | BindingFlags.Instance | |
||
85 | BindingFlags.DeclaredOnly); |
||
86 | |||
87 | LoadFieldTypes(); |
||
88 | |||
89 | if (fields.Length == 0) |
||
90 | return; |
||
91 | |||
92 | foreach (FieldInfo f in fields) |
||
93 | { |
||
94 | if (f.Name != "Data") |
||
95 | m_Fields[f.Name] = f; |
||
96 | else |
||
97 | m_DataField = f; |
||
98 | } |
||
99 | |||
100 | } |
||
101 | |||
102 | private void LoadFieldTypes() |
||
103 | { |
||
104 | m_FieldTypes = new Dictionary<string, string>(); |
||
105 | |||
106 | string query = string.Format(@"select column_name,data_type |
||
107 | from INFORMATION_SCHEMA.COLUMNS |
||
108 | where table_name = lower('{0}'); |
||
109 | |||
110 | ", m_Realm); |
||
111 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
112 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn)) |
||
113 | { |
||
114 | conn.Open(); |
||
115 | using (NpgsqlDataReader rdr = cmd.ExecuteReader()) |
||
116 | { |
||
117 | while (rdr.Read()) |
||
118 | { |
||
119 | // query produces 0 to many rows of single column, so always add the first item in each row |
||
120 | m_FieldTypes.Add((string)rdr[0], (string)rdr[1]); |
||
121 | } |
||
122 | } |
||
123 | } |
||
124 | } |
||
125 | |||
126 | private void CheckColumnNames(NpgsqlDataReader reader) |
||
127 | { |
||
128 | if (m_ColumnNames != null) |
||
129 | return; |
||
130 | |||
131 | m_ColumnNames = new List<string>(); |
||
132 | |||
133 | DataTable schemaTable = reader.GetSchemaTable(); |
||
134 | |||
135 | foreach (DataRow row in schemaTable.Rows) |
||
136 | { |
||
137 | if (row["ColumnName"] != null && |
||
138 | (!m_Fields.ContainsKey(row["ColumnName"].ToString()))) |
||
139 | m_ColumnNames.Add(row["ColumnName"].ToString()); |
||
140 | |||
141 | } |
||
142 | } |
||
143 | |||
144 | // TODO GET CONSTRAINTS FROM POSTGRESQL |
||
145 | private List<string> GetConstraints() |
||
146 | { |
||
147 | List<string> constraints = new List<string>(); |
||
148 | string query = string.Format(@"SELECT kcu.column_name |
||
149 | FROM information_schema.table_constraints tc |
||
150 | LEFT JOIN information_schema.key_column_usage kcu |
||
151 | ON tc.constraint_catalog = kcu.constraint_catalog |
||
152 | AND tc.constraint_schema = kcu.constraint_schema |
||
153 | AND tc.constraint_name = kcu.constraint_name |
||
154 | |||
155 | LEFT JOIN information_schema.referential_constraints rc |
||
156 | ON tc.constraint_catalog = rc.constraint_catalog |
||
157 | AND tc.constraint_schema = rc.constraint_schema |
||
158 | AND tc.constraint_name = rc.constraint_name |
||
159 | |||
160 | LEFT JOIN information_schema.constraint_column_usage ccu |
||
161 | ON rc.unique_constraint_catalog = ccu.constraint_catalog |
||
162 | AND rc.unique_constraint_schema = ccu.constraint_schema |
||
163 | AND rc.unique_constraint_name = ccu.constraint_name |
||
164 | |||
165 | where tc.table_name = lower('{0}') |
||
166 | and lower(tc.constraint_type) in ('primary key') |
||
167 | and kcu.column_name is not null |
||
168 | ;", m_Realm); |
||
169 | |||
170 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
171 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn)) |
||
172 | { |
||
173 | conn.Open(); |
||
174 | using (NpgsqlDataReader rdr = cmd.ExecuteReader()) |
||
175 | { |
||
176 | while (rdr.Read()) |
||
177 | { |
||
178 | // query produces 0 to many rows of single column, so always add the first item in each row |
||
179 | constraints.Add((string)rdr[0]); |
||
180 | } |
||
181 | } |
||
182 | return constraints; |
||
183 | } |
||
184 | } |
||
185 | |||
186 | public virtual T[] Get(string field, string key) |
||
187 | { |
||
188 | return Get(new string[] { field }, new string[] { key }); |
||
189 | } |
||
190 | |||
191 | public virtual T[] Get(string[] fields, string[] keys) |
||
192 | { |
||
193 | if (fields.Length != keys.Length) |
||
194 | return new T[0]; |
||
195 | |||
196 | List<string> terms = new List<string>(); |
||
197 | |||
198 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
199 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
200 | { |
||
201 | |||
202 | for (int i = 0; i < fields.Length; i++) |
||
203 | { |
||
204 | if ( m_FieldTypes.ContainsKey(fields[i]) ) |
||
205 | cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i], m_FieldTypes[fields[i]])); |
||
206 | else |
||
207 | cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i])); |
||
208 | |||
209 | terms.Add(" \"" + fields[i] + "\" = :" + fields[i]); |
||
210 | } |
||
211 | |||
212 | string where = String.Join(" AND ", terms.ToArray()); |
||
213 | |||
214 | string query = String.Format("SELECT * FROM {0} WHERE {1}", |
||
215 | m_Realm, where); |
||
216 | |||
217 | cmd.Connection = conn; |
||
218 | cmd.CommandText = query; |
||
219 | conn.Open(); |
||
220 | return DoQuery(cmd); |
||
221 | } |
||
222 | } |
||
223 | |||
224 | protected T[] DoQuery(NpgsqlCommand cmd) |
||
225 | { |
||
226 | List<T> result = new List<T>(); |
||
227 | if (cmd.Connection == null) |
||
228 | { |
||
229 | cmd.Connection = new NpgsqlConnection(m_connectionString); |
||
230 | } |
||
231 | if (cmd.Connection.State == ConnectionState.Closed) |
||
232 | { |
||
233 | cmd.Connection.Open(); |
||
234 | } |
||
235 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) |
||
236 | { |
||
237 | if (reader == null) |
||
238 | return new T[0]; |
||
239 | |||
240 | CheckColumnNames(reader); |
||
241 | |||
242 | while (reader.Read()) |
||
243 | { |
||
244 | T row = new T(); |
||
245 | |||
246 | foreach (string name in m_Fields.Keys) |
||
247 | { |
||
248 | if (m_Fields[name].GetValue(row) is bool) |
||
249 | { |
||
250 | int v = Convert.ToInt32(reader[name]); |
||
251 | m_Fields[name].SetValue(row, v != 0 ? true : false); |
||
252 | } |
||
253 | else if (m_Fields[name].GetValue(row) is UUID) |
||
254 | { |
||
255 | UUID uuid = UUID.Zero; |
||
256 | |||
257 | UUID.TryParse(reader[name].ToString(), out uuid); |
||
258 | m_Fields[name].SetValue(row, uuid); |
||
259 | } |
||
260 | else if (m_Fields[name].GetValue(row) is int) |
||
261 | { |
||
262 | int v = Convert.ToInt32(reader[name]); |
||
263 | m_Fields[name].SetValue(row, v); |
||
264 | } |
||
265 | else |
||
266 | { |
||
267 | m_Fields[name].SetValue(row, reader[name]); |
||
268 | } |
||
269 | } |
||
270 | |||
271 | if (m_DataField != null) |
||
272 | { |
||
273 | Dictionary<string, string> data = |
||
274 | new Dictionary<string, string>(); |
||
275 | |||
276 | foreach (string col in m_ColumnNames) |
||
277 | { |
||
278 | data[col] = reader[col].ToString(); |
||
279 | |||
280 | if (data[col] == null) |
||
281 | data[col] = String.Empty; |
||
282 | } |
||
283 | |||
284 | m_DataField.SetValue(row, data); |
||
285 | } |
||
286 | |||
287 | result.Add(row); |
||
288 | } |
||
289 | return result.ToArray(); |
||
290 | } |
||
291 | } |
||
292 | |||
293 | public virtual T[] Get(string where) |
||
294 | { |
||
295 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
296 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
297 | { |
||
298 | |||
299 | string query = String.Format("SELECT * FROM {0} WHERE {1}", |
||
300 | m_Realm, where); |
||
301 | cmd.Connection = conn; |
||
302 | cmd.CommandText = query; |
||
303 | |||
304 | //m_log.WarnFormat("[PGSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where); |
||
305 | |||
306 | conn.Open(); |
||
307 | return DoQuery(cmd); |
||
308 | } |
||
309 | } |
||
310 | |||
311 | public virtual bool Store(T row) |
||
312 | { |
||
313 | List<string> constraintFields = GetConstraints(); |
||
314 | List<KeyValuePair<string, string>> constraints = new List<KeyValuePair<string, string>>(); |
||
315 | |||
316 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
317 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
318 | { |
||
319 | |||
320 | StringBuilder query = new StringBuilder(); |
||
321 | List<String> names = new List<String>(); |
||
322 | List<String> values = new List<String>(); |
||
323 | |||
324 | foreach (FieldInfo fi in m_Fields.Values) |
||
325 | { |
||
326 | names.Add(fi.Name); |
||
327 | values.Add(":" + fi.Name); |
||
328 | // Temporarily return more information about what field is unexpectedly null for |
||
329 | // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the |
||
330 | // InventoryTransferModule or we may be required to substitute a DBNull here. |
||
331 | if (fi.GetValue(row) == null) |
||
332 | throw new NullReferenceException( |
||
333 | string.Format( |
||
334 | "[PGSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null", |
||
335 | fi.Name, row)); |
||
336 | |||
337 | if (constraintFields.Count > 0 && constraintFields.Contains(fi.Name)) |
||
338 | { |
||
339 | constraints.Add(new KeyValuePair<string, string>(fi.Name, fi.GetValue(row).ToString() )); |
||
340 | } |
||
341 | if (m_FieldTypes.ContainsKey(fi.Name)) |
||
342 | cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row), m_FieldTypes[fi.Name])); |
||
343 | else |
||
344 | cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row))); |
||
345 | } |
||
346 | |||
347 | if (m_DataField != null) |
||
348 | { |
||
349 | Dictionary<string, string> data = |
||
350 | (Dictionary<string, string>)m_DataField.GetValue(row); |
||
351 | |||
352 | foreach (KeyValuePair<string, string> kvp in data) |
||
353 | { |
||
354 | if (constraintFields.Count > 0 && constraintFields.Contains(kvp.Key)) |
||
355 | { |
||
356 | constraints.Add(new KeyValuePair<string, string>(kvp.Key, kvp.Key)); |
||
357 | } |
||
358 | names.Add(kvp.Key); |
||
359 | values.Add(":" + kvp.Key); |
||
360 | |||
361 | if (m_FieldTypes.ContainsKey(kvp.Key)) |
||
362 | cmd.Parameters.Add(m_database.CreateParameter("" + kvp.Key, kvp.Value, m_FieldTypes[kvp.Key])); |
||
363 | else |
||
364 | cmd.Parameters.Add(m_database.CreateParameter("" + kvp.Key, kvp.Value)); |
||
365 | } |
||
366 | |||
367 | } |
||
368 | |||
369 | query.AppendFormat("UPDATE {0} SET ", m_Realm); |
||
370 | int i = 0; |
||
371 | for (i = 0; i < names.Count - 1; i++) |
||
372 | { |
||
373 | query.AppendFormat("\"{0}\" = {1}, ", names[i], values[i]); |
||
374 | } |
||
375 | query.AppendFormat("\"{0}\" = {1} ", names[i], values[i]); |
||
376 | if (constraints.Count > 0) |
||
377 | { |
||
378 | List<string> terms = new List<string>(); |
||
379 | for (int j = 0; j < constraints.Count; j++) |
||
380 | { |
||
381 | terms.Add(String.Format(" \"{0}\" = :{0}", constraints[j].Key)); |
||
382 | } |
||
383 | string where = String.Join(" AND ", terms.ToArray()); |
||
384 | query.AppendFormat(" WHERE {0} ", where); |
||
385 | |||
386 | } |
||
387 | cmd.Connection = conn; |
||
388 | cmd.CommandText = query.ToString(); |
||
389 | |||
390 | conn.Open(); |
||
391 | if (cmd.ExecuteNonQuery() > 0) |
||
392 | { |
||
393 | //m_log.WarnFormat("[PGSQLGenericTable]: Updating {0}", m_Realm); |
||
394 | return true; |
||
395 | } |
||
396 | else |
||
397 | { |
||
398 | // assume record has not yet been inserted |
||
399 | |||
400 | query = new StringBuilder(); |
||
401 | query.AppendFormat("INSERT INTO {0} (\"", m_Realm); |
||
402 | query.Append(String.Join("\",\"", names.ToArray())); |
||
403 | query.Append("\") values (" + String.Join(",", values.ToArray()) + ")"); |
||
404 | cmd.Connection = conn; |
||
405 | cmd.CommandText = query.ToString(); |
||
406 | |||
407 | // m_log.WarnFormat("[PGSQLGenericTable]: Inserting into {0} sql {1}", m_Realm, cmd.CommandText); |
||
408 | |||
409 | if (conn.State != ConnectionState.Open) |
||
410 | conn.Open(); |
||
411 | if (cmd.ExecuteNonQuery() > 0) |
||
412 | return true; |
||
413 | } |
||
414 | |||
415 | return false; |
||
416 | } |
||
417 | } |
||
418 | |||
419 | public virtual bool Delete(string field, string key) |
||
420 | { |
||
421 | return Delete(new string[] { field }, new string[] { key }); |
||
422 | } |
||
423 | |||
424 | public virtual bool Delete(string[] fields, string[] keys) |
||
425 | { |
||
426 | if (fields.Length != keys.Length) |
||
427 | return false; |
||
428 | |||
429 | List<string> terms = new List<string>(); |
||
430 | |||
431 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
||
432 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
433 | { |
||
434 | for (int i = 0; i < fields.Length; i++) |
||
435 | { |
||
436 | if (m_FieldTypes.ContainsKey(fields[i])) |
||
437 | cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i], m_FieldTypes[fields[i]])); |
||
438 | else |
||
439 | cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i])); |
||
440 | |||
441 | terms.Add(" \"" + fields[i] + "\" = :" + fields[i]); |
||
442 | } |
||
443 | |||
444 | string where = String.Join(" AND ", terms.ToArray()); |
||
445 | |||
446 | string query = String.Format("DELETE FROM {0} WHERE {1}", m_Realm, where); |
||
447 | |||
448 | cmd.Connection = conn; |
||
449 | cmd.CommandText = query; |
||
450 | conn.Open(); |
||
451 | |||
452 | if (cmd.ExecuteNonQuery() > 0) |
||
453 | { |
||
454 | //m_log.Warn("[PGSQLGenericTable]: " + deleteCommand); |
||
455 | return true; |
||
456 | } |
||
457 | return false; |
||
458 | } |
||
459 | } |
||
460 | public long GetCount(string field, string key) |
||
461 | { |
||
462 | return GetCount(new string[] { field }, new string[] { key }); |
||
463 | } |
||
464 | |||
465 | public long GetCount(string[] fields, string[] keys) |
||
466 | { |
||
467 | if (fields.Length != keys.Length) |
||
468 | return 0; |
||
469 | |||
470 | List<string> terms = new List<string>(); |
||
471 | |||
472 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
473 | { |
||
474 | for (int i = 0; i < fields.Length; i++) |
||
475 | { |
||
476 | cmd.Parameters.AddWithValue(fields[i], keys[i]); |
||
477 | terms.Add("\"" + fields[i] + "\" = :" + fields[i]); |
||
478 | } |
||
479 | |||
480 | string where = String.Join(" and ", terms.ToArray()); |
||
481 | |||
482 | string query = String.Format("select count(*) from {0} where {1}", |
||
483 | m_Realm, where); |
||
484 | |||
485 | cmd.CommandText = query; |
||
486 | |||
487 | Object result = DoQueryScalar(cmd); |
||
488 | |||
489 | return Convert.ToInt64(result); |
||
490 | } |
||
491 | } |
||
492 | |||
493 | public long GetCount(string where) |
||
494 | { |
||
495 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
||
496 | { |
||
497 | string query = String.Format("select count(*) from {0} where {1}", |
||
498 | m_Realm, where); |
||
499 | |||
500 | cmd.CommandText = query; |
||
501 | |||
502 | object result = DoQueryScalar(cmd); |
||
503 | |||
504 | return Convert.ToInt64(result); |
||
505 | } |
||
506 | } |
||
507 | |||
508 | public object DoQueryScalar(NpgsqlCommand cmd) |
||
509 | { |
||
510 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_ConnectionString)) |
||
511 | { |
||
512 | dbcon.Open(); |
||
513 | cmd.Connection = dbcon; |
||
514 | |||
515 | return cmd.ExecuteScalar(); |
||
516 | } |
||
517 | } |
||
518 | } |
||
519 | } |