OpenSim-Test/OpenSim/Data/MSSQL/MSSQLAssetData.cs

/*
 * Copyright (c) Contributors, http://opensimulator.org/
 * See CONTRIBUTORS.TXT for a full list of copyright holders.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *     * Redistributions of source code must retain the above copyright
 *       notice, this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright
 *       notice, this list of conditions and the following disclaimer in the
 *       documentation and/or other materials provided with the distribution.
 *     * Neither the name of the OpenSim Project nor the
 *       names of its contributors may be used to endorse or promote products
 *       derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using libsecondlife;
using log4net;
using OpenSim.Framework;

namespace OpenSim.Data.MSSQL
{
    /// <summary>
    /// A MSSQL Interface for the Asset server
    /// </summary>
    internal class MSSQLAssetData : AssetDataBase
    {
        private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        private MSSQLManager database;

        #region IAssetProvider Members

        /// <summary>
        /// Migration method
        /// <list type="bullet">
        /// <item>Execute "CreateAssetsTable.sql" if tableName == null</item>
        /// </list>
        /// </summary>
        /// <param name="tableName">Name of table</param>
        private void UpgradeAssetsTable(string tableName)
        {
            // null as the version, indicates that the table didn't exist
            if (tableName == null)
            {
                m_log.Info("[ASSET DB]: Creating new database tables");
                database.ExecuteResourceSql("CreateAssetsTable.sql");
                return;
            }
            
        }

        /// <summary>
        /// Ensure that the assets related tables exists and are at the latest version
        /// </summary>
        private void TestTables()
        {
            Dictionary<string, string> tableList = new Dictionary<string, string>();

            tableList["assets"] = null;
            database.GetTableVersion(tableList);

            UpgradeAssetsTable(tableList["assets"]);
        }

        /// <summary>
        /// Fetch Asset from database
        /// </summary>
        /// <param name="assetID">the asset UUID</param>
        /// <returns></returns>
        override public AssetBase FetchAsset(LLUUID assetID)
        {
            Dictionary<string, string> param = new Dictionary<string, string>();
            param["id"] = assetID.ToString();

            using (IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param))
            using (IDataReader reader = result.ExecuteReader())
            {
                return database.getAssetRow(reader);
            }
        }

        /// <summary>
        /// Create asset in database
        /// </summary>
        /// <param name="asset">the asset</param>
        override public void CreateAsset(AssetBase asset)
        {
            if (ExistsAsset((LLUUID) asset.FullID))
            {
                return;
            }


            using (AutoClosingSqlCommand cmd =
                database.Query(
                    "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" +
                    " VALUES " +
                    "(@id, @name, @description, @assetType, @local, @temporary, @data)"))
            {

                //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar);
                //p.Value = asset.FullID.ToString();
                cmd.Parameters.AddWithValue("id", asset.FullID.ToString());
                cmd.Parameters.AddWithValue("name", asset.Name);
                cmd.Parameters.AddWithValue("description", asset.Description);
                SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt);
                e.Value = asset.Type;
                SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt);
                g.Value = asset.Local;
                SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt);
                h.Value = asset.Temporary;
                SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image);
                i.Value = asset.Data;

                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Update asset in database
        /// </summary>
        /// <param name="asset">the asset</param>
        override public void UpdateAsset(AssetBase asset)
        {
            using (IDbCommand command = database.Query("UPDATE assets set id = @id, " +
                                                "name = @name, " +
                                                "description = @description," +
                                                "assetType = @assetType," +
                                                "local = @local," +
                                                "temporary = @temporary," +
                                                "data = @data where " +
                                                "id = @keyId;"))
            {
                SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToString());
                SqlParameter param2 = new SqlParameter("@name", asset.Name);
                SqlParameter param3 = new SqlParameter("@description", asset.Description);
                SqlParameter param4 = new SqlParameter("@assetType", asset.Type);
                SqlParameter param6 = new SqlParameter("@local", asset.Local);
                SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary);
                SqlParameter param8 = new SqlParameter("@data", asset.Data);
                SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToString());
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                command.Parameters.Add(param3);
                command.Parameters.Add(param4);
                command.Parameters.Add(param6);
                command.Parameters.Add(param7);
                command.Parameters.Add(param8);
                command.Parameters.Add(param9);

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    m_log.Error(e.ToString());
                }
            }
        }

        /// <summary>
        /// Check if asset exist in database
        /// </summary>
        /// <param name="uuid"></param>
        /// <returns>true if exist.</returns>
        override public bool ExistsAsset(LLUUID uuid)
        {
            if (FetchAsset(uuid) != null)
            {
                return true;
            }
            return false;
        }

        #endregion

        #region IPlugin Members

        override public void Dispose() { }

        /// <summary>
        /// <para>Initialises asset interface</para>
        /// <para>
        /// TODO: this would allow you to pass in connnect info as
        /// a string instead of file, if someone writes the support
        /// </para>
        /// </summary>
        /// <param name="connect">connect string</param>
        override public void Initialise(string connect)
        {
            Initialise();
        }

        /// <summary>
        /// Initialises asset interface
        /// </summary>
        /// <remarks>it use mssql_connection.ini</remarks>
        override public void Initialise()
        {
            IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
            string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
            string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
            string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
            string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
            string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");

            database =
                new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
                                 settingPassword);

            TestTables();
        }

        /// <summary>
        /// Database provider version. 
        /// </summary>
        override public string Version
        {
            get { return database.getVersion(); }
        }

        /// <summary>
        /// The name of this DB provider. 
        /// </summary>
        override public string Name
        {
            get { return "MSSQL Asset storage engine"; }
        }

        #endregion
    }
}