Для каких целей? На момент возникновения идеи было еще не совсем понятно. Но предпосылки развития идеи были:

• создание дополнительного хранилища данных. В Б24 есть встроенные сущности для хранения данных, расширяемые за счет добавления пользовательских полей, например контакты, сделки, хранилища, и т.д. Но что делать если:
  

  • нужно спроектировать хранение произвольного набора данных. Например, мы разрабатываем приложение для расчета сметы строительных/ремонтных работ. Или для расчета KPI сотрудников. Или внедряем на портале кейс по продаже билетов. Как правило исходные данные в таких случаях хранятся в нескольких связанных таблицах, которые периодически пополняются и изменяются. Можно конечно использовать универсальные списки Б24. Но, во-первых, они включены начиная с тарифа "Профессиональный". А во-вторых, делать выборки по условию с помощью фильтров Б24 из списков не так уж и удобно.

  • мы хотим организовать доступ к данным из внешних источников, при этом ограничив доступ к порталу Б24. Например, предоставить клиентам доступ к просмотру на странице внешнего интернет-сайта стоимости ремонтных работ. Или наличию свободных мест при заказе билета. При этом актуальные данные должны храниться в одном месте - на портале Б24.

• увеличение скорости доступа к данным. Не секрет, что запрос на получение данных по REST API - не самая быстрая операция. А если важна скорость? Например, в предыдущей публикации рассматривался кейс идентификации лиц с вебкамеры по базе контактов Б24. В той публикации применен кейс хранения "цифрового отпечатка" лица в базе контактов Б24. Для обеспечения приемлемой скорости распознавания использовался "костыль" - вся база контактов предварительно загружаласть в память приложения средствами REST API. А сама загрузка 85 тыс. контактов занимала 30 минут. Что конечно не очень хорошо.

Итак, ищем возможность добавления на портал Б24 доступного и быстрого инструмента хранения произвольного набора данных.

Выбор инструмента

Самый очевидный вариант - база данных. Стремление реализовать решение без зависимостей от внешний сервисов свело список баз к единственному пункту - sqlite. Тем более что нашлась интересная библиотека sql.js, которая позволяет организовать полноценные sql-запросы к базе данных средствами javascript. На котором можно написать локальное приложение для Б24.

Особенность реализации sqlite - все хранится в одном файле. Возникает вопрос - где должен располагаться данный файл. Конечно же на диске портала Б24! Тем более что REST API предоставляет доступ к чтению и записи файлов на диск. Правда реализовано это через callback-и, что не удобно. Поэтому код получения пути к файлу БД пришлось обернуть в промис, примерно вот так:

let getPath2DB = (id_file) => {
  return new Promise(
    (resolve, reject) => {
        BX24.callMethod("disk.file.get",{id: id_file}, function(result){
            if (result.error()) {
                reject(result.error());
            }
            else {
                resolve(result.data()["DOWNLOAD_URL"]);
            }
       })
   }
  )
}

...

const path_to_dbfile = await getDB(FILE_ID)

Требования к функционалу

Итак, реализуем демо-пример локального приложения, которое должно уметь:

1. создавать новую базу данных и сохранять ее на диск Б24

2. добавлять таблицы БД,

3. добавлять и изменять записи в таблицах,

4. выполнять sql-запросы на получение данных из таблиц БД,

5. сохранять в БД внесенные изменения.

Начнем по порядку

Работа с файлом базы данных

В API Битрикс24 нет метода создающего/сохраняющего на диске новый файл. Но есть метод disk.file.uploadversion , загружающий на диск новую версию существующего файла. Поэтому нам нужно предварительно создать файл пустой базы данных и разместить его на диск Б24. Сделать это можно например с помощью online интерпретатора sql.js. В текстовом поле sql-запроса введем и выполним команду VACUUM;, после этого сохраним полученный файл и запишем его на диск Б24. Через внутреннюю ссылку можно узнать id файла, который нам понадобится.

