Земля вызывает базу

Ок. СУБД поставили, что дальше?
Надо бы как то начать управлять этим богатством. Какие есть варианты?

1️⃣ Командная утилита psql
Аналог sqlplus в мире Oracle.

Доступна в установленном контейнере. Подключиться можно командой:

docker exec -it mypgdb14 psql -U postgres

docker exec -it mypgdb14 - эта часть относится к docker
psql -U postgres - эта к утилите psql. -U к какому юзеру подключаемся.

2️⃣ IDE с человеческим интерфейсом
Их достаточное количество на любой вкус и цвет.
DBeaver, DataGrip, PgAdmin4 и другие.

На скрине показано окно настроек подключения в DBeaver.
▫️Server: localhost
▫️Dbname: postgres
▫️Port: 5434 (указывали при создании)
▫️User: postgres
▫️Pass: mypass (указывали при создании)

Ввели креды, протестировали. Все должно быть ок ✅

Завтра продолжим 😉

#postgresql #ide
Oracle Developer

К серверу подключились. Что дальше?

Сервер БД (кластер) может содержать несколько БД. Близкая аналогия с Oracle - это набор PDB. По умолчанию создается БД postgres, а также шаблонная БД (template).

Можем создать БД владельцем, которой будет пользователь postgres. Мы пойдем другим путем. Создадим владельца будущей базы. А потом уже БД с указанием владельца.

1️⃣ Создаем пользователя:
create role mydb_owner login password 'mypass';
или
create user mydb_owner password 'mypass';

А причем тут роль? Да, в мире PG роль может быть как пользователем, так и ролью.
Юзер/роль создается общая на весь кластер.

2️⃣ Создаем отдельную БД
create database mydb owner mydb_owner;

БД создали. Где будем хранить объекты? В схеме.
В Oracle при создании пользователя, автоматически создается схема. В Pg это нужно делать отдельно. По умолчанию создается схема public.

3️⃣ Создаем схему
create schema my_schema;

Не обязательно использовать psql, достаточно использовать IDE 😇

Продолжение следует😉

#postgresql
Oracle Developer

Создаем объекты

Начнем с таблиц. Синтаксис PG чуть более богат чем Oracle (до 23й версии). Можем использовать if exists/not exists.

drop table if exists my_tab;

create table if not exists my_tab
(
id bigserial constraint my_tab_pk primary key,
d date not null,
t time not null,
note text
);

🔸 bigserial - большое целое с автоувеличением (похож на generated always as identity в Oracle);
🔸 date - только дата;
🔸 time - только время;
🔸 text - строка неограниченной переменной длины (рекомендован к применению; похож на clob в Oracle).

Добавим несколько строк:
insert into my_tab(d, t, note) values
(now(), now(), 'text 1'),
(now(), now(), 'text 2');

insert into my_tab values
(default, now(), now(), 'text 3'),
(default, now(), now(), 'text 4');

🔸 now() - аналог sysdate.
🔸 default - есть в Oracle.

Заметьте, как элегантно можно вставить N строк в одном операторе. Это будет представлено только в Oracle 23. На текущий момент, придется написать аж четыре команды.

#postgresql
Oracle Developer

Пишем код на PL/pgSQL

PL/pgSQL - это процедурный язык для СУБД PostgreSQL. Аналог в Oracle - PL/SQL.

Он издалека похож на PL/SQL со своим синтаксисом и особенностями. К слову, о миграции с Oracle на PG, нельзя просто взять и портировать код с PL/SQL на PL/pgSQL.
Лично меня сильно расстраивает отсутствие пакетов. Можно выкрутиться путем создания схем (как будто это пакет), но по мне так, это не то пальто.

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

Не принято писать в PG те жирные приложения, которые мы привыкли создавать в Oracle. В основном, PG идет в паре с middleware (java, python,…), в котором, как раз, содержится вся бизнес-логика. БД используется лишь для хранения данных. В некоторых случаях, с небольшим налетом кода для технических нужд (например, нарезание секций, чистка таблиц и т.п.).

Продолжение ⬇️

