Self-modifying SQL: динамическое изменение SQL-кода в процессе выполнения / Хабр

Self-modifying SQL — это техника, при которой SQL-запросы не просто выполняют фиксированную операцию, а генерируют, изменяют и выполняют другие SQL-запросы во время работы приложения. Эта концепция может показаться экзотической и даже спорной, но в определённых сценариях она позволяет создать гибкие, адаптивные решения для динамического управления базой данных.

Эта статья предназначена для разработчиков всех уровней: от начинающих, которые хотят понять основы динамического SQL, до продвинутых специалистов, интересующихся нетривиальными приёмами и автоматизацией управления данными.

1. Введение

1.1 Что такое Self-modifying SQL?

Self-modifying SQL (самомодифицирующийся SQL) — это подход, когда SQL-запросы создают или изменяют другие SQL-запросы в ходе своей работы. Другими словами, вместо того чтобы работать с фиксированным набором инструкций, система генерирует новые запросы на основе текущего состояния базы данных, условий или логики приложения.

1.2 Зачем это нужно?

Динамическая адаптация: в условиях постоянно меняющихся данных можно автоматически корректировать структуру базы, создавать или обновлять индексы, триггеры и представления без ручного вмешательства.
Автоматизация администрирования: Self-modifying SQL позволяет автоматизировать рутинные задачи (например, создание временных таблиц, пересоздание индексов) и реагировать на изменения в данных.
Оптимизация производительности: при анализе статистики запросов можно генерировать SQL-код, который улучшает скорость выполнения запросов.
Эксперименты и исследовательская работа: тема интересна с точки зрения метапрограммирования в SQL, позволяя исследовать границы возможностей языка.

2. Основы динамического SQL

2.1 Динамический SQL vs. Self-modifying SQL

Динамический SQL - это механизм формирования SQL-запросов в коде во время выполнения, например, с помощью EXECUTE или PREPARE. Он позволяет изменять параметры запроса, но не меняет саму структуру базы.
Self-modifying SQL не просто создаёт запросы динамически, а изменяет саму структуру БД или генерирует новые SQL-запросы, влияющие на последующие операции.

2.2 Примеры динамического SQL

1. Простой пример динамического SQL в PostgreSQL:

-- Пример использования EXECUTE в PL/pgSQL для динамического выполнения 
DO $$ 
DECLARE 
    table_name TEXT := 'users';
    user_count INTEGER;
BEGIN 
    EXECUTE 'SELECT count(*) FROM ' || quote_ident(table_name)
    INTO user_count;
    RAISE NOTICE 'Number of users: %', user_count;
END $$;

В этом примере динамически генерируется запрос для подсчета строк в таблице users. Это классический пример динамического SQL, он не включает самоизменение запроса.

2. Динамическое изменение структуры таблицы:

-- Сначала создадим тестовую таблицу dynamic_data
CREATE TABLE dynamic_data (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- Теперь запускаем код, который автоматически добавит колонку new_column, если её ещё нет
DO $$ 
DECLARE v_column_name TEXT := 'new_column';
BEGIN
    -- Проверяем, есть ли колонка
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'dynamic_data' 
        AND column_name = v_column_name
    ) THEN
        -- Добавляем колонку динамически
	-- Используем %I в format() для безопасного экранирования имён колонок
        EXECUTE format('ALTER TABLE dynamic_data ADD COLUMN %I TEXT;', v_column_name);
        RAISE NOTICE 'Колонка % добавлена!', v_column_name;
    ELSE
        RAISE NOTICE 'Колонка % уже существует!', v_column_name;
    END IF;
END $$;

-- Теперь посмотрим, добавилась ли колонка
SELECT column_name FROM information_schema.columns WHERE table_name = 'dynamic_data';

В этом примере добавляется новый столбец, если он отсутствует.

3. Self-modifying SQL: концепция и пример

3.1 Генерация SQL-запросов на лету

Одним из примеров self-modifying SQL является генерация новых запросов на основе данных, полученных из текущего запроса. Рассмотрим пример, когда требуется создать индексы для всех таблиц в схеме, если они отсутствуют.

Пример: Автоматическое создание индексов

DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        -- Генерируем и выполняем запрос на создание индекса, если он не существует
        -- Проверяем, содержит ли таблица поле id перед созданием индекса
