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