clockwerk-opensim – Blame information for rev 1

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