1
0

SQLiteManager.cs 9.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections.Generic;
  29. using System.Data;
  30. using System.Data.SQLite;
  31. using System.Reflection;
  32. using log4net;
  33. using OpenMetaverse;
  34. namespace OpenSim.Data.SQLite
  35. {
  36. /// <summary>
  37. /// SQLite Manager
  38. /// </summary>
  39. internal class SQLiteManager : SQLiteUtil
  40. {
  41. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  42. private IDbConnection dbcon;
  43. /// <summary>
  44. /// <list type="bullet">
  45. /// <item>Initialises and creates a new SQLite connection and maintains it.</item>
  46. /// <item>use default URI if connect string is empty.</item>
  47. /// </list>
  48. /// </summary>
  49. /// <param name="connect">connect string</param>
  50. public SQLiteManager(string connect)
  51. {
  52. try
  53. {
  54. string connectionString = String.Empty;
  55. if (connect != String.Empty)
  56. {
  57. connectionString = connect;
  58. }
  59. else
  60. {
  61. m_log.Warn("[SQLITE] grid db not specified, using default");
  62. connectionString = "URI=file:GridServerSqlite.db;";
  63. }
  64. dbcon = new SQLiteConnection(connectionString);
  65. dbcon.Open();
  66. }
  67. catch (Exception e)
  68. {
  69. throw new Exception("Error initialising SQLite Database: " + e.ToString());
  70. }
  71. }
  72. /// <summary>
  73. /// Shuts down the database connection
  74. /// </summary>
  75. public void Close()
  76. {
  77. dbcon.Close();
  78. dbcon = null;
  79. }
  80. /// <summary>
  81. /// Runs a query with protection against SQL Injection by using parameterised input.
  82. /// </summary>
  83. /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
  84. /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
  85. /// <returns>A SQLite DB Command</returns>
  86. public IDbCommand Query(string sql, Dictionary<string, string> parameters)
  87. {
  88. SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand();
  89. dbcommand.CommandText = sql;
  90. foreach (KeyValuePair<string, string> param in parameters)
  91. {
  92. SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value);
  93. dbcommand.Parameters.Add(paramx);
  94. }
  95. return (IDbCommand) dbcommand;
  96. }
  97. /// <summary>
  98. /// Reads a region row from a database reader
  99. /// </summary>
  100. /// <param name="reader">An active database reader</param>
  101. /// <returns>A region profile</returns>
  102. public RegionProfileData getRow(IDataReader reader)
  103. {
  104. RegionProfileData retval = new RegionProfileData();
  105. if (reader.Read())
  106. {
  107. // Region Main
  108. retval.regionHandle = (ulong) reader["regionHandle"];
  109. retval.regionName = (string) reader["regionName"];
  110. retval.UUID = new UUID((string) reader["uuid"]);
  111. // Secrets
  112. retval.regionRecvKey = (string) reader["regionRecvKey"];
  113. retval.regionSecret = (string) reader["regionSecret"];
  114. retval.regionSendKey = (string) reader["regionSendKey"];
  115. // Region Server
  116. retval.regionDataURI = (string) reader["regionDataURI"];
  117. retval.regionOnline = false; // Needs to be pinged before this can be set.
  118. retval.serverIP = (string) reader["serverIP"];
  119. retval.serverPort = (uint) reader["serverPort"];
  120. retval.serverURI = (string) reader["serverURI"];
  121. // Location
  122. retval.regionLocX = (uint) ((int) reader["locX"]);
  123. retval.regionLocY = (uint) ((int) reader["locY"]);
  124. retval.regionLocZ = (uint) ((int) reader["locZ"]);
  125. // Neighbours - 0 = No Override
  126. retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"];
  127. retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"];
  128. retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"];
  129. retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"];
  130. // Assets
  131. retval.regionAssetURI = (string) reader["regionAssetURI"];
  132. retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
  133. retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
  134. // Userserver
  135. retval.regionUserURI = (string) reader["regionUserURI"];
  136. retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
  137. retval.regionUserSendKey = (string) reader["regionUserSendKey"];
  138. }
  139. else
  140. {
  141. throw new Exception("No rows to return");
  142. }
  143. return retval;
  144. }
  145. /// <summary>
  146. /// Inserts a new region into the database
  147. /// </summary>
  148. /// <param name="profile">The region to insert</param>
  149. /// <returns>Success?</returns>
  150. public bool insertRow(RegionProfileData profile)
  151. {
  152. string sql =
  153. "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
  154. sql +=
  155. "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
  156. sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES ";
  157. sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
  158. sql +=
  159. "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
  160. sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
  161. Dictionary<string, string> parameters = new Dictionary<string, string>();
  162. parameters["regionHandle"] = profile.regionHandle.ToString();
  163. parameters["regionName"] = profile.regionName;
  164. parameters["uuid"] = profile.UUID.ToString();
  165. parameters["regionRecvKey"] = profile.regionRecvKey;
  166. parameters["regionSendKey"] = profile.regionSendKey;
  167. parameters["regionDataURI"] = profile.regionDataURI;
  168. parameters["serverIP"] = profile.serverIP;
  169. parameters["serverPort"] = profile.serverPort.ToString();
  170. parameters["serverURI"] = profile.serverURI;
  171. parameters["locX"] = profile.regionLocX.ToString();
  172. parameters["locY"] = profile.regionLocY.ToString();
  173. parameters["locZ"] = profile.regionLocZ.ToString();
  174. parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
  175. parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
  176. parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
  177. parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
  178. parameters["regionAssetURI"] = profile.regionAssetURI;
  179. parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
  180. parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
  181. parameters["regionUserURI"] = profile.regionUserURI;
  182. parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
  183. parameters["regionUserSendKey"] = profile.regionUserSendKey;
  184. bool returnval = false;
  185. try
  186. {
  187. IDbCommand result = Query(sql, parameters);
  188. if (result.ExecuteNonQuery() == 1)
  189. returnval = true;
  190. result.Dispose();
  191. }
  192. catch (Exception)
  193. {
  194. return false;
  195. }
  196. return returnval;
  197. }
  198. }
  199. }