Задача
Итак на работе недавно встала задача регулярной проверки целостности базы данных продукта после каждой новой версии. Вывод я ожидал увидеть в виде:
[FAIL] Object missing: INDEX USERS_IDX [FAIL] Table column is wrong. Should be: GROUPS.G_GROUP_ID NUMBER 22 N [FAIL] DB key missing: CONFIG.C_NAME='product.default.dir' [FAIL] DB key missing: GROUPS.G_NAME='Admins' _______________________ Errors: 4
Что мы имеем:
Есть некий продукт, который использует Oracle DB. С версии на версию база обновляется вручную с SQL скриптом. Есть две инсталляции продукта: тестовая и продакшн. Увы, но связи с DB серверами инсталляций нет совсем (такова политика безопасности). С новыми версиями время от времени возникали проблемы: скрипт обновления не всегда включал все обновления или же попросту запускали его невнимательно и в итоге мы имели продакшн базу с изъянами.
Дабы как-то исправить ситуацию, было решено написать скрипт проверки целостности базы на продакшн окружении. Я думаю ситуация не так редко случается в IT мире и кому-то сие творение может пригодится. В процессе написания я старался выбирать наиболее универсальный способ реализации, чтобы абстрагироваться от кокретного продукта.
Основная идея
Основная идея была в том, чтобы реализовать проверку наиболее быстро и обновление от версии к версии занимало как можно меньше времени и работы. Посему я выбрал PL SQL в качестве языка реализации (не требует никаких дополнительных инсталляций). Обновления скрипта я решил делать наиболее автоматизированно: делать слепок с эталонной базы. В качестве таковой базы используется тестовое окружение, т к оно проверяется со всех сторон в процессе написания версии.
Реализация
Чтобы покрыть наиболее большую часть базы, я решил разбить весь процесс на три этапа:
- Проверка наличия нужных объектов, кроме таблиц: Index, Sequence, Trigger, etc
- Проверка типов всех колонок во всех таблицах. Как бонус, проверяется наличие как таблиц, так и колонок
- Проверка наличия необходимых ключей в некоторых таблицах (CONFIG, SETTINGS, etc)
PL SQL я знаю примерно 5 дней, посему местами реалиция может быть очень корявой. Уже в процессе написания я понял, что PL SQL не самый лучший язык реализации задумки, т к у него есть несколько очень неудобных ограничений:
- Размер PL SQL программы оганичен, у меня это было примерно 1700 строчек. подробнее можно глянуть например, тут.
- Файлы можно использовать только на серверной стороне, в моем случае это ставило крест на хранении инфы в файлах.
Из-за второго ограничения все эталонные данные пришлось инициализировать прямо в скрипте:
idx := idx + 1;
refObjects(idx).TYPE := 'INDEX';
refObjects(idx).NAME := 'USERS_PK';
Таких инициализаций у меня получилось порядка 1200 и на каждую пришлось 3 — 5 строчек кода, т е порядка 7000 строчек. После запуска такой махины Oracle меня послал с напутствием: “PLS-00123:program too large”.
Для решения этой проблемы пришлось писать функцию для инициализации. После чего 3 строки выше превратились в лаконичную:
idx := initRefObject(refObjects,idx,'INDEX','USERS_PK'); Итак, для первого этапа (проверка объектов), я описал курсор, который будет выгребать нужные для этого данные:
CURSOR getCurrentRefObjects IS
SELECT OBJECT_TYPE,OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE','INDEX','TRIGGER') AND OBJECT_NAME NOT LIKE 'SYS_%' ORDER BY OBJECT_TYPE;
Tip:
Таблица USER_OBJECTS есть в каждой Oracle схеме, поэтому эта часть полностью универсальна. Типы объектов можно изменять в этом кусочке: OBJECT_TYPE IN ('SEQUENCE','INDEX','TRIGGER'). OBJECT_NAME NOT LIKE 'SYS_%' применяется для отсеивания системных объектов.
Вывод курсора будет в виде:
OBJECT_TYPE OBJECT_NAME
— — INDEX USERS_IDX
SEQUENCE GROUPS_SEQ
TRIGGER GROUPS_TRG
Теперь, чтобы хранить эталонные данные, нам нужен новый тип данных:
TYPE RefObjects IS TABLE OF getCurrentRefObjects%ROWTYPE
INDEX BY BINARY_INTEGER;
Как я говорил выше, для уменьшения кода описываем функцию инициализации:
FUNCTION initRefObject
(refObjects IN OUT RefObjects,
idx IN NUMBER,
f_ObjectType IN USER_OBJECTS.OBJECT_TYPE%TYPE,
f_ObjectName IN USER_OBJECTS.OBJECT_NAME%TYPE)
RETURN NUMBER IS
idxOut NUMBER;
BEGIN
refObjects(idx).OBJECT_TYPE := f_ObjectType;
refObjects(idx).OBJECT_NAME := f_ObjectName;
idxOut := idx + 1;
RETURN idxOut;
END initRefObject;Теперь инициализация эталонных объектов будет происходить в виде:
idx := initRefObject(refObjects,idx,'INDEX','USERS_IDX'); Для сбора эталонных объектов я использовал запрос, которых нужно выполнить на эталонной DB:
SELECT CHR(9) || 'idx := initRefObject(refObjects,idx,''' || OBJECT_TYPE || ''',''' || OBJECT_NAME || ''');' FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE','INDEX','TRIGGER') AND OBJECT_NAME NOT LIKE 'AUDIT_LOG_%' AND OBJECT_NAME NOT LIKE 'SYS_%' ORDER BY OBJECT_TYPE
Запрос выведет данные в готовом виде:
idx := initRefObject(refObjects,idx,'INDEX','USERS_IDX');
idx := initRefObject(refObjects,idx,'SEQUENCE','GROUPS_SEQ');
idx := initRefObject(refObjects,idx,'TRIGGER','GROUPS_TRG');
...
Теперь сам процесс проверки. Сначала считываем текущие данные из курсора:
OPEN getCurrentRefObjects;
FETCH getCurrentRefObjects BULK COLLECT INTO currentObjects;
CLOSE getCurrentRefObjects;И проверяем все объекты:
FOR i IN refObjects.FIRST..refObjects.LAST LOOP
flag := false;
FOR j IN currentObjects.FIRST..currentObjects.LAST LOOP
IF refObjects(i).OBJECT_TYPE = currentObjects(j).OBJECT_TYPE THEN
IF refObjects(i).OBJECT_NAME = currentObjects(j).OBJECT_NAME THEN
flag := true;
END IF;
END IF;
END LOOP;
IF flag = false THEN
errs := errs + 1;
DBMS_OUTPUT.Put_Line(FAIL_STRING || 'Object missing: ' || refObjects(i).OBJECT_TYPE || ' ' ||refObjects(i).OBJECT_NAME);
END IF;
END LOOP;
Tip:
В PL SQL нельзя просто написать refTables(i) = currentTables(j), нужно проверять все поля по-отдельности.
Весь IF я разбил на части для ускорения проверки — если одно поле не совпадает, второе можно не проверять.
Второй этап (проверка таблиц и колонок) реализован по той же схеме, посему опишу кратко.
Курсор:
CURSOR getCurrentRefTables IS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH, NULLABLE FROM ALL_TAB_COLS WHERE OWNER = (select sys_context('userenv', 'current_schema') from dual) ORDER BY TABLE_NAME;
Tip:
Таблица ALL_TAB_COLS существует во всех инстансах Oracle, посему эта часть также универсальна.
Вывод курсора:
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
— — — — — GROUPS G_GROUP_ID NUMBER 22 N
GROUPS G_DESCRIPTION VARCHAR2 1000 N
GROUPS G_NAME VARCHAR2 100 N
Новый тип:
TYPE RefTables IS TABLE OF getCurrentRefTables%ROWTYPE
INDEX BY BINARY_INTEGER;
Функция инициализации:
FUNCTION initRefTable
(refTables IN OUT RefTables,
idx IN NUMBER,
f_TableName IN ALL_TAB_COLS.TABLE_NAME%TYPE,
f_ColumnName IN ALL_TAB_COLS.COLUMN_NAME%TYPE,
f_DataType IN ALL_TAB_COLS.DATA_TYPE%TYPE,
f_DataLength IN ALL_TAB_COLS.DATA_LENGTH%TYPE,
f_Nullable IN ALL_TAB_COLS.NULLABLE%TYPE)
RETURN NUMBER IS
idxOut NUMBER;
BEGIN
refTables(idx).TABLE_NAME := f_TableName;
refTables(idx).COLUMN_NAME := f_ColumnName;
refTables(idx).DATA_TYPE := f_DataType;
refTables(idx).DATA_LENGTH := f_DataLength;
refTables(idx).NULLABLE := f_Nullable;
idxOut := idx + 1;
RETURN idxOut;
END initRefTable;
Запрос для сбора эталонных таблиц:
SELECT CHR(9) || 'idx := initRefTable(refTables,idx,''' || TABLE_NAME || ''',''' || COLUMN_NAME || ''',''' || DATA_TYPE || ''',''' || DATA_LENGTH || ''',''' || NULLABLE || ''');' FROM ALL_TAB_COLS where OWNER = 'MSO_TYPICAL_MNG_COPY-E' ORDER BY TABLE_NAMEВывод запроса:
idx := initRefTable(refTables, idx, 'GROUPS', 'G_GROUP_ID', 'NUMBER', '22', 'N');
idx := initRefTable(refTables, idx, 'GROUPS', 'G_DESCRIPTION', 'VARCHAR2', '1000', 'N');
idx := initRefTable(refTables, idx, 'GROUPS', 'G_NAME', 'VARCHAR2', '100', 'N');
...
Проверку я писать здесь не буду, т к она сделана по тому же шаблону, что и в первом этапе.
Третий этап будет немного отличаться, т к ключевые значения мне нужно было выбирать из разных таблиц. Для начала я решил, что за один раз я буду проверять только одно значение из одной таблицы в одной колонке. Далее описал тип данных для одного ключа:
TYPE RefKey IS RECORD
(
f_TableName VARCHAR2(255),
f_ColumnName VARCHAR2(255),
f_Value VARCHAR2(255)
);
И контейнер для таких записей:
TYPE RefKeys IS TABLE OF RefKey
INDEX BY BINARY_INTEGER;
Затем фунция инициализации:
FUNCTION initRefKey
(refKeys IN OUT RefKeys,
idx IN NUMBER,
f_TableName IN VARCHAR2,
f_ColumnName IN VARCHAR2,
f_Value IN VARCHAR2)
RETURN NUMBER IS
idxOut NUMBER;
BEGIN
refKeys(idx).f_TableName := f_TableName;
refKeys(idx).f_ColumnName := f_ColumnName;
refKeys(idx).f_Value := f_Value;
idxOut := idx + 1;
RETURN idxOut;
END initRefKey;
Увы, но в моем случае простым SELECT только нужные записи не выбрать, пришлось вбивать данные вручную:
idx := initRefKey(refKeys, idx, 'CONFIG', 'C_NAME', 'product.default.dir');
idx := initRefKey(refKeys, idx, 'GROUPS', 'G_NAME', 'Admins');
Реализация проверки также отличается. Увы, текущая реализация имеет узкое место — при несуществующей таблице или колонке она завалит весь скрипт. Я планирую устранить эту оплошность, но на данные момент она еще есть.
FOR i IN refKeys.FIRST..refKeys.LAST LOOP
idx := 0;
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || refKeys(i).f_TableName || ' WHERE ' || refKeys(i).f_ColumnName || ' = ' || '''' || refKeys(i).f_Value || '''' INTO idx;
IF idx = 0 THEN
errs := errs + 1;
DBMS_OUTPUT.Put_Line(FAIL_STRING || 'DB key missing: ' || refKeys(i).f_TableName || '.' || refKeys(i).f_ColumnName || '=''' || refKeys(i).f_Value || '''');
END IF;
END LOOP;
Послесловие
- Пусть и не идеально, но скрипт работает и я думаю, что приживется в качестве одной из составляющей каждой версии.
- Из-за ограничений PL SQL я планирую написать утилиту на чем-то более гибком, например на Java. Благо теперь я четко представляю как будет работать утилита, ее интерфейс и в каком виде хранить эталонные данные.
- Скрипт должен запускаться от имени пользователя продукта (Oracle schema)
Полная версия скрипта