Launching SQLite Manager

Once the SQLite Manager is installed via the Add-on gallery, you will be able to launch it from the Tools menu in Firefox. Figure 2-1 shows the SQLite Manager menu item in the Tools menu in Firefox.

Figure 2-1. SQLite Manager menu in Firefox

The SQLite Manager Menu

The SQLite Manager has a quick-access menu of the main activities you can perform on a SQLite database, in addition to various menu options. Under each menu option you can create, drop, rename, and modify the listed database element. Under the Database menu, in addition to creating, modifying, indexing, re-indexing, attaching, and detaching a database, you can also compact a database and create in-memory databases. The app also offers options to analyze a database.

Figure 2-2 provides a visual of the SQLite Manager Toolbar. You can choose to create new databases, tables, and views. You can also open an existing database.

Figure 2-2. SQLite Manager toolbar

For the purposes of this example, I will create a database and then add tables, views, a trigger, and indexes. Once the database is created, I will add it to the Db Mgr project.

The Directory selector lets you change the current directory from the Profile directory, which is the directory used by Firefox to store the various databases it uses, to use the directory where our databases will be located.

Create the Database

You can create the database using the new document icon in the menu bar or by choosing the New Database option under the Database menu item. I will name the database Chapter 2 for this example. By default, the .sqlite extension is used; however, you can change this behavior in the settings (the criss-cross screwdriver/wrench icon). Figure 2-3 shows the dialog box that lets you provide the SQLite database name.

Figure 2-3. Name the SQLite database

SQLite Manager offers you many options to tweak and analyze a database. Switch to the Db Settings tab, for instance, after the database is open, and you can set a panoply of different settings to fine tune the database (Figure 2-4). I will accept the defaults for this example, but I invite you to explore these many features of SQLite Manager and the different settings available through the SQLite API.

Figure 2-4. SQLite Manager Settings tab

I would like to touch on a couple of interesting and useful concepts, which are the sqlite_master table located under the Master Table node, the sqlite_sequence table, and the Main database versus the Temp database or other databases.

Sqlite_master

The sqlite_master table contains all the queries that are used to create the database schema; for instance, when you add or modify a table, view, or trigger. The table also contains the names and types of each database schema element. Later in the chapter, we will make use of this table to populate the data source for the TableView in the MasterViewController in the next chapter. The schema for the table looks like the code snippet that follows. SQLite creates this table for you when the first table is added to the database. This is a representation only:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

You can also browse the table by selecting the Browse & Search tab with the sqlite_master table selected. You can also browse the information in the table or any other table by performing a SELECT query on it, as in the code snippet following Figure 2-5.

Figure 2-5. Sqlite_master Browse & Search window

SELECT * FROM sqlite_master                    

or                    

SELECT name, tbl_name FROM sqlite_master                    

The sqlite_sequence

The sqlite_sequencetable maintains the largest ROWINDEXof a given table. It is used in conjunction with the AUTOINCREMENT property on a column. When I create the tables in the database, I will use AUTOINCREMENT as the primary key. If the table is empty, then the largest ROWID will be 1 and so on as records are added to the table.

The last feature I want to touch on is the Main database. In SQLite you can attach many databases to the same connection or database file. When you create a new database file or connection, SQLite automatically adds a database to the file and calls it Main. You have the option to create additional databases in the same connection and you name these databases separately and attach them together using the ATTACHcommand. Likewise, you can remove these auxiliary databases using the DETACHcommand.

Figure 2-6 shows a screenshot the Directory menu item. The Directory menu is another useful way of locating SQLite databases and their home directory. You can locate the database file on disk by choosing the "Default User Directory" option. This will open a Finder window if you are on OS X or a File Explorer window if you are using Windows.

Figure 2-6. SQLite Manager Directory menu

With the basic database in place, I will move to the next step and create a table and columns .

Add Table and Columns

Creating tables and columns in SQLite is quite easy. You can opt to use the new table icon or select the Create Table option from the Table menu. I have provided a screenshot of the Create Table interface in SQLite Manager (Figure 2-7). When you enter the table definition, it only captures information to build a Create Table query.

