book

Inside Symbian SQL: A Mobile Developer's Guide to SQLite

Name: Inside Symbian SQL: A Mobile Developer's Guide to SQLite
ISBN: 9780470744024

by Ivan Litovski, Richard Maynard

April 2010

Intermediate to advanced

432 pages

10h 23m

English

Wiley

Read now

Unlock full access

Copyright
Foreword
Foreword1
Open SourceAnd Finally . . .
Author Biographies
Author's Acknowledgments
Symbian Acknowledgments
Publisher's Acknowledgements
1. Introduction
1.1. Where Should You Start?1.2. Symbian Terminology and Version Numbering1.3. The Relational Model and the Structured Query Language (SQL)1.4. What Is Symbian SQL?1.5. A Well-Oiled Machine1.6. Tools and Troubleshooting1.7. Further Reading and Resources
2. Getting Started
2.1. Where to Get Symbian SQL2.2. Overview of Symbian SQL APIs2.3. First Database Example2.3.1. Project Setup2.3.2. Creating a Database2.3.3. Creating a Table2.3.4. Creating an Index2.3.5. Inserting Records2.3.6. Selecting Records2.3.7. Updating a Record2.3.8. Updating Records Using Column Binding and Streaming2.3.9. Deleting a Record2.3.10. Closing a Database2.4. Summary
3. The Relational Model
3.1. Background3.1.1. The Three Components3.1.2. SQL and the Relational Model3.2. The Structural Component3.2.1. The Information Principle3.2.2. The Sanctity of the Logical Level3.2.3. The Anatomy of the Logical Level3.2.4. Tuples3.2.5. Relations3.2.5.1. Degree and Cardinality3.2.5.2. Mathematical Relations3.2.5.3. Relational Relations3.2.6. Tables: Relation Variables3.2.7. Views: Virtual Tables3.2.7.1. Logical Data Independence3.2.7.2. Updatable Views3.2.8. The System Catalog3.3. The Integrity Component3.3.1. Primary Keys3.3.2. Foreign Keys3.3.3. Constraints3.3.4. Null Values3.4. Normalization3.4.1. Normal Forms3.4.2. First Normal Form3.4.3. Functional Dependencies3.4.4. Second Normal Form3.4.5. Third Normal Form3.5. The Manipulative Component3.5.1. Relational Algebra and Calculus3.5.2. The Relational Query Language3.5.3. The Advent of SQL3.6. The Meaning of Relational3.7. Summary

4. Everything You Ever Wanted to Know about SQL but Were Afraid to Ask
4.1. The Relational Model4.1.1. Query Languages4.1.2. Growth of SQL4.2. The Example Database4.2.1. Installation4.2.2. Running the Examples4.3. Syntax4.3.1. Commands4.3.2. Literals4.3.3. Keywords and Identifiers4.3.4. Comments4.4. Creating a Database4.4.1. Creating a Table4.4.2. Altering a Table4.5. Querying a Database4.5.1. Relational Operations4.5.2. The Operational Pipeline4.5.3. Filtering Rows4.5.3.1. Values4.5.3.2. Operators4.5.3.3. Unary Operators4.5.3.4. Binary Operators4.5.3.5. Arithmetic and Relational Operators4.5.3.6. Logical Operators4.5.3.7. The LIKE Operator4.5.4. Limiting and Ordering4.5.5. Functions and Aggregates4.5.6. Grouping4.5.7. Removing Duplicates4.5.8. Joining Tables4.5.8.1. Inner Joins4.5.8.2. Cross Joins4.5.8.3. Outer Joins4.5.8.4. Natural Joins4.5.8.5. Preferred Join Syntax4.5.9. Column Names and Aliases4.5.10. Subqueries4.5.11. Compound Queries4.5.12. Conditional Results4.5.13. The Thing Called NULL4.5.14. Set Operations4.6. Modifying Data4.6.1. Inserting Records4.6.2. Updating Records4.6.3. Deleting Records4.7. Data Integrity4.7.1. Entity Integrity4.7.1.1. UNIQUE Constraints4.7.1.2. PRIMARY KEY Constraints4.7.2. Domain Integrity4.7.2.1. Default Values4.7.2.2. NOT NULL Constraints4.7.2.3. CHECK Constraints4.7.2.4. Collations4.7.3. Storage Classes4.7.4. Manifest Typing4.7.5. Type Affinity4.7.5.1. Column Types and Affinities4.7.5.2. Affinities and Storage4.7.5.3. Affinities in Action4.7.5.4. Storage Classes and Type Conversions4.7.5.5. Makeshift Strict Typing4.8. Transactions4.8.1. Transaction Scope4.8.2. Conflict Resolution4.8.3. Transaction Types4.9. Database Administration4.9.1. Views4.9.2. Indexes4.9.2.1. Collation4.9.2.2. Index Utilization4.9.3. Triggers4.9.3.1. UPDATE Triggers4.9.3.2. Error Handling4.9.3.3. Conflict Resolution4.9.3.4. Updatable Views4.9.3.5. Implementing Foreign Key Constraints4.9.4. Attaching Databases4.9.5. Cleaning Databases4.9.6. The System Catalog4.9.7. Viewing Query Plans4.10. Summary
5. Database Concepts
5.1. Database Types5.1.1. Public Databases5.1.2. Private Databases5.1.3. Secure Shared Databases5.1.3.1. Access Control Policies5.1.3.2. Access Control Policies in Use5.1.3.3. Anti-spoofing Protection5.2. Executing SQL5.2.1. How the Database Processes SQL5.2.2. One-shot Execution5.2.3. Prepared Statements5.2.3.1. Parameterized SQL5.2.3.2. Working with Large Amounts of Data5.2.3.3. Returning Results from Queries5.3. Multi-Client Access5.3.1. Transactions5.3.2. Connections5.3.3. Locking5.3.3.1. Granularity of Locking5.3.3.2. Symbian SQL Locking Specifics5.3.3.3. Failing to Acquire a Lock5.3.3.4. Read Uncommitted Mode5.3.4. Connections, Transactions and Locking5.3.5. Prepared Statements and Locking5.4. Summary
6. Using Symbian SQL
6.1. The Database Connection Class6.1.1. Connecting and Disconnecting6.1.1.1. Database Configuration6.1.1.2. Database Filename Conventions6.1.2. Creating and Deleting Databases6.1.3. Attaching Additional Databases6.1.4. Executing SQL6.1.5. Copying and Deleting a Database File6.1.6. Managing Disk Usage6.1.6.1. Retrieving the Database File Size and Free Space6.1.6.2. Compacting a Database6.1.6.3. Managing Low Disk Space6.1.7. Transaction APIs6.1.8. Retrieving the Security Policy6.2. The Prepared Statement Class6.2.1. Preparing and Executing SQL Statements6.2.2. Retrieving Column Values6.2.2.1. Extracting Column Values6.2.2.2. Ancillary APIs6.2.3. Parameterized Queries6.3. Working with Variable-Length Data Objects6.3.1. Retrieving Data6.3.2. Retrieving Large Amounts of Data6.3.3. Streaming Data6.4. The Scalar Query Class6.5. Security Policies6.6. Summary
7. SQLite Internals on Symbian
7.1. Why SQLite Is Right for Symbian7.2. The SQLite Modules7.2.1. Public Interface7.2.1.1. Database Connection Objects7.2.1.2. Creating or Opening a Database7.2.1.3. Closing a Database7.2.1.4. Executing SQL Statements7.2.1.5. Using Prepared Statements7.2.1.6. Prepared Statement Objects7.2.1.7. Preparing a Statement7.2.1.8. Binding Values to a Prepared Statement7.2.1.9. Stepping Through a Prepared Statement7.2.1.10. Accessing Column Values of a Record7.2.1.11. Resetting a Prepared Statement7.2.1.12. Destroying a Prepared Statement7.2.1.13. Handling Errors7.2.2. Compiler7.2.2.1. Tokenizer7.2.2.2. Parser7.2.2.3. Code Generator7.2.3. Virtual Machine7.2.4. B-Tree Module7.2.4.1. Database File Format7.2.4.2. B-tree of a Table7.2.4.3. Record Data Packing7.2.4.4. Overflow Pages7.2.4.5. B-tree of an Index7.2.5. Pager7.2.5.1. Database File Locking7.2.5.2. Page Cache7.2.5.3. Rollback Journal File7.2.5.4. Page Reuse and Auto-vacuum7.2.6. OS Interface7.3. SQLite Configuration Options7.3.1. Auto-vacuum7.3.2. Page Size7.3.3. Maximum Cache Size7.3.4. Database Encoding7.3.5. SQL Statement Encoding7.4. SQLite Optimizations on Symbian OS7.4.1. Sharing the Cache7.4.2. Retaining Page Cache Contents7.4.3. Imposing a Soft Heap Limit7.4.4. Increasing the Maximum Cache Size7.4.5. Avoiding Unnecessary Reads7.4.5.1. Deleting the Last Overflow Page Without Reading It7.4.5.2. Reusing a Free Page Without Reading It7.4.6. Avoiding Unnecessary Writes7.4.6.1. Updating the Database Write Counter7.4.6.2. Avoiding Page Journaling7.4.7. Minimizing the Cost of Using Journal Files7.4.7.1. Avoiding Journal File Deletion7.4.7.2. Checking for a Hot Journal File7.4.8. Mass Storage and Fast Retrieval of Binary Data (BLOBs)7.4.8.1. Zeroblobs7.4.8.2. BLOB Handles7.4.8.3. Opening a Handle to a BLOB7.4.8.4. Closing a Handle to a BLOB7.4.8.5. Reading from a BLOB7.4.8.6. Writing to a BLOB7.4.8.7. Retrieving the Size of a BLOB7.4.9. Background Database Compaction7.5. Summary
8. Performance Tuning
8.1. Design and Development Considerations8.1.1. Identify Key Use Cases8.1.2. Set Performance Objectives8.1.3. Measure Performance8.1.4. Develop Performance Tests8.1.5. Follow up Test Results8.2. An Introduction to Optimization8.2.1. Understand the Operation of the Application8.2.2. Identify Bottlenecks Before Optimizing8.2.3. Avoid Early Optimization8.2.4. Maintain Performance Test Records8.2.5. Carry Out One Optimization at a Time8.2.6. Optimize in the Right Environment8.3. Optimizing Applications8.3.1. Application Layer8.3.1.1. Use Active Objects with Care8.3.1.2. Inspect Loops8.3.1.3. Use Asynchronous APIs Carefully8.3.1.4. Use Incremental Retrieval8.3.2. Data Model8.3.2.1. Normalization8.3.2.2. Large Data Types8.3.3. Indexing8.3.3.1. Index Size8.3.3.2. Avoiding Common Index Pitfalls8.3.3.3. Choosing the Correct Index Type8.3.3.4. Using ROWID as a Special Index8.3.3.5. Using Multiple Indexes in a Statement8.3.3.6. Optimizing Index Selection8.3.4. SQL Queries8.3.5. Memory Usage8.3.5.1. Database Connections8.3.5.2. Prepared Statements8.3.5.3. Deleting Multiple Records8.3.5.4. Manipulating Large Volumes of Data8.3.5.5. Transient Tables8.3.6. System Tuning8.3.6.1. Auto-Vacuum8.3.6.2. Page Size8.3.6.3. Cache Size8.3.6.4. Database Encoding8.3.6.5. Query Encoding8.4. Symbian SQL Optimization Tips8.4.1. Data Organization8.4.1.1. Put Small and Frequently Accessed Columns at the Beginning of a Table8.4.1.2. Store Large BLOBs Outside the Database8.4.1.3. Use CROSS JOIN to Force Ordering8.4.2. Expressions8.4.2.1. Rewrite Expressions for Optimization8.4.2.2. Put Constant Subexpressions Inside Subqueries8.4.2.3. MIN() and MAX() Optimization8.4.2.4. Use UNION ALL Rather Than UNION8.4.2.5. Avoid Using OFFSET for Scrolling Cursors8.4.2.6. Use Conjunctions in WHERE Clause Expressions8.4.3. Statements8.4.3.1. Enclose INSERT and UPDATE Statements in Transactions8.4.3.2. Batch INSERT, UPDATE, and DELETE Operations8.4.3.3. Use Bound Parameters8.4.3.4. Cache and Reuse Prepared Statements8.4.4. Indexing8.4.4.1. Use Indexes to Speed Up Access8.4.4.2. Create Indexes Automatically Using PRIMARY KEY and UNIQUE8.4.4.3. INTEGER PRIMARY KEY is a Fast Special Case8.4.4.4. Use Small Positive Integers for INTEGER PRIMARY KEY and ROWID8.4.4.5. Use Indexed Column Names in WHERE Clauses8.4.4.6. Use Multi-Column Indexes8.4.4.7. Use Inequality Constraints on the Last Index Term8.4.4.8. Use Indexes to Help ORDER BY Clauses Evaluate Faster8.4.4.9. Add Result Columns to the End of an Index8.4.4.10. Resolve Indexing Ambiguities Using the Unary + Operator8.4.4.11. Avoid Indexing Large BLOBs and CLOBs8.4.4.12. Avoid Creating Too Many Indexes8.4.5. Using the Optimizer8.4.5.1. The ANALYZE Command8.4.5.2. EXPLAIN QUERY PLAN8.4.6. Resource Usage8.4.6.1. Shrink a Database File8.4.6.2. Avoid Running out of Filesystem Space8.4.6.3. Keep Schemas Small and Unchanging8.4.6.4. Avoid Tables and Indexes with an Excessive Number of Columns8.4.6.5. Avoid Corrupting Database Files8.4.6.6. Avoid Queries that Require Transient Tables8.4.6.7. Avoid Using Excess Memory8.5. Summary
9. Using Symbian SQL: Three Case Studies
9.1. Contacts Model9.1.1. Contacts Model on DBMS9.1.2. Aims of Migration9.1.3. Contacts Model on Symbian SQL9.1.3.1. Scalability9.1.3.2. Performance9.1.3.3. RAM Usage9.1.3.4. Ease of Development and Maintenance9.1.4. Possible Future Improvements9.1.5. Case Study Summary9.2. Columbo Search Service9.2.1. Why Symbian SQL?9.2.1.1. Reuse of Developer Skills9.2.1.2. Agile-Friendly API9.2.1.3. Highly Competitive Performance9.2.1.4. Portability9.2.1.5. Reliability and Fault-Tolerance9.2.2. Developing with SQLite9.2.3. Case Study Summary9.3. Wikipedia Demo Application9.3.1. Downloading the Symbian Wikipedia Demo Application9.3.2. Storing Wikipedia on a Phone9.3.3. Designing the Schema (the Wrong Way)9.3.4. Designing the Schema (the Right Way)9.3.5. Displaying the List of Articles9.3.6. Retrieving the Content of Articles9.3.7. Case Study Summary9.4. Summary
A. Troubleshooting
A.1. Executing SQL StatementsA.2. SQL LanguageA.2.1. DatatypesA.2.2. SQLite Command LineA.2.3. DatesA.2.3.1. Storing Dates as StringsA.2.3.2. Storing Dates as Long IntegersA.2.3.3. Storing Dates as Fixed-format IntegersA.2.3.4. Advantages and Disadvantages of the OptionsA.3. Memory and Disk UsageA.4. TransactionsA.4.1. Implicit Single TransactionsA.4.2. Explicit Bulk TransactionsA.5. Error Handling
B. SDB Database Creation Tool
B.1. Obtaining and Installing SDBB.2. Using SDBB.3. SDBv1 Configuration FilesB.3.1. The Properties FileB.3.2. Security SettingsB.3.3. Database Page SizeB.3.4. Text Encoding
C. Symbian SQL Error Codes
References

