Шаблоны SQL

При работе с базами данных мы часто выполняем повторяющиеся SQL-запросы, например, связанные с созданием базы данных (CREATE DATABASE), созданием таблиц (CREATE TABLE) и их заполнением (INSERT INTO), а также запросы, связанные с выборкой данных (SELECT). С целью экономии времени в некоторых СУБД присутствует такой функционал, как шаблоны. В этой статье мы рассмотрим, что такое шаблоны, а также рассмотрим их практическое применение на примере двух СУБД: PostgreSQL и Microsoft SQL Server. 

Предварительные требования

Для работы с функционалом шаблонов SQL нам потребуется:

• Один сервер или виртуальная машина на операционной системе Ubuntu 22.04 с предустановленой СУБД PostgreSQL. В данной статье будет использоваться 15 версия PostgreSQL. Установить PostgreSQL на Ubuntu вам поможет наша инструкция: Установка PostgreSQL.

• Один сервер или виртуальная машина на операционной системе Windows Server 2022 с предустановленой СУБД Microsoft SQL Server. Использоваться будет версия 2022 и издание Developer, которое можно бесплатно скачать с официального сайта Microsoft. Также потребуется официальная клиентская утилита SQL Server Management Studio (SSMS) для подключения к базам данных и управления ими. 

Регистрация в Timeweb Cloud

Чтобы арендовать облачный или выделенный сервер в Timeweb Cloud, необходимо пройти регистрацию. 

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

• ФИО,

• адрес электронной почты,

• телефон.

После регистрации на указанный адрес почты придет сообщение с ссылкой для активации аккаунта и входа в панель — перейдите по ней.

Создание облачного сервера

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

1) Переходим на страницу авторизации и входим в аккаунт.

2) После успешной авторизации отобразится панель управления текущего проекта. Переходим в раздел «Облачные серверы» и нажимаем «Создать» или «Добавить».

3) Выбираем операционную систему, которая будет установлена на сервер. В нашем случае нам необходим один сервер с ОС Ubuntu версии 22.04, на который будет установлена PostgreSQL, а также второй сервер с ОС Windows Server 2022, на котором будет установлена СУБД Microsoft SQL Server.

4) Выбираем регион, в котором будет находиться наш сервер. Выбирать рекомендуется тот регион, который ближе всего находится к вам физически. У каждого доступного региона справа вверху отображается ping, т.е. время, необходимое для передачи данных с вашего компьютера на сервер. Чем меньше указанное время, тем быстрее будет осуществляться передача данных.

5) Далее выбираем необходимую конфигурацию для серверов. Для комфортного запуска СУБД PostgreSQL выберем конфигурацию с двухъядерным процессором, 2 ГБ оперативной памяти и 40 ГБ жесткого диска. Для запуска Microsoft SQL Server выберем конфигурацию с двухъядерным процессором, 4 ГБ оперативной памяти и 50 ГБ жесткого диска. В реальности вам необходимо выбирать именно ту конфигурацию, которая будет удовлетворять вашим потребностям для базы данных. 

6) Далее необходимо решить, будет ли сервер доступен из внешний сети или же только из приватной (частной) сети. 

7) По желанию можно оформить дополнительные услуги, включая резервные копии и защиту от DDoS-атак (последняя доступна в Санкт-Петербурге и Москве).

8) Также заранее можно загрузить SSH-ключ, чтобы не входить на север при помощи пароля.

9) Можно задать необходимое имя для сервера которое будет отображаться в панели управления, а также выбрать проект.

10) Для создания сервера необходимо нажать на кнопку «Заказать».

Если на вашем аккаунте недостаточно средств, то будет выведено предупреждение о необходимости пополнить баланс. После оплаты и создания сервера откроется Дашборд сервера, где можно будет найти IP-адрес, логин и пароль для подключения.

Что такое шаблон в SQL и для чего используются

