Introducing SQLite for Mobile Developers (eBook)

Jesse Feiler is a developer, consultant, and author specializing in database technologies and location-based apps. He has worked with databases and data management on computers from mainframes to iPhone, iPad, and Apple TV using data management tools from DB2 (IBM) and DMSII (Burroughs) to Enterprise Objects Framework and Core Data, MySQL, Oracle,  and, of course, MySQL. In the early days of the web, he built the page caching mechanism for the Prodigy web browser for Mac using a relational database library similar in some ways to SQLite.He is the creator of Minutes Machine the meeting management app, as well as Saranac River Trail app a guide to the Trail that includes location-based updates as well as social media tools. His apps are available in the App Store and are published by Champlain Arts Corp (champlainarts-dot-com). As a consultant; he has worked with small businesses and nonprofits on projects such as production control, publishing, and project management usually involving FileMaker.His books include:•Swift for Dummies (Wiley, 2014)•iOS App Development for Dummies (Wiley, 2014)•iWork  for Dummies (Wiley, 2012)He is heard regularly on WAMC Public Radio for the Northeast's The Roundtable. founder of Friends of Saranac River Trail, Inc. A native of Washington DC, he has lived in New York City and currently lives in Plattsburgh NY.He can be reached at northcountryconsulting-dot-com (consulting) and champlainarts-dot-com (app development).