Figure 2-7. SQLite Manager's Create Table interface

You’ll notice the dropdown menu for the database selection near the top of the interface. You have a choice between main, which is already selected, and temp. If you attach other databases, they would appear here as well.

If you have experience developing database applications, you will find many of these fields self-explanatory. You have a field for the table name. You can choose to make the table temporary using the “Temporary table” option. You should always select the “if not exists” option so you don’t accidentally overwrite the database table and its contents when running the app. This option is added to the query, and the database engine checks to see if the table already exists before creating it.

Figure 2-7 illustrates how you can also define your table columns. You can provide a column name, a data type—the dropdown includes the data types supported by SQL language—and whether the column will act as the primary key. You can have the column auto increment by enabling the "Autoinc" option. Note that this option is only active if you opt to enable the primary key on the column. Of course, you can choose to not allow nulls by selecting the "Yes" option. This is especially important for primary key fields. The "Unique" option ensures that no duplicates are introduced through an INSERTor UPDATE. Finally, you can set a default value depending on the selected data type.

Once the table definition is complete and you click on the OK button, a confirmation will appear displaying the exact query that will be performed to create the table. This is a great way to learn the proper syntax to use to prepare the SQL queries supported by SQLite.

For my sample database, I will create a table to store some basic information on books, like the book title, author, pages, selling price, royalties, and publisher. Figure 2-8 provides the basic details of the table. The screenshot immediately following the table structure provides the details of the actual query string being used to create the table in the database (Figure 2-9). You could easily do a copy + paste in another iOS application if you weren’t sure of the proper syntax or if you were lazy like I am sometimes and didn’t feel like writing the code.

Figure 2-8. Create Table definition

Figure 2-9. Create Table query

In SQLite Manager, you can browse the table structure by selecting the table name and expanding the node in the left pane, then selecting the Structure tab along the top in the pane on the right-hand side (Figure 2-10). In addition to browsing the table structure, you can also perform some operations, like dropping the table or re-indexing the table.

Figure 2-10. Browse database structure

Notice in the screenshot that an index was automatically added under the Indexes node. This index got created as a result of my specifying a primary key in my Book table. SQLite also added a sqlite_sequence table, which I mentioned before, to maintain a handle on the latest ROWID. Although SQLite created the index for us, I’ll show you the syntax of the SQLite query needed to create your own.

Add an Index

In a SQLite database, as in all other relational databases, tables have indexes to help them locate records quickly. Creating indexes in SQLite is very straightforward, especially with SQLite Manager. Figure 2-11 shows the Chapter.sqlite schema in SQLite Manager with one index, sqlite_autoindex_book_1. This index is automatically created when a column is configured with the autoincrement property when creating a table.

Figure 2-11. Indexes in SQLite Manager

To create an index, select the Create Index command from the Index menu in SQLite Manager. The interface window provides the necessary fields to build the Index query in SQLite. You need to provide the name of the target table. In Figure 2-12, the Book table is preselected, since it is the only one in the database other than the sqlite_sequence table. In the Define Index Columns section, which displays the available columns of the selected table, you can define which columns are needed for the index. Click OK to generate the query shown in Figure 2-13.

Figure 2-12. Create Index interface in SQLite Manager

Figure 2-13. Create Index query

With the index now in place for the table, I will proceed with adding a view and a trigger before adding the finished database to an iOS project.

Add a View

A view provides a listing based on a SELECT statement run against the contents of a table. The view may present all the data in a table or a subset of data depending on your needs. A view can provide better performance when accessing large datasets if you are limiting yourself to a subset of the entire table's contents.

The View menu in SQLite Manager provides several operations that you can perform on a view in addition to creating a view. For instance, you can opt to modify a view, drop a view, or rename a view. For now, select the Create View command from the View menu. However, in the case of modifying or altering a view, in reality you would be performing a drop view/create view operation. You can only alter, in the true sense of the word, a table in SQLite. Figure 2-14 illustrates the Create View interface. In the following screenshot, you can see where to add the view name and the target database. You can also indicate if it is temporary or that SQLite should check to see if the view already exists. The Select Statement field lets you define the subset of records to view through the view.

