SaveToDB Framework для SQLite

SaveToDB Framework для SQLite

SaveToDB Framework for SQLite

SaveToDB Framework for SQLite содержит готовые решения задач разработки приложений с использованием SQLite и Microsoft Excel.

Фреймворк корректно устанавливается и удаляется.

Фреймворк можно быстро установить непосредственно из плагина SaveToDB и сгенерировать книгу для настройки фреймворка из Microsoft Excel.

См. Мастер установки SaveToDB Framework и Мастер создания книги настройки.

 

Далее можно настроить наиболее интересующие возможности:

 

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

SQLite практически полностью совместим с Microsoft SQL Server Compact, но еще поддерживает работу с представлениями. Это лучший выбор для создания персональных приложений.

Microsoft Excel позволяет работать с SQLite с использованием ODBC-драйвера (в отличие от Microsoft SQL Compact).

Плагин SaveToDB также позволяет работать с SQLite c использованием встроенного .NET-провайдера, без установки дополнительных драйверов.

Приложения для Microsoft Excel с использованием SQLite получают новые большие возможности:

  • Хранение данных в таблице позволяет использовать их на различных листах и в различных книгах.
  • За счет применения SQL-кода можно одни и те же данные видеть в различных разрезах.
  • За счет применения SQL-кода и http-запросов можно получать данные как из Списка запросов и контекстных меню, так и в обработчиках событий.
  • Имена таблиц и полей таблиц могут быть разными в Microsoft Excel и базе данных за счет возможностей перевода.

При персональном использовании данных SQLite отлично дополняет недостающие возможности Microsoft Excel.

Очень удобно использовать SQL-код для формирования формул DDE, что позволяет отказаться от использования макросов.

 

Для работы с SQLite не требуются дополнительные инструменты, т.к. плагин SaveToDB позволяет:

Установка и удаление

Варианты установки и удаления SaveToDB Framework

Возможны следующие варианты установки и удаления SaveToDB Framework:

  1. С использованием Мастера установки SaveToDB Framework из плагина SaveToDB.
  2. С использованием программы SaveToDB Framework Installer из состава SaveToDB SDK.
  3. С использованием исходного SQL-кода.
  4. С использованием командных файлов.

Первые два способа позволяют установить или удалить SaveToDB Framework во всех поддерживаемых серверах баз данных:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB и SQLite.

Описание шагов SaveToDB Framework Installer полностью совпадает с Мастером установки SaveToDB Framework.

Исходный SQL код находится в папке "SaveToDB Framework for SQL Server Compact" в файлах:

  • framework-install-ru.sql
  • framework-remove-ru.sql

Вариант с использованием командных файлов описан ниже.

Замечания к установке и удалению

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

Важно! При удалении объектов SaveToDB Framework удаляется вся информация о выполненных настройках из конфигурационных таблиц.

Если Вы планируете установить SaveToDB Framework повторно, сохраните предварительно данные конфигурационных таблиц.

Установка и удаление командными файлами

Код и утилиты фреймворка находится в папке "SaveToDB Framework for SQL Server Compact".

Установка

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите 2-install-savetodb-framework.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Вместо удаления пароля в строке подключения на шаге 3, можно зашифровать строку:

выберите опцию "Только пользователь" в программе gConnectionManager.

Но шифрование поддерживается только в платных версиях SQLite.

Удаление

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите framework-remove.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Вывод объектов базы данных

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите framework-list.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Список запросов

Назначение

С плагином SaveToDB пользователи могут переключать запросы к базе данных, используя список запросов на ленте.

Это удобно, т.к. в книге Microsoft Excel не нужно делать много листов, а можно динамически переключать запросы на одном листе. К тому же, при добавлении новых запросов в базу данных, пользователю достаточно обновить список запросов, и можно получать данные новых объектов.

Список запросов может содержать объекты базы данных, SQL-код, http-запросы и запросы к текстовым файлам.

Все эти возможности могут быть настроены в базе данных, и SaveToDB Framework предлагает готовую инфраструктуру.

См. Настройка списка запросов.

Таблица конфигурации списка запросов

Таблица QueryList содержит объекты списка запросов.

