You are previewing Inside Symbian SQL: A Mobile Developer's Guide to SQLite.
O'Reilly logo
Inside Symbian SQL: A Mobile Developer's Guide to SQLite

Book Description

This is the definitive guide for Symbian C++ developers looking to use Symbian SQL in applications or system software.

Since Symbian SQL and SQLite are relatively new additions to the Symbian platform, Inside Symbian SQL begins with an introduction to database theory and concepts, including a Structured Query Language (SQL) tutorial.

Inside Symbian SQL also provides a detailed overview of the Symbian SQL APIs. From the outset, you will “get your hands dirty” writing Symbian SQL code. The book includes snippets and examples that application developers can immediately put to use to get started quickly.

For device creators and system software developers, Inside Symbian SQL offers a unique view into the internals of the implementation and a wealth of practical advice on how to make best and most efficient use of the Symbian SQL database. Several case studies are presented – these are success stories 'from the trenches', written by Symbian engineers.

Special Features:

  • The book assumes no prior knowledge of databases

  • Includes detailed and approachable explanations of database concepts

  • Easy to follow SQL tutorial with SQLite examples

  • Unique view into the Symbian SQL internals

  • Troubleshooting section with solutions to common problems

Written by the Symbian engineers who implemented SQLite on Symbian, with more than 40 years combined Symbian C++ experience, this book is for anyone interested in finding out more about using a database on Symbian.

