aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
diff options
context:
space:
mode:
authorSean Dague2008-04-02 15:24:31 +0000
committerSean Dague2008-04-02 15:24:31 +0000
commitc52c68f314c67c76c7181a6d0828f476290fbd66 (patch)
tree66ab347502892902a096fa985f31b25738eb1381 /OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
parentreorganizing namespaces to put all the Data stuff into it's own namespace (diff)
downloadopensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.zip
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.gz
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.bz2
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.xz
whole lot more moving
Diffstat (limited to 'OpenSim/Framework/Data.MSSQL/MSSQLManager.cs')
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLManager.cs529
1 files changed, 0 insertions, 529 deletions
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
deleted file mode 100644
index efe62be..0000000
--- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
+++ /dev/null
@@ -1,529 +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 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 OpenSim.Framework.Console;
36
37namespace OpenSim.Framework.Data.MSSQL
38{
39 /// <summary>
40 /// A management class for the MS SQL Storage Engine
41 /// </summary>
42 public class MSSQLManager
43 {
44 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
45
46 /// <summary>
47 /// The database connection object
48 /// </summary>
49 private IDbConnection dbcon;
50
51 /// <summary>
52 /// Connection string for ADO.net
53 /// </summary>
54 private readonly string connectionString;
55
56 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId,
57 string password)
58 {
59 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog +
60 ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" +
61 password + ";";
62 dbcon = new SqlConnection(connectionString);
63 dbcon.Open();
64 }
65
66 //private DataTable createRegionsTable()
67 //{
68 // DataTable regions = new DataTable("regions");
69
70 // createCol(regions, "regionHandle", typeof (ulong));
71 // createCol(regions, "regionName", typeof (String));
72 // createCol(regions, "uuid", typeof (String));
73
74 // createCol(regions, "regionRecvKey", typeof (String));
75 // createCol(regions, "regionSecret", typeof (String));
76 // createCol(regions, "regionSendKey", typeof (String));
77
78 // createCol(regions, "regionDataURI", typeof (String));
79 // createCol(regions, "serverIP", typeof (String));
80 // createCol(regions, "serverPort", typeof (String));
81 // createCol(regions, "serverURI", typeof (String));
82
83
84 // createCol(regions, "locX", typeof (uint));
85 // createCol(regions, "locY", typeof (uint));
86 // createCol(regions, "locZ", typeof (uint));
87
88 // createCol(regions, "eastOverrideHandle", typeof (ulong));
89 // createCol(regions, "westOverrideHandle", typeof (ulong));
90 // createCol(regions, "southOverrideHandle", typeof (ulong));
91 // createCol(regions, "northOverrideHandle", typeof (ulong));
92
93 // createCol(regions, "regionAssetURI", typeof (String));
94 // createCol(regions, "regionAssetRecvKey", typeof (String));
95 // createCol(regions, "regionAssetSendKey", typeof (String));
96
97 // createCol(regions, "regionUserURI", typeof (String));
98 // createCol(regions, "regionUserRecvKey", typeof (String));
99 // createCol(regions, "regionUserSendKey", typeof (String));
100
101 // createCol(regions, "regionMapTexture", typeof (String));
102 // createCol(regions, "serverHttpPort", typeof (String));
103 // createCol(regions, "serverRemotingPort", typeof (uint));
104
105 // // Add in contraints
106 // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
107 // return regions;
108 //}
109
110 protected static void createCol(DataTable dt, string name, Type type)
111 {
112 DataColumn col = new DataColumn(name, type);
113 dt.Columns.Add(col);
114 }
115
116 protected static string defineTable(DataTable dt)
117 {
118 string sql = "create table " + dt.TableName + "(";
119 string subsql = String.Empty;
120 foreach (DataColumn col in dt.Columns)
121 {
122 if (subsql.Length > 0)
123 {
124 // a map function would rock so much here
125 subsql += ",\n";
126 }
127
128 subsql += col.ColumnName + " " + SqlType(col.DataType);
129 if (col == dt.PrimaryKey[0])
130 {
131 subsql += " primary key";
132 }
133 }
134 sql += subsql;
135 sql += ")";
136 return sql;
137 }
138
139
140 // this is something we'll need to implement for each db
141 // slightly differently.
142 public static string SqlType(Type type)
143 {
144 if (type == typeof(String))
145 {
146 return "varchar(255)";
147 }
148 else if (type == typeof(Int32))
149 {
150 return "integer";
151 }
152 else if (type == typeof(Double))
153 {
154 return "float";
155 }
156 else if (type == typeof(Byte[]))
157 {
158 return "image";
159 }
160 else
161 {
162 return "varchar(255)";
163 }
164 }
165
166 /// <summary>
167 /// Shuts down the database connection
168 /// </summary>
169 public void Close()
170 {
171 dbcon.Close();
172 dbcon = null;
173 }
174
175 /// <summary>
176 /// Reconnects to the database
177 /// </summary>
178 public void Reconnect()
179 {
180 lock (dbcon)
181 {
182 try
183 {
184 // Close the DB connection
185 dbcon.Close();
186 // Try reopen it
187 dbcon = new SqlConnection(connectionString);
188 dbcon.Open();
189 }
190 catch (Exception e)
191 {
192 m_log.Error("Unable to reconnect to database " + e.ToString());
193 }
194 }
195 }
196
197 /// <summary>
198 /// Runs a query with protection against SQL Injection by using parameterised input.
199 /// </summary>
200 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
201 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
202 /// <returns>A Sql DB Command</returns>
203 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
204 {
205 SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand();
206 dbcommand.CommandText = sql;
207 foreach (KeyValuePair<string, string> param in parameters)
208 {
209 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
210 }
211
212 return (IDbCommand)dbcommand;
213 }
214
215 /// <summary>
216 /// Runs a database reader object and returns a region row
217 /// </summary>
218 /// <param name="reader">An active database reader</param>
219 /// <returns>A region row</returns>
220 public RegionProfileData getRegionRow(IDataReader reader)
221 {
222 RegionProfileData regionprofile = new RegionProfileData();
223
224 if (reader.Read())
225 {
226 // Region Main
227 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
228 regionprofile.regionName = (string)reader["regionName"];
229 regionprofile.UUID = new LLUUID((string)reader["uuid"]);
230
231 // Secrets
232 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
233 regionprofile.regionSecret = (string)reader["regionSecret"];
234 regionprofile.regionSendKey = (string)reader["regionSendKey"];
235
236 // Region Server
237 regionprofile.regionDataURI = (string)reader["regionDataURI"];
238 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
239 regionprofile.serverIP = (string)reader["serverIP"];
240 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
241 regionprofile.serverURI = (string)reader["serverURI"];
242 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
243 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
244
245
246 // Location
247 regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]);
248 regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]);
249 regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]);
250
251 // Neighbours - 0 = No Override
252 regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]);
253 regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]);
254 regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]);
255 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
256
257 // Assets
258 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
259 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
260 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
261
262 // Userserver
263 regionprofile.regionUserURI = (string)reader["regionUserURI"];
264 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
265 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
266 try
267 {
268 regionprofile.owner_uuid = new LLUUID((string)reader["owner_uuid"]);
269 }
270 catch(Exception)
271 {}
272 // World Map Addition
273 string tempRegionMap = reader["regionMapTexture"].ToString();
274 if (tempRegionMap != String.Empty)
275 {
276 regionprofile.regionMapTextureID = new LLUUID(tempRegionMap);
277 }
278 else
279 {
280 regionprofile.regionMapTextureID = new LLUUID();
281 }
282 }
283 else
284 {
285 reader.Close();
286 throw new Exception("No rows to return");
287 }
288 return regionprofile;
289 }
290
291 /// <summary>
292 /// Reads a user profile from an active data reader
293 /// </summary>
294 /// <param name="reader">An active database reader</param>
295 /// <returns>A user profile</returns>
296 public UserProfileData readUserRow(IDataReader reader)
297 {
298 UserProfileData retval = new UserProfileData();
299
300 if (reader.Read())
301 {
302 retval.UUID = new LLUUID((string)reader["UUID"]);
303 retval.username = (string)reader["username"];
304 retval.surname = (string)reader["lastname"];
305
306 retval.passwordHash = (string)reader["passwordHash"];
307 retval.passwordSalt = (string)reader["passwordSalt"];
308
309 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
310 retval.homeLocation = new LLVector3(
311 Convert.ToSingle(reader["homeLocationX"].ToString()),
312 Convert.ToSingle(reader["homeLocationY"].ToString()),
313 Convert.ToSingle(reader["homeLocationZ"].ToString()));
314 retval.homeLookAt = new LLVector3(
315 Convert.ToSingle(reader["homeLookAtX"].ToString()),
316 Convert.ToSingle(reader["homeLookAtY"].ToString()),
317 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
318
319 retval.created = Convert.ToInt32(reader["created"].ToString());
320 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
321
322 retval.userInventoryURI = (string)reader["userInventoryURI"];
323 retval.userAssetURI = (string)reader["userAssetURI"];
324
325 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
326 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
327
328 retval.profileAboutText = (string)reader["profileAboutText"];
329 retval.profileFirstText = (string)reader["profileFirstText"];
330
331 retval.profileImage = new LLUUID((string)reader["profileImage"]);
332 retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]);
333 retval.webLoginKey = new LLUUID((string)reader["webLoginKey"]);
334 }
335 else
336 {
337 return null;
338 }
339 return retval;
340 }
341
342 /// <summary>
343 /// Reads an agent row from a database reader
344 /// </summary>
345 /// <param name="reader">An active database reader</param>
346 /// <returns>A user session agent</returns>
347 public UserAgentData readAgentRow(IDataReader reader)
348 {
349 UserAgentData retval = new UserAgentData();
350
351 if (reader.Read())
352 {
353 // Agent IDs
354 retval.UUID = new LLUUID((string)reader["UUID"]);
355 retval.sessionID = new LLUUID((string)reader["sessionID"]);
356 retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]);
357
358 // Agent Who?
359 retval.agentIP = (string)reader["agentIP"];
360 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
361 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
362
363 // Login/Logout times (UNIX Epoch)
364 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
365 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
366
367 // Current position
368 retval.currentRegion = (string)reader["currentRegion"];
369 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
370 LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
371 }
372 else
373 {
374 return null;
375 }
376 return retval;
377 }
378
379 public AssetBase getAssetRow(IDataReader reader)
380 {
381 AssetBase asset = new AssetBase();
382 if (reader.Read())
383 {
384 // Region Main
385
386 asset = new AssetBase();
387 asset.Data = (byte[])reader["data"];
388 asset.Description = (string)reader["description"];
389 asset.FullID = new LLUUID((string)reader["id"]);
390 asset.InvType = Convert.ToSByte(reader["invType"]);
391 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
392 asset.Name = (string)reader["name"];
393 asset.Type = Convert.ToSByte(reader["assetType"]);
394 }
395 else
396 {
397 return null; // throw new Exception("No rows to return");
398 }
399 return asset;
400 }
401
402
403 /// <summary>
404 /// Inserts a new row into the log database
405 /// </summary>
406 /// <param name="serverDaemon">The daemon which triggered this event</param>
407 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
408 /// <param name="methodCall">The method call where the problem occured</param>
409 /// <param name="arguments">The arguments passed to the method</param>
410 /// <param name="priority">How critical is this?</param>
411 /// <param name="logMessage">Extra message info</param>
412 /// <returns>Saved successfully?</returns>
413 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
414 string logMessage)
415 {
416 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
417 sql += "(@target, @server, @method, @arguments, @priority, @message);";
418
419 Dictionary<string, string> parameters = new Dictionary<string, string>();
420 parameters["server"] = serverDaemon;
421 parameters["target"] = target;
422 parameters["method"] = methodCall;
423 parameters["arguments"] = arguments;
424 parameters["priority"] = priority.ToString();
425 parameters["message"] = logMessage;
426
427 bool returnval = false;
428
429 try
430 {
431 IDbCommand result = Query(sql, parameters);
432
433 if (result.ExecuteNonQuery() == 1)
434 returnval = true;
435
436 result.Dispose();
437 }
438 catch (Exception e)
439 {
440 m_log.Error(e.ToString());
441 return false;
442 }
443
444 return returnval;
445 }
446
447 /// <summary>
448 /// Execute a SQL statement stored in a resource, as a string
449 /// </summary>
450 /// <param name="name"></param>
451 public void ExecuteResourceSql(string name)
452 {
453 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon);
454 cmd.ExecuteNonQuery();
455 cmd.Dispose();
456 }
457
458 public SqlConnection getConnection()
459 {
460 return (SqlConnection)dbcon;
461 }
462
463 /// <summary>
464 /// Given a list of tables, return the version of the tables, as seen in the database
465 /// </summary>
466 /// <param name="tableList"></param>
467 public void GetTableVersion(Dictionary<string, string> tableList)
468 {
469 lock (dbcon)
470 {
471 Dictionary<string, string> param = new Dictionary<string, string>();
472 param["dbname"] = dbcon.Database;
473 IDbCommand tablesCmd =
474 Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param);
475 using (IDataReader tables = tablesCmd.ExecuteReader())
476 {
477 while (tables.Read())
478 {
479 try
480 {
481 string tableName = (string)tables["TABLE_NAME"];
482 if (tableList.ContainsKey(tableName))
483 tableList[tableName] = tableName;
484 }
485 catch (Exception e)
486 {
487 m_log.Error(e.ToString());
488 }
489 }
490 tables.Close();
491 }
492 }
493 }
494
495 private string getResourceString(string name)
496 {
497 Assembly assem = GetType().Assembly;
498 string[] names = assem.GetManifestResourceNames();
499
500 foreach (string s in names)
501 if (s.EndsWith(name))
502 using (Stream resource = assem.GetManifestResourceStream(s))
503 {
504 using (StreamReader resourceReader = new StreamReader(resource))
505 {
506 string resourceString = resourceReader.ReadToEnd();
507 return resourceString;
508 }
509 }
510 throw new Exception(string.Format("Resource '{0}' was not found", name));
511 }
512
513 /// <summary>
514 /// Returns the version of this DB provider
515 /// </summary>
516 /// <returns>A string containing the DB provider</returns>
517 public string getVersion()
518 {
519 Module module = GetType().Module;
520 string dllName = module.Assembly.ManifestModule.Name;
521 Version dllVersion = module.Assembly.GetName().Version;
522
523
524 return
525 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
526 dllVersion.Revision);
527 }
528 }
529}
generated by cgit v1.1 at 2024-12-22 10:39:01 +0000