Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

Регистрозависим ли язык T-SQL? Часть 1/2.


юридическая помощь при дтп 366bef3a

03/02/2012 Комментарии: нет  

Разумеется, фразу «в T-SQL регистр не важен» слышали все, и вы не исключение. Это стало настолько заезженным штампом что на первый взгляд непонятно — чего именно автор обсуждать сегодня собрался? Более того, SQL Server в своем языке T-SQL должен, по идее, следовать известному (хотя сейчас уже несколько устаревшему) стандарту SQL-92 standard. А последний не только этот штамп подтверждает, но и указывает как именно разработчики того или иного «ветвления» от основной магистрали языков SQL должны его реализовать. В пункте 5.4 Names and identifiers мы имеем удовольствие прочесть параграф 4-й, вот его начало:

An SQL language identifier is equivalent to an SQL language identifier in which every letter that is a lower-case letter is replaced by the equivalent upper-case letter or letters.

В вольном переводе: «считайте что все маленькие буквы — большие», конец цитаты. Казалось бы — вопрос ясен. Однако не будем спешить с финальными выводами а все аккуратно проверим. Уже не раз так случалось: стандарты сами по себе, а реальная жизнь несколько от них отличается. Не тот ли случай у нас?

Что будем проверять?

Очертим круг «подозреваемых». Очевидно что регистрозависимость может оказать влияние (а может и нет) при создании и использовании в коде вот таких конструкций языка T-SQL:

  • Ключевые и зарезервированные слова, Keywords & Reserved Words. Это остов и базис языка, слова которые движок сервера, собственно, именно что резервирует для своих «внутренних нужд». Вам подавляющее большинство представителей этого списка, безусловно, известны. SELECT, CREATE, DROP, WHERE, IN, LIKE, JOIN и т.д. Ну кто же их не знает?
  • Литералы (они же константы), Literals. Это некоторое конкретное значение указываемое вами для определенных целей. Целью может быть помещение этого значения в колонку (т.е. запись в ячейку таблицы). Или сравнение текущего значения колонки с вашим. Или передача такого значения в качестве параметра функции/хранимой процедуры, ну и так далее. Каждый литерал обязательно имеет тип. По условиям поставленной задачи мы можем отбросить все не-строковые типы и полностью сосредоточится на последних. Таким образом, в сферу наших интересов попадают всего лишь 2 типа констант: varchar и nvarchar. Все что мы пишем в одинарных кавычках (а это и есть формат записи строковых литералов) будет или вторым типом (если перед открывающей кавычкой стоит префикс N, юникод), или первым (если префикса нет). Возможно что эти типы будут с модификатором max (если объем литерала превысит 8000 или 4000 символов для varchar/nvarchar соответственно), однако для темы нашей беседы это ровно ничего не меняет.
  • Идентификаторы, Identifiers. Это довольно обширные по своим задачам программные сущности и тут уже будет похитрее ситуация. Дабы обеспечить полное «покрытие» исследуемого вопроса нам придется разбить эту группу участников на три под-группы:
    • идентификаторы уровня сервера: это имена собственные тех объектов, что принадлежат серверу, а не отдельной базе. В эту группу попадают имена логинов (logins), оконечных точек (endpoints), триггеров уровня сервера, и т.д. Имена самих баз данных тоже, кстати, попадают в эту группу;
    • идентификаторы уровня базы: тоже что и предыдущее, только объекты теперь будут относится к отдельной базе. Имена таблиц, колонок таблиц, представлений, индексов, функций, процедур и многого другого войдут в эту группу;
    • идентификаторы переменных: сюда попадает все то, чье имя начинается с «собачки» (@) а то и двух: непосредственно переменные вводимые в код инструкцией DECLARE, аргументы процедур и функций, встроенные системные функции типа @@ERROR и @@IDENTITY (именно их имена начинаются с двух «собачек»). Строго говоря последние являются именно функциями и ничем иным, но поскольку в более ранних версиях нашего сервера их «обзывали» «global variables», посмотрим, для «завершения картины», и их тоже. Как не удивительно, но сюда попадают и временные таблицы (в смысле, разумеется, их имена), несмотря на то, что они-то как раз начинаются с «решетки». Связь последних с переменными будет ясна из текста данной заметки
    Таким образом, анализируя эту группу и три ее под-группы мы выясним равны ли (т.е. воспринимаются ли сервером как указатели на один и тот же объект) MyTable и MYtable, myLOGIN и MYlogiN, @myvar и @MYVAR и т.п.

