Статьи

PostgreSQL для SQLite: путешествие

Эта статья будет полезна, если вы хотите поддерживать как PostgreSQL, так и SQLite, используя JDBC. Это будет особенно полезно, если вы:


  • Уже обращаетесь к значениям из вашей базы данных (PostgreSQL), используя обычный интерфейс JDBC ResultSet, например:
    Date d = rs.getDate("date_field");
    BigDecimal bd = rs.getBigDecimal("bigdecimal_field");

    И это создает проблемы, когда вы делаете то же самое для SQLite, но вы не хотите изменять этот код.
  • Уже получают автоматически сгенерированные ключи в PostgreSQL с предложением RETURNING , но это не будет работать в SQLite. Вы хотите унифицированное решение, которое работает для обеих баз данных.
  • Задуманные внешние ключи применяются в SQLite по умолчанию (как в PostgreSQL) и разрушаются стеной. SQLite позволяет вам удалять записи из ваших таблиц, даже если на них есть ссылки в другой таблице, и вы явно сообщили об этом SQLite с помощью предложения  REFERENCES table_name (field_name) .
  • Возникают проблемы с различиями в диалектах PostgreSQL и SQLite (в основном в отношении типов данных), например, при создании фильтров запросов с логическими значениями.
  • У вас был свой собственный способ управления исключениями для PostgreSQL, и он не работает для SQLite (очевидно). Вы хотите, чтобы SQLite вписывался в модель, которая у вас уже есть.
  • Другие вещи могут появиться, если вы будете в курсе …

Несколько месяцев назад я хотел перенести приложение на SQLite в качестве бэкэнда данных. На самом деле, я хотел, чтобы он работал с PostgreSQL и SQLite нечетко (но не одновременно). Я хотел легко переключаться между этими двумя базами данных, не меняя код. Я сделал это, но по пути мне пришлось решать некоторые проблемы, которые могут быть интересны многим другим людям.

Многие решения, которые я нашел, были распространены по сети, но не было ни одного места, которое объясняло бы, как полностью достичь того, чего я хотел. Итак, цель этого поста — попытаться объединить мои знания в одну статью, которая может быть полезна другим в качестве (полу) полного руководства. Это руководство может быть полезно не только тем, кто создает свои собственные фреймворки, но и всем, кто их не использует и хочет попробовать некоторые хитрости и приемы, чтобы заставить их приложение работать.

НАЧАЛО

Между PostgreSQL и SQLite существует множество несовместимостей между базами данных, особенно в отношении типов данных. Если вы хотите, чтобы один и тот же код работал для обеих баз данных, вам лучше использовать среду, которая управляет этим для вас. Но вот в чем дело:  используемая мной среда   создается мной и не учитывает (полностью) эти различия, поскольку я в основном использую PostgreSQL в качестве базы данных; вот как и почему у меня возникли проблемы. 

Моя структура передает много вещей, но я сосредоточен здесь на части доступа к данным. Он использует драйвер JDBC для подключения к базам данных, но предоставляет более абстрактные способы сделать это; это в значительной степени часть доступа к данным в рамках.

Базовый класс DAO для моей платформы будет выглядеть так:

public class MyDAO extends BaseDAO {
    public MyDAO() {
        super("context_alias", new DefaultDataMappingStrategy() {
            @Override
            public Object createResultObject(ResultSet rs) throws SQLException {
                MyModel model = (MyModel)ObjectsFactory.getObject("my_model_alias");
               
                model.setStringField(rs.getString("string_field"));
                model.setIntegerField(rs.getInt("integer_field"));
                model.setBigDecimalField(rs.getBigDecimal("bigdecimal_field"));
                model.setDateField(rs.getDate("date_field"));
                model.setBooleanField(rs.getBoolean("boolean_field"));
               
                return model;
            }
        });
    }

    @Override
    public String getTableName() {
        return "table_name";
    }

    @Override
    public String getKeyFields() {
        return "string_field|integer_field";
    }
   
    @Override
    protected Map getInsertionMap(Object obj) {
        Map map = new HashMap();
        MyModel model = (MyModel) obj;
        map.put("string_field", model.getStringField());
        map.put("integer_field", model.getIntegerField());
        map.put("bigdecimal_field", model.getBigDecimalField());
        map.put("date_field", model.getDateField());
        map.put("boolean_field", model.getBooleanField());
        return map;
    }
   
