Inside Symbian SQL -- Contents -- Foreword by Richard Hipp -- Foreword by Chris Dudding -- Author Biographies -- Author's Acknowledgments -- Symbian Acknowledgments -- Publisher's Acknowledgements -- 1 Introduction -- 1.1 Where Should You Start? -- 1.2 Symbian Terminology and Version Numbering -- 1.3 The Relational Model and the Structured Query Language (SQL) -- 1.4 What Is Symbian SQL? -- 1.5 A Well-Oiled Machine -- 1.6 Tools and Troubleshooting -- 1.7 Further Reading and Resources -- 2 Getting Started -- 2.1 Where to Get Symbian SQL -- 2.2 Overview of Symbian SQL APIs -- 2.3 First Database Example -- 2.3.1 Project Setup -- 2.3.2 Creating a Database -- 2.3.3 Creating a Table -- 2.3.4 Creating an Index -- 2.3.5 Inserting Records -- 2.3.6 Selecting Records -- 2.3.7 Updating a Record -- 2.3.8 Updating Records Using Column Binding and Streaming -- 2.3.9 Deleting a Record -- 2.3.10 Closing a Database -- 2.4 Summary -- 3 The Relational Model -- 3.1 Background -- 3.1.1 The Three Components -- 3.1.2 SQL and the Relational Model -- 3.2 The Structural Component -- 3.2.1 The Information Principle -- 3.2.2 The Sanctity of the Logical Level -- 3.2.3 The Anatomy of the Logical Level -- 3.2.4 Tuples -- 3.2.5 Relations -- 3.2.6 Tables: Relation Variables -- 3.2.7 Views: Virtual Tables -- 3.2.8 The System Catalog -- 3.3 The Integrity Component -- 3.3.1 Primary Keys -- 3.3.2 Foreign Keys -- 3.3.3 Constraints -- 3.3.4 Null Values -- 3.4 Normalization -- 3.4.1 Normal Forms -- 3.4.2 First Normal Form -- 3.4.3 Functional Dependencies -- 3.4.4 Second Normal Form -- 3.4.5 Third Normal Form -- 3.5 The Manipulative Component -- 3.5.1 Relational Algebra and Calculus -- 3.5.2 The Relational Query Language -- 3.5.3 The Advent of SQL -- 3.6 The Meaning of Relational -- 3.7 Summary -- 4 Everything You Ever Wanted to Know about SQL but Were Afraid to Ask.

4.1 The Relational Model -- 4.1.1 Query Languages -- 4.1.2 Growth of SQL -- 4.2 The Example Database -- 4.2.1 Installation -- 4.2.2 Running the Examples -- 4.3 Syntax -- 4.3.1 Commands -- 4.3.2 Literals -- 4.3.3 Keywords and Identifiers -- 4.3.4 Comments -- 4.4 Creating a Database -- 4.4.1 Creating a Table -- 4.4.2 Altering a Table -- 4.5 Querying a Database -- 4.5.1 Relational Operations -- 4.5.2 The Operational Pipeline -- 4.5.3 Filtering Rows -- 4.5.4 Limiting and Ordering -- 4.5.5 Functions and Aggregates -- 4.5.6 Grouping -- 4.5.7 Removing Duplicates -- 4.5.8 Joining Tables -- 4.5.9 Column Names and Aliases -- 4.5.10 Subqueries -- 4.5.11 Compound Queries -- 4.5.12 Conditional Results -- 4.5.13 The Thing Called NULL -- 4.5.14 Set Operations -- 4.6 Modifying Data -- 4.6.1 Inserting Records -- 4.6.2 Updating Records -- 4.6.3 Deleting Records -- 4.7 Data Integrity -- 4.7.1 Entity Integrity -- 4.7.2 Domain Integrity -- 4.7.3 Storage Classes -- 4.7.4 Manifest Typing -- 4.7.5 Type Affinity -- 4.8 Transactions -- 4.8.1 Transaction Scope -- 4.8.2 Conflict Resolution -- 4.8.3 Transaction Types -- 4.9 Database Administration -- 4.9.1 Views -- 4.9.2 Indexes -- 4.9.3 Triggers -- 4.9.4 Attaching Databases -- 4.9.5 Cleaning Databases -- 4.9.6 The System Catalog -- 4.9.7 Viewing Query Plans -- 4.10 Summary -- 5 Database Concepts -- 5.1 Database Types -- 5.1.1 Public Databases -- 5.1.2 Private Databases -- 5.1.3 Secure Shared Databases -- 5.2 Executing SQL -- 5.2.1 How the Database Processes SQL -- 5.2.2 One-shot Execution -- 5.2.3 Prepared Statements -- 5.3 Multi-Client Access -- 5.3.1 Transactions -- 5.3.2 Connections -- 5.3.3 Locking -- 5.3.4 Connections, Transactions and Locking -- 5.3.5 Prepared Statements and Locking -- 5.4 Summary -- 6 Using Symbian SQL -- 6.1 The Database Connection Class -- 6.1.1 Connecting and Disconnecting.

