22.11.2024 в 12:49

Миграция SQL между базами данных

Приложения, работающие с разными базами данных, сталкиваются с проблемой несовместимости SQL-запросов, несмотря на общую схожесть синтаксиса. Ручная адаптация запросов трудоемка и подвержена ошибкам.

Полная автоматизация перевода SQL невозможна из-за различий в функциональности. Однако, основная проблема — разный синтаксис SQL-функций, особенно для работы с датами и строками. Отсутствие стандарта приводит к тому, что каждая СУБД реализует эти функции по-своему (например, преобразование строки «2020-02-05» в дату).

ORACLE:

select TO_DATE('2020-02-05', 'YYYY-MM-DD') from USER

SQL-сервер:

select CONVERT(varchar(100), '2020-02-05', 23) from USER

MySQL:

select DATE_FORMAT('2020-02-05','%Y-%m-%d') from USER

Для обеспечения совместимости с разными базами данных, SQL-запросы приходится переписывать при смене СУБД.

Решение SPL решает эту проблему, предлагая механизм автоматического преобразования стандартного SQL в запросы, совместимые с конкретными базами данных. Это упрощает миграцию и обеспечивает бесшовный переход между различными СУБД.

Функция sql.sqltranslate(dbtype) переводит SQL-запрос. sql — это исходный запрос, а dbtype — тип целевой базы данных. Функция поддерживает только определённые SQL-функции (список см. в справочнике esProc). Неподдерживаемые функции не будут преобразованы.

Миграция SQL в среде IDE SPL

Теперь рассмотрим миграцию SQL в среде IDE SPL. Преобразуем запрос SELECT EID, NAME, BIRTHDAY, ADDDAYS(BIRTHDAY,10) DAY10 FROM EMP в синтаксис, совместимый с другими базами данных.

Функция ADDDAYS успешно преобразована для разных баз данных, демонстрируя успешную межбазовую миграцию SQL.

Дополнительные примеры

Пример 1: Например, добавление 10 к значению месяца

SELECT EID, NAME, BIRTHDAY, ADDMONTHS(BIRTHDAY,10) DAY10 FROM EMP

Переведите этот оператор SQL в синтаксис для разных баз данных:

ORACLE:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+NUMTOYMINTERVAL(10,'MONTH') DAY10 FROM EMP

SQLSVR:

SELECT EID, NAME, BIRTHDAY, DATEADD(MM,10,BIRTHDAY) DAY10 FROM EMP

DB2:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+10 MONTHS DAY10 FROM EMP

MySQL:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+INTERVAL 10 MONTH DAY10 FROM EMP

POSTGRES:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+interval '10 months' DAY10 FROM EMP

Функция ADDMONTHS реализована по-разному в разных СУБД. В SQL Server используется DATEADD, в MySQL и PostgreSQL — прямое сложение, а Oracle комбинирует оба подхода.

Пример 2: Получить QUARTER

SELECT EID,AREA,QUARTER(ORDERDATE) QUA, AMOUNT FROM ORDERS

Преображение выражения в:

ORACLE:

SELECT EID,AREA,FLOOR((EXTRACT(MONTH FROM ORDERDATE)+2)/3) QUA, AMOUNT FROM ORDERS

SQLSVR:

SELECT EID,AREA,DATEPART(QQ,ORDERDATE) QUA, AMOUNT FROM ORDERS

POSTGRES:

SELECT EID,AREA,EXTRACT(QUARTER FROM ORDERDATE) QUA, AMOUNT FROM ORDERS

TERADATA:

SELECT EID,AREA,TD_QUARTER_OF_YEAR(ORDERDATE) QUA, AMOUNT FROM ORDERS

Функция QUARTER реализована в разных СУБД с вариациями в именовании и параметрах.

Пример 3: Преобразование типов

SELECT EID, NAME, DATETOCHAR(BIRTHDAY) FROM EMP

Перевод утверждение в:

ORACLE:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

SQLSVR:

SELECT EID, NAME, CONVERT(CHAR,BIRTHDAY,120) FROM EMP

DB2:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

MySQL:

