|
Кодогенерация в Oracle и не только — различия между версиями
Материал из CustisWiki
|
|
(не показана одна промежуточная версия этого же участника) | Строка 1: |
Строка 1: |
− | <blockquote>''[[:Категория:Виктор Осоловский (Статьи)|Виктор Осоловский]], наш разработчик-эксперт, опубликовал в блоге компании на [http://habrahabr.ru/ «Хабрахабре»] статью, посвященную решению проблемы кодогенерации при разработке приложений для СУБД Oracle. Какие существуют возможности для генерации кода «из коробки»? В чем заключаются достоинства и недостатки клиентских средств и сторонних решений? Каким образом удалось создать свой инструмент для решения проблемы с генерацией кода? Ответы на эти вопросы — в материале [http://habrahabr.ru/company/custis/blog/253881/ «Кодогенерация в Oracle и не только»].''</blockquote> | + | <blockquote>''[[:Категория:Виктор Осоловский (Статьи)|Виктор Осоловский]], наш разработчик-эксперт, опубликовал в блоге компании на [http://habrahabr.ru/ «Хабрахабре»] статью, посвященную решению проблемы кодогенерации при разработке приложений для СУБД Oracle. Какие существуют возможности для генерации кода «из коробки»? В чем заключаются достоинства и недостатки клиентских средств и сторонних решений? Каким образом удалось создать свой инструмент для решения проблемы с генерацией кода? Ответы на эти вопросы — в материале [http://habrahabr.ru/company/custis/blog/253881/ «Кодогенерация в Oracle и не только»].''</blockquote> |
− | В этой статье я расскажу о решении проблемы кодогенерации при разработке приложений для СУБД Oracle. Хотя написанное ниже частично можно применить и к другим СУБД, и для генерации не только кода. | + | |
| + | В этой статье я расскажу о решении проблемы кодогенерации при разработке приложений для СУБД Oracle. Хотя написанное ниже частично можно применить и к другим СУБД, и для генерации не только кода. |
| | | |
| == Проблема == | | == Проблема == |
| | | |
− | При разработке приложений иногда возникают ситуации, когда требуется написать много похожих кусков кода, которые в силу различных ограничений нельзя заменить одной универсальной программной единицей. Следуя принципу [http://en.wikipedia.org/wiki/Don%27t_repeat_yourself DRY], вместо того чтобы копировать похожие фрагменты кода вручную с заменой отличающихся частей, лучше воспользоваться техникой кодогенерации из статичных заготовок на основе метаданных, вносящих разнообразие в получившиеся экземпляры кода. К кодогенерации приходится прибегать как в develop/deploy-time (когда все вариации известны заранее и есть возможность создать весь спектр экземпляров кода в статичном виде), так и в runtime, когда код генерируется на основе неизвестных заранее данных и сразу же исполняется. | + | При разработке приложений иногда возникают ситуации, когда требуется написать много похожих кусков кода, которые в силу различных ограничений нельзя заменить одной универсальной программной единицей. Следуя принципу [http://en.wikipedia.org/wiki/Don%27t_repeat_yourself DRY], вместо того чтобы копировать похожие фрагменты кода вручную с заменой отличающихся частей, лучше воспользоваться техникой кодогенерации из статичных заготовок на основе метаданных, вносящих разнообразие в получившиеся экземпляры кода. К кодогенерации приходится прибегать как в develop/deploy-time (когда все вариации известны заранее и есть возможность создать весь спектр экземпляров кода в статичном виде), так и в runtime, когда код генерируется на основе неизвестных заранее данных и сразу же исполняется. |
| | | |
− | Специфика кодогенерации при разработке приложений для СУБД состоит в том, что метаданные, как правило, хранятся в ней же. | + | Специфика кодогенерации при разработке приложений для СУБД состоит в том, что метаданные, как правило, хранятся в ней же. |
| | | |
| Приведу несколько примеров задач, в которых востребована кодогенерация: | | Приведу несколько примеров задач, в которых востребована кодогенерация: |
| | | |
| * формирование SQL/DML/DDL-выражений на основе пользовательского | | * формирование SQL/DML/DDL-выражений на основе пользовательского |
− | * создание процедуры загрузки в систему-приемник очередного источника данных по его метаописанию в удаленной (remote) системе-источнике; | + | * создание процедуры загрузки в систему-приемник очередного источника данных по его метаописанию в удаленной (remote) системе-источнике; |
− | * разворачивание системы в разных окружениях, версиях и редакциях СУБД, где необходимо (де)активировать некоторые опции или создать вспомогательные объекты: например, при разворачивании Oracle-приложения на редакции EE секционировать таблицы и строить локальные индексы; или при разворачивании на версии Oracle ниже 11 вместо виртуальных колонок и индексов по ним создавать представления-обертки с дополнительными вычислимыми колонками и строить function-based-индексы. | + | * разворачивание системы в разных окружениях, версиях и редакциях СУБД, где необходимо (де)активировать некоторые опции или создать вспомогательные объекты: например, при разворачивании Oracle-приложения на редакции EE секционировать таблицы и строить локальные индексы; или при разворачивании на версии Oracle ниже 11 вместо виртуальных колонок и индексов по ним создавать представления-обертки с дополнительными вычислимыми колонками и строить function-based-индексы. |
| | | |
| == Родные средства Oracle == | | == Родные средства Oracle == |
| | | |
− | Oracle предоставляет несколько возможностей для генерации кода «из коробки». Бегло рассмотрим существующие средства и их достоинства и недостатки. | + | Oracle предоставляет несколько возможностей для генерации кода «из коробки». Бегло рассмотрим существующие средства и их достоинства и недостатки. |
| | | |
| === Серверные средства === | | === Серверные средства === |
| | | |
− | «Серверные» в данном контексте означает, что процесс кодогенерации происходит на сервере внутри самой СУБД без привлечения сторонних утилит. Как кодовая основа, так и метаданные хранятся в БД или иных источниках, доступных средствами СУБД. | + | «Серверные» в данном контексте означает, что процесс кодогенерации происходит на сервере внутри самой СУБД без привлечения сторонних утилит. Как кодовая основа, так и метаданные хранятся в БД или иных источниках, доступных средствами СУБД. |
| | | |
− | ==== Динамический SQL (Dynamic SQL) ==== | + | ==== Динамический SQL (Dynamic SQL) ==== |
| | | |
− | Этот способ доступен с давних версий Oracle и является самым универсальным из возможных. Разработчик практически ничем не ограничен и, используя все возможности языка PL/SQL, может формировать произвольные строковые выражения на основе данных, извлеченных из БД, и исполнять их на инстансе. | + | Этот способ доступен с давних версий Oracle и является самым универсальным из возможных. Разработчик практически ничем не ограничен и, используя все возможности языка PL/SQL, может формировать произвольные строковые выражения на основе данных, извлеченных из БД, и исполнять их на инстансе. |
| | | |
− | Еще с версии 8i существует два механизма исполнения cгенерированного таким образом кода: DBMS_SQL и Native Dynamic SQL. Их различия в контексте данной статьи не очень принципиальны, важно лишь, что пользоваться вторым проще, но не всегда возможно, поэтому используются оба — в зависимости от задач. Подробнее о них и их различиях можно почитать в [https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011 документации]. | + | Еще с версии 8i существует два механизма исполнения cгенерированного таким образом кода: DBMS_SQL и Native Dynamic SQL. Их различия в контексте данной статьи не очень принципиальны, важно лишь, что пользоваться вторым проще, но не всегда возможно, поэтому используются оба — в зависимости от задач. Подробнее о них и их различиях можно почитать в [https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011 документации]. |
| | | |
| ===== Достоинства ===== | | ===== Достоинства ===== |
| | | |
− | Использование всех возможностей PL/SQL как процедурного языка для компоновки генерируемого текста и как языка доступа к БД для выборки и обработки метаданных. | + | Использование всех возможностей PL/SQL как процедурного языка для компоновки генерируемого текста и как языка доступа к БД для выборки и обработки метаданных. |
| | | |
| ===== Недостатки ===== | | ===== Недостатки ===== |
| | | |
− | Сложность кода-генератора, как правило, усыпанного строковыми литералами, кавычками, операторами конкатенации, замены, символами переноса строк. Все это перемежается с управляющими конструкциями PL/SQL — условными переходами, циклами. За «пеленой» кода-генератора бывает непросто разглядеть генерируемый код и понять его логику. Поддерживать такую смесь довольно сложно. В то же время это самый распространенный способ кодогенерации. | + | Сложность кода-генератора, как правило, усыпанного строковыми литералами, кавычками, операторами конкатенации, замены, символами переноса строк. Все это перемежается с управляющими конструкциями PL/SQL — условными переходами, циклами. За «пеленой» кода-генератора бывает непросто разглядеть генерируемый код и понять его логику. Поддерживать такую смесь довольно сложно. В то же время это самый распространенный способ кодогенерации. |
| | | |
| Выглядит это примерно так. | | Выглядит это примерно так. |
| | | |
− | Приведенный ниже пакет генерирует скрипт создания секционированной таблицы товаров ORDERS, у которой partitioning clause заранее неизвестен и формируется на основе запроса к другой таблице-справочнику — SHOPS, разбивая заказы по регионам расположения магазинов. | + | Приведенный ниже пакет генерирует скрипт создания секционированной таблицы товаров ORDERS, у которой partitioning clause заранее неизвестен и формируется на основе запроса к другой таблице-справочнику — SHOPS, разбивая заказы по регионам расположения магазинов. |
| <code-sql>create or replace package body generator as | | <code-sql>create or replace package body generator as |
| | | |
Строка 143: |
Строка 144: |
| /</code-sql> | | /</code-sql> |
| | | |
− | Результат генерации — созданная таблица и выведенный в output скрипт: | + | Результат генерации — созданная таблица и выведенный в output скрипт: |
| | | |
| <code-sql>create table orders ( | | <code-sql>create table orders ( |
Строка 177: |
Строка 178: |
| /</code-sql> | | /</code-sql> |
| | | |
− | ==== Условная компиляция PL/SQL (PL/SQL Conditional Compilation) ==== | + | ==== Условная компиляция PL/SQL (PL/SQL Conditional Compilation) ==== |
| | | |
− | Официально этот способ появился в версии 10.2, но с помощью скрытого параметра его можно активировать и в более ранних версиях — 9.2.0.6 и 10.1.0.4. Это не совсем инструмент кодогенерации, но он позволяет привнести немного динамики в статичный программный код на этапе компиляции с помощью специальных директив. Условная компиляция потому так и называется, что предоставляет лишь условный оператор с возможностью проверки специальных флагов компиляции (PLSQL_CCFLAGS) и пакетных констант (инициализированных строго литералами) числового или булевого типа. Несмотря на примитивность, этот способ позволяет, например, сильно облегчить написание кода, переносимого между разными версиями СУБД, скрывая от компилятора не поддерживаемые целевым инстансом фрагменты кода. Также это неплохое решение для добавления необходимого для отладки кода, который при компиляции в production-окружении просто отключается и не требует выполнения постоянных проверок типа if is debug_mode then. | + | Официально этот способ появился в версии 10.2, но с помощью скрытого параметра его можно активировать и в более ранних версиях — 9.2.0.6 и 10.1.0.4. Это не совсем инструмент кодогенерации, но он позволяет привнести немного динамики в статичный программный код на этапе компиляции с помощью специальных директив. Условная компиляция потому так и называется, что предоставляет лишь условный оператор с возможностью проверки специальных флагов компиляции (PLSQL_CCFLAGS) и пакетных констант (инициализированных строго литералами) числового или булевого типа. Несмотря на примитивность, этот способ позволяет, например, сильно облегчить написание кода, переносимого между разными версиями СУБД, скрывая от компилятора не поддерживаемые целевым инстансом фрагменты кода. Также это неплохое решение для добавления необходимого для отладки кода, который при компиляции в production-окружении просто отключается и не требует выполнения постоянных проверок типа if is debug_mode then. |
| | | |
| ===== Достоинства ===== | | ===== Достоинства ===== |
Строка 187: |
Строка 188: |
| ===== Недостатки ===== | | ===== Недостатки ===== |
| | | |
− | Крайне примитивный функционал, возможность использования только в программных единицах (анонимных блоках, пакетах, процедурах и т. д.), но, например, не в представлениях. | + | Крайне примитивный функционал, возможность использования только в программных единицах (анонимных блоках, пакетах, процедурах и т. д.), но, например, не в представлениях. |
| | | |
− | Приведенный ниже скрипт выводит названия таблиц текущего пользователя вместе со списком их колонок. Начиная с версии Oracle 11.2 используется более естественная для строковой агрегации функция listagg, а для версий ниже — воркэраунд из микса различных функций. | + | Приведенный ниже скрипт выводит названия таблиц текущего пользователя вместе со списком их колонок. Начиная с версии Oracle 11.2 используется более естественная для строковой агрегации функция listagg, а для версий ниже — воркэраунд из микса различных функций. |
| | | |
| <code-sql>begin | | <code-sql>begin |
Строка 241: |
Строка 242: |
| /</code-sql> | | /</code-sql> |
| | | |
− | Подробно об условной компиляции PL/SQL можно почитать в [http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf официальном обзоре] этой фичи. | + | Подробно об условной компиляции PL/SQL можно почитать в [http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf официальном обзоре] этой фичи. |
| | | |
| ==== XSLT ==== | | ==== XSLT ==== |
| | | |
− | На мой взгляд, не самый удобный способ генерации кода, однако и он иногда применяется. Несмотря на то, что XML не вполне нативный формат для реляционных СУБД, он хорошо поддерживается Oracle, в том числе есть и встроенный XSLT-процессор. Основная же причина, по которой его стоит упомянуть, — он является примером шаблонной генерации: в отличие от первого способа (DynSQL), здесь генерируемый код является первичным и, как следствие, легче читаемым в теле шаблона, а управляющие конструкции кода-генератора, наоборот, вторичны и требуют к себе внимания только в тех местах, где код становится динамичным. При том XSLT — механизм только генерации кода, но не его исполнения, поэтому пользоваться динамическим SQL все равно приходится. | + | На мой взгляд, не самый удобный способ генерации кода, однако и он иногда применяется. Несмотря на то, что XML не вполне нативный формат для реляционных СУБД, он хорошо поддерживается Oracle, в том числе есть и встроенный XSLT-процессор. Основная же причина, по которой его стоит упомянуть, — он является примером шаблонной генерации: в отличие от первого способа (DynSQL), здесь генерируемый код является первичным и, как следствие, легче читаемым в теле шаблона, а управляющие конструкции кода-генератора, наоборот, вторичны и требуют к себе внимания только в тех местах, где код становится динамичным. При том XSLT — механизм только генерации кода, но не его исполнения, поэтому пользоваться динамическим SQL все равно приходится. |
| | | |
| ===== Достоинства ===== | | ===== Достоинства ===== |
| | | |
− | Целевой код представлен в виде шаблона, его легче понимать и поддерживать, чем собираемый по частям из строковых литералов. | + | Целевой код представлен в виде шаблона, его легче понимать и поддерживать, чем собираемый по частям из строковых литералов. |
| | | |
| ===== Недостатки ===== | | ===== Недостатки ===== |
| | | |
− | Нетипичный для реляционных СУБД формат, недружелюбный громоздкий синтаксис языка шаблонов, необходимость предварительно приводить метаданные к XML для последующей трансформации. | + | Нетипичный для реляционных СУБД формат, недружелюбный громоздкий синтаксис языка шаблонов, необходимость предварительно приводить метаданные к XML для последующей трансформации. |
| | | |
− | С позволения читателя, я не буду приводить примеры использования XSLT в Oracle. Интересующиеся темой могут ознакомиться с [http://docs.oracle.com/database/121/ADXDB/xdb08tra.htm#ADXDB0900 документацией] и [http://www.codeproject.com/Articles/263100/XSL-Code-Generator примерами] генерации кода на других языках с его помощью. | + | С позволения читателя, я не буду приводить примеры использования XSLT в Oracle. Интересующиеся темой могут ознакомиться с [http://docs.oracle.com/database/121/ADXDB/xdb08tra.htm#ADXDB0900 документацией] и [http://www.codeproject.com/Articles/263100/XSL-Code-Generator примерами] генерации кода на других языках с его помощью. |
| | | |
| === Клиентские средства === | | === Клиентские средства === |
| | | |
− | «Клиентские» в данном контексте означает, что код генерируется внешней утилитой, работающей снаружи СУБД (хотя хост-средой может быть сам сервер, на котором поднят инстанс) и имеющей доступ к ее данным как клиент. | + | «Клиентские» в данном контексте означает, что код генерируется внешней утилитой, работающей снаружи СУБД (хотя хост-средой может быть сам сервер, на котором поднят инстанс) и имеющей доступ к ее данным как клиент. |
| | | |
| ==== SQL*Plus ==== | | ==== SQL*Plus ==== |
| | | |
− | SQL*PLus — это стандартная CLI-утилита для работы с СУБД Oracle с небольшим набором собственных команд. Она предоставляет возможность сделать код немного динамичнее благодаря [https://docs.oracle.com/database/121/SQPUG/ch_five.htm#i1211130 переменным подстановки] (substitution variables) и [https://docs.oracle.com/database/121/SQPUG/ch_five.htm#i1211006 вложению фрагментов] (script nesting). | + | SQL*PLus — это стандартная CLI-утилита для работы с СУБД Oracle с небольшим набором собственных команд. Она предоставляет возможность сделать код немного динамичнее благодаря [https://docs.oracle.com/database/121/SQPUG/ch_five.htm#i1211130 переменным подстановки] (substitution variables) и [https://docs.oracle.com/database/121/SQPUG/ch_five.htm#i1211006 вложению фрагментов] (script nesting). |
| | | |
− | В отличие от директив компиляции, переменные подстановки можно использовать вообще в любом месте кода, а значения им могут быть присвоены произвольным запросом. Правда, эти переменные могут быть только строковыми, а в самом SQL*Plus нет даже условного оператора и циклов. Их имитация выглядит весьма пугающе и делает этот инструмент малопригодным для кодогенерации хоть со сколько-нибудь сложной логикой: см. примеры [http://orasql.org/2013/04/09/sqlplus-tips-3-iterators-and-recursions/ итераций] и [http://orasql.org/2013/04/17/sqlplus-tips-4-branching-execution/ ветвления] из блога [http://habrahabr.ru/users/xtender/ @xtender]. Кроме того, кодовая основа может быть доступна SQL*Plus'у только из файлов. | + | В отличие от директив компиляции, переменные подстановки можно использовать вообще в любом месте кода, а значения им могут быть присвоены произвольным запросом. Правда, эти переменные могут быть только строковыми, а в самом SQL*Plus нет даже условного оператора и циклов. Их имитация выглядит весьма пугающе и делает этот инструмент малопригодным для кодогенерации хоть со сколько-нибудь сложной логикой: см. примеры [http://orasql.org/2013/04/09/sqlplus-tips-3-iterators-and-recursions/ итераций] и [http://orasql.org/2013/04/17/sqlplus-tips-4-branching-execution/ ветвления] из блога [http://habrahabr.ru/users/xtender/ @xtender]. Кроме того, кодовая основа может быть доступна SQL*Plus'у только из файлов. |
| | | |
| ===== Достоинства ===== | | ===== Достоинства ===== |
Строка 277: |
Строка 278: |
| == Сторонние проекты == | | == Сторонние проекты == |
| | | |
− | Что предлагает сообщество для решения обозначенной проблемы? Удалось найти лишь несколько проектов по данной теме. | + | Что предлагает сообщество для решения обозначенной проблемы? Удалось найти лишь несколько проектов по данной теме. |
| | | |
| ==== PL/Generator ==== | | ==== PL/Generator ==== |
| | | |
− | Очень старый проект 1998 года от известного евангелиста Стивена Ферштейна для шаблонной генерации PL/SQL-кода, написанный еще под Oracle 7/8. Давно не поддерживается. Использует специальный язык разметки CGML, очень жестко привязанный к специфике Oracle и PL/SQL, что сильно ограничивает область его применения. Для работы генератора требуется доступ к файловой системе сервера (через UTL_FILE) для чтения шаблонов и сохранения результатов генерации, хотя, судя по документации, автономная работа только внутри БД тоже была предусмотрена и, возможно, даже реализована. Скачать проект по-прежнему можно с [http://archive.stevenfeuerstein.com/puter/gencentral.htm сайта автора]. | + | Очень старый проект 1998 года от известного евангелиста Стивена Ферштейна для шаблонной генерации PL/SQL-кода, написанный еще под Oracle 7/8. Давно не поддерживается. Использует специальный язык разметки CGML, очень жестко привязанный к специфике Oracle и PL/SQL, что сильно ограничивает область его применения. Для работы генератора требуется доступ к файловой системе сервера (через UTL_FILE) для чтения шаблонов и сохранения результатов генерации, хотя, судя по документации, автономная работа только внутри БД тоже была предусмотрена и, возможно, даже реализована. Скачать проект по-прежнему можно с [http://archive.stevenfeuerstein.com/puter/gencentral.htm сайта автора]. |
| | | |
| ==== Quest CodeGen Utility ==== | | ==== Quest CodeGen Utility ==== |
| | | |
− | Реинкарнация PL/Generator, развиваемая в 2004–2007 годах. Представляет из себя целую IDE с возможностью генерировать PL/SQL-код из тех же CGML-шаблонов и проносить результат на сервер. Проект давно закрыт, сайт не работает, документации и даже упоминаний в интернете почти не осталось. Удалось найти только предпоследнюю версию на одном софтверном ресурсе. Разобраться с продуктом оказалось непросто, а, учитывая ограниченность CGML, изучать его досконально я не стал. | + | Реинкарнация PL/Generator, развиваемая в 2004–2007 годах. Представляет из себя целую IDE с возможностью генерировать PL/SQL-код из тех же CGML-шаблонов и проносить результат на сервер. Проект давно закрыт, сайт не работает, документации и даже упоминаний в интернете почти не осталось. Удалось найти только предпоследнюю версию на одном софтверном ресурсе. Разобраться с продуктом оказалось непросто, а, учитывая ограниченность CGML, изучать его досконально я не стал. |
| | | |
| ==== iCodeGenerator ==== | | ==== iCodeGenerator ==== |
| | | |
− | Современный проект с открытым кодом на GitHub, позиционирующий себя как БД-центричный шаблонный кодогенератор для любых языков с поддержкой разных СУБД, в том числе Oracle. На поверку оказалось, что шаблонный язык чрезвычайно скуден и, кроме как для краткого документирования содержимого схемы, ни на что не годится. Представляет собой клиентское приложение на .NET. Подключиться к Oracle согласно инструкции так и не получилось. Доступен на [http://www.icodegenerator.net/ своем сайте]. | + | Современный проект с открытым кодом на GitHub, позиционирующий себя как БД-центричный шаблонный кодогенератор для любых языков с поддержкой разных СУБД, в том числе Oracle. На поверку оказалось, что шаблонный язык чрезвычайно скуден и, кроме как для краткого документирования содержимого схемы, ни на что не годится. Представляет собой клиентское приложение на .NET. Подключиться к Oracle согласно инструкции так и не получилось. Доступен на [http://www.icodegenerator.net/ своем сайте]. |
| | | |
| ==== Многочисленные Table API-генераторы ==== | | ==== Многочисленные Table API-генераторы ==== |
| | | |
− | [http://sourceforge.net/projects/plsqlgenpkg/ PL/SQLGenPkg], [http://sourceforge.net/projects/plsqlintgen/ PL/SQL Interface Generator], [http://sourceforge.net/projects/tapigen/ tapiGen], [https://code.google.com/p/tapig/ TAPIG] — узкопрофильные средства для генерации обвязок вокруг таблиц для возможности выполнения операций CRUD через них вместо обычного SQL. | + | [http://sourceforge.net/projects/plsqlgenpkg/ PL/SQLGenPkg], [http://sourceforge.net/projects/plsqlintgen/ PL/SQL Interface Generator], [http://sourceforge.net/projects/tapigen/ tapiGen], [https://code.google.com/p/tapig/ TAPIG] — узкопрофильные средства для генерации обвязок вокруг таблиц для возможности выполнения операций CRUD через них вместо обычного SQL. |
| | | |
− | Все рассмотренные либо давно заброшены, либо слишком узкоспецифичны и решают частные проблемы, нежели являются универсальными инструментами. | + | Все рассмотренные либо давно заброшены, либо слишком узкоспецифичны и решают частные проблемы, нежели являются универсальными инструментами. |
| | | |
| == Создаем свой инструмент == | | == Создаем свой инструмент == |
| | | |
− | Итак, инструмента, который удовлетворял бы широким потребностям промышленной разработки, к сожалению, не нашлось. Поэтому было решено сделать свой. Были выбраны самые полезные возможности из рсенала штатных средств и составлен список фич, необходимых для комфортной работы: | + | Итак, инструмента, который удовлетворял бы широким потребностям промышленной разработки, к сожалению, не нашлось. Поэтому было решено сделать свой. Были выбраны самые полезные возможности из арсенала штатных средств и составлен список фич, необходимых для комфортной работы: |
| | | |
− | * от обычного PL/SQL были взяты способы обращения к БД: выполнение запросов, вызов хранимых функций, получение результата в виде сложносоставных типов — result set'ов; | + | * от обычного PL/SQL были взяты способы обращения к БД: выполнение запросов, вызов хранимых функций, получение результата в виде сложносоставных типов — result set'ов; |
| | | |
− | * от PLSQLCC — возможность писать и хранить управляющие инструкции кода-генератора как plain text прямо в теле хранимых программных единиц, чтобы редактирование в IDE ничем не отличалось от привычной разработки обычного статичного кода; | + | * от PLSQLCC — возможность писать и хранить управляющие инструкции кода-генератора как plain text прямо в теле хранимых программных единиц, чтобы редактирование в IDE ничем не отличалось от привычной разработки обычного статичного кода; |
| | | |
− | * от XSLT — принцип шаблонной генерации: первичность генерируемого кода над кодом-генератором; | + | * от XSLT — принцип шаблонной генерации: первичность генерируемого кода над кодом-генератором; |
| | | |
− | * от SQL*Plus — возможность выполнения на клиенте. | + | * от SQL*Plus — возможность выполнения на клиенте. |
| | | |
− | Возникло еще одно требование, продиктованное инертностью Enterprise-сектора, — совместимость с Oracle как минимум 10-й версии (при текущей 12-й), поскольку она все еще активно используется в production. | + | Возникло еще одно требование, продиктованное инертностью Enterprise-сектора, — совместимость с Oracle как минимум 10-й версии (при текущей 12-й), поскольку она все еще активно используется в production. |
| | | |
| === Выбор движка шаблонов === | | === Выбор движка шаблонов === |
Строка 315: |
Строка 316: |
| Поскольку Oracle имеет [https://docs.oracle.com/database/121/JJDEV/chone.htm#JJDEV13016 встроенную JVM] (ранее известную как Aurora), а Java-мир очень богат на инструменты любого вида, было решено посмотреть в сторону [http://java-source.net/open-source/template-engines Java Template Engines]. | | Поскольку Oracle имеет [https://docs.oracle.com/database/121/JJDEV/chone.htm#JJDEV13016 встроенную JVM] (ранее известную как Aurora), а Java-мир очень богат на инструменты любого вида, было решено посмотреть в сторону [http://java-source.net/open-source/template-engines Java Template Engines]. |
| | | |
− | Практически сразу же определились два кандидата: [https://velocity.apache.org/ Velocity] и [http://freemarker.org/ FreeMarker] — самые популярные шаблонные движки. Изучив их возможности и попрактиковавшись с ними, мы поняли, что Velocity заморожен несколько лет назад, а FreeMarker обладает более обширным [http://freemarker.org/features.html арсеналом фич] и более гибким [http://freemarker.org/docs/index.html хорошо документированным] языком (FTL), продолжает активно развиваться и периодически выпускает новые версии. | + | Практически сразу же определились два кандидата: [https://velocity.apache.org/ Velocity] и [http://freemarker.org/ FreeMarker] — самые популярные шаблонные движки. Изучив их возможности и попрактиковавшись с ними, мы поняли, что Velocity заморожен несколько лет назад, а FreeMarker обладает более обширным [http://freemarker.org/features.html арсеналом фич] и более гибким [http://freemarker.org/docs/index.html хорошо документированным] языком (FTL), продолжает активно развиваться и периодически выпускает новые версии. |
| | | |
− | Судя по его описанию, FreeMarker теоретически позволял интегрироваться с Oracle и «допилить» его таким образом, чтобы из FTL было удобно обращаться к БД. | + | Судя по его описанию, FreeMarker теоретически позволял интегрироваться с Oracle и «допилить» его таким образом, чтобы из FTL было удобно обращаться к БД. |
| | | |
− | Кроме того, Java-приложения можно запускать на клиенте так же успешно, как и внутри СУБД. Это решало бы проблему совместимости с Oracle XE, в котором нет поддержки Java, а также очень старыми версиями Oracle (младше 10), где JVM-версии — ниже 1.4, несовместимые с FreeMarker. | + | Кроме того, Java-приложения можно запускать на клиенте так же успешно, как и внутри СУБД. Это решало бы проблему совместимости с Oracle XE, в котором нет поддержки Java, а также очень старыми версиями Oracle (младше 10), где JVM-версии — ниже 1.4, несовместимые с FreeMarker. |
| | | |
− | === Интеграция с Oracle === | + | === Интеграция с Oracle === |
| | | |
− | Чтобы сделать полноценный инструмент, необходимо было решить три основные проблемы. | + | Чтобы сделать полноценный инструмент, необходимо было решить три основные проблемы. |
− | | + | |
− | # Поселить FreeMarker в СУБД, заставить его там работать и дописать набор классов, расширяющий язык FTL возможностями JDBC.
| + | |
− | | + | |
− | # Научиться хранить шаблоны FTL наиболее удобным и нативным способом, как обычные программные единицы, сделать работу с ними в популярных IDE комфортной.
| + | |
| | | |
| + | # Поселить FreeMarker в СУБД, заставить его там работать и дописать набор классов, расширяющий язык FTL возможностями. |
| + | # Научиться хранить шаблоны FTL наиболее удобным и нативным способом, как обычные программные единицы, сделать работу с ними в популярных IDE комфортной. |
| # Свести к минимуму заботу разработчика о процессинге шаблонов и выполнении сгенерированных скриптов на сервере. | | # Свести к минимуму заботу разработчика о процессинге шаблонов и выполнении сгенерированных скриптов на сервере. |
| | | |
− | Забегая вперед, скажу, что все эти проблемы были успешно решены и родился инструмент FTLDB (названный по аналогии c HTMLDB и XMLDB), который теперь лежит [https://github.com/ftldb/ftldb на GitHub] под [https://www.apache.org/licenses/LICENSE-2.0 лицензией Apache License 2.0]. | + | Забегая вперед, скажу, что все эти проблемы были успешно решены и родился инструмент FTLDB (названный по аналогии c HTMLDB и XMLDB), который теперь лежит [https://github.com/ftldb/ftldb на GitHub] под [https://www.apache.org/licenses/LICENSE-2.0 лицензией Apache License 2.0]. |
| | | |
− | А теперь расскажу подробнее о том, что было сделано. | + | А теперь расскажу подробнее о том, что было сделано. |
| | | |
| ==== Доработка FreeMarker ==== | | ==== Доработка FreeMarker ==== |
| | | |
− | Вся Java-разработка велась с минимальной конкретикой на Oracle, с использованием преимущественно интерфейсов JDBC из java.sql.*. Это позволило в дальнейшем использовать инструмент не только для Oracle, но и для других СУБД. В тех местах, где без специфики было не обойтись (серверное выполнение на встроенной JVM), были оставлены возможности расширения и для других СУБД, потенциально способных исполнять Java-код самостоятельно (например, PostgreSQL + PL/Java). | + | Вся Java-разработка велась с минимальной конкретикой на Oracle, с использованием преимущественно интерфейсов JDBC из java.sql.*. Это позволило в дальнейшем использовать инструмент не только для Oracle, но и для других СУБД. В тех местах, где без специфики было не обойтись (серверное выполнение на встроенной JVM), были оставлены возможности расширения и для других СУБД, потенциально способных исполнять Java-код самостоятельно (например, PostgreSQL + PL/Java). |
| | | |
− | Язык FTL был дополнен методами создания JDBC-соединений (или использования текущего соединения при работе на server-side), выполнения в рамках них SQL-запросов и программных вызовов, работы с результатами их выполнения в родном для FTL виде. Также были добавлены некоторые утилитарные методы для FTL, загрузчик шаблонов из БД с помощью произвольного вызова и main-методы для запуска движка из CLI и PL/SQL. | + | Язык FTL был дополнен методами создания JDBC-соединений (или использования текущего соединения при работе на server-side), выполнения в рамках них SQL-запросов и программных вызовов, работы с результатами их выполнения в родном для FTL виде. Также были добавлены некоторые утилитарные методы для FTL, загрузчик шаблонов из БД с помощью произвольного вызова и main-методы для запуска движка из CLI и PL/SQL. |
| | | |
| ==== Хранение шаблонов ==== | | ==== Хранение шаблонов ==== |
| | | |
− | С помощью директив PLSQLCC был придуман простой и элегантный способ хранения шаблонов в БД в телах хранимых программных единиц. Используя директиву $if $then $end с явным ложным условием, в теле любого пакета можно создать «контейнер», невидимый компилятору, в который можно поместить как plain text вообще любой код, а в частности — шаблоны FTL. Это позволяет очень удобно редактировать код в IDE (с сохранением подсветки и code assist фич) и устанавливать его на сервер так же, как обычные пакеты (к слову, проносить их в виде CLOB было бы не так тривиально). | + | С помощью директив PLSQLCC был придуман простой и элегантный способ хранения шаблонов в БД в телах хранимых программных единиц. Используя директиву $if $then $end с явным ложным условием, в теле любого пакета можно создать «контейнер», невидимый компилятору, в который можно поместить как plain text вообще любой код, а в частности — шаблоны FTL. Это позволяет очень удобно редактировать код в IDE (с сохранением подсветки и code assist фич) и устанавливать его на сервер так же, как обычные пакеты (к слову, проносить их в виде CLOB было бы не так тривиально). |
| | | |
| Выглядит это примерно так: | | Выглядит это примерно так: |
Строка 363: |
Строка 362: |
| /</code-sql> | | /</code-sql> |
| | | |
− | Очень похоже на хранимые функции в PostgreSQL :) | + | Очень похоже на хранимые функции в PostgreSQL :) |
| | | |
| ==== Процессинг шаблонов ==== | | ==== Процессинг шаблонов ==== |
| | | |
− | Для извлечения шаблонов из таких контейнеров был написан утилитарный пакет, позволяющий обращаться к ним по имени содержащего их объекта Oracle и возвращающий их в виде CLOB. Этот пакет используется загрузчиком шаблонов в FreeMarker. | + | Для извлечения шаблонов из таких контейнеров был написан утилитарный пакет, позволяющий обращаться к ним по имени содержащего их объекта Oracle и возвращающий их в виде CLOB. Этот пакет используется загрузчиком шаблонов в FreeMarker. |
| | | |
− | Обработка шаблонов осуществляется движком FreeMarker с использованием наших расширений JDBC для извлечения метаданных, после чего результат генерации возвращаются в вызывающую среду в виде out-потока (client-side) или CLOB (Oracle server-side). | + | Обработка шаблонов осуществляется движком FreeMarker с использованием наших расширений JDBC для извлечения метаданных, после чего результат генерации возвращаются в вызывающую среду в виде out-потока (client-side) или CLOB (Oracle server-side). |
| | | |
− | Кроме того, был написан специальный объектный PL/SQL-тип, в который опционально преобразуется результат обработки шаблона. Он представляет собой контейнер исполняемых выражений, которые можно выводить в output или запускать на инстансе с помощью встроенных методов. Это своеобразный серверный аналог SQL*Plus, позволяющий компоновать множество шаблонов или отдельных выражений в один объект и выполнять его как скрипт. | + | Кроме того, был написан специальный объектный PL/SQL-тип, в который опционально преобразуется результат обработки шаблона. Он представляет собой контейнер исполняемых выражений, которые можно выводить в output или запускать на инстансе с помощью встроенных методов. Это своеобразный серверный аналог SQL*Plus, позволяющий компоновать множество шаблонов или отдельных выражений в один объект и выполнять его как скрипт. |
| | | |
− | === Что получилось и как выглядит === | + | === Что получилось и как выглядит === |
| | | |
− | FTLDB для Oracle распространяется в виде инсталлятора под Windows batch и *nix shell. Устанавливается он как отдельная схема (так рекомендуется, хотя можно ставить и в существующую по соседству с другими объектами). Все объекты создаются в режиме выполнения c правами вызывающего (invoker's rights), что позволяет использовать его из других схем совершенно прозрачно. | + | FTLDB для Oracle распространяется в виде инсталлятора под Windows batch и *nix shell. Устанавливается он как отдельная схема (так рекомендуется, хотя можно ставить и в существующую по соседству с другими объектами). Все объекты создаются в режиме выполнения c правами вызывающего (invoker's rights), что позволяет использовать его из других схем совершенно прозрачно. |
| | | |
− | Для работы на клиенте (с любой СУБД) нужны только jar-архивы FreeMarker и FTLDB из инсталлятора, а также JDBC-драйвер, распространяемый производителем СУБД. | + | Для работы на клиенте (с любой СУБД) нужны только jar-архивы FreeMarker и FTLDB из инсталлятора, а также JDBC-драйвер, распространяемый производителем СУБД. |
| | | |
| Меньше слов — больше примеров! Посмотрите еще раз на демонстрационный фрагмент кода из раздела про DynSQL. | | Меньше слов — больше примеров! Посмотрите еще раз на демонстрационный фрагмент кода из раздела про DynSQL. |
| | | |
− | Вот как то же самое делается с помощью FTLDB на серверной стороне. | + | Вот как то же самое делается с помощью FTLDB на серверной стороне. |
| | | |
− | Сам пакет немного изменился в получении метаданных, а вместо конкатенации скрипта таблицы по кусочкам есть настоящий шаблон: | + | Сам пакет немного изменился в получении метаданных, а вместо конкатенации скрипта таблицы по кусочкам есть настоящий шаблон: |
| | | |
| <code-sql>create or replace package body generator as | | <code-sql>create or replace package body generator as |
Строка 497: |
Строка 496: |
| /</code-sql> | | /</code-sql> |
| | | |
− | А вот так на клиентской. | + | А вот так на клиентской. |
| | | |
− | Шаблон храним в отдельном файле: | + | Шаблон храним в отдельном файле: |
| | | |
| <code-xml><#assign conn = new_connection(template_args[0], template_args[1], template_args[2])/> | | <code-xml><#assign conn = new_connection(template_args[0], template_args[1], template_args[2])/> |
Строка 563: |
Строка 562: |
| <#assign void = conn.close()/></code-xml> | | <#assign void = conn.close()/></code-xml> |
| | | |
− | Вызов генерации и запуск скрипта: | + | Вызов генерации и запуск скрипта: |
| | | |
| <code-bash>java -cp ../java/* ftldb.CommandLine orders.ftl "jdbc:oracle:oci:@orcl" scott tiger 1> orders.sql | | <code-bash>java -cp ../java/* ftldb.CommandLine orders.ftl "jdbc:oracle:oci:@orcl" scott tiger 1> orders.sql |
Строка 569: |
Строка 568: |
| sqlplus scott/tiger@orcl @orders.sql</code-bash> | | sqlplus scott/tiger@orcl @orders.sql</code-bash> |
| | | |
− | Больше примеров использования доступны в демо проекта. Документация API приложена к дистрибутиву. | + | Больше примеров использования доступны в демо проекта. Документация API приложена к дистрибутиву. |
| | | |
| === Другие варианты применения === | | === Другие варианты применения === |
| | | |
− | Поскольку клиентский режим целиком и полностью работает на абстракции JDBC и не имеет Oracle-специфичных «зашивок», FTLDB можно использовать для клиентской генерации скриптов для любой СУБД, предоставляющей JDBC-драйвер. Вообще говоря, FTLDB подходит для генерации произвольных текстов в любой области применения самого FreeMarker, например, документации или web-страниц. | + | Поскольку клиентский режим целиком и полностью работает на абстракции JDBC и не имеет Oracle-специфичных «зашивок», FTLDB можно использовать для клиентской генерации скриптов для любой СУБД, предоставляющей JDBC-драйвер. Вообще говоря, FTLDB подходит для генерации произвольных текстов в любой области применения самого FreeMarker, например, документации или web-страниц. |
| | | |
− | === Настоящее и будущее проекта === | + | === Настоящее и будущее проекта === |
| | | |
| FTLDB сейчас используется в нескольких наших промышленных проектах, поддерживается авторами и имеет планы дальнейшего развития. | | FTLDB сейчас используется в нескольких наших промышленных проектах, поддерживается авторами и имеет планы дальнейшего развития. |
| | | |
− | С удовольствием ждем ваших отзывов, предложений по доработке и pull request'ов. | + | С удовольствием ждем ваших отзывов, предложений по доработке и pull request'ов. |
| + | |
| [[Категория:Виктор Осоловский (Статьи)]] | | [[Категория:Виктор Осоловский (Статьи)]] |
| [[Категория:Хабрахабр (Публикации)]] | | [[Категория:Хабрахабр (Публикации)]] |
| [[Категория:2015 год (Статьи)]] | | [[Категория:2015 год (Статьи)]] |
| [[Категория:CustisWikiToLib]] | | [[Категория:CustisWikiToLib]] |
Текущая версия на 13:12, 24 апреля 2015
Виктор Осоловский, наш разработчик-эксперт, опубликовал в блоге компании на «Хабрахабре» статью, посвященную решению проблемы кодогенерации при разработке приложений для СУБД Oracle. Какие существуют возможности для генерации кода «из коробки»? В чем заключаются достоинства и недостатки клиентских средств и сторонних решений? Каким образом удалось создать свой инструмент для решения проблемы с генерацией кода? Ответы на эти вопросы — в материале «Кодогенерация в 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 существует два механизма исполнения 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'ов.
|
![](/skins/custisru/headline_right.gif) |