Ок. Давайте напишем первую функцию на PL/pgSQL.
Функция будет возвращать строку в Uppercase + выводить в буфер вывода информацию о входной и выходной строках (просто для примера).

Код функции:

CREATE OR REPLACE FUNCTION my_schema.my_upper(p_text text)
 RETURNS text
 LANGUAGE plpgsql
 immutable
AS $function$
declare
  v_out text;
begin
    v_out := upper(p_text);    
    raise notice 'In: %. Out: %', p_text, v_out;
  return v_out;
end;
$function$;

Примечание: код можно написать лаконичней.

Согласитесь есть что-то общее с PL/SQL, но не 1 в 1.

🔸 LANGUAGE plpgsql - язык кода на PL/pgSQL (может быть Sql, Python и др).
🔸 RAISE NOTICE ~ dbms_output
🔸 immutable = deterministic

В синтаксисе создания модулей можно наворотить многое. Ссылка на доку под постом.

Вызов функции:

select my_upper('my text');

Обратите внимание на вызов. Никакого from dual нет. Кстати, после выхода Oracle 23, мы так же будем избавлены от этого рудимента (кто перейдет 😁)

Продолжение следует 🎓

#postgresql #plpgsql
Oracle Developer

Система прав и привилегий 🔐

Да, она отличается от Oracle.
Как минимум бросается в глаза выдача грантов на объекты.
Рассмотрим на простом примере.

1️⃣ Создадим функцию, которая использует внутри себя таблицу my_tab (пару постов назад мы её создавали и наполняли).

create or replace function get_note(p_id my_tab.id%type)
returns my_tab.note %type as $$
  select note from my_tab t where t.id = p_id;
$$ language sql;

2️⃣ Создадим еще одного юзера в нашей БД

create user mydb_app password 'apppass';

3️⃣ Попробуем выполнить функцию под новым пользователем

select my_schema.getNote(1);

ERROR: permission denied for schema my_schema
LINE 1: select my_schema.getNote(1);

Упс… не хватает прав на использование схемы my_schema 🤷🏻‍♂️

4️⃣ Дадим грант на схему

grant usage on schema my_schema to mydb_app;

Вызовем функцию, получим следующую ошибку:
ERROR: permission denied for table my_tab
CONTEXT: SQL function "getnote" statement 1

5️⃣ Дадим грант на таблицу

grant select on my_tab to mydb_app;

Выполним и получим результат:
mydb=> select my_schema.getNote(1);
getnote
---------
text 1
(1 row)

Ура! Все работает 👍

Получилось немного витиевато. В Oracle:
🔹не требуется выдавать отдельно грант на использование схемы;
🔹нужно выдать грант на execute функции;
🔹не нужны гранты на каждый объект, который используется внутри функции (за некоторыми исключениями).

Если окунуться в документацию, то можно обнаружить множество отличий в системе прав от Oracle.

Продолжение следует 🎓

#postgresql
Oracle Developer

Расширение PostgreSQL

Что мне сильно нравится в Pg, так это возможность добавлять новые фишки в СУБД за счет расширений (extension).

Расширения разделяются на два типа:
1️⃣ поставляемые в дистрибутиве;
2️⃣ созданные пользователями.

Любой может написать свой плагин. Довольно крутая концепция, используемая многими open source продуктами. Из кубиков конструируете нужное вам решение.

Уже создано достаточное количество пользовательских расширений. Вполне вероятно, под вашу специальную задачу уже есть готовое решение.

Можно легко нагуглить инструкции как создавать плагины + есть отличная документация.

Установкой, обновлением, удалением расширений, обычно, занимаются DBA с подачи разработки.

Список установленных расширений в БД:

select * from pg_available_extensions

Естественно, есть платные и бесплатные плагины. Часть из них можно посмотреть в этом каталоге или в этом + гугл никто не отменял. Очень бы пригодился репозиторий аля MavenCentral 🤷🏻‍♂️

Продолжение следует 🎓

#postgresql
Oracle Developer

PostgreSQL заключение

В этих 10 постах, мы едва коснулись PostgreSQL. Кроличья нора гораздо глубже 🐰