CREATE TABLE [QueryList] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_TYPE] [nvarchar](128) NOT NULL,
    [TABLE_CODE] [ntext],
    [INSERT_PROCEDURE] [ntext],
    [UPDATE_PROCEDURE] [ntext],
    [DELETE_PROCEDURE] [ntext],
    [PROCEDURE_TYPE] [nvarchar](128)
);

CREATE UNIQUE INDEX [QueryList_TableName]
    ON [QueryList] (
    [TABLE_NAME]
);

Таблица позволяет:

  • Указать SQL-код в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип CODE.
  • Указать http-запрос в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип HTTP.
  • Указать запрос к текстовому файлу в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип TEXT.
  • Указать таблицу сохранения данных, полученных из SQL-кода.
    Таблица должна быть указана во всех трех полях INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE.
  • Указать SQL код для сохранения изменений данных, полученных из SQL-кода.
    Должны быть заполнены все три поля INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE.
  • Указать таблицу или SQL код для слияния данных, полученных из любых источников, в том числе из веб или текстовых файлов.
    Должно быть заполнено одно поле INSERT_PROCEDURE.

См. Настройка списка запросов и Настройка сохранения данных.

Поле TABLE_NAME должно быть заполнено для всех объектов и быть уникальным.

При выборе таблицы в качестве списка запросов в Мастере подключения к данным будут отражены только объекты из таблицы.

Чтобы иметь возможность подключения к настроенным объектам таблицы и всем объектам базы данных используется представление ниже.

Представление конфигурации списка запросов

Представление viewQueryList выводит список объектов базы данных и объекты, настроенные в таблице QueryList.

CREATE VIEW viewQueryList
AS
SELECT
    NULL AS TABLE_SCHEMA
    , t.name AS TABLE_NAME
    , t.type AS TABLE_TYPE
    , NULL AS TABLE_CODE
    , NULL AS INSERT_PROCEDURE
    , NULL AS UPDATE_PROCEDURE
    , NULL AS DELETE_PROCEDURE
    , NULL AS PROCEDURE_TYPE
FROM
    sqlite_master t
WHERE
    t.type IN ('table', 'view')
    AND t.name NOT IN ('sqlite_master', 'sqlite_sequence',
        'ColumnTranslation', 'ObjectTranslation', 'EventHandlers', 'ParameterValues', 'TableFormats', 'QueryList',
        'viewColumnTranslation', 'viewObjectTranslation', 'viewEventHandlers', 'viewParameterValues', 'viewTableFormats', 'viewQueryList')
UNION
SELECT
    t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.TABLE_TYPE
    , t.TABLE_CODE
    , t.INSERT_PROCEDURE
    , t.UPDATE_PROCEDURE
    , t.DELETE_PROCEDURE
    , t.PROCEDURE_TYPE
FROM
    QueryList t
;

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

Обработка событий

Назначение

Используя плагин SaveToDB к Microsoft Excel можно обрабатывать события Microsoft Excel из SQLite заданным SQL-кодом или http-запросами:

  • Change
  • DoubleClick
  • SelectionChange

За счет этого можно изменять любые данные базы данных из Microsoft Excel, реализовывать уточняющие запросы, логику Master-Details и т.д.

Также можно настроить контекстное меню Microsoft Excel и меню "Действия" плагина для запуска:

  • SQL-кода,
  • http-запросов,
  • запросов к текстовым файлам,
  • макросов,
  • команд Windows Shell и CMD,
  • обновления справочников Excel из баз данных.

См. Настройка обработчиков событий.

Таблица конфигурации обработчиков событий

Таблица EventHandlers содержит конфигурацию обработчиков событий.

CREATE TABLE [EventHandlers] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128),
    [EVENT_NAME] [nvarchar](50) NOT NULL,
    [HANDLER_SCHEMA] [nvarchar](128),
    [HANDLER_NAME] [nvarchar](128) NOT NULL,
    [HANDLER_TYPE] [nvarchar](60),
    [HANDLER_CODE] [ntext],
    [TARGET_WORKSHEET] [nvarchar](255),
    [MENU_ORDER] integer,
    [EDIT_PARAMETERS] [bit]
);

CREATE UNIQUE INDEX [EventHandlers_TableName_ColumnName_EventName_HandlerName]
    ON [EventHandlers] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [EVENT_NAME],
    [HANDLER_NAME]
);