6.1.2 Creating and Deleting Databases -- 6.1.3 Attaching Additional Databases -- 6.1.4 Executing SQL -- 6.1.5 Copying and Deleting a Database File -- 6.1.6 Managing Disk Usage -- 6.1.7 Transaction APIs -- 6.1.8 Retrieving the Security Policy -- 6.2 The Prepared Statement Class -- 6.2.1 Preparing and Executing SQL Statements -- 6.2.2 Retrieving Column Values -- 6.2.3 Parameterized Queries -- 6.3 Working with Variable-Length Data Objects -- 6.3.1 Retrieving Data -- 6.3.2 Retrieving Large Amounts of Data -- 6.3.3 Streaming Data -- 6.4 The Scalar Query Class -- 6.5 Security Policies -- 6.6 Summary -- 7 SQLite Internals on Symbian -- 7.1 Why SQLite Is Right for Symbian -- 7.2 The SQLite Modules -- 7.2.1 Public Interface -- 7.2.2 Compiler -- 7.2.3 Virtual Machine -- 7.2.4 B-Tree Module -- 7.2.5 Pager -- 7.2.6 OS Interface -- 7.3 SQLite Configuration Options -- 7.3.1 Auto-vacuum -- 7.3.2 Page Size -- 7.3.3 Maximum Cache Size -- 7.3.4 Database Encoding -- 7.3.5 SQL Statement Encoding -- 7.4 SQLite Optimizations on Symbian OS -- 7.4.1 Sharing the Cache -- 7.4.2 Retaining Page Cache Contents -- 7.4.3 Imposing a Soft Heap Limit -- 7.4.4 Increasing the Maximum Cache Size -- 7.4.5 Avoiding Unnecessary Reads -- 7.4.6 Avoiding Unnecessary Writes -- 7.4.7 Minimizing the Cost of Using Journal Files -- 7.4.8 Mass Storage and Fast Retrieval of Binary Data (BLOBs) -- 7.4.9 Background Database Compaction -- 7.5 Summary -- 8 Performance Tuning -- 8.1 Design and Development Considerations -- 8.1.1 Identify Key Use Cases -- 8.1.2 Set Performance Objectives -- 8.1.3 Measure Performance -- 8.1.4 Develop Performance Tests -- 8.1.5 Follow up Test Results -- 8.2 An Introduction to Optimization -- 8.2.1 Understand the Operation of the Application -- 8.2.2 Identify Bottlenecks Before Optimizing -- 8.2.3 Avoid Early Optimization -- 8.2.4 Maintain Performance Test Records.

8.2.5 Carry Out One Optimization at a Time -- 8.2.6 Optimize in the Right Environment -- 8.3 Optimizing Applications -- 8.3.1 Application Layer -- 8.3.2 Data Model -- 8.3.3 Indexing -- 8.3.4 SQL Queries -- 8.3.5 Memory Usage -- 8.3.6 System Tuning -- 8.4 Symbian SQL Optimization Tips -- 8.4.1 Data Organization -- 8.4.2 Expressions -- 8.4.3 Statements -- 8.4.4 Indexing -- 8.4.5 Using the Optimizer -- 8.4.6 Resource Usage -- 8.5 Summary -- 9 Using Symbian SQL: Three Case Studies -- 9.1 Contacts Model -- 9.1.1 Contacts Model on DBMS -- 9.1.2 Aims of Migration -- 9.1.3 Contacts Model on Symbian SQL -- 9.1.4 Possible Future Improvements -- 9.1.5 Case Study Summary -- 9.2 Columbo Search Service -- 9.2.1 Why Symbian SQL? -- 9.2.2 Developing with SQLite -- 9.2.3 Case Study Summary -- 9.3 Wikipedia Demo Application -- 9.3.1 Downloading the Symbian Wikipedia Demo Application -- 9.3.2 Storing Wikipedia on a Phone -- 9.3.3 Designing the Schema (the Wrong Way) -- 9.3.4 Designing the Schema (the Right Way) -- 9.3.5 Displaying the List of Articles -- 9.3.6 Retrieving the Content of Articles -- 9.3.7 Case Study Summary -- 9.4 Summary -- Appendix A Troubleshooting -- A.1 Executing SQL Statements -- A.2 SQL Language -- A.3 Memory and Disk Usage -- A.4 Transactions -- A.5 Error Handling -- Appendix B SDB Database Creation Tool -- B.1 Obtaining and Installing SDB -- B.2 Using SDB -- B.3 SDBv1 Configuration Files -- Appendix C Symbian SQL Error Codes -- References -- Index.