SELECT EID, NAME, DATE_FORMAT(BIRTHDAY, '%Y-%m-%d %H:%i:%S) FROM EMP

POSTGRES:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

TERADATA:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

Функции преобразования типов также имеют несоответствия в именовании и синтаксисе между различными СУБД. Функция SPL sqltranslate() решает эту проблему.

Определение и расширение функций

Список поддерживаемых СУБД и функций находится в файле /com/scudata/dm/sql/function.xml внутри esproc-bin.jar.

<?xml version="1.0" encoding="utf-8"?>
<STANDARD>
    <FUNCTIONS type="FixParam">
        <FUNCTION name="ADDDAYS" paramcount="2" value="">
            <INFO dbtype="ORACLE" value="?1+NUMTODSINTERVAL(?2,'DAY')"></INFO>
            <INFO dbtype="SQLSVR" value="DATEADD(DD,?2,?1)"></INFO>
            <INFO dbtype="DB2" value="?1+?2 DAYS"></INFO>
            <INFO dbtype="MYSQL" value="?1+INTERVAL ?2 DAY"></INFO>
            <INFO dbtype="HSQL" value="DATEADD('dd', ?2, ?1)"></INFO>
            <INFO dbtype="TERADATA" value="?1+CAST(?2 AS INTERVAL DAY)"></INFO>
            <INFO dbtype="POSTGRES" value="?1+interval '?2 days'"></INFO>
            <INFO dbtype="ESPROC" value="elapse(?1,?2)"></INFO>
        </FUNCTION>
    </FUNCTIONS>
</STANDARD>

XML-файл function.xml описывает соответствие SQL-функций для разных СУБД. Раздел FUNCTIONS группирует функции по типу (type), например, FixParam для функций с фиксированным числом параметров. Каждый элемент FUNCTION определяет функцию (name, paramcount), а value — её значение по умолчанию (пустое значение означает отсутствие преобразования). Раздел INFO задаёт преобразование функции для каждой СУБД (dbtype, value), используя ? или ?n как плейсхолдеры для параметров. Если для данной СУБД в FUNCTION отсутствует INFO, функция не преобразуется. function.xml содержит не все функции; новые функции можно добавлять вручную, как показано на примере добавления функции DATEDIFF для вычисления разницы между датами.

<FUNCTION name="DATEDIFF" paramcount="2" value="">
    <INFO dbtype="ORACLE" value="?1-?2"></INFO>
    <INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"></INFO>
    <INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"></INFO>
    <INFO dbtype="POSTGRES" value="?1-?2"></INFO>
    <INFO dbtype="ESPROC" value="interval(?2,?1)"></INFO>
</FUNCTION>

Поддержка новых СУБД добавляется путём создания новых узлов INFO с соответствующими настройками. Например, для добавления поддержки SQLite и реализации перевода функции вычисления разницы дат, достаточно добавить необходимые данные в узел INFO.

<FUNCTION name="DATEDIFF" paramcount="2" value="">
    <INFO dbtype="ORACLE" value="?1-?2"></INFO>
    <INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"></INFO>
    <INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"></INFO>
    <INFO dbtype="POSTGRES" value="?1-?2"></INFO>
    <INFO dbtype="ESPROC" value="interval(?2,?1)"></INFO>
    <INFO dbtype="SQLite" value="JULIANDAY(?1) - JULIANDAY(?2)"></INFO>
</FUNCTION>

Фиксированное число параметров

В отличие от функций с фиксированным числом параметров, некоторые функции, такие как конкатенация строк, CASE WHEN или поиск первого ненулевого значения, требуют динамического количества аргументов. SPL поддерживает это, используя тип AnyParam в узле FUNCTIONS.

<FUNCTIONS type="AnyParam">
    <FUNCTION classname="com.scudata.dm.sql.simple.Case" name="case">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Case"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Coalesce" name="coalesce">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Coalesce"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Concat" name="concat">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Concat"></INFO>
    </FUNCTION>
</FUNCTIONS>

Необходимо создать классы Java для определенных функций в каждой базе данных. Например, чтобы добавить поддержку строковой функции Oracle CONCAT, мы можем написать следующий код:

public class Concat implements IFunction
{
    public String getFormula(String[] params)
    {
        StringBuffer sb = new StringBuffer();
        for(int i = 0, len = params.length; i < len; i++) {
            if(params[i].isEmpty()) {
                throw new RQException("Concat function parameter cannot be empty");
            }
            if(i > 0) {
                sb.append(" || ");
            }
            sb.append(params[i]);
        }
        return sb.toString();
    }
}

Необходимо скомпилировать этот фрагмент кода и поместить его внутри пути /com/scudata/dm/sql/oracle внутри esproc-bin.jar.

Затем настройте класс трансляции, соответствующий Oracle в function.xml.

<FUNCTIONS type="AnyParam">
    <FUNCTION classname="com.scudata.dm.sql.simple.Case" name="case">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Case"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Coalesce" name="coalesce">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Coalesce"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Concat" name="concat">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Concat"></INFO>
        <INFO dbtype="ORACLE" classname="com.scudata.dm.sql.oracle.Concat"></INFO>**
    </FUNCTION>
</FUNCTIONS>

Перезапустите IDE после изменения jar-файла.

Функция sqltranslate() демонстрирует различия в синтаксисе между СУБД: например, конкатенация трёх параметров в Oracle использует ||, а в esProc — -. Мы рассмотрели настройку sqltranslate(), добавление функций и СУБД, включая функции с динамическим числом параметров.

Интеграция с приложениями

Интеграция SPL проста: добавить esproc-bin-xxxx.jar, icu4j-60.3.jar в [installation directory]\esProc\lib и скопировать raqsoftConfig.xml из [installation directory]\esProc\config в classpath приложения. raqsoftConfig.xml — обязательный файл конфигурации.

Работа с одной базой данных

Использование 1 — Использовать только трансляцию SQL

Простейший способ использования — трансляция SQL-запросов с помощью sqltranslate() и последующее выполнение. Функция com.scudata.dm.sql.SQLUtil.translate обеспечивает эту трансляцию.

String sql = "select name, birthday, adddays(birthday,10) day10 from emp";
sql = com.scudata.dm.sql.SQLUtil.translate(sql, "MYSQL");

Хотя SPL рекомендует использовать JDBC, прямое использование API практичнее для простых преобразований SQL-запросов. Для обеспечения прозрачности миграции между базами данных, тип базы данных хранится в конфигурационном файле (например, dbconfig.properties), исключая необходимость переписывания или перекомпиляции кода при смене СУБД. Пример файла dbconfig.properties для MySQL приведён ниже.

database.type=MYSQL

Затем мы можем инкапсулировать метод перевода, который вызывает API SPL для реализации перевода SQL.

public static String translateSQL(String sql) {
    String dbType = null;
    try (InputStream input = SQLTranslator.class.getClassLoader().getResourceAsStream("dbconfig.properties")) {
        Properties prop = new Properties();
        if (input == null) {
            System.out.println("Sorry, unable to find dbconfig.properties");
            return null;
        }
        prop.load(input);
        dbType = prop.getProperty("database.type");
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return SQLUtil.translate(sql, dbType);
}

Перевод SQL выполняется внешней функцией. В основной программе достаточно добавить вызов этой функции (sql = translateSQL) — остальной код (настройка параметров, выполнение запроса и обработка результатов) остаётся без изменений.

Использование 2 – прозрачное выполнение SQL

Первый метод, использующий `sqltranslate()` для каждого запроса, неэффективен при большом количестве SQL-запросов и использует нерекомендованные интерфейсы. Альтернативный подход — прозрачное выполнение SQL через SPL, используя стандартный JDBC. При этом изменение базы данных требует лишь настройки драйвера и URL в SPL, сам код приложения остаётся неизменным.

public static void main(String[] args) {
    String driver = "com.esproc.jdbc.InternalDriver";
    String url = "jdbc:esproc:local://";
    try {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url);
        String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "
            + "FROM orders WHERE employeeid > ? AND amount > ?";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setObject(1,"506");
        st.setObject(2,9900);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            String employeeid = rs.getString("employeeid");
            System.out.print(employeeid+",");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Механизм SQL-трансляции основан на шлюзе JDBC в SPL. Предварительно настроенный SPL-скрипт (например, gateway.splx) обрабатывает и выполняет все SQL-запросы, передаваемые ему, автоматизируя как трансляцию, так и выполнение. Настройка этого шлюза осуществляется в файле raqsoftConfig.xml через узел JDBC.

<JDBC>
    <load>Runtime,Server</load>
    <gateway>gateway.splx</gateway>
</JDBC>

Script шлюза требуются два параметра: один — sql для получения операторов SQL, а другой — args для получения параметров, которые передаются из JDBC в SQL и используются в операторе SQL.

Для получения нескольких параметров оператора SQL необходимо отметить опцию ниже «Последний параметр является динамическим параметром (The last parameter is a dinamic parameter)».

Вот сценарий:

A1 оценивает, существует ли переменная dbName. Если ее нет, вызовите скрипт инициализации initGlobalVars.splx в B1:

Этот скрипт считывает имя источника данных и тип базы данных из файла конфигурации и помещает их в глобальные переменные dbType и dbName с помощью функции env().

Вот содержимое файла конфигурации dbconfig.properties:

database.type=MYSQL
database.name=MYDATASOURCE

Метод A2 выполняет трансляцию SQL-запроса (описано ранее). A3 определяет количество параметров, а B3 конкатенирует их в одну строку (например, для двух параметров). A4 устанавливает соединение с источником данных, настроенным в raqsoftConfig.xml путём добавления информации о подключении в узел DB. Поддержка нескольких источников данных реализована.

<DB name="MYDATASOURCE">
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb?useCursorFetch=true"></property>
    <property name="driver" value="com.mysql.jdbc.Driver"></property>
    <property name="type" value="10"></property>
    <property name="user" value="root"></property>
    <property name="password" value="root"></property>
    <property name="batchSize" value="0"></property>
    <property name="autoConnect" value="false"></property>
    <property name="useSchema" value="false"></property>
    <property name="addTilde" value="false"></property>
    <property name="caseSentence" value="false"></property>
</DB>

A5 определяет, является ли SQL-запрос оператором SELECT. Так как запросы SELECT (DQL) и остальные (DML) обрабатываются по-разному, необходимо разделение. Для SELECT-запросов (B5) используется функция db.query() для получения данных с последующим закрытием соединения (@x). В случае других типов запросов (A6) применяется db.execute(). Простота сценария и интерпретируемая природа SPL позволяют изменять его без перезапуска приложения, обеспечивая поддержку горячей замены. Этот же механизм подходит и для операторов DML, таких как UPDATE.

public static void main(String[] args) {
    String driver = "com.esproc.jdbc.InternalDriver";
    String url = "jdbc:esproc:local://";
    try {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url);
        String sql = "update orders set customername = ? where orderid = ? ";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setObject(1,"PTCAG001");
        st.setObject(2,"1");
        st.executeUpdate();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Работа с несколькими базами данных

Как нам следует поступать в ситуациях, когда приложение использует несколько баз данных?

Использование 1 - Использовать только преобразование SQL

Давайте сначала рассмотрим использование только для перевода.

Нам все равно нужно сохранить имя и тип источника данных в файле конфигурации. Добавьте следующее в файл dbconfig.properties:

database.oracleds.type=ORACLE
database.mysqlds.type=MYSQL
database.pgds.type=POSTGRESQL

Средняя часть строки перед знаком равенства обозначает название источника данных, например oracleds, а часть справа от него указывает тип источника данных, например ORACLE. Поскольку существует несколько баз данных, нам нужно искать типы в соответствии с названиями источников данных.

Напишите метод перевода, выполните поиск типа по названию источника данных, загрузите файл конфигурации для получения свойств и переведите инструкции SQL:

public static String translateSQL(String sql, String dataSourceName) {
    try (InputStream input = SQLTranslator.class.getClassLoader().getResourceAsStream("dbsconfig.properties")) {
        Properties prop = new Properties();
        if (input == null) {
            System.out.println("Sorry, unable to find dbsconfig.properties");
            return null;
        }
        prop.load(input);
        String dbType = prop.getProperty("database." + dataSourceName.toLowerCase() + ".type");
        if (dbType == null) {
            throw new RuntimeException("Data source " + dataSourceName + " not configured in the configuration file.");
        }
        return SQLUtil.translate(sql, dbType);
    } catch (Exception ex) {
        ex.printStackTrace();
        return null;
    }
}

Этот процесс аналогичен тому, что было упомянуто ранее, поэтому мы не будем повторять его здесь.

Когда основная программа работает, в нее передается инструкция SQL вместе с именем источника данных, которым могут быть MYSQLD или другие источники, и выполняется трансляция SQL, за которой следует установка параметров, выполнение инструкции SQL и получение результирующих наборов, которые полностью совпадают с исходными программа.

public static void main(String[] args) {
    String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "
    + "FROM orders WHERE employeeid > ? AND amount > ?";
    String dataSourceName = "mysqlds"; 
    String translatedSQL = translateSQL(sql, dataSourceName);
    System.out.println("Translated SQL: " + translatedSQL);
    ……
}

Использование 2 – Переведите SPL-скрипт и выполните SQL

Ранее мы обсуждали преимущества и недостатки использования только перевода. Теперь давайте рассмотрим использование SPL gateway для перевода и выполнения SQL-инструкций.

public static void main(String[] args) {
    String driver = "com.esproc.jdbc.InternalDriver";
    String url = "jdbc:esproc:local://";
    try {
        Class.forName(driver);
        String mysqlDsName = "mysqlds";
        Connection mysqlConn = DriverManager.getConnection(url);
        String setDS = "setds "+ mysqlDsName;
        PreparedStatement setst = mysqlConn.prepareStatement(setDS);
        setst.execute();
        String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "
            + "FROM orders WHERE employeeid > ? AND amount > ?";
        PreparedStatement st = mysqlConn.prepareStatement(sql);
        st.setObject(1, "506");
        st.setObject(2, 9900);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            String employeeid = rs.getString("employeeid");
            System.out.print(employeeid + ",");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

Здесь мы устанавливаем соединения с разными источниками данных по отдельности, с дополнительным шагом для задания имени источника данных. Синтаксический анализ источника данных выполняется в скрипте gateway, в то время как остальная часть выполнения SQL полностью соответствует исходной программе.

Параметры в скрипте gateway полностью совпадают с параметрами в предыдущем скрипте single database gateway, где параметр sql получает инструкции SQL, а параметр args получает параметры SQL.

Ниже приведено содержимое скрипта шлюза gateway.splx:

Сценарий шлюза для работы с несколькими базами данных также включает в себя процесс настройки имени источника данных.

Сценарий инициализации initGlobalVars Multi.splx, вызываемый B1, считывает файл конфигурации:

И получает следующий результат:

A3 получает от программы параметр для установки источника данных, который задается как setds mysqld. Если он начинается с setds, B3 присвоит имя источника данных переменной задания dsName. Область действия переменной задания - то же соединение. Затем все инструкции SQL в этом источнике данных могут быть запущены напрямую. Аналогично, B4 выполняет поиск типа базы данных в соответствии со списком источников данных dbs и присваивает его переменной задания DbType.

Начиная с версии A6, скрипт работает так же, как и в версии с единой базой данных, и мы не будем объяснять это снова.

Этот скрипт шлюза по-прежнему может обрабатывать все инструкции SQL и обеспечивает плавную миграцию.

Итоги

Выше описано все, что нам нужно знать о миграции SQL в SPL. С SPL переключение базы данных больше не требует изменения кода, и может быть достигнута плавная миграция.

Конечно, возможности SPL значительно расширяются. Он также поддерживает параллельное выполнение поиска данных SQL, упрощает запросы к разным базам данных, позволяет выполнять смешанные вычисления между базами данных и источниками, не относящимися к базам данных, и использует свои вычислительные мощности для оптимизации производительности SQL. Мы будем рассматривать эти темы в будущих обсуждениях по очереди.

SPL теперь доступен с открытым исходным кодом. Вы можете получить исходный код на GitHub в разделе https://github.com/SPLWare/esProc.

Источник:

Exit fullscreen mode

#Oracle #SQL #MySQL #Data Science

Войти на сайт

Миграция SQL между базами данных

Миграция SQL в среде IDE SPL

Дополнительные примеры

Пример 1: Например, добавление 10 к значению месяца

Пример 2: Получить QUARTER

Пример 3: Преобразование типов

Определение и расширение функций

Фиксированное число параметров

Интеграция с приложениями

Работа с одной базой данных

Использование 1 — Использовать только трансляцию SQL

Использование 2 – прозрачное выполнение SQL

Работа с несколькими базами данных

Использование 1 - Использовать только преобразование SQL

Использование 2 – Переведите SPL-скрипт и выполните SQL

Итоги

Источник: