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?

[define-private-public]

I think this does look good, though some questions:

1. What is the type of old_connection?
2. Would this API support downgrading? E.g. 2 -> 1? I've seen in a few places where customers would like to exchange/view things in and older format for compatibility. I remember for a while after .docx was released, I still used .doc because it worked better with more application for a while.
3. If you specify migration functions for 1 -> 2, and 2 -> 3. But don't define a 1 -> 3, would the API automatically call the two migration functions if a programmer did storage.migrate(3)?
4. I'm assuming this would run fully independent of sync_schema?

[fnc12]

1. old_connection will have a new custom type used for connection encapsulation. It can be passed as the first argument into storage ctor instead of filename and must be used only within migration functionality;
2. IDK. I can do it if you like. I hardly can imagine when someone needs to do a reverse migration. If you have detailed cases I'd like to read about it. But ok I got you;
3. IDK yet. We need to decide. If yes I will need to add a graph search algorithm cause user can have 1 -> 2, 1 -> 3, 2 -> 3, 2 -> 5, 5 -> 9, and then call migrate from 1 to 9. I don't want to add a graph search algorithm and I'd like to leave it as a client's responsibility. So API will be like this:

storage.migrate(2);   // if PRAGMA user_version is 1 then 1 -> 2 migration will be called. Otherwise nothing happens
storage.migrate(3);  // same for user_version 3
storage.migrate(4);
..
storage.migrate(68);

So legacy projects will have a lot of these lines during lifetime. So when user will want to migrate from user_version 1 to 68 68 migrations will be performed. Or if developer will register a migration for 1 to 68 then this will be called. Otherwise exception will be throws about 'migration callback not found for user_versions: 1 -> 68'.
4. Yes it will be independent of sync_schema.

[fnc12]

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

[define-private-public]

Sure thing.