1
0

PGSQLRegionData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392
  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.Drawing;
  31. using System.IO;
  32. using System.Reflection;
  33. using log4net;
  34. using OpenMetaverse;
  35. using OpenSim.Framework;
  36. using OpenSim.Region.Framework.Interfaces;
  37. using OpenSim.Region.Framework.Scenes;
  38. using RegionFlags = OpenSim.Framework.RegionFlags;
  39. using Npgsql;
  40. namespace OpenSim.Data.PGSQL
  41. {
  42. /// <summary>
  43. /// A PGSQL Interface for the Region Server.
  44. /// </summary>
  45. public class PGSQLRegionData : IRegionData
  46. {
  47. private string m_Realm;
  48. private List<string> m_ColumnNames = null;
  49. private string m_ConnectionString;
  50. private PGSQLManager m_database;
  51. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  52. protected Dictionary<string, string> m_FieldTypes = new Dictionary<string, string>();
  53. protected virtual Assembly Assembly
  54. {
  55. get { return GetType().Assembly; }
  56. }
  57. public PGSQLRegionData(string connectionString, string realm)
  58. {
  59. m_Realm = realm;
  60. m_ConnectionString = connectionString;
  61. m_database = new PGSQLManager(connectionString);
  62. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  63. {
  64. conn.Open();
  65. Migration m = new Migration(conn, GetType().Assembly, "GridStore");
  66. m.Update();
  67. }
  68. LoadFieldTypes();
  69. }
  70. private void LoadFieldTypes()
  71. {
  72. m_FieldTypes = new Dictionary<string, string>();
  73. string query = string.Format(@"select column_name,data_type
  74. from INFORMATION_SCHEMA.COLUMNS
  75. where table_name = lower('{0}');
  76. ", m_Realm);
  77. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  78. using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
  79. {
  80. conn.Open();
  81. using (NpgsqlDataReader rdr = cmd.ExecuteReader())
  82. {
  83. while (rdr.Read())
  84. {
  85. // query produces 0 to many rows of single column, so always add the first item in each row
  86. m_FieldTypes.Add((string)rdr[0], (string)rdr[1]);
  87. }
  88. }
  89. }
  90. }
  91. public List<RegionData> Get(string regionName, UUID scopeID)
  92. {
  93. string sql = "select * from "+m_Realm+" where lower(\"regionName\") like lower(:regionName) ";
  94. if (scopeID != UUID.Zero)
  95. sql += " and \"ScopeID\" = :scopeID";
  96. sql += " order by lower(\"regionName\")";
  97. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  98. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  99. {
  100. cmd.Parameters.Add(m_database.CreateParameter("regionName", regionName));
  101. if (scopeID != UUID.Zero)
  102. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  103. conn.Open();
  104. return RunCommand(cmd);
  105. }
  106. }
  107. public RegionData Get(int posX, int posY, UUID scopeID)
  108. {
  109. string sql = "select * from "+m_Realm+" where \"locX\" = :posX and \"locY\" = :posY";
  110. if (scopeID != UUID.Zero)
  111. sql += " and \"ScopeID\" = :scopeID";
  112. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  113. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  114. {
  115. cmd.Parameters.Add(m_database.CreateParameter("posX", posX));
  116. cmd.Parameters.Add(m_database.CreateParameter("posY", posY));
  117. if (scopeID != UUID.Zero)
  118. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  119. conn.Open();
  120. List<RegionData> ret = RunCommand(cmd);
  121. if (ret.Count == 0)
  122. return null;
  123. return ret[0];
  124. }
  125. }
  126. public RegionData Get(UUID regionID, UUID scopeID)
  127. {
  128. string sql = "select * from "+m_Realm+" where uuid = :regionID";
  129. if (scopeID != UUID.Zero)
  130. sql += " and \"ScopeID\" = :scopeID";
  131. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  132. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  133. {
  134. cmd.Parameters.Add(m_database.CreateParameter("regionID", regionID));
  135. if (scopeID != UUID.Zero)
  136. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  137. conn.Open();
  138. List<RegionData> ret = RunCommand(cmd);
  139. if (ret.Count == 0)
  140. return null;
  141. return ret[0];
  142. }
  143. }
  144. public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
  145. {
  146. string sql = "select * from "+m_Realm+" where \"locX\" between :startX and :endX and \"locY\" between :startY and :endY";
  147. if (scopeID != UUID.Zero)
  148. sql += " and \"ScopeID\" = :scopeID";
  149. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  150. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  151. {
  152. cmd.Parameters.Add(m_database.CreateParameter("startX", startX));
  153. cmd.Parameters.Add(m_database.CreateParameter("startY", startY));
  154. cmd.Parameters.Add(m_database.CreateParameter("endX", endX));
  155. cmd.Parameters.Add(m_database.CreateParameter("endY", endY));
  156. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  157. conn.Open();
  158. return RunCommand(cmd);
  159. }
  160. }
  161. public List<RegionData> RunCommand(NpgsqlCommand cmd)
  162. {
  163. List<RegionData> retList = new List<RegionData>();
  164. NpgsqlDataReader result = cmd.ExecuteReader();
  165. while (result.Read())
  166. {
  167. RegionData ret = new RegionData();
  168. ret.Data = new Dictionary<string, object>();
  169. UUID regionID;
  170. UUID.TryParse(result["uuid"].ToString(), out regionID);
  171. ret.RegionID = regionID;
  172. UUID scope;
  173. UUID.TryParse(result["ScopeID"].ToString(), out scope);
  174. ret.ScopeID = scope;
  175. ret.RegionName = result["regionName"].ToString();
  176. ret.posX = Convert.ToInt32(result["locX"]);
  177. ret.posY = Convert.ToInt32(result["locY"]);
  178. ret.sizeX = Convert.ToInt32(result["sizeX"]);
  179. ret.sizeY = Convert.ToInt32(result["sizeY"]);
  180. if (m_ColumnNames == null)
  181. {
  182. m_ColumnNames = new List<string>();
  183. DataTable schemaTable = result.GetSchemaTable();
  184. foreach (DataRow row in schemaTable.Rows)
  185. m_ColumnNames.Add(row["ColumnName"].ToString());
  186. }
  187. foreach (string s in m_ColumnNames)
  188. {
  189. if (s == "uuid")
  190. continue;
  191. if (s == "ScopeID")
  192. continue;
  193. if (s == "regionName")
  194. continue;
  195. if (s == "locX")
  196. continue;
  197. if (s == "locY")
  198. continue;
  199. ret.Data[s] = result[s].ToString();
  200. }
  201. retList.Add(ret);
  202. }
  203. return retList;
  204. }
  205. public bool Store(RegionData data)
  206. {
  207. if (data.Data.ContainsKey("uuid"))
  208. data.Data.Remove("uuid");
  209. if (data.Data.ContainsKey("ScopeID"))
  210. data.Data.Remove("ScopeID");
  211. if (data.Data.ContainsKey("regionName"))
  212. data.Data.Remove("regionName");
  213. if (data.Data.ContainsKey("posX"))
  214. data.Data.Remove("posX");
  215. if (data.Data.ContainsKey("posY"))
  216. data.Data.Remove("posY");
  217. if (data.Data.ContainsKey("sizeX"))
  218. data.Data.Remove("sizeX");
  219. if (data.Data.ContainsKey("sizeY"))
  220. data.Data.Remove("sizeY");
  221. if (data.Data.ContainsKey("locX"))
  222. data.Data.Remove("locX");
  223. if (data.Data.ContainsKey("locY"))
  224. data.Data.Remove("locY");
  225. string[] fields = new List<string>(data.Data.Keys).ToArray();
  226. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  227. using (NpgsqlCommand cmd = new NpgsqlCommand())
  228. {
  229. string update = "update " + m_Realm + " set \"locX\"=:posX, \"locY\"=:posY, \"sizeX\"=:sizeX, \"sizeY\"=:sizeY ";
  230. foreach (string field in fields)
  231. {
  232. update += ", ";
  233. update += " \"" + field + "\" = :" + field;
  234. if (m_FieldTypes.ContainsKey(field))
  235. cmd.Parameters.Add(m_database.CreateParameter(field, data.Data[field], m_FieldTypes[field]));
  236. else
  237. cmd.Parameters.Add(m_database.CreateParameter(field, data.Data[field]));
  238. }
  239. update += " where uuid = :regionID";
  240. if (data.ScopeID != UUID.Zero)
  241. update += " and \"ScopeID\" = :scopeID";
  242. cmd.CommandText = update;
  243. cmd.Connection = conn;
  244. cmd.Parameters.Add(m_database.CreateParameter("regionID", data.RegionID));
  245. cmd.Parameters.Add(m_database.CreateParameter("regionName", data.RegionName));
  246. cmd.Parameters.Add(m_database.CreateParameter("scopeID", data.ScopeID));
  247. cmd.Parameters.Add(m_database.CreateParameter("posX", data.posX));
  248. cmd.Parameters.Add(m_database.CreateParameter("posY", data.posY));
  249. cmd.Parameters.Add(m_database.CreateParameter("sizeX", data.sizeX));
  250. cmd.Parameters.Add(m_database.CreateParameter("sizeY", data.sizeY));
  251. conn.Open();
  252. try
  253. {
  254. if (cmd.ExecuteNonQuery() < 1)
  255. {
  256. string insert = "insert into " + m_Realm + " (uuid, \"ScopeID\", \"locX\", \"locY\", \"sizeX\", \"sizeY\", \"regionName\", \"" +
  257. String.Join("\", \"", fields) +
  258. "\") values (:regionID, :scopeID, :posX, :posY, :sizeX, :sizeY, :regionName, :" + String.Join(", :", fields) + ")";
  259. cmd.CommandText = insert;
  260. try
  261. {
  262. if (cmd.ExecuteNonQuery() < 1)
  263. {
  264. return false;
  265. }
  266. }
  267. catch (Exception ex)
  268. {
  269. m_log.Warn("[PGSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert);
  270. }
  271. }
  272. }
  273. catch (Exception ex)
  274. {
  275. m_log.Warn("[PGSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update);
  276. }
  277. }
  278. return true;
  279. }
  280. public bool SetDataItem(UUID regionID, string item, string value)
  281. {
  282. string sql = "update " + m_Realm +
  283. " set \"" + item + "\" = :" + item + " where uuid = :UUID";
  284. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  285. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  286. {
  287. cmd.Parameters.Add(m_database.CreateParameter("" + item, value));
  288. cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID));
  289. conn.Open();
  290. if (cmd.ExecuteNonQuery() > 0)
  291. return true;
  292. }
  293. return false;
  294. }
  295. public bool Delete(UUID regionID)
  296. {
  297. string sql = "delete from " + m_Realm +
  298. " where uuid = :UUID";
  299. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  300. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  301. {
  302. cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID));
  303. conn.Open();
  304. if (cmd.ExecuteNonQuery() > 0)
  305. return true;
  306. }
  307. return false;
  308. }
  309. public List<RegionData> GetDefaultRegions(UUID scopeID)
  310. {
  311. return Get((int)RegionFlags.DefaultRegion, scopeID);
  312. }
  313. public List<RegionData> GetDefaultHypergridRegions(UUID scopeID)
  314. {
  315. return Get((int)RegionFlags.DefaultHGRegion, scopeID);
  316. }
  317. public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
  318. {
  319. List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
  320. RegionDataDistanceCompare distanceComparer = new RegionDataDistanceCompare(x, y);
  321. regions.Sort(distanceComparer);
  322. return regions;
  323. }
  324. public List<RegionData> GetHyperlinks(UUID scopeID)
  325. {
  326. return Get((int)RegionFlags.Hyperlink, scopeID);
  327. }
  328. private List<RegionData> Get(int regionFlags, UUID scopeID)
  329. {
  330. string sql = "SELECT * FROM " + m_Realm + " WHERE (\"flags\" & " + regionFlags.ToString() + ") <> 0";
  331. if (scopeID != UUID.Zero)
  332. sql += " AND \"ScopeID\" = :scopeID";
  333. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  334. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  335. {
  336. cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
  337. conn.Open();
  338. return RunCommand(cmd);
  339. }
  340. }
  341. }
  342. }