Firebird (database server)

Как вести логи изменений данных пользователями в базе данных, сохраняя их в другой базе данных

Уважаемые читатели. Это вторая статья из цикла по базам данных. Решил сделать некоторое оглавление по планируемым статьям этого цикла:

1. Как сделать разный часовой пояс в разных базах данных на одном сервере.
2. Как вести логи изменений данных пользователями в базе данных, сохраняя их в другой базе данных, для того чтобы основная база данных не забивалась мусором и не росла.
3. Как создать свою файловую систему на основе blob полей в базе данных. Почему это удобно. Вопросы эффективности хранения файлов: как получить максимальное быстродействие и при этом минимальное занимаемое место.

Я был удивлен количеством комментариев к первой статье, поэтому сразу хочу заметить, что не претендую на единственно правильный способ реализации. Я уверен, что творческие люди найдут еще немало других способов реализовать данную задачу. Но реализуя ее в свое время, я не нашел ни одной статьи с описанием такого функционала и делать данную задачу пришлось с нуля, хотя она на мой взгляд актуальна. Реализация, которую я буду описывать, полностью рабочая и используется мной на практике.
Так же я приветствую конструктивную критику. Бывает люди пишут интересные вещи и ты можешь взглянуть на проблему под углом, о котором не предполагал и как-то улучшить свои механизмы.
Итак начнем.
База данных firebird 3.
Формулировка задачи следующая: необходимо писать подробные логи изменений данных пользователями в базе данных (insert, update, delete), но при этом писать их в другой базе данных на другом сервере. Необходимо это для того чтобы размер основной базы данных не рос как на дрожжах, ее удобно было бекапить, ресторить, чтобы она работала быстро, не накапливала мусора, не содержала лишней и редконужной информации.

Компонент TFDTransaction

Параметр
Назначение
Connection
Связь с компонентом FDConnection.
Options.AutoCommit
Управляет автоматическим стартом и завершением транзакции. Значение по умолчанию True.
Если значение этого свойства установлено в True, то FireDAC делает следующее:Запускается транзакция (если требуется) перед выполнением каждой SQL команды, и завершает транзакцию после выполнения SQL команды. Если команда выполнится успешно, то транзакция будет завершена как COMMIT, иначе — ROLLBACK.
Если приложение вызывает метод StartTransaction, то автоматическое управление транзакциями будет отключено, до тех пор, пока транзакция не завершится как Commit или Rollback.

В Firebird автоматическое управление транзакциями эмулируется самими компонентами FireDAC.
Options.AutoStart
Управляет автоматическим запуском транзакции. По умолчанию True.
Options.AutoStop
Управляет автоматическим завершением транзакции. По умолчанию True.
Options.DisconnectAction
Действие, которое будет выполнено при закрытии соединения, если транзакция активна. Значение по умолчанию xdCommit. Возможны следующие варианты:xdNone – ничего не будет сделано. Действие будет отдано на откуп СУБД;
xdCommit – подтверждение транзакции;
xdRollback – откат транзакции.

В других компонентах доступа значение по умолчанию для подобного свойства xdRollback. Поэтому необходимо выставлять это свойство вручную в то значение которое действительно требуется.
Options.EnableNested
Управляет вложенными транзакциями. Значение по умолчанию True.
Когда транзакция активна, то следующий вызов StartTransaction создаст вложенную транзакцию. FireDAC эмулирует вложенные транзакции, используя точки сохранения, если СУБД не поддерживает вложенные транзакции в явном виде. Чтобы отключить вложенные транзакции, установите EnableNested в False и следующий вызов StartTransaction вызовет исключение.
Firebird не поддерживает вложенные транзакции в явном виде.
Options.Isolation
Определяет уровень изолированности транзакции

Это самое важное свойство транзакции. Значение по умолчанию xiReadCommited

