SQLiteAssetData.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347
  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.Data;
  29. using System.Reflection;
  30. using System.Collections.Generic;
  31. using log4net;
  32. using Mono.Data.SqliteClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. namespace OpenSim.Data.SQLiteLegacy
  36. {
  37. /// <summary>
  38. /// An asset storage interface for the SQLite database system
  39. /// </summary>
  40. public class SQLiteAssetData : AssetDataBase
  41. {
  42. // private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  43. private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
  44. private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, UUID from assets limit :start, :count";
  45. private const string DeleteAssetSQL = "delete from assets where UUID=:UUID";
  46. private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :Local, :Temporary, :Data)";
  47. private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID";
  48. private const string assetSelect = "select * from assets";
  49. private SqliteConnection m_conn;
  50. override public void Dispose()
  51. {
  52. if (m_conn != null)
  53. {
  54. m_conn.Close();
  55. m_conn = null;
  56. }
  57. }
  58. /// <summary>
  59. /// <list type="bullet">
  60. /// <item>Initialises AssetData interface</item>
  61. /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
  62. /// <item>use default URI if connect string is empty.</item>
  63. /// </list>
  64. /// </summary>
  65. /// <param name="dbconnect">connect string</param>
  66. override public void Initialise(string dbconnect)
  67. {
  68. if (dbconnect == string.Empty)
  69. {
  70. dbconnect = "URI=file:Asset.db,version=3";
  71. }
  72. m_conn = new SqliteConnection(dbconnect);
  73. m_conn.Open();
  74. Assembly assem = GetType().Assembly;
  75. Migration m = new Migration(m_conn, assem, "AssetStore");
  76. m.Update();
  77. return;
  78. }
  79. /// <summary>
  80. /// Fetch Asset
  81. /// </summary>
  82. /// <param name="uuid">UUID of ... ?</param>
  83. /// <returns>Asset base</returns>
  84. override public AssetBase GetAsset(UUID uuid)
  85. {
  86. lock (this)
  87. {
  88. using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
  89. {
  90. cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
  91. using (IDataReader reader = cmd.ExecuteReader())
  92. {
  93. if (reader.Read())
  94. {
  95. AssetBase asset = buildAsset(reader);
  96. reader.Close();
  97. return asset;
  98. }
  99. else
  100. {
  101. reader.Close();
  102. return null;
  103. }
  104. }
  105. }
  106. }
  107. }
  108. /// <summary>
  109. /// Create an asset
  110. /// </summary>
  111. /// <param name="asset">Asset Base</param>
  112. override public void StoreAsset(AssetBase asset)
  113. {
  114. //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
  115. if (ExistsAsset(asset.FullID))
  116. {
  117. //LogAssetLoad(asset);
  118. lock (this)
  119. {
  120. using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
  121. {
  122. cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
  123. cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
  124. cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
  125. cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
  126. cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
  127. cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
  128. cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
  129. cmd.ExecuteNonQuery();
  130. }
  131. }
  132. }
  133. else
  134. {
  135. lock (this)
  136. {
  137. using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
  138. {
  139. cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
  140. cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
  141. cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
  142. cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
  143. cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
  144. cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
  145. cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
  146. cmd.ExecuteNonQuery();
  147. }
  148. }
  149. }
  150. }
  151. // /// <summary>
  152. // /// Some... logging functionnality
  153. // /// </summary>
  154. // /// <param name="asset"></param>
  155. // private static void LogAssetLoad(AssetBase asset)
  156. // {
  157. // string temporary = asset.Temporary ? "Temporary" : "Stored";
  158. // string local = asset.Local ? "Local" : "Remote";
  159. //
  160. // int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
  161. //
  162. // m_log.Debug("[ASSET DB]: " +
  163. // string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
  164. // asset.FullID, asset.Name, asset.Description, asset.Type,
  165. // temporary, local, assetLength));
  166. // }
  167. /// <summary>
  168. /// Check if an asset exist in database
  169. /// </summary>
  170. /// <param name="uuid">The asset UUID</param>
  171. /// <returns>True if exist, or false.</returns>
  172. override public bool ExistsAsset(UUID uuid)
  173. {
  174. lock (this) {
  175. using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
  176. {
  177. cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
  178. using (IDataReader reader = cmd.ExecuteReader())
  179. {
  180. if (reader.Read())
  181. {
  182. reader.Close();
  183. return true;
  184. }
  185. else
  186. {
  187. reader.Close();
  188. return false;
  189. }
  190. }
  191. }
  192. }
  193. }
  194. /// <summary>
  195. /// Delete an asset from database
  196. /// </summary>
  197. /// <param name="uuid"></param>
  198. public void DeleteAsset(UUID uuid)
  199. {
  200. using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
  201. {
  202. cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
  203. cmd.ExecuteNonQuery();
  204. }
  205. }
  206. /// <summary>
  207. ///
  208. /// </summary>
  209. /// <param name="row"></param>
  210. /// <returns></returns>
  211. private static AssetBase buildAsset(IDataReader row)
  212. {
  213. // TODO: this doesn't work yet because something more
  214. // interesting has to be done to actually get these values
  215. // back out. Not enough time to figure it out yet.
  216. AssetBase asset = new AssetBase(
  217. new UUID((String)row["UUID"]),
  218. (String)row["Name"],
  219. Convert.ToSByte(row["Type"]),
  220. UUID.Zero.ToString()
  221. );
  222. asset.Description = (String) row["Description"];
  223. asset.Local = Convert.ToBoolean(row["Local"]);
  224. asset.Temporary = Convert.ToBoolean(row["Temporary"]);
  225. asset.Data = (byte[]) row["Data"];
  226. return asset;
  227. }
  228. private static AssetMetadata buildAssetMetadata(IDataReader row)
  229. {
  230. AssetMetadata metadata = new AssetMetadata();
  231. metadata.FullID = new UUID((string) row["UUID"]);
  232. metadata.Name = (string) row["Name"];
  233. metadata.Description = (string) row["Description"];
  234. metadata.Type = Convert.ToSByte(row["Type"]);
  235. metadata.Temporary = Convert.ToBoolean(row["Temporary"]); // Not sure if this is correct.
  236. // Current SHA1s are not stored/computed.
  237. metadata.SHA1 = new byte[] {};
  238. return metadata;
  239. }
  240. /// <summary>
  241. /// Returns a list of AssetMetadata objects. The list is a subset of
  242. /// the entire data set offset by <paramref name="start" /> containing
  243. /// <paramref name="count" /> elements.
  244. /// </summary>
  245. /// <param name="start">The number of results to discard from the total data set.</param>
  246. /// <param name="count">The number of rows the returned list should contain.</param>
  247. /// <returns>A list of AssetMetadata objects.</returns>
  248. public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  249. {
  250. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  251. lock (this)
  252. {
  253. using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
  254. {
  255. cmd.Parameters.Add(new SqliteParameter(":start", start));
  256. cmd.Parameters.Add(new SqliteParameter(":count", count));
  257. using (IDataReader reader = cmd.ExecuteReader())
  258. {
  259. while (reader.Read())
  260. {
  261. AssetMetadata metadata = buildAssetMetadata(reader);
  262. retList.Add(metadata);
  263. }
  264. }
  265. }
  266. }
  267. return retList;
  268. }
  269. /***********************************************************************
  270. *
  271. * Database Binding functions
  272. *
  273. * These will be db specific due to typing, and minor differences
  274. * in databases.
  275. *
  276. **********************************************************************/
  277. #region IPlugin interface
  278. /// <summary>
  279. ///
  280. /// </summary>
  281. override public string Version
  282. {
  283. get
  284. {
  285. Module module = GetType().Module;
  286. // string dllName = module.Assembly.ManifestModule.Name;
  287. Version dllVersion = module.Assembly.GetName().Version;
  288. return
  289. string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
  290. dllVersion.Revision);
  291. }
  292. }
  293. /// <summary>
  294. /// Initialise the AssetData interface using default URI
  295. /// </summary>
  296. override public void Initialise()
  297. {
  298. Initialise("URI=file:Asset.db,version=3");
  299. }
  300. /// <summary>
  301. /// Name of this DB provider
  302. /// </summary>
  303. override public string Name
  304. {
  305. get { return "SQLite Asset storage engine"; }
  306. }
  307. public override bool Delete(string id)
  308. {
  309. return false;
  310. }
  311. #endregion
  312. }
  313. }