diff options
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLRegionData.cs (renamed from OpenSim/Data/MSSQL/MSSQLRegionData.cs) | 193 |
1 files changed, 119 insertions, 74 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs index 0d89706..b3076f0 100644 --- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs +++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs | |||
@@ -28,7 +28,6 @@ | |||
28 | using System; | 28 | using System; |
29 | using System.Collections.Generic; | 29 | using System.Collections.Generic; |
30 | using System.Data; | 30 | using System.Data; |
31 | using System.Data.SqlClient; | ||
32 | using System.Drawing; | 31 | using System.Drawing; |
33 | using System.IO; | 32 | using System.IO; |
34 | using System.Reflection; | 33 | using System.Reflection; |
@@ -38,45 +37,80 @@ using OpenSim.Framework; | |||
38 | using OpenSim.Region.Framework.Interfaces; | 37 | using OpenSim.Region.Framework.Interfaces; |
39 | using OpenSim.Region.Framework.Scenes; | 38 | using OpenSim.Region.Framework.Scenes; |
40 | using RegionFlags = OpenSim.Framework.RegionFlags; | 39 | using RegionFlags = OpenSim.Framework.RegionFlags; |
40 | using Npgsql; | ||
41 | 41 | ||
42 | namespace OpenSim.Data.MSSQL | 42 | namespace OpenSim.Data.PGSQL |
43 | { | 43 | { |
44 | /// <summary> | 44 | /// <summary> |
45 | /// A MSSQL Interface for the Region Server. | 45 | /// A PGSQL Interface for the Region Server. |
46 | /// </summary> | 46 | /// </summary> |
47 | public class MSSQLRegionData : IRegionData | 47 | public class PGSQLRegionData : IRegionData |
48 | { | 48 | { |
49 | private string m_Realm; | 49 | private string m_Realm; |
50 | private List<string> m_ColumnNames = null; | 50 | private List<string> m_ColumnNames = null; |
51 | private string m_ConnectionString; | 51 | private string m_ConnectionString; |
52 | private MSSQLManager m_database; | 52 | private PGSQLManager m_database; |
53 | private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); | 53 | private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); |
54 | 54 | ||
55 | public MSSQLRegionData(string connectionString, string realm) | 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) | ||
56 | { | 63 | { |
57 | m_Realm = realm; | 64 | m_Realm = realm; |
58 | m_ConnectionString = connectionString; | 65 | m_ConnectionString = connectionString; |
59 | m_database = new MSSQLManager(connectionString); | 66 | m_database = new PGSQLManager(connectionString); |
60 | 67 | ||
61 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 68 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
62 | { | 69 | { |
63 | conn.Open(); | 70 | conn.Open(); |
64 | Migration m = new Migration(conn, GetType().Assembly, "GridStore"); | 71 | Migration m = new Migration(conn, GetType().Assembly, "GridStore"); |
65 | m.Update(); | 72 | m.Update(); |
66 | } | 73 | } |
74 | LoadFieldTypes(); | ||
67 | } | 75 | } |
68 | 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 | |||
69 | public List<RegionData> Get(string regionName, UUID scopeID) | 101 | public List<RegionData> Get(string regionName, UUID scopeID) |
70 | { | 102 | { |
71 | string sql = "select * from ["+m_Realm+"] where regionName like @regionName"; | 103 | string sql = "select * from "+m_Realm+" where lower(\"regionName\") like lower(:regionName) "; |
72 | if (scopeID != UUID.Zero) | 104 | if (scopeID != UUID.Zero) |
73 | sql += " and ScopeID = @scopeID"; | 105 | sql += " and \"ScopeID\" = :scopeID"; |
74 | sql += " order by regionName"; | 106 | sql += " order by lower(\"regionName\")"; |
75 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 107 | |
76 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 108 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
109 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
77 | { | 110 | { |
78 | cmd.Parameters.Add(m_database.CreateParameter("@regionName", regionName)); | 111 | cmd.Parameters.Add(m_database.CreateParameter("regionName", regionName)); |
79 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); | 112 | if (scopeID != UUID.Zero) |
113 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); | ||
80 | conn.Open(); | 114 | conn.Open(); |
81 | return RunCommand(cmd); | 115 | return RunCommand(cmd); |
82 | } | 116 | } |
@@ -84,16 +118,17 @@ namespace OpenSim.Data.MSSQL | |||
84 | 118 | ||
85 | public RegionData Get(int posX, int posY, UUID scopeID) | 119 | public RegionData Get(int posX, int posY, UUID scopeID) |
86 | { | 120 | { |
87 | string sql = "select * from ["+m_Realm+"] where locX = @posX and locY = @posY"; | 121 | string sql = "select * from "+m_Realm+" where \"locX\" = :posX and \"locY\" = :posY"; |
88 | if (scopeID != UUID.Zero) | 122 | if (scopeID != UUID.Zero) |
89 | sql += " and ScopeID = @scopeID"; | 123 | sql += " and \"ScopeID\" = :scopeID"; |
90 | 124 | ||
91 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 125 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
92 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 126 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) |
93 | { | 127 | { |
94 | cmd.Parameters.Add(m_database.CreateParameter("@posX", posX.ToString())); | 128 | cmd.Parameters.Add(m_database.CreateParameter("posX", posX)); |
95 | cmd.Parameters.Add(m_database.CreateParameter("@posY", posY.ToString())); | 129 | cmd.Parameters.Add(m_database.CreateParameter("posY", posY)); |
96 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); | 130 | if (scopeID != UUID.Zero) |
131 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); | ||
97 | conn.Open(); | 132 | conn.Open(); |
98 | List<RegionData> ret = RunCommand(cmd); | 133 | List<RegionData> ret = RunCommand(cmd); |
99 | if (ret.Count == 0) | 134 | if (ret.Count == 0) |
@@ -105,14 +140,15 @@ namespace OpenSim.Data.MSSQL | |||
105 | 140 | ||
106 | public RegionData Get(UUID regionID, UUID scopeID) | 141 | public RegionData Get(UUID regionID, UUID scopeID) |
107 | { | 142 | { |
108 | string sql = "select * from ["+m_Realm+"] where uuid = @regionID"; | 143 | string sql = "select * from "+m_Realm+" where uuid = :regionID"; |
109 | if (scopeID != UUID.Zero) | 144 | if (scopeID != UUID.Zero) |
110 | sql += " and ScopeID = @scopeID"; | 145 | sql += " and \"ScopeID\" = :scopeID"; |
111 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 146 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
112 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 147 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) |
113 | { | 148 | { |
114 | cmd.Parameters.Add(m_database.CreateParameter("@regionID", regionID)); | 149 | cmd.Parameters.Add(m_database.CreateParameter("regionID", regionID)); |
115 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); | 150 | if (scopeID != UUID.Zero) |
151 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); | ||
116 | conn.Open(); | 152 | conn.Open(); |
117 | List<RegionData> ret = RunCommand(cmd); | 153 | List<RegionData> ret = RunCommand(cmd); |
118 | if (ret.Count == 0) | 154 | if (ret.Count == 0) |
@@ -124,28 +160,28 @@ namespace OpenSim.Data.MSSQL | |||
124 | 160 | ||
125 | public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID) | 161 | public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID) |
126 | { | 162 | { |
127 | string sql = "select * from ["+m_Realm+"] where locX between @startX and @endX and locY between @startY and @endY"; | 163 | string sql = "select * from "+m_Realm+" where \"locX\" between :startX and :endX and \"locY\" between :startY and :endY"; |
128 | if (scopeID != UUID.Zero) | 164 | if (scopeID != UUID.Zero) |
129 | sql += " and ScopeID = @scopeID"; | 165 | sql += " and \"ScopeID\" = :scopeID"; |
130 | 166 | ||
131 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 167 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
132 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 168 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) |
133 | { | 169 | { |
134 | cmd.Parameters.Add(m_database.CreateParameter("@startX", startX)); | 170 | cmd.Parameters.Add(m_database.CreateParameter("startX", startX)); |
135 | cmd.Parameters.Add(m_database.CreateParameter("@startY", startY)); | 171 | cmd.Parameters.Add(m_database.CreateParameter("startY", startY)); |
136 | cmd.Parameters.Add(m_database.CreateParameter("@endX", endX)); | 172 | cmd.Parameters.Add(m_database.CreateParameter("endX", endX)); |
137 | cmd.Parameters.Add(m_database.CreateParameter("@endY", endY)); | 173 | cmd.Parameters.Add(m_database.CreateParameter("endY", endY)); |
138 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); | 174 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); |
139 | conn.Open(); | 175 | conn.Open(); |
140 | return RunCommand(cmd); | 176 | return RunCommand(cmd); |
141 | } | 177 | } |
142 | } | 178 | } |
143 | 179 | ||
144 | public List<RegionData> RunCommand(SqlCommand cmd) | 180 | public List<RegionData> RunCommand(NpgsqlCommand cmd) |
145 | { | 181 | { |
146 | List<RegionData> retList = new List<RegionData>(); | 182 | List<RegionData> retList = new List<RegionData>(); |
147 | 183 | ||
148 | SqlDataReader result = cmd.ExecuteReader(); | 184 | NpgsqlDataReader result = cmd.ExecuteReader(); |
149 | 185 | ||
150 | while (result.Read()) | 186 | while (result.Read()) |
151 | { | 187 | { |
@@ -217,43 +253,46 @@ namespace OpenSim.Data.MSSQL | |||
217 | 253 | ||
218 | string[] fields = new List<string>(data.Data.Keys).ToArray(); | 254 | string[] fields = new List<string>(data.Data.Keys).ToArray(); |
219 | 255 | ||
220 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 256 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
221 | using (SqlCommand cmd = new SqlCommand()) | 257 | using (NpgsqlCommand cmd = new NpgsqlCommand()) |
222 | { | 258 | { |
223 | 259 | ||
224 | string update = "update [" + m_Realm + "] set locX=@posX, locY=@posY, sizeX=@sizeX, sizeY=@sizeY "; | 260 | string update = "update " + m_Realm + " set \"locX\"=:posX, \"locY\"=:posY, \"sizeX\"=:sizeX, \"sizeY\"=:sizeY "; |
225 | 261 | ||
226 | foreach (string field in fields) | 262 | foreach (string field in fields) |
227 | { | 263 | { |
228 | 264 | ||
229 | update += ", "; | 265 | update += ", "; |
230 | update += "[" + field + "] = @" + field; | 266 | update += " \"" + field + "\" = :" + field; |
231 | 267 | ||
232 | cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field])); | 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])); | ||
233 | } | 272 | } |
234 | 273 | ||
235 | update += " where uuid = @regionID"; | 274 | update += " where uuid = :regionID"; |
236 | 275 | ||
237 | if (data.ScopeID != UUID.Zero) | 276 | if (data.ScopeID != UUID.Zero) |
238 | update += " and ScopeID = @scopeID"; | 277 | update += " and \"ScopeID\" = :scopeID"; |
239 | 278 | ||
240 | cmd.CommandText = update; | 279 | cmd.CommandText = update; |
241 | cmd.Connection = conn; | 280 | cmd.Connection = conn; |
242 | cmd.Parameters.Add(m_database.CreateParameter("@regionID", data.RegionID)); | 281 | cmd.Parameters.Add(m_database.CreateParameter("regionID", data.RegionID)); |
243 | cmd.Parameters.Add(m_database.CreateParameter("@regionName", data.RegionName)); | 282 | cmd.Parameters.Add(m_database.CreateParameter("regionName", data.RegionName)); |
244 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID)); | 283 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", data.ScopeID)); |
245 | cmd.Parameters.Add(m_database.CreateParameter("@posX", data.posX)); | 284 | cmd.Parameters.Add(m_database.CreateParameter("posX", data.posX)); |
246 | cmd.Parameters.Add(m_database.CreateParameter("@posY", data.posY)); | 285 | cmd.Parameters.Add(m_database.CreateParameter("posY", data.posY)); |
247 | cmd.Parameters.Add(m_database.CreateParameter("@sizeX", data.sizeX)); | 286 | cmd.Parameters.Add(m_database.CreateParameter("sizeX", data.sizeX)); |
248 | cmd.Parameters.Add(m_database.CreateParameter("@sizeY", data.sizeY)); | 287 | cmd.Parameters.Add(m_database.CreateParameter("sizeY", data.sizeY)); |
249 | conn.Open(); | 288 | conn.Open(); |
250 | try | 289 | try |
251 | { | 290 | { |
252 | if (cmd.ExecuteNonQuery() < 1) | 291 | if (cmd.ExecuteNonQuery() < 1) |
253 | { | 292 | { |
254 | string insert = "insert into [" + m_Realm + "] ([uuid], [ScopeID], [locX], [locY], [sizeX], [sizeY], [regionName], [" + | 293 | string insert = "insert into " + m_Realm + " (uuid, \"ScopeID\", \"locX\", \"locY\", \"sizeX\", \"sizeY\", \"regionName\", \"" + |
255 | String.Join("], [", fields) + | 294 | String.Join("\", \"", fields) + |
256 | "]) values (@regionID, @scopeID, @posX, @posY, @sizeX, @sizeY, @regionName, @" + String.Join(", @", fields) + ")"; | 295 | "\") values (:regionID, :scopeID, :posX, :posY, :sizeX, :sizeY, :regionName, :" + String.Join(", :", fields) + ")"; |
257 | 296 | ||
258 | cmd.CommandText = insert; | 297 | cmd.CommandText = insert; |
259 | 298 | ||
@@ -266,13 +305,13 @@ namespace OpenSim.Data.MSSQL | |||
266 | } | 305 | } |
267 | catch (Exception ex) | 306 | catch (Exception ex) |
268 | { | 307 | { |
269 | m_log.Warn("[MSSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert); | 308 | m_log.Warn("[PGSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert); |
270 | } | 309 | } |
271 | } | 310 | } |
272 | } | 311 | } |
273 | catch (Exception ex) | 312 | catch (Exception ex) |
274 | { | 313 | { |
275 | m_log.Warn("[MSSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update); | 314 | m_log.Warn("[PGSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update); |
276 | } | 315 | } |
277 | } | 316 | } |
278 | 317 | ||
@@ -281,13 +320,14 @@ namespace OpenSim.Data.MSSQL | |||
281 | 320 | ||
282 | public bool SetDataItem(UUID regionID, string item, string value) | 321 | public bool SetDataItem(UUID regionID, string item, string value) |
283 | { | 322 | { |
284 | string sql = "update [" + m_Realm + | 323 | string sql = "update " + m_Realm + |
285 | "] set [" + item + "] = @" + item + " where uuid = @UUID"; | 324 | " set \"" + item + "\" = :" + item + " where uuid = :UUID"; |
286 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 325 | |
287 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 326 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
327 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
288 | { | 328 | { |
289 | cmd.Parameters.Add(m_database.CreateParameter("@" + item, value)); | 329 | cmd.Parameters.Add(m_database.CreateParameter("" + item, value)); |
290 | cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID)); | 330 | cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID)); |
291 | conn.Open(); | 331 | conn.Open(); |
292 | if (cmd.ExecuteNonQuery() > 0) | 332 | if (cmd.ExecuteNonQuery() > 0) |
293 | return true; | 333 | return true; |
@@ -297,12 +337,12 @@ namespace OpenSim.Data.MSSQL | |||
297 | 337 | ||
298 | public bool Delete(UUID regionID) | 338 | public bool Delete(UUID regionID) |
299 | { | 339 | { |
300 | string sql = "delete from [" + m_Realm + | 340 | string sql = "delete from " + m_Realm + |
301 | "] where uuid = @UUID"; | 341 | " where uuid = :UUID"; |
302 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 342 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
303 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 343 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) |
304 | { | 344 | { |
305 | cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID)); | 345 | cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID)); |
306 | conn.Open(); | 346 | conn.Open(); |
307 | if (cmd.ExecuteNonQuery() > 0) | 347 | if (cmd.ExecuteNonQuery() > 0) |
308 | return true; | 348 | return true; |
@@ -315,6 +355,11 @@ namespace OpenSim.Data.MSSQL | |||
315 | return Get((int)RegionFlags.DefaultRegion, scopeID); | 355 | return Get((int)RegionFlags.DefaultRegion, scopeID); |
316 | } | 356 | } |
317 | 357 | ||
358 | public List<RegionData> GetDefaultHypergridRegions(UUID scopeID) | ||
359 | { | ||
360 | return Get((int)RegionFlags.DefaultHGRegion, scopeID); | ||
361 | } | ||
362 | |||
318 | public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y) | 363 | public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y) |
319 | { | 364 | { |
320 | List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID); | 365 | List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID); |
@@ -331,14 +376,14 @@ namespace OpenSim.Data.MSSQL | |||
331 | 376 | ||
332 | private List<RegionData> Get(int regionFlags, UUID scopeID) | 377 | private List<RegionData> Get(int regionFlags, UUID scopeID) |
333 | { | 378 | { |
334 | string sql = "SELECT * FROM [" + m_Realm + "] WHERE (flags & " + regionFlags.ToString() + ") <> 0"; | 379 | string sql = "SELECT * FROM " + m_Realm + " WHERE (\"flags\" & " + regionFlags.ToString() + ") <> 0"; |
335 | if (scopeID != UUID.Zero) | 380 | if (scopeID != UUID.Zero) |
336 | sql += " AND ScopeID = @scopeID"; | 381 | sql += " AND \"ScopeID\" = :scopeID"; |
337 | 382 | ||
338 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | 383 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) |
339 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | 384 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) |
340 | { | 385 | { |
341 | cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); | 386 | cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); |
342 | conn.Open(); | 387 | conn.Open(); |
343 | return RunCommand(cmd); | 388 | return RunCommand(cmd); |
344 | } | 389 | } |