Возможны следующие варианты:xiUnspecified – используется уровень изоляции по умолчанию для вашей СУБД (в Firebird это SNAPSHOT, т.е. с параметрами read write concurrency wait);
xiDirtyRead – этого уровня изолированности в Firebird не существует поэтому вместо него будет использован READ COMMITED;
xiReadCommited – уровень изолированности READ COMMITED. В Firebird такая транзакция стартует с параметрами read write read_committed rec_version nowait;
xiRepeatableRead – этого уровня изолированности в Firebird не существует поэтому вместо него будет использован SNAPSHOT;
xiSnapshot – уровень изолированности SNAPSHOT. В Firebird такая транзакция стартует с параметрами read write concurrency wait;
xiSerializable – уровень изолированности SERIALIZABLE. На самом деле в Firebird не существует транзакции с данным уровнем изолированности, но он эмулируется запуском транзакции с параметрами read write consistency wait.

Options.Params
Специфичные для СУБД параметры транзакции. В настоящее время используется только для Firebird и Interbase. Возможные значения:read
write
read_committed
concurrency
consistency
wait
nowait
rec_version
no rec_version

Options.ReadOnly
Указывает является ли транзакция только для чтения. По умолчанию False. Если установлено в True, то любые изменения в рамках текущей транзакции невозможны, в Firebird в этом случае отсутствует значение read в параметрах транзакции.
Установка этого свойства в True позволяет СУБД оптимизировать использование ресурсов.

Описание

Оператор GRANT предоставляет одну или несколько привилегий для объектов базы данных пользователям, ролям, хранимым процедурам, триггерам и представлениям.

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

Для таблиц существуют следующие привилегии:

Для выполнения хранимых процедур существует отдельная привилегия EXECUTE. Она позволяет выполнять хранимые процедуры и делать выборку данных из процедур выбора (с помощью оператора SELECT).

В предложении TO указывается список пользователей, ролей и объектов базы данных (процедур, триггеров и представлений) для которых будут выданы перечисленные привилегии. Необязательные предложения USER и ROLE позволяют уточнить, кому именно выдается привилегия. Если ключевое слово USER или ROLE не указано, то сервер проверяет, существует ли роль с данным именем, если таковой не существует, то привилегии назначаются пользователю. Существование пользователя, которому выдаются права, не проверяются при выполнении оператора GRANT. Если привилегия выдается объекту базы данных, то необходимо обязательно указывать тип объекта.

Замечание:
Несмотря на то, что ключевые слова USER и ROLE не обязательные, желательно использовать их, чтобы избежать путаницы.

В SQL существует специальный пользователь PUBLIC, представляющий всех пользователей. Если какая-то операция разрешена пользователю PUBLIC, значит, любой аутентифицированный пользователь может выполнить эту операцию над указанным объектом.

Замечание:
Если привилегии назначены пользователю PUBLIC, то отозваны они должны быть у пользователя PUBLIC.

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

Другое назначение оператора GRANT заключается в назначении ролей для группы перечисленных пользователей. В этом случае после предложения GRANT следует список ролей, которые будут назначены списку пользователей, указанному после предложения TO.

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

При предоставлении прав в базе данных в качестве лица, предоставившего эти права, обычно записывается текущий пользователь. Используя предложение GRANTED BY можно предоставлять права от имени другого пользователя. При использовании оператора REVOKE после GRANTED BY права будут удалены только в том случае, если они были зарегистрированы от удаляющего пользователя. Для облегчения миграции из некоторых других реляционных СУБД нестандартное предложение AS поддерживается как синоним оператора GRANTED BY.

Предложение GRANTED BY может использовать:

• Владелец базы данных;
• SYSDBA;
• Любой пользователь, имеющий права на роль RDB$ADMIN и указавший ее при соединении с базой данных;
• При использовании флага AUTO ADMIN MAPPING — любой администратор операционной системы Windows (при условии использования сервером доверенной авторизации — trusted authentication), даже без указания роли.

Даже владелец роли не может использовать GRANTED BY, если он не находится в вышеупомянутом списке.

Установка Firebird

