Типобезопасный SQL на Kotlin +15


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


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


vs


Ссылка на GitHub для нетерпеливых


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


Данная статья расскажет про библиотеку на Kotlin, которая позволяет писать SQL запросы в синтаксисе Kotlin. Также, мы немного посмотрим на внутренности библиотеки, чтобы понять как это работает.


Немного теории


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


Однако, под SQL скрывается довольно мощный фундамент в виде теории реляционных баз данных — там всё очень логично. Чтобы понять структуру запросов, обратимся к простой выборке:


SELECT id, name -- проекция (projection), ?(id, name)
  FROM employees -- источник (table)
  WHERE organization_id = 1 -- выборка с предикатом (predicate), ?(organization_id = 1)

Что важно понять: запрос состоит из трех последовательных частей. Каждая из этих частей во-первых — зависит от предыдущей, во-вторых — подразумевает ограниченный набор выражений для продолжения запроса. На самом деле даже не совсем так: выражение FROM тут явно является первичным по отношению к SELECT, т.к. то, какой набор полей мы можем выбрать, напрямую зависит от таблицы, из которой производится выборка, но никак не наоборот.


SQL


Перенос на Kotlin


Итак, FROM первичен по отношению к любым другим конструкциям языка запросов. Именно из этого выражения возникают все возможные варианты продолжения запроса. В Kotlin мы отразим это через функцию from(T), которая будет принимать на вход объект, представляющий из себя таблицу, у которой есть набор колонок.


object Employees : Table("employees") {
    val id = Column("id")
    val name = Column("name")
    val organizationId = Column("organization_id")
}

Функция вернет объект, который содержит в себе методы, отражающие возможное продолжение запроса. Конструкция from всегда идет самой первой, перед любыми другими выражениями, поэтому она предполагает большое количество продожений, включая завершающий SELECT (в противоположность SQL, где SELECT всегда идет перед FROM). Код, эквивалентный SQL-запросу выше будет выглядеть следующим образом:


from(Employees)
    .where { e -> e.organizationId eq 1 }
    .select { e -> e.id .. e.name }

Интересно, что таким образом мы можем предотвратить невалидный SQL еще во время компиляции. Каждое выражение, каждый вызов метода в цепочке предполагает ограниченное число продолжений. Мы можем контролировать валидность запроса средствами языка Kotlin. Как пример — выражение where не предполагает после себя продолжения в виде еще одного where и, тем более, from, а вот конструкции groupBy, having, orderBy, limit, offset и завершающий select все являются валидными.


Kuery


Лямбды, переданные в качестве агрументов операторам where и select призваны сконструировать предикат и проекцию соответственно (мы уже упоминали их ранее). На вход лямбде передается таблица, чтобы можно было обращаться к колонкам. Важно, что типобезопасность сохраняется и на этом уровне — с помощью перегрузки операторов мы можем добиться того, что предикат в конечном итоге будет представлять из себя псевдобулевое выражение, которое не скомпилируется при наличии синтаксической ошибки или ошибки, связанной с типами. То же самое касается и проекции.


fun where(predicate: (T) -> Predicate): WhereClause<T>

fun select(projection: (T) -> Iterable<Projection>): SelectStatement<T>

JOIN


Реляционные базы данных позволяют работать с множеством таблиц и связями между ними. Было бы хорошо дать возможность разработчику работать с JOIN и в нашей библиотеке. Благо, реляционная модель хорошо ложится на всё, что было описанно ранее — нужно лишь добавить метод join, который добавит вторую таблицу в наше выражение.


fun <T2: Table> join(table2: T2): JoinClause<T, T2>

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


from(Employees)
    .join(Organizations).on { e, o -> o.id eq e.organizationId }
    .where { e, o -> e.organizationId eq 1 }
    .select { e, o -> e.id .. e.name .. o.name }

Управление данными


Data manipulation language — средство языка SQL, которое позволяет помимо запросов к таблицам осуществлять вставку, модификацию и удаление данных. Эти конструкции хорошо вписываются в нашу модель. Для поддержки update и delete нам понадобится всего-лишь дополнить выражения from и where вариантом с вызовом соответствующих методов. Для поддержки insert, введем дополнительную функцию into.


from(Employees)
    .where { e -> e.id eq 1 }
    .update { e -> e.name("John Doe") }

from(Employees)
    .where { e -> e.id eq 0 }
    .delete()

into(Employees)
    .insert { e -> e.name("John Doe") .. e.organizationId(1) }

Описание данных


SQL работает со структурированными данными в виде таблиц. Таблицы требуют описания перед началом работы с ними. Эта часть языка называется Data definition language.


Операторы CREATE TABLE и DROP TABLE реализованы аналогично — функция over будет служить стартовой точкой.


