com.tianyisoft.database.grammar.Grammar.kt Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of querybuilder Show documentation
Show all versions of querybuilder Show documentation
A query builder from laravel
The newest version!
package com.tianyisoft.database.grammar
import com.tianyisoft.database.Builder
import com.tianyisoft.database.Expression
import com.tianyisoft.database.JoinClause
import com.tianyisoft.database.util.empty
import com.tianyisoft.database.util.flatten
import com.tianyisoft.database.util.readInstanceProperty
import kotlin.reflect.full.memberFunctions
import kotlin.reflect.jvm.isAccessible
open class Grammar {
open val operators = listOf()
protected var selectComponents = listOf(
"aggregate",
"columns",
"from",
"joins",
"wheres",
"groups",
"havings",
"orders",
"limit",
"offset"
)
open fun compileSelect(query: Builder): String {
if (query.unions.isNotEmpty() && query.aggregate.isNotEmpty()) {
return compileUnionAggregate(query)
}
val original = query.columns
if (query.columns.isEmpty()) {
query.columns = mutableListOf("*")
}
var sql = concatenate(
compileComponents(query)
)
if (query.unions.isNotEmpty()) {
sql = wrapUnion(sql) + " " + compileUnions(query)
}
query.columns = original
return sql
}
protected open fun compileUnionAggregate(query: Builder): String {
val sql = compileAggregate(query, query.aggregate)
query.aggregate.clear()
return "$sql from (${compileSelect(query)}) as ${wrapTable("temp_table")}"
}
protected open fun compileComponents(query: Builder): MutableMap {
val sql = mutableMapOf()
selectComponents.forEach { com ->
val value: Any? = readInstanceProperty(query, com)
if (!empty(value)) {
val method = "compile" + com.replaceFirstChar { it.uppercase() }
val function = this::class.memberFunctions.first { it.name == method }
function.isAccessible = true
sql[com] = function.call(this, query, value!!) as String
}
}
return sql
}
protected open fun compileAggregate(query: Builder, value: Map): String {
var column = columnize(value["columns"] as List<*>)
if (query.distinct && column != "*") {
column = "distinct $column"
}
return "select ${value["function"]}($column) as aggregate"
}
protected open fun compileColumns(query: Builder, value: Any): String {
value as List<*>
if (query.aggregate.isNotEmpty()) {
return ""
}
val select = if (query.distinct) {
"select distinct "
} else {
"select "
}
return select + columnize(value)
}
protected open fun compileFrom(query: Builder, value: Any): String {
return "from " + wrapTable(value)
}
protected open fun compileJoins(query: Builder, joins: List): String {
return joins.map { join ->
val table = wrapTable(join.table)
val nestedJoins = if (join.joins.isEmpty()) "" else " " + this.compileJoins(query, join.joins)
val tableAndNestedJoins = if (join.joins.isEmpty()) table else "($table$nestedJoins)"
"${join.type} join $tableAndNestedJoins ${compileWheres(join, 0)}".trim() // 这个 0 是乱写的，用不到
}.joinToString(" ")
}
protected open fun compileWheres(query: Builder, value: Any): String {
if (query.wheres.isEmpty()) {
return ""
}
val sql = compileWheresToList(query)
if (sql.isNotEmpty()) {
return concatenateWhereClauses(query, sql)
}
return ""
}
protected open fun compileLimit(query: Builder, value: Any): String {
return "limit " + value as Int
}
protected open fun compileOffset(query: Builder, value: Any): String {
return "offset " + value as Int
}
protected open fun compileWheresToList(query: Builder): List {
return query.wheres.map {
val method = "where" + it["type"]
val function = this::class.memberFunctions.first { f -> f.name == method }
function.isAccessible = true
it["boolean"].toString() + " " + function.call(this, query, it) as String
}
}
protected open fun concatenateWhereClauses(query: Builder, sql: List): String {
val conjunction = if(query is JoinClause) "on" else "where"
return conjunction + " " + removeLeadingBoolean(sql.joinToString(" "))
}
protected open fun removeLeadingBoolean(sql: String): String {
return sql.replaceFirst(Regex("and |or ", RegexOption.IGNORE_CASE), "")
}
protected open fun whereRaw(query: Builder, where: Map): String {
return where["sql"] as String
}
protected open fun whereBasic(query: Builder, where: Map): String {
return "${wrap(where["column"])} ${where["operator"]} ${parameter(where["value"])}"
}
protected open fun whereIn(query: Builder, where: Map): String {
val params = where["values"] as List<*>
if (params.isNotEmpty()) {
return "${wrap(where["column"])} in (${parameterize(params)})"
}
return "0 = 1"
}
protected open fun whereNotIn(query: Builder, where: Map): String {
val params = where["values"] as List<*>
if (params.isNotEmpty()) {
return "${wrap(where["column"])} not in (${parameterize(params)})"
}
return "1 = 1"
}
protected open fun whereNull(query: Builder, where: Map): String {
return "${wrap(where["column"])} is null"
}
protected open fun whereNotNull(query: Builder, where: Map): String {
return "${wrap(where["column"])} is not null"
}
protected open fun whereBetween(query: Builder, where: Map): String {
val between = if (where["not"] as Boolean) "not between" else "between"
val values = where["values"] as List
return "${wrap(where["column"])} $between ${parameter(values.first())} and ${parameter(values.last())}"
}
protected open fun whereBetweenColumns(query: Builder, where: Map): String {
val between = if (where["not"] as Boolean) "not between" else "between"
val values = where["values"] as List<*>
return "${wrap(where["columns"] as String)} $between ${wrap(values[0])} and ${wrap(values.last())}"
}
protected open fun whereDate(query: Builder, where: Map) = dateBasedWhere("date", query, where)
protected open fun whereTime(query: Builder, where: Map) = dateBasedWhere("time", query, where)
protected open fun whereDay(query: Builder, where: Map) = dateBasedWhere("day", query, where)
protected open fun whereMonth(query: Builder, where: Map) = dateBasedWhere("month", query, where)
protected open fun whereYear(query: Builder, where: Map) = dateBasedWhere("year", query, where)
protected open fun dateBasedWhere(format: String, query: Builder, where: Map): String {
if (where.containsKey("null_operator") && where["value"] == null) {
return "${wrap(where["column"])} ${if (where["null_operator"] == "Null") "is" else "is not"} null"
}
return "$format(${wrap(where["column"])}) ${where["operator"]} ${parameter(where["value"])}"
}
protected open fun whereColumn(query: Builder, where: Map): String {
return "${wrap(where["first"])} ${where["operator"]} ${wrap(where["second"])}"
}
protected open fun whereNested(query: Builder, where: Map): String {
val offset = if (query is JoinClause) 3 else 6
// 这个 0 是随便加的，为了凑参数个数
return "(${compileWheres(where["query"] as Builder, 0).substring(offset)})"
}
protected open fun whereSub(query: Builder, where: Map): String {
val select = compileSelect(where["query"] as Builder)
return "${wrap(where["column"])} ${where["operator"]} ($select)"
}
protected open fun whereExists(query: Builder, where: Map): String {
return "exists (${compileSelect(where["query"] as Builder)})"
}
protected open fun whereNotExists(query: Builder, where: Map): String {
return "not ${whereExists(query, where)}"
}
protected open fun compileGroups(query: Builder, groups: List): String {
return "group by " + columnize(groups)
}
protected open fun compileHavings(query: Builder, havings: List>): String {
val sql = havings.map { compileHaving(it) }.joinToString(" ")
return "having " + removeLeadingBoolean(sql)
}
protected open fun compileHaving(having: Map): String {
return when(having["type"]) {
"Raw" -> "${having["boolean"]} ${having["sql"]}"
"Between" -> compileHavingBetween(having)
"Null", "NotNull" -> compileHavingNull(having)
else -> compileBasicHaving(having)
}
}
protected open fun compileBasicHaving(having: Map): String {
return "${having["boolean"]} ${wrap(having["column"])} ${having["operator"]} ${parameter(having["value"])}"
}
protected open fun compileHavingNull(having: Map): String {
return "${having["boolean"]} ${wrap(having["column"])} ${if (having["type"] == "Null") "is" else "is not"} null"
}
protected open fun compileHavingBetween(having: Map): String {
val between = if (having["not"] as Boolean) "not between" else "between"
val values = having["value"] as List
return "${having["boolean"]} ${wrap(having["column"])} $between ${parameter(values.first())} and ${parameter(values.last())}"
}
protected open fun compileOrders(query: Builder, value: List>): String {
if (!empty(value)) {
return "order by " + compileOrdersToList(query, value).joinToString(", ")
}
return ""
}
protected open fun compileOrdersToList(query: Builder, orders: List>): List {
return orders.map {
if (it.containsKey("sql")) {
it["sql"] as String
} else {
"${wrap(it["column"])} ${it["direction"]}"
}
}
}
open fun compileRandom(seed: String): String {
return "RANDOM()"
}
protected open fun columnize(value: List<*>): String {
return value.map { wrap(it) }.joinToString(",")
}
protected open fun parameter(value: Any?): String {
return if (value is Expression) value.value else "?"
}
protected open fun parameterize(values: List): String{
return values.joinToString(", ") { parameter(it) }
}
open fun ifnull(sql: String, default: Any): String {
return "ifnull($sql, $default)"
}
open fun wrap(value: Any?): String? {
if (value == null) {
return null
}
if (value is Expression) {
return value.value
}
if (value !is String) {
return wrap(value.toString())
}
if (value.contains(" as ", true)) {
return wrapAliasedValue(value)
}
return wrapSegments(value.split("."))
}
protected open fun wrapAliasedValue(value: String): String {
val segments = value.split(Regex("\\s+as\\s+", RegexOption.IGNORE_CASE))
return wrap(segments[0]) + " as " + wrapValue(segments[1])
}
protected open fun wrapSegments(segments: List): String {
return segments.mapIndexed { index, s ->
if (index == 0 && segments.size > 1) {
wrapTable(s)
} else {
wrapValue(s)
}
}.joinToString(".")
}
open fun wrapTable(table: Any): String {
if (table is Expression) {
return table.value
}
return wrap(table)!!
}
protected open fun wrapValue(value: String): String {
if (value != "*") {
return "`$value`"
}
return value
}
protected open fun wrapUnion(sql: String): String {
return "($sql)"
}
protected open fun compileUnions(query: Builder): String {
var sql = ""
query.unions.forEach {
sql += compileUnion(it)
}
if (query.unionOrders.isNotEmpty()) {
sql += " " + compileOrders(query, query.unionOrders)
}
if (query.unionLimit != null) {
sql += " " + compileLimit(query, query.unionLimit!!)
}
if (query.unionOffset != null) {
sql += " " + compileOffset(query, query.unionOffset!!)
}
return sql.trimStart()
}
protected open fun compileUnion(union: Map): String {
val conjunction = if (union["all"] as Boolean) " union all " else " union "
return conjunction + wrapUnion((union["query"] as Builder).toSql())
}
protected open fun concatenate(segments: Map): String {
return segments.filter { it.value != "" }.values.joinToString(" ")
}
open fun compileExists(builder: Builder): String {
val select = compileSelect(builder)
return "select exists($select) as ${wrap("exists")}"
}
open fun compileInsertGetId(builder: Builder, values: Map, sequence: String?): String {
return compileInsert(builder, listOf(values))
}
open fun compileInsert(builder: Builder, values: List>): String {
val table = wrapTable(builder.from!!)
if (values.isEmpty()) {
return "insert into $table default values"
}
val columns = values.first().keys.sorted()
val parameters: String = values.joinToString(", ") { record ->
var sql = "("
sql += columns.joinToString(", ") { column ->
parameter(record[column])
}
"$sql)"
}
return "insert into $table (${columnize(columns)}) values $parameters"
}
open fun compileInsertOrIgnore(builder: Builder, values: List>): String {
throw NotImplementedError("not implemented")
}
open fun compileInsertUsing(builder: Builder, columns: List, sql: String): String {
return "insert into ${wrapTable(builder.from!!)} (${columnize(columns)}) $sql"
}
open fun compileUpdate(builder: Builder, values: Map): String {
val table = wrapTable(builder.from!!)
val columns = values.keys.sorted().joinToString(", ") { "${wrap(it)} = ${parameter(values[it])}" }
val where = compileWheres(builder, builder.wheres)
return if (builder.joins.isEmpty()) {
compileUpdateWithoutJoins(builder, table, columns, where)
} else {
compileUpdateWithJoins(builder, table, columns, where)
}.trim()
}
protected open fun compileUpdateWithoutJoins(builder: Builder, table: String, columns: String, where: String): String {
return "update $table set $columns $where"
}
protected open fun compileUpdateWithJoins(builder: Builder, table: String, columns: String, where: String): String {
val joins = compileJoins(builder, builder.joins)
return "update $table $joins set $columns $where"
}
open fun prepareBindingsForUpdate(bindings: Map>, values: Map): List {
val cleanBindings = bindings.toMap()
cleanBindings as MutableMap
cleanBindings.remove("select")
cleanBindings.remove("join")
val parameters = mutableListOf()
parameters.addAll(bindings["join"]!!)
values.keys.sorted().forEach {
parameters.add(values[it])
}
parameters.addAll(flatten(cleanBindings))
return parameters
}
open fun compileUpsert(builder: Builder, values: List>, update: Map, uniqueBy: List): String {
throw NotImplementedError("not implemented")
}
open fun compileDelete(builder: Builder): String {
val table = wrapTable(builder.from!!)
val where = compileWheres(builder, builder.wheres)
return if (builder.joins.isEmpty()) {
compileDeleteWithoutJoins(builder, table, where)
} else {
compileDeleteWithJoins(builder, table, where)
}
}
protected open fun compileDeleteWithJoins(builder: Builder, table: String, where: String): String {
val alias = table.split(" as ").last()
val joins = compileJoins(builder, builder.joins)
return "delete $alias from $table $joins $where"
}
protected open fun compileDeleteWithoutJoins(builder: Builder, table: String, where: String): String {
return "delete from $table $where"
}
open fun prepareBindingsForDelete(bindings: Map>): List {
val cleanBindings = bindings.toMap()
cleanBindings as MutableMap
cleanBindings.remove("select")
return flatten(cleanBindings)
}
open fun compileTruncate(builder: Builder): Map> {
return hashMapOf("truncate table ${wrapTable(builder.from!!)}" to listOf())
}
}