1.   Диски куда будет устанавливаться FirebirdSQL cервер Firebird и любые базы данных которые вы создаете или к которым подключаетесь должны распологаться на жестком диске, который физически подключен к хост-компьютеру. Вы не можете расположить файлы сервера или любой другой базы данных на mapped drive, filesystem share или сетевой файловой системе.
Вы можете подключиться к базе данных расположенной на CD-ROM (в режиме чтения), но вы не можете запустить с него SQL сервер Firebird и не сможете обновить данные в базе.

2. Установочный скрипт или программа
Несмотря на то, что есть возможность установить Firebird простым копированием файлов, такая как «untarring» файла билда или распаковка .zip-файла, но при первой установке очень рекомендуется использование распространяемого комплекта ПО. Исполняемый файл установки под Windows, RPM-пакет (RedHat Package Manager) под Linux и официальный файл tar.gz для других POSIX-платформ.

2.1.  Платформа Windows
На серверных версиях Windows — Windows NT, 2000, ХР и Vista сервис Firebird запустится после завершения установки. После перезагрузки вашего компьютера он запустится автоматически.

Несерверные версии Windows — Windows 95, 98 и ME, не поддерживают сервисы. SQL cервер Firebird будет работать как приложение, защищенное другим приложением известное как Guardian. Если серверное приложение по какой-то причине завершилось неправильно, то Guardian попробует перезапустить его.
Копия клиентской библиотеки — fbclient.dll или gds32.dll будет установлена в системную директорию Windows.
2.2.  POSIX-платформы
В любом случае прочитайте release notes относящиеся к той версии Firebird которую вы собираетесь устанавливать. В них могут быть значительные отличия от релиза к релизу любой POSIX-совместимой операционной системы, в особенности относящиеся к категории open source. Где возможно, инженеры создающие билды для каждой версии Firebird, документируют любую известную проблему.
Если в вашем комплекте ПО нет копии release notes, то сходите на download-страницу веб-сайта Firebirdи скачайте копию оттуда.
Проще всего осуществить установку с использованием портов. Если порт под Firebird (InterBase) отсутствует его достаточно просто скачать в Интернете и установить в систему. На примере Freebsd. Заходим в /usr/ports/databases/firebird-server-xx (где хх — версия Firebird (InterBase)) и набираем make install. Все должно установиться. После чего аналогично поступаем с /usr/ports/databases/firebird-clientr-xx. В других Unix-овых системах установка через порты примерно такая же.
Если у вас есть дистрибутив Linux поддерживающий установку из RPM-пакетов, то, если необходимо, прочитайте соответствующую документацию для инструкций по использованию RedHat Package Manager’a. В большинстве дистрибутов у вас есть выбор производить ли установку из командной строки или посредством GUI интерфейса.
Для дистрибутивов не умеющих работать с RPM-пакетами и для ряда UNIX систем, используйте комплект „tar.gz». В release notes вы найдете подробные инструкции по установке.
В комплект включены shell-скрипты. В некоторых случаях, в release notes может быть написано чтобы вы изменили эти скрипты и произвели некоторые корректировки.
Для установки из rpm пакета необходимо выполнить следующую команду:
$rpm -Uvh Firebird.x.x.x.rpm
где Firebird.x.x.x.rpm является именем скачанного инсталляционного пакета.
Для установки с использованием пакета в формате tar.gz необходимо выполнить следующее: $tar -xzf Firebird.x.x.x.tar.gz 
$cd install 
$./install.sh

Выполнение этих команд приводит к одному и тому же результату — на Linux-машине будет установлен Firebird (InterBase). Вариант с архитектурой SuperServer будет функционировать в виде демона, a Classic — в виде сервиса.
Надо заметить, что приведенный порядок установки годится практически для любой версии Firebird (InterBase). Правда, для различных дистрибутивов Linux могут существовать свои особенности и хитрости при установке, так что лучше посмотреть рекомендации по установке для вашей версии ОС Linux/Unix.

Слепки состояния базы данных (database snapshots) на основе порядка фиксации

