[WISH] simple form of evaluating statements with parameters

[Oldes]

When populating a database, I can see code patterns like:

stmt: sqlite/prepare db {
    INSERT INTO Authors (first_name, family_name, date_of_birth, date_of_death)
    VALUES (?,?,?,?)
}
sqlite/step/with stmt ["Patrick" "Rothfuss" "1973-06-06" none]
sqlite/step/with stmt ["Ben" "Bova" "1932-11-8" none]
sqlite/step/with stmt ["Isaac" "Asimov" "1920-01-02" "1992-04-06"]
sqlite/step/with stmt ["Bob" "Billings" none none]
sqlite/step/with stmt ["Jim" "Jones" "1971-12-16" none]
sqlite/finalize stmt

It is using the low level step command, but it could be simplified using a new command, which could look like:

sqlite/eval db [
    {INSERT INTO Authors (first_name, family_name, date_of_birth, date_of_death) VALUES (?,?,?,?)}
    ["Patrick" "Rothfuss" "1973-06-06"             ]
    ["Ben"     "Bova"     "1932-11-08"             ]
    ["Isaac"   "Asimov"   "1920-01-02" "1992-04-06"]
    ["Bob"     "Billings"                          ]
    ["Jim"     "Jones"    "1971-12-16"             ]
]

Using block for each record above. It may accept also flat data for multiple records like:

sqlite/eval db [
    "INSERT INTO Genres (name) VALUES (?)"
    "Fantasy" "Science Fiction" "French Poetry"
]

First value of the command's block could be string or prepared statement handle. In case of string the command would prepare and finalize the statement itself.

[Oldes]

@fvanzeveren: can you help me to decide, if command like:

sqlite/eval db "SELECT * FROM Genres"

should be processed using the sqlite_exec call, or using sqlite_prepare+sqlite_step+sqlite_finalize?

The difference is, that sqlite_exec can process multiple statements (like does current extension's exec command)... the disadvantage of it is, that only column names and text representation of values can be collected using a callback.

I almost think, that I would use the version with step.. like in case, when there are no parameters:

sqlite/eval db ["SELECT * FROM Genres"]

Because the multi-statement queries should not be used to collect any data anyway, because it is almost impossible to see, what SQL statement created the data:/

[fvanzeveren]

@Oldes I have read that sqlite_exec is a wrapper for

1. sqlite_prepare_v2
2. sqlite_step
3. sqlite_finalize I would avoid sqlite_exec and would use these 3 statements instead Also, there should be functions to manage transactions like sqlite/begin, sqlite/rollback and sqlite/commit. Finally, sqlite/last-insert-rowid could be interesting.

CHeers

François

[fvanzeveren]

@Oldes Could you build a windows version with this fix? Indeed, I am able to complile for linux and Haiku, but I am struggling to set-up the build environment on Windows. Windows is a pain when it comes to develop :/

Thank you.

[fvanzeveren]

Note: why a 'sqlite scheme? Why not 'sql, so that when other backends databases (MySQL, MariaDB, Oracle, etc...) will be available ;), we could have the same 'scheme to avoid impacting scripts.

[Oldes]

I have updated binaries available here with the current state: https://github.com/Siskin-framework/Rebol-SQLite/releases/tag/3.42.0.0

Btw... I use Github Actions to make releases: https://github.com/Siskin-framework/Builder/actions/runs/5270302675

[Oldes]

It will be easy to add sqlite/last-insert-id as there is a dedicated function for it. Meanwhile you can use: "SELECT last_insert_rowid();"

But there is no API for begin, rolback, commit etc.. you must use queries for it.

[Oldes]

Note: why a 'sqlite scheme? Why not 'sql, so that when other backends databases (MySQL, MariaDB, Oracle, etc...) will be available ;), we could have the same 'scheme to avoid impacting scripts.

As long as schemes will be compatible, it makes sense to have these separated.. you may have:

db: open sqlite:file.db

And or:

db: open mysql://localhost:9000

And then use in code scheme actions like insert db, read db etc... and don't care, if the db is sqlite or mysql.