Standard library · Databases

db/oxisql

Imported as import "db/oxisql" as oxisql, its names are then oxisql.…. Every signature below is the one the checker infers.

Functions

fn sql_connect(host: Str, port: Int) -> Result(Int, Str)

# not run
import "db/oxisql"
fd = sql_connect("127.0.0.1", 7411)?

fn sql_hang_up(fd: Int) -> Unit

# not run
import "db/oxisql"
sql_hang_up(fd)

fn sql_request(fields: List(Field)) -> Json

import "db/oxisql"
sql_request(Cons(Field("sql", JStr("SELECT 1")), Nil))   # => JObj(Cons(Field(engine, JStr(sql)), Cons(Field(cmd, JStr(sql)), Cons(Field(sql, JStr(SELECT 1)), Nil))))

fn sql_cmd(name: Str, fields: List(Field)) -> Json

import "db/oxisql"
sql_cmd("backup", Nil)   # => JObj(Cons(Field(engine, JStr(sql)), Cons(Field(cmd, JStr(backup)), Nil)))

fn sql_prepare(fd: Int, text: Str) -> Result(Int, Str)

# not run
import "db/oxisql"
id = sql_prepare(fd, "SELECT * FROM people WHERE age > ?")?
sql_exec(fd, id, Cons(JInt(30), Nil))
sql_close(fd, id)

fn sql_exec(fd: Int, id: Int, params: List(Json)) -> Result(Json, Str)

# not run
import "db/oxisql"
sql_exec(fd, id, Cons(JInt(30), Nil))   # the prepared statement, with its parameters

fn sql_close(fd: Int, id: Int) -> Result(Json, Str)

# not run
import "db/oxisql"
sql_close(fd, id)

fn sql_run(fd: Int, text: Str) -> Result(Json, Str)

# not run
import "db/oxisql"
sql_run(fd, "SELECT 1; SELECT 2")   # Ok(one result per statement)

fn sql_run_with(fd: Int, text: Str, params: List(Json)) -> Result(Json, Str)

? in the text takes the next value from params, which is how a value reaches the engine without being written into the statement.

# not run
import "db/oxisql"
sql_run_with(fd, "SELECT * FROM people WHERE name = ?", Cons(JStr("ada"), Nil))

fn sql_backup(fd: Int, path: Str) -> Result(Json, Str)

Admin-only, and gated as such before the request reaches the engine.

# not run
import "db/oxisql"
sql_backup(fd, "/backups/today.tar")

fn sql_restore(fd: Int, archive: Str, target: Str) -> Result(Json, Str)

# not run
import "db/oxisql"
sql_restore(fd, "/backups/today.tar", "/data")

fn sql_results(answer: Json) -> List(Json)

One result per statement sent.

import "db/oxisql"
len(sql_results(JArr(Cons(JObj(Nil), Cons(JObj(Nil), Nil)))))   # => 2

fn sql_first(answer: Json) -> Json

The common case is one statement, and a caller that sent one should not have to say so twice.

import "db/oxisql"
sql_first(JArr(Cons(JObj(Cons(Field("affected", JInt(1)), Nil)), Nil)))   # => JObj(Cons(Field(affected, JInt(1)), Nil))
sql_first(JArr(Nil))                                                        # => JNull

fn sql_columns(result: Json) -> List(Str)

import "db/oxisql"
sql_columns(JObj(Cons(Field("columns", JArr(Cons(JStr("name"), Cons(JStr("age"), Nil)))), Nil)))   # => Cons(name, Cons(age, Nil))

fn sql_types(result: Json) -> List(Str)

A column's declared type — INT, TEXT, DOUBLE — or the empty string where the engine had none to give, as for a bare NULL in a select list.

import "db/oxisql"
sql_types(JObj(Cons(Field("types", JArr(Cons(JStr("TEXT"), Nil))), Nil)))   # => Cons(TEXT, Nil)

fn sql_rows(result: Json) -> List(Json)

import "db/oxisql"
sql_rows(JObj(Cons(Field("rows", JArr(Cons(JArr(Cons(JInt(1), Nil)), Nil))), Nil)))   # => Cons(JArr(Cons(JInt(1), Nil)), Nil)

fn sql_affected(result: Json) -> Int

import "db/oxisql"
sql_affected(JObj(Cons(Field("affected", JInt(2)), Nil)))   # => 2

fn sql_is_ddl(result: Json) -> Bool

import "db/oxisql"
sql_is_ddl(JObj(Cons(Field("ddl", JBool(true)), Nil)))   # => true
sql_is_ddl(JObj(Nil))                                     # => false

fn sql_at(result: Json, name: Str) -> Int

Where a named column sits in every row of this result, or -1. Worth holding on to across a loop rather than asking per row.

import "db/oxisql"
r = JObj(Cons(Field("columns", JArr(Cons(JStr("name"), Cons(JStr("age"), Nil)))), Nil))
sql_at(r, "age")    # => 1
sql_at(r, "nope")   # => -1

fn sql_cell(row: Json, i: Int) -> Json

import "db/oxisql"
sql_cell(JArr(Cons(JStr("ada"), Cons(JInt(36), Nil))), 1)   # => JInt(36)
sql_cell(JArr(Nil), -1)                                   # => JNull

fn sql_value(result: Json, row: Json, name: Str) -> Json

The value of a named column in one row. Fine for a handful; for a table use sql_at once and sql_cell per row.

import "db/oxisql"
r = JObj(Cons(Field("columns", JArr(Cons(JStr("name"), Cons(JStr("age"), Nil)))), Nil))
sql_value(r, JArr(Cons(JStr("ada"), Cons(JInt(36), Nil))), "name")   # => JStr(ada)