aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLManager.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLManager.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs909
1 files changed, 909 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
new file mode 100644
index 0000000..579667b
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLManager.cs
@@ -0,0 +1,909 @@
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 OpenSim 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.IO;
33using System.Reflection;
34using libsecondlife;
35using MySql.Data.MySqlClient;
36using OpenSim.Framework.Console;
37
38namespace OpenSim.Framework.Data.MySQL
39{
40 /// <summary>
41 /// A MySQL Database manager
42 /// </summary>
43 internal class MySQLManager
44 {
45 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
46
47 /// <summary>
48 /// The database connection object
49 /// </summary>
50 private MySqlConnection dbcon;
51
52 /// <summary>
53 /// Connection string for ADO.net
54 /// </summary>
55 private string connectionString;
56
57 /// <summary>
58 /// Initialises and creates a new MySQL connection and maintains it.
59 /// </summary>
60 /// <param name="hostname">The MySQL server being connected to</param>
61 /// <param name="database">The name of the MySQL database being used</param>
62 /// <param name="username">The username logging into the database</param>
63 /// <param name="password">The password for the user logging in</param>
64 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param>
65 public MySQLManager(string hostname, string database, string username, string password, string cpooling,
66 string port)
67 {
68 try
69 {
70 connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" +
71 username + ";Password=" + password + ";Pooling=" + cpooling + ";";
72 dbcon = new MySqlConnection(connectionString);
73
74 try
75 {
76 dbcon.Open();
77 }
78 catch(Exception e)
79 {
80 throw new Exception( "Connection error while using connection string ["+connectionString+"]", e );
81 }
82
83 m_log.Info("[MYSQL]: Connection established");
84 }
85 catch (Exception e)
86 {
87 throw new Exception("Error initialising MySql Database: " + e.ToString());
88 }
89 }
90
91 /// <summary>
92 /// Get the connection being used
93 /// </summary>
94 public MySqlConnection Connection
95 {
96 get { return dbcon; }
97 }
98
99 /// <summary>
100 /// Shuts down the database connection
101 /// </summary>
102 public void Close()
103 {
104 dbcon.Close();
105 dbcon = null;
106 }
107
108 /// <summary>
109 /// Reconnects to the database
110 /// </summary>
111 public void Reconnect()
112 {
113 lock (dbcon)
114 {
115 try
116 {
117 // Close the DB connection
118 dbcon.Close();
119 // Try reopen it
120 dbcon = new MySqlConnection(connectionString);
121 dbcon.Open();
122 }
123 catch (Exception e)
124 {
125 m_log.Error("Unable to reconnect to database " + e.ToString());
126 }
127 }
128 }
129
130 /// <summary>
131 /// Returns the version of this DB provider
132 /// </summary>
133 /// <returns>A string containing the DB provider</returns>
134 public string getVersion()
135 {
136 Module module = GetType().Module;
137 string dllName = module.Assembly.ManifestModule.Name;
138 Version dllVersion = module.Assembly.GetName().Version;
139
140 return
141 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
142 dllVersion.Revision);
143 }
144
145 /// <summary>
146 /// Extract a named string resource from the embedded resources
147 /// </summary>
148 /// <param name="name">name of embedded resource</param>
149 /// <returns>string contained within the embedded resource</returns>
150 private string getResourceString(string name)
151 {
152 Assembly assem = GetType().Assembly;
153 string[] names = assem.GetManifestResourceNames();
154
155 foreach (string s in names)
156 {
157 if (s.EndsWith(name))
158 {
159 using (Stream resource = assem.GetManifestResourceStream(s))
160 {
161 using (StreamReader resourceReader = new StreamReader(resource))
162 {
163 string resourceString = resourceReader.ReadToEnd();
164 return resourceString;
165 }
166 }
167 }
168 }
169 throw new Exception(string.Format("Resource '{0}' was not found", name));
170 }
171
172 /// <summary>
173 /// Execute a SQL statement stored in a resource, as a string
174 /// </summary>
175 /// <param name="name"></param>
176 public void ExecuteResourceSql(string name)
177 {
178 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
179 cmd.ExecuteNonQuery();
180 }
181
182 /// <summary>
183 /// Given a list of tables, return the version of the tables, as seen in the database
184 /// </summary>
185 /// <param name="tableList"></param>
186 public void GetTableVersion(Dictionary<string, string> tableList)
187 {
188 lock (dbcon)
189 {
190 MySqlCommand tablesCmd =
191 new MySqlCommand(
192 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
193 dbcon);
194 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
195 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
196 {
197 while (tables.Read())
198 {
199 try
200 {
201 string tableName = (string) tables["TABLE_NAME"];
202 string comment = (string) tables["TABLE_COMMENT"];
203 if (tableList.ContainsKey(tableName))
204 {
205 tableList[tableName] = comment;
206 }
207 }
208 catch (Exception e)
209 {
210 m_log.Error(e.ToString());
211 }
212 }
213 tables.Close();
214 }
215 }
216 }
217
218 // TODO: at some time this code should be cleaned up
219
220 /// <summary>
221 /// Runs a query with protection against SQL Injection by using parameterised input.
222 /// </summary>
223 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
224 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
225 /// <returns>A MySQL DB Command</returns>
226 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
227 {
228 try
229 {
230 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
231 dbcommand.CommandText = sql;
232 foreach (KeyValuePair<string, string> param in parameters)
233 {
234 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
235 }
236
237 return (IDbCommand) dbcommand;
238 }
239 catch
240 {
241 lock (dbcon)
242 {
243 // Close the DB connection
244 try
245 {
246 dbcon.Close();
247 }
248 catch
249 {
250 }
251
252 // Try to reopen it
253 try
254 {
255 dbcon = new MySqlConnection(connectionString);
256 dbcon.Open();
257 }
258 catch (Exception e)
259 {
260 m_log.Error("Unable to reconnect to database " + e.ToString());
261 }
262
263 // Run the query again
264 try
265 {
266 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
267 dbcommand.CommandText = sql;
268 foreach (KeyValuePair<string, string> param in parameters)
269 {
270 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
271 }
272
273 return (IDbCommand) dbcommand;
274 }
275 catch (Exception e)
276 {
277 // Return null if it fails.
278 m_log.Error("Failed during Query generation: " + e.ToString());
279 return null;
280 }
281 }
282 }
283 }
284
285 /// <summary>
286 /// Reads a region row from a database reader
287 /// </summary>
288 /// <param name="reader">An active database reader</param>
289 /// <returns>A region profile</returns>
290 public RegionProfileData readSimRow(IDataReader reader)
291 {
292 RegionProfileData retval = new RegionProfileData();
293
294 if (reader.Read())
295 {
296 // Region Main gotta-have-or-we-return-null parts
297 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out retval.regionHandle))
298 return null;
299 if (!LLUUID.TryParse((string)reader["uuid"], out retval.UUID))
300 return null;
301
302 // non-critical parts
303 retval.regionName = (string)reader["regionName"];
304 retval.originUUID = new LLUUID((string) reader["originUUID"]);
305
306 // Secrets
307 retval.regionRecvKey = (string) reader["regionRecvKey"];
308 retval.regionSecret = (string) reader["regionSecret"];
309 retval.regionSendKey = (string) reader["regionSendKey"];
310
311 // Region Server
312 retval.regionDataURI = (string) reader["regionDataURI"];
313 retval.regionOnline = false; // Needs to be pinged before this can be set.
314 retval.serverIP = (string) reader["serverIP"];
315 retval.serverPort = (uint) reader["serverPort"];
316 retval.serverURI = (string) reader["serverURI"];
317 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
318 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
319
320 // Location
321 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
322 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
323 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
324
325 // Neighbours - 0 = No Override
326 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
327 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
328 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
329 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
330
331 // Assets
332 retval.regionAssetURI = (string) reader["regionAssetURI"];
333 retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
334 retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
335
336 // Userserver
337 retval.regionUserURI = (string) reader["regionUserURI"];
338 retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
339 retval.regionUserSendKey = (string) reader["regionUserSendKey"];
340
341 // World Map Addition
342 LLUUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID);
343 LLUUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid);
344 }
345 else
346 {
347 return null;
348 }
349 return retval;
350 }
351
352 /// <summary>
353 /// Reads a reservation row from a database reader
354 /// </summary>
355 /// <param name="reader">An active database reader</param>
356 /// <returns>A reservation data object</returns>
357 public ReservationData readReservationRow(IDataReader reader)
358 {
359 ReservationData retval = new ReservationData();
360 if (reader.Read())
361 {
362 retval.gridRecvKey = (string) reader["gridRecvKey"];
363 retval.gridSendKey = (string) reader["gridSendKey"];
364 retval.reservationCompany = (string) reader["resCompany"];
365 retval.reservationMaxX = Convert.ToInt32(reader["resXMax"].ToString());
366 retval.reservationMaxY = Convert.ToInt32(reader["resYMax"].ToString());
367 retval.reservationMinX = Convert.ToInt32(reader["resXMin"].ToString());
368 retval.reservationMinY = Convert.ToInt32(reader["resYMin"].ToString());
369 retval.reservationName = (string) reader["resName"];
370 retval.status = Convert.ToInt32(reader["status"].ToString()) == 1;
371 LLUUID.TryParse((string) reader["userUUID"], out retval.userUUID);
372 }
373 else
374 {
375 return null;
376 }
377 return retval;
378 }
379
380 /// <summary>
381 /// Reads an agent row from a database reader
382 /// </summary>
383 /// <param name="reader">An active database reader</param>
384 /// <returns>A user session agent</returns>
385 public UserAgentData readAgentRow(IDataReader reader)
386 {
387 UserAgentData retval = new UserAgentData();
388
389 if (reader.Read())
390 {
391 // Agent IDs
392 if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID))
393 return null;
394 LLUUID.TryParse((string) reader["sessionID"], out retval.sessionID);
395 LLUUID.TryParse((string)reader["secureSessionID"], out retval.secureSessionID);
396
397 // Agent Who?
398 retval.agentIP = (string) reader["agentIP"];
399 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
400 retval.agentOnline = Convert.ToBoolean(Convert.ToInt16(reader["agentOnline"].ToString()));
401
402 // Login/Logout times (UNIX Epoch)
403 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
404 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
405
406 // Current position
407 retval.currentRegion = new LLUUID((string)reader["currentRegion"]);
408 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
409 LLVector3.TryParse((string) reader["currentPos"], out retval.currentPos);
410 }
411 else
412 {
413 return null;
414 }
415 return retval;
416 }
417
418 /// <summary>
419 /// Reads a user profile from an active data reader
420 /// </summary>
421 /// <param name="reader">An active database reader</param>
422 /// <returns>A user profile</returns>
423 public UserProfileData readUserRow(IDataReader reader)
424 {
425 UserProfileData retval = new UserProfileData();
426
427 if (reader.Read())
428 {
429 if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID))
430 return null;
431 retval.username = (string) reader["username"];
432 retval.surname = (string) reader["lastname"];
433
434 retval.passwordHash = (string) reader["passwordHash"];
435 retval.passwordSalt = (string) reader["passwordSalt"];
436
437 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
438 retval.homeLocation = new LLVector3(
439 Convert.ToSingle(reader["homeLocationX"].ToString()),
440 Convert.ToSingle(reader["homeLocationY"].ToString()),
441 Convert.ToSingle(reader["homeLocationZ"].ToString()));
442 retval.homeLookAt = new LLVector3(
443 Convert.ToSingle(reader["homeLookAtX"].ToString()),
444 Convert.ToSingle(reader["homeLookAtY"].ToString()),
445 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
446
447 retval.created = Convert.ToInt32(reader["created"].ToString());
448 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
449
450 retval.userInventoryURI = (string) reader["userInventoryURI"];
451 retval.userAssetURI = (string) reader["userAssetURI"];
452
453 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
454 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
455
456 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
457 retval.profileAboutText = "";
458 else
459 retval.profileAboutText = (string) reader["profileAboutText"];
460
461 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
462 retval.profileFirstText = "";
463 else
464 retval.profileFirstText = (string)reader["profileFirstText"];
465
466 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
467 retval.profileImage = LLUUID.Zero;
468 else
469 LLUUID.TryParse((string)reader["profileImage"], out retval.profileImage);
470
471 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
472 retval.profileFirstImage = LLUUID.Zero;
473 else
474 LLUUID.TryParse((string)reader["profileFirstImage"], out retval.profileFirstImage);
475
476 if(reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
477 {
478 retval.webLoginKey = LLUUID.Zero;
479 }
480 else
481 {
482 LLUUID.TryParse((string)reader["webLoginKey"], out retval.webLoginKey);
483 }
484 }
485 else
486 {
487 return null;
488 }
489 return retval;
490 }
491
492 /// <summary>
493 /// Inserts a new row into the log database
494 /// </summary>
495 /// <param name="serverDaemon">The daemon which triggered this event</param>
496 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
497 /// <param name="methodCall">The method call where the problem occured</param>
498 /// <param name="arguments">The arguments passed to the method</param>
499 /// <param name="priority">How critical is this?</param>
500 /// <param name="logMessage">Extra message info</param>
501 /// <returns>Saved successfully?</returns>
502 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
503 string logMessage)
504 {
505 string sql = "INSERT INTO logs (`target`, `server`, `method`, `arguments`, `priority`, `message`) VALUES ";
506 sql += "(?target, ?server, ?method, ?arguments, ?priority, ?message)";
507
508 Dictionary<string, string> parameters = new Dictionary<string, string>();
509 parameters["?server"] = serverDaemon;
510 parameters["?target"] = target;
511 parameters["?method"] = methodCall;
512 parameters["?arguments"] = arguments;
513 parameters["?priority"] = priority.ToString();
514 parameters["?message"] = logMessage;
515
516 bool returnval = false;
517
518 try
519 {
520 IDbCommand result = Query(sql, parameters);
521
522 if (result.ExecuteNonQuery() == 1)
523 returnval = true;
524
525 result.Dispose();
526 }
527 catch (Exception e)
528 {
529 m_log.Error(e.ToString());
530 return false;
531 }
532
533 return returnval;
534 }
535
536 /// <summary>
537 /// Creates a new user and inserts it into the database
538 /// </summary>
539 /// <param name="uuid">User ID</param>
540 /// <param name="username">First part of the login</param>
541 /// <param name="lastname">Second part of the login</param>
542 /// <param name="passwordHash">A salted hash of the users password</param>
543 /// <param name="passwordSalt">The salt used for the password hash</param>
544 /// <param name="homeRegion">A regionHandle of the users home region</param>
545 /// <param name="homeLocX">Home region position vector</param>
546 /// <param name="homeLocY">Home region position vector</param>
547 /// <param name="homeLocZ">Home region position vector</param>
548 /// <param name="homeLookAtX">Home region 'look at' vector</param>
549 /// <param name="homeLookAtY">Home region 'look at' vector</param>
550 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
551 /// <param name="created">Account created (unix timestamp)</param>
552 /// <param name="lastlogin">Last login (unix timestamp)</param>
553 /// <param name="inventoryURI">Users inventory URI</param>
554 /// <param name="assetURI">Users asset URI</param>
555 /// <param name="canDoMask">I can do mask</param>
556 /// <param name="wantDoMask">I want to do mask</param>
557 /// <param name="aboutText">Profile text</param>
558 /// <param name="firstText">Firstlife text</param>
559 /// <param name="profileImage">UUID for profile image</param>
560 /// <param name="firstImage">UUID for firstlife image</param>
561 /// <returns>Success?</returns>
562 public bool insertUserRow(LLUUID uuid, string username, string lastname, string passwordHash,
563 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
564 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
565 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
566 string aboutText, string firstText,
567 LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey)
568 {
569 m_log.Debug("[MySQLManager]: Fetching profile for " + uuid.ToString());
570 string sql =
571 "INSERT INTO users (`UUID`, `username`, `lastname`, `passwordHash`, `passwordSalt`, `homeRegion`, ";
572 sql +=
573 "`homeLocationX`, `homeLocationY`, `homeLocationZ`, `homeLookAtX`, `homeLookAtY`, `homeLookAtZ`, `created`, ";
574 sql +=
575 "`lastLogin`, `userInventoryURI`, `userAssetURI`, `profileCanDoMask`, `profileWantDoMask`, `profileAboutText`, ";
576 sql += "`profileFirstText`, `profileImage`, `profileFirstImage`, `webLoginKey`) VALUES ";
577
578 sql += "(?UUID, ?username, ?lastname, ?passwordHash, ?passwordSalt, ?homeRegion, ";
579 sql +=
580 "?homeLocationX, ?homeLocationY, ?homeLocationZ, ?homeLookAtX, ?homeLookAtY, ?homeLookAtZ, ?created, ";
581 sql +=
582 "?lastLogin, ?userInventoryURI, ?userAssetURI, ?profileCanDoMask, ?profileWantDoMask, ?profileAboutText, ";
583 sql += "?profileFirstText, ?profileImage, ?profileFirstImage, ?webLoginKey)";
584
585 Dictionary<string, string> parameters = new Dictionary<string, string>();
586 parameters["?UUID"] = uuid.ToString();
587 parameters["?username"] = username.ToString();
588 parameters["?lastname"] = lastname.ToString();
589 parameters["?passwordHash"] = passwordHash.ToString();
590 parameters["?passwordSalt"] = passwordSalt.ToString();
591 parameters["?homeRegion"] = homeRegion.ToString();
592 parameters["?homeLocationX"] = homeLocX.ToString();
593 parameters["?homeLocationY"] = homeLocY.ToString();
594 parameters["?homeLocationZ"] = homeLocZ.ToString();
595 parameters["?homeLookAtX"] = homeLookAtX.ToString();
596 parameters["?homeLookAtY"] = homeLookAtY.ToString();
597 parameters["?homeLookAtZ"] = homeLookAtZ.ToString();
598 parameters["?created"] = created.ToString();
599 parameters["?lastLogin"] = lastlogin.ToString();
600 parameters["?userInventoryURI"] = String.Empty;
601 parameters["?userAssetURI"] = String.Empty;
602 parameters["?profileCanDoMask"] = "0";
603 parameters["?profileWantDoMask"] = "0";
604 parameters["?profileAboutText"] = aboutText;
605 parameters["?profileFirstText"] = firstText;
606 parameters["?profileImage"] = profileImage.ToString();
607 parameters["?profileFirstImage"] = firstImage.ToString();
608 parameters["?webLoginKey"] = string.Empty;
609
610 bool returnval = false;
611
612 try
613 {
614 IDbCommand result = Query(sql, parameters);
615
616 if (result.ExecuteNonQuery() == 1)
617 returnval = true;
618
619 result.Dispose();
620 }
621 catch (Exception e)
622 {
623 m_log.Error(e.ToString());
624 return false;
625 }
626
627 m_log.Debug("[MySQLManager]: Fetch user retval == " + returnval.ToString());
628 return returnval;
629 }
630
631 /// <summary>
632 /// Creates a new user and inserts it into the database
633 /// </summary>
634 /// <param name="uuid">User ID</param>
635 /// <param name="username">First part of the login</param>
636 /// <param name="lastname">Second part of the login</param>
637 /// <param name="passwordHash">A salted hash of the users password</param>
638 /// <param name="passwordSalt">The salt used for the password hash</param>
639 /// <param name="homeRegion">A regionHandle of the users home region</param>
640 /// <param name="homeLocX">Home region position vector</param>
641 /// <param name="homeLocY">Home region position vector</param>
642 /// <param name="homeLocZ">Home region position vector</param>
643 /// <param name="homeLookAtX">Home region 'look at' vector</param>
644 /// <param name="homeLookAtY">Home region 'look at' vector</param>
645 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
646 /// <param name="created">Account created (unix timestamp)</param>
647 /// <param name="lastlogin">Last login (unix timestamp)</param>
648 /// <param name="inventoryURI">Users inventory URI</param>
649 /// <param name="assetURI">Users asset URI</param>
650 /// <param name="canDoMask">I can do mask</param>
651 /// <param name="wantDoMask">I want to do mask</param>
652 /// <param name="aboutText">Profile text</param>
653 /// <param name="firstText">Firstlife text</param>
654 /// <param name="profileImage">UUID for profile image</param>
655 /// <param name="firstImage">UUID for firstlife image</param>
656 /// <returns>Success?</returns>
657 public bool updateUserRow(LLUUID uuid, string username, string lastname, string passwordHash,
658 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
659 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
660 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
661 string aboutText, string firstText,
662 LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey)
663 {
664 string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname ";
665 sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , ";
666 sql += "`homeRegion` = ?homeRegion , `homeLocationX` = ?homeLocationX , ";
667 sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , ";
668 sql += "`homeLookAtX` = ?homeLookAtX , `homeLookAtY` = ?homeLookAtY , ";
669 sql += "`homeLookAtZ` = ?homeLookAtZ , `created` = ?created , `lastLogin` = ?lastLogin , ";
670 sql += "`userInventoryURI` = ?userInventoryURI , `userAssetURI` = ?userAssetURI , ";
671 sql += "`profileCanDoMask` = ?profileCanDoMask , `profileWantDoMask` = ?profileWantDoMask , ";
672 sql += "`profileAboutText` = ?profileAboutText , `profileFirstText` = ?profileFirstText, ";
673 sql += "`profileImage` = ?profileImage , `profileFirstImage` = ?profileFirstImage , ";
674 sql += "`webLoginKey` = ?webLoginKey WHERE UUID = ?UUID";
675
676 Dictionary<string, string> parameters = new Dictionary<string, string>();
677 parameters["?UUID"] = uuid.ToString();
678 parameters["?username"] = username.ToString();
679 parameters["?lastname"] = lastname.ToString();
680 parameters["?passwordHash"] = passwordHash.ToString();
681 parameters["?passwordSalt"] = passwordSalt.ToString();
682 parameters["?homeRegion"] = homeRegion.ToString();
683 parameters["?homeLocationX"] = homeLocX.ToString();
684 parameters["?homeLocationY"] = homeLocY.ToString();
685 parameters["?homeLocationZ"] = homeLocZ.ToString();
686 parameters["?homeLookAtX"] = homeLookAtX.ToString();
687 parameters["?homeLookAtY"] = homeLookAtY.ToString();
688 parameters["?homeLookAtZ"] = homeLookAtZ.ToString();
689 parameters["?created"] = created.ToString();
690 parameters["?lastLogin"] = lastlogin.ToString();
691 parameters["?userInventoryURI"] = inventoryURI;
692 parameters["?userAssetURI"] = assetURI;
693 parameters["?profileCanDoMask"] = "0";
694 parameters["?profileWantDoMask"] = "0";
695 parameters["?profileAboutText"] = aboutText;
696 parameters["?profileFirstText"] = firstText;
697 parameters["?profileImage"] = profileImage.ToString();
698 parameters["?profileFirstImage"] = firstImage.ToString();
699 parameters["?webLoginKey"] = webLoginKey.ToString();
700
701 bool returnval = false;
702 try
703 {
704 IDbCommand result = Query(sql, parameters);
705
706 if (result.ExecuteNonQuery() == 1)
707 returnval = true;
708
709 result.Dispose();
710 }
711 catch (Exception e)
712 {
713 m_log.Error(e.ToString());
714 return false;
715 }
716
717 m_log.Debug("[MySQLManager]: update user retval == " + returnval.ToString());
718 return returnval;
719 }
720
721 /// <summary>
722 /// Inserts a new region into the database
723 /// </summary>
724 /// <param name="profile">The region to insert</param>
725 /// <returns>Success?</returns>
726 public bool insertRegion(RegionProfileData regiondata)
727 {
728 bool GRID_ONLY_UPDATE_NECESSARY_DATA = false;
729
730 string sql = String.Empty;
731 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
732 {
733 sql += "INSERT INTO ";
734 }
735 else
736 {
737 sql += "REPLACE INTO ";
738 }
739
740 sql += "regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
741 sql +=
742 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
743
744 // part of an initial brutish effort to provide accurate information (as per the xml region spec)
745 // wrt the ownership of a given region
746 // the (very bad) assumption is that this value is being read and handled inconsistently or
747 // not at all. Current strategy is to put the code in place to support the validity of this information
748 // and to roll forward debugging any issues from that point
749 //
750 // this particular section of the mod attempts to implement the commit of a supplied value
751 // server for the UUID of the region's owner (master avatar). It consists of the addition of the column and value to the relevant sql,
752 // as well as the related parameterization
753 sql +=
754 "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID) VALUES ";
755
756 sql += "(?regionHandle, ?regionName, ?uuid, ?regionRecvKey, ?regionSecret, ?regionSendKey, ?regionDataURI, ";
757 sql +=
758 "?serverIP, ?serverPort, ?serverURI, ?locX, ?locY, ?locZ, ?eastOverrideHandle, ?westOverrideHandle, ?southOverrideHandle, ?northOverrideHandle, ?regionAssetURI, ?regionAssetRecvKey, ";
759 sql +=
760 "?regionAssetSendKey, ?regionUserURI, ?regionUserRecvKey, ?regionUserSendKey, ?regionMapTexture, ?serverHttpPort, ?serverRemotingPort, ?owner_uuid, ?originUUID)";
761
762 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
763 {
764 sql += "ON DUPLICATE KEY UPDATE serverIP = ?serverIP, serverPort = ?serverPort, serverURI = ?serverURI, owner_uuid - ?owner_uuid;";
765 }
766 else
767 {
768 sql += ";";
769 }
770
771 Dictionary<string, string> parameters = new Dictionary<string, string>();
772
773 parameters["?regionHandle"] = regiondata.regionHandle.ToString();
774 parameters["?regionName"] = regiondata.regionName.ToString();
775 parameters["?uuid"] = regiondata.UUID.ToString();
776 parameters["?regionRecvKey"] = regiondata.regionRecvKey.ToString();
777 parameters["?regionSecret"] = regiondata.regionSecret.ToString();
778 parameters["?regionSendKey"] = regiondata.regionSendKey.ToString();
779 parameters["?regionDataURI"] = regiondata.regionDataURI.ToString();
780 parameters["?serverIP"] = regiondata.serverIP.ToString();
781 parameters["?serverPort"] = regiondata.serverPort.ToString();
782 parameters["?serverURI"] = regiondata.serverURI.ToString();
783 parameters["?locX"] = regiondata.regionLocX.ToString();
784 parameters["?locY"] = regiondata.regionLocY.ToString();
785 parameters["?locZ"] = regiondata.regionLocZ.ToString();
786 parameters["?eastOverrideHandle"] = regiondata.regionEastOverrideHandle.ToString();
787 parameters["?westOverrideHandle"] = regiondata.regionWestOverrideHandle.ToString();
788 parameters["?northOverrideHandle"] = regiondata.regionNorthOverrideHandle.ToString();
789 parameters["?southOverrideHandle"] = regiondata.regionSouthOverrideHandle.ToString();
790 parameters["?regionAssetURI"] = regiondata.regionAssetURI.ToString();
791 parameters["?regionAssetRecvKey"] = regiondata.regionAssetRecvKey.ToString();
792 parameters["?regionAssetSendKey"] = regiondata.regionAssetSendKey.ToString();
793 parameters["?regionUserURI"] = regiondata.regionUserURI.ToString();
794 parameters["?regionUserRecvKey"] = regiondata.regionUserRecvKey.ToString();
795 parameters["?regionUserSendKey"] = regiondata.regionUserSendKey.ToString();
796 parameters["?regionMapTexture"] = regiondata.regionMapTextureID.ToString();
797 parameters["?serverHttpPort"] = regiondata.httpPort.ToString();
798 parameters["?serverRemotingPort"] = regiondata.remotingPort.ToString();
799 parameters["?owner_uuid"] = regiondata.owner_uuid.ToString();
800 parameters["?originUUID"] = regiondata.originUUID.ToString();
801
802 bool returnval = false;
803
804 try
805 {
806 IDbCommand result = Query(sql, parameters);
807
808 //Console.WriteLine(result.CommandText);
809 int x;
810 if ((x = result.ExecuteNonQuery()) > 0)
811 {
812 returnval = true;
813 }
814 result.Dispose();
815 }
816 catch (Exception e)
817 {
818 m_log.Error(e.ToString());
819 return false;
820 }
821
822 return returnval;
823 }
824
825 /// <summary>
826 /// Delete a region from the database
827 /// </summary>
828 /// <param name="profile">The region to insert</param>
829 /// <returns>Success?</returns>
830 //public bool deleteRegion(RegionProfileData regiondata)
831 public bool deleteRegion(string uuid)
832 {
833 bool returnval = false;
834
835 string sql = "DELETE FROM regions WHERE uuid = ?uuid;";
836
837 Dictionary<string, string> parameters = new Dictionary<string, string>();
838
839 try
840 {
841 parameters["?uuid"] = uuid;
842
843 IDbCommand result = Query(sql, parameters);
844
845 int x;
846 if ((x = result.ExecuteNonQuery()) > 0)
847 {
848 returnval = true;
849 }
850 result.Dispose();
851 }
852 catch (Exception e)
853 {
854 m_log.Error(e.ToString());
855 return false;
856 }
857
858 return returnval;
859 }
860
861 /// <summary>
862 /// Creates a new agent and inserts it into the database
863 /// </summary>
864 /// <param name="agentdata">The agent data to be inserted</param>
865 /// <returns>Success?</returns>
866 public bool insertAgentRow(UserAgentData agentdata)
867 {
868 string sql = String.Empty;
869 sql += "REPLACE INTO ";
870 sql += "agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES ";
871 sql += "(?UUID, ?sessionID, ?secureSessionID, ?agentIP, ?agentPort, ?agentOnline, ?loginTime, ?logoutTime, ?currentRegion, ?currentHandle, ?currentPos);";
872 Dictionary<string, string> parameters = new Dictionary<string, string>();
873
874 parameters["?UUID"] = agentdata.UUID.ToString();
875 parameters["?sessionID"] = agentdata.sessionID.ToString();
876 parameters["?secureSessionID"] = agentdata.secureSessionID.ToString();
877 parameters["?agentIP"] = agentdata.agentIP.ToString();
878 parameters["?agentPort"] = agentdata.agentPort.ToString();
879 parameters["?agentOnline"] = (agentdata.agentOnline == true) ? "1" : "0";
880 parameters["?loginTime"] = agentdata.loginTime.ToString();
881 parameters["?logoutTime"] = agentdata.logoutTime.ToString();
882 parameters["?currentRegion"] = agentdata.currentRegion.ToString();
883 parameters["?currentHandle"] = agentdata.currentHandle.ToString();
884 parameters["?currentPos"] = "<" + ((int)agentdata.currentPos.X).ToString() + "," + ((int)agentdata.currentPos.Y).ToString() + "," + ((int)agentdata.currentPos.Z).ToString() + ">";
885
886 bool returnval = false;
887
888 try
889 {
890 IDbCommand result = Query(sql, parameters);
891
892 //Console.WriteLine(result.CommandText);
893 int x;
894 if ((x = result.ExecuteNonQuery()) > 0)
895 {
896 returnval = true;
897 }
898 result.Dispose();
899 }
900 catch (Exception e)
901 {
902 m_log.Error(e.ToString());
903 return false;
904 }
905
906 return returnval;
907 }
908 }
909}
generated by cgit v1.1 at 2024-11-06 10:14:16 +0000