Skip to content

Instantly share code, notes, and snippets.

@hikaMaeng

hikaMaeng/sql_ChSql.kt

Created July 22, 2019 06:36
Show Gist options
  • Save hikaMaeng/adf5445db3424c8645b99616a1675bfa to your computer and use it in GitHub Desktop.
Save hikaMaeng/adf5445db3424c8645b99616a1675bfa to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sql_ChSql.kt
package chela.kotlinJS.sql
import chela.kotlinJS.sql.DBType.localStorage
object ChSql{
private val queries = mutableMapOf<String, Query>()
private val Dbs = mutableMapOf<String, DataBase>()
val mode = localStorage
fun getDb(k:String) = Dbs[k] ?: throw Throwable("invalid getDb $k")
fun addDb(k:String, vararg create:String){
if(Dbs.containsKey(k)) throw Throwable("exist getDb:$k")
Dbs[k] = when(mode){
localStorage -> LocalDataBase(k, 1, create)
}
}
fun removeDb(k:String) = Dbs[k]?.remove()
fun getQuery(key:String) = queries[key]
fun addQuery(k:String, body:String){if(k.isNotBlank()) queries[k] = Query.parse(body)}
fun removeQuery(k:String) = queries.remove(k)
}
Raw
sql_Database.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS
abstract class DataBase(protected val db:String, protected val ver:Int, protected val create: Array<out String>){
internal abstract fun remove()
internal abstract fun select(r:dynamic, fields:List<String>?):Array<dynamic>?
internal abstract fun insert(r:dynamic):Array<dynamic>?
internal abstract fun delete(r: dynamic):Array<dynamic>?
internal abstract fun update(r: dynamic):Array<dynamic>?
fun query(k:String, vararg arg:Pair<String, Any>) = ChSql.getQuery(k)?.query(this, arg)
protected fun fieldZip(r:Array<dynamic>, fields:List<String>?) = fields?.let {f->
if(f[0] != "*") r.map{o->ChJS.obj{f.forEach{this[it] = o[it]}}}.toTypedArray() else null
} ?: r
}
Raw
sql_DBType.kt
package chela.kotlinJS.sql
enum class DBType{
localStorage
}
Raw
sql_Delete.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS.obj
class Delete(it:MatchResult): Query(){
@Suppress("UnsafeCastFromDynamic")
override val query = JSON.stringify(obj{
from = it.groupValues[1]
.replace(rWhere){
where = parseWhere(it.groupValues[1])
""
}.trim()
})
override fun exec(db: DataBase, r:dynamic) = db.delete(r)
}
/*
{
from:"Table_Name",
where:{
column1:{like:"a%"}, //%a|%a%|a%
column1:{in:[value1, value2, ...]},
column1:{'>':some_value}, //> | < | >= | <= | = | !=
column1:some_value,
or:{
Column2: some_another_value,
or:{
Column2: some_another_value
}
}
}
}
*/
Raw
sql_Insert.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS.obj
import chela.kotlinJS.core.ChJS.then
class Insert(it:MatchResult): Query(){
@Suppress("UnsafeCastFromDynamic")
override val query = JSON.stringify(obj{
val gv = it.groupValues
into = gv[1]
values = arrayOf(obj{
val v = rArrayItem.findAll(gv[3]).map{value(it.groupValues[1])}.toList()
rArrayItem.findAll(gv[2]).forEachIndexed{idx, it->
this[it.groupValues[1]] = v[idx]
}
})
})
override fun exec(db:DataBase, r:dynamic) = db.insert(r)
}
/*
{
into: "TABLE_NAME",
values: [{
column1:value,
column2:value
},{
column1:value,
column2:value
}]
}
*/
Raw
sql_LocalDataBase.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS
import chela.kotlinJS.core.ChJS.obj
import kotlin.browser.localStorage
class LocalDataBase internal constructor(db:String, ver:Int, create:Array<out String>):DataBase(db, ver, create){
companion object{
internal val isLocal by lazy{
js("(function(){return window['localStorage']?true:false})()") as Boolean
}
}
internal val key = "CHDB@$db.$ver"
private val tables = mutableMapOf<String, Table>()
private val info by lazy{
mutableMapOf<String, dynamic>().apply{
if(isLocal) localStorage.getItem(key)?.let{
ChJS.objForEach(JSON.parse(it)){k, v->this[k] = v}
}
}
}
init{
if(!isLocal || localStorage.getItem(key) == null) create.forEach{
val r = Query.create(it.trim())
if(r != null) createTable(r) else throw Throwable("invalid query:$it")
}
}
private fun getTable(tname:String) = tables[tname] ?: run {
if(!info.containsKey(tname)) throw Throwable("invalid table:$tname")
val t = Table(this, info[tname])
tables[tname] = t
t
}
private fun createTable(r:dynamic){
val tname = "${r.name}"
if(info.containsKey(tname)) return
info[tname] = r
if(isLocal) localStorage.setItem(key, JSON.stringify(obj{
info.forEach {(k, v)->this[k] = v}
}))
}
override fun remove() {
localStorage.getItem(key)?.let{
ChJS.objForEach(JSON.parse(it)){k, v->
(tables[k] ?: Table(this, v)).remove()
}
}
}
override fun insert(r:dynamic) = run{
getTable("${r.into}").insert(r.values[0])
null
}
override fun delete(r:dynamic) = run{
getTable("${r.from}").removeRecord(r.where)
null
}
override fun update(r:dynamic)= run{
val table = getTable("${r.`in`}")
table.select(r.where).forEach {
ChJS.objForEach(r.set){k, v->it[k] = v}
table.update(it)
}
null
}
override fun select(r:dynamic, field:List<String>?):Array<dynamic>?{
var fields:MutableList<String>? = field as? MutableList<String>
val table = getTable("${r.from}")
var rs = table.select(r.where)
if(rs.isEmpty()) return null
if(r.groupBy != undefined){
val group = rs.groupBy {it["${r.groupBy}"]}
if(r.aggregate != undefined){
val f = when {
r.aggregate.min != undefined -> {
val f = "${r.aggregate.min}"
rs = group.map{(k, v)->
val obj = v.sortedBy{it[f]}.take(1)[0]
obj.min = obj[f]
obj
}.toTypedArray()
"min"
}
r.aggregate.max != undefined -> {
val f = "${r.aggregate.max}"
rs = group.map{(k, v)->
val obj = v.sortedBy{it[f]}.takeLast(1)[0]
obj.max = obj[f]
obj
}.toTypedArray()
"max"
}
r.aggregate.count != undefined -> {
val f = "${r.aggregate.count}"
rs = group.map{(k, v)->
val obj = v[0]
obj.count = v.size
obj
}.toTypedArray()
"count"
}
r.aggregate.sum != undefined -> {
val f = "${r.aggregate.sum}"
rs = group.map{(k, v)->
val obj = v[0]
obj.sum = v.sumBy{it[f]}
obj
}.toTypedArray()
"sum"
}
r.aggregate.avg != undefined -> {
val f = "${r.aggregate.avg}"
rs = group.map{(k, v)->
val obj = v[0]
obj.avg = v.sumBy{it[f]}.toDouble() / v.size.toDouble()
obj
}.toTypedArray()
"avg"
}
else->throw Throwable("invaild aggregate")
}
fields?.add(f) ?: run{fields = mutableListOf(f)}
}
}else if(r.aggregate != undefined){
val f = when {
r.aggregate.min != undefined -> {
val f = "${r.aggregate.min}"
rs.sort { a, b ->
if(a[f] > b[f]) 1
else if(a[f] < b[f]) -1
else 0
}
rs = arrayOf(rs[0])
rs[0].min = rs[0][f]
"min"
}
r.aggregate.max != undefined -> {
val f = "${r.aggregate.max}"
rs.sort { a, b ->
if(a[f] > b[f]) -1
else if(a[f] < b[f]) 1
else 0
}
rs = arrayOf(rs[0])
rs[0].max = rs[0][f]
"max"
}
r.aggregate.count != undefined -> {
val f = "${r.aggregate.count}"
val size = rs.size
rs = arrayOf(rs[0])
rs[0].count = size
"count"
}
r.aggregate.sum != undefined -> {
val f = "${r.aggregate.sum}"
val sum = rs.sumBy{it[f]}
rs = arrayOf(rs[0])
rs[0].sum = sum
"sum"
}
r.aggregate.avg != undefined -> {
val f = "${r.aggregate.avg}"
val avg = rs.sumBy{it[f]}.toDouble() / rs.size.toDouble()
rs = arrayOf(rs[0])
rs[0].avg = avg
"avg"
}
else->throw Throwable("invaild aggregate")
}
fields?.add(f) ?: run{fields = mutableListOf(f)}
}
if(r.order != undefined){
val order = if(r.order.type == "asc") 1 else -1
val by = "${r.order.by}"
rs.sort{a, b->
val av = a[by]
val bv = b[by]
when {
av > bv -> 1
av < bv -> -1
else -> 0
} * order
}
}
if(r.skip != undefined) rs = rs.drop(r.skip).toTypedArray()
if(r.limit != undefined) rs = rs.take(r.limit).toTypedArray()
return fieldZip(rs, fields)
}
}
Raw
sql_Query.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS
abstract class Query{
companion object{
private val rCreate = """^\s*create +table(?: +if +not +exists)? +([a-zA-Z0-9_]+) *\(\s*((?:\s|\S)+)\)$""".toRegex(RegexOption.IGNORE_CASE)
private var rColumn = """\s*([a-zA-Z_]+)\s+([a-z0-9()]+)(?:\s+(.*)\s*)?""".toRegex(RegexOption.IGNORE_CASE)
private val rProp = """(not null)|(primary key)|(autoincrement)|(unique)|(default += +[a-zA-Z0-9])""".toRegex(RegexOption.IGNORE_CASE)
private val rNumType = """^(?:num|int|float|double)""".toRegex(RegexOption.IGNORE_CASE)
private val rBoolType = """^bool""".toRegex(RegexOption.IGNORE_CASE)
@Suppress("UnsafeCastFromDynamic")
internal fun create(q:String):dynamic = rCreate.find(q)?.let{
ChJS.obj {
name = it.groupValues[1]
columns = js("[]")
it.groupValues[2].split(",").forEachIndexed{idx, s ->
rColumn.find(s)?.let {
val v = it.groupValues
columns[idx] = ChJS.obj {
name = v[1]
//String, Object, Number, Boolean, Array, DateTime
dataType = when{
rNumType.matches(v[2]) -> "number"
rBoolType.matches(v[2]) -> "boolean"
else -> "string"
}
if(v[3] != "") rProp.findAll(v[3]).forEach {
val v = it.groupValues
if(v[1] != "") notNull = true
if(v[2] != "") primaryKey = true
if(v[3] != "") autoIncrement = true
if(v[4] != "") unique = true
if(v[5] != "") default = when(dataType){
"number" -> v[5].toInt(10)
"boolean" -> v[5] == "true"
else -> v[5]
}
}
}
}
}
}
} ?: null
internal val rLimit = """\s+limit\s+(.+)\s*$""".toRegex(RegexOption.IGNORE_CASE)
internal val rSkip = """\s+skip\s+(.+)\s*$""".toRegex(RegexOption.IGNORE_CASE)
internal val rOrder = """\sorder\s+by\s+([\S]+)(?:\s+(desc|asc))?\s*$""".toRegex(RegexOption.IGNORE_CASE)
internal val rGroup = """\sgroup\s+by\s+(.+?)\s*$""".toRegex(RegexOption.IGNORE_CASE)
internal val rAggregate = """(count|sum|avg|max|min)\(([^)]+)\)""".toRegex(RegexOption.IGNORE_CASE)
internal val rWhere = """\swhere\s+([\s\S]+?)\s*$""".toRegex(RegexOption.IGNORE_CASE)
internal val rArrayItem = """\s*((?:'[^']*')|(?:[^,]+))\s*(?:,|\s*$)""".toRegex()
private val rSelect = """^\s*select\s+([\s\S]+?)\s+from([\s\S]+?)\s*$""".toRegex(RegexOption.IGNORE_CASE)
private val rDelete = """^\s*delete\s+from([\s\S]+?)\s*$""".toRegex(RegexOption.IGNORE_CASE)
private val rInsert = """^\s*insert\s+into\s+([a-zA-Z0-9_]+)\s*\(\s*([\sa-zA-Z0-9_,]+)\s*\)\s*values\s*\(\s*([^)]+)\s*\)$""".toRegex(RegexOption.IGNORE_CASE)
private val rUpdate = """^\s*update\s+([a-zA-Z0-9_]+)\s+set\s+(.+)\s*$""".toRegex(RegexOption.IGNORE_CASE)
private val rItem = """@([^@]+)@""".toRegex()
private val rOr = """\s+or\s+""".toRegex(RegexOption.IGNORE_CASE)
private val rAnd = """\s+and\s+""".toRegex(RegexOption.IGNORE_CASE)
private val rWhereItem = """^\s*([0-9a-zA-Z_.]+)\s*(?:(?:(=|!=|[<>]=?)\s*(.+))|like\s+'(%?[^%]+%?)'|in\s*\(([^)]|\s)+\))\s*$""".toRegex()
fun parse(v:String) = rSelect.find(v)?.let{Select(it)} ?:
rInsert.find(v)?.let{Insert(it)} ?:
rUpdate.find(v)?.let{Update(it)} ?:
rDelete.find(v)?.let{Delete(it)} ?:
throw Throwable("invalid query:$v")
protected fun value(v:String) = when(v){
"true"->true
"false"->false
"null"->null
else->when(v[0]) {
'@' -> v
'\'' -> v.substring(1, v.length - 1)
else -> v.toDouble()
}
}
protected fun parseWhere(v:String?) = v?.let{
val obj = js("{}")
var prev:dynamic = null
it.split(rOr).forEach {
val target = js("{}")
if(obj.where == undefined) obj.where = target
else prev.or = target
prev = target
it.split(rAnd).forEach {
rWhereItem.findAll(it).forEach{
val gv = it.groupValues
target[gv[1]] = if(gv[2] != "") ChJS.obj{this[gv[2]] = value(gv[3])}
else if(gv[4] != "") ChJS.obj{like = gv[4]}
else if(gv[5] != "") ChJS.obj{
`in` = mutableListOf<Any?>().apply{
rArrayItem.findAll(gv[5]).forEach{this += value(it.groupValues[1])}
}.toTypedArray()
}
else throw Throwable("invalid value:$gv")
}
}
}
obj
}
}
protected abstract val query:String
internal fun query(db:DataBase, arg: Array<out Pair<String, Any>>) = run{
val args = arg.toMap()
val r = JSON.parse<dynamic>(query.replace(rItem){
val k = it.groupValues[1]
args[k]?.let{"$it"} ?: throw Throwable("invalid key: $k")
})
//println("select - ${JSON.stringify(r)}")
exec(db, r)
}
internal abstract fun exec(db:DataBase, r:dynamic):Array<dynamic>?
}
Raw
sql_Select.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS.obj
class Select(it:MatchResult): Query(){
private val fields = it.groupValues[1].trim().split(",").map{it.trim()}.filter{!rAggregate.matches(it)}
@Suppress("UnsafeCastFromDynamic")
override val query = JSON.stringify(obj{
rAggregate.find(it.groupValues[1])?.let{
aggregate = obj{
this[it.groupValues[1]] = it.groupValues[2]
}
}
from = it.groupValues[2]
.replace(rLimit){
limit = value(it.groupValues[1])
""
}
.replace(rSkip){
skip = value(it.groupValues[1])
""
}
.replace(rOrder){
order = obj {
by = it.groupValues[1]
type = if(it.groupValues[2] == "desc") "desc" else "asc"
}
""
}
.replace(rGroup){
groupBy = it.groupValues[1]
""
}.replace(rWhere){
where = parseWhere(it.groupValues[1])
""
}.trim()
})
override fun exec(db:DataBase, r:dynamic) = db.select(r, fields)
}
/*
{
from:"Table_Name",
aggregate:{min:Column_Name}, //count|sum|avg|max|min
where:{
column1:{like:"a%"}, //%a|%a%|a%
column1:{in:[value1, value2, ...]},
column1:{'>':some_value}, //> | < | >= | <= | = | !=
column1:some_value,
or:{
Column2: some_another_value,
or:{
Column2: some_another_value
}
}
},
order: {
by: column_name,
type:asc //asc|desc
},
groupBy:Column_Name,
skip: number,
limit: number
}
*/
Raw
sql_Table.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS
import chela.kotlinJS.sql.LocalDataBase.Companion.isLocal
import kotlin.browser.localStorage
/*
v = {
name:"tableName",
columns:{
name:{dataType:'int', primaryKey:true, autoIncrement: true, notNull: true}
},
}
*/
class Table(val db:LocalDataBase, v:dynamic){
private val key = "${db.key}/${v.name}"
private val pk:String
private val isAuto:Boolean
init{
var pkv = ""
var auto = false
ChJS.objAny(v.columns){k, v->
if(v.primaryKey == true){
pkv = v.name
auto = v.autoIncrement == true
true
}else false
}
pk = pkv
isAuto = auto
}
private var seed = 1
private val records = mutableMapOf<Any,dynamic>()
private var isInited = false
private fun init(){
if(isInited) return
isInited = true
if(isLocal) localStorage.getItem(key)?.let{
var max = 1
JSON.parse<dynamic>(it).unsafeCast<Array<dynamic>>().forEach{
if(isAuto && it[pk] > max) max = it[pk]
records[it[pk]] = it
}
if(isAuto) seed = max + 1
}
}
private fun save(){
if(isLocal) localStorage.setItem(key, "[" + records.values.joinToString(","){
JSON.stringify(it)
} + "]")
}
fun remove(){
records.clear()
if(isLocal) localStorage.removeItem(key)
}
fun select(where:dynamic):Array<dynamic>{
init()
var rs = records.values.toTypedArray()
if(where != undefined){
rs = rs.filter {record->
var isOk = true
var target = where
var i = 10
do {
var or:dynamic = null
ChJS.objForEach(target) { k, v ->
if (ChJS.isObj(v)) {
if (k == "or") or = v
else if(v[">"] != undefined) isOk = record[k] > v[">"]
else if(v["<"] != undefined) isOk = record[k] > v["<"]
else if(v["!="] != undefined) isOk = record[k] > v["!="]
else if(v[">="] != undefined) isOk = record[k] > v[">="]
else if(v["<="] != undefined) isOk = record[k] > v["<="]
else if(v["%like%"] != undefined) isOk = "${record[k]}".contains("${v["%like%"]}")
else if(v["%like"] != undefined) isOk = "${record[k]}".startsWith("${v["%like"]}")
else if(v["like%"] != undefined) isOk = "${record[k]}".endsWith("${v["like%"]}")
else if(v["in"] != undefined) isOk = v["in"].unsafeCast<Array<dynamic>>().any { record[k] == v }
} else if(record[k] != v) isOk = false
}
if(or == null) break
else target = or
}while(i-- > 0)
isOk
}.toTypedArray()
}
return rs
}
fun insert(v:dynamic){
init()
if(isAuto){
v[pk] = seed++
}
records[v[pk]] = v
save()
}
fun update(v:dynamic){
init()
records[v[pk]] = v
save()
}
fun removeRecord(where:dynamic){
select(where).forEach {records.remove(it[pk])}
save()
}
}
Raw
sql_Update.kt
package chela.kotlinJS.sql
import chela.kotlinJS.core.ChJS.obj
import chela.kotlinJS.core.ChJS.then
class Update(it:MatchResult):Query(){
@Suppress("UnsafeCastFromDynamic")
override val query = JSON.stringify(obj{
`in` = it.groupValues[1]
set = obj{
rArrayItem.findAll(it.groupValues[2].replace(rWhere){
where = parseWhere(it.groupValues[1])
""
}.trim()).forEach {
val (k, v) = it.groupValues[1].split('=').map{it.trim()}
this[k] = v
}
}
})
override fun exec(db: DataBase, r:dynamic) = db.update(r)
}
/*
{
in: "Table_Name",
set: {
column1: value1,
column2: value2
},
where: {
column3: some_value,
column4: some_another_value
}
}
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment