Как я могу заставить SQLite возвращать правильный DateTime при выполнении ISQLQuery.UniqueResult<DateTime>()?

Question

TLDR:

Следующий код запускается в разных базах данных Oracle: select sysdate from dual SQLite select datetime('now')

При выполнении Session.CreateSQLQuery(cmd).UniqueResult<DateTime>() результатом является DateTime при работе с Oracle, но строка при работе с SQLite.

Это похоже на ошибку в драйвере SQLite и хак для проверки возвращаемого типа и выполнения DateTime.Parse(), если это строка. Я мог бы сделать это, но есть ли способы, чтобы NHibernate возвращал правильный тип?

Я пытаюсь получить текущее время базы данных из базы данных. Он работает нормально при использовании Oracle, но когда я пытаюсь сделать это с SQLite (в моих модульных тестах), он ломается, так как возвращаемая дата не DateTime, а строка.

Я видел решения, использующие пользовательский IUserType, но я не могу понять, как я должен использовать это в этом случае. Какие-либо предложения?

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Dialect.Function;

namespace My.Common.Types {

    public class MyNHibernateDialectException : Exception {
        public MyNHibernateDialectException(string message) : base(message) { }
    } 

    /// <summary>
    /// Define all custom functions here by name. It is important that when adding a new custom sql function, that function will work
    /// in all dialects supported.
    /// </summary>
    public static class MyDatabaseDialects {

        public enum Query {
            SysDate
        }

        /// <summary>
        ///  Dialect implementations will use this function to verify that they all implement the same functions.
        /// </summary>
        /// <param name="dialect"></param>
        public static void VerifyRegistrations(this NHibernate.Dialect.Dialect dialect) {
            // Verify that the required function are there
            foreach (var func in Enum.GetValues(typeof(Function))) {
                var enumName = func.ToString();
                if (!dialect.Functions.ContainsKey(enumName)) {
                    throw new MyNHibernateDialectException(
                        string.Format("The custom function '{0}' is not defined. Did you forget it in factory '{1}'?", enumName, dialect));
                }
            }
        }

    }

    /// <summary>
    /// An interface to reveal more advanced functionality that is database specific
    /// </summary>
    public interface IDialectExtensions {

        /// <summary>
        /// Fetch a query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query);

        /// <summary>
        /// Fetch a parameterized query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams);
    }

    /// <summary>
    /// Class to store database specific objects except functions (which are supported by NHibernate).
    /// </summary>
    class DialectExtension {

        private readonly Dictionary<MyDatabaseDialects.Query, string> queryDictionary = new Dictionary<MyDatabaseDialects.Query, string>();

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return this.GetQuery(session, query, null);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            var cmd = (queryParams == null) ? queryDictionary[query] : string.Format(queryDictionary[query], queryParams);
            return session.Session.CreateSQLQuery(cmd);
        }

        public void RegisterQuery(MyDatabaseDialects.Query query, string hqlString) {
            queryDictionary.Add(query, hqlString);
        }

        public void VerifyQueryRegistrations() {

            foreach (var query in Enum.GetValues(typeof(MyDatabaseDialects.Query))) {
                if (!queryDictionary.ContainsKey((MyDatabaseDialects.Query)query)) {
                    throw new MyNHibernateDialectException(string.Format("The custom query '{0}' is not defined.", query.ToString()));
                }
            }
        }
    }

    public class MyOracle10gDialect : NHibernate.Dialect.Oracle10gDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MyOracle10gDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select sysdate from dual");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions

        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }
    }

    public class MySqliteDialect : NHibernate.Dialect.SQLiteDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MySqliteDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select datetime('now')");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }

    }

}

И я использую код выше, как это:

/// <summary>
/// Fetches a DialectExtensions object allowing us to have more advanced functionality that is database specific
/// </summary>
public static IDialectExtensions GetDialectExtensions(this IOperationContext operationContext) {
    return Session.GetSessionImplementation().Factory.Dialect as IDialectExtensions;
}

/// <summary>
/// Get the database time by executing a raw SQL statement.
/// </summary>
public static DateTime? GetDatabaseTime() {
    DateTime? result = null;
    try {
        result = GetDialectExtensions()
            .GetQuery(Session, MyDatabaseDialects.Query.SysDate)
            .UniqueResult<DateTime>();
    } catch {
        // SQLite will throw exception here as the result is returned as a string instead of a DateTime
    }
    return result;
}

Answer 1

При выполнении Session.CreateSQLQuery(cmd).UniqueResult() результатом является DateTime при работе с Oracle, но строка при работе с SQLite.

