1. technology and computing
2. software
3. databases

How To Develop iOS Database Apps using
SQLite
Tutorials on developing iPhone and iPad database apps
using SQLite
Kevin Languedoc
This book is for sale at http://leanpub.com/iossqlite
This version was published on 2014-09-17
This is a Leanpub book. Leanpub empowers authors and publishers with the Lean Publishing
process. Lean Publishing is the act of publishing an in-progress ebook using lightweight tools and
many iterations to get reader feedback, pivot until you have the right book and build traction once
you do.
©2014 Kevin Languedoc
Tweet This Book!
Please help Kevin Languedoc by spreading the word about this book on Twitter!
The suggested hashtag for this book is ##iossqliteapps.
Find out what other people are saying about the book by clicking on this link to search for this
hashtag on Twitter:
https://twitter.com/search?q=##iossqliteapps
To all the programmers who struggle day in, day out to write great iOS apps
Contents
Updating an iOS SQLite Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQLite Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Develop the SQLite iPhone Application . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
1
4
Updating an iOS SQLite Database
This chapter is about modifying existing SQLite database during runtime. Although SQLite has a
light version of SQL, it does offers some capabilities to modify an existing database. What makes
SQLite stand out amongst database engines is its ability to an user to modify a database during
runtime which can be very useful.
Another important concept is the fact that each database is an independent file. If you are using
more than more database, which you can, each database is stored in its own file.
To demonstrate how to modify a database at runtime, I created a sample iOS iPhone application
using the Single View template. The UI is very primitive but I wanted to focus on the SQLite queries
and how to set them up to be able to modify a database. Another liberty that I am taking is that
I have hard coded a lot of new column name, table name, index, trigger, and so on that I use to
create these new objects in the SQLite database. Reason again was not to build a complete database
manager app. Actually I am working on one, but more on that in the appendix.
In this sample app, I provide the code to alter a table, add a column, which are both part of the Alter
Table statement. I am also providing the code to create a table, trigger, column, and view. Likewise,
you will find code to drop (delete, remove) a table, view, trigger or index.
To manage the SQLite SQL query operations, I created a custom class as a sub-class of the NSObject.
This custom class is called DatabaseOperations.
Before I get into the actual application and how it is built, I thought it might be a good idea to provide
the queries of each of the SQLite operations to modify a database. These are provided below.
SQLite Queries
I took the liberty to hardcode the names of the objects to be modified, preferring to focus on how
to setup the query for each type of possible modification instead. In a production environment, the
hardcoded values would be replaced with variables with the new value. All the queries use NSString
class to store the query string because I find it easier to manage concatenation using the C based
const char * variable name design. In the following I am providing just the example queries, in the
next chapter I will demonstrate how to use them.
Alter Table
Updating an iOS SQLite Database
1
2
3
4
5
NSString*(^at)(void) = ^{
NSString * alterTbl = [[NSString alloc]init];
alterTbl = @"Alter Table todoTbl Rename to bar";
return alterTbl;
};
Add Column
1
2
3
4
5
NSString *(^ac)(void)=^{
NSString * addCol = [[NSString alloc]init];
addCol = @"Alter Table bar Add column baz CHAR(50)";
return addCol;
};
Create Table
1
2
3
4
5
6
NSString *(^ct)(void)=^{
NSString *addTbl = [[NSString alloc]init];
addTbl = @"create table if not exists todoTbl(todoName varchar,
todoDescription varchar, todoDate varchar)";
return addTbl;
};
Create Index
1
2
3
4
5
NSString *(^ci)(void)= ^{
NSString *addIn =[[NSString alloc]init];
addIn = @"Create index if not exists fooindex on bar(todoName)";
return addIn;
};
Create Trigger
2
Updating an iOS SQLite Database
1
2
3
4
5
6
7
NSString*(^ctrg)(void)=^{
NSString *addTrig =[[NSString alloc]init];
addTrig = @"Create trigger if not exists baztrig after insert on bar beg\
in insert into todoTblLog(todoName,todoDescription, Time) values('foo','bar', da\
tetime('now')); end";
return addTrig;
};
Create View
1
2
3
4
5
6
NSString *(^cv)(void)=^{
NSString *addVw =[[NSString alloc]init];
addVw = @"Create view if not exists aView as Select todoName, todoDescri\
ption from bar";
return addVw;
};
Drop Database
See the DeleteDatabase method section in this chapter.
Drop Table
1
2
3
4
5
6
NSString*(^dt)(void)= ^{
NSString *dropTbl = [[NSString alloc]init];
dropTbl = @"drop if exist bar ";
return dropTbl;
};
Drop Index
1
2
3
4
5
6
NSString*(^di)(void)=^{
NSString *dropIndex = [[NSString alloc]init];
dropIndex = @"drop if exists fooIndex";
return dropIndex;
};
Drop Trigger
3
Updating an iOS SQLite Database
1
2
3
4
5
4
NSString*(^dtrg)(void)= ^{
NSString *dropTrig = [[NSString alloc]init];
dropTrig = @"drop if exists baztrig";
return dropTrig;
};
Drop View
1
2
3
4
5
NSString*(^dv)(void) = ^{
NSString *dropView = [[NSString alloc]init];
dropView = @"drop if exists aView";
return dropView;
};
Develop the SQLite iPhone Application
Since the purpose of the application is to show you how to write the code to be able to modify a
SQLite the UI has limited functionality and I focus primarily on the interaction between the SQLite
engine, the queries and the controller.
Let’s start by creating a new iOS Single View iPhone app. You can get that set up through Xcode list
of app templates by either selecting that template from the new project dashboard or selecting New
Project from the Xcode menu.
Once the project is created, add the SQLite library through the Linked Libraries and Frameworks
section in the Project Summary page. For newbies, select the project root in the project explorer and
the right side of the IDE, or main window, this is the Project Summary page. Scroll to the bottom and
you will find the Linked Library and Frameworks section. By clicking on the “+” button, a search
panel will appear allowing you to enter the “sqlite3” search term. Select the sqlite3.dylib
Create the Model
To handle the database operations and the interactions with the actually databases, I created
a NSObject subclass called DatabaseOperations. This object provides the implementation of the
SQLite queries. As you see from the code listing below on the header file, you need to import the
SQLite3 library using the import statement followed by the name of the library in double quotes.
Since I will need a pop a warning message and I will to implement the UIAlert, I need to the
UIAlertViewDelegate protocol which provides the interactivity to the UIAlertView.
Next I declare a sqlite3 variable which will be our main conduit to the SQLite database engine. I
wanted to use constants to declare the various database operations, so I have declare these constants
using the FOUNDATION_EXPORT which is part of the NSObjCRuntime class. Mind you, I could
have use the extern directive in C. I will these later in the implementation with a Block which is a
5
Updating an iOS SQLite Database
closure function or an inline function if you prefer. If you want to be really well organized and will
need constants in different parts of the app, you could place all these constants in constants.h file
and implementation object.
Most of the operations happen inside a SQLite database but we need a method to allow us to
create databases. So the BuildDatabase method will taken a database name string as input. The
next method, ExecuteDatabaseOperation will implement the Block code in order to the select the
proper query and launch the ModifyDatabase method. You will notice that these method don’t have
any facilities to receive or pass the values of the objects to the changed. I did this on purpose to
keep the example simple. In a real app you would need to provide a parameter for the value you to
had or change. The DeleteDatabase will be use to delete the database file. There is no actual query
associated with this method as I am only calling the removeItemAtPath in the NSFileManager class
to delete the file as we will see in a bit.
The last method, WarningMessage will display the UIAlertView when needed.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//
//
//
//
//
//
//
DatabaseOperations.h
Update SQLite Database
Created by Kevin Languedoc on 4/28/14.
Copyright (c) 2014 Kevin Languedoc. All rights reserved.
#import <Foundation/Foundation.h>
#import "sqlite3.h"
@interface DatabaseOperations : NSObject<UIAlertViewDelegate>
{
sqlite3 *db;
}
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
*const
*const
*const
*const
*const
*const
*const
*const
*const
*const
*const
*const
ERROR_OCCURRED;
ERROR_BAD_STMT;
DATABASE_EXIST;
ALTER_DATABASE;
ALTER_TABLE;
ADD_COLUMN;
CREATE_DATABASE;
CREATE_TABLE;
CREATE_INDEX;
CREATE_VIEW;
CREATE_TRIGGER;
DROP_DATABASE;
6
Updating an iOS SQLite Database
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
FOUNDATION_EXPORT
NSString
NSString
NSString
NSString
*const
*const
*const
*const
DROP_TABLE;
DROP_INDEX;
DROP_VIEW;
DROP_TRIGGER;
-(void)BuildDatabase:(NSString*)DatabaseName;
-(NSString *)ExecuteDatabaseOperation : (NSString*)
databaseName : (NSString*)operation;
-(void)WarningMessage :(NSString*)warning;
-(void)ModifyDatabase:(NSString*)databaseName : (NSString*)query ;
-(void)DeleteDatabase: (NSString*)dataName;
-(NSMutableArray*)GetDatabaseList;
@end
The implementation is very straightforward. The first thing I will do is initialize the constants. To
do this, I use the NSString *const statement followed by the name of the constant and its assigned
value as in the code below. These constants can be used anywhere the DatabaseOperations is used
as you will see later in the ViewController.
The first method that I will implement is the -(void)BuildDatabase:(NSString*)DatabaseName. This
method creates a new database. The code is very similar in design to the tutorials in chapter 1.
Although I have datababseName as parameter that can be used to pass in the value of the new
database, I am assuming for the sake of simplicity that the database name is “test” and the extension
is “db”. The code obtains the path for the Documents directory because this is the main read/write
directory in an apps sandbox or its own unique filesystem. Then I set the path as a string to the new
database using the the first element in the array of folders in the Documents path. If your app will
have multiple sub-folders under the Documents path, you will need t make sure that you are getting
the right folder. A possible solution id to set a db sub-folder and reserve it for the databases.
With the path to the database in hand, mind you the database hasn’t been created it, the code checks
to see if the database already exists. Here you could pop a warning message like I am doing in the
code method below or handle the conflict by possibly offering to replace or create a second database
with a modified name. Then using the sqlite3_open function I will create the database and open it.
The -(void)ExecuteDatabaseOperation : (NSString)databaseName : (NSString)operation method is
very interesting because I am using a Block to emulate a switch statement. As you may know
Objective-C doesn’t have a switch statement. Since Objective-C is a superset of the C language,
you can use any part of the C language directly in Objective-C. In the C language, the expression in
the switch statement needs an integer value, however I want to use a string value which is the value
Updating an iOS SQLite Database
7
that appears in the UIPickerView in the UI. So I am using a block which is a closure construct instead.
The block encapsulates individual blocks for each query. Each query uses NSString to construct the
query string and it is returned to the calling sqlite3_exec function in the ModifyDatabase method.
The -(void)ModifyDatabase: (NSString)databaseName : (NSString)query is very simple and can be
used as is in a production grade app, or almost once you build in some error handling. This method
uses the sqlite3_exec SQLite 3 function can encapsulates all the necessary operations needed to
perform a SQLite query. If the function can’t perform the operation and get a successful return code,
the statement will display an error message using the -(void)WarningMessage:(NSString *)warning
method which implements the UIAlertView object from the CocoaTouch UIKit framework.
The final method we will implement is the -(void)DeleteDatabase:(NSString *)dataName method.
This method doesn’t execute any SQL query code. Instead it deletes the database file after it is
detached from the SQLite engine. Notice the DROP_DATABASE query option in the blocks in the
ModifyDatabase method. This query detaches the database but the file is still in the Documents
folder. It must be removed to free any space that it is occupying at the moment. The first operation
in the method is to call the ExecuteDatabaseOperation, passing in the name of the database file to
be deleted. The second parameter is hard coded to DROP_DATABASE. Once the query successfully
detaches the database the NSFileManager can be called to delete the sqlite file in the Documents
directory or one of its sub-folders if you choose that path in your code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//
//
//
//
//
//
//
DatabaseOperations.m
Update SQLite Database
Created by Kevin Languedoc on 4/28/14.
Copyright (c) 2014 Kevin Languedoc. All rights reserved.
#import "DatabaseOperations.h"
@implementation
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
NSString *const
DatabaseOperations
ERROR_OCCURRED= @"An error has occurred";
ERROR_BAD_STMT = @"There is a problem with statement";
DATABASE_EXIST=@"This database already exist";
ALTER_DATABASE=@"Alter Database";
ALTER_TABLE=@"Alter Table";
ADD_COLUMN=@"Add Column";
ALTER_VIEW=@"Alter View";
ALTER_TRIGGER=@"Alter Trigger";
ALTER_INDEX=@"Alter Index";
ALTER_COLUMN=@"Alter Column";
CREATE_DATABASE=@"Create Database";
CREATE_TABLE=@"Create Table";
Updating an iOS SQLite Database
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
NSString
NSString
NSString
NSString
NSString
NSString
NSString
NSString
*const
*const
*const
*const
*const
*const
*const
*const
CREATE_INDEX=@"Create Index";
CREATE_VIEW=@"Create View";
CREATE_TRIGGER=@"Create Trigger";
DROP_DATABASE=@"Drop Database";
DROP_TABLE=@"Drop Table";
DROP_INDEX=@"Drop Index";
DROP_VIEW=@"Drop View";
DROP_TRIGGER=@"Drop Trigger";
-(void)BuildDatabase:(NSString*)DatabaseName{
BOOL fileExist;
//Get list of directories in Document path
NSArray * dirPath = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory,
NSUserDomainMask, YES);
//Define new path for database
/*NSString * documentPath = [[[dirPath objectAtIndex:0]
stringByAppendingPathComponent:DatabaseName ]
stringByAppendingString:@".db"];*/
/*
for the sake of simplicity of simplicity
*/
NSString * documentPath = [[[dirPath objectAtIndex:0]
stringByAppendingPathComponent:@"test" ]
stringByAppendingString:@".db"];
fileExist = [[NSFileManager alloc] fileExistsAtPath:documentPath];
if(!fileExist){
if(!(sqlite3_open([documentPath UTF8String], &db) == SQLITE_OK))
{
NSLog(@"%@",ERROR_OCCURRED);
[self WarningMessage:ERROR_OCCURRED];
}
8
Updating an iOS SQLite Database
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
}else{
[self WarningMessage:DATABASE_EXIST];
}
}
-(NSString *)ExecuteDatabaseOperation : (NSString*)databaseName
: (NSString*)operation{
NSString*(^at)(void) = ^{
NSString * alterTbl = [[NSString alloc]init];
alterTbl = @"Alter Table todoTbl Rename to bar";
return alterTbl;
};
//
NSString *(^ac)(void)=^{
NSString * addCol = [[NSString alloc]init];
addCol = @"Alter Table bar Add column baz CHAR(50)";
return addCol;
};
//
NSString *(^ct)(void)=^{
NSString *addTbl = [[NSString alloc]init];
addTbl = @"create table if not exists todoTbl(todoName varchar,
todoDescription varchar, todoDate varchar)";
return addTbl;
};
//
NSString *(^cv)(void)=^{
NSString *addVw =[[NSString alloc]init];
addVw = @"Create view if not exists aView as Select todoName,
todoDescription from bar";
return addVw;
};
//-----
9
Updating an iOS SQLite Database
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
NSString *(^ci)(void)= ^{
NSString *addIn =[[NSString alloc]init];
addIn = @"Create index if not exists fooindex on bar(todoName)";
return addIn;
};
NSString*(^ctrg)(void)=^{
NSString *addTrig =[[NSString alloc]init];
addTrig = @"Create trigger if not exists baztrig after insert on
bar begin insert into todoTblLog(todoName,todoDescription, Time)
values('foo','bar', datetime('now')); end";
return addTrig;
};
NSString*(^dt)(void)= ^{
NSString *dropTbl = [[NSString alloc]init];
dropTbl = @"drop todoTbl ";
return dropTbl;
};
NSString*(^di)(void)=^{
NSString *dropIndex = [[NSString alloc]init];
dropIndex = @"drop if exists fooIndex";
return dropIndex;
};
NSString*(^dtrg)(void)= ^{
NSString *dropTrig = [[NSString alloc]init];
dropTrig = @"drop if exists baztrig";
return dropTrig;
};
NSString*(^dv)(void) = ^{
NSString *dropView = [[NSString alloc]init];
dropView = @"drop if exists aView";
return dropView;
};
10
Updating an iOS SQLite Database
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
NSDictionary* selectOps = [[NSDictionary alloc]
initWithObjectsAndKeys:
at(), ALTER_TABLE,
ac(), ALTER_COLUMN,
ct(), CREATE_TABLE,
cv(), CREATE_VIEW,
ci(), CREATE_INDEX,
ctrg(), CREATE_TRIGGER,
dt(), DROP_TABLE,
di(), DROP_INDEX,
dtrg(), DROP_TRIGGER,
dv(), DROP_VIEW,
nil];
// if (selectOps != nil)
return [selectOps objectForKey:operation];
}
-(void)ModifyDatabase:(NSString*)databaseName
:(NSString*)query{
BOOL fileExist;
NSArray * dirPath = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * documentPath = [[[dirPath objectAtIndex:0] stringByAppendingPathC\
omponent:@"test" ]
stringByAppendingString:@".db"];
fileExist = [[NSFileManager alloc] fileExistsAtPath:documentPath];
if((sqlite3_open([documentPath UTF8String], &db) == SQLITE_OK))
{
char *emsg;
NSString* queryStr = [[NSString alloc]init];
queryStr = [self ExecuteDatabaseOperation:databaseName :query];
11
Updating an iOS SQLite Database
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
if(sqlite3_exec(db, [[self ExecuteDatabaseOperation
:databaseName :query] UTF8String], NULL, NULL, &emsg) != SQLITE_OK)
{
NSLog(@"%s",emsg);
[self WarningMessage:ERROR_BAD_STMT];
}
}else{
NSLog(@"%@",ERROR_OCCURRED);
[self WarningMessage:ERROR_OCCURRED];
}
}
-(void)WarningMessage:(NSString *)warning{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Warning"
message:warning
delegate:self
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[alert show];
}
-(void)DeleteDatabase:(NSString *)dataName{
NSFileManager *fileManager = [NSFileManager defaultManager];
NSArray *paths = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString *documentsPath = [paths objectAtIndex:0];
NSString *filePath = [documentsPath
stringByAppendingPathComponent:[NSString
stringWithFormat:@"%@", dataName]];
[fileManager removeItemAtPath:filePath error:NULL];
}
-(NSMutableArray*)GetDatabaseList{
NSError * err;
NSMutableArray * databaseList=[[NSMutableArray alloc]init];
NSArray* dbarr = [[NSArray alloc] init];
NSArray * dirPath = NSSearchPathForDirectoriesInDomains
12
Updating an iOS SQLite Database
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
13
(NSDocumentDirectory, NSUserDomainMask, YES);
//returns an array of files at a defined path. For the sake of simplicity
I am not handling any potential errors.
dbarr = [[NSFileManager defaultManager] contentsOfDirectoryAtPath
:[dirPath objectAtIndex:0] error:&err];
databaseList = [[NSMutableArray alloc] initWithArray:dbarr];
[databaseList addObject:@""];
return databaseList;
}
@end
This completes the necessary code to handle all available database related update queries. However
as I said before, I have hard coded the values in the queries but in a production app, you would need
to allow the user the supply the values to modify the database. I am currently working on a SQLite
database manager app for the iPad with full source code which will be made available through the
iosdev101.com web site. Stay tuned.
Create the Controller
The next layer up in the MVC programming pattern that all iOS apps are built on is the Controller
which is implemented through the standard ViewController sub-class of the UIViewController class.
I will use the ViewController object that was created by default when I chose the Single View
app template. The first code listing below is the header file. Since we will need to implement
the DatabaseOperations class to create a new object I will add an import statement for the
DatabaseOperations header file.
Then we will need to use the UIAlertView object and the UIPickerView, so we will add the
UIAlertViewDelegate protocol and the UIPickerViewDataSource, UIPickerViewDelegate protocols.
The first will allow us to add a data source in the form of an array (NSArray or NSMutableArray or
a dictionary NSDictionary). The second, UIPickerViewDelegate, provides the required methods to
animate the UIPickerViews.
The dbOperations NSArray is the data source for the database operations pick list. The databases
NSMutableArray is the data source for the database pick list. The dbOperation DatabaseOperations’
variable provides the implementation of that class. The selectedDatabase and selectedOperation
variables contain the values that will be selected from the corresponding pick list when the
14
Updating an iOS SQLite Database
app is running. The databasePickerView and the operationsPickerView are IBOutlets which are
connections between the UI elements in the storyboard that will be defined in the next section.
This also applies to the IBAction, updateDatabase, which creates an interface between the UIButton
in the UI and the corresponding View Controller. Take a look at the following screenshot for a
reference. notice the filled in dots in the margin next to these properties. I will explain how to set
this up in the next section for any newbies.
The last element to add is the DoDatabaseOperation method which will implement the code to
implement the database operations.
Figure 1: IBOutlet and IBAction Connections
1
2
3
4
5
6
7
8
9
10
11
12
//
//
//
//
//
//
//
ViewController.h
Update SQLite Database
Created by Kevin Languedoc on 4/27/14.
Copyright (c) 2014 Kevin Languedoc. All rights reserved.
#import <UIKit/UIKit.h>
#import "DatabaseOperations.h"
@interface ViewController : UIViewController <UIPickerViewDataSource,
15
Updating an iOS SQLite Database
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
UIPickerViewDelegate, UIAlertViewDelegate>
@property(nonatomic,
@property(nonatomic,
@property(nonatomic,
@property(nonatomic,
@property(nonatomic,
strong)
strong)
strong)
strong)
strong)
NSArray * dbOperations;
NSMutableArray *databases;
DatabaseOperations *dbOperation;
NSString * selectedDatabase;
NSString * selectedOperation;
@property (strong, nonatomic) IBOutlet UIPickerView *databasePickerView;
@property (strong, nonatomic) IBOutlet UIPickerView *operationsPickerView;
-(void)DoDatabaseOperation:(NSString*)database :(NSString*)ops;
- (IBAction)updateDatabase:(id)sender;
@end
The first thing we need to do is create the getter setter methods which is accomplished using
the @syntheize directive. You can create the properties like I have one in the code below, or you
can create them individually each on their own line and assign a different name like @synthesize
dbOperations = _ dbOps;. I prefer the first way because it is easier to keep track of the properties in
larger apps.
The database operations data source, dbOperations, will populated when the app is loaded through
the viewDidLoad method. You will notice from the code below that I am using the constants from
the DatabaseOperations class. Since these constants I don’t need to create an object before using
them.
The UIPickView protocols have some mandatory methods. These are numberOfComponentsInPickerView, ** numberOfRowsInComponent, ** titleForRow and ** didSelectRow**. We will look
at each of these next along with the DoDatabaseOperation and UpdateDatabase.
The numberOfComponentsInPickerView lets you set the number of columns your pick list will have.
You can hardcode this value or set dynamically. For this example, I am setting both UIPickListViews
to 1 column. The numberOfRowsInComponent method allows us to set the number of rows each
UIPickListView will have. Since we have two pick list, I have set the tag value to 1 and 2 and
depending depending which of the two is being referenced, I will set according to the number of
elements in the arrays of their respective data source. I will show you how to set the tags in the
Storyboard of the Interface Builder (IB) in the next section. You don’t have to explicitly call these
methods. Since we implemented the protocols, these methods get called automatically when the app
is running.
The titleForRow follows the same pattern as the previous method except that I am referencing the
each value in the arrays. This displays the actual values in the UIPickListView. The didSelectRow
Updating an iOS SQLite Database
16
is called when the user makes a selection in either of the pick list. the selected value will be stored
in one of two variables, selectedDatabase or selectedOperation, that we will later use as input to let
the DatabaseOperations object know which SQL operations to perform on which SQLite database.
The DoDatabaseOperation simply determines which DatabaseOperations method to call based on
the selectedOperations value. To create a database and delete a database , I chose to use a dedicated
method because the logic didn’t follow the same pattern as the other modification operations. At the
start of the method, I initialize a DatabaseOperations object called dbOperation which I created in
the header file. I then check to see if the ops parameter is equal to CREATE_DATABASE which calls
the BuildDatabase method and then calls the reloadAllComponents method of the UIPickerView
protocol to call the protocol methods like we did when the app was launched. This will rebuild
the data source and allow the UIPickerView to display the new values. If the ops value is DROP_DATABASE, then the DeleteDatabase method is called. Otherwise the ModifyDatabase method is
called passing the database name and the selected database operation that will be executed.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//
//
//
//
//
//
//
ViewController.m
Update SQLite Database
Created by Kevin Languedoc on 4/27/14.
Copyright (c) 2014 Kevin Languedoc. All rights reserved.
#import "ViewController.h"
@interface ViewController ()
@end
@implementation ViewController
@synthesize dbOperations,databases, dbOperation,
selectedDatabase,selectedOperation;
- (void)viewDidLoad
{
[super viewDidLoad];
/*
Setup data source for Db Operations list
*/
dbOperations = [[NSArray alloc] initWithObjects:
@"",
CREATE_DATABASE,
Updating an iOS SQLite Database
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE_INDEX,
CREATE_VIEW,
CREATE_TRIGGER,
ALTER_DATABASE,
ALTER_TABLE,
DROP_DATABASE,
DROP_TABLE,
DROP_INDEX,
DROP_VIEW,
DROP_TRIGGER,
nil];
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
//number of columns
- (NSInteger)numberOfComponentsInPickerView:(UIPickerView *)
pickerView{
return 1;
}
// returns the # of rows in each component..
- (NSInteger)pickerView:(UIPickerView *)pickerView
numberOfRowsInComponent:(NSInteger)component{
if(pickerView.tag==1){
return [databases count];
}else if (pickerView.tag==2){
return [dbOperations count];
}else{
return 0;
}
}
- (NSString *)pickerView:(UIPickerView *)pickerView
titleForRow:(NSInteger)row forComponent:(NSInteger)component{
17
Updating an iOS SQLite Database
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
if(pickerView.tag==1){
return [databases objectAtIndex:row];
}else if (pickerView.tag==2){
return [dbOperations objectAtIndex:row];
}else{
return @"";
}
}
- (void)pickerView:(UIPickerView *)pickerView
didSelectRow:(NSInteger)row inComponent:(NSInteger)component{
if(pickerView.tag==1){
selectedDatabase = [[NSString alloc]
initWithString:[databases objectAtIndex:row]];
}else if (pickerView.tag==2){
selectedOperation = [[NSString alloc]
initWithString:[dbOperations objectAtIndex:row]];
}
}
-(void)DoDatabaseOperation:(NSString*)database :(NSString*)ops{
if([ops isEqual: CREATE_DATABASE]){
[dbOperation BuildDatabase:database];
[dbOperation GetDatabaseList];
[self.databasePickerView reloadAllComponents];
}else if([ops isEqual:DROP_DATABASE]){
[dbOperation DeleteDatabase:database];
[dbOperation GetDatabaseList];
[self.databasePickerView reloadAllComponents];
}else{
[dbOperation ModifyDatabase:database :ops];
}
}
- (IBAction)updateDatabase:(id)sender {
if(selectedDatabase == nil && selectedOperation ==nil)
{
UIAlertView *alert = [[UIAlertView alloc]
initWithTitle:@"Warning"
18
19
Updating an iOS SQLite Database
113
114
115
116
117
118
119
120
121
122
123
124
125
126
message:@"You need to select a database
and an operation"
delegate:self
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[alert show];
}else{
[self DoDatabaseOperation:selectedDatabase :selectedOperation];
}
}
@end
This wraps up the controller section of the application. We have seen how to setup the database
operations to modify a SQLite database. The next section is focus on the UI.
Create The View
In the MVC design pattern, the View is the user interface or UI. My little app doesn’t have great UI.
It is simply a rudimentary functional UI to provide the basic functioning to my app. Ok that I have
hopefully managed expectations, we can begin building the UI.
Typically an app will have one Storyboard unless you are building an universal (iPad and iPhone )
app then you would have to Storyboards. Open the Storyboard that was created with the template.
For this app, we will need two UIPickerViews, one button (UIButton) and a couple of UILabels all
of which you can drag from the palette on the bottom right of the IDE.
20
Updating an iOS SQLite Database
Figure 2: Object Library Xcode
Once you have your UI laid out like in the previous screenshot, I want you to select each
UIPickerView in turn and select the Attributes inspector view on the right of the IDE and set the tag
attribute to 1 for the first UIPickerView and 2 for the second. Below is a screenshot of the utility.
21
Updating an iOS SQLite Database
Figure 3: Attributes inspector
Now we will create the connections. Open the header file of the ViewController for the UI by clicking
on the tuxedo icon (see screenshot below). In the following the code is already done. To create a
connection hold down the control button on the keyboard and drag a line from the top UIPickerView
over to the open header file using the mouse. When you release the mouse button a popover panel
will appear (figure 5) providing an input field to set the name of the IBOutlet. You will need to
set the IBOutlet to databasePickerView and click on the connect button. The code will be added
automatically to the header file and add the dot in the margin to indicate that a connection was
created. Repeat the process for the second UIPickerView, naming that one operationsPickerView.
For the UIButton, you follow the same process but you will need to change the connection type to
Action (figure 6).
22
Updating an iOS SQLite Database
Figure 4: Assistant editor
Figure 5: IBOutlet popover
Figure 6: IBAction editor
To add the configuration for the UIPickerView protocols, select each UIPickerView in turn and
control drag a connection line to the proxy icon (first yellow icon below the ViewController).
When you release the mouse button, a popover panel will appear allow you to select either the
dataSource or Delete Outlets. You will have to select both individually for each UIPickerView and
create connections for each. The screenshot below (figure 7) provides a visual.
23
Updating an iOS SQLite Database
Figure 7: Assistant editor
Now you are ready to go. No more programming is needed. When the app runs for the first time,
you will need to create a database, so turn the UIPickerView to the Create Database option and
click on the Update button. For the example, the database will be created using the test.db file name.
However ideally you would have an inout screen t allow a person to create a database. Once the UI
refreshes, the top UIPickerView should have test.db in the choices to chose from. Now you will be
able to select the database and perform the various options below
24
Updating an iOS SQLite Database
Figure 8: Running app
Conclusion
That is it. This chapter provided a working app to modify a new or existing database if you create one
beforehand. It also provides examples on all the update related queries that SQLite can handle.The
series of chapters will provide example and tutorials on inserting, selecting, updating and deleting
records from a database. You will be able to work with al data types, including images, videos and
audio files although it is not recommended to store this large files because of data storage. However
there are always exceptions to the rule.