Sqlite3

Сегодня Я хотел бы поделиться своей не большой разработкой, которая помогает мне уже более чем пол года: "Модуль для работы с sqlite3".

Концепция

Вместо написания SQL запросов мы будем передавать ключи, значения, названия таблиц, условия и callback'и, которые будут вызывать по завершению запросов(в каждый callback мы будем передавать ошибку и результат, если такой есть).

Представим модуль в виде класса.

Всего будет 4 метода:

1. getData() - для получения данных из таблицы.

2. insertData() - для добавления данных в таблицу.

3. updateData() - для обновления данных в таблице.

4. deleteData() - для удаления данных из таблицы.

Конечно же с помощью 4 методов приведенных выше мы не сможем исключить все виды запросов, но в моем случаи эти запросы самые частые.

Кодим

Для начало создадим сам класс, настроем экспорт и подключимся к БД и создадим метод, который будет добавлять кавычки у элемента, если тип элемента соответствует типу строки.

class DataBase {    /**     *      * @readonly     */    static sqlite3 = require('sqlite3').verbose();        /**    *     * @readonly    */   static database = new this.sqlite3.Database('./database/database.db');    static ToString(value) {        return typeof(value) === 'string' ? '\'' + value + '\'' : value;    }}module.exports = {    database: DataBase};

Для начало напишем метод, который будет возвращать данные из таблицы, не обязательно, но мы будем учитывать, кол-во строк, которые нам будут возвращаться(одна или несколько).

Начинаем собирать запрос с ключевого слова "SELECT", далее перебираем все столбцы, которые нам передали и добавляем их, но не забываем, что символ "*" не нужно "заковывать" в кавычки, т к этот символ говорит нам, что нужно получить все столбцы. Заканчиваем собор указанием таблицы и условия. Далее, исходя из кол-во строк, которые нам надо вернуть вызываем соответствующий метод.