В Firebird 4.0 кардинально переработана концепция создания слепков состояния базы данных (database snapshot). Ранее для создания слепков (snapshot) требовалось создать копию TIP (transaction inventory page), в Firebird 4 достаточно просто запомнить номер фиксации (Commit Number). Таким образом, процесс создания слепка в Firebird 4 требует значительно меньше ресурсов, чем ранее.

Режим изолированности READ COMMITTED READ CONSISTENCY

Поскольку процесс создания слепка состояния базы данных в Firebird 4, обходится значительно дешевле чем ранее. Это позволяет создавать такие слепки не только в момент старта транзакции SNAPSHOT, но и для каждого SQL запроса и открытого курсора в новом режиме изолированности транзакции . В режиме изолированности запросы всегда читают согласованное состояние базы данных на момент старта запроса.

Промежуточная сборка мусора

Ранее ненужные версии записей (мусор) могли быть удалены только для версий, созданных транзакцией, номер которой меньше чем OST (Oldest Snapshot Transaction). То есть мусор можно было удалить только из конца цепочки версий. Новая концепция создания слепков состояния базы данных (database snapshot) позволила удалять мусор в цепочке версий между активными версиями записи (версий для которых есть активный слепок состояния). Это позволяет значительно сократить длину цепочки версий при наличии длительных активных транзакций и сократить их негативное влияние на производительность.

Установка режимов клиент/сервер и встроенного режима сервера

Firebird может работать в режиме клиент/сервер и в режиме встроенного сервера.

Client/Server означает, что у вас где-то есть физический сервер Firebird: либо на локальной машине, либо на другой машине, доступной по вашей сети. Соединения с сервером проходят через TCP/IP; при указании соединения имя хоста содержит имя или IP-адрес. Серверу Firebird для «общения» с вашими компонентами доступа необходима fbclient.dll/fbclient.so.nnn (вместе с его файлами поддержки).

Embedded Firebird означает, что ваше приложение для доступа к базе данных Firebird на локальной машине загружает единственную библиотеку, которая является и сервером, и клиентской библиотекой Firebird «в одном флаконе». При указании строки соединения имя хоста всегда пусто (не указывается). Серверу Firebird для «общения» с вашими компонентами доступа необходима fbembed.dll/fbembed.so (вместе с его файлами поддержки).
См. вики-страницу на Firebird embedded для более подробной информации.

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

Прим.перев.: необходимо уточнить, что выше описанное справедливо для версий FB 1.5, FB 2.0, FB 2.1 и FB 2.5. Начиная с версии FB 3.0, режим используемого сервера зависит от строки подключения, а клиентская библиотека на все случаи жизни одна — все та же fbclient.dll/libfbclient.so.3.0.n (где n — номер билда версии FB).

Windows

Win64: пожалуйста, см. предупреждение о нежелательном использовании некоторых версий FPC/Lazarus Win64.

Для Windows: (это относится ко всем драйверам базы данных SQLDB) необходимо иметь fbclient.dll (или fbembed.dll’ ) и его поддержку dll, установленных в:

• каталог проекта и каталог вывода исполняемого файла/каталог приложения (например, lib /что-то еще в каталоге вашего проекта)
• или каталог в вашем PATH (не в системном каталоге)
• Если вы хотите использовать системный каталог, пожалуйста, используйте официальный установщик и поставьте галочку «скопировать fbclient в системный каталог»

Как и для всех библиотек DLL (базы данных), разрядность библиотеки DLL должна соответствовать вашему приложению: используйте 32-разрядную библиотеку для 32-разрядной скомпилированной программы и 64-разрядную библиотеку для 64-разрядной программы.

Unix/Linux/macOS

В Linux/macOS/FreeBSD должна быть установлена ​​клиентская библиотека Firebird (например, вашим менеджером пакетов; установите обычный пакет и пакет -dev), или они должны быть прописаны в путях поиска библиотеки.

