From: John Cowan <xxxxxx@ccil.org>
Date: Thursday, September 19, 2019 2:46 PM

On Wed, Sep 18, 2019 at 10:32 PM <xxxxxx@ancell-ent.com> wrote:
(open-sql-database filename mode) -> db
mode is symbol: read, write, create

This is still SQLite specific. I've been thinking a more general open should return a "connection", since that's what it generally is, and you may find it useful to open more than one connection to a non-embedded database. For that matter, you can do the same with SQLite, can't you? Won't it do the right thing, while only allowing one writer or write at a time?

Thus filename should be something like connect-string, magic-string, whatever, it's content is by convention and for the lowest level of the stack to understand. mode should be optional and something like the SRFI-167 Ordered Key Value Store (OKVS) config, there an association list, which for SQLite would have one item, 'mode, being 'read, 'write, or 'create.

Fixed. However, I have added a database-type symbol that specifies which driver to use.

Or that could be part of the plist that Lassi strongly suggests we use instead of a connect string. Which would also address my config desire.

Are the following uses of timespecs intervals, instead of a point in time since an epoch? The latter is the only thing they're currently defined to be in SRFI-174.

Well, the epoch begins when the database call begins. Yes, it's hacky.

Ugh. Or we could *define* proper intervals, why embed hacks into our APIs if we don't have to?

Would we want intervals to be a separate type than timespecs?? Which themselves aren't isn't necessarily a disjoint type, i.e. a pair will do.

[...]

(sql-in-transaction db thunk) -> what thunk returns
When proc returns, roll back unless sql-commit has been called.

Why not assume success and commit on the completion of the thunk, and require an explicit rollback? That's what the PostgreSQL egg does, and what I'm planning on doing for my DBI. See this modification of an example supplied by Peter Bex:

I can't make up my mind what's best here. Can you provide some arguments?

Smaller API surface
Code is more concise, closing paren of with-transaction instead of a call to a commit procedure
When would you commit other than at the end of a with-transaction?
Normal flow of control assumes success which is normal, the rollback procedure acts like an exception (may well be implemented as one).
This is almost how the Clojure Korma DSL does it ^_^. But seriously, it works very well.

Let me again quote the slightly modified Peter Bex example:

(with-transaction db
(lambda ()
    (query db "INSERT INTO foo (bar, qux) VALUES (1, 2)")

    (with-transaction
      (lambda ()
        (query db "INSERT INTO foo (bar, qux) VALUES (3, 4)")
        ;; Oops, decide to roll this back
        (rollback)))

    (query db "INSERT INTO foo (bar, qux) VALUES (5, 6)")))

That is a nice code pattern. Here's an example from the Korma website:

(transaction
(if-not (valid?)
    (rollback)
    (do-complicated-query))
(when-not (is-rollback?)
    (println "success!")))

In that case, the rollback would seem to just be a call to the database and setting internal state. Simpler to implement....

Per the above, I don't think this is needed. Rollback is definitely needed, and if you make commit the default, you can reduce the API surface by remove this manual commit.

I think it's better to allow being explicit.

Repeating, when would you issue a commit other than as the last procedure called in a with-transaction?

Whereas a rollback can happen at any time you realize "oops!!!"

(sql-rollback db) -> unspecified

Per the above, a call to the rollback procedure gets you to the containing with-transaction, which already has db.

You might be inside transactions on two connections at the same time, as when you are copying data from one db to another.

80/20 rule? We shouldn't make all transactions ugly if we can figure out something to make this use case work.

But this requires though. Maybe blast out of all transactions? Maybe require in such a case naming which transaction you're rolling back, which would also roll back a transaction that's in the way??

(sql-statement db code bindings) -> statement
It is an error to mutate either code or bindings; this enables caching of either compiled but unbound statements, fully bound statements, or both.

I've been thinking that while there should be a sane default, you ought to be able to provide a perhaps per-database type (SQLite, PostgreSQL, etc.) hint, suggestion, or order to prepare the supplied statement.

I don't understand. I think what you are saying is what this is already meant to do. This is preparation as part of the per-db API, as opposed to the SQL PREPARE statement.

Let me get back to you on this after a do a bit more study and work; I'm at the stage where I've got enough of a goal at the top of my DBI that I'm now going to work from the bottom up towards it, cutting code for the usual reasons.

I assume the specifics of code and bindings are still up in the air based on the in progress discussions?

Well, code is meant to be SQL, at least so far, possibly with question marks, since named parameters are so disappointing.

I've just found that the wide column Apache Cassandra and graph Neo4j databases have very suitable query languages to support, or rather, to see if we can not get in the way of. They're fairly popular, have what are supposed to be good C libraries, don't have SQL front ends, and aren't insanely complicated in several dimensions like Elasticsearch.

There's also a serious ISO/IEC Graph Query Language effort standard in progress, according to the Neo4j people it's taking a lot from their Cypher query language: https://neo4j.com/blog/gql-standard-query-language-property-graphs/ "[...] it is highly likely that some reasonably complete draft will have been created by the second half of 2020".

(I'm glad Scheme standards work doesn't require traveling....)

(sql-exec db statement) -> implementation-dependent
Use this procedure when statement does not return a result set.
(sql-result-set db statement thunk) -> whatever thunk returns
Executes statement and calls thunk, passing a result-set object.

And here you ought to be able to provide a hint, suggestion or order to get the full result set, or play the cursor game underneath the API.

If you want to process the whole result set, use sql-for-each or sql-map->list. APIs providing access to the whole result set (apparently all except SQLite) can use their bulk API here.

I need to review all this from the top down, it's been a very long time since I used cursors. But for now, do you mean the statement is not actually executed until you get data from the result-set object, or the results are held in suspense until you either ask for the whole set or use a cursor?

[ Null is 'null, "which is what consensus JSON parsers use." ]

[...]

One thought I've had is that to the extent you can't introspect this information, the API can provide a facility for keeping a (hopefully in-sync) copy of what [ column type data is ] missing.

You should be able to read it from some database-type-dependent table(s). In SQLite it's sqlite_master.

I would think so. I would sincerely hope so. But Peter Bex warned us about this in general.

[...]

- Harold