Contents at a Glance 4
Contents 5
About the Author 11
About the Technical Reviewers 12
Acknowledgments 13
Introduction 14
Chapter 1: Getting Up to Speed with Databases and SQLite 15
Moving Beyond Big 15
Databases Are Structured and Organized 16
Databases Are Smart 16
Writing Code Is Just the Beginning 17
Parlez-vous Python? Sprechen Sie Scala? 17
Relational Databases and SQL to the Rescue 18
Looking Inside a Relational Table and Query 19
Basic Query Structure 20
SQL Action: SELECT 20
SQL Data to Select: List of Column Names 20
SQL Data Source: Table Name 20
SQL Condition: WHERE 21
Looking at Other Query Choices 21
Chapter 2: Understanding What SQLite Is 22
Putting a Database in Perspective 22
Defining SQLite 23
SQLite Is Designed for a Single User 24
Single User Doesn’t Mean Single-Thread 24
Using SQLite with Multiple Users 24
SQLite Is Self-Contained 25
Self-Contained Code 25
Self-Contained Data 26
SQLite Supports Transactions and Is ACID-Compliant 26
Chapter 3: Using SQLite Basics: Storing and Retrieving Data 28
Using sqlite3 29
Run sqlite3 and Let It Create a New Database 29
Create and Name a New sqlite3 Database 30
Delete the Database 30
Run sqlite3 and Open an Existing Database 30
Experimenting with SQLite Syntax 31
Exploring Your sqlite3 Database with a Graphical SQLite Editor 32
Creating a Table 34
Using a Graphical SQLite Editor 34
Creating Table Columns 35
Using SQLite3 37
Inserting Data into a Table 37
Using a Graphical User Interface 37
Using SQLite3 39
Retrieving Data 39
Using a Graphical User Interface 39
Using sqlite3 40
Deleting Data 40
Summary 40
Chapter 4: Working with the Relational Model and SQLite 41
Building the Users Table 42
Building the Scores Table 43
Relating the Tables 44
Using Aliases to Identify Multiple Tables in a SELECT Statement 44
Using the rowid Primary Key 45
Changing a Name in One Table 46
Using a Foreign Key 46
Joining the Tables 49
Summary 50
Chapter 5: Using SQLite Features—What You Can Do with SELECT Statements 51
Looking at the Test Data 52
Ordering Data Makes It Easier to Use 52
Grouping Data Can Consolidate It 53
Using Variables in Queries 54
Summary 55
Chapter 6: Using SQLite with PHP 56
Putting PHP and SQLite Together: The Basics 57
Verifying PHP in Your Environment 57
Preparing the SQLite Database 58
Connecting to Your SQLite Database 61
1. Create a New PDO Object 62
2. Create and Prepare the Query 63
3. Execute the Query 63
4. Fetch the Results 63
5. Use the Results 63
Summary 65
Chapter 7: Using SQLite with Android/Java 66
Integrating SQLite with Any Operating System, Framework, or Language 66
Using Android and SQLite 68
Using the Static Values 68
Static Values May Be in the APIs 68
Static Values May Be in Imported Files. 68
Static Values May Be in the Main File 68
Extend SQliteOpenHelper 69
Summary 71
Chapter 8: Using SQLite with Core Data (iOS and OS X) 72
Introducing the Core Data Framework 73
Using the Core Data Model Editor 74
Using Entities 77
Working with Attributes 79
Managing Relationships 80
Summary 84
Chapter 9: Using SQLite/Core Data with Swift (iOS and OS X) 85
Looking at the Core Data Stack 85
Fetching Data to the Core Data Stack 86
Structuring a Core Data App 86
Passing a Managed Object Context to a View Controller in iOS 87
Setting Up the Core Data Stack in AppDelegate for iOS 88
Creating applicationDocumentsDirectory in iOS 88
Creating managedObjectModel in iOS 88
Creating persistentStoreCoordinator in iOS 89
Creating managedObjectContext in iOS 90
Setting Up the Core Data Stack in AppDelegate for OS X 91
Creating application DocumentsDirectory in OS X 91
Creating managedObjectModel in OS X 92
Creating persistentStoreCoordinator in OS X 92
Creating managedObjectContext in OS X 94
Creating a Fetch Request in iOS 94
Saving the Managed Object Context 95
Saving in iOS 95
Saving in OS X 96
Working with NSManagedObject 97
Creating a New NSManagedObject Instance 98
Working with a Subclass of NSManagedObject 100
Summary 105
Chapter 10: Using SQLite/Core Data with Objective-C (iOS and Mac) 106
Looking at the Core Data Stack 107
Fetching Data to the Core Data Stack 108
Objective-C Highlights 108
Using Quoted Strings 108
Objective-C Is a Messaging Language 108
Using Brackets in Objective-C 108
Chaining Messages 109
Ending Statements with a Semicolon 109
Separating Headers and Bodies in Objective-C 109
Looking at Method Declarations 110
Handling nil in Objective- C 110
Structuring a Core Data App with Objective-C 111
Passing a Managed Object Context to a View Controller in iOS 111
Setting up the Core Data Stack in AppDelegate for iOS 112
Creating the App Delegate Header 113
Synthesizing Properties in AppDelegate. m 113
Creating applicationDocumentsDirectory in iOS 113
Creating managedObjectModel in iOS and OS X 114
Creating persistentStoreCoordinator in iOS 114
Creating managedObjectContext in iOS 115
Setting Up the Core Data Stack in AppDelegate for OS X 116
Creating applicationDocumentsDirectory in OS X 116
Creating managedObjectModel in OS X 117
Creating persistentStoreCoordinator in OS X 117
Creating managedObjectContext in OS X 118
Creating a Fetch Request in iOS 119
Saving the Managed Object Context 120
Saving in iOS 120
Saving in OS X 121
Working with NSManagedObject 121
Creating a New NSManagedObject Instance 122
Working with a Subclass of NSManagedObject 124
Summary 129
Chapter 11: Using the Simple Database with a PHP Web Site 130
Reviewing the Database 130
Previewing the Web Site 133
Implementing the PHP Web Site 136
Looking at the Basic PHP/SQLite Structure 137
Building the Drop-Down Selection List ( phpsql1.php) 139
Showing the Selected Data ( phpsql2.php) 140
Adding New Data ( phpsql3.php) 142
Using Try/Catch Blocks with PHP and PDO 143
Summary 143
Chapter 12: Using the Simple Database with a Core Data/iOS App 144
The Story Continues… 144
Adjusting the Data Model and Template for Core Data 145
Getting Rid of Keys and Revising the Data Model 146
Changing timeStamp to name 146
Create a New Database on Your Device or Simulator 147
Add the Score Table and Interface to the App 147
Making Sure You Can Add New Users with + in the Master View Controller 147
Working with the Detail View 148
Working with the Detail View for Score 148
Use NSManagedObject Subclasses 150
Use a Table View Controller for DetailViewController 150
Modify DetailViewController Code for DetailViewController 151
Using the Subclasses 151
Change detail to detailUser 152
Change configureCell to Use the Subclass 152
Modify MasterViewController to Pass the User to DetailViewController 152
Summary 153
Index 154