Фирма

«Инрэко ЛАН»

Введение.

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

Автоматическая генерация SQL для добавления колонок

Ситуация, послужившая импульсом для написания этой статьи, взята из реальной жизни. Мне понадобилось написать SQL-скрипт для добавления колонки с отметкой об удалении сразу примерно к двум десяткам таблиц, что-то вроде этого:

IF NOT EXISTS (
  SELECT * 
    FROM sys.columns 
   WHERE [object_id] = OBJECT_ID(N'SimpleTable') AND name = 'IsDeleted')
   
  ALTER TABLE SimpleTable
      ADD IsDeleted bit NOT NULL CONSTRAINT DF_SimpleTable_IsDeleted DEFAULT(0)
GO

Сходу можно придумать три решения:

  1. Скопировать 20 раз повторяющийся фрагмент кода и 20 раз заменить имя таблицы.
  2. Написать на SQL сценарий, который генерирует SQL и потом исполняет его
  3. Написать программу, которая генерирует SQL.

Первый способ был бы хорош, когда таблиц 3-5. Он вполне применим и в моем случае, но чреват мелкими, но неприятными ошибками. Например, можно при неаккуратном копировании получить имя ограничения (DEFAULT CONSTRAINT), не соответствующее шаблону DF_<имя таблицы>_<имя колонки>. В результате применения второго способа получится не очень хорошо читаемый сценарий. Третий способ лишен этих недостатков.

Главное, чем нужно руководствоваться, выбирая между первым и третьим способом, трудоемкость. Как только я подумал о программе на компилируемом языке, я сразу понял, что это не для данного случая. Поискав на рабочей машине какой-нибудь интерпретатор, я ничего не нашел, кроме Emacs, в котором есть интерпретатор Лиспа.

Лисп довольно старый и не очень распространенный в настоящее время язык, поэтому надо сказать пару слов о нем, прежде чем продолжить. Лисп был разработан в конце 1950-х годов в Массачусетском Технологическом Институте в результате проекта по исследованию искусственного интеллекта. Emacs LisP намного проще, чем Common LisP, и чтобы освоить его на начальном уровне требуется совсем немного времени. Само название LisP означает List Processing. Уже из названия ясно, что списки - основа Лиспа.

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

  1. ничего не сделает, а только вернет вам сам список;
  2. отобразит сообщение об ошибке;
  3. обработает первый символ в списке как команду сделать что-либо.

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

(+ 2 2)

Присваивание переменной осуществляется при помощи выражения:

(setq имя-переменной значение переменной)

Определение функции:

(defun имя-функции (параметры) выражения)

Для работы со списками применяются выражения: list - создать список, car - получить первый элемент в списке, cdr - получить список без первого элемента

Чтобы дать подробное описание синтаксиса Лиспа маленькой статьи мало, поэтому я сразу приведу свое решение с небольшими комментариями, чтобы было более понятно:

