SQL: избегать жесткого кодирования или магических чисел

Вопрос: Какие еще стратегии избегают магических чисел или жестко заданных значений в сценариях SQL или хранимых процедурах?

Рассмотрим хранимую процедуру, задачей которой является проверка/обновление значения записи на основе ее StatusID или некоторой другой таблицы поиска FK или диапазона значений.

Рассмотрим таблицу Status, где идентификатор наиболее важен, поскольку он является FK для другой таблицы:

Сценарии SQL, которые следует избегать, выглядят примерно так:

DECLARE  @ACKNOWLEDGED tinyint

SELECT  @ACKNOWLEDGED = 3   --hardcoded BAD

UPDATE  SomeTable
SET     CurrentStatusID = @ACKNOWLEDGED
WHERE   ID = @SomeID

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

Также старайтесь избегать SELECT на основе текстового описания/имени состояния:

UPDATE  SomeTable
SET     CurrentStatusID = (SELECT ID FROM [Status] WHERE [Name] = 'Acknowledged')
WHERE   ID = @SomeID

Некоторые другие мысли о том, как достичь этого:

добавьте новый столбец bit (по имени "IsAcknowledged" ) и наборы правил, где может быть только одна строка со значением 1. Это поможет найти уникальную строку: SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)

Ответ 1

На некотором уровне будет некоторая "жесткая кодировка" значений. Идея их устранения происходит с двух сторон:

