opensim-development – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 eva 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.Data.SqlClient;
32 using System.Reflection;
33 using log4net;
34 using OpenMetaverse;
35 using OpenSim.Framework;
36 using OpenSim.Region.Framework.Interfaces;
37  
38 namespace 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 }