Sqlite

Сегодня Я хотел бы поделиться своей не большой разработкой, которая помогает мне уже более чем пол года: "Модуль для работы с 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

Eсть таблица:

CREATE TABLE person(    id         uuid primary key,    name       text,    birth_date date)

и соотвтетствующий ей дата-класс:

data class Person(    val id: UUID,    val name: String,    val birthDate: LocalDate,)

Что если для того чтобы выполнить базовые CRUD операции:

• сохранить список Person-ов

• вычитать всё из таблицы

• удалить все записи в таблице

• найти по ID

• удалить по имени

будет достаточно создать интерфейс:

@SqliteRepositoryinterface PersonRepository : Repository<People> {    fun saveAll(people: List<Person>)    fun selectAll(): List<Person>    fun deleteAll()    fun selectBy(id: UUID): Person?    fun deleteBy(name: String)}

а имплеметнация будет сгенерирована автоматически.

Напоминает Spring Data? Но это не Spring, не Hibernate и даже не JPA.

TL;DR

• Kotlin-центричная библиотека (не фреймворк)

• Не ORM (не содержит JPA)

• Генерирует SQL и JDBC до этапа компиляции (Kotlin Annotation Precessing)

• Нет магии в рантайме

• Сгенерированный код отформатирован, можно дебажить, работает навигация, можноскопировать в проект и модифицировать

• Удобный DSL для работы с базой

• Есть 2 имплементации: под Postgres и Sqlite

Конфигурация

На данный момент есть 2 реализации этой библиотеки: для Postgresql и Sqlite. В данной статье примеры будут для Sqlite.

Для начала нужно сконфигурировать Gradle (да простят меня пользователи Maven):

build.gradle.kts

plugins {    kotlin("kapt") version "1.4.31" //(1)    kotlin("plugin.serialization") version "1.4.31"}dependencies {    implementation("com.github.mfarsikov:kotlite-core:0.5.0") //(2)    implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.0.0") //(3)    implementation("org.xerial:sqlite-jdbc:3.34.0") //(4)    kapt("com.github.mfarsikov:kotlite-kapt:0.5.0") //(5)    }kapt {    arguments {        arg("kotlite.db.qualifiedName", "my.pkg.DB") //(6)    }}

Репозиторий

import kotlite.annotations.SqliteRepository@SqliteRepositoryinterface PersonRepository

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

Команда ./gradlew kaptKotlin сгенерирует:

@Generatedinternal class PersonRepositoryImpl(    private val connection: Connection) : PersonRepository

Первый запрос

import kotlite.annotations.Queryimport kotlite.annotations.SqliteRepository@SqliteRepositoryinterface PersonRepository {    @Query("SELECT id, name, birth_date FROM person")    fun findPeople(): List<Person>}

Kotlite знает достаточно, чтобы сгенерировать код для этого запроса:

• Из возвращаемого типа List следует, что записей может быть от 0 до N

• Из возвращаемого типа Person следует, что каждый кортеж будет содержать три поля: id, name и birth_date.

• По конвенции, для поля в классе birthDate ожидается значение в кортеже birth_date

В результате сгенерируется метод:

public override fun findPeople(): List<Person> {    val query = "SELECT id, name, birth_date FROM person"    return connection.prepareStatement(query).use {        it.executeQuery().use {            val acc = mutableListOf<Person>()            while (it.next()) {                acc +=                    Person(                        birthDate = it.getObject("birth_date", LocalDate::class.java),                        id = it.getObject("id", java.util.UUID::class.java),                        name = it.getString("name"),                    )            }            acc        }    }}

Как выполнить этот запрос?

В конфигурации (build.gradle.kts) мы указывали, что нужно сгенерировать класс my.pkg.DB. Это главный объект, через который осуществляется доступ ко всем сгенерированным репозиториям. Для его создания нужен DataSource. Все объявленные нами репозитории доступны внутри транзакции:

main.kt

import my.pkg.DBimport org.sqlite.SQLiteDataSourcefun main() {    val datasource = SQLiteDataSource().apply {        url = "jdbc:sqlite:path/to/my/test.db"    }    val db = DB(datasource)    val people: List<Person> = db.transaction {        personRepository.findPeople()    }    println(people)}

Запрос с параметрами

@Query("SELECT id, name, birth_date FROM person WHERE name = :firstName")fun findPeopleBy(firstName: String): List<Person>

Параметры метода могут быть использованы в запросе. Перед именем параметра должно быть двоеточие.

public override fun findPeopleBy(firstName: String): List<Person> {    val query = "SELECT id, name, birth_date FROM person WHERE name = ?"    return connection.prepareStatement(query).use {        it.setString(1, firstName)        it.executeQuery().use {            val acc = mutableListOf<Person>()            while (it.next()) {                acc +=                    Person(                        birthDate = LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )            }            acc        }    }}

Возвращаемые типы

В зависимости от возвращаемого типа Kotlite генерирует различное поведение.

Список (List)

Cамый обычный тип. Полностью соответствует тому, что возвращает база от 0 до N элементов. Другие коллекции не поддерживаются.

Сущность (Entity)

На первый взгляд ничего особенного, но есть несколько нюансов:

• что если запрос не вернет ни одного значения

• что если запрос вернет больше одного значения

В обоих случаях сгенерированный код выбросит исключение. Для второго случая предусмотрена небольшая оптимизация в виде добавления LIMIT 2.

@Query("SELECT id, name, birth_date FROM person WHERE name = :name")fun findPersonBy(name: String): Person

public override fun findPersonBy(name: String): Person {    val query = """     |SELECT id, name, birth_date FROM person WHERE name = ?     |LIMIT 2     """.trimMargin()    return connection.prepareStatement(query).use {        it.setString(1, name)        it.executeQuery().use {            if (it.next()) {                val result =                    Person(                        birthDate = LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )                if (it.next()) {                    throw IllegalStateException("Query has returned more than one element")                }                result            } else {                throw NoSuchElementException()            }        }    }}

Для выбора первого значения можно пометить метод аннотацией kotlite.annotations.First

Скаляр

Возвращаемым типом может быть не только сущность, но и любое скалярное ("примитивное") значение. Например: Int, String, UUID LocalDateи т.п.

@Query("SELECT name FROM person WHERE id = :id")fun findPersonNameBy(id: UUID): String

Если запрос не вернул ни одного значения, или если вернул больше одного, то так-же как и для сущности будет выброшено исключение.

public override fun findPersonNameBy(id: UUID): String {    val query = """        |SELECT name FROM person WHERE id = ?        |LIMIT 2        """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, id)        it.executeQuery().use {            if (it.next()) {                val result =                    it.getString(1)                if (it.next()) {                    throw IllegalStateException("Query has returned more than one element")                }                result            } else {                throw NoSuchElementException()            }        }    }}

Для выбора первого значения можно пометить метод аннотацией kotlite.annotations.First

Nullable значения

Скаляр или сущность могут быть объявлены как Nullable. В таком случае вернется nullесли запрос не вернул ни одной записи.

@Query("SELECT name FROM person WHERE id = :id")fun findPersonNameBy(id: UUID): String?

public override fun findPersonNameBy(id: UUID): String? {    val query = """     |SELECT name FROM person WHERE id = ?     |LIMIT 2     """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, id)        it.executeQuery().use {            if (it.next()) {                val result =                    it.getString(1)                if (it.next()) {                    throw IllegalStateException("Query has returned more than one element")                }                result            } else {                null            }        }    }}

Постраничный вывод (Pagination)

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

import kotlite.aux.page.Pageimport kotlite.aux.page.Pageable@SqliteRepositoryinterface PersonRepository : Repository<Person> {    @Query("SELECT name FROM person")    fun selectAll(pageable: Pageable): Page<String>}

public override fun selectAll(pageable: Pageable): Page<String> {    val query = """        |SELECT name FROM person        |LIMIT ? OFFSET ?        """.trimMargin()    return connection.prepareStatement(query).use {        it.setInt(1, pageable.pageSize)        it.setInt(2, pageable.offset)        it.executeQuery().use {            val acc = mutableListOf<String>()            while (it.next()) {                acc +=                    it.getString(1)            }            Page(pageable, acc)        }    }}

Генерация SQL

Все что мы рассмотрели до этого была в основном генерация JDBC кода. SQL запросы нужно было писать разработчику самостоятельно. Но во многих случаях запросы выглядят тривиально, и могут быть сгенерированы автоматически.

Для этого нужно дать библиотеке немного информации о том, с какой сущностью мы работаем. Делается это через переменную типа, интерфейса kotlite.aux.Repository

import kotlite.annotations.SqliteRepositoryimport kotlite.aux.Repository@SqliteRepositoryinterface PersonRepository : Repository<Person> 

Теперь библиотека знает достаточно о нашей сущности, чтобы можно было сгенерировать SQL автоматически.

Известно название таблицы. По конвенции это имя клaсса, сконвертированное из UpperCamelCaseв snake_case. Название таблицы может быть явно указано в аннотацииkotlite.annotations.Table.

Также известно количество, названия и типы колонок таблицы. Названия колонок конвертируются из camelCaseв snake_case Альтернативно, название может быть указано в аннотации kotlite.annotations.Column

Что это нам дает?

Сохранение и обновление

Для любого метода, имя которого начинается на save(либо который помечен аннотацией kotlite.annotations.Save) будет сгенерирован INSERT . Такой метод должен принимать в качестве параметро либо саму сущность, либо список сущностей. Возвращаемый тип должен быть Unit

fun save(person: Person)

public override fun save(person: Person): Unit {    val query = """        |INSERT INTO person        |("birth_date", "id", "name")        |VALUES (?, ?, ?)        """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.birthDate)        it.setObject(2, person.id)        it.setString(3, person.name)        it.executeUpdate()    }}

Если сущность имеет первичный ключ (как минимум одно из полей помечено аннотацией kotlite.annotations.ID) будет сгенерирован INSERT/UPDATE

public override fun save(person: Person): Unit {    val query = """    |INSERT INTO person    |("birth_date", "id", "name")    |VALUES (?, ?, ?)    |ON CONFLICT (id) DO     |UPDATE SET "birth_date" = EXCLUDED."birth_date", "id" = EXCLUDED."id", "name" = EXCLUDED."name"    |""".trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.birthDate)        it.setObject(2, person.id)        it.setString(3, person.name)        it.executeUpdate()    }}

Это поведение можно переопределить аннотацией:

import kotlite.annotations.OnConflictFail@OnConflictFailfun save(person: Person)

Оптимистическая блокировка

Если числовое поле класса помечено аннотацией kotlite.annotations.Versionдля такой сущности запросы обновления и удаления будут содержать проверку текущей версии

