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 #if CSharpSqlite
34 using Community.CsharpSqlite.Sqlite;
35 #else
36 using Mono.Data.Sqlite;
37 #endif
38 using OpenMetaverse;
39 using OpenSim.Framework;
40 using OpenSim.Region.Framework.Interfaces;
41  
42 namespace OpenSim.Data.SQLite
43 {
44 public class SQLiteEstateStore : IEstateDataStore
45 {
46 private static readonly ILog m_log =
47 LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48  
49 private SqliteConnection m_connection;
50 private string m_connectionString;
51  
52 private FieldInfo[] m_Fields;
53 private Dictionary<string, FieldInfo> m_FieldMap =
54 new Dictionary<string, FieldInfo>();
55  
56 protected virtual Assembly Assembly
57 {
58 get { return GetType().Assembly; }
59 }
60  
61 public SQLiteEstateStore()
62 {
63 }
64  
65 public SQLiteEstateStore(string connectionString)
66 {
67 Initialise(connectionString);
68 }
69  
70 public void Initialise(string connectionString)
71 {
72 if (Util.IsWindows())
73 Util.LoadArchSpecificWindowsDll("sqlite3.dll");
74  
75 m_connectionString = connectionString;
76  
77 m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
78  
79 m_connection = new SqliteConnection(m_connectionString);
80 m_connection.Open();
81  
82 Migration m = new Migration(m_connection, Assembly, "EstateStore");
83 m.Update();
84  
85 //m_connection.Close();
86 // m_connection.Open();
87  
88 Type t = typeof(EstateSettings);
89 m_Fields = t.GetFields(BindingFlags.NonPublic |
90 BindingFlags.Instance |
91 BindingFlags.DeclaredOnly);
92  
93 foreach (FieldInfo f in m_Fields)
94 if (f.Name.Substring(0, 2) == "m_")
95 m_FieldMap[f.Name.Substring(2)] = f;
96 }
97  
98 private string[] FieldList
99 {
100 get { return new List<string>(m_FieldMap.Keys).ToArray(); }
101 }
102  
103 public EstateSettings LoadEstateSettings(UUID regionID, bool create)
104 {
105 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";
106  
107 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
108 {
109 cmd.CommandText = sql;
110 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
111  
112 return DoLoad(cmd, regionID, create);
113 }
114 }
115  
116 private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
117 {
118 EstateSettings es = new EstateSettings();
119 es.OnSave += StoreEstateSettings;
120 IDataReader r = null;
121 try
122 {
123 r = cmd.ExecuteReader();
124 }
125 catch (SqliteException)
126 {
127 m_log.Error("[SQLITE]: There was an issue loading the estate settings. This can happen the first time running OpenSimulator with CSharpSqlite the first time. OpenSimulator will probably crash, restart it and it should be good to go.");
128 }
129  
130 if (r != null && r.Read())
131 {
132 foreach (string name in FieldList)
133 {
134 if (m_FieldMap[name].GetValue(es) is bool)
135 {
136 int v = Convert.ToInt32(r[name]);
137 if (v != 0)
138 m_FieldMap[name].SetValue(es, true);
139 else
140 m_FieldMap[name].SetValue(es, false);
141 }
142 else if (m_FieldMap[name].GetValue(es) is UUID)
143 {
144 UUID uuid = UUID.Zero;
145  
146 UUID.TryParse(r[name].ToString(), out uuid);
147 m_FieldMap[name].SetValue(es, uuid);
148 }
149 else
150 {
151 m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
152 }
153 }
154 r.Close();
155 }
156 else if (create)
157 {
158 DoCreate(es);
159 LinkRegion(regionID, (int)es.EstateID);
160 }
161  
162 LoadBanList(es);
163  
164 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
165 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
166 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
167 return es;
168 }
169  
170 public EstateSettings CreateNewEstate()
171 {
172 EstateSettings es = new EstateSettings();
173 es.OnSave += StoreEstateSettings;
174  
175 DoCreate(es);
176  
177 LoadBanList(es);
178  
179 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
180 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
181 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
182  
183 return es;
184 }
185  
186 private void DoCreate(EstateSettings es)
187 {
188 List<string> names = new List<string>(FieldList);
189  
190 IDataReader r = null;
191  
192 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
193 {
194 names.Remove("EstateID");
195  
196 string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
197  
198 cmd.CommandText = sql;
199 cmd.Parameters.Clear();
200  
201 foreach (string name in FieldList)
202 {
203 if (m_FieldMap[name].GetValue(es) is bool)
204 {
205 if ((bool)m_FieldMap[name].GetValue(es))
206 cmd.Parameters.AddWithValue(":"+name, "1");
207 else
208 cmd.Parameters.AddWithValue(":"+name, "0");
209 }
210 else
211 {
212 cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
213 }
214 }
215  
216 cmd.ExecuteNonQuery();
217  
218 cmd.CommandText = "select LAST_INSERT_ROWID() as id";
219 cmd.Parameters.Clear();
220  
221 r = cmd.ExecuteReader();
222 }
223  
224 r.Read();
225  
226 es.EstateID = Convert.ToUInt32(r["id"]);
227  
228 r.Close();
229  
230 es.Save();
231 }
232  
233 public void StoreEstateSettings(EstateSettings es)
234 {
235 List<string> fields = new List<string>(FieldList);
236 fields.Remove("EstateID");
237  
238 List<string> terms = new List<string>();
239  
240 foreach (string f in fields)
241 terms.Add(f+" = :"+f);
242  
243 string sql = "update estate_settings set "+String.Join(", ", terms.ToArray())+" where EstateID = :EstateID";
244  
245 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
246 {
247 cmd.CommandText = sql;
248  
249 foreach (string name in FieldList)
250 {
251 if (m_FieldMap[name].GetValue(es) is bool)
252 {
253 if ((bool)m_FieldMap[name].GetValue(es))
254 cmd.Parameters.AddWithValue(":"+name, "1");
255 else
256 cmd.Parameters.AddWithValue(":"+name, "0");
257 }
258 else
259 {
260 cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
261 }
262 }
263  
264 cmd.ExecuteNonQuery();
265 }
266  
267 SaveBanList(es);
268 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
269 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
270 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
271 }
272  
273 private void LoadBanList(EstateSettings es)
274 {
275 es.ClearBans();
276  
277 IDataReader r;
278  
279 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
280 {
281 cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID";
282 cmd.Parameters.AddWithValue(":EstateID", es.EstateID);
283  
284 r = cmd.ExecuteReader();
285 }
286  
287 while (r.Read())
288 {
289 EstateBan eb = new EstateBan();
290  
291 UUID uuid = new UUID();
292 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
293  
294 eb.BannedUserID = uuid;
295 eb.BannedHostAddress = "0.0.0.0";
296 eb.BannedHostIPMask = "0.0.0.0";
297 es.AddBan(eb);
298 }
299 r.Close();
300 }
301  
302 private void SaveBanList(EstateSettings es)
303 {
304 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
305 {
306 cmd.CommandText = "delete from estateban where EstateID = :EstateID";
307 cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
308  
309 cmd.ExecuteNonQuery();
310  
311 cmd.Parameters.Clear();
312  
313 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( :EstateID, :bannedUUID, '', '', '' )";
314  
315 foreach (EstateBan b in es.EstateBans)
316 {
317 cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
318 cmd.Parameters.AddWithValue(":bannedUUID", b.BannedUserID.ToString());
319  
320 cmd.ExecuteNonQuery();
321 cmd.Parameters.Clear();
322 }
323 }
324 }
325  
326 void SaveUUIDList(uint EstateID, string table, UUID[] data)
327 {
328 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
329 {
330 cmd.CommandText = "delete from "+table+" where EstateID = :EstateID";
331 cmd.Parameters.AddWithValue(":EstateID", EstateID.ToString());
332  
333 cmd.ExecuteNonQuery();
334  
335 cmd.Parameters.Clear();
336  
337 cmd.CommandText = "insert into "+table+" (EstateID, uuid) values ( :EstateID, :uuid )";
338  
339 foreach (UUID uuid in data)
340 {
341 cmd.Parameters.AddWithValue(":EstateID", EstateID.ToString());
342 cmd.Parameters.AddWithValue(":uuid", uuid.ToString());
343  
344 cmd.ExecuteNonQuery();
345 cmd.Parameters.Clear();
346 }
347 }
348 }
349  
350 UUID[] LoadUUIDList(uint EstateID, string table)
351 {
352 List<UUID> uuids = new List<UUID>();
353 IDataReader r;
354  
355 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
356 {
357 cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID";
358 cmd.Parameters.AddWithValue(":EstateID", EstateID);
359  
360 r = cmd.ExecuteReader();
361 }
362  
363 while (r.Read())
364 {
365 // EstateBan eb = new EstateBan();
366  
367 UUID uuid = new UUID();
368 UUID.TryParse(r["uuid"].ToString(), out uuid);
369  
370 uuids.Add(uuid);
371 }
372 r.Close();
373  
374 return uuids.ToArray();
375 }
376  
377 public EstateSettings LoadEstateSettings(int estateID)
378 {
379 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_settings where estate_settings.EstateID = :EstateID";
380  
381 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
382 {
383 cmd.CommandText = sql;
384 cmd.Parameters.AddWithValue(":EstateID", estateID.ToString());
385  
386 return DoLoad(cmd, UUID.Zero, false);
387 }
388 }
389  
390 public List<EstateSettings> LoadEstateSettingsAll()
391 {
392 List<EstateSettings> estateSettings = new List<EstateSettings>();
393  
394 List<int> estateIds = GetEstatesAll();
395 foreach (int estateId in estateIds)
396 estateSettings.Add(LoadEstateSettings(estateId));
397  
398 return estateSettings;
399 }
400  
401 public List<int> GetEstates(string search)
402 {
403 List<int> result = new List<int>();
404  
405 string sql = "select EstateID from estate_settings where estate_settings.EstateName = :EstateName";
406 IDataReader r;
407  
408 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
409 {
410 cmd.CommandText = sql;
411 cmd.Parameters.AddWithValue(":EstateName", search);
412  
413 r = cmd.ExecuteReader();
414 }
415  
416 while (r.Read())
417 {
418 result.Add(Convert.ToInt32(r["EstateID"]));
419 }
420 r.Close();
421  
422 return result;
423 }
424  
425 public List<int> GetEstatesAll()
426 {
427 List<int> result = new List<int>();
428  
429 string sql = "select EstateID from estate_settings";
430 IDataReader r;
431  
432 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
433 {
434 cmd.CommandText = sql;
435  
436 r = cmd.ExecuteReader();
437 }
438  
439 while (r.Read())
440 {
441 result.Add(Convert.ToInt32(r["EstateID"]));
442 }
443 r.Close();
444  
445 return result;
446 }
447  
448 public List<int> GetEstatesByOwner(UUID ownerID)
449 {
450 List<int> result = new List<int>();
451  
452 string sql = "select EstateID from estate_settings where estate_settings.EstateOwner = :EstateOwner";
453 IDataReader r;
454  
455 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
456 {
457 cmd.CommandText = sql;
458 cmd.Parameters.AddWithValue(":EstateOwner", ownerID);
459  
460 r = cmd.ExecuteReader();
461 }
462  
463 while (r.Read())
464 {
465 result.Add(Convert.ToInt32(r["EstateID"]));
466 }
467 r.Close();
468  
469 return result;
470 }
471  
472 public bool LinkRegion(UUID regionID, int estateID)
473 {
474 SqliteTransaction transaction = m_connection.BeginTransaction();
475  
476 // Delete any existing estate mapping for this region.
477 using(SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
478 {
479 cmd.CommandText = "delete from estate_map where RegionID = :RegionID";
480 cmd.Transaction = transaction;
481 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
482  
483 cmd.ExecuteNonQuery();
484 }
485  
486 using(SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
487 {
488 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
489 cmd.Transaction = transaction;
490 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
491 cmd.Parameters.AddWithValue(":EstateID", estateID.ToString());
492  
493 if (cmd.ExecuteNonQuery() == 0)
494 {
495 transaction.Rollback();
496 return false;
497 }
498 else
499 {
500 transaction.Commit();
501 return true;
502 }
503 }
504 }
505  
506 public List<UUID> GetRegions(int estateID)
507 {
508 return new List<UUID>();
509 }
510  
511 public bool DeleteEstate(int estateID)
512 {
513 return false;
514 }
515 }
516 }