Схема Schema с точки зрения базы данных представляет собой контейнер объектов типа таблиц, триггеров, хранимых процедур и т.п. В данной статье будут рассмотрены вопросы создания и удаления схемы БД следующих СУБД :

Oracle : Schema привязывается к пользователю, т.е. наименование схемы, как правило, является учетной записью пользователя. Схема создается при создании пользователем первого объекта, и все последующие объекты созданные этим пользователем становятся частью этой схемы. Кроме этого Oracle позволяет создавать схему как контейнер одновременно с объектами базы данных.
MSSQL : в Microsoft SQL Server начиная с версии 2005 жесткая связь между пользователями и схемами была отменена. Пользователи могут получить доступ на выполнение определенных операций с объектами схемы : чтение, запись, обновление или выполнение.
PostgreSQL : Schema создается внутри объекта базы данных. Сервер может управлять несколькими базами данных, каждая из которых может включать несколько схем. То есть, как и в MSSQL, схема не связана с учетной записью пользователя.
MySQL : понятие Schema имеет тождественный смысл с Database. База данных Database является контейнером объектов, к которым пользователь получает доступ.
Derby : Schema не имеет жесткой связи с пользователем и является контейнером объектов, для доступа к которым пользователь должен иметь соответствующие привилегии.

Создание схемы, CREATE SCHEMA

Для создания схемы необходимо использовать SQL скрипт CREATE SCHEMA. Разные схемы могут включать одноименные объекты. При обращении к объектам разных схем необходимо указывать наименование схемы как префикс. Для создания схемы пользователь должен иметь соответствующие привилегии. Конечно же, superuser'ы данной привилегией владеют.

Создание схемы Oracle

Oracle относится к тем платформам СУБД, которые не имеют явной поддержки команды CREATE SCHEMA. Однако он все же неявно создаёт схему, когда пользователь создаёт свой первый объект базы данных. Данная СУБД использует команду "CREATE SCHEMA" для создания за одну транзакцию таблиц и представлений вместе с предоставлением доступа к ним.

Необходимо отметить, что Oracle разрешает дополнительно использовать в инструкции CREATE SCHEMA стандартные скрипты CREATE TABLE, CREATE VIEW и GRANT. Нельзя использовать любые расширения этих команд, имеющиеся в Oracle, если эти команды включены в инструкцию CREATE SCHEMA. Синтаксис создания объектов со схемой.

CREATE SCHEMA AUTHORIZATION schema
  options
    CREATE TABLE
    CREATE VIEW
    GRANT

В следующем примере для схемы "painter"" создаются таблица и представление. Коме этого в инструкции CREATE SCHEMA определен доступ к объектам.

CREATE SCHEMA AUTHORIZATION painter
    CREATE TABLE paint (
        paint_id    NUMBER PRIMARY KEY,
        paint_size  NUMBER,
        colour      VARCHAR2(10)
    )
    CREATE VIEW large_paints AS 
        SELECT paint_id, colour 
		    FROM paint 
			   WHERE paint_size = 100

    GRANT select ON large_paints TO scott;

Порядок команд создания объектов и предоставления прав доступа в инструкции CREATE SCHEMA не критичен, но все же следует соблюдать синтаксис. Oracle выполняет инструкцию CREATE SCHEMA только в том случае, если все входящие в нее инструкции CREATE и GRANT были выполнены успешно.

Создание схемы MS SQL

В СУБД MS SQL при помощи транзакции CREATE SCHEMA можно создать схему одновременно с созданием в ней таблиц, представлений и предоставить или запретить доступ на эти объекты с использованием операторов GRANT, DENY или REVOKE.

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::= {
    schema_name | AUTHORIZATION owner_name | 
    schema_name AUTHORIZATION owner_name
}

<schema_element> ::= { 
    table_definition | 
    view_definition  | 
    grant_statement  | 
    revoke_statement | 
    deny_statement 
}

Параметры скрипта :

schema_name
Идентификационного наименование схемы.
AUTHORIZATION owner_name
Учетная запись пользователя базы данных, который является владельцем схемы.
table_definition
SQL скрипт создания таблицы внутри схемы. Пользователь должен иметь права CREATE TABLE в текущей базе данных.
view_definition
SQL скрипт создания представления внутри схемы. Пользователь должен иметь права CREATE VIEW в текущей базе данных.
grant_statement
Предоставление разрешения на любой объект, за исключением новой схемы.
revoke_statement
Отмена разрешения на любой объект, за исключением новой схемы.
deny_statement
Запрещение разрешения на любой объект, за исключением новой схемы.

Транзакция CREATE SCHEMA являются атомарной. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, то ни один из указанных объектов не создается и ни одно разрешение не предоставляется.

Объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.

При помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.

Создание схемы PostgreSQL

Новая схема создается в текущей базе данных сервера, с которым установлено соединение. Наименование схемы должно быть уникально для данной Database.

CREATE SCHEMA schema_name [ AUTHORIZATION username ] [ schema_element [ ... ] ]

CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

Параметры скрипта :

username
имя пользователя (role name) владельца схемы. Если username отсутствует, то будет использоваться имя пользователя, исполняющего данный скрипт. Чтобы создать схему для владельца с другой ролью (role) необходимо иметь права данной роли или быть superuser'ом.
schema_element
определяет SQL скрипт одновременного создания внутри схемы объектов. Вместе со схемой можно создать TABLE, VIEW, INDEX, SEQUENCE, TRIGGER с получением на них соответствующих прав. Если в скрипте присутствует AUTHORIZATION username то все права будут принадлежать username. После создания схемы можно также создать в ней другие объекты.

Примеры создания схемы в PostgreSQL :

CREATE SCHEMA orders;

