Database support

[the-moisrex]

Features

• [ ] User should be able to switch database types without touching the application layer code
• [ ] Is database a service? An API? so we can also switch between databases and services?
• [ ] Is GraphQL server a database?
• [ ] #126
• [ ] Database Connection
  • [ ] Generate a database dump
  • [x] #127
  • [ ] MySQL
  • [ ] Redis
  • [x] #129
  • [x] #128
  • [x] #130
• [x] Run simple, unsafe queries
• [x] Prepare queries
• [ ] Transactions
• [ ] Savepoints
• [ ] Scheme Builder
• [ ] Query builder
  • [ ] #144
  • [ ] GQL
  • [ ] Cypher
  • [ ] GraphQL
  • [ ] Database Functions
  • [ ] Table prefixes, suffix; in general: name modifier (encrypt and decrypt)
  • [ ] Database name prefixes, suffix; in general: name modifier
  • [ ] Value modifiers (encrypt and decrypt values)
  • [ ] Optimizer: optimize the query
  • [ ] Rollback query maker (create a rollback query for the specified query if possible)
  • [ ] Cache query generation (is it possible?)
• [ ] Query Parser and Modifier (to work with database dumps, and migrations)
  • [ ] Modify tables
• [ ] Models (ORM)
  • [ ] Model relations
  • [ ] Model Collection
    • [ ] Model Collection search, transform, ... functions
• [ ] Sharing database connections between sub-apps and extensions
• [ ] Connection Pack
  • [ ] Choose which connection should be used for each query
  • [ ] Log (encrypted and decrypted) queries for debug purposes
  • [ ] Backup databases
    • [ ] Store encrypted data in an unsafe database as a backup
    • [ ] Auto recovery
  • [ ] Cache
• [ ] Migrations
  • [ ] Migration types:
    • [ ] Time based migrations: run the migrations in-order that they were made, like Laravel
    • [ ] Status based migrations: run the migrations based on the "status" function for each migrations that checks if the database requires to migrate or rollback
  • [ ] SDK support for migrations
    • [ ] Rollback command
    • [ ] Migrate command
    • [ ] Create a migration file command
    • [ ] Each application should be able to have its own set of migrations so the application is share-able.
  • [ ] Initial migration (Don't need that much migration when we're in development phase)
  • [ ] Live website migration
  • [ ] Migration storage (table in the database that stores migration meta data)
• [ ] Scheme file (something like what prisma does)
• [ ] Seeding
• [ ] Detects lost connection
• TESTS
  • [x] sqlite connection test
  • [x] sqlite statement test
  • [x] sql statement (the wrapper) test

Example usages that I hope to implement:

auto user = db.tables["users"].where("id", 20).first();
user["age"]++;                            // operator is overloaded, sync is the default.
user["age"] += 20;                        //
user["username"] = "hello world";         //
user.commit();                            // run username change NOW

auto usr = user.async;
// we should be able to use this instead of keep getting the .async version

auto posts = db.tables["posts"].where(field("id") > 10);                  // haven't fetched them yet
auto names = db.tables["names"].where("date"_field > (now() - 100_days));
auto pics = db.tables["pictures"]
              .select("src")
              .where(
                  func::tolower("title"_field) == "cute" ||
                  "id"_field == 200
              );
auto authors = db.tables["authors"]
                 .select(
                      "username",
                      "firstname",
                      "lastname",
                      field(func::concat("firstname", "lastname")).as("name")
                 )
                 .where("date"_field > now() - 10_days)
                 .and_where("date"_field <= now())
                 .get();

using func = database::functions;
posts.each["title"] = func::tolower(post.title);           // tolower is SQL function not C++
posts.update();

db.transaction([&]() {
  // ...
});

Examples of the user configuring the database:

int main() {
 database::mysql localhost;
 application app;
 app.databases.push_back(std::move(localhost));
 return app();
}