aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/PGSQLEstateData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLEstateData.cs')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLEstateData.cs598
1 files changed, 598 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
new file mode 100644
index 0000000..347baf3
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
@@ -0,0 +1,598 @@
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.Generic;
30using System.Reflection;
31using log4net;
32using OpenMetaverse;
33using OpenSim.Framework;
34using OpenSim.Region.Framework.Interfaces;
35using System.Data;
36using Npgsql;
37using NpgsqlTypes;
38
39namespace OpenSim.Data.PGSQL
40{
41 public class PGSQLEstateStore : IEstateDataStore
42 {
43 private const string _migrationStore = "EstateStore";
44
45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 private PGSQLManager _Database;
48 private string m_connectionString;
49 private FieldInfo[] _Fields;
50 private Dictionary<string, FieldInfo> _FieldMap = new Dictionary<string, FieldInfo>();
51
52 #region Public methods
53
54 public PGSQLEstateStore()
55 {
56 }
57
58 public PGSQLEstateStore(string connectionString)
59 {
60 Initialise(connectionString);
61 }
62
63 protected virtual Assembly Assembly
64 {
65 get { return GetType().Assembly; }
66 }
67
68 /// <summary>
69 /// Initialises the estatedata class.
70 /// </summary>
71 /// <param name="connectionString">connectionString.</param>
72 public void Initialise(string connectionString)
73 {
74 if (!string.IsNullOrEmpty(connectionString))
75 {
76 m_connectionString = connectionString;
77 _Database = new PGSQLManager(connectionString);
78 }
79
80 //Migration settings
81 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
82 {
83 conn.Open();
84 Migration m = new Migration(conn, GetType().Assembly, "EstateStore");
85 m.Update();
86 }
87
88 //Interesting way to get parameters! Maybe implement that also with other types
89 Type t = typeof(EstateSettings);
90 _Fields = t.GetFields(BindingFlags.NonPublic |
91 BindingFlags.Instance |
92 BindingFlags.DeclaredOnly);
93
94 foreach (FieldInfo f in _Fields)
95 {
96 if (f.Name.Substring(0, 2) == "m_")
97 _FieldMap[f.Name.Substring(2)] = f;
98 }
99 }
100
101 /// <summary>
102 /// Loads the estate settings.
103 /// </summary>
104 /// <param name="regionID">region ID.</param>
105 /// <returns></returns>
106 public EstateSettings LoadEstateSettings(UUID regionID, bool create)
107 {
108 EstateSettings es = new EstateSettings();
109
110 string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) +
111 "\" from estate_map left join estate_settings on estate_map.\"EstateID\" = estate_settings.\"EstateID\" " +
112 " where estate_settings.\"EstateID\" is not null and \"RegionID\" = :RegionID";
113
114 bool insertEstate = false;
115 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
116 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
117 {
118 cmd.Parameters.Add(_Database.CreateParameter("RegionID", regionID));
119 conn.Open();
120 using (NpgsqlDataReader reader = cmd.ExecuteReader())
121 {
122 if (reader.Read())
123 {
124 foreach (string name in FieldList)
125 {
126 FieldInfo f = _FieldMap[name];
127 object v = reader[name];
128 if (f.FieldType == typeof(bool))
129 {
130 f.SetValue(es, v);
131 }
132 else if (f.FieldType == typeof(UUID))
133 {
134 UUID estUUID = UUID.Zero;
135
136 UUID.TryParse(v.ToString(), out estUUID);
137
138 f.SetValue(es, estUUID);
139 }
140 else if (f.FieldType == typeof(string))
141 {
142 f.SetValue(es, v.ToString());
143 }
144 else if (f.FieldType == typeof(UInt32))
145 {
146 f.SetValue(es, Convert.ToUInt32(v));
147 }
148 else if (f.FieldType == typeof(Single))
149 {
150 f.SetValue(es, Convert.ToSingle(v));
151 }
152 else
153 f.SetValue(es, v);
154 }
155 }
156 else
157 {
158 insertEstate = true;
159 }
160 }
161 }
162
163 if (insertEstate && create)
164 {
165 DoCreate(es);
166 LinkRegion(regionID, (int)es.EstateID);
167 }
168
169 LoadBanList(es);
170
171 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
172 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
173 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
174
175 //Set event
176 es.OnSave += StoreEstateSettings;
177 return es;
178 }
179
180 public EstateSettings CreateNewEstate()
181 {
182 EstateSettings es = new EstateSettings();
183 es.OnSave += StoreEstateSettings;
184
185 DoCreate(es);
186
187 LoadBanList(es);
188
189 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
190 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
191 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
192
193 return es;
194 }
195
196 private void DoCreate(EstateSettings es)
197 {
198 List<string> names = new List<string>(FieldList);
199
200 names.Remove("EstateID");
201
202 string sql = string.Format("insert into estate_settings (\"{0}\") values ( :{1} )", String.Join("\",\"", names.ToArray()), String.Join(", :", names.ToArray()));
203
204 m_log.Debug("[DB ESTATE]: SQL: " + sql);
205 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
206 using (NpgsqlCommand insertCommand = new NpgsqlCommand(sql, conn))
207 {
208 insertCommand.CommandText = sql + "; Select cast(lastval() as int) as ID ;";
209
210 foreach (string name in names)
211 {
212 insertCommand.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es)));
213 }
214 //NpgsqlParameter idParameter = new NpgsqlParameter("ID", SqlDbType.Int);
215 //idParameter.Direction = ParameterDirection.Output;
216 //insertCommand.Parameters.Add(idParameter);
217 conn.Open();
218
219 es.EstateID = 100;
220
221 using (NpgsqlDataReader result = insertCommand.ExecuteReader())
222 {
223 if (result.Read())
224 {
225 es.EstateID = (uint)result.GetInt32(0);
226 }
227 }
228
229 }
230
231 //TODO check if this is needed??
232 es.Save();
233 }
234
235 /// <summary>
236 /// Stores the estate settings.
237 /// </summary>
238 /// <param name="es">estate settings</param>
239 public void StoreEstateSettings(EstateSettings es)
240 {
241 List<string> names = new List<string>(FieldList);
242
243 names.Remove("EstateID");
244
245 string sql = string.Format("UPDATE estate_settings SET ");
246 foreach (string name in names)
247 {
248 sql += "\"" + name + "\" = :" + name + ", ";
249 }
250 sql = sql.Remove(sql.LastIndexOf(","));
251 sql += " WHERE \"EstateID\" = :EstateID";
252
253 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
254 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
255 {
256 foreach (string name in names)
257 {
258 cmd.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es)));
259 }
260
261 cmd.Parameters.Add(_Database.CreateParameter("EstateID", es.EstateID));
262 conn.Open();
263 cmd.ExecuteNonQuery();
264 }
265
266 SaveBanList(es);
267 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
268 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
269 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
270 }
271
272 #endregion
273
274 #region Private methods
275
276 private string[] FieldList
277 {
278 get { return new List<string>(_FieldMap.Keys).ToArray(); }
279 }
280
281 private void LoadBanList(EstateSettings es)
282 {
283 es.ClearBans();
284
285 string sql = "select \"bannedUUID\" from estateban where \"EstateID\" = :EstateID";
286
287 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
288 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
289 {
290 NpgsqlParameter idParameter = new NpgsqlParameter("EstateID", DbType.Int32);
291 idParameter.Value = es.EstateID;
292 cmd.Parameters.Add(idParameter);
293 conn.Open();
294 using (NpgsqlDataReader reader = cmd.ExecuteReader())
295 {
296 while (reader.Read())
297 {
298 EstateBan eb = new EstateBan();
299
300 eb.BannedUserID = new UUID((Guid)reader["bannedUUID"]); //uuid;
301 eb.BannedHostAddress = "0.0.0.0";
302 eb.BannedHostIPMask = "0.0.0.0";
303 es.AddBan(eb);
304 }
305 }
306 }
307 }
308
309 private UUID[] LoadUUIDList(uint estateID, string table)
310 {
311 List<UUID> uuids = new List<UUID>();
312
313 string sql = string.Format("select uuid from {0} where \"EstateID\" = :EstateID", table);
314
315 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
316 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
317 {
318 cmd.Parameters.Add(_Database.CreateParameter("EstateID", estateID));
319 conn.Open();
320 using (NpgsqlDataReader reader = cmd.ExecuteReader())
321 {
322 while (reader.Read())
323 {
324 uuids.Add(new UUID((Guid)reader["uuid"])); //uuid);
325 }
326 }
327 }
328
329 return uuids.ToArray();
330 }
331
332 private void SaveBanList(EstateSettings es)
333 {
334 //Delete first
335 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
336 {
337 conn.Open();
338 using (NpgsqlCommand cmd = conn.CreateCommand())
339 {
340 cmd.CommandText = "delete from estateban where \"EstateID\" = :EstateID";
341 cmd.Parameters.AddWithValue("EstateID", (int)es.EstateID);
342 cmd.ExecuteNonQuery();
343
344 //Insert after
345 cmd.CommandText = "insert into estateban (\"EstateID\", \"bannedUUID\",\"bannedIp\", \"bannedIpHostMask\", \"bannedNameMask\") values ( :EstateID, :bannedUUID, '','','' )";
346 cmd.Parameters.AddWithValue("bannedUUID", Guid.Empty);
347 foreach (EstateBan b in es.EstateBans)
348 {
349 cmd.Parameters["bannedUUID"].Value = b.BannedUserID.Guid;
350 cmd.ExecuteNonQuery();
351 }
352 }
353 }
354 }
355
356 private void SaveUUIDList(uint estateID, string table, UUID[] data)
357 {
358 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
359 {
360 conn.Open();
361 using (NpgsqlCommand cmd = conn.CreateCommand())
362 {
363 cmd.Parameters.AddWithValue("EstateID", (int)estateID);
364 cmd.CommandText = string.Format("delete from {0} where \"EstateID\" = :EstateID", table);
365 cmd.ExecuteNonQuery();
366
367 cmd.CommandText = string.Format("insert into {0} (\"EstateID\", uuid) values ( :EstateID, :uuid )", table);
368 cmd.Parameters.AddWithValue("uuid", Guid.Empty);
369 foreach (UUID uuid in data)
370 {
371 cmd.Parameters["uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
372 cmd.ExecuteNonQuery();
373 }
374 }
375 }
376 }
377
378 public EstateSettings LoadEstateSettings(int estateID)
379 {
380 EstateSettings es = new EstateSettings();
381 string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) + "\" from estate_settings where \"EstateID\" = :EstateID";
382 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
383 {
384 conn.Open();
385 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
386 {
387 cmd.Parameters.AddWithValue("EstateID", (int)estateID);
388 using (NpgsqlDataReader reader = cmd.ExecuteReader())
389 {
390 if (reader.Read())
391 {
392 foreach (string name in FieldList)
393 {
394 FieldInfo f = _FieldMap[name];
395 object v = reader[name];
396 if (f.FieldType == typeof(bool))
397 {
398 f.SetValue(es, Convert.ToInt32(v) != 0);
399 }
400 else if (f.FieldType == typeof(UUID))
401 {
402 f.SetValue(es, new UUID((Guid)v)); // uuid);
403 }
404 else if (f.FieldType == typeof(string))
405 {
406 f.SetValue(es, v.ToString());
407 }
408 else if (f.FieldType == typeof(UInt32))
409 {
410 f.SetValue(es, Convert.ToUInt32(v));
411 }
412 else if (f.FieldType == typeof(Single))
413 {
414 f.SetValue(es, Convert.ToSingle(v));
415 }
416 else
417 f.SetValue(es, v);
418 }
419 }
420
421 }
422 }
423 }
424 LoadBanList(es);
425
426 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
427 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
428 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
429
430 //Set event
431 es.OnSave += StoreEstateSettings;
432 return es;
433
434 }
435
436 public List<EstateSettings> LoadEstateSettingsAll()
437 {
438 List<EstateSettings> allEstateSettings = new List<EstateSettings>();
439
440 List<int> allEstateIds = GetEstatesAll();
441
442 foreach (int estateId in allEstateIds)
443 allEstateSettings.Add(LoadEstateSettings(estateId));
444
445 return allEstateSettings;
446 }
447
448 public List<int> GetEstates(string search)
449 {
450 List<int> result = new List<int>();
451 string sql = "select \"estateID\" from estate_settings where lower(\"EstateName\") = lower(:EstateName)";
452 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
453 {
454 conn.Open();
455 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
456 {
457 cmd.Parameters.AddWithValue("EstateName", search);
458
459 using (IDataReader reader = cmd.ExecuteReader())
460 {
461 while (reader.Read())
462 {
463 result.Add(Convert.ToInt32(reader["EstateID"]));
464 }
465 reader.Close();
466 }
467 }
468 }
469
470 return result;
471 }
472
473 public List<int> GetEstatesAll()
474 {
475 List<int> result = new List<int>();
476 string sql = "select \"EstateID\" from estate_settings";
477 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
478 {
479 conn.Open();
480 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
481 {
482 using (IDataReader reader = cmd.ExecuteReader())
483 {
484 while (reader.Read())
485 {
486 result.Add(Convert.ToInt32(reader["EstateID"]));
487 }
488 reader.Close();
489 }
490 }
491 }
492
493 return result;
494 }
495
496 public List<int> GetEstatesByOwner(UUID ownerID)
497 {
498 List<int> result = new List<int>();
499 string sql = "select \"estateID\" from estate_settings where \"EstateOwner\" = :EstateOwner";
500 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
501 {
502 conn.Open();
503 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
504 {
505 cmd.Parameters.AddWithValue("EstateOwner", ownerID);
506
507 using (IDataReader reader = cmd.ExecuteReader())
508 {
509 while (reader.Read())
510 {
511 result.Add(Convert.ToInt32(reader["EstateID"]));
512 }
513 reader.Close();
514 }
515 }
516 }
517
518 return result;
519 }
520
521 public bool LinkRegion(UUID regionID, int estateID)
522 {
523 string deleteSQL = "delete from estate_map where \"RegionID\" = :RegionID";
524 string insertSQL = "insert into estate_map values (:RegionID, :EstateID)";
525 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
526 {
527 conn.Open();
528
529 NpgsqlTransaction transaction = conn.BeginTransaction();
530
531 try
532 {
533 using (NpgsqlCommand cmd = new NpgsqlCommand(deleteSQL, conn))
534 {
535 cmd.Transaction = transaction;
536 cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
537
538 cmd.ExecuteNonQuery();
539 }
540
541 using (NpgsqlCommand cmd = new NpgsqlCommand(insertSQL, conn))
542 {
543 cmd.Transaction = transaction;
544 cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
545 cmd.Parameters.AddWithValue("EstateID", estateID);
546
547 int ret = cmd.ExecuteNonQuery();
548
549 if (ret != 0)
550 transaction.Commit();
551 else
552 transaction.Rollback();
553
554 return (ret != 0);
555 }
556 }
557 catch (Exception ex)
558 {
559 m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
560 transaction.Rollback();
561 }
562 }
563 return false;
564 }
565
566 public List<UUID> GetRegions(int estateID)
567 {
568 List<UUID> result = new List<UUID>();
569 string sql = "select \"RegionID\" from estate_map where \"EstateID\" = :EstateID";
570 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
571 {
572 conn.Open();
573 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
574 {
575 cmd.Parameters.AddWithValue("EstateID", estateID);
576
577 using (IDataReader reader = cmd.ExecuteReader())
578 {
579 while (reader.Read())
580 {
581 result.Add(DBGuid.FromDB(reader["RegionID"]));
582 }
583 reader.Close();
584 }
585 }
586 }
587
588 return result;
589 }
590
591 public bool DeleteEstate(int estateID)
592 {
593 // TODO: Implementation!
594 return false;
595 }
596 #endregion
597 }
598}