    @Override
    protected Map getUpdateMap(Object obj) {
        Map map = new HashMap();
        MyModel model = (MyModel) obj;
        map.put("bigdecimal_field", model.getBigDecimalField());
        map.put("date_field", model.getDateField());
        map.put("boolean_field", model.getBooleanField());
        return map;
    }

    @Override
    public String getFindAllStatement() {
        return "SELECT * FROM :@ ";
    }

То, что я хотел переключаться между базами данных без изменения кода, означает, что я хотел переключаться без изменения моих классов DAO.

Для SQLite я использовал  драйвер xerial-jdbc-sqlite  . Я говорю о драйверах, потому что есть некоторые вещи, которые могут зависеть от драйверов при решении некоторых проблем; поэтому, когда я говорю «SQlite делает это так», я обычно имею в виду «драйвер xerial-jdbc-sqlite делает это так».

Теперь начнем.

ВНИМАНИЕ: Некоторые решения, которые я здесь привожу, вписываются в мою среду, но могут не вписываться в ваш код. Вам решать, как адаптировать то, что я здесь предоставляю.

ТИПЫ ДАННЫХ

Поскольку между PostgreSQL и SQLite существуют некоторые различия в отношении типов данных, и я хотел продолжать получать доступ к значениям базы данных через обычный интерфейс ResultSet, мне нужно было иметь некоторый механизм для перехвата вызова, например, resultset.getDate («date_field») ) . Поэтому я создал класс ResultSetWrapper, который переопределит методы, которые меня интересуют, например, так:

public class ResultSetWrapper implements ResultSet {
    
    // The wrappped ResultSet
    ResultSet wrapped;
   
    /* I will use this DateFormat to format dates. I'm assuming an SQLite style pattern. I should not */
    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

    public ResultSetWrapper(ResultSet wrapped) {
        this.wrapped = wrapped;
    }

    /* Lots of ResultSet methods implementations go here, 
       but this is an example of redefining a method 
       I'm interested in changing its behavior: */

    public Date getDate(String columnLabel) throws SQLException {
        Object value = this.wrapped.getObject(columnLabel);
        return (Date)TypesInferreer.inferDate(value);
    }	
}

Метод getDate () в ResultSetWrapper использует TypesInferreer для преобразования полученного значения в значение Date.

Все преобразования типов данных будут инкапсулированы внутри TypesInferreer, который будет иметь методы для преобразования из различных типов данных по мере необходимости. Например, у него будет такой метод:

public static Object inferDate(Object value) {
    java.util.Date date;
   
    // Do convertions here (convert value and asign to date)

    return date;
}

Который пытается преобразовать любое значение в дату (я покажу фактическую реализацию далее).

Теперь вместо того, чтобы использовать исходный набор результатов, извлеченный из выражения  prepareStatement.executeQuery () , вы используете  новый ResultSetWrapper (prepareStatement.executeQuery ()) . Вот что делает мой фреймворк: он передает этот новый набор результатов объектам DAO.

Теперь давайте посмотрим некоторые преобразования типов.

Смешивание PostgreSQL Date и SQLite Long / String

Вы можете хранить значения Date в виде текста в базе данных SQLite (например, «2013-10-09»); это можно сделать вручную при создании базы данных, но когда SQLite сохраняет объект Date, по умолчанию он преобразует его в значение Long. Это не проблема при сохранении значения в базе данных SQLite, но если вы попытаетесь получить его с помощью resultset.getDate («date_field»), то все будет запутано; Это просто не будет работать (CastException).

Как вы получаете доступ к значениям даты, тогда? Этот метод создается в TypesInfereer, который охватывает варианты как String, так и Long:

public static Object inferDate(Object value) {
        java.util.Date date = null;
        if(value == null) return null;
        if(value instanceof String) {
            try {
                date = df.parse((String)value);
            } catch (ParseException ex) {
               // Deal with ex
            }
        } else if(value instanceof Long) {
            date = new java.util.Date((Long)value);
        } else {
            date = (Date)value;
        }
        return new Date(date.getTime());
    }

И, как вы видели, функция getDate () в ResultSetWrapper переопределяется следующим образом:

@Override
public Date getDate(String columnLabel) throws SQLException {
    Object value = this.wrapped.getObject(columnLabel);
    return (Date)TypesInferreer.inferDate(value);
}

Теперь все DAO могут получать значения Date из обеих баз данных нечетко, используя resultset.getDate («date_field»).

Смешивание чисел PostgreSQL и SQLite Integer / Double / …