FPC ищет наиболее распространенные имена библиотек (например, libfbclient.so.2.5, libgds.so и libfbembed.so.2.5; пожалуйста, проверьте ibase60.inc, если ваша версия отличается). При желании можно указать имя библиотеки. Есть 2 способа сделать это:

• использовать компонент TSQLDBLibraryLoader из sqldblib (FPC 2.7.1). Работает для всех компонентов коннектора SQLDB.
• вызвать функцию

  function InitialiseIBase60(Const LibraryName  AnsiString)  integer;
  

  с правильным именем библиотеки (для этого может потребоваться использовать модуль ibase60dyn).

Улучшение производительности сортировок

Исторически сложилось, что когда выполняется внешняя сортировка, Firebird записывает в блоки сортировки как ключевые поля (перечислены в предложениях ORDER BY или GROUP BY), так и не ключевые (все остальные) поля запроса. После завершения сортировки (все) поля запроса вычитываются из блоков сортировки, находящихся в памяти или/и во (временных) файлах. Этот подход обычно считается более быстрым, поскольку записи читаются в порядке хранения вместо случайной выборки страниц данных, соответствующих отсортированным записям. Однако, если не ключевые поля велики (например, задействованы длинные VARCHAR), то они увеличивают размер блоков сортировки, провоцируя их вытеснение на диск и более интенсивный ввод-вывод для временных файлов. Firebird 4 предоставляет альтернативный подход, когда только ключевые поля и записи DBKEY хранятся внутри блоков сортировки, а не ключевые поля повторно выбираются со страниц данных после сортировки. Это улучшает производительность сортировки в случае длинных не ключевых полей.

Firebird 3.0.6.33328_0 скачать бесплатно — Бесплатные программы

Firebird – это система управления базами данных от независимых разработчиков. Программа поддерживается сообществом, а ее распространение осуществляется на основе лицензий IDPL и InterBase Public License.

Инструмент Firebird представляет собой сервер и в составе операционной системы работает как служба или отдельное приложение. Программа не имеет собственного графического интерфейса. Управление базами данных, таблицами, полями и выборкой осуществляется посредством создания SQL-запросов на соответствующем языке в командной строке (CLI), однако в сети в огромном множестве наличествуют визуальные редакторы, призванные упростить процесс администрирования и обращения к БД.

Преимущества Firebird

• Распространение продукта на бесплатной основе.
• Поддержка сервера сообществом разработчиков.
• Малый объем инсталлятора.
• Наличие множества сторонних модулей, призванных упростить работу с СУБД.

Недостатки Firebird

Отсутствие редактора кода и средств визуального администрирования баз данных; все управление осуществляется из командной строки.

Заключение

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

Установка Firebird

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

Изменения в последней версии (история изменений)

• Добавлена недостающая поддержка перемещения в сборках MacOS.
• Увеличена скорость разбора длинных запросов.
• Улучшена производительность при использовании плагина SRP.
• Добавлен лучший контроль транзакций в ISQL.

СкачатьFirebird

Повышенная точность хранения и вычисления для типов NUMERIC и DECIMAL

Типы NUMERIC и DECIMAL теперь могут хранить числа с точностью до 38 цифр. Для хранения чисел с точностью более 18 цифр Firebird 4.0 использует тип INT128 (128 битное целое). Кроме того улучшена обработка промежуточных результатов вычислений с типами данных NUMERIC и DECIMAL. В предыдущих версиях Firebird числа, внутренне представленные типом данных
BIGINT (то есть с точностью от 10 до 18 десятичных цифр), умножались/делились с использованием того же типа данных BIGINT для промежуточных вычислений, что могло вызвать ошибки переполнения из-за ограничения доступной точности. В Firebird 4 такие вычисления выполняются с использованием 128-битных целых чисел, что снижает вероятность неожиданных переполнений.

Тип INT128 также доступен для использования.

Запуск набора тестов базы данных FPC