public override fun save(person: Person): Unit {    val query = """        |INSERT INTO person        |("birth_date", "id", "name", "version")        |VALUES (?, ?, ?, ? + 1)        |ON CONFLICT (id) DO         |UPDATE SET "birth_date" = EXCLUDED."birth_date", "id" = EXCLUDED."id", "name" = EXCLUDED."name", "version" = EXCLUDED."version"        |WHERE person.version = EXCLUDED.version - 1        """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.birthDate)        it.setObject(2, person.id)        it.setString(3, person.name)        it.setInt(4, person.version)        val rows = it.executeUpdate()        if (rows != 1) {            throw OptimisticLockFailException()        }    }}public override fun delete(person: Person): Unit {    val query = """        |DELETE         |FROM person        |WHERE "id" = ? AND "version" = ?        """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.id)        it.setInt(2, person.version)        val rows = it.executeUpdate()        if (rows != 1) {            throw OptimisticLockFailException()        }    }}

Удаление

Для любого метода, имя которого начинается на delete (или который помечен аннотацией kotlite.annotations.Delete) будет сгенерирован DELETE

fun deleteAll()

public override fun deleteAll(): Unit {    val query = """    |DELETE     |FROM person    """.trimMargin()    return connection.prepareStatement(query).use {        it.executeUpdate()    }}

Такой метод может принимать сущность в качестве параметра:

fun delete(person: Person)

Удаление будет происходить по всем полям сущности

public override fun delete(person: Person): Unit {    val query = """        |DELETE         |FROM person        |WHERE "birth_date" = ? AND "id" = ? AND "name" = ?        """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.birthDate)        it.setObject(2, person.id)        it.setString(3, person.name)        it.executeUpdate()    }}

Если сущность имеет первичный ключ (хотя бы одно поле помечено kotlite.annotations.Id) удаление будет по первичному ключу:

public override fun delete(person: Person): Unit {    val query = """    |DELETE     |FROM person    |WHERE "id" = ?    """.trimMargin()    return connection.prepareStatement(query).use {        it.setObject(1, person.id)        it.executeUpdate()    }}

Кроме этого метод удаления может так-же принимать и другие параметры, см. разделы "Метод с параметрами" и "Сложные условия" ниже.

Метод без параметров

Любой метод, объявленный в репозитории, считается запросом типа SELECT(кроме методов, названия которых начинаются со слов saveи delete).

fun selectAll(): List<Person>

public override fun selectAll(): List<Person> {    val query = """     |SELECT "birth_date", "id", "name"     |FROM person     """.trimMargin()    return connection.prepareStatement(query).use {        it.executeQuery().use {            val acc = mutableListOf<Person>()            while (it.next()) {                acc +=                    Person(                        birthDate = LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )            }            acc        }    }}

Функции fun selectAll(): List<Person>и fun blaBlaBla(): List<Person> ничем не отличаются друг от друга и для них будет сгенерирован абсолютно одинаковый код.

Метод с параметрами

Все параметры метода должны совпадать по названию с полями класса. Они будут использованы как условия равенства во WHEREи объединены через AND.

fun selectBy(name: String, birthDate: LocalDate): Person?

public override fun selectBy(name: String, birthDate: LocalDate): Person? {    val query = """     |SELECT "birth_date", "id", "name"     |FROM person     |WHERE "name" = ? AND "birth_date" = ?     |LIMIT 2     """.trimMargin()    return connection.prepareStatement(query).use {        it.setString(1, name)        it.setObject(2, birthDate)        it.executeQuery().use {            if (it.next()) {                val result =                    Person(                        birthDate = java.time.LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )                if (it.next()) {                    throw IllegalStateException("Query has returned more than one element")                }                result            } else {                null            }        }    }}

Сложные условия

Если вместо стандартного равенства нужно использовать >, <=, != и т.д., или условия должны быть объединены с помощьюOR с расстановкой скобок, для этого подойдет аннотация kotlite.annotations.Where:

@Where("name = :name OR birth_date < :birthDate")fun selectBy(name: String, birthDate: LocalDate): Person?

Её содержимое будет подставлено в запрос почти без изменений.

public override fun selectBy(name: String, birthDate: LocalDate): Person? {    val query = """        |SELECT "birth_date", "id", "name"        |FROM person        |WHERE name = ? OR birth_date < ?        |LIMIT 2        """.trimMargin()    return connection.prepareStatement(query).use {        it.setString(1, name)        it.setObject(2, birthDate)        it.executeQuery().use {            if (it.next()) {                val result =                    Person(                        birthDate = java.time.LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )                if (it.next()) {                    throw IllegalStateException("Query has returned more than one element")                }                result            } else {                null            }        }    }}

Сортировка

Часто вместе с постраничным выводом необходимо задать порядок:

@OrderBy("name DESC, birth_date")fun selectAll(): List<Person>

public override fun selectAll(): List<Person> {    val query = """    |SELECT "birth_date", "id", "name"    |FROM person    |ORDER BY name DESC, birth_date    """.trimMargin()    return connection.prepareStatement(query).use {        it.executeQuery().use {            val acc = mutableListOf<Person>()            while (it.next()) {                acc +=                    Person(                        birthDate = LocalDate.parse(it.getString("birth_date")),                        id = UUID.fromString(it.getString("id")),                        name = it.getString("name"),                    )            }            acc        }    }}

Вложенные объекты

Вложенные объекты не могут быть представлены как связь один-к-одному. Поля вложенных объектов должны быть представлены колонками в этой же таблице. Т.е. быть @Embeddableв терминах JPA.

data class Person(    val name: Name,)data class Name(    val firstName: String,    val lastName: String,)

CREATE TABLE person(    first_name text,    last_name text)

Альтернативно вложенные объекты могут быть сериализованы в JSON. Предмет для добавления в ближайшие версии.

Вложенные коллекции

Вложенные коллекции не могут быть представлены как связь один-ко-многим.Вместо этого они автоматически сериализуются в JSON.

data class Person(    val habits: List<String>)@SqliteRepositoryinterface PersonRepository: Repository<Person> {    fun save(person: Person)    fun select(): List<Person>}

public override fun select(): List<Person> {    val query = """    |SELECT "habits"    |FROM person    """.trimMargin()    return connection.prepareStatement(query).use {        it.executeQuery().use {            val acc = mutableListOf<Person>()            while (it.next()) {                acc +=                    Person(                        habits = Json.decodeFromString(it.getString("habits")),                    )            }            acc        }    }}public override fun save(person: Person): Unit {    val query = """    |INSERT INTO person    |("habits")    |VALUES (?)    """.trimMargin()    return connection.prepareStatement(query).use {        it.setString(1, Json.encodeToString(person.habits))        it.executeUpdate()    }}

Особенности (сравнительно с JPA/Hibernate)

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

• Поскольку во главу угла поставлена простота, нет возможности создавать связи `один-к-одному`, `один-ко-многим` (и нет N+1 проблемы).

• Нет ленивых загрузок (и нет `SessionClosedException`).

• Нет встроенного механизма конвертеров типов (не переусложнен API, библиотека решает только одну задачу).

• Нет возможности сохранения иерархий наследования (в основном из-за личной неприязни автора к наследованию. Возможно будет добавлено в будущем).

• Не питает иллюзий относительно легкой миграции на другую базу данных.

На этом наши полномочия всё

Спасибо за уделенное внимание.

Sqlite

Posgresql

Для будущих студентов курса "Golang Developer. Professional" и всех интересующихся подготовили перевод интересного материала.

Также приглашаем на открытый вебинар по теме Go-каналы снаружи и внутри. Каналы в Go это одна из основ и особенностей языка. Понимание того, как они устроены ключ к правильному и оптимальному использованию. Присоединяйтесь, чтобы обсудить тему.

Введение

В основном я предпочитаю использовать реляционные базы данных (SQL), поскольку они предоставляют несколько возможностей, которые весьма полезны при работе с данными. SQLite - отличный выбор, так как база данных там представляет собой единый файл, что упрощает обмен данными. Несмотря на то, что это единый файл, SQLite может обрабатывать до 281 терабайта данных. SQLite также поставляется с клиентом командной строки sqlite3, который отлично подходит для быстрого прототипирования.

Примечание: В других базах данных есть транзакции, языки запросов и схемы. Однако базы данных на основе SQL имеют тенденцию быть более развитыми испытанными временем, а сам SQL стандартизирован.

Определения

Начнем с нескольких определений, которые помогут нам внести ясность.

SQLite: это встроенная база данных SQL. SQLite небольшая, быстрая и самая часто используемая база данных. Это также, безусловно, мой излюбленный способ обмена большими объемами данных между двумя программами.

Транзакции: вы вводите данные в базу данных SQL внутри транзакции. Это означает, что либо поступают сразу все данные, либо не поступают никакие. Транзакции на порядки упрощают код повторного выполнения операций в конвейерах данных.

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

SQL: Structured Query Language (язык структурированных запросов) это язык для выборки и изменения данных. Вам не нужно изобретать еще один способ выборки интересующих вас частей данных. SQL это устоявшийся формат, вокруг которого накоплено много знаний и инструментов.

Проект

Мы напишем HTTP-сервер на Go, который будет получать уведомления о сделках (trades) и хранить их в базе данных SQLite. Затем мы напишем программу на Python, которая будет обрабатывать эти данные.

В Go мы будем использовать github.com/mattn/go-sqlite3, который является оболочкой для библиотеки C SQLite.

Примечание: Поскольку go-sqlite использует cgo, изначальное время сборки будет больше обычного. Использование cgo означает, что результирующий исполняемый файл зависит от некоторых разделяемых библиотек из ОС, что немного усложняет распространение.

В Python мы будем использовать встроенный модуль sqlite3 и функцию read_sql Pandas, чтобы загружать данные. Исходный код из этой статьи доступен по ссылке.

Код на Go

Код, который я собираюсь вам показать, можно найти в файле trades.go.

Листинг 1: Структура Trade

37 // Trade - это сделка о покупке/продаже по символу ценной бумаги.38 type Trade struct {39     Time   time.Time40     Symbol string41     Price  float6442     IsBuy  bool43 }

В Листинге 1 показана структура данных Trade. У нее есть поле Time, отражающее время сделки, поле Symbol, хранящее биржевое сокращение (символ акции, например, AAPL), поле Price, содержащее цену, и логический флаг, который сообщает, это сделка покупки или продажи.

Листинг 2: Схема базы данных

24     schemaSQL = `25 CREATE TABLE IF NOT EXISTS trades (26     time TIMESTAMP,27     symbol VARCHAR(32),28     price FLOAT,29     buy BOOLEAN30 );31 32 CREATE INDEX IF NOT EXISTS trades_time ON trades(time);33 CREATE INDEX IF NOT EXISTS trades_symbol ON trades(symbol);34 `

В Листинге 2 объявляется схема базы данных, соответствующая структуре Trade. В строке 25 мы создаем таблицу под названием trades. В строках 26-29 мы определяем столбцы таблицы, которые соответствуют полям структуры Trade. В строках 32-33 мы создаем индексы для таблицы, чтобы обеспечить быстрое запрашивание по time и symbol.

Листинг 3: Внесение записи

16     insertSQL = `17 INSERT INTO trades (18     time, symbol, price, buy19 ) VALUES (20     ?, ?, ?, ?21 )22 `

Листинг 3 определяет SQL-запрос для внесения записи в базу данных. В строке 20 мы используем символ-заполнитель ? для параметров этого запроса. Никогда не используйте fmt.Sprintf для создания SQL-запроса вы рискуете создать SQL-инъекцию.

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

Листинг 4: DB

45 // DB - это база данных биржевых торгов.46 type DB struct {47     sql    *sql.DB48     stmt   *sql.Stmt49     buffer []Trade50 }

В Листинге 4 описана структура DB. В строке 47 мы устанавливаем соединение с базой данных. В строке 48 мы храним подготовленный (предварительно скомпилированный) оператор для вставки, а в строке 49 находится наш буфер ожидающих транзакций в памяти.

Листинг 5: NewDB

53 // NewDB создает Trades для управления торговлей акциями в базе данных SQLite. Этот API не является потокобезопасным.54 func NewDB(dbFile string) (*DB, error) {55     sqlDB, err := sql.Open("sqlite3", dbFile)56     if err != nil {57         return nil, err58     }59 60     if _, err = sqlDB.Exec(schemaSQL); err != nil {61         return nil, err62     }63 64     stmt, err := sqlDB.Prepare(insertSQL)65     if err != nil {66         return nil, err67     }68 69     db := DB{70         sql:    sqlDB,71         stmt:   stmt,72         buffer: make([]Trade, 0, 1024),73     }74     return &db, nil75 }

Листинг 5 демонстрирует создание готовой к использованию базы данных DB. В строке 55 мы подключаемся к базе данных с помощью драйвера sqlite3. В строке 60 мы применяем SQL-схему, чтобы создать таблицу trades, если она еще не существует. В строке 64 мы предварительно компилируем инструкцию InsertSQL. В строке 72 мы создаем внутренний буфер с длиной 0 и емкостью 1024.

Примечание: Чтобы не усложнять код, предоставляемый мной API DB не горутино-безопасен (в отличие от sql.DB). Если несколько горутин вызывают API одновременно, вы столкнетесь с состоянием гонки. Я оставлю это вам в качестве упражнения сделайте этот код горутино-безопасным.

Листинг 6: Add

77 // Add сохраняет сделку в буфер. Как только буфер заполняется, сделки вносятся в базу данных.79 func (db *DB) Add(trade Trade) error {80     if len(db.buffer) == cap(db.buffer) {81         return errors.New("trades buffer is full")82     }83 84     db.buffer = append(db.buffer, trade)85     if len(db.buffer) == cap(db.buffer) {86         if err := db.Flush(); err != nil {87             return fmt.Errorf("unable to flush trades: %w", err)88         }89     }90 91     return nil92 }

В Листинге 6 приведен метод Add. В строке 84 мы добавляем сделку (trade) в буфер в памяти. В строке 85 мы проверяем, заполнен ли буфер, и если да, то мы вызываем метод Flush в строке 86, который вносит записи из буфера в базу данных.

Листинг 7: Flush

94  // Flush вносит ждущие обработки сделки в базу данных.95  func (db *DB) Flush() error {96      tx, err := db.sql.Begin()97      if err != nil {98          return err99      }100 101     for _, trade := range db.buffer {102         _, err := tx.Stmt(db.stmt).Exec(trade.Time, trade.Symbol, trade.Price, trade.IsBuy)103         if err != nil {104             tx.Rollback()105             return err106         }107     }108 109     db.buffer = db.buffer[:0]110     return tx.Commit()111 }

В Листинге 7 приведен метод Flush. В строке 96 мы начинаем транзакцию. В строке 101 мы итерируем по внутреннему буферу, а в строке 102 вносим каждую сделку. Если при внесении произошла ошибка, мы выполняем rollback в строке 104. В строке 109 мы сбрасываем буфер сделок в памяти. И наконец, в строке 110 мы выполняем commit.

Листинг 8: Close

113 // Close вносит (посредством Flush) все сделки в базу данных и предотвращает любую торговлю в будущем.114 func (db *DB) Close() error {115     defer func() {116         db.stmt.Close()117         db.sql.Close()118     }()119 120     if err := db.Flush(); err != nil {121         return err122     }123 124     return nil125 }

В Листинге 8 приведен метод Close. В строке 120 мы вызываем Flush, чтобы внести все оставшиеся сделки в базу данных. В строках 116 и 117 мы закрываем (close) инструкцию и базу данных. Функции, создающие DB, должны иметь функцию defer db.Close(), чтобы убедиться, что связь с базой данных закончена.

Листинг 9: Импорты

5 // Ваши пакеты main и test требуют эти импорты, чтобы пакет sql был правильно инициализирован.6 // _ "github.com/mattn/go-sqlite3"7 8 import (9     "database/sql"10     "errors"11     "fmt"12     "time"13 )

В Листинге 9 приведен импорт для файла. В строке 5 мы импортируем database/sql, которая определяет API для работы с базами данных SQL. database/sql не содержит какого-либо конкретного драйвера базы данных.

Как говорится в комментарии к оператору импорта, чтобы использовать пакет trades, вам необходимо импортировать пакет, который реализует драйвер базы данных sqlite3 (например, github.com/mattn/go-sqlite3). Поскольку вы импортируете пакет, реализующий драйвер только для небольшого изменения регистрации протокола sqlite3, мы используем перед импортом, сообщая компилятору Go, что то, что мы не используем этот пакет в коде это нормально.

Листинг 10: Пример использования

Код этих примеров можно найти в файле tradestest.go.

