SQL-Ex blog

Необычность разных реализаций схемы в базах данных

С точки зрения человека, знакомого с общей идеей схемы, может действительно показаться необычным, что базы данных типа SQL Server, Oracle, MariaDB/MySQL и PostgreSQL каждая слегка (а иногда в значительной степени) по-разному интерпретирует и внедряет схемы. Хотя основная идея схемы как структурированного контейнера или пространства имен для объектов базы данных остается в некоторой степени согласующейся, точная природа, назначение и поведение схем различаются в разных системах.

Почему каждая база данных реализует схему по-разному

1. Исторические или унаследованные причины: Многие системы баз данных начали свою историю десятилетия назад. Со временем, по мере развития, они опирались на существующую архитектуру, что приводило к вариациям в таких функциях, как схема. Например, понятие схемы в Oracle тесно связана с пользователем, что было обусловлено ранними архитектурными решениями Oracle.


2. Философия проектирования и целевая аудитория: Некоторые базы данных проектировались под конкретную аудиторию. Oracle, например, был спроектирован для приложений уровня предприятия, что могло оказать влияние на их проект схемы, ориентированный на пользователя. С другой стороны, MySQL был изначально нацелен на веб-приложения, считая схемы синонимами баз данных, возможно, для упрощения.


3. Соответствие стандарту против практичности: Хотя существуют стандарты ANSI SQL, которые базы данных могут пытаться поддерживать, имеется также стремление соблюсти баланс между поддержкой стандарта и предлагаемыми функциями, которые считаются более практичными или выгодными в первую очередь для пользователей баз данных. Например, PostgreSQL, который нацелен на высокое соответствие стандарту, также вводит продвинутые функции, отсутствующие в стандарте, когда он считает это целесообразным.


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


5. Влияние сообщества и управления: Базы данных с открытыми исходными кодами типа PostgreSQL и MariaDB могут подвергаться влиянию своих сообществ разработчиков. Различные сообщества могут оказывать приоритет определенным функциям или философии, что приводит к различным реализациям.


6. Гибкость и расширяемость: Базы данных могут реализовывать схемы с целью сделать свой подход более гибким или расширяемым для будущих изменений. Это может привести их к принятию нестандартных или уникальных подходов.

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

Взгляд на схему реляционных баз данных

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

Вот краткий обзор схем в упомянутых базах данных:

1. SQL Server:
   
   
   • Определение: В SQL Server схема - это контейнер объектов и может быть назначена конкретным пользователям или ролям в целях безопасности. По умолчанию имеется схема с именем "dbo" (владелец базы данных).
   
   
   • Создание:

     CREATE SCHEMA SchemaName AUTHORIZATION OwnerName;
     
     

   
   
2. Oracle:
   
   
   • Определение: В Oracle понятие схемы тесно связано с пользователем. Когда вы создаете пользователя вы по сути создаете схему. Схема пользователя содержит все объекты, которыми владеет данный пользователь.
   
   
   • Создание: В Oracle схема не создается непосредственно; вы создаете пользователя, а схема создается автоматически.

     CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;

   
   
3. MariaDB/MySQL:
   
   
   • Определение: В MySQL/MariaDB термин "схема" является синонимом "база данных". Это по сути коллекция таблиц, представлений и других объектов базы данных.
   
   
   • Создание:

     CREATE DATABASE SchemaName;

   
   
4. PostrgeSQL:
   
   
   • Определение: PostgreSQL использует схемы в манере, которая наиболее согласуется со стандартным определением реляционных баз данных. База данных PostgreSQL может содержать множество схем, и каждая схема может иметь свой собственный набор таблиц, представлений, индексов и т.п.
   
   
   • Создание:

     CREATE SCHEMA SchemaName;

   
   

Создание схемы в ANSI SQL: Американский национальный институт стандартов (ANSI) устанавливает стандарт для SQL, и базовый синтаксис создания схемы в ANSI SQL имеет вид:

CREATE SCHEMA SchemaName;

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

Схема SQL Server

