Создание сайтов: лендинг, визитка, интернет магазин. Продвижение, поддержка, реклама.

Эта статья будет рассказывать как организовать легкое проектирование бизнес логики веб-сервиса в базе данных на встроенном PL-SQL.
Я расскажу как сделать простой сервис ретранслятор для фронтенда (пример будет на php), а так же как легко проектировать в базе аналог API и регистрировать это для ретрансляции фронтенду.

PS: пример бекенда и фронтенда будет на PHP, база данных firebird. Но это не обязательно, можно использовать любой язык программирования и любую БД.

PS: Прошу шапками не закидывать. Знаю, сейчас это не популярно, в силу определенных общественных причин, специалистов по PHP или Python на рынке больше чем SQL-программистов, они стоят дешевле, все любят ОРМ. Считается что при закладке логики в базе сложнее организовать микросервсиную архитектуру. Go компилируется и должен работать быстрее чем БД. Популярно закладывать бизнес логику в фреймворках на php и т.п.
Причин много и у всех есть аргументы и с одной и с другой стороны, и абсолютно по каждому можно глубоко поспорить.
Но эта статья для тех, кто хочет задавать бизнес логику именно в базе, считая что это проще и более эффективно. Здесь нет цели пропагандировать такой способ. Отвечать на комментарии призывающие поднять срач по поводу что лучше или хуже — не буду.

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

CREATE TABLE DFM_PROC (
    ID           INTEGER NOT NULL,
    NAME         VARCHAR(70) NOT NULL,
    DISCRIPTION  VARCHAR(1000)
);

Например:

Схема следующая:

Здесь мы будем рассматривать как организовать работу микросервиса «Service 
for interface».

Пример фронтенда приведу на php

function ser1($proc_id,$json)
{
//Формируем запрос к микросервису
$post='hash='.$_SESSION['sess_id'].'&user_id='.$_SESSION['id_user'].'&proc_id='.$proc_id.'&json='.base64_encode($json);

//Адрес микросервиса
$url = 'http://192.168.128.1/ser.php';
	 
$ch = curl_init();
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_POST , true);
    curl_setopt($ch, CURLOPT_POSTFIELDS ,$post);
    $result = curl_exec($ch);
    $arr_res=json_decode($result,true);			
curl_close($ch);
	
return $arr_res;
}

//В фронтенде например при обработке нажатия кнопки формируем запрос к бекенду
//В данном примере будет создаваться новый профиль юзера
//Процедура 4 - добавляет новый профиль в нашем примере
if (isset($_POST['go_new_prof']))
{
    unset($arr);
    $arr['0']=$_SESSION['id_user'];
    $arr['1']=(int)$_GET['tp'];
    $arr['2']=(int)$_POST['lang_list'];
    $arr_prof=ser1(4,json_encode($arr));
}

Пример вызываемой процедуры на SQL

create or alter procedure DFM_PROF_ADD (
    USER_ID smallint,
    TYPE_ varchar(10),
    LANG smallint)
as
begin

  INSERT INTO DFM_PROFILES (USER_ID, TYPE_, LANG)
    VALUES (:USER_ID, :TYPE_, :LANG);
  suspend;

end

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

<?php

$proc_id=(int)$_POST['proc_id'];
$json= base64_decode($_POST['json']);

$arr_json = json_decode($json,true);

//Валидируем JSON
switch (json_last_error()) {
   case JSON_ERROR_NONE:
      $err = null;
   break;
   case JSON_ERROR_DEPTH:
      $err = 'Достигнута максимальная глубина стека';
   break;
   case JSON_ERROR_STATE_MISMATCH:
      $err = 'Некорректные разряды или не совпадение режимов';
   break;
   case JSON_ERROR_CTRL_CHAR:
      $err = 'Некорректный управляющий символ';
   break;
   case JSON_ERROR_SYNTAX:
      $err = 'Синтаксическая ошибка, не корректный JSON';
   break;
   case JSON_ERROR_UTF8:
      $err = 'Некорректные символы UTF-8, возможно неверная кодировка';
   break;
   default:
      $err = 'Неизвестная ошибка';
   break;
}