То, что компании пытаются перейти с Oracle на Pg, не означает, что архитектура, язык PL/pgSQL один в один. Общие принципы - одинаковые, но реализация - разная. Взять, например, мультиверсионность строк.
Когда я учился на курсах PostgresPro (архитектура и оптимизация в 2019м), выписал около трех листов А4 различий между двумя СУБД с точки зрения разработчика. Если углубиться в администрирование там еще наберется листов Nцать.

Так почему же компании переходит на PG? Все просто, PostgreSQL - это достойная альтернатива Oracle в классе РСУБД.

Я не особый любитель Pg. Это как пересесть с Mercedes (Oracle) на отечественный авто (Pg). Тем не менее, избегать изучения PostgreSQL не стоит.

Причины
🔹 Активное развитие СУБД - авторы и энтузиасты дорабатывают ядро, пилят многочисленные расширения, фишки платных версий постепенно заносят в community-версию;
🔹 Наличие бесплатной версии - снижение стоимости владения по сравнению с тем же Oracle;
🔹 Все больше решений с использованием Pg;
🔹 Политические и санкционные моменты (актуально для РФ).

На мой взгляд, в будущем Postgres существенно потеснит конкурентов с рынка 👍

Пора заканчивать с Pg, что-то я соскучился по Oracle ☺️

Если интересно раскрытие темы по различию между Pg и Oracle - ставьте 🔥. Наберется хотя бы 200, буду иногда пилить посты на эту тему 🎓

#postgresql
Oracle Developer

Собеседование в Сбер.ДомКлик

Друзья всем привет!

Что-то давно у нас ничего про собеседования не было. Исправляюсь.
Перед новым годом рекрутеры оживились. Было аж два приглашения на поболтать.
Одна из контор - Сбер, на проект ДомКлик.

Кратко по вакансии (полное в чатике)
Мы используем: IBSO, OpenShift, PL/Plus, Oracle PL/SQL, Java, Kotlin, Groovy, Spring Boot, Hibernate, REST, VuGen, BPMN, Camunda, Jenkins, Git, Bitbucket, Jira, Confluence.

Задачи:
разработка новых и развитие существующих кредитных микросервисов на АБС IBSO и Java

Наши требования:
Опыт разработки IBSO PL/Plus, Oracle PL/SQL, приветствуется опыт разработки на java, управление контейнерами с сервисами в OpenShift.

Продолжение ⬇️

Этапы собеседования
1️⃣ предварительное общение с HR в телеграме;
2️⃣ общение с руководителем;
3️⃣ техническое собеседование;
4️⃣ регистрация в их спец платформе для кандидатов;
5️⃣ проверка безопасниками;
6️⃣ оффер или неееет.

С первым этапом - никаких проблем.

Второй этап занял почти 1.5ч. На нем были руководитель + еще несколько спецов. Общались на разные темы от проектирования приложений, различий между монолитами/ микросервисами и заканчивая Agile. По самой СУБД вопросов, практически, не было.

Также, обсуждали организационные моменты, проект, задачи, которыми предстоит заниматься. Отдельно остановились на ЦФТшном PL/Plus - был ли опыт (упаси боже) и готов ли изучать (ну, конечно, да 😂).

Кратко по работе:
Бизнес область: кредитование.

Два технических стрима:
1️⃣ монолит с ЦФТ на Oracle - это хозяйство нужно вялотекуще поддерживать. Нужны компетенции в Oracle + PL/Plus.

2️⃣ перевод, всего чего только можно, на микросервисы на Java-стеке. Нужны, хотя бы, базовые знания Java и около того. Готовы обучать.
Второй стрим в самом начале - будет шанс поучаствовать на старте всего проекта.

Продолжение в четверг

#собеседование #сбер

продолжение ⬇️

Собеседование в Сбер.ДомКлик. Финал

Мы остановились на втором этапе - общении с руководителем.

По окончании сессии, мне сказали, что этап с тех собеседованием не потребуется.
Насколько это частая история? Не знаю. Кто туда недавно собесился - напишите плиз в чатике.

