Skip to content

Latest commit

 

History

History
328 lines (288 loc) · 28.8 KB

README.md

File metadata and controls

328 lines (288 loc) · 28.8 KB

FastSqliteHelper v1.1

Небольшая библиотека для упрощения работы с SQLite базой данных в ZennoPoster. Легковесный C# wrapper для SQLite. В первую очередь, данная библиотека поможет исключить множество повторяющегося кода (перехват ошибок, создание SQLiteCommand на каждый запрос, правильную передачу параметров через SQLiteCommand.Parameters). А также, облегчит разработку и упростит обновление и изменение ваших проектов по ходу их развития. Идея и часть реализации подсмотрена у DmitryAk + PHP PDO database framework Medoo. Текущий релиз не избавляет полностью от работы с System.Data.SQLite объектами + содержит несколько изъянов, которые могут допустить SQL-injection (или скорее порчу данных), но даже эта версия уже пригодна для использования (аккуратного).

Установка

  1. Скомпилировать самостоятельно или взять последнюю версию FastSqliteHelper.dll из релизов и положить в директорию: C:\Program Files (x86)\ZennoLab\RU\ZennoPoster Pro\[версия ZP]\Progs\ExternalAssemblies
  2. Скопировать System.Data.SQLite.dll и SQLite.Interop.dll из папки sqlite-netFx46-binary-x64-2015-1.0.107.0 в вышеприведенную директорию ExternalAssemblies
  3. В проекте выбрать Добавить действие -> Свой код -> Ссылки из GAC
  4. Зайти в появившийся внизу блок References (в Расширенном редакторе), нажать кнопку Добавить..., затем кнопку Обзор...
  5. В появившемся окне выбрать: C:\Program Files (x86)\ZennoLab\RU\ZennoPoster Pro\[версия ZP]\Progs\ExternalAssemblies\FastSqliteHelper.dll
  6. Повторить пункт 4 и в появившемся окне выбрать: C:\Program Files (x86)\ZennoLab\RU\ZennoPoster Pro\[версия ZP]\Progs\ExternalAssemblies\System.Data.SQLite.dll
  7. В проекте выбрать Добавить действие -> Свой код -> Директивы using и общий код
  8. Зайти в появившийся внизу блок OwnCodeUsings (в Расширенном редакторе) и в окне "Директивы Using" вставить:
using FastSqliteHelperLib;
using System.Data.SQLite;

После этого у вас в коде появится класс FastSqliteHelper с публичными методами, описанными ниже.

История версий

v1.1 [20.02.2018]

  • Добавлен метод GetLastQuery (последний отправленный запрос) + добавлено его описание в readme
  • В описании ошибок, которые генерируются из библиотеки, добавлен вывод последнего отправленного запроса
  • Доступ к методам GenerateErrorMessage и SendToLog изменен на приватный
  • Улучшена простановка connection string для подключения + в случае ошибки добавлено отображение connection string в описании ошибки
  • Исправлено неправильное описание подключения с использованием типа журнала WAL + исправлен тестовый шаблон

v1.0 [17.02.2018]

Первая версия библиотеки.

Советы по использованию

  • Примеры работы практически со всеми методами есть в шаблоне test_project.xmlz.
  • При использовании многопоточных шаблонов в ZennoPoster всегда добавляйте к строке дополнительных параметров connection string в методе FastSqliteHelper.Init включение типа журнала WAL с помощью:
Version=3;Journal Mode=WAL;

С ним скорее всего станет возможно писать и читать из одной таблицы без получения состояния "database is locked" в многопотоке. Но появятся дополнительные файлы (*-shm и *-wal), при этом вам нужно будет решить: оставить фиксацию измененных данных в автоматическом режиме или вручную делать так называемый "checkpoint" (везде советуют делать его в отдельном процессе, когда работа с базой в этот момент сведена к минимуму).

  • Всегда закрывайте соединение с базой данных через метод FastSqliteHelper.DeInit().
  • Если в результате выполнения какого-либо из методов в результате возвращается объект класса System.Data.SQLite.SQLiteDataReader ("читатель"), то необходимо всегда закрывать его через метод .Close, чтобы избежать ошибок в многопотоке. Пример корректного закрытия "читателя" при получения множества строк с помощью метода FastSqliteHelper.Select:
