Кодогенерация в Oracle и не только

Виктор Осоловский, наш разработчик-эксперт, опубликовал в блоге компании на «Хабрахабре» статью, посвященную решению проблемы кодогенерации при разработке приложений для СУБД Oracle. Какие существуют возможности для генерации кода «из коробки»? В чем заключаются достоинства и недостатки клиентских средств и сторонних решений? Каким образом удалось создать свой инструмент для решения проблемы с генерацией кода? Ответы на эти вопросы — в материале «Кодогенерация в Oracle и не только».

В этой статье я расскажу о решении проблемы кодогенерации при разработке приложений для СУБД Oracle. Хотя написанное ниже частично можно применить и к другим СУБД, и для генерации не только кода.

Содержание

1 Проблема
2 Родные средства Oracle
- 2.1 Серверные средства
- 2.2 Клиентские средства
  - 2.2.1 SQL*Plus
    - 2.2.1.1 Достоинства
    - 2.2.1.2 Недостатки
3 Сторонние проекты
4 Создаем свой инструмент

Проблема

При разработке приложений иногда возникают ситуации, когда требуется написать много похожих кусков кода, которые в силу различных ограничений нельзя заменить одной универсальной программной единицей. Следуя принципу DRY, вместо того чтобы копировать похожие фрагменты кода вручную с заменой отличающихся частей, лучше воспользоваться техникой кодогенерации из статичных заготовок на основе метаданных, вносящих разнообразие в получившиеся экземпляры кода. К кодогенерации приходится прибегать как в develop/deploy-time (когда все вариации известны заранее и есть возможность создать весь спектр экземпляров кода в статичном виде), так и в runtime, когда код генерируется на основе неизвестных заранее данных и сразу же исполняется.

Специфика кодогенерации при разработке приложений для СУБД состоит в том, что метаданные, как правило, хранятся в ней же.

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

формирование SQL/DML/DDL-выражений на основе пользовательского
создание процедуры загрузки в систему-приемник очередного источника данных по его метаописанию в удаленной (remote) системе-источнике;
разворачивание системы в разных окружениях, версиях и редакциях СУБД, где необходимо (де)активировать некоторые опции или создать вспомогательные объекты: например, при разворачивании Oracle-приложения на редакции EE секционировать таблицы и строить локальные индексы; или при разворачивании на версии Oracle ниже 11 вместо виртуальных колонок и индексов по ним создавать представления-обертки с дополнительными вычислимыми колонками и строить function-based-индексы.

Родные средства Oracle

Oracle предоставляет несколько возможностей для генерации кода «из коробки». Бегло рассмотрим существующие средства и их достоинства и недостатки.

Серверные средства

«Серверные» в данном контексте означает, что процесс кодогенерации происходит на сервере внутри самой СУБД без привлечения сторонних утилит. Как кодовая основа, так и метаданные хранятся в БД или иных источниках, доступных средствами СУБД.

Динамический SQL (Dynamic SQL)

Этот способ доступен с давних версий Oracle и является самым универсальным из возможных. Разработчик практически ничем не ограничен и, используя все возможности языка PL/SQL, может формировать произвольные строковые выражения на основе данных, извлеченных из БД, и исполнять их на инстансе.

Еще с версии 8i существует два механизма исполнения cгенерированного таким образом кода: DBMS_SQL и Native Dynamic SQL. Их различия в контексте данной статьи не очень принципиальны, важно лишь, что пользоваться вторым проще, но не всегда возможно, поэтому используются оба — в зависимости от задач. Подробнее о них и их различиях можно почитать в документации.

Достоинства

Использование всех возможностей PL/SQL как процедурного языка для компоновки генерируемого текста и как языка доступа к БД для выборки и обработки метаданных.

Недостатки

Сложность кода-генератора, как правило, усыпанного строковыми литералами, кавычками, операторами конкатенации, замены, символами переноса строк. Все это перемежается с управляющими конструкциями PL/SQL — условными переходами, циклами. За «пеленой» кода-генератора бывает непросто разглядеть генерируемый код и понять его логику. Поддерживать такую смесь довольно сложно. В то же время это самый распространенный способ кодогенерации.

Выглядит это примерно так.

Приведенный ниже пакет генерирует скрипт создания секционированной таблицы товаров ORDERS, у которой partitioning clause заранее неизвестен и формируется на основе запроса к другой таблице-справочнику — SHOPS, разбивая заказы по регионам расположения магазинов.

CREATE OR REPLACE package body generator AS
 
 
 