//Выполняем только если нет ошибки JSON
if ($err==null) 
{
   
   //Перед всем этим кодом авторизируйте юзера и задайте его ID
   $user_id=1;

   //Получаем название процедуры по ее ID
   $sql_proc = "select p.name from DFM_PROC p where p.id=".$proc_id;
   $res_proc = ibase_query($dbh, $sql_proc);
   $prom_proc = ibase_fetch_row($res_proc);
   
   //Начинаем формировать текст запроса в базу
   $sql_in='select ';
   
   //Получаем исходящие параметры процедуры, 
   //в служебной таблице RDB$PROCEDURE_PARAMETERS содержится название входящих и исходящих параметров процедуры
   $sql = 'select pp.rdb$parameter_number, pp.rdb$parameter_name from DFM_PROC p
left join RDB$PROCEDURE_PARAMETERS pp on pp.rdb$procedure_name=UPPER(p.name)
where p.id='.$proc_id.' and pp.rdb$parameter_type=1 --исходящие
order by pp.rdb$parameter_number';
   $res = ibase_query($dbh, $sql);
   $i_cou_out=0;
   while($prom = ibase_fetch_row($res))
   {
      //p. - это будет ниже мнемоника процедуры
      $i_cou_out++;
      if ($prom[0]>0) $sql_in.=','; 
      $sql_in.='p.'.$prom[1];
      
      $name_out[$prom[0]]=$prom[1];
   }
   
   //После того как сформировали строку с параметрами идем дальше в формировании запроса - вбиваем название процедуры
   $sql_in.=' from '.$prom_proc[0];
   
   //Если ответа быть не должно, а чисто выполнение процедуры, то делаем execute procedure
   if ($i_cou_out==0) $sql_in='execute procedure '.$prom_proc[0];
   
   //заполняем входящие параметры с учетом типа данных
   $sql = 'select
pp.rdb$parameter_number,
pp.rdb$parameter_name,
case
   when f.rdb$field_type=7 then 1 --smalint
   when f.rdb$field_type=8 then 2 --integer
   when f.rdb$field_type=16 and f.rdb$field_scale=0 then 3 --bigint
   when f.rdb$field_type=16 and f.rdb$field_scale<0 then 4 --frloat
   when f.rdb$field_type=12 then 5 --date
   when f.rdb$field_type=13 then 6 --time
   when f.rdb$field_type=35 then 7 --timestamp
   when f.rdb$field_type=37 then 8 --varcahr
end,
f.rdb$field_type, --тип данных
f.rdb$character_length, --длина текста
f.rdb$field_precision, --целых знаков
f.rdb$field_scale --дробных знаков
from DFM_PROC p
left join RDB$PROCEDURE_PARAMETERS pp on pp.rdb$procedure_name=UPPER(p.name)
left join RDB$FIELDS f on f.rdb$field_name=pp.rdb$field_source
where p.id='.$proc_id.' and pp.rdb$parameter_type=0 --входящие
order by pp.rdb$parameter_number';
   $res = ibase_query($dbh, $sql);
            
   $i_cou=0;
   while($prom = ibase_fetch_row($res))
   {
      $i_cou++;
      if ($prom[0]>0)  $sql_in.=','; else $sql_in.='(';
      
      
      if (($prom[2]==5)or($prom[2]==6)or($prom[2]==7)or($prom[2]==8))
         //Обрабатываем параметры где нужны кавычки
         //Елси пришла пустота подставляем null
         if ($arr_json[$prom[0]]=='')
            $sql_in.="null";
         else
            $sql_in.="'".($arr_json[$prom[0]])."'";
      else
         //Обрабатываем параметры без кавычек
         if ($arr_json[$prom[0]]=='')
            $sql_in.="null";
         else
            $sql_in.=($arr_json[$prom[0]]);
      
   }
   
   //Закрываем входящие параметры процедуры
   if ($i_cou_out==0)
      {if ($i_cou>0) $sql_in.=')';}
   else
      {if ($i_cou>0) $sql_in.=') p'; else $sql_in.=' p';}
      //Тут задали мнемонику p. для исходящих параметров
   
   //Выполняем процедуру
   $res_in = ibase_query($dbh, $sql_in);
   
   
   if ($i_cou_out==0)
   {
      //Задаем ответ фронтенду если execute procedure
      $json_out='{
"error_json":"",
"error_code_json":"",
"result":"выполнено execute procedure",
"result_code":0
}'; 
   }
   else
   {
      //Заполняем в json ответ фронтенду
      $i_json=0;
      $json_out='{';
      while($prom_in = ibase_fetch_row($res_in))
      {
         if ($i_json>0) $json_out.=',';
         $json_out.='"'.$i_json.'":{';
         foreach($prom_in as $k => $v)
         {
            if ($k>0) $json_out.=',
';
            $json_out.='"'.trim($name_out[$k]).'":"'.$v.'"';
         }
         $json_out.='}';
         $i_json++;
      }
      $json_out.='}';
   }
   
   //Если процедура была выполнена с ошибкой - отправляем код ошибки, если нет, то результат
   if (ibase_errmsg()=='')
   {
      //Результат для фронтенда
      echo $json_out;   
   }
   else
   {
      //Код ошибки для фронтенда
      $err_json='{
"error_json":"'.$err.'",
"error_code_json":'.json_last_error().',
"result":"'.str_replace('"',''',ibase_errmsg()).'",
"result_code":2,
"sql_err":"'.$sql_in.'"}';
      echo $err_json;
   }
   
            
}
else 
{
   //Код ошибки валидации входящего JSON
   $err_json='{
"error_json":"'.$err.'",
"error_code_json":'.json_last_error().',
"result":"Ошибка json",
"result_code":3
}';
   echo $err_json;   
   
}