Похоже, вам, возможно, потребуется дополнительно изменить спящий диалект для SQLite, чтобы он возвращал те же результаты. Убедитесь, что у вас установлены типы карт столбцов по умолчанию для SQLite, чтобы соответствовать возвращаемому Oracle. Я сопоставляю столбцы метки времени с датой и временем. Вот мой SQLiteDialect, имейте в виду, что это Java-версия, но она все равно должна быть похожей:

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        super();

        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "longvarchar");
        registerColumnType(Types.TIMESTAMP, "datetime");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");
        registerColumnType(Types.NULL, "null");
        registerHibernateType(Types.NULL, "null");

        registerFunction("concat", new VarArgsSQLFunction(IntegerType.INSTANCE,
            "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(IntegerType.INSTANCE,
            "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));
        registerFunction("substring", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));

        registerFunction("replace", new StandardSQLFunction("replace",
            IntegerType.INSTANCE));

        registerFunction("current_time", new NoArgSQLFunction(
            "datetime('now')",
            CalendarType.INSTANCE, false));
        registerFunction("current_timestamp", new NoArgSQLFunction(
            "strftime('%s','now')*1000", CalendarType.INSTANCE, false));
        registerFunction("current_date", new NoArgSQLFunction(
            "strftime('%s','now')*1000",
            CalendarType.INSTANCE, false));

        registerFunction(
            "trunc",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d-%m-%Y\", ?1/1000, 'unixepoch', 'localtime') as string)"));
        registerFunction(
            "second",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%S\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "minute",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%M\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "hour",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%H\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "day",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "month",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%m\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "year",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%Y\", ?1/1000, 'unixepoch', 'localtime') as integer)"));

        registerFunction("trim", new AbstractAnsiTrimEmulationFunction() {

            protected SQLFunction resolveBothSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1)");
            }

            protected SQLFunction resolveBothSpaceTrimFromFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?2)");
            }

            protected SQLFunction resolveLeadingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1)");
            }

            protected SQLFunction resolveTrailingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1)");
            }

            protected SQLFunction resolveBothTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1, ?2)");
            }

            protected SQLFunction resolveLeadingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1, ?2)");
            }

            protected SQLFunction resolveTrailingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1, ?2)");
            }
        });
    }

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    /*
     * public boolean supportsInsertSelectIdentity() { return true; // As
     * specify in NHibernate dialect }
     */

    @Override
    public boolean hasDataTypeInIdentityColumn() {
        return false; // As specify in NHibernate dialect
    }

    /*
     * public String appendIdentitySelectToInsert(String insertString) { return
     * new StringBuffer(insertString.length()+30). // As specify in NHibernate
     * dialect append(insertString).
     * append("; ").append(getIdentitySelectString()). toString(); }
     */

    @Override
    public String getIdentityColumnString() {
        // return "integer primary key autoincrement";
        return "integer";
    }

    @Override
    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    @Override
    public boolean supportsLimit() {
        return true;
    }

    // Added fix from bug,
    // http://code.google.com/p/hibernate-sqlite/issues/detail?id=1
    @Override
    public boolean bindLimitParametersInReverseOrder() {
        return true;
    }

    @Override
    public String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query)
            .append(hasOffset ? " limit ? offset ?" : " limit ?").toString();
    }

    @Override
    public String getLimitString(final String s, final int min, final int max) {
        String limitString = super.getLimitString(s, min, max);

        return limitString;
    }

    @Override
    public boolean supportsTemporaryTables() {
        return true;
    }

    @Override
    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    @Override
    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    @Override
    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    @Override
    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    @Override
    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    @Override
    public boolean supportsUnionAll() {
        return true;
    }

    @Override
    public boolean hasAlterTable() {
        return false; // As specify in NHibernate dialect
    }

    @Override
    public boolean dropConstraints() {
        return false;
    }

    @Override
    public String getAddColumnString() {
        return "add column";
    }

    @Override
    public String getForUpdateString() {
        return "";
    }

    @Override
    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    @Override
    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException(
            "No drop foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String
                    getAddForeignKeyConstraintString(String constraintName,
                                                     String[] foreignKey,
                                                     String referencedTable,
                                                     String[] primaryKey,
                                                     boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException(
            "No add foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException(
            "No add primary key syntax supported by SQLiteDialect");
    }

    @Override
    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    @Override
    public boolean supportsIfExistsAfterTableName() {
        return false;
    }

    @Override
    public boolean supportsCascadeDelete() {
        return false;
    }
}

Убедитесь, что вы установили свойство 'hibernate.dialect' перед созданием фабрики сеанса:

private SessionFactory buildSessionFactory() throws Exception {
    StandardServiceRegistryBuilder serviceRegistryBuilder =
        new StandardServiceRegistryBuilder();

    org.hibernate.cfg.Configuration config = new Configuration();
    config.setProperty("hibernate.dialect", dialect);
    //Any additional config options, or just set this in your hibernate xml config...


    serviceRegistryBuilder.applySettings(config.getProperties());

    ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();

    sessionFactory = config.buildSessionFactory(serviceRegistry);

    return sessionFactory;
}

Вот уменьшенная версия, которую я нашел для.NET

Это должно решить вашу проблему. Если это не так, есть другие обходные пути, которые вы можете использовать, устанавливая временные типы для ваших настроек отображения ORM:

[Временная (TemporalType.TIMESTAMP)]

Очевидно, TemporalTypes упоминаются как CustomTypes в csharp. В одном из ответов на этот вопрос приведен пример.

Кроме того, вам, возможно, придется учитывать часовые пояса.

Answer 2

Но почему база данных возвращает строку? Какой тип столбца? И ваш запрос HQL или SQL? Если это HQL, вы можете выполнить приведение:

select cast(Property as DateTime) from Entity

Если вы хотите сделать это на стороне клиента, почему бы и нет:

var value = Convert.ToDateTime(query.UniqueResult());