66 func ExampleDB() {67     dbFile := "/tmp/db-test" + time.Now().Format(time.RFC3339)68     db, err := trades.NewDB(dbFile)69     if err != nil {70         fmt.Println("ERROR: create -", err)71         return72     }73     defer db.Close()74 75     const count = 10_00076     for i := 0; i < count; i++ {77         trade := trades.Trade{78             Time:   time.Now(),79             Symbol: "AAPL",80             Price:  rand.Float64() * 200,81             IsBuy:  i%2 == 0,82         }83         if err := db.Add(trade); err != nil {84             fmt.Println("ERROR: insert - ", err)85             return86         }87     }88 89     fmt.Printf("inserted %d records\n", count)90     // Вывод:91     // inserted 10000 records92 }

В Листинге 10 показан пример использования (в виде тестируемого примера). В строке 67 мы создаем новую базу данных, а в строке 73 мы закрываем ее с помощью оператора defer. В строке 76 мы запускаем цикл для вставки сделок, а в строке 83 мы собственно и вставляем сделку в базу данных.

Код на Python

Примеры кода на Python можно найти в файле analysis_trades.py.

Листинг 11: Импорты

02 import sqlite303 from contextlib import closing04 from datetime import datetime05 06 import pandas as pd

В Листинге 11 показаны библиотеки, которые мы используем в нашем Python-коде. В строке 2 мы импортируем встроенный модуль sqlite3, а в строке 6 библиотеку pandas.

Листинг 12: Select SQL

08 select_sql = """09 SELECT * FROM trades10 WHERE time >= ? AND time <= ?11 """

В Листинге 12 показан SQL-запрос для получения данных. В строке 10 мы выбираем все столбцы из таблицы trades. В строке 10 мы добавляем элемент WHERE для выбора временного диапазона. Как и в Go-коде мы используем заполнители аргументов ? и не пишем SQL-запросы вручную.

Листинг 13: Загрузка сделок

14 def load_trades(db_file, start_time, end_time):15     """Загружаем сделки из db_file за заданный временной диапазон."""16     conn = sqlite3.connect(db_file)17     with closing(conn) as db:18         df = pd.read_sql(select_sql, db, params=(start_time, end_time))19 20     # Мы не можем использовать здесь detect_types=sqlite3.PARSE_DECLTYPES, поскольку Go вставляет часовой пояс, а sqlite3 Python не умеет обрабатывать его.22     # Смотрите https://bugs.python.org/issue29099# См. Https://bugs.python.org/issue2909923     df["time"] = pd.to_datetime(df["time"])24     return df

В Листинге 13 показан код для загрузки сделок из заданного временного диапазона. В строке 16 мы подключаемся к базе данных. В строке 17 мы используем менеджер контекста, что-то вроде defer в Go, чтобы убедиться, что база данных закрыта. В строке 18 мы используем функцию pandas read_sql для загрузки данных из SQL-запроса в DataFrame. Python имеет API для подключения к базам данных (например, database/sql), а Pandas может использовать любой совместимый драйвер. В строке 23 мы конвертируем столбец time в Timestamp pandas. Это особенность SQLite, в котором нет встроенной поддержки TIMESTAMP типов.

Листинг 14: Средняя цена

27 def average_price(df):28     """Возвращает среднюю цену в df, сгруппированную по (stock, buy)"""29     return df.groupby(["symbol", "buy"], as_index=False)["price"].mean()

В Листинге 14 показано, как вычислить среднюю цену на symbol и buy. В строке 29 мы используем DataFrame groupby для группировки по symbol и buy. Мы используем as_index=False, чтобы получить symbol и buy в виде столбцов в итоговом фрейме данных. Затем мы берем столбец price и вычисляем среднее значение для каждой группы.

Листинг 15: Вывод

symbol,buy,priceAAPL,0,250.82925665004535AAPL,1,248.28277375538832GOOG,0,250.11537993385295GOOG,1,252.4726772487683MSFT,0,250.9214212695317MSFT,1,248.60187022941685NVDA,0,250.3844763417279NVDA,1,249.3578146208962

В Листинге 15 показан результат выполнения кода Python на тестовых данных.

Заключение

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

Я максимально упростил код, чтобы показать наиболее интересные моменты. Есть несколько мест, где вы можете попробовать улучшить его:

• Добавить код повторного выполнения в Flush

• Выполнить дополнительную проверку ошибок в Close

• Сделать DB горутино-безопасным

• Реализовать больше аналитики в Python-части

Я также рекомендую вам изучить SQL, так как очень много данных хранятся в базах данных SQL. Вам не нужно быть экспертом, знание базовых вариантов select уже даст вам новый мощный инструмент в вашем инструментарии анализа данных.

Если вы не хотите напрямую работать с SQL, есть несколько альтернатив, в мире Go есть такие пакеты, как sqlx, gorm, а в мире Python есть SQLAlchemy, который может использовать Pandas.

Приятного вам кодинга. Дайте мне знать, как вы используете SQL в целом и SQLite в частности.

Узнать подробнее о курсе "Golang Developer. Professional".

Смотреть открытый вебинар по теме Go-каналы снаружи и внутри.

Добрый день, уважаемые читатели! Материал адресован всем специалистам, работающим с данными, которые решили написать первое веб-приложение. В данной публикации я не буду выкладывать листинги кода. На просторах Интернета есть масса практических примеров сборки сервисов, написанных на разных фреймворках. Но вот теоретических статей о логике процесса, архитектуре решения, а, главное, трудностях, с которыми впервые столкнется специалист, крайне мало. Я решил заполнить эту нишу и описать свой личный опыт, который кому-то может быть полезен.

Специально для статьи я подготовил два идентичных примера на Flask и Dash и выложил их на GitHub. В них иллюстрируется расчет и вывод показателей юнит-экономики абстрактного IT-маркета, который называется Хабр (а почему бы и нет, ведь сейчас все компании начали заниматься электронной коммерцией:).

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

ОПП: не умеешь не берись! Когда речь заходит об ОПП, мне почему-то автоматически вспоминается Django с его классами. Но если посмотреть работы начинающих data scientist-ов или аналитиков данных, то мы увидим совсем другую картину. Классы применяются ради самих классов. В данную структуру языка просто сливается весь код. За что отвечает этот монстр? За все! Как искать ошибки или переписывать код, не понятно. Лично у меня такое мнение на этот счет. Если не знаешь когда, как и почему следует применять ОПП, то лучше для небольших разработок использовать процедурно-функциональный стиль.

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

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

Муки выбора или о разных фреймворках замолвим слово. Сочетание каких технологий можно использовать для создания собственного сервиса? Приведу несколько вариантов, которые сразу приходят на ум. Заранее прошу прощения, что обойду вниманием PHP, Ruby, C#:

• Flask статичные страницы с шаблонами HTML+CSS

• Django статичные страницы с шаблонами HTML+CSS

• Flask Rest API/FastAPI/Django Rest Framework динамические страницы HTML+CSS+фреймворк Javascript (Vue, React, Angular)

• Dash (по сути работает Flask) Dask (по сути работает React)

Как бы рассуждал я, если передо мной стоял выбор.

• Нужно выводить таблицы, графики, интерактивные элементы здесь и сейчас Dash

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

• Нужно выводить разноплановую информацию, нужна интерактивность. Есть много времени, есть ресурсы, плюс поддержка верстальщика и фронтенд-программиста FastAPI Vue.js

Теперь приведу скриншоты работ на Flask и Dash и сделаю несколько замечаний касательно данных платформ.

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

В проекте Flask файл, который отвечает за вывод результатов, страницы html и фреймворк css это разные сущности. Документация по Bootstrap4 довольно качественная, но так как у меня нет навыков верстки, мне не удалось добиться корректного вывода всех сводных таблиц.

В проекте Dash за все операции отвечает единый файл, так как я выбрал вариант с хранением таблицы стилей в app.py. Если дашборд простой, то читаемость кода будет приемлемой. Но с ростом проекта с этим могут возникнуть трудности. Стили можно переместить в папку asset. Можно ли как-то еще раздробить основной файл я не знаю. Сразу из коробки имеется хорошая поддержка всех аналитических компонентов, включая таблицы, но нужно время для ознакомления со спецификой разработки.

Архитектура всему голова. Заранее продумывайте архитектуру своего приложения. Все файлы должны быть разнесены по модулям согласно их функционалу. При этом нужно стремиться к тому, чтобы, если изъять из сервиса часть модулей, остальная часть программы сохранила работоспособность. Компоненты должны спокойно интегрироваться в другой сервис с минимальными доработками. Переходим к моим ошибкам. Скрипты для запуска etl-процессов и расчета показателей лежат рядом с главным файлом проекта.

Многофункциональности здесь не место. В продолжение предыдущего пункта. Ваше приложение должно делать хорошо что-то одно. Мой сервис выполняет etl-команды, формирует БД, а затем наполняет ее записями и отвечает за вывод дашборда. Это три разных процесса, которые с большой долей вероятности в реальности будут разнесены во времени. И конечно, нужно убирать файл с данными из приложения, так как он только занимает место.

Что SQL-запросом вытянешь, то и считать будешь. Максимально перенесите расчетную нагрузку на сторону БД. При этом следует учитывать разности в диалектах sql. Старайтесь писать запросы максимально универсальными. Мои ошибки. База данных в качестве физического файла присутствует в проекте. В запросах имеются уникальные конструкции диалекта SQLite.

Pandas мне друг, но производительность дороже. Мне пришлось применить данную библиотеку, так требовалась именно сводная таблица, а получить ее на стороне БД проблематично. В большинстве случаев лучше обойтись только нативным Python.

Не все то золото, что YAML-файл. Идею применения yaml файла для хранения констант проекта я почерпнул из одного видео-ролика практикующего data scientist-а на Youtube. Что в этом плохого или хорошего я не знаю. Решать только вам.

А не замахнуться ли нам на Docker. Небольшое лирическое отступление. Чего мне реально не хватает в Windows, так это Docker. В Windows 10 эту проблему решили, а вот в предыдущих версиях пользователям остается лишь устанавливать Docker Toolbox. Но в настоящее время разработка и поддержка данного продукта завершена, хотя архивный файл можно по-прежнему скачать на официальном аккаунте Docker на GitHub. Лично у меня по некоторым причинам установлен Windows 8.1, поэтому я задался вопросом, как еще можно заполучить в распоряжение эту программу. Установку второй операционной системы я отмел сразу, а вот вариант с виртуальной машиной меня заинтересовал. Для экономии ресурсов я выбрал Debian 10. Если выделить под нужды ВМ один процессор и три гигабайта оперативной памяти, то вполне можно тестировать свои идеи. Но стоит оговориться, что если захочется собрать и запустить контейнер с Apache Airflow, то указанных вычислительных мощностей будет недостаточно.

Теперь можно возвращаться к нашим приложениям. Как сбилдить и запустить контейнер я рассказывать не буду, так как данную информацию легко можно нагуглить в Интернете. Есть лишь пара моментов, на которых я заострю внимание. В процессе сборки будет выдаваться предупреждение о необходимости создания виртуального окружения внутри контейнера. Я решил пренебречь им, так как контейнер и так изолирован от рабочей среды Linux. И еще момент. После того, как приложение на Dash было упаковано в docker-контейнер, перестал отображаться логотип Хабра. Явной причины этого я быстро не нашел, а время, отведенное на эксперимент, было исчерпано.

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

И вот вам конкретный пример. Я построил контейнер на Dash, а дашборд в браузере не отображается. В локальном варианте все было нормально. Оказалось, я просто забыл поменять в файле app.py хост с 127.0.0.1, на 0.0.0.0.

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

На этом все. Всем здоровья, удачи и профессиональных успехов!

Добрый день, уважаемые читатели! Материал носит теоретический характер и адресован исключительно начинающим аналитикам, которые впервые столкнулись с BI-аналитикой.

Что традиционно понимается под этим понятием? Если говорить простым языком, то это комплексная система (как и, например, бюджетирование) по сбору, обработке и анализу данных, представляющая конечные результаты в виде графиков, диаграмм, таблиц.

Это требует слаженной работы сразу нескольких специалистов. Дата-инженер отвечает за хранилища и ETL/ELT-процессы, аналитик данных помогает в заполнении базы данных, аналитик BI разрабатывает управленческие панели, бизнес-аналитик упрощает коммуникации с заказчиками отчетов. Но такой вариант возможен, только если фирма готова оплачивать работу команды. В большинстве случаев небольшие компании для минимизации затрат делают ставку на одного человека, который зачастую вообще не обладает широким кругозором в области BI, а имеет лишь шапочное знакомство с платформой для отчетов.

В таком случае происходит следующее: сбор, обработка и анализ данных происходит силами единственного инструмента самой BI-платформой. При этом данные предварительно никак не очищаются, не проходят компоновки. Забор информации идет из первичных источников без участия промежуточного хранилища. Результаты такого подхода можно легко лицезреть на тематических форумах. Если постараться обобщить все вопросы касательно BI-инструментов, то в топ-3 попадут, наверное, следующие: как загрузить в систему плохо структурированные данные, как по ним рассчитать требуемые метрики, что делать, если отчет работает очень медленно. Что удивительно, на этих форумах вы практически не найдете обсуждений ETL-инструментов, описания опыта применения хранилищ данных, лучших практик программирования и запросов SQL. Более того, я неоднократно сталкивался с тем, что опытные BI-аналитики не очень лестно отзывались о применении R/Python/Scala, мотивируя это тем, что все проблемы можно решить только силами BI-платформы. Вместе с тем всем понятно, что грамотный дата инжиниринг позволяет закрывать массу проблем при построении BI-отчетности.

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

Data BI Самый простой вариант. Именно с него начинается прототипирование управленческих панелей. В роли источника данных часто выступает отдельный (-ые) статичный файл (csv, txt, xlsx и т. д.).

Плюсы. Самый быстрый способ построения отчетности. Идеально подходит, для ситуационной аналитики или когда результат нужен был еще вчера. Не требует применения вспомогательных инструментов, следовательно, не нужно тратить ресурсы на их поддержание. Аналитик BI не обязан иметь компетенции в области дата инжиниринга или программирования.

Минусы. Далеко не изо всех источников можно забрать информацию напрямую (пример, прикладные решения на платформе 1С). Если массивы плохо структурированы, то это потребует много дополнительных шагов по их обработке. Качество данных никак не проверяется (проблема дубликатов, пустых строк, некорректного написания значений и т. д.). При большом количестве строк заметно замедляется работа самой BI-платформы, вплоть до полной невозможности перестраивать графики и диаграммы. Нет возможности составить расписание на обновление исходников.

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

Плюсы. Есть возможность агрегировать разрозненные, однотипные файлы. Нагрузку по хранению информации теперь несет хранилище. Есть возможность задействовать всю мощь языка запросов SQL (или его диалекта), чтобы отфильтровать или агрегировать сырые строки перед их передачей в BI-инструмент. Уменьшается размер файла с управленческими панелями.

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

Data ETL DB BI Частичная автоматизация. В качестве ETL-инструмента может выступать как программный продукт с графическим интерфейсом, так и код написанный на R/Python/Scala и т. д. Все данные проходят предварительный предпроцессинг. Структура наполняемых таблиц прописывается заранее.

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

Минусы. Аналитик должен уверенно владеть ETL-инструментом и языком запросов SQL. Процесс разработки и тестирования скриптов требует времени. Если источников информации много, то затрудняется синхронизация получения информации.

Для иллюстрации этого варианта я решил написать простейшие скрипты. В рамках игрушечного примера я использую SQLite. Это позволит прикрепить базу данных к публикации, чтобы каждый желающий мог попрактиковаться в написании скриптов (архив). Датасет для разбора это E-Commerce Data с сайта Kaggle.

# импорт библиотекimport pandas as pd# опции отображенияpd.set_option('display.max_columns', 10)pd.set_option('display.expand_frame_repr', False)path_dataset = 'dataset/ecommerce_data.csv'# Предварительная обработка датасетаdef func_main(path_dataset: str):    # Считываем датасет    df = pd.read_csv(path_dataset, sep=',')    # Приводим названия столбцов датасета к нижнему регистру    list_col = list(map(str.lower, df.columns))    df.columns = list_col    # Избавляемся от времени и трансформируем строку-дату в правильный формат    df['invoicedate'] = df['invoicedate'].apply(lambda x: x.split(' ')[0])    df['invoicedate'] = pd.to_datetime(df['invoicedate'], format='%m/%d/%Y')    # Рассчитываем сумму покупки по каждому товару    df['amount'] = df['quantity'] * df['unitprice']    # Удаляем ненужные для дальнейшего анализа столбцы    df_result = df.drop(['invoiceno', 'quantity', 'unitprice', 'customerid'], axis=1)    # Задаем порядок вывода столбцов для визуального контроля результата    df_result = df_result[['invoicedate', 'country', 'stockcode', 'description', 'amount']]    return df_result# Таблица Продажиdef func_sale():    tbl = func_main(path_dataset)    df_sale = tbl.groupby(['invoicedate', 'country', 'stockcode'])['amount'].sum().reset_index()    return df_sale# Таблица Страныdef func_country():    tbl = func_main(path_dataset)    df_country = pd.DataFrame(sorted(pd.unique(tbl['country'])), columns=['country'])    return df_country# Таблица Товарыdef func_product():    tbl = func_main(path_dataset)    df_product = tbl[['stockcode','description']].\        drop_duplicates(subset=['stockcode'], keep='first').reset_index(drop=True)    return df_product

В коде сочетается Extract и Transform. Считываем датасет, парсим столбец с датами. Рассчитываем сумму покупки по каждой строке и удаляем ненужные для дальнейшего анализа колонки. Так как датафрейм записывается в базу данных не монолитом, а разбивается на таблицы, то готовим три вспомогательные функции.

# импорт библиотекimport pandas as pdimport sqlite3 as sqfrom etl1 import func_country,func_product,func_salecon = sq.connect('sale.db')cur = con.cursor()## Таблица Страны# cur.executescript('''DROP TABLE IF EXISTS country;#                     CREATE TABLE IF NOT EXISTS country (#                     country_id INTEGER PRIMARY KEY AUTOINCREMENT,#                     country TEXT NOT NULL UNIQUE);''')# func_country().to_sql('country',con,index=False,if_exists='append')## Таблица Товары# cur.executescript('''DROP TABLE IF EXISTS product;#                     CREATE TABLE IF NOT EXISTS product (#                     product_id INTEGER PRIMARY KEY AUTOINCREMENT,#                     stockcode TEXT NOT NULL UNIQUE,#                     description TEXT);''')# func_product().to_sql('product',con,index=False,if_exists='append')## Таблица Продажи (основная)# cur.executescript('''DROP TABLE IF EXISTS sale;#                     CREATE TABLE IF NOT EXISTS sale (#                     sale_id INTEGER PRIMARY KEY AUTOINCREMENT,#                     invoicedate TEXT NOT NULL,#                     country_id INTEGER NOT NULL,#                     product_id INTEGER NOT NULL,#                     amount REAL NOT NULL,#                     FOREIGN KEY(country_id)  REFERENCES country(country_id),#                     FOREIGN KEY(product_id)  REFERENCES product(product_id));''')## Таблица Продажи (временная)# cur.executescript('''DROP TABLE IF EXISTS sale_data_lake;#                     CREATE TABLE IF NOT EXISTS sale_data_lake (#                     sale_id INTEGER PRIMARY KEY AUTOINCREMENT,#                     invoicedate TEXT NOT NULL,#                     country TEXT NOT NULL,#                     stockcode TEXT NOT NULL,#                     amount REAL NOT NULL);''')# func_sale().to_sql('sale_data_lake',con,index=False,if_exists='append')## Перегружаем данные из вспомогательной таблицы (sale_data_lake) в основную (sale)# cur.executescript('''INSERT INTO sale (invoicedate, country_id, product_id, amount)#                     SELECT  sdl.invoicedate, c.country_id, pr.product_id, sdl.amount#                     FROM sale_data_lake as sdl LEFT JOIN country as c ON sdl.country = c.country#                     LEFT JOIN product as pr ON sdl.stockcode = pr.stockcode#                     ''')## Очищаем вспомогательную таблицу# cur.executescript('''DELETE FROM sale_data_lake''')def select(sql):  return pd.read_sql(sql,con)sql = '''select *        from (select s.invoicedate,                      c.country,                      pr.description,                      round(s.amount,1) as amount               from sale as s left join country as c on s.country_id = c.country_id                            left join product as pr on s.product_id = pr.product_id)'''print(select(sql))cur.close()con.close()

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

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

Так как BI-инструмент не может из коробки напрямую подключиться к SQLite напишем простейший скрипт на Python.

import pandas as pdimport sqlite3 as sqcon = sq.connect('C:/Users/Pavel/PycharmProjects/test/sale.db')cur = con.cursor()def select(sql):  return pd.read_sql(sql,con)sql = '''select *        from (select s.invoicedate,                      c.country,                      pr.description,                      replace(round(s.amount,1),'.',',') as amount               from sale as s left join country as c on s.country_id = c.country_id                            left join product as pr on s.product_id = pr.product_id)'''tbl = select(sql)print(tbl)

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

Data Workflow management platform + ETL DB BI Полная автоматизация. Оркестратор скриптов берет на себя контроль за своевременным выполнением всех вспомогательных процессов в системе.

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

Минусы. Усложнение инфраструктуры. Рост требований к квалификации аналитика BI. Необходимо осваивать дополнительные инструменты или языки программирования.

Data Workflow management platform + ELT Data Lake Workflow management platform + ETL DB BI Самый сложный вариант, где информация проходит двухступенчатый конвейер: сначала это неструктурированные данные (Data Lake), а затем уже хранилище (DB), где информация отсортирована и преобразована к требуемому виду.

Плюсы. Возможность разнести во времени сбор информации и ее обработку. Если на этапе проектирования таблиц учтены не все требования, возможно обращение за дополнительными данными в Data Lake.

Минусы. Аналогичны предыдущему варианту. Если выбранная платформа Data Lake платная, как следствие рост расходов на аналитику компании.

Краткие выводы.

• Построение BI-аналитики без даты инжиниринга возможно лишь на старте.

• Если аналитик BI работает в единственном числе и система постоянно усложняется, то он обязан подменять собой сразу несколько специалистов.

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

На этом все. Всем здоровья, удачи и профессиональных успехов!

Предыстория

Полгода назад взялся за один проект с возможностью оплаты биткойном. Так как проект делали на языке python, то и оплату хотелось реализовать на нем же. Сразу же взялся анализировать готовые решения, доступные библиотеки и Rest API Blockchain.com. С апи блокчейна я моментально обломался, так как их токен для использования апи довольно не просто получить.

Затем решил юзать различные библиотеки (block-io, bitcoinlib, blockchain и др.) После пару ночей попыток реализовать нормальную оплату, остановился на bitcoinlib, так как она более менее стабильно работала, и я спокойно переводил с одного кошелька на другой. Беда наступила когда появились первые 100 пользователей и вся оплата внезапно рухнула. Возможно я криво написал или что-то не так понял с работой библиотеки, но любые попытки восстановить работу оплаты были безуспешны, только если обнулять бдшку, но и так неизвестно сколько бы она продержалась.

В итоге решили оставить без BTC оплаты. Я опечалился и не связывался с оплатой биткойном полгода.

К чему я пришел

На днях я все-таки решил добить этот вопрос для себя, надеюсь кому-то еще пригодятся мои наработки.

Все начинается с seed фразы. Мнемоническаяфраза(англ. Mnemonic phrase илиSeed фраза) - это список слов, которые хранят всю информацию, необходимую для восстановления биткоин-кошелька. Существуют несколько стандартов генерации фраз BIP 32, BIP 39, BIP 44, и еще BIP 49. Самый распространенный - это BIP 44 (12 слов).

Пример seed фразы:

vivid area able second bicycle advance demand alpha flip stable drift route

Чтобы сгенерировать фразу будем использовать библиотеку bipwallet. Чтобы ее установить воспользуемся командой pip install bipwallet.

from bipwallet import wallet# generate 12 word mnemonic seedseed = wallet.generate_mnemonic()print(seed)

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

https://login.blockchain.com/#/recover

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

Чтобы во всем не запутаться и знать какие данные мы должны получить, я использовал сайт https://iancoleman.io/bip39/

Генерация дочернего адреса кошелька для каждого пользователя:

Чтобы получить наш нулевой адрес Биткойн кошелька на основе seed фразы (12VeK1eRgPHRUikNLXq3Nuz99gS2S46QMD), нам нужно пройти всю цепочку преобразований. Методом проб и ошибок мне все-таки удалось получить адрес кошелька следующим кодом:

from bipwallet.utils import *def gen_address(index):    # Наша seed фраза    seed = 'vivid area able second bicycle advance demand alpha flip stable drift route'    # Мастер ключ из seed фразы    master_key = HDPrivateKey.master_key_from_mnemonic(seed)    # Public key из мастер ключа по пути 'm/44/0/0/0'    root_keys = HDKey.from_path(master_key, "m/44'/0'/0'/0")[-1].public_key.to_b58check()    # Extended public key    xpublic_key = str(root_keys, encoding="utf-8")    # Адрес дочернего кошелька в зависимости от значения index    address = Wallet.deserialize(xpublic_key, network='BTC').get_child(index, is_prime=False).to_address()    rootkeys_wif = HDKey.from_path(master_key, f"m/44'/0'/0'/0/{index}")[-1]    # Extended private key    xprivatekey = str(rootkeys_wif.to_b58check(), encoding="utf-8")    # Wallet import format    wif = Wallet.deserialize(xprivatekey, network='BTC').export_to_wif()    return address, str(wif, 'utf-8')print(gen_address(0))

Данная функция возвращает адрес кошелька и wif в зависимости номера. Максимальное число с которым удалось получить адрес это 999999999.

wif (Wallet import format) - это просто кодирование байтов ключа в кодировку Base58 + контрольная сумма. Он нам понадобится в дальнейшем при генерации транзакции.

Это все значит, что имея только одну seed фразу мы можем создать 1 млрд дочерних адресов. Каждому пользователю при регистрации мы будем выдавать новый адрес, через который он сможет оплачивать по BTC. Появляется ограничение на 1 млрд пользователей, но нам никто не запрещает использовать несколько seed фраз или генерировать каждому юзеру новую фразу, но тогда каждая оплата будет кидаться не в общий ваш кошелек, а по разным.

Проверка баланса и транзакции:

Теперь когда у каждого пользователя свой личный адрес биткойн кошелька, нужно проверить баланс этого адреса. Для этого мы будем обращаться к сайту Blockchain.com дабы получить нужную информацию.

import requests# Адрес кошелька пользователя wallet = '12VeK1eRgPHRUikNLXq3Nuz99gS2S46QMD'# wallet = gen_address(0)url = f'https://blockchain.info/rawaddr/{wallet}'x = requests.get(url)wallet = x.json()print('Итоговый баланс:'+str(wallet['final_balance']))print('Транзакции:'+str(wallet['txs']))if wallet['total_received']==0:  print('баланс пустой')

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

Транзакции

На данном этапе мы дали каждому пользователю свой адрес кошелька и знаем все транзакции с данным адресом, но этого недостаточно. Нам нужно чтобы мы могли отправить его же деньги обратно. Для этого воспользуемся библотекой bit. Чтобы ее установить воспользуемся командой pip install bit.

from bit import PrivateKey# Приватный ключ из wifmy_key = PrivateKey(wif='L46ixenNSu8Bqk899ZrH8Y96t8DHqJ1ZyxzQBGFTbh38rLHLaPoY')# Количество долларов перевода, можно поменять на btcmoney=0.1# Кошелек куда будут переведены деньгиwallet='17ya3bCpPioyPH8kAyFkEDBUqdjF6wwPxo'# Коммисия перевода, если поставить слишком маленькую, то транзакцию не примут# И чем больше коммисия, тем быстрее пройдет переводfee=2000# Генерация транзакцииtx_hash = my_key.create_transaction([(wallet, money, 'usd')],fee=fee,absolute_fee=True)print(tx_hash)

В итоге мы получили вот такую транзакцию:

0100000001fe64490fce5e85d5eb00865663a3d44f4108549fdb2840b086cfc781390d4a2d010000006a47304402202dc1496d28bb10d50d94d70870e2a79ea472c5960de8f7418bb30f9b96643efc02204691547c98edad3181a056bf6404601efe289200ba8e3073a2f5b7c0c7f4fec10121026516c551584b484ce3ca7bb71bbf24cce133bf40bdf4e2ce5a3936bc7e66a2abffffffff02e3020000000000001976a9144c83a20250ccb62ce2b3b1ea80c6082b634fdf9f88ac08f40200000000001976a9144c83a20250ccb62ce2b3b1ea80c6082b634fdf9f88ac00000000

Выглядит красиво, но что с этим делать?

Можно зайти на сайт https://www.blockchain.com/btc/pushtx

и вручную отправить эту транзакцию.

Также можем декодировать эту транзакцию и проверить все ли верно мы указали https://www.blockchain.com/btc/decode-tx

Но нам нужно это автоматизировать, поэтому напишем несколько строк:

import requestsurl = 'https://blockchain.info/pushtx'tx='0100000001fe64490fce5e85d5eb00865663a3d44f4108549fdb2840b086cfc781390d4a2d010000006a47304402202dc1496d28bb10d50d94d70870e2a79ea472c5960de8f7418bb30f9b96643efc02204691547c98edad3181a056bf6404601efe289200ba8e3073a2f5b7c0c7f4fec10121026516c551584b484ce3ca7bb71bbf24cce133bf40bdf4e2ce5a3936bc7e66a2abffffffff02e3020000000000001976a9144c83a20250ccb62ce2b3b1ea80c6082b634fdf9f88ac08f40200000000001976a9144c83a20250ccb62ce2b3b1ea80c6082b634fdf9f88ac00000000'x = requests.post(url, data = {'tx':tx})result = x.textprint(result)

Выполним пост запрос, если получаем ответ: Transaction Submitted. Это значит, что через несколько секунд транзакция появится в сети и деньги спишутся с пользователя.

Применение

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

Для демонстрации работы BTC оплаты, я напишу простенького телеграм бота, который будет выполнять роль клиента Blockchain.com, то есть вы сможете хранить в нем свои биткойны и от туда же переводить другим людям. Ссылка на исходники бота будут в конце.

Проверить работу бота можно тут: https://t.me/Blockchain_client_bot

Задеплоил на heroku, так что надеюсь не будет падать)

