This chapter doesn’t show any new SQLite APIs. Rather, it focuses on how you can use the SQLite APIs to create an iOS iPhone app for searching for records in a SQLite database. In this chapter, we will explore the following:
Creating an iOS app
Creating a SQLite database
Adding the Search function
Developing the UI for searches
Searching for records
Displaying search results
Developing a UISearchBar iPhone app
The Search App
This tutorial demonstrates how to use the SELECT statement to search for content in a database using the UISearchBar and display the results in a TableView embedded in a ViewController. Figure 9-1 provides a visual of the running app.
Figure 9-1. The Search iPhone app
The UISearchBarand UITableViewController are embedded in the ViewController. The search term is passed to a function in the ViewController.
The UISearchBar is an iOS component in the UIKit that was introduced in version 2.0 of the Cocoa Touch Framework and iOS SDK. The UIControl and protocol have several important features to help a developer quickly implement a Search field in their applications. You can enable a Search button, a Cancel button, and a Bookmark button. The delegate has methods that interact with those buttons that are pressed.
This tutorial will demonstrate how to quickly develop an iPhone app that searches a SQLite database using the UISearchBar text field. The database contains a list of names in separate columns. The app will implement a SQL query to search either field, then display the results in a UITableView.
The whole application is built using a single-view application template. The SQLite database is built and sample data is added to it using the SQLite Manager in Firefox.
Create the SQLite Database
For this example app, we will create a SQLite database using SQLite Manager in Firefox, which is a free add-on. As Figure 9-2 shows, we create a database titled dbsearch.sqlite and one table, names, to hold a sample of first and last names. The file should be saved to a convenient location, since it will have to be added to the iOS project later.
Figure 9-2. Create dbSearch.sqlite and names table
Then, we add two columns:
firstname:varcharlastname:varchar
Figure 9-3 shows a screenshot of how SQLite Manager in Firefox creates an input screen based on the columns you define. Using SQLite Manager, we add some sampling data so that we can perform a search later. Figure 9-4 shows the sample data entered into the database through SQLite Manager in Firefox.
Figure 9-3. SQLite data input
Figure 9-4. Sample data in dbSearch.sqlite
Create the iOS/SQLite Project
Figure 9-5 shows where to select a template for the project. Choose the single-view application template from the iOS Project category to create a simple iOS iPhone app. Call the app SQLiteSearch and ensure that the Swift language is selected in the language options.
Figure 9-5. Create th e SQLiteSearch app
Once the project is created, you need to add the sqlite3 library to the project and create the bridge. Figure 9-6 shows how to add the sqlite3 library to the project. Select the project root in the navigator and scroll to the Linked Libraries and Frameworks section on the General tab. Click on the “+” button to bring up the library selector popup. In the Search field, type "sqlite" and then select the libsqlite3.tbd library. Click Add to close the popup and add the library to the project.
Figure 9-6. Add the sqlite3 library
Next, right-click on the DbSearch group and select the “Add files to …” context-menu item. Browse to the location where the dbsearch.sqlite file was saved, select the database file, and click Add to begin copying the database to the project. A second popup will appear from which you will need to select the first option: “copy items to destination group’s folder (if needed)” to actual the project. This is important, or only a reference will be added. Now that the project is set up, we can build the storyboard and controller logic.
Set Up the Bridge
With the library in place, we must set up the Objective-C bridge. Add a new file to the project by selecting the Cocoa Touch class template from the iOS Source category from the available templates (Figure 9-7), then do the following:
Name the bridge SQLiteSearchBridge
Select the category for the file types, which will trigger the Add Bridge Interface
Select the NSObject class
Figure 9-7. Create the SQLiteSearchBridge
Figure 9-8 shows the Create Bridge Interface , which appears after adding the file to the project. When we create the bridge using this method, Xcode will create the bridge file and add the file to the Build settings for the Swift Compiler. You can discard the Objective-C header and implementation files that are also created. In this project, the files are NSObject+SQLiteSearchBridge.h and NSObject+SQLiteSearchBridge.m. We only need the SQLiteSearch-Bridging-Header.h file. You need to open this latter file and add the #import <sqlite3.h > directive in order to interface with the SQLite3 API.
Figure 9-8. Setting up the bridge in the Xcode
The Controller Code
Before getting to the interface and adding the controls and the IBOutlets, we will need to add the code to the ViewController to handle the search operations and interface with the UISearchBar, UITablewView, and UITableViewCell.
Open the ViewController and add the UISearchBarDelegate, UITableViewDelegate, and UITableViewDataSource next to the class definition (see following code). We will also need to set up the delegates when the app loads, so we need to assign the delegates to selfin the viewDidLoad function.
import UIKitclass ViewController: UIViewController, UISearchBarDelegate, UITableViewDelegate, UITableViewDataSource {// code skipped for clarityoverride func viewDidLoad() {super.viewDidLoad()searchResults.dataSource = selfsearchResults.delegate = selfsearchField.delegate = self}
A variable for an array will be used to store that data for the UITableView, hence its data source is needed as well. For this project, the array, nameList, is of the String data type. If you want to manipulate the data in the UITableView, you would interface with its data source. In other words, with the array. Also, we need to create an UnsafeBirCast pointer called SQLITE_TRANSIENT. This is a destructor pointer that is used in the sqlite3_bind_text later in the searchDatabase function.
var nameList = [String]()internal let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
For the UITableViewDelegateand UITableViewDataSource protocols, as well as for the UITableView itself, we need to add some required functions. These are described next.
The numberOfSectionsInTableView Function
To display the returned results, we will need to set up the UITableView delegate and data source. numberOfSectionsInTableViewtells the table how many sections the table will have. Sections are groups of rows. We will set this to 1:
func numberOfSectionsInTableView(tableView: UITableView) -> Int{return 1}
The tableView:numberOfRowsInSection Function
numberOfRowsInSectiontells the table how many rows to display. It is customary to indicate the number of elements in the array or data source:
func tableView(tableView: UITableView,numberOfRowsInSection section: Int) -> Int{return nameList.count}
The tableView:cellForRowAtIndexPath:indexPath Function
This method configures the UITableCell by getting a handle on the cell prototype in the UITableView and assigning the value at indexPath of the array to the label property of the cell. This method will be called repeatedly for each row that was defined in the numberOfRowsInSection function or the number of objects in the array.
The cell identifier, searchResultCell, will be configured later in the UI. Also, famousName is the UILabel IBOutlet that we will add later in the UITableViewCell. With the cell variable set up and cast as a searchResultCellTableViewCell UITableViewCell type, the other variable, nameObject, is assigned the value from the current array index. This value is then assigned to call.famousName.text:
func tableView(tableView: UITableView,cellForRowAtIndexPath indexPath: NSIndexPath) -> UITableViewCell{let cell = tableView.dequeueReusableCellWithIdentifier("searchResultCell", forIndexPath: indexPath) as! searchResultCellTableViewCell// Configure the cell...let nameObj = nameList[indexPath.row]cell.famousName.text = nameObjreturn cell}
So, for the UISearchBardelegate, UISearchBarDelegate, which requires the implementation of the searchBarCancelButtonClicked and searchBarSearchButtonClicked methods, the interactivity will respond to the buttons in the UISearchBar. For the UITableView, the UITableViewDelegate and UITableViewDataSource protocols. The function definitions are provided next.
The searchBarCancelButtonClicked Function
The searchBarCancelButtonClicked method will reset not only the UISearchBar field but also its data source and UITableView, and will also dismiss the keyboard. The complete code is provided here:
func searchBarCancelButtonClicked(searchBar: UISearchBar){self.searchField.text=""searchResults.reloadData()searchField.resignFirstResponder()self.view.endEditing(true)}
The searchBarSearchButtonClicked Function
The searchBarSearchButtonClicked method is similar in design to searchBarCancelButtonClicked except that it calls the searchDatabase method. Then, the code resets the Search field, reloads the data in the UITableView, and resigns the keyboard:
func searchBarSearchButtonClicked(searchBar: UISearchBar){self.searchDatabase(searchField.text!)self.searchField.text=""searchResults.reloadData()searchField.resignFirstResponder()self.view.endEditing(true)}
The searchDatabase Function
The other addition is a method to interface with the database: searchDatabase. This method will take one argument for the search term. Technically, you should be able to pass multiple search terms, which would be split into an array, but for simplicity's sake I am assuming a single-term search term.
The first variable, fileExist, is a Boolean. It will allow us to ensure that the dbSearch.sqlite file is available in the main application bundle. The db variable is a COpaquePointer for the SQLite database. Likewise, the sqlStatement variable is a COpaquePointer for the sqlite3_stmt statement.
In order to open the database, we need to get the path to the file in the main application bundle. We are leaving it there because we don't need to write to it and the main bundle is read-only during runtime. The projectBundle gets a handle to the mainBundle through the NSBundle class. Then the fileMgr constant is created as an NSFileManager. This class handles interactivity with the file system. The resourcePath String constant will be assigned the fully qualified path to the database using pathForResource.
Once these constants and variables are defined and assigned their initial values, we check to make sure the database file is available using fileExistsAtPath, which returns a Boolean value. If the database file exists, the database is opened using the sqlite3_open function. SQLITE_OK is returned if the database is successfully opened or will print an error message to the console if not.
Once open, we define a SQL SELECT query string, sqlQry, which takes two arguments in the WHERE clause. Both arguments will receive a copy of the searchTerm argument that is passed to the function from the UISearchBar IBOutlet. We then assign and bind the inout values to the SQL query using the sqlite3_bind_text function—one for each argument in the WHERE clause. Then, the code will execute and return the values if successful. These values are then assigned to the concatName constant and in turn are appended to the nameList array. Finally, the memory is cleaned up and the database is closed.
func searchDatabase(searchTerm:String){var fileExist:Bool = falsevar db:COpaquePointer = nilvar sqlStatement:COpaquePointer=nillet projectBundle = NSBundle.mainBundle()let fileMgr = NSFileManager.defaultManager()let resourcePath = projectBundle.pathForResource("dbsearch", ofType: "sqlite")fileExist = fileMgr.fileExistsAtPath(resourcePath!)if(fileExist){if(!(sqlite3_open(resourcePath!, &db) == SQLITE_OK)){print("An error has occured.")}else{let sqlQry = "SELECT firstname,lastname FROM names where firstname=? or lastname=?"if(sqlite3_prepare_v2(db, sqlQry, -1, &sqlStatement, nil) != SQLITE_OK){print("Problem with prepared statement " + String(sqlite3_errcode(db)));}sqlite3_bind_text(sqlStatement, 0, searchTerm, -1, SQLITE_TRANSIENT)sqlite3_bind_text(sqlStatement, 1, searchTerm, -1, SQLITE_TRANSIENT)while (sqlite3_step(sqlStatement)==SQLITE_ROW) {let concatName:String = String.fromCString(UnsafePointer<Int8>(sqlite3_column_text(sqlStatement,0)))! + " " + String.fromCString(UnsafePointer<Int8>(sqlite3_column_text(sqlStatement,1)))!print("This is the name : " + concatName)nameList.append(concatName)}sqlite3_finalize(sqlStatement);sqlite3_close(db);}}}
With the array populated, the UITableView in the ViewController will display the results.
The searchResultCellTableViewCell Function
Before we get to the storyboard, we need to add a UITableViewCell object called searchResultCellTableViewCell. We will add the IBOutlet for the UILabel that we will add to the cell prototype next.
Develop the Storyboard
The storyboard is going to be very simple, with only a UISearchBar control, a UITableView, and the corresponding UITableCell. Figure 9-9 depicts the UISearchBar control that needs to be added to the ViewController. Before adding any controls, select the ViewController and set Simulator Size to iPhone 4.7 inch. You can adjust the setting in the Attributes inspector.
Figure 9-9. Adding the UISearchBar to the ViewController
The UISearchBar & UITableView
Drag a UISearchBar onto the canvas and add it to the top of the scene (Figure 9-9). Figure 9-10 shows how to set the attributes for the Cancel and Search buttons. With the control key pressed, open the Attributes inspector and select the following options:
Figure 9-10. Setting the UISearchBar attributes
Shows Search Results button
Shows Cancel button
We won’t need the other options for this example app. Next, add a UITableView UIControl and a UITableCell (Figure 9-11). Superimpose the cell control on the table. With the UITableCell selected, add an identifier through the Attributes inspector page. Drag a connection from the UITableView to the UIViewController proxy (yellow globe or circle on the bar at the top of the main scene), as shown in Figure 9-12. When you release the mouse button, a popup will appear to allow you to set the delegate and the data source. Set both of them.
Figure 9-11. Adding the UITableView and UITableViewCell
Figure 9-12. Adding proxy for table delegate and data source
The IBOutlets
As shown in Figure 9-13, next we will create the IBOutlets. Open the Assistant Editor by clicking on the double circle icon in the toolbar. To create the IBOutlets, drag a connection (ctrl + drag) using the mouse button to the open header file in the Assistant Editor. Releasing the mouse button activates a popup, thus allowing you to create an IBOutlet connection by entering a name for the connection in the appropriate field and clicking on Connect. For the UISearchBar, I named the IBOutlet searchField. Repeat this operation for the UISearchBar and the UITableView. For the UITableView, I named the outlet searchResults.
Figure 9-13. Add the searchField IBOutlet
@IBOutlet weak var searchField: UISearchBar!@IBOutlet weak var searchResults: UITableView!
The Prototype Cell
Finally, for the prototype cell we need to configure the cell identifier and add a UILabel to display the search results. To configure the cell identifier, open the document outline and select searchResultCell from the document hierarchy (Figure 9-14). Then, open the Attributes inspector, add the searchResultCell name, and hit Enter. This value is used in the ViewController, as we saw in the previous section.
Figure 9-14. Configuring the prototype cell
Also add a UILabel to the cell and add an IBOutlet to the searchResultCellTableViewCellController by dragging a connection to the open Identity inspector.
import UIKitclass searchResultCellTableViewCell: UITableViewCell {@IBOutlet weak var famousName: UILabel!//code removed for brevity}
All that is needed now is to run the app and test the functionality.
Running the App
As Figure 9-15 shows, when a user enters a name in the Search field and performs the search by clicking on the Search button, the results, if any, are fetched from the database and displayed in the database. The UITableView is reset before each search. For this example, I entered the name David and hit Enter, since I am testing in the simulator.
Figure 9-15. Enter a search term, such as David
Figure 9-16 displays two names from the database that match our search term.
Figure 9-16. The search results
Summary
In this chapter, we revisited the SQLite SELECT statement and implemented a database search function, which can be helpful for locating information in a database. The next chapter will focus on attaching and using multiple databases in a single file.