Схема исправляет методы

На работе для SQL Server мы пишем сценарии изменения схемы, которые сначала откатывают вносимые изменения (идемпотентно, поэтому выполняется раздел отката нормально, даже если изменение схемы еще не применено), а затем раздел для применения изменения. В TSQL легко заглянуть в системный каталог или другие таблицы, чтобы увидеть, существуют ли уже таблицы / столбцы / индексы / строки, и ничего не делать, если нет.

В PostgreSQL вы немного более ограничены тем, какие команды вы можете просто отправить на сервер - но, с другой стороны, DDL транзакционный, так что изменения наполовину примененной схемы не должно происходить. Я довольно хорошо адаптировал схему, к которой я привык на работе, для использования в моих собственных маленьких проектах (излишество? Но даже здесь у меня есть база данных dev / test и «настоящая» база данных), например:

\echo Rolling back schema change #35

BEGIN;

DELETE FROM schema_version WHERE schema_id = 35;

DROP TABLE IF EXISTS location_coordinates;
DROP FUNCTION IF EXISTS location_coordinates_populate();

END;

\echo Applying schema change #35

BEGIN;

INSERT INTO schema_version(schema_id, description) VALUES(35, 'Add location_coordinates table');

CREATE TABLE location_coordinates(
 location_id INT PRIMARY KEY REFERENCES location(location_id),
 latitude FLOAT NOT NULL,
 longitude FLOAT NOT NULL,
 earth_coordinates earth NOT NULL,
 box_10miles cube NOT NULL
);

GRANT SELECT, INSERT, UPDATE, DELETE ON location_coordinates TO ui;

CREATE FUNCTION location_coordinates_populate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS $$
BEGIN
  new.earth_coordinates := ll_to_earth(new.latitude, new.longitude);
  new.box_10miles := earth_box(new.earth_coordinates, 10 * 1609.344);
  RETURN new;
END
$$;

CREATE TRIGGER location_coordinates_populate BEFORE INSERT OR UPDATE ON location_coordinates
 FOR EACH ROW EXECUTE PROCEDURE location_coordinates_populate();

INSERT INTO location_coordinates(location_id, latitude, longitude)
 SELECT location_id, latitude, longitude FROM location WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

CREATE INDEX location_coordinates_10miles ON location_coordinates USING gist (box_10miles);

END;

\echo Done

This Скрипт можно запустить в базе данных с помощью команды "psql -f schema-changes / 35.sql". Просто вырезав сообщение «применить ...», я могу получить команды для отката. Как видите, при изменении сохраняется таблица метаданных schema_version, поэтому я могу видеть, какие изменения были применены. Все изменения выполняются в виде транзакции, переноса данных и всего остального. Здесь я использовал возможность «IF EXISTS» команд DROP, чтобы сделать раздел отката счастливым, даже если изменение не применяется. Одна вещь, которую мы сделали в работе для Oracle, - это запись изменений схемы как PL / SQL - возможно, вы могли бы иметь некоторые функции в plpgsql, чтобы помочь с внесением изменений?

Обратите внимание, что в приведенном выше изменении, где я переношу " столбцы широты и долготы (которые допускали значение NULL) из «местоположения» в отдельное отношение «location_coordinates» (и добавление материала «earthdistance»), я не отбрасывал старые столбцы. Одна вещь, о которой мы должны быть осторожны, - это сделать изменения схемы обратно совместимыми, если это возможно. Поэтому я могу применить это изменение схемы до обновления приложения для использования новых таблиц. У меня было бы второе изменение, чтобы удалить старые столбцы, чтобы применить после обновления приложения. На работе это будет происходить в двух разных циклах выпуска: поэтому во время выпуска X у нас по-прежнему есть возможность отката приложения для выпуска X-1 без необходимости сначала откатить все изменения схемы; а также возможность развертывать изменения схемы в отдельном окне перед приложениями. (Технически я должен был написать триггер, чтобы обновления старой таблицы синхронизировались с новой таблицей, но я этого не сделал, потому что это слишком похоже на работу :))

У нас также есть такие вещи, как приложение, которое сканирует все наши базы данных чтобы увидеть, что находится в таблице schema_version , и отслеживать изменения, чтобы люди могли даже видеть, какие изменения были внесены без необходимости подключения, и получить представление об истории каждого изменения (мы отслеживаем «откат в dev "," применяется в разработке "и т. д.). При работе наша таблица schema_version также включает информацию об авторстве и т. Д. Было бы здорово использовать волшебный способ применения информации о версии из системы контроля версий. Одна из наших проблем заключается в том, что если SC применяется, например, в QA, а затем изменяется в Perforce, возможно, никто не заметит. Таким образом, было бы неплохо отслеживать это изменение схемы. Было бы неплохо применить 35 ревизии №4.

Следует отметить одно: изменения схемы для нас нумеруются независимо от версий приложения. Очевидно, они связаны - это еще одна вещь, которую позволяет людям использовать приложение для пауков - но мы стараемся внести много мелких изменений, а не гигантский патч «вот все для выпуска X». Изменения схемы также используются для таких вещей, как добавление новых индексов, поэтому могут вообще не управляться приложением. В общем, изменения схемы «принадлежат» разработчикам, а не администраторам баз данных, хотя в приведенном выше примере «создать индекс» администратор баз данных в основном выступает в роли разработчика и отвечает за изменение схемы. Да, мы настаиваем на высоком уровне владения SQL от разработчиков, хотя другие группы в компании работают немного иначе и отдают больше работы команде БД.