Функционал бота

Регистрация пользователя

В качестве БД я использовал sqlite3 и создал одну таблицу пользователей:

import sqlite3conn = sqlite3.connect("my.db")  # или :memory: чтобы сохранить в RAMcursor = conn.cursor()cursor.execute("CREATE TABLE users (chatid INTEGER , name TEXT, balance INTEGER, btc_wallet TEXT, wif TEXT, btc_sent TEXT, state INTEGER)")conn.commit()

При нажатии start мы регистрируем пользователя, генерируем для него адрес биткойн кошелька, wif и добавляем данные в БД:

sql = "SELECT COUNT(*) FROM users "cursor.execute(sql)user = cursor.fetchone()  address, wif= gen_address(user[0]+1)sql_insert = "INSERT INTO users VALUES ({}, '{}', 0,'{}','{}','no',0)".format(message.chat.id,                                                                           message.chat.first_name,address,wif)cursor.execute(sql_insert)conn.commit()

Проверка баланса

if message.text == '? Ваш баланс':  url = f'https://blockchain.info/rawaddr/{data[3]}'  x = requests.get(url)  wallet = x.json()  await bot.send_message(message.chat.id, f'''? *Итоговый баланс:* {format(wallet['final_balance'] / 100000000, '.9f')} BTC*Всего получено:* {format(wallet['total_received'] / 100000000, '.9f')} BTC*Всего отправлено:* {format(wallet['total_sent'] / 100000000, '.9f')} BTChttps://www.blockchain.com/ru/btc/address/{data[3]}''', parse_mode= "Markdown")