over(Employees)
    .create {
        integer(it.id).primaryKey(autoIncrement = true)..
        text(it.name).unique().notNull()..
        integer(it.organizationId).foreignKey(references = Organizations.id)
    }

over(Employees).drop()

Вы можете помочь и перевести немного средств на развитие сайта

Теги:



Комментарии (49):

  1. ov7a
    /#18791081

    Как-то чужеродно выглядит range-оператор .. в этом dsl. И eq тоже режет глаз.

    • x2bool
      /#18791217 / -1

      Альтернатива range это что-нибудь типа listOf, очень многословно получится. Что касается eq, то переопределить == не получится, т.к. он обязан вернуть Boolean, а для нужд библиотеки подойдет только внутренний тип Expression.

      • ov7a
        /#18791527

        Предпосылки-то понятны, но не уверен, что это самый лучший вариант. Какие варианты, кстати, вообще рассматривались?

        • x2bool
          /#18792021

          Можно в методе прямо прописать, можно строкой прямо. Не уверен, что это чем-то лучше. Есть другие идеи?

      • speshuric
        /#18791695

        Зато можно инфиксную == в backticks определить. В JVM, но не js, правда


            val c = A() `==` A()
        
            class A{
                infix fun `==` (b: A) : A {
                    return A()
                }    
            }

        • x2bool
          /#18792071 / -1

          Можно. Но нужно ли? Как по мне, так eq лаконичнее, плюс там все операторы придется так делать. Еще я не уверен, что DEX позволит такое имя, не хотелось бы исключать Android.

  2. Beholder
    /#18791623 / +1

    Ну и почему тогда не упомянули фирменный фреймворк Exposed от самих JetBrains?

    • x2bool
      /#18791983

      Exposed больше ORM и только для JDBC. А я, в основном, на Android использую.

  3. shaggyone
    /#18791973

    Возможно ли с использованием данного DSL построить запрос, в котором количество join'ов зависит от внешних условий?

    • x2bool
      /#18792001

      Условные джоины вполне возможны:
      1) Делаете базовый запрос
      2) Дополняете джоинами по условию, в зависимости от ваших потребностей

      • shaggyone
        /#18792061

        «Я не настоящий сварщик», с Kotlin не работал.

        Поясню, что я имею ввиду.

        Вот такое как правильно написать?

        x = from(Employees)
            .join(Organizations).on { e, o -> o.id eq e.organizationId }
            .where { e, o -> e.organizationId eq 1 }
            .select { e, o -> e.id .. e.name .. o.name }
        if <some external condition>
          x = x.join(Countries).on { e, o, c  -> e.country_id = c.id } # Уже 3 переменные в роли алиасов
        end if
        if <some other external condition>
          x = x.join(Users).on { e, o, c, u  -> e.user_id = u.id } # Уже 3 либо 4 переменные в роли алиасов 
        end if 
        

        • x2bool
          /#18792121

          Так — нет. Можно, что-то такое:


          x = from(Employees)
              .join(Organizations).on { e, o -> o.id eq e.organizationId }
          
          if (some external condition)
            y = x.join(Countries).on { e, o, c  -> e.country_id = c.id } # Уже 3 переменные в роли алиасов
                 .where { e, o, c -> e.organizationId eq 1 }
                 .select { e, o, c -> e.id .. e.name .. o.name }
          else
            y = x.where { e, o -> e.organizationId eq 1 }
                 .select { e, o -> e.id .. e.name .. o.name }

          • shaggyone
            /#18792167

            Дублирование этого кода — сильно не комильфо.

            .where { e, o, c -> e.organizationId eq 1 }
            .select { e, o, c -> e.id .. e.name .. o.name }
            


            я так понимаю это ограничение языка?

            • x2bool
              /#18792249

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

              • shaggyone
                /#18792371

                Я бы подумал на тему явного объявления алиасов при построении запросов.

                У меня примерно такие мысли появились:

                # Declare aliases first
                val e = generate_alias
                val o = generate_alias
                val c = generate_alias
                val u = generate_alias
                
                # Bind e to Employee
                x = from(Employees, e)
                # bind o to Organizations
                x = x.join(Organizations, o).on { |o| -> o.id eq e.organizationId }
                
                # Pick columns from employees table
                x = x.select { e.id .. e.name }
                # Add column from organizations table
                x = x.select { o.name }
                
                if some_condition
                  # Join countries and add countries.country_name to select
                  x = x.join(Countries, c).on { |c| e.country_id = c.id }
                          .select { c.country_name }
                
                if another_condition
                  # Join countries and add users.user_name to select
                  x = x.join(Users, u).on { |u| e.user_id = u.id }
                          .select { u.user_name }
                


                x.where { e.name eq… }

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

                Кстати, как и сами выражения on. Если язык позволяет, я бы их объединил с join.

  4. zindur2
    /#18792213

    Вот не вижу смысла, зачем прослойка между девелопером и SQL?
    SQL — не такой трудный. Потом не известно что ещё накомпайлит этот Kotlin потом ищи почему перформас упал потому что Kotlin скомпилировал неефективный запрос

    • x2bool
      /#18792237

      Либа ничего не "накомпайлит". Запросы транслируются один в один.

      • zindur2
        /#18792267

        наверное неправильно выразился :) это и хотел сказать — что «накомпайлит»/згенерирует не-ефективный запрос

        • Neikist
          /#18792457 / +1

          Видимо снова не так выразились) Запрос же пишет разработчик. Как напишет так и будет, если конечно нет каких то ляпов в библиотеке.

    • Neikist
      /#18792255 / +1

      Это как строгая статическая vs слабая динамическая типизация. Проверки во время компиляции радуют. Плюс подозреваю что автокомплит и подсказки на таком коде работать будут, в отличие от SQL.

      • bano-notit
        /#18792909

        У JB есть автокомплит в SQL)

        • Neikist
          /#18792917

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

          • bano-notit
            /#18792943

            Эмм… Нет. К сожалению с конкатенацией оно работает плохо. Причём на всех встраиваемых в строки языках. Но если использовать

            ` .. ${some exp} .. `
            из JS, то работает вполне себе сносно.

  5. mwizard
    /#18793583

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

    • x2bool
      /#18793631

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


      С этим фактом, кстати, связаны множество проблем автокомплита SQL в IDE: невозможно дать подсказку по колонкам таблицы без информации о том, из какой таблицы будет выборка.

      • Neikist
        /#18794153

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

    • speshuric
      /#18794429 / +1

      Спорно. Это вечная проблема, когда пишешь SELECT, а автокомплиту нечего тебе предложить, потому что он еще не знает ни таблиц, ни алиасов, ни полей. И когда запросы большие, видишьti.value, и, мотая вниз, думаешь "это, блин, вообще из какой таблицы???". Чаще всего сначала пишешь select * from или select 1 from, потом лепишь "tablesource" при помощи join/apply/where, а потом возвращаешься к списку полей.
      В том же linq также пошли.

      • mwizard
        /#18794745

        В таком случае, что-то наподобие `SELECT FROM table1, table2 COLUMNS foo, table1.bar AS bar1, table2.bar AS bar2`… было бы логичнее.

        • speshuric
          /#18794811

          Ну тогда слово SELECT становится мусорным и видим то что видим в этой статье или LINQ.
          А вообще, черт его знает, что логичнее :). Весь SQL — сборище исторических нелогичных костылей за 50 лет. Точнее продукт эволюции computer science, костылей для обхода текущих возможностей железа, костылей для обхода текущих кривостей реализаии, перетягиваний одеяла между вендорами СУБД и необходимости как-то работать с данными. Дедушка Дейт, вон, тоже постоянно ворчит, что SQL кривым получился.

          • mwizard
            /#18794813

            А как же UPDATE, INSERT, DELETE?..

            • speshuric
              /#18794837

              А что они? У них таблица в начале обозначена. Там, конечно, может для update/delete для некоторых СУБД и алиас быть, но хоть какой-то референс.

  6. 0x1000000
    /#18793913

    А для C# что-нибудь подобное есть?

    • Dansoid
      /#18794433 / +1

      Да и очень давно: LinqToDB
      То что здесь описано лишь одна сотая часть того что может LinqToDB. Уже и не припомню когда голые SQL писал.

    • speshuric
      /#18794435 / -1

      Пусть он не моден, но… Linq?

      • 0x1000000
        /#18795639

        Если вы имеет в виду Linq to SQL то это не совсем полноценный аналог, поскольку в нем не строится в явном виде синтаксическое дерево SQL запросов. Linq пытается преобразовать выражения на языке C# в SQL запрос, что не дает полноценного контроля над результатом.

        • Dansoid
          /#18798283

          Можете уточнить что вы имеете ввиду под «полноценным контролем над результатом»?

          • 0x1000000
            /#18799981

            Допустим мне нужен LEFT JOIN. С билдером синтаксического дерева, я вызываю функцию “LeftJoin”. В случае LINQ я лезу в гугл и нахожу, что именно вот такая последовательность вызовов будет интерпретирована как Left Join:
            join p in products on c.Category equals p.Category into ps
            from p in ps.DefaultIfEmpty()

            Идея в том, что нет однозначного отображения из LINQ в SQL

  7. speshuric
    /#18794681 / +1

    x2bool, этот комментарий будет достаточно резкий, но досмотрите его до конца, пожалуйста.
    У вас и концепция, и статья, и код получились неудачными. Я отмечу только то, что в глаза бросилось, потому что иначе комментарий будет длиннее статьи.


    1. В статье, например, синтаксические диаграммы некорректные и бессмысленные.
    2. Код абсолютно небезопасный и не продуман с точки зрения надёжности: даже прямые включения строк в SQL (привет, injection).
    3. DDL непонятно когда и непонятно как вызывается. В том смысле, что если есть таблица в БД, то что, её при следующем запуске снова создавать?
    4. Запросы возможные только совсем-совсем примитивные. Не верите? Берите какой-нибудь http://sql-ex.ru/, нарешайте там штук 20-30 примеров (это несложно) и попробуйте воспроизвести.
    5. Ваша модель диалектов не позволяет учесть даже базовых различий между СУБД.
    6. Код на котлине написан "не по-котлински". Совсем не DRY, с кучей явных лишних обработок null. То есть вот просто каждый файл проекта надо брать и почти полностью переписывать.
    7. Не учтена архитектура предшественников. Тот же linq для начала, ну и ORM типа Hibernate/NHibernate.
    8. Конечная цель — проверка на этапе компиляции — не достигнута (даже автоинкремент в рантайме проверяется), а где достигнута, то это явным хардкодом типов полей.

    На самом деле это всё косметика. Главная проблема — задача просто невообразимо сложнее, чем те приемы, которыми вы её пытаетесь решить. Там прямо в каждой маленькой детали нюансов больше, чем весь проект на текущий момент. С этим подходом не то что до промышленного, до учебного качества проект не довести.


    НО.


    1. Вы сделали прототип. Это важная стадия до которой не добирается и 10% идей, наверное. Этот прототип может быть на выброс, но он компилируется и показывает, куда вы хотите идти.
    2. Вы правильно сделали, что вынесли прототип на обсуждение. Местами неприятный, но единственный способ получить обратную связь и посмотреть на решение "снаружи". Каждый час, потраченный на то, чтобы идти в неправильную сторону — это в итоге несколько часов потраченных зря.
    3. Вы правильно заметили, что тулинг между kotlin и db далёк от совершенства. Эту тему есть куда развивать.

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

    • x2bool
      /#18794857

      Спасибо за развернутый комментарий. Приятно, когда вникают в суть статьи. Теперь по пунктам:


      1) хм???
      2) Абсолютно верно. Это вообще решается плэйсхолдерами и передачей аргументов в prepared statements, т.е. хэндлиться должно уровнем ниже. Думаю стоит этот момент задокуметировать, чтобы не сбивать с толку людей.
      3) Никакой магии не происходит "за кадром". Вызывать DDL нужно руками. Библиотека не является средством миграции или ORM. Сейчас цель сделать этакий билдер для SQL запросов.
      4) Правда. Как доберусь до подзапросов, будет немного лучше.
      5) Очень может быть. Библиотека в продакшене используется только на Android (SQLite). Может, я что-то упустил из других диалектов.
      6) Очень может быть
      7) Не совсем понял. С LINQ я "на ты", т.к. по основному роду деятельности я дотнетчик.
      8) Согласен. Полностью проверить запросы на этапе компиляции не получится, но можно добиться некоторого улучшения по сравнению с SQL. В рантайме автоинкремент не проверяется.


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

      • rjhdby
        /#18796561 / +1

        Полностью проверить запросы на этапе компиляции не получится

        Собственно стандартный JDBC


        stmt.setInt(1, someInt)

        И ничего, кроме Int'а туда не передашь. Проверяется на этапе компиляции.


        Собственно вам нужно:


        1. Не строку генерить, а PreparedStatement.
        2. На этапе .where { e -> e.id eq 1 } добавлять не "1", а "?". Запоминать индекс, тип и значение. Это довольно не сложно реализовывается, даже для IN (...)
        3. На конечном этапе совершить подстановку всех параметров

        • x2bool
          /#18797049

          А это идея! Можно сделать методы расширения для конкретных реализаций типа JDBC для преобразования в prepared statement вместо строк. Благодарю за наводку.

  8. Tishka17
    /#18798445

    А можно реализовать запрос такого типа (могу ошибиться с синтаксисом, но хочу донести идею)


    INSERT INTO table1 (field1) VALUES (SELECT MAX(field2)*2 FROM TABLE 2 WHERE somefield IN ("Q", "W", "E") GROUP BY someotherfield) 

    • x2bool
      /#18798473

      Это частный случай подзапроса. Подзапросы еще не готовы, но первые в очереди на реализацию. Будет.

      • Tishka17
        /#18798819

        А функции, case и прочее? Я, конечно, утрирую, но все же


        CASE 
          WHEN (LENGTH(SUBSTR(TRIM(field1), 1, 5)) + INSTR(LOWER(field2), field3) + field4) / 2 < field5: "foo",
          WHEN field6 > random(): "bar"
          ELSE: "baz"
        END

        • x2bool
          /#18799335

          Функции на подходе. Условные выражения тоже вполне реализуемы.