SQLite Forum

Distinguishing CREATE TABLE failure (already exists) from othe failures
Login

Distinguishing CREATE TABLE failure (already exists) from othe failures

(1.2) By Rick Gumpertz (rgumpertz) on 2022-01-19 04:55:19 edited from 1.1 [link] [source]

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...

(2) By Gunter Hick (gunter_hick) on 2022-01-19 06:27:03 in reply to 1.2 [source]

What is the use case? Why not check if the table exists (and has the desired structure) before attempting to create it?

(4.1) By Rick Gumpertz (rgumpertz) on 2022-01-19 18:25:21 edited from 4.0 in reply to 2 [link] [source]

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.

(5) By Richard Hipp (drh) on 2022-01-19 18:36:05 in reply to 4.1 [link] [source]

Do you know about:

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

(10) By Rick Gumpertz (rgumpertz) on 2022-01-20 03:22:25 in reply to 5 [link] [source]

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.

(19) By Simon Slavin (slavin) on 2022-01-21 22:34:43 in reply to 10 [link] [source]

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 ?

(6) By Richard Hipp (drh) on 2022-01-19 18:39:12 in reply to 4.1 [link] [source]

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.

(11) By Rick Gumpertz (rgumpertz) on 2022-01-20 03:26:07 in reply to 6 [link] [source]

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.

(13) By Keith Medcalf (kmedcalf) on 2022-01-20 03:30:21 in reply to 11 [link] [source]

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.

(15) By AlexJ (CompuRoot) on 2022-01-20 10:23:26 in reply to 11 [link] [source]

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 ?

(16) By anonymous on 2022-01-21 08:19:46 in reply to 11 [link] [source]

(3) By Simon Slavin (slavin) on 2022-01-19 13:19:43 in reply to 1.2 [link] [source]

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

(7) By anonymous on 2022-01-19 20:27:29 in reply to 3 [link] [source]

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?

(8) By Larry Brasfield (larrybr) on 2022-01-19 20:33:45 in reply to 7 [link] [source]

What am I missing?

select * from pragma_table_list('employees');

Note the 1-character spelling difference.

(9) By anonymous on 2022-01-19 20:45:44 in reply to 8 [link] [source]

(oops!)

Thank you.

(12) By Rick Gumpertz (rgumpertz) on 2022-01-20 03:29:20 in reply to 7 [link] [source]

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.

(14.1) By Gunter Hick (gunter_hick) on 2022-01-20 07:15:25 edited from 14.0 in reply to 12 [link] [source]

The simple SQL-only way :

<create_and_insert> :==
CREATE TABLE IF NOT EXISTS...
INSERT OR IGNORE...

Any process running that sequence will be sure that the table exists and the default records (as identified by the primary key) are there, regardless of which process does what.

To make sure only one process actually does the work, use a transaction:

<trans_create_insert> :==
BEGIN IMMEDIATE;
<create_and_insert>
COMMIT;

This has the benefit of making any other process wait until the first one is finished.

<table_does_not_exist> :== choose your favorite method

If you want to avoid executing the code unless the table already exists:

if (<table_does_not_exist>) execute <trans_create_and_insert>

And for minimal SQL execution:

if (<table_does_not_exist>)
{
    execute BEGIN IMMEDIATE;
    if (<table_does_not_exist>) execute <create_and_insert>;
    execute COMMIT;
}

Or the condensed version:

execute BEGIN IMMEDIATE;
if (<table_does_not_exist>) execute <create_and_insert>;
execute COMMIT;

The last version makes every process wait for the winner of BEGIN IMMEDIATE, who then goes on to create and populate the table. All of the later processes just check.

Now pick your favorite method. Edit: You may want to actually time different solutions to see which one works the fastest in your target environment.

(17.1) By Rick Gumpertz (rgumpertz) on 2022-01-21 19:24:58 edited from 17.0 in reply to 14.1 [link] [source]

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().

(18) By Larry Brasfield (larrybr) on 2022-01-21 20:16:40 in reply to 17.1 [link] [source]

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.)

(22) By Gunter Hick (gunter_hick) on 2022-01-24 07:56:45 in reply to 17.1 [link] [source]

It seems you have completely missed the point of transactions in the first place and the fact that nothing, absoluteley nothing, gets done in SQLite without a transaction.

Your approach will have 1 transaction to create the table and one transaction each for every one of your initial rows. Plus, you have to synchronize your multiple processes somehow to ensure that exactly one process performs all of your transactions and that all other processes wait until that one process has inserted all the initial records.

And all that just to avoid using an explicit transaction, that gives you all of the synchronization you need automagically.

(20) By Keith Medcalf (kmedcalf) on 2022-01-21 23:52:02 in reply to 1.2 [link] [source]

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
;

(21) By Keith Medcalf (kmedcalf) on 2022-01-21 23:57:21 in reply to 20 [link] [source]

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
               )
;