PostgreSQL for Bjolang: bjosql over Npgsql, for bjoroutines
Every operation suspends its fiber while it waits for the server, and the ambient cancellation token cancels the query. A Db is a pool; a transaction holds one connection and hands its function a Db bound to it, and a nested one is a savepoint. Each SqlValue is a typed parameter, and a value is read back by the PostgreSQL type of its column. The server's errors are DbError with its SQLSTATE, and a lost connection is DbError 08000. 🤖 Generated with [ECA](https://eca.dev) Co-Authored-By: eca-agent <git@eca.dev>
This commit is contained in:
commit
b7e9c8076f
8 changed files with 1391 additions and 0 deletions
180
Readme.org
Normal file
180
Readme.org
Normal file
|
|
@ -0,0 +1,180 @@
|
|||
* bjopg — PostgreSQL for Bjolang
|
||||
|
||||
The [[https://github.com/bjoli/bjosql][bjosql]] query language, running on PostgreSQL through
|
||||
[[https://www.npgsql.org][Npgsql]]. Every operation is a bjoroutine: a query suspends its fiber while it
|
||||
waits for the server, and does not hold a thread.
|
||||
|
||||
#+BEGIN_SRC scheme
|
||||
(import (std datetime))
|
||||
(import (std uuid))
|
||||
(import (bjosql core))
|
||||
(import (bjopg core))
|
||||
|
||||
(def/table aches Ache
|
||||
(id Uuid #:primary-key)
|
||||
(what string)
|
||||
(felt Instant)
|
||||
(severity int))
|
||||
|
||||
(defbjo (main args)
|
||||
(match (call-with-db "Host=localhost;Database=aches"
|
||||
(bjoroutine (db)
|
||||
(def (Ok _) (create-table db aches) :propagate)
|
||||
(def (Ok _) (insert db aches (Ache (id (uuid-v7)) (what "knee") (felt (now)) (severity 2))) :propagate)
|
||||
(fetch db aches '(select (where (severity (>= 2))) (order-by (desc felt))))))
|
||||
((Ok rows) (loop (:for a rows) (:do (println (record-ref a what)))) 0)
|
||||
((Err e) (println (sql-error-message e)) 1)))
|
||||
#+END_SRC
|
||||
|
||||
** Using it
|
||||
|
||||
#+BEGIN_SRC scheme
|
||||
(depends
|
||||
(package (name (bjopg))
|
||||
(version (version-at-least "0.1.0"))
|
||||
(source (git (url "https://rikspucko.koketteriet.se/bjoli/bjopg.git")))))
|
||||
#+END_SRC
|
||||
|
||||
~(bjosql)~ comes along with it. The package declares Npgsql, and ~bjo~
|
||||
restores it for any build that uses the package, so a program declares no NuGet
|
||||
packages of its own.
|
||||
|
||||
** Operations
|
||||
|
||||
Each operation is a bjoroutine, and returns a ~(Result SqlError ...)~.
|
||||
|
||||
| ~(open-db connection-string)~ | a pool of connections; see below |
|
||||
| ~(close-db db)~ | closes the pool and its connections |
|
||||
| ~(call-with-db connection-string f)~ | open, call ~f~, and close however ~f~ returns |
|
||||
| ~(create-table db t)~ | ~CREATE TABLE IF NOT EXISTS~ |
|
||||
| ~(insert db t row)~ | the row as stored, with the key the server chose for an ~#:auto~ column |
|
||||
| ~(insert-all db t rows)~ | all of the rows or none of them, in one transaction |
|
||||
| ~(fetch db t '(select ...))~ | the rows, read into the table's record |
|
||||
| ~(fetch-one db t '(select ...))~ | the first row, as an ~Option~ |
|
||||
| ~(fetch-rows db t '(select ...))~ | the rows as ~Row~ values, for a projection |
|
||||
| ~(execute db t '(update ...))~ | the number of rows changed; also for ~delete~ |
|
||||
| ~(query-sql db s)~ ~(execute-sql db s)~ | SQL written by hand, from ~sql~, with ~?~ for each value |
|
||||
| ~(with-transaction db f)~ | commits when ~f~ returns ~Ok~; rolls back on ~Err~ or on a throw |
|
||||
|
||||
The connection string is Npgsql's: ~"Host=localhost;Port=5432;Database=app;Username=ada;Password=..."~.
|
||||
~open-db~ makes one connection and gives it back, so a server that cannot be
|
||||
reached is an ~Err~ from ~open-db~ and not from the first query. A connection
|
||||
string that cannot be read is the program's mistake, and throws.
|
||||
|
||||
** The pool and transactions
|
||||
|
||||
A ~Db~ is a pool. Each operation takes a connection, runs, and gives it back, so
|
||||
any number of fibers can use one ~Db~ at the same time. Open one ~Db~ when the
|
||||
program starts, and use it everywhere.
|
||||
|
||||
~with-transaction~ holds one connection for its whole function, and gives the
|
||||
function a ~Db~ that runs everything on that connection. Use that ~Db~ inside
|
||||
the function, not the pool: a statement given to the pool runs on another
|
||||
connection, outside the transaction.
|
||||
|
||||
#+BEGIN_SRC scheme
|
||||
(with-transaction db
|
||||
(bjoroutine (tx)
|
||||
(def (Ok _) (execute tx accounts '(update (set (balance ,(- a 100))) (where (id (= 1))))) :propagate)
|
||||
(execute tx accounts '(update (set (balance ,(+ b 100))) (where (id (= 2)))))))
|
||||
#+END_SRC
|
||||
|
||||
A transaction inside a transaction is a savepoint. When it fails, it is rolled
|
||||
back, and the outer transaction goes on. PostgreSQL refuses every statement in
|
||||
a transaction after an error until it is rolled back; the savepoint is what
|
||||
makes the outer transaction usable again.
|
||||
|
||||
The ~Db~ of a transaction is one connection, so use it from one fiber at a time.
|
||||
|
||||
** Fibers and cancellation
|
||||
|
||||
A call suspends the fiber while it waits for the server. The ambient
|
||||
cancellation token is passed to Npgsql, so a cancelled scope cancels its query
|
||||
on the server. With bjoweb's ~#:cancel-on-disconnect #t~, a client that goes
|
||||
away cancels the query it was waiting for.
|
||||
|
||||
There is no blocking version of the operations yet. From code that is not a
|
||||
bjoroutine, use ~(sync/blocking (promise-join (bjo ...)))~.
|
||||
|
||||
** Errors
|
||||
|
||||
~(DbError code message)~ is PostgreSQL's SQLSTATE and message:
|
||||
|
||||
| ~"23505"~ | a unique violation |
|
||||
| ~"23503"~ | a foreign key violation |
|
||||
| ~"42601"~ | SQL the server cannot parse |
|
||||
| ~"3D000"~ | a database that does not exist |
|
||||
| ~"08000"~ | a server that cannot be reached, or a connection that was lost |
|
||||
|
||||
~"08000"~ is connection_exception, the class the SQL standard has for this. A
|
||||
lost connection is a ~DbError~ and not an exception, because a server on the
|
||||
network can go away while the program is correct. ~(DecodeError message)~ is a
|
||||
row that does not fit its record, and ~(BadQuery message)~ is a statement that
|
||||
cannot be written. Anything else is an exception.
|
||||
|
||||
** Types
|
||||
|
||||
| Bjolang | PostgreSQL | Sent as (.NET) |
|
||||
|-----------------+---------------+-------------------------------|
|
||||
| ~int~ | ~integer~ | ~long~ |
|
||||
| ~long~ | ~bigint~ | ~long~ |
|
||||
| ~double~ | ~double precision~ | ~double~ |
|
||||
| ~string~ | ~text~ | ~string~ |
|
||||
| ~bool~ | ~boolean~ | ~bool~ |
|
||||
| ~(Array byte)~ | ~bytea~ | ~byte[]~ |
|
||||
| ~Date~ | ~date~ | ~DateOnly~ |
|
||||
| ~Time~ | ~time~ | ~TimeOnly~ |
|
||||
| ~DateTime~ | ~timestamp~ | ~DateTime~, kind Unspecified |
|
||||
| ~Instant~ | ~timestamptz~ | ~DateTime~, kind Utc |
|
||||
| ~decimal~ | ~numeric~ | ~decimal~ |
|
||||
| ~Uuid~ | ~uuid~ | ~Guid~ |
|
||||
| ~JsonDoc~ | ~jsonb~ | ~string~, as ~jsonb~ |
|
||||
|
||||
Every value is a typed parameter, so no cast is written in the SQL. A value is
|
||||
read back by the PostgreSQL type of its column: a ~date~, a ~timestamp~ and a
|
||||
~timestamptz~ all arrive as a .NET ~DateTime~, and only the type name tells
|
||||
them apart.
|
||||
|
||||
A decimal is ~numeric~, so ~ORDER BY~, ~<~ and ~SUM~ on it are exact and
|
||||
numeric, as they are not in SQLite. ~jsonb~ stores JSON in a form of its own:
|
||||
the value is the same after a read, but the key order and the spaces can
|
||||
differ.
|
||||
|
||||
A type bjosql does not have, such as ~inet~, is a column with ~#:sql-type
|
||||
"INET"~ and a type whose ~ToSql~ answers ~(SqlOther "inet" text)~. It is sent as
|
||||
text with a cast, ~$1::inet~, and the server checks it. A column of a type
|
||||
with no case of its own is read as ~(SqlOther type-name text)~.
|
||||
|
||||
** A server for development
|
||||
|
||||
PostgreSQL does not need systemd. As your own user, without root:
|
||||
|
||||
#+BEGIN_SRC sh
|
||||
initdb -D ~/.local/share/postgres/dev -U "$USER" --auth=trust --encoding=UTF8 --locale=C.UTF-8
|
||||
echo "listen_addresses = 'localhost'" >> ~/.local/share/postgres/dev/postgresql.conf
|
||||
echo "unix_socket_directories = '/tmp'" >> ~/.local/share/postgres/dev/postgresql.conf
|
||||
pg_ctl -D ~/.local/share/postgres/dev -l ~/.local/share/postgres/dev.log start
|
||||
createdb -h localhost bjosql_test
|
||||
#+END_SRC
|
||||
|
||||
~--auth=trust~ lets every local connection in without a password, so the
|
||||
server only listens on localhost. ~pg_ctl ... stop~ stops it. A container that
|
||||
restarts does not start it again: run the ~start~ line.
|
||||
|
||||
** Tests
|
||||
|
||||
#+BEGIN_SRC sh
|
||||
BJOPG_TEST="Host=localhost;Database=bjosql_test" bjo run tests/pg.bjo
|
||||
#+END_SRC
|
||||
|
||||
Every line should start with "ok:". Without ~BJOPG_TEST~ the test says so and
|
||||
runs nothing. It makes tables named ~bjopg_...~, and drops them first and last.
|
||||
|
||||
It checks that each type is stored as PostgreSQL's own type and reads back
|
||||
equal; that ~ORDER BY~ and tests on an instant, a uuid and a decimal are those
|
||||
of the values; nulls in typed columns; the counts of updates and deletes; the
|
||||
SQLSTATE of a unique violation, of bad SQL, of a database that does not exist
|
||||
and of a server that is not there; transactions that commit, roll back on
|
||||
~Err~ and on a throw, and a savepoint that fails inside a transaction that then
|
||||
commits; a type of one's own sent as ~SqlOther~ with a cast; and 20 fibers
|
||||
querying one pool at once, which overlap rather than wait for each other.
|
||||
Loading…
Add table
Add a link
Reference in a new issue