1
0

SQLiteAssetData.cs 15 KB

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