Генерация запросов SQL

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

Для автоматического формирования запросов к базе генератор должен знать структуру входящих в нее таблиц, запросы к которым будут создаваться. Эта структура должна быть представлена в памяти программы в удобном для генерации виде.

Рассмотрим пример. Пусть имеются две таблицы, содержащие список чертежей и список оборудования. В таблице чертежей drawing хранится информация о названии чертежа, ее описании и номера последней версии. Таблица оборудования equipment содержит информацию о серийном номере оборудования, наименовании модели и описании оборудования.

Поля таблицы drawing с описаниями

Таблица drawing
id	number	идентификатор
name	varchar2(100 char)	название чертежа
title	varchar2(1000 char)	описание
revision	varchar2(10 char)	номер последней версии (ревизии)

Описание таблицы equipment

Таблица equipment
id	number	идентификатор
serial_number	varchar2(100 char)	серийный номер оборудования
model	varchar2(100 char)	наименование модели
description	varchar2(1000 char)	краткое описание назначения оборудования

Мы будем использовать для ввода данных XML-файлы, где будем хранить имена таблиц и их короткие обозначения. Также будут храниться имя, тип и длина полей таблиц. Заполним данные в XML-файлах drawing.xml и equipment.xml:

<?xml version="1.0" encoding="utf-8" ?>
<table name="drawing" shortname="d">
  <field name="id" type="number" length=""/>
  <field name="name" type="varchar2" length="(100 char)"/>
  <field name="title" type="varchar2" length="(1000 char)"/>
  <field name="revision" type="varchar2" length="(10 char)"/>
</table>
    

<?xml version="1.0" encoding="utf-8" ?>
<table name="equipment" shortname="e">
  <field name="id" type="number" length=""/>
  <field name="serial_number" type="varchar2" length="(100 char)"/>
  <field name="model" type="varchar2" length="(100 char)"/>
  <field name="description" type="varchar2" length="(1000 char)"/>
</table>
    

Чтобы представить таблицы equipment и drawing в памяти приложения, составим модели таблиц и полей в классах. Определим структуру (частный случай класса) Field и класс Table.

struct Field
{
    public string name;
    public string type;
    public string length;
}
    

class Table
{
    public string name;
    public string shortname;
    public List<Field> fields;
}
    

Мы создали представление таблиц и полей базы данных в программе. Кроме этого, нам будет нужна некоторая функциональность, позволяющая считывать данные из файлов XML и создавать объекты классов Field и Table. Для этого добавим в класс Table конструктор, читающий из XML-файла данные и создающий объект Table. Полный код класса Table будет выглядеть так:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

class Table
{
    public string name;
    public string shortname;
    public List<Field> fields;
    public Table(string path)
    {
        //создается объект XML-документа
        XmlDocument reader = new XmlDocument();
        //документ считывается по заданному пути
        reader.Load(path);
        //считывается корневой элемент
        XmlElement elem =reader.DocumentElement;
        //извлекаются имя и короткое имя таблицы
        name = elem.GetAttribute("name");
        shortname = elem.GetAttribute("shortname");
        fields = new List<Field>();
        //цикл по полям таблицы, извлекаются имя,тип и длина каждого поля
        for (int i = 0; i < elem.ChildNodes.Count; i++)
        {
            if (elem.ChildNodes[i].Name == "field")
            {
                XmlElement elemField = (XmlElement)elem.ChildNodes[i];
                Field fld = new Field();
                fld.name = elemField.GetAttribute("name");
                fld.type = elemField.GetAttribute("type");
                fld.length = elemField.GetAttribute("length");
                fields.Add(fld);
            }
        }
    }
}
    

Конструктор Table принимает в качестве входного параметра путь к XML-файлу, в котором содержится описание структуры таблицы. Первым делом объявляется объект типа XmlDocument, в который считывается файл по указанному пути. Потом считываются параметры name и shortname таблицы. Далее в цикле считываются атрибуты полей таблицы. Вся эта информация записывается в соответствующие переменные объекта. Таким образом создается объект класса Table. Этот конструктор мы будем применять практически во всех примерах лекции.

Простые запросы SELECT

Рассмотрим генерацию простого запроса вида "SELECT field1, field2,…fieldN from tableX", применяя созданный только что класс.