Поэтому сорян, технических задачек со Сбера не будет. Обломали меня со свежим материалом 🤷🏻‍♂️

Далее, не интересные этапы с регистрацией в Сбер HR-платформе «Пульс», заполнения 100500 страниц анкеты и проверка безопасниками.

В итоге: был получен оффер на позицию «Главный инженер по разработке».

Из плюсов
🔹 Можно очень сильно прокачаться в другом стеке.
🔹 Высокая з/п - с учетом всех плюшек, премий и т.п. в месяц набегает довольно приличная сумма по нынешним временам. И это только на входе.
🔹 Жирный соц пакет.

Из минусов
🔸 PL/Plus
🔸 Офис/гибрид в Москве
🔸 Кому-то не зайдет работодатель.

Наверняка, все слышали страшилки про Сбер, когда человек мог просидеть три месяца без работы заполняя заявки, и только к концу испытательного срока получить всё необходимое для работы.
HR клятвенно заверила, что такого больше нет 🤷🏻‍♂️

Однако, я был приятно удивлен оперативности всех цепочек от HR до ЛПР. Прошло всего три недели (с учетом НГ праздников) от первого касания до оффера.
Для Сбера, я считаю, это огромный прогресс.

На всех этапах потенциальные коллеги были максимально вежливы и проявляли профессионализм.

В целом, эта вакансия - отличная возможность перекатиться из Oracle в Java-мир, если не смущают минусы.

Вот такая история получилась в этот раз 😊

#собеседование #сбер

Восстанавливаем текст запроса

Друзья всем привет!

Недавно был на собеседовании в одной из компаний. Об этом я обязательно расскажу позже 😉
Гоняли долго по разным темам. Все тщательно записал и сохранил 😁

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

Задание направлено на понимание планов запросов, когда какие операции используются и т.п. Этакий reverse engineering.

В моем курсе по оптимизации, будем разбирать подобные задачи для усвоения материала и натаскивания на собеседования ✅

Анализ, как всегда, в четверг 🎓
Обсудить в нашем ламповом чатике.

#задача
Oracle Developer

Восстанавливаем текст запроса. Анализ

Последовательность выполнения шагов: 3, 2, 4, 1, 0 или 3, 2, 1, 4, 0 (как посмотреть на соединение).

1️⃣ Шаг 3. Происходит Range Scan индекса DEPT_LOCATION_IX.
Доступ происходит по предикату "D"."LOCATION_ID">1700 (звездочка в плане + predicate information)

2️⃣ Шаг 2. Выгребание строк по найденным Rowid (на шаге 3) из таблицы DEPARTMENTS
Почему без него никак? В индексе нет достаточного количества данных, чтобы выполнить последующее соединение и материализовать результат.

3️⃣ Шаг 4. NESTED LOOPS SEMI - полусоединение двух таблиц (ищется первое совпадение по предикатам соединения).
Используется в конструкциях типа exists/in.
Исходя из predicate information 4, соединение двух таблиц осуществляется по столбцам "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID".
Отсюда же можно получить названия алиасов к таблицам - d/e.

4️⃣ Шаг 1. Доступ ко второй таблицы происходит по EMP_DEPARTMENT_IX.

5️⃣ Шаг 0. Происходит SELECT.

По этим данным никак нельзя понять название второй таблицы. Тут уж просто кругозор 🤷🏻‍♂️
Только ленивый или совсем новичок не щупал схему HR с набором табличек.
Название второй таблички - employees.
Даже, если не знаете, я думаю ничего страшного, если на собесе её назовете, хоть, tab2.

Итоговый запрос

select *
  from hr.departments d
 where d.location_id > 1700 
     and exists (select 1 from hr.employees e where d.department_id = e.department_id);

Минутка юмора
Подписчица канала, и мой экс-босс (Наташа, привет), прогнала задание через BingAI и получила текст запроса идентичный натуральному. Есть над чем задуматься 😉
Конечно, на собеседовании у вас не будет времени на это. Да и в работе, лучше понимать "что куда и как".