Немного о том, как работать с БД. Файл базы загружается в память приложения

async function loadDB(path_to_db){
    let config = {
        locateFile: () => "https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.wasm",
        };
    const sqlPromise = initSqlJs(config);
    const dataPromise = fetch(path_to_db).then(res => res.arrayBuffer());
    const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
    db = new SQL.Database(new Uint8Array(buf));
    return db
}

const db = await loadDB(path_to_dbfile)

В процессе работы с БД, ее содержимое может быть изменено. Чтобы сохранить изменения базу данных нужно записать на диск Б24, например так:

BX24.callMethod(
    "disk.file.uploadversion",
    {
        id: file_id,
        fileContent: [file_name_on_disk, b64]
    },
    function (result){
        if (result.error())
            console.error(result.error());
        else
            console.dir(result.data());
    }
);

Обратите внимание на переменную b64. В памяти приложения БД хранится как Uint8Array а REST API Б24 сохраняет файлы на диск как последовательность base64. Для конвертации удобно использовать библиотеку base64.js, подключить ее можно по ссылке

let uint8_arr = await db.export()
let b64 = base64js.fromByteArray(uint8_arr)

Тестирование

В контексте заявленных выше предпосылок спроектируем и проверим работоспособность набора данных, удовлетворяющих следующим условиям:

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

2. количество данных должно быть достаточно большим

Если при заданных условиях тесты покажут удовлетворительные результаты - значит идея внедрения sqlite на портал Битрикс24 имеет право на жизнь.

Практический кейс

Задача - организовать на портале клиента работу по продаже авиабилетов. Реализация может быть такой. Воронка "Продажа билетов" со стадиями:

• заявка

• билет оплачен

• регистрация пройдена

• возврат (провал)

• клиент полетел (успех)

Логику продажи билета разделим на две части:

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

• представление, отображающее продажи билетов - данные пассажира, информация о перелете (стоимость билета, номер рейса, дата-время вылета, информация о регистрации и т.д.) хранятся сделках воронки и содержат в полях сделки информацию из базы данных.

В качестве теста возьмем набор данных отсюда. Создадим и наполним в нашей базе следующие таблицы:

aircrafts_data - модели самолетов - 9 записей
airports_data - аэропорты - 104 записи
boarding_passes - посадочные талоны - 579685 записей
bookings - бронирование билетов - 262788 записей
flights - рейсы - 33121 запись
seats - схема самолета (места) - 1338 записей
ticket_flights - перелеты - 924799 записей
tickets - билеты - 366733 записей

Конечно не бигдата, но приличный объем данных для проведения тестов. Итоговый размер файла БД - 102 МБ. Проверим, как будет работать с такой БД наше приложение на портале Б24.

1. При старте приложения, загрузка БД такого размера в память занимает около 2-х минут. В памяти приложение БД занимает столько же места, что и на диске - 102 Мб. Поскольку вся работа с БД выполняется в памяти приложения, отсюда следует и плюс и минус:

   • плюс: запросы на получение данных выполняются быстро

   • минус: для операций добавления/изменения данных нужно думать о целостности БД - внесенные в памяти приложения изменения нужно сохранить в файл на диске Б24. А при больших размерах данных это не быстрая история. Может возникнуть конфликт если несколько пользователей одновременно меняют данные в БД

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

select 
   flights.flight_no,
   flights.scheduled_departure,
   json_extract(departure.city, "$.ru") as departure,
   json_extract(arrival.city, "$.ru")  as arrival
from ticket_flights 
	join flights on ticket_flights.flight_id = flights.flight_id 
	join airports_data departure on departure.airport_code =  flights.departure_airport
	join airports_data arrival on arrival.airport_code =  flights.arrival_airport
where scheduled_departure > "2017-07-18" and scheduled_departure < "2017-07-19"
ORDER BY scheduled_departure

Т.е. необходимые данные можно получить всего одним sql-запросом. А это значительно упрощает разработку при добавлении дополнительного функционала в приложение. Кто знаком с основами языка SQL знают, что при правильно спроектированной бд во многих случаях можно получить данные любой сложности буквально одном sql-запросом.

Что касается скорости, то запросы выполняются очень быстро. Можно скачать демо приложение с Github и убедиться в этом самостоятельно. Например, у меня любые запросы на заявленном объеме данных не выполнялись более 1-2 сек. А если оптимизировать БД, добавив необходимые индексы и представления - будут выполняться еще быстрее. Правда за это придется расплачиваться увеличением размера файла БД.

3. Выполнение "сложных" запросов. Какие запросы на получение данных не умеет делать REST API Битрикс24? Правильно - запросы на выполнение группировочных операций, возвращающих сводную информацию по заданным признакам. Для этого необходимо сделать несколько запросов и обработать полученные данные в коде, прежде чем получить нужную информацию.

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

Если вся необходимая информация хранится в полях сделок, то алгоритм действий такой:

1. Получить все сделки для которых выполняются условия:

   • маршрут перелета совпадает с заданным

   • билет по данной сделке уже выкуплен

   • сделка находится в рабочей стадии (пассажир еще не улетел или билет не сдан)

2. Для каждой сделки, удовлетворяющей условиям 1 получить пару (номер рейса, дата вылета)

3. Сгруппировать все найденные сделки по совпадающим значениям пары

4. Посчитать количество сделок в каждой группе

5. Вернуть результат

А вот так описанный алгоритм может быть реализован через sql-запрос, если информация хранится в базе данных:

select 
   flights.flight_no,
   flights.scheduled_departure,
   json_extract(departure.city, "$.ru") as departure,
   json_extract(arrival.city, "$.ru")  as arrival,
   count(ticket_flights.ticket_no) as count 
from ticket_flights 
	join flights on ticket_flights.flight_id = flights.flight_id 
	join airports_data departure on departure.airport_code =  flights.departure_airport
	join airports_data arrival on arrival.airport_code =  flights.arrival_airport
where flights.status="Scheduled" AND departure LIKE "Ростов%" AND arrival = "Сочи"
GROUP by (flights.flight_id)
ORDER BY scheduled_departure

по-моему преимущества использования sqlite здесь очевидны. Тем более, помним, что REST API возвращает результат пакетами, что сказывается на скорости получения информации.

Демо приложение

Скачать демо можно с Github . Для экспериментов не обязательно устанавливать его на портале Б24. Можно запустить на компе на локальном сервере. В этом случае поле "ID файла" нужно оставить пустым. Если в одной папке с index.html лежит файл БД именем sql.db, то при нажатии кнопки "Загрузить БД" он будет загружен в приложение.

В текстовом поле можно вводить один любой валидный sql-запрос. Демо-приложение было написано для тестирования возможностей и скорости sql.js поэтому заморачиваться с сохранением изменений в бд я не стал. Как сохранить базу для приложения Б24 описано в публикации выше.

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

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

вывести список таблиц БД:

SELECT name FROM sqlite_master WHERE type='table';

узнать названия полей в таблице и их тип

pragma table_info(table_name);

Выводы

1. В качестве дополнительного инструмента хранения и обработки данных существует возможность добавлять на портал Б24 базу данных sqlite, на любом платном тарифе.

2. Возможности sqlite позволяют разрабатывать приложения, хранящие и обрабатывающие данные сложной структуры, чего не могут встроенные средства хранения, доступные на облачных версиях порталов Б24.

3. Поддержка языка sql упрощает разработку приложений и ускоряет их работу внутри портала Битрикс24

Идея внедрения на портал Б24 sqlite и разработки приложений на его основе имеет право на жизнь. По моему мнению.

Полный код, как обычно, можно скачать на Github.