System.Data.SQLite.SQLiteDataReader reader = FastSqliteHelper.Select("table_name", new string[]{"id", "value"});
try {
    foreach(dynamic row in reader) {
        project.SendInfoToLog("Строка: " + row["id"] + " -> " + row["value"]);
    }
} finally {
    reader.Close();
}

Пример корректного закрытия "читателя" при получении единственного поля из единственной строки с помощью метода FastSqliteHelper.Select:

System.Data.SQLite.SQLiteDataReader reader = FastSqliteHelper.Select("table_name", "count(id) as count_all");
int count = 0;
try {
    if (reader.Read()) {
        count = Convert.ToInt32(reader["count_all"]);
    }
} finally {
    reader.Close();
}
  • Если в каком-то условии (condition) для выборки/обновления/удаления вам нужно сделать LIMIT, ORDER BY или что-то, что идет за WHERE - не забывайте, что в библиотеке это условие конкатенуется ("склеивается") с:
WHERE 1=1 and

Поэтому, если вам не нужно указывать критерий для выборки (WHERE) - лучше добавить в условие что-то вроде (пример для LIMIT):

2=2 LIMIT 0,10

Для того, чтобы избежать ошибки syntax error.

  • Для массового добавления строк в таблицу необходимо открывать транзакцию (пример реализации работы через транзакцию есть в методе FastSqliteHelper.Insert, но он может быть неудобен для ваших задач, т.к. предварительно нужно будет сделать список словарей с данными для вставки). Если не открывать транзакцию, то SQLite сделает это неявно на каждый Insert, поэтому у вас с очень большой долей вероятности будут "тормоза". Также, для ускорения процесса вставки (только при использовании в однопоточном шаблоне!) лучше отключить тип журнала и тип синхронизации при подключении к БД:
Journal Mode=OFF;Synchronous=0;
  • В SQLite при дефолтном "journal_mode" очень просто поймать состояние "database is locked", оно возникает в том случае, если первый поток читает данные из таблицы, а второй поток туда пишет. Чтобы предотвратить такого рода поведение - нужно заранее продумывать логику работы шаблона и обязательно использовать оператор блокировки:
lock(YourOwn.LockerObject) {
	// работа с БД через методы FastSqliteHelper
}

Плюс, как было написано в самом начале - лучше использовать тип журнала WAL, но не факт, что это даст 100% гарантии того, что шаблон не попадет в такое состояние.

  • Всегда явно указывайте столбцы для выборки, избегайте "*" для возвращения всех столбцов из таблицы. Это ускорит работу и поможет избежать ошибок, допущенных по невнимательности.
  • В методе подключения к БД есть один очень важный параметр: throw_exc_on_errors - "выдавать ли исключение в случае ошибки или просто по-тихому писать в лог ZP". Я настоятельно рекомендую выставлять его всегда в true, чтобы в случае ошибки - шаблон останавливал свое выполнение и прекращал работу, минуя тем самым другие ошибки, которые могут произойти далее.
  • К сожалению, в текущей реализации невозможно работать с 2 и более более SQLite базами данных одновременно. Это ограничение связано с тем, что класс FastSqliteHelper и его методы - статические. Можно сделать "хак" с monkey-patching объекта подключения и переопределять его "на лету", но это не тот подход, который хотелось бы предлагать для использования. Возможно, когда-то в дальнейшем эта оплошность будет устранена.
  • Для удобного просмотра/изменения базы данных я советую использовать бесплатную программу SQLiteStudio (в ней есть русификация).
  • Помните, что SQLite в первую очередь - это встраиваемая база данных в одном файле, поэтому ожидать от неё существенного прироста производительности - не стоит. Для таких целей лучше использовать MySQL или PostgreSQL. Но в целом, для небольших проектов/шаблонов, в которых не будет миллионов строк в базе - это очень хорошее решение, которое поможет избавиться от "списков" и "таблиц" в ZennoPoster. Советую к прочтению небольшой цикл статей о SQLite на хабре.

Описание методов FastSqliteHelper

Инициализация подключения к SQLite базе данных

bool Init(string database_path, IZennoPosterProjectModel zp_project, string add_to_connection_string="", bool throw_exc_on_errors=true, bool show_log_in_poster=false)
Параметр Описание
database_path полный путь к файлу базы данных
zp_project объект project из Zenno
add_to_connection_string строка дополнительных параметров, добавляемых к connection string
throw_exc_on_errors выдавать ли исключение в случае ошибки или просто по-тихому писать в лог ZP
show_log_in_poster показывать ли ошибку в логе ZennoPoster (в случае false - сообщение будет отображено только в ProjectMaker)
возвращаемое значение удалось ли подключиться к БД

