Возможности и особенности внутреннего языка запросов (внутреннего SQL)

1. Введение
2. Стандартные операторы SQL (select и single, insert, update, delete)
3. Join на внутреннем SQL, или наглядные и простые объединения таблиц
4. Агрегация и особенности группировки
5. Вложенные запросы
6. Хранимые процедуры, функции и триггеры
7. Особенности типа DataSet и запросы к локальным наборам данных

1. Введение

Для кого предназначена данная статья? Для людей, которые имеют базовые знания по языку sql, и которые только начали ознакомление со средой разработки tmaplatform®.

Приложения, разрабатываемые на tmaplatform®, могут быть перенесены в среду многих популярных Систем Управления Базами Данных (далее СУБД). Для возможности легко осуществить перенос и был разработан внутренний язык запросов платформы (далее внутренний SQL). Внутренний SQL с платформой tmaplatform® образует тот уровень, который располагается между СУБД и прикладной программой. Такая архитектура позволяет разработчику сконцентрироваться на решении определенной задачи, не вникая в особенности СУБД, с которой будет работать его решение. С другой стороны, конечные пользователи программы могут по своим потребностям выбирать СУБД самостоятельно.

Существует несколько способов использования операторов SQL в программах, разрабатываемых на tmaplatform®:

• Непосредственно в коде программы (непосредственные запросы). Рекомендуемый способ, корректность запроса проверяется на этапе ввода (все примеры данной статьи с непосредственными запросами)
• Используя функцию Query (в качестве параметра передается строка с текстом запроса на внутреннем SQL, эту строку можно формировать динамически). Результатом выполнения этой функции будет набор данных
• Используя функцию Query1 (в качестве параметра передается строка с текстом запроса на внутреннем SQL, эту строку можно формировать динамически). А в данном случае функция вернет первую ячейку первой строки данных, значение которой можно присвоить переменной
• Используя функцию DirectQuery – платформа без всякой проверки посылает запрос СУБД. Можно использовать какие-нибудь уникальные возможности конкретного сервера баз данных, но в этом случае будет проблематично перейти на другой сервер. Не рекомендуем использовать эту функцию без необходимости. Параметр функции - строка, содержащая sql запрос.

Следует упомянуть, что в платформе нет операторов DDL (Data Definition Language). Все манипуляции с метаданными БД (создание, изменение и удаление таблиц и полей, индексов, внешних ключей, процедур, триггеров и т.д.) выполнит платформа на основе визуально спроектированной структуры БД в редакторе. Но при необходимости таких манипуляций, всегда можно использовать DirectQuery (не рекомендуется).

2. Стандартные операторы SQL (select и single, insert, update, delete)

Синтаксис операторов манипуляции с данными в платформе стандартный (стандарта SQL92). Перечислим все особенности внутреннего языка запросов с краткими примерами. Во всех примерах используется таблица Table1 с полями id (типа integer, первичный ключ и счетчик) и name (типа string).

Оператор select – возвращает набор данных (тип Dataset).

Procedure OnCreate;
var ds:dataset; 
begin
  ds:=(select id, name from Table1);
end;

Для копирования данных из одной таблицы в другую можно использовать запрос вида (insert into … select…)

Procedure OnCreate;
begin
  (insert into Table1 (name) select name from Table1); //продублирует все записи таблицы
end;

Для объединения нескольких запросов в один набор данных предназначен оператор union all:

select id, name from Table1
union all
select id, name from Tabel1

Оператор single – возвращает первое найденное значение, которое можно присвоить переменной простого типа (например, integer).

Procedure OnCreate;
var i:integer;
begin
  i:=(single id from Table1 order by id); //вернет первое значение поля id в таблице Table1
end;

В качестве параметров в запросах можно использовать переменные программы, например:

Procedure OnCreate;
var i:integer; n:string;
begin
  i:=random((single count(*) from Table1));
  n:=(single name from Table1 where id = :i);
  warning('Случайное имя: ' + n);
end;

Операторы insert, update и delete стандартны, и подробно описывать их нет смысла, остановимся на некоторых особенностях внутреннего языка запросов.

