Using SQLite - Helion

ebook

Autor: Jay A. Kreibich
ISBN: 978-14-493-9964-1
stron: 530, Format: ebook
Data wydania: 2010-08-10
Księgarnia: Helion

Cena książki: 118,15 zł (poprzednio: 137,38 zł)
Oszczędzasz: 14% (-19,23 zł)

Osoby, które kupiły tę książkę, wybierały także »

Tagi: SQL - Programowanie

Application developers, take note: databases aren't just for the IS group any more. You can build database-backed applications for the desktop, Web, embedded systems, or operating systems without linking to heavy-duty client-server databases such as Oracle and MySQL. This book shows you how to use SQLite, a small and lightweight relational database engine that you can build directly into your application.

With SQLite, you'll discover how to develop a database-backed application that remains manageable in size and complexity. This book guides you every step of the way. You'll get a crash course in data modeling, become familiar with SQLite's dialect of the SQL database language, and much more.

Learn how to maintain localized storage in a single file that requires no configuration
Build your own SQLite library or use a precompiled distribution in your application
Get a primer on SQL, and learn how to use several language functions and extensions
Work with SQLite using a scripting language or a C-based language such as C# or Objective-C
Understand the basics of database design, and learn how to transfer what you already know to SQLite
Take advantage of virtual tables and modules

"Complex SQL concepts explained clearly."

--D. Richard Hipp, creator of SQLite

Osoby które kupowały "Using SQLite", wybierały także:

Microsoft SQL Server. Kurs video. Tworzenie zaawansowanych zapytań 199,00 zł, (59,70 zł -70%)
SQL. Kurs video. Od zera do bohatera. Modyfikowanie danych 159,00 zł, (47,70 zł -70%)
SQL. Kurs video. Od zera do bohatera. Odczytywanie danych 159,00 zł, (47,70 zł -70%)
SQL. Kurs video. Projektowanie i implementacja baz danych 79,00 zł, (23,70 zł -70%)
Instalacja i konfiguracja baz danych. Kurs video. Przygotowanie do egzaminu 70-765 Provisioning SQL Databases 299,00 zł, (89,70 zł -70%)

Spis treści

Using SQLite. Small. Fast. Reliable. Choose Any Three eBook -- spis treści

Using SQLite
Dedication
SPECIAL OFFER: Upgrade this ebook with OReilly
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
- Whats 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
  - ENABLE_READLINE
- Default Values
  - SQLITE_DEFAULT_AUTOVACUUM
  - SQLITE_DEFAULT_CACHE_SIZE
  - SQLITE_DEFAULT_FILE_FORMAT
  - SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
  - SQLITE_DEFAULT_MEMSTATUS
  - SQLITE_DEFAULT_PAGE_SIZE
  - SQLITE_DEFAULT_TEMP_CACHE_SIZE
  - YYSTACKDEPTH
- Sizes and Limits
  - SQLITE_MAX_ATTACHED
  - SQLITE_MAX_COLUMN
  - SQLITE_MAX_COMPOUND_SELECT
  - SQLITE_MAX_DEFAULT_PAGE_SIZE
  - SQLITE_MAX_EXPR_DEPTH
  - SQLITE_MAX_FUNCTION_ARG
  - SQLITE_MAX_LENGTH
  - SQLITE_MAX_LIKE_PATTERN_LENGTH
  - SQLITE_MAX_PAGE_COUNT
  - SQLITE_MAX_PAGE_SIZE
  - SQLITE_MAX_SQL_LENGTH
  - SQLITE_MAX_TRIGGER_DEPTH
  - SQLITE_MAX_VARIABLE_NUMBER
- Operation and Behavior
  - SQLITE_CASE_SENSITIVE_LIKE
  - SQLITE_HAVE_ISNAN
  - SQLITE_OS_OTHER
  - SQLITE_SECURE_DELETE
  - SQLITE_THREADSAFE
  - SQLITE_TEMP_STORE
- Debug Settings
  - SQLITE_DEBUG
  - SQLITE_MEMDEBUG
- Enable Extensions
  - SQLITE_ENABLE_ATOMIC_WRITE
  - SQLITE_ENABLE_COLUMN_METADATA
  - SQLITE_ENABLE_FTS3
  - SQLITE_ENABLE_FTS3_PARENTHESIS
  - SQLITE_ENABLE_ICU
  - SQLITE_ENABLE_IOTRACE
  - SQLITE_ENABLE_LOCKING_STYLE
  - SQLITE_ENABLE_MEMORY_MANAGEMENT
  - SQLITE_ENABLE_MEMSYS3
  - SQLITE_ENABLE_MEMSYS5
  - SQLITE_ENABLE_RTREE
  - SQLITE_ENABLE_STAT2
  - SQLITE_ENABLE_UPDATE_DELETE_LIMIT
  - SQLITE_ENABLE_UNLOCK_NOTIFY
  - YYTRACKMAXSTACKDEPTH