Инициализация подключения к SQLite базе данных (дополнительные параметры connection string в виде словаря)

bool Init(string database_path, IZennoPosterProjectModel zp_project, Dictionary<string, string> add_to_connection_string, bool throw_exc_on_errors=true, bool show_log_in_poster=false)
Параметр Описание
database_path полный путь к файлу базы данных
zp_project объект project из Zenno
add_to_connection_string словарь "ключ" => "значение" дополнительных параметров, добавляемых к connection string
throw_exc_on_errors выдавать ли исключение в случае ошибки или просто по-тихому писать в лог ZP
show_log_in_poster показывать ли ошибку в логе ZennoPoster (в случае false - сообщение будет отображено только в ProjectMaker)
возвращаемое значение удалось ли подключиться к БД

Закрытие подключения к базе данных

void DeInit()

Установка параметра PRAGMA для текущего подключения к БД

void PragmaSet(string name_and_value)
Параметр Описание
name_and_value название и значение параметра PRAGMA

Чтение параметра PRAGMA из текущего подключения к БД

object PragmaGet(string name)
Параметр Описание
name название параметра PRAGMA
возвращаемое значение значение параметра

Получение данных из таблицы по условию (столбцы в виде строки)

SQLiteDataReader Select(string table, string columns, string condition="")
Параметр Описание
table таблица
columns строка столбцов, разделенных запятой
condition условие выборки
возвращаемое значение объект SQLiteDataReader с содержимым (не забывайте его закрыть!)

Получение данных из таблицы по условию (столбцы в виде массива строк)

SQLiteDataReader Select(string table, string[] columns, string condition="")
Параметр Описание
table таблица
columns массив строк столбцов
condition условие выборки
возвращаемое значение объект SQLiteDataReader с содержимым (не забывайте его закрыть!)

Получение данных из таблицы по условию (столбцы в виде списка)

SQLiteDataReader Select(string table, IEnumerable columns, string condition="")
Параметр Описание
table таблица
columns список строк столбцов
condition условие выборки
возвращаемое значение объект SQLiteDataReader с содержимым (не забывайте его закрыть!)

Добавление одной записи в таблицу

int Insert(string table, Dictionary<string, object> data)
Параметр Описание
table таблица
data данные в виде словаря "столбец" => "значение"
возвращаемое значение количество добавленных записей

Добавление множества записей в таблицу (используя транзакцию на все добавляемые данные)

int Insert(string table, List<Dictionary<string, object>> data_list)
Параметр Описание
table таблица
data_list список словарей с данными в виде "столбец" => "значение"
возвращаемое значение количество добавленных записей

Идентификатор последней добавленной записи

long LastInsertID()
Параметр Описание
возвращаемое значение идентификатор

Обновление значений в таблице по указанному условию

bool Update(string table, Dictionary<string, object> data, string condition="")
Параметр Описание
table таблица
data словарь "столбец" => "значение" для изменения
condition условие изменения
возвращаемое значение успешно ли произошло обновление

Обновление значения одного столбца в таблице по указанному условию

bool Update(string table, string field_name, object field_value, string condition="")
Параметр Описание
table таблица
field_name столбец
field_value новое значение
condition условие изменения
возвращаемое значение успешно ли произошло обновление

Удаление записей из таблицы по указанному условию в виде строки

int Delete(string table, string condition)
Параметр Описание
table таблица
condition условие для удаления
возвращаемое значение количество удаленных записей

Удаление записей из таблицы по указанному условию в виде словаря

int Delete(string table, Dictionary<string, object> conditions, string logical_operator="AND")
Параметр Описание
table таблица
conditions словарь "столбец" => "значение" для формирования условия выборки
logical_operator логический оператор (AND/OR) для формирования условия
возвращаемое значение количество удаленных записей

Любой запрос к БД, возвращающий количество затронутых строк

int Query(string sql)
Параметр Описание
sql текст запроса
возвращаемое значение количество затронутых строк

Любой запрос к БД, возвращающий объект SQLiteDataReader с содержимым

SQLiteDataReader QueryReader(string sql)
Параметр Описание
sql текст запроса
возвращаемое значение объект SQLiteDataReader с содержимым (не забывайте его закрыть!)

