iOS7 > Lesson#20 - SQLite Introduction

What is SQLite?


SQLite is an embedded, relational database management system (RDBMS). 

---

Access SQLite through Terminal

DB path in Xcode project:

/Users//Library/Application Support/iPhone Simulator/<7.1>/Applications/<Application ID>/Documents

---

<Common SQLite Statement Samples>

Create table

For example:

sqlite> CREATE TABLE Feed (feedID INTEGER PRIMARY KEY AUTOINCREMENT, feedStartDateTime DATETIME, feedEndDateTime DATETIME, feedTypeID INT, feedVolume INT, feedRemarks TEXT, feedCreatedDateTime default current_timestamp, feedLastModifiedDateTime default current_timestamp);

sqlite> CREATE TABLE Feed (feedID INTEGER PRIMARY KEY AUTOINCREMENT, feedStartDateTime TEXT, feedEndDateTime TEXT, feedTypeID INT, feedVolume INT, feedRemarks TEXT, feedCreatedDateTime default current_timestamp, feedLastModifiedDateTime default current_timestamp);

Field Type:
INT
PRIMARY KEY
AUTOINCREMENT - AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

NULL
INTEGER 
REAL - float
TEXT
BLOB

More info: 
http://www.tutorialspoint.com/sqlite/sqlite_data_types.htm
http://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

Insert data

For example:

sqlite> insert into Feed (feedStartDateTime, feedEndDateTime, feedTypeID, feedVolume) VALUES ('2014-03-23 14:30','2014-03-23 14:40', 2, 130);

Update data
sqlite> update Book set bookName = "Demo 1" where bookID = 1;

Delete data
sqlite> delete from Feed where feedID = 1;

Drop table

sqlite> drop table Feed;

Show table column:
sqlite> pragma table_info(<table_name>)

Show tables which exist:
sqlite> .tables

Exit from sqlite:
sqlite> .exit