Кодогенерация в Oracle и не только
В этой статье я расскажу о решении проблемы кодогенерации при разработке приложений для СУБД Oracle. Хотя написанное ниже можно частично применить и к другим СУБД, и для генерации не только кода.ПроблемаПри разработке приложений иногда возникают ситуации, когда требуется написать много похожих кусков кода, которые в силу различных ограничений нельзя заменить одной универсальной программной единицей. Следуя принципу DRY, вместо того чтобы копировать похожие фрагменты кода вручную с заменой отличающихся частей, лучше воспользоваться техникой кодогенерации из статичных заготовок на основе метаданных, вносящих разнообразие в получившиеся экземпляры кода. К кодогенерации приходится прибегать как в develop/deploy-time (когда все вариации известны заранее и есть возможность создать весь спектр экземпляров кода в статичном виде), так и в runtime, когда код генерируется на основе неизвестных заранее данных и сразу же исполняется.Специфика кодогенерации при разработке приложений для СУБД состоит в том, что метаданные, как правило, хранятся в ней же.
Приведу несколько примеров задач, в которых востребована кодогенерация:
формирование SQL/DML/DDL-выражений на основе пользовательского выбора; создание процедуры загрузки в систему-приемник очередного источника данных по его метаописанию в удаленной (remote) системе-источнике; разворачивание системы в разных окружениях, версиях и редакциях СУБД, где необходимо (де)активировать некоторые опции или создать вспомогательные объекты: например, при разворачивании Oracle-приложения на редакции EE секционировать таблицы и строить локальные индексы; или при разворачивании на версии Oracle ниже 11 вместо виртуальных колонок и индексов по ним создавать представления-обертки с дополнительными вычислимыми колонками и строить function-based-индексы. Родные средства Oracle Oracle предоставляет несколько возможностей для генерации кода «из коробки». Бегло рассмотрим существующие средства и их достоинства и недостатки.Серверные средства «Серверные» в данном контексте означает, что процесс кодогенерации происходит на сервере внутри самой СУБД без привлечения сторонних утилит. Как кодовая основа, так и метаданные хранятся в БД или иных источниках, доступных средствами СУБД.Динамический SQL (Dynamic SQL) Этот способ доступен с давних версий Oracle и является самым универсальным из возможных. Разработчик практически ничем не ограничен и, используя все возможности языка PL/SQL, может формировать произвольные строковые выражения на основе данных, извлеченных из БД, и исполнять их на инстансе.Еще с версии 8i существует два механизма исполнения сгенерированного таким образом кода: 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 — самые популярные шаблонные движки. После изучения их возможностей и небольшой практики с ними определился фаворит — FreeMarker. В отличие от Velocity он обладает бо́льшим арсеналом фич (а вот — сравнение с конкурентом) и более гибким хорошо документированным языком FTL. Авторы продолжают активно его развивать и периодически выпускают новые версии.
Судя по его описанию, FreeMarker теоретически позволял интегрироваться с Oracle и «допилить» его таким образом, чтобы из FTL было удобно обращаться к БД.
Кроме того, Java-приложения можно запускать на клиенте так же успешно, как и внутри СУБД. Это решало бы проблему совместимости с непромышленным Oracle XE, в котором нет встроенной JVM, и все еще встречающимися в production старыми версиями Oracle — 8 и 9, чьи JVM несовместимы с FreeMarker, написанном на J2SE 1.4.
Интеграция с Oracle Чтобы сделать полноценный инструмент, необходимо было решить три основные проблемы: поселить FreeMarker в СУБД, заставить его там работать и дописать набор классов, расширяющий язык FTL возможностями JDBC; научиться хранить шаблоны 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>, )
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>, ) /
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’ов.