Описание полей конфигурации см. в разделе Настройка обработчиков событий.

Поле HANDLER_CODE может содержать SQL-код или текст http-запросов.

Поле TABLE_SCHEMA игнорируется в SQLite.

Перевод данных

Назначение

Плагин SaveToDB позволяет переводить описания объектов базы данных и полей объектов на бизнес-язык пользователей или любой естественный язык в самом Microsoft Excel после загрузки запроса.

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

См. Настройка перевода данных.

Таблица перевода объектов базы данных

Таблица ObjectTranslation содержит данные перевода объектов.

CREATE TABLE [ObjectTranslation] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128),
    [TRANSLATED_DESC] [nvarchar](255),
    [TRANSLATED_COMMENT] [nvarchar](4000)
);

CREATE UNIQUE INDEX [IX_ObjectTranslation_TableName_LanguageName]
    ON [ObjectTranslation] (
    [TABLE_NAME],
    [LANGUAGE_NAME]
);

Описание полей см. в разделе Настройка перевода данных.

Поле TABLE_SCHEMA игнорируется в SQLite.

Можно изменить разрядность полей TRANSLATED_*, если требуются описания большей длины.

Таблица перевода полей таблиц

Таблица ColumnTranslation содержит данные перевода полей объектов базы данных.

CREATE TABLE [ColumnTranslation] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128),
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128),
    [TRANSLATED_DESC] [nvarchar](1024)
);

CREATE UNIQUE INDEX [ColumnTranslation_TableName_ColumnName_Language]
    ON [ColumnTranslation] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [LANGUAGE_NAME]
);

Описание полей см. в разделе Настройка перевода данных.

Поле TABLE_SCHEMA игнорируется в SQLite.

Значения параметров

Назначение

Используя плагин SaveToDB пользователи могут работать в Microsoft Excel с запросами в виде SQL кода так же, как с таблицами.

Если SQL код имеет параметры, то плагин SaveToDB выводит параметры на ленту и пользователи могут изменять значения.

Для таблиц и представлений пользователи могут выбрать поля, которые будут показаны на ленте и использоваться при формировании запроса WHERE.

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

См. Настройка значений параметров.

Таблица конфигурации запросов списков значений полей

Таблица ParameterValues содержит конфигурацию запросов списков значений полей.

CREATE TABLE [ParameterValues] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [PARAMETER_NAME] [nvarchar](128) NOT NULL,
    [SELECT_SCHEMA] [nvarchar](128),
    [SELECT_NAME] [nvarchar](128) NOT NULL,
    [SELECT_TYPE] [nvarchar](128),
    [SELECT_CODE] [ntext]
);

CREATE UNIQUE INDEX [ParameterValues_SpecificName_ParameterName]
    ON [ParameterValues] (
    [TABLE_NAME],
    [PARAMETER_NAME]
);

SQL-код запроса значений добавляется в поле SELECT_CODE, в поле SELECT_TYPE должен быть указан тип CODE.

Поле TABLE_SCHEMA игнорируется в SQLite.

Форматы таблиц

Назначение

Данный компонент позволяет сохранять и восстанавливать форматирование таблиц Microsoft Excel для объектов БД в базе данных.

Форматирование таблицы загружается при первом подключении к объекту.
За счет этого пользователь получает не только данные, но и полное форматирование, включая:

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

Управление форматами производится из Мастера управления форматами таблиц.

См. также Настройка форматов таблиц.

Таблица форматов

Таблица TableFormats хранит форматирование таблиц Microsoft Excel для объектов базы данных.

CREATE TABLE [TableFormats] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_EXCEL_FORMAT_XML] [ntext]
);

CREATE UNIQUE INDEX [TableFormats_TableName]
    ON [TableFormats] (
    [TABLE_NAME]
);

Поле TABLE_SCHEMA игнорируется в SQLite.

Данные таблицы управляются плагином при сохранении из Мастера управления форматами таблиц с использованием встроенной процедуры.

История изменений

Версия 7.2 от 20.03.2017