Оператор insert во внутреннем sql возвращает первичный ключ добавленной записи (при условии, что ключ состоит из одного поля таблицы и заполняется счетчиком или последовательностью).

Procedure OnCreate;
var i:integer;
begin
  i:=(insert into Table1 (name) values ('Имя'));
  warning('Ключ добавленной записи: ' + ToStr(i));
end;

Пример запроса с оператором update:

Procedure OnCreate;
var n:string;
begin
  n:='Имя';
  (update Table1 set name = :n); //обновит всю таблицу
end;

Оператор Delete внутреннего языка запросов не работает без условия where, это сделано для защиты от случайного удаления всех записей таблицы.

Procedure OnCreate;
var i:integer;
begin
  i:=random((single count(*) from Table1));
  (delete from Table1 where id = :i); //удалили случайную запись таблицы
end;

3. Join на внутреннем SQL, или наглядные и простые объединения таблиц

В качестве примера будем использовать две таблицы: Master с полями id типа integer, name типа string и таблица Detail c полями id типа integer, name типа string, m_id типа *Master. Классическая связь один ко многим.

Примечание: тип данных *Table – фактически это тип integer, поле этого типа будет указывать на первичный ключ таблицы Table (если не отключено авто создание внешних ключей - платформа создаст соответствующий внешний ключ)

Для объединения нескольких таблиц в один набор данных в платформе используется запрос вида (выполним его в Анализаторе sql запросов, доступного из меню Файл):

select name, m_id.name from Detail

Перед выполнением данного запроса запустим утилиту Монитор (также в меню Файл) и увидим, в каком виде данный запрос передается СУБД:

select t0.Name, t1.Name From Detail t0 left join Master t1 on t1.Id=t0.m_id

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

Примечание: ограничения на количество объединений зависит только от ограничений выбранной СУБД.

4. Агрегация и особенности группировки.

Агрегация относится к таким операциям, когда больший набор строк свёртывается в меньший. Типичные агрегатные функции - Count, Min, Max, Sum и Avg. Типичные агрегационные запросы возвращают одну строку с результатом выполнения функции. Для группировки строк по определенным полям используется оператор Group By. С особенностями этого оператора нам надо ознакомиться подробнее, так как его реализация немного отличается от стандарта. В качестве примера возьмем пример из предыдущего раздела данной статьи, но добавим поле Summa типа Currency в таблицу Detail:

Нельзя использовать напрямую механизм внутреннего join в возвращаемых значениях при использовании группировки:

select name, m_id.id from Detail group by name // так делать нельзя

Для обхода этого ограничения надо переписать запрос в следующий вид:

select name, master_id from Detail group by name, m_id.id as master_id //а вот так делать можно

Однако внутри агрегирующих функций использование механизма внутреннего join допустимо:

select name, sum(m_id.id) from detail group by name //так будет работать

Вложенные запросы и группировка. При использовании Group By в запросах, вы не можете использовать в возвращаемых значениях (и в условии после группировки) вложенные запросы. Вы можете использовать подзапросы в возвращаемых значениях только внутри агрегационных функций (min, max, avg, sum) и в конструкции Group By. Например:

select name, (select id from master where id = m_id) from detail group by name //так делать нельзя

Перепишем запрос в следующий вид:

select name, example from detail group by name, (select id from master where id = .m_id) as example //а вот так делать можно

Обратим внимание на еще одну особенность внутреннего Sql, которую использовали в последнем запросе, об этом в следующем разделе.

5. Вложенные запросы.

Для обращения к полям внешнего запроса из вложенного можно использовать запись следующего вида:

select name, (select name from master where id = .m_id) from detail //пример вложенного запроса

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

6. Хранимые процедуры, функции и триггеры.

Хранимые процедуры, функции и триггеры – программы на внутреннем языке СУБД (T-SQL, PL/SQL и т.д.), которые хранятся и выполняются на сервере БД. Платформа предоставляет возможность писать эти программы на языке платформы для независимости от какой-либо СУБД. Рассмотрим их особенности. Для начала синтаксис:

Процедура:

