aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridData.cs587
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLogData.cs146
-rw-r--r--OpenSim/Data/MSSQL/MSSQLRegionData.cs10
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserAccountData.cs17
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs1214
5 files changed, 26 insertions, 1948 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs
deleted file mode 100644
index 8a3d332..0000000
--- a/OpenSim/Data/MSSQL/MSSQLGridData.cs
+++ /dev/null
@@ -1,587 +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.Data.SqlClient;
32using System.Reflection;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.MSSQL
38{
39 /// <summary>
40 /// A grid data interface for MSSQL Server
41 /// </summary>
42 public class MSSQLGridData : GridDataBase
43 {
44 private const string _migrationStore = "GridStore";
45
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 /// <summary>
49 /// Database manager
50 /// </summary>
51 private MSSQLManager database;
52
53 private string m_regionsTableName = "regions";
54
55 #region IPlugin Members
56
57 // [Obsolete("Cannot be default-initialized!")]
58 override public void Initialise()
59 {
60 m_log.Info("[GRID DB]: " + Name + " cannot be default-initialized!");
61 throw new PluginNotInitialisedException(Name);
62 }
63
64 /// <summary>
65 /// Initialises the Grid Interface
66 /// </summary>
67 /// <param name="connectionString">connect string</param>
68 /// <remarks>use mssql_connection.ini</remarks>
69 override public void Initialise(string connectionString)
70 {
71 if (!string.IsNullOrEmpty(connectionString))
72 {
73 database = new MSSQLManager(connectionString);
74 }
75 else
76 {
77 // TODO: make the connect string actually do something
78 IniFile iniFile = new IniFile("mssql_connection.ini");
79
80 string settingDataSource = iniFile.ParseFileReadValue("data_source");
81 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
82 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
83 string settingUserId = iniFile.ParseFileReadValue("user_id");
84 string settingPassword = iniFile.ParseFileReadValue("password");
85
86 m_regionsTableName = iniFile.ParseFileReadValue("regionstablename");
87 if (m_regionsTableName == null)
88 {
89 m_regionsTableName = "regions";
90 }
91
92 database =
93 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
94 settingPassword);
95 }
96
97 //New migrations check of store
98 database.CheckMigration(_migrationStore);
99 }
100
101 /// <summary>
102 /// Shuts down the grid interface
103 /// </summary>
104 override public void Dispose()
105 {
106 database = null;
107 }
108
109 /// <summary>
110 /// The name of this DB provider.
111 /// </summary>
112 /// <returns>A string containing the storage system name</returns>
113 override public string Name
114 {
115 get { return "MSSQL OpenGridData"; }
116 }
117
118 /// <summary>
119 /// Database provider version.
120 /// </summary>
121 /// <returns>A string containing the storage system version</returns>
122 override public string Version
123 {
124 get { return "0.1"; }
125 }
126
127 #endregion
128
129 #region Public override GridDataBase methods
130
131 /// <summary>
132 /// Returns a list of regions within the specified ranges
133 /// </summary>
134 /// <param name="xmin">minimum X coordinate</param>
135 /// <param name="ymin">minimum Y coordinate</param>
136 /// <param name="xmax">maximum X coordinate</param>
137 /// <param name="ymax">maximum Y coordinate</param>
138 /// <returns>null</returns>
139 /// <remarks>always return null</remarks>
140 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
141 {
142 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax"))
143 {
144 command.Parameters.Add(database.CreateParameter("xmin", xmin));
145 command.Parameters.Add(database.CreateParameter("ymin", ymin));
146 command.Parameters.Add(database.CreateParameter("xmax", xmax));
147 command.Parameters.Add(database.CreateParameter("ymax", ymax));
148
149 List<RegionProfileData> rows = new List<RegionProfileData>();
150
151 using (SqlDataReader reader = command.ExecuteReader())
152 {
153 while (reader.Read())
154 {
155 rows.Add(ReadSimRow(reader));
156 }
157 }
158
159 if (rows.Count > 0)
160 {
161 return rows.ToArray();
162 }
163 }
164 m_log.Info("[GRID DB] : Found no regions within range.");
165 return null;
166 }
167
168
169 /// <summary>
170 /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
171 /// </summary>
172 /// <param name="namePrefix">The name to match against</param>
173 /// <param name="maxNum">Maximum number of profiles to return</param>
174 /// <returns>A list of sim profiles</returns>
175 override public List<RegionProfileData> GetRegionsByName (string namePrefix, uint maxNum)
176 {
177 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE regionName LIKE @name"))
178 {
179 command.Parameters.Add(database.CreateParameter("name", namePrefix + "%"));
180
181 List<RegionProfileData> rows = new List<RegionProfileData>();
182
183 using (SqlDataReader reader = command.ExecuteReader())
184 {
185 while (rows.Count < maxNum && reader.Read())
186 {
187 rows.Add(ReadSimRow(reader));
188 }
189 }
190
191 return rows;
192 }
193 }
194
195 /// <summary>
196 /// Returns a sim profile from its location
197 /// </summary>
198 /// <param name="handle">Region location handle</param>
199 /// <returns>Sim profile</returns>
200 override public RegionProfileData GetProfileByHandle(ulong handle)
201 {
202 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle"))
203 {
204 command.Parameters.Add(database.CreateParameter("handle", handle));
205
206 using (SqlDataReader reader = command.ExecuteReader())
207 {
208 if (reader.Read())
209 {
210 return ReadSimRow(reader);
211 }
212 }
213 }
214 m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle);
215 return null;
216 }
217
218 /// <summary>
219 /// Returns a sim profile from its UUID
220 /// </summary>
221 /// <param name="uuid">The region UUID</param>
222 /// <returns>The sim profile</returns>
223 override public RegionProfileData GetProfileByUUID(UUID uuid)
224 {
225 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid"))
226 {
227 command.Parameters.Add(database.CreateParameter("uuid", uuid));
228
229 using (SqlDataReader reader = command.ExecuteReader())
230 {
231 if (reader.Read())
232 {
233 return ReadSimRow(reader);
234 }
235 }
236 }
237 m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid);
238 return null;
239 }
240
241 /// <summary>
242 /// Returns a sim profile from it's Region name string
243 /// </summary>
244 /// <param name="regionName">The region name search query</param>
245 /// <returns>The sim profile</returns>
246 override public RegionProfileData GetProfileByString(string regionName)
247 {
248 if (regionName.Length > 2)
249 {
250 using (AutoClosingSqlCommand command = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName"))
251 {
252 command.Parameters.Add(database.CreateParameter("regionName", regionName + "%"));
253
254 using (SqlDataReader reader = command.ExecuteReader())
255 {
256 if (reader.Read())
257 {
258 return ReadSimRow(reader);
259 }
260 }
261 }
262 m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName);
263 return null;
264 }
265
266 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
267 return null;
268 }
269
270 /// <summary>
271 /// Adds a new specified region to the database
272 /// </summary>
273 /// <param name="profile">The profile to add</param>
274 /// <returns>A dataresponse enum indicating success</returns>
275 override public DataResponse StoreProfile(RegionProfileData profile)
276 {
277 if (GetProfileByUUID(profile.UUID) == null)
278 {
279 if (InsertRegionRow(profile))
280 {
281 return DataResponse.RESPONSE_OK;
282 }
283 }
284 else
285 {
286 if (UpdateRegionRow(profile))
287 {
288 return DataResponse.RESPONSE_OK;
289 }
290 }
291
292 return DataResponse.RESPONSE_ERROR;
293 }
294
295 /// <summary>
296 /// Deletes a sim profile from the database
297 /// </summary>
298 /// <param name="uuid">the sim UUID</param>
299 /// <returns>Successful?</returns>
300 //public DataResponse DeleteProfile(RegionProfileData profile)
301 override public DataResponse DeleteProfile(string uuid)
302 {
303 using (AutoClosingSqlCommand command = database.Query("DELETE FROM regions WHERE uuid = @uuid;"))
304 {
305 command.Parameters.Add(database.CreateParameter("uuid", uuid));
306 try
307 {
308 command.ExecuteNonQuery();
309 return DataResponse.RESPONSE_OK;
310 }
311 catch (Exception e)
312 {
313 m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message);
314 return DataResponse.RESPONSE_ERROR;
315 }
316 }
317 }
318
319 #endregion
320
321 #region Methods that are not used or deprecated (still needed because of base class)
322
323 /// <summary>
324 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
325 /// </summary>
326 /// <param name="uuid">The UUID of the challenger</param>
327 /// <param name="handle">The attempted regionHandle of the challenger</param>
328 /// <param name="authkey">The secret</param>
329 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
330 override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
331 {
332 bool throwHissyFit = false; // Should be true by 1.0
333
334 if (throwHissyFit)
335 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
336
337 RegionProfileData data = GetProfileByUUID(uuid);
338
339 return (handle == data.regionHandle && authkey == data.regionSecret);
340 }
341
342 /// <summary>
343 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
344 /// </summary>
345 /// <remarks>This requires a security audit.</remarks>
346 /// <param name="uuid"></param>
347 /// <param name="handle"></param>
348 /// <param name="authhash"></param>
349 /// <param name="challenge"></param>
350 /// <returns></returns>
351 public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
352 {
353 // SHA512Managed HashProvider = new SHA512Managed();
354 // Encoding TextProvider = new UTF8Encoding();
355
356 // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
357 // byte[] hash = HashProvider.ComputeHash(stream);
358 return false;
359 }
360
361 /// <summary>
362 /// NOT IMPLEMENTED
363 /// WHEN IS THIS GONNA BE IMPLEMENTED.
364 /// </summary>
365 /// <param name="x"></param>
366 /// <param name="y"></param>
367 /// <returns>null</returns>
368 override public ReservationData GetReservationAtPoint(uint x, uint y)
369 {
370 return null;
371 }
372
373 #endregion
374
375 #region private methods
376
377 /// <summary>
378 /// Reads a region row from a database reader
379 /// </summary>
380 /// <param name="reader">An active database reader</param>
381 /// <returns>A region profile</returns>
382 private static RegionProfileData ReadSimRow(IDataRecord reader)
383 {
384 RegionProfileData retval = new RegionProfileData();
385
386 // Region Main gotta-have-or-we-return-null parts
387 UInt64 tmp64;
388 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64))
389 {
390 return null;
391 }
392
393 retval.regionHandle = tmp64;
394
395// UUID tmp_uuid;
396// if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid))
397// {
398// return null;
399// }
400
401 retval.UUID = new UUID((Guid)reader["uuid"]); // tmp_uuid;
402
403 // non-critical parts
404 retval.regionName = reader["regionName"].ToString();
405 retval.originUUID = new UUID((Guid)reader["originUUID"]);
406
407 // Secrets
408 retval.regionRecvKey = reader["regionRecvKey"].ToString();
409 retval.regionSecret = reader["regionSecret"].ToString();
410 retval.regionSendKey = reader["regionSendKey"].ToString();
411
412 // Region Server
413 retval.regionDataURI = reader["regionDataURI"].ToString();
414 retval.regionOnline = false; // Needs to be pinged before this can be set.
415 retval.serverIP = reader["serverIP"].ToString();
416 retval.serverPort = Convert.ToUInt32(reader["serverPort"]);
417 retval.serverURI = reader["serverURI"].ToString();
418 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
419 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
420
421 // Location
422 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
423 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
424 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
425
426 // Neighbours - 0 = No Override
427 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
428 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
429 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
430 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
431
432 // Assets
433 retval.regionAssetURI = reader["regionAssetURI"].ToString();
434 retval.regionAssetRecvKey = reader["regionAssetRecvKey"].ToString();
435 retval.regionAssetSendKey = reader["regionAssetSendKey"].ToString();
436
437 // Userserver
438 retval.regionUserURI = reader["regionUserURI"].ToString();
439 retval.regionUserRecvKey = reader["regionUserRecvKey"].ToString();
440 retval.regionUserSendKey = reader["regionUserSendKey"].ToString();
441
442 // World Map Addition
443 retval.regionMapTextureID = new UUID((Guid)reader["regionMapTexture"]);
444 retval.owner_uuid = new UUID((Guid)reader["owner_uuid"]);
445 retval.maturity = Convert.ToUInt32(reader["access"]);
446 return retval;
447 }
448
449 /// <summary>
450 /// Update the specified region in the database
451 /// </summary>
452 /// <param name="profile">The profile to update</param>
453 /// <returns>success ?</returns>
454 private bool UpdateRegionRow(RegionProfileData profile)
455 {
456 bool returnval = false;
457
458 //Insert new region
459 string sql =
460 "UPDATE " + m_regionsTableName + @" SET
461 [regionHandle]=@regionHandle, [regionName]=@regionName,
462 [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey,
463 [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI,
464 [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle,
465 [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle,
466 [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI,
467 [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey,
468 [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey,
469 [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort,
470 [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid , [originUUID]=@originUUID
471 where [uuid]=@uuid";
472
473 using (AutoClosingSqlCommand command = database.Query(sql))
474 {
475 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
476 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
477 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
478 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
479 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
480 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
481 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
482 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
483 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
484 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
485 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
486 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
487 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
488 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
489 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
490 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
491 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
492 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
493 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
494 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
495 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
496 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
497 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
498 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
499 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
500 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
501 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
502 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
503
504 try
505 {
506 command.ExecuteNonQuery();
507 returnval = true;
508 }
509 catch (Exception e)
510 {
511 m_log.Error("[GRID DB] : Error updating region, error: " + e.Message);
512 }
513 }
514
515 return returnval;
516 }
517
518 /// <summary>
519 /// Creates a new region in the database
520 /// </summary>
521 /// <param name="profile">The region profile to insert</param>
522 /// <returns>Successful?</returns>
523 private bool InsertRegionRow(RegionProfileData profile)
524 {
525 bool returnval = false;
526
527 //Insert new region
528 string sql =
529 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
530 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
531 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
532 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
533 [serverRemotingPort], [owner_uuid], [originUUID], [access])
534 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
535 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
536 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
537 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID, @access);";
538
539 using (AutoClosingSqlCommand command = database.Query(sql))
540 {
541 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
542 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
543 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
544 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
545 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
546 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
547 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
548 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
549 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
550 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
551 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
552 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
553 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
554 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
555 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
556 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
557 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
558 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
559 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
560 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
561 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
562 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
563 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
564 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
565 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
566 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
567 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
568 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
569 command.Parameters.Add(database.CreateParameter("access", profile.maturity));
570
571 try
572 {
573 command.ExecuteNonQuery();
574 returnval = true;
575 }
576 catch (Exception e)
577 {
578 m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message);
579 }
580 }
581
582 return returnval;
583 }
584
585 #endregion
586 }
587}
diff --git a/OpenSim/Data/MSSQL/MSSQLLogData.cs b/OpenSim/Data/MSSQL/MSSQLLogData.cs
deleted file mode 100644
index 72c50e6..0000000
--- a/OpenSim/Data/MSSQL/MSSQLLogData.cs
+++ /dev/null
@@ -1,146 +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.Reflection;
30using log4net;
31using OpenSim.Framework;
32
33namespace OpenSim.Data.MSSQL
34{
35 /// <summary>
36 /// An interface to the log database for MSSQL
37 /// </summary>
38 internal class MSSQLLogData : ILogDataPlugin
39 {
40 private const string _migrationStore = "LogStore";
41
42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 public MSSQLManager database;
48
49 [Obsolete("Cannot be default-initialized!")]
50 public void Initialise()
51 {
52 m_log.Info("[LOG DB]: " + Name + " cannot be default-initialized!");
53 throw new PluginNotInitialisedException (Name);
54 }
55
56 /// <summary>
57 /// Artificial constructor called when the plugin is loaded
58 /// </summary>
59 public void Initialise(string connect)
60 {
61 if (!string.IsNullOrEmpty(connect))
62 {
63 database = new MSSQLManager(connect);
64 }
65 else
66 {
67 // TODO: do something with the connect string
68 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
69 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
70 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
71 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
72 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
73 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
74
75 database =
76 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
77 settingPassword);
78 }
79
80 //Updating mechanisme
81 database.CheckMigration(_migrationStore);
82 }
83
84 /// <summary>
85 /// Saves a log item to the database
86 /// </summary>
87 /// <param name="serverDaemon">The daemon triggering the event</param>
88 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
89 /// <param name="methodCall">The method call where the problem occured</param>
90 /// <param name="arguments">The arguments passed to the method</param>
91 /// <param name="priority">How critical is this?</param>
92 /// <param name="logMessage">The message to log</param>
93 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
94 string logMessage)
95 {
96 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
97 sql += "(@target, @server, @method, @arguments, @priority, @message);";
98
99 using (AutoClosingSqlCommand command = database.Query(sql))
100 {
101 command.Parameters.Add(database.CreateParameter("server", serverDaemon));
102 command.Parameters.Add(database.CreateParameter("target",target));
103 command.Parameters.Add(database.CreateParameter("method", methodCall));
104 command.Parameters.Add(database.CreateParameter("arguments", arguments));
105 command.Parameters.Add(database.CreateParameter("priority", priority.ToString()));
106 command.Parameters.Add(database.CreateParameter("message", logMessage));
107
108 try
109 {
110 command.ExecuteNonQuery();
111 }
112 catch (Exception e)
113 {
114 //Are we not in a loop here
115 m_log.Error("[LOG DB] Error logging : " + e.Message);
116 }
117 }
118 }
119
120 /// <summary>
121 /// Returns the name of this DB provider
122 /// </summary>
123 /// <returns>A string containing the DB provider name</returns>
124 public string Name
125 {
126 get { return "MSSQL Logdata Interface"; }
127 }
128
129 /// <summary>
130 /// Closes the database provider
131 /// </summary>
132 public void Dispose()
133 {
134 database = null;
135 }
136
137 /// <summary>
138 /// Returns the version of this DB provider
139 /// </summary>
140 /// <returns>A string containing the provider version</returns>
141 public string Version
142 {
143 get { return "0.1"; }
144 }
145 }
146}
diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
index a898aab..fbfb78e 100644
--- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
@@ -307,5 +307,15 @@ namespace OpenSim.Data.MSSQL
307 } 307 }
308 return false; 308 return false;
309 } 309 }
310
311 public List<RegionData> GetDefaultRegions(UUID scopeID)
312 {
313 return null;
314 }
315
316 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
317 {
318 return null;
319 }
310 } 320 }
311} 321}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
index 2d92cb1..01c64dc 100644
--- a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
@@ -65,7 +65,7 @@ namespace OpenSim.Data.MSSQL
65 public UserAccountData Get(UUID principalID, UUID scopeID) 65 public UserAccountData Get(UUID principalID, UUID scopeID)
66 { 66 {
67 UserAccountData ret = new UserAccountData(); 67 UserAccountData ret = new UserAccountData();
68 ret.Data = new Dictionary<string, object>(); 68 ret.Data = new Dictionary<string, string>();
69 69
70 string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm); 70 string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
71 if (scopeID != UUID.Zero) 71 if (scopeID != UUID.Zero)
@@ -168,6 +168,11 @@ namespace OpenSim.Data.MSSQL
168 return true; 168 return true;
169 } 169 }
170 170
171 public bool Store(UserAccountData data, UUID principalID, string token)
172 {
173 return false;
174 }
175
171 public bool SetDataItem(UUID principalID, string item, string value) 176 public bool SetDataItem(UUID principalID, string item, string value)
172 { 177 {
173 string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item); 178 string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
@@ -184,5 +189,15 @@ namespace OpenSim.Data.MSSQL
184 } 189 }
185 return false; 190 return false;
186 } 191 }
192
193 public UserAccountData[] Get(string[] keys, string[] vals)
194 {
195 return null;
196 }
197
198 public UserAccountData[] GetUsers(UUID scopeID, string query)
199 {
200 return null;
201 }
187 } 202 }
188} 203}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
deleted file mode 100644
index 3ef1053..0000000
--- a/OpenSim/Data/MSSQL/MSSQLUserData.cs
+++ /dev/null
@@ -1,1214 +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.Data.SqlClient;
33using System.Reflection;
34using log4net;
35using OpenMetaverse;
36using OpenSim.Framework;
37
38namespace OpenSim.Data.MSSQL
39{
40 /// <summary>
41 /// A database interface class to a user profile storage system
42 /// </summary>
43 public class MSSQLUserData : UserDataBase
44 {
45 private const string _migrationStore = "UserStore";
46
47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48
49 /// <summary>
50 /// Database manager for MSSQL
51 /// </summary>
52 public MSSQLManager database;
53
54 private const string m_agentsTableName = "agents";
55 private const string m_usersTableName = "users";
56 private const string m_userFriendsTableName = "userfriends";
57
58 // [Obsolete("Cannot be default-initialized!")]
59 override public void Initialise()
60 {
61 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
62 throw new PluginNotInitialisedException(Name);
63 }
64
65 /// <summary>
66 /// Loads and initialises the MSSQL storage plugin
67 /// </summary>
68 /// <param name="connect">connectionstring</param>
69 /// <remarks>use mssql_connection.ini</remarks>
70 override public void Initialise(string connect)
71 {
72 if (!string.IsNullOrEmpty(connect))
73 {
74 database = new MSSQLManager(connect);
75 }
76 else
77 {
78 IniFile iniFile = new IniFile("mssql_connection.ini");
79
80 string settingDataSource = iniFile.ParseFileReadValue("data_source");
81 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
82 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
83 string settingUserId = iniFile.ParseFileReadValue("user_id");
84 string settingPassword = iniFile.ParseFileReadValue("password");
85
86 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
87 }
88
89 //Check migration on DB
90 database.CheckMigration(_migrationStore);
91 }
92
93 /// <summary>
94 /// Releases unmanaged and - optionally - managed resources
95 /// </summary>
96 override public void Dispose() { }
97
98 #region User table methods
99
100 /// <summary>
101 /// Searches the database for a specified user profile by name components
102 /// </summary>
103 /// <param name="user">The first part of the account name</param>
104 /// <param name="last">The second part of the account name</param>
105 /// <returns>A user profile</returns>
106 override public UserProfileData GetUserByName(string user, string last)
107 {
108 string sql = string.Format(@"SELECT * FROM {0}
109 WHERE username = @first AND lastname = @second", m_usersTableName);
110 using (AutoClosingSqlCommand command = database.Query(sql))
111 {
112 command.Parameters.Add(database.CreateParameter("first", user));
113 command.Parameters.Add(database.CreateParameter("second", last));
114 try
115 {
116 using (SqlDataReader reader = command.ExecuteReader())
117 {
118 return ReadUserRow(reader);
119 }
120 }
121 catch (Exception e)
122 {
123 m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message);
124 return null;
125 }
126 }
127 }
128
129 /// <summary>
130 /// See IUserDataPlugin
131 /// </summary>
132 /// <param name="uuid"></param>
133 /// <returns></returns>
134 override public UserProfileData GetUserByUUID(UUID uuid)
135 {
136 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName);
137 using (AutoClosingSqlCommand command = database.Query(sql))
138 {
139 command.Parameters.Add(database.CreateParameter("uuid", uuid));
140 try
141 {
142 using (SqlDataReader reader = command.ExecuteReader())
143 {
144 return ReadUserRow(reader);
145 }
146 }
147 catch (Exception e)
148 {
149 m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message);
150 return null;
151 }
152 }
153 }
154
155
156 /// <summary>
157 /// Creates a new users profile
158 /// </summary>
159 /// <param name="user">The user profile to create</param>
160 override public void AddNewUserProfile(UserProfileData user)
161 {
162 try
163 {
164 InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
165 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
166 user.HomeLocation.Z,
167 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
168 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
169 user.CanDoMask, user.WantDoMask,
170 user.AboutText, user.FirstLifeAboutText, user.Image,
171 user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID,
172 user.GodLevel, user.UserFlags, user.CustomType, user.Partner);
173 }
174 catch (Exception e)
175 {
176 m_log.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message);
177 }
178 }
179
180 /// <summary>
181 /// update a user profile
182 /// </summary>
183 /// <param name="user">the profile to update</param>
184 /// <returns></returns>
185 override public bool UpdateUserProfile(UserProfileData user)
186 {
187 string sql = string.Format(@"UPDATE {0}
188 SET UUID = @uuid,
189 username = @username,
190 lastname = @lastname,
191 email = @email,
192 passwordHash = @passwordHash,
193 passwordSalt = @passwordSalt,
194 homeRegion = @homeRegion,
195 homeLocationX = @homeLocationX,
196 homeLocationY = @homeLocationY,
197 homeLocationZ = @homeLocationZ,
198 homeLookAtX = @homeLookAtX,
199 homeLookAtY = @homeLookAtY,
200 homeLookAtZ = @homeLookAtZ,
201 created = @created,
202 lastLogin = @lastLogin,
203 userInventoryURI = @userInventoryURI,
204 userAssetURI = @userAssetURI,
205 profileCanDoMask = @profileCanDoMask,
206 profileWantDoMask = @profileWantDoMask,
207 profileAboutText = @profileAboutText,
208 profileFirstText = @profileFirstText,
209 profileImage = @profileImage,
210 profileFirstImage = @profileFirstImage,
211 webLoginKey = @webLoginKey,
212 homeRegionID = @homeRegionID,
213 userFlags = @userFlags,
214 godLevel = @godLevel,
215 customType = @customType,
216 partner = @partner WHERE UUID = @keyUUUID;",m_usersTableName);
217 using (AutoClosingSqlCommand command = database.Query(sql))
218 {
219 command.Parameters.Add(database.CreateParameter("uuid", user.ID));
220 command.Parameters.Add(database.CreateParameter("username", user.FirstName));
221 command.Parameters.Add(database.CreateParameter("lastname", user.SurName));
222 command.Parameters.Add(database.CreateParameter("email", user.Email));
223 command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash));
224 command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt));
225 command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion));
226 command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X));
227 command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y));
228 command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z));
229 command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X));
230 command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y));
231 command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z));
232 command.Parameters.Add(database.CreateParameter("created", user.Created));
233 command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin));
234 command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI));
235 command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI));
236 command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask));
237 command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask));
238 command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText));
239 command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText));
240 command.Parameters.Add(database.CreateParameter("profileImage", user.Image));
241 command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage));
242 command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey));
243 command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID));
244 command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags));
245 command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel));
246 command.Parameters.Add(database.CreateParameter("customType", user.CustomType));
247 command.Parameters.Add(database.CreateParameter("partner", user.Partner));
248 command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID));
249
250 try
251 {
252 int affected = command.ExecuteNonQuery();
253 return (affected != 0);
254 }
255 catch (Exception e)
256 {
257 m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message);
258 }
259 }
260 return false;
261 }
262
263 #endregion
264
265 #region Agent table methods
266
267 /// <summary>
268 /// Returns a user session searching by name
269 /// </summary>
270 /// <param name="name">The account name</param>
271 /// <returns>The users session</returns>
272 override public UserAgentData GetAgentByName(string name)
273 {
274 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
275 }
276
277 /// <summary>
278 /// Returns a user session by account name
279 /// </summary>
280 /// <param name="user">First part of the users account name</param>
281 /// <param name="last">Second part of the users account name</param>
282 /// <returns>The users session</returns>
283 override public UserAgentData GetAgentByName(string user, string last)
284 {
285 UserProfileData profile = GetUserByName(user, last);
286 return GetAgentByUUID(profile.ID);
287 }
288
289 /// <summary>
290 /// Returns an agent session by account UUID
291 /// </summary>
292 /// <param name="uuid">The accounts UUID</param>
293 /// <returns>The users session</returns>
294 override public UserAgentData GetAgentByUUID(UUID uuid)
295 {
296 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName);
297 using (AutoClosingSqlCommand command = database.Query(sql))
298 {
299 command.Parameters.Add(database.CreateParameter("uuid", uuid));
300 try
301 {
302 using (SqlDataReader reader = command.ExecuteReader())
303 {
304 return readAgentRow(reader);
305 }
306 }
307 catch (Exception e)
308 {
309 m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message);
310 return null;
311 }
312 }
313 }
314
315 /// <summary>
316 /// Creates a new agent
317 /// </summary>
318 /// <param name="agent">The agent to create</param>
319 override public void AddNewUserAgent(UserAgentData agent)
320 {
321 try
322 {
323 InsertUpdateAgentRow(agent);
324 }
325 catch (Exception e)
326 {
327 m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message);
328 }
329 }
330
331 #endregion
332
333 #region User Friends List Data
334
335 /// <summary>
336 /// Add a new friend in the friendlist
337 /// </summary>
338 /// <param name="friendlistowner">UUID of the friendlist owner</param>
339 /// <param name="friend">Friend's UUID</param>
340 /// <param name="perms">Permission flag</param>
341 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
342 {
343 int dtvalue = Util.UnixTimeSinceEpoch();
344 string sql = string.Format(@"INSERT INTO {0}
345 (ownerID,friendID,friendPerms,datetimestamp)
346 VALUES
347 (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
348 using (AutoClosingSqlCommand command = database.Query(sql))
349 {
350 command.Parameters.Add(database.CreateParameter("ownerID", friendlistowner));
351 command.Parameters.Add(database.CreateParameter("friendID", friend));
352 command.Parameters.Add(database.CreateParameter("friendPerms", perms));
353 command.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue));
354 command.ExecuteNonQuery();
355
356 try
357 {
358 sql = string.Format(@"INSERT INTO {0}
359 (ownerID,friendID,friendPerms,datetimestamp)
360 VALUES
361 (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
362 command.CommandText = sql;
363 command.ExecuteNonQuery();
364 }
365 catch (Exception e)
366 {
367 m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message);
368 return;
369 }
370 }
371 }
372
373 /// <summary>
374 /// Remove an friend from the friendlist
375 /// </summary>
376 /// <param name="friendlistowner">UUID of the friendlist owner</param>
377 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param>
378 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
379 {
380 string sql = string.Format(@"DELETE from {0}
381 WHERE ownerID = @ownerID
382 AND friendID = @friendID", m_userFriendsTableName);
383 using (AutoClosingSqlCommand command = database.Query(sql))
384 {
385 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
386 command.Parameters.Add(database.CreateParameter("@friendID", friend));
387 command.ExecuteNonQuery();
388 sql = string.Format(@"DELETE from {0}
389 WHERE ownerID = @friendID
390 AND friendID = @ownerID", m_userFriendsTableName);
391 command.CommandText = sql;
392 try
393 {
394 command.ExecuteNonQuery();
395 }
396 catch (Exception e)
397 {
398 m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message);
399 }
400 }
401 }
402
403 /// <summary>
404 /// Update friendlist permission flag for a friend
405 /// </summary>
406 /// <param name="friendlistowner">UUID of the friendlist owner</param>
407 /// <param name="friend">UUID of the friend</param>
408 /// <param name="perms">new permission flag</param>
409 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
410 {
411 string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms
412 WHERE ownerID = @ownerID
413 AND friendID = @friendID", m_userFriendsTableName);
414 using (AutoClosingSqlCommand command = database.Query(sql))
415 {
416 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
417 command.Parameters.Add(database.CreateParameter("@friendID", friend));
418 command.Parameters.Add(database.CreateParameter("@friendPerms", perms));
419
420 try
421 {
422 command.ExecuteNonQuery();
423 }
424 catch (Exception e)
425 {
426 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
427 }
428 }
429 }
430
431 /// <summary>
432 /// Get (fetch?) the user's friendlist
433 /// </summary>
434 /// <param name="friendlistowner">UUID of the friendlist owner</param>
435 /// <returns>Friendlist list</returns>
436 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
437 {
438 List<FriendListItem> friendList = new List<FriendListItem>();
439
440 //Left Join userfriends to itself
441 string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms
442 FROM {0} as a, {0} as b
443 WHERE a.ownerID = @ownerID
444 AND b.ownerID = a.friendID
445 AND b.friendID = a.ownerID", m_userFriendsTableName);
446 using (AutoClosingSqlCommand command = database.Query(sql))
447 {
448 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
449 try
450 {
451 using (SqlDataReader reader = command.ExecuteReader())
452 {
453 while (reader.Read())
454 {
455 FriendListItem fli = new FriendListItem();
456 fli.FriendListOwner = new UUID((Guid)reader["ownerID"]);
457 fli.Friend = new UUID((Guid)reader["friendID"]);
458 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
459
460 // This is not a real column in the database table, it's a joined column from the opposite record
461 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
462 friendList.Add(fli);
463 }
464 }
465 }
466 catch (Exception e)
467 {
468 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
469 }
470 }
471 return friendList;
472 }
473
474 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
475 {
476 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
477 try
478 {
479 foreach (UUID uuid in uuids)
480 {
481 string sql = string.Format(@"SELECT agentOnline,currentHandle
482 FROM {0} WHERE UUID = @uuid", m_agentsTableName);
483 using (AutoClosingSqlCommand command = database.Query(sql))
484 {
485 command.Parameters.Add(database.CreateParameter("@uuid", uuid));
486 using (SqlDataReader reader = command.ExecuteReader())
487 {
488 while (reader.Read())
489 {
490 FriendRegionInfo fri = new FriendRegionInfo();
491 fri.isOnline = (byte)reader["agentOnline"] != 0;
492 fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
493
494 infos[uuid] = fri;
495 }
496 }
497 }
498 }
499 }
500 catch (Exception e)
501 {
502 m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e);
503 }
504
505 return infos;
506 }
507 #endregion
508
509 #region Money functions (not used)
510
511 /// <summary>
512 /// Performs a money transfer request between two accounts
513 /// </summary>
514 /// <param name="from">The senders account ID</param>
515 /// <param name="to">The receivers account ID</param>
516 /// <param name="amount">The amount to transfer</param>
517 /// <returns>false</returns>
518 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
519 {
520 return false;
521 }
522
523 /// <summary>
524 /// Performs an inventory transfer request between two accounts
525 /// </summary>
526 /// <remarks>TODO: Move to inventory server</remarks>
527 /// <param name="from">The senders account ID</param>
528 /// <param name="to">The receivers account ID</param>
529 /// <param name="item">The item to transfer</param>
530 /// <returns>false</returns>
531 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
532 {
533 return false;
534 }
535
536 #endregion
537
538 #region Appearance methods
539
540 /// <summary>
541 /// Gets the user appearance.
542 /// </summary>
543 /// <param name="user">The user.</param>
544 /// <returns></returns>
545 override public AvatarAppearance GetUserAppearance(UUID user)
546 {
547 try
548 {
549 AvatarAppearance appearance = new AvatarAppearance();
550 string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID";
551 using (AutoClosingSqlCommand command = database.Query(sql))
552 {
553 command.Parameters.Add(database.CreateParameter("@UUID", user));
554 using (SqlDataReader reader = command.ExecuteReader())
555 {
556 if (reader.Read())
557 appearance = readUserAppearance(reader);
558 else
559 {
560 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
561 return null;
562 }
563
564 }
565 }
566
567 appearance.SetAttachments(GetUserAttachments(user));
568
569 return appearance;
570 }
571 catch (Exception e)
572 {
573 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
574 }
575 return null;
576 }
577
578 /// <summary>
579 /// Update a user appearence into database
580 /// </summary>
581 /// <param name="user">the used UUID</param>
582 /// <param name="appearance">the appearence</param>
583 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
584 {
585 string sql = @"DELETE FROM avatarappearance WHERE owner=@owner;
586 INSERT INTO avatarappearance
587 (owner, serial, visual_params, texture, avatar_height,
588 body_item, body_asset, skin_item, skin_asset, hair_item,
589 hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset,
590 pants_item, pants_asset, shoes_item, shoes_asset, socks_item,
591 socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset,
592 undershirt_item, undershirt_asset, underpants_item, underpants_asset,
593 skirt_item, skirt_asset)
594 VALUES
595 (@owner, @serial, @visual_params, @texture, @avatar_height,
596 @body_item, @body_asset, @skin_item, @skin_asset, @hair_item,
597 @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset,
598 @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item,
599 @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset,
600 @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset,
601 @skirt_item, @skirt_asset)";
602
603 using (AutoClosingSqlCommand cmd = database.Query(sql))
604 {
605 cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner));
606 cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial));
607 cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams));
608 cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.GetBytes()));
609 cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight));
610 cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem));
611 cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset));
612 cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem));
613 cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset));
614 cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem));
615 cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset));
616 cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem));
617 cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset));
618 cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem));
619 cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset));
620 cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem));
621 cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset));
622 cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem));
623 cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset));
624 cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem));
625 cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset));
626 cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem));
627 cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset));
628 cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem));
629 cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset));
630 cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem));
631 cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset));
632 cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem));
633 cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset));
634 cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem));
635 cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset));
636
637 try
638 {
639 cmd.ExecuteNonQuery();
640 }
641 catch (Exception e)
642 {
643 m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", e.Message);
644 }
645 }
646 UpdateUserAttachments(user, appearance.GetAttachments());
647 }
648
649 #endregion
650
651 #region Attachment methods
652
653 /// <summary>
654 /// Gets all attachment of a agent.
655 /// </summary>
656 /// <param name="agentID">agent ID.</param>
657 /// <returns></returns>
658 public Hashtable GetUserAttachments(UUID agentID)
659 {
660 Hashtable returnTable = new Hashtable();
661 string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid";
662 using (AutoClosingSqlCommand command = database.Query(sql, database.CreateParameter("@uuid", agentID)))
663 {
664 using (SqlDataReader reader = command.ExecuteReader())
665 {
666 while (reader.Read())
667 {
668 int attachpoint = Convert.ToInt32(reader["attachpoint"]);
669 if (returnTable.ContainsKey(attachpoint))
670 continue;
671 Hashtable item = new Hashtable();
672 item.Add("item", reader["item"].ToString());
673 item.Add("asset", reader["asset"].ToString());
674
675 returnTable.Add(attachpoint, item);
676 }
677 }
678 }
679 return returnTable;
680 }
681
682 /// <summary>
683 /// Updates all attachments of the agent.
684 /// </summary>
685 /// <param name="agentID">agentID.</param>
686 /// <param name="data">data with all items on attachmentpoints</param>
687 public void UpdateUserAttachments(UUID agentID, Hashtable data)
688 {
689 string sql = "DELETE FROM avatarattachments WHERE UUID = @uuid";
690
691 using (AutoClosingSqlCommand command = database.Query(sql))
692 {
693 command.Parameters.Add(database.CreateParameter("uuid", agentID));
694 command.ExecuteNonQuery();
695 }
696 if (data == null)
697 return;
698
699 sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset)
700 VALUES (@uuid, @attachpoint, @item, @asset)";
701
702 using (AutoClosingSqlCommand command = database.Query(sql))
703 {
704 bool firstTime = true;
705 foreach (DictionaryEntry e in data)
706 {
707 int attachpoint = Convert.ToInt32(e.Key);
708
709 Hashtable item = (Hashtable)e.Value;
710
711 if (firstTime)
712 {
713 command.Parameters.Add(database.CreateParameter("@uuid", agentID));
714 command.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint));
715 command.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString())));
716 command.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString())));
717 firstTime = false;
718 }
719 command.Parameters["@uuid"].Value = agentID.Guid; //.ToString();
720 command.Parameters["@attachpoint"].Value = attachpoint;
721 command.Parameters["@item"].Value = new Guid(item["item"].ToString());
722 command.Parameters["@asset"].Value = new Guid(item["asset"].ToString());
723
724 try
725 {
726 command.ExecuteNonQuery();
727 }
728 catch (Exception ex)
729 {
730 m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message);
731 }
732 }
733 }
734 }
735
736 /// <summary>
737 /// Resets all attachments of a agent in the database.
738 /// </summary>
739 /// <param name="agentID">agentID.</param>
740 override public void ResetAttachments(UUID agentID)
741 {
742 string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid";
743 using (AutoClosingSqlCommand command = database.Query(sql))
744 {
745 command.Parameters.Add(database.CreateParameter("uuid", agentID));
746 command.ExecuteNonQuery();
747 }
748 }
749
750 override public void LogoutUsers(UUID regionID)
751 {
752 }
753
754 #endregion
755
756 #region Other public methods
757
758 /// <summary>
759 ///
760 /// </summary>
761 /// <param name="queryID"></param>
762 /// <param name="query"></param>
763 /// <returns></returns>
764 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
765 {
766 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
767 string[] querysplit = query.Split(' ');
768 if (querysplit.Length == 2)
769 {
770 try
771 {
772 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
773 WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName);
774 using (AutoClosingSqlCommand command = database.Query(sql))
775 {
776 //Add wildcard to the search
777 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
778 command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%"));
779 using (SqlDataReader reader = command.ExecuteReader())
780 {
781 while (reader.Read())
782 {
783 AvatarPickerAvatar user = new AvatarPickerAvatar();
784 user.AvatarID = new UUID((Guid)reader["UUID"]);
785 user.firstName = (string)reader["username"];
786 user.lastName = (string)reader["lastname"];
787 returnlist.Add(user);
788 }
789 }
790 }
791 }
792 catch (Exception e)
793 {
794 m_log.Error(e.ToString());
795 }
796 }
797 else if (querysplit.Length == 1)
798 {
799 try
800 {
801 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
802 WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName);
803 using (AutoClosingSqlCommand command = database.Query(sql))
804 {
805 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
806
807 using (SqlDataReader reader = command.ExecuteReader())
808 {
809 while (reader.Read())
810 {
811 AvatarPickerAvatar user = new AvatarPickerAvatar();
812 user.AvatarID = new UUID((Guid)reader["UUID"]);
813 user.firstName = (string)reader["username"];
814 user.lastName = (string)reader["lastname"];
815 returnlist.Add(user);
816 }
817 }
818 }
819 }
820 catch (Exception e)
821 {
822 m_log.Error(e.ToString());
823 }
824 }
825 return returnlist;
826 }
827
828 /// <summary>
829 /// Store a weblogin key
830 /// </summary>
831 /// <param name="AgentID">The agent UUID</param>
832 /// <param name="WebLoginKey">the WebLogin Key</param>
833 /// <remarks>unused ?</remarks>
834 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
835 {
836 UserProfileData user = GetUserByUUID(AgentID);
837 user.WebLoginKey = WebLoginKey;
838 UpdateUserProfile(user);
839 }
840
841 /// <summary>
842 /// Database provider name
843 /// </summary>
844 /// <returns>Provider name</returns>
845 override public string Name
846 {
847 get { return "MSSQL Userdata Interface"; }
848 }
849
850 /// <summary>
851 /// Database provider version
852 /// </summary>
853 /// <returns>provider version</returns>
854 override public string Version
855 {
856 get { return database.getVersion(); }
857 }
858
859 #endregion
860
861 #region Private functions
862
863 /// <summary>
864 /// Reads a one item from an SQL result
865 /// </summary>
866 /// <param name="reader">The SQL Result</param>
867 /// <returns>the item read</returns>
868 private static AvatarAppearance readUserAppearance(SqlDataReader reader)
869 {
870 try
871 {
872 AvatarAppearance appearance = new AvatarAppearance();
873
874 appearance.Owner = new UUID((Guid)reader["owner"]);
875 appearance.Serial = Convert.ToInt32(reader["serial"]);
876 appearance.VisualParams = (byte[])reader["visual_params"];
877 appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length);
878 appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]);
879 appearance.BodyItem = new UUID((Guid)reader["body_item"]);
880 appearance.BodyAsset = new UUID((Guid)reader["body_asset"]);
881 appearance.SkinItem = new UUID((Guid)reader["skin_item"]);
882 appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]);
883 appearance.HairItem = new UUID((Guid)reader["hair_item"]);
884 appearance.HairAsset = new UUID((Guid)reader["hair_asset"]);
885 appearance.EyesItem = new UUID((Guid)reader["eyes_item"]);
886 appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]);
887 appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]);
888 appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]);
889 appearance.PantsItem = new UUID((Guid)reader["pants_item"]);
890 appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]);
891 appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]);
892 appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]);
893 appearance.SocksItem = new UUID((Guid)reader["socks_item"]);
894 appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]);
895 appearance.JacketItem = new UUID((Guid)reader["jacket_item"]);
896 appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]);
897 appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]);
898 appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]);
899 appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]);
900 appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]);
901 appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]);
902 appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]);
903 appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]);
904 appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]);
905
906 return appearance;
907 }
908 catch (SqlException e)
909 {
910 m_log.Error(e.ToString());
911 }
912
913 return null;
914 }
915
916 /// <summary>
917 /// Insert/Update a agent row in the DB.
918 /// </summary>
919 /// <param name="agentdata">agentdata.</param>
920 private void InsertUpdateAgentRow(UserAgentData agentdata)
921 {
922 string sql = @"
923
924IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
925 BEGIN
926 UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos
927 WHERE UUID = @UUID
928 END
929ELSE
930 BEGIN
931 INSERT INTO
932 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
933 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
934 END
935";
936
937 using (AutoClosingSqlCommand command = database.Query(sql))
938 {
939 command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID));
940 command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID));
941 command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID));
942 command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP));
943 command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort));
944 command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline));
945 command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime));
946 command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime));
947 command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region));
948 command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle));
949 command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">"));
950
951 command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
952 try
953 {
954 if (command.ExecuteNonQuery() > 0)
955 {
956 command.Transaction.Commit();
957 return;
958 }
959
960 command.Transaction.Rollback();
961 return;
962 }
963 catch (Exception e)
964 {
965 command.Transaction.Rollback();
966 m_log.Error(e.ToString());
967 return;
968 }
969 }
970
971 }
972
973 /// <summary>
974 /// Reads an agent row from a database reader
975 /// </summary>
976 /// <param name="reader">An active database reader</param>
977 /// <returns>A user session agent</returns>
978 private UserAgentData readAgentRow(SqlDataReader reader)
979 {
980 UserAgentData retval = new UserAgentData();
981
982 if (reader.Read())
983 {
984 // Agent IDs
985 retval.ProfileID = new UUID((Guid)reader["UUID"]);
986 retval.SessionID = new UUID((Guid)reader["sessionID"]);
987 retval.SecureSessionID = new UUID((Guid)reader["secureSessionID"]);
988
989 // Agent Who?
990 retval.AgentIP = (string)reader["agentIP"];
991 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
992 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
993
994 // Login/Logout times (UNIX Epoch)
995 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
996 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
997
998 // Current position
999 retval.Region = new UUID((Guid)reader["currentRegion"]);
1000 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
1001 Vector3 tmp_v;
1002 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
1003 retval.Position = tmp_v;
1004
1005 }
1006 else
1007 {
1008 return null;
1009 }
1010 return retval;
1011 }
1012
1013 /// <summary>
1014 /// Creates a new user and inserts it into the database
1015 /// </summary>
1016 /// <param name="uuid">User ID</param>
1017 /// <param name="username">First part of the login</param>
1018 /// <param name="lastname">Second part of the login</param>
1019 /// <param name="email">Email of person</param>
1020 /// <param name="passwordHash">A salted hash of the users password</param>
1021 /// <param name="passwordSalt">The salt used for the password hash</param>
1022 /// <param name="homeRegion">A regionHandle of the users home region</param>
1023 /// <param name="homeLocX">Home region position vector</param>
1024 /// <param name="homeLocY">Home region position vector</param>
1025 /// <param name="homeLocZ">Home region position vector</param>
1026 /// <param name="homeLookAtX">Home region 'look at' vector</param>
1027 /// <param name="homeLookAtY">Home region 'look at' vector</param>
1028 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
1029 /// <param name="created">Account created (unix timestamp)</param>
1030 /// <param name="lastlogin">Last login (unix timestamp)</param>
1031 /// <param name="inventoryURI">Users inventory URI</param>
1032 /// <param name="assetURI">Users asset URI</param>
1033 /// <param name="canDoMask">I can do mask</param>
1034 /// <param name="wantDoMask">I want to do mask</param>
1035 /// <param name="aboutText">Profile text</param>
1036 /// <param name="firstText">Firstlife text</param>
1037 /// <param name="profileImage">UUID for profile image</param>
1038 /// <param name="firstImage">UUID for firstlife image</param>
1039 /// <param name="webLoginKey">web login key</param>
1040 /// <param name="homeRegionID">homeregion UUID</param>
1041 /// <param name="godLevel">has the user godlevel</param>
1042 /// <param name="userFlags">unknown</param>
1043 /// <param name="customType">unknown</param>
1044 /// <param name="partnerID">UUID of partner</param>
1045 /// <returns>Success?</returns>
1046 private void InsertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
1047 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
1048 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
1049 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
1050 string aboutText, string firstText,
1051 UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID,
1052 int godLevel, int userFlags, string customType, UUID partnerID)
1053 {
1054 string sql = string.Format(@"INSERT INTO {0}
1055 ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt],
1056 [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX],
1057 [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI],
1058 [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText],
1059 [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey],
1060 [homeRegionID], [userFlags], [godLevel], [customType], [partner])
1061 VALUES
1062 (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt,
1063 @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX,
1064 @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI,
1065 @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText,
1066 @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey,
1067 @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName);
1068
1069 try
1070 {
1071 using (AutoClosingSqlCommand command = database.Query(sql))
1072 {
1073 command.Parameters.Add(database.CreateParameter("UUID", uuid));
1074 command.Parameters.Add(database.CreateParameter("username", username));
1075 command.Parameters.Add(database.CreateParameter("lastname", lastname));
1076 command.Parameters.Add(database.CreateParameter("email", email));
1077 command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash));
1078 command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt));
1079 command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion));
1080 command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX));
1081 command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY));
1082 command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ));
1083 command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX));
1084 command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY));
1085 command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ));
1086 command.Parameters.Add(database.CreateParameter("created", created));
1087 command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin));
1088 command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI));
1089 command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI));
1090 command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask));
1091 command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask));
1092 command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText));
1093 command.Parameters.Add(database.CreateParameter("profileFirstText", firstText));
1094 command.Parameters.Add(database.CreateParameter("profileImage", profileImage));
1095 command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage));
1096 command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey));
1097 command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID));
1098 command.Parameters.Add(database.CreateParameter("userFlags", userFlags));
1099 command.Parameters.Add(database.CreateParameter("godLevel", godLevel));
1100 command.Parameters.Add(database.CreateParameter("customType", customType));
1101 command.Parameters.Add(database.CreateParameter("partner", partnerID));
1102
1103 command.ExecuteNonQuery();
1104 return;
1105 }
1106 }
1107 catch (Exception e)
1108 {
1109 m_log.Error(e.ToString());
1110 return;
1111 }
1112 }
1113
1114 /// <summary>
1115 /// Reads a user profile from an active data reader
1116 /// </summary>
1117 /// <param name="reader">An active database reader</param>
1118 /// <returns>A user profile</returns>
1119 private static UserProfileData ReadUserRow(SqlDataReader reader)
1120 {
1121 UserProfileData retval = new UserProfileData();
1122
1123 if (reader.Read())
1124 {
1125 retval.ID = new UUID((Guid)reader["UUID"]);
1126 retval.FirstName = (string)reader["username"];
1127 retval.SurName = (string)reader["lastname"];
1128 if (reader.IsDBNull(reader.GetOrdinal("email")))
1129 retval.Email = "";
1130 else
1131 retval.Email = (string)reader["email"];
1132
1133 retval.PasswordHash = (string)reader["passwordHash"];
1134 retval.PasswordSalt = (string)reader["passwordSalt"];
1135
1136 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
1137 retval.HomeLocation = new Vector3(
1138 Convert.ToSingle(reader["homeLocationX"].ToString()),
1139 Convert.ToSingle(reader["homeLocationY"].ToString()),
1140 Convert.ToSingle(reader["homeLocationZ"].ToString()));
1141 retval.HomeLookAt = new Vector3(
1142 Convert.ToSingle(reader["homeLookAtX"].ToString()),
1143 Convert.ToSingle(reader["homeLookAtY"].ToString()),
1144 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
1145
1146 if (reader.IsDBNull(reader.GetOrdinal("homeRegionID")))
1147 retval.HomeRegionID = UUID.Zero;
1148 else
1149 retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]);
1150
1151 retval.Created = Convert.ToInt32(reader["created"].ToString());
1152 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
1153
1154 if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI")))
1155 retval.UserInventoryURI = "";
1156 else
1157 retval.UserInventoryURI = (string)reader["userInventoryURI"];
1158
1159 if (reader.IsDBNull(reader.GetOrdinal("userAssetURI")))
1160 retval.UserAssetURI = "";
1161 else
1162 retval.UserAssetURI = (string)reader["userAssetURI"];
1163
1164 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
1165 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
1166
1167
1168 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
1169 retval.AboutText = "";
1170 else
1171 retval.AboutText = (string)reader["profileAboutText"];
1172
1173 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
1174 retval.FirstLifeAboutText = "";
1175 else
1176 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
1177
1178 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
1179 retval.Image = UUID.Zero;
1180 else
1181 retval.Image = new UUID((Guid)reader["profileImage"]);
1182
1183 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
1184 retval.Image = UUID.Zero;
1185 else
1186 retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]);
1187
1188 if (reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
1189 retval.WebLoginKey = UUID.Zero;
1190 else
1191 retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]);
1192
1193 retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString());
1194 retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString());
1195 if (reader.IsDBNull(reader.GetOrdinal("customType")))
1196 retval.CustomType = "";
1197 else
1198 retval.CustomType = reader["customType"].ToString();
1199
1200 if (reader.IsDBNull(reader.GetOrdinal("partner")))
1201 retval.Partner = UUID.Zero;
1202 else
1203 retval.Partner = new UUID((Guid)reader["partner"]);
1204 }
1205 else
1206 {
1207 return null;
1208 }
1209 return retval;
1210 }
1211 #endregion
1212 }
1213
1214}
generated by cgit v1.1 at 2024-11-09 11:55:04 +0000