cocoa-touchiphonesqlite
I am trying to learn SQLite, and I am building an iPhone app. But I would like to add an SQLite database to my building app. I have got three tutorials, but I am not clear on that code.
How can I add an SQLite database to my building app? What would sample code look like?
There are a few steps to see the tables in an SQLite database:
List the tables in your database:
.tables
List how the table looks:
.schema tablename
Print the entire table:
SELECT * FROM tablename;
List all of the available SQLite prompt commands:
.help
Assuming three columns in the table: ID, NAME, ROLE
BAD: This will insert or replace all columns with new values for ID=1:
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:
INSERT OR REPLACE INTO Employee (id, role)
VALUES (1, 'code monkey');
GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!
UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.
GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).
INSERT OR REPLACE INTO Employee (id, role, name)
VALUES ( 1,
'code monkey',
(SELECT name FROM Employee WHERE id = 1)
);
This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES ( 1,
'Susan Bar',
COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
);
Best Solution
First of all you need to create your database.
From the command line create your db file.
Then create the tables within your database
Repeat that for any tables that you want in your database.
Then you need to include the database file in your project. Add the existing database file to your project as you would any other existing file.
Next you will have to link in the framework to interact with the database. This can be found under you current iPhone SDK folder.
/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.sdk/usr/lib/libsqlite3.0.dylib
Finally you have to include the header file sqlite3.h from
/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.sdk/usr/include/sqlite3.h
It should now be possible to write code to access your sqlite database from within your iPhone application.