book

SQLite

Name: SQLite
Author: Chris Newman
ISBN: 9780672326851

by Chris Newman

November 2004

Intermediate to advanced

336 pages

6h 22m

English

Sams

Read now

Unlock full access

Copyright
Foreword
About the Author
We Want to Hear from You!
Reader Services
Introduction Welcome to SQLite
Why Use SQLite?Who This Book Is ForHow the Book Is OrganizedPart I—General SQLite UsePart II—Using SQLite Programming InterfacesPart III—SQLite AdministrationVersions of Software CoveredAdditional Resources
I. General SQLite Use
1. Getting Started
IntroductionFeatures and LimitationsSpeedPortabilitySecuritySQL ImplementationCustomizationSupported APIsC/C++PHPPerlTclOther Programming LanguagesScalabilityWhen Not to Choose SQLiteWhen SQLite Is Probably WrongNetwork or Client/Server ApplicationsHigh-Volume WebsitesHigh ConcurrencyWhen SQLite Is Probably RightWebsitesEmbedded DevicesAd-hoc File StorageInternal Data ManipulationLooking at SQLite DatabasesThe sqlite ToolSQLite Database BrowserHelp and Support
2. Working with Data
SQLite BasicsPrerequisitesObtaining the Sample DatabaseCreating and Connecting to a DatabaseExecuting SQL from a FileThe Sample DatabaseTypeless Data TypesThe INTEGER PRIMARY KEYWorking with DatesInserting the Sample DataBLOBs and Binary DataQuerying and Updating the DatabaseThe SELECT StatementThe WHERE ClauseString ComparisonsNULL ValuesArithmetic FunctionsString Operators and FunctionsJoinsAggregate FunctionsOrdering DataLimiting DataUpdating and Deleting RecordsLeft JoinsNested SubqueriesCartesian JoinsTransactionsThe sqlite_master Table
3. SQLite Syntax and Use
Naming ConventionsValid CharactersName LengthReserved KeywordsCase SensitivityCreating and Dropping TablesTable Column DefinitionsResolving ConflictsThe CHECK ClauseUsing Temporary TablesAnatomy of a SELECT StatementThe WHERE ClauseGROUP BY and Aggregate FunctionsHAVING ClauseColumn AliasesAttaching to Another DatabaseManipulating DataTransactionsInserting DataINSERT Using VALUESINSERT Using SELECTUpdating DataDeleting DataAltering a Table SchemaLoading Data from a FileIndexesCreating and Dropping IndexesUNIQUE IndexesViewsCreating and Dropping ViewsUsing ViewsTriggersCreating and Dropping TriggersUsing TriggersInterrupting a TriggerCreating a Trigger on a ViewWorking with Dates and TimesValid Timestring FormatsDisplaying a Formatted Date and TimeDate and Time ModifiersHandling Different Time ZonesSQL92 Features Not Supported