Figure 2-14. Create View query interface

Figure 2-15 provides a confirmation of the query that will be used to generate the view. For this example, I am selecting all the records, but I could have easily created a subset of content by modifying the SELECT query accordingly. Click the OK button, and the SQLite database engine will provide the finished CREATE VIEWSQL query string to create the view.

Figure 2-15. Create View query confirmation

Views are a handy tool for accessing subsets of records in a table or tables, since you could add a JOIN or even an inline SELECT statement to build a more complex SELECT statement.

Add a Trigger

The trigger is the final database element I will demonstrate before adding the database to the iOS project. Triggers act on data in a table after a record is inserted, updated, or deleted. These database programs are useful for performing a given operation or generating logs in case you need to maintain an audit trail.

Triggers can be created from the Trigger menu in SQLite Manager or from the context menu by right-clicking on the Trigger node, like all the other database schema elements, as shown in Figure 2-16.

Figure 2-16. Create Trigger context menu

The Trigger interface window provides the necessary fields to define the trigger (Figure 2-17). You need to specify a name and the target table or view. You also need to indicate if the trigger is launched before, after, or instead of the creation of a record, as well as which database event the trigger is the result of, like UPDATE, INSERT, or DELETE.

Figure 2-17. Create Trigger interface

Next, you need to write the query to be performed on the records. In this example, I want the trigger to calculate the royalties an author will receive on book sales. I have a provided the code snippet for the example trigger CalcRoyalties (Figure 2-18).

Figure 2-18. Create Trigger query

CREATE TRIGGER CalcRoyalties  AFTER  INSERT ON Book
FOR EACH ROW  
WHEN (Sales) >= 1
BEGIN
 update Book set Royalties = Sales * .15;

END

Once the trigger query is to your liking, you can add the trigger to the database by clicking the OK button. As usual, SQLite Manager will confirm the CREATE TRIGGER action before actually adding it to the database.

Add Database to the Project

Adding the database to the project is simple. From the File menu in Xcode, select the Add Files to “Db Mgr ...” command. A Finder window will open requesting the file location of the database. If you don’t remember where you saved the database file, you can go back to SQLite Manager and use Directory ➤ Select Default Directory to identify the location of the directory where the SQLite database file is located.

Select the file and click the Add button. By default, the file is added to the selected directory or group, if you selected a group. You can drag and drop the database file anywhere in the Explorer. However, as I mentioned earlier, the SQLite database file is read-only in this location. You need to copy or move the file to the Documents directory to ensure that it's writable.

For this example, I will add code to the AppDelegate application's didFinishLaunchWithOptionsmethod to copy the database file to the Documents directory. The code is provided here:

func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: [_ NSObject: AnyObject]?) -> Bool {

//....Code remove for brevity   ....
        var srcPath:URL
        var destPath:URL
        let dirManager = FileManager.default()
        let projectBundle = Bundle.main()
        do {
            let resourcePath = projectBundle.pathForResource("Chapter2", ofType: "sqlite")
            let documentURL = try dirManager.urlForDirectory(FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask, appropriateFor: nil, create: true)

            srcPath = URL(fileURLWithPath: resourcePath!)
            destPath = documentURL.appendingPathComponent("Chapter2.sqlite")

            if !dirManager.fileExists(atPath:destPath.path!) {
               try dirManager.copyItem(at: srcPath, to: destPath)
            }

        } catch let err as NSError {
            print("Error: (err.domain)")
        }

    }

This code will run every time the app is started and will check to see if the file is located in the Documents directory. If it’s not, then it is copied. Ideally, the code should check to see if the file in the bundle is newer than the version in the Documents directory and update accordingly.

There are different ways you could handle this, like having a button in the UI that provides the syntax to select the file and copy it to the Documents directory. Or, you could have the same code in the viewDidLoad method of the main ViewController that is loaded after the app is started.