Table table = new Table(filepath);
string query = "select ";
for (int i = 0; i < table.fields.Count; i++)
{
    if (i > 0) query += ", ";
    query += table.fields[i].name;
}
query += " from " + table.name;
Output.PutResult(query, resultpath);
    

В первой строке конструктором создается объект класса Table. При вызове конструктора указывается путь к файлу с определениями таблицы. Можно указать путь к файлу drawing.xml или equipment.xml. В следующей строке программы инициализируется переменная query, которая будет содержать текст запроса. Ей присвоено изначальное значение, к которому впоследствии в процессе генерации будет присоединяться остальная часть запроса. Далее в цикле через запятую добавляются названия столбцов таблицы. После завершения цикла к тексту запроса добавляется инструкция from и название таблицы. В последней строке результат запроса выводится в файл, имеющий путь resultpath. Применяется при этом метод класса Output, рассмотренного в предыдущей лекции.

Для таблицы drawing будет выводиться следующий результат:

select id, name, title, revision from drawing
    

а для таблицы equipment такой:

select id, serial_number, model, description from equipment
    

Создание таблицы базы данных

Потребоваться могут запросы не только для выборки данных, но и для создания объектов базы данных (если эти объекты еще не были созданы). Рассмотрим генерацию запросов на создание таблицы базы данных, а потом и на создание последовательности (sequence) Oracle.

Table table = new Table(filepath);
List<string> query = new List<string>();
query.Add("create table " + table.name+"(");
for (int i = 0; i < table.fields.Count; i++)
{
    string text = "\t" + table.fields[i].name + " " + table.fields[i].type + table.fields[i].length;
    if (i < table.fields.Count - 1) text += ",";
    query.Add(text);
}
query.Add(")");
Output.PutResult(query, resultpath);
    

Аналогично предыдущему примеру считывается структура таблицы из файла. Затем формируется запрос согласно имени таблицы, а также имен и типов столбцов таблицы. Заметьте, что при обработке столбцов добавляется символ табуляции "\t" в начало каждой строки. Это сделано для того, чтобы формат выводимого запроса был более удобен для восприятия.

Запрос на создание таблицы drawing.

create table drawing(
	id number,
	name varchar2(100 char),
	title varchar2(1000 char),
	revision varchar2(10 char)
)
    

Запрос на создание таблицы equipment.

create table equipment(
	id number,
	serial_number varchar2(100 char),
	model varchar2(100 char),
	description varchar2(1000 char)
)
    

Уже на этом небольшом примере видно насколько согласованным и единообразным может быть выводимый генератором код.

Создание последовательности Oracle (sequence)

В таблицах equipment и drawing имеются поля id, являющиеся идентификаторами записи в таблице. Для формирования значений этих полей можно применять последовательности. Рассмотрим упрощенный запрос создания последовательности Oracle вида: create sequence seq_name, где seq_name - имя объекта последовательности. Для присвоения следующего значения и увеличения счетчика используется команда seq_name.nextval. Рассмотрим программу для генерации запроса на создание последовательности.

Table table = new Table(filepath);
string query = "create sequence seq_" + table.name;
Output.PutResult(query, resultpath);
    

Для таблицы чертежей будет выводиться следующий запрос.

create sequence seq_drawing
    

А для таблицы оборудования такой.

create sequence seq_equipment
    

Запрос INSERT

Рассмотрим генерацию запросов на вставку новых записей в таблицу. Запрос будет следующего вида.

insert into tableX(field1, field2, …,fieldN)
values (value1, value2, …,valueN)
    

Программа для генерации запроса вставки строки в таблицу

Table table = new Table(filepath);
List<string> query = new List<string>();
string text = "insert into " + table.name + "(";
for (int i = 0; i < table.fields.Count; i++)
{
    text += table.fields[i].name;
    if (i < table.fields.Count - 1) text += ","; else text += ")";
}
query.Add(text);
text = "values " + "(seq_" + table.name + ".nextval";
for (int i = 1; i < table.fields.Count; i++)
{
    text += "p_" + table.fields[i].name;
    if (i < table.fields.Count - 1) text += ","; else text += ")";
}
query.Add(text);
Output.PutResult(query, resultpath);
    

