clockwerk-opensim – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 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.Reflection;
32 using log4net;
33 using MySql.Data.MySqlClient;
34 using OpenMetaverse;
35 using OpenSim.Framework;
36 using OpenSim.Region.Framework.Interfaces;
37 using OpenSim.Data;
38  
39 namespace OpenSim.Data.MySQL
40 {
41 public class MySQLEstateStore : IEstateDataStore
42 {
43 private static readonly ILog m_log =
44 LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45  
46 private const string m_waitTimeoutSelect = "select @@wait_timeout";
47  
48 private string m_connectionString;
49 private long m_waitTimeout;
50 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
51 // private long m_lastConnectionUse;
52  
53 private FieldInfo[] m_Fields;
54 private Dictionary<string, FieldInfo> m_FieldMap =
55 new Dictionary<string, FieldInfo>();
56  
57 protected virtual Assembly Assembly
58 {
59 get { return GetType().Assembly; }
60 }
61  
62 public MySQLEstateStore()
63 {
64 }
65  
66 public MySQLEstateStore(string connectionString)
67 {
68 Initialise(connectionString);
69 }
70  
71 public void Initialise(string connectionString)
72 {
73 m_connectionString = connectionString;
74  
75 try
76 {
77 m_log.Info("[REGION DB]: MySql - connecting: " + Util.GetDisplayConnectionString(m_connectionString));
78 }
79 catch (Exception e)
80 {
81 m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
82 }
83  
84 GetWaitTimeout();
85  
86 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
87 {
88 dbcon.Open();
89  
90 Migration m = new Migration(dbcon, Assembly, "EstateStore");
91 m.Update();
92  
93 Type t = typeof(EstateSettings);
94 m_Fields = t.GetFields(BindingFlags.NonPublic |
95 BindingFlags.Instance |
96 BindingFlags.DeclaredOnly);
97  
98 foreach (FieldInfo f in m_Fields)
99 {
100 if (f.Name.Substring(0, 2) == "m_")
101 m_FieldMap[f.Name.Substring(2)] = f;
102 }
103 }
104 }
105  
106 private string[] FieldList
107 {
108 get { return new List<string>(m_FieldMap.Keys).ToArray(); }
109 }
110  
111 protected void GetWaitTimeout()
112 {
113 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
114 {
115 dbcon.Open();
116  
117 using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
118 {
119 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
120 {
121 if (dbReader.Read())
122 {
123 m_waitTimeout
124 = Convert.ToInt32(dbReader["@@wait_timeout"]) *
125 TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
126 }
127 }
128 }
129  
130 // m_lastConnectionUse = DateTime.Now.Ticks;
131  
132 m_log.DebugFormat(
133 "[REGION DB]: Connection wait timeout {0} seconds",
134 m_waitTimeout / TimeSpan.TicksPerSecond);
135 }
136 }
137  
138 public EstateSettings LoadEstateSettings(UUID regionID, bool create)
139 {
140 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
141 " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
142  
143 using (MySqlCommand cmd = new MySqlCommand())
144 {
145 cmd.CommandText = sql;
146 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
147  
148 EstateSettings e = DoLoad(cmd, regionID, create);
149 if (!create && e.EstateID == 0) // Not found
150 return null;
151  
152 return e;
153 }
154 }
155  
156 public EstateSettings CreateNewEstate()
157 {
158 EstateSettings es = new EstateSettings();
159 es.OnSave += StoreEstateSettings;
160  
161 DoCreate(es);
162  
163 LoadBanList(es);
164  
165 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
166 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
167 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
168  
169 return es;
170 }
171  
172 private EstateSettings DoLoad(MySqlCommand cmd, UUID regionID, bool create)
173 {
174 EstateSettings es = new EstateSettings();
175 es.OnSave += StoreEstateSettings;
176  
177 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
178 {
179 dbcon.Open();
180  
181 cmd.Connection = dbcon;
182  
183 bool found = false;
184  
185 using (IDataReader r = cmd.ExecuteReader())
186 {
187 if (r.Read())
188 {
189 found = true;
190  
191 foreach (string name in FieldList)
192 {
193 if (m_FieldMap[name].FieldType == typeof(bool))
194 {
195 m_FieldMap[name].SetValue(es, Convert.ToInt32(r[name]) != 0);
196 }
197 else if (m_FieldMap[name].FieldType == typeof(UUID))
198 {
199 m_FieldMap[name].SetValue(es, DBGuid.FromDB(r[name]));
200 }
201 else
202 {
203 m_FieldMap[name].SetValue(es, r[name]);
204 }
205 }
206 }
207 }
208  
209 if (!found && create)
210 {
211 DoCreate(es);
212 LinkRegion(regionID, (int)es.EstateID);
213 }
214 }
215  
216 LoadBanList(es);
217 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
218 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
219 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
220 return es;
221 }
222  
223 private void DoCreate(EstateSettings es)
224 {
225 // Migration case
226 List<string> names = new List<string>(FieldList);
227  
228 names.Remove("EstateID");
229  
230 string sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
231  
232 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
233 {
234 dbcon.Open();
235 using (MySqlCommand cmd2 = dbcon.CreateCommand())
236 {
237 cmd2.CommandText = sql;
238 cmd2.Parameters.Clear();
239  
240 foreach (string name in FieldList)
241 {
242 if (m_FieldMap[name].GetValue(es) is bool)
243 {
244 if ((bool)m_FieldMap[name].GetValue(es))
245 cmd2.Parameters.AddWithValue("?" + name, "1");
246 else
247 cmd2.Parameters.AddWithValue("?" + name, "0");
248 }
249 else
250 {
251 cmd2.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
252 }
253 }
254  
255 cmd2.ExecuteNonQuery();
256  
257 cmd2.CommandText = "select LAST_INSERT_ID() as id";
258 cmd2.Parameters.Clear();
259  
260 using (IDataReader r = cmd2.ExecuteReader())
261 {
262 r.Read();
263 es.EstateID = Convert.ToUInt32(r["id"]);
264 }
265  
266 es.Save();
267 }
268 }
269 }
270  
271 public void StoreEstateSettings(EstateSettings es)
272 {
273 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
274  
275 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
276 {
277 dbcon.Open();
278  
279 using (MySqlCommand cmd = dbcon.CreateCommand())
280 {
281 cmd.CommandText = sql;
282  
283 foreach (string name in FieldList)
284 {
285 if (m_FieldMap[name].GetValue(es) is bool)
286 {
287 if ((bool)m_FieldMap[name].GetValue(es))
288 cmd.Parameters.AddWithValue("?" + name, "1");
289 else
290 cmd.Parameters.AddWithValue("?" + name, "0");
291 }
292 else
293 {
294 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
295 }
296 }
297  
298 cmd.ExecuteNonQuery();
299 }
300 }
301  
302 SaveBanList(es);
303 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
304 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
305 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
306 }
307  
308 private void LoadBanList(EstateSettings es)
309 {
310 es.ClearBans();
311  
312 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
313 {
314 dbcon.Open();
315  
316 using (MySqlCommand cmd = dbcon.CreateCommand())
317 {
318 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
319 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
320  
321 using (IDataReader r = cmd.ExecuteReader())
322 {
323 while (r.Read())
324 {
325 EstateBan eb = new EstateBan();
326  
327 UUID uuid = new UUID();
328 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
329  
330 eb.BannedUserID = uuid;
331 eb.BannedHostAddress = "0.0.0.0";
332 eb.BannedHostIPMask = "0.0.0.0";
333 es.AddBan(eb);
334 }
335 }
336 }
337 }
338 }
339  
340 private void SaveBanList(EstateSettings es)
341 {
342 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
343 {
344 dbcon.Open();
345  
346 using (MySqlCommand cmd = dbcon.CreateCommand())
347 {
348 cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
349 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
350  
351 cmd.ExecuteNonQuery();
352  
353 cmd.Parameters.Clear();
354  
355 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
356  
357 foreach (EstateBan b in es.EstateBans)
358 {
359 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
360 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
361  
362 cmd.ExecuteNonQuery();
363 cmd.Parameters.Clear();
364 }
365 }
366 }
367 }
368  
369 void SaveUUIDList(uint EstateID, string table, UUID[] data)
370 {
371 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
372 {
373 dbcon.Open();
374  
375 using (MySqlCommand cmd = dbcon.CreateCommand())
376 {
377 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
378 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
379  
380 cmd.ExecuteNonQuery();
381  
382 cmd.Parameters.Clear();
383  
384 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
385  
386 foreach (UUID uuid in data)
387 {
388 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
389 cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
390  
391 cmd.ExecuteNonQuery();
392 cmd.Parameters.Clear();
393 }
394 }
395 }
396 }
397  
398 UUID[] LoadUUIDList(uint EstateID, string table)
399 {
400 List<UUID> uuids = new List<UUID>();
401  
402 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
403 {
404 dbcon.Open();
405  
406 using (MySqlCommand cmd = dbcon.CreateCommand())
407 {
408 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
409 cmd.Parameters.AddWithValue("?EstateID", EstateID);
410  
411 using (IDataReader r = cmd.ExecuteReader())
412 {
413 while (r.Read())
414 {
415 // EstateBan eb = new EstateBan();
416 uuids.Add(DBGuid.FromDB(r["uuid"]));
417 }
418 }
419 }
420 }
421  
422 return uuids.ToArray();
423 }
424  
425 public EstateSettings LoadEstateSettings(int estateID)
426 {
427 using (MySqlCommand cmd = new MySqlCommand())
428 {
429 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = ?EstateID";
430  
431 cmd.CommandText = sql;
432 cmd.Parameters.AddWithValue("?EstateID", estateID);
433  
434 EstateSettings e = DoLoad(cmd, UUID.Zero, false);
435 if (e.EstateID != estateID)
436 return null;
437 return e;
438 }
439 }
440  
441 public List<EstateSettings> LoadEstateSettingsAll()
442 {
443 List<EstateSettings> allEstateSettings = new List<EstateSettings>();
444  
445 List<int> allEstateIds = GetEstatesAll();
446  
447 foreach (int estateId in allEstateIds)
448 allEstateSettings.Add(LoadEstateSettings(estateId));
449  
450 return allEstateSettings;
451 }
452  
453 public List<int> GetEstatesAll()
454 {
455 List<int> result = new List<int>();
456  
457 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
458 {
459 dbcon.Open();
460  
461 using (MySqlCommand cmd = dbcon.CreateCommand())
462 {
463 cmd.CommandText = "select estateID from estate_settings";
464  
465 using (IDataReader reader = cmd.ExecuteReader())
466 {
467 while (reader.Read())
468 {
469 result.Add(Convert.ToInt32(reader["EstateID"]));
470 }
471 reader.Close();
472 }
473 }
474  
475 dbcon.Close();
476 }
477  
478 return result;
479 }
480  
481 public List<int> GetEstates(string search)
482 {
483 List<int> result = new List<int>();
484  
485 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
486 {
487 dbcon.Open();
488  
489 using (MySqlCommand cmd = dbcon.CreateCommand())
490 {
491 cmd.CommandText = "select estateID from estate_settings where EstateName = ?EstateName";
492 cmd.Parameters.AddWithValue("?EstateName", search);
493  
494 using (IDataReader reader = cmd.ExecuteReader())
495 {
496 while (reader.Read())
497 {
498 result.Add(Convert.ToInt32(reader["EstateID"]));
499 }
500 reader.Close();
501 }
502 }
503  
504 dbcon.Close();
505 }
506  
507 return result;
508 }
509  
510 public List<int> GetEstatesByOwner(UUID ownerID)
511 {
512 List<int> result = new List<int>();
513  
514 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
515 {
516 dbcon.Open();
517  
518 using (MySqlCommand cmd = dbcon.CreateCommand())
519 {
520 cmd.CommandText = "select estateID from estate_settings where EstateOwner = ?EstateOwner";
521 cmd.Parameters.AddWithValue("?EstateOwner", ownerID);
522  
523 using (IDataReader reader = cmd.ExecuteReader())
524 {
525 while (reader.Read())
526 {
527 result.Add(Convert.ToInt32(reader["EstateID"]));
528 }
529 reader.Close();
530 }
531 }
532  
533 dbcon.Close();
534 }
535  
536 return result;
537 }
538  
539 public bool LinkRegion(UUID regionID, int estateID)
540 {
541 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
542 {
543 dbcon.Open();
544 MySqlTransaction transaction = dbcon.BeginTransaction();
545  
546 try
547 {
548 // Delete any existing association of this region with an estate.
549 using (MySqlCommand cmd = dbcon.CreateCommand())
550 {
551 cmd.Transaction = transaction;
552 cmd.CommandText = "delete from estate_map where RegionID = ?RegionID";
553 cmd.Parameters.AddWithValue("?RegionID", regionID);
554  
555 cmd.ExecuteNonQuery();
556 }
557  
558 using (MySqlCommand cmd = dbcon.CreateCommand())
559 {
560 cmd.Transaction = transaction;
561 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
562 cmd.Parameters.AddWithValue("?RegionID", regionID);
563 cmd.Parameters.AddWithValue("?EstateID", estateID);
564  
565 int ret = cmd.ExecuteNonQuery();
566  
567 if (ret != 0)
568 transaction.Commit();
569 else
570 transaction.Rollback();
571  
572 dbcon.Close();
573  
574 return (ret != 0);
575 }
576 }
577 catch (MySqlException ex)
578 {
579 m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
580 transaction.Rollback();
581 }
582  
583 dbcon.Close();
584 }
585  
586 return false;
587 }
588  
589 public List<UUID> GetRegions(int estateID)
590 {
591 List<UUID> result = new List<UUID>();
592  
593 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
594 {
595 dbcon.Open();
596  
597 try
598 {
599 using (MySqlCommand cmd = dbcon.CreateCommand())
600 {
601 cmd.CommandText = "select RegionID from estate_map where EstateID = ?EstateID";
602 cmd.Parameters.AddWithValue("?EstateID", estateID.ToString());
603  
604 using (IDataReader reader = cmd.ExecuteReader())
605 {
606 while(reader.Read())
607 result.Add(DBGuid.FromDB(reader["RegionID"]));
608 reader.Close();
609 }
610 }
611 }
612 catch (Exception e)
613 {
614 m_log.Error("[REGION DB]: Error reading estate map. " + e.ToString());
615 return result;
616 }
617 dbcon.Close();
618 }
619  
620 return result;
621 }
622  
623 public bool DeleteEstate(int estateID)
624 {
625 return false;
626 }
627 }
628 }