1
0

PGSQLGenericTableHandler.cs 20 KB

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