opensim – 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.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 return DoLoad(cmd, regionID, create);
149 }
150 }
151  
152 public EstateSettings CreateNewEstate()
153 {
154 EstateSettings es = new EstateSettings();
155 es.OnSave += StoreEstateSettings;
156  
157 DoCreate(es);
158  
159 LoadBanList(es);
160  
161 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
162 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
163 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
164  
165 return es;
166 }
167  
168 private EstateSettings DoLoad(MySqlCommand cmd, UUID regionID, bool create)
169 {
170 EstateSettings es = new EstateSettings();
171 es.OnSave += StoreEstateSettings;
172  
173 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
174 {
175 dbcon.Open();
176  
177 cmd.Connection = dbcon;
178  
179 bool found = false;
180  
181 using (IDataReader r = cmd.ExecuteReader())
182 {
183 if (r.Read())
184 {
185 found = true;
186  
187 foreach (string name in FieldList)
188 {
189 if (m_FieldMap[name].FieldType == typeof(bool))
190 {
191 m_FieldMap[name].SetValue(es, Convert.ToInt32(r[name]) != 0);
192 }
193 else if (m_FieldMap[name].FieldType == typeof(UUID))
194 {
195 m_FieldMap[name].SetValue(es, DBGuid.FromDB(r[name]));
196 }
197 else
198 {
199 m_FieldMap[name].SetValue(es, r[name]);
200 }
201 }
202 }
203 }
204  
205 if (!found && create)
206 {
207 DoCreate(es);
208 LinkRegion(regionID, (int)es.EstateID);
209 }
210 }
211  
212 LoadBanList(es);
213 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
214 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
215 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
216 return es;
217 }
218  
219 private void DoCreate(EstateSettings es)
220 {
221 // Migration case
222 List<string> names = new List<string>(FieldList);
223  
224 names.Remove("EstateID");
225  
226 string sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
227  
228 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
229 {
230 dbcon.Open();
231 using (MySqlCommand cmd2 = dbcon.CreateCommand())
232 {
233 cmd2.CommandText = sql;
234 cmd2.Parameters.Clear();
235  
236 foreach (string name in FieldList)
237 {
238 if (m_FieldMap[name].GetValue(es) is bool)
239 {
240 if ((bool)m_FieldMap[name].GetValue(es))
241 cmd2.Parameters.AddWithValue("?" + name, "1");
242 else
243 cmd2.Parameters.AddWithValue("?" + name, "0");
244 }
245 else
246 {
247 cmd2.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
248 }
249 }
250  
251 cmd2.ExecuteNonQuery();
252  
253 cmd2.CommandText = "select LAST_INSERT_ID() as id";
254 cmd2.Parameters.Clear();
255  
256 using (IDataReader r = cmd2.ExecuteReader())
257 {
258 r.Read();
259 es.EstateID = Convert.ToUInt32(r["id"]);
260 }
261  
262 es.Save();
263 }
264 }
265 }
266  
267 public void StoreEstateSettings(EstateSettings es)
268 {
269 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
270  
271 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
272 {
273 dbcon.Open();
274  
275 using (MySqlCommand cmd = dbcon.CreateCommand())
276 {
277 cmd.CommandText = sql;
278  
279 foreach (string name in FieldList)
280 {
281 if (m_FieldMap[name].GetValue(es) is bool)
282 {
283 if ((bool)m_FieldMap[name].GetValue(es))
284 cmd.Parameters.AddWithValue("?" + name, "1");
285 else
286 cmd.Parameters.AddWithValue("?" + name, "0");
287 }
288 else
289 {
290 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
291 }
292 }
293  
294 cmd.ExecuteNonQuery();
295 }
296 }
297  
298 SaveBanList(es);
299 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
300 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
301 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
302 }
303  
304 private void LoadBanList(EstateSettings es)
305 {
306 es.ClearBans();
307  
308 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
309 {
310 dbcon.Open();
311  
312 using (MySqlCommand cmd = dbcon.CreateCommand())
313 {
314 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
315 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
316  
317 using (IDataReader r = cmd.ExecuteReader())
318 {
319 while (r.Read())
320 {
321 EstateBan eb = new EstateBan();
322  
323 UUID uuid = new UUID();
324 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
325  
326 eb.BannedUserID = uuid;
327 eb.BannedHostAddress = "0.0.0.0";
328 eb.BannedHostIPMask = "0.0.0.0";
329 es.AddBan(eb);
330 }
331 }
332 }
333 }
334 }
335  
336 private void SaveBanList(EstateSettings es)
337 {
338 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
339 {
340 dbcon.Open();
341  
342 using (MySqlCommand cmd = dbcon.CreateCommand())
343 {
344 cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
345 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
346  
347 cmd.ExecuteNonQuery();
348  
349 cmd.Parameters.Clear();
350  
351 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
352  
353 foreach (EstateBan b in es.EstateBans)
354 {
355 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
356 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
357  
358 cmd.ExecuteNonQuery();
359 cmd.Parameters.Clear();
360 }
361 }
362 }
363 }
364  
365 void SaveUUIDList(uint EstateID, string table, UUID[] data)
366 {
367 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
368 {
369 dbcon.Open();
370  
371 using (MySqlCommand cmd = dbcon.CreateCommand())
372 {
373 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
374 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
375  
376 cmd.ExecuteNonQuery();
377  
378 cmd.Parameters.Clear();
379  
380 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
381  
382 foreach (UUID uuid in data)
383 {
384 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
385 cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
386  
387 cmd.ExecuteNonQuery();
388 cmd.Parameters.Clear();
389 }
390 }
391 }
392 }
393  
394 UUID[] LoadUUIDList(uint EstateID, string table)
395 {
396 List<UUID> uuids = new List<UUID>();
397  
398 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
399 {
400 dbcon.Open();
401  
402 using (MySqlCommand cmd = dbcon.CreateCommand())
403 {
404 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
405 cmd.Parameters.AddWithValue("?EstateID", EstateID);
406  
407 using (IDataReader r = cmd.ExecuteReader())
408 {
409 while (r.Read())
410 {
411 // EstateBan eb = new EstateBan();
412 uuids.Add(DBGuid.FromDB(r["uuid"]));
413 }
414 }
415 }
416 }
417  
418 return uuids.ToArray();
419 }
420  
421 public EstateSettings LoadEstateSettings(int estateID)
422 {
423 using (MySqlCommand cmd = new MySqlCommand())
424 {
425 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = ?EstateID";
426  
427 cmd.CommandText = sql;
428 cmd.Parameters.AddWithValue("?EstateID", estateID);
429  
430 return DoLoad(cmd, UUID.Zero, false);
431 }
432 }
433  
434 public List<EstateSettings> LoadEstateSettingsAll()
435 {
436 List<EstateSettings> allEstateSettings = new List<EstateSettings>();
437  
438 List<int> allEstateIds = GetEstatesAll();
439  
440 foreach (int estateId in allEstateIds)
441 allEstateSettings.Add(LoadEstateSettings(estateId));
442  
443 return allEstateSettings;
444 }
445  
446 public List<int> GetEstatesAll()
447 {
448 List<int> result = new List<int>();
449  
450 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
451 {
452 dbcon.Open();
453  
454 using (MySqlCommand cmd = dbcon.CreateCommand())
455 {
456 cmd.CommandText = "select estateID from estate_settings";
457  
458 using (IDataReader reader = cmd.ExecuteReader())
459 {
460 while (reader.Read())
461 {
462 result.Add(Convert.ToInt32(reader["EstateID"]));
463 }
464 reader.Close();
465 }
466 }
467  
468 dbcon.Close();
469 }
470  
471 return result;
472 }
473  
474 public List<int> GetEstates(string search)
475 {
476 List<int> result = new List<int>();
477  
478 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
479 {
480 dbcon.Open();
481  
482 using (MySqlCommand cmd = dbcon.CreateCommand())
483 {
484 cmd.CommandText = "select estateID from estate_settings where EstateName = ?EstateName";
485 cmd.Parameters.AddWithValue("?EstateName", search);
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 dbcon.Close();
498 }
499  
500 return result;
501 }
502  
503 public List<int> GetEstatesByOwner(UUID ownerID)
504 {
505 List<int> result = new List<int>();
506  
507 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
508 {
509 dbcon.Open();
510  
511 using (MySqlCommand cmd = dbcon.CreateCommand())
512 {
513 cmd.CommandText = "select estateID from estate_settings where EstateOwner = ?EstateOwner";
514 cmd.Parameters.AddWithValue("?EstateOwner", ownerID);
515  
516 using (IDataReader reader = cmd.ExecuteReader())
517 {
518 while (reader.Read())
519 {
520 result.Add(Convert.ToInt32(reader["EstateID"]));
521 }
522 reader.Close();
523 }
524 }
525  
526 dbcon.Close();
527 }
528  
529 return result;
530 }
531  
532 public bool LinkRegion(UUID regionID, int estateID)
533 {
534 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
535 {
536 dbcon.Open();
537 MySqlTransaction transaction = dbcon.BeginTransaction();
538  
539 try
540 {
541 // Delete any existing association of this region with an estate.
542 using (MySqlCommand cmd = dbcon.CreateCommand())
543 {
544 cmd.Transaction = transaction;
545 cmd.CommandText = "delete from estate_map where RegionID = ?RegionID";
546 cmd.Parameters.AddWithValue("?RegionID", regionID);
547  
548 cmd.ExecuteNonQuery();
549 }
550  
551 using (MySqlCommand cmd = dbcon.CreateCommand())
552 {
553 cmd.Transaction = transaction;
554 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
555 cmd.Parameters.AddWithValue("?RegionID", regionID);
556 cmd.Parameters.AddWithValue("?EstateID", estateID);
557  
558 int ret = cmd.ExecuteNonQuery();
559  
560 if (ret != 0)
561 transaction.Commit();
562 else
563 transaction.Rollback();
564  
565 dbcon.Close();
566  
567 return (ret != 0);
568 }
569 }
570 catch (MySqlException ex)
571 {
572 m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
573 transaction.Rollback();
574 }
575  
576 dbcon.Close();
577 }
578  
579 return false;
580 }
581  
582 public List<UUID> GetRegions(int estateID)
583 {
584 List<UUID> result = new List<UUID>();
585  
586 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
587 {
588 dbcon.Open();
589  
590 try
591 {
592 using (MySqlCommand cmd = dbcon.CreateCommand())
593 {
594 cmd.CommandText = "select RegionID from estate_map where EstateID = ?EstateID";
595 cmd.Parameters.AddWithValue("?EstateID", estateID.ToString());
596  
597 using (IDataReader reader = cmd.ExecuteReader())
598 {
599 while(reader.Read())
600 result.Add(DBGuid.FromDB(reader["RegionID"]));
601 reader.Close();
602 }
603 }
604 }
605 catch (Exception e)
606 {
607 m_log.Error("[REGION DB]: Error reading estate map. " + e.ToString());
608 return result;
609 }
610 dbcon.Close();
611 }
612  
613 return result;
614 }
615  
616 public bool DeleteEstate(int estateID)
617 {
618 return false;
619 }
620 }
621 }