4. Query Optimization
Keys and IndexesWhat an Index DoesHow Indexes Work in SQLiteIndexing Multiple ColumnsUnique IndexesWhen to Create an IndexWhen Indexes Can Be UsedTweaking the Table ListBenchmarkingSome ExamplesThe EXPLAIN StatementUsing TransactionsThe VACUUM StatementTuning the Database Itself
II. Using SQLite Programming Interfaces
5. The PHP Interface
Configuring PHP for SQLite SupportConfiguring PHP for Linux/UnixConfiguring PHP for WindowsChecking for SQLite SupportGetting Information About the SQLite ExtensionUsing the PHP SQLite ExtensionOpening a DatabasePassing Queries and Commands to SQLiteUsing Commands That Change the DatabaseWorking with Retrieved DataWorking with User-Defined FunctionsThe php() FunctionCreating Aggregating FunctionsWorking with Binary Data in UDFsUsing the PEAR Database Class
6. The C/C++ Interface
Preparing to Use the C/C++ InterfaceUsing the C Language InterfaceOpening and Closing a DatabaseGetting Information About the SQLite LibraryExecuting SQL StatementsUsing Commands That Change the DatabaseCallback Functions for SELECT QueriesExecuting SQL Without a Callback FunctionFetching a Whole Dataset with sqlite_get_table()Adding New SQL FunctionsCreating Aggregating Functions
7. The Perl Interface
Preparing to Use the SQLite InterfaceAbout the Perl DBIGetting Information About the DBIUsing DBD DriversUsing the SQLite DBDOpening and Closing the DatabaseExecuting SQL StatementsUsing Bind VariablesUsing Commands That Change the DatabaseTransactionsFetching Records from the DatabaseError CheckingTracingAdding New SQL FunctionsCreating FunctionsCreating Aggregating Functions
8. The Tcl Interface
Preparing to Use the Tcl InterfaceUsing the Tcl InterfaceOpening and Closing a DatabaseExecuting SQL StatementsUsing Commands That Change the DatabaseFetching Records from the DatabaseThe onecolumn Database MethodValidating an SQL StatementAdjusting the Database Timeout ActionAdding New SQL Functions
9. The Python Interface
Preparing to Use the Python InterfaceUsing the Python InterfaceOpening and Closing a DatabaseExecuting SQL CommandsError HandlingConnection ParametersUsing Commands That Change the DatabaseTransactionsFetching Records from the DatabaseWorking with Data TypesAdding New SQL FunctionsAdding Aggregating Functions
III. SQLite Administration
10. General Database Administration
The PRAGMA CommandFetching Database InformationAltering Database ParametersAltering Database Parameters PermanentlyAltering Query ParametersAnalyzing the DatabaseBacking Up and Restoring DataThe .dump CommandExploring the SQLite Virtual Database EngineSQLite ArchitectureInterfaceTokenizerParserCode GeneratorVirtual MachineBackendB-TreePagerOS InterfaceRed/Black TreeVirtual Machine OpcodesAccess to the Database FileFile PermissionsLocking and TimeoutsMultithreaded Database Access
IV. Appendixes
A. Downloading and Installing SQLite
Obtaining SQLiteRPM Installation for LinuxBinary Installation for LinuxBinary Installation for WindowsInstalling from Source Code
B. Command Reference for the sqlite Tool
Dot CommandsObtaining a List of Dot CommandsChanging the Output FormatReading SQL Commands from a FileSending Output to a FileGetting Information about the Database SchemaChanging the sqlite PromptsSetting the Database TimeoutExiting the sqlite Tool
C. SQL Syntax Reference
Naming ConventionsReserved KeywordsSQL Command SyntaxCreating and Dropping Database ObjectsCREATE TABLEColumn ConstraintsCREATE INDEXCREATE VIEWCREATE TRIGGERThe SELECT StatementThe INSERT StatementThe UPDATE StatementThe DELETE StatementThe COPY StatementResolving ConflictsTransactionsAttaching to Other DatabasesPerformance TuningCommentsANSI SQL Commands and Features Not SupportedALTER TABLECOUNT(DISTINCT column-name)GRANT and REVOKEINSERT, UPDATE, and DELETE on ViewsRIGHT OUTER JOINCHECK and FOREIGN KEY ConstraintsTrigger LimitationsNested TransactionsVariable Subqueries
D. PHP Interface Reference
Predefined ConstantsRuntime ConfigurationFunction ReferenceOpening and Closing a DatabaseExecuting a QueryError ReportingFinding Information About a QueryProcessing a Result SetRandom Data Access FunctionsConvenience FunctionsFinding Information About SQLiteCustom Functions (UDF)
E. C Interface Reference
The Core APIOpening and Closing a DatabaseExecuting a QueryError CodesThe Non-Callback APICreating a Virtual MachineStep-by-Step Execution of an SQL StatementReturn CodesThe Extended APIFinding Information About the SQLite LibraryFinding Information About Changes to the DatabaseChecking SQL StatementsInterrupting an SQL StatementConvenience FunctionsThe _printf() Wrapper FunctionsMemory ManagementDealing with Locked Database FilesPerforming Background Jobs During Large QueriesAdding New SQL FunctionsRegistering FunctionsSetting Return ValuesReferencing Arbitrary Data
F. Perl Interface Reference
The Perl DBIOpening and Closing a DatabaseExecuting SQL StatementsUsing Bind ValuesMaking Data SafeGetting Information About a QueryTransactionsFetching Rows from a QueryError ReportingCreating User-Defined Functions
G. Tcl Interface Reference
The Tcl LibraryOpening and Closing a DatabaseExecuting a QueryConvenience FunctionsFinding Information About a QueryChecking SQL StatementsDealing with Locked Database FilesError ReportingFinding Information About SQLiteCustom Functions (UDF)
H. Python Interface Reference
Opening and Closing a DatabaseExecuting SQL StatementsSetting Data Type MappingsCreating User-Defined FunctionsError Handling
I. The Future of SQLite
SQLite Version 3.0Naming ChangesFile Format ChangesData TypingUser-Defined Collating SequencesImproved Concurrency

Content preview from SQLite

Chapter 3. SQLite Syntax and Use

In this chapter we look in detail at the SQL syntax understood by SQLite. We will discuss the full capabilities of the language and you will learn to write effective, accurate SQL.

You have already come across most of the supported SQL commands in Chapter 2, “Working with Data,” in the context of the demo database. This chapter builds on that knowledge by exploring the syntax and usage of each command in more detail to give a very broad overview of what you can do using SQLite.

Naming Conventions

Each database, table, column, index, trigger, or view has a name by which it is identified and almost always the name is supplied by the developer. The rules governing how a valid identifier is formed in SQLite are set out ...

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: 067232685XPurchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQLite

by Chris Newman

Chapter 3. SQLite Syntax and Use

Naming Conventions

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.