Is there any good way to distinguish CREATE TABLE failure due to table already exists from other possible failures? The cleanest I have found is to compare the string returned by sqlite3_errmsg(...) with the text "table ... already exists". I have learned from experience, however, that it is a usually bad idea for a program to depend upon the wording of an error message intended for human consumption. The message might get reworded or translated into another language!

This seems like the perfect opportunity for using an Extended Result Code such as SQLITE_ERROR_ALREADY_EXISTS but there is no such extended result code. Moby Sigh.

(1) What is the best way to distinguish CREATE TABLE failing due to ALREADY EXISTS from other possible failures?

(2) Why aren't commonly encountered SQLITE_ERROR codes like this broken down into extended codes?

(No, CREATE TABLE IF NOT EXISTS won't help me in this case unless there is a way that I can determine whether it succeeded due to actually creating the table or due to the table already existing.)

Also, I would rather not add any extra SQL execution to distinguish the results: instead the C API should tell me...

Use case: If the table already exists, I want to do nothing else before adding real data rows to it. If it doesn't exist, I want to create it and then add some default rows before adding real data rows). If a failing create returned SQLITE_ERROR_ALREADY_EXISTS instead of SQLITE_ERROR, then I would not have to do ANY extra SQL.

This code will be in a heavy usage area so I want to minimize the number of SQL statements executed. Checking with a PRAGMA whether the table exits adds an extra SQL call, which likely to be even more costly (though perhaps more reliable shoud errmsg() change) than analyzing the errmsg string.

It is my belief, perhaps not in agreement with the SQLite3 maintainers, that a primary purpose of Extended Result Codes should be to give callers of the API the ability to get more information about the nature of the error. Code should not have to examine messages intended for human consumption; the extended result code should convey as much information as possible.

Is this a critical change? Of course not! It just seems that a lot of information was left out of the extended result codes that could have (and should have) been included.

Do you know about:

CREATE TABLE IF NOT EXISTS mynewtable(...);

CREATE TABLE IF NOT EXISTS would only help if I could determine after running it whether it created the table or skipped because it already existed.

But this does not agree with another post of yours in this thread where you say you want to know what issued the CREATE TABLE command.

You can find the exact CREATE TABLE command used (unless someone later used one of the ALTER TABLE commands to modify the schema of that table). Would that do what you want ? If not, what are you actually trying to do ?

See also the db_table_exists() function from Fossil, which demonstrates the use of the sqlite3_table_column_metadata() interface to determine if a table already exists or not.

The sqlite3_table_column_metadata interface doesn't help either: I can't tell whether I created the table or it already existed at the time of the create. This leads to a race condition if multiple processes might be creating the table.

This leads to a race condition if multiple processes might be creating the table.

This is true only if the creation of a race condition was a design goal. If one was not hell-bent on creating a race condition then avoiding one is trivial.

This leads to a race condition if multiple processes might be creating the table.

Shouldn't you use mutex to avoid race condition if you creating tables simultaneously in multiprocessor environment ?

https://www.sqlite.org/lang_transaction.html

You could use this PRAGMA:

PRAGMA table_list(table-name)

and check the number of rows returned. If the table exists you'll get one row back, if it doesn't you'll get zero rows back. If there's a problem with the connection you'll get a different result rather than SQLITE_OK. The fact that there are three results should let you distinguish between the different possibilities.

https://sqlite.org/pragma.html#pragma_table_list

In the CLI, I can use:

PRAGMA database_list;

or

select * from PRAGMA_database_list();

No problem with

sqlite> pragma table_list('employees');

I get back

main

employees

table

15

0

0

However:

sqlite> select * from pragma table_list('employees');

Error: in prepare, near "(": syntax error (1)

What am I missing?

What am I missing?

select * from pragma_table_list('employees');

Note the 1-character spelling difference.

(oops!)

Thank you.

Yes, I can find out whether a table exists in many different ways! I want to find out whether I created it with a particular CREATE TABLE request because ONLY the creator is expected to also initialize it.

All that to avoid parsing the errmsg()?

(1) Note that in this application there is no need for the initialization to be done as a transaction; the only restriction is that only one process should do it.

(2) Searching the errmsg() for "already exists" has to be faster than a transaction and table does not exist.

(3) All I really want is an Extended Result Code so that I am not forced to know what wording will be used in errmsg().

All that to avoid parsing the errmsg()?

No. All that consisted of a few alternatives, differing in various costs.

(1) Note that in this application there is no need for the initialization to be done as a transaction; the only restriction is that only one process should do it.

(1) I do not see how you intend to arrange the "only one process", but a single "CREATE TABLE ..." statement is going to run in a transaction whether you see it as needed or not, at least when using SQLite.

(2) Searching the errmsg() for "already exists" has to be faster than a transaction and table does not exist.

(2) You should include the cost of the sqlite3_prepare call in your accounting. Does the error message search or potential extended error code examination still win when you do that?

All I really want is an Extended Result Code so that I am not forced to know what wording will be used in errmsg().

It's an interesting dilemma you have. Your program can work with either present and older versions of SQLite which have complained "table * already exists" for many years, or it can work only with some future version having this feature you "really want", a version which may never exist. This would present no hard choice for me.

The introspection features are relatively new (compared to SQLite v3), but they will be stable. And they take very little code, either in source or compiled form. It seems to me that unless your table definitions(s) will never change, simply knowing that it exists will not be enough either; some examination of how it exists will be needed. And the work to discover than will yield whether it exists with virtually no extra work. In my view, this should be considered when worrying about "all that" versus the "try and see what went wrong" approach.

Your "Extended Result Code" is no panacea either. Such results tend to not be stable, perhaps even less stable than simple error messages. Even if the same extended error codes can be returned, as the set grows how those returns map to the (unbounded) set of possible input errors changes. Of course, that can be managed with extended^N error codes, where the exponent N increases over time.

I think developers' time is better spent getting things right in a deterministic manner than in deciding what needs doing based on parsing error returns, however they may be structured. (That's why this would no hard choice for me.)

The easiest way to test for the existance of a table called theTable in schema main is to execute the following SQL:

select 1 
  from main.sqlite_master
 where type == 'table'
   and name == 'theTable' collate nocase
;

Note that if you want to ensure that you get a row back containing either true (non-zero) or false (zero) then you can execute the following SQL, which will always return precisely one row containing exactly one integer result.

select exists (
               select *
                 from main.sqlite_master
                where type == 'table'
                  and name == ? collate nocase
               )
;