Use sqlx as database

[Septias]

Finally get rid of surrealdb in favor of sqlx.

I've changed the structure of the db which was a struct before to a bare module which takes a sqliteConnection as an argument. This way the bot might utilize transactions later to rollback complex db changes in case of failure.

closes #2

[hpk42]

What's the benefit of using sqlx over rusqlite here?

IIRC @dignifiedquire spent many many hours trying to make sqlx work for core and it failed.

On Tue, Jun 06, 2023 at 11:32 -0700, Sebastian Klähn wrote:

Finally get rid of surrealdb in favor of sqlx. The code got a lot more verbose, because some special types needed on the bot structs don't implement the neccessary Traits for encoding and decoding (Encode<> and Decode<>). Possible workarounds are:

1. Adding sqlx with a feature flag to deltachat core to implement these traits.
2. Add some wrapper types which are trasformed to/from in the database which are easy to put into the db.

Both of them are fine I guess but the added verbosity isn't to bad either.

I've changed the structure of the db which was a struct before to a bare module which takes a sqliteConnection as an argument. This way the bot might utilize transactions later to rollback complex db changes in case of failure. You can view, comment on, or merge this pull request online at:

https://github.com/webxdc/appstore-bot/pull/29

-- Commit Summary --

• clean db
• add sqlx and fix some deps
• insert new db
• adjust frontend
• fix rebase
• automatically create testers, publishers, genesis

-- File Changes --

A .env (1)
M Cargo.lock (1118)
M Cargo.toml (12)
A frontend/src/bindings/AppInfo.ts (3)
M frontend/src/bindings/DownloadResponse.ts (2)
D frontend/src/bindings/FrontendAppInfo.ts (3)
M frontend/src/bindings/UpdateResponse.ts (4)
M frontend/src/components/AppInfo.tsx (10)
M frontend/src/entries/review.tsx (6)
M frontend/src/entries/shop.tsx (15)
M frontend/src/entries/submit.tsx (8)
M frontend/src/mock.ts (6)
M frontend/src/types.ts (6)
A migrations/20230604175742_init.sql (49)
M src/bot.rs (129)
M src/db.rs (712)
M src/main.rs (29)
M src/request_handlers/genisis.rs (8)
M src/request_handlers/mod.rs (49)
M src/request_handlers/review.rs (62)
M src/request_handlers/shop.rs (69)
M src/request_handlers/submit.rs (53)
M src/utils.rs (23)

-- Patch Links --

https://github.com/webxdc/appstore-bot/pull/29.patch https://github.com/webxdc/appstore-bot/pull/29.diff

-- Reply to this email directly or view it on GitHub: https://github.com/webxdc/appstore-bot/pull/29 You are receiving this because you are subscribed to this thread.

Message ID: @.***>

[link2xt]

sqlx has async interfaces and can switch from SQLite to MySQL and PostgreSQL if needed.

The reason we switched back from sqlx to rusqlite was a performance problem due to sqlx using SQLite database from multiple threads, submitting query in one thread and stepping over the results in a different thread. This problem is reportedly fixed in sqlx 0.7 and we don't plan to put too much load on this database anyway. If the database ever becomes a bottleneck, we can switch to PostgreSQL easily.

[link2xt]

cargo test is failing by the way, it is not tested in CI currently.

[link2xt]

I think we can still reshape even the initial database schema before it is deployed, so better merge it and then it can be improved in smaller PRs.