1
0

PGSQLRegionData.cs 17 KB

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