SQLiteManager.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282
  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.SQLite;
  31. using libsecondlife;
  32. using Mono.Data.SqliteClient;
  33. using OpenSim.Framework.Console;
  34. namespace OpenSim.Framework.Data.SQLite
  35. {
  36. internal class SQLiteManager : SQLiteUtil
  37. {
  38. private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
  39. private IDbConnection dbcon;
  40. /// <summary>
  41. /// Initialises and creates a new SQLite connection and maintains it.
  42. /// </summary>
  43. /// <param name="hostname">The SQLite server being connected to</param>
  44. /// <param name="database">The name of the SQLite database being used</param>
  45. /// <param name="username">The username logging into the database</param>
  46. /// <param name="password">The password for the user logging in</param>
  47. /// <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>
  48. public SQLiteManager(string hostname, string database, string username, string password, string cpooling)
  49. {
  50. try
  51. {
  52. string connectionString = "URI=file:GridServerSqlite.db;";
  53. dbcon = new SQLiteConnection(connectionString);
  54. dbcon.Open();
  55. }
  56. catch (Exception e)
  57. {
  58. throw new Exception("Error initialising SQLite Database: " + e.ToString());
  59. }
  60. }
  61. /// <summary>
  62. /// Shuts down the database connection
  63. /// </summary>
  64. public void Close()
  65. {
  66. dbcon.Close();
  67. dbcon = null;
  68. }
  69. /// <summary>
  70. /// Runs a query with protection against SQL Injection by using parameterised input.
  71. /// </summary>
  72. /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
  73. /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
  74. /// <returns>A SQLite DB Command</returns>
  75. public IDbCommand Query(string sql, Dictionary<string, string> parameters)
  76. {
  77. SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand();
  78. dbcommand.CommandText = sql;
  79. foreach (KeyValuePair<string, string> param in parameters)
  80. {
  81. SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value);
  82. dbcommand.Parameters.Add(paramx);
  83. }
  84. return (IDbCommand) dbcommand;
  85. }
  86. // TODO: unused
  87. // private bool TestTables(SQLiteConnection conn)
  88. // {
  89. // SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM regions", conn);
  90. // SQLiteDataAdapter pDa = new SQLiteDataAdapter(cmd);
  91. // DataSet tmpDS = new DataSet();
  92. // try
  93. // {
  94. // pDa.Fill(tmpDS, "regions");
  95. // }
  96. // catch (SqliteSyntaxException)
  97. // {
  98. // m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
  99. // InitDB(conn);
  100. // }
  101. // return true;
  102. // }
  103. // TODO: unused
  104. // private DataTable createRegionsTable()
  105. // {
  106. // DataTable regions = new DataTable("regions");
  107. // createCol(regions, "regionHandle", typeof (ulong));
  108. // createCol(regions, "regionName", typeof (String));
  109. // createCol(regions, "uuid", typeof (String));
  110. // createCol(regions, "regionRecvKey", typeof (String));
  111. // createCol(regions, "regionSecret", typeof (String));
  112. // createCol(regions, "regionSendKey", typeof (String));
  113. // createCol(regions, "regionDataURI", typeof (String));
  114. // createCol(regions, "serverIP", typeof (String));
  115. // createCol(regions, "serverPort", typeof (String));
  116. // createCol(regions, "serverURI", typeof (String));
  117. // createCol(regions, "locX", typeof (uint));
  118. // createCol(regions, "locY", typeof (uint));
  119. // createCol(regions, "locZ", typeof (uint));
  120. // createCol(regions, "eastOverrideHandle", typeof (ulong));
  121. // createCol(regions, "westOverrideHandle", typeof (ulong));
  122. // createCol(regions, "southOverrideHandle", typeof (ulong));
  123. // createCol(regions, "northOverrideHandle", typeof (ulong));
  124. // createCol(regions, "regionAssetURI", typeof (String));
  125. // createCol(regions, "regionAssetRecvKey", typeof (String));
  126. // createCol(regions, "regionAssetSendKey", typeof (String));
  127. // createCol(regions, "regionUserURI", typeof (String));
  128. // createCol(regions, "regionUserRecvKey", typeof (String));
  129. // createCol(regions, "regionUserSendKey", typeof (String));
  130. // // Add in contraints
  131. // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
  132. // return regions;
  133. // }
  134. // TODO: unused
  135. // private void InitDB(SQLiteConnection conn)
  136. // {
  137. // string createUsers = defineTable(createRegionsTable());
  138. // SQLiteCommand pcmd = new SQLiteCommand(createUsers, conn);
  139. // conn.Open();
  140. // pcmd.ExecuteNonQuery();
  141. // conn.Close();
  142. // }
  143. /// <summary>
  144. /// Reads a region row from a database reader
  145. /// </summary>
  146. /// <param name="reader">An active database reader</param>
  147. /// <returns>A region profile</returns>
  148. public RegionProfileData getRow(IDataReader reader)
  149. {
  150. RegionProfileData retval = new RegionProfileData();
  151. if (reader.Read())
  152. {
  153. // Region Main
  154. retval.regionHandle = (ulong) reader["regionHandle"];
  155. retval.regionName = (string) reader["regionName"];
  156. retval.UUID = new LLUUID((string) reader["uuid"]);
  157. // Secrets
  158. retval.regionRecvKey = (string) reader["regionRecvKey"];
  159. retval.regionSecret = (string) reader["regionSecret"];
  160. retval.regionSendKey = (string) reader["regionSendKey"];
  161. // Region Server
  162. retval.regionDataURI = (string) reader["regionDataURI"];
  163. retval.regionOnline = false; // Needs to be pinged before this can be set.
  164. retval.serverIP = (string) reader["serverIP"];
  165. retval.serverPort = (uint) reader["serverPort"];
  166. retval.serverURI = (string) reader["serverURI"];
  167. // Location
  168. retval.regionLocX = (uint) ((int) reader["locX"]);
  169. retval.regionLocY = (uint) ((int) reader["locY"]);
  170. retval.regionLocZ = (uint) ((int) reader["locZ"]);
  171. // Neighbours - 0 = No Override
  172. retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"];
  173. retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"];
  174. retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"];
  175. retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"];
  176. // Assets
  177. retval.regionAssetURI = (string) reader["regionAssetURI"];
  178. retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
  179. retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
  180. // Userserver
  181. retval.regionUserURI = (string) reader["regionUserURI"];
  182. retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
  183. retval.regionUserSendKey = (string) reader["regionUserSendKey"];
  184. }
  185. else
  186. {
  187. throw new Exception("No rows to return");
  188. }
  189. return retval;
  190. }
  191. /// <summary>
  192. /// Inserts a new region into the database
  193. /// </summary>
  194. /// <param name="profile">The region to insert</param>
  195. /// <returns>Success?</returns>
  196. public bool insertRow(RegionProfileData profile)
  197. {
  198. string sql =
  199. "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
  200. sql +=
  201. "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
  202. sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES ";
  203. sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
  204. sql +=
  205. "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
  206. sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
  207. Dictionary<string, string> parameters = new Dictionary<string, string>();
  208. parameters["regionHandle"] = profile.regionHandle.ToString();
  209. parameters["regionName"] = profile.regionName;
  210. parameters["uuid"] = profile.UUID.ToString();
  211. parameters["regionRecvKey"] = profile.regionRecvKey;
  212. parameters["regionSendKey"] = profile.regionSendKey;
  213. parameters["regionDataURI"] = profile.regionDataURI;
  214. parameters["serverIP"] = profile.serverIP;
  215. parameters["serverPort"] = profile.serverPort.ToString();
  216. parameters["serverURI"] = profile.serverURI;
  217. parameters["locX"] = profile.regionLocX.ToString();
  218. parameters["locY"] = profile.regionLocY.ToString();
  219. parameters["locZ"] = profile.regionLocZ.ToString();
  220. parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
  221. parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
  222. parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
  223. parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
  224. parameters["regionAssetURI"] = profile.regionAssetURI;
  225. parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
  226. parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
  227. parameters["regionUserURI"] = profile.regionUserURI;
  228. parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
  229. parameters["regionUserSendKey"] = profile.regionUserSendKey;
  230. bool returnval = false;
  231. try
  232. {
  233. IDbCommand result = Query(sql, parameters);
  234. if (result.ExecuteNonQuery() == 1)
  235. returnval = true;
  236. result.Dispose();
  237. }
  238. catch (Exception)
  239. {
  240. return false;
  241. }
  242. return returnval;
  243. }
  244. }
  245. }