PGSQLAssetData.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317
  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 OpenMetaverse;
  32. using log4net;
  33. using OpenSim.Framework;
  34. using Npgsql;
  35. using NpgsqlTypes;
  36. namespace OpenSim.Data.PGSQL
  37. {
  38. /// <summary>
  39. /// A PGSQL Interface for the Asset server
  40. /// </summary>
  41. public class PGSQLAssetData : AssetDataBase
  42. {
  43. private const string _migrationStore = "AssetStore";
  44. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  45. private long m_ticksToEpoch;
  46. /// <summary>
  47. /// Database manager
  48. /// </summary>
  49. private PGSQLManager m_database;
  50. private string m_connectionString;
  51. protected virtual Assembly Assembly
  52. {
  53. get { return GetType().Assembly; }
  54. }
  55. #region IPlugin Members
  56. override public void Dispose() { }
  57. /// <summary>
  58. /// <para>Initialises asset interface</para>
  59. /// </summary>
  60. // [Obsolete("Cannot be default-initialized!")]
  61. override public void Initialise()
  62. {
  63. m_log.Info("[PGSQLAssetData]: " + Name + " cannot be default-initialized!");
  64. throw new PluginNotInitialisedException(Name);
  65. }
  66. /// <summary>
  67. /// Initialises asset interface
  68. /// </summary>
  69. /// <para>
  70. /// a string instead of file, if someone writes the support
  71. /// </para>
  72. /// <param name="connectionString">connect string</param>
  73. override public void Initialise(string connectionString)
  74. {
  75. m_ticksToEpoch = new System.DateTime(1970, 1, 1).Ticks;
  76. m_database = new PGSQLManager(connectionString);
  77. m_connectionString = connectionString;
  78. //New migration to check for DB changes
  79. m_database.CheckMigration(_migrationStore);
  80. }
  81. /// <summary>
  82. /// Database provider version.
  83. /// </summary>
  84. override public string Version
  85. {
  86. get { return m_database.getVersion(); }
  87. }
  88. /// <summary>
  89. /// The name of this DB provider.
  90. /// </summary>
  91. override public string Name
  92. {
  93. get { return "PGSQL Asset storage engine"; }
  94. }
  95. #endregion
  96. #region IAssetDataPlugin Members
  97. /// <summary>
  98. /// Fetch Asset from m_database
  99. /// </summary>
  100. /// <param name="assetID">the asset UUID</param>
  101. /// <returns></returns>
  102. override public AssetBase GetAsset(UUID assetID)
  103. {
  104. string sql = "SELECT * FROM assets WHERE id = :id";
  105. using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
  106. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  107. {
  108. cmd.Parameters.Add(m_database.CreateParameter("id", assetID));
  109. conn.Open();
  110. using (NpgsqlDataReader reader = cmd.ExecuteReader())
  111. {
  112. if (reader.Read())
  113. {
  114. AssetBase asset = new AssetBase(
  115. DBGuid.FromDB(reader["id"]),
  116. (string)reader["name"],
  117. Convert.ToSByte(reader["assetType"]),
  118. reader["creatorid"].ToString()
  119. );
  120. // Region Main
  121. asset.Description = (string)reader["description"];
  122. asset.Local = Convert.ToBoolean(reader["local"]);
  123. asset.Temporary = Convert.ToBoolean(reader["temporary"]);
  124. asset.Flags = (AssetFlags)(Convert.ToInt32(reader["asset_flags"]));
  125. asset.Data = (byte[])reader["data"];
  126. return asset;
  127. }
  128. return null; // throw new Exception("No rows to return");
  129. }
  130. }
  131. }
  132. /// <summary>
  133. /// Create asset in m_database
  134. /// </summary>
  135. /// <param name="asset">the asset</param>
  136. override public bool StoreAsset(AssetBase asset)
  137. {
  138. string sql =
  139. @"UPDATE assets set name = :name, description = :description, " + "\"assetType\" " + @" = :assetType,
  140. local = :local, temporary = :temporary, creatorid = :creatorid, data = :data
  141. WHERE id=:id;
  142. INSERT INTO assets
  143. (id, name, description, " + "\"assetType\" " + @", local,
  144. temporary, create_time, access_time, creatorid, asset_flags, data)
  145. Select :id, :name, :description, :assetType, :local,
  146. :temporary, :create_time, :access_time, :creatorid, :asset_flags, :data
  147. Where not EXISTS(SELECT * FROM assets WHERE id=:id)
  148. ";
  149. string assetName = asset.Name;
  150. if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
  151. {
  152. assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
  153. m_log.WarnFormat(
  154. "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
  155. asset.Name, asset.ID, asset.Name.Length, assetName.Length);
  156. }
  157. string assetDescription = asset.Description;
  158. if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
  159. {
  160. assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
  161. m_log.WarnFormat(
  162. "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
  163. asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
  164. }
  165. using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
  166. using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))
  167. {
  168. int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
  169. command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
  170. command.Parameters.Add(m_database.CreateParameter("name", assetName));
  171. command.Parameters.Add(m_database.CreateParameter("description", assetDescription));
  172. command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
  173. command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
  174. command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
  175. command.Parameters.Add(m_database.CreateParameter("access_time", now));
  176. command.Parameters.Add(m_database.CreateParameter("create_time", now));
  177. command.Parameters.Add(m_database.CreateParameter("asset_flags", (int)asset.Flags));
  178. command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID));
  179. command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
  180. conn.Open();
  181. try
  182. {
  183. command.ExecuteNonQuery();
  184. }
  185. catch(Exception e)
  186. {
  187. m_log.Error("[ASSET DB]: Error storing item :" + e.Message + " sql "+sql);
  188. }
  189. }
  190. return true;
  191. }
  192. // Commented out since currently unused - this probably should be called in GetAsset()
  193. // private void UpdateAccessTime(AssetBase asset)
  194. // {
  195. // using (AutoClosingSqlCommand cmd = m_database.Query("UPDATE assets SET access_time = :access_time WHERE id=:id"))
  196. // {
  197. // int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
  198. // cmd.Parameters.AddWithValue(":id", asset.FullID.ToString());
  199. // cmd.Parameters.AddWithValue(":access_time", now);
  200. // try
  201. // {
  202. // cmd.ExecuteNonQuery();
  203. // }
  204. // catch (Exception e)
  205. // {
  206. // m_log.Error(e.ToString());
  207. // }
  208. // }
  209. // }
  210. /// <summary>
  211. /// Check if the assets exist in the database.
  212. /// </summary>
  213. /// <param name="uuids">The assets' IDs</param>
  214. /// <returns>For each asset: true if it exists, false otherwise</returns>
  215. public override bool[] AssetsExist(UUID[] uuids)
  216. {
  217. if (uuids.Length == 0)
  218. return new bool[0];
  219. HashSet<UUID> exist = new HashSet<UUID>();
  220. string ids = "'" + string.Join("','", uuids) + "'";
  221. string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids);
  222. using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
  223. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  224. {
  225. conn.Open();
  226. using (NpgsqlDataReader reader = cmd.ExecuteReader())
  227. {
  228. while (reader.Read())
  229. {
  230. UUID id = DBGuid.FromDB(reader["id"]);
  231. exist.Add(id);
  232. }
  233. }
  234. }
  235. bool[] results = new bool[uuids.Length];
  236. for (int i = 0; i < uuids.Length; i++)
  237. results[i] = exist.Contains(uuids[i]);
  238. return results;
  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. string sql = @" SELECT id, name, description, " + "\"assetType\"" + @", temporary, creatorid
  252. FROM assets
  253. order by id
  254. limit :stop
  255. offset :start;";
  256. using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
  257. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  258. {
  259. cmd.Parameters.Add(m_database.CreateParameter("start", start));
  260. cmd.Parameters.Add(m_database.CreateParameter("stop", start + count - 1));
  261. conn.Open();
  262. using (NpgsqlDataReader reader = cmd.ExecuteReader())
  263. {
  264. while (reader.Read())
  265. {
  266. AssetMetadata metadata = new AssetMetadata();
  267. metadata.FullID = DBGuid.FromDB(reader["id"]);
  268. metadata.Name = (string)reader["name"];
  269. metadata.Description = (string)reader["description"];
  270. metadata.Type = Convert.ToSByte(reader["assetType"]);
  271. metadata.Temporary = Convert.ToBoolean(reader["temporary"]);
  272. metadata.CreatorID = (string)reader["creatorid"];
  273. retList.Add(metadata);
  274. }
  275. }
  276. }
  277. return retList;
  278. }
  279. public override bool Delete(string id)
  280. {
  281. return false;
  282. }
  283. #endregion
  284. }
  285. }