Две строки запроса генерируются в результате работы двух циклов. В этом запросе значение nextval последовательности вставляется в поле id. При использовании запроса внутри приложения в секцию values нужно будет передавать значения некоторых переменных. В данном случае для удобства переменным назначаются имена в виде p_field1, p_field2,…, p_fieldN, где field1, field2,…, fieldN являются именами полей таблицы. Предполагается, что сгенерированный запрос будет применяться внутри процедуры PL/SQL. Если необходимо создавать запросы для применения в другой среде, достаточно немного поменять формат выводимых запросов.

Результат для таблиц drawing и equipment будет такой:

insert into drawing(id,number,title,revision)
values (seq_drawing.nextval,p_number,p_title,p_revision)

insert into equipment(id,number,model,description)
values (seq_equipment.nextval,p_number,p_model,p_description)
    

Генерация запроса на обновление записи таблицы

Теперь рассмотрим генерацию запросов с применением оператора UPDATE. Для таблиц drawing и equipment запрос на обновление строки будет иметь следующий вид.

update drawing set
	name = p_name,
	title = p_title,
	revision = p_revision
where id = p_id

update equipment set
	serial_number = p_serial_number,
	model = p_model,
	description = p_description
where id = p_id
    

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

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

Table table = new Table(filepath);
List<string> query = new List<string>();
string text = "update " + table.name + " set";
query.Add(text);
for (int i = 0; i < table.fields.Count; i++)
{
    if (table.fields[i].name != "id")
    {
        text = "\t" + table.fields[i].name + " = p_" + table.fields[i].name;
        if (i < table.fields.Count - 1) text += ",";
        query.Add(text);
    }
}
query.Add("where id = p_id");
Output.PutResult(query, resultpath);
    

Так же как и для предыдущего примера, в цикле проходим по всем полям таблицы, кроме идентификатора id. В самом конце в условие where добавляется выбор строки по значению идентификатора.

Генерация запроса на удаление строки таблицы

Генерация запроса с применением оператора DELETE будет выглядеть так.

Table table = new Table(filepath);
string query = "delete from " + table.name + " where id = p_id";
Output.PutResult(query, resultpath);
    

Для таблиц drawing и equipment будут сгенерированы такие запросы

delete from drawing where id = p_id

delete from equipment where id = p_id
    

Генерация запроса на выборку всех полей из нескольких таблиц

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

<?xml version="1.0" encoding="utf-8" ?>
<tables>
  <table name="drawing" shortname="d">
    <field name="id" type="number" length=""/>
    <field name="name" type="varchar2" length="(100 char)"/>
    <field name="title" type="varchar2" length="(1000 char)"/>
    <field name="revision" type="varchar2" length="(10 char)"/>
  </table>
  <table name="equipment" shortname="e">
    <field name="id" type="number" length=""/>
    <field name="serial_number" type="varchar2" length="(100 char)"/>
    <field name="model" type="varchar2" length="(100 char)"/>
    <field name="description" type="varchar2" length="(1000 char)"/>
  </table>
  <table name="drawing_equipment" shortname="de">
    <field name="id" type="number" length=""/>
    <field name="drawing_id" type="number" length="" foreign_key_table="d" foreign_key_field="id"/>
    <field name="equipment_id" type="number" length="(100 char)" foreign_key_table="e" foreign_key_field="id"/>
  </table>
</tables>
    

Создадим класс DBStructure, хранящий структуру таблиц в виде списка объектов класса Table. Определим в нем конструктор, выполняющий чтение структуры таблиц по указанному пути к XML-файлу.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

class DBStructure
{
    public List<Table> tables;
    public DBStructure(string path)
    {
        XmlDocument reader = new XmlDocument();
        reader.Load(path);
        XmlElement elem = reader.DocumentElement;
        tables = new List<Table>();
        for (int i = 0; i < elem.ChildNodes.Count; i++)
        {
            Table table = new Table((XmlElement)elem.ChildNodes[i]);
            tables.Add(table);
        }
    }
}
    

Так как мы добавили атрибуты foreign_key_table и foreign_key_field, то класс Table и структура Field тоже изменятся - добавится чтение соответствующих атрибутов. В остальном код класса Table и структуры Field останутся без изменений.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

