Is it possible to structure a SQL query to automatically create a table if it does not already exist?

Question

I am a complete newbie to SQL, and I am trying to execute the following (pseudocode) 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 should I do it? Can this be done with a single query? I am using SQLite3 if this 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 restore all lines:

SELECT * FROM config_data;

These are fantastic combinations that elude me :)

Answer 1

To create a table if it does not already exist, you can use IF DOESN’T EXIST :

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

INSERT OR IGNORE ( ), , , , (, unqiue key):

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

Answer 2

CREATE TABLE, ...

:

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

, :

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

Answer 3

, CONSTRAINT.

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