В SQL Server понятие схемы является многогранным, и понимание ее использования можно разделить на рассмотрение вопросов: "как", "что", "когда" и "где".

Как схема используется в SQL Server?

1. Организация объектов: Схемы используются для логической группировки и управления объектами базы данных, такими как таблицы, представления, хранимые процедуры и т.д. Например, таблица может быть создана в пределах схемы следующим образом: CREATE TABLE ИмяСхемы.ИмяТаблицы (Столбец1 ТипДанных1, Столбец2 ТипДанных2, ...);


2. Управление пространством имен: Схемы обеспечивают пространства имен, которые позволяют объектам, находящихся в разных схемах в одной и той же базе данных, иметь одинаковые имена.


3. Управление разрешениями: Схемы дают возможность более тонкого управления разрешениями. Вы можете предоставлять или отменять разрешения на схему, тем самым воздействуя на все объекты в схеме.

Какие объекты могут быть сопоставлены схеме в SQL Server?

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

• Таблицы


• Представления


• Хранимые процедуры


• Функции


• Типы


• Триггеры


• Индексы


• ...

Когда в SQL Server используются схемы?

1. На стадии проектирования: Схемы часто рассматриваются на стадии начального проектирования базы данных. Стратегия хорошо структурированных схем позволит в будущем облегчить модификацию и масштабирование.


2. Миграция: При переносе или объединении данных из других систем схемы могут использоваться для разделения различных наборов данных.


3. Установка разрешений: При необходимости установить или изменить разрешения на множество связанных объектов базы данных.


4. Рефакторинг: При реорганизации объектов для лучшей ясности или производительности.

Где находятся схемы в SQL Server?

В базе данных SQL Server вы можете найти список схем в Object Explorer (SSMS) в узле конкретной базы данных. Они находятся на том же уровне иерархии, что и таблицы, представления и т.д., но действуют как контейнер или пространство имен для этих объектов.

Назначение схем в SQL Server

1. Логическая организация: Схемы обеспечивают способ логической группировки связанных объектов базы данных, делая базу данных более понятной и управляемой.


2. Безопасность и управление разрешениями: Контролируя доступ к схеме, администраторы могут опосредованно управлять доступом ко всем объектам в пределах этой схемы.


3. Гибкость: Схемы обеспечивают гибкость при проектировании и администрировании баз данных. Например, вы можете передать владение схемой (и ее объектами) от одного пользователя другому.


4. Управление пространством имен: Средство избежать конфликтов при наличии объектов с одинаковыми именами в разных схемах.


5. Миграция и интеграция: Обеспечение более гладкой миграции или интеграции с данными из другой системы посредством группировки импортированных объектов в конкретные схемы.

В SQL Server схемы играют ключевую роль в повышении организации, безопасности, гибкости и управляемости объектов базы данных. Они действуют и как организационные контейнеры, и как границы безопасности, что делает их незаменимыми в различных операциях и стратегиях баз данных.

Схема в Oracle

В Oracle понятие "схема" тесно переплетается с понятием "пользователь". Схема в Oracle - это фактически коллекция объектов, которыми владеет пользователь. Этот проект отличается от некоторых других систем СУБД, таких как SQL Server, где схема больше чем логический контейнер или пространство имен.

Как схемы используются в Oracle?

1. Владение объектами: Каждый объект базы данных принадлежит схеме, и по сути имя схемы - это имя учетной записи пользователя, которая ей владеет.


2. Управление пространством имен: Схемы разделяют объекты с одинаковым именем, но принадлежащих разным пользователям.


3. Безопасность и разрешения: Предоставляя или отменяя привилегии для конкретного пользователя (схемы), вы можете управлять доступом к объектам в схеме.

Какие объекты могут быть связаны со схемой в Oracle?

Почти все объекты базы данных могут принадлежать схеме в Oracle:

• Таблицы


• Представления


• Хранимые процедуры и функции


• Последовательности


• Пакеты


• Синонимы


• Триггеры


• Индексы


• ...

Когда в Oracle используются схемы?

1. Создание и проектирование базы данных: Схемы неявно создаются, когда создается пользователь, и обычно сопоставляются на основе ролей и обязанностей в приложении.


