Всем привет!
 
Сегодня в этом канале нас уже больше 500 человек. И мы благодарны каждому подписчику, каждому неравнодушному к теме СУБД специалисту.
 
Добавляясь в наш канал вы видите, как по шагам идёт развитие SoQoL. Мы стараемся рассказывать постепенно обо всём, спрашиваем вашего мнения и радуемся как дети каждому, кто прочитал, протестировал, прислал обратную связь.

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

После выпуска бета-версии SoQoL многие из вас скачали её, начали активно тестировать и присылать обратную связь. Это очень ценно!

Особую благодарность мы хотели бы выразить подписчику с ником andyinx (Андрей). Он буквально засыпал нас вопросами и предложениями, подкреплёнными обоснованиями из собственного опыта. И за особую активность и вклад в развитие SoQoL мы презентовали Андрею фирменный подарок (см. фото).

Ваши знания и опыт помогают создавать СУБД, удобную для пользователя. Мы горды, что у нас есть такие коллеги!

Спасибо вам за всё, что вы делаете в сфере ИТ. Ваша работа и вклад неоценимы!
 
Оставайтесь с нами и мы будем продолжать радовать вас не только новыми постами и разработками в нашей СУБД SoQoL, но и подарками.
 
P.S. Кстати, свежие сборки СУБД SoQoL всегда можно скачать здесь https://t.me/+Wa_WxMvKkwsyMjhi

«Сильно не хватает…»
 
«Сильно не хватает функций работы с датой-временем. типа dateadd, datediff, datepart как в mssql,» — такое сообщение мы получили от одного из подписчиков.
 
И действительно, их не хватает...

Но «не хватает» — это одно, а вот как должно быть — это другое. И начали с поисков ответов на вопросы:
- какие функции реализованы в других СУБД;
- какие наиболее востребованы;
- нужны ли доработки известных функций.
 
Итак, если кратко по поводу первого пункта:
1. Для извлечения части даты из заданного значения
- в PostgreSQL, Oracle и MySQL это функция EXTRACT с одинаковым синтаксисом:
 

EXTRACT (unit FROM date)

 
- в SQL Server и SQLite наименование функции отличается (DATEPART и STRFTIME соответственно), но синтаксис схож:
 

DATEPART (unit, date)

 
2. Для вычисления разницы в заданном элементе между заданными датами
 - в MySQL и SQL Server это функции с одинаковым наименованием, но отличным синтаксисом:
 
В первом случае это DATEDIFF (date1, date2) — возвращает разницу между датами date1 и date2 в днях.
 
Во втором случае это DATEDIFF (datepart, date1, date2) — возвращает разницу между датами date1 и date2 в заданном интервале. Это вариант удобнее, т.к. можно выбрать ту часть даты, разницу в которой нужно вычислить.
 
- в PostgreSQL и Oracle можно выполнить date2 - date1 и получить разницу между date1 и date2 в виде числа дней.
 
3. Для выполнение операции добавления / вычитания по отношению к элементу даты
В MySQL и SQL Server это

DATE_ADD (date, INTERVAL value unit) 

и DATEADD (unit, value, date) соответственно. Реализация, как и названия функций, очень похожа.
 
В Oracle реализована функция ADD_MONTHS (date, num_months)
 
Также в Oracle и PostgreSQL есть и тип данных Interval, позволяющий выполнять операции с заданными интервалами, такие как сложение, вычитание и сравнение.
 
Например, в PostgreSQL для добавления к дате одного часа можно выполнить:
DATE '2001-09-28' + INTERVAL '1 HOUR' → 2001-09-28 01:00:00
 
Разные СУБД + разные пользователи = разные решения. Поэтому по второму и третьему пункту информация пока собирается. Мы этот вопрос изучаем, чтобы понять — какое решение наиболее оптимально. В общем, вопрос пока открыт.

Остановись мгновение

Функции CURRENT в СУБД SoQoL используется для получения текущего значения:
- даты и времени (CURRENT_DATE и CURRENT_TIMESTAMP);
- имени БД (CURRENT_DBNAME);
- имени пользователя БД (CURRENT_USER);
- имени схемы по умолчанию текущего пользователя (CURRENT_SCHEMA);
- уровня изоляции транзакции (CURRENT_ISOLATION_LEVEL).
 
Эти функции могут быть полезными, когда нужно работать с текущими значениями в запросах или выражениях.
 
Например:
В таблице продаж номер сделки заполняется значениями из генератора последовательности, а имя продавца и дату необходимо вносить актуальные на момент сделки:

create table SALE (NUMBER bigint DEFAULT NUMSEQ.nextval primary key, SELLER varchar (50), DATE date);
 
В этом случае запрос с функциями CURRENT добавит в таблицу текущие имя пользователя БД (продавца) и дату соответственно:
 
