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