SQLiteAssetData.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397
  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. string assetName = asset.Name;
  124. if (asset.Name.Length > 64)
  125. {
  126. assetName = asset.Name.Substring(0, 64);
  127. m_log.WarnFormat(
  128. "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
  129. asset.Name, asset.ID, asset.Name.Length, assetName.Length);
  130. }
  131. string assetDescription = asset.Description;
  132. if (asset.Description.Length > 64)
  133. {
  134. assetDescription = asset.Description.Substring(0, 64);
  135. m_log.WarnFormat(
  136. "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
  137. asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
  138. }
  139. //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
  140. if (ExistsAsset(asset.FullID))
  141. {
  142. //LogAssetLoad(asset);
  143. lock (this)
  144. {
  145. using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
  146. {
  147. cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
  148. cmd.Parameters.Add(new SqliteParameter(":Name", assetName));
  149. cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription));
  150. cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
  151. cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
  152. cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
  153. cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
  154. cmd.Parameters.Add(new SqliteParameter(":CreatorID", asset.Metadata.CreatorID));
  155. cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
  156. cmd.ExecuteNonQuery();
  157. }
  158. }
  159. }
  160. else
  161. {
  162. lock (this)
  163. {
  164. using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
  165. {
  166. cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
  167. cmd.Parameters.Add(new SqliteParameter(":Name", assetName));
  168. cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription));
  169. cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
  170. cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
  171. cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
  172. cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
  173. cmd.Parameters.Add(new SqliteParameter(":CreatorID", asset.Metadata.CreatorID));
  174. cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
  175. cmd.ExecuteNonQuery();
  176. }
  177. }
  178. }
  179. }
  180. // /// <summary>
  181. // /// Some... logging functionnality
  182. // /// </summary>
  183. // /// <param name="asset"></param>
  184. // private static void LogAssetLoad(AssetBase asset)
  185. // {
  186. // string temporary = asset.Temporary ? "Temporary" : "Stored";
  187. // string local = asset.Local ? "Local" : "Remote";
  188. //
  189. // int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
  190. //
  191. // m_log.Debug("[ASSET DB]: " +
  192. // string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
  193. // asset.FullID, asset.Name, asset.Description, asset.Type,
  194. // temporary, local, assetLength));
  195. // }
  196. /// <summary>
  197. /// Check if an asset exist in database
  198. /// </summary>
  199. /// <param name="uuid">The asset UUID</param>
  200. /// <returns>True if exist, or false.</returns>
  201. override public bool ExistsAsset(UUID uuid)
  202. {
  203. lock (this)
  204. {
  205. using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
  206. {
  207. cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
  208. using (IDataReader reader = cmd.ExecuteReader())
  209. {
  210. if (reader.Read())
  211. {
  212. reader.Close();
  213. return true;
  214. }
  215. else
  216. {
  217. reader.Close();
  218. return false;
  219. }
  220. }
  221. }
  222. }
  223. }
  224. /// <summary>
  225. ///
  226. /// </summary>
  227. /// <param name="row"></param>
  228. /// <returns></returns>
  229. private static AssetBase buildAsset(IDataReader row)
  230. {
  231. // TODO: this doesn't work yet because something more
  232. // interesting has to be done to actually get these values
  233. // back out. Not enough time to figure it out yet.
  234. AssetBase asset = new AssetBase(
  235. new UUID((String)row["UUID"]),
  236. (String)row["Name"],
  237. Convert.ToSByte(row["Type"]),
  238. (String)row["CreatorID"]
  239. );
  240. asset.Description = (String) row["Description"];
  241. asset.Local = Convert.ToBoolean(row["Local"]);
  242. asset.Temporary = Convert.ToBoolean(row["Temporary"]);
  243. asset.Flags = (AssetFlags)Convert.ToInt32(row["asset_flags"]);
  244. asset.Data = (byte[])row["Data"];
  245. return asset;
  246. }
  247. private static AssetMetadata buildAssetMetadata(IDataReader row)
  248. {
  249. AssetMetadata metadata = new AssetMetadata();
  250. metadata.FullID = new UUID((string) row["UUID"]);
  251. metadata.Name = (string) row["Name"];
  252. metadata.Description = (string) row["Description"];
  253. metadata.Type = Convert.ToSByte(row["Type"]);
  254. metadata.Temporary = Convert.ToBoolean(row["Temporary"]); // Not sure if this is correct.
  255. metadata.Flags = (AssetFlags)Convert.ToInt32(row["asset_flags"]);
  256. metadata.CreatorID = row["CreatorID"].ToString();
  257. // Current SHA1s are not stored/computed.
  258. metadata.SHA1 = new byte[] {};
  259. return metadata;
  260. }
  261. /// <summary>
  262. /// Returns a list of AssetMetadata objects. The list is a subset of
  263. /// the entire data set offset by <paramref name="start" /> containing
  264. /// <paramref name="count" /> elements.
  265. /// </summary>
  266. /// <param name="start">The number of results to discard from the total data set.</param>
  267. /// <param name="count">The number of rows the returned list should contain.</param>
  268. /// <returns>A list of AssetMetadata objects.</returns>
  269. public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  270. {
  271. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  272. lock (this)
  273. {
  274. using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
  275. {
  276. cmd.Parameters.Add(new SqliteParameter(":start", start));
  277. cmd.Parameters.Add(new SqliteParameter(":count", count));
  278. using (IDataReader reader = cmd.ExecuteReader())
  279. {
  280. while (reader.Read())
  281. {
  282. AssetMetadata metadata = buildAssetMetadata(reader);
  283. retList.Add(metadata);
  284. }
  285. }
  286. }
  287. }
  288. return retList;
  289. }
  290. /***********************************************************************
  291. *
  292. * Database Binding functions
  293. *
  294. * These will be db specific due to typing, and minor differences
  295. * in databases.
  296. *
  297. **********************************************************************/
  298. #region IPlugin interface
  299. /// <summary>
  300. ///
  301. /// </summary>
  302. override public string Version
  303. {
  304. get
  305. {
  306. Module module = GetType().Module;
  307. // string dllName = module.Assembly.ManifestModule.Name;
  308. Version dllVersion = module.Assembly.GetName().Version;
  309. return
  310. string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
  311. dllVersion.Revision);
  312. }
  313. }
  314. /// <summary>
  315. /// Initialise the AssetData interface using default URI
  316. /// </summary>
  317. override public void Initialise()
  318. {
  319. Initialise("URI=file:Asset.db,version=3");
  320. }
  321. /// <summary>
  322. /// Name of this DB provider
  323. /// </summary>
  324. override public string Name
  325. {
  326. get { return "SQLite Asset storage engine"; }
  327. }
  328. // TODO: (AlexRa): one of these is to be removed eventually (?)
  329. /// <summary>
  330. /// Delete an asset from database
  331. /// </summary>
  332. /// <param name="uuid"></param>
  333. public bool DeleteAsset(UUID uuid)
  334. {
  335. lock (this)
  336. {
  337. using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
  338. {
  339. cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
  340. cmd.ExecuteNonQuery();
  341. }
  342. }
  343. return true;
  344. }
  345. public override bool Delete(string id)
  346. {
  347. UUID assetID;
  348. if (!UUID.TryParse(id, out assetID))
  349. return false;
  350. return DeleteAsset(assetID);
  351. }
  352. #endregion
  353. }
  354. }