Шаблоны представляют собой обычные текстовые файлы, чаще всего в формате .sql, в которых содержатся SQL-команды для создания объектов в базе данных или выполнения запросов на выборку. Вместо того, чтобы выполнять каждую команду по отдельности, можно запустить шаблон, который автоматически выполнит все шаги, описанные в файле шаблона (применимо для СУБД Microsoft SQL Server): создаст требуемый объект или изменит его, сделает необходимые запросы на выборку данных. Или же можно применить шаблон прямо при создании базы данных (в случае СУБД PostgreSQL).

dbaas

Использование шаблонов в СУБД PostgreSQL

Начнем с СУБД PostgreSQL. 

Сначала отдельно рассмотрим принцип работы шаблонов в PostgreSQL. Каждый раз при создании новой базы данных в PostgreSQL фактически используется ее готовый шаблон, т.е. происходит копирование уже существующей базы данных. По умолчанию в PostgreSQL помимо стандартной базы с именем postgres присутствует еще две системные базы с именами template0 и template1:

template1 — шаблон базы данных, из которого создается новая база данных, в частности при использовании оператора CREATE DATABASE. template1 можно редактировать для создания или редактирования новых объектов, а также для выполнения других запросов. При создании новой базы данных, если вы укажете в качестве шаблона template1, все объекты, которые были заранее созданы в самом шаблоне, также будут созданы в новой базе данных (данный способ будет показан на практике далее в статье). Также в template1 можно добавлять расширения. Более подробно про установку и использование расширений в PostgreSQL можно почитать в нашей статье.

template0 — шаблон базы данных, в котором хранится исходная «чистая» база данных. template0 используется в тех случаях, когда необходимо создать новую базу данных без внесения изменений в шаблон template1. Также template0 можно использовать, чтобы вернуть template1 в изначальное состояние, а также если есть необходимость внести изменения в кодировку или локаль создаваемой базы данных. Данные изменения можно вносить только в шаблон template0.

Как было упомянуто ранее, если внести изменения (добавить объекты или прочие SQL-запросы) в шаблон template1, они будут созданы при создании новой базы данных. Рассмотрим на конкретном примере.

1) Переходим в оболочку psql, используя логин postgres:

sudo -i -u postgres psql

2) Выведем список всех доступных баз данных:

\l

По умолчанию после установки PostgreSQL всегда создаются три базы данных: postgres, template0 и template1.

3) Подключаемся к базе данных с шаблоном template1:

\c template1

4) По умолчанию база данных template1 пустая — в ней отсутствуют какие-либо объекты (схемы, таблицы и т.д.). В этом можно убедиться, выполнив команду:

\dt

5) Для примера создадим таблицу с именем users, в которой будет два столбца: user_id и username:

CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR (50) NOT NULL
);

6) Проверяем, что таблица присутствует в базе данных:

\dt

7) Далее создадим новую базу данных с именем new_db при помощи шаблона template1, используя оператор TEMPLATE:

CREATE DATABASE new_db TEMPLATE template1;

8) Подключаемся к созданной базе new_db:

\c new_db

И проверяем наличие таблицы:

\dt

Как можно увидеть на скриншоте выше, ранее созданная таблица users в шаблоне template1 автоматически была создана в новой базе данных с именем new_db.

Если же при создании новой базы данных вам необходима «чистая» база данных, то необходимо использовать другой шаблон — template0. Синтаксис команды будет одинаковым, как и при использовании template1 (кроме имени самого шаблона). Создадим новую «чистую» базу данных с именем clean_db:

CREATE DATABASE clean_db TEMPLATE template0;

Проверяем, что новая созданная база данных пустая:

\dt

Использование шаблонов в СУБД Microsoft SQL Server

Шаблоны в СУБД Microsoft SQL Server работают похожим образом. Но в отличие от PostgreSQL, вы можете создавать собственные файлы шаблонов, содержащие SQL-код, с целью их дальнейшего использования и запуска. По умолчанию в клиентской программе SQL Server Management Studio (SSMS) уже присутствует коллекция различных шаблонов, они находятся в меню View -> Template Explorer.

Также для вызова меню шаблонов можно воспользоваться сочетанием клавиш Ctrl + Alt + T. 

Рассмотрим использование шаблонов на практике.

