Важность применения стандартов SQL при разработке прикладных систем

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

Под мобильностью прикладной системы мы понимаем не столько возможность её сравнительно легкого переноса на другую аппаратную платформу, но возможность сравнительно её легкого приспособления к использованию другой СУБД.

Когда мы говорим о возможности приспособления прикладной системы к использованию различных СУБД, то, конечно, имеем в виду не произвольные СУБД, а системы, поддерживающие международный стандарт языка SQL. Другими словами, мы предполагаем прямое использование языка SQL при разработке прикладной системы, а также то, что все взаимодействия с системой БД производятся только с использованием этого языка. На самом деле, это несущественно ограничивает возможный набор СУБД. Реализация стандарта SQL вообще говоря означает, что для работы с БД не требуется привлечение никакого другого языка.

К сожалению, на практике дела обстоят так, что в разных СУБД, производители которых объявляют их соответствующими стандарту SQL, достаточно часто реализуются немного разные языки (диалекты SQL). Частично это объясняется недостатками самого стандарта, частично - историческими и конъюнктурными обстоятельствами.

Принятый в 1992 г. международный стандарт языка SQL был тоже не лишен недостатков, но в то же время являлся существенно более точным и полным, чем SQL/89. SQL/92 не только восполнял недостатки SQL/89, но содержал также много новых свойств.

SQL:1999 является техническим определением следующего поколения SQL - стандартного метода общения с ведущими базами данных. Приближающаяся совместная публикация Американским национальным институтом стандартов (ANSI) и Международной организацией по стандартизации (ISO) этого стандарта вводит в употребление новые технологии в области интеграции объектной ориентации и Java. Oracle внес существенный вклад в процесс формулирования стандарта, исполняя обязанности председателя технического комитета ANSI и редактора стандарта ANSI/ISO.

Компания Oracle, с самого момента ее создания, около семи лет тому назад, является активным участником группы по созданию стандарта SQL:1999. За это время, работая совместно с такими производителями программного обеспечения баз данных какIBM, Sybase и Informix, Oracle помог сформировать, а затем и пересмотреть стандарт, чтобы он соответствовал изменившимся требованиям нового поколения баз данных для вычислений в Интернет и работы с хранилищами данных.

SQL:1999 дает возможность поддерживать объектную ориентацию, включая определенные пользователем типы с одиночным наследованием, динамические методы и полную инкапсуляцию. В системах объектно-реляционных баз данных, например, Oracle8 i, эти возможности уже реализованы. Объектная модель SQL:1999 очень похожа на объектную модель Java, что сильно облегчает совместное использование двух этих языков. Новая часть стандарта SQL:1999, которую планируется опубликовать в 2000 году, позволяет включать операторы SQL в Java-программы, в то время как другие, еще готовящиеся возможности, позволят использовать методы Java в коде SQL

Формальное название стандарта SQL - это ISO/IEC 9075 "Database Language SQL" (Язык баз данных SQL). Время от времени выпускается пересмотренная версия этого стандарта; наиболее свежая была выпущена в 2003 году. Эта версия известна как ISO/IEC 9075:2003 или просто как SQL:2003. Предыдущие версия назывались SQL:1999 и SQL92. Каждая версия замещает предыдущую, так что требования соответствия стандарту более ранних версий не имеют официальной силы. Разработка PostgreSQL имеет целью достижение совместимости с последней официальной версией стандарта, где такая совместимость не противоречит традиционным возможностям или здравому смыслу. Проект PostgreSQL не был представлен в Рабочей Группе ISO/IEC 9095 во время подготовки SQL:2003. Но несмотря на это, многие из возможностей, требуемых по стандарту SQL:2003 уже поддерживаются PostgreSQL, хотя иногда и с небольшими отличиями в синтаксисе или функции. Дальнейшее сближение со стандартом можно ожидать в следующих выпусках.

SQL92 определяет три списка возможностей по достигнутой совместимости: Entry, Intermediate и Full (Начальная, Промежуточная и Полная). Большинство СУБД претендующих на совместимость со стандартом SQL, соответствовали только уровню Entry, так как полный список возможностей для уровней Intermediate и Full был или слишком объёмный или приводил к конфликту с существующим поведением СУБД. Начиная с SQL:1999 определяет большой список отдельных возможностей, в отличие от неэффективного широкого разделения на три уровня в SQL92. Большой подсписок этих возможностей представлят собой "Core" (основные) возможности, каждая из которых должна поддерживаться совместимой со стандартом реализацией SQL. Оставшиеся возможности являются просто необязательными. Некоторые необязательные возможности группируются вместе в форме "пакетов", на соотвествие с которыми могут претендовать реализации SQL.

Стандарт SQL:2003 также разбит на несколько частей. Каждая часть имеет краткое название. Заметим, что эти части пронумерованы непоследовательно.

· ISO/IEC 9075-1 Framework (SQL/Framework)

· ISO/IEC 9075-2 Foundation (SQL/Foundation)

· ISO/IEC 9075-3 Call Level Interface (SQL/CLI)

· ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)

· ISO/IEC 9075-9 Management of External Data (SQL/MED)

· ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)

· ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)

· ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)

· ISO/IEC 9075-14 XML-related specifications (SQL/XML)

PostgreSQL реализует только части 1, 2 и 11. Часть 3 похожа на ODBC интерфейс, а часть 4 похожа на язык программирования PL/pgSQL, но в этом случае точное соответствие не планировалось и не проверялось.

PostgreSQL поддерживает большинство главных возможностей SQL:2003. Из свыше 164 обязательных возможностей, требуемых для полной совместимости "Core", PostgreSQL соответствует по крайней мере 150. Кроме того, существует длинный список поддерживаемых необязательных возможностей. Возможно это и не имеет значение, но в момент написания этих слов, ни одна текущая версия, ни одной СУБД, не соответствует полностью "Core" SQL:2003.

 

 

Тема 7: SQL – операции определения данных (DDL)

Введение

Изучение языка SQL мы будем проводить на примере диалекта используемого в СУБД MySQL. При этом по возможности мы будем отмечать, что та или иная возможность является расширением, реализованным в MySQL и отсутствует в стандарте.

Дальнейший материал во многом является переводом документации MySQL.

Типы данных

MySQL поддерживает несколько типов столбцов, которые можно разделить на три категории: числовые типы данных, типы данных для хранения даты и времени и символьные (строковые) типы данных. Вначале дадим обзор всех возможных типов и приведём требования по хранению для каждого типа столбца, затем опишем более подробно по каждой категории свойства типов.

Ниже перечислены типы столбцов, поддерживаемые MySQL. В описаниях используются следующие обозначения:

• M – указывает максимальный размер вывода. Максимально допустимый размер вывода составляет 255 символов.
• D – употребляется для типов данных с плавающей точкой и указывает количество разрядов, следующих за десятичной точкой. Максимально возможная величина составляет 30 разрядов, но не может быть больше, чем M-2.

Квадратные скобки (‘[’ и ‘]’) указывают группы необязательных признаков (параметров).

Предупреждение: следует помнить, что при выполнении вычитания между числовыми величинами, одна из которых относится к типу UNSIGNED (без знака), результат будет беззнаковым!

Целые типы данных

TINYINT[(M)] [UNSIGNED] [ZEROFILL] Очень малое целое число. Диапазон со знаком от
-128 до 127. Диапазон без знака от 0 до 255.

BIT, BOOL Являются синонимами для TINYINT(1).

SMALLINT[(M)] [UNSIGNED] [ZEROFILL] Малое целое число. Диапазон со знаком от
-32768 до 32767. Диапазон без знака от 0 до 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

Целое число среднего размера. Диапазон со знаком от -8388608 до 8388607. Диапазон без знака от 0 до 16777215.

INT[(M)] [UNSIGNED] [ZEROFILL]

Целое число нормального размера. Диапазон со знаком от -2147483648 до 2147483647. Диапазон без знака от 0 до 4294967295.

INTEGER[(M)] [UNSIGNED] [ZEROFILL] Синоним для INT.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Большое целое число. Диапазон со знаком от -9223372036854775808 до 9223372036854775807. Диапазон без знака от 0 до 18446744073709551615.

Действительные числа

FLOAT(точность) [UNSIGNED] [ZEROFILL]

Число с плавающей точкой. Атрибут точности может иметь значение <=24 для числа с плавающей точкой обычной (одинарной) точности и между 25 и 53 - для числа с плавающей точкой удвоенной точности.

Следует отметить, что использование типа данных FLOAT может привести к неожиданным проблемам, так как все вычисления в MySQL выполняются с удвоенной точностью. Данный синтаксис обеспечивает совместимость с ODBC.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

Малое число с плавающей точкой обычной точности. Допустимые значения: от
-3,402823466E+38 до -1,175494351E-38, 0, и от 1,175494351E-38 до 3,402823466E+38. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D - количество разрядов, следующих за десятичной точкой. Обозначение FLOAT без указания аргументов или запись вида FLOAT(X), где X <=24 справедливы для числа с плавающей точкой обычной точности.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

Число с плавающей точкой удвоенной точности нормального размера. Допустимые значения: от -1,7976931348623157E+308 до -2,2250738585072014E-308, 0, и от 2,2250738585072014E-308 до 1,7976931348623157E+308. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D - количество разрядов, следующих за десятичной точкой.

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

Данные обозначения являются синонимами для DOUBLE.

 

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

“Неупакованное” число с плавающей точкой. Ведет себя подобно столбцу CHAR, содержащему цифровое значение. Термин “неупакованное” означает, что число хранится в виде строки и при этом для каждого десятичного знака используется один символ. Разделительный знак десятичных разрядов, а также знак ‘-’ для отрицательных чисел не учитываются в M (но место для них зарезервировано). Если атрибут D равен 0, величины будут представлены без десятичного знака, т.е. без дробной части. Максимальный интервал значений типа DECIMAL тот же, что и для типа DOUBLE, но действительный интервал для конкретного столбца DECIMAL может быть ограничен выбором значений атрибутов M и D. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Если атрибут D не указан, его значение по умолчанию равно 0. Если не указан M, его значение по умолчанию равно 10.