Получить BTC

Для создания qr-кода я использовал библиотеку qrcode и на вход передал ранее сгенерированный адрес биткойн кошелька из БД.

 if message.text == '? Получить BTC':    img = qrcode.make(data[3])    img.save('qr.jpg')    await bot.send_message(message.chat.id, f'''? Ваш адрес биткойн кошелька:*{data[3]}*''', parse_mode= "Markdown")        await bot.send_photo(message.chat.id,photo=open('qr.jpg', 'rb'))

Отправить BTC

try:    sum = float(message.text)    url = f'https://blockchain.info/rawaddr/{data[3]}'    x = requests.get(url)    wallet = x.json()    if sum + 10000 <= wallet['final_balance'] / 100000000:        try:            my_key = PrivateKey(wif=data[4])            # Коммисия перевода, если поставить слишком маленькую, то транзакцию не примут            # И чем больше коммисия, тем быстрее пройдет перевод            fee = 10000            # Генерация транзакции            tx_hash = my_key.create_transaction([(data[5], sum, 'btc')], fee=fee, absolute_fee=True)            print(tx_hash)            url = 'https://blockchain.info/pushtx'            x = requests.post(url, data={'tx': tx_hash})            result = x.text            sql = "UPDATE users SET state = {} WHERE chatid = {}".format(0, message.chat.id)            cursor.execute(sql)            conn.commit()            await bot.send_message(message.chat.id, result)        except Exception:            await bot.send_message(message.chat.id, " Ошибка при выолнении транзакции")    else:        await bot.send_message(message.chat.id, '  На вашем балансе недостаточно средств.')except ValueError:    await bot.send_message(message.chat.id, 'Неправильно введена сумма отправления, попробуйте еще раз')

Проверим через сайт, что транзакция отправилась:

Исходники и как запустить

Скачать исходники бота можно тут: github.com/Lil-hack/blockchain-client

Склонировав репозиторий, устанавливаем необходимые пакеты:

pip install-rrequirements.txt

Некоторые библиотеки у меня не заработали на windows, так что лучше сразу запускать на linux.

В файле main.py заменяем ваш токен телеграм бота:

# Ваш токен от BotFatherTOKEN = 'YOUR TOKEN'

В файле btc_core.py заменяем на вашу seed фразу:

# Ваша seed фразаseed = 'YOUR SEED'

И запускаем бота командой: python main.py

Работает на python 3.7.0 и выше. Бот написан за один вечер, так что просьба строго не судить ^^

Итого

Как оказалось, все довольно не сложно, и в несколько десятков строк можно добавить оплату BTC в любой python проект. Я не профи в криптографии, так что скорее всего многие моменты упустил, но надеюсь кому-то эта статья будет полезна.

Добрый день уважаемые читатели! Несколько дней назад перечитывая книгу Энтони Молинаро SQL. Сборник рецептов, в одной из глав я наткнулся на тему, которая была посвящена определению начала и конца диапазона последовательных значений. Бегло ознакомившись с материалом, я сразу вспомнил, что уже сталкивался с данным вопросом в качестве одного из тестовых заданий, но тогда тема была заявлена как Задача на поиск сессий. Фишкой технического собеседования был не разбор выполненной работы, а один из вопросов интервьюера о том, как получить аналогичные значения с помощью Spark. Готовясь к собеседованию, я не знал, что в компании применяется (а может и не применяется) Apache Spark, и поэтому не собрал информацию по новому на тот момент для меня инструменту. Оставалось лишь выдвинуть гипотезу, что искомое решение может быть подобно скрипту, который можно написать c помощью библиотеки Pandas. Хотя очень отдалено я все-таки попал в цель, однако поработать в данной организации не получилось.

Справедливости ради хочу заметить, что за прошедшие годы я несильно продвинулся в изучении Apache Spark. Но я все равно хочу поделиться с читателями наработками, так как многие аналитики вообще не сталкивались с этим инструментом, а другим возможно предстоит подобное собеседование. Если вы являетесь профессионалом Spark, то всегда можно предложить более оптимальный код в комментариях к публикации.

Это была преамбула, приступим непосредственно к разбору данной темы. Пойдем сначала и напишем SQL скрипт. Но прежде создадим базу данных и заполним ее значениями. Так как это демо-пример предлагаю использовать SQLite. Данная БД уступает более мощным коллегам по цеху, но ее возможностей для разработки скрипта нам хватит сполна. Чтобы автоматизировать заявленные выше операции, я написал вот такой код на Python.

# Импорт библиотекimport sqlite3# Данные для записи в БДprojects = [    ('2020-01-01', '2020-01-02'),    ('2020-01-02', '2020-01-03'),    ('2020-01-03', '2020-01-04'),    ('2020-01-04', '2020-01-05'),    ('2020-01-06', '2020-01-07'),    ('2020-01-16', '2020-01-17'),    ('2020-01-17', '2020-01-18'),    ('2020-01-18', '2020-01-19'),    ('2020-01-19', '2020-01-20'),    ('2020-01-21', '2020-01-22'),    ('2020-01-26', '2020-01-27'),    ('2020-01-27', '2020-01-28'),    ('2020-01-28', '2020-01-29'),    ('2020-01-29', '2020-01-30')]try:    # Создаем соединение    con = sqlite3.connect("projects.sqlite")    # Создаем курсор    cur = con.cursor()    # Создаем таблицу    cur.execute("""CREATE TABLE IF NOT EXISTS projects (                    proj_id INTEGER PRIMARY KEY AUTOINCREMENT,                    proj_start TEXT,                    proj_end TEXT)""")    # Добавляем записи    cur.executemany("INSERT INTO projects VALUES(NULL, ?,?)", projects)    # Сохраняем транзакцию    con.commit()    # Закрываем курсор    cur.close()except sqlite3.Error as err:    print("Ошибка выполнения запроса", err)finally:    # Закрываем соединение    con.close()    print("Соединение успешно закрыто")