Content preview from Inside Symbian SQL: A Mobile Developer's Guide to SQLite

Appendix B. SDB Database Creation Tool

Symbian SQL uses SQLite as a database engine and therefore database files created on the emulator or device can be read and manipulated in a desktop environment using the usual SQLite tools. However, Symbian SQL server offers some additional functionality, particularly related to Symbian platform security and the symbian_settings table, which is not directly supported by SQLite tools.

In order to simplify the task of creating databases, Symbian offers the Symbian database (SDB) tool. This appendix provides an overview, configuration reference and some examples on how to use SDB.

Note that, at the time of writing, only SDB version 1 is publicly released. SDB version 2 will add support for creating Symbian DBMS and contacts databases as well as central repository CommDB files. Functionality supported by SDBv1 is carefully preserved in SDBv2 so examples in this guide can be used with both versions.

Obtaining and Installing SDB

SDB is part of the Symbian Product Development Toolkit (PDT). The toolkit contains a set of tools for Symbian device creators and developers, and can be downloaded from developer.symbian.org.

Using SDB

SDB has a simple command-line interface:

sdb <command> [<command-specific options>]

SDBv1 requires a command, primarily for future proofing, which was clearly beneficial for SDBv2. SDBv1 supports only two commands: createdb and help. The createdb command is used as follows:

sdb createdb -x <XML file> -s <SQL file> -d <Existing DB file> ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470744024Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design