功能编程和SQL

Question

我想拥有简单的fp函数以从mysql获取记录。

在节点JS中：

例如：

exec(select('users'), where({active: 1})) // SELECT * FROM users WHERE active = 1

但是，如果哪里很复杂，或者我有一个加入如何使灵活的。

exec(select('users'), where(condition))

在条件下，我想具有相当于：'" first_name last_name =" John Smith"'的MySQL等效如果我的条件在前

WHERE num_comments > STRLEN(first_name + last_name)

我不想通过SQL，因为我希望能够在MySQL，Postgres和Mongodb之间切换。

如果我传递了高阶功能，我应该首先获取所有用户并在nodejs中过滤。

这将非常减慢响应。

是否有解决此问题的解决方案？

Answer 1

您有一个非常广泛的问题，但是希望这能让您入门。我将从您的SQL查询的语义组件围绕一些基本包装器开始

const Field = (name) =>
  ({ type: Field, name })
const SqlLiteral = (value) =>
  ({ type: SqlLiteral, value })
const Condition = (operator, left, right) =>
  ({ type: Condition, operator, left, right })

然后您做一个表达式 -> sql扩展器

const toSql = (expr) =>
{
  switch (expr.type) {
    case Field:
      return expr.name
    case SqlLiteral:
      return JSON.stringify (expr.value) // just works for strings, numbers
    case Condition:
      return toSql (expr.left) + expr.operator + toSql (expr.right)
    default:
      throw Error (`Unhandled expression type: ${expr.type}`)
  }
}

测试一些表达式

toSql (Condition ("=", Field ("name"), SqlLiteral ("bruce")))
// name="bruce"
toSql (Condition (">", Field ("earnings"), Field ("expenses")))
// earnings>expenses
toSql (Condition (">", Field ("earnings"), SqlLiteral (100)))
// earnings>100

我们可以继续添加到此

const And = (left, right) =>
  ({ type: And, left, right })
const toSql = (expr) =>
{
  switch (expr.type) {
    case And:
      return toSql (expr.left) + " AND " + toSql (expr.right)
    ...
  }
}
toSql
  ( And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
        , Condition ("=", Field ("last"), SqlLiteral ("lee"))
        )
  )
// first="bruce" AND last="lee"

继续前进...我们可以支持像SQL函数这样的电话

const SqlFunc = (func, arg) =>
  ({ type: SqlFunc, func, arg })
const toSql = (expr) =>
{
  switch (expr.type) {
    case SqlFunc:
      return expr.func + "(" + toSql (expr.arg) + ")"
    ...
  }
}
toSql
  ( Condition ( "<"
              , SqlFunc ("strlen", Field ("name"))
              , SqlLiteral (10)
              )
  )
// strlen(name)<10

继续前进！

const Select = (from, ...fields) =>
  ({ type: Select, from, fields: fields.map(Field) })
const Table = (name) =>
  ({ type: Field, name })
const Where = (select, condition) =>
  ({ type: Where, select, condition })

const toSql = (expr) =>
{
  switch (expr.type) {
    case Select:
      return `SELECT ${expr.fields.map(toSql).join(',')} FROM ${toSql (expr.from)}`
    case Field:
    case Table:
      return expr.name
    case Where:
      return toSql (expr.select) + " WHERE " + toSql (expr.condition)
    ...
  }
}

现在让我们看看一个更高级的查询源

toSql
  ( Where ( Select ( Table ("people")
                   , "first"
                   , "last"
                   , "email"
                   , "age"
                   )
          , And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
                , Condition ("=", Field ("last"), SqlLiteral ("lee"))
                )
          )
  )
// SELECT first,last,email,age FROM people WHERE first="bruce" AND last="lee"

显然，这里有大量的工作要做，但是一个想法是，一旦您拥有所有的构建块和合适的toSql扩展器，您就可以将魔术包装纸围绕。

例如

const where = (descriptor = {}) =>
  Object.entries (descriptor)
    .map (([ k, v ]) =>
      Condition ("=", Field (k), SqlLiteral (v)))
    .reduce (And)
toSql (where ({ first: "bruce", last: "lee"}))
// first="bruce" AND last="lee"'

我对您的一般建议是不要从头开始，除非您想构建它是为了学习如何制作它的唯一目的。SQL非常复杂，还有无数其他项目以各种方式尝试了类似的事情。寻找他们如何处理更棘手的场景。

下面的完整程序演示

const Select = (from, ...fields) =>
  ({ type: Select, from, fields: fields.map(Field) })
const Table = (name) =>
  ({ type: Field, name })
  
const Field = (name) =>
  ({ type: Field, name })
  
const SqlLiteral = (value) =>
  ({ type: SqlLiteral, value })
  
const Condition = (operator, left, right) =>
  ({ type: Condition, operator, left, right })
  
const And = (left, right, ...more) =>
  more.length === 0
    ? ({ type: And, left, right })
    : And (left, And (right, ...more))
const Where = (select, condition) =>
  ({ type: Where, select, condition })
const SqlFunc = (func, arg) =>
  ({ type: SqlFunc, func, arg })
const toSql = (expr) =>
{
  switch (expr.type) {
    case Select:
      return `SELECT ${expr.fields.map(toSql).join(',')} FROM ${toSql (expr.from)}`
    case Field:
      return expr.name
    case Table:
      return expr.name
    case SqlLiteral:
      return JSON.stringify (expr.value) // just works for strings, numbers
    case SqlFunc:
      return expr.func + "(" + toSql (expr.arg) + ")"
    case Condition:
      return toSql (expr.left) + expr.operator + toSql (expr.right)
    case And:
      return toSql (expr.left) + " AND " + toSql (expr.right)
    case Where:
      return toSql (expr.select) + " WHERE " + toSql (expr.condition)
    default:
      throw Error (`Unhandled expression type: ${JSON.stringify(expr)}`)
  }
}
const sql =
  toSql(
    Where ( Select ( Table ("people")
                   , "first"
                   , "last"
                   , "email"
                   , "age"
                   )
          , And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
                , Condition ("=", Field ("last"), SqlLiteral ("lee"))
                , Condition ( ">"
                            , Field ("age")
                            , SqlLiteral (30)
                            )
                )
          )
  )
  
console.log (sql)
// SELECT first,last,email,age FROM people WHERE first="bruce" AND last="lee" AND age>30

Answer 2

您是否考虑过将其分解。它肯定会使它成为更快的查询，并且您可以索引姓氏。

SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'john'
exec(select('users'), where({last_name: 'smith', first_name: 'john'}))