Сделать код более читаемым (т.е. сказать 'Acknowledged', а не 3, вероятно, сделает ваши намерения более очевидными для читателя.
Создание более динамичного кода, где одна функция может принимать параметр, а не несколько функций, которые этого не делают (очевидно, это упрощение, но значение должно быть достаточно само собой разумеющимся)

Создание столбцов bit для различных состояний может быть хорошей или плохой идеей; это действительно зависит от данных. Если данные проходят через различные "этапы" ( "Получено", "Подтверждено", "Под обзором", "Отклонено", "Принято", "Ответировано" и т.д.), То этот подход быстро масштабируется из-за жизнеспособности (не говоря уже о раздражающем процессе обеспечения того, чтобы только один из столбцы устанавливаются в 1 в любой момент времени). Если, с другой стороны, состояние действительно так же просто, как вы описываете, то это может сделать код более читаемым, а индексы работать лучше.

Самое большое значение no-no в жестких кодирующих значениях - это жесткие значения кодирования, которые ссылаются на другие объекты (другими словами, жесткое кодирование первичного ключа для соответствующего объекта). Строка 'Acknowledged 'по-прежнему является жестко запрограммированным значением, она более прозрачна по своему значению и не является ссылкой на что-то еще. Для меня это сводится к следующему: , если вы можете (разумно) найти его, сделайте это. Если вы не можете (или если что-то делает его необоснованной задачей либо с точки зрения производительности, либо с точки зрения удобства обслуживания), попробуйте его. Используя это, вы можете найти значение 3 с помощью Acknowledged; вы не можете искать Acknowledged из всего остального.

Ответ 2

В ситуациях, подобных вашей таблице состояния, я создаю то, что я называю "статическими" наборами данных. Эти таблицы содержат данные, которые

Устанавливается и определяется при создании,
Никогда не меняется и
ВСЕГДА то же самое, от экземпляра базы данных до экземпляра базы данных без исключений

То есть, в то же время вы создаете таблицу, вы также заполняете ее, используя script, чтобы гарантировать, что значения всегда одинаковы. После этого, независимо от того, где и когда база данных, вы будете знать, что такое значения, и может жестко закодировать соответственно и соответствующим образом. (Я бы никогда не использовал суррогатные ключи или свойство столбца идентификатора в этих ситуациях.)

Вам не нужно использовать числа, вы можете использовать строки - или двоичные файлы или даты, или что-то самое простое, простое и наиболее подходящее. (Когда я могу, я использую строки char, а не varchars, такие как "RCVD", "DLVR", ACKN "и т.д., Являются более простыми закодированными значениями, чем, скажем, 0, 2 и 3.)

Эта система работает для нерасширяемых наборов значений. Если эти значения могут быть изменены (такие, что 0 больше не означает "подтверждено", тогда у вас есть проблема с доступом к безопасности. Если у вас есть система, в которой новые коды могут быть добавлены пользователями, тогда у вас есть другая и сложная проблема дизайна для решения.

Ответ 3

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

CREATE VIEW V_Execution_State AS
SELECT 10 AS Pending, 20 AS Running, 30 AS Done

DECLARE @state INT
SELECT @state = Pending FROM V_Execution_State

Ответ 4

Вот как я это сделаю. (Потому что это намного быстрее, чем ваш пример)

UPDATE SomeTable
SET CurrentStatusID = [Status].[ID]
FROM SomeTable
 RIGHT JOIN [Status] ON [Name] = 'Acknowledged'
WHERE SomeTable.[ID] = @SomeID

(не проверены могут быть опечатки)

Ответ 5

Не полагайтесь на IDENTITY для всех своих идентификаторов. Например, если у вас есть таблица поиска, длина которой составляет менее 50 строк, вполне разумно либо определять эти запросы как имеющие определенные идентификаторы, либо использовать для них строковый код. В любом случае "жесткое кодирование" больше не является проблемой.

Ответ 6

Одна идея:

CREATE FUNC dbo.CONST_ACKNOWLEDGED()
RETURNS tinyint
AS
BEGIN
   RETURN 3
END

Однако это имеет смысл только в том случае, если у вас нет autonumber, IMHO

Ответ 7

Если ваш случай прост, как указано выше, где бит IsA подтвержденный будет работать, я бы пошел по этому маршруту. У меня никогда не было никаких проблем с этим. Если у вас более сложные сценарии, где у вас будет десяток бит полей, я не вижу проблемы с использованием "магического числа", если вы полностью контролируете его. Если вы беспокоитесь о том, что столбец идентификатора неправильно отображается при переносе базы данных, вы можете создать другой (не идентичный) уникальный столбец с вашими значениями идентификатора, целочисленным или направляющим или другим, что было бы полезно.

Ответ 8

Если объект "Статус", который является частью вашей модели домена, имеет предопределенные значения, некоторые из которых должны обрабатываться определенным образом хранимыми процедурами, тогда вполне нормально указывать ссылки на эти конкретные значения в ваш код. Проблема здесь в том, что вы путаете то, что потенциально является абстрактным ключом (столбец идентификатора ID) для значения, имеющего значение в вашей модели домена. Несмотря на то, что вы сохраняете свой идентификационный столбец идентификатора, вы должны использовать значимый атрибут вашего объекта домена, ссылаясь на него в коде, это может быть имя, или это может быть числовой псевдоним. Но этот числовой псевдоним должен быть определен в вашей модели домена, например. 3 означает "Подтвержденный", и его не следует путать с полем абстрактного идентификатора, который, как вы говорите, может быть столбцом идентификации в некоторых экземплярах базы данных.

Ответ 9

Для начала следует избегать Business Logic на уровне хранилища.

Как представляется, это неизбежно при использовании базы данных, такой как Sql Server, где большая часть BL может существовать в БД, я думаю, вы скорее вернетесь к использованию идентификаторов строк, а не к авто ID.

Это будет намного более управляемым, чем auto ids, и его можно обработать лучшими способами даже при использовании фактического уровня приложения.

Например, используя подход .NET, многие уникальные идентификаторы строк могут храниться в любом месте от файлов конфигурации, до дополнительных поисков, используя выбранные файлы db, XML.

Ответ 10

Представьте

table dbo.Status
(
     Id int PK
    ,Description varchar
)
values
1, Received
2, Acknowledged
3, Under Review
etc

Итак, просто

declare @StatusReceived int = 1
declare @StatusAcknowledged int = 2
declare @StatusUnderReview = 3
etc

Как упоминают другие, это предполагает, что IDENTITY не установлен.

Я тоже использовал JOIN для таблиц поиска, но это делает SELECT короче и легче читается.

Этот подход поддается автоматизации, поэтому я генерирую всю таблицу в отдельном запросе, а затем копирую элементы, которые мне нужны (не все).