?>

Что получаем в итоге.

1) Микросервис-рестранслятор пишем 1 раз. Все остальное проектирование архитектуры бекенда происходит в базе. В PHP (или Go, Python- на чем будет микросервис) мы больше не лезем.
Например понадобилась новая фишка на сайте — делается фронтенд, делается процедура. Процедура регистрируется в табличке и фронтенд по ее регистрационному номеру обращается к API микросервиса. ВСЕ.

Рекомендую микросервис-ретранслятор писать на Go как на компилируемом и существенно более быстром. Преимущество в том — что это надо написать только 1 раз и программа не сложна. При этом это будет высоконагруженный элемент, к которому происходят интенсивные обращения.
Библиотека для подключения firebird к goland: https://github.com/nakagami/firebirdsql

2) Вся обработка уходит в процедуры — мы получаем компилированные элементы, которые обрабатывают все на уровне базы данных и выдают РЕЗУЛЬТАТ. Т.е. если операция состоит из нескольких select, insert, update и т.п. мы не гоняем данные по сети к бекенду, а сразу обрабатываем в базе.
Плюс процедура — компилированный элемент с сформированным планом запроса. На это тоже ресурсы не тратятся.

3) Микросервис ретарнслятор чтобы сформировать процедуру обращается в базу данных 4 раза.
1. Получает ее название
2. Получает ее исходящие параметры
3. Получает ее входящие параметры
4. Выполняет процедуру
4 — раза, потому что рассмотрели универсальный способ. Это можно сократить до одного раза.

Как:
1. Это можно хранить локально в тектовике на веб сервере например. Периодически просто дергая эту таблицу и обновляя когда добавляется новое.
2,3. Аналогично можно хранить локально, дергая все это когда что-то обновляется.

4) Не весь бекенд можно сделать на базе данных! Это существенная часть всего, но надо руководствоваться соображениями целесообразности.
Например какие-нибудь чаты, рассылки и т.п. стороны жизни веб-сервиса конечно надо делать отдельными микросервисами на языке программирования, наиболее удобного для этого. Не надо всю логику веб-сверсиса перенсить в базу во чтобы это ни стало! Надо переносить только ту часть, которая удобна для этого!