cursor cur_partitions IS
 
  SELECT
 
    t.region name,
 
    listagg(t.shop_id, ', ')
 
      WITHIN GROUP (ORDER BY t.shop_id) vals
 
  FROM shops t
 
  GROUP BY t.region
 
  ORDER BY t.region;
 
 
 
PROCEDURE gen_orders_plsql
 
IS
 
  l_scr CLOB;
 
BEGIN
 
  l_scr :=
 
    'create table orders (' || chr(10) ||
 
    '  order_id integer not null primary key,' || chr(10) ||
 
    '  customer_id integer not null,' || chr(10) ||
 
    '  shop_id integer not null,' || chr(10) ||
 
    '  order_date date not null,' || chr(10) ||
 
    '  status varchar2(10) not null' || chr(10) ||
 
    ')' || chr(10) ||
 
    'partition by list(shop_id) (';
 
 
 
  FOR r IN cur_partitions loop
 
    l_scr := l_scr ||
 
      CASE WHEN cur_partitions%rowcount > 1 THEN ',' END || chr(10) ||
 
      '  partition ' || r.name || ' values (' || r.vals || ')';
 
  END loop;
 
 
 
  l_scr := l_scr || chr(10) || ')';
 
 
 
  dbms_output.put_line(l_scr);
 
  dbms_output.put_line('/');
 
  EXECUTE immediate l_scr;
 
 
 
  l_scr := 'comment on table orders is ''Orders partitioned by region.''';
 
 
 
  dbms_output.put_line(l_scr);
 
  dbms_output.put_line('/');
 
  EXECUTE immediate l_scr;
 
 
 
END gen_orders_plsql;
 
 
 
END generator;
 
/

Вызов генерации:

BEGIN
 
  generator.gen_orders_plsql();
 
END;
 
/

Результат генерации — созданная таблица и выведенный в output скрипт:

CREATE TABLE orders (
 
  order_id INTEGER NOT NULL PRIMARY KEY,
 
  customer_id INTEGER NOT NULL,
 
  shop_id INTEGER NOT NULL,
 
  order_date DATE NOT NULL,
 
  STATUS varchar2(10) NOT NULL
 
)
 
partition BY list(shop_id) (
 
  partition east VALUES (2, 3, 7),
 
  partition north VALUES (1, 4, 6, 9),
 
  partition south VALUES (5, 8, 12),
 
  partition west VALUES (10, 11, 13)
 
)
 
/
 
comment ON TABLE orders IS 'Orders partitioned by region.'
 
/

Условная компиляция PL/SQL (PL/SQL Conditional Compilation)

Официально этот способ появился в версии 10.2, но с помощью скрытого параметра его можно активировать и в более ранних версиях — 9.2.0.6 и 10.1.0.4. Это не совсем инструмент кодогенерации, но он позволяет привнести немного динамики в статичный программный код на этапе компиляции с помощью специальных директив. Условная компиляция потому так и называется, что предоставляет лишь условный оператор с возможностью проверки специальных флагов компиляции (PLSQL_CCFLAGS) и пакетных констант (инициализированных строго литералами) числового или булевого типа. Несмотря на примитивность, этот способ позволяет, например, сильно облегчить написание кода, переносимого между разными версиями СУБД, скрывая от компилятора не поддерживаемые целевым инстансом фрагменты кода. Также это неплохое решение для добавления необходимого для отладки кода, который при компиляции в production-окружении просто отключается и не требует выполнения постоянных проверок типа if is debug_mode then.

Достоинства

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

Недостатки

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

Приведенный ниже скрипт выводит названия таблиц текущего пользователя вместе со списком их колонок. Начиная с версии Oracle 11.2 используется более естественная для строковой агрегации функция listagg, а для версий ниже — воркэраунд из микса различных функций.