Может быть полезно запускать среду тестирования базы данных FPC (в каталоге packages\fcl-db\tests), когда вы охотитесь за ошибками, пытаетесь выяснить, как что-то работает или улучшаете код базы данных.

Сообщаем платформе, какую базу данных использовать

Вам нужно будет отредактировать INI-файл db test framework, чтобы сообщить исполняемому файлу теста, что ему нужно использовать вашу встроенную базу данных.
См. для получения инструкций о том, как это сделать.

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

Если вы настроили среду для использования Firebird, вам нужно будет подготовить среду для тестирования.

Скопируйте библиотеки embedded Firebird и другие файлы для вашей архитектуры в тестовый каталог.

Затем создайте тестовую базу данных — вручную или программно/

Создание базы данных вручную

isql

create database 'test.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
exit;

Программное создание базы данных

Вы можете программно создать базу данных. В приведенном ниже примере создается локальная встроенная база данных, но ее можно легко изменить на удаленную базу данных (измените имя хоста и, если необходимо, путь):

program firebirdembeddedtest;
{$mode objfpc}{$H+}

uses 
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF} 
  Classes, SysUtils,
  sqldb, IBConnection {for Firebird};

const
  // Имя для базы данных. Обязательно укажите одно и то же имя в вашем database.ini
  DatabaseFile = 'test.fdb'; //Разумное имя для тестовой базы данных, верно?
var
  Fire TIBConnection;

begin
  //Подключение к базе данных Firebird
  //Следующая строка нужна для довольно старых версий FPC
  //UseEmbeddedFirebird: = true; 
  //Более новые версии сначала будут искать файл fbembed.dll в каталоге приложения автоматически 
  //Использование встроенного (и fbembed.dll) или обычного клиент/сервера (fbclient.dll) требует использования ibase60dyn
  Fire:=TIBConnection.Create(nil);
  try
    Fire.HostName := ''; //должен быть пустым для embedded Firebird; должен быть заполнен для клиент/сервера Firebird
    Fire.DatabaseName := DatabaseFile; //(путь и ) имя файла
    // Имя пользователя и пароль не имеют значения для аутентификации, но вы получаете авторизацию в базе данных    
    // на основе имени (и, возможно, роли), которое вы даете.
    Fire.Username := 'SYSDBA';
    Fire.Password := 'masterkey'; //пароль для SYSDBA по умолчанию
    Fire.Charset := 'UTF8'; //Отправка и получение строковых данных в кодировке UTF8
    Fire.Dialect := 3; //Никто больше не использует диалекты 1 или 2 (прим.перев.: это справедливо лишь в отношении новых проектов)
    Fire.Params.Add('PAGE_SIZE=16384'); //Мне нравится большой размер страницы (используется при создании базы данных). Полезно для больших индексов ⇒ больший возможный размер столбца
    // Выясните, есть ли база данных в каталоге приложения. 
    // Если нет, создайте его. Примечание: это может не сработать, если у вас недостаточно прав.

    // Если вы используете клиент / сервер, вам явно не нужна эта часть кода.
    if (not FileExists(DatabaseFile)) then
    begin
        writeln('Файл '+DatabaseFile+' не существует.');
        writeln('Создание базы данных Firebird embedded ...');
        // Создаем базу данных, поскольку она не существует
        try
          Fire.CreateDB; //Создаем файл базы данных.
        except
          on E Exception do
          begin
            writeln('ОШИБКА при создании базы данных. Возможно проблемы с загрузкой встроенной библиотеки:');
            writeln('- не все файлы присутствуют');
            writeln('- неправильная архитектура (например, 32 бит вместо 64 бит)');
            writeln('Exception message:');
            writeln(E.ClassName+'/'+E.Message);
          end;
        end;
        Fire.Close;
    end;
  finally
    Fire.Free;
  end; 
end.

Наконец, скомпилируйте и запустите dbtestframework.pas программы тестирования базы данных. Он должен генерировать вывод состояния на консоли и вывод XML в файлах с результатами теста, которые можно проанализировать и отправить разработчикам.