Наконец, прежде чем приступать к исследованиям и делать на их основе выводы, замечу, что для полного понимания текста данной заметки очень рекомендуется уверенное владение таким компонентом SQL Server как «набор правил сопоставления двух строковых значений», или (в оригинале) collation, или (как нравится автору больше всего) коллейшены. Как мы увидим далее именно они станут опорной точкой всего повествования, а значит предполагается плотное, и даже можно сказать, уверенное, знакомство с ними. Если вы не чувствуете себя достаточно компетентным в этом разрезе, автор может подсказать вам неплохую (на его скромный взгляд :oops: ) статью по данной теме: Понимаете ли вы коллейшены? Знакомство с ней будет тем более полезно если мы учтем тот факт, что заметка читаемая вами в настоящий момент является, по сути, логическим продолжением указанной статьи и просто расширяет описание тех горизонтов до которых простирается влияние коллейшенов. Так что несмотря на изрядный объем статьи рекомендуемой автор не колеблясь предлагает и настойчиво советует обратить на нее самое пристальное ваше внимание.

Ну а если коллейшены, благодаря ли статье упомянутой выше или просто из опыта прошлой работы с SQL Server, не представляют для вас тайны за семью печатями — вперед!

Ключевые и зарезервированные слова

Данный раздел будет совсем коротким и не требующим никаких особых дополнительных знаний. Все и каждое из ключевых (зарезервированных) слов (а с их исчерпывающим списком можно ознакомится) всегда можно писать в любом регистре, точка. И даже их (регистры) смешивать. Поэтому SELECT, select, seLEct или даже по настоящему «олд-скульное» SeLeCt или там какой-нибудь DroP tABLe — работает все и всегда! Разумеется автор полностью оставляет за скобками такие вещи как «красивый код», «внятное оформление кода», «соглашение по стилю кодирования для команды» и т.п. Это все совершенно разумно, и правильно, и должно быть учтено совершенно безусловно, но в рамках данной заметки нас будет интересовать чистый синтаксис и ничего более. А он — вот таков.

Итак, данная конструкция полностью подтверждает тот самый штамп о котором говорилось в начале статьи. Ключевые слова совершенно «отвязаны» от регистра. Переходим к конструкции следующей.

Литералы

Эта конструкция нас тоже не задержит долее чем на минуту, но вывод и резюме будут прямо противоположные предыдущим: регистр учитывается всегда и безусловно. Т.е. в конструкции ...WHERE Col1='Иванов' фамилия воспринимается сервером ровно как написано — первая буква заглавная, остальные строчные. Однако это все вовсе не означает, что при наличии в колонке Col1 фамилии 'иванОВ' у показанного фильтра нет ни единого шанса ее обнаружить. Вот тут все будет зависеть от коллейшена колонки. Ну или от коллейшена примененного в самом фильтре, как вариант. За подробностями управлением сопоставления строк через коллейшены автор вновь рекомендует обратиться к статье упомянутой выше, ну а мы — резюмируем:

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

Идентификаторы уровня сервера

А вот тут уже придется «взять в руки шашки», ну или хотя бы клавиатуру, поскольку без довольно серьезных опытов дело не обойдется. Для эксперимента нам потребуется целых два сервера (инстанса):

  • сервер на котором коллейшен не чувствителен к регистру; автор в качестве такового выбрал Cyrillic_General_CI_AS
  • сервер на котором коллейшен чувствителен к регистру; автор в качестве такового выбрал SQL_Scandinavian_CP850_CS_AS