class Table
{
    public string name;
    public string shortname;
    public List<Field> fields;
    public Table(XmlElement elem)
    {
        name = elem.GetAttribute("name");
        shortname = elem.GetAttribute("shortname");
        fields = new List<Field>();
        for (int i = 0; i < elem.ChildNodes.Count; i++)
        {
            if (elem.ChildNodes[i].Name == "field")
            {
                XmlElement elemField = (XmlElement)elem.ChildNodes[i];
                Field fld = new Field();
                fld.name = elemField.GetAttribute("name");
                fld.type = elemField.GetAttribute("type");
                fld.length = elemField.GetAttribute("length");
                fld.foreign_key_table = elemField.GetAttribute("foreign_key_table");
                fld.foreign_key_field = elemField.GetAttribute("foreign_key_field");
                fields.Add(fld);
            }
        }
    }
}
    

struct Field
{
    public string name;
    public string type;
    public string length;
    public string foreign_key_table;
    public string foreign_key_field;
}
    

Рассмотрим непосредственно саму программу генерации запроса:

using System;
using System.Collections.Generic;
using System.Text;

class Program
{
    static void AllSelectSQL()
    {
        //считывается структура таблиц
        DBStructure dbstructure = new DBStructure(@"A:\input\structure.xml");
        //задаются начальные значения переменных, 
        // в которых будут храниться части запросов,
        // начинающиеся с select, from, where
        string select_query = "select ";
        string from_query = "from ";
        string where_query = "where ";
        List<string> query = new List<string>();
        for (int i = 0; i < dbstructure.tables.Count; i++)
        {
            //к "from" добавляется имя каждой таблицы
            from_query+=dbstructure.tables[i].name + " " + dbstructure.tables[i].shortname;
            if(i < dbstructure.tables.Count-1) from_query+=", ";
            //цикл по полям таблицы
            for (int j = 0; j < dbstructure.tables[i].fields.Count; j++)
            {
                //к "select" добавляется имя каждого поля 
                // вместе с коротким именем таблицы
                select_query += dbstructure.tables[i].shortname + ".";
                select_query += dbstructure.tables[i].fields[j].name + " ";
                if (j < dbstructure.tables[i].fields.Count - 1) select_query += ", ";
                //для каждого внешнего ключа добавляется 
                // соединение таблиц в условие where
                if (dbstructure.tables[i].fields[j].foreign_key_table != "")
                {
                    where_query += dbstructure.tables[i].name + ".";
                    where_query += dbstructure.tables[i].fields[j].name + " = ";
                    where_query += dbstructure.tables[i].fields[j].foreign_key_table + ".";
                    where_query += dbstructure.tables[i].fields[j].foreign_key_field + " and ";
                }
            }
        }
        if (where_query.Length > 6) where_query = where_query.Substring(0, where_query.Length - 5);
        query.Add(select_query);
        query.Add(from_query);
        query.Add(where_query);
        Output.PutResult(query, @"A:\Result\select_all.sql");
    }
    static void Main()
    {
        AllSelectSQL();
    }
}
    

Во внешнем цикле for делается проход по всем таблицам. В этом цикле одновременно формируются тексты частей запроса, начинающиеся на select, from, update. Причем части запроса, которые начинаются с select и update формируются во вложенном цикле, когда делается проход по полям таблиц. В результате работы программы будет выведен следующий запрос:

select d.id , d.name , d.title , d.revision e.id , e.serial_number , e.model , 
 e.description de.id , de.drawing_id , de.equipment_id 
from drawing d, equipment e, drawing_equipment de
where drawing_equipment.drawing_id = d.id and drawing_equipment.equipment_id = e.id
    

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

Применение шаблонов XSLT мы рассмотрим подробно в пятой лекции. Поэтому, если приведенный ниже пример оказался непонятен, можете вернуться к нему после изучения пятой лекции.

Для вывода рассмотренного запроса при помощи XSLT надо добавить следующую строку в указанный выше XML-файл:

<?xml-stylesheet type="text/xsl" href="allselect.xsl"?>
    

