[odb-users] Check if table exists

[Boris Kolpackov]

Hi Gunjur,

Gunjur Munireddy Prashanth / LOKISA Smart Energy GmbH <GPrashanth at prosolartec.de> writes:

> I have to add multiple tables to the sqlite database say for example: 
> table1, table2, table3...... etc . How can I check if the table exists
> or not ? say table1 and table3 exists in database, now I want to add
> table3 without deleting the other tables.

As Per mentioned, the proper way to handle this would be to use
database schema evolution support which is coming in the next
release. This way, you could say that the first version of
your schema has table1 and table2. While the second version
adds table3. ODB will also handle adding the table to the
database.

In the meantime, you can use a native, SQLite-specific query
to check if the table exists:

if (db.execute ("SELECT 1 FROM sqlite_master "
                "WHERE type='table' AND name='table3'") == 0)
{
  // Create table3.
}

If you are using ODB to create the tables for you (i.e., the
--generate-schema option), then there is another problem: how
to create the schema for table3 without affecting table1 and 
table2. The workaround for this problem is to assign a
different schema name (--schema-name option) to the schema
for table3. For example, assuming table3 corresponds to
class3:

odb -d sqlite ... --generate-schema --schema-name schema3 class3.hxx

Then our code will look like this:

transaction t (db.begin ());

if (db.execute ("SELECT 1 FROM sqlite_master "
                "WHERE type='table' AND name='table3'") == 0)
{
  schema_catalog::create_schema (db, "schema3");
}

t.commit ();

But, as I said above, all this will be taken care of once
schema evolution support is ready.

Boris