MSSQLManager.cs 22 KB

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