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

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

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

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

Мы будем использовать для ввода данных 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)