Заметим, что для хранения точных дробных чисел рекомендуется использовать столбцы типа DECIMAL, а не FLOAT, так как в столбцах типа DECIMAL данные хранятся без округления.

 

Типы даты и времени

 

DATE

Дата. Поддерживается интервал от '1000-01-01' до '9999-12-31'. MySQL выводит значения DATE в формате 'YYYY-MM-DD', но можно установить значения в столбец DATE, используя как строки, так и числа.

 

DATETIME

Комбинация даты и времени. Поддерживается интервал от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'. MySQL выводит значения DATETIME в формате 'YYYY-MM-DD HH:MM:SS', но можно устанавливать значения в столбце DATETIME, используя как строки, так и числа.

 

TIMESTAMP[(M)]

Временная метка. Интервал от '1970-01-01 00:00:00' до некоторого значения времени в 2037 году. MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD или YYMMDD в зависимости от значений M: 14 (или отсутствующее), 12, 8, или 6; но можно также устанавливать значения в столбце TIMESTAMP, используя как строки, так и числа. Столбец TIMESTAMP полезен для записи даты и времени при выполнении операций INSERT или UPDATE, так как при этом автоматически вносятся значения даты и времени самой последней операции, если эти величины не введены программой. Можно также устанавливать текущее значение даты и времени, задавая значение NULL. Аргумент M влияет только на способ вывода столбца TIMESTAMP; для хранения его значений всегда используется 4 байта. Следует учитывать, что столбцы TIMESTAMP(M), где M равно 8 или 14, представляют собой числа, в то время, как столбцы TIMESTAMP(M) с иным значением аргумента M являются строками. Это убеждает, что можно надежно сделать дамп и восстановить таблицу с этими типами столбцов!

 

TIME Время. Интервал от '-838:59:59' до '838:59:59'. MySQL выводит значения TIME в формате 'HH:MM:SS', но можно устанавливать значения в столбце TIME, используя как строки, так и числа.

YEAR[(2|4)]

Год в двухзначном или четырехзначном форматах (по умолчанию формат четырехзначный). Допустимы следующие значения: с 1901 по 2155, 0000 для четырехзначного формата года и 1970-2069 при использовании двухзначного формата (70-69). MySQL выводит значения YEAR в формате YYYY, но можно задавать значения в столбце YEAR, используя как строки, так и числа.

Строковые типы данных

[NATIONAL] CHAR(M) [BINARY]

Строка фиксированной длины, при хранении всегда дополняется пробелами в конце строки до заданного размера. Диапазон аргумента M составляет от 0 до 255 символов (от 1 до 255 в версиях, предшествующих MySQL 3.23). Концевые пробелы удаляются при выводе значения. Если не задан атрибут чувствительности к регистру BINARY, то величины CHAR сортируются и сравниваются как независимые от регистра в соответствии с установленным по умолчанию алфавитом.

Атрибут NATIONAL CHAR (или его эквивалентная краткая форма NCHAR) представляет собой принятый в ANSI SQL способ указания, что в столбце CHAR должен использоваться установленный по умолчанию набор символов (CHARACTER). В MySQL это принято по умолчанию. CHAR является сокращением от CHARACTER. MySQL позволяет создавать столбец типа CHAR(0).

В основном это полезно, если необходимо обеспечить совместимость с некоторыми старыми приложениями, которые зависят от наличия столбца, но реально эту величину не используют. Кроме того, такая возможность может очень пригодиться в случае, если необходим столбец, который может содержать только 2 значения, а именно CHAR(0) (т.е. столбец, который не определен как NOT NULL, занимает только один бит и принимает только 2 значения: NULL или "").

CHAR Это синоним для CHAR(1).

 

[NATIONAL] VARCHAR(M) [BINARY]

Строка переменной длины. Примечание: концевые пробелы удаляются при сохранении значения (в этом заключается отличие от спецификации ANSI SQL). Диапазон аргумента M составляет от 0 до 65 535 символов (от 1 до 255 в версиях, предшествующих MySQL Version 4.0.2). Если не задан атрибут чувствительности к регистру BINARY, то величины VARCHAR сортируются и сравниваются как независимые от регистра. Термин VARCHAR является сокращением от CHARACTER VARYING.

 

TINYBLOB, TINYTEXT Столбец типа BLOB или TEXT с максимальной длиной 255 (2^8 - 1) символов.

 

BLOB, TEXT Столбец типа BLOB или TEXT с максимальной длиной 65535 (2^16 - 1) символов.

 

MEDIUMBLOB, MEDIUMTEXT Столбец типа BLOB или TEXT с максимальной длиной 16777215 (2^24 - 1) символов

 

LONGBLOB, LONGTEXT Столбец типа BLOB или TEXT с максимальной длиной 4294967295 (2^32 - 1) символов. Следует учитывать, что в настоящее время протокол передачи данных сервер/клиент и таблицы MyISAM имеют ограничение 16 Мб на передаваемый пакет/строку таблицы, поэтому пока нельзя использовать этот тип данных в его полном диапазоне.