aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Framework/Data.MSSQL/MSSQLManager.cs')
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLManager.cs616
1 files changed, 571 insertions, 45 deletions
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
index c0b2edf..4bca2e1 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
@@ -29,19 +29,28 @@ using System;
29using System.Collections.Generic; 29using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Data.SqlClient; 31using System.Data.SqlClient;
32using System.IO;
33using System.Reflection;
32using libsecondlife; 34using libsecondlife;
33 35
36using OpenSim.Framework.Console;
37
34namespace OpenSim.Framework.Data.MSSQL 38namespace OpenSim.Framework.Data.MSSQL
35{ 39{
36 /// <summary> 40 /// <summary>
37 /// A management class for the MS SQL Storage Engine 41 /// A management class for the MS SQL Storage Engine
38 /// </summary> 42 /// </summary>
39 internal class MSSqlManager 43 class MSSQLManager
40 { 44 {
41 /// <summary> 45 /// <summary>
42 /// The database connection object 46 /// The database connection object
43 /// </summary> 47 /// </summary>
44 private IDbConnection dbcon; 48 IDbConnection dbcon;
49
50 /// <summary>
51 /// Connection string for ADO.net
52 /// </summary>
53 private string connectionString;
45 54
46 /// <summary> 55 /// <summary>
47 /// Initialises and creates a new Sql connection and maintains it. 56 /// Initialises and creates a new Sql connection and maintains it.
@@ -51,14 +60,14 @@ namespace OpenSim.Framework.Data.MSSQL
51 /// <param name="username">The username logging into the database</param> 60 /// <param name="username">The username logging into the database</param>
52 /// <param name="password">The password for the user logging in</param> 61 /// <param name="password">The password for the user logging in</param>
53 /// <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> 62 /// <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>
54 public MSSqlManager(string hostname, string database, string username, string password, string cpooling) 63 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, string password)
55 { 64 {
56 try 65 try
57 { 66 {
58 string connectionString = "Server=" + hostname + ";Database=" + database + ";User ID=" + username + 67
59 ";Password=" + password + ";Pooling=" + cpooling + ";"; 68 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + password+";";
60 dbcon = new SqlConnection(connectionString); 69 dbcon = new SqlConnection(connectionString);
61 70 TestTables(dbcon);
62 dbcon.Open(); 71 dbcon.Open();
63 } 72 }
64 catch (Exception e) 73 catch (Exception e)
@@ -67,6 +76,144 @@ namespace OpenSim.Framework.Data.MSSQL
67 } 76 }
68 } 77 }
69 78
79 private bool TestTables(IDbConnection conn)
80 {
81 IDbCommand cmd = this.Query("SELECT * FROM regions", new Dictionary<string, string>());
82 //SqlCommand cmd = (SqlCommand)dbcon.CreateCommand();
83 //cmd.CommandText = "SELECT * FROM regions";
84 try
85 {
86 conn.Open();
87 cmd.ExecuteNonQuery();
88 cmd.Dispose();
89 conn.Close();
90 }
91 catch (Exception)
92 {
93 MainLog.Instance.Verbose("DATASTORE", "MSSQL Database doesn't exist... creating");
94 InitDB(conn);
95 }
96 return true;
97 }
98
99 private void InitDB(IDbConnection conn)
100 {
101 string createRegions = defineTable(createRegionsTable());
102 Dictionary<string, string> param = new Dictionary<string, string>();
103 IDbCommand pcmd = this.Query(createRegions, param);
104 if (conn.State == ConnectionState.Closed) {
105 conn.Open();
106 }
107 pcmd.ExecuteNonQuery();
108 pcmd.Dispose();
109
110 this.ExecuteResourceSql("Mssql-users.sql");
111 this.ExecuteResourceSql("Mssql-agents.sql");
112 this.ExecuteResourceSql("Mssql-logs.sql");
113
114 conn.Close();
115
116 }
117
118 private DataTable createRegionsTable()
119 {
120 DataTable regions = new DataTable("regions");
121
122 createCol(regions, "regionHandle", typeof(ulong));
123 createCol(regions, "regionName", typeof(System.String));
124 createCol(regions, "uuid", typeof(System.String));
125
126 createCol(regions, "regionRecvKey", typeof(System.String));
127 createCol(regions, "regionSecret", typeof(System.String));
128 createCol(regions, "regionSendKey", typeof(System.String));
129
130 createCol(regions, "regionDataURI", typeof(System.String));
131 createCol(regions, "serverIP", typeof(System.String));
132 createCol(regions, "serverPort", typeof(System.String));
133 createCol(regions, "serverURI", typeof(System.String));
134
135
136 createCol(regions, "locX", typeof(uint));
137 createCol(regions, "locY", typeof(uint));
138 createCol(regions, "locZ", typeof(uint));
139
140 createCol(regions, "eastOverrideHandle", typeof(ulong));
141 createCol(regions, "westOverrideHandle", typeof(ulong));
142 createCol(regions, "southOverrideHandle", typeof(ulong));
143 createCol(regions, "northOverrideHandle", typeof(ulong));
144
145 createCol(regions, "regionAssetURI", typeof(System.String));
146 createCol(regions, "regionAssetRecvKey", typeof(System.String));
147 createCol(regions, "regionAssetSendKey", typeof(System.String));
148
149 createCol(regions, "regionUserURI", typeof(System.String));
150 createCol(regions, "regionUserRecvKey", typeof(System.String));
151 createCol(regions, "regionUserSendKey", typeof(System.String));
152
153 createCol(regions, "regionMapTexture", typeof(System.String));
154 createCol(regions, "serverHttpPort", typeof(System.String));
155 createCol(regions, "serverRemotingPort", typeof(uint));
156
157 // Add in contraints
158 regions.PrimaryKey = new DataColumn[] { regions.Columns["UUID"] };
159 return regions;
160 }
161
162 protected static void createCol(DataTable dt, string name, System.Type type)
163 {
164 DataColumn col = new DataColumn(name, type);
165 dt.Columns.Add(col);
166 }
167
168 protected static string defineTable(DataTable dt)
169 {
170 string sql = "create table " + dt.TableName + "(";
171 string subsql = "";
172 foreach (DataColumn col in dt.Columns)
173 {
174 if (subsql.Length > 0)
175 { // a map function would rock so much here
176 subsql += ",\n";
177 }
178
179 subsql += col.ColumnName + " " + SqlType(col.DataType);
180 if (col == dt.PrimaryKey[0])
181 {
182 subsql += " primary key";
183 }
184 }
185 sql += subsql;
186 sql += ")";
187 return sql;
188 }
189
190
191 // this is something we'll need to implement for each db
192 // slightly differently.
193 private static string SqlType(Type type)
194 {
195 if (type == typeof(System.String))
196 {
197 return "varchar(255)";
198 }
199 else if (type == typeof(System.Int32))
200 {
201 return "integer";
202 }
203 else if (type == typeof(System.Double))
204 {
205 return "float";
206 }
207 else if (type == typeof(System.Byte[]))
208 {
209 return "image";
210 }
211 else
212 {
213 return "varchar(255)";
214 }
215 }
216
70 /// <summary> 217 /// <summary>
71 /// Shuts down the database connection 218 /// Shuts down the database connection
72 /// </summary> 219 /// </summary>
@@ -77,6 +224,29 @@ namespace OpenSim.Framework.Data.MSSQL
77 } 224 }
78 225
79 /// <summary> 226 /// <summary>
227 /// Reconnects to the database
228 /// </summary>
229 public void Reconnect()
230 {
231 lock (dbcon)
232 {
233 try
234 {
235 //string connectionString = "Data Source=WRK-OU-738\\SQLEXPRESS;Initial Catalog=rex;Persist Security Info=True;User ID=sa;Password=rex";
236 // Close the DB connection
237 dbcon.Close();
238 // Try reopen it
239 dbcon = new SqlConnection(connectionString);
240 dbcon.Open();
241 }
242 catch (Exception e)
243 {
244 MainLog.Instance.Error("Unable to reconnect to database " + e.ToString());
245 }
246 }
247 }
248
249 /// <summary>
80 /// Runs a query with protection against SQL Injection by using parameterised input. 250 /// Runs a query with protection against SQL Injection by using parameterised input.
81 /// </summary> 251 /// </summary>
82 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param> 252 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
@@ -84,14 +254,14 @@ namespace OpenSim.Framework.Data.MSSQL
84 /// <returns>A Sql DB Command</returns> 254 /// <returns>A Sql DB Command</returns>
85 public IDbCommand Query(string sql, Dictionary<string, string> parameters) 255 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
86 { 256 {
87 SqlCommand dbcommand = (SqlCommand) dbcon.CreateCommand(); 257 SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand();
88 dbcommand.CommandText = sql; 258 dbcommand.CommandText = sql;
89 foreach (KeyValuePair<string, string> param in parameters) 259 foreach (KeyValuePair<string, string> param in parameters)
90 { 260 {
91 dbcommand.Parameters.AddWithValue(param.Key, param.Value); 261 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
92 } 262 }
93 263
94 return (IDbCommand) dbcommand; 264 return (IDbCommand)dbcommand;
95 } 265 }
96 266
97 /// <summary> 267 /// <summary>
@@ -99,74 +269,201 @@ namespace OpenSim.Framework.Data.MSSQL
99 /// </summary> 269 /// </summary>
100 /// <param name="reader">An active database reader</param> 270 /// <param name="reader">An active database reader</param>
101 /// <returns>A region row</returns> 271 /// <returns>A region row</returns>
102 public RegionProfileData getRow(IDataReader reader) 272 public RegionProfileData getRegionRow(IDataReader reader)
103 { 273 {
104 RegionProfileData regionprofile = new RegionProfileData(); 274 RegionProfileData regionprofile = new RegionProfileData();
105 275
106 if (reader.Read()) 276 if (reader.Read())
107 { 277 {
108 // Region Main 278 // Region Main
109 regionprofile.regionHandle = (ulong) reader["regionHandle"]; 279 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
110 regionprofile.regionName = (string) reader["regionName"]; 280 regionprofile.regionName = (string)reader["regionName"];
111 regionprofile.UUID = new LLUUID((string) reader["uuid"]); 281 regionprofile.UUID = new LLUUID((string)reader["uuid"]);
112 282
113 // Secrets 283 // Secrets
114 regionprofile.regionRecvKey = (string) reader["regionRecvKey"]; 284 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
115 regionprofile.regionSecret = (string) reader["regionSecret"]; 285 regionprofile.regionSecret = (string)reader["regionSecret"];
116 regionprofile.regionSendKey = (string) reader["regionSendKey"]; 286 regionprofile.regionSendKey = (string)reader["regionSendKey"];
117 287
118 // Region Server 288 // Region Server
119 regionprofile.regionDataURI = (string) reader["regionDataURI"]; 289 regionprofile.regionDataURI = (string)reader["regionDataURI"];
120 regionprofile.regionOnline = false; // Needs to be pinged before this can be set. 290 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
121 regionprofile.serverIP = (string) reader["serverIP"]; 291 regionprofile.serverIP = (string)reader["serverIP"];
122 regionprofile.serverPort = (uint) reader["serverPort"]; 292 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
123 regionprofile.serverURI = (string) reader["serverURI"]; 293 regionprofile.serverURI = (string)reader["serverURI"];
294 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
295 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
296
124 297
125 // Location 298 // Location
126 regionprofile.regionLocX = (uint) ((int) reader["locX"]); 299 regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]);
127 regionprofile.regionLocY = (uint) ((int) reader["locY"]); 300 regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]);
128 regionprofile.regionLocZ = (uint) ((int) reader["locZ"]); 301 regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]);
129 302
130 // Neighbours - 0 = No Override 303 // Neighbours - 0 = No Override
131 regionprofile.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; 304 regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]);
132 regionprofile.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; 305 regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]);
133 regionprofile.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; 306 regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]);
134 regionprofile.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; 307 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
135 308
136 // Assets 309 // Assets
137 regionprofile.regionAssetURI = (string) reader["regionAssetURI"]; 310 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
138 regionprofile.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; 311 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
139 regionprofile.regionAssetSendKey = (string) reader["regionAssetSendKey"]; 312 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
140 313
141 // Userserver 314 // Userserver
142 regionprofile.regionUserURI = (string) reader["regionUserURI"]; 315 regionprofile.regionUserURI = (string)reader["regionUserURI"];
143 regionprofile.regionUserRecvKey = (string) reader["regionUserRecvKey"]; 316 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
144 regionprofile.regionUserSendKey = (string) reader["regionUserSendKey"]; 317 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
318
319 // World Map Addition
320 string tempRegionMap = reader["regionMapTexture"].ToString();
321 if (tempRegionMap != "")
322 {
323 regionprofile.regionMapTextureID = new LLUUID(tempRegionMap);
324 }
325 else
326 {
327 regionprofile.regionMapTextureID = new LLUUID();
328 }
145 } 329 }
146 else 330 else
147 { 331 {
332 reader.Close();
148 throw new Exception("No rows to return"); 333 throw new Exception("No rows to return");
334
149 } 335 }
150 return regionprofile; 336 return regionprofile;
151 } 337 }
152 338
153 /// <summary> 339 /// <summary>
340 /// Reads a user profile from an active data reader
341 /// </summary>
342 /// <param name="reader">An active database reader</param>
343 /// <returns>A user profile</returns>
344 public UserProfileData readUserRow(IDataReader reader)
345 {
346 UserProfileData retval = new UserProfileData();
347
348 if (reader.Read())
349 {
350 retval.UUID = new LLUUID((string)reader["UUID"]);
351 retval.username = (string)reader["username"];
352 retval.surname = (string)reader["lastname"];
353
354 retval.passwordHash = (string)reader["passwordHash"];
355 retval.passwordSalt = (string)reader["passwordSalt"];
356
357 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
358 retval.homeLocation = new LLVector3(
359 Convert.ToSingle(reader["homeLocationX"].ToString()),
360 Convert.ToSingle(reader["homeLocationY"].ToString()),
361 Convert.ToSingle(reader["homeLocationZ"].ToString()));
362 retval.homeLookAt = new LLVector3(
363 Convert.ToSingle(reader["homeLookAtX"].ToString()),
364 Convert.ToSingle(reader["homeLookAtY"].ToString()),
365 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
366
367 retval.created = Convert.ToInt32(reader["created"].ToString());
368 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
369
370 retval.userInventoryURI = (string)reader["userInventoryURI"];
371 retval.userAssetURI = (string)reader["userAssetURI"];
372
373 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
374 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
375
376 retval.profileAboutText = (string)reader["profileAboutText"];
377 retval.profileFirstText = (string)reader["profileFirstText"];
378
379 retval.profileImage = new LLUUID((string)reader["profileImage"]);
380 retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]);
381
382 }
383 else
384 {
385 return null;
386 }
387 return retval;
388 }
389
390 /// <summary>
391 /// Reads an agent row from a database reader
392 /// </summary>
393 /// <param name="reader">An active database reader</param>
394 /// <returns>A user session agent</returns>
395 public UserAgentData readAgentRow(IDataReader reader)
396 {
397 UserAgentData retval = new UserAgentData();
398
399 if (reader.Read())
400 {
401 // Agent IDs
402 retval.UUID = new LLUUID((string)reader["UUID"]);
403 retval.sessionID = new LLUUID((string)reader["sessionID"]);
404 retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]);
405
406 // Agent Who?
407 retval.agentIP = (string)reader["agentIP"];
408 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
409 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
410
411 // Login/Logout times (UNIX Epoch)
412 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
413 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
414
415 // Current position
416 retval.currentRegion = (string)reader["currentRegion"];
417 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
418 LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
419 }
420 else
421 {
422 return null;
423 }
424 return retval;
425 }
426
427 public AssetBase getAssetRow(IDataReader reader)
428 {
429 AssetBase asset = new AssetBase();
430 if (reader.Read())
431 {
432 // Region Main
433
434 asset = new AssetBase();
435 asset.Data = (byte[])reader["data"];
436 asset.Description = (string)reader["description"];
437 asset.FullID = new LLUUID((string)reader["id"]);
438 asset.InvType = Convert.ToSByte(reader["invType"]);
439 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
440 asset.Name = (string)reader["name"];
441 asset.Type = Convert.ToSByte(reader["assetType"]);
442
443 }
444 else
445 {
446 return null; // throw new Exception("No rows to return");
447 }
448 return asset;
449 }
450
451 /// <summary>
154 /// Creates a new region in the database 452 /// Creates a new region in the database
155 /// </summary> 453 /// </summary>
156 /// <param name="profile">The region profile to insert</param> 454 /// <param name="profile">The region profile to insert</param>
157 /// <returns>Successful?</returns> 455 /// <returns>Successful?</returns>
158 public bool insertRow(RegionProfileData profile) 456 public bool insertRegionRow(RegionProfileData profile)
159 { 457 {
160 string sql = 458
161 "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; 459 //Insert new region
162 sql += 460 string sql = "INSERT INTO regions ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], ";
163 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; 461 sql += "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], ";
164 sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES "; 462 sql += "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort]) VALUES ";
165 463
166 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; 464 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
167 sql += 465 sql += "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
168 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; 466 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);";
169 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
170 467
171 Dictionary<string, string> parameters = new Dictionary<string, string>(); 468 Dictionary<string, string> parameters = new Dictionary<string, string>();
172 469
@@ -174,6 +471,7 @@ namespace OpenSim.Framework.Data.MSSQL
174 parameters["regionName"] = profile.regionName; 471 parameters["regionName"] = profile.regionName;
175 parameters["uuid"] = profile.UUID.ToString(); 472 parameters["uuid"] = profile.UUID.ToString();
176 parameters["regionRecvKey"] = profile.regionRecvKey; 473 parameters["regionRecvKey"] = profile.regionRecvKey;
474 parameters["regionSecret"] = profile.regionSecret;
177 parameters["regionSendKey"] = profile.regionSendKey; 475 parameters["regionSendKey"] = profile.regionSendKey;
178 parameters["regionDataURI"] = profile.regionDataURI; 476 parameters["regionDataURI"] = profile.regionDataURI;
179 parameters["serverIP"] = profile.serverIP; 477 parameters["serverIP"] = profile.serverIP;
@@ -192,6 +490,56 @@ namespace OpenSim.Framework.Data.MSSQL
192 parameters["regionUserURI"] = profile.regionUserURI; 490 parameters["regionUserURI"] = profile.regionUserURI;
193 parameters["regionUserRecvKey"] = profile.regionUserRecvKey; 491 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
194 parameters["regionUserSendKey"] = profile.regionUserSendKey; 492 parameters["regionUserSendKey"] = profile.regionUserSendKey;
493 parameters["regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated();
494 parameters["serverHttpPort"] = profile.httpPort.ToString();
495 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
496
497
498 bool returnval = false;
499
500 try
501 {
502 IDbCommand result = Query(sql, parameters);
503
504 if (result.ExecuteNonQuery() == 1)
505 returnval = true;
506
507 result.Dispose();
508 }
509 catch (Exception e)
510 {
511 MainLog.Instance.Error("MSSQLManager : " + e.ToString());
512
513 }
514
515 return returnval;
516
517 }
518
519
520
521 /// <summary>
522 /// Inserts a new row into the log database
523 /// </summary>
524 /// <param name="serverDaemon">The daemon which triggered this event</param>
525 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
526 /// <param name="methodCall">The method call where the problem occured</param>
527 /// <param name="arguments">The arguments passed to the method</param>
528 /// <param name="priority">How critical is this?</param>
529 /// <param name="logMessage">Extra message info</param>
530 /// <returns>Saved successfully?</returns>
531 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage)
532 {
533 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
534 sql += "(@target, @server, @method, @arguments, @priority, @message);";
535
536 Dictionary<string, string> parameters = new Dictionary<string, string>();
537 parameters["server"] = serverDaemon;
538 parameters["target"] = target;
539 parameters["method"] = methodCall;
540 parameters["arguments"] = arguments;
541 parameters["priority"] = priority.ToString();
542 parameters["message"] = logMessage;
195 543
196 bool returnval = false; 544 bool returnval = false;
197 545
@@ -204,12 +552,190 @@ namespace OpenSim.Framework.Data.MSSQL
204 552
205 result.Dispose(); 553 result.Dispose();
206 } 554 }
207 catch (Exception) 555 catch (Exception e)
208 { 556 {
557 MainLog.Instance.Error(e.ToString());
209 return false; 558 return false;
210 } 559 }
211 560
212 return returnval; 561 return returnval;
213 } 562 }
563
564
565 /// <summary>
566 /// Creates a new user and inserts it into the database
567 /// </summary>
568 /// <param name="uuid">User ID</param>
569 /// <param name="username">First part of the login</param>
570 /// <param name="lastname">Second part of the login</param>
571 /// <param name="passwordHash">A salted hash of the users password</param>
572 /// <param name="passwordSalt">The salt used for the password hash</param>
573 /// <param name="homeRegion">A regionHandle of the users home region</param>
574 /// <param name="homeLocX">Home region position vector</param>
575 /// <param name="homeLocY">Home region position vector</param>
576 /// <param name="homeLocZ">Home region position vector</param>
577 /// <param name="homeLookAtX">Home region 'look at' vector</param>
578 /// <param name="homeLookAtY">Home region 'look at' vector</param>
579 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
580 /// <param name="created">Account created (unix timestamp)</param>
581 /// <param name="lastlogin">Last login (unix timestamp)</param>
582 /// <param name="inventoryURI">Users inventory URI</param>
583 /// <param name="assetURI">Users asset URI</param>
584 /// <param name="canDoMask">I can do mask</param>
585 /// <param name="wantDoMask">I want to do mask</param>
586 /// <param name="aboutText">Profile text</param>
587 /// <param name="firstText">Firstlife text</param>
588 /// <param name="profileImage">UUID for profile image</param>
589 /// <param name="firstImage">UUID for firstlife image</param>
590 /// <returns>Success?</returns>
591 public bool insertUserRow(libsecondlife.LLUUID uuid, string username, string lastname, string passwordHash, string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
592 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, string aboutText, string firstText,
593 libsecondlife.LLUUID profileImage, libsecondlife.LLUUID firstImage)
594 {
595 string sql = "INSERT INTO users ";
596 sql += "([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], ";
597 sql += "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], ";
598 sql += "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], ";
599 sql += "[profileFirstText], [profileImage], [profileFirstImage]) VALUES ";
600
601 sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, ";
602 sql += "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, ";
603 sql += "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, ";
604 sql += "@profileFirstText, @profileImage, @profileFirstImage);";
605
606 Dictionary<string, string> parameters = new Dictionary<string, string>();
607 parameters["UUID"] = uuid.ToStringHyphenated();
608 parameters["username"] = username.ToString();
609 parameters["lastname"] = lastname.ToString();
610 parameters["passwordHash"] = passwordHash.ToString();
611 parameters["passwordSalt"] = passwordSalt.ToString();
612 parameters["homeRegion"] = homeRegion.ToString();
613 parameters["homeLocationX"] = homeLocX.ToString();
614 parameters["homeLocationY"] = homeLocY.ToString();
615 parameters["homeLocationZ"] = homeLocZ.ToString();
616 parameters["homeLookAtX"] = homeLookAtX.ToString();
617 parameters["homeLookAtY"] = homeLookAtY.ToString();
618 parameters["homeLookAtZ"] = homeLookAtZ.ToString();
619 parameters["created"] = created.ToString();
620 parameters["lastLogin"] = lastlogin.ToString();
621 parameters["userInventoryURI"] = "";
622 parameters["userAssetURI"] = "";
623 parameters["profileCanDoMask"] = "0";
624 parameters["profileWantDoMask"] = "0";
625 parameters["profileAboutText"] = "";
626 parameters["profileFirstText"] = "";
627 parameters["profileImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated();
628 parameters["profileFirstImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated();
629
630 bool returnval = false;
631
632 try
633 {
634 IDbCommand result = Query(sql, parameters);
635
636 if (result.ExecuteNonQuery() == 1)
637 returnval = true;
638
639 result.Dispose();
640 }
641 catch (Exception e)
642 {
643 MainLog.Instance.Error(e.ToString());
644 return false;
645 }
646
647 return returnval;
648 }
649
650 /// <summary>
651 /// Execute a SQL statement stored in a resource, as a string
652 /// </summary>
653 /// <param name="name"></param>
654 public void ExecuteResourceSql(string name)
655 {
656 try
657 {
658
659 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon);
660 cmd.ExecuteNonQuery();
661 cmd.Dispose();
662 }
663 catch (Exception e)
664 {
665 MainLog.Instance.Error("Unable to execute query " + e.ToString());
666 }
667 }
668
669 public SqlConnection getConnection()
670 {
671 return (SqlConnection)dbcon;
672 }
673
674 /// <summary>
675 /// Given a list of tables, return the version of the tables, as seen in the database
676 /// </summary>
677 /// <param name="tableList"></param>
678 public void GetTableVersion(Dictionary<string, string> tableList)
679 {
680 lock (dbcon)
681 {
682 Dictionary<string, string> param = new Dictionary<string, string>();
683 param["dbname"] = dbcon.Database;
684 IDbCommand tablesCmd = this.Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param);
685 using (IDataReader tables = tablesCmd.ExecuteReader())
686 {
687 while (tables.Read())
688 {
689 try
690 {
691 string tableName = (string)tables["TABLE_NAME"];
692 if (tableList.ContainsKey(tableName))
693 tableList[tableName] = tableName;
694 }
695 catch (Exception e)
696 {
697 MainLog.Instance.Error(e.ToString());
698 }
699 }
700 tables.Close();
701 }
702 }
703 }
704
705 private string getResourceString(string name)
706 {
707 Assembly assem = this.GetType().Assembly;
708 string[] names = assem.GetManifestResourceNames();
709
710 foreach (string s in names)
711 if (s.EndsWith(name))
712 using (Stream resource = assem.GetManifestResourceStream(s))
713 {
714 using (StreamReader resourceReader = new StreamReader(resource))
715 {
716 string resourceString = resourceReader.ReadToEnd();
717 return resourceString;
718 }
719 }
720 throw new Exception(string.Format("Resource '{0}' was not found", name));
721 }
722
723 /// <summary>
724 /// Returns the version of this DB provider
725 /// </summary>
726 /// <returns>A string containing the DB provider</returns>
727 public string getVersion()
728 {
729 System.Reflection.Module module = this.GetType().Module;
730 string dllName = module.Assembly.ManifestModule.Name;
731 Version dllVersion = module.Assembly.GetName().Version;
732
733
734 return string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, dllVersion.Revision);
735 }
736
214 } 737 }
215} \ No newline at end of file 738
739
740
741}