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.cs602
1 files changed, 602 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
new file mode 100644
index 0000000..141b8ed
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
@@ -0,0 +1,602 @@
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 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
205 using (NpgsqlCommand insertCommand = new NpgsqlCommand(sql, conn))
206 {
207 insertCommand.CommandText = sql;
208
209 foreach (string name in names)
210 {
211 insertCommand.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es)));
212 }
213 //NpgsqlParameter idParameter = new NpgsqlParameter("ID", SqlDbType.Int);
214 //idParameter.Direction = ParameterDirection.Output;
215 //insertCommand.Parameters.Add(idParameter);
216 conn.Open();
217
218 es.EstateID = 100;
219
220 if (insertCommand.ExecuteNonQuery() > 0)
221 {
222 insertCommand.CommandText = "Select cast(lastval() as int) as ID ;";
223
224 using (NpgsqlDataReader result = insertCommand.ExecuteReader())
225 {
226 if (result.Read())
227 {
228 es.EstateID = (uint)result.GetInt32(0);
229 }
230 }
231 }
232
233 }
234
235 //TODO check if this is needed??
236 es.Save();
237 }
238
239 /// <summary>
240 /// Stores the estate settings.
241 /// </summary>
242 /// <param name="es">estate settings</param>
243 public void StoreEstateSettings(EstateSettings es)
244 {
245 List<string> names = new List<string>(FieldList);
246
247 names.Remove("EstateID");
248
249 string sql = string.Format("UPDATE estate_settings SET ");
250 foreach (string name in names)
251 {
252 sql += "\"" + name + "\" = :" + name + ", ";
253 }
254 sql = sql.Remove(sql.LastIndexOf(","));
255 sql += " WHERE \"EstateID\" = :EstateID";
256
257 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
258 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
259 {
260 foreach (string name in names)
261 {
262 cmd.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es)));
263 }
264
265 cmd.Parameters.Add(_Database.CreateParameter("EstateID", es.EstateID));
266 conn.Open();
267 cmd.ExecuteNonQuery();
268 }
269
270 SaveBanList(es);
271 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
272 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
273 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
274 }
275
276 #endregion
277
278 #region Private methods
279
280 private string[] FieldList
281 {
282 get { return new List<string>(_FieldMap.Keys).ToArray(); }
283 }
284
285 private void LoadBanList(EstateSettings es)
286 {
287 es.ClearBans();
288
289 string sql = "select \"bannedUUID\" from estateban where \"EstateID\" = :EstateID";
290
291 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
292 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
293 {
294 NpgsqlParameter idParameter = new NpgsqlParameter("EstateID", DbType.Int32);
295 idParameter.Value = es.EstateID;
296 cmd.Parameters.Add(idParameter);
297 conn.Open();
298 using (NpgsqlDataReader reader = cmd.ExecuteReader())
299 {
300 while (reader.Read())
301 {
302 EstateBan eb = new EstateBan();
303
304 eb.BannedUserID = new UUID((Guid)reader["bannedUUID"]); //uuid;
305 eb.BannedHostAddress = "0.0.0.0";
306 eb.BannedHostIPMask = "0.0.0.0";
307 es.AddBan(eb);
308 }
309 }
310 }
311 }
312
313 private UUID[] LoadUUIDList(uint estateID, string table)
314 {
315 List<UUID> uuids = new List<UUID>();
316
317 string sql = string.Format("select uuid from {0} where \"EstateID\" = :EstateID", table);
318
319 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
320 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
321 {
322 cmd.Parameters.Add(_Database.CreateParameter("EstateID", estateID));
323 conn.Open();
324 using (NpgsqlDataReader reader = cmd.ExecuteReader())
325 {
326 while (reader.Read())
327 {
328 uuids.Add(new UUID((Guid)reader["uuid"])); //uuid);
329 }
330 }
331 }
332
333 return uuids.ToArray();
334 }
335
336 private void SaveBanList(EstateSettings es)
337 {
338 //Delete first
339 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
340 {
341 conn.Open();
342 using (NpgsqlCommand cmd = conn.CreateCommand())
343 {
344 cmd.CommandText = "delete from estateban where \"EstateID\" = :EstateID";
345 cmd.Parameters.AddWithValue("EstateID", (int)es.EstateID);
346 cmd.ExecuteNonQuery();
347
348 //Insert after
349 cmd.CommandText = "insert into estateban (\"EstateID\", \"bannedUUID\",\"bannedIp\", \"bannedIpHostMask\", \"bannedNameMask\") values ( :EstateID, :bannedUUID, '','','' )";
350 cmd.Parameters.AddWithValue("bannedUUID", Guid.Empty);
351 foreach (EstateBan b in es.EstateBans)
352 {
353 cmd.Parameters["bannedUUID"].Value = b.BannedUserID.Guid;
354 cmd.ExecuteNonQuery();
355 }
356 }
357 }
358 }
359
360 private void SaveUUIDList(uint estateID, string table, UUID[] data)
361 {
362 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
363 {
364 conn.Open();
365 using (NpgsqlCommand cmd = conn.CreateCommand())
366 {
367 cmd.Parameters.AddWithValue("EstateID", (int)estateID);
368 cmd.CommandText = string.Format("delete from {0} where \"EstateID\" = :EstateID", table);
369 cmd.ExecuteNonQuery();
370
371 cmd.CommandText = string.Format("insert into {0} (\"EstateID\", uuid) values ( :EstateID, :uuid )", table);
372 cmd.Parameters.AddWithValue("uuid", Guid.Empty);
373 foreach (UUID uuid in data)
374 {
375 cmd.Parameters["uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
376 cmd.ExecuteNonQuery();
377 }
378 }
379 }
380 }
381
382 public EstateSettings LoadEstateSettings(int estateID)
383 {
384 EstateSettings es = new EstateSettings();
385 string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) + "\" from estate_settings where \"EstateID\" = :EstateID";
386 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
387 {
388 conn.Open();
389 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
390 {
391 cmd.Parameters.AddWithValue("EstateID", (int)estateID);
392 using (NpgsqlDataReader reader = cmd.ExecuteReader())
393 {
394 if (reader.Read())
395 {
396 foreach (string name in FieldList)
397 {
398 FieldInfo f = _FieldMap[name];
399 object v = reader[name];
400 if (f.FieldType == typeof(bool))
401 {
402 f.SetValue(es, Convert.ToInt32(v) != 0);
403 }
404 else if (f.FieldType == typeof(UUID))
405 {
406 f.SetValue(es, new UUID((Guid)v)); // uuid);
407 }
408 else if (f.FieldType == typeof(string))
409 {
410 f.SetValue(es, v.ToString());
411 }
412 else if (f.FieldType == typeof(UInt32))
413 {
414 f.SetValue(es, Convert.ToUInt32(v));
415 }
416 else if (f.FieldType == typeof(Single))
417 {
418 f.SetValue(es, Convert.ToSingle(v));
419 }
420 else
421 f.SetValue(es, v);
422 }
423 }
424
425 }
426 }
427 }
428 LoadBanList(es);
429
430 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
431 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
432 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
433
434 //Set event
435 es.OnSave += StoreEstateSettings;
436 return es;
437
438 }
439
440 public List<EstateSettings> LoadEstateSettingsAll()
441 {
442 List<EstateSettings> allEstateSettings = new List<EstateSettings>();
443
444 List<int> allEstateIds = GetEstatesAll();
445
446 foreach (int estateId in allEstateIds)
447 allEstateSettings.Add(LoadEstateSettings(estateId));
448
449 return allEstateSettings;
450 }
451
452 public List<int> GetEstates(string search)
453 {
454 List<int> result = new List<int>();
455 string sql = "select \"estateID\" from estate_settings where lower(\"EstateName\") = lower(:EstateName)";
456 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
457 {
458 conn.Open();
459 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
460 {
461 cmd.Parameters.AddWithValue("EstateName", search);
462
463 using (IDataReader reader = cmd.ExecuteReader())
464 {
465 while (reader.Read())
466 {
467 result.Add(Convert.ToInt32(reader["EstateID"]));
468 }
469 reader.Close();
470 }
471 }
472 }
473
474 return result;
475 }
476
477 public List<int> GetEstatesAll()
478 {
479 List<int> result = new List<int>();
480 string sql = "select \"EstateID\" from estate_settings";
481 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
482 {
483 conn.Open();
484 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
485 {
486 using (IDataReader reader = cmd.ExecuteReader())
487 {
488 while (reader.Read())
489 {
490 result.Add(Convert.ToInt32(reader["EstateID"]));
491 }
492 reader.Close();
493 }
494 }
495 }
496
497 return result;
498 }
499
500 public List<int> GetEstatesByOwner(UUID ownerID)
501 {
502 List<int> result = new List<int>();
503 string sql = "select \"estateID\" from estate_settings where \"EstateOwner\" = :EstateOwner";
504 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
505 {
506 conn.Open();
507 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
508 {
509 cmd.Parameters.AddWithValue("EstateOwner", ownerID);
510
511 using (IDataReader reader = cmd.ExecuteReader())
512 {
513 while (reader.Read())
514 {
515 result.Add(Convert.ToInt32(reader["EstateID"]));
516 }
517 reader.Close();
518 }
519 }
520 }
521
522 return result;
523 }
524
525 public bool LinkRegion(UUID regionID, int estateID)
526 {
527 string deleteSQL = "delete from estate_map where \"RegionID\" = :RegionID";
528 string insertSQL = "insert into estate_map values (:RegionID, :EstateID)";
529 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
530 {
531 conn.Open();
532
533 NpgsqlTransaction transaction = conn.BeginTransaction();
534
535 try
536 {
537 using (NpgsqlCommand cmd = new NpgsqlCommand(deleteSQL, conn))
538 {
539 cmd.Transaction = transaction;
540 cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
541
542 cmd.ExecuteNonQuery();
543 }
544
545 using (NpgsqlCommand cmd = new NpgsqlCommand(insertSQL, conn))
546 {
547 cmd.Transaction = transaction;
548 cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
549 cmd.Parameters.AddWithValue("EstateID", estateID);
550
551 int ret = cmd.ExecuteNonQuery();
552
553 if (ret != 0)
554 transaction.Commit();
555 else
556 transaction.Rollback();
557
558 return (ret != 0);
559 }
560 }
561 catch (Exception ex)
562 {
563 m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
564 transaction.Rollback();
565 }
566 }
567 return false;
568 }
569
570 public List<UUID> GetRegions(int estateID)
571 {
572 List<UUID> result = new List<UUID>();
573 string sql = "select \"RegionID\" from estate_map where \"EstateID\" = :EstateID";
574 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
575 {
576 conn.Open();
577 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
578 {
579 cmd.Parameters.AddWithValue("EstateID", estateID);
580
581 using (IDataReader reader = cmd.ExecuteReader())
582 {
583 while (reader.Read())
584 {
585 result.Add(DBGuid.FromDB(reader["RegionID"]));
586 }
587 reader.Close();
588 }
589 }
590 }
591
592 return result;
593 }
594
595 public bool DeleteEstate(int estateID)
596 {
597 // TODO: Implementation!
598 return false;
599 }
600 #endregion
601 }
602}