Merge - Новая инструкция Firebird 2.1 для объединения данных

В Firebird 2.1 появилась новая инструкция MERGE. Это групповая операция, которая позволяет произвести произвольную выборку и объединить её с уже существующими в таблице данными. В зависимости от условия condition, будет произведено либо обновление данных - блок <merge when matched>, либо их вставка - блок <merge when not matched>. Шаблон инструкции MERGE:

<merge statement>::=
MERGE 
 INTO <table or view> [ [AS] <correlation name> ]
 USING <table or view or derived table> [ [AS] <correlation name> ]
 ON <condition>
 [ <merge when matched> ]
 [ <merge when not matched> ]

<merge when matched> ::=
 WHEN MATCHED THEN
    UPDATE SET <assignment list>

<merge when not matched> ::=
  WHEN NOT MATCHED THEN
    INSERT [ <left paren> <column list> <right paren> ]
    VALUES <left paren> <value list> <right paren>

В инструкции MERGE должно быть определено как минимум одно из выражений <merge when matched> и <merge when not matched>. Пример MERGE:

 MERGE INTO customers c
 USING (SELECT * FROM customers_delta WHERE id > 10) cd
 ON (c.id = cd.id)
  WHEN MATCHED THEN
   UPDATE SET name = cd.name
  WHEN NOT MATCHED THEN
   INSERT (id, name) VALUES (cd.id, cd.name)

Работа с Firebird 2.1 в Visual Studio

Доброго времени суток друзья!

Набрел на просторах интернета на статью в которой рассматривается работа с Firebird 2.1 в ADO .Net.

Автор приводит примеры использования оператора RETURNING для получения значений после операций удаления и обновления. Рассмотрена новая инструкция UPDATE OR INSERT. Приведены примеры обращения к таблицам мониторинга на c#.

Статья Firebird + ADO .Net доступна в двух вариантах:

• На русском: Firebird 2.1
• На английском: Firebird 2.1

Firebird SQL Server

Firebird SQL server - система управления базами данных, основанная на открытом исходном коде Interbase 6.0. Сервер Firebird разрабатывается под различные платформы: Linux, Solaris, Windows, Unix, MacOS, под 64-х и 32-х битную операционные системы. Firebird является бесплатным более развитым аналогом своего предшественника - Interbase.

Firebird SQL Server существует в 3-х вариантах:

• Firebird Embedded - предназначен для встраиваемых баз данных;
• Firebird Classic Server - на каждое клиентское подключение создает отдельный серверный процесс;
• Firebird Super Server - все клиентские коннекты обслуживаются одним серверным процессом, имеется общий клиентский кеш;

Если вы только начинаете работать с Firebird, то советую вам остановить свой выбор на Firebird Super Server

Скачать последние версии Firebird можно здесь: скачать Firebird.

Компоненты доступа к Firebird

Помимо самого SQL сервера, вам понадобится драйвер для работы с Firebird из различных средств разработки. Я рекомендую воспользоваться IBProvider . Этот OLE DB provider, позволяет работать со всеми версиями Interbase и Firebird, включая Firebird 2.5. Технология OLE DB позволяет разрабатывать приложения под большинство известных средств разработки для Windows: Delphi, ADO, Visual Studio.Net, VBScript, VBA, C++, C#, ASP, ASP.Net и т.Д. Скачать IBProvider можно на официальном сайте: Firebird provider и Interbase driver.

Администрирование Firebird

В дистрибутив Firebird SQL Server включена утилита командной строки, позволяющая производить операции с базой данных. Утилита позволяет выполнять простые функции администрирования БД: создание, удаление, изменение структуры, создание таблиц и связей. Так же предусмотрена возможность выполнения SQL-запросов в режиме командной строки.

isql не единственное средство администрирования Firebird и Interbase. Кроме него существует множество платных и беспланых графических утилит. Наиболее популярные из них: IBExpert, Flame Robin, Blaze Top, IB/FB Development Studio.

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

Firebird 2.1 - Таблицы мониторинга

В Firebird 2.1 появилось 7 таблиц мониторинга.Это виртуальные таблицы, с их помощью можно получить данные об активности сервера, информацию о выполняющихся запросах, активных подключениях, а так же собрать статистику базы данных Firebird. Все таблицы доступны только на чтение. Для таблицы MON$STATEMENTS можно применять оператор DELETE для отмены запросов.