[*] Новые типы обработчиков событий: ConvertFormulas и DoNotConvertFormulas.
[*] Добавлены списки проверки значений в таблицы QueryList, EventHandlers, ParameterValues и их представления.
[*] Поле EventHandlers.HANDLER_NAME может содержать значения NULL.

Версия 7.1 от 03.02.2017

[*] Добавлены типы RANGE и VALUES в поле SELECT_TYPE.
[*] Добавлены типы RANGE и VALUES в поле HANDLER_TYPE.

Версия 7.0 от 17.01.2017

[+]Первая версия.

Системные требования

Поддерживаемые версии SaveToDB:

  • SaveToDB 7.0 или выше

Поддерживаемые версии SQLite:

  • SQLite версии 3

Примеры для SQLite

SaveToDB SDK включает рабочие книги Microsoft Excel и готовые базы данных примеров для SQLite.

SaveToDB SDK также включает полный исходный код примеров и командные файлы для установки и удаления примеров.

В примерах продемонстрированы возможности плагина SaveToDB для построения приложений с использованием Microsoft Excel и баз данных SQLite.

Приложение реестра платежей

Приложение позволяет вести реестр платежей и строить отчеты о движении денежных средств.

Пример Northwind

Этот комплексный пример содержит набор книг для демонстрации самых разных возможностей

Пример DataTypes

Пример содержит таблицу для тестирования различных типов данных SQLite.

Установка и удаление примеров приложений

Установка примеров приложений SQLite

Для развертывания примера следует выполнить следующие шаги:

  1. Установить базу данных примера приложения.
  2. Изменить данные подключения в книге Microsoft Excel примера.

Установка базы данных примера

Код развертывания базы данных примеров SQLite находится в папке "source code" соответствующего примера.

Откройте папку и выполните следующие командные файлы:

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите 2-create-database.cmd, если необходимо.
  3. Запустите 3-install-savetodb-framework.cmd для установки SaveToDB Framework for SQLite.
  4. Запустите 4-install-application.cmd для установки приложения.
  5. Запустите 5-clear-credentials.cmd и удалите пароль подключения "setup".

На шаге 1 можно также создать базу данных SQLite в визуальном режиме, если необходимо.

Вместо удаления пароля в строке подключения на шаге 5, можно зашифровать строку:
выберите опцию "Только пользователь" в программе gConnectionManager.

Пример также можно установить вручную, используя файл application-install.sql.

Изменение параметров подключения в рабочей книге Microsoft Excel

В папке примеров содержатся предварительно настроенные рабочие книги Microsoft Excel для конечных пользователей и разработчиков приложений.

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

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

Для изменения параметров подключения следует:

  1. Активировать вкладку SaveToDB и запустить "Мастер изменения строк подключения".
  2. Ввести новые параметры сервера и базы данных и нажать кнопку "Далее".
  3. Отметить все таблицы и нажать кнопку "Готово".

Далее следует обновить данные всех таблиц рабочей книги Microsoft Excel.

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

Удаление примеров приложений SQLite

Для удаления откройте папку "source code" соответствующего примера и выполните следующие командные файлы:

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите application-remove.cmd.
  3. Запустите 5-clear-credentials.cmd и удалите пароль подключения "setup".

Вместо удаления пароля в строке подключения на шаге 3, можно зашифровать строку:
выберите опцию "Только пользователь" в программе gConnectionManager.

Пример также можно удалить вручную, используя файл application-remove.sql.

Утилита командной строки gsqlcmd

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

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

В составе SaveToDB SDK утилита находится в подкаталоге gsqlcmd и доступна из только подкаталогов SaveToDB SDK.

История изменений

Версия 7.2 от 20.03.2017

[*] Обновлен SaveToDB Framework 7.2.

Версия 7.0 от 17.01.2017

[+] Первая версия примеров для SQLite в составе SaveToDB SDK.
[+] Добавлен пример "Приложение реестра платежей и отчета о ДДС".
[+] Добавлен пример "Northwind".
[+] Добавлен пример "DataTypes".

Системные требования

Поддерживаемые версии SaveToDB:

  • SaveToDB 7.0 и выше

Поддерживаемые версии SaveToDB Framework for SQLite:

  • SaveToDB Framework 7.0 for SQLite

Поддерживаемые версии SQLite:

  • SQLite версии 3