EXECUTE format(
    'DO $$ BEGIN 
        IF EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = %L AND column_name = ''id'') 
        THEN CREATE INDEX IF NOT EXISTS idx_%I_id ON %I (id); 
        END IF; END $$;', 
    tbl.table_name, tbl.table_name, tbl.table_name
);
    END LOOP;
END $$;

В этом примере мы перебираем все таблицы в публичной схеме и для каждой таблицы динамически генерируем SQL-запрос, который создаёт индекс по полю id, если индекс ещё не существует.

3.2 Автоматическое создание триггеров

Еще один пример self-modifying SQL — автоматическая генерация триггеров для всех таблиц, чтобы логировать изменения.

Пример: Генерация триггеров для логирования изменений

DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        EXECUTE '
            CREATE OR REPLACE FUNCTION log_changes_' || tbl.table_name || '() 
            RETURNS trigger AS $$
            BEGIN
                INSERT INTO change_log(table_name, operation, changed_at)
                VALUES (''' || tbl.table_name || ''', TG_OP, NOW());
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        ';

        EXECUTE '
            DROP TRIGGER IF EXISTS trg_' || tbl.table_name || '_log ON ' || tbl.table_name || ';
            CREATE TRIGGER trg_' || tbl.table_name || '_log
            AFTER INSERT OR UPDATE OR DELETE ON ' || tbl.table_name || '
            FOR EACH ROW EXECUTE FUNCTION log_changes_' || tbl.table_name || '();
        ';
    END LOOP;
END $$;

В этом примере для каждой таблицы создаётся функция логирования и триггер, который будет вызываться при изменении данных в таблице. Это демонстрирует, как SQL может изменять структуру базы данных на лету.

3.3 Динамическое изменение триггеров

-- Создадим таблицу users и вспомогательную users_log для логирования
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE users_log (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    created_at TIMESTAMP DEFAULT now()
);

-- Создаём функцию для логирования
CREATE OR REPLACE FUNCTION log_table_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users_log (user_id, action) 
    VALUES (NEW.id, 'INSERTED OR UPDATED');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Запускаем код, который создаёт триггер
DO $$ 
DECLARE trigger_name TEXT := 'users_log_trigger';
BEGIN
    -- Удаляем старый триггер
    EXECUTE format('DROP TRIGGER IF EXISTS %I ON users;', trigger_name);

    -- Создаём новый триггер
    EXECUTE format('
        CREATE TRIGGER %I 
        AFTER INSERT OR UPDATE 
        ON users 
        FOR EACH ROW 
        EXECUTE FUNCTION log_table_changes();
    ', trigger_name);
    
    RAISE NOTICE 'Триггер % пересоздан!', trigger_name;
END $$;

-- Проверяем - вставляем пользователя
INSERT INTO users (name) VALUES ('PPR');

-- Смотрим логи
SELECT * FROM users_log;

4. Преимущества и риски self-modifying SQL

4.1 Преимущества

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

4.2 Риски и предостережения

Безопасность: динамическое выполнение SQL повышает риск SQL-инъекций, если динамически генерируемые строки не экранируются корректно. Например:

EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';

Если user_input = 'John' OR 1=1 --, то запрос вернёт всех пользователей.
Используйте quote_literal() и quote_ident() для защиты.

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

5. Лучшие практики для реализации self-modifying SQL

Валидация и экранирование: всегда проверяйте и экранируйте переменные, используемые для генерации SQL-запросов (например, с помощью quote_ident в PostgreSQL).
Логирование изменений: записывайте, какие запросы были сгенерированы и выполнены, чтобы в случае ошибок можно было восстановить исходное состояние.
Ограничение области применения: используйте self-modifying SQL только для задач, которые действительно требуют динамической адаптации. Для рутинных операций лучше применять стандартные миграции или автоматизированные инструменты администрирования.
Тестирование: обязательно тестируйте сценарии использования на тестовых базах данных, прежде чем применять в продакшене.
Документация: поскольку самокорректирующийся SQL-код сложен для понимания, документируйте его логику, чтобы другие разработчики могли разобраться в механизмах работы.

6. Заключение

Самомодифицирующийся SQL помогает автоматизировать администрирование, изменять структуру БД и оптимизировать запросы.

Но он усложняет отладку и может создавать уязвимости, если не учитывать безопасность.

Где использовать: в автоматизации, администрировании, исследовательских задачах.
Где не стоит: в критически важных системах без строгой валидации данных

Self-modifying SQL: динамическое изменение SQL-кода в процессе выполнения