0

I'm developing a Rust application for user registration via SSH (like the one working for SDF).

I'm using the SQLite3 database as a backend to store the information about users.

I'm opening the database file (or creating it if it does not exist) but I don't know the approach for checking if the necessary tables with expected structure are present in the database.

I tried to use PRAGMA schema_version for versioning purposes, but this approach is unreliable.

I found that there are posts with answers that are heavily related to my question:

Shepmaster
  • 388,571
  • 95
  • 1,107
  • 1,366
  • The usual way is to use the `application_id` and `user_version` pragmas and create tables etc. if they're not set to appropriate numbers. – Shawn Dec 11 '18 at 20:11
  • No worries. You can see the [revision history](https://stackoverflow.com/posts/53731610/revisions) for every post. – Shepmaster Dec 11 '18 at 20:36

1 Answers1

0

I'm opening the database file (or creating it if it does not exist) but I don't know the approach for checking if the necessary tables

I found querying sqlite_master to check for tables, indexes, triggers and views and for columns using PRAGMA table_info(the_table_name) to check for columns.

e.g. the following would allow you to get the core basic information and to then be able to process it with relative ease (just for tables for demonstration):-

SELECT name, sql FROM sqlite_master WHERE type = 'table' AND name LIKE 'my%';

with expected structure

PRAGMA table_info(mytable);

The first results in (for example) :-

enter image description here

Whilst the second results in (for mytable) :-

enter image description here

  • Note that type is blank/null for all columns as the SQL to create the table doesn't specify column types.

If you are using SQLite 3.16.0 or greater then you could use PRAGMA Functions (e.g. pragma_table_info(table_name)) rather than the two step approach need prior to 3.16.0.

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Wow! You've performed a nice investigation. Much better than related answers. –  Dec 12 '18 at 15:55