CREATE SCHEMA AUTHORIZATION alex;

CREATE SCHEMA customers;
CREATE TABLE customers.companies (id name not null, 
                                  name varchar(64) not null, 
                                  address varchar(128),
                                  registered date);
CREATE VIEW customers.v_companies AS
    SELECT id, name, address FROM customers.companies WHERE address IS NOT NULL;

Примечание : Согласно SQL стандарту, владелец схемы всегда является "хозяином" всех находящихся внутри объектов. PostgreSQL, также как и MSSQL, разрешает создание внутри схем объектов, "хозяином" которых может быть не владелец схемы, но имеющий соответствующие привилегии данной схемы.

Создание базы данных MySQL

В MySQL понятие базы данных database равносильно понятию schema в других СУБД типа MSSQL, PostgreSQL, Derby и имеет тот же смысл - контейнер не привязанных к пользователю объектов. Создание базы данных выполняется с помощью оператора CREATE DATABASE.

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];

Параметры скрипта :

db_name
Имя создаваемой базы данных.
IF NOT EXISTS
Если данный паарметр не указать, то при создании базы данных с уже существующим именем, возникнет ошибка выполнения команды.
CHARACTER SET
Определение кодировки таблиц базы данных.
COLLATE
Определение порядка сортировки данных.

Если при создании таблицы эти параметры CHARACTER SET и COLLATE не указываются, то кодировка и порядок сортировки вновь создаваемой таблицы берутся из значений, указанных для текущей базы данных.

Примеры использования CREATE DATABASE

CREATE DATABASE forum

CREATE DATABASE forum CHARACTER SET utf8 COLLATE utf8_general_ci;

Создание схемы Derby

CREATE SCHEMA { [ schemaName AUTHORIZATION username ] | 
                [ schemaName ] | [ AUTHORIZATION username ] }

Наименование схемы не должно содержать более 128 символов и быть уникальным внутри базы данных. Также наименование не должно начинаться с префикса SYS.

Только владелец базы данных может создавать схему с наименованием, отличным от имени/логина пользователя, и только владелец базы данных может определять AUTHORIZATION username с именем/логином пользователя, отличным от текущего логина.

Примечание : username может принадлежать только пользователю, а не role.

CREATE SCHEMA CUSTOMERS AUTHORIZATION serg;

CREATE SCHEMA AUTHORIZATION loran;

Удаление схемы, DROP SCHEMA

Для удаления схемы необходимо использовать SQL скрипт drop schema.

Удаление схемы Oracle

Для удаление схемы СУБД Oracle необходимо удалить пользователя; объекты схемы удаляются автоматически :

DROP USER user_name [CASCADE];

Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем) объекты.

Удаление схемы MSSQL

DROP SCHEMA schema_name

Удаляемая схема не должна содержать никаких объектов. Если схема содержит объекты, выполнение инструкции DROP заканчивается сбоем. Сведения о схемах можно увидеть в представлении каталога sys.schemas.

Удаление схемы PostgreSQL

Схема может быть удалена только её владельцем или superuser'ом. Необходимо помнить, что владелец owner может удалить схему и все содержащиеся в ней объекты даже если они ему не принадлежат.

При удалении схемы в PostgreSQL можно дополнительно включить параметры :

DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ]

Параметры скрипта :

IF EXISTS
Проверка наличия схемы. Если схемы нет, то исключительная ситуация не возникнет.
CASCADE
Автоматически удалять объекты, содержащиеся в схеме.
RESTRICT
Не удалять схему, если она содержит объекты. Этот параметр используется по умолчанию.

Пример удаления схемы orders вместе с содержащимися в ней объектами :

DROP SCHEMA orders CASCADE;

Удаление базы данных MySQL

В СУБД MySQL удалить можно не только пустую базу данных.

DROP DATABASE [IF EXISTS] db_name

Если не указать параметр IF EXISTS, то при попытке удаления не существующей базы данных, возникнет ошибка выполнения команды. Данный параметр доступен в MySQL 3.22 и более поздних версиях. При выполнении команды DROP DATABASE удаляется как сама база данных, так и все объекты, которые в ней находятся.

В следующем примере удаляется база данных "forum" :

DROP DATABASE forum

Удаление схемы Derby

В СУБД Derby удалить можно только пустую схему. Схемы SYS и APP (схема пользователя по умолчанию) не могут быть удалены.

DROP SCHEMA schema_name RESTRICT;

Ключевое слово RESTRICT является обязательным и обязывает выполнение проверки наличия объектов в удаляемой схеме.

Обновление схемы, ALTER SCHEMA

В SQL стандарте скрипт ALTER SCHEMA не определен.

В PostgreSQL владельца или наименование схемы можно изменить скриптом ALTER SCHEMA.

ALTER SCHEMA name OWNER TO newowner;
ALTER SCHEMA name RENAME TO newname;

Чтобы использовать ALTER SCHEMA необходимо быть владельцем схемы и иметь соответствующие привилегии. При изменении наименования схемы нужно иметь привилегии CREATE для текущей базы данных. Чтобы сменить владельца, необходимо быть членом соответствующей роли и иметь в ней привилегии CREATE.

В СУБД MSSQL с помощью скрипта ALTER SCHEMA можно перенести объекты из одной схемы в другую.

ALTER SCHEMA schema_name TRANSFER [ <entity_type> :: ] securable_name [;]

<entity_type> ::= {
    Object | Type | XML Schema Collection
}

Пользователи и схемы в MSSQL полностью разделены. Инструкция ALTER SCHEMA применяется только для перемещения объектов между схемами в пределах одной базы данных. В следующем примере схема Customers изменяется путем перемещения в нее таблицы Cities из схемы Persons.

ALTER SCHEMA Customers TRANSFER Persons.Cities;