(progn
  (defun append-script (table-name)
    (setq script (concat "
IF NOT EXISTS (
  SELECT * 
    FROM sys.columns 
   WHERE [object_id] = OBJECT_ID(N'" table-name "') AND name = 'IsDeleted')
   
  ALTER TABLE " table-name "
      ADD IsDeleted bit NOT NULL CONSTRAINT DF_" table-name "_IdDeleted DEFAULT(0)
GO

"))
    (set-buffer (get-buffer-create "script.sql"))
    (insert script)
    )

  (defun append-scripts (tables)
    (if tables
  (list (append-script (car tables))
   (append-scripts (cdr tables)))))

  (setq tables '("SimpleTable" "SimpleTable2" ... "SimpleTable20"))
  (append-scripts tables)
)

Здесь выражение set-buffer устанавливает буфер, в котором будет осуществляться редактирование, get-buffer-create возвращает буфер с именем, переданным в качестве параметра (если нет, то будет создан), insert - вставляет текст в текущий выбранный буфер.

Выполнить программу еще проще, чем ее написать. Код надо открыть в буфере с режимом Elisp (например, в стандартном *scratch*), разместить курсор в конце буфера и выполнить команду evaluate (C-x-C-e в станадартной настройке клавиатуры). В результате будет создан буфер с названием script.sql, содержащий то, что и требовалось.

Полезные ссылки:

Emacs
http://www.gnu.org/software/emacs/
Emacs Lisp
http://www.emacs.uniyar.ac.ru/doc/elisp-intro/elisp-intro-ru_toc.html

Метки: Emacs | LisP | SQL | автоматизация разработки

Комментарии  

#1 kim 20.09.2010 19:58
Иван, всё вышеописанное прекрасно делается средствами самого Sql Server:

DECLARE @Tables TABLE
(
TableName VARCHAR(256) COLLATE Latin1_General_CI_AI
)
INSERT INTO @Tables VALUES ('Table1')
INSERT INTO @Tables VALUES ('Table2')
INSERT INTO @Tables VALUES ('Table3')
INSERT INTO @Tables VALUES ('Table4')
INSERT INTO @Tables VALUES ('Table5')

-- Check tables
DECLARE
@SQL NVARCHAR(MAX),
@TableName VARCHAR(256)

SET @SQL = ''
DECLARE CursorTables CURSOR FOR
SELECT TableName FROM @Tables ORDER BY TableName
OPEN CursorTables
FETCH NEXT FROM CursorTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL +
'IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE [object_id] = OBJECT_ID(N''' + @TableName + ''') AND name = ''IsDeleted'')

ALTER TABLE '+ @TableName +
' ADD IsDeleted bit NOT NULL CONSTRAINT DF_' + @TableName + '_IsDeleted DEFAULT(0)
GO' + CHAR(10) + CHAR(13)

FETCH NEXT FROM CursorTables INTO @TableName
END
CLOSE CursorTables
DEALLOCATE CursorTables
PRINT @SQL
Цитировать
#2 Иван 21.09.2010 01:41
Согласен, я даже об этом написал. Но ведь так интереснее :-). Transact SQL я и так неплохо знаю, а здесь получилось совместить приятное с полезным: освежить свои знания по Лиспу и работу сделать.
Цитировать
#3 kim 21.09.2010 16:44
Если есть время на освежение знаний, тогда не спорю - можно и на Lisp-е. )))
Цитировать
#4 iamzet 23.09.2010 00:54
Ну да, согласен с kim, очевидных преимуществ в подходе нет. В примере выше для простоты можно даже курсор убрать, получится один select, вряд ли этот код будет менее читаемым чем lisp программа. В любом случае, вряд ли стоит использовать такие экзотические решения на постоянной основе - сложнее environment, сложнее поддерживать. Ведь такой же скрипт для удаления столбца с проверкой и удалением всех (зараннее неизвестных) зависимостей, будет радикально сложнее.
Цитировать
#5 Иван 23.09.2010 02:53
Хотелось обойтись без лирических отступлений, но видимо не получится. Emacs мой любимый текстовый редактор, но встроенный в него Лисп я ни разу не применял (кроме простой правки настроек). На C# + Transact SQL я сейчас пишу 8 часов 5 дней в неделю. Не скажу, что мне это не нравится, но иногда хочется разнообразия. Когда возникла необходимость решить задачу, о которой я здесь написал, я в первый момент выбирал между копипастом и тем решением, что предложил kim (с той лишь разницей, что вместо PRINT @SQL использовать sp_executesql @SQL. Потом подумал: "А почему бы и нет?!" - и написал то, что написал. В результате и отдохнул (так как немного отвлекся основной работы), и работу при этом сделал.
В том, что на постоянной основе использовать подобные эксперименты не стоит полностью согласен с iamzet. На счет читабельности не очень понял - в результате получается одинаковый sql-скрипт, который будет сохранен в репозитории для дальнейшего использования.
Цитировать

Добавить комментарий