Обратите внимание, что оба упомянутых коллейшена должны быть установлены на самом «верху», т.е. именно на уровне экземпляра сервера. Если все готово — вот наш тест:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE master
go
CREATE LOGIN "TestLog" WITH PASSWORD=N'123'
GO
CREATE ENDPOINT [ENDpoint_miRRoring] STATE = STOPPED AS TCP(LISTENER_PORT=2244)
FOR DATABASE_MIRRORING (AUTHENTICATION=WINDOWS KERBEROS,ROLE=ALL);
GO
CREATE DATABASE LetSTry
GO
DROP LOGIN "TESTLog"
GO
DROP ENDPOINT [endpoint_mirroring]
GO
DROP DATABASE letstRY

Мы создаем три разнотипных объекта, каждый из которых «принадлежит» серверу, и тут же пытаемся их «стереть», намеренно «ошибаясь» в регистрах их идентификаторов. Что нам даст на выходе первый сервер, который к регистру безразличен?

Command(s) completed successfully.

И больше ничего. Ну а второй, регистрозависимый?

Cannot drop the login 'TESTLog', because it does not exist or you do not have permission.
Cannot find the object "endpoint_mirroring" because it does not exist or you do not have permissions.
Cannot drop the database 'letstRY', because it does not exist or you do not have permission.

Вот оно что, все дело, оказывается, в коллейшенах! Причем, обратите внимание, попытка заключения идентификатора в разделители (хоть кавычки, хоть скобки) оказывается совершенно бесплодной, ситуация не меняется ни на йоту. Резюмируем:

Зависимость имен объектов уровня сервера от регистра букв их составляющих находится в прямой зависимости от той же самой характеристики коллейшена, заданного на том же самом уровне. «Правило-штамп», как минимум, не корректно на все 100%.

Идентификаторы уровня базы данных

Тут два сервера не нужны, можно и одним ограничится, зато нужны две базы с такими же «противоположными» (по атрибуту зависимости от регистра) коллейшенами. Впрочем, если вы уже для предыдущего опыта «подняли» оба инстанса и назначили им разные коллейшены, можете, «для очистки совести», воспроизвести последующий код на обоих. Хотя автор сразу вас уверяет — это излишне, главное теперь заключается именно в типе коллейшене уровня базы. Возьмем, допустим, такую «парочку»:

1
2
3
4
5
6
7
8
9
USE master;
GO
IF DB_ID (N'~DB_CS~') IS NOT NULL DROP DATABASE [~DB_CS~];
IF DB_ID (N'~DB_CI~') IS NOT NULL DROP DATABASE [~DB_CI~];
GO
CREATE DATABASE [~DB_CS~] COLLATE SQL_Czech_Cp1250_CS_AS
GO
CREATE DATABASE [~DB_CI~] COLLATE SQL_Czech_Cp1250_CI_AS
GO

Сначала посмотрим как оно все будет в регистро-независимой базе:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE [~DB_CI~]
go
CREATE TABLE "TestTable" (ColName varchar(10) NOT NULL CONSTRAINT DF_def_CONS DEFAULT ('ABC'))
go
CREATE VIEW [MYVW] AS SELECT 1 AS num
GO
CREATE PROCEDURE mySP AS SELECT 1
GO
ALTER TABLE "TestTable" DROP CONSTRAINT df_def_CONs
GO
DROP TABLE "testtable"
GO
DROP VIEW [MYvw]
GO
DROP PROCEDURE MYsp
GO

Как вы уже, наверно, догадались

Command(s) completed successfully.

А если тоже самое но в контексте базы зависимой от регистра? А вот тогда:

'df_def_CONs' is not a constraint.
Cannot drop the table 'testtable', because it does not exist or you do not have permission.
Cannot drop the view 'MYvw', because it does not exist or you do not have permission.
Cannot drop the procedure 'MYsp', because it does not exist or you do not have permission.

И вновь наши «хитрости» с заключением имен объектов в кавычки/скобки ни к чему не ведут, все упирается в коллейшен! Не удивительно, что резюме этого раздела практически копирует его же из раздела предыдущего:

Зависимость имен объектов уровня базы от регистра букв их составляющих находится в прямой зависимости от той же самой характеристики коллейшена, заданного на том же самом уровне. «Правило-штамп», как минимум, не корректно на все 100%.

Относится к рубрикам: Изучаем T-SQL