Home Page Icon
Home Page
Table of Contents for
Scalar Functions
Close
Scalar Functions
by Jay A. Kreibich
Using SQLite
Dedication
A Note Regarding Supplemental Files
Preface
SQLite Versions
Email Lists
Example Code Download
How We Got Here
Conventions Used in This Book
Using Code Examples
SafariĀ® Books Online
How to Contact Us
1. What Is SQLite?
Self-Contained, No Server Required
Single File Database
Zero Configuration
Embedded Device Support
Unique Features
Compatible License
Highly Reliable
2. Uses of SQLite
Database Junior
Application Files
Application Cache
Archives and Data Stores
Client/Server Stand-in
Teaching Tool
Generic SQL Engine
Not the Best Choice
Big Name Users
3. Building and Installing SQLite
SQLite Products
Precompiled Distributions
Documentation Distribution
Source Distributions
The Amalgamation
Source Files
Source Downloads
Building
Configure
Manually
Build Customization
Build and Installation Options
An sqlite3 Primer
Summary
4. The SQL Language
Learning SQL
Brief Background
Declarative
Portability
General Syntax
Basic Syntax
Three-Valued Logic
Simple Operators
SQL Data Languages
Data Definition Language
Tables
The basics
Column types
Column constraints
Primary keys
Table constraints
Tables from queries
Altering tables
Dropping tables
Virtual tables
Views
Indexes
Data Manipulation Language
Row Modification Commands
INSERT
UPDATE
DELETE
The Query Command
Transaction Control Language
ACID Transactions
SQL Transactions
Save-Points
System Catalogs
Wrap-up
5. The SELECT Command
SQL Tables
The SELECT Pipeline
FROM Clause
CROSS JOIN
INNER JOIN
OUTER JOIN
Table aliases
WHERE Clause
GROUP BY Clause
SELECT Header
HAVING Clause
DISTINCT Keyword
ORDER BY Clause
LIMIT and OFFSET Clauses
Advanced Techniques
Subqueries
Compound SELECT Statements
Alternate JOIN Notation
SELECT Examples
Simple SELECTs
Simple JOINs
JOIN...ON
JOIN...USING, NATURAL JOIN
OUTER JOIN
Compound JOIN
Self JOIN
WHERE Examples
GROUP BY Examples
ORDER BY Examples
Whatās Next
6. Database Design
Tables and Keys
Keys Define the Table
Foreign Keys
Foreign Key Constraints
Generic ID Keys
Keep It Specific
Common Structures and Relationships
One-to-One Relationships
One-to-Many Relationships
Many-to-Many Relationships
Hierarchies and Trees
Adjacency Model
Nested set
More information
Normal Form
Normalization
Denormalization
The First Normal Form
The Second Normal Form
The Third Normal Form
Higher Normal Forms
Indexes
How They Work
Must Be Diverse
INTEGER PRIMARY KEYs
Order Matters
One at a Time
Index Summary
Transferring Design Experience
Tables Are Types
Keys Are Backwards Pointers
Do One Thing
Closing
7. C Programming Interface
API Overview
Structure
Strings and Unicode
Error Codes
Structures and Allocations
More Info
Library Initialization
Database Connections
Opening
Special Cases
Closing
Example
Prepared Statements
Statement Life Cycle
Prepare
Step
Result Columns
Reset and Finalize
Statement Transitions
Examples
Bound Parameters
Parameter Tokens
Binding Values
Security and Performance
Example
Potential Pitfalls
Convenience Functions
Result Codes and Error Codes
Standard Codes
Extended Codes
Error Functions
Prepare v2
Transactions and Errors
Database Locking
Busy handlers
Deadlocks
Avoiding SQLITE_BUSY
Avoiding deadlocks
When BUSY becomes BLOCKED
Utility Functions
Version Management
Memory Management
Summary
8. Additional Features and APIs
Date and Time Features
Application Requirements
Representations
Julian Day
Text values
Time zones
Time and Date Functions
Conversion Function
Convenience functions
Time literals
Examples
ICU Internationalization Extension
Full-Text Search Module
Creating and Populating FTS Tables
Searching FTS Tables
More Details
R*Trees and Spatial Indexing Module
Scripting Languages and Other Interfaces
Perl
PHP
Python
Java
Tcl
ODBC
.NET
C++
Other Languages
Mobile and Embedded Development
Memory
Storage
Other Resources
iPhone Support
Other Environments
Additional Extensions
9. SQL Functions and Extensions
Scalar Functions
Registering Functions
Extracting Parameters
Returning Results and Errors
Example
sql_set_limit
sql_get_limit
sql_getset_limit
sql_getset_var_limit
Aggregate Functions
Defining Aggregates
Aggregate Context
Example
Collation Functions
Registering a Collation
Collation Example
SQLite Extensions
Extension Architecture
Extension Design
Example Extension: sql_trig
Building and Integrating Static Extensions
Using Loadable Extensions
Building Loadable Extensions
Loadable Extension Security
Loading Loadable Extensions
Multiple Entry Points
Chapter Summary
10. Virtual Tables and Modules
Introduction to Modules
Internal Modules
External Modules
Example Modules
SQL for Anything
Module API
Simple Example: dblist Module
Create and Connect
Allocate the vtab structure
Define the table structure
Storage initialization
Create/connect dblist example
Disconnect and Destroy
Query Optimization
Custom Functions
Table Rename
Opening and Closing Table Cursors
Filtering Rows
Extracting and Returning Data
Virtual Table Modifications
Cursor Sequence
Transaction Control
Register the Module
Example Usage
Advanced Example: weblog Module
Create and Connect
Disconnect and Destroy
Other Table Functions
Open and Close
Filter
Rows and Columns
Register the Module
Example Usage
Best Index and Filter
Purpose and Need
xBestIndex()
xFilter()
Typical Usage
Wrap-Up
A. SQLite Build Options
Shell Directives
Default Values
Sizes and Limits
Operation and Behavior
Debug Settings
Enable Extensions
Limit Features
Omit Core Features
B. sqlite3 Command Reference
Command-Line Options
Interactive Dot-Commands
C. SQLite SQL Command Reference
SQLite SQL Commands
D. SQLite SQL Expression Reference
Literal Expressions
Logic Representations
Unary Expressions
Binary Expressions
Function Calls
Column Names
General Expressions
E. SQLite SQL Function Reference
Scalar Functions
Aggregate Functions
F. SQLite SQL PRAGMA Reference
SQLite PRAGMAs
G. SQLite C API Reference
API Datatypes
API Functions
Index
About the Author
Colophon
Copyright
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
E. SQLite SQL Function Reference
Next
Next Chapter
abs()
Scalar Functions
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