MON$DATABASE

В данной таблице содержится информация о текущей базе данных:

• Путь к базе данных.
• Дата создания.
• Диалект.
• Номера транзакций.
• Размер страницы.
• Версия ODS.
• И т.п.

MON$ATTACHMENTS

В таблице отображаются активные на данные момент подключения к серверу Firebird. С помощью MON$ATTACHMENTS можно прочитать информацию о подключившемся клиенте: путь, PID процесса, имя хоста, протокол, пользователь, состояние подключения и т.д.

В релизе Firebird 2.1 таблица только на чтение. Возможность удалять зависшие коннекты из данной таблицы появится в Firebird 2.5

MON$TRANSACTIONS

Информация о запущенных транзакциях: тип, уровень изоляции, режим autocommit, состояние (idle или active), ссылка на подключение и т.п.

MON$STATEMENTS

Информация о выполняющихся и подготовленных запросах SQL и GDML. В FB 2.1 вы можете выполнить операцию DELETE над этой таблицей для отмены выполняющегося запроса:

delete from mon$statements
where mon$attachment_id <> current_connection

MON$CALL_STACK

MON$CALL_STACK - Информация об активных запросах PSQL, т.е. о выполняемых в данный момент хранимых процедурах и триггерах.

MON$IO_STATS

MON$IO_STATS - Статистика ввода вывода, число прочитанных/записанных страниц и т.п.

MON$RECORD_STATS

MON$RECORD_STATS - Статистика уровня записей: число удаленных, добавленных, вставленных записей, число записей прочитанных по индексу и последовательным перебором и т.д.

Firebird 2.1 - Краткий обзор новых возможностей

Для тех, кто планирует переход на Firebird 2.1 с более старых версий FB я собрал краткий обзор тех изменений, которые характерны для данного релиза. Итак:

Таблицы мониторинга Firebird 2.1

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

Глобальные временные таблицы

• Временные таблицы позволяют хранить промежуточные данных. Время жизни – транзакция или подключение.
• Общие метаданные и раздельные данные.
• Поддерживаются индексы, триггеры, уникальность и ссылочная целостность.
• Данные хранятся во временных файлах - Мгновенная очистка.
• Нет проблем сборки мусора.

Аутентификация Firebird средствами Windows

В Firebird 2.1 реализована возможность SSPI аутентификации средствами Windows. Администраторы домена на уровне БД определяются как суперпользователи SYSDBA.

Общие табличные выражения. CTE (Common Table Expressions)

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

Повышение производительности

• Группировка пакетов для передачи по сети.
• Кэширование информации о запросе.
• Улучшения сетевого протокола.
• Оптимизация алгоритма сброса страниц на диск для кеша большого размера.
• Большие сортировки на диске.
• Повышена скорость чтения\записи внешних таблиц.
• Прочее.

Триггеры уровня базы данных

Поддерживаются следующие виды триггеров:

• CONNECT
• DISCONNECT
• TRANSACTION START
• TRANSACTION COMMIT
• TRANSACTION ROLLBACK

Для отключения триггеров БД в Firebird 2.1 можно воспользоваться утилитой командной строки:

isql temp.fdb -user SYSDBA -pass masterkey -nodbtriggers
Database: temp.fdb, User: SYSDBA 
SQL> ALTER TRIGGER TRG_CONN INACTIVE; 
SQL> EXIT;

Домены в процедурах и триггерах

Домены в параметрах хранимых процедур:

CREATE PROCEDURE MY_PROC(IN_PARAM [TYPE OF] DOMAIN_A) RETURNS (OUT_PARAM [TYPE OF] DOMAIN_B)

Декларативные переменные в хранимых процедурах и триггерах:

DECLARE VARIABLE VAR1 [TYPE OF] DOMAIN_A

Приведение типов к заданному домену:

OUT_PARAM = CAST(VAR1 AS [TYPE OF] DOMAIN_B)

Изменения в SQL

• MERGE - объединение диапазонов данных по заданным условиям.
• INSERT OR UPDATE - общая инстукция для вставки и обновления.
• Возврат значений через RETURNING для UPDATE, DELETE, INSERT OR UPDATE.

Добавлено множество новых встроенных функций. В следующих сообщениях этого блога все эти вопросы будут рассмотрены более подробно.