В своем курсе по оптимизации, я буду давать подобные темы 🎓

Понравилось? ставьте 👍

#решениезадачи #оптимизация
Oracle Developer

Релиз Oracle 23c

Друзья всем привет!

Еще в сентябре прошлого года, компания Oracle анонсировала выход новой версии СУБД зимой 23 года.
Версия LTS (long term support), а значит она с нами на продолжительное время.
И вот, 03.04 состоялся релиз новой версии 23с Free - Developer Release.

В новой версии заявлено достаточно много интересных фич. Некоторые из них:
1️⃣ Select без from

было: select 1+2 from dual;
стало: select 1+2; 

2️⃣ Использование алиасов в Group by и Having

select trunc(dtime, ‘dd’) day
  from tab
group by day

3️⃣ Тип boolean в SQL

create table client(
  id number(38),
  is_active boolean
);
insert into client values(1, true);
insert into client values(2, false);

И многие другие фишки, которые уже давно надо было добавить. Наконец-то, подумали о разработчиках 🥰

А вот смогут/захотят на неё переходить компании в РФ, вопрос хороший, как раз для нашего лампового чатика.

В следующем посте расскажу, как эту версию развернуть в Docker 🐳 и потестить новые фичи 😉

#oracle23c
Oracle Developer

Установка Oracle 23c в Docker

Есть три варианта:
🔸 Docker-образ;
🔸 RPM-пакет для *nix;
🔸 Виртуалка для VirtualBox.

Мне нравится вариант с Docker.

1️⃣ Устанавливаем Docker в нашу ОС (если нет)
Win Mac Linux

2️⃣ Создаем контейнер с указанием параметров

docker run -d --name oracle23c -p 1524:1521 -e ORACLE_PWD=booble34 -e ORACLE_CHARACTERSET=UTF8 container-registry.oracle.com/database/free:latest

вместо latest можно указать конкретную версию 23.2.0.0.
имя контейнера - oracle23c, порт доступа - 1524, пароль для sys/system - booble34.

Если у вас еще нет скаченного image, то процесс займет некоторое время - нужно получить ~3Gb.

3️⃣ Устанавливаем Oracle client (если нет) и настраиваем network\admin\tnsnames.ora
Добавляете в файл алиас на новую БД.

oradb23c =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1524))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

FREEPDB1 - БД, созданная по умолчанию.

Всё 🔥

Внутрь контейнера можно попасть через

docker exec -it oracle23c /bin/bash

К БД можно подключиться

sqlplus system/booble34@oradb23c  

Для Mac/*nix почти тоже самое.

А что по опциям в этом дистрибутиве?
Выполняем и смотрим:

select * from v$option;

Много чего есть. Например: секционирование.

Обсудить в чатике 💬

PS. Параметров создания БД гораздо больше, можно настроить очень гибко.
По умолчанию, удаленное подключение (не с сервера) к SYS + пароль не доступно (не важно как вы устанавливаете БД). Для настройки нужно выполнять доп. приседания.

#docker #oracle23c
Oracle Developer

Использование алиасов в Group by и Having

Друзья, всем привет!

В Oracle23c, наконец-то, сделали возможность использование алиаса в group by и having.
Это бывает полезно, когда выражение, по которому происходит группировка и фильтрация групп размашистое.

-- до 23c
select extract(year from t.hire_date) year, count(*)
  from employees t
 group by extract(year from t.hire_date)
 having extract(year from t.hire_date) >= 2014;

-- 23с
select extract(year from t.hire_date) year, count(*)
  from employees t
 group by year
 having year >= 2014;

Красота 🔥

Иногда на собесах спрашивают: можно ли использовать алиасы в group by?
Теперь правильный ответ: зависит от версии, до 23 - нельзя, после - можно.

А можно ли обратиться по позиции?
Да можно, но только в group by и с включением этой возможности в сесиии.

alter session set group_by_position_enabled = true;

select extract(year from t.hire_date) year, count(*)
  from employees t
 group by 1
 having year >= 2014; 

Обсудить в чатике 💬

#oracle23c
Oracle Developer