При написании SQL кода для разворачивания БД и написании хранимых процедур порой очень нехватает простейшего макроязыка. Простыми макросами можно заменять сложные похожие выражения, последовательности операторов SQL или даже из множества файлов собирать один скрипт для разворачивания БД.
Несмотря на то, что наиболее развитым макроязыком является m4, многие разрабочики его не знают. Поэтому был выбран более простой, но многим знакомый препроцессор C - cpp из состава GCC.
Все дальнейшие примеры верны для Linux с установленными GCC и Perl. К сожалению, Windows под рукой у меня нет, но проблем с установкой там GCC и Perl возникнуть не должно.
Чтобы обойти эти ограничения был написан простейший скрипт на Perl
#!bin\perl
open INFILE, $ARGV[0];
if ( $#ARGV>0 ) {
open OUTFILE, '>', $ARGV[1];
}
LINE:
while (<INFILE>) {
s/(`\|)|(`\[)|(`\])|(`\{)|(`\})/
if (length($2)>0 | length($3)>0) {chr(0x22)}
elsif (length($4)>0 | length($5)>0) {chr(0x27)}
else {''}/eg;
} continue {
if ( $#ARGV>0 ) {
print OUTFILE;
} else {
print;
}
}
Я не знаток Perl, так что специалисты наверняка найдут в нем множество огрехов. Но свои функции он выполняет, производя в тексте замены в соответствии со следующей таблицей:
А в самой директории создадим следующий файл:
#include "INC/_macros_list.sql" // глобальные макроопределения
#include "SYS/_before_list.sql" // системные скрипты выполняемые в начале
#include "TYPE/_types_list.sql" // список файлов типов данных
#include "CRE/_tables_list.sql" // список файлов таблиц
#include "VIEW/_views_list.sql" // список файлов представлений
#include "PROC/_procedures_list.sql" // список файлов процедур и функций
#include "SYS/_after_list.sql" // системные скрипты выполняемые в конце
Каждый из перечисленный файлов может быть либо пустым, либо содержать строки #include, включающие необходимые файлы уже непосредственно макросов или SQL скриптов.
В качестве примера использования рассмотрим, как можно упростить журналирование исключений. Создадим в директории CRE файл SVC_ExecutionLog.sql
#define OBJECT_NAME SVC_ExecutionLog
CREATE TABLE IF NOT EXISTS SQL_DB_INSTANCE.OBJECT_NAME (
Id serial PRIMARY KEY,
LogTime timestamp DEFAULT clock_timestamp(),
PID integer DEFAULT pg_backend_pid(),
Schema text DEFAULT current_schema,
SourceName text DEFAULT current_query(),
LogLevel smallint DEFAULT 0, // 0 - Notice, 4 - Info, 8 - Warning, 12 - user error, 16 - user severe error, 32 - SQL error
SessionId integer NOT NULL,
IsStart boolean NULL,
IsFinish boolean NULL,
ParameterId smallint NULL,
SQLState text NULL,
SQLColumn text NULL,
SQLConstraint text NULL,
SQLDataType text NULL,
SQLTable text NULL,
SQLSchema text NULL,
SQLMessage text NULL,
SQLDetail text NULL,
SQLHint text NULL,
SQLContext text NULL,
LogMessageId integer NULL,
LogMessage text NULL,
LogIntData bigint NULL,
LogDateData timestamp NULL,
LogIntervalData interval NULL,
LogTextData varchar NULL,
LogNumData float8 NULL,
LogBooleanData boolean NULL,
LogTableContent text NULL,
LogIntervalms integer NULL
);
CREATE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, LogTime);
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Parmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, ParameterId)
WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_SessionParmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (ParameterId, SessionId)
WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Initialized_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsStart;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Finished_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsFinish;
ALTER TABLE SQL_DB_INSTANCE.OBJECT_NAME OWNER TO postgres;
GRANT SELECT, INSERT ON SQL_DB_INSTANCE.OBJECT_NAME TO some_user;
#undef OBJECT_NAME
и добавим в файл CRE/_tables_list.sql строку
#include "SVC_ExecutionLog.sql"
А директории INC создадим файл LogSQLException.inc
#define SVC_LOG_SQL_EXCEPTION_AND_RETURN \
EXCEPTION WHEN OTHERS THEN \
GET STACKED DIAGNOSTICS proc.sql_state=RETURNED_SQLSTATE, proc.sql_column=COLUMN_NAME, proc.sql_constraint=CONSTRAINT_NAME, \
proc.sql_datatype=PG_DATATYPE_NAME, proc.sql_table=TABLE_NAME, proc.sql_schema=SCHEMA_NAME, proc.sql_message=MESSAGE_TEXT, \
proc.sql_detail=PG_EXCEPTION_DETAIL, proc.sql_hint=PG_EXCEPTION_HINT, proc.sql_context=PG_EXCEPTION_CONTEXT; \
INSERT INTO SQL_DB_INSTANCE.SVC_ExecutionLog (LogLevel, SessionId, LogMessage, \
SQLState, SQLColumn, SQLConstraint, SQLDataType, SQLTable, SQLSchema, SQLMessage, SQLDetail, SQLHint, SQLContext) \
VALUES (32, session_id, 'SQL error '||proc.sql_message, \
proc.sql_state, proc.sql_column, proc.sql_constraint, proc.sql_datatype, proc.sql_table, proc.sql_schema, \
proc.sql_message, proc.sql_detail, proc.sql_hint, proc.sql_context); \
COMMIT; \
RAISE WARNING 'SQL error %', sql_message; \
RETURN 32; \
END;
Так же включим его в файл INC/_macros_list.sql строкой #include, как сделали выше.
Теперь где-нибудь в хранимой процедуре, вызываемой каким-то сервисом мы можем использовать следующую конструкцию:
BEGIN
<какие-то DDL или DML предложения>
SVC_LOG_SQL_EXCEPTION_AND_RETURN
Подразумевается, что при запуске cpp указывается параметр -D SQL_DB_INSTANCE=schema_name, а результат работы cpp обрабатывается приведенным выше Perl скриптом.
Если тема будет интересна, могу привести в следующей статье ряд более сложных и практически интересных примеров использования препроцессора.
habr.com
Несмотря на то, что наиболее развитым макроязыком является m4, многие разрабочики его не знают. Поэтому был выбран более простой, но многим знакомый препроцессор C - cpp из состава GCC.
Все дальнейшие примеры верны для Linux с установленными GCC и Perl. К сожалению, Windows под рукой у меня нет, но проблем с установкой там GCC и Perl возникнуть не должно.
Обходим ограничения C препроцессора
Использовать макросы хочется везде, но препроцессор C не обрабатывает то, что находится между одинарными и двойными скобками. Так же в нем не всегда удобно пользоваться конкатенацией.Чтобы обойти эти ограничения был написан простейший скрипт на Perl
#!bin\perl
open INFILE, $ARGV[0];
if ( $#ARGV>0 ) {
open OUTFILE, '>', $ARGV[1];
}
LINE:
while (<INFILE>) {
s/(`\|)|(`\[)|(`\])|(`\{)|(`\})/
if (length($2)>0 | length($3)>0) {chr(0x22)}
elsif (length($4)>0 | length($5)>0) {chr(0x27)}
else {''}/eg;
} continue {
if ( $#ARGV>0 ) {
print OUTFILE;
} else {
print;
}
}
Я не знаток Perl, так что специалисты наверняка найдут в нем множество огрехов. Но свои функции он выполняет, производя в тексте замены в соответствии со следующей таблицей:
Искомая комбинация символов | Замена |
`| | (пустая строка - конкатенация) |
`[ или `] | " (двойные кавычки) |
`[ или `] | ' (одинарная кавычка) |
Пример использования
Создадим директорию проекта под любым именем, а в ней несколько директорий:Директория | Назначение |
CRE | DDL файлы таблиц |
INC | включаемые файлы препроцессора |
PROC | файлы хранимых процедур и функций |
SYS | файлы системных скриптов |
TYPE | файлы типов данных |
VIEW | файлы представлений |
#include "INC/_macros_list.sql" // глобальные макроопределения
#include "SYS/_before_list.sql" // системные скрипты выполняемые в начале
#include "TYPE/_types_list.sql" // список файлов типов данных
#include "CRE/_tables_list.sql" // список файлов таблиц
#include "VIEW/_views_list.sql" // список файлов представлений
#include "PROC/_procedures_list.sql" // список файлов процедур и функций
#include "SYS/_after_list.sql" // системные скрипты выполняемые в конце
Каждый из перечисленный файлов может быть либо пустым, либо содержать строки #include, включающие необходимые файлы уже непосредственно макросов или SQL скриптов.
В качестве примера использования рассмотрим, как можно упростить журналирование исключений. Создадим в директории CRE файл SVC_ExecutionLog.sql
#define OBJECT_NAME SVC_ExecutionLog
CREATE TABLE IF NOT EXISTS SQL_DB_INSTANCE.OBJECT_NAME (
Id serial PRIMARY KEY,
LogTime timestamp DEFAULT clock_timestamp(),
PID integer DEFAULT pg_backend_pid(),
Schema text DEFAULT current_schema,
SourceName text DEFAULT current_query(),
LogLevel smallint DEFAULT 0, // 0 - Notice, 4 - Info, 8 - Warning, 12 - user error, 16 - user severe error, 32 - SQL error
SessionId integer NOT NULL,
IsStart boolean NULL,
IsFinish boolean NULL,
ParameterId smallint NULL,
SQLState text NULL,
SQLColumn text NULL,
SQLConstraint text NULL,
SQLDataType text NULL,
SQLTable text NULL,
SQLSchema text NULL,
SQLMessage text NULL,
SQLDetail text NULL,
SQLHint text NULL,
SQLContext text NULL,
LogMessageId integer NULL,
LogMessage text NULL,
LogIntData bigint NULL,
LogDateData timestamp NULL,
LogIntervalData interval NULL,
LogTextData varchar NULL,
LogNumData float8 NULL,
LogBooleanData boolean NULL,
LogTableContent text NULL,
LogIntervalms integer NULL
);
CREATE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, LogTime);
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Parmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId, ParameterId)
WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_SessionParmeters_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (ParameterId, SessionId)
WHERE ParameterId IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Initialized_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsStart;
CREATE UNIQUE INDEX IF NOT EXISTS OBJECT_NAME`|_Session_Finished_Idx ON SQL_DB_INSTANCE.OBJECT_NAME (SessionId) WHERE IsFinish;
ALTER TABLE SQL_DB_INSTANCE.OBJECT_NAME OWNER TO postgres;
GRANT SELECT, INSERT ON SQL_DB_INSTANCE.OBJECT_NAME TO some_user;
#undef OBJECT_NAME
и добавим в файл CRE/_tables_list.sql строку
#include "SVC_ExecutionLog.sql"
А директории INC создадим файл LogSQLException.inc
#define SVC_LOG_SQL_EXCEPTION_AND_RETURN \
EXCEPTION WHEN OTHERS THEN \
GET STACKED DIAGNOSTICS proc.sql_state=RETURNED_SQLSTATE, proc.sql_column=COLUMN_NAME, proc.sql_constraint=CONSTRAINT_NAME, \
proc.sql_datatype=PG_DATATYPE_NAME, proc.sql_table=TABLE_NAME, proc.sql_schema=SCHEMA_NAME, proc.sql_message=MESSAGE_TEXT, \
proc.sql_detail=PG_EXCEPTION_DETAIL, proc.sql_hint=PG_EXCEPTION_HINT, proc.sql_context=PG_EXCEPTION_CONTEXT; \
INSERT INTO SQL_DB_INSTANCE.SVC_ExecutionLog (LogLevel, SessionId, LogMessage, \
SQLState, SQLColumn, SQLConstraint, SQLDataType, SQLTable, SQLSchema, SQLMessage, SQLDetail, SQLHint, SQLContext) \
VALUES (32, session_id, 'SQL error '||proc.sql_message, \
proc.sql_state, proc.sql_column, proc.sql_constraint, proc.sql_datatype, proc.sql_table, proc.sql_schema, \
proc.sql_message, proc.sql_detail, proc.sql_hint, proc.sql_context); \
COMMIT; \
RAISE WARNING 'SQL error %', sql_message; \
RETURN 32; \
END;
Так же включим его в файл INC/_macros_list.sql строкой #include, как сделали выше.
Теперь где-нибудь в хранимой процедуре, вызываемой каким-то сервисом мы можем использовать следующую конструкцию:
BEGIN
<какие-то DDL или DML предложения>
SVC_LOG_SQL_EXCEPTION_AND_RETURN
Подразумевается, что при запуске cpp указывается параметр -D SQL_DB_INSTANCE=schema_name, а результат работы cpp обрабатывается приведенным выше Perl скриптом.
Если тема будет интересна, могу привести в следующей статье ряд более сложных и практически интересных примеров использования препроцессора.
Макроопределения для PostgreSQL
При написании SQL кода для разворачивания БД и написании хранимых процедур порой очень нехватает простейшего макроязыка. Простыми макросами можно заменять сложные похожие выражения, последовательности...
habr.com