insert into SALE (SELLER, DATE) values (current_user, current_date) returning NUMBER, SELLER, DATE;
 
NUMBER | SELLER | DATE
--------------+---------------+--------------------------
1 | IvanovaDS | 2023-07-26 09:00:43
 
Кроме вышеуказанных функций в СУБД SoQoL реализованы функции:
- SESSION_ID – возвращает номер текущей сессии пользователя;
- TRANSACTION_ID – возвращает номер текущей транзакции пользователя;
- VERSION – возвращает текущую версию сервера.
 
 А что по поводу упомянутых функций в других СУБД?
 
В Oracle, PostgreSQL, MySQL также есть функции, возвращающие текущие значения дат, времени и т.д., которые могут отличаться названием.
 
Например, в MS SQL Server текущую дату вернёт функция GETDATE. Для получения текущего значения даты в конкретном часовом поясе необходимо вместо GETDATE использовать CURRENT_TIMESTAMP с функцией AT TIME ZONE.
 
В Oracle, PostgreSQL функция с приставкой LOCAL вместо CURRENT возвращает текущие дату и время в часовом поясе сеанса.

Какие функции у вас наиболее востребованы? В решении каких задач их используете?

Право имею или…
 
Никто не будет спорить с тем, как важно понимать и разграничивать права доступа для обеспечения безопасности в базах данных. И для работы с БД недостаточно просто создать пользователя и дать ему пароль.
 
Для подключения и работы необходимо предоставить пользователю права, позволяющие выполнять требуемые действия.
 
В SoQoL реализованы предустановленные:
1. Системные привилегии, которые относятся к управлению самой БД. Такие привилегии дают право, например, на подключение к БД, управление пользователями и ролями и т.д.
2. Объектные привилегии позволяют проводить манипуляции с данными конкретного объекта.
3. Роль PUBLIC, которая изначально пуста и предоставляется всем пользователям БД.
4. Системные роли, включающие в себя те или иные системные привилегии.
 
Предустановленных пока четыре роли, но они позволяют крупными блоками разграничить возможности их обладателей.
 
Изменять предустановленные привилегии и роли нельзя (кроме PUBLIC). При этом можно:
- создавать новые роли;
- наполнять их нужным набором привилегий;
- представлять роли пользователям БД или другим ролям.
 
Например, для разграничения доступа сотрудников разных должностей можно для каждой должности создать отдельную роль, включающую в себя отличный друг от друга набор привилегий:
 

create role "Младший_менеджер";
create role "Старший_менеджер";

 
Для должности "Младший_менеджер", например, должно быть допустимо только добавлять данные в таблицу "Продажи" и выполнять выборку данных из неё. Поэтому предоставляем ему эти привилегии:
 
grant INSERT, SELECT on "Продажи" to " Младший_менеджер";
 
При необходимости набор привилегий в каждой роли можно изменить: добавить новые привилегии / роли или отозвать ранее предоставленные.
 
Создавать, удалять, предоставлять привилегии и роли может только пользователь с привилегией администратора. И в каждой базе данных СУБД SoQoL автоматически создаёт пользователя с такой привилегией.
 
Но текущая реализация ролей и привилегий – это лишь одна из первых ступенек к качественному обеспечению безопасности. В будущем планируется расширение перечня привилегий и ролей, а также реализация мандатного доступа. И эта реализация будет осуществляться в соответствии с регламентирующими документами ФСТЭК в том числе.

Скажите, наличие каких привилегий для вас наиболее важно? Пользовался ли кто-то из вас при разработке приложений разграничениями мандатного доступа? С какими проблемами при этом сталкивались?

Все вместе, но каждый индивидуален
 
«Я правильно понимаю, что если я подключен к БД1, то не могу выполнить запрос к БД2?» 
 
Это вопрос одного из наших подписчиков. Он написал, что является типичным пользователем MS SQL и в этой СУБД допустимо «из подключения к БД1 выполнять запросы к БД2».
 
В Microsoft SQL Server можно использовать функции OPENQUERY и OPENROWSET для выполнения запросов из одной базы данных в другую. А можно воспользоваться инструментом sp_addlinkedserver для создания связанного сервера и установить связь между двумя различными экземплярами Microsoft SQL Server или другими базами данных.
 
И MS SQL не единственная с возможностью установления связи между двумя БД, только реализация отличается.
 
В Oracle для выполнения запросов из одной базы данных в другую можно использовать инструмент Database Link, с помощью которого создаются ссылки на удалённые базы данных и выполняются запросы к этим БД.
 
В MySQL можно использовать функции CONNECT и FEDERATED для выполнения запросов из одной базы данных в другую. При этом CONNECT позволяет подключаться к удалённым серверам MySQL и выполнять запросы к ним, а FEDERATED позволяет создавать таблицы, которые являются ссылками на другие базы данных MySQL.
 
