Made a sample app

[define-private-public]

https://gitlab.com/define-private-public/sqlite_orm_todo_sample

Right now I'm working on a much larger app, but I wanted to take some time to better learn how to use SQLite ORM (e.g. "how would I do a data migration?"). So I spent some time making a sample application. It's a very simple command line TODO list.

If you have any suggestions on how to make this a better sample for others, or design/architectural patterns that should be used instead I'm open to suggestions. If you think it might also be good for other users to look at, I would appreciate a link to it in the README. I'll be sure to keep this project up to date to match any changes in SQLite ORM.

[fnc12]

That is nice. Let me add some comments:

1. using a sequence autoincrement(), primary_key() is legacy and will be removed at v1.8. Better write primary_key(), autoincrement() instead. Later it will be refactored to primary_key().autoincrement() but not right now;
2. it is better to serialize enum using built in sqlite_orm way just like it is implemented in enum_binding example just because it reduces amount of static_casts in your code. Example shows string binding but you can make int binding the same way. But it is up to you;
3. const string quit_cmd("quit"); can be refactored to const string_view quit_cmd("quit"); to reduce heap allocations;
4. migration code which uses insert with update_all can be changed using one replace or raw insert query:

const V2::TodoItemRecord new_todo{
    .id     = old_todo.id,
    .who_id = who_id,
    .thing  = old_todo.thing,
    .status = status_v1_to_v2(old_todo.status)
};
db.replace(new_todo);

or

const V2::TodoItemRecord new_todo{
    .id     = old_todo.id,
    .who_id = who_id,
    .thing  = old_todo.thing,
    .status = status_v1_to_v2(old_todo.status)
};
db.insert(into<TodoItemRecord>(), 
          columns(&V2::TodoItemRecord::id, &V2::TodoItemRecord::who_id, &V2::TodoItemRecord::thing, &V2::TodoItemRecord::status), 
          values(new_todo.id, new_todo.who_id, new_todo.thing, new_todo.status));

[define-private-public]

1. Thanks for the heads up. Done.
2. I actually did that for V2. Take a look: https://gitlab.com/define-private-public/sqlite_orm_todo_sample/-/blob/7f15127a9d419394eec91f9e8c1103310fa2101b/src_v2/Data/V2/StatusEnum_sqliteorm_serialize.h
3. I'm aware of string_view. I just wanted to keep things simple at the moment
4. Thanks! I was wondering if there was a way to do that with one DB operation.

[fnc12]

I was thinking about more powerful migrations API. If you have ideas you are welcome. One of options is:

//  configure migrations
storage.register_migration(1, 2, [](auto oldConnection, auto &storage) {
    struct OldType {
        //..
    };
    auto oldStorage = make_storage(oldConnection, ...old schema with OldType(s)...;
    //  here select some important data from oldStorage and insert it into new storage (the second argument, the same as initial storage itself)
});

//  call migration
storage.migrate(2);  // this call gets current user_version pragma and runs callbacks provided for 1 to 2 migration, next it sets PRAGMA user_version to 2. If you want to migrate to 3, then you need to add migration callback for 1, 3 arguments or two migration callbacks for 1, 2 and for 2, 3

Of course this functionality will be better if storage ATTACH feature will be implemented. What do you think at all? Does it fit all your migrations requirements and will it be better that current migration functionality?

[fnc12]

@define-private-public I'll convert this issue to discussion if you don't mind