MSSQLRegionData.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311
  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.Data.SqlClient;
  31. using System.Drawing;
  32. using System.IO;
  33. using System.Reflection;
  34. using log4net;
  35. using OpenMetaverse;
  36. using OpenSim.Framework;
  37. using OpenSim.Region.Framework.Interfaces;
  38. using OpenSim.Region.Framework.Scenes;
  39. namespace OpenSim.Data.MSSQL
  40. {
  41. /// <summary>
  42. /// A MSSQL Interface for the Region Server.
  43. /// </summary>
  44. public class MSSQLRegionData : IRegionData
  45. {
  46. private string m_Realm;
  47. private List<string> m_ColumnNames = null;
  48. private string m_ConnectionString;
  49. private MSSQLManager m_database;
  50. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  51. public MSSQLRegionData(string connectionString, string realm)
  52. {
  53. m_Realm = realm;
  54. m_ConnectionString = connectionString;
  55. m_database = new MSSQLManager(connectionString);
  56. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  57. {
  58. conn.Open();
  59. Migration m = new Migration(conn, GetType().Assembly, "GridStore");
  60. m.Update();
  61. }
  62. }
  63. public List<RegionData> Get(string regionName, UUID scopeID)
  64. {
  65. string sql = "select * from ["+m_Realm+"] where regionName like @regionName";
  66. if (scopeID != UUID.Zero)
  67. sql += " and ScopeID = @scopeID";
  68. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  69. using (SqlCommand cmd = new SqlCommand(sql, conn))
  70. {
  71. cmd.Parameters.Add(m_database.CreateParameter("@regionName", regionName));
  72. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  73. conn.Open();
  74. return RunCommand(cmd);
  75. }
  76. }
  77. public RegionData Get(int posX, int posY, UUID scopeID)
  78. {
  79. string sql = "select * from ["+m_Realm+"] where locX = @posX and locY = @posY";
  80. if (scopeID != UUID.Zero)
  81. sql += " and ScopeID = @scopeID";
  82. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  83. using (SqlCommand cmd = new SqlCommand(sql, conn))
  84. {
  85. cmd.Parameters.Add(m_database.CreateParameter("@posX", posX.ToString()));
  86. cmd.Parameters.Add(m_database.CreateParameter("@posY", posY.ToString()));
  87. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  88. conn.Open();
  89. List<RegionData> ret = RunCommand(cmd);
  90. if (ret.Count == 0)
  91. return null;
  92. return ret[0];
  93. }
  94. }
  95. public RegionData Get(UUID regionID, UUID scopeID)
  96. {
  97. string sql = "select * from ["+m_Realm+"] where uuid = @regionID";
  98. if (scopeID != UUID.Zero)
  99. sql += " and ScopeID = @scopeID";
  100. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  101. using (SqlCommand cmd = new SqlCommand(sql, conn))
  102. {
  103. cmd.Parameters.Add(m_database.CreateParameter("@regionID", regionID));
  104. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  105. conn.Open();
  106. List<RegionData> ret = RunCommand(cmd);
  107. if (ret.Count == 0)
  108. return null;
  109. return ret[0];
  110. }
  111. }
  112. public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
  113. {
  114. string sql = "select * from ["+m_Realm+"] where locX between @startX and @endX and locY between @startY and @endY";
  115. if (scopeID != UUID.Zero)
  116. sql += " and ScopeID = @scopeID";
  117. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  118. using (SqlCommand cmd = new SqlCommand(sql, conn))
  119. {
  120. cmd.Parameters.Add(m_database.CreateParameter("@startX", startX.ToString()));
  121. cmd.Parameters.Add(m_database.CreateParameter("@startY", startY.ToString()));
  122. cmd.Parameters.Add(m_database.CreateParameter("@endX", endX.ToString()));
  123. cmd.Parameters.Add(m_database.CreateParameter("@endY", endY.ToString()));
  124. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  125. conn.Open();
  126. return RunCommand(cmd);
  127. }
  128. }
  129. public List<RegionData> RunCommand(SqlCommand cmd)
  130. {
  131. List<RegionData> retList = new List<RegionData>();
  132. SqlDataReader result = cmd.ExecuteReader();
  133. while (result.Read())
  134. {
  135. RegionData ret = new RegionData();
  136. ret.Data = new Dictionary<string, object>();
  137. UUID regionID;
  138. UUID.TryParse(result["uuid"].ToString(), out regionID);
  139. ret.RegionID = regionID;
  140. UUID scope;
  141. UUID.TryParse(result["ScopeID"].ToString(), out scope);
  142. ret.ScopeID = scope;
  143. ret.RegionName = result["regionName"].ToString();
  144. ret.posX = Convert.ToInt32(result["locX"]);
  145. ret.posY = Convert.ToInt32(result["locY"]);
  146. ret.sizeX = Convert.ToInt32(result["sizeX"]);
  147. ret.sizeY = Convert.ToInt32(result["sizeY"]);
  148. if (m_ColumnNames == null)
  149. {
  150. m_ColumnNames = new List<string>();
  151. DataTable schemaTable = result.GetSchemaTable();
  152. foreach (DataRow row in schemaTable.Rows)
  153. m_ColumnNames.Add(row["ColumnName"].ToString());
  154. }
  155. foreach (string s in m_ColumnNames)
  156. {
  157. if (s == "uuid")
  158. continue;
  159. if (s == "ScopeID")
  160. continue;
  161. if (s == "regionName")
  162. continue;
  163. if (s == "locX")
  164. continue;
  165. if (s == "locY")
  166. continue;
  167. ret.Data[s] = result[s].ToString();
  168. }
  169. retList.Add(ret);
  170. }
  171. return retList;
  172. }
  173. public bool Store(RegionData data)
  174. {
  175. if (data.Data.ContainsKey("uuid"))
  176. data.Data.Remove("uuid");
  177. if (data.Data.ContainsKey("ScopeID"))
  178. data.Data.Remove("ScopeID");
  179. if (data.Data.ContainsKey("regionName"))
  180. data.Data.Remove("regionName");
  181. if (data.Data.ContainsKey("posX"))
  182. data.Data.Remove("posX");
  183. if (data.Data.ContainsKey("posY"))
  184. data.Data.Remove("posY");
  185. if (data.Data.ContainsKey("sizeX"))
  186. data.Data.Remove("sizeX");
  187. if (data.Data.ContainsKey("sizeY"))
  188. data.Data.Remove("sizeY");
  189. if (data.Data.ContainsKey("locX"))
  190. data.Data.Remove("locX");
  191. if (data.Data.ContainsKey("locY"))
  192. data.Data.Remove("locY");
  193. string[] fields = new List<string>(data.Data.Keys).ToArray();
  194. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  195. using (SqlCommand cmd = new SqlCommand())
  196. {
  197. string update = "update [" + m_Realm + "] set locX=@posX, locY=@posY, sizeX=@sizeX, sizeY=@sizeY ";
  198. foreach (string field in fields)
  199. {
  200. update += ", ";
  201. update += "[" + field + "] = @" + field;
  202. cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
  203. }
  204. update += " where uuid = @regionID";
  205. if (data.ScopeID != UUID.Zero)
  206. update += " and ScopeID = @scopeID";
  207. cmd.CommandText = update;
  208. cmd.Connection = conn;
  209. cmd.Parameters.Add(m_database.CreateParameter("@regionID", data.RegionID));
  210. cmd.Parameters.Add(m_database.CreateParameter("@regionName", data.RegionName));
  211. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
  212. cmd.Parameters.Add(m_database.CreateParameter("@posX", data.posX));
  213. cmd.Parameters.Add(m_database.CreateParameter("@posY", data.posY));
  214. cmd.Parameters.Add(m_database.CreateParameter("@sizeX", data.sizeX));
  215. cmd.Parameters.Add(m_database.CreateParameter("@sizeY", data.sizeY));
  216. conn.Open();
  217. try
  218. {
  219. if (cmd.ExecuteNonQuery() < 1)
  220. {
  221. string insert = "insert into [" + m_Realm + "] ([uuid], [ScopeID], [locX], [locY], [sizeX], [sizeY], [regionName], [" +
  222. String.Join("], [", fields) +
  223. "]) values (@regionID, @scopeID, @posX, @posY, @sizeX, @sizeY, @regionName, @" + String.Join(", @", fields) + ")";
  224. cmd.CommandText = insert;
  225. try
  226. {
  227. if (cmd.ExecuteNonQuery() < 1)
  228. {
  229. return false;
  230. }
  231. }
  232. catch (Exception ex)
  233. {
  234. m_log.Warn("[MSSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert);
  235. }
  236. }
  237. }
  238. catch (Exception ex)
  239. {
  240. m_log.Warn("[MSSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update);
  241. }
  242. }
  243. return true;
  244. }
  245. public bool SetDataItem(UUID regionID, string item, string value)
  246. {
  247. string sql = "update [" + m_Realm +
  248. "] set [" + item + "] = @" + item + " where uuid = @UUID";
  249. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  250. using (SqlCommand cmd = new SqlCommand(sql, conn))
  251. {
  252. cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
  253. cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID));
  254. conn.Open();
  255. if (cmd.ExecuteNonQuery() > 0)
  256. return true;
  257. }
  258. return false;
  259. }
  260. public bool Delete(UUID regionID)
  261. {
  262. string sql = "delete from [" + m_Realm +
  263. "] where uuid = @UUID";
  264. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  265. using (SqlCommand cmd = new SqlCommand(sql, conn))
  266. {
  267. cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID));
  268. conn.Open();
  269. if (cmd.ExecuteNonQuery() > 0)
  270. return true;
  271. }
  272. return false;
  273. }
  274. }
  275. }