А как в SoQoL?
 
В бета-версии SoQoL так: каждая база данных обособлена, но все базы данных находятся под управлением одного сервиса. Поэтому:
 
– в каждой базе данных свой администратор и пользователи, не имеющие доступа к данным других баз данных.
 
– находясь в подключении к конкретной базе данных можно делать любые запросы, но только по отношению к её объектам.
 
– находясь в подключении к сервису можно выполнять запросы к объектам сервиса и управлять базами данных только «снаружи», как целым объектом. Например, создавать БД, останавливать её, запускать, удалять и т.д.
 
А как же взаимосвязь? Возможно ли в светлом будущем связать базы данных SoQoL между собой?
 
Скажем так: в SoQoL есть техническая возможность сделать поддержку баз данных как каталогов (по аналогии, например, с MS SQL), но это вопрос времени и сил. Поэтому задача такая есть, но на данный момент для нас она не самая приоритетная.
 
Поделитесь своим опытом: в каких задачах вам приходилось выполнять запрос из одной БД к другой? Почему так сложилось? Какой вариант реализации такого доступа вам видится наиболее удобным? Какие проблемы нужно учесть при проектировании аналогичных средств в SoQoL?

Запуск… проверка…
 
И нет, речь не про спутники и ракеты. Сегодня речь пойдёт о конфигурационном файле СУБД SoQoL.

Знакомьтесь: soqol_config.yml
 
При запуске сервер проверяет наличие в текущей папке конфигурационного файла и загружает настройки из него. Если таковой не находится, то он создаётся автоматически со значениями по умолчанию.
 
А есть другой вариант запуска сервера
 
Запуск сервера с указанием имени конфигурационного файла. Если файл с таким именем есть – всё ок, а вот если «по указанному адресу нет адресата», то запуск закончится ошибкой. Это сделано для чёткого понимания пользователя, с каким конфигурационным файлом запускается система.
 
Но что мы так трясёмся с этим файлом?
 
Конфигурационный файл СУБД содержит настройки и параметры, которые определяют поведение и параметры работы самой СУБД.
 
В конфигурационном файле СУБД SoQoL можно увидеть / задать / скорректировать значения важных параметров:

1. Сервис СУБД
Здесь указываются параметры, содержащие такие данные, как адреса сервера для приема соединений (в виде комбинаций протокола, адреса и порта), максимальное число подключений к сервису, общий объём используемой сервером оперативной памяти, количество используемых сервером ядер рабочей станции, интервал времени работы сопрограммы без переключения, отведённое планировщиком, рабочий каталог сервиса.

2. Трассировка
Здесь указываются параметры, содержащие список активных компонентов трассировки, максимальный размер одного файла трассировки и размер буфера памяти, в котором накапливаются сообщения перед записью на диск.

3. Пакетная запись страниц хранилища
Параметры этого блока содержат данные о максимальном количестве страниц пакетной записи и глубину очереди разного типа операций.
 
Конфигурационный файл может быть не только создан автоматически системой, но его можно создать вручную. В этом случае важно знать:
- какие именно параметры можно в нём указать;
- их верное наименование;
- допустимые диапазоны значений для каждого параметра и единицы измерения;
- поведение системы при некорректно составленном конфигурационном файле.
 
Где взять ответы по каждому пункту?
 
Краткая информация есть в автоматически формируемом конфигурационном файле. Более подробная информация – в документации пользователя.
 
Ещё момент про изменение параметров:
1. Их можно скорректировать вручную, открыв конфигурационный файл.
2. Или их можно скорректировать командой ALTER SERVICE, после выполнения которой указанные значения параметров записываются в конфигурационный файл.
 
В обоих случаях новые настройки будут применены после перезапуска сервера, который можно выполнить командой RESTART SERVICE.
 
Если вы работали в разных СУБД, то уже знаете, что их конфигурационные файлы отличаются. Например, в конфигурационном файле Oracle можно указать размеры памяти для буферов БД, а в PostgreSQL – можно указать размер памяти для отдельных компонентов (например, для кэширования данных или для выполнения операций сортировки).

Стоит отметить, что в SoQoL политика контроля оперативной памяти строгая – СУБД использует только ту оперативную память, которая была предоставлена ей в настройках.

Расскажите, какие характеристики системы вы изменяли при работе с СУБД? Какие цели преследовали или может какие проблемы решали ручной настройкой? Поделитесь своим опытом.
 
P.S. Важно отметить – изменение конфигурационного файла обычно является задачей для опытных администраторов СУБД, т.к. неверные правки могут привести к серьёзным проблемам с производительностью или стабильностью системы.