- Limit Features
  - SQLITE_DISABLE_LFS
  - SQLITE_DISABLE_DIRSYNC
  - SQLITE_ZERO_MALLOC
- Omit Core Features
B. sqlite3 Command Reference
- Command-Line Options
- Interactive Dot-Commands
  - .backup
  - .bail
  - .databases
  - .dump
  - .echo
  - .exit
  - .explain
  - .headers
  - .help
  - .import
  - .indices
  - .iotrace
  - .load
  - .log
  - .mode
  - .nullvalue
  - .output
  - .prompt
  - .quit
  - .read
  - .restore
  - .schema
  - .separator
  - .show
  - .tables
  - .timeout
  - .timer
  - .width
C. SQLite SQL Command Reference
- SQLite SQL Commands
  - ALTER TABLE
  - ANALYZE
  - ATTACH DATABASE
  - BEGIN TRANSACTION
  - COMMIT TRANSACTION
  - CREATE INDEX
  - CREATE TABLE
  - CREATE TRIGGER
  - CREATE VIEW
  - CREATE VIRTUAL TABLE
  - DELETE
  - DETACH DATABASE
  - DROP INDEX
  - DROP TABLE
  - DROP TRIGGER
  - DROP VIEW
  - END TRANSACTION
  - EXPLAIN
  - INSERT
  - PRAGMA
  - REINDEX
  - RELEASE SAVEPOINT
  - REPLACE
  - ROLLBACK TRANSACTION
  - SAVEPOINT
  - SELECT
  - UPDATE
  - VACUUM
D. SQLite SQL Expression Reference
- Literal Expressions
- Logic Representations
- Unary Expressions
- Binary Expressions
- Function Calls
- Column Names
- General Expressions
  - AND
  - BETWEEN
  - CASE
  - CAST
  - COLLATE
  - EXISTS
  - GLOB
  - IN
  - IS
  - ISNULL
  - LIKE
  - MATCH
  - NOTNULL
  - OR
  - RAISE
  - REGEXP
  - SELECT
E. SQLite SQL Function Reference
- Scalar Functions
  - abs()
  - changes()
  - coalesce()
  - date()
  - datetime()
  - glob()
  - ifnull()
  - hex()
  - julianday()
  - last_insert_rowid()
  - length()
  - like()
  - load_extension()
  - lower()
  - ltrim()
  - match()
  - max()
  - min()
  - nullif()
  - quote()
  - random()
  - randomblob()
  - regex()
  - replace()
  - round()
  - rtrim()
  - sqlite_compileoption_get()
  - sqlite_compileoption_used()
  - sqlite_source_id()
  - sqlite_version()
  - strftime()
  - substr()
  - time()
  - total_changes()
  - trim()
  - typeof()
  - upper()
  - zeroblob()
- Aggregate Functions
  - avg()
  - count()
  - group_concat()
  - max()
  - min()
  - sum()
  - total()
F. SQLite SQL PRAGMA Reference
- SQLite PRAGMAs
  - auto_vacuum
  - cache_size
  - case_sensitive_like
  - collation_list
  - count_changes
  - database_list
  - default_cache_size
  - encoding
  - foreign_keys
  - foreign_key_list
  - freelist_count
  - full_column_names
  - fullfsync
  - ignore_check_constraints
  - incremental_vacuum
  - index_info
  - index_list
  - integrity_check
  - journal_mode
  - journal_size_limit
  - legacy_file_format
  - locking_mode
  - lock_proxy_file
  - lock_status
  - max_page_count
  - omit_readlock
  - page_count
  - page_size
  - parser_trace
  - quick_check
  - read_uncommitted
  - recursive_triggers
  - reverse_unordered_selects
  - schema_version
  - secure_delete
  - short_column_names
  - sql_trace
  - synchronous
  - table_info
  - temp_store
  - temp_store_directory
  - user_version
  - vdbe_trace
  - vdbe_listing
  - writable_schema
G. SQLite C API Reference
- API Datatypes
  - sqlite3
  - sqlite3_backup
  - sqlite3_blob
  - sqlite3_context
  - sqlite3_int64, sqlite3_uint64, sqlite_int64, sqlite_uint64
  - sqlite3_module
  - sqlite3_mutex
  - sqlite3_stmt
  - sqlite3_value
  - sqlite3_vfs