2. Управление разрешениями: Предоставление и отмена доступа к множеству связанных объектов базы данных осуществляется настройкой привилегий пользователя.


3. Организационное разделение: Различные отделы или функциональные обязанности в организации могут получить отдельные схемы для лучшей организации и управления.


4. Внедрение приложений: Различные приложения или модули могут использовать различные схемы.

Где находятся схемы в Oracle?

В Oracle понятие схемы тесно связано с пользователем. Поэтому когда вы видите пользовательские аккаунты в базе данных Oracle (обычно с помощью таких инструментов как Oracle SQL Developer или Oracle Enterprise Manager) вы по сути смотрите на список схем. Объекты каждого пользователя будут находиться в его схеме.

Назначение схем в Oracle

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


2. Безопасность: Безопасность базы данных реализуется на уровне схемы (пользователя). Предоставление или отмена разрешений на схему влияет на все объекты в этой схеме.


3. Управление пространством имен: Обеспечивает сосуществование объектов с одинаковыми именами, принадлежащих разным схемам.


4. Логическое разделение: Обеспечивает логическое разделение объектов базы данных, что может быть полезным для управления большими приложениями или множеством приложений, работающих с одной и той же базой.


5. Управление ресурсами: Oracle проводит политику управления ресурсами на уровне пользователя/схемы, поэтому определенные схемы могут потреблять больше или меньше ресурсов на основе имеющихся требований.

Схемы (пользователи) в Oracle представляют фундамент для проектирования и организации базы данных. Они обеспечивают владение, безопасность, логическую организацию и управление объектами базы данных. Такой интегрированный подход пользователь-схема является отличительным аспектом проектирования базы данных Oracle.

Схема в MariaDB/MySQL

В MariaDB и MySQL термин "схема" является синонимом "база данных". Слова "схема" и "база данных" используются взаимозаменяемо, и хотя во многих системах схема представляет собой логический контейнер внутри базы данных, в MariaDB/MySQL это, по сути, одно и то же.

Как схемы (базы данных) используются в MariaDB/MySQL?

1. Контейнер объектов: Схема (или база данных) в MariaDB/MySQL действует как главный контейнер для таблиц, представлений, хранимых процедур и функций.


2. Управление пространством имен: Каждая схема действует как пространство имен, гарантируя, что объекты типа таблицы имеют уникальные имена в пределах схемы, но могут иметь совпадающие имена в других схемах.


3. Безопасность: Привилегии могут предоставляться или отменяться на уровне схемы, влияя на доступ ко всем объектам внутри этой схемы.

Какие объекты могут быть связаны со схемой (базой данных) в MariaDB/MySQL?

Схема в MariaDB/MySQL может содержать:

• Таблицы


• Представления


• Хранимые процедуры и функции


• Триггеры


• События


• Другие реляционные объекты

Когда схемы (базы данных) используются в MariaDB/MySQL?

1. Создание базы данных: При установке нового приложения или сервиса может быть создана новая схема, чтобы логически разделить данные.


2. Разделение данных: Для мультитенантных приложений могут использоваться отдельные схемы для каждого арендатора или группы арендаторов.


3. Резервирование и восстановление: Для резервирования или восстановления данных для конкретного приложения или службы могут быть выполнены операции на уровне схемы.


4. Развертывание приложения: Различные приложения могут использовать отдельные схемы для лучшего управления и изоляции.

Где находятся схемы (базы данных) в MariaDB/MySQL?

Вы можете просмотреть список схем, используя клиентские инструменты типа MySQL Workbench, phpMyAdmin или даже интерфейс командной строки. В клиенте командной строки команда SHOW DATABASES; выведет список всех схем.

Назначение схем (баз данных) в MariaDB/MySQL:

1. Логическое разделение: Схемы обеспечивают логическое разделение данных, что существенно для организации данных, особенно в среде с множеством приложений или служб.


2. Безопасность и управление разрешениями: Обеспечивают уровень, на котором можно управлять разрешениями, позволяя контролировать тех, кто может иметь доступ или модифицировать данные.