Мой драйвер SQLite не реализовал функцию getBigDecimal (). Он жаловался так, когда я его назвал: java.sql.SQLException: не реализовано драйвером SQLite JDBC .

Поэтому мне пришлось придумать решение, которое было бы справедливо как для PostgreSQL, так и для SQlite. Вот что я сделал в ResultSetWrapper:

@Override
public BigDecimal getBigDecimal(String columnLabel) throws SQLException {
    Object value = this.wrapped.getObject(columnLabel);
    return (BigDecimal)TypesInferreer.inferBigDecimal(value);
}

Но значение получит разные типы в зависимости от фактического значения, хранящегося в базе данных; это может быть целое число, двойное число или что-то еще. Я решил все случаи, выполнив это в TypesInfereer:

public static Object inferBigDecimal(Object value) {
    if(value == null) return null;
    if(value instanceof BigDecimal == false) {
        return new BigDecimal(String.valueOf(value));
    }
    return value;
}

В любом случае, рекомендуется использовать String конструктор BigDecimal, так что с этим все в порядке. Теперь вы можете извлечь значения BigDecimal с помощью resultset.getBigDecimal («bigdecimal_field») из обеих баз данных.

Смешивание PostgreSQL Boolean и SQLite Integer

SQLite не имеет логических значений. Вместо этого он интерпретирует любое другое значение как логическое, следуя некоторым правилам. Когда SQLite сохраняет логическое значение в базе данных, он сохраняет его как 0 или 1 для false или true соответственно. Кроме того, поскольку драйверы могут интерпретировать любое значение как логическое, вы можете использовать resultset.getBoolean («boolean_field»), и оно будет работать в соответствии с правилами.

Но проблема, с которой я столкнулся, была при создании фильтров. Если значение true хранится как 1 в базе данных SQLite, вы не можете ожидать, что предложение WHERE boolean_field = true будет работать. Вы никогда не найдете совпадения. Вместо этого вы должны были сказать ГДЕ boolean_field = 1 .

В моем приложении я создал фильтры следующим образом:

dao.addFilter(new FilterSimple("boolean_field", true));

Теперь мне нужно было FilterSimple, чтобы сделать вывод, что для SQLite я имел в виду 1 вместо true. Поэтому я создал то, что я назвал DatasourceVariation. Это объекты, которые являются специфическими для каждого типа базы данных и используются во всех доступах к данным DAO, фильтрами и другими объектами. Эти объекты позаботятся об управлении всеми моими несовместимостями между базами данных, включая:

  • Способ ссылки на объект базы данных: в PostgreSQL вы должны добавлять имя схемы к каждому объекту базы данных, на который вы ссылаетесь в своих запросах. В SQLite нет.
  • Способ управления исключениями: объяснено далее в этом посте.
  • Способ резервного копирования и восстановления данных: объяснено далее в этом посте.
  • Выражение между предложениями: объяснено далее в этом посте.
  • А также, выводя логические значения.

Для VariationSQLite я сделал это:

@Override
public Object getReplaceValue(Object value) {
    if(value instanceof Boolean) {
        if((Boolean)value == true) return new Integer(1);
        else return new Integer(0);
    }  
    return value;
}

Теперь мы можем сказать dao.addFilter (новый FilterSimple («boolean_field», true)) для обеих баз данных, предполагая, что FilterSimple использует вариацию для адаптации значения перед построением предложения.

RETRIEVING AUTOGENERATED KEYS

When you have autonumeric fields (eg. Serial), in PostgreSQL you can specify a RETURNING clause at the end of an INSERT statement to automatically retrieve the values of autogenerated fields by doing this:

PreparedStatement pstm = conn.prepareStatement(queryWithReturningClause); // ex. select * from table_x returning field_x
ResultSet rs = statement.executeQuery();
if(rs.next()) {
    // Get autogenerated fields from rs
}

But that won’t work with SQLite. In SQLite, retrieving autogenerated fields conveys a process that goes from creating the statement, executing the query and explicitly asking for the generated values. Like this:

PreparedStatement pstm = conn.prepareStatement(queryWITHOUTreturningClause, Statement.RETURN_GENERATED_KEYS); 
pstm.executeUpdate();  
ResultSet rs = pstm.getGeneratedKeys(); 
if (rs != null && rs.next()) {
    // Get autogenerated fields from rs
}

The good news is that this code works both for PostgreSQL and SQLite, so I replaced my previous code for this, and didn’t have to make any distinction between databases.

ENFORCING FOREIGN KEYS