- API Functions
  - sqlite3_aggregate_context()
  - sqlite3_auto_extension()
  - sqlite3_backup_finish()
  - sqlite3_backup_init()
  - sqlite3_backup_pagecount()
  - sqlite3_backup_remaining()
  - sqlite3_backup_step()
  - sqlite3_bind_xxx()
  - sqlite3_bind_parameter_count()
  - sqlite3_bind_parameter_index()
  - sqlite3_bind_parameter_name()
  - sqlite3_blob_bytes()
  - sqlite3_blob_close()
  - sqlite3_blob_open()
  - sqlite3_blob_read()
  - sqlite3_blob_write()
  - sqlite3_busy_handler()
  - sqlite3_busy_timeout()
  - sqlite3_changes()
  - sqlite3_clear_bindings()
  - sqlite3_close()
  - sqlite3_collation_needed()
  - sqlite3_column_xxx()
  - sqlite3_column_bytes()
  - sqlite3_column_count()
  - sqlite3_column_database_name()
  - sqlite3_column_decltype()
  - sqlite3_column_name()
  - sqlite3_column_origin_name()
  - sqlite3_column_table_name()
  - sqlite3_column_type()
  - sqlite3_commit_hook()
  - sqlite3_compileoption_get()
  - sqlite3_compileoption_used()
  - sqlite3_complete()
  - sqlite3_config()
  - sqlite3_context_db_handle()
  - sqlite3_create_collation()
  - sqlite3_create_function()
  - sqlite3_create_module()
  - sqlite3_data_count()
  - sqlite3_db_config()
  - sqlite3_db_handle()
  - sqlite3_db_mutex()
  - sqlite3_db_status()
  - sqlite3_declare_vtab()
  - sqlite3_enable_load_extension()
  - sqlite3_enable_shared_cache()
  - sqlite3_errcode()
  - sqlite3_errmsg()
  - sqlite3_exec()
  - sqlite3_extended_errcode()
  - sqlite3_extended_result_codes()
  - sqlite3_file_control()
  - sqlite3_finalize()
  - sqlite3_free()
  - sqlite3_free_table()
  - sqlite3_get_autocommit()
  - sqlite3_get_auxdata()
  - sqlite3_get_table()
  - sqlite3_initialize()
  - sqlite3_interrupt()
  - sqlite3_last_insert_rowid()
  - sqlite3_libversion()
  - sqlite3_libversion_number()
  - sqlite3_limit()
  - sqlite3_load_extension()
  - sqlite3_log()
  - sqlite3_malloc()
  - sqlite3_memory_highwater()
  - sqlite3_memory_used()
  - sqlite3_mprintf()
  - sqlite3_mutex_alloc()
  - sqlite3_mutex_enter()
  - sqlite3_mutex_free()
  - sqlite3_mutex_held()
  - sqlite3_mutex_leave()
  - sqlite3_mutex_notheld()
  - sqlite3_mutex_try()
  - sqlite3_next_stmt()
  - sqlite3_open()
  - sqlite3_open_v2()
  - sqlite3_overload_function()
  - sqlite3_prepare_xxx()
  - sqlite3_profile()
  - sqlite3_progress_handler()
  - sqlite3_randomness()
  - sqlite3_realloc()
  - sqlite3_release_memory()
  - sqlite3_reset()
  - sqlite3_reset_auto_extension()
  - sqlite3_result_xxx()
  - sqlite3_result_error_xxx()
  - sqlite3_rollback_hook()
  - sqlite3_set_authorizer()
  - sqlite3_set_auxdata()
  - sqlite3_shutdown()
  - sqlite3_sleep()
  - sqlite3_snprintf()
  - sqlite3_soft_heap_limit()
  - sqlite3_sourceid()
  - sqlite3_sql()
  - sqlite3_status()
  - sqlite3_step()
  - sqlite3_stmt_status()
  - sqlite3_strnicmp()
  - sqlite3_table_column_metadata()
  - sqlite3_threadsafe()
  - sqlite3_total_changes()
  - sqlite3_trace()
  - sqlite3_unlock_notify()
  - sqlite3_update_hook()
  - sqlite3_user_data()
  - sqlite3_value_xxx()
  - sqlite3_value_bytes()
  - sqlite3_value_numeric_type()
  - sqlite3_value_type()
  - sqlite3_version[]
  - sqlite3_vfs_find()
  - sqlite3_vfs_register()
  - sqlite3_vfs_unregister()
  - sqlite3_vmprintf()
Index
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with OReilly
Copyright