Home Page Icon
Home Page
Table of Contents for
IV. Appendixes
Close
IV. Appendixes
by Chris Newman
SQLite
Copyright
Foreword
About the Author
We Want to Hear from You!
Reader Services
Introduction Welcome to SQLite
Why Use SQLite?
Who This Book Is For
How the Book Is Organized
Part I—General SQLite Use
Part II—Using SQLite Programming Interfaces
Part III—SQLite Administration
Versions of Software Covered
Additional Resources
I. General SQLite Use
1. Getting Started
Introduction
Features and Limitations
Speed
Portability
Security
SQL Implementation
Customization
Supported APIs
C/C++
PHP
Perl
Tcl
Other Programming Languages
Scalability
When Not to Choose SQLite
When SQLite Is Probably Wrong
Network or Client/Server Applications
High-Volume Websites
High Concurrency
When SQLite Is Probably Right
Websites
Embedded Devices
Ad-hoc File Storage
Internal Data Manipulation
Looking at SQLite Databases
The sqlite Tool
SQLite Database Browser
Help and Support
2. Working with Data
SQLite Basics
Prerequisites
Obtaining the Sample Database
Creating and Connecting to a Database
Executing SQL from a File
The Sample Database
Typeless Data Types
The INTEGER PRIMARY KEY
Working with Dates
Inserting the Sample Data
BLOBs and Binary Data
Querying and Updating the Database
The SELECT Statement
The WHERE Clause
String Comparisons
NULL Values
Arithmetic Functions
String Operators and Functions
Joins
Aggregate Functions
Ordering Data
Limiting Data
Updating and Deleting Records
Left Joins
Nested Subqueries
Cartesian Joins
Transactions
The sqlite_master Table
3. SQLite Syntax and Use
Naming Conventions
Valid Characters
Name Length
Reserved Keywords
Case Sensitivity
Creating and Dropping Tables
Table Column Definitions
Resolving Conflicts
The CHECK Clause
Using Temporary Tables
Anatomy of a SELECT Statement
The WHERE Clause
GROUP BY and Aggregate Functions
HAVING Clause
Column Aliases
Attaching to Another Database
Manipulating Data
Transactions
Inserting Data
INSERT Using VALUES
INSERT Using SELECT
Updating Data
Deleting Data
Altering a Table Schema
Loading Data from a File
Indexes
Creating and Dropping Indexes
UNIQUE Indexes
Views
Creating and Dropping Views
Using Views
Triggers
Creating and Dropping Triggers
Using Triggers
Interrupting a Trigger
Creating a Trigger on a View
Working with Dates and Times
Valid Timestring Formats
Displaying a Formatted Date and Time
Date and Time Modifiers
Handling Different Time Zones
SQL92 Features Not Supported
4. Query Optimization
Keys and Indexes
What an Index Does
How Indexes Work in SQLite
Indexing Multiple Columns
Unique Indexes
When to Create an Index
When Indexes Can Be Used
Tweaking the Table List
Benchmarking
Some Examples
The EXPLAIN Statement
Using Transactions
The VACUUM Statement
Tuning the Database Itself
II. Using SQLite Programming Interfaces
5. The PHP Interface
Configuring PHP for SQLite Support
Configuring PHP for Linux/Unix
Configuring PHP for Windows
Checking for SQLite Support
Getting Information About the SQLite Extension
Using the PHP SQLite Extension
Opening a Database
Passing Queries and Commands to SQLite
Using Commands That Change the Database
Working with Retrieved Data
Working with User-Defined Functions
The php() Function
Creating Aggregating Functions
Working with Binary Data in UDFs
Using the PEAR Database Class
6. The C/C++ Interface
Preparing to Use the C/C++ Interface
Using the C Language Interface
Opening and Closing a Database
Getting Information About the SQLite Library
Executing SQL Statements
Using Commands That Change the Database
Callback Functions for SELECT Queries
Executing SQL Without a Callback Function
Fetching a Whole Dataset with sqlite_get_table()
Adding New SQL Functions
Creating Aggregating Functions
7. The Perl Interface
Preparing to Use the SQLite Interface
About the Perl DBI
Getting Information About the DBI
Using DBD Drivers
Using the SQLite DBD
Opening and Closing the Database
Executing SQL Statements
Using Bind Variables
Using Commands That Change the Database
Transactions
Fetching Records from the Database
Error Checking
Tracing
Adding New SQL Functions
Creating Functions
Creating Aggregating Functions
8. The Tcl Interface
Preparing to Use the Tcl Interface
Using the Tcl Interface
Opening and Closing a Database
Executing SQL Statements
Using Commands That Change the Database
Fetching Records from the Database
The onecolumn Database Method
Validating an SQL Statement
Adjusting the Database Timeout Action
Adding New SQL Functions
9. The Python Interface
Preparing to Use the Python Interface
Using the Python Interface
Opening and Closing a Database
Executing SQL Commands
Error Handling
Connection Parameters
Using Commands That Change the Database
Transactions
Fetching Records from the Database
Working with Data Types
Adding New SQL Functions
Adding Aggregating Functions
III. SQLite Administration
10. General Database Administration
The PRAGMA Command
Fetching Database Information
Altering Database Parameters
Altering Database Parameters Permanently
Altering Query Parameters
Analyzing the Database
Backing Up and Restoring Data
The .dump Command
Exploring the SQLite Virtual Database Engine
SQLite Architecture
Interface
Tokenizer
Parser
Code Generator
Virtual Machine
Backend
B-Tree
Pager
OS Interface
Red/Black Tree
Virtual Machine Opcodes
Access to the Database File
File Permissions
Locking and Timeouts
Multithreaded Database Access
IV. Appendixes
A. Downloading and Installing SQLite
Obtaining SQLite
RPM Installation for Linux
Binary Installation for Linux
Binary Installation for Windows
Installing from Source Code
B. Command Reference for the sqlite Tool
Dot Commands
Obtaining a List of Dot Commands
Changing the Output Format
Reading SQL Commands from a File
Sending Output to a File
Getting Information about the Database Schema
Changing the sqlite Prompts
Setting the Database Timeout
Exiting the sqlite Tool
C. SQL Syntax Reference
Naming Conventions
Reserved Keywords
SQL Command Syntax
Creating and Dropping Database Objects
CREATE TABLE
Column Constraints
CREATE INDEX
CREATE VIEW
CREATE TRIGGER
The SELECT Statement
The INSERT Statement
The UPDATE Statement
The DELETE Statement
The COPY Statement
Resolving Conflicts
Transactions
Attaching to Other Databases
Performance Tuning
Comments
ANSI SQL Commands and Features Not Supported
ALTER TABLE
COUNT(DISTINCT column-name)
GRANT and REVOKE
INSERT, UPDATE, and DELETE on Views
RIGHT OUTER JOIN
CHECK and FOREIGN KEY Constraints
Trigger Limitations
Nested Transactions
Variable Subqueries
D. PHP Interface Reference
Predefined Constants
Runtime Configuration
Function Reference
Opening and Closing a Database
Executing a Query
Error Reporting
Finding Information About a Query
Processing a Result Set
Random Data Access Functions
Convenience Functions
Finding Information About SQLite
Custom Functions (UDF)
E. C Interface Reference
The Core API
Opening and Closing a Database
Executing a Query
Error Codes
The Non-Callback API
Creating a Virtual Machine
Step-by-Step Execution of an SQL Statement
Return Codes
The Extended API
Finding Information About the SQLite Library
Finding Information About Changes to the Database
Checking SQL Statements
Interrupting an SQL Statement
Convenience Functions
The _printf() Wrapper Functions
Memory Management
Dealing with Locked Database Files
Performing Background Jobs During Large Queries
Adding New SQL Functions
Registering Functions
Setting Return Values
Referencing Arbitrary Data
F. Perl Interface Reference
The Perl DBI
Opening and Closing a Database
Executing SQL Statements
Using Bind Values
Making Data Safe
Getting Information About a Query
Transactions
Fetching Rows from a Query
Error Reporting
Creating User-Defined Functions
G. Tcl Interface Reference
The Tcl Library
Opening and Closing a Database
Executing a Query
Convenience Functions
Finding Information About a Query
Checking SQL Statements
Dealing with Locked Database Files
Error Reporting
Finding Information About SQLite
Custom Functions (UDF)
H. Python Interface Reference
Opening and Closing a Database
Executing SQL Statements
Setting Data Type Mappings
Creating User-Defined Functions
Error Handling
I. The Future of SQLite
SQLite Version 3.0
Naming Changes
File Format Changes
Data Typing
User-Defined Collating Sequences
Improved Concurrency
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
10. General Database Administration
Next
Next Chapter
A. Downloading and Installing SQLite
Part IV. Appendixes
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset