Views

[lasyakoechlin]

I was looking but couldn't find anything on the wiki or examples.

Is there a way to programmatically create views using sqlite_orm?

Thanks!

[juandent]

How important is it for you to have support for views?

[juandent]

it is the opinion of some people that views are seldom use in real programming being more a feature used academically. Do you oppose this opinion? Is your design dependent on views? I can only assume that it does!!

[lasyakoechlin]

I am using a 3rd party library which is expecting a specific database format which relies heavily on a single view.

My past solution was to expect that a pre-defined database schema file was provided which matched that structure. This inflated the deployed app size, etc.

So, instead, I am trying to use sqlite_orm to programatically create the database schema and manage it more smoothly. I realized that it wasn't working and I've identified the source as the one missing view.

I am ok to programmatically create the view myself in terms of the modifying the sqlite file directly, if that's an option.

Is there a way I can executor a manual hardcoded query on the sqlite file? That way I can manually create the necessary view?

[lasyakoechlin]

Maybe using storage.execute(...) or something? I am investigating this now...

[fnc12]

@lasyakoechlin what kind of SQL exactly do you need?

BTW it is duplicate #306

[lasyakoechlin]

I basically just need a hardcoded:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name;

[lasyakoechlin]

I don't need to query it or anything. I just need for the sqlite file to create the view so that it can be accessed by a third part library (where I supply the database file link)

[fnc12]

ok I see. Views is a very complex feature. And unfortunately it is not implemented in sqlite_orm right now. The schema of a database can consist of 4 types of entities in SQLite:

• tables
• indexes
• triggers
• views.

Everything is implemented except views cause views is a very complex feature. To make it right first we need to make sure we have CTE feature. CTE is also a very complex feature but we can't skip it cause we can't cover all cases of SQL queries without CTE. Fortunately @trueqbit is working on CTEs here #1124 and one day it will be merged. Once it is merged we may think on views API for sqlite_orm.

Thank you for providing an example. How do you expect to interact with your view?

[fnc12]

@lasyakoechlin if you don't need to query it so you can create a view outside of sqlite_orm and provide the file by yourself. You can call sync_schema any amount of times but it will never alter any view

[lasyakoechlin]

Yeah, so supplying it externally does work. The problem is that my re-design was trying to avoid supplying the file externally.

So, I was wondering if I could use sqlite_orm to execute a manual create view command (like, sqlite3pp for example would allow me to execute any arbitrary string command that I want).

I guess I am wanting to know if there's a way for my to directly supply a string to the internal sqlite3

[fnc12]

Yeah, so supplying it externally does work. The problem is that my re-design was trying to avoid supplying the file externally.

I understand this intent and I agree with you - this is what I do in my projects as well. But sqlite_orm is not going to have raw string API. Until views feature is not implemented I can give one not beautiful but working solution. You can use storage.on_open callback which actually is designed for encoding/decoding a database but actually it provides you with access to sqlite3* pointer and is called when database is opened. It will look like this:

storage.on_open = [](sqlite3* db) {
    sqlite3_exec(db, "CREATE VIEW IF NOT EXISTS view_name AS ...", nullptr, nullptr, nullptr);
};

[lasyakoechlin]

That worked perfectly for my needs! Thank you.

Another unrelated follow-up, when I was making my tables, I did things like this:

inline auto TblMapTileInfo() {
            return sqlite_orm::make_table(MapTileInfo::table_name,
                                          sqlite_orm::make_column("tile_id", &MapTileInfo::tile_id),
                                          sqlite_orm::make_column("tile_row", &MapTileInfo::tile_row),
                                          sqlite_orm::make_column("tile_column", &MapTileInfo::tile_column),
                                          sqlite_orm::make_column("zoom_level", &MapTileInfo::zoom_level),
                                          sqlite_orm::unique(&MapTileInfo::tile_row,
                                                             &MapTileInfo::tile_column,
                                                             &MapTileInfo::zoom_level));
        }

where MapTileInfo has the following structure:

struct MapTileInfo {
            inline static const std::string table_name { "map" };

            std::string tile_id;
            int tile_row;
            int tile_column;
            int zoom_level;
        };

Anyways, when I manually download the generated database file, I notice that despite me trying to add an index on row, column, zoom_level, there are no indexes which were created.

Did I set something up incorrectly?

Thanks!

[lasyakoechlin]

For example, when I open up the sqlite DB, I don't see any indexes. (Note that I now see the view, correctly, so that's great!)

[fnc12]

1. I don't see any make_index calls in your code so it is correct not to see any indexes in external database viewers
2. we can't guarantee that database viewer you work works good actually

[lasyakoechlin]

Maybe I got confused by the difference between creating an index and this:

sqlite_orm::unique(&MapTileInfo::tile_row,
                                                             &MapTileInfo::tile_column,
                                                             &MapTileInfo::zoom_level)

[fnc12]

@lasyakoechlin you can know for sure whether you have to see indexes if your db has UNIQUE table constraint. Just create the same table with raw SQLite and check whether your DB viewer shows any indexes

[lasyakoechlin]

Thanks for the help/support! I was using unique when i should have been using unique_index. I also found in your documentation that I needed to create the indices before the tables. Anyways, that, combined with the manual view creation, and everything is working as expected. Much appreciated!

[fnc12]

@lasyakoechlin thank to you! BTW you can improve your sqlite_orm experience with real migrations API which allows you to call sync_schema without fear that data will be lost. If you are interested you can get info here https://sqliteorm.com/pricing