How to check if table or column in table is present?

[NikitaFeodonit]

There are the several similar databases, many of the tables in which can differ by the presence of the optional columns, like this:

CREATE TABLE table_a (number NUMERIC, color TEXT, title TEXT)
or
CREATE TABLE table_a (number NUMERIC, color TEXT)

Before querying the table "table_a", I need to check the presence of the column "title" to form the request accordingly with SELECT.

How to check if the column "title" is present? Can I use "PRAGMA schema.table_info(table-name)" for it?

And in these tables are optional tables. How to check if the table is present in database?

All databases are read-only with the many optional tables and with the several tables, which contain the many optional columns, because of the historical development of the data storage format.

[zauguin]

AFAICT this is not really specific to sqlite_modern_cpp and more a general SQLite question, so the sqlite mailing list or StackOverflow would be better places to ask. Anyway:

The PRAGMA is a good approach. If you use the pragma function pragma_table_info(...) instead of the normal PRAGMA you can use SQL statements to work with the result. For checking the existance of tables you can use the sqlite_master table.

#include <sqlite_modern_cpp.h>
#include <iostream>
#include <string_view>

bool has_table(sqlite::database &db, const std::string &table) {
  bool result;
  db << "SELECT COUNT(*) FROM sqlite_master WHERE name = ? AND type = 'table'"
     << table >> result;
  return result;
}
bool has_column(sqlite::database &db, const std::string &table, const std::string &column) {
  bool result;
  db << "SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?"
     << table << column >> result;
  return result;
}

int main(int argc, char **argv) {
  if(argc < 2) {
    std::cerr << "Please provide a database name." << std::endl;
    return -1;
  }
  sqlite::database db(argv[1]);
  std::string table;
  std::cout << "Table: ";
  std::getline(std::cin, table);
  if(!has_table(db, table)) {
    std::cout << "There is no table called '" << table << "'\n";
    return 0;
  }
  std::string column;
  std::cout << "Column: ";
  std::getline(std::cin, column);
  if(!has_column(db, table, column)) {
    std::cout << "In this table there is no column called '" << column << "'\n";
  } else {
    std::cout << "Everything found.\n";
  }
  return 0;
}

[NikitaFeodonit]

Thank you for your help. Everything work well.

I checked the work with PRAGMA, it also works well.

It's great that your library allows to do such things with SQLite.

Ich bin Ihnen sehr dankbar! Dankeschön!