From ff8a76825841533bdc5d534b6f58b2ab964ea6c6 Mon Sep 17 00:00:00 2001
From: Fernando Oliveira
Date: Sat, 12 Oct 2013 16:33:45 -0500
Subject: Fernando Oliveira's Postgress SQL Server Data Connector as a single
commit. * Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs *
PostgreSQL data access implementation * PostgreSQL dll binarie and
RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres
SQL Type fixes * Postgres SQL Connection string * Data type issues * more
fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to
internal csharp types * More data type fix (PostgreSQL fields are case
sensitive) :( * more field case sensitive fixes * changed the migration files
to be case sensitive for fields. * fixed fields case * finished converting,
now search for hidden bugs. * some more fixes * bool type fixed * more case
fixes; * creatorID case fixed * case fields fixed * fixed default now() for
TMStamp fields with don't allow nulls. * fix case sensitve for Region name
and Estate name * fixed case for names for search * fix class name Error *
Bug fixed on select and migrations * Un-Reverting my change due to Postgres
issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for
integration with Diva Distro * Added System.Core to prebuild.xml for PG
project * Configured to make DIff for Push to OpenSim Project * Diffs only to
PostgreSQL mods.
---
OpenSim/Data/PGSQL/PGSQLAssetData.cs | 295 +++++++++++++++++++++++++++++++++++
1 file changed, 295 insertions(+)
create mode 100644 OpenSim/Data/PGSQL/PGSQLAssetData.cs
(limited to 'OpenSim/Data/PGSQL/PGSQLAssetData.cs')
diff --git a/OpenSim/Data/PGSQL/PGSQLAssetData.cs b/OpenSim/Data/PGSQL/PGSQLAssetData.cs
new file mode 100644
index 0000000..ab74856
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLAssetData.cs
@@ -0,0 +1,295 @@
+/*
+ * 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 OpenSimulator 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.Data;
+using System.Reflection;
+using System.Collections.Generic;
+using OpenMetaverse;
+using log4net;
+using OpenSim.Framework;
+using Npgsql;
+using NpgsqlTypes;
+
+namespace OpenSim.Data.PGSQL
+{
+ ///
+ /// A PGSQL Interface for the Asset server
+ ///
+ public class PGSQLAssetData : AssetDataBase
+ {
+ private const string _migrationStore = "AssetStore";
+
+ private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
+ private long m_ticksToEpoch;
+ ///
+ /// Database manager
+ ///
+ private PGSQLManager m_database;
+ private string m_connectionString;
+
+ protected virtual Assembly Assembly
+ {
+ get { return GetType().Assembly; }
+ }
+
+ #region IPlugin Members
+
+ override public void Dispose() { }
+
+ ///
+ /// Initialises asset interface
+ ///
+ // [Obsolete("Cannot be default-initialized!")]
+ override public void Initialise()
+ {
+ m_log.Info("[PGSQLAssetData]: " + Name + " cannot be default-initialized!");
+ throw new PluginNotInitialisedException(Name);
+ }
+
+ ///
+ /// Initialises asset interface
+ ///
+ ///
+ /// a string instead of file, if someone writes the support
+ ///
+ /// connect string
+ override public void Initialise(string connectionString)
+ {
+ m_ticksToEpoch = new System.DateTime(1970, 1, 1).Ticks;
+
+ m_database = new PGSQLManager(connectionString);
+ m_connectionString = connectionString;
+
+ //New migration to check for DB changes
+ m_database.CheckMigration(_migrationStore);
+ }
+
+ ///
+ /// Database provider version.
+ ///
+ override public string Version
+ {
+ get { return m_database.getVersion(); }
+ }
+
+ ///
+ /// The name of this DB provider.
+ ///
+ override public string Name
+ {
+ get { return "PGSQL Asset storage engine"; }
+ }
+
+ #endregion
+
+ #region IAssetDataPlugin Members
+
+ ///
+ /// Fetch Asset from m_database
+ ///
+ /// the asset UUID
+ ///
+ override public AssetBase GetAsset(UUID assetID)
+ {
+ string sql = "SELECT * FROM assets WHERE id = :id";
+ using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
+ using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
+ {
+ cmd.Parameters.Add(m_database.CreateParameter("id", assetID));
+ conn.Open();
+ using (NpgsqlDataReader reader = cmd.ExecuteReader())
+ {
+ if (reader.Read())
+ {
+ AssetBase asset = new AssetBase(
+ DBGuid.FromDB(reader["id"]),
+ (string)reader["name"],
+ Convert.ToSByte(reader["assetType"]),
+ reader["creatorid"].ToString()
+ );
+ // Region Main
+ asset.Description = (string)reader["description"];
+ asset.Local = Convert.ToBoolean(reader["local"]);
+ asset.Temporary = Convert.ToBoolean(reader["temporary"]);
+ asset.Flags = (AssetFlags)(Convert.ToInt32(reader["asset_flags"]));
+ asset.Data = (byte[])reader["data"];
+ return asset;
+ }
+ return null; // throw new Exception("No rows to return");
+ }
+ }
+ }
+
+ ///
+ /// Create asset in m_database
+ ///
+ /// the asset
+ override public void StoreAsset(AssetBase asset)
+ {
+
+ string sql =
+ @"UPDATE assets set name = :name, description = :description, " + "\"assetType\" " + @" = :assetType,
+ local = :local, temporary = :temporary, creatorid = :creatorid, data = :data
+ WHERE id=:id;
+
+ INSERT INTO assets
+ (id, name, description, " + "\"assetType\" " + @", local,
+ temporary, create_time, access_time, creatorid, asset_flags, data)
+ Select :id, :name, :description, :assetType, :local,
+ :temporary, :create_time, :access_time, :creatorid, :asset_flags, :data
+ Where not EXISTS(SELECT * FROM assets WHERE id=:id)
+ ";
+
+ string assetName = asset.Name;
+ if (asset.Name.Length > 64)
+ {
+ assetName = asset.Name.Substring(0, 64);
+ m_log.WarnFormat(
+ "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
+ asset.Name, asset.ID, asset.Name.Length, assetName.Length);
+ }
+
+ string assetDescription = asset.Description;
+ if (asset.Description.Length > 64)
+ {
+ assetDescription = asset.Description.Substring(0, 64);
+ m_log.WarnFormat(
+ "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
+ asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
+ }
+
+ using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
+ using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))
+ {
+ int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
+ command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
+ command.Parameters.Add(m_database.CreateParameter("name", assetName));
+ command.Parameters.Add(m_database.CreateParameter("description", assetDescription));
+ command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
+ command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
+ command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
+ command.Parameters.Add(m_database.CreateParameter("access_time", now));
+ command.Parameters.Add(m_database.CreateParameter("create_time", now));
+ command.Parameters.Add(m_database.CreateParameter("asset_flags", (int)asset.Flags));
+ command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID));
+ command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
+ conn.Open();
+ try
+ {
+ command.ExecuteNonQuery();
+ }
+ catch(Exception e)
+ {
+ m_log.Error("[ASSET DB]: Error storing item :" + e.Message + " sql "+sql);
+ }
+ }
+ }
+
+
+// Commented out since currently unused - this probably should be called in GetAsset()
+// private void UpdateAccessTime(AssetBase asset)
+// {
+// using (AutoClosingSqlCommand cmd = m_database.Query("UPDATE assets SET access_time = :access_time WHERE id=:id"))
+// {
+// int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
+// cmd.Parameters.AddWithValue(":id", asset.FullID.ToString());
+// cmd.Parameters.AddWithValue(":access_time", now);
+// try
+// {
+// cmd.ExecuteNonQuery();
+// }
+// catch (Exception e)
+// {
+// m_log.Error(e.ToString());
+// }
+// }
+// }
+
+ ///
+ /// Check if asset exist in m_database
+ ///
+ ///
+ /// true if exist.
+ override public bool ExistsAsset(UUID uuid)
+ {
+ if (GetAsset(uuid) != null)
+ {
+ return true;
+ }
+ return false;
+ }
+
+ ///
+ /// Returns a list of AssetMetadata objects. The list is a subset of
+ /// the entire data set offset by containing
+ /// elements.
+ ///
+ /// The number of results to discard from the total data set.
+ /// The number of rows the returned list should contain.
+ /// A list of AssetMetadata objects.
+ public override List FetchAssetMetadataSet(int start, int count)
+ {
+ List retList = new List(count);
+ string sql = @" SELECT id, name, description, " + "\"assetType\"" + @", temporary, creatorid
+ FROM assets
+ order by id
+ limit :stop
+ offset :start;";
+
+ using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
+ using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
+ {
+ cmd.Parameters.Add(m_database.CreateParameter("start", start));
+ cmd.Parameters.Add(m_database.CreateParameter("stop", start + count - 1));
+ conn.Open();
+ using (NpgsqlDataReader reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ AssetMetadata metadata = new AssetMetadata();
+ metadata.FullID = DBGuid.FromDB(reader["id"]);
+ metadata.Name = (string)reader["name"];
+ metadata.Description = (string)reader["description"];
+ metadata.Type = Convert.ToSByte(reader["assetType"]);
+ metadata.Temporary = Convert.ToBoolean(reader["temporary"]);
+ metadata.CreatorID = (string)reader["creatorid"];
+ retList.Add(metadata);
+ }
+ }
+ }
+
+ return retList;
+ }
+
+ public override bool Delete(string id)
+ {
+ return false;
+ }
+ #endregion
+ }
+}
--
cgit v1.1