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/PGSQLManager.cs | 321 +++++++++++++++++++++++++++++++++++++ 1 file changed, 321 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLManager.cs (limited to 'OpenSim/Data/PGSQL/PGSQLManager.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLManager.cs b/OpenSim/Data/PGSQL/PGSQLManager.cs new file mode 100644 index 0000000..3ddaf38 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLManager.cs @@ -0,0 +1,321 @@ +/* + * 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.Collections.Generic; +using System.Data; +using System.IO; +using System.Reflection; +using log4net; +using OpenMetaverse; +using Npgsql; +using NpgsqlTypes; + +namespace OpenSim.Data.PGSQL +{ + /// + /// A management class for the MS SQL Storage Engine + /// + public class PGSQLManager + { +// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// Connection string for ADO.net + /// + private readonly string connectionString; + + /// + /// Initialize the manager and set the connectionstring + /// + /// + public PGSQLManager(string connection) + { + connectionString = connection; + } + + /// + /// Type conversion to a SQLDbType functions + /// + /// + /// + internal NpgsqlDbType DbtypeFromType(Type type) + { + if (type == typeof(string)) + { + return NpgsqlDbType.Varchar; + } + if (type == typeof(double)) + { + return NpgsqlDbType.Double; + } + if (type == typeof(Single)) + { + return NpgsqlDbType.Double; + } + if (type == typeof(int)) + { + return NpgsqlDbType.Integer; + } + if (type == typeof(bool)) + { + return NpgsqlDbType.Boolean; + } + if (type == typeof(UUID)) + { + return NpgsqlDbType.Uuid; + } + if (type == typeof(byte)) + { + return NpgsqlDbType.Smallint; + } + if (type == typeof(sbyte)) + { + return NpgsqlDbType.Integer; + } + if (type == typeof(Byte[])) + { + return NpgsqlDbType.Bytea; + } + if (type == typeof(uint) || type == typeof(ushort)) + { + return NpgsqlDbType.Integer; + } + if (type == typeof(ulong)) + { + return NpgsqlDbType.Bigint; + } + if (type == typeof(DateTime)) + { + return NpgsqlDbType.Timestamp; + } + + return NpgsqlDbType.Varchar; + } + + internal NpgsqlDbType DbtypeFromString(Type type, string PGFieldType) + { + if (PGFieldType == "") + { + return DbtypeFromType(type); + } + + if (PGFieldType == "character varying") + { + return NpgsqlDbType.Varchar; + } + if (PGFieldType == "double precision") + { + return NpgsqlDbType.Double; + } + if (PGFieldType == "integer") + { + return NpgsqlDbType.Integer; + } + if (PGFieldType == "smallint") + { + return NpgsqlDbType.Smallint; + } + if (PGFieldType == "boolean") + { + return NpgsqlDbType.Boolean; + } + if (PGFieldType == "uuid") + { + return NpgsqlDbType.Uuid; + } + if (PGFieldType == "bytea") + { + return NpgsqlDbType.Bytea; + } + + return DbtypeFromType(type); + } + + /// + /// Creates value for parameter. + /// + /// The value. + /// + private static object CreateParameterValue(object value) + { + Type valueType = value.GetType(); + + if (valueType == typeof(UUID)) //TODO check if this works + { + return ((UUID) value).Guid; + } + if (valueType == typeof(UUID)) + { + return ((UUID)value).Guid; + } + if (valueType == typeof(bool)) + { + return (bool)value; + } + if (valueType == typeof(Byte[])) + { + return value; + } + if (valueType == typeof(int)) + { + return value; + } + return value; + } + + /// + /// Create value for parameter based on PGSQL Schema + /// + /// + /// + /// + internal static object CreateParameterValue(object value, string PGFieldType) + { + if (PGFieldType == "uuid") + { + UUID uidout; + UUID.TryParse(value.ToString(), out uidout); + return uidout; + } + if (PGFieldType == "integer") + { + int intout; + int.TryParse(value.ToString(), out intout); + return intout; + } + if (PGFieldType == "boolean") + { + return (value.ToString() == "true"); + } + if (PGFieldType == "timestamp with time zone") + { + return (DateTime)value; + } + if (PGFieldType == "timestamp without time zone") + { + return (DateTime)value; + } + return CreateParameterValue(value); + } + + /// + /// Create a parameter for a command + /// + /// Name of the parameter. + /// parameter object. + /// + internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject) + { + return CreateParameter(parameterName, parameterObject, false); + } + + /// + /// Creates the parameter for a command. + /// + /// Name of the parameter. + /// parameter object. + /// if set to true parameter is a output parameter + /// + internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject, bool parameterOut) + { + //Tweak so we dont always have to add : sign + if (parameterName.StartsWith(":")) parameterName = parameterName.Replace(":",""); + + //HACK if object is null, it is turned into a string, there are no nullable type till now + if (parameterObject == null) parameterObject = ""; + + NpgsqlParameter parameter = new NpgsqlParameter(parameterName, DbtypeFromType(parameterObject.GetType())); + + if (parameterOut) + { + parameter.Direction = ParameterDirection.Output; + } + else + { + parameter.Direction = ParameterDirection.Input; + parameter.Value = CreateParameterValue(parameterObject); + } + + return parameter; + } + + /// + /// Create a parameter with PGSQL schema type + /// + /// + /// + /// + /// + internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject, string PGFieldType) + { + //Tweak so we dont always have to add : sign + if (parameterName.StartsWith(":")) parameterName = parameterName.Replace(":", ""); + + //HACK if object is null, it is turned into a string, there are no nullable type till now + if (parameterObject == null) parameterObject = ""; + + NpgsqlParameter parameter = new NpgsqlParameter(parameterName, DbtypeFromString(parameterObject.GetType(), PGFieldType)); + + parameter.Direction = ParameterDirection.Input; + parameter.Value = CreateParameterValue(parameterObject, PGFieldType); + + return parameter; + } + + /// + /// Checks if we need to do some migrations to the database + /// + /// migrationStore. + public void CheckMigration(string migrationStore) + { + using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) + { + connection.Open(); + Assembly assem = GetType().Assembly; + PGSQLMigration migration = new PGSQLMigration(connection, assem, migrationStore); + + migration.Update(); + } + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + Module module = GetType().Module; + // string dllName = module.Assembly.ManifestModule.Name; + Version dllVersion = module.Assembly.GetName().Version; + + return + string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, + dllVersion.Revision); + } + } +} -- cgit v1.1