Table of Contents

  1. Copyright
  2. Foreword
  3. Foreword1
    1. Open Source
    2. And Finally . . .
  4. Author Biographies
  5. Author's Acknowledgments
  6. Symbian Acknowledgments
  7. Publisher's Acknowledgements
  8. 1. Introduction
    1. 1.1. Where Should You Start?
    2. 1.2. Symbian Terminology and Version Numbering
    3. 1.3. The Relational Model and the Structured Query Language (SQL)
    4. 1.4. What Is Symbian SQL?
    5. 1.5. A Well-Oiled Machine
    6. 1.6. Tools and Troubleshooting
    7. 1.7. Further Reading and Resources
  9. 2. Getting Started
    1. 2.1. Where to Get Symbian SQL
    2. 2.2. Overview of Symbian SQL APIs
    3. 2.3. First Database Example
      1. 2.3.1. Project Setup
      2. 2.3.2. Creating a Database
      3. 2.3.3. Creating a Table
      4. 2.3.4. Creating an Index
      5. 2.3.5. Inserting Records
      6. 2.3.6. Selecting Records
      7. 2.3.7. Updating a Record
      8. 2.3.8. Updating Records Using Column Binding and Streaming
      9. 2.3.9. Deleting a Record
      10. 2.3.10. Closing a Database
    4. 2.4. Summary
  10. 3. The Relational Model
    1. 3.1. Background
      1. 3.1.1. The Three Components
      2. 3.1.2. SQL and the Relational Model
    2. 3.2. The Structural Component
      1. 3.2.1. The Information Principle
      2. 3.2.2. The Sanctity of the Logical Level
      3. 3.2.3. The Anatomy of the Logical Level
      4. 3.2.4. Tuples
      5. 3.2.5. Relations
        1. 3.2.5.1. Degree and Cardinality
        2. 3.2.5.2. Mathematical Relations
        3. 3.2.5.3. Relational Relations
      6. 3.2.6. Tables: Relation Variables
      7. 3.2.7. Views: Virtual Tables
        1. 3.2.7.1. Logical Data Independence
        2. 3.2.7.2. Updatable Views
      8. 3.2.8. The System Catalog
    3. 3.3. The Integrity Component
      1. 3.3.1. Primary Keys
      2. 3.3.2. Foreign Keys
      3. 3.3.3. Constraints
      4. 3.3.4. Null Values
    4. 3.4. Normalization
      1. 3.4.1. Normal Forms
      2. 3.4.2. First Normal Form
      3. 3.4.3. Functional Dependencies
      4. 3.4.4. Second Normal Form
      5. 3.4.5. Third Normal Form
    5. 3.5. The Manipulative Component
      1. 3.5.1. Relational Algebra and Calculus
      2. 3.5.2. The Relational Query Language
      3. 3.5.3. The Advent of SQL
    6. 3.6. The Meaning of Relational
    7. 3.7. Summary
  11. 4. Everything You Ever Wanted to Know about SQL but Were Afraid to Ask
    1. 4.1. The Relational Model
      1. 4.1.1. Query Languages
      2. 4.1.2. Growth of SQL
    2. 4.2. The Example Database
      1. 4.2.1. Installation
      2. 4.2.2. Running the Examples
    3. 4.3. Syntax
      1. 4.3.1. Commands
      2. 4.3.2. Literals
      3. 4.3.3. Keywords and Identifiers
      4. 4.3.4. Comments
    4. 4.4. Creating a Database
      1. 4.4.1. Creating a Table
      2. 4.4.2. Altering a Table
    5. 4.5. Querying a Database
      1. 4.5.1. Relational Operations
      2. 4.5.2. The Operational Pipeline
      3. 4.5.3. Filtering Rows
        1. 4.5.3.1. Values
        2. 4.5.3.2. Operators
        3. 4.5.3.3. Unary Operators
        4. 4.5.3.4. Binary Operators
        5. 4.5.3.5. Arithmetic and Relational Operators
        6. 4.5.3.6. Logical Operators
        7. 4.5.3.7. The LIKE Operator
      4. 4.5.4. Limiting and Ordering
      5. 4.5.5. Functions and Aggregates
      6. 4.5.6. Grouping
      7. 4.5.7. Removing Duplicates
      8. 4.5.8. Joining Tables
        1. 4.5.8.1. Inner Joins
        2. 4.5.8.2. Cross Joins
        3. 4.5.8.3. Outer Joins
        4. 4.5.8.4. Natural Joins
        5. 4.5.8.5. Preferred Join Syntax
      9. 4.5.9. Column Names and Aliases
      10. 4.5.10. Subqueries
      11. 4.5.11. Compound Queries
      12. 4.5.12. Conditional Results
      13. 4.5.13. The Thing Called NULL
      14. 4.5.14. Set Operations
    6. 4.6. Modifying Data
      1. 4.6.1. Inserting Records
      2. 4.6.2. Updating Records
      3. 4.6.3. Deleting Records
    7. 4.7. Data Integrity
      1. 4.7.1. Entity Integrity
        1. 4.7.1.1. UNIQUE Constraints
        2. 4.7.1.2. PRIMARY KEY Constraints
      2. 4.7.2. Domain Integrity
        1. 4.7.2.1. Default Values
        2. 4.7.2.2. NOT NULL Constraints
        3. 4.7.2.3. CHECK Constraints
        4. 4.7.2.4. Collations
      3. 4.7.3. Storage Classes
      4. 4.7.4. Manifest Typing
      5. 4.7.5. Type Affinity
        1. 4.7.5.1. Column Types and Affinities
        2. 4.7.5.2. Affinities and Storage
        3. 4.7.5.3. Affinities in Action
        4. 4.7.5.4. Storage Classes and Type Conversions
        5. 4.7.5.5. Makeshift Strict Typing
    8. 4.8. Transactions
      1. 4.8.1. Transaction Scope
      2. 4.8.2. Conflict Resolution
      3. 4.8.3. Transaction Types
    9. 4.9. Database Administration
      1. 4.9.1. Views
      2. 4.9.2. Indexes
        1. 4.9.2.1. Collation
        2. 4.9.2.2. Index Utilization
      3. 4.9.3. Triggers
        1. 4.9.3.1. UPDATE Triggers
        2. 4.9.3.2. Error Handling
        3. 4.9.3.3. Conflict Resolution
        4. 4.9.3.4. Updatable Views
        5. 4.9.3.5. Implementing Foreign Key Constraints
      4. 4.9.4. Attaching Databases
      5. 4.9.5. Cleaning Databases
      6. 4.9.6. The System Catalog
      7. 4.9.7. Viewing Query Plans
    10. 4.10. Summary
  12. 5. Database Concepts
    1. 5.1. Database Types
      1. 5.1.1. Public Databases
      2. 5.1.2. Private Databases
      3. 5.1.3. Secure Shared Databases
        1. 5.1.3.1. Access Control Policies
        2. 5.1.3.2. Access Control Policies in Use
        3. 5.1.3.3. Anti-spoofing Protection
    2. 5.2. Executing SQL
      1. 5.2.1. How the Database Processes SQL
      2. 5.2.2. One-shot Execution
      3. 5.2.3. Prepared Statements
        1. 5.2.3.1. Parameterized SQL
        2. 5.2.3.2. Working with Large Amounts of Data
        3. 5.2.3.3. Returning Results from Queries
    3. 5.3. Multi-Client Access
      1. 5.3.1. Transactions
      2. 5.3.2. Connections
      3. 5.3.3. Locking
        1. 5.3.3.1. Granularity of Locking
        2. 5.3.3.2. Symbian SQL Locking Specifics
        3. 5.3.3.3. Failing to Acquire a Lock
        4. 5.3.3.4. Read Uncommitted Mode
      4. 5.3.4. Connections, Transactions and Locking
      5. 5.3.5. Prepared Statements and Locking
    4. 5.4. Summary
  13. 6. Using Symbian SQL
    1. 6.1. The Database Connection Class
      1. 6.1.1. Connecting and Disconnecting
        1. 6.1.1.1. Database Configuration
        2. 6.1.1.2. Database Filename Conventions
      2. 6.1.2. Creating and Deleting Databases
      3. 6.1.3. Attaching Additional Databases
      4. 6.1.4. Executing SQL
      5. 6.1.5. Copying and Deleting a Database File
      6. 6.1.6. Managing Disk Usage
        1. 6.1.6.1. Retrieving the Database File Size and Free Space
        2. 6.1.6.2. Compacting a Database
        3. 6.1.6.3. Managing Low Disk Space
      7. 6.1.7. Transaction APIs
      8. 6.1.8. Retrieving the Security Policy
    2. 6.2. The Prepared Statement Class
      1. 6.2.1. Preparing and Executing SQL Statements
      2. 6.2.2. Retrieving Column Values
        1. 6.2.2.1. Extracting Column Values
        2. 6.2.2.2. Ancillary APIs
      3. 6.2.3. Parameterized Queries
    3. 6.3. Working with Variable-Length Data Objects
      1. 6.3.1. Retrieving Data
      2. 6.3.2. Retrieving Large Amounts of Data
      3. 6.3.3. Streaming Data
    4. 6.4. The Scalar Query Class
    5. 6.5. Security Policies
    6. 6.6. Summary
  14. 7. SQLite Internals on Symbian
    1. 7.1. Why SQLite Is Right for Symbian
    2. 7.2. The SQLite Modules
      1. 7.2.1. Public Interface
        1. 7.2.1.1. Database Connection Objects
        2. 7.2.1.2. Creating or Opening a Database
        3. 7.2.1.3. Closing a Database
        4. 7.2.1.4. Executing SQL Statements
        5. 7.2.1.5. Using Prepared Statements
        6. 7.2.1.6. Prepared Statement Objects
        7. 7.2.1.7. Preparing a Statement
        8. 7.2.1.8. Binding Values to a Prepared Statement
        9. 7.2.1.9. Stepping Through a Prepared Statement
        10. 7.2.1.10. Accessing Column Values of a Record
        11. 7.2.1.11. Resetting a Prepared Statement
        12. 7.2.1.12. Destroying a Prepared Statement
        13. 7.2.1.13. Handling Errors
      2. 7.2.2. Compiler
        1. 7.2.2.1. Tokenizer
        2. 7.2.2.2. Parser
        3. 7.2.2.3. Code Generator
      3. 7.2.3. Virtual Machine
      4. 7.2.4. B-Tree Module
        1. 7.2.4.1. Database File Format
        2. 7.2.4.2. B-tree of a Table
        3. 7.2.4.3. Record Data Packing
        4. 7.2.4.4. Overflow Pages
        5. 7.2.4.5. B-tree of an Index
      5. 7.2.5. Pager
        1. 7.2.5.1. Database File Locking
        2. 7.2.5.2. Page Cache
        3. 7.2.5.3. Rollback Journal File
        4. 7.2.5.4. Page Reuse and Auto-vacuum
      6. 7.2.6. OS Interface
    3. 7.3. SQLite Configuration Options
      1. 7.3.1. Auto-vacuum
      2. 7.3.2. Page Size
      3. 7.3.3. Maximum Cache Size
      4. 7.3.4. Database Encoding
      5. 7.3.5. SQL Statement Encoding
    4. 7.4. SQLite Optimizations on Symbian OS
      1. 7.4.1. Sharing the Cache
      2. 7.4.2. Retaining Page Cache Contents
      3. 7.4.3. Imposing a Soft Heap Limit
      4. 7.4.4. Increasing the Maximum Cache Size
      5. 7.4.5. Avoiding Unnecessary Reads
        1. 7.4.5.1. Deleting the Last Overflow Page Without Reading It
        2. 7.4.5.2. Reusing a Free Page Without Reading It
      6. 7.4.6. Avoiding Unnecessary Writes
        1. 7.4.6.1. Updating the Database Write Counter
        2. 7.4.6.2. Avoiding Page Journaling
      7. 7.4.7. Minimizing the Cost of Using Journal Files
        1. 7.4.7.1. Avoiding Journal File Deletion
        2. 7.4.7.2. Checking for a Hot Journal File
      8. 7.4.8. Mass Storage and Fast Retrieval of Binary Data (BLOBs)
        1. 7.4.8.1. Zeroblobs
        2. 7.4.8.2. BLOB Handles
        3. 7.4.8.3. Opening a Handle to a BLOB
        4. 7.4.8.4. Closing a Handle to a BLOB
        5. 7.4.8.5. Reading from a BLOB
        6. 7.4.8.6. Writing to a BLOB
        7. 7.4.8.7. Retrieving the Size of a BLOB
      9. 7.4.9. Background Database Compaction
    5. 7.5. Summary
  15. 8. Performance Tuning
    1. 8.1. Design and Development Considerations
      1. 8.1.1. Identify Key Use Cases
      2. 8.1.2. Set Performance Objectives
      3. 8.1.3. Measure Performance
      4. 8.1.4. Develop Performance Tests
      5. 8.1.5. Follow up Test Results
    2. 8.2. An Introduction to Optimization
      1. 8.2.1. Understand the Operation of the Application
      2. 8.2.2. Identify Bottlenecks Before Optimizing
      3. 8.2.3. Avoid Early Optimization
      4. 8.2.4. Maintain Performance Test Records
      5. 8.2.5. Carry Out One Optimization at a Time
      6. 8.2.6. Optimize in the Right Environment
    3. 8.3. Optimizing Applications
      1. 8.3.1. Application Layer
        1. 8.3.1.1. Use Active Objects with Care
        2. 8.3.1.2. Inspect Loops
        3. 8.3.1.3. Use Asynchronous APIs Carefully
        4. 8.3.1.4. Use Incremental Retrieval
      2. 8.3.2. Data Model
        1. 8.3.2.1. Normalization
        2. 8.3.2.2. Large Data Types
      3. 8.3.3. Indexing
        1. 8.3.3.1. Index Size
        2. 8.3.3.2. Avoiding Common Index Pitfalls
        3. 8.3.3.3. Choosing the Correct Index Type
        4. 8.3.3.4. Using ROWID as a Special Index
        5. 8.3.3.5. Using Multiple Indexes in a Statement
        6. 8.3.3.6. Optimizing Index Selection
      4. 8.3.4. SQL Queries
      5. 8.3.5. Memory Usage
        1. 8.3.5.1. Database Connections
        2. 8.3.5.2. Prepared Statements
        3. 8.3.5.3. Deleting Multiple Records
        4. 8.3.5.4. Manipulating Large Volumes of Data
        5. 8.3.5.5. Transient Tables
      6. 8.3.6. System Tuning
        1. 8.3.6.1. Auto-Vacuum
        2. 8.3.6.2. Page Size
        3. 8.3.6.3. Cache Size
        4. 8.3.6.4. Database Encoding
        5. 8.3.6.5. Query Encoding
    4. 8.4. Symbian SQL Optimization Tips
      1. 8.4.1. Data Organization
        1. 8.4.1.1. Put Small and Frequently Accessed Columns at the Beginning of a Table
        2. 8.4.1.2. Store Large BLOBs Outside the Database
        3. 8.4.1.3. Use CROSS JOIN to Force Ordering
      2. 8.4.2. Expressions
        1. 8.4.2.1. Rewrite Expressions for Optimization
        2. 8.4.2.2. Put Constant Subexpressions Inside Subqueries
        3. 8.4.2.3. MIN() and MAX() Optimization
        4. 8.4.2.4. Use UNION ALL Rather Than UNION
        5. 8.4.2.5. Avoid Using OFFSET for Scrolling Cursors
        6. 8.4.2.6. Use Conjunctions in WHERE Clause Expressions
      3. 8.4.3. Statements
        1. 8.4.3.1. Enclose INSERT and UPDATE Statements in Transactions
        2. 8.4.3.2. Batch INSERT, UPDATE, and DELETE Operations
        3. 8.4.3.3. Use Bound Parameters
        4. 8.4.3.4. Cache and Reuse Prepared Statements
      4. 8.4.4. Indexing
        1. 8.4.4.1. Use Indexes to Speed Up Access
        2. 8.4.4.2. Create Indexes Automatically Using PRIMARY KEY and UNIQUE
        3. 8.4.4.3. INTEGER PRIMARY KEY is a Fast Special Case
        4. 8.4.4.4. Use Small Positive Integers for INTEGER PRIMARY KEY and ROWID
        5. 8.4.4.5. Use Indexed Column Names in WHERE Clauses
        6. 8.4.4.6. Use Multi-Column Indexes
        7. 8.4.4.7. Use Inequality Constraints on the Last Index Term
        8. 8.4.4.8. Use Indexes to Help ORDER BY Clauses Evaluate Faster
        9. 8.4.4.9. Add Result Columns to the End of an Index
        10. 8.4.4.10. Resolve Indexing Ambiguities Using the Unary + Operator
        11. 8.4.4.11. Avoid Indexing Large BLOBs and CLOBs
        12. 8.4.4.12. Avoid Creating Too Many Indexes
      5. 8.4.5. Using the Optimizer
        1. 8.4.5.1. The ANALYZE Command
        2. 8.4.5.2. EXPLAIN QUERY PLAN
      6. 8.4.6. Resource Usage
        1. 8.4.6.1. Shrink a Database File
        2. 8.4.6.2. Avoid Running out of Filesystem Space
        3. 8.4.6.3. Keep Schemas Small and Unchanging
        4. 8.4.6.4. Avoid Tables and Indexes with an Excessive Number of Columns
        5. 8.4.6.5. Avoid Corrupting Database Files
        6. 8.4.6.6. Avoid Queries that Require Transient Tables
        7. 8.4.6.7. Avoid Using Excess Memory
    5. 8.5. Summary
  16. 9. Using Symbian SQL: Three Case Studies
    1. 9.1. Contacts Model
      1. 9.1.1. Contacts Model on DBMS
      2. 9.1.2. Aims of Migration
      3. 9.1.3. Contacts Model on Symbian SQL
        1. 9.1.3.1. Scalability
        2. 9.1.3.2. Performance
        3. 9.1.3.3. RAM Usage
        4. 9.1.3.4. Ease of Development and Maintenance
      4. 9.1.4. Possible Future Improvements
      5. 9.1.5. Case Study Summary
    2. 9.2. Columbo Search Service
      1. 9.2.1. Why Symbian SQL?
        1. 9.2.1.1. Reuse of Developer Skills
        2. 9.2.1.2. Agile-Friendly API
        3. 9.2.1.3. Highly Competitive Performance
        4. 9.2.1.4. Portability
        5. 9.2.1.5. Reliability and Fault-Tolerance
      2. 9.2.2. Developing with SQLite
      3. 9.2.3. Case Study Summary
    3. 9.3. Wikipedia Demo Application
      1. 9.3.1. Downloading the Symbian Wikipedia Demo Application
      2. 9.3.2. Storing Wikipedia on a Phone
      3. 9.3.3. Designing the Schema (the Wrong Way)
      4. 9.3.4. Designing the Schema (the Right Way)
      5. 9.3.5. Displaying the List of Articles
      6. 9.3.6. Retrieving the Content of Articles
      7. 9.3.7. Case Study Summary
    4. 9.4. Summary
  17. A. Troubleshooting
    1. A.1. Executing SQL Statements
    2. A.2. SQL Language
      1. A.2.1. Datatypes
      2. A.2.2. SQLite Command Line
      3. A.2.3. Dates
        1. A.2.3.1. Storing Dates as Strings
        2. A.2.3.2. Storing Dates as Long Integers
        3. A.2.3.3. Storing Dates as Fixed-format Integers
        4. A.2.3.4. Advantages and Disadvantages of the Options
    3. A.3. Memory and Disk Usage
    4. A.4. Transactions
      1. A.4.1. Implicit Single Transactions
      2. A.4.2. Explicit Bulk Transactions
    5. A.5. Error Handling
  18. B. SDB Database Creation Tool
    1. B.1. Obtaining and Installing SDB
    2. B.2. Using SDB
    3. B.3. SDBv1 Configuration Files
      1. B.3.1. The Properties File
      2. B.3.2. Security Settings
      3. B.3.3. Database Page Size
      4. B.3.4. Text Encoding
  19. C. Symbian SQL Error Codes
  20. References