Как написать sql скрипт

Как сгенерировать SQL скрипт создания объектов и данных в Microsoft SQL Server?

Привет! Сегодня мы поговорим о том, как можно сгенерировать SQL скрипты создания объектов базы данных Microsoft SQL Server, включая сами данные, стандартными средствами SQL Server Management Studio (SSMS).

Что такое SQL скрипт объекта базы данных?

SQL скрипт объекта базы данных – это SQL инструкция, с помощью которой создается этот объект, сохраненная в текстовом файле.

Иными словами, это простой SQL запрос, обычно сохраненный в текстовом файле с расширением .sql. В этом SQL запросе содержатся все необходимые инструкции создания объекта (или объектов), включая инструкции наполнения его данными.

Такой SQL скрипт можно открыть любым текстовым редактором, скопировать текст SQL запроса и выполнить, например, в среде SQL Server Management Studio, таким образом, создав объект базы данных, не разрабатывая соответствующие SQL инструкции самостоятельно.

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

Что могут содержать SQL скрипты?

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

• Инструкции создания таблиц (CREATE);
• Заполнение таблиц (инструкции INSERT);
• Определение представлений, функций, хранимых процедур, триггеров;
• Определение ограничений и индексов;
• Определение создания других объектов;
• И другие SQL инструкции.

Для чего могут потребоваться SQL скрипты объектов базы данных?

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

Или для того, чтобы передать эти SQL скрипты другому администратору, разработчику или заказчику, чтобы он создал подобные объекты на своем экземпляре SQL Server.

Таким образом, такие SQL скрипты необходимы для хранения копий SQL инструкций, с помощью которых создавались объекты базы данных.

Как создать SQL скрипт объекта базы данных в Microsoft SQL Server?

Так как SQL скрипт – это обычный текстовый файл, его можно создать вручную, например, когда Вы разрабатываете объект БД, Вы просто сохраняете SQL инструкцию в файл, и добавляете в него по мере необходимости другие SQL инструкции.

Однако также возможно автоматически сгенерировать SQL скрипты объектов базы данных специальными инструментами, например, в среде SQL Server Management Studio (SSMS). А как это делается, я сейчас и покажу.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Создание SQL скрипта объекта базы данных Microsoft SQL Server

В качестве исходных данных у меня будет база данных TestDB и таблица Goods, SQL скрипт которой мне и нужно создать. При этом мне необходимо, чтобы скрипт включал не только определение инструкции CREATE, но и данные, которые содержит эта таблица.

В качестве инструмента я буду использовать SQL Server Management Studio.

Шаг 1 – Запускаем SSMS

Сначала запускаем среду SQL Server Management Studio любым удобным для Вас способом, иными словами, никаких особых манипуляций с открытием SSMS выполнять не требуется.

Шаг 2 – Запускаем задачу «Сформировать скрипты»

Далее в обозревателе объектов находим нужную нам базу данных, затем открываем контекстное меню правой кнопкой мыши, ищем пункт «Задачи», и выбираем задачу «Сформировать скрипты».

В итоге запустится мастер создания скриптов. В окне «Введение» можем сразу нажать «Далее».

Шаг 3 – Выбираем объекты для включения в SQL скрипт

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

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

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

Шаг 4 – Задание параметров SQL скрипта

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

• Сохранить в файл – при этом мы можем сами указать нужный нам каталог для сохранения;
• Сохранить в буфер обмена;
• Сохранить в новое окно запросов.

Также есть и дополнительные параметры, чтобы их открыть, необходимо нажать на соответствующую кнопку.

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

Также Вы можете включить в скрипты инструкции DROP на случай, если Вам нужно пересоздать объекты.

После того как все параметры заданы, нажимаем «ОК», а после для продолжения кнопку «Далее».

Шаг 5 – Проверка параметров и запуск процесса создания скрипта

На данном шаге все проверяем и запускаем сам процесс формирования скрипта, т.е. нажимаем «Далее».

Шаг 6 – Завершение процесса и результат

Когда процесс будет завершен, программа сообщит Вам об этом, нажимаем «Готово».

В результате в каталоге, который Вы указали в параметрах формирования скрипта, появится SQL скрипт, а по факту обычный текстовый файл с расширением .sql.

Если его открыть, например, текстовым редактором, то можно посмотреть на все SQL инструкции. Они будут выглядеть примерно следующим образом.

Видео-инструкция

На сегодня это все, надеюсь, материал был Вам полезен, пока!

Источник статьи: http://info-comp.ru/create-script-in-ms-sql-server

MySql-миграции: что это и как реализовать простым php-скриптом

Когда разрабатываешь веб-приложение не один, а в команде и/или на нескольких машинах, рано или поздно сталкиваешься с проблемой синхронизации кода проекта и базы данных. Для управления кодом есть системы контроля версий, в частности, git, а для СУБД придуманы миграции.

Есть много готовых разнообразных инструментов, которые занимаются миграциями, но!

Очень часто все, что мы хотим — это просто залить в базу изменения, которые сделаны другим разработчиком или же самим собой на другой машине. И желательно при этом затратив минимальные усилия, в том числе и на изучение и настройку незнакомой системы. К тому же далеко не всегда мы располагаем полным доступом к серверу для установки оных инструментов.

