FastSqliteHelper v1.1

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

Установка

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

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

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

Лицензия

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

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

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

Name		Name	Last commit message	Last commit date
Latest commit History 16 Commits
Properties		Properties
sqlite-netFx46-binary-x64-2015-1.0.107.0		sqlite-netFx46-binary-x64-2015-1.0.107.0
.gitignore		.gitignore
FastSqliteHelper.OpenCover.Settings		FastSqliteHelper.OpenCover.Settings
FastSqliteHelper.cs		FastSqliteHelper.cs
FastSqliteHelper.csproj		FastSqliteHelper.csproj
FastSqliteHelper.csproj.sdsettings		FastSqliteHelper.csproj.sdsettings
FastSqliteHelper.sln		FastSqliteHelper.sln
LICENSE.md		LICENSE.md
README.md		README.md
test_project.xmlz		test_project.xmlz

License

lord-alfred/FastSqliteHelper

Folders and files

Latest commit

History

Repository files navigation