class DataBase {    /**     *      * @readonly     */    static sqlite3 = require('sqlite3').verbose();        /**    *     * @readonly    */   static database = new this.sqlite3.Database('./database/database.db');        /**     *      * @param {String[]} keys      * @param {String} table      * @param {String} condition      * @param {Boolean} some      * @param {Function()} callback      */    static getData(keys, table, condition = '', some = true, callback = () => {}) {        let sql = 'SELECT ';        for (let i = 0; i < keys.length; i++) {            sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';            if (keys.length > i + 1)                sql += ', ';        }        sql += ' FROM `' + table + '` ' + condition;                if (some)            this.database.all(sql, (err, rows) => {                callback(err, rows);            });        else            this.database.get(sql, (err, row) => {                callback(err, row);            });    };    static ToString(value) {        return typeof(value) === 'string' ? '\'' + value + '\'' : value;    }}module.exports = {    database: DataBase};

Напишем метод отвечающий за обновление данных.

Начинаем с указания таблицы, установкой ключей и значений, а завершаем добавлением условия.

class DataBase {    /**     *      * @readonly     */    static sqlite3 = require('sqlite3').verbose();        /**    *     * @readonly    */   static database = new this.sqlite3.Database('./database/database.db');        /**     *      * @param {String[]} keys      * @param {String} table      * @param {String} condition      * @param {Boolean} some      * @param {Function()} callback      */    static getData(keys, table, condition = '', some = true, callback = () => {}) {        let sql = 'SELECT ';        for (let i = 0; i < keys.length; i++) {            sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';            if (keys.length > i + 1)                sql += ', ';        }        sql += ' FROM `' + table + '` ' + condition;                if (some)            this.database.all(sql, (err, rows) => {                callback(err, rows);            });        else            this.database.get(sql, (err, row) => {                callback(err, row);            });    };        /**     *      * @param {String[]} keys      * @param {Values[]} values      * @param {String} table      * @param {String} condition      * @param {Function()} callback      */    static updateData(keys, values, table, condition, callback = () => {}) {        let sql = 'UPDATE `' + table + '` SET ';        for (let i = 0; i < keys.length; i++) {            sql += '`' + keys[i] + '` = ' + this.ToString(values[i]);            if (keys.length > i + 1)                sql += ', ';        }        sql += ' ' + condition;                this.database.run(sql, (err) => {            callback(err);        });    }    static ToString(value) {        return typeof(value) === 'string' ? '\'' + value + '\'' : value;    }}module.exports = {    database: DataBase};

Остается совсем чуть-чуть, напишем метод для удаления данных(она максимально простой) и метод для добавления данных.

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

class DataBase {    /**     *      * @readonly     */    static sqlite3 = require('sqlite3').verbose();        /**    *     * @readonly    */   static database = new this.sqlite3.Database('./database/database.db');        /**     *      * @param {String[]} keys      * @param {String} table      * @param {String} condition      * @param {Boolean} some      * @param {Function()} callback      */    static getData(keys, table, condition = '', some = true, callback = () => {}) {        let sql = 'SELECT ';        for (let i = 0; i < keys.length; i++) {            sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';            if (keys.length > i + 1)                sql += ', ';        }        sql += ' FROM `' + table + '` ' + condition;                if (some)            this.database.all(sql, (err, rows) => {                callback(err, rows);            });        else            this.database.get(sql, (err, row) => {                callback(err, row);            });    };        /**     *      * @param {String[]} keys      * @param {Values[]} values      * @param {String} table      * @param {String} condition      * @param {Function()} callback      */    static updateData(keys, values, table, condition, callback = () => {}) {        let sql = 'UPDATE `' + table + '` SET ';        for (let i = 0; i < keys.length; i++) {            sql += '`' + keys[i] + '` = ' + this.ToString(values[i]);            if (keys.length > i + 1)                sql += ', ';        }        sql += ' ' + condition;                this.database.run(sql, (err) => {            callback(err);        });    }        /**     * @param {String[]} keys     * @param {String[]} values     * @param {String} table      * @param {Function()} callback      */    static insertData(keys, values, table, callback = () => {}) {        let sql = 'INSERT INTO `' + table + '` (';        for (let i = 0; i < keys.length; i++) {            sql += '`' + keys[i] + '`';            if (keys.length > i + 1)                sql += ', ';        }        sql += ') VALUES (';        for (let i = 0; i < values.length; i++) {            sql += this.ToString(values[i]);            if (values.length > i + 1)                sql += ', ';        }        sql += ')';        this.database.run(sql, (err) => {            callback(err);        });    };    /**     *      * @param {String} table      * @param {String} condition      * @param {Function()} callback      */    static deleteData(table, condition = '', callback = () => {}) {        this.database.run('DELETE FROM `' + table + '` ' + condition, (err) => {            callback(err);        });    }    static ToString(value) {        return typeof(value) === 'string' ? '\'' + value + '\'' : value;    }}module.exports = {    database: DataBase};

На этом все, спасибо за внимание!
Проект на GitHub

SQLite во многих случаях является удобным, незаменимым инструментом. Я уже не могу себе представить - как мы все жили без него. Тем не менее, есть некоторые неудобства при его использовании, связанные с тем, что это легкая встраиваемая СУБД.

Самое большое неудобство для меня, как Delphi-разработчика - отсутствие хранимых процедур. Я очень не люблю смешивать Delphi-код и SQL-скрипты. Это делает код намного менее читабильным, и затрудняет его поддержку. Следовательно, нужно как-то разнести код Delphi и тексты SQL-скриптов.

Предлагаю свой вариант решения проблемы

• Выносим весь SQL-код в отдельный тестовый файл ресурсов, подключенный к проекту.

• Запросы в SQL-файле разделяем маркерами начала с идентификаторами и маркерами конца. В моём случае синтаксис маркера начала - //SQL ИмяПроцедуры. Маркер конца - GO.

• Создаем класс - менеджер SQL-запросов. При загрузке приложения он читает SQL-файл из ресурсов и составляет из него список хранимых процедур с уникальными именами-идентификаторами.

• В процессе работы приложения мендежер извлекает текст SQL-запроса по его идентификатору для последующей его передачи на выполнение.

Главная идея - простота и легкость использования, подобная вызову хранимых процедур и удобство при создании и модификации SQL-запросов

Код юнита менеджера запросов:

unit uSqlList;interfaceuses System.Classes, Winapi.Windows, System.SysUtils,  System.Generics.Collections;type  TSqlList = class(TObjectDictionary<string, TStrings>)  const    SCRIPTS_RCNAME = 'SqlList';  private    function GetScripts(const AName: string): TStrings;    procedure FillList;    function GetItem(const AKey: string): string;  public    constructor Create;  public    property Sql[const Key: string]: string read GetItem; default;  end;var  SqlList: TSqlList;implementationfunction GetStringResource(const AName: string): string;var  LResource: TResourceStream;begin  LResource := TResourceStream.Create(hInstance, AName, RT_RCDATA);  with TStringList.Create do    try      LoadFromStream(LResource);      Result := Text;    finally      Free;      LResource.Free;    end;end;{ TScriptList }constructor TSqlList.Create;begin  inherited Create([doOwnsValues]);  FillList;end;procedure TSqlList.FillList;var  LScripts: TStrings;  I: Integer;  S, LKey: string;  LStarted: Boolean;  LSql: TStrings;begin  LScripts := GetScripts(SCRIPTS_RCNAME);  try    LStarted := False;    LSql := nil;    for I := 0 to LScripts.Count - 1 do    begin      S := LScripts[I];      if LStarted then      begin        if S = 'GO' then        begin          LStarted := False;          Continue;        end        else if not S.StartsWith('//') then          LSql.Add(S);      end      else      begin        LStarted := S.StartsWith('//SQL ');        if LStarted then        begin          LKey := S.Substring(6);          LSql := TStringList.Create;          Add(LKey, LSql);        end;        Continue;      end;    end;  finally    LScripts.Free;  end;end;function TSqlList.GetItem(const AKey: string): string;begin  Result := Items[AKey].Text;end;function TSqlList.GetScripts(const AName: string): TStrings;begin  Result := TStringList.Create;  try    Result.Text := GetStringResource(AName);  except    FreeAndNil(Result);    raise;  end;end;initializationSqlList := TSqlList.Create;finalizationFreeAndNil(SqlList);end.

Пример содержимого файла SQL-скриптов:

//SQL GetOrderSELECT * FROM Orders WHERE ID = :IDGO//SQL DeleteOpenedOrdersDELETE FROM Orders WHERE Closed = 0GO

Подключение файла скриптов к проекту:

{$R 'SqlList.res' '..\Common\DataBase\SqlList.rc'}

Использование с компонентом TFDConnection:

  Connection.ExecSQL(SqlList['GetOrder'], ['123']);

Собственно, это всё. Использую данное решение уже в нескольких проектах и мне оно кажется очень удобным. Буду благодарен за советы и замечания. Рад, если мой посто кому-то будет полезен!