aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL')
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs2
-rw-r--r--OpenSim/Data/MySQL/MySQLAvatarData.cs (renamed from OpenSim/Data/MySQL/MySQLSuperManager.cs)39
-rw-r--r--OpenSim/Data/MySQL/MySQLFriendsData.cs68
-rw-r--r--OpenSim/Data/MySQL/MySQLGenericTableHandler.cs10
-rw-r--r--OpenSim/Data/MySQL/MySQLGridData.cs422
-rw-r--r--OpenSim/Data/MySQL/MySQLLogData.cs166
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs1248
-rw-r--r--OpenSim/Data/MySQL/MySQLPresenceData.cs155
-rw-r--r--OpenSim/Data/MySQL/MySQLRegionData.cs26
-rw-r--r--OpenSim/Data/MySQL/MySQLUserAccountData.cs150
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs766
-rw-r--r--OpenSim/Data/MySQL/Resources/001_Avatar.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/001_Friends.sql9
-rw-r--r--OpenSim/Data/MySQL/Resources/001_FriendsStore.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/001_Presence.sql15
-rw-r--r--OpenSim/Data/MySQL/Resources/001_UserAccount.sql13
-rw-r--r--OpenSim/Data/MySQL/Resources/002_AuthStore.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/002_Friends.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/002_FriendsStore.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/002_Presence.sql7
-rw-r--r--OpenSim/Data/MySQL/Resources/002_UserAccount.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/003_AuthStore.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/003_Presence.sql6
-rw-r--r--OpenSim/Data/MySQL/Resources/003_UserAccount.sql9
-rw-r--r--OpenSim/Data/MySQL/Resources/004_UserAccount.sql8
-rw-r--r--OpenSim/Data/MySQL/Resources/005_GridStore.sql6
-rw-r--r--OpenSim/Data/MySQL/Resources/006_GridStore.sql5
-rw-r--r--OpenSim/Data/MySQL/Resources/007_GridStore.sql7
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLAssetTest.cs22
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLEstateTest.cs54
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLGridTest.cs94
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLInventoryTest.cs30
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLRegionTest.cs53
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLUserTest.cs85
34 files changed, 530 insertions, 2980 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index 666c22f..d55369a 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -122,7 +122,7 @@ namespace OpenSim.Data.MySQL
122 { 122 {
123 if (dbReader.Read()) 123 if (dbReader.Read())
124 { 124 {
125 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]); 125 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"], UUID.Zero.ToString());
126 asset.Data = (byte[])dbReader["data"]; 126 asset.Data = (byte[])dbReader["data"];
127 asset.Description = (string)dbReader["description"]; 127 asset.Description = (string)dbReader["description"];
128 128
diff --git a/OpenSim/Data/MySQL/MySQLSuperManager.cs b/OpenSim/Data/MySQL/MySQLAvatarData.cs
index c579432..5611302 100644
--- a/OpenSim/Data/MySQL/MySQLSuperManager.cs
+++ b/OpenSim/Data/MySQL/MySQLAvatarData.cs
@@ -25,28 +25,43 @@
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */ 26 */
27 27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
28using System.Threading; 32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using MySql.Data.MySqlClient;
29 37
30namespace OpenSim.Data.MySQL 38namespace OpenSim.Data.MySQL
31{ 39{
32 public class MySQLSuperManager 40 /// <summary>
41 /// A MySQL Interface for the Grid Server
42 /// </summary>
43 public class MySQLAvatarData : MySQLGenericTableHandler<AvatarBaseData>,
44 IAvatarData
33 { 45 {
34 public bool Locked; 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
35 private readonly Mutex m_lock = new Mutex(false);
36 public MySQLManager Manager;
37 public string Running;
38 47
39 public void GetLock() 48 public MySQLAvatarData(string connectionString, string realm) :
49 base(connectionString, realm, "Avatar")
40 { 50 {
41 Locked = true;
42 m_lock.WaitOne();
43 } 51 }
44 52
45 public void Release() 53 public bool Delete(UUID principalID, string name)
46 { 54 {
47 m_lock.ReleaseMutex(); 55 MySqlCommand cmd = new MySqlCommand();
48 Locked = false; 56
49 } 57 cmd.CommandText = String.Format("delete from {0} where `PrincipalID` = ?PrincipalID and `Name` = ?Name", m_Realm);
58 cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString());
59 cmd.Parameters.AddWithValue("?Name", name);
50 60
61 if (ExecuteNonQuery(cmd) > 0)
62 return true;
63
64 return false;
65 }
51 } 66 }
52} 67}
diff --git a/OpenSim/Data/MySQL/MySQLFriendsData.cs b/OpenSim/Data/MySQL/MySQLFriendsData.cs
new file mode 100644
index 0000000..663fad6
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLFriendsData.cs
@@ -0,0 +1,68 @@
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;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using MySql.Data.MySqlClient;
35
36namespace OpenSim.Data.MySQL
37{
38 public class MySqlFriendsData : MySQLGenericTableHandler<FriendsData>, IFriendsData
39 {
40 public MySqlFriendsData(string connectionString, string realm)
41 : base(connectionString, realm, "FriendsStore")
42 {
43 }
44
45 public bool Delete(UUID principalID, string friend)
46 {
47 MySqlCommand cmd = new MySqlCommand();
48
49 cmd.CommandText = String.Format("delete from {0} where PrincipalID = ?PrincipalID and Friend = ?Friend", m_Realm);
50 cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString());
51 cmd.Parameters.AddWithValue("?Friend", friend);
52
53 ExecuteNonQuery(cmd);
54
55 return true;
56 }
57
58 public FriendsData[] GetFriends(UUID principalID)
59 {
60 MySqlCommand cmd = new MySqlCommand();
61
62 cmd.CommandText = String.Format("select a.*,case when b.Flags is null then -1 else b.Flags end as TheirFlags from {0} as a left join {0} as b on a.PrincipalID = b.Friend and a.Friend = b.PrincipalID where a.PrincipalID = ?PrincipalID", m_Realm);
63 cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString());
64
65 return DoQuery(cmd);
66 }
67 }
68}
diff --git a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
index 7d3593c..b170dde 100644
--- a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
+++ b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
@@ -95,12 +95,12 @@ namespace OpenSim.Data.MySQL
95 } 95 }
96 } 96 }
97 97
98 public T[] Get(string field, string key) 98 public virtual T[] Get(string field, string key)
99 { 99 {
100 return Get(new string[] { field }, new string[] { key }); 100 return Get(new string[] { field }, new string[] { key });
101 } 101 }
102 102
103 public T[] Get(string[] fields, string[] keys) 103 public virtual T[] Get(string[] fields, string[] keys)
104 { 104 {
105 if (fields.Length != keys.Length) 105 if (fields.Length != keys.Length)
106 return new T[0]; 106 return new T[0];
@@ -194,7 +194,7 @@ namespace OpenSim.Data.MySQL
194 return result.ToArray(); 194 return result.ToArray();
195 } 195 }
196 196
197 public T[] Get(string where) 197 public virtual T[] Get(string where)
198 { 198 {
199 using (MySqlCommand cmd = new MySqlCommand()) 199 using (MySqlCommand cmd = new MySqlCommand())
200 { 200 {
@@ -208,7 +208,7 @@ namespace OpenSim.Data.MySQL
208 } 208 }
209 } 209 }
210 210
211 public bool Store(T row) 211 public virtual bool Store(T row)
212 { 212 {
213 using (MySqlCommand cmd = new MySqlCommand()) 213 using (MySqlCommand cmd = new MySqlCommand())
214 { 214 {
@@ -248,7 +248,7 @@ namespace OpenSim.Data.MySQL
248 } 248 }
249 } 249 }
250 250
251 public bool Delete(string field, string val) 251 public virtual bool Delete(string field, string val)
252 { 252 {
253 using (MySqlCommand cmd = new MySqlCommand()) 253 using (MySqlCommand cmd = new MySqlCommand())
254 { 254 {
diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs
deleted file mode 100644
index f4e7b85..0000000
--- a/OpenSim/Data/MySQL/MySQLGridData.cs
+++ /dev/null
@@ -1,422 +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.Reflection;
32using System.Threading;
33using log4net;
34using MySql.Data.MySqlClient;
35using OpenMetaverse;
36using OpenSim.Framework;
37
38namespace OpenSim.Data.MySQL
39{
40 /// <summary>
41 /// A MySQL Interface for the Grid Server
42 /// </summary>
43 public class MySQLGridData : GridDataBase
44 {
45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 private MySQLManager m_database;
48 private object m_dbLock = new object();
49 private string m_connectionString;
50
51 override public void Initialise()
52 {
53 m_log.Info("[MySQLGridData]: " + Name + " cannot be default-initialized!");
54 throw new PluginNotInitialisedException (Name);
55 }
56
57 /// <summary>
58 /// <para>Initialises Grid interface</para>
59 /// <para>
60 /// <list type="bullet">
61 /// <item>Loads and initialises the MySQL storage plugin</item>
62 /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
63 /// <item>Check for migration</item>
64 /// </list>
65 /// </para>
66 /// </summary>
67 /// <param name="connect">connect string.</param>
68 override public void Initialise(string connect)
69 {
70 m_connectionString = connect;
71 m_database = new MySQLManager(connect);
72
73 // This actually does the roll forward assembly stuff
74 Assembly assem = GetType().Assembly;
75
76 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
77 {
78 Migration m = new Migration(dbcon, assem, "GridStore");
79 m.Update();
80 }
81 }
82
83 /// <summary>
84 /// Shuts down the grid interface
85 /// </summary>
86 override public void Dispose()
87 {
88 }
89
90 /// <summary>
91 /// Returns the plugin name
92 /// </summary>
93 /// <returns>Plugin name</returns>
94 override public string Name
95 {
96 get { return "MySql OpenGridData"; }
97 }
98
99 /// <summary>
100 /// Returns the plugin version
101 /// </summary>
102 /// <returns>Plugin version</returns>
103 override public string Version
104 {
105 get { return "0.1"; }
106 }
107
108 /// <summary>
109 /// Returns all the specified region profiles within coordates -- coordinates are inclusive
110 /// </summary>
111 /// <param name="xmin">Minimum X coordinate</param>
112 /// <param name="ymin">Minimum Y coordinate</param>
113 /// <param name="xmax">Maximum X coordinate</param>
114 /// <param name="ymax">Maximum Y coordinate</param>
115 /// <returns>Array of sim profiles</returns>
116 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
117 {
118 try
119 {
120 Dictionary<string, object> param = new Dictionary<string, object>();
121 param["?xmin"] = xmin.ToString();
122 param["?ymin"] = ymin.ToString();
123 param["?xmax"] = xmax.ToString();
124 param["?ymax"] = ymax.ToString();
125
126 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
127 {
128 dbcon.Open();
129
130 using (IDbCommand result = m_database.Query(dbcon,
131 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
132 param))
133 {
134 using (IDataReader reader = result.ExecuteReader())
135 {
136 RegionProfileData row;
137
138 List<RegionProfileData> rows = new List<RegionProfileData>();
139
140 while ((row = m_database.readSimRow(reader)) != null)
141 rows.Add(row);
142
143 return rows.ToArray();
144 }
145 }
146 }
147 }
148 catch (Exception e)
149 {
150 m_log.Error(e.Message, e);
151 return null;
152 }
153 }
154
155 /// <summary>
156 /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
157 /// </summary>
158 /// <param name="name">The name to match against</param>
159 /// <param name="maxNum">Maximum number of profiles to return</param>
160 /// <returns>A list of sim profiles</returns>
161 override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum)
162 {
163 try
164 {
165 Dictionary<string, object> param = new Dictionary<string, object>();
166 param["?name"] = namePrefix + "%";
167
168 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
169 {
170 dbcon.Open();
171
172 using (IDbCommand result = m_database.Query(dbcon,
173 "SELECT * FROM regions WHERE regionName LIKE ?name",
174 param))
175 {
176 using (IDataReader reader = result.ExecuteReader())
177 {
178 RegionProfileData row;
179
180 List<RegionProfileData> rows = new List<RegionProfileData>();
181
182 while (rows.Count < maxNum && (row = m_database.readSimRow(reader)) != null)
183 rows.Add(row);
184
185 return rows;
186 }
187 }
188 }
189 }
190 catch (Exception e)
191 {
192 m_log.Error(e.Message, e);
193 return null;
194 }
195 }
196
197 /// <summary>
198 /// Returns a sim profile from it's location
199 /// </summary>
200 /// <param name="handle">Region location handle</param>
201 /// <returns>Sim profile</returns>
202 override public RegionProfileData GetProfileByHandle(ulong handle)
203 {
204 try
205 {
206 Dictionary<string, object> param = new Dictionary<string, object>();
207 param["?handle"] = handle.ToString();
208
209 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
210 {
211 dbcon.Open();
212
213 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE regionHandle = ?handle", param))
214 {
215 using (IDataReader reader = result.ExecuteReader())
216 {
217 RegionProfileData row = m_database.readSimRow(reader);
218 return row;
219 }
220 }
221 }
222 }
223 catch (Exception e)
224 {
225 m_log.Error(e.Message, e);
226 return null;
227 }
228 }
229
230 /// <summary>
231 /// Returns a sim profile from it's UUID
232 /// </summary>
233 /// <param name="uuid">The region UUID</param>
234 /// <returns>The sim profile</returns>
235 override public RegionProfileData GetProfileByUUID(UUID uuid)
236 {
237 try
238 {
239 Dictionary<string, object> param = new Dictionary<string, object>();
240 param["?uuid"] = uuid.ToString();
241
242 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
243 {
244 dbcon.Open();
245
246 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE uuid = ?uuid", param))
247 {
248 using (IDataReader reader = result.ExecuteReader())
249 {
250 RegionProfileData row = m_database.readSimRow(reader);
251 return row;
252 }
253 }
254 }
255 }
256 catch (Exception e)
257 {
258 m_log.Error(e.Message, e);
259 return null;
260 }
261 }
262
263 /// <summary>
264 /// Returns a sim profile from it's Region name string
265 /// </summary>
266 /// <returns>The sim profile</returns>
267 override public RegionProfileData GetProfileByString(string regionName)
268 {
269 if (regionName.Length > 2)
270 {
271 try
272 {
273 Dictionary<string, object> param = new Dictionary<string, object>();
274 // Add % because this is a like query.
275 param["?regionName"] = regionName + "%";
276
277 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
278 {
279 dbcon.Open();
280
281 // Order by statement will return shorter matches first. Only returns one record or no record.
282 using (IDbCommand result = m_database.Query(dbcon,
283 "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
284 param))
285 {
286 using (IDataReader reader = result.ExecuteReader())
287 {
288 RegionProfileData row = m_database.readSimRow(reader);
289 return row;
290 }
291 }
292 }
293 }
294 catch (Exception e)
295 {
296 m_log.Error(e.Message, e);
297 return null;
298 }
299 }
300
301 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
302 return null;
303 }
304
305 /// <summary>
306 /// Adds a new profile to the database
307 /// </summary>
308 /// <param name="profile">The profile to add</param>
309 /// <returns>Successful?</returns>
310 override public DataResponse StoreProfile(RegionProfileData profile)
311 {
312 try
313 {
314 if (m_database.insertRegion(profile))
315 return DataResponse.RESPONSE_OK;
316 else
317 return DataResponse.RESPONSE_ERROR;
318 }
319 catch
320 {
321 return DataResponse.RESPONSE_ERROR;
322 }
323 }
324
325 /// <summary>
326 /// Deletes a sim profile from the database
327 /// </summary>
328 /// <param name="uuid">the sim UUID</param>
329 /// <returns>Successful?</returns>
330 //public DataResponse DeleteProfile(RegionProfileData profile)
331 override public DataResponse DeleteProfile(string uuid)
332 {
333 try
334 {
335 if (m_database.deleteRegion(uuid))
336 return DataResponse.RESPONSE_OK;
337 else
338 return DataResponse.RESPONSE_ERROR;
339 }
340 catch
341 {
342 return DataResponse.RESPONSE_ERROR;
343 }
344 }
345
346 /// <summary>
347 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
348 /// </summary>
349 /// <param name="uuid">The UUID of the challenger</param>
350 /// <param name="handle">The attempted regionHandle of the challenger</param>
351 /// <param name="authkey">The secret</param>
352 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
353 override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
354 {
355 bool throwHissyFit = false; // Should be true by 1.0
356
357 if (throwHissyFit)
358 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
359
360 RegionProfileData data = GetProfileByUUID(uuid);
361
362 return (handle == data.regionHandle && authkey == data.regionSecret);
363 }
364
365 /// <summary>
366 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
367 /// </summary>
368 /// <remarks>This requires a security audit.</remarks>
369 /// <param name="uuid"></param>
370 /// <param name="handle"></param>
371 /// <param name="authhash"></param>
372 /// <param name="challenge"></param>
373 /// <returns></returns>
374 public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
375 {
376 // SHA512Managed HashProvider = new SHA512Managed();
377 // Encoding TextProvider = new UTF8Encoding();
378
379 // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
380 // byte[] hash = HashProvider.ComputeHash(stream);
381
382 return false;
383 }
384
385 /// <summary>
386 /// Adds a location reservation
387 /// </summary>
388 /// <param name="x">x coordinate</param>
389 /// <param name="y">y coordinate</param>
390 /// <returns></returns>
391 override public ReservationData GetReservationAtPoint(uint x, uint y)
392 {
393 try
394 {
395 Dictionary<string, object> param = new Dictionary<string, object>();
396 param["?x"] = x.ToString();
397 param["?y"] = y.ToString();
398
399 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
400 {
401 dbcon.Open();
402
403 using (IDbCommand result = m_database.Query(dbcon,
404 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
405 param))
406 {
407 using (IDataReader reader = result.ExecuteReader())
408 {
409 ReservationData row = m_database.readReservationRow(reader);
410 return row;
411 }
412 }
413 }
414 }
415 catch (Exception e)
416 {
417 m_log.Error(e.Message, e);
418 return null;
419 }
420 }
421 }
422}
diff --git a/OpenSim/Data/MySQL/MySQLLogData.cs b/OpenSim/Data/MySQL/MySQLLogData.cs
deleted file mode 100644
index 304883c..0000000
--- a/OpenSim/Data/MySQL/MySQLLogData.cs
+++ /dev/null
@@ -1,166 +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 */
27using System;
28using System.Collections.Generic;
29using System.Reflection;
30using log4net;
31using OpenSim.Framework;
32
33namespace OpenSim.Data.MySQL
34{
35 /// <summary>
36 /// An interface to the log database for MySQL
37 /// </summary>
38 internal class MySQLLogData : ILogDataPlugin
39 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41
42 /// <summary>
43 /// The database manager
44 /// </summary>
45 public MySQLManager database;
46
47 public void Initialise()
48 {
49 m_log.Info("[MySQLLogData]: " + Name + " cannot be default-initialized!");
50 throw new PluginNotInitialisedException (Name);
51 }
52
53 /// <summary>
54 /// Artificial constructor called when the plugin is loaded
55 /// Uses the obsolete mysql_connection.ini if connect string is empty.
56 /// </summary>
57 /// <param name="connect">connect string</param>
58 public void Initialise(string connect)
59 {
60 if (connect != String.Empty)
61 {
62 database = new MySQLManager(connect);
63 }
64 else
65 {
66 m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead");
67
68 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
69 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
70 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
71 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
72 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
73 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
74 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
75
76 database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
77 settingPooling, settingPort);
78 }
79
80 // This actually does the roll forward assembly stuff
81 Assembly assem = GetType().Assembly;
82
83 using (MySql.Data.MySqlClient.MySqlConnection dbcon = new MySql.Data.MySqlClient.MySqlConnection(connect))
84 {
85 dbcon.Open();
86
87 Migration m = new Migration(dbcon, assem, "LogStore");
88
89 // TODO: After rev 6000, remove this. People should have
90 // been rolled onto the new migration code by then.
91 TestTables(m);
92
93 m.Update();
94 }
95 }
96
97 /// <summary></summary>
98 /// <param name="m"></param>
99 private void TestTables(Migration m)
100 {
101 // under migrations, bail
102 if (m.Version > 0)
103 return;
104
105 Dictionary<string, string> tableList = new Dictionary<string, string>();
106 tableList["logs"] = null;
107 database.GetTableVersion(tableList);
108
109 // migrations will handle it
110 if (tableList["logs"] == null)
111 return;
112
113 // we have the table, so pretend like we did the first migration in the past
114 if (m.Version == 0)
115 m.Version = 1;
116 }
117
118 /// <summary>
119 /// Saves a log item to the database
120 /// </summary>
121 /// <param name="serverDaemon">The daemon triggering the event</param>
122 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
123 /// <param name="methodCall">The method call where the problem occured</param>
124 /// <param name="arguments">The arguments passed to the method</param>
125 /// <param name="priority">How critical is this?</param>
126 /// <param name="logMessage">The message to log</param>
127 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
128 string logMessage)
129 {
130 try
131 {
132 database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage);
133 }
134 catch
135 {
136 }
137 }
138
139 /// <summary>
140 /// Returns the name of this DB provider
141 /// </summary>
142 /// <returns>A string containing the DB provider name</returns>
143 public string Name
144 {
145 get { return "MySQL Logdata Interface";}
146 }
147
148 /// <summary>
149 /// Closes the database provider
150 /// </summary>
151 /// <remarks>do nothing</remarks>
152 public void Dispose()
153 {
154 // Do nothing.
155 }
156
157 /// <summary>
158 /// Returns the version of this DB provider
159 /// </summary>
160 /// <returns>A string containing the provider version</returns>
161 public string Version
162 {
163 get { return "0.1"; }
164 }
165 }
166}
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
deleted file mode 100644
index ace2027..0000000
--- a/OpenSim/Data/MySQL/MySQLManager.cs
+++ /dev/null
@@ -1,1248 +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;
30using System.Collections.Generic;
31using System.Data;
32using System.IO;
33using System.Reflection;
34using log4net;
35using MySql.Data.MySqlClient;
36using OpenMetaverse;
37using OpenSim.Framework;
38
39namespace OpenSim.Data.MySQL
40{
41 /// <summary>
42 /// A MySQL Database manager
43 /// </summary>
44 public class MySQLManager
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 /// <summary>
49 /// Connection string for ADO.net
50 /// </summary>
51 private string connectionString;
52
53 private object m_dbLock = new object();
54
55 private const string m_waitTimeoutSelect = "select @@wait_timeout";
56
57 /// <summary>
58 /// Wait timeout for our connection in ticks.
59 /// </summary>
60 private long m_waitTimeout;
61
62 /// <summary>
63 /// Make our storage of the timeout this amount smaller than it actually is, to give us a margin on long
64 /// running database operations.
65 /// </summary>
66 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
67
68 /// <summary>
69 /// Holds the last tick time that the connection was used.
70 /// </summary>
71 private long m_lastConnectionUse;
72
73 /// <summary>
74 /// Initialises and creates a new MySQL connection and maintains it.
75 /// </summary>
76 /// <param name="hostname">The MySQL server being connected to</param>
77 /// <param name="database">The name of the MySQL database being used</param>
78 /// <param name="username">The username logging into the database</param>
79 /// <param name="password">The password for the user logging in</param>
80 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param>
81 /// <param name="port">The MySQL server port</param>
82 public MySQLManager(string hostname, string database, string username, string password, string cpooling,
83 string port)
84 {
85 string s = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" +
86 username + ";Password=" + password + ";Pooling=" + cpooling + ";";
87
88 Initialise(s);
89 }
90
91 /// <summary>
92 /// Initialises and creates a new MySQL connection and maintains it.
93 /// </summary>
94 /// <param name="connect">connectionString</param>
95 public MySQLManager(String connect)
96 {
97 Initialise(connect);
98 }
99
100 /// <summary>
101 /// Initialises and creates a new MySQL connection and maintains it.
102 /// </summary>
103 /// <param name="connect">connectionString</param>
104 public void Initialise(String connect)
105 {
106 try
107 {
108 connectionString = connect;
109 //dbcon = new MySqlConnection(connectionString);
110
111 try
112 {
113 //dbcon.Open();
114 }
115 catch(Exception e)
116 {
117 throw new Exception("Connection error while using connection string ["+connectionString+"]", e);
118 }
119
120 m_log.Info("[MYSQL]: Connection established");
121 GetWaitTimeout();
122 }
123 catch (Exception e)
124 {
125 throw new Exception("Error initialising MySql Database: " + e.ToString());
126 }
127 }
128
129 /// <summary>
130 /// Get the wait_timeout value for our connection
131 /// </summary>
132 protected void GetWaitTimeout()
133 {
134 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
135 {
136 dbcon.Open();
137
138 using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
139 {
140 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
141 {
142 if (dbReader.Read())
143 {
144 m_waitTimeout
145 = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
146 }
147 }
148 }
149 }
150
151 m_lastConnectionUse = DateTime.Now.Ticks;
152
153 m_log.DebugFormat(
154 "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
155 }
156
157 public string ConnectionString
158 {
159 get { return connectionString; }
160 }
161
162 /// <summary>
163 /// Returns the version of this DB provider
164 /// </summary>
165 /// <returns>A string containing the DB provider</returns>
166 public string getVersion()
167 {
168 Module module = GetType().Module;
169 // string dllName = module.Assembly.ManifestModule.Name;
170 Version dllVersion = module.Assembly.GetName().Version;
171
172 return
173 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
174 dllVersion.Revision);
175 }
176
177 /// <summary>
178 /// Extract a named string resource from the embedded resources
179 /// </summary>
180 /// <param name="name">name of embedded resource</param>
181 /// <returns>string contained within the embedded resource</returns>
182 private string getResourceString(string name)
183 {
184 Assembly assem = GetType().Assembly;
185 string[] names = assem.GetManifestResourceNames();
186
187 foreach (string s in names)
188 {
189 if (s.EndsWith(name))
190 {
191 using (Stream resource = assem.GetManifestResourceStream(s))
192 {
193 using (StreamReader resourceReader = new StreamReader(resource))
194 {
195 string resourceString = resourceReader.ReadToEnd();
196 return resourceString;
197 }
198 }
199 }
200 }
201 throw new Exception(string.Format("Resource '{0}' was not found", name));
202 }
203
204 /// <summary>
205 /// Execute a SQL statement stored in a resource, as a string
206 /// </summary>
207 /// <param name="name">name of embedded resource</param>
208 public void ExecuteResourceSql(string name)
209 {
210 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
211 {
212 dbcon.Open();
213
214 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
215 cmd.ExecuteNonQuery();
216 }
217 }
218
219 /// <summary>
220 /// Execute a MySqlCommand
221 /// </summary>
222 /// <param name="sql">sql string to execute</param>
223 public void ExecuteSql(string sql)
224 {
225 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
226 {
227 dbcon.Open();
228
229 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
230 cmd.ExecuteNonQuery();
231 }
232 }
233
234 public void ExecuteParameterizedSql(string sql, Dictionary<string, string> parameters)
235 {
236 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
237 {
238 dbcon.Open();
239
240 MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
241 cmd.CommandText = sql;
242 foreach (KeyValuePair<string, string> param in parameters)
243 {
244 cmd.Parameters.AddWithValue(param.Key, param.Value);
245 }
246 cmd.ExecuteNonQuery();
247 }
248 }
249
250 /// <summary>
251 /// Given a list of tables, return the version of the tables, as seen in the database
252 /// </summary>
253 /// <param name="tableList"></param>
254 public void GetTableVersion(Dictionary<string, string> tableList)
255 {
256 lock (m_dbLock)
257 {
258 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
259 {
260 dbcon.Open();
261
262 using (MySqlCommand tablesCmd = new MySqlCommand(
263 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", dbcon))
264 {
265 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
266
267 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
268 {
269 while (tables.Read())
270 {
271 try
272 {
273 string tableName = (string)tables["TABLE_NAME"];
274 string comment = (string)tables["TABLE_COMMENT"];
275 if (tableList.ContainsKey(tableName))
276 {
277 tableList[tableName] = comment;
278 }
279 }
280 catch (Exception e)
281 {
282 m_log.Error(e.Message, e);
283 }
284 }
285 }
286 }
287 }
288 }
289 }
290
291 // TODO: at some time this code should be cleaned up
292
293 /// <summary>
294 /// Runs a query with protection against SQL Injection by using parameterised input.
295 /// </summary>
296 /// <param name="dbcon">Database connection</param>
297 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
298 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
299 /// <returns>A MySQL DB Command</returns>
300 public IDbCommand Query(MySqlConnection dbcon, string sql, Dictionary<string, object> parameters)
301 {
302 try
303 {
304 MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
305 dbcommand.CommandText = sql;
306 foreach (KeyValuePair<string, object> param in parameters)
307 {
308 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
309 }
310
311 return (IDbCommand)dbcommand;
312 }
313 catch (Exception e)
314 {
315 // Return null if it fails.
316 m_log.Error("Failed during Query generation: " + e.Message, e);
317 return null;
318 }
319 }
320
321 /// <summary>
322 /// Reads a region row from a database reader
323 /// </summary>
324 /// <param name="reader">An active database reader</param>
325 /// <returns>A region profile</returns>
326 public RegionProfileData readSimRow(IDataReader reader)
327 {
328 RegionProfileData retval = new RegionProfileData();
329
330 if (reader.Read())
331 {
332 // Region Main gotta-have-or-we-return-null parts
333 UInt64 tmp64;
334 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64))
335 {
336 return null;
337 }
338 else
339 {
340 retval.regionHandle = tmp64;
341 }
342 UUID tmp_uuid;
343 if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid))
344 {
345 return null;
346 }
347 else
348 {
349 retval.UUID = tmp_uuid;
350 }
351
352 // non-critical parts
353 retval.regionName = (string)reader["regionName"];
354 retval.originUUID = new UUID((string) reader["originUUID"]);
355
356 // Secrets
357 retval.regionRecvKey = (string) reader["regionRecvKey"];
358 retval.regionSecret = (string) reader["regionSecret"];
359 retval.regionSendKey = (string) reader["regionSendKey"];
360
361 // Region Server
362 retval.regionDataURI = (string) reader["regionDataURI"];
363 retval.regionOnline = false; // Needs to be pinged before this can be set.
364 retval.serverIP = (string) reader["serverIP"];
365 retval.serverPort = (uint) reader["serverPort"];
366 retval.serverURI = (string) reader["serverURI"];
367 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
368 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
369
370 // Location
371 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
372 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
373 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
374
375 // Neighbours - 0 = No Override
376 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
377 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
378 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
379 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
380
381 // Assets
382 retval.regionAssetURI = (string) reader["regionAssetURI"];
383 retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
384 retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
385
386 // Userserver
387 retval.regionUserURI = (string) reader["regionUserURI"];
388 retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
389 retval.regionUserSendKey = (string) reader["regionUserSendKey"];
390
391 // World Map Addition
392 UUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID);
393 UUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid);
394 retval.maturity = Convert.ToUInt32(reader["access"]);
395 }
396 else
397 {
398 return null;
399 }
400 return retval;
401 }
402
403 /// <summary>
404 /// Reads a reservation row from a database reader
405 /// </summary>
406 /// <param name="reader">An active database reader</param>
407 /// <returns>A reservation data object</returns>
408 public ReservationData readReservationRow(IDataReader reader)
409 {
410 ReservationData retval = new ReservationData();
411 if (reader.Read())
412 {
413 retval.gridRecvKey = (string) reader["gridRecvKey"];
414 retval.gridSendKey = (string) reader["gridSendKey"];
415 retval.reservationCompany = (string) reader["resCompany"];
416 retval.reservationMaxX = Convert.ToInt32(reader["resXMax"].ToString());
417 retval.reservationMaxY = Convert.ToInt32(reader["resYMax"].ToString());
418 retval.reservationMinX = Convert.ToInt32(reader["resXMin"].ToString());
419 retval.reservationMinY = Convert.ToInt32(reader["resYMin"].ToString());
420 retval.reservationName = (string) reader["resName"];
421 retval.status = Convert.ToInt32(reader["status"].ToString()) == 1;
422 UUID tmp;
423 UUID.TryParse((string) reader["userUUID"], out tmp);
424 retval.userUUID = tmp;
425 }
426 else
427 {
428 return null;
429 }
430 return retval;
431 }
432
433 /// <summary>
434 /// Reads an agent row from a database reader
435 /// </summary>
436 /// <param name="reader">An active database reader</param>
437 /// <returns>A user session agent</returns>
438 public UserAgentData readAgentRow(IDataReader reader)
439 {
440 UserAgentData retval = new UserAgentData();
441
442 if (reader.Read())
443 {
444 // Agent IDs
445 UUID tmp;
446 if (!UUID.TryParse((string)reader["UUID"], out tmp))
447 return null;
448 retval.ProfileID = tmp;
449
450 UUID.TryParse((string) reader["sessionID"], out tmp);
451 retval.SessionID = tmp;
452
453 UUID.TryParse((string)reader["secureSessionID"], out tmp);
454 retval.SecureSessionID = tmp;
455
456 // Agent Who?
457 retval.AgentIP = (string) reader["agentIP"];
458 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
459 retval.AgentOnline = Convert.ToBoolean(Convert.ToInt16(reader["agentOnline"].ToString()));
460
461 // Login/Logout times (UNIX Epoch)
462 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
463 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
464
465 // Current position
466 retval.Region = new UUID((string)reader["currentRegion"]);
467 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
468 Vector3 tmp_v;
469 Vector3.TryParse((string) reader["currentPos"], out tmp_v);
470 retval.Position = tmp_v;
471 Vector3.TryParse((string)reader["currentLookAt"], out tmp_v);
472 retval.LookAt = tmp_v;
473 }
474 else
475 {
476 return null;
477 }
478 return retval;
479 }
480
481 /// <summary>
482 /// Reads a user profile from an active data reader
483 /// </summary>
484 /// <param name="reader">An active database reader</param>
485 /// <returns>A user profile</returns>
486 public UserProfileData readUserRow(IDataReader reader)
487 {
488 UserProfileData retval = new UserProfileData();
489
490 if (reader.Read())
491 {
492 UUID id;
493 if (!UUID.TryParse((string)reader["UUID"], out id))
494 return null;
495
496 retval.ID = id;
497 retval.FirstName = (string) reader["username"];
498 retval.SurName = (string) reader["lastname"];
499 retval.Email = (reader.IsDBNull(reader.GetOrdinal("email"))) ? "" : (string) reader["email"];
500
501 retval.PasswordHash = (string) reader["passwordHash"];
502 retval.PasswordSalt = (string) reader["passwordSalt"];
503
504 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
505 retval.HomeLocation = new Vector3(
506 Convert.ToSingle(reader["homeLocationX"].ToString()),
507 Convert.ToSingle(reader["homeLocationY"].ToString()),
508 Convert.ToSingle(reader["homeLocationZ"].ToString()));
509 retval.HomeLookAt = new Vector3(
510 Convert.ToSingle(reader["homeLookAtX"].ToString()),
511 Convert.ToSingle(reader["homeLookAtY"].ToString()),
512 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
513
514 UUID regionID = UUID.Zero;
515 UUID.TryParse(reader["homeRegionID"].ToString(), out regionID); // it's ok if it doesn't work; just use UUID.Zero
516 retval.HomeRegionID = regionID;
517
518 retval.Created = Convert.ToInt32(reader["created"].ToString());
519 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
520
521 retval.UserInventoryURI = (string) reader["userInventoryURI"];
522 retval.UserAssetURI = (string) reader["userAssetURI"];
523
524 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
525 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
526
527 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
528 retval.AboutText = "";
529 else
530 retval.AboutText = (string) reader["profileAboutText"];
531
532 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
533 retval.FirstLifeAboutText = "";
534 else
535 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
536
537 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
538 retval.Image = UUID.Zero;
539 else {
540 UUID tmp;
541 UUID.TryParse((string)reader["profileImage"], out tmp);
542 retval.Image = tmp;
543 }
544
545 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
546 retval.FirstLifeImage = UUID.Zero;
547 else {
548 UUID tmp;
549 UUID.TryParse((string)reader["profileFirstImage"], out tmp);
550 retval.FirstLifeImage = tmp;
551 }
552
553 if (reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
554 {
555 retval.WebLoginKey = UUID.Zero;
556 }
557 else
558 {
559 UUID tmp;
560 UUID.TryParse((string)reader["webLoginKey"], out tmp);
561 retval.WebLoginKey = tmp;
562 }
563
564 retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString());
565 retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString());
566 if (reader.IsDBNull(reader.GetOrdinal("customType")))
567 retval.CustomType = "";
568 else
569 retval.CustomType = reader["customType"].ToString();
570
571 if (reader.IsDBNull(reader.GetOrdinal("partner")))
572 {
573 retval.Partner = UUID.Zero;
574 }
575 else
576 {
577 UUID tmp;
578 UUID.TryParse((string)reader["partner"], out tmp);
579 retval.Partner = tmp;
580 }
581 }
582 else
583 {
584 return null;
585 }
586 return retval;
587 }
588
589 /// <summary>
590 /// Reads an avatar appearence from an active data reader
591 /// </summary>
592 /// <param name="reader">An active database reader</param>
593 /// <returns>An avatar appearence</returns>
594 public AvatarAppearance readAppearanceRow(IDataReader reader)
595 {
596 AvatarAppearance appearance = null;
597 if (reader.Read())
598 {
599 appearance = new AvatarAppearance();
600 appearance.Owner = new UUID((string)reader["owner"]);
601 appearance.Serial = Convert.ToInt32(reader["serial"]);
602 appearance.VisualParams = (byte[])reader["visual_params"];
603 appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length);
604 appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]);
605 appearance.BodyItem = new UUID((string)reader["body_item"]);
606 appearance.BodyAsset = new UUID((string)reader["body_asset"]);
607 appearance.SkinItem = new UUID((string)reader["skin_item"]);
608 appearance.SkinAsset = new UUID((string)reader["skin_asset"]);
609 appearance.HairItem = new UUID((string)reader["hair_item"]);
610 appearance.HairAsset = new UUID((string)reader["hair_asset"]);
611 appearance.EyesItem = new UUID((string)reader["eyes_item"]);
612 appearance.EyesAsset = new UUID((string)reader["eyes_asset"]);
613 appearance.ShirtItem = new UUID((string)reader["shirt_item"]);
614 appearance.ShirtAsset = new UUID((string)reader["shirt_asset"]);
615 appearance.PantsItem = new UUID((string)reader["pants_item"]);
616 appearance.PantsAsset = new UUID((string)reader["pants_asset"]);
617 appearance.ShoesItem = new UUID((string)reader["shoes_item"]);
618 appearance.ShoesAsset = new UUID((string)reader["shoes_asset"]);
619 appearance.SocksItem = new UUID((string)reader["socks_item"]);
620 appearance.SocksAsset = new UUID((string)reader["socks_asset"]);
621 appearance.JacketItem = new UUID((string)reader["jacket_item"]);
622 appearance.JacketAsset = new UUID((string)reader["jacket_asset"]);
623 appearance.GlovesItem = new UUID((string)reader["gloves_item"]);
624 appearance.GlovesAsset = new UUID((string)reader["gloves_asset"]);
625 appearance.UnderShirtItem = new UUID((string)reader["undershirt_item"]);
626 appearance.UnderShirtAsset = new UUID((string)reader["undershirt_asset"]);
627 appearance.UnderPantsItem = new UUID((string)reader["underpants_item"]);
628 appearance.UnderPantsAsset = new UUID((string)reader["underpants_asset"]);
629 appearance.SkirtItem = new UUID((string)reader["skirt_item"]);
630 appearance.SkirtAsset = new UUID((string)reader["skirt_asset"]);
631 }
632 return appearance;
633 }
634
635 // Read attachment list from data reader
636 public Hashtable readAttachments(IDataReader r)
637 {
638 Hashtable ret = new Hashtable();
639
640 while (r.Read())
641 {
642 int attachpoint = Convert.ToInt32(r["attachpoint"]);
643 if (ret.ContainsKey(attachpoint))
644 continue;
645 Hashtable item = new Hashtable();
646 item.Add("item", r["item"].ToString());
647 item.Add("asset", r["asset"].ToString());
648
649 ret.Add(attachpoint, item);
650 }
651
652 return ret;
653 }
654
655 /// <summary>
656 /// Inserts a new row into the log database
657 /// </summary>
658 /// <param name="serverDaemon">The daemon which triggered this event</param>
659 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
660 /// <param name="methodCall">The method call where the problem occured</param>
661 /// <param name="arguments">The arguments passed to the method</param>
662 /// <param name="priority">How critical is this?</param>
663 /// <param name="logMessage">Extra message info</param>
664 /// <returns>Saved successfully?</returns>
665 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
666 string logMessage)
667 {
668 string sql = "INSERT INTO logs (`target`, `server`, `method`, `arguments`, `priority`, `message`) VALUES ";
669 sql += "(?target, ?server, ?method, ?arguments, ?priority, ?message)";
670
671 Dictionary<string, object> parameters = new Dictionary<string, object>();
672 parameters["?server"] = serverDaemon;
673 parameters["?target"] = target;
674 parameters["?method"] = methodCall;
675 parameters["?arguments"] = arguments;
676 parameters["?priority"] = priority.ToString();
677 parameters["?message"] = logMessage;
678
679 bool returnval = false;
680
681 try
682 {
683 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
684 {
685 dbcon.Open();
686
687 IDbCommand result = Query(dbcon, sql, parameters);
688
689 if (result.ExecuteNonQuery() == 1)
690 returnval = true;
691
692 result.Dispose();
693 }
694 }
695 catch (Exception e)
696 {
697 m_log.Error(e.ToString());
698 return false;
699 }
700
701 return returnval;
702 }
703
704 /// <summary>
705 /// Creates a new user and inserts it into the database
706 /// </summary>
707 /// <param name="uuid">User ID</param>
708 /// <param name="username">First part of the login</param>
709 /// <param name="lastname">Second part of the login</param>
710 /// <param name="passwordHash">A salted hash of the users password</param>
711 /// <param name="passwordSalt">The salt used for the password hash</param>
712 /// <param name="homeRegion">A regionHandle of the users home region</param>
713 /// <param name="homeRegionID"> The UUID of the user's home region</param>
714 /// <param name="homeLocX">Home region position vector</param>
715 /// <param name="homeLocY">Home region position vector</param>
716 /// <param name="homeLocZ">Home region position vector</param>
717 /// <param name="homeLookAtX">Home region 'look at' vector</param>
718 /// <param name="homeLookAtY">Home region 'look at' vector</param>
719 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
720 /// <param name="created">Account created (unix timestamp)</param>
721 /// <param name="lastlogin">Last login (unix timestamp)</param>
722 /// <param name="inventoryURI">Users inventory URI</param>
723 /// <param name="assetURI">Users asset URI</param>
724 /// <param name="canDoMask">I can do mask</param>
725 /// <param name="wantDoMask">I want to do mask</param>
726 /// <param name="aboutText">Profile text</param>
727 /// <param name="firstText">Firstlife text</param>
728 /// <param name="profileImage">UUID for profile image</param>
729 /// <param name="firstImage">UUID for firstlife image</param>
730 /// <param name="webLoginKey">Ignored</param>
731 /// <returns>Success?</returns>
732 public bool insertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
733 string passwordSalt, UInt64 homeRegion, UUID homeRegionID, float homeLocX, float homeLocY, float homeLocZ,
734 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
735 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
736 string aboutText, string firstText,
737 UUID profileImage, UUID firstImage, UUID webLoginKey, int userFlags, int godLevel, string customType, UUID partner)
738 {
739 m_log.Debug("[MySQLManager]: Creating profile for \"" + username + " " + lastname + "\" (" + uuid + ")");
740 string sql =
741 "INSERT INTO users (`UUID`, `username`, `lastname`, `email`, `passwordHash`, `passwordSalt`, `homeRegion`, `homeRegionID`, ";
742 sql +=
743 "`homeLocationX`, `homeLocationY`, `homeLocationZ`, `homeLookAtX`, `homeLookAtY`, `homeLookAtZ`, `created`, ";
744 sql +=
745 "`lastLogin`, `userInventoryURI`, `userAssetURI`, `profileCanDoMask`, `profileWantDoMask`, `profileAboutText`, ";
746 sql += "`profileFirstText`, `profileImage`, `profileFirstImage`, `webLoginKey`, `userFlags`, `godLevel`, `customType`, `partner`) VALUES ";
747
748 sql += "(?UUID, ?username, ?lastname, ?email, ?passwordHash, ?passwordSalt, ?homeRegion, ?homeRegionID, ";
749 sql +=
750 "?homeLocationX, ?homeLocationY, ?homeLocationZ, ?homeLookAtX, ?homeLookAtY, ?homeLookAtZ, ?created, ";
751 sql +=
752 "?lastLogin, ?userInventoryURI, ?userAssetURI, ?profileCanDoMask, ?profileWantDoMask, ?profileAboutText, ";
753 sql += "?profileFirstText, ?profileImage, ?profileFirstImage, ?webLoginKey, ?userFlags, ?godLevel, ?customType, ?partner)";
754
755 Dictionary<string, object> parameters = new Dictionary<string, object>();
756 parameters["?UUID"] = uuid.ToString();
757 parameters["?username"] = username;
758 parameters["?lastname"] = lastname;
759 parameters["?email"] = email;
760 parameters["?passwordHash"] = passwordHash;
761 parameters["?passwordSalt"] = passwordSalt;
762 parameters["?homeRegion"] = homeRegion;
763 parameters["?homeRegionID"] = homeRegionID.ToString();
764 parameters["?homeLocationX"] = homeLocX;
765 parameters["?homeLocationY"] = homeLocY;
766 parameters["?homeLocationZ"] = homeLocZ;
767 parameters["?homeLookAtX"] = homeLookAtX;
768 parameters["?homeLookAtY"] = homeLookAtY;
769 parameters["?homeLookAtZ"] = homeLookAtZ;
770 parameters["?created"] = created;
771 parameters["?lastLogin"] = lastlogin;
772 parameters["?userInventoryURI"] = inventoryURI;
773 parameters["?userAssetURI"] = assetURI;
774 parameters["?profileCanDoMask"] = canDoMask;
775 parameters["?profileWantDoMask"] = wantDoMask;
776 parameters["?profileAboutText"] = aboutText;
777 parameters["?profileFirstText"] = firstText;
778 parameters["?profileImage"] = profileImage.ToString();
779 parameters["?profileFirstImage"] = firstImage.ToString();
780 parameters["?webLoginKey"] = webLoginKey.ToString();
781 parameters["?userFlags"] = userFlags;
782 parameters["?godLevel"] = godLevel;
783 parameters["?customType"] = customType == null ? "" : customType;
784 parameters["?partner"] = partner.ToString();
785 bool returnval = false;
786
787 try
788 {
789 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
790 {
791 dbcon.Open();
792
793 IDbCommand result = Query(dbcon, sql, parameters);
794
795 if (result.ExecuteNonQuery() == 1)
796 returnval = true;
797
798 result.Dispose();
799 }
800 }
801 catch (Exception e)
802 {
803 m_log.Error(e.ToString());
804 return false;
805 }
806
807 //m_log.Debug("[MySQLManager]: Fetch user retval == " + returnval.ToString());
808 return returnval;
809 }
810
811 /// <summary>
812 /// Update user data into the database where User ID = uuid
813 /// </summary>
814 /// <param name="uuid">User ID</param>
815 /// <param name="username">First part of the login</param>
816 /// <param name="lastname">Second part of the login</param>
817 /// <param name="passwordHash">A salted hash of the users password</param>
818 /// <param name="passwordSalt">The salt used for the password hash</param>
819 /// <param name="homeRegion">A regionHandle of the users home region</param>
820 /// <param name="homeLocX">Home region position vector</param>
821 /// <param name="homeLocY">Home region position vector</param>
822 /// <param name="homeLocZ">Home region position vector</param>
823 /// <param name="homeLookAtX">Home region 'look at' vector</param>
824 /// <param name="homeLookAtY">Home region 'look at' vector</param>
825 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
826 /// <param name="created">Account created (unix timestamp)</param>
827 /// <param name="lastlogin">Last login (unix timestamp)</param>
828 /// <param name="inventoryURI">Users inventory URI</param>
829 /// <param name="assetURI">Users asset URI</param>
830 /// <param name="canDoMask">I can do mask</param>
831 /// <param name="wantDoMask">I want to do mask</param>
832 /// <param name="aboutText">Profile text</param>
833 /// <param name="firstText">Firstlife text</param>
834 /// <param name="profileImage">UUID for profile image</param>
835 /// <param name="firstImage">UUID for firstlife image</param>
836 /// <param name="webLoginKey">UUID for weblogin Key</param>
837 /// <returns>Success?</returns>
838 public bool updateUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
839 string passwordSalt, UInt64 homeRegion, UUID homeRegionID, float homeLocX, float homeLocY, float homeLocZ,
840 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
841 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
842 string aboutText, string firstText,
843 UUID profileImage, UUID firstImage, UUID webLoginKey, int userFlags, int godLevel, string customType, UUID partner)
844 {
845 string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname, `email` = ?email ";
846 sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , ";
847 sql += "`homeRegion` = ?homeRegion , `homeRegionID` = ?homeRegionID, `homeLocationX` = ?homeLocationX , ";
848 sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , ";
849 sql += "`homeLookAtX` = ?homeLookAtX , `homeLookAtY` = ?homeLookAtY , ";
850 sql += "`homeLookAtZ` = ?homeLookAtZ , `created` = ?created , `lastLogin` = ?lastLogin , ";
851 sql += "`userInventoryURI` = ?userInventoryURI , `userAssetURI` = ?userAssetURI , ";
852 sql += "`profileCanDoMask` = ?profileCanDoMask , `profileWantDoMask` = ?profileWantDoMask , ";
853 sql += "`profileAboutText` = ?profileAboutText , `profileFirstText` = ?profileFirstText, ";
854 sql += "`profileImage` = ?profileImage , `profileFirstImage` = ?profileFirstImage , ";
855 sql += "`userFlags` = ?userFlags , `godLevel` = ?godLevel , ";
856 sql += "`customType` = ?customType , `partner` = ?partner , ";
857 sql += "`webLoginKey` = ?webLoginKey WHERE UUID = ?UUID";
858
859 Dictionary<string, object> parameters = new Dictionary<string, object>();
860 parameters["?UUID"] = uuid.ToString();
861 parameters["?username"] = username;
862 parameters["?lastname"] = lastname;
863 parameters["?email"] = email;
864 parameters["?passwordHash"] = passwordHash;
865 parameters["?passwordSalt"] = passwordSalt;
866 parameters["?homeRegion"] = homeRegion;
867 parameters["?homeRegionID"] = homeRegionID.ToString();
868 parameters["?homeLocationX"] = homeLocX;
869 parameters["?homeLocationY"] = homeLocY;
870 parameters["?homeLocationZ"] = homeLocZ;
871 parameters["?homeLookAtX"] = homeLookAtX;
872 parameters["?homeLookAtY"] = homeLookAtY;
873 parameters["?homeLookAtZ"] = homeLookAtZ;
874 parameters["?created"] = created;
875 parameters["?lastLogin"] = lastlogin;
876 parameters["?userInventoryURI"] = inventoryURI;
877 parameters["?userAssetURI"] = assetURI;
878 parameters["?profileCanDoMask"] = canDoMask;
879 parameters["?profileWantDoMask"] = wantDoMask;
880 parameters["?profileAboutText"] = aboutText;
881 parameters["?profileFirstText"] = firstText;
882 parameters["?profileImage"] = profileImage.ToString();
883 parameters["?profileFirstImage"] = firstImage.ToString();
884 parameters["?webLoginKey"] = webLoginKey.ToString();
885 parameters["?userFlags"] = userFlags;
886 parameters["?godLevel"] = godLevel;
887 parameters["?customType"] = customType == null ? "" : customType;
888 parameters["?partner"] = partner.ToString();
889
890 bool returnval = false;
891 try
892 {
893 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
894 {
895 dbcon.Open();
896
897 IDbCommand result = Query(dbcon, sql, parameters);
898
899 if (result.ExecuteNonQuery() == 1)
900 returnval = true;
901
902 result.Dispose();
903 }
904 }
905 catch (Exception e)
906 {
907 m_log.Error(e.ToString());
908 return false;
909 }
910
911 //m_log.Debug("[MySQLManager]: update user retval == " + returnval.ToString());
912 return returnval;
913 }
914
915 /// <summary>
916 /// Inserts a new region into the database
917 /// </summary>
918 /// <param name="regiondata">The region to insert</param>
919 /// <returns>Success?</returns>
920 public bool insertRegion(RegionProfileData regiondata)
921 {
922 bool GRID_ONLY_UPDATE_NECESSARY_DATA = false;
923
924 string sql = String.Empty;
925 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
926 {
927 sql += "INSERT INTO ";
928 }
929 else
930 {
931 sql += "REPLACE INTO ";
932 }
933
934 sql += "regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
935 sql +=
936 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
937
938 // part of an initial brutish effort to provide accurate information (as per the xml region spec)
939 // wrt the ownership of a given region
940 // the (very bad) assumption is that this value is being read and handled inconsistently or
941 // not at all. Current strategy is to put the code in place to support the validity of this information
942 // and to roll forward debugging any issues from that point
943 //
944 // this particular section of the mod attempts to implement the commit of a supplied value
945 // server for the UUID of the region's owner (master avatar). It consists of the addition of the column and value to the relevant sql,
946 // as well as the related parameterization
947 sql +=
948 "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID, access) VALUES ";
949
950 sql += "(?regionHandle, ?regionName, ?uuid, ?regionRecvKey, ?regionSecret, ?regionSendKey, ?regionDataURI, ";
951 sql +=
952 "?serverIP, ?serverPort, ?serverURI, ?locX, ?locY, ?locZ, ?eastOverrideHandle, ?westOverrideHandle, ?southOverrideHandle, ?northOverrideHandle, ?regionAssetURI, ?regionAssetRecvKey, ";
953 sql +=
954 "?regionAssetSendKey, ?regionUserURI, ?regionUserRecvKey, ?regionUserSendKey, ?regionMapTexture, ?serverHttpPort, ?serverRemotingPort, ?owner_uuid, ?originUUID, ?access)";
955
956 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
957 {
958 sql += "ON DUPLICATE KEY UPDATE serverIP = ?serverIP, serverPort = ?serverPort, serverURI = ?serverURI, owner_uuid - ?owner_uuid;";
959 }
960 else
961 {
962 sql += ";";
963 }
964
965 Dictionary<string, object> parameters = new Dictionary<string, object>();
966
967 parameters["?regionHandle"] = regiondata.regionHandle.ToString();
968 parameters["?regionName"] = regiondata.regionName.ToString();
969 parameters["?uuid"] = regiondata.UUID.ToString();
970 parameters["?regionRecvKey"] = regiondata.regionRecvKey.ToString();
971 parameters["?regionSecret"] = regiondata.regionSecret.ToString();
972 parameters["?regionSendKey"] = regiondata.regionSendKey.ToString();
973 parameters["?regionDataURI"] = regiondata.regionDataURI.ToString();
974 parameters["?serverIP"] = regiondata.serverIP.ToString();
975 parameters["?serverPort"] = regiondata.serverPort.ToString();
976 parameters["?serverURI"] = regiondata.serverURI.ToString();
977 parameters["?locX"] = regiondata.regionLocX.ToString();
978 parameters["?locY"] = regiondata.regionLocY.ToString();
979 parameters["?locZ"] = regiondata.regionLocZ.ToString();
980 parameters["?eastOverrideHandle"] = regiondata.regionEastOverrideHandle.ToString();
981 parameters["?westOverrideHandle"] = regiondata.regionWestOverrideHandle.ToString();
982 parameters["?northOverrideHandle"] = regiondata.regionNorthOverrideHandle.ToString();
983 parameters["?southOverrideHandle"] = regiondata.regionSouthOverrideHandle.ToString();
984 parameters["?regionAssetURI"] = regiondata.regionAssetURI.ToString();
985 parameters["?regionAssetRecvKey"] = regiondata.regionAssetRecvKey.ToString();
986 parameters["?regionAssetSendKey"] = regiondata.regionAssetSendKey.ToString();
987 parameters["?regionUserURI"] = regiondata.regionUserURI.ToString();
988 parameters["?regionUserRecvKey"] = regiondata.regionUserRecvKey.ToString();
989 parameters["?regionUserSendKey"] = regiondata.regionUserSendKey.ToString();
990 parameters["?regionMapTexture"] = regiondata.regionMapTextureID.ToString();
991 parameters["?serverHttpPort"] = regiondata.httpPort.ToString();
992 parameters["?serverRemotingPort"] = regiondata.remotingPort.ToString();
993 parameters["?owner_uuid"] = regiondata.owner_uuid.ToString();
994 parameters["?originUUID"] = regiondata.originUUID.ToString();
995 parameters["?access"] = regiondata.maturity.ToString();
996
997 bool returnval = false;
998
999 try
1000 {
1001 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1002 {
1003 dbcon.Open();
1004
1005 IDbCommand result = Query(dbcon, sql, parameters);
1006
1007 // int x;
1008 // if ((x = result.ExecuteNonQuery()) > 0)
1009 // {
1010 // returnval = true;
1011 // }
1012 if (result.ExecuteNonQuery() > 0)
1013 {
1014 returnval = true;
1015 }
1016 result.Dispose();
1017 }
1018 }
1019 catch (Exception e)
1020 {
1021 m_log.Error(e.ToString());
1022 return false;
1023 }
1024
1025 return returnval;
1026 }
1027
1028 /// <summary>
1029 /// Delete a region from the database
1030 /// </summary>
1031 /// <param name="uuid">The region to delete</param>
1032 /// <returns>Success?</returns>
1033 //public bool deleteRegion(RegionProfileData regiondata)
1034 public bool deleteRegion(string uuid)
1035 {
1036 bool returnval = false;
1037
1038 string sql = "DELETE FROM regions WHERE uuid = ?uuid;";
1039
1040 Dictionary<string, object> parameters = new Dictionary<string, object>();
1041
1042 try
1043 {
1044 parameters["?uuid"] = uuid;
1045
1046 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1047 {
1048 dbcon.Open();
1049
1050 IDbCommand result = Query(dbcon, sql, parameters);
1051
1052 // int x;
1053 // if ((x = result.ExecuteNonQuery()) > 0)
1054 // {
1055 // returnval = true;
1056 // }
1057 if (result.ExecuteNonQuery() > 0)
1058 {
1059 returnval = true;
1060 }
1061 result.Dispose();
1062 }
1063 }
1064 catch (Exception e)
1065 {
1066 m_log.Error(e.ToString());
1067 return false;
1068 }
1069
1070 return returnval;
1071 }
1072
1073 /// <summary>
1074 /// Creates a new agent and inserts it into the database
1075 /// </summary>
1076 /// <param name="agentdata">The agent data to be inserted</param>
1077 /// <returns>Success?</returns>
1078 public bool insertAgentRow(UserAgentData agentdata)
1079 {
1080 string sql = String.Empty;
1081 sql += "REPLACE INTO ";
1082 sql += "agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos, currentLookAt) VALUES ";
1083 sql += "(?UUID, ?sessionID, ?secureSessionID, ?agentIP, ?agentPort, ?agentOnline, ?loginTime, ?logoutTime, ?currentRegion, ?currentHandle, ?currentPos, ?currentLookAt);";
1084 Dictionary<string, object> parameters = new Dictionary<string, object>();
1085
1086 parameters["?UUID"] = agentdata.ProfileID.ToString();
1087 parameters["?sessionID"] = agentdata.SessionID.ToString();
1088 parameters["?secureSessionID"] = agentdata.SecureSessionID.ToString();
1089 parameters["?agentIP"] = agentdata.AgentIP.ToString();
1090 parameters["?agentPort"] = agentdata.AgentPort.ToString();
1091 parameters["?agentOnline"] = (agentdata.AgentOnline == true) ? "1" : "0";
1092 parameters["?loginTime"] = agentdata.LoginTime.ToString();
1093 parameters["?logoutTime"] = agentdata.LogoutTime.ToString();
1094 parameters["?currentRegion"] = agentdata.Region.ToString();
1095 parameters["?currentHandle"] = agentdata.Handle.ToString();
1096 parameters["?currentPos"] = "<" + (agentdata.Position.X).ToString().Replace(",", ".") + "," + (agentdata.Position.Y).ToString().Replace(",", ".") + "," + (agentdata.Position.Z).ToString().Replace(",", ".") + ">";
1097 parameters["?currentLookAt"] = "<" + (agentdata.LookAt.X).ToString().Replace(",", ".") + "," + (agentdata.LookAt.Y).ToString().Replace(",", ".") + "," + (agentdata.LookAt.Z).ToString().Replace(",", ".") + ">";
1098
1099 bool returnval = false;
1100
1101 try
1102 {
1103 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1104 {
1105 dbcon.Open();
1106
1107 IDbCommand result = Query(dbcon, sql, parameters);
1108
1109 // int x;
1110 // if ((x = result.ExecuteNonQuery()) > 0)
1111 // {
1112 // returnval = true;
1113 // }
1114 if (result.ExecuteNonQuery() > 0)
1115 {
1116 returnval = true;
1117 }
1118 result.Dispose();
1119 }
1120 }
1121 catch (Exception e)
1122 {
1123 m_log.Error(e.ToString());
1124 return false;
1125 }
1126
1127 return returnval;
1128 }
1129
1130 /// <summary>
1131 /// Create (or replace if existing) an avatar appearence
1132 /// </summary>
1133 /// <param name="appearance"></param>
1134 /// <returns>Succes?</returns>
1135 public bool insertAppearanceRow(AvatarAppearance appearance)
1136 {
1137 string sql = String.Empty;
1138 sql += "REPLACE INTO ";
1139 sql += "avatarappearance (owner, serial, visual_params, texture, avatar_height, ";
1140 sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, ";
1141 sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, ";
1142 sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, ";
1143 sql += "skirt_item, skirt_asset) values (";
1144 sql += "?owner, ?serial, ?visual_params, ?texture, ?avatar_height, ";
1145 sql += "?body_item, ?body_asset, ?skin_item, ?skin_asset, ?hair_item, ?hair_asset, ?eyes_item, ?eyes_asset, ";
1146 sql += "?shirt_item, ?shirt_asset, ?pants_item, ?pants_asset, ?shoes_item, ?shoes_asset, ?socks_item, ?socks_asset, ";
1147 sql += "?jacket_item, ?jacket_asset, ?gloves_item, ?gloves_asset, ?undershirt_item, ?undershirt_asset, ?underpants_item, ?underpants_asset, ";
1148 sql += "?skirt_item, ?skirt_asset)";
1149
1150 bool returnval = false;
1151
1152 // we want to send in byte data, which means we can't just pass down strings
1153 try
1154 {
1155 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1156 {
1157 dbcon.Open();
1158
1159 using (MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand())
1160 {
1161 cmd.CommandText = sql;
1162 cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString());
1163 cmd.Parameters.AddWithValue("?serial", appearance.Serial);
1164 cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams);
1165 cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes());
1166 cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight);
1167 cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString());
1168 cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString());
1169 cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString());
1170 cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString());
1171 cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString());
1172 cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString());
1173 cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString());
1174 cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString());
1175 cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString());
1176 cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString());
1177 cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString());
1178 cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString());
1179 cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString());
1180 cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString());
1181 cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString());
1182 cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString());
1183 cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString());
1184 cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString());
1185 cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString());
1186 cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString());
1187 cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString());
1188 cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString());
1189 cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString());
1190 cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString());
1191 cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString());
1192 cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString());
1193
1194 if (cmd.ExecuteNonQuery() > 0)
1195 returnval = true;
1196 }
1197 }
1198 }
1199 catch (Exception e)
1200 {
1201 m_log.Error(e.ToString());
1202 return false;
1203 }
1204
1205 return returnval;
1206
1207 }
1208
1209 public void writeAttachments(UUID agentID, Hashtable data)
1210 {
1211 string sql = "delete from avatarattachments where UUID = ?uuid";
1212
1213 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1214 {
1215 dbcon.Open();
1216
1217 MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
1218 cmd.CommandText = sql;
1219 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
1220
1221 cmd.ExecuteNonQuery();
1222
1223 if (data == null)
1224 return;
1225
1226 sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)";
1227
1228 cmd = (MySqlCommand)dbcon.CreateCommand();
1229 cmd.CommandText = sql;
1230
1231 foreach (DictionaryEntry e in data)
1232 {
1233 int attachpoint = Convert.ToInt32(e.Key);
1234
1235 Hashtable item = (Hashtable)e.Value;
1236
1237 cmd.Parameters.Clear();
1238 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
1239 cmd.Parameters.AddWithValue("?attachpoint", attachpoint);
1240 cmd.Parameters.AddWithValue("?item", item["item"]);
1241 cmd.Parameters.AddWithValue("?asset", item["asset"]);
1242
1243 cmd.ExecuteNonQuery();
1244 }
1245 }
1246 }
1247 }
1248}
diff --git a/OpenSim/Data/MySQL/MySQLPresenceData.cs b/OpenSim/Data/MySQL/MySQLPresenceData.cs
new file mode 100644
index 0000000..fcbe3d6
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLPresenceData.cs
@@ -0,0 +1,155 @@
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.Reflection;
32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using MySql.Data.MySqlClient;
37
38namespace OpenSim.Data.MySQL
39{
40 /// <summary>
41 /// A MySQL Interface for the Grid Server
42 /// </summary>
43 public class MySQLPresenceData : MySQLGenericTableHandler<PresenceData>,
44 IPresenceData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public MySQLPresenceData(string connectionString, string realm) :
49 base(connectionString, realm, "Presence")
50 {
51 }
52
53 public PresenceData Get(UUID sessionID)
54 {
55 PresenceData[] ret = Get("SessionID",
56 sessionID.ToString());
57
58 if (ret.Length == 0)
59 return null;
60
61 return ret[0];
62 }
63
64 public void LogoutRegionAgents(UUID regionID)
65 {
66 MySqlCommand cmd = new MySqlCommand();
67
68 cmd.CommandText = String.Format("update {0} set Online='false' where `RegionID`=?RegionID", m_Realm);
69
70 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
71
72 ExecuteNonQuery(cmd);
73 }
74
75 public bool ReportAgent(UUID sessionID, UUID regionID, string position,
76 string lookAt)
77 {
78 PresenceData[] pd = Get("SessionID", sessionID.ToString());
79 if (pd.Length == 0)
80 return false;
81
82 MySqlCommand cmd = new MySqlCommand();
83
84 cmd.CommandText = String.Format("update {0} set RegionID=?RegionID, Position=?Position, LookAt=?LookAt, Online='true' where `SessionID`=?SessionID", m_Realm);
85
86 cmd.Parameters.AddWithValue("?SessionID", sessionID.ToString());
87 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
88 cmd.Parameters.AddWithValue("?Position", position.ToString());
89 cmd.Parameters.AddWithValue("?LookAt", lookAt.ToString());
90
91 if (ExecuteNonQuery(cmd) == 0)
92 return false;
93
94 return true;
95 }
96
97 public bool SetHomeLocation(string userID, UUID regionID, Vector3 position, Vector3 lookAt)
98 {
99 PresenceData[] pd = Get("UserID", userID);
100 if (pd.Length == 0)
101 return false;
102
103 MySqlCommand cmd = new MySqlCommand();
104
105 cmd.CommandText = String.Format("update {0} set HomeRegionID=?HomeRegionID, HomePosition=?HomePosition, HomeLookAt=?HomeLookAt where UserID=?UserID", m_Realm);
106
107 cmd.Parameters.AddWithValue("?UserID", userID);
108 cmd.Parameters.AddWithValue("?HomeRegionID", regionID.ToString());
109 cmd.Parameters.AddWithValue("?HomePosition", position);
110 cmd.Parameters.AddWithValue("?HomeLookAt", lookAt);
111
112 if (ExecuteNonQuery(cmd) == 0)
113 return false;
114
115 return true;
116 }
117
118 public void Prune(string userID)
119 {
120 MySqlCommand cmd = new MySqlCommand();
121
122 cmd.CommandText = String.Format("select * from {0} where UserID=?UserID", m_Realm);
123
124 cmd.Parameters.AddWithValue("?UserID", userID);
125;
126 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
127 {
128 dbcon.Open();
129
130 cmd.Connection = dbcon;
131
132 using (IDataReader reader = cmd.ExecuteReader())
133 {
134
135 List<UUID> deleteSessions = new List<UUID>();
136 int online = 0;
137
138 while(reader.Read())
139 {
140 if (bool.Parse(reader["Online"].ToString()))
141 online++;
142 else
143 deleteSessions.Add(new UUID(reader["SessionID"].ToString()));
144 }
145
146 if (online == 0 && deleteSessions.Count > 0)
147 deleteSessions.RemoveAt(0);
148
149 foreach (UUID s in deleteSessions)
150 Delete("SessionID", s.ToString());
151 }
152 }
153 }
154 }
155}
diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs
index a1a08b1..aa9a104 100644
--- a/OpenSim/Data/MySQL/MySQLRegionData.cs
+++ b/OpenSim/Data/MySQL/MySQLRegionData.cs
@@ -283,5 +283,31 @@ namespace OpenSim.Data.MySQL
283 283
284 return false; 284 return false;
285 } 285 }
286 public List<RegionData> GetDefaultRegions(UUID scopeID)
287 {
288 string command = "select * from `"+m_Realm+"` where (flags & 1) <> 0";
289 if (scopeID != UUID.Zero)
290 command += " and ScopeID = ?scopeID";
291
292 MySqlCommand cmd = new MySqlCommand(command);
293
294 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
295
296 return RunCommand(cmd);
297 }
298
299 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
300 {
301 string command = "select * from `"+m_Realm+"` where (flags & 2) <> 0";
302 if (scopeID != UUID.Zero)
303 command += " and ScopeID = ?scopeID";
304
305 MySqlCommand cmd = new MySqlCommand(command);
306
307 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
308
309 // TODO: distance-sort results
310 return RunCommand(cmd);
311 }
286 } 312 }
287} 313}
diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
index 3cb0010..aa69d68 100644
--- a/OpenSim/Data/MySQL/MySQLUserAccountData.cs
+++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
@@ -35,150 +35,50 @@ using MySql.Data.MySqlClient;
35 35
36namespace OpenSim.Data.MySQL 36namespace OpenSim.Data.MySQL
37{ 37{
38 public class MySqlUserAccountData : MySqlFramework, IUserAccountData 38 public class MySqlUserAccountData : MySQLGenericTableHandler<UserAccountData>, IUserAccountData
39 { 39 {
40 private string m_Realm;
41 private List<string> m_ColumnNames;
42 // private string m_connectionString;
43
44 public MySqlUserAccountData(string connectionString, string realm) 40 public MySqlUserAccountData(string connectionString, string realm)
45 : base(connectionString) 41 : base(connectionString, realm, "UserAccount")
46 {
47 m_Realm = realm;
48 m_connectionString = connectionString;
49
50 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
51 {
52 dbcon.Open();
53 Migration m = new Migration(dbcon, GetType().Assembly, "UserStore");
54 m.Update();
55 }
56 }
57
58 public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
59 { 42 {
60 return null;
61 } 43 }
62 44
63 public UserAccountData Get(UUID principalID, UUID scopeID) 45 public UserAccountData[] GetUsers(UUID scopeID, string query)
64 { 46 {
65 UserAccountData ret = new UserAccountData(); 47 string[] words = query.Split(new char[] {' '});
66 ret.Data = new Dictionary<string, object>();
67
68 string command = "select * from `"+m_Realm+"` where UUID = ?principalID";
69 if (scopeID != UUID.Zero)
70 command += " and ScopeID = ?scopeID";
71 48
72 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) 49 for (int i = 0 ; i < words.Length ; i++)
73 { 50 {
74 dbcon.Open(); 51 if (words[i].Length < 3)
75 MySqlCommand cmd = new MySqlCommand(command, dbcon);
76
77 cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
78 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
79
80 IDataReader result = cmd.ExecuteReader();
81
82 if (result.Read())
83 { 52 {
84 ret.PrincipalID = principalID; 53 if (i != words.Length - 1)
85 UUID scope; 54 Array.Copy(words, i + 1, words, i, words.Length - i - 1);
86 UUID.TryParse(result["ScopeID"].ToString(), out scope); 55 Array.Resize(ref words, words.Length - 1);
87 ret.ScopeID = scope;
88
89 if (m_ColumnNames == null)
90 {
91 m_ColumnNames = new List<string>();
92
93 DataTable schemaTable = result.GetSchemaTable();
94 foreach (DataRow row in schemaTable.Rows)
95 m_ColumnNames.Add(row["ColumnName"].ToString());
96 }
97
98 foreach (string s in m_ColumnNames)
99 {
100 if (s == "UUID")
101 continue;
102 if (s == "ScopeID")
103 continue;
104
105 ret.Data[s] = result[s].ToString();
106 }
107
108 return ret;
109 }
110 else
111 {
112 return null;
113 } 56 }
114 } 57 }
115 }
116 58
117 public bool Store(UserAccountData data) 59 if (words.Length == 0)
118 { 60 return new UserAccountData[0];
119 if (data.Data.ContainsKey("UUID"))
120 data.Data.Remove("UUID");
121 if (data.Data.ContainsKey("ScopeID"))
122 data.Data.Remove("ScopeID");
123
124 string[] fields = new List<string>(data.Data.Keys).ToArray();
125
126 using (MySqlCommand cmd = new MySqlCommand())
127 {
128 string update = "update `" + m_Realm + "` set ";
129 bool first = true;
130 foreach (string field in fields)
131 {
132 if (!first)
133 update += ", ";
134 update += "`" + field + "` = ?" + field;
135 61
136 first = false; 62 if (words.Length > 2)
63 return new UserAccountData[0];
137 64
138 cmd.Parameters.AddWithValue("?" + field, data.Data[field]); 65 MySqlCommand cmd = new MySqlCommand();
139 }
140
141 update += " where UUID = ?principalID";
142
143 if (data.ScopeID != UUID.Zero)
144 update += " and ScopeID = ?scopeID";
145 66
146 cmd.CommandText = update; 67 if (words.Length == 1)
147 cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString()); 68 {
148 cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); 69 cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?search or LastName like ?search)", m_Realm);
149 70 cmd.Parameters.AddWithValue("?search", "%" + words[0] + "%");
150 if (ExecuteNonQuery(cmd) < 1) 71 cmd.Parameters.AddWithValue("?ScopeID", scopeID.ToString());
151 {
152 string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
153 String.Join("`, `", fields) +
154 "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
155
156 cmd.CommandText = insert;
157
158 if (ExecuteNonQuery(cmd) < 1)
159 {
160 cmd.Dispose();
161 return false;
162 }
163 }
164 } 72 }
165 73 else
166 return true;
167 }
168
169 public bool SetDataItem(UUID principalID, string item, string value)
170 {
171 using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" +
172 item + "` = ?" + item + " where UUID = ?UUID"))
173 { 74 {
174 cmd.Parameters.AddWithValue("?" + item, value); 75 cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?searchFirst or LastName like ?searchLast)", m_Realm);
175 cmd.Parameters.AddWithValue("?UUID", principalID.ToString()); 76 cmd.Parameters.AddWithValue("?searchFirst", "%" + words[0] + "%");
176 77 cmd.Parameters.AddWithValue("?searchLast", "%" + words[1] + "%");
177 if (ExecuteNonQuery(cmd) > 0) 78 cmd.Parameters.AddWithValue("?ScopeID", scopeID.ToString());
178 return true;
179 } 79 }
180 80
181 return false; 81 return DoQuery(cmd);
182 } 82 }
183 } 83 }
184} 84}
diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs
deleted file mode 100644
index 0a9d2e3..0000000
--- a/OpenSim/Data/MySQL/MySQLUserData.cs
+++ /dev/null
@@ -1,766 +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;
30using System.Collections.Generic;
31using System.Data;
32using System.Reflection;
33using System.Text.RegularExpressions;
34using System.Threading;
35using log4net;
36using MySql.Data.MySqlClient;
37using OpenMetaverse;
38using OpenSim.Framework;
39
40namespace OpenSim.Data.MySQL
41{
42 /// <summary>
43 /// A database interface class to a user profile storage system
44 /// </summary>
45 public class MySQLUserData : UserDataBase
46 {
47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48
49 private MySQLManager m_database;
50 private string m_connectionString;
51 private object m_dbLock = new object();
52
53 public int m_maxConnections = 10;
54 public int m_lastConnect;
55
56 private string m_agentsTableName = "agents";
57 private string m_usersTableName = "users";
58 private string m_userFriendsTableName = "userfriends";
59 private string m_appearanceTableName = "avatarappearance";
60 private string m_attachmentsTableName = "avatarattachments";
61
62 public override void Initialise()
63 {
64 m_log.Info("[MySQLUserData]: " + Name + " cannot be default-initialized!");
65 throw new PluginNotInitialisedException(Name);
66 }
67
68 /// <summary>
69 /// Initialise User Interface
70 /// Loads and initialises the MySQL storage plugin
71 /// Warns and uses the obsolete mysql_connection.ini if connect string is empty.
72 /// Checks for migration
73 /// </summary>
74 /// <param name="connect">connect string.</param>
75 public override void Initialise(string connect)
76 {
77 m_connectionString = connect;
78 m_database = new MySQLManager(connect);
79
80 // This actually does the roll forward assembly stuff
81 Assembly assem = GetType().Assembly;
82
83 using (MySql.Data.MySqlClient.MySqlConnection dbcon = new MySql.Data.MySqlClient.MySqlConnection(m_connectionString))
84 {
85 dbcon.Open();
86 Migration m = new Migration(dbcon, assem, "UserStore");
87 m.Update();
88 }
89 }
90
91 public override void Dispose()
92 {
93 }
94
95 // see IUserDataPlugin
96 public override UserProfileData GetUserByName(string user, string last)
97 {
98 try
99 {
100 Dictionary<string, object> param = new Dictionary<string, object>();
101 param["?first"] = user;
102 param["?second"] = last;
103
104 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
105 {
106 dbcon.Open();
107
108 using (IDbCommand result = m_database.Query(dbcon,
109 "SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param))
110 {
111 using (IDataReader reader = result.ExecuteReader())
112 {
113 UserProfileData row = m_database.readUserRow(reader);
114 return row;
115 }
116 }
117 }
118 }
119 catch (Exception e)
120 {
121 m_log.Error(e.Message, e);
122 return null;
123 }
124 }
125
126 #region User Friends List Data
127
128 public override void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
129 {
130 int dtvalue = Util.UnixTimeSinceEpoch();
131
132 Dictionary<string, object> param = new Dictionary<string, object>();
133 param["?ownerID"] = friendlistowner.ToString();
134 param["?friendID"] = friend.ToString();
135 param["?friendPerms"] = perms.ToString();
136 param["?datetimestamp"] = dtvalue.ToString();
137
138 try
139 {
140 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
141 {
142 dbcon.Open();
143
144 using (IDbCommand adder = m_database.Query(dbcon,
145 "INSERT INTO `" + m_userFriendsTableName + "` " +
146 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
147 "VALUES " +
148 "(?ownerID,?friendID,?friendPerms,?datetimestamp)",
149 param))
150 {
151 adder.ExecuteNonQuery();
152 }
153
154 using (IDbCommand adder = m_database.Query(dbcon,
155 "INSERT INTO `" + m_userFriendsTableName + "` " +
156 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
157 "VALUES " +
158 "(?friendID,?ownerID,?friendPerms,?datetimestamp)",
159 param))
160 {
161 adder.ExecuteNonQuery();
162 }
163 }
164 }
165 catch (Exception e)
166 {
167 m_log.Error(e.Message, e);
168 return;
169 }
170 }
171
172 public override void RemoveUserFriend(UUID friendlistowner, UUID friend)
173 {
174 Dictionary<string, object> param = new Dictionary<string, object>();
175 param["?ownerID"] = friendlistowner.ToString();
176 param["?friendID"] = friend.ToString();
177
178 try
179 {
180 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
181 {
182 dbcon.Open();
183
184 using (IDbCommand updater = m_database.Query(dbcon,
185 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID",
186 param))
187 {
188 updater.ExecuteNonQuery();
189 }
190
191 using (IDbCommand updater = m_database.Query(dbcon,
192 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID",
193 param))
194 {
195 updater.ExecuteNonQuery();
196 }
197 }
198 }
199 catch (Exception e)
200 {
201 m_log.Error(e.Message, e);
202 return;
203 }
204 }
205
206 public override void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
207 {
208 Dictionary<string, object> param = new Dictionary<string, object>();
209 param["?ownerID"] = friendlistowner.ToString();
210 param["?friendID"] = friend.ToString();
211 param["?friendPerms"] = perms.ToString();
212
213 try
214 {
215 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
216 {
217 dbcon.Open();
218
219 using (IDbCommand updater = m_database.Query(dbcon,
220 "update " + m_userFriendsTableName +
221 " SET friendPerms = ?friendPerms " +
222 "where ownerID = ?ownerID and friendID = ?friendID",
223 param))
224 {
225 updater.ExecuteNonQuery();
226 }
227 }
228 }
229 catch (Exception e)
230 {
231 m_log.Error(e.Message, e);
232 return;
233 }
234 }
235
236 public override List<FriendListItem> GetUserFriendList(UUID friendlistowner)
237 {
238 List<FriendListItem> Lfli = new List<FriendListItem>();
239
240 Dictionary<string, object> param = new Dictionary<string, object>();
241 param["?ownerID"] = friendlistowner.ToString();
242
243 try
244 {
245 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
246 {
247 dbcon.Open();
248
249 //Left Join userfriends to itself
250 using (IDbCommand result = m_database.Query(dbcon,
251 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " +
252 m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
253 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
254 param))
255 {
256 using (IDataReader reader = result.ExecuteReader())
257 {
258 while (reader.Read())
259 {
260 FriendListItem fli = new FriendListItem();
261 fli.FriendListOwner = new UUID((string)reader["ownerID"]);
262 fli.Friend = new UUID((string)reader["friendID"]);
263 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
264
265 // This is not a real column in the database table, it's a joined column from the opposite record
266 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
267
268 Lfli.Add(fli);
269 }
270 }
271 }
272 }
273 }
274 catch (Exception e)
275 {
276 m_log.Error(e.Message, e);
277 return Lfli;
278 }
279
280 return Lfli;
281 }
282
283 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
284 {
285 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
286
287 try
288 {
289 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
290 {
291 dbcon.Open();
292
293 foreach (UUID uuid in uuids)
294 {
295 Dictionary<string, object> param = new Dictionary<string, object>();
296 param["?uuid"] = uuid.ToString();
297
298 using (IDbCommand result = m_database.Query(dbcon, "select agentOnline,currentHandle from " + m_agentsTableName +
299 " where UUID = ?uuid", param))
300 {
301 using (IDataReader reader = result.ExecuteReader())
302 {
303 while (reader.Read())
304 {
305 FriendRegionInfo fri = new FriendRegionInfo();
306 fri.isOnline = (sbyte)reader["agentOnline"] != 0;
307 fri.regionHandle = (ulong)reader["currentHandle"];
308
309 infos[uuid] = fri;
310 }
311 }
312 }
313 }
314 }
315 }
316 catch (Exception e)
317 {
318 m_log.Warn("[MYSQL]: Got exception on trying to find friends regions:", e);
319 m_log.Error(e.Message, e);
320 }
321
322 return infos;
323 }
324
325 #endregion
326
327 public override List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
328 {
329 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
330
331 Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]");
332
333 string[] querysplit;
334 querysplit = query.Split(' ');
335 if (querysplit.Length > 1 && querysplit[1].Trim() != String.Empty)
336 {
337 Dictionary<string, object> param = new Dictionary<string, object>();
338 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
339 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%";
340
341 try
342 {
343 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
344 {
345 dbcon.Open();
346
347 using (IDbCommand result = m_database.Query(dbcon,
348 "SELECT UUID,username,lastname FROM " + m_usersTableName +
349 " WHERE username like ?first AND lastname like ?second LIMIT 100",
350 param))
351 {
352 using (IDataReader reader = result.ExecuteReader())
353 {
354 while (reader.Read())
355 {
356 AvatarPickerAvatar user = new AvatarPickerAvatar();
357 user.AvatarID = new UUID((string)reader["UUID"]);
358 user.firstName = (string)reader["username"];
359 user.lastName = (string)reader["lastname"];
360 returnlist.Add(user);
361 }
362 }
363 }
364 }
365 }
366 catch (Exception e)
367 {
368 m_log.Error(e.Message, e);
369 return returnlist;
370 }
371 }
372 else
373 {
374 try
375 {
376 Dictionary<string, object> param = new Dictionary<string, object>();
377 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
378
379 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
380 {
381 dbcon.Open();
382
383 using (IDbCommand result = m_database.Query(dbcon,
384 "SELECT UUID,username,lastname FROM " + m_usersTableName +
385 " WHERE username like ?first OR lastname like ?first LIMIT 100",
386 param))
387 {
388 using (IDataReader reader = result.ExecuteReader())
389 {
390 while (reader.Read())
391 {
392 AvatarPickerAvatar user = new AvatarPickerAvatar();
393 user.AvatarID = new UUID((string)reader["UUID"]);
394 user.firstName = (string)reader["username"];
395 user.lastName = (string)reader["lastname"];
396 returnlist.Add(user);
397 }
398 }
399 }
400 }
401 }
402 catch (Exception e)
403 {
404 m_log.Error(e.Message, e);
405 return returnlist;
406 }
407 }
408 return returnlist;
409 }
410
411 /// <summary>
412 /// See IUserDataPlugin
413 /// </summary>
414 /// <param name="uuid">User UUID</param>
415 /// <returns>User profile data</returns>
416 public override UserProfileData GetUserByUUID(UUID uuid)
417 {
418 try
419 {
420 Dictionary<string, object> param = new Dictionary<string, object>();
421 param["?uuid"] = uuid.ToString();
422
423 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
424 {
425 dbcon.Open();
426
427 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param))
428 {
429 using (IDataReader reader = result.ExecuteReader())
430 {
431 UserProfileData row = m_database.readUserRow(reader);
432 return row;
433 }
434 }
435 }
436 }
437 catch (Exception e)
438 {
439 m_log.Error(e.Message, e);
440 return null;
441 }
442 }
443
444 /// <summary>
445 /// Returns a user session searching by name
446 /// </summary>
447 /// <param name="name">The account name : "Username Lastname"</param>
448 /// <returns>The users session</returns>
449 public override UserAgentData GetAgentByName(string name)
450 {
451 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
452 }
453
454 /// <summary>
455 /// Returns a user session by account name
456 /// </summary>
457 /// <param name="user">First part of the users account name</param>
458 /// <param name="last">Second part of the users account name</param>
459 /// <returns>The users session</returns>
460 public override UserAgentData GetAgentByName(string user, string last)
461 {
462 UserProfileData profile = GetUserByName(user, last);
463 return GetAgentByUUID(profile.ID);
464 }
465
466 /// <summary>
467 /// </summary>
468 /// <param name="AgentID"></param>
469 /// <param name="WebLoginKey"></param>
470 /// <remarks>is it still used ?</remarks>
471 public override void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
472 {
473 Dictionary<string, string> param = new Dictionary<string, string>();
474 param["?UUID"] = AgentID.ToString();
475 param["?webLoginKey"] = WebLoginKey.ToString();
476
477 try
478 {
479 m_database.ExecuteParameterizedSql(
480 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " +
481 "where UUID = ?UUID",
482 param);
483 }
484 catch (Exception e)
485 {
486 m_log.Error(e.Message, e);
487 return;
488 }
489 }
490
491 /// <summary>
492 /// Returns an agent session by account UUID
493 /// </summary>
494 /// <param name="uuid">The accounts UUID</param>
495 /// <returns>The users session</returns>
496 public override UserAgentData GetAgentByUUID(UUID uuid)
497 {
498 try
499 {
500 Dictionary<string, object> param = new Dictionary<string, object>();
501 param["?uuid"] = uuid.ToString();
502
503 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
504 {
505 dbcon.Open();
506
507 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param))
508 {
509 using (IDataReader reader = result.ExecuteReader())
510 {
511 UserAgentData row = m_database.readAgentRow(reader);
512 return row;
513 }
514 }
515 }
516 }
517 catch (Exception e)
518 {
519 m_log.Error(e.Message, e);
520 return null;
521 }
522 }
523
524 /// <summary>
525 /// Creates a new users profile
526 /// </summary>
527 /// <param name="user">The user profile to create</param>
528 public override void AddNewUserProfile(UserProfileData user)
529 {
530 UUID zero = UUID.Zero;
531 if (user.ID == zero)
532 {
533 return;
534 }
535
536 try
537 {
538 m_database.insertUserRow(
539 user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
540 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y,
541 user.HomeLocation.Z,
542 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
543 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
544 user.CanDoMask, user.WantDoMask,
545 user.AboutText, user.FirstLifeAboutText, user.Image,
546 user.FirstLifeImage, user.WebLoginKey, user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
547 }
548 catch (Exception e)
549 {
550 m_log.Error(e.Message, e);
551 }
552 }
553
554 /// <summary>
555 /// Creates a new agent
556 /// </summary>
557 /// <param name="agent">The agent to create</param>
558 public override void AddNewUserAgent(UserAgentData agent)
559 {
560 UUID zero = UUID.Zero;
561 if (agent.ProfileID == zero || agent.SessionID == zero)
562 return;
563
564 try
565 {
566 m_database.insertAgentRow(agent);
567 }
568 catch (Exception e)
569 {
570 m_log.Error(e.Message, e);
571 }
572 }
573
574 /// <summary>
575 /// Updates a user profile stored in the DB
576 /// </summary>
577 /// <param name="user">The profile data to use to update the DB</param>
578 public override bool UpdateUserProfile(UserProfileData user)
579 {
580 try
581 {
582 m_database.updateUserRow(
583 user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
584 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y,
585 user.HomeLocation.Z, user.HomeLookAt.X,
586 user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin,
587 user.UserInventoryURI,
588 user.UserAssetURI, user.CanDoMask, user.WantDoMask, user.AboutText,
589 user.FirstLifeAboutText, user.Image, user.FirstLifeImage, user.WebLoginKey,
590 user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
591
592 return true;
593 }
594 catch
595 {
596 return false;
597 }
598 }
599
600 /// <summary>
601 /// Performs a money transfer request between two accounts
602 /// </summary>
603 /// <param name="from">The senders account ID</param>
604 /// <param name="to">The receivers account ID</param>
605 /// <param name="amount">The amount to transfer</param>
606 /// <returns>Success?</returns>
607 public override bool MoneyTransferRequest(UUID from, UUID to, uint amount)
608 {
609 return false;
610 }
611
612 /// <summary>
613 /// Performs an inventory transfer request between two accounts
614 /// </summary>
615 /// <remarks>TODO: Move to inventory server</remarks>
616 /// <param name="from">The senders account ID</param>
617 /// <param name="to">The receivers account ID</param>
618 /// <param name="item">The item to transfer</param>
619 /// <returns>Success?</returns>
620 public override bool InventoryTransferRequest(UUID from, UUID to, UUID item)
621 {
622 return false;
623 }
624
625 public override AvatarAppearance GetUserAppearance(UUID user)
626 {
627 try
628 {
629 Dictionary<string, object> param = new Dictionary<string, object>();
630 param["?owner"] = user.ToString();
631
632 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
633 {
634 dbcon.Open();
635
636 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_appearanceTableName + " WHERE owner = ?owner", param))
637 {
638 using (IDataReader reader = result.ExecuteReader())
639 {
640 AvatarAppearance appearance = m_database.readAppearanceRow(reader);
641
642 if (appearance == null)
643 {
644 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
645 return null;
646 }
647 else
648 {
649 appearance.SetAttachments(GetUserAttachments(user));
650 return appearance;
651 }
652 }
653 }
654 }
655 }
656 catch (Exception e)
657 {
658 m_log.Error(e.Message, e);
659 return null;
660 }
661 }
662
663 /// <summary>
664 /// Updates an avatar appearence
665 /// </summary>
666 /// <param name="user">The user UUID</param>
667 /// <param name="appearance">The avatar appearance</param>
668 // override
669 public override void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
670 {
671 try
672 {
673 appearance.Owner = user;
674 m_database.insertAppearanceRow(appearance);
675
676 UpdateUserAttachments(user, appearance.GetAttachments());
677 }
678 catch (Exception e)
679 {
680 m_log.Error(e.Message, e);
681 }
682 }
683
684 /// <summary>
685 /// Database provider name
686 /// </summary>
687 /// <returns>Provider name</returns>
688 public override string Name
689 {
690 get { return "MySQL Userdata Interface"; }
691 }
692
693 /// <summary>
694 /// Database provider version
695 /// </summary>
696 /// <returns>provider version</returns>
697 public override string Version
698 {
699 get { return "0.1"; }
700 }
701
702 public Hashtable GetUserAttachments(UUID agentID)
703 {
704 Dictionary<string, object> param = new Dictionary<string, object>();
705 param["?uuid"] = agentID.ToString();
706
707 try
708 {
709 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
710 {
711 dbcon.Open();
712
713 using (IDbCommand result = m_database.Query(dbcon,
714 "SELECT attachpoint, item, asset from " + m_attachmentsTableName + " WHERE UUID = ?uuid", param))
715 {
716 using (IDataReader reader = result.ExecuteReader())
717 {
718 Hashtable ret = m_database.readAttachments(reader);
719 return ret;
720 }
721 }
722 }
723 }
724 catch (Exception e)
725 {
726 m_log.Error(e.Message, e);
727 return null;
728 }
729 }
730
731 public void UpdateUserAttachments(UUID agentID, Hashtable data)
732 {
733 m_database.writeAttachments(agentID, data);
734 }
735
736 public override void ResetAttachments(UUID userID)
737 {
738 Dictionary<string, string> param = new Dictionary<string, string>();
739 param["?uuid"] = userID.ToString();
740
741 m_database.ExecuteParameterizedSql(
742 "UPDATE " + m_attachmentsTableName +
743 " SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = ?uuid",
744 param);
745 }
746
747 public override void LogoutUsers(UUID regionID)
748 {
749 Dictionary<string, string> param = new Dictionary<string, string>();
750 param["?regionID"] = regionID.ToString();
751
752 try
753 {
754 m_database.ExecuteParameterizedSql(
755 "update " + m_agentsTableName + " SET agentOnline = 0 " +
756 "where currentRegion = ?regionID",
757 param);
758 }
759 catch (Exception e)
760 {
761 m_log.Error(e.Message, e);
762 return;
763 }
764 }
765 }
766}
diff --git a/OpenSim/Data/MySQL/Resources/001_Avatar.sql b/OpenSim/Data/MySQL/Resources/001_Avatar.sql
new file mode 100644
index 0000000..27a3072
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/001_Avatar.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3CREATE TABLE Avatars (PrincipalID CHAR(36) NOT NULL, Name VARCHAR(32) NOT NULL, Value VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY(PrincipalID, Name), KEY(PrincipalID));
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/001_Friends.sql b/OpenSim/Data/MySQL/Resources/001_Friends.sql
new file mode 100644
index 0000000..e158a2c
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/001_Friends.sql
@@ -0,0 +1,9 @@
1BEGIN;
2
3CREATE TABLE `Friends` (
4 `PrincipalID` CHAR(36) NOT NULL,
5 `FriendID` VARCHAR(255) NOT NULL,
6 `Flags` CHAR(16) NOT NULL DEFAULT '0'
7) ENGINE=InnoDB;
8
9COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/001_FriendsStore.sql b/OpenSim/Data/MySQL/Resources/001_FriendsStore.sql
new file mode 100644
index 0000000..da2c59c
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/001_FriendsStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3CREATE TABLE `Friends` (`PrincipalID` CHAR(36) NOT NULL, `Friend` VARCHAR(255) NOT NULL, `Flags` VARCHAR(16) NOT NULL DEFAULT 0, `Offered` VARCHAR(32) NOT NULL DEFAULT 0, PRIMARY KEY(`PrincipalID`, `Friend`), KEY(`PrincipalID`));
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/001_Presence.sql b/OpenSim/Data/MySQL/Resources/001_Presence.sql
new file mode 100644
index 0000000..b8abaf7
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/001_Presence.sql
@@ -0,0 +1,15 @@
1BEGIN;
2
3CREATE TABLE `Presence` (
4 `UserID` VARCHAR(255) NOT NULL,
5 `RegionID` CHAR(36) NOT NULL,
6 `SessionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
7 `SecureSessionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
8 `Online` CHAR(5) NOT NULL DEFAULT 'false',
9 `Login` CHAR(16) NOT NULL DEFAULT '0',
10 `Logout` CHAR(16) NOT NULL DEFAULT '0',
11 `Position` CHAR(64) NOT NULL DEFAULT '<0,0,0>',
12 `LookAt` CHAR(64) NOT NULL DEFAULT '<0,0,0>'
13) ENGINE=InnoDB;
14
15COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/001_UserAccount.sql b/OpenSim/Data/MySQL/Resources/001_UserAccount.sql
new file mode 100644
index 0000000..07da571
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/001_UserAccount.sql
@@ -0,0 +1,13 @@
1BEGIN;
2
3CREATE TABLE `UserAccounts` (
4 `PrincipalID` CHAR(36) NOT NULL,
5 `ScopeID` CHAR(36) NOT NULL,
6 `FirstName` VARCHAR(64) NOT NULL,
7 `LastName` VARCHAR(64) NOT NULL,
8 `Email` VARCHAR(64),
9 `ServiceURLs` TEXT,
10 `Created` INT(11)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/002_AuthStore.sql b/OpenSim/Data/MySQL/Resources/002_AuthStore.sql
new file mode 100644
index 0000000..dc7dfe0
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_AuthStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey) SELECT `UUID` AS UUID, `passwordHash` AS passwordHash, `passwordSalt` AS passwordSalt, `webLoginKey` AS webLoginKey FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/002_Friends.sql b/OpenSim/Data/MySQL/Resources/002_Friends.sql
new file mode 100644
index 0000000..5ff6438
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_Friends.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3INSERT INTO Friends (PrincipalID, FriendID, Flags) SELECT ownerID, friendID, friendPerms FROM userfriends;
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/002_FriendsStore.sql b/OpenSim/Data/MySQL/Resources/002_FriendsStore.sql
new file mode 100644
index 0000000..a363867
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_FriendsStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3INSERT INTO `Friends` SELECT `ownerID`, `friendID`, `friendPerms`, 0 FROM `userfriends`;
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/002_Presence.sql b/OpenSim/Data/MySQL/Resources/002_Presence.sql
new file mode 100644
index 0000000..e65f105
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_Presence.sql
@@ -0,0 +1,7 @@
1BEGIN;
2
3ALTER TABLE Presence ADD COLUMN `HomeRegionID` CHAR(36) NOT NULL;
4ALTER TABLE Presence ADD COLUMN `HomePosition` CHAR(64) NOT NULL DEFAULT '<0,0,0>';
5ALTER TABLE Presence ADD COLUMN `HomeLookAt` CHAR(64) NOT NULL DEFAULT '<0,0,0>';
6
7COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/002_UserAccount.sql b/OpenSim/Data/MySQL/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..ad2ddda
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_UserAccount.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, lastname AS LastName, email as Email, CONCAT('AssetServerURI=', userAssetURI, ' InventoryServerURI=', userInventoryURI, ' GatewayURI= HomeURI=') AS ServiceURLs, created as Created FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/003_AuthStore.sql b/OpenSim/Data/MySQL/Resources/003_AuthStore.sql
new file mode 100644
index 0000000..af9ffe6
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/003_AuthStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE `auth` ADD COLUMN `accountType` VARCHAR(32) NOT NULL DEFAULT 'UserAccount';
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/003_Presence.sql b/OpenSim/Data/MySQL/Resources/003_Presence.sql
new file mode 100644
index 0000000..0efefa8
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/003_Presence.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3CREATE UNIQUE INDEX SessionID ON Presence(SessionID);
4CREATE INDEX UserID ON Presence(UserID);
5
6COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/003_UserAccount.sql b/OpenSim/Data/MySQL/Resources/003_UserAccount.sql
new file mode 100644
index 0000000..e42d93b
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/003_UserAccount.sql
@@ -0,0 +1,9 @@
1BEGIN;
2
3CREATE UNIQUE INDEX PrincipalID ON UserAccounts(PrincipalID);
4CREATE INDEX Email ON UserAccounts(Email);
5CREATE INDEX FirstName ON UserAccounts(FirstName);
6CREATE INDEX LastName ON UserAccounts(LastName);
7CREATE INDEX Name ON UserAccounts(FirstName,LastName);
8
9COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/004_UserAccount.sql b/OpenSim/Data/MySQL/Resources/004_UserAccount.sql
new file mode 100644
index 0000000..8abcd53
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/004_UserAccount.sql
@@ -0,0 +1,8 @@
1BEGIN;
2
3ALTER TABLE UserAccounts ADD COLUMN UserLevel integer NOT NULL DEFAULT 0;
4ALTER TABLE UserAccounts ADD COLUMN UserFlags integer NOT NULL DEFAULT 0;
5ALTER TABLE UserAccounts ADD COLUMN UserTitle varchar(64) NOT NULL DEFAULT '';
6
7COMMIT;
8
diff --git a/OpenSim/Data/MySQL/Resources/005_GridStore.sql b/OpenSim/Data/MySQL/Resources/005_GridStore.sql
new file mode 100644
index 0000000..835ba89
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/005_GridStore.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3ALTER TABLE `regions` ADD COLUMN `flags` integer NOT NULL DEFAULT 0;
4CREATE INDEX flags ON regions(flags);
5
6COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/006_GridStore.sql b/OpenSim/Data/MySQL/Resources/006_GridStore.sql
new file mode 100644
index 0000000..91322d6
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/006_GridStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE `regions` ADD COLUMN `last_seen` integer NOT NULL DEFAULT 0;
4
5COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/007_GridStore.sql b/OpenSim/Data/MySQL/Resources/007_GridStore.sql
new file mode 100644
index 0000000..dbec584
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/007_GridStore.sql
@@ -0,0 +1,7 @@
1BEGIN;
2
3ALTER TABLE `regions` ADD COLUMN `PrincipalID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000';
4ALTER TABLE `regions` ADD COLUMN `Token` varchar(255) NOT NULL;
5
6COMMIT;
7
diff --git a/OpenSim/Data/MySQL/Tests/MySQLAssetTest.cs b/OpenSim/Data/MySQL/Tests/MySQLAssetTest.cs
index e1d3f81..a46fdf8 100644
--- a/OpenSim/Data/MySQL/Tests/MySQLAssetTest.cs
+++ b/OpenSim/Data/MySQL/Tests/MySQLAssetTest.cs
@@ -31,6 +31,7 @@ using OpenSim.Data.Tests;
31using log4net; 31using log4net;
32using System.Reflection; 32using System.Reflection;
33using OpenSim.Tests.Common; 33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
34 35
35namespace OpenSim.Data.MySQL.Tests 36namespace OpenSim.Data.MySQL.Tests
36{ 37{
@@ -39,7 +40,7 @@ namespace OpenSim.Data.MySQL.Tests
39 { 40 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 41 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41 public string file; 42 public string file;
42 public MySQLManager database; 43 private string m_connectionString;
43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;"; 44 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
44 45
45 [TestFixtureSetUp] 46 [TestFixtureSetUp]
@@ -52,7 +53,6 @@ namespace OpenSim.Data.MySQL.Tests
52 // tests. 53 // tests.
53 try 54 try
54 { 55 {
55 database = new MySQLManager(connect);
56 db = new MySQLAssetData(); 56 db = new MySQLAssetData();
57 db.Initialise(connect); 57 db.Initialise(connect);
58 } 58 }
@@ -70,10 +70,22 @@ namespace OpenSim.Data.MySQL.Tests
70 { 70 {
71 db.Dispose(); 71 db.Dispose();
72 } 72 }
73 if (database != null) 73 ExecuteSql("drop table migrations");
74 ExecuteSql("drop table assets");
75 }
76
77 /// <summary>
78 /// Execute a MySqlCommand
79 /// </summary>
80 /// <param name="sql">sql string to execute</param>
81 private void ExecuteSql(string sql)
82 {
83 using (MySqlConnection dbcon = new MySqlConnection(connect))
74 { 84 {
75 database.ExecuteSql("drop table migrations"); 85 dbcon.Open();
76 database.ExecuteSql("drop table assets"); 86
87 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
88 cmd.ExecuteNonQuery();
77 } 89 }
78 } 90 }
79 } 91 }
diff --git a/OpenSim/Data/MySQL/Tests/MySQLEstateTest.cs b/OpenSim/Data/MySQL/Tests/MySQLEstateTest.cs
index 48486b1..01afcae 100644
--- a/OpenSim/Data/MySQL/Tests/MySQLEstateTest.cs
+++ b/OpenSim/Data/MySQL/Tests/MySQLEstateTest.cs
@@ -31,6 +31,8 @@ using OpenSim.Data.Tests;
31using log4net; 31using log4net;
32using System.Reflection; 32using System.Reflection;
33using OpenSim.Tests.Common; 33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
35
34 36
35namespace OpenSim.Data.MySQL.Tests 37namespace OpenSim.Data.MySQL.Tests
36{ 38{
@@ -39,7 +41,6 @@ namespace OpenSim.Data.MySQL.Tests
39 { 41 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41 public string file; 43 public string file;
42 public MySQLManager database;
43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;"; 44 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
44 45
45 [TestFixtureSetUp] 46 [TestFixtureSetUp]
@@ -52,9 +53,8 @@ namespace OpenSim.Data.MySQL.Tests
52 // tests. 53 // tests.
53 try 54 try
54 { 55 {
55 database = new MySQLManager(connect);
56 // clear db incase to ensure we are in a clean state 56 // clear db incase to ensure we are in a clean state
57 ClearDB(database); 57 ClearDB();
58 58
59 regionDb = new MySQLDataStore(); 59 regionDb = new MySQLDataStore();
60 regionDb.Initialise(connect); 60 regionDb.Initialise(connect);
@@ -75,29 +75,41 @@ namespace OpenSim.Data.MySQL.Tests
75 { 75 {
76 regionDb.Dispose(); 76 regionDb.Dispose();
77 } 77 }
78 ClearDB(database); 78 ClearDB();
79 } 79 }
80 80
81 private void ClearDB(MySQLManager manager) 81 private void ClearDB()
82 { 82 {
83 // if a new table is added, it has to be dropped here 83 // if a new table is added, it has to be dropped here
84 if (manager != null) 84 ExecuteSql("drop table if exists migrations");
85 ExecuteSql("drop table if exists prims");
86 ExecuteSql("drop table if exists primshapes");
87 ExecuteSql("drop table if exists primitems");
88 ExecuteSql("drop table if exists terrain");
89 ExecuteSql("drop table if exists land");
90 ExecuteSql("drop table if exists landaccesslist");
91 ExecuteSql("drop table if exists regionban");
92 ExecuteSql("drop table if exists regionsettings");
93 ExecuteSql("drop table if exists estate_managers");
94 ExecuteSql("drop table if exists estate_groups");
95 ExecuteSql("drop table if exists estate_users");
96 ExecuteSql("drop table if exists estateban");
97 ExecuteSql("drop table if exists estate_settings");
98 ExecuteSql("drop table if exists estate_map");
99 }
100
101 /// <summary>
102 /// Execute a MySqlCommand
103 /// </summary>
104 /// <param name="sql">sql string to execute</param>
105 private void ExecuteSql(string sql)
106 {
107 using (MySqlConnection dbcon = new MySqlConnection(connect))
85 { 108 {
86 manager.ExecuteSql("drop table if exists migrations"); 109 dbcon.Open();
87 manager.ExecuteSql("drop table if exists prims"); 110
88 manager.ExecuteSql("drop table if exists primshapes"); 111 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
89 manager.ExecuteSql("drop table if exists primitems"); 112 cmd.ExecuteNonQuery();
90 manager.ExecuteSql("drop table if exists terrain");
91 manager.ExecuteSql("drop table if exists land");
92 manager.ExecuteSql("drop table if exists landaccesslist");
93 manager.ExecuteSql("drop table if exists regionban");
94 manager.ExecuteSql("drop table if exists regionsettings");
95 manager.ExecuteSql("drop table if exists estate_managers");
96 manager.ExecuteSql("drop table if exists estate_groups");
97 manager.ExecuteSql("drop table if exists estate_users");
98 manager.ExecuteSql("drop table if exists estateban");
99 manager.ExecuteSql("drop table if exists estate_settings");
100 manager.ExecuteSql("drop table if exists estate_map");
101 } 113 }
102 } 114 }
103 } 115 }
diff --git a/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs b/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs
deleted file mode 100644
index 8272316..0000000
--- a/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs
+++ /dev/null
@@ -1,94 +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 NUnit.Framework;
30using OpenSim.Data.Tests;
31using log4net;
32using System.Reflection;
33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
35
36namespace OpenSim.Data.MySQL.Tests
37{
38 [TestFixture, DatabaseTest]
39 public class MySQLGridTest : BasicGridTest
40 {
41 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
42
43 public string file;
44 public MySQLManager database;
45 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
46
47 [TestFixtureSetUp]
48 public void Init()
49 {
50 SuperInit();
51 // If we manage to connect to the database with the user
52 // and password above it is our test database, and run
53 // these tests. If anything goes wrong, ignore these
54 // tests.
55 try
56 {
57 database = new MySQLManager(connect);
58 db = new MySQLGridData();
59 db.Initialise(connect);
60 }
61 catch (Exception e)
62 {
63 m_log.Error("Exception {0}", e);
64 Assert.Ignore();
65 }
66
67 // This actually does the roll forward assembly stuff
68 Assembly assem = GetType().Assembly;
69
70 using (MySqlConnection dbcon = new MySqlConnection(connect))
71 {
72 dbcon.Open();
73 Migration m = new Migration(dbcon, assem, "AssetStore");
74 m.Update();
75 }
76 }
77
78 [TestFixtureTearDown]
79 public void Cleanup()
80 {
81 m_log.Warn("Cleaning up.");
82 if (db != null)
83 {
84 db.Dispose();
85 }
86 // if a new table is added, it has to be dropped here
87 if (database != null)
88 {
89 database.ExecuteSql("drop table migrations");
90 database.ExecuteSql("drop table regions");
91 }
92 }
93 }
94}
diff --git a/OpenSim/Data/MySQL/Tests/MySQLInventoryTest.cs b/OpenSim/Data/MySQL/Tests/MySQLInventoryTest.cs
index a3a32dc..4575493 100644
--- a/OpenSim/Data/MySQL/Tests/MySQLInventoryTest.cs
+++ b/OpenSim/Data/MySQL/Tests/MySQLInventoryTest.cs
@@ -31,6 +31,8 @@ using OpenSim.Data.Tests;
31using log4net; 31using log4net;
32using System.Reflection; 32using System.Reflection;
33using OpenSim.Tests.Common; 33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
35
34 36
35namespace OpenSim.Data.MySQL.Tests 37namespace OpenSim.Data.MySQL.Tests
36{ 38{
@@ -39,7 +41,6 @@ namespace OpenSim.Data.MySQL.Tests
39 { 41 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41 public string file; 43 public string file;
42 public MySQLManager database;
43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;"; 44 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
44 45
45 [TestFixtureSetUp] 46 [TestFixtureSetUp]
@@ -52,7 +53,6 @@ namespace OpenSim.Data.MySQL.Tests
52 // tests. 53 // tests.
53 try 54 try
54 { 55 {
55 database = new MySQLManager(connect);
56 DropTables(); 56 DropTables();
57 db = new MySQLInventoryData(); 57 db = new MySQLInventoryData();
58 db.Initialise(connect); 58 db.Initialise(connect);
@@ -71,17 +71,29 @@ namespace OpenSim.Data.MySQL.Tests
71 { 71 {
72 db.Dispose(); 72 db.Dispose();
73 } 73 }
74 if (database != null) 74 DropTables();
75 {
76 DropTables();
77 }
78 } 75 }
79 76
80 private void DropTables() 77 private void DropTables()
81 { 78 {
82 database.ExecuteSql("drop table IF EXISTS inventoryitems"); 79 ExecuteSql("drop table IF EXISTS inventoryitems");
83 database.ExecuteSql("drop table IF EXISTS inventoryfolders"); 80 ExecuteSql("drop table IF EXISTS inventoryfolders");
84 database.ExecuteSql("drop table IF EXISTS migrations"); 81 ExecuteSql("drop table IF EXISTS migrations");
82 }
83
84 /// <summary>
85 /// Execute a MySqlCommand
86 /// </summary>
87 /// <param name="sql">sql string to execute</param>
88 private void ExecuteSql(string sql)
89 {
90 using (MySqlConnection dbcon = new MySqlConnection(connect))
91 {
92 dbcon.Open();
93
94 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
95 cmd.ExecuteNonQuery();
96 }
85 } 97 }
86 } 98 }
87} 99}
diff --git a/OpenSim/Data/MySQL/Tests/MySQLRegionTest.cs b/OpenSim/Data/MySQL/Tests/MySQLRegionTest.cs
index 0dc8b7d..e7e57e4 100644
--- a/OpenSim/Data/MySQL/Tests/MySQLRegionTest.cs
+++ b/OpenSim/Data/MySQL/Tests/MySQLRegionTest.cs
@@ -31,6 +31,7 @@ using OpenSim.Data.Tests;
31using log4net; 31using log4net;
32using System.Reflection; 32using System.Reflection;
33using OpenSim.Tests.Common; 33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
34 35
35namespace OpenSim.Data.MySQL.Tests 36namespace OpenSim.Data.MySQL.Tests
36{ 37{
@@ -39,7 +40,6 @@ namespace OpenSim.Data.MySQL.Tests
39 { 40 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 41 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41 public string file; 42 public string file;
42 public MySQLManager database;
43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;"; 43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
44 44
45 [TestFixtureSetUp] 45 [TestFixtureSetUp]
@@ -52,9 +52,8 @@ namespace OpenSim.Data.MySQL.Tests
52 // tests. 52 // tests.
53 try 53 try
54 { 54 {
55 database = new MySQLManager(connect);
56 // this is important in case a previous run ended badly 55 // this is important in case a previous run ended badly
57 ClearDB(database); 56 ClearDB();
58 57
59 db = new MySQLDataStore(); 58 db = new MySQLDataStore();
60 db.Initialise(connect); 59 db.Initialise(connect);
@@ -73,28 +72,40 @@ namespace OpenSim.Data.MySQL.Tests
73 { 72 {
74 db.Dispose(); 73 db.Dispose();
75 } 74 }
76 ClearDB(database); 75 ClearDB();
77 } 76 }
78 77
79 private void ClearDB(MySQLManager manager) 78 private void ClearDB()
80 { 79 {
81 if (manager != null) 80 ExecuteSql("drop table if exists migrations");
81 ExecuteSql("drop table if exists prims");
82 ExecuteSql("drop table if exists primshapes");
83 ExecuteSql("drop table if exists primitems");
84 ExecuteSql("drop table if exists terrain");
85 ExecuteSql("drop table if exists land");
86 ExecuteSql("drop table if exists landaccesslist");
87 ExecuteSql("drop table if exists regionban");
88 ExecuteSql("drop table if exists regionsettings");
89 ExecuteSql("drop table if exists estate_managers");
90 ExecuteSql("drop table if exists estate_groups");
91 ExecuteSql("drop table if exists estate_users");
92 ExecuteSql("drop table if exists estateban");
93 ExecuteSql("drop table if exists estate_settings");
94 ExecuteSql("drop table if exists estate_map");
95 }
96
97 /// <summary>
98 /// Execute a MySqlCommand
99 /// </summary>
100 /// <param name="sql">sql string to execute</param>
101 private void ExecuteSql(string sql)
102 {
103 using (MySqlConnection dbcon = new MySqlConnection(connect))
82 { 104 {
83 manager.ExecuteSql("drop table if exists migrations"); 105 dbcon.Open();
84 manager.ExecuteSql("drop table if exists prims"); 106
85 manager.ExecuteSql("drop table if exists primshapes"); 107 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
86 manager.ExecuteSql("drop table if exists primitems"); 108 cmd.ExecuteNonQuery();
87 manager.ExecuteSql("drop table if exists terrain");
88 manager.ExecuteSql("drop table if exists land");
89 manager.ExecuteSql("drop table if exists landaccesslist");
90 manager.ExecuteSql("drop table if exists regionban");
91 manager.ExecuteSql("drop table if exists regionsettings");
92 manager.ExecuteSql("drop table if exists estate_managers");
93 manager.ExecuteSql("drop table if exists estate_groups");
94 manager.ExecuteSql("drop table if exists estate_users");
95 manager.ExecuteSql("drop table if exists estateban");
96 manager.ExecuteSql("drop table if exists estate_settings");
97 manager.ExecuteSql("drop table if exists estate_map");
98 } 109 }
99 } 110 }
100 } 111 }
diff --git a/OpenSim/Data/MySQL/Tests/MySQLUserTest.cs b/OpenSim/Data/MySQL/Tests/MySQLUserTest.cs
deleted file mode 100644
index cf8139a..0000000
--- a/OpenSim/Data/MySQL/Tests/MySQLUserTest.cs
+++ /dev/null
@@ -1,85 +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 NUnit.Framework;
30using OpenSim.Data.Tests;
31using log4net;
32using System.Reflection;
33using OpenSim.Tests.Common;
34
35namespace OpenSim.Data.MySQL.Tests
36{
37 [TestFixture, DatabaseTest]
38 public class MySQLUserTest : BasicUserTest
39 {
40 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
41 public string file;
42 public MySQLManager database;
43 public string connect = "Server=localhost;Port=3306;Database=opensim-nunit;User ID=opensim-nunit;Password=opensim-nunit;Pooling=false;";
44
45 [TestFixtureSetUp]
46 public void Init()
47 {
48 SuperInit();
49 // If we manage to connect to the database with the user
50 // and password above it is our test database, and run
51 // these tests. If anything goes wrong, ignore these
52 // tests.
53 try
54 {
55 database = new MySQLManager(connect);
56 db = new MySQLUserData();
57 db.Initialise(connect);
58 }
59 catch (Exception e)
60 {
61 m_log.Error("Exception {0}", e);
62 Assert.Ignore();
63 }
64 }
65
66 [TestFixtureTearDown]
67 public void Cleanup()
68 {
69 if (db != null)
70 {
71 db.Dispose();
72 }
73 // if a new table is added, it has to be dropped here
74 if (database != null)
75 {
76 database.ExecuteSql("drop table migrations");
77 database.ExecuteSql("drop table users");
78 database.ExecuteSql("drop table userfriends");
79 database.ExecuteSql("drop table agents");
80 database.ExecuteSql("drop table avatarappearance");
81 database.ExecuteSql("drop table avatarattachments");
82 }
83 }
84 }
85}
generated by cgit v1.1 at 2024-11-10 10:14:21 +0000