Procedure имя ( { [var] { параметр , } : тип ; } );
[
Var
  { { переменная , } : тип ; }
]
Begin
  { Команда ; }
End;

Функция:

Function имя ( { { параметр , } : тип ; } ) : тип;
[
Var
  { { переменная , } : тип ; }
]
Begin
  { Команда ; }
End;

Триггер:

[
Var
  { { переменная , } : тип ; }
]
Begin
  { Команда ; }
End;

Поддерживаются следующие типы данных: Integer, String, Datetime, Currency, Float, Boolean.

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

Для вызова хранимой процедуры используются команда (Call имя процедуры). Хранимую процедуру или функцию можно вызвать из SQL-запроса, как любую стандартную SQL-функцию.

Примечание: в платформе нельзя вызвать процедуру, имеющую переменные параметры (var-параметры) Однако вы можете вызывать такие процедуры внутри других процедур и функций. Например:

Procedure Stored1(a,b:Integer; Var x:Float);
Begin
  x := 1.3 + a*b
End;

Function Stored2(a:Integer) : Float;
Begin
  (Call Stored1(2, :a, :Result))
End;

Возможности языка платформы для написания хранимых процедур, функций и триггеров:

• Foreach (Select ...) In Переменная Do Команда;//Выполнить запрос Select. И для каждой строки результата выполнить команду
• While Условие Do Команда;//цикл
• Break, Continue;//для прерывания, продолжения цикла
• If Условие Then Команда1 [ Else Команда2];
• Exit;//для выхода из процедуры, функции или триггера
• (insert/delete/update…);//манипуляция с данными
• DirectQuery(string);//если надо воспользоваться особенностями какой-то СУБД (не рекомендуем)
• Переменная:=значение; Result:=значение;//установить значение переменной и результат функции
• Raise(String);//вызвать исключение

7. Особенности типа DataSet и запросы к локальным наборам данным.

Любые запросы выбора записей таблицы (select…) в платформе возвращают набор данных (тип данных DataSet):

Procedure OnCreate;
Var Ds:DataSet;
Begin
  Ds:=(select id, name from Table1);
  Ds:=Query(‘select id, name from Table1’);
  Ds:=DirectQuery(‘select id, name from Table1’); //все три запроса вернут одинаковые наборы данных
End;

Работа с объектом Dataset во многом аналогична массиву:

Procedure OnCreate;
Var
  Ds : Dataset;
  i : integer;
Begin
  Ds := (Select Id, Name From Table1);

  For i:=0 to Ds.Count-1 Do
    Warning(Ds[i, 1] As String);
End;

Если заранее известна структура DataSet, удобнее использовать типизированные наборы данных, для которых доступна конструкция языка foreach:

Type
  DSRecord = Record
    Id:integer;
    Name : String;
  End;

Procedure OnCreate;
Var Ds:DataSet of DSRecord;
Begin
  Ds:=(select id, name from Table1);
  ForEach R in Ds do Alert(R.Name, '');
End;

Можно написать этот пример более коротким способом:

Procedure OnCreate;
Var Ds:DataSet of Record
         id:integer;
         name:string;
       end;
Begin
  Ds:=(select id, name from Table1);
  ForEach R in Ds do Alert(R.Name, '');
End;

Преимущества и наглядность использования типизированных наборов данных очевидна.

Ну и самая главная особенность наборов данных, что к ним можно писать запросы на внутреннем SQL. Лучше всего это продемонстрировать на примере:

Procedure OnCreate;
Var
  Data : Dataset Of Record Name:String; Age : Integer; End;
  Data1 : Dataset Of Record X:String; Z : Integer; End;
  i : Integer;
Begin
  For i:=0 To 20 Do
  Begin
    Data.Append;
    Data[i].Name := Chr(Ord('A')+Random(26));
    Data[i].Age := Random(4)+1;
  End;
    
  Data1 := (Select '['+Name+Name+']', Age From :Data Where Age<>2 Order By -Age, Name);

  ForEach E In Data1 Do
    Warning(E.X+' '+ToStr(E.Z));
  Warning('---');
End;

© RTIT