Полезные ссылки СУБД SoQoL:
🔹 Канал со сборками — https://t.me/soqol_database_distrib
🔹 Документация — https://soqol.ru/docs (также расположена в архиве с дистрибутивом)
🔹 Сайт — https://soqol.ru
🔹 Попробовать SQL запросы онлайн — https://sqlize.online/sql/soqol/new
🔹 Бот техподдержки — https://t.me/soqol_sd_bot

Что было до
 
В опубликованных ранее материалах мы затрагивали темы объектов баз данных, команд манипулирования объектами и их данными и т.д. Однако, ни раз не сказали, что делать до этого? Нужно исправлять этот момент.
 
Итак, СУБД установлена, сервер запущен. Первый раз. Сервер реализует сервис управления базами данных SoQoL. Что делать дальше?
 
В текущей реализации SoQoL нет предустановленной базы данных.
 
Поэтому логичным шагом будет подключиться к сервису управления и создать базу данных командой CREATE DATABASE, которая помимо этого регистрирует и запускает её обслуживание в сервисе управления базами данных.
 
Пример:
create database WORKERS;
 
- база данных создана и готова к работе;
- каталог базы данных, с файлами для хранения её данных, автоматически создан в текущем каталоге;
- каталог созданной базы данных имеет одноимённое с ней имя.
 
Всё! Теперь к базе данных можно подключаться под предустановленным администратором, создавать в ней пользователей, объекты, наполнять их данными.
Простой пример? Да. Хотите чуть посложнее? Пожалуйста!
 
Или почитаем документацию? ;-) Нет? Ну ладно :-)
 
Пример:
Вы понимаете, что текущий диск сильно нагружен, и вы хотите разместить новую базу данных на другом диске, например, на D:. В этом случае при создании БД можно указать полный путь с именем каталога БД (который может отличаться от имени самой БД):
 

create database WORKERS on 'D:\WORKERS_CITY';

 
Продолжим погружаться в детали?

При создании БД можно настроить её отдельные опции и выставить им значения, отличные от значений по умолчанию. Например, можно настроить:
- кодировку строковых данных по умолчанию;
- признак автоматического запуска обслуживания базы данных при запуске сервера;
- признак автоматического сбора статистики, которая используется для оптимизации запросов;
- размеры буферов для основного, исторического журнала, файла логирования и др.

Пример:
Допустим, необходимо создать базу данных для хранения архивных данных, которая будет редко использоваться. Подключение этой БД будет выполняться вручную при необходимости командой STARTUP DATABASE. Поэтому при создании БД отключаем ее автозапуск:
 

create database CITY with database.autostart='off';

 
Для уже созданных БД можно изменить значения ряда опций командой ALTER DATABASE. Но стоит помнить, что значения некоторых опций можно задать только при создании БД и для корректировки они больше недоступны. Например, указание кодировки строковых данных по умолчанию не доступно для корректировки.
 
Итак, база данных создана с «авторскими настройками»… некоторые опции при необходимости подкорректировали. А что дальше?
 
После успешного выполнения ALTER DATABASE новые значения всех опций, будут действительны после перезапуска базы данных. Но и здесь есть одно исключение из правил: новое значение опции автоматического сбора статистики будет действительно сразу после успешного выполнения команды и не нужно ничего перезапускать.
 
В какой-то момент база данных перестает быть актуальной. Её нужно либо отправить в архив, либо вообще удалить.
 
В этом случае базу данных сначала останавливаем командой SHUTDOWN DATABASE, а потом дерегистрируем в сервисе управления базами данных командой DETACH DATABASE. Только после этого архивируем или удаляем командой DROP DATABASE.
 
Для возвращения в работу дерегистрированную ранее БД нужно снова зарегистрировать командой ATTACH DATABASE, указав её имя и путь к каталогу. Ну а дальше запускаем и в бой.

Вышеозвученные команды может выполнить только администратор сервиса из подключения к сервису. А вот команду ALTER DATABASE помимо него может выполнить еще и администратор конкретной БД.

Кроме упомянутых команд в арсенале администратора сервиса есть ещё команда RESTART SERVICE – для перезапуска сервиса. Кстати, эта доработка была выполнена недавно по предложению одного из наших любимых пользователей.
 
Помимо «долговечных» настроек БД есть команда ALTER SESSION для изменения настроек, действующих в текущем соединении с базой данных. Например, таких:
- включение / отключение автокоммита;
- установка правила сравнения строковых данных по умолчанию.

Как думаете, нам с вами будет этого достаточно на первое время? Нет? ;-) Или чего-то не хватает?
Что думаете по поводу необходимости предустановленных БД? Поделитесь опытом.

P.S. Надеемся, что подключиться к сервису и БД вы смогли сами :-) Подробнее о деталях подключения расскажем позже в одной из следующих публикаций.

Убрать слева, справа или везде

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

Что может помочь в таких случаях?