И создать XSLT-стиль со следующим содержимым.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
    select 
    <xsl:for-each select="tables/table">
      <xsl:variable name="tshort" select="@shortname"/>
      <xsl:for-each select="field">
        <xsl:value-of select="$tshort"/>
        <xsl:text>.</xsl:text>
        <xsl:value-of select="@name"/>
        <xsl:if test="not(position()=last())">, </xsl:if>
      </xsl:for-each>
      <xsl:if test="not(position()=last())">, </xsl:if>
    </xsl:for-each>
    <br/>
    from
    <xsl:for-each select="tables/table">
      <xsl:value-of select="@name"/>
      <xsl:text> </xsl:text>
      <xsl:value-of select="@shortname"/>
      <xsl:if test="not(position()=last())">, </xsl:if>
    </xsl:for-each>
    <br/>
    where
    <xsl:for-each select="tables/table">
      <xsl:variable name="tshort" select="@shortname"/>
      <xsl:for-each select="field">
        <xsl:if test="@foreign_key_table">
          <xsl:value-of select="$tshort"/>
          <xsl:text>.</xsl:text>
          <xsl:value-of select="@name"/>
          <xsl:text> = </xsl:text>
          <xsl:value-of select="@foreign_key_table"/>
          <xsl:text>.</xsl:text>
          <xsl:value-of select="@foreign_key_field"/>
          <xsl:if test="not(position()=last())"> and </xsl:if>
        </xsl:if>
      </xsl:for-each>
    </xsl:for-each>
    <br/>
  </xsl:template>
</xsl:stylesheet>
    

Результат будет следующим (то есть тем же самым):

select d.id, d.name, d.title, d.revision, e.id, e.serial_number, e.model, 
  e.description, de.id, de.drawing_id, de.equipment_id
from drawing d, equipment e, drawing_equipment de
where de.drawing_id = d.id and de.equipment_id = e.id
    

Как видим, в XSLT генерация из простых шаблонов получается легче.

Генерация SQL при помощи SQL

Напоследок рассмотрим достаточно редкую технику - это генерация запросов SQL с помощью запросов SQL. Рассмотрим следующий запрос:

select
'Grant select on '||table_name||' to public;'
from user_tables where created > sysdate -1;
    

В нем из встроенного представления Oracle берутся названия таблиц, созданных не более чем 24 часа назад (берется значение sysdate-1) и генерируются запросы предоставления доступа на чтение всем пользователям. Результат для одной таблицы будет следующего вида:

Grant select on <имя таблицы> to public;
    

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

Это был пример операции DDL, но можно также генерировать запросы DML. Рассмотрим такой пример:

select
'Insert into all_history(id, object_id, table_name, update_date)'||
'select seq_all_history.nextval, id,'''||table_name||'''||
'from '||table_name||';'
from user_tables where created > sysdate -1;
    

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

Insert into all_history(id, object_id, table_name, update_date)
select seq_all_history.nextval, id,'tbl_name'
from table _name;
    

Вставляются имя таблицы, дата обновления, идентификатор строки в таблице. Также ведется идентификатор общей таблицы. Сгенерированное множество запросов вставки новых записей в таблицу также может быть сохранено в отдельном файле и запущено.

Замена подстроки во всей базе данных

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

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

create or replace function value_exists
        (table_name in varchar2
        ,column_name in varchar2
        ,text in varchar2
        )
return number
as
        cnt number;
begin
        begin
        execute immediate
                'select count(*)'||
                ' from '||table_name||
                ' where lower('||column_name||') like ''%'||lower(text)||'%'''
                into cnt;
        exception when others then
                return 0;
        end;
        return cnt;
end;
/
    

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

А теперь рассмотрим запрос, генерирующий запросы UPDATE. В нем используется встроенное представление COLS базы Oracle, имеющее поля column_name и table_name. Запросы UPDATE генерируются для тех полей, в которых имеется хотя бы одна строка, содержащая заменяемый текст. Если же в таблице по данному полю нет ни одной такой строки, то запрос на обновление для этого поля не генерируется.

select 'update '|| lower(table_name) || ' set '||lower(column_name)||  ' = replace('||lower(column_name)||','
 'value1'',''value2'') where lower('||lower(column_name)||') like ''%value1%'';'
from cols
where cols.data_type like '%CHAR%'
  and value_exists(table_name, column_name, 'value1') > 0
  order by table_name, column_name
    

Генерируются запросы, заменяющие одну подстроку на другую. Просматриваются все поля из представления COLS, которые имеют строковый тип. Если в таблице содержится много полей, содержащих заменяемый текст, то для одной этой таблицы будет сгенерировано несколько запросов. Все запросы будут похожими на следующий запрос:

update equipment set description = replace(description,'value1','value2') where lower(description) like '%value1%';
    

В этом запросе подстрока "value1" заменяется на "value2" в поле description таблицы equipment.

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