BEGIN
 
  FOR r IN (
 
    SELECT
 
      TABLE_NAME,
 
      $if
 
        dbms_db_version.version = 11 AND dbms_db_version.release > 1 OR
 
        dbms_db_version.version > 11
 
      $then
 
        listagg(column_name, ', ') WITHIN GROUP(ORDER BY column_id)
 
      $else
 
        substr(utl_i18n.unescape_reference(
 
          xmlagg(sys_xmlgen(', ' || column_name) ORDER BY column_id)
 
            .extract('//text()')
 
            .GetStringVal()
 
        ), 3)
 
      $end AS column_list
 
    FROM
 
      user_tab_columns
 
    GROUP BY TABLE_NAME
 
    ORDER BY TABLE_NAME
 
  ) loop
 
    dbms_output.put_line(r.table_name || '''s columns: ' || r.column_list);
 
  END loop;   
 
END;
 
/

Подробно об условной компиляции PL/SQL можно почитать в официальном обзоре этой фичи.

XSLT

На мой взгляд, не самый удобный способ генерации кода, однако и он иногда применяется. Несмотря на то, что XML не вполне нативный формат для реляционных СУБД, он хорошо поддерживается Oracle, в том числе есть и встроенный XSLT-процессор. Основная же причина, по которой его стоит упомянуть, — он является примером шаблонной генерации: в отличие от первого способа (DynSQL), здесь генерируемый код является первичным и, как следствие, легче читаемым в теле шаблона, а управляющие конструкции кода-генератора, наоборот, вторичны и требуют к себе внимания только в тех местах, где код становится динамичным. При том XSLT — механизм только генерации кода, но не его исполнения, поэтому пользоваться динамическим SQL все равно приходится.

Достоинства

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

Недостатки

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

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

Клиентские средства

«Клиентские» в данном контексте означает, что код генерируется внешней утилитой, работающей снаружи СУБД (хотя хост-средой может быть сам сервер, на котором поднят инстанс) и имеющей доступ к ее данным как клиент.

SQL*Plus

SQL*PLus — это стандартная CLI-утилита для работы с СУБД Oracle с небольшим набором собственных команд. Она предоставляет возможность сделать код немного динамичнее благодаря переменным подстановки (substitution variables) и вложению фрагментов (script nesting).

В отличие от директив компиляции, переменные подстановки можно использовать вообще в любом месте кода, а значения им могут быть присвоены произвольным запросом. Правда, эти переменные могут быть только строковыми, а в самом SQL*Plus нет даже условного оператора и циклов. Их имитация выглядит весьма пугающе и делает этот инструмент малопригодным для кодогенерации хоть со сколько-нибудь сложной логикой: см. примеры итераций и ветвления из блога @xtender. Кроме того, кодовая основа может быть доступна SQL*Plus'у только из файлов.

Достоинства

Простая в использовании операция подстановки.

Недостатки

Только клиентская сторона выполнения и хранения кода, примитивность управляющих инструкций.

Сторонние проекты

Что предлагает сообщество для решения обозначенной проблемы? Удалось найти лишь несколько проектов по данной теме.

PL/Generator

Очень старый проект 1998 года от известного евангелиста Стивена Ферштейна для шаблонной генерации PL/SQL-кода, написанный еще под Oracle 7/8. Давно не поддерживается. Использует специальный язык разметки CGML, очень жестко привязанный к специфике Oracle и PL/SQL, что сильно ограничивает область его применения. Для работы генератора требуется доступ к файловой системе сервера (через UTL_FILE) для чтения шаблонов и сохранения результатов генерации, хотя, судя по документации, автономная работа только внутри БД тоже была предусмотрена и, возможно, даже реализована. Скачать проект по-прежнему можно с сайта автора.

Quest CodeGen Utility

Реинкарнация PL/Generator, развиваемая в 2004–2007 годах. Представляет из себя целую IDE с возможностью генерировать PL/SQL-код из тех же CGML-шаблонов и проносить результат на сервер. Проект давно закрыт, сайт не работает, документации и даже упоминаний в интернете почти не осталось. Удалось найти только предпоследнюю версию на одном софтверном ресурсе. Разобраться с продуктом оказалось непросто, а, учитывая ограниченность CGML, изучать его досконально я не стал.

iCodeGenerator

Современный проект с открытым кодом на GitHub, позиционирующий себя как БД-центричный шаблонный кодогенератор для любых языков с поддержкой разных СУБД, в том числе Oracle. На поверку оказалось, что шаблонный язык чрезвычайно скуден и, кроме как для краткого документирования содержимого схемы, ни на что не годится. Представляет собой клиентское приложение на .NET. Подключиться к Oracle согласно инструкции так и не получилось. Доступен на своем сайте.

Многочисленные Table API-генераторы

PL/SQLGenPkg, PL/SQL Interface Generator, tapiGen, TAPIG — узкопрофильные средства для генерации обвязок вокруг таблиц для возможности выполнения операций CRUD через них вместо обычного SQL.

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

Создаем свой инструмент

Итак, инструмента, который удовлетворял бы широким потребностям промышленной разработки, к сожалению, не нашлось. Поэтому было решено сделать свой. Были выбраны самые полезные возможности из арсенала штатных средств и составлен список фич, необходимых для комфортной работы:

от обычного PL/SQL были взяты способы обращения к БД: выполнение запросов, вызов хранимых функций, получение результата в виде сложносоставных типов — result set'ов;

от PLSQLCC — возможность писать и хранить управляющие инструкции кода-генератора как plain text прямо в теле хранимых программных единиц, чтобы редактирование в IDE ничем не отличалось от привычной разработки обычного статичного кода;

от XSLT — принцип шаблонной генерации: первичность генерируемого кода над кодом-генератором;

от SQL*Plus — возможность выполнения на клиенте.

Возникло еще одно требование, продиктованное инертностью Enterprise-сектора, — совместимость с Oracle как минимум 10-й версии (при текущей 12-й), поскольку она все еще активно используется в production.

Выбор движка шаблонов

Поскольку Oracle имеет встроенную JVM (ранее известную как Aurora), а Java-мир очень богат на инструменты любого вида, было решено посмотреть в сторону Java Template Engines.

Практически сразу же определились два кандидата: Velocity и FreeMarker — самые популярные шаблонные движки. Изучив их возможности и попрактиковавшись с ними, мы поняли, что Velocity заморожен несколько лет назад, а FreeMarker обладает более обширным арсеналом фич и более гибким хорошо документированным языком (FTL), продолжает активно развиваться и периодически выпускает новые версии.

Судя по его описанию, FreeMarker теоретически позволял интегрироваться с Oracle и «допилить» его таким образом, чтобы из FTL было удобно обращаться к БД.

Кроме того, Java-приложения можно запускать на клиенте так же успешно, как и внутри СУБД. Это решало бы проблему совместимости с Oracle XE, в котором нет поддержки Java, а также очень старыми версиями Oracle (младше 10), где JVM-версии — ниже 1.4, несовместимые с FreeMarker.

Интеграция с Oracle

Чтобы сделать полноценный инструмент, необходимо было решить три основные проблемы.

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

Забегая вперед, скажу, что все эти проблемы были успешно решены и родился инструмент FTLDB (названный по аналогии c HTMLDB и XMLDB), который теперь лежит на GitHub под лицензией Apache License 2.0.

А теперь расскажу подробнее о том, что было сделано.

Доработка FreeMarker

Вся Java-разработка велась с минимальной конкретикой на Oracle, с использованием преимущественно интерфейсов JDBC из java.sql.*. Это позволило в дальнейшем использовать инструмент не только для Oracle, но и для других СУБД. В тех местах, где без специфики было не обойтись (серверное выполнение на встроенной JVM), были оставлены возможности расширения и для других СУБД, потенциально способных исполнять Java-код самостоятельно (например, PostgreSQL + PL/Java).

Язык FTL был дополнен методами создания JDBC-соединений (или использования текущего соединения при работе на server-side), выполнения в рамках них SQL-запросов и программных вызовов, работы с результатами их выполнения в родном для FTL виде. Также были добавлены некоторые утилитарные методы для FTL, загрузчик шаблонов из БД с помощью произвольного вызова и main-методы для запуска движка из CLI и PL/SQL.

Хранение шаблонов

С помощью директив PLSQLCC был придуман простой и элегантный способ хранения шаблонов в БД в телах хранимых программных единиц. Используя директиву $if $then $end с явным ложным условием, в теле любого пакета можно создать «контейнер», невидимый компилятору, в который можно поместить как plain text вообще любой код, а в частности — шаблоны FTL. Это позволяет очень удобно редактировать код в IDE (с сохранением подсветки и code assist фич) и устанавливать его на сервер так же, как обычные пакеты (к слову, проносить их в виде CLOB было бы не так тривиально).

Выглядит это примерно так:

CREATE OR REPLACE package my_template AS
 
$if FALSE $then
 
 
 
здесь можно хранить любой невалидный с точки зрения компилятора код
 
 
 
$end
 
END;
 
/

Очень похоже на хранимые функции в PostgreSQL :)

Процессинг шаблонов

Для извлечения шаблонов из таких контейнеров был написан утилитарный пакет, позволяющий обращаться к ним по имени содержащего их объекта Oracle и возвращающий их в виде CLOB. Этот пакет используется загрузчиком шаблонов в FreeMarker.

Обработка шаблонов осуществляется движком FreeMarker с использованием наших расширений JDBC для извлечения метаданных, после чего результат генерации возвращаются в вызывающую среду в виде out-потока (client-side) или CLOB (Oracle server-side).

Кроме того, был написан специальный объектный PL/SQL-тип, в который опционально преобразуется результат обработки шаблона. Он представляет собой контейнер исполняемых выражений, которые можно выводить в output или запускать на инстансе с помощью встроенных методов. Это своеобразный серверный аналог SQL*Plus, позволяющий компоновать множество шаблонов или отдельных выражений в один объект и выполнять его как скрипт.

Что получилось и как выглядит

FTLDB для Oracle распространяется в виде инсталлятора под Windows batch и *nix shell. Устанавливается он как отдельная схема (так рекомендуется, хотя можно ставить и в существующую по соседству с другими объектами). Все объекты создаются в режиме выполнения c правами вызывающего (invoker's rights), что позволяет использовать его из других схем совершенно прозрачно.

Для работы на клиенте (с любой СУБД) нужны только jar-архивы FreeMarker и FTLDB из инсталлятора, а также JDBC-драйвер, распространяемый производителем СУБД.

Меньше слов — больше примеров! Посмотрите еще раз на демонстрационный фрагмент кода из раздела про DynSQL.

Вот как то же самое делается с помощью FTLDB на серверной стороне.

Сам пакет немного изменился в получении метаданных, а вместо конкатенации скрипта таблицы по кусочкам есть настоящий шаблон:

CREATE OR REPLACE package body generator AS
 
 
 
FUNCTION get_partitions RETURN sys_refcursor
 
IS
 
  l_rc sys_refcursor;
 
BEGIN
 
  OPEN l_rc FOR
 
    SELECT
 
      t.region name,
 
      listagg(t.shop_id, ', ')
 
        WITHIN GROUP (ORDER BY t.shop_id) vals
 
    FROM shops t
 
    GROUP BY t.region
 
    ORDER BY t.region;
 
  RETURN l_rc;
 
END get_partitions;
 
 
 
$if FALSE $then
 
--%begin orders_ftl
 
 
 
<#import "ftldb.sql_ftl" AS SQL/>
 
<#assign partitions = SQL.fetch('generator.get_partitions')/>
 
 
 
CREATE TABLE orders (
 
  order_id INTEGER NOT NULL PRIMARY KEY,
 
  customer_id INTEGER NOT NULL,
 
  shop_id INTEGER NOT NULL,
 
  order_date DATE NOT NULL,
 
  STATUS varchar2(10) NOT NULL
 
)
 
partition BY list(shop_id) (
 
<#list partitions.hash_rows AS p>
 
  partition ${p.NAME} VALUES (${p.VALS})<#if p_has_next>,</#if>
 
</#list>
 
)
 
</>
 
 
 
comment ON TABLE orders IS 'Orders partitioned by region.'
 
</>
 
 
 
--%end orders_ftl
 
$end
 
 
 
FUNCTION gen_orders_ftldb RETURN ftldb.script_ot
 
IS
 
BEGIN
 
  RETURN ftldb.ftldb_api.process('generator%orders_ftl');
 
END gen_orders_ftldb;
 
 
 
END generator;
 
/

Вызов генерации (создать скрипт и запустить его в текущей сессии с выводом в output):

BEGIN
 
  generator.gen_orders_ftldb().exec(TRUE);
 
END;
 
/

А вот так на клиентской.

Шаблон храним в отдельном файле:

<#assign conn = new_connection(template_args[0], template_args[1], template_args[2])/>
 
 
 
<#assign
 
  partitions = conn.query(
 
    "select " +
 
      "t.region name, " +
 
      "listagg(t.shop_id, ', ') within group (order by t.shop_id) vals " +
 
    "from shops t " +
 
    "group by t.region " +
 
    "order by t.region"
 
  )
 
/>
 
 
 
create table orders (
 
  order_id integer not null primary key,
 
  customer_id integer not null,
 
  shop_id integer not null,
 
  order_date date not null,
 
  status varchar2(10) not null
 
)
 
partition by list(shop_id) (
 
<#list partitions.hash_rows as p>
 
  partition ${p.NAME} values (${p.VALS})<#if p_has_next>,</#if>
 
</#list>
 
)
 
/
 
 
 
comment on table orders is 'Orders partitioned by region.'
 
/
 
 
 
<#assign void = conn.close()/>

Вызов генерации и запуск скрипта:

java -cp ../java/* ftldb.CommandLine orders.ftl "jdbc:oracle:oci:@orcl" scott tiger 1> orders.sql
 
sqlplus scott/tiger@orcl @orders.sql

Больше примеров использования доступны в демо проекта. Документация API приложена к дистрибутиву.

Другие варианты применения

Поскольку клиентский режим целиком и полностью работает на абстракции JDBC и не имеет Oracle-специфичных «зашивок», FTLDB можно использовать для клиентской генерации скриптов для любой СУБД, предоставляющей JDBC-драйвер. Вообще говоря, FTLDB подходит для генерации произвольных текстов в любой области применения самого FreeMarker, например, документации или web-страниц.

Настоящее и будущее проекта

FTLDB сейчас используется в нескольких наших промышленных проектах, поддерживается авторами и имеет планы дальнейшего развития.

С удовольствием ждем ваших отзывов, предложений по доработке и pull request'ов.