sql - 函数式编程和 SQL

Question

我想要简单的 FP 函数来从 mysql 中获取记录。

在 Node JS 中:

例如:

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

但是如果 where 很复杂或者我有一个 join 如何变得灵活。

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

在这种情况下，我想让 mysql 等效于:'"first_name + last_name = "John Smith"'如果我事先有条件，那就更容易了，但以这个为例:

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"

显然这里有大量的工作要做，但我们的想法是，一旦您拥有所有构建 block 和合适的 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