Verify the structure of a database? (SQLite in C++ / Qt)

Question

I was wondering what the "best" way to verify the structure of my database is with SQLite in Qt / C++. I'm using SQLite so there is a file which contains my database, and I want to make sure that, when launching the program, the database is structured the way it should be- i.e., it has X tables each with their own Y columns, appropriately named, etc. Could someone point my in the right direction? Thanks so much!

Answer 1

You can get a list of all the tables in the database with this query:

select tbl_name from sqlite_master;

And then for each table returned, run this query to get column information

pragma table_info(my_table);

For the pragma, each row of the result set will contain: a column index, the column name, the column's type affinity, whether the column may be NULL, and the column's default value.

(I'm assuming here that you know how to run SQL queries against your database in the SQLite C interface.)

Answer 2

If you have QT and thus QtSql at hand, you can also use the QSqlDatabase::tables() (API doc) method to get the tables and QSqlDatabase::record(tablename) to get the field names. It can also give you the primary key(s), but for further details you will have to follow pkh's advice to use the table_info pragma.