diff options
author | Melanie Thielker | 2014-06-21 00:39:55 +0200 |
---|---|---|
committer | Melanie Thielker | 2014-06-21 00:39:55 +0200 |
commit | 159fcbf150b7da0e229b29aa7b94793484543d12 (patch) | |
tree | b8c0ff3b4c758a3fba8315b556c923ef4c02a185 /OpenSim/Data/PGSQL/PGSQLRegionData.cs | |
parent | Merge commit '68c8633ba18f0a11cfc0ed04d1d0c7c59e6cec76' (diff) | |
parent | Merge branch 'master' into careminster (diff) | |
download | opensim-SC_OLD-159fcbf150b7da0e229b29aa7b94793484543d12.zip opensim-SC_OLD-159fcbf150b7da0e229b29aa7b94793484543d12.tar.gz opensim-SC_OLD-159fcbf150b7da0e229b29aa7b94793484543d12.tar.bz2 opensim-SC_OLD-159fcbf150b7da0e229b29aa7b94793484543d12.tar.xz |
Merge branch 'master' of ssh://3dhosting.de/var/git/careminster
Conflicts:
OpenSim/Region/Framework/Scenes/SceneObjectGroup.cs
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLRegionData.cs')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLRegionData.cs | 392 |
1 files changed, 392 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs new file mode 100644 index 0000000..b3076f0 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs | |||
@@ -0,0 +1,392 @@ | |||
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 | } | ||