opensim – Blame information for rev 1

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