You’d think that using a REFERENCES table_name(field_name) clause when creating a SQLite database table makes foreign keys to be checked when deleting, updating, etc. You’re wrong!

Foreign keys are not enforced in SQLite by default. You have to explicitly say it, and it’s done when creating the connection (WARNING: This is very driver-specific):

SQLiteConfig config = new SQLiteConfig();
config.enforceForeignKeys(true);
Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dataSourcePath, config.toProperties());

For PostgreSQL it’s different, so you better have a connection pool for each type of database, and decide which one to use at runtime. My framework does exactly that.

NOTE: If you are capable of getting the connection depending on the database type, then you can enforce foreign keys transparently for both databases (for PostgreSQL it happens naturally without extra code). For instance, you could have an abstract getConnection() method, and each database’s connection pool would return the connection in its own way.

MANAGING EXCEPTIONS

I had defined some different types of database exceptions in my framework: ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere, etc, which would be thrown and raised to upper layers in my architecture. For PostgreSQL, these exceptions directly mapped to some constant codes (which normally are vendor/driver specific): UNIQUE_VIOLATION = «23505», FOREIGN_KEY_VIOLATION = «23503», etc. So, for PostgreSQL, I managed database exceptions something like this:

@Override
public void manageException(SQLException ex) throws ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere {
        if (ex.getSQLState() == null) {
            ex = (SQLException) ex.getCause();
        }
        if (ex.getSQLState().equals(UNIQUE_VIOLATION)) {
            throw new ExceptionDBDuplicateEntry();
        } else if(ex.getSQLState().equals(FOREIGN_KEY_VIOLATION)) {
            throw new ExceptionDBEntryReferencedElsewhere();
        } else {
            DAOPackage.log(ex);
            throw new ExceptionDBUnknownError(ex);
        }
}

That won’t work for SQLite, obviously! So, what I did was move the database exceptions management to the DataSourceVariation. The VariationPostgresql class would have a method similar to the one above. For VarialtionSQLite, I did sort of a hack, but it’s something that has worked until now (maybe until I change my driver).

@Override
public void manageException(SQLException ex) throws ExceptionDBDuplicateEntry, ExceptionDBEntryReferencedElsewhere {
        // This is a hack (is it???)
        String message = ex.getMessage().toLowerCase();
        if(message.contains("sqlite_constraint")) {
            if(message.contains("is not unique")) throw new ExceptionDBDuplicateEntry();
            else if(message.contains("foreign key constraint failed")) throw new ExceptionDBEntryReferencedElsewhere();
            else {
                DAOPackage.log(ex);
                throw new ExceptionDBUnknownError(ex);
            }
        } else {
            DAOPackage.log(ex);
            throw new ExceptionDBUnknownError(ex);
        }
}

UpdateThis technique might have some flaws. But hey, can you find a better approach right away?

FIXING BETWEEN CLAUSE

The problem with the BETWEEN clause appeared while using a filter like this: 

dao.addFilter(new FilterBetween("date_field", date1, date2)); // date1 and date2 are java.util.Date objects

FilterBetween would create a BETWEEN clause by formatting Dates as Strings, normally with the format ‘yyyy-MM-dd’ (although this should be configurable). Since dates in SQLite are long values, we can’t create a clause like date_field BETWEEN ‘2013-01-01’ AND ‘2013-02-01’. It had to be something like date_field >=1357016400000 AND date_field <= 1359694800000.

So, I moved the creation of BETWEEN clauses to…. that’s right, to DataSourceVariation. VariationSQLite does it like this:

@Override
public String getBetweenExpression(String fieldName, Object d1, Object d2) {
    String filter = "";
    try {
        Date dd1 = null;
        Date dd2 = null;
           
        SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd"); // Remember, this should be configurable
        if(d1 instanceof String) dd1 = df.parse((String)d1); else dd1 = (Date)d1;
        if(d2 instanceof String)dd2 = df.parse((String)d2); else dd2 = (Date)d2;
           
        filter = fieldName + " >= " + dd1.getTime() + " AND " + fieldName + " <= " + dd2.getTime();
    } catch (ParseException ex) {
        DAOPackage.log(ex);
        throw new ExceptionDBUnknownError(ex);
    }        
    return filter;
}

CONCLUSIONS

As you can see, there are many intricacies when making an app support multiple database types. All I did here was only to support PostgreSQL and SQLite, but who knows what is needed to support other databases at the same time too. You can’t expect JDBC alone will do all the work, so be prepared to solve some problems (and another problem, and another, …) to make a database migration. And please, share your journey.