Для решения таких моментов в СУБД SoQoL реализованы функции: LTRIM, RTRIM, TRIM.

Посмотрим сначала на LTRIM и RTRIM:
– позволяют удалить заданное множество символов. Эти два слова намеренно выделены жирным. Мы к ним ещё вернёмся;
– удаляемым по умолчанию символом является пробел;
– каждая функция может удалить символы только с одной стороны исходной строки: L – слева, R – справа.

Пример:
Предположим, у нас есть таблица "EMPLOYEES" с полем "NAME", в котором содержатся фамилии и имена сотрудников:
 
NAME
---------------
|  Мария Иванова|
|Александр Котов|
|  Пётр Кислов|
|  Алёна Иванова|
 
Как видно, в начале некоторых имён есть пробелы. Необходимо их убрать, чтобы имена были внесены в таблицу однородно:
 
update EMPLOYEES set NAME = ltrim (NAME);
 
NAME
------------------
|Мария Иванова|
|Александр Котов|
|Пётр Кислов|
|Алёна Иванова|
 
Одним лёгким движением в строках всего столбца убраны ненужные символы. С этим массивом данных можно работать дальше.
 
Функция RTRIM работает также как и LTRIM, только справа.
И такая реализация LTRIM и RTRIM во всех популярных СУБД.

Далее, изучив работу функции TRIM в других СУБД, мы реализовали и её.

В СУБД SoQoL функцией TRIM можно удалять символы по сценарию, соответствующему указанному ключевому слову в команде:
- LEADING – слева строки;
- TRAILING – справа строки;
- BOTH – с начала и конца строки. И это правило по умолчанию.
Важен один момент (и вот здесь мы возвращаемся к выделенному жирным в начале) - мы реализовали в SoQoL функцию TRIM как в Oracle, и она позволяет сейчас указать для удаления только один символ.

Например, в Postgres и MSSQL функция TRIM, по аналогии с LTRIM и RTRIM, позволяет указывать для удаления не один символ, а набор символов, которые будут удалены из заданной строки.
 
Такая реализация TRIM, как в Postgres, выглядит логичным продолжением LTRIM и RTRIM, покрывая собой их способности. Поэтому мы призадумались об изменении поведения функции TRIM.
 
Что скажете? Как опытные пользователи СУБД Oracle, почему по вашему мнению, в функции TRIM допустимо для удаления указание только одного символа? Может это на что-то влияет?

Немного про кеш, исследования и тесты

В начале лета в «Открытых системах» была опубликована статья — «Реляционная СУБД для современного оборудования», в которой, в том числе, были приведены некоторые измерения бенчмарка для SoQoL в разных степенях вытесняемости.

И вроде бы результаты положительные, но анализируя разные измерения, мы не удовлетворились такими достижениями, а начали исследовать и работать над системой.

Про кеш

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

Что же из себя представляет кеш в нашей СУБД?

Алгоритм LRU в SoQoL не может быть использован из-за требований неблокирующей архитектуры (не путать с блокировками в транзакциях).

Есть ещё вариант FIFO и на хабре была статья, достаточно хорошо описывающая суть кеша, построенного на основе FIFO-контейнеров.

В SoQoL реализован вариант кеша, за основу которого взят FIFO (описанный в статье), но с более сложной структурой. И кеш в нашей СУБД интегрирован с его внутренней системой управления памятью.

Память кеша делится на 3 области:
- горячий кеш — ведется статистика доступа к страницам;
- холодный кеш — страницы не требуют записи, не имеют ссылок/статистики, являются источником свободных страниц (если страница запрошена методами исполнения оператора, то она «разогревается»);
- свободные страницы — не имеют ассоциации со страницами хранилища на диске.

Все области памяти организуются на основе циклических буферов (FIFO), а горячий кеш состоит из четырёх FIFO-буферов.

А теперь про результаты

В результате проделанной работы была обнаружена неэффективность в подсистеме управления памятью и кешем, которая сильно снижала оптимальность работы всей системы в режимах с вытеснением. Нам очень повезло — мы провели интересное лето ... :-)

Пришла осень, а с ней и предварительные, но такие долгожданные результаты!

Напомним, что в качестве референтного бенчмарка мы используем TPC-C, 1024 клиента, 250 складов (30Гб в хранилищах). Для ограничения системной памяти используем cgroups.

Рассмотрим конфигурацию, в которой для СУБД выделяется оперативная память, размер которой составляет 10% от размера базы данных (т.е. в данном случае 3Гб).

Какие цифры получили после исправления в подсистеме управления памяти и кэшем (для тех, кто в курсе терминов TPC-C):

- было - 40774 NOPM, 211661 TPM

- стало - 356733 NOPM, 1334717 TPM