Поэтому всех, кому интересно узнать, как самим сделать простую утилиту миграций, написав полсотни строк php-кода, прошу в статью.

Идея миграций

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

Нужно учесть 2 вещи: во-первых, каждая миграция должна выполняться строго один раз, а во-вторых, в строго определенном порядке. Это разумно и обязательно, потому как если нам прилетают от коллеги 2 миграции, одна из которых создает таблицу users, а другая добавляет в нее тестовых пользователей, то мы хотим выполнить эти скрипты именно в таком порядке и не добавить при этом данные в базу больше одного раза.

Проблему повторных выполнений миграций мы решим, записывая в отдельную таблицу уже отработавшие скрипты, а порядок выполнения установим четкими правилами именования sql-файлов. Как это решается в коде, увидим чуть позже, а пока займемся подготовкой самих тестовых миграций (исходники всех миграций и php-скрипта в конце статьи)

Создаем тестовые sql-скрипты

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

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

В тестовом примере рассмотрим такой ход событий.
Первая миграция накатывает уже существующие данные и создает таблицу versions — она содержит уже выполненные скрипты.
Вторая создает таблицу users с тремя полями: id, email, password.
Третья добавляет в users три тестовых записи.
А четвертая добавляет в users новую колонку active.

Напишем первую миграцию: дамп базы и таблица versions. Пусть на момент внедрения миграций у нас есть таблица goods с парой товаров. Их нужно скинуть в скрипт и в тот же скрипт добавить таблицу versions. В итоге файл будет выглядеть так.
0000_base.sql

В файле мы видим структуру goods и 2 строчки с данными, а также versions. Эта таблица содержит 3 столбца: первичный ключ id, name — имя файла с миграцией, created — дата ее накатывания. Как Вы догадываетесь, в versions мы будем добавлять строки после накатывания новых миграций. А пока создадим оставшиеся 3 миграции.

На заметку: возможно, Вам не удобно писать sql-скрипты руками, Вы привыкли создавать и заполнять таблицы через phpMyAdmin или другой инструмент. Спешу успокоить, все известные мне утилиты позволяют генерировать такой sql-код автоматически. То есть Вы можете работать с базой, как удобно, а при подготовке файла-миграции вытащить нужный скрипт из условного phpMyAdmin-a в режиме copy-paste.

Обратим внимание на правила наименования файлов — это важная часть.

• 0001_base.sql
• 0002_add_users.sql
• 0003_insert_data_into_users.sql
• 0004_add_column_active_to_users.sql

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

В нашем варианте мы предполагаем, что миграций не будет больше 9999 штук, и номер всегда должен состоять из 4-х цифр. Например, 10-я миграция будет называться 0010_description, а 101-я — 0101_description.

Теперь у нас все готово к написанию php-скрипта для выполнения миграций — migration.php.

Пишем php-код для запуска миграций

Подумаем, что нам нужно уметь делать.

• Во-первых, получить список всех sql-файлов из папки sql.
• Во-вторых, понять, какие из них уже накатились ранее (сравнить этот список с тем, что лежит в таблице versions).
• В-третьих, залить содержимое отобранных файлов в базу, заодно записав в versions их названия.

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

Каркас migration.php, базовые константы и функции

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

Пробежимся по коду.

• Сначала объявляем константы для подключения к базе и с названием таблицы versions (она будет упоминаться в коде несколько раз).
• Функция connectDB возвращает mysqli-объект для работы с базой.
• getMigrationFiles($conn) возвращают список актуальных, еще не выполненых миграций.
• migrate($conn, $file) накатывает одну миграцию — по имени файла.

Дальше сама логика работы.

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

Теперь нам нужен код для getMigrationFiles()

Почти каждая строка прокомментирована, но поясню еще подробнее.
В переменную $sqlFolder попадает полный абсолютный путь к текущему файлу migration.php. А соответственно, и к папке sql с миграциями. Замена \\ на / нужна для тех случаев, когда realpath возвращает путь с обратными слешами вместо прямых (например, в windows). Первый \ нужен для экранирования второго.

Дальше функция glob вытащит все файлы из указанной папки по нужной маске *.sql.

Затем нужно понять, не в первый ли раз мы собираемся накатывать миграции. Определить это можно только по наличию таблицы versions в базе. Мы же помним, что она создается в самой первой миграции. Если запрос show tables from test like «versions» вернет пустой список, значит таблицы еще нет, в переменную $firstMigration запишем true и вернем из функции весь список файлов.

Если же таблица versions существует, то вытаскиваем из нее список файлов — уже выполненные миграции. И возвращаем из функции файлы, которых нет в таблице versions. В этом поможет array_diff.

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

Из интересного в этой функции только shell_exec($command) — выполнение строки $command в терминале. Залить содержимое sql-файла в базу можно такой командой
mysql -uuser -ppassword -h host -D database Метки:

• рубрика «полезное»
• mysql
• php

Источник статьи: http://webdevkin.ru/posts/backend/mysql-migrations