Используйте календарные таблицы для работы с датами

Обоснование
Всегда начинайте работу над новым проектом с создания нумерационной и календарной таблиц. Таблица-календарь индексируется по дате, а в неключевом поле размещается информация о том, какое значение данная дата имеет для вашей организации. Рабочий это день или выходной? Какова его юлианская дата? К какому финансовому периоду он относится? Короче говоря, все, что может вызывать необходимость в принятии каких-то решений.
Таблица с датами на 20 лет занимает всего около 7 тыс. строк — это мелочь. О том, как работать с такой таблицей, читайте в группах новостей или в работе Селко (1999).

Исключения
Нет.

Выполняйте расчеты с помощью вспомогательных таблиц

Если функция или формула возвращает не более нескольких тысяч значений, не пересчитывайте их раз за разом, а лучше заранее подготовьте таблицу с параметрами и результатами, а затем извлекайте нужное значение с помощью оператора JOIN. Операторы JOIN в SQL выполняются эффективно, а вычисления — нет.

Исключения
Если вычисление ограничивается четырьмя действиями арифметики, создание специальной таблицы для него — излишняя роскошь. Создать таблицу для функции, область значений которой очень широка, будет попросту невозможно.

Скалярные и структурированные параметры

В стандартном SQL-92 нет ни массивов, ни списков. Есть только одна структура данных — таблица. Это может быть основная таблица, производная таблица, представление — но это всегда таблица. В процедурных языках применяются другие структуры — массивы, списки, записи. Привыкшие к ним программисты отчаянно пытаются протащить их в SQL Результат — неуклюжий и непроизводительный код. Но бывает и хуже, когда программист прибегает к динамическому SQL, чтобы выстроить выражение или целую программу “на лету”.
В вызове хранимой процедуры ожидается скалярный параметр, а не структурированный или динамический. Прибегнув к некоторым хитростям, вы сможете воспользоваться преимуществами хранимых процедур, сохранив некоторую гибкость. Типичная проблема состоит в передаче списка значений в предикат IN(), как в этом псевдокоде:

SELECT a, b, с FROM Foobar WHERE a IN (<список параметров>);

Типичное неудачное решение — использование динамического SQL, в котором в качестве списка параметров передается список значений, разделенных запятыми. Можно, как мы делали в разделе “Не создавайте себе проблем с переносимостью”, поместить список в таблицу и написать такое выражение:

SELECT a, b, с FROM Foobar WHERE a IN (SELECT aa FROM ParmList);

Но еще лучше загрузить данные списка в таблицу с помощью выражения INSERT INTO. Детали от продукта к продукту будут варьироваться, но в стандартном SQL это делается так.

INSERT INTO Parmlist (parm) VALUES (1), (2), (3), (4);

У списка VALUES() должно быть заранее известно количество строк, но его легко превратить в динамический список, добавив в него значения NULL или другие пустые значения. На стороне БД вы должны будете от них избавиться, а также при необходимости с помощью SELECT DISTINCT удалить повторяющиеся значения. Полностью выражение на хост-языке будет выглядеть как-то так:

INSERT INTO Parmlist (parm)

SELECT DISTINCT parm

FROM (VALUES (:h1), (:h2), (:h3), (:h4)) AS X(parm)

WHERE X.parm IS NOT NULL;

He пользуйтесь динамическим SQL

Динамический SQL медленен и опасен. Он также говорит о том, что программисту не удалось толком спроектировать приложение, и потому он передает этот груз пользователям. Назначение динамического SQL — разработка инструментария для метаданных, а не приложений. Инструмент для работы с метаданными обращается с объектами схемы как с объектами схемы, а не как с элементами модели данных.

Быстродействие

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

SELECT name, rank, serial_nbr

FROM CombatMarines

WHERE sex =:input_sex_code;

Если параметр “:input_sex_code” равен 1 (мужской пол в кодировке ISO), тогда лучший способ выполнения запроса — просмотр таблицы. Если параметр равен 2 (женский пол), лучше воспользоваться индексом. Если у параметра любое другое значение, нужно просто вернуть пустой набор. Конечно, все это зависит от реализации. Однако современные оптимизаторы способны создавать несколько планов выполнения, опираясь на статистику, и выбирают нужный на основании значения параметра. Короче говоря, возвращаемся к старому правилу — оптимизатору нужно верить!

SQL-инъекция

SQL-инъекция — одна из разновидностей хакерских атак, в которой злоумышленник размещает свой SQL-код в вашей процедуре и выполняет ее. Как только вы разрешили использовать динамический SQL в хранимой процедуре или генерировать выражения SQL в клиентском коде, вы в опасности. Вот пример функции, которая генерирует простую строку динамического SQL, предложенный [email protected]:

CREATE FUNCTION Search_Orders (custname VARCHAR(60))

RETURNS VARCHAR(3000)

RETURN ('SELECT * FROM Orders WHERE ' || COALESCE (custname, '1=1'));

Допустим, что параметр custname поступает непосредственно из интерфейса без какой бы то ни было фильтрации или проверки. Злоумышленник может передать в качестве параметра строку:

SET custname = ' 1=1; DROP TABLE Orders;';

Результирующее SQL-выражение будет выглядеть так:

'SELECT * FROM Orders WHERE 1=1; DROP TABLE orders;'

Хост-программа выполнит это выражение, и таблицы как ни бывало.
Конечно, вряд ли у простого пользователя есть право на удаление таблиц. Но с помощью SQL-инъекции можно выполнять любые выражения. Сначала нападающий определяет, какой ввод приводит не к ошибке времени выполнения, а к синтаксической ошибке. Так он узнает, что на стороне БД имеется динамический SQL. Затем пишется код, который при необходимости завершается точкой с запятой или символом комментария, чтобы отбросить ту часть запроса, что будет стоять после него. Немного проб и ошибок, и хакер найдет, как причинить вред.
Первая линия обороны — не давать пользователям больше прав, чем им нужно для работы. Простому пользователю достаточно разрешить использование оператора SELECT. Но лучше всего просто не использовать динамический SQL.

 

ГЛАВА 9.
Эмпирические правила кодирования

Приведенные здесь приемы и правила не являются математически точными. Больше того, некоторые из них покажутся вам довольно странными. Но, как заметил однажды Ларри Константайн (Larry Constantine), задача метода, указать вам, что делать дальше, когда вы не знаете, что делать дальше. Вам остается только надеяться, что метод даст вам, может быть, не самое удачное, но, по крайней мере, работоспособное решение.
Рассмотрим несколько простых задач программирования, по мере продвижения вперед применяя к ним различные правила. Рассмотрим так называемую проблему выбора партнера по танцу. Дан список людей и их пол. Ваша задача — составить из них пары.

CREATE TABLE People (name VARCHAR (35) NOT NULL PRIMARY KEY, gender INTEGER DEFAULT 1 NOT NULLCHECK (gender IN (1,2))); -- коды полов

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

CREATE TABLE Orders (order_nbr INTEGER NOT NULL,...);CREATE TABLE OrdersDetails (order_nbr INTEGER NOT NULL REFERENCES Orders (order_nbr) ON UPDATE CASCADE ON DELETE CASCADE, sku CHAR(1O) NOT NULL REFERENCES Inventory (sku) ON UPDATE CASCADE ON DELETE CASCADE, description CHAR(20) NOT NULL, qty INTEGER NOT NULL CHECK(qty > 0), unit_price DECIMAL(12,4) NOT NULL,...);