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