Решение тривиальное и не требует дополнительных комментариев. Для коммуникации с созданной БД я использовал DBeaver. Клиентское приложение хорошо себя зарекомендовало, поэтому я часто использую его для разработки SQL запросов.

В рукописи Молинаро приводится два варианта решения данной задачи, но, по сути, это один и тот же код. Просто в первом случае не применяются оконные функции, а во-втором они присутствуют. Я выбрал последний, чтобы упростить ход рассуждений. Текстовую версию скрипта и все вспомогательные файлы к публикации вы можете найти по адресу (ссылка).

select       p3.proj_group,       min(p3.proj_start) as date_start,      max(p3.proj_end) as date_end,      julianday(max(p3.proj_end))-julianday( min(p3.proj_end))+1 as deltafrom    (select      p2.*,     sum(p2.flag)over(order by p2.proj_id) as proj_groupfrom (select       p.proj_id ,       p.proj_start,       p.proj_end,       case       when lag(p.proj_end)over(order by p.proj_id) = p.proj_start then 0 else 1       end as flagfrom projects as p) as p2) as p3group by p3.proj_group

Если вы раньше уже использовали оконные функции, то разобраться самостоятельно с написанной конструкцией не составит никакого труда. Я лишь кратко опишу логику. Первоначальная таблица представляет собой последовательные шаги, для которых заданы два параметра: дата начала и дата конца. Если дата начала шага соответствует дате конца предыдущего шага, то два шага считаются одной сессией. Следовательно, начинать расчеты нужно со смещения, за это отвечает оконная функция lag. На следующем этапе сравниваем дату старта текущего шага и дату конца предыдущего и выводим либо 0, либо 1. Если к новому столбцу применить суммирование с нарастающим итогом, то получим номера сессий. Стандартная группировка по номерам с агрегирующими функциями позволит извлечь начало и конец диапазона значений. Я также рассчитал дельту между двумя датами на случай, если потребуется установить самую длинную или короткую сессию. Приведенный код будет актуален и для других БД. Ошибка будет выводиться только на строчке, где находится разница между двумя датами (функция julianday это прерогатива SQLite). На этом первая часть тестового задания выполнена. Переходим к Spark.

Если верить Википедии, то Apache Spark это фреймворксоткрытым исходным кодомдля реализации распределённой обработкинеструктурированныхи слабоструктурированных данных, входящий в экосистему проектовHadoop. Так как я не пишу на Java, Scala или R, то для получения функциональности Spark решил использовать PySpark. Устанавливать на компьютер все необходимые для работы компоненты я не стал. Для экспериментов выбрал облачный сервис Google Colab, так как у меня уже был заведенный аккаунт. Основной минус - при каждом новом сеансе работы нужно заново скачивать файлы, связанные с запуском нашего инструмента. На просторах Интернета я встречал вариант с фиксированной установкой, но пока не пробовал его на практике.

С помощью базовых команд Linux мы устанавливаем OpenJDK, скачиваем и разархивируем файлы Spark. Затем прописываем две переменные среды. Нужно не забыть о вспомогательной библиотеке findspark. Подготовительная работа закончена, осталось только открыть сессию.

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

Так как операций в Spark довольно много, рекомендую сразу обзавестись шпаргалками. Если говорить о литературе для изучения данного инструмента, то радует два факта. Во-первых, есть как англоязычные, так и переводные издания, а во-вторых, источников информации предостаточно. Если вы не владеете языком Шекспира, то могу порекомендовать в первую очередь Изучаем Spark. Молниеносный анализ данных, авторы Холден Карау, Энди Конвински, Патрик Венделл, Матей Захария.

После того, как датафрейм подготовлен, можно пойти сначала самым простым путем и использовать уже имеющийся скрипт SQL. В код нужно лишь внести две правки: изменить имя базовой таблицы, изменить способ нахождения дельты (функция datediff).

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

from pyspark.sql.functions import lagfrom pyspark.sql import functions as Ffrom pyspark.sql.window import Window# Equivalent of Pandas.dataframe.shift() methodw = Window().partitionBy().orderBy(col("proj_id"))df_dataframe = df.withColumn('lag', F.lag("proj_end").over(w))#...# Equivalent of SQL- CASE WHEN...THEN...ELSE... ENDdf_dataframe = df_dataframe.withColumn('flag',F.when(df_dataframe["proj_start"] == df_dataframe["lag"],0).otherwise(1))#...# Cumsum by column flagw = Window().partitionBy().orderBy(col("proj_id"))df_dataframe = df_dataframe.withColumn("proj_group", F.sum("flag").over(w))#...# Equivalent of SQL - GROUP BYfrom pyspark.sql.functions import  min, maxdf_group = df_dataframe.groupBy("proj_group").agg(min("proj_start").alias("date_start"), \                                                  max("proj_end").alias("date_end"))df_group = df_group.withColumn("delta", F.datediff(df_group.date_end,df_group.date_start))df_group.show()

Краткие выводы.

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

2. Даже если вы раньше никогда не работали со Spark, это не повод отказываться от конкурса на вакантную позицию. Основы PySpark можно освоить в сжатые сроки, при условии, что в бэкграунде уже есть опыт программирования с использованием библиотеки Pandas.

3. Недостатка в книгах по Spark не наблюдается.

На этом все. Всем здоровья, удачи и профессиональных успехов!

Вступление

Для многих новичков в разработке на Python возникает проблема как подключить базу данных? Я сам столкнулся с такой проблемой в начале разработки. Тема оказалось довольно простой, но в интернете есть множество гайдов, которые могут запутать. В этом туториале я расскажу о том, как просто интегрировать базу данных Sqlite3 на примере Telegram бота.

Начало работы

Для работы нам понадобятся базовые знания в Python, достаточные для написания простого бота. Данный гайд можно использовать не только для интеграции с ботом, но и для других проектов. Если вы не используете PyCharm, и у вас нет средства для работы с БД, то необходимо установить утилиту SQLiteStudio с официального сайта.

Теперь давайте перейдем к написанию кода. В терминале необходимо установить pyTelegramBotAPI (если по какой-то причине у вас его нет).

pip install pyTelegramBotAPI

Далее создаем новый файл с расширением .py. В нем прописываем следующее:

import sqlite3import telebotbot = telebot.TeleBot("token")

Устанавливать sqlite3 не нужно это стандартная библиотека. Вместо "token" необходимо указать токен бота, который выдал вам BotFather. Теперь проверим, работает ли бот.

@bot.message_handler(commands=['start'])def start_message(message):bot.send_message(message.chat.id, 'Добро пожаловать')

Запустим код. Если при запуске бота он пишет нам "Добро пожаловать", то все работает отлично.

Как мы видим, все работает.

Настройка базы данных

Открываем SQLiteStudio. Во вкладке Database выбираем пункт Add a database. Для удобства можно использовать сочетание Ctrl+O.

Далее нажимаем на зеленую кнопку Создать новый фал базы данных. Выбираем директорию вашего проекта. В ней можете создать отдельную папку db, где будет хранится файл с базой. Вводим название, например database. Расширение будет присвоено автоматически. Нажимаем Сохранить. Теперь слева в списке у нас появился наш файл. Кликаем по нему ПКМ и выбираем пункт Connect to the database. Соединение с базой установлено. Появилось два подпункта: Таблицы и Представления. В этом уроке мы будем затрагивать только пункт Таблицы. Нажимаем по нему ПКМ и выбираем Add a table.

В поле для ввода указываем имя для таблицы. Я назову ее test. Чуть выше есть активная кнопка Добавить столбец (Ins). В появившемся окне указываем название столбца и тип данных. Для начала вам могут понадобится такие условия, как Первичный ключ, Не NULLи Уникальность. Первый столбец я назову id, выберу тип данных INTEGER и установлю все три условия. У Первичный ключ выберу настройку Автоинкремент. Этот столбец будет автоматически создавать ID записи в таблице. Работать с ним в коде мы не будем.

Второй столбец будет хранить user_id. Он будет иметь тип данных INT, уникален и не равен нулю. Третий столбец я назову user_name, присвою тип данных STRING и выберу пункт Не NULL. Еще 2 столбца будут называться user_surname и username. После того, как вы создали столбцы, нажимаем зеленую кнопку с галочкой.

В общем все будет выглядеть вот так.

Работа с базой данных в коде

Теперь давайте вновь перейдем к коду. Создадим 2 переменные.

conn = sqlite3.connect('db/database.db', check_same_thread=False)cursor = conn.cursor()

В первой мы подключаем нашу базу данных, а во второй создаем курсор для работы с таблицами. Обратите внимание на то, что у меня в директории проекта создана папка db, а уже в ней находится файл БД. Если у вас по другому, укажите расположение в первой переменной.

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

def db_table_val(user_id: int, user_name: str, user_surname: str, username: str):cursor.execute('INSERT INTO test (user_id, user_name, user_surname, username) VALUES (?, ?, ?, ?)', (user_id, user_name, user_surname, username))conn.commit()

Итак, сейчас объясню, что тут происходит. В аргументах функции мы указываем переменную user_id с типом данных inst. Далее добавляем запись в таблицу test в столбец user_id. С остальными значениями точно так же. В конце мы просто применяем изменения. Эта функция не будет выполнятся, пока мы ее не вызовем. Давайте исправим это. Создадим обработчик сообщений от пользователя и будем вносить в базу его данные.

@bot.message_handler(content_types=['text'])def get_text_messages(message):if message.text.lower() == 'привет':bot.send_message(message.from_user.id, 'Привет! Ваше имя добавленно в базу данных!')        us_id = message.from_user.idus_name = message.from_user.first_nameus_sname = message.from_user.last_nameusername = message.from_user.usernamedb_table_val(user_id=us_id, user_name=us_name, user_surname=us_sname, username=username)

В переменные мы вносим имя пользователя, id, имя и фамилию в Telegram, а потом сохраняем в БД. В конце кода можем сделать так, чтобы бот работал постоянно.

bot.polling(none_stop=True)

Пришло время проверить, как работает код. Запускаем бота и пишем ему "Привет".

Теперь можем зайти в SQLiteStudio, и выбрав вкладку Данные посмотреть что получилось:

Как мы видим, данные уже в базе. Если их по какой-то причине нет, то обновите базу, нажав на синюю кнопку Обновить

Вот весь код:

import sqlite3import telebotbot = telebot.TeleBot("token")conn = sqlite3.connect('db/database.db', check_same_thread=False)cursor = conn.cursor()def db_table_val(user_id: int, user_name: str, user_surname: str, username: str):cursor.execute('INSERT INTO test (user_id, user_name, user_surname, username) VALUES (?, ?, ?, ?)', (user_id, user_name, user_surname, username))conn.commit()@bot.message_handler(commands=['start'])def start_message(message):bot.send_message(message.chat.id, 'Добро пожаловать')@bot.message_handler(content_types=['text'])def get_text_messages(message):if message.text.lower() == 'привет':bot.send_message(message.chat.id, 'Привет! Ваше имя добавлено в базу данных!')us_id = message.from_user.idus_name = message.from_user.first_nameus_sname = message.from_user.last_nameusername = message.from_user.usernamedb_table_val(user_id=us_id, user_name=us_name, user_surname=us_sname, username=username)bot.polling(none_stop=True)

Заключение

В этой статье мы немного познакомились с sqlite3 и сделали простой скрипт добавления сведений о пользователе. Этого, конечно, не достаточно. В скором времени выпущу продолжение, в котором затрону тему удаления пользователей и редактирования полей.

Огромное спасибо за то, что прочитали. Надеюсь, кому-то помог. Всем удачи и поменьше багов в коде!

Квалификацию надо иногда повышать, и вообще учиться для мозгов полезно. А потому пошел я недавно на курсы - поизучать Python и всякие его фреймворки. На днях вот до Django добрался. И тут мы в ходе обучения коллективно выловили не то чтобы баг, но дивный эффект на стыке Python 3, Sqlite 3, JSON и Win 10. Причем эффект был настолько дивен, что гугль нам не помог - пришлось собираться всей заинтересованной группой вместе с преподавателем и коллективным разумом его решать.
А дело вот в чем: изучали мы базу данных (а у Django предустановлена Sqlite 3) и, чтоб каждый раз заново руками данные не вбивать, прикрутили загрузку скриптом из json-файлов. А в файлы данные из базы штатно дампили питоновскими же методами:

python manage.py dumpdata -e contenttypes -o db.json

Внезапно те, кто работал под виндой (за все версии не поручусь, у нас подобрались только обитатели Win 10), обнаружили, что дамп у них производится в кодировке windows-1251. Более того, джейсоны в этой кодировке отлично скармливаются базе. Но стоило только переформатировать их в штатную по документам для Sqlite 3, Python 3 и особенно для JSON кодировку UTF-8, как в лучшем случае кириллица в базе превращалась в тыкву, а в худшем ломался вообще весь процесс загрузки данных.
Ничего подобного найти не удалось ни в документации, ни во всем остальном гугле, считая и англоязычный. Что самое загадочное, ручная загрузка тех же самых данных через консоль или админку проекта работала как часы, хотя уж там-то кодировка была точно UTF-8. Более того, принудительное прописывание кодировки базе никакого эффекта не дало.
Мы предположили, что причиной эффекта было взаимодействие джейсона с операционной системой - каким-то образом при записи и чтении именно джейсонов система навязывала свою родную кодировку вместо нормальной. И действительно, когда при открытии файла принудительно устанавливалась кодировка UTF-8:

open(os.path.join(JSON_PATH, file_name + '.json'), 'r', encoding="utf-8")

в базу попадали не кракозябры, а нормальные русские буквы. Но проблему с созданием дампа таким способом не решишь, а переделывать кодировку потом руками тоже как-то не по-нашему. И тогда мы решили поискать способ укротить винду.
И такой способ нашелся. Вот он:

• открываем панель управления, но не новую красивую, а старую добрую:

• открываем (по стрелке) окошко региона:

• по стрелкам переключаем вкладку "Дополнительно" и открываем окошко "Изменить язык системы":

• и в нем ставим галку по стрелке в чекбоксе "Бета-версия: Использовать Юникод (UTF-8) для поддержки языка во всем мире.

Система потребует перезагрузки, после чего проблема будет решена.

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

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

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

Дело в том что почти треть работы видеомонтажера заключается в рутинном поиске видеоряда из исходников, которые надо каждый раз шерстить в поиске контекстного плана под закадровый текст, по моему это не несет никакой творческой составляющей, особенно когда ты занимаешься этим 15 лет). Ну и подумал я, а что если написать софтину, которая будет проходится по папке с исходниками, распознавать объекты, аккуратненько складывать их в БД. Далее, в момент поиска видео фрагментов для так называемой джинсы, вводится поисковое слово, например Солнце, и все что находится каким то образом передается в монтажную систему.

Идея зрела, собирался стёк, писать решил на Swift, обученные модели собственно Core ML, база данных SQLite. На первый взгляд идея казалась легко реализуемой, вроде ничего сложного.

Очень быстро накидал основной код, который вытаскивает кадры из видео, распознает обьекты с помощью модели Resnet50, которую рекомендовали яблочники у себя на сайте, она очень шустро работала и позволяла настраивать процент при котором считать объект распознанным. Сам код спокойно раздается на том же apple.com для всех желающих. Подключил библиотеку SQLite.swift, обернул ее функции в свои методы, все работает!

Потом еще пришлось неплохо повозиться с алгоритмами создания очереди обработки списка файлов и в этот момент я обратил внимание что программа то разрослась! Уже после 1000-й строчки кода вдруг пришло понимание что mvc-паттерн уже совсем не подходит для этого проекта, а именно он обычно и предлагается на всех туториалах и подсказках из Stackoverflow. Как же затягивает процесс когда все получается и даже не обращаешь внимания что у тебя весь код навален в одном файле. Стал раскидывать все по классам, синглтонам и прочим сущностям. Вроде стало полегче, но это не надолго, ибо впереди еще нужно распаралелить процессы на потоки, что бы программа не замирала пока идет процесс распознавания в большом количестве файлов.

Почитал статьи о многопоточных приложениях, о Grand Central Dispatch (GCD) - технологии Apple, предназначенная для многоядерных процессоров, вроде бы тоже все просто - кидаешь фоновую работу в основной поток а обновление интерфейса в главный поток и опять все работает! Но что то подсказывало что так легко и быстро не бывает! Начался процесс тестирования.

Первый серьезный глюк дал о себе знать когда запустил сканировать большой архив семейных видеофайлов, 70 гигов, видео снятые в разное время на разные телефоны и поэтому и разные форматы - идеально! Как раз то что надо для тестирования! Сканирование останавливалось на 420-ом файле, снятом на какой-то старый Самсунг под windows mobile, ну да ладно, может битый файл, подумал я и удалил его, запустил снова. опять 420 файл! Совершенно в другом формате, с яблофона, не битый! Что за магия такая? Ну давайте и его удалим. еще раз опять 420 файл пора лезть в дебаггер.

Две недели, две недели жизни (в свободное от работы время) я посвятил поиску этой ошибки! Виновником оказался объект VNCoreMLRequest, работающий с запросами к ML-модели и который не любит когда его используют в нескольких потоках, при этом он никак не проявляет себя в логах дебаггера а просто выдает ошибку времени выполнения, проще говоря кладет один из потоков. Так же порадовал метод обработки изображений copyCGImage, который отказывался работать стабильно, правда яблочники предупредили об этом на своем ресурсе для разработчиков и предлагали использовать вместо него другой асинхронный метод generateCGImagesAsynchronously, который как ни странно работал еще хуже, в итоге я вернулся к первому методу окружив его блоком try catch.

Следующим этапом проектирования стала идея распознавать обьекты сразу несколькими обученными моделями для более эффективного результата. То есть если одна модель в одном кадре ничего не нашла, то вторая тоже имея другой набор данных в своих нейронах может чего и найдет. Я создал три слота для подгрузки моделей: первые два для поставляемых с приложением моделей YOLOv3 и Resnet50, и третий слот для любой другой модели, которая может быть подгружена из файла. Я посчитал что кто то из пользователей заведет свои собственные модели для специфических задач и тогда третий слот будет очень кстати.

К тому же Apple в поставке с Xcode теперь предлагает отдельный инструмент Create ML для создания своих моделей из набора картинок, там все очень просто, никаких командных строк, обычный пользовательский интерфейс для практически любого юсера.

Программа сформировывалась в завершенный продукт, не хватало одного - как пользователю выводить найденные видео фрагменты в программу видеомонтажа. Я наметил два варианта - это форматы EDL и XML. Реализовать первый формат не составляло особого труда, это старый известный с ленточных времен формат, используемый киношниками для переноса намеченных фрагментов в системы монтажа. Но проблема состояла в том, что EDL не содержит информацию о передаваемых файлах, а только о таймкодах, точках входа и выхода фрагмента, то есть в итоге пользователь получит набор фрагментов в секвенции, но они все будут оффлайн, потому что не известно из каких файлов брать эти куски, а ведь их, этих файлов может быть много, для каждого фрагмента свой файл. Другое дело XML! Он содержит всю информацию которую ты только можешь в него запихнуть: и путь, и формат файлов, и настройки звука, и даже применяемые маркеры, все что нужно, современный формат! Но вот реализовать всю эту крутизну это дело далеко не простое, и прочитать надо литературу которой нигде нет, ибо нужна информация именно по XML, используемом для экспорта секвенции именно с видео данными, а не какого нибудь там каталога для инет-магазина. Эту задачу я стал решать с изучения выведенного изAdobe Premiere шаблонной секвенции с парой файлов на таймлайне в XML. Полученный файл я открыл в текстовом редакторе и стал изучать. Постепенно стали вырисовываться блоки кода, для каждого плана на секвенции три блока - один для видео и два для звука, в общем теле кода сначала идут видео блоки а потом привязанные к ним аудио блоки, так же есть начальные и завершающее блоки файла с тегами описывающими, видимо, формат секвенции. Я разделил все эти блоки в отдельные файлы, которые обозначил как многострочные String ресурсы в Xcode. Создал отдельный класс, который оперирует этими блоками в цикле, собирая их в нужной последовательности в один код и подставляя в нужные места строковые данные с именем файла и информацией о таймкоде. Та еще работка! Хотя может быть абсолютно привычно для html-верстальщика.На первый взгляд сложная задача, но решена была довольно быстро, хотя это можно назвать хакерским методом) Но формат то по сути открытый! Другое дело что мы используем версию XML , сгенерированную Аdobe Premiere, с его тэгами, но насколько эти теги имеют проприетарный формат я рассуждать не берусь, знаю только что все работает, и в Final Cut Pro (в полной версии), и вдругих монтажках

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

Сейчас я думаю, чего еще можно добавить в приложение, есть уже некоторые идеи, и собственные и присланные пользователями, которые уже пользуются приложением. Например сейчас с появлением новых процессоров Apple Silicon, которые имеют аппаратное ускорение ML процессов до 16x, нужно обязательно сделать поддержку этой платформы в новых версиях. Ну а пока программа уже доступна в Mac App Store, называется Videoindex.

Надеюсь кому то пригодится мой опыт и этот инструмент, позволяющихся сэкономить до трети времени видеомонтажа

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']);

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