1) Убедитесь, что SQL Server запущен на сервере. Для этого перейдите на сервер, на котором установлен экземпляр SQL Server, далее откройте меню «Пуск» и в поиске введите «services». Запустите программу services (сервисы) и найдите сервис с именем SQL Server (MSSQLSERVER). Убедитесь, что в статусе отображается «Running»:

Если же статус будет отсутствовать, то запустите SQL Server вручную.

2) Запускаем SQL Server Management Studio (программа должна быть заранее установлена) и в появившемся окне подключаемся к серверу базы данных. Для этого необходимо использовать следующие поля:

• Server type — выбрать Database Engine.

• Server name — указать имя сервера (hostname) или IP-адрес сервера, на котором установлен SQL Server. По умолчанию SQL Server Management Studio предложит подключиться к локальной базе, указав текущее имя хоста.

• Authentication — если на этапе установки в качестве аутентификации была выбрана аутентификация Windows, то необходимо выбрать Windows Authentication.

Если после нажатия на кнопку Connect появится ошибка, то необходимо поставить чекбокс напротив параметра Trust server certificate (см. выше на скриншоте).

3) После того как подключение к серверу базы данных было установлено, переходим в меню View и в выпадающим списке выбираем пункт Template Explorer:

Справа появится меню с шаблонами:

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

4) Рассмотрим содержимое шаблона «Create Database». Для этого кликаем по названию шаблона и выбираем пункт Edit. Откроется редактор SQL-кода:

Внутри шаблона находится код на языке Transact-SQL (разновидность языка SQL). В данном примере шаблон создает новую базу данных, перед этим проверяя, не существует ли уже такая база данных, и если она присутствует, сначала удалит ее и только потом создаст новую.

5) Отредактируем данный пример, создав новую базу данных с именем testdb. Приводим шаблон к следующему виду:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'testdb'
)
DROP DATABASE testdb
GO

CREATE DATABASE testdb
GO

6) Чтобы запустить шаблон, необходимо перейти в меню Query и в выпадающем списке выбрать пункт Execute:

7) Если запрос был выполнен успешно, то в выводе отобразится соответствующая надпись: Commands completed successfully.

8) Проверим, что созданная база данных testdb действительно создалась. Для этого на панели Object Explorer сначала обновляем статус подключения (кнопка Refresh с синей стрелкой) и только потом смотрим на список доступных баз данных:

Как можно увидеть на скриншоте выше, база данных testdb была успешно создана.

У шаблонов также присутствуют параметры, значения которых можно изменять. Для этого откройте на редактирование необходимый шаблон, перейдите в меню Query, которое находится сверху, и в выпадающем меню выберите пункт Specify Values for Template Parameters:

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

После того как необходимые изменения будут внесены, нажмите на кнопку OK.

Создание собственных шаблонов

1) Также вы можете создавать свои шаблоны. Создадим шаблон, который создаст базу данных с именем newdb, таблицу customer и 3 столбца: user_id, first_name, last_name. Для этого в меню шаблонов (Template Browser) кликаем правой кнопкой мыши по любой директории и в контекстном меню выбираем пункт New → Template:

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

2) Назовем наш новый шаблон Create db and table. В имени шаблонов разрешены пробелы:

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

USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'newdb'
)
DROP DATABASE newdb
GO

CREATE DATABASE newdb
GO

USE newdb
GO


CREATE TABLE customers
(
	user_id INT NOT NULL,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
)
GO

4) Нажимаем на кнопку Execute наверху панели инструментов.

5) Проверяем, что запрос был успешно выполнен:

6) Возвращаемся в Object Explorer, обновляем подключение и проверяем, что база данных newdb была успешно создана, а также были созданы таблица customers и столбцы user_id, first_name, last_name:

Все ранее заданные объекты были успешно созданы.

Также стоит отметить, что по умолчанию файлы шаблонов хранятся по следующему пути (для SQL Management Studio версии 20.0):

C:\Users\Administrator\AppData\Roaming\Microsoft\SQL Server Management Studio\20.0\Templates\Sql

Где Administrator — это имя учетной записи, из-под которой производится запуск SQL Management Studio.

 Содержимое каталога Database:

Разверните базу данных в облаке<br>в пару кликов

Заключение

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