3. Резервирование и обслуживание: Позволяет выполнять операции на уровне схемы, делая более управляемыми такие задачи, как резервирование, восстановление или оптимизация.


4. Управление пространством имен: Гарантирует, что имена таблиц и других объектов могут быть уникальными в пределах схемы, допуская объекты с одинаковыми именами в различных схемах.


5. Гибкость: Обеспечивает гибкость в управлении ресурсами, настройках производительности и конфигурировании на уровне схемы.

Схемы (или базы данных) MariaDB/MySQL играют центральную роль в организации данных, безопасности и управлении. Их назначение - обеспечить логическую структуру, предоставляя организованную, безопасную и эффективную обработку данных.

Схема в PostgreSQL

В PostgreSQL важное значение имеет понятие "роли". В отличие от MariaDB/MySQL, где схема является синонимом базы данных, в PostgreSQL схема является пространством имен в базе данных.

Как в PostgreSQL используются схемы?

1. Пространство имен для объектов: Схема предоставляет пространство имен, делая возможным множеству объектов (таких как таблицы или функции) иметь одинаковые имена, если они находятся в разных схемах.


2. Путь поиска: PostgreSQL использует "путь поиска", чтобы определить, какую схему следует искать при ссылке на объект. Путь поиска может быть установлен таким образом, чтобы вы не указывали имя схемы при доступе к ее объектам.


3. Безопасность: Разрешения доступа огпределяются на уровне схемы. Вы можете управлять тем, какие роли имеют доступ к каким схемам и содержащимся в них объетам.

Какие объекты могут быть связаны со схемой в PostgreSQL?

В схеме PostgreSQL у вас могут быть:

• Таблицы


• Представления


• Функции


• Последовательности


• Типы данных


• Операторы


• Индексы


• Домены


• И другие объекты базы данных

Когда в PostgreSQL используются схемы?

1. Начальное проектирование базы данных: На этапе проектирования базы данных могут быть созданы схемы для разделения функциональности или модулей.


2. Разделение данных: Для мультитенантных приложений для каждого арендатора могут быть созданы отдельные схемы.


3. Изоляция модулей: Модули разных прилождений могут иметь собственные схемы для лучшей организации.


4. Миграция: При слиянии баз данных или таблиц, схемы помогут избежать коллизии имен.

Где находятся схемы в PostgreSQL?

В PostgreSQL схемы находятся внутри конкретной базы данных. Вы можете просмотреть их, используя инструменты типа pgAdmin, DBeaver или интерфейса командной строки (psql). В psql список схем выведет команда \dn.

Назначение схем в PostgreSQL

1. Управление пространством имен: Позволяет сосуществовать объектам базы данных с одинаковыми именами в одной и той же базе, если они находятся в разных схемах.


2. Логическая организация: Облегчает логическую группировку объектов базы данных на основе функциональности, модулей приложений или других критериев.


3. Безопасность: Обеспечивает слой безопасности для управления доступом. Устанавливая разрешения на уровне схемы, вы можете контролировать доступ к множеству объектов.


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


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

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

Заключение

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

Напротив, в MariaDB и MySQL "схема" является синонимом "база данных", служа главным образом в качестве контейнера для таблиц, представлений и других связанных объектов. Это определение делает упор на логическом разделении данных и управлении пространством имен на сервере баз данных. Подход PostgreSQL имеет свои нюансы, где схема действует как пространство имен в пределах конкретной базы данных, допуская существование объектов в идентичными именами, если они находятся в разных схемах. Такая структура в PostgreSQL обеспечивает сложные организационные возможности и повышает безопасность и степень детализации управления в рамках одной базы данных.

Ссылки по теме
1. Создание схемы SQL для организации объектов базы данных, предоставления разрешений и упрощения обслуживания
2. Схемы в PostgreSQL. Изучаем PostgreSQL вместе с Grant Fritchey
3. Роли, схемы, привилегии и владельцы в SQL Server, Oracle и PostgreSQL. Часть II
4. Основы PostgreSQL: начала работы с psql