Is it possible to structure a SQL query to automatically create a table if it doesn't already exist?

Question

I'm a complete SQL novice, and I'm trying to accomplish the following (pseudo-code) with a single query:

if (a table named "config_data" does not exist)
{ create a table named "config_data" }

if ("config_data" has no rows)
{ add a row to "config_data" with some default data }

return all rows in "config_data"

How would I go about it? Can it be done with a single query? I'm using SQLite3, if that helps.

So far, I know how to create a table, and insert data into it:

CREATE TABLE config_data (key TEXT NOT NULL, value TEXT);
INSERT INTO config_data VALUES ("key_1", "value_1");
INSERT INTO config_data VALUES ("key_2", "value_2");

And also how to retreive all rows:

SELECT * FROM config_data;

It's the fancy combinations that elude me :)

Answer 1

You can use CREATE TABLE IF NOT EXISTS...

Take a look here:

http://cookbooks.adobe.com/post_Easy_way_to_create_a_SQLite_connection-12846.html

You can also use what is shown at the end of this page:

http://php.net/manual/en/book.sqlite.php

Answer 2

To create a table if it does not already exist, you can use IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS config_data (
    key TEXT NOT NULL, value TEXT,
    CONSTRAINT key_unique UNIQUE(key)
)

For your second part, you can use INSERT OR IGNORE (more info here) to insert a value, assuming you have a unique constraint that would otherwise be violated by inserting the value twice (for example, you might have a unqiue constraint on your key column):

INSERT OR IGNORE INTO config_data VALUES ("key_1", "def_value_1")

Answer 3

Also, you can specify a default value per column in the CONSTRAINT clause.

CREATE TABLE IF NOT EXISTS config_data 
('someinteger' INTEGER, 'sometext' TEXT, CONSTRAINT sometext DEFAULT 'should be text but isn't yet')