Любой запрос к БД, возвращающий первый столбец первой строки

object QueryScalar(string sql)
Параметр Описание
sql текст запроса
возвращаемое значение первый столбец первой строки (если вернулась хотя бы 1 строка) или null (в ином случае)

Получить последний отправленный запрос (включая запросы, в ходе обработки которых возникла ошибка)

string GetLastQuery()
Параметр Описание
возвращаемое значение последний отправленный запрос

TODO

Ниже описан план развития, задачи из которого по моему мнению нужно сделать в первую очередь, минуя добавление другого функционала, который может быть реализован непосредственно через работу с System.Data.SQLite. Если у вас есть непреодолимое желание помочь этому проекту в развитии - посмотрите ниже, что я хотел бы видеть в следующих версиях этой библиотеки, сделайте Fork репозитория и отправьте свой Pull Request с решением любой из задач :)

  • Написать тесты для каждого метода, чтобы в случае выпуска нового релиза можно было проверить не сломалось ли что-то где-то.
  • Реализовать тестовый проект для консольного приложения, чтобы не было нужды проверять все методы в ProjectMaker, а была возможность работы в VisualStudio/SharpDevelop.
  • Проверить и разобраться почему при установке какого-либо значения PRAGMA через строку подключения - чтение этого параметра через метод PragmaGet получает другой результат.
  • Избавиться от возвращения SQLiteDataReader в нескольких методах, чтобы можно было не думать о том, что его нужно всегда "закрывать".
  • Облегчить работу с транзакциями (избавиться от явного вызова Commit/Rollback), используя анонимные функции (делегаты в C#?). Пример правильной организации выполнения запросов внутри транзакции на PHP: https://medoo.in/api/action
  • Реализовать передачу условий в методы, чтобы они не "склеивались" с условием WHERE 1=1 and, чтобы была возможность передать LIMIT 0,10 без добавления конструкции вида 2=2.
  • Продумать и реализовать способ передачи условий в виде "столбец" => "значение" без ограничения на то, что все имена столбцов должны быть уникальны.
  • Добавить метод для экранирования значений, которые могут быть в дальнейшем переданы в виде plain-запроса в методы Query*. Пример похожего функционала в PHP PDO драйвере: http://php.net/manual/ru/pdo.quote.php
  • Добавить возможность работы с несколькими SQLite базами одновременно (убрать статический модификатор для класса, но это повлечет проблемы при передаче объекта через project.Context в ProjectMaker или сделать поле connection в виде словаря подключений, но нужно будет предварительно протестировать этот вариант в многопоточном режиме, чтоб не вышло, что данные будут пересекаться).
  • Переименовать методы Init -> Open, DeInit -> Close. Сомнительно и добавит обратную несовместимость, но читаемость кода повысится.
  • Реализовать получение/вставку/обновление/удаление данных через объекты, а не через словари. Плюс - не нужно будет делать некрасивые словари для работы с этими методами; минус - для каждой таблицы, с которой нужно будет работать - нужно будет создавать классы и вести работу через них.
  • Проверить и исправить все методы, которые подвержены sql-injection, чтобы в ходе работы не испортились данные.
  • В методах, которые не требуют работы с параметрами (SQLiteCommand.Parameters) - переписать отправку запроса/получение результата через внутренние методы Query*. Но это может привести к тому, что будет сложно разобрать в сообщении об ошибке - откуда именно упало исключение (поэтому данная задача под сомнением).
  • Добавить метод, который будет возвращать последний выполненный запрос (или даже лучше - отправленный, чтобы в случае перехвата ошибки внутри метода его можно было отобразить). Пример такого функционала в PHP фреймворке Medoo: https://medoo.in/api/last
  • Подумать над тем, что в последний отправленный запрос может быть списком (логом) всех запросов. Плюс решить стоит ли там заменять параметры на их значения.
  • Написать Contributing Guidelines, которые будут включать в себя: правильную настройку редактора, чтобы избежать табуляций в коде; использование String.Format/String.Concat; удаление trailing whitespace и другие полезные вещи.

Контрибьютеры

  • тут может быть твой ник и ссылка на тебя ;-)

Лицензия

CC BY-NC-SA 3.0 (Creative Commons — «Attribution-NonCommercial-ShareAlike» 3.0)

Лицензия «С указанием авторства — Некоммерческая — С сохранением условий»

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