What is a database without tables?
My next chalenge in SQLite is to create tables. Using the
SQLite create table documentation I am able to create tables without any hassle.
Say for example I want to store data about a person in the database and to do that I want to create a table with fields for firstnames, surname, preferred name, birthdate,title and some sort of primary reference that should be indexed and that should auto increment whenever a new row is added to the table. I also want to indicate in this table whether the person is active.
So I start up SQLite and attached my database;
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> ATTACH DATABASE 'myDB.fl' AS myDB;
sqlite> .databases
Ok so I now have everything in place to start creating the table.
sqlite> CREATE TABLE myDB.person
...> (
...> 'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
...> 'FIRSTNAMES' TEXT,
...> 'SURNAME' TEXT,
...> 'NAME' TEXT,
...> 'BIRTHDATE' DEFAULT CURRENT_DATE,
...> 'TITLE' TEXT,
...> 'ACTIVE' TEXT(1) DEFAULT 'N'
...> );
Fine so far. Now to insert data!
sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','BIRTHDATE','TITLE
') VALUES ('Johannes','Strydom','Johan','1977-02-22','Mr');
No problemo. To see whether everything works I do a SELECT
sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
And another insert to test the default date.
sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','TITLE'
,'ACTIVE') VALUES ('Armourette','Strydom','Armourette','Mrs','Y');
sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
2|Armourette|Strydom|Armourette|2008-01-17|Mrs|Y
sqlite>
Wasn't that easy? Now If I can only get SQLite to show me the table schema. For some reason it does not want to do that.
sqlite> .schema myDB.person
sqlite>
So what worked:
- Create Table (passed)
- Insert (passed)
- Auto Increment (passed)
- Default values (passed)
- .schema (failed)
There must be a configuration or command somewhere for schema which I must have missed.
I solved the problem by exiting SQLite and opening my database as a parameter to the SQLite command line executable.
C:\sqlite-3_5_4\sqlite3.exe myDB.fl
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .schema person
CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
);
sqlite>
So the master database is now my SQLite database! Isn't that cool? :)
With my database as the master I am also able to get the schema by running the following SELECT statement
sqlite> SELECT * FROM sqlite_master WHERE type IN ('table') AND tbl_name NOT LIKE 'INFORMATION_SCHEMA_%' ;
table|person|person|2|CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE sqlite_sequence(name,seq)
sqlite>
Conclusion?