В процессе работы мы расширили статистику и замерили итоговый показатель попадания в кеш (cache hit) в указанном тесте. Получили очень позитивный результат: cache hit=97%. Появилась некоторая удовлетворенность по данному вопросу.

P.S. Но мы не останавливаемся, поскольку есть еще идеи для улучшений.

Не Update
 
С тех пор как мы здесь писали про создание таблиц в SoQoL, добавление и изменение данных в таблице, прошло немало времени. И в настоящий момент проводится уже тестирование нового функционала SoQoL - команды ALTER TABLE.
 
Посмотрим подробнее, на что способна команда ALTER TABLE в СУБД SoQoL:
 
- установка или удаление ограничения таблицы;
- переименование столбца или таблицы;
- удаление столбца с удалением всех ссылочных ограничений целостности.
  
Такой набор возможностей ALTER TABLE реализован во многих СУБД. Но есть ещё одна возможность, с помощью которой можно изменить характеристики существующего столбца. В разных СУБД эта возможность обозначается разными ключевыми словами, но суть одна.
 
Мы решили что эта возможность нужна и пользователям SoQoL, и добавили по примеру Oracle:
 

ALTER TABLE <имя_таблицы> MODIFY <имя_столбца> <характеристики>;

 
Давайте кратко на примерах посмотрим возможности ALTER TABLE в SoQoL:
Допустим, ранее мы создавали таблицу:
 

create table WORKERS (
   TN int primary key, 
   DEPT varchar (100),
   UNIT varchar (50), 
   CITY varchar (85), 
   NAME varchar (35), 
   SALARY number (10, 2));

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

alter table WORKERS add (BONUS number (10, 2) not null);

 
А еще поняли, что размер типа данных для имени маловат, нужно увеличить:

alter table WORKERS MODIFY (NAME varchar (125));

 
И здесь важно: если бы новый размер типа был меньше текущего размера данных, то была бы возвращена ошибка.

Теперь предположим, что данные с детализацией по сотрудникам были вынесены в отдельную таблицу PERSON_DETAILS, поэтому из таблицы WORKERS часть столбцов можно удалить:

alter table WORKERS drop (DEPT, UNIT , CITY);

 
А затем еще и свяжем эти таблицы между собой ограничением:

alter table WORKERS add constraint PERSONAL foreign key (TN) references PERSON_DETAILS (TN);

Вот такая небольшая экскурсия по жизненному пути маленькой базы данных.
 
Если говорить в целом про ALTER и для других объектов базы данных, то в планах реализация команды ALTER INDEX, которая позволяет:
 
- изменить имя индекса и его тип;
- установить или удалить компрессию индекса;
- изменить метод выравнивания индекса;
- пересоздать индекс и т.д.
 
В настоящий момент мы остановились на реализации ALTER INDEX для возможности изменения его имени. Почему только имени?

После совместного обсуждения приняли решение, что такая реализация на текущий момент наиболее приоритетна. Реализация более сложного синтаксиса команды CREATE INDEX (с возможностью указания других характеристик индекса как, например, в Oracle или Postgres) пока рассматривается.

И если про ALTER TABLE более-менее всё понятно, то что скажете про индексы? Как часто пользуетесь командами изменения индекса и в каком их варианте?

Удалить объект в любом случае
 
Если подытожить ранние публикации про таблицы в СУБД SoQoL, то мы рассказали о реализованных возможностях:
- создать таблицу
- внести в неё данные
- изменить данные таблицы
- удалить из таблицы данные
- изменить структуру таблицы
 
Теперь можно перейти и к DROP TABLE в SoQoL.
 
Если бы мы начали рассказывать о данной команде сразу после выпуска бета-версии SoQoL, то рассказ был бы кратким, а синтаксис выглядел бы так:
 

DROP TABLE <имя_таблицы>;

 
И такой командой можно удалить любую, несвязанную с другими, таблицу.
 
Например, такую:
 

create table PERSON (TN int primary key, DEPT varchar (75), NAME varchar (75));
drop table PERSON;

 
При этом стоит отметить, что при удалении таблицы:
1. Удаляются и:
- индексы, построенные по её столбцам;
- привилегии пользователей на доступ к ней.
 
2. Становятся нерабочими те объекты, которые ссылаются на неё. Т.е. при обращении к представлению или вызове процедуры, которые ссылаются на удалённую таблицу, будет возвращена ошибка. И так будет до тех пор, пока не будет создана таблица (или представление), одноимённая с удалённой.
 
Только и этого мало.
 
На текущий момент возможности этой команды в нашей СУБД стали шире. Давайте взглянем чуть подробнее.
 
Например, если ранее на таблицу были наложены ссылочные ограничения целостности в виде FOREIGN KEY, то удалить её было бы невозможно до момента удаления этих самых ограничений. Теперь можно и это.
 
Посмотрим на примере:
 
Возьмём таблицы из прошлого поста:
- с детализацией данных по сотрудникам:
 

create table PERSON_DETAILS (TN int primary key, DEPT varchar (75), UNIT varchar (35), CITY varchar (35));

 
- с остальной информацией по сотрудникам и установлением FOREIGN KEY, связывающим эти две таблицы:
 

create table WORKERS (TN int primary key, NAME varchar (35),  SALARY number (10, 2)
        constraint PERSONAL foreign key (TN) references PERSON_DETAILS (TN));

 
Попытка удалить таблицу PERSON_DETAILS командой «раннего» синтаксиса закончится возвратом ошибки с предупреждением, что есть ссылки на внешние ключи.
Поэтому, если нам нужно удалить таблицу несмотря на эти ссылки, действуем так:
 

drop table PERSON_DETAILS cascade;

 
С небольшим, но важным дополнением в виде CASCADE таблица удаляется успешно, а вместе с ней и все ссылочные ограничения.
 
Ещё появилось дополнение к команде в виде фразы «if exists»:

drop table if exists PERSON_DETAILS cascade;

Это дополнение позволяет в случае отсутствия таблицы в БД предотвратить ошибку, что очень удобно использовать в повторно исполняемых скриптах создания структуры БД.

На этом разговоры о таблицах пока закончим.
 
Или может рассказать про реализацию FOREIGN KEY в SoQoL? А может есть другие вопросы, предложения – пишите!

Измерения после оптимизации режима вытеснения (левая часть графика). Вам есть что сказать/спросить?

Как быть в теме

Знать актуальную информацию о разработке нашей СУБД SoQoL, иметь возможность задать вопрос или обменяться мнениями с единомышленниками?

Конечно, нужно быть подписанным на наш канал. Но, когда публикуется новый пост или идёт оживлённая беседа... не сидеть же в канале постоянно?!

Для получения своевременного уведомления о происходящем у нас:
– вам нужно подписаться на наш канал https://t.me/soqol_dbms;
– в верхнем правом углу канала нажимайте “включить уведомления”. Так вы будете получать уведомление о новых публикациях.

Ещё у основного канала есть чат - СУБД SoQoL Chat. О чате не все знают, но в нём единым потоком идут сообщения ко всем постам нашего канала. И из него тоже можно получать уведомления!

Как вступить в чат канала? В комментариях прикрепили инструкцию.

Будьте с нами, будьте всегда в курсе происходящего в жизни SoQoL🙂

P.S. Это сообщение можно пересылать всем, кто интересуется современными СУБД

Найди три отличия
 
Часто функции, выполняющие одинаковые действия и дающие схожий результат, имеют в разных СУБД если не одинаковые имена, то очень похожие. Но только не в этот раз
 
Данная функция работает со строковыми типами данных и позволяет найти позицию первого или определённого по счёту вхождения подстроки в исходной строке. Но функции, выполняющие схожие операции, в разных СУБД имеют своё имя… давайте разбираться кто-где-кто
 
Начнём с PostgreSQL
В этой СУБД функция носит имя POSITION и принимает два аргумента:
▪️ исходную строку;
▪️ подстроку.
Возвращает POSITION позицию первого вхождения подстроки в исходную строку или 0, если подстрока не найдена. Начальная позиция, с которой начинается поиск, всегда первая и изменить её нельзя.
 
Краткий пример:
SELECT POSITION('lo' IN 'hello world'); -- результат: 4
 
MS SQL
Здесь мы видим функцию CHARINDEX, которая схожа с POSITION в Postgres и также принимает два аргумента - исходную строку и подстроку.
CHARINDEX возвращает позицию первого вхождения подстроки в исходную строку или 0, если подстрока не найдена. И она также как POSITION в Postgres не позволяет задать начальную позицию для поиска.
 
Oracle
Здесь это функция носит имя INSTR. Но она отличается от вышеупомянутых функций тем, что принимает четыре аргумента:
▪️ исходную строку;
▪️ подстроку;
▪️ начальную позицию для поиска;
▪️ номер вхождения
 
INSTR возвращает позицию первого или указанного по счёту вхождения подстроки в исходную строку, или 0 если подстрока не найдена. Если начальная позиция не указана, то по умолчанию поиск начинается с позиции 1.
 
Краткий пример:
SELECT INSTR('hello world', 'lo') FROM dual; -- результа: 4
 
Перед реализацией данного функционала в СУБД SoQoL мы проанализировали схожие функции в популярных СУБД. POSITION в Postgres и CHARINDEX в MS SQL не позволяют задать, например, начальную позицию для поиска или номер вхождения. Наиболее полной нам показалась реализация в Oracle. Поэтому мы позаимствовали его вместе с наименованием функции.
 
Итак, СУБД SoQoL
У нас реализована функция INSTR, которая:
1. Принимает два обязательных аргумента (исходную строку, подстроку) и два необязательных (начальную позицию для поиска, номер вхождения).
2. Возвращает позицию первого или указанного по счёту вхождения подстроки в исходную строку, или 0 если подстрока не найдена.
3. Если значение номера вхождения не задано, то по умолчанию равно 1.
4. Если начальная позиция не указана, то по умолчанию поиск начинается с позиции 1.
 
Значение строки и подстроки могут быть значениями типа:
▪️ CHAR, VARCHAR;
▪️ NUMBER, BOOLEAN, DATE, TIMESTAMP, BINARY, VARBINARY, ROWID. В этом случае будет выполнена попытка приведения значения аргумента к значению типа VARCHAR.

Над возможностью использования значение типа CLOB в строке и подстроке нам предстоит еще поработать в недалеком будущем.

Значения аргумента номера символа и номер вхождения задаются целым числом. Если указано дробное число, то функция усечёт его до целого числа. Если задать значение в типе CHAR, VARCHAR, то будет попытка преобразования к типу NUMBER.
 
Посмотрим работу функции на шуточных, но наглядных примерах:
 

select instr ('АаааАА, кричал сисадмин', 'а');

Результат: 2
 

select instr ('АаааАА, кричал сисадмин', 'аа', 1, 2);

Результат: 3
 

select instr ('аааААаа, кричал сисадмин, АА', 'АА', -3, 1);

Результат: 4
 

select instr ('аааАА, кричал сисадмин', 'дас', -3, 1);

Результат: 0

Поздравляем!
 
В нашей большой стране проживает больше 140 миллионов человек. Страна наша многонациональная и все мы разные. Но мы — россияне, Россия — наш общий дом.
 
Цените, уважайте и поддерживайте ближнего. Никогда не забывайте, что, несмотря на национальность, религиозные убеждения и даже цвет кожи, все люди имеют душу и сердце.
 
Желаем всем жить в согласии, с уважением друг к другу и тогда мы сможем свернуть горы! С Днем народного единства!

Он кроется в деталях

Прошлый пост со сравнением производительности двух СУБД наделал много шуму, оно и понятно почему :) Ну а пока идёт подготовка к новым тестам, рассмотрим менее резонансные, но важные темы

Про функцию NULLIF

В общем, и в SoQoL, и в Oracle и в PostgreSQL реализация данной функции схожа:
- она имеет одинаковый синтаксис NULLIF (<выражение1>, <выражение2>);
- допускает для аргументов значения любого типа данных;
- тип данных результата соответствует типу данных первого аргумента.

И если посмотреть элементарный пример, то и результаты во всех озвученных СУБД будут одинаковые:

select nullif (10, 5); 

Результат: 10, т.к. значения аргументов не равны

select nullif('admin', 'admin'); 

Результат: NULL, так как значения равны

Но есть момент в деталях реализации NULLIF в Oracle, который проявляется при иных случаях применения функции.

Возьмём простой генератор псевдослучайных чисел, который возвращает либо 0, либо 1:

dbms_random.value (0, 2) 

И попробуем получить ряд целых случайных чисел или NULL, если сгенерированное число = 0.
Записываем и исполняем несколько раз:

select nullif (trunc (dbms_random.value(0, 2)), 0) FROM dual; 

Результат: 1
Результат: NULL
Результат: 0

Но если сгенерированное число 0, то результат должен быть NULL. Почему иногда возвращает 0?

А тут стоит обратить внимание на детали процесса, происходящего внутри:
1. Сначала вычисляется значение первого аргумента, и если он равен второму, то возвращается NULL;
2. Если же нет, то первый аргумент вычисляется заново и возвращается полученный результат. Т.е. в примере выше сначала генератор вернул значение 1, функция проверила, что 1 не равно 0, и подставила в результат первое выражение. Только сгенерировалось новое значение, которое оказалось равным 0.

Получается, что все выражения функции NULLIF в Oracle трансформируются оптимизатором в:

CASE WHEN <a> = <b> THEN NULL ELSE <a> END

Тогда логично, что число генерируется дважды и функция NULLIF возвращает 0.

Если смотреть SoQoL, то в нём запрос:

select nullif (trunc (random_value (0, 2)), 0);

возвращает значение только двух вариантов: NULL или 1.

В Postgres запрос select nullif (trunc (random ()*2), 0); возвращает NULL или 1.

Так почему в Oracle выбран именно такой сценарий реализации функции NULLIF? Есть мнение, что NULLIF был введен в целях совместимости, поэтому является закамуфлированной версией CASE.

Но может есть в такой реализации функции более глубокие причины? Кто знает Oracle досконально? Поделитесь свои мнением.

Немного других достижений нашей компании РЕЛЭКС