Index

Special Characters

$argc parameter, 225

$func_ref parameter, 225

$name parameter, 225

$name variable, 250

$type_id variable, 250

$x variable, 245

% operator, 61

& operator, 61

* operator, 61

_rowid_ value, 94

| operator, 61

|| operator, 61

+ operator, 61

< operator, 61

<< operator, 61

<= operator, 61

<> operator, 61

!= operator, 61

= operator, 61

== operator, 61

> operator, 61

>= operator, 61

>> operator, 61

/ operator, 61

- operator, 61

A

abort policy, 113

abort resolution, 113–115

abs( ) function, 66

access functions, for B-tree module API, 308–309

activation, of WAL, 319

add column clause, 55

ADD COLUMN command, 12

ADD CONSTRAINT command, 12

Add File dialog box, 263

Additional Dependencies property page, 28

administration, 118–124

attaching databases, 118–119

cleaning databases, 119

of databases, 35–45

backing up database, 42–43

creating database, 35–37

database file information, 44–45

exporting data, 39

formatting data, 40–41

getting schema information, 37–39

importing data, 40

unattended maintenance, 41–42

explain query plan command, 123

and pragmas, 120–122

auto_vacuum, 122

cache size, 120

database schema, 120–121

integrity_check, 122

synchronous, 121–122

temp_store, 122

temp_store_directory, 122

sqlite_master table, 123

administrators, SQLite for, 3

Advanced tab, 19

after keyword, 108

after trigger, 110

after value, 134

aggregates, 67

in Extension C API, 204–209

example of, 206–209

registering, 205–206

with language extensions

for Perl, 225–226

for Python, 231–232

user-defined, 137

aliases, for tables, 77–79

ALTER COLUMN command, 12

ALTER TABLE command, 12, 54

alter table statement, 286, 288

altering tables, 54–55

ambiguity, of column names, 77

aName variable, 272

AND operator, 61

Android DDMS, 280

Android Developer Tools, 280–281

Android development, 279–301

backup service for databases, 300

and large databases, 300–301

prerequisites for, 279–284

additional components, 281–284

downloading Android Developer Tools, 280–281

downloading Android SDK Starter Package, 280

JDK (Java Development Kit), 280

Seinfeld Android app, 294–300

adding database to project, 296

creating project, 295–296

linking data and user interface, 298–299

querying foods table, 296–297

user interface for, 297–298

viewing app, 299–300

SQLiteDatabase class, 286–290

convenience methods for, 288–289

executing queries with, 287–288

implementing, 290–292

opening and closing databases with, 286–287

transactions with, 289

useful methods for, 290

SQLiteOpenHelper class, 285–286, 290–292

SQLiteQueryBuilder class, 293–294

Android Platforms component, 282

Android Virtual Device (AVD), 283

android.database.sqlite namespace, 285

API, for B-tree module

access functions, 308–309

configuration functions, 310

cursor functions, 309

record functions, 310

table functions, 309

Appcelerator Titanium framework, 258, 284

Application Settings, 26, 28

applicationDidFinishLaunching event, 276

applicationDidFinishLaunching function, 269

applicationWillTerminate event, 272

apt utility, 31

apt-get utility, 31

architecture, 5–8

back end, 7–8

compiler, 6

interface, 6

utilities, 8

virtual machine, 6–7

Arithmetic operators, 62

as keyword, 79

asc sort order, 65

atomic principle, 111

attach command, 118

ATTACH event, 182

attach statement, 184

attaching databases, 118–119

auto_vacuum pragma, 119, 122, 128

autocommit mode, and write transactions, 143–145

autoincrement column, 95

autoincrement constraint, 95–96

autoincrement keyword, 94–95

available_drivers( ) function, 222

AVD (Android Virtual Device), 283

avg( ) function, 67, 137, 195

avg aggregate, 67

B

b column, 317

back end, architecture of SQLite, 7–8

backing up, 42–43

BackupAgentHelper API, 300

BackupManager API, 300

Bakery row, 64

bar.db database, 10–11

base table, 54

before keyword, 108

before trigger, 110

begin command, 111, 116–118, 139, 141

begin exclusive command, 118, 148

begin immediate command, 118, 148–149

begin line, 141

begin statement, 117

begin.commit command, 141

begin…commit/rollback transaction scope, 112

beginTransaction( ) method, 249, 289

beginTransactionWithListener( ) method, 289

binaries

for Linux, 30–31

for Mac OS X, 30–31

for POSIX systems, 30–31

binary collation, 101, 138, 216

Binary large object (BLOB), 4, 102

binary operators, for where clause, 60–62

bind_param( ) method, 234

bind_params( ) method, 234

BLOB (Binary large object), 4, 102

blob class, 102–103, 196

blob columns, 315

B-tree module, 303–310

API for

access functions, 308–309

configuration functions, 310

cursor functions, 309

record functions, 310

table functions, 309

data structures of SQLite, 127

and database file format

B+trees, 304–305

field types in, 305–306

hierarchical data organization in, 307

overflow pages in, 307–308

page reuse in, 304

record structure in, 305

records in, 304

btree.c file, 310

btree.h file, 310

Build and Run menu option, 272

buildUnionQuery( ) method, 294

busy( ) function, 147

busy conditions, error handling in C API of, 176–177

busy handler, using with transactions, 146–147

C

C API, 153–193

error handling in, 174–176

of busy conditions, 176–177

of schema changes, 177–178

fetching records with, 164–169

column information for, 165–166

column values in, 166–167

example of, 168–169

operational control functions, 178–189

commit_hook() function, 178–179

rollback_hook() function, 179

set_authorizer() function, 180–189

update_hook() function, 179–180

parameterized queries with, 169–174

named parameters, 173

numbered parameters, 172

Tcl parameters, 173–174

prepared queries with, 161–164

compilation of, 161–162

execution of, 162

finalization of, 163–164

threading support in, 190–193

and memory management, 193

shared cache mode, 190–193

wrapper functions in, 153–160

for connecting and disconnecting, 153–155

exec() function, 155–159

get_table() function, 159–160

Cache partition size parameter, 284

cache_size pragma, 120, 145

Cartesian join, 75

cascade action, 101

case expression, 83–84

cast( ) function, 212, 317

cast table, 115

cast.name column, 115

check constraints, 99–100, 137, 315, 317–318

checkpoints, and WAL, 318

Churcher, Claire, 47

class values, 103

Classes folder, 265

-classpath option, 237

clauses

distinct clause, 72

group by clause, 67–71

limit clause, 64

order by clause, 64–66

where clause, 59–64

binary operators, 60–62

glob operator, 64

like operator, 63

logical operators, 63

operators, 60

values, 60

cleaning databases, 119

cleanup handlers, for functions in Extension C API, 202–203

clone command, 23

close( ) method, 228, 244, 285, 287

Close instruction, 151

closing databases, with SQLiteDatabase class, 286–287

CLP (command-line program), 17, 32–35

in command-line mode, 34–35

installing on Windows, 18–21

in shell mode, 33–34

cnx argument, 197

coalesce function, 85–86

code and SQLite, 149–152

importance of finalizing, 150–151

shared cache mode, 151–152

using multiple connections, 149–150

collate keyword, 101

collate nocase constraint, 54

collate1.sql file, 209

collate2.sql file, 210

Collation interface, 199

collation_name parameter, 119

collations

for columns, and indexes, 107

and domain integrity, 101

in Extension C API, 209–217

defined, 210–212

on demand, 216–217

example of, 212–216

overview of, 210–212

types of, 212

user-defined, 138

column constraints, 54

column names, and ambiguity of, 77

column types, and type affinity, 313

column_def attribute, 55

column_definitions attribute, 54

column_list variable, 87

columns

collations for, and indexes, 107

information about, in C API, 165–166

values of, in C API, 166–167

columns( ) method, 239

columns variable, 106

command-line program. See CLP

commands

delete command, 92

explain query plan command, 123

insert command, 87–91

multiple rows, 90–91

one row, 87–89

set of rows, 89–90

select command, 57–59

syntax for SQL in SQLite, 51–52

update command, 91–92

comma-separated values (CSV), 40

comments, syntax for SQL in SQLite, 53

commit( ) method, 249

commit command, 111, 115, 118, 139, 141, 143

commit line, 141

commit_hook( ) function, in C API, 178–179

compactness, features of SQLite, 9

compilation, of prepared queries, 161–162

compile( ) method, 239

compiler, 6

compileStatement( ) method, 288

compound queries, 81–83

con.commit( ) lines, 230

concurrency, and WAL, 319

Configuration drop-down box, 26, 28

configuration functions, for B-tree module API, 310

configure command, 30

conflict resolution, and transactions, 112–115

connect( ) function, 227

connecting to SQLite, using language extension

for Java, 238

for Perl, 222

for PHP, 248

for Python, 227

for Ruby, 233

for Tcl, 244

Connection::create_aggregate( ) method, 231

connections, data structures of SQLite, 126–127

constraints

of check, 99–100

of foreign key, 100–101

of not null, 98–99

of primary key, 94–97

of unique, 93

contacts database, 93

contacts table, 92, 94, 97

contacts.name column, 101

Control Panel, 19

convenience, features of SQLite, 10–11

correlated subquery, 80

:cosmo parameter, 173

count( ) function, 67, 69, 137, 195

count aggregate, 67–68, 72

count(*)<20 predicate, 71

create index command, 115

Create Table As Select (CTAS), 91

create table command, 53–55, 88, 90

create table statement, 91, 100, 161, 286, 288

create_function( ) method, 230

CREATE_IF_NECESSARY behavior, 287

createaggregatefunction( ) function, 137

crf( ) function, 216

cross joins, 75–76

cross product, 75

c:sqlite directory, 25, 33

CSV (comma-separated values), 40

CTAS (Create Table As Select), 91

ctx argument, 198

current_date reserved word, 98

current_time reserved word, 98

current_timestamp reserved word, 98

cursor functions, for B-tree module API, 309

Cursor.executemany( ) method, 229

D

data argument, 179, 216

data definition language (DDL), 38, 53

data integrity, 92–104

domain integrity, 97–101

check constraints, 99–100

and collations, 101

default values, 98

foreign key constraints, 100–101

NOT NULL constraints, 98–99

entity integrity, 93–97

primary key constraints, 94–97

unique constraints, 93

indexes, 106–108

and collations for columns, 107

utilization of, 107–108

storage classes for SQLite, 101–104

triggers, 108–111

error handling with, 110

and updatable views, 110–111

update triggers, 109–110

views in SQLite, 104–106

Data Manipulation Language (DML), 39, 53, 87

data source name (DSN), 248

data structures, 126–135

B-tree and pager, 127

connections and statements, 126–127

database administrator (DBA), 3

database file format, and B-tree module

B+trees, 304–305

field types in, 305–306

hierarchical data organization in, 307

overflow pages in, 307–308

page reuse in, 304

record structure in, 305

records in, 304

database schema pragma, 120–121

database_list pragma, 118, 121

database_name parameter, 118–119

Database::create_function( ) method, 236

Database::get_first_row( ) method, 235

Database::get_first_value( ) method, 235

databaseName variable, 271

databasePath variable, 270

Database::prepare( ) method, 233

Database::query( ) method, 235

databases

attaching, 118–119

backing up, 42–43

cleaning, 119

connecting to, 128

creating, 35–37, 53–55

example, 47–50

installation, 48–49

running examples, 49–50

exporting data from, 39–41

file information for, 44–45

formatting data, 40–41

importing data into, 40

querying, 55–86

daterepr property, 241

db argument, 216

db parameter, 293

db2 database, 119

db2.foo table, 119

DBA (database administrator), 3

DBI::connect function, 222

DBL (declarative burger language), 51

DDL (data definition language), 38, 53

DDMS, Android, 280

deadlocks, and transactions, 116–117

declarative burger language (DBL), 51

declarative language, 50

default constraint, 98–99

default keyword, 98

default values, and domain integrity, 98

default_cache_size pragma, 120

deferrable clause, 101

deferred transaction type, 117

delete command, 92, 108, 111

delete operation, 135, 179–180

Delete SQLITE_BUSY value, 146

delete statement, 12, 132, 159, 184

delete trigger, 109, 111

derived tables, 80, 104

desc sort order, 65

description value, 83

detach command, 119, 185

DETACH event, 182

detach statement, 184

details view, 105

Developer Program, membership of, 254

Developer Tools, Android, 280–281

developers, 2–3

difference operation, 82

disconnect method, 222

distinct clause, 72

distinct keyword, 72, 293

distutils package, 226

divide operation, 56

Djava.library.path=. option, 237

DLL (Dynamic-link library), installing on Windows, 21–22, 25–27

dlltool utility, 22

DML (Data Manipulation Language), 39, 53, 87

do( ) method, 223

Documentation component, 282

domain integrity, 97–101

check constraints, 99–100

and collations, 101

default values, 98

foreign key constraints, 100–101

NOT NULL constraints, 98–99

double data type, 241

downloading SQLite, 17–18

dpkg utility, 31

DROP COLUMN command, 12

drop index command, 107, 193

drop table command, 193

drop table statement, 286

DROP VIEW command, 106

dselect utility, 31

DSN (data source name), 248

.dump command, 39–40, 42–43

dylib file, 262

.dylib shared library, 17

Dynamic-link library (DLL), installing on Windows, 21–22, 25–27

E

.e command, 34

echo( ) function, 203–204, 233

.echo command, 40

Edit System Variables dialog box, 19

else condition, 84

embedded databases, 1

Empty Project check box, 26, 28

endTransaction( ) method, 289

entity integrity, 93–97

primary key constraints, 94–97

unique constraints, 93

Environmental Variables button, 19

episodes table, 7, 48, 78–79, 145–146, 151, 306

episodes_foods table, 78

equality (=) operator, 108

errmsg argument, 157

error conditions, for functions in Extension C API, 203

error handling

in C API, 174–176

of busy conditions, 176–177

of schema changes, 177–178

and SQLite API, 133–134

with triggers, 110

eTextRep argument, 197

example database, 47–50

installation, 48–49

running examples, 49–50

examples folder, 195

examples zip file, 221

except keyword, 81

except operation, 82

exclusive command, 118

EXCLUSIVE lock, 138, 143–145, 148

exclusive state

and transactions, 145

for write transactions, 142–143

exclusive transaction type, 117–118

exec( ) function, in C API, 155–159

exec_query( ) function, 240

execSQL( ) methods, 287–288

execute( ) method, 222–224, 228–229, 233

executemany( ) method, 230

.exit command, 20, 32, 34, 37, 42

explain query plan command, 123

exporting data from database, 39–41

Extension C API, 195–217

aggregates with, 204–209

example of, 206–209

registering, 205–206

collations with, 209–217

defined, 210–212

on demand, 216–217

example of, 212–216

overview of, 210–212

types of, 212

functions with, 200–204

cleanup handlers for, 202–203

error conditions for, 203

returning input values, 203–204

values for, 202

registering functions in, 196–197

step function for, 198

values for, 198–199

F

f parameter, 266

factory parameter, 287

fail resolution, 113–114

fcntl( ) implementation, 12

features, 8–11

compactness, 9

convenience, 10–11

flexibility, 9

liberal licensing, 9

portability, 8

reliability, 10

simplicity, 9

zero configuration, 8

fetch( ) method, 222–223

fetchrow_array( ) method, 223

fetchrow_hashref( ) method, 223

f.id variable, 80

field types, and database file format, 305–306

file information, for database, 44–45

file value, 122

file.csv file, 41

filename argument, 154

filename parameter, 118

file.sql file, 39–40

fillData( ) method, 298

final( ) function, 198

finalize( ) method, 137, 150–151, 231

find utility, 3

finish( ) method, 224

Fink package management system, 31

flags parameter, 155, 287

flexibility of SQLite, 9

FLOAT class, 196

float data type, 241

FlushFileBuffers( ) function, 143

foo table, 118–119, 185–186

foo_idx index, 108

Food class, in iSeinfeld example app, 265–266

foo.db database, 11

FoodViewController class, in iSeinfeld example app, 266–268

FoodViewController UIViewController class, 266

FoodViewController.h file, 266–267

FoodViewController.m file, 267

FoodViewController.xib file, 266–267

FOR EACH ROW behavior, 12

FOR EACH STATEMENT trigger, 12

foreign key, constraints of, 100–101

foreign key relationship, 72

fork( ) function, 190

formatting

data from database, 40–41

SQL statements, 134–135

Fossil source control, compiling source code on Windows, 23–25

fossil.exe file, 23

framework alternatives, for iOS development, 258–259

frequency column, 84

frequency values, 84

from clause, 57–59, 67, 70, 73, 80

frq argument, 189

fsync( ) function, 143, 320

full mode, 122

full outer join, 76

FULL OUTER JOIN statement, 12

full synchronous pragma, 121

functions

for B-tree module API

access functions, 308–309

configuration functions, 310

cursor functions, 309

record functions, 310

table functions, 309

in Extension C API, 200–204

cleanup handlers for, 202–203

error conditions for, 203

registering, 196–197

returning input values, 203–204

values for, 202

SQL for SQLite, 66

user-defined, 136–137

G

GCC (GNU Compiler Collection), 28, 31

gdbm storage manager, 4

get_table( ) function, 132–133, 159–160

getAttribute( ) method, PDO class, 249

getReadableDatabase( ) method, 285

getWriteableDatabase( ) method, 285

glob operator, where clause, 64

GNU Compiler Collection (GCC), 28, 31

GRANT command, 13

grep utility, 3

group by clause, 67–71

group_concat( ) function, 206

H

.h command, 34

.h file, 26

Häring, Gerhard, 226

having clause, 71

having predicate, 70

.headers command, 37–38, 40

hello_newman( ) method, 136–137, 200, 225, 231, 236, 240–241, 247, 251

.help command, 20, 32–33

-help switch, 34

Hewlett-Packard Unix (HP-UX), 3

Hipp, Richard, 3, 5

history of SQLite, 3–4

hot journal, 145

HP-UX (Hewlett-Packard Unix), 3

.htaccess controls, 248

I

ICL (imperative chef language), 51

id column, 37, 54, 76, 87, 89, 94–95, 110, 124

id value, 89, 100

identifiers, 53

if construct, 271

ignore argument, 110

ignore resolution, 113

IllegalStateException exception, 289

immediate transaction type, 117–118

imperative chef language (ICL), 51

imperative language, 50

.import [file][table] command, 40

.import command, 39

importing data into database, 40

IN operator, 61, 79, 108

index_info schema pragma, 121

index_list schema pragma, 121

index_name variable, 106

indexes, 106–108

and collations for columns, 107

utilization of, 107–108

.indices [table name] command, 37

.indices shell command, 107

init function, 226, 265

init option, 42

initialValues ContentValue map, 289

initialValues map, 289

initWithName function, 265–266

inline views, 80

inner joins, 74–75

Input submenu, 26, 28

insert( ) method, 289

insert command, 87–91

multiple rows, 90–91

one row, 87–89

set of rows, 89–90

insert filter, 183

insert operation, 135, 179–180

insert or replace expression, 114

INSERT OR REPLACE (…) statement, 10

insert statements, 37, 88–89, 98, 132, 159, 183, 288, 312, 315

insert trigger, 109, 111

Install New Software screen, 280

Installation Type screen, 256

instead of keywords, 110

INSTEAD OF triggers, 12

int data type, 241

integer class, 102–103, 196

integer column, 314

integer primary key, 54, 88, 94–96

integer type, 54

integrity_check pragma, 122

interface, architecture of SQLite, 6

Interface Builder tool, 267–268

intersect keyword, 81

intersect operation, 81–82

intersection, 74

inTransaction( ) method, 289

iOS development, 253–277

framework alternatives for, 258–259

iSeinfeld example app, 259–276

accessing database upon startup, 269–272

adding SQLite framework to project, 261–262

creating Xcode project, 259–260

Food class in, 265–266

foods database or, 263–264

FoodViewController class in, 266–268

and large databases, 276

prerequisites for, 253–259

Developer Program membership, 254

downloading iOS SDK, 254–258

is not null operator, 85

is null operator, 85

IS operator, 62

iSeinfeld example app, 259–276

accessing database upon startup, 269–272

adding SQLite framework to project, 261–262

creating Xcode project, 259–260

Food class in, 265–266

foods database or, 263–264

FoodViewController class in, 266–268

iSeinfeldAppDelegate class, 269

iSeinfeldAppDelegate.h file, 269

iSeinfeldAppDelegate.m file, 269–270, 274

isReadOnly( ) method, 285–286

J

Java, language extension for, 236–243

connecting to, 238

installation of, 237

JDBC support with, 241–243

query processing with, 238–240

user-defined functions with, 240–241

Java Database Connectivity (JDBC), support for Java language extension, 241–243

Java Development Kit (JDK), 280

Java runtime environment (JRE), 280

java.lang.String data type, 241

java.sql.Date data type, 241

javasqlite3.mak file, 237

javasqlite.mak file, 237

java.sql.Time data type, 241

java.sql.Timestamp data type, 241

JDBC (Java Database Connectivity), support for Java language extension, 241–243

JDK (Java Development Kit), 280

join condition, 75

joining tables, 72–77

cross joins, 75–76

inner joins, 74–75

natural joins, 77

outer joins, 76

preferred syntax for, 77

journal_mode PRAGMA, 320

JRE (Java runtime environment), 280

justincase savepoint, 112

K

keywords, 53

L

Label elements, 267

language extensions, 219–252

Java, 236–243

connecting to, 238

installation of, 237

JDBC support with, 241–243

query processing with, 238–240

user-defined functions with, 240–241

Perl, 221–226

aggregates with, 225–226

connecting to, 222

installation of, 221–222

parameters for, 224

query processing with, 222–224

user-defined functions with, 224–225

PHP, 247–252

connecting to, 248

installation of, 248

query processing with, 248–251

user-defined functions with, 251–252

Python, 226–232

aggregates with, 231–232

APSW as alternative, 232

connecting to, 227

installation of, 226–227

parameters for, 229–230

query processing with, 227–229

user-defined functions with, 231

Ruby, 232–236

connecting to, 233

installation of, 232–233

parameters for, 234–235

query processing with, 233–234

user-defined functions with, 236

selecting, 220–221

Tcl, 243–247

connecting to, 244

installation of, 243–244

query processing with, 244–246

user-defined functions with, 247

large databases, and Android development, 300–301

large file support (LFS), 176

last_insert_rowid( ) function, 37, 88–89, 159

last_step( ) function, 241

left outer join, 76

LEFT OUTER JOIN statement, 12

length_first collation, 212–214, 216

length(name) expression, 67

LFS (large file support), 176

liberal licensing, features of SQLite, 9

libsqlite_jni.so file, 237

libsqlite3.0.dylib file, 262

libsqlite3.dylib library, 262

life cycles, for transactions, 138–139

like operator, 37, 62–64

limit clause, 64, 80, 293

limit keyword, 64–65

Linker folder, 26, 28

Linux, 30–32

binaries and packages, 30–31

compiling SQLite from source, 31–32

.list command, 40

literals, syntax for SQL in SQLite, 52

lock states, transactions, 139–141

LockFile( ) function, 148

LockFileEx( ) function, 148

locks

and deadlocks, 116–117

and transactions, 115–116

log table, 109

log_sql( ) function, 201

logical operators, for where clause, 63

lower( ) function, 66

M

Mac OS X, 30–32

binaries and packages, 30–31

compiling SQLite from source, 31–32

MacPorts package management system, 31

main database, 119

main.xml file, 297

make command, 30

manifest typing, 311–313

match predicate, 64

max( ) function, 67

maxed_out table, 96

maxed_out.id value, 95

Maximum VM application heap size option, 284

memcmp( ) function, 101, 103–104, 195, 212

memory management, and threading support in C API, 193

:memory: string, 128

memory value, 122

MERGE statement, 10

methods, for SQLiteDatabase class, 288–290

min( ) function, 67

MinGW, building SQLite with on Windows, 28–30

.mode command, 37–38, 40

modifying data, 87–92

Module Definition File property page, 26

multiple connections, using in code, 149–150

myDatabaseAdapter class, 298

myDatabaseHelper class, 294, 296, 300

N

n parameter, 266

name attribute, 48, 106

name column, 54–55, 87–88, 93, 98, 101, 109, 115, 123

name parameter, 104, 106, 285

name table, 104

name value, 101, 109

named parameters, in C API, 173

nArg argument, 197–198

natural joins, 77

nBytes argument, 198

new.name column, 110

newrow( ) method, 239

next( ) method, 233

no action action, 101

nocase collation, 101, 138, 212

normal synchronous pragma, 122

not null constraints, 54, 98–99, 113

NOT operator, 64

null, 84

NULL class, 102–103, 196

null values, 83–84, 89

nullColumnHack parameter, 289

nullif function, 86

.nullvalue command, 40

numbered parameters, in C API, 172

O

ODBC (Open Database Connectivity), 4

off synchronous pragma, 122

offset clause, 64

offset keyword, 64–65

oid value, 94

on delete restrict option, 100

onCreate( ) method, 285–286, 298

onOpen( ) method, 285–286

onUpgrade( ) method, 285–286

open command, 25

Open Database Connectivity (ODBC), 4

Open drop-down box, 19

openDatabase( ) method, 287

opening databases, with SQLiteDatabase class, 286–287

openOrCreate Database( ) method, 287

operation_code argument, 180

operational control, with SQLite API, 135

operators

glob operator, where clause, 64

for where clause, 60–63

binary operators, 60–62

like operator, 63

logical operators, 63

OR operator, 61

order by clause, 64–66, 80–82, 210

order by keyword, 66

order by subquery, 80

outer joins, 76

.output [filename] command, 39

.output stdout command, 39

overflow pages, and database file format, 307–308

Owens, Michael, 226

P

p->zResult field, 208

package require directive, 244

packages

for Linux, 30–31

for Mac OS X, 30–31

for POSIX systems, 30–31

page cache, and write transactions, 145–146

page_size pragma, 128

pager, 127

pages, and database file format

overflow pages in, 307–308

reuse of, 304

parameters

in C API, 169–174

named parameters, 173

numbered parameters, 172

Tcl parameters, 173–174

for language extensions

for Perl, 224

for Python, 229–230

for Ruby, 234–235

and SQLite API, 131–132

Path entry, 19

PATH environment variable, 23

pBlocked parameter, 193

PDO (PHP Data Objects), 247–248

PDO_ATTR_TIMEOUT parameter, 249

PDOStatement class, 248

PDOStatement::bindColumn( ) method, 250

PDOStatement::bindParam( ) method, 249

pending state, for write transactions, 142

performance

and limitations of SQLite, 11–13

and WAL, 321

Perl, language extension for, 221–226

aggregates with, 225–226

connecting to, 222

installation of, 221–222

parameters for, 224

query processing with, 222–224

user-defined functions with, 224–225

perlsum( ) aggregate, 225

perlsum.pm package, 225

phone column, 54, 93, 98–99

phone value, 93, 99, 101

PhoneGap framework, 258, 284

PHP, language extension for, 247–252

connecting to, 248

installation of, 248

query processing with, 248–251

user-defined functions with, 251–252

PHP Data Objects (PDO), 247–248

phpsum aggregate, 251

Platforms component, Android, 282

pNotifyArg pointer, 192

portability, features of SQLite, 8

POSIX systems, 30–32

binaries and packages, 30–31

compiling SQLite from source, 31–32

power consumption, 12

ppDb argument, 155

ppStmt parameter, 161

PRAGMA commands, 39

PRAGMA journal_mode=WAL setting, 319

pragmas, 120–122

auto_vacuum, 122

cache size, 120

database schema, 120–121

integrity_check, 122

synchronous, 121–122

temp_store, 122

temp_store_directory, 122

Precompiled Binaries For Windows section, 21

--prefix=DIR option, 237

prepare( ) method, 131–132, 222, 224, 249

prepared queries

with C API, 161–164

compilation of, 161–162

execution of, 162

finalization of, 163–164

executing, 129–131

prerequisites

for Android development, 279–284

additional components, 281–284

downloading Android Developer Tools, 280–281

downloading Android SDK Starter Package, 280

JDK (Java Development Kit), 280

for iOS development, 253–259

Developer Program membership, 254

downloading iOS SDK, 254–258

primary key constraints, 54, 94–97

print_sql_result( ) function, 187, 201

.prompt [value] command, 40

Property Pages dialog box, 26

public boolean isDbLockedByOtherThreads( ) method, 290

public int getVersion( ) method, 290

public long getMaximumSize( ) method, 290

public static int releaseMemory( ) method, 290

pUserData argument, 197–198

pysum( ) aggregate, 137, 231

Python, language extension for, 226–232

aggregates with, 231–232

APSW as alternative, 232

connecting to, 227

installation of, 226–227

parameters for, 229–230

query processing with, 227–229

user-defined functions with, 231

pzTail parameter, 164

Q

queries, with SQLiteDatabase class, 287–288

query( ) method, 239, 248–249, 287–288

querying databases, 55–86

compound queries, 81–83

executing prepared queries, 129–131

executing wrapped queries, 132–133

relational operations, 55–56

subqueries, 79–80

queryWithFactory( ) method, 288

R

raise( ) function, 110

RANK( ) function, 13

rawQuery( ) method, 287–288, 294

rawQueryWithFactory( ) method, 288

RDBMS (relational database management system), 1

.read command, 40

read transactions, 141

read_committed pragma, 191

read_uncommitted pragma, 151, 191

readFoodsFromDatabase function, 270

README file, 196

read-uncommitted isolation level, shared cache mode, 191–192

read-uncommitted mode, 151

real class, 102–103

record functions, for B-tree module API, 310

records, and database file format, 304–305

recovery, issues with WAL, 321–322

recursive triggers, 12

referential integrity, 92

regexp predicate, 64

registering in Extension C API

aggregates, 205–206

functions, 196–197

reindex command, 119

relational database management system (RDBMS), 1

relational operations, querying databases, 55–56

relational operators, 62

release command, 112

reliability, features of SQLite, 10

rename clause, 55

rename operation, 77

RENAME TABLE command, 12

replace( ) methods, 289

replace command, 114

res folder, 297

RESERVED connection, 148

RESERVED lock, 140–141, 144, 148

RESERVED state, 140–142, 144–145, 147–148

reset( ) function, 132, 150–151

Resources folder, 263

restrict action, 101

resultp argument, 159

resultp pointer, 159

reverse collation, 101

REVOKE command, 13

right outer join, 76

RIGHT OUTER JOIN statement, 12

R.java file, 298

rollback( ) method, 249

rollback command, 111–112, 139

rollback resolution, 113, 115

rollback_hook( ) function, in C API, 179

rootpage column, 123

Row class, 239

ROW_NUMBER( ) function, 13

rowid argument, 180

rowid column, 96–97

rowid value, 94–96

rows, and insert command

multiple rows, 90–91

one row, 87–89

set of rows, 89–90

rsync utility, 3

RTRIM collation, 138

Ruby, language extension for, 232–236

connecting to, 233

installation of, 232–233

parameters for, 234–235

query processing with, 233–234

user-defined functions with, 236

S

SAggCtx struct, 208

Samples component, 282

savepoint command, 112

.schema [table name] command, 38

.schema command, 42, 55

.schema foods shell command, 88

schema information, administration of databases, 37–39

.schema shell command, 55

schema view, 37, 40

scope, for transactions, 111–112

SDK Starter Package, prerequisite for Android development, 280

Seinfeld Android app, 294–300

adding database to project, 296

creating project, 295–296

linking data and user interface, 298–299

querying foods table, 296–297

user interface for, 297–298

viewing app, 299–300

select clause, 57–59, 65, 67–68, 70–71, 79, 89

select command, 55–59, 67, 72, 75, 80, 87

select form, insert command, 90

select hello_newman( ) function, 137

select id clause, 77

SELECT statements, 10–11, 129, 131, 210

selectrow( ) function, 222

select-stmt parameter, 104

.separator command, 40

SET clause, 92

set default action, 101

set null action, 101

set_authorizer( ) function, in C API, 180–189

setAttribute( ) method, PDO class, 249

setDistinct( ) method, 293

setProjectionMap (Map<String, String> columnMap) method, 293

setTables( ) method, 293

setTransactionSuccessful( ) method, 289

setup.cfg file, 227

setup.exe file, 28–29

shared cache mode

overview, 151–152

threading support in C API, 190–193

read-uncommitted isolation level, 191–192

unlock notification, 192–193

SHARED connection, 148

Shared library, 31

SHARED locks, 142, 144, 146–147, 149–151

shared object (so), 17

SHARED state, 140–142, 144–145

shell mode, command-line program in, 33–34

shell.c file, 26, 28

short data type, 241

.show command, 40

show_datatypes pragma, 242

simplicity, features of SQLite, 9

sleep( ) function, 177

so (shared object), 17

source clause, 78

source code

compiling on Linux, 31–32

compiling on Mac OS X, 31–32

compiling on POSIX systems, 31–32

compiling on Windows, 22–25

Fossil source control, 23–25

stable source distribution, 22

Source Code section, 22

spam_mother_in_law( ) function, 147

sprintf( ) function, 134

sql argument, 156, 159

sql column, 123

SQL for SQLite

administration, 118–124

attaching databases, 118–119

cleaning databases, 119

explain query plan command, 123

and pragmas, 120–122

of pragmas, 122

sqlite_master table, 123

aggregates, 67

aliases, for tables, 77–79

and ambiguity of column names, 77

case expression, 83–84

compound queries, 81–83

creating databases, 53–55

altering tables, 54–55

creating tables, 53–54

data integrity, 92–104

domain integrity, 97–101

entity integrity, 93–97

indexes, 106–108

storage classes for SQLite, 101–104

triggers, 108–111

views in SQLite, 104–106

delete command, 92

distinct clause, 72

example database, 47–50

installation, 48–49

running examples, 49–50

functions, 66

group by clause, 67–71

indexes, 106–108

and collations for columns, 107

utilization of, 107–108

insert command, 87–91

multiple rows, 90–91

one row, 87–89

set of rows, 89–90

joining tables, 72–77

cross joins, 75–76

inner joins, 74–75

natural joins, 77

outer joins, 76

preferred syntax for, 77

limit clause, 64

modifying data, 87–92

null in SQLite, 84

order by clause, 64–66

querying databases, 55–86

select command, 57–59

storage classes for, 101–104

subqueries, 79–80

syntax for, 50–53

commands, 51–52

comments, 53

identifiers, 53

keywords, 53

literals, 52

transactions, 111–118

and conflict resolution, 112–115

and deadlocks, 116–117

and locks, 115–116

scope for, 111–112

types of, 117–118

triggers, 108–111

error handling with, 110

and updatable views, 110–111

update triggers, 109–110

update command, 91–92

views in, 104–106

where clause, 59–64

binary operators, 60–62

glob operator, 64

like operator, 63

logical operators, 63

operators, 60

values, 60

SQLite

for administrators, 3

for developers, 2–3

downloading, 17–18

embedded database, 1

history of, 3–4

tools for, 45

where used, 4–5

SQLite API, 125–138

core API, 127–135

connecting to database, 128

and error handling, 133–134

executing prepared queries, 129–131

executing wrapped queries, 132–133

formatting SQL statements, 134–135

and parameters, 131–132

operational control with, 135

principal data structures, 126–135

B-tree and pager, 127

connections and statements, 126–127

user-defined extensions

aggregates, 137

collations, 138

functions, 136–137

using threads, 136

sqlite directory, 33

sqlite_analyzer view, 44

SQLITE_ATTACH event, 182

SQLITE_BUSY error, 118, 133

SQLITE_BUSY value, 146–148, 151

sqlite_column_type( ) function, 199

sqlite_complete( ) function, 164

sqlite_create_funtion( ) function, 64

SQLITE_CREATE_INDEX event, 181

SQLITE_CREATE_TABLE event, 181

SQLITE_CREATE_TEMP_INDEX event, 181

SQLITE_CREATE_TEMP_TABLE event, 181

SQLITE_CREATE_TEMP_TRIGGER event, 181

SQLITE_CREATE_TEMP_VIEW event, 181

SQLITE_CREATE_TRIGGER event, 181

SQLITE_CREATE_VIEW event, 181

SQLITE_DELETE event, 181

SQLITE_DENY constant, 180

SQLITE_DETACH event, 182

SQLITE_DONE value, 129, 151

SQLITE_DROP_INDEX event, 181

SQLITE_DROP_TABLE event, 181

SQLITE_DROP_TEMP_INDEX event, 181

SQLITE_DROP_TEMP_TABLE event, 181

SQLITE_DROP_TEMP_TRIGGER event, 181

SQLITE_DROP_TEMP_VIEW event, 181

SQLITE_DROP_TRIGGER event, 181

SQLITE_DROP_VIEW event, 181

SQLITE_ENABLE_COLUMN_METADATA directive, 166

SQLITE_ENABLE_MEMORY_MANAGEMENT directive, 193

SQLITE_ERROR error, 133

SQLITE_FULL error, 95

SQLITE_IGNORE constant, 180, 183

SQLITE_INSERT event, 181

SQLITE_LOCKED value, 151

sqlite_master table, 107, 123, 186, 188, 192, 303–304

sqlite_master view, 38, 44, 135

SQLITE_NOMEM error, 208

SQLITE_OK constant, 180

SQLITE_OPEN_CREATE flag, 154

SQLITE_OPEN_FULLMUTEX flag, 155

SQLITE_OPEN_NOMUTEX flag, 155

SQLITE_OPEN_PRIVATECACHE flag, 155

SQLITE_OPEN_SHAREDCACHE flag, 155

SQLITE_PRAGMA event, 181

SQLITE_READ event, 180–181, 187

SQLITE_RESULT value, 151

sqlite_result_xxx( ) functions, 202

SQLITE_ROW value, 129

SQLITE_SCHEMA condition, 178, 189

SQLITE_SCHEMA errors, 162, 178, 192

SQLITE_SCHEMA event, 189

SQLITE_SELECT event, 181, 187

sqlite_sequence table, 95

SQLITE_TRANSACTION event, 182

SQLITE_UPDATE event, 180, 182

sqlite3 command, 244

sqlite3 handle, 126, 128, 132

sqlite3 package, 244

sqlite3 structure, 248

sqlite3_aggregate_context( ) function, 198, 207–208

sqlite3_analyzer tool, 145

sqlite3_bind( ) function, 176

sqlite3_bind_double( ) function, 170

sqlite3_bind_parameter_index( ) function, 173

sqlite3_bind_xxx( ) functions, 167, 170, 202

sqlite3_busy_handler( ) function, 176–177

sqlite3_busy_timeout( ) function, 147, 176–177

sqlite3_changes( ) function, 159

sqlite3_close( ) function, 155, 192, 272

sqlite3_collation_needed( ) function, 216

sqlite3_column_blob( ) function, 167

sqlite3_column_bytes( ) function, 167

sqlite3_column_count( ) function, 165

sqlite3_column_decltype( ) function, 165–166

sqlite3_column_name( ) function, 165

sqlite3_column_origin_name( ) function, 166

sqlite3_column_text( ) method, 272

sqlite3_column_type( ) function, 165

sqlite3_column_xxx( ) functions, 161, 166, 199, 203, 272

sqlite3_commit_hook( ) function, 135, 178–179

sqlite3_complete( ) function, 164

sqlite3_create_collation( ) function, 138

sqlite3_create_collation_v2( ) function, 213

sqlite3_create_function( ) function, 136, 196–198, 205, 213

sqlite3_data_count( ) function, 165

sqlite3_db_handle( ) function, 169

sqlite3_errcode( ) function, 134

sqlite3_errmsg( ) function, 134, 162, 169, 174

sqlite3_exec( ) function, 132, 155–156, 158–159, 161, 164, 189–190, 248

sqlite3_finalize( ) function, 129, 161, 163, 171, 224, 272

sqlite3_free( ) function, 157, 203

sqlite3_free_table( ) function, 159

sqlite3_free(errmsg) function, 157

sqlite3_get_table( ) function, 132–133, 159–161, 164, 189

sqlite3_interrupt( ) function, 189

sqlite3_last_insert_rowid( ) function, 159

sqlite3_malloc( ) function, 159, 202

sqlite3_mprintf( ) function, 134, 171, 203

sqlite3_open( ) function, 128, 154

sqlite3_open_v2( ) function, 154–155, 174, 271

sqlite3_open_v2( ), sqlite3_prepare( ) method, 263

sqlite3_open16( ) function, 154

sqlite3_prepare( ) function, 163–164, 169–170, 172–173, 178, 228

sqlite3_prepare( )_v2 function, 131

sqlite3_prepare_v2( ) function, 129, 131, 161–162, 164, 174, 202, 223

sqlite3_progress_handler( ) function, 189, 237

sqlite3_release_memory( ) function, 193

sqlite3_reset( ) function, 132, 161, 163, 171, 229, 235

sqlite3_result_error( ) function, 203

sqlite3_result_error_nomem( ) function, 208

sqlite3_result_text( ) function, 202–203, 208

sqlite3_result_value( ) function, 203

sqlite3_result_xxx( ) function, 203, 208

sqlite3_rollback_hook( ) function, 135, 179

sqlite3_set_authorizer( ) function, 135, 176, 180, 188, 237

sqlite3_soft_heap_limit( ) function, 193

sqlite3_step( ) function, 129, 161–162, 170–171, 177–178, 192, 223, 228

sqlite3_stmt handle, 127, 129, 227

sqlite3_stmt structure, 161, 170, 233–234, 248

sqlite3_trace( ) function, 178, 201, 237

sqlite3_unlock_notify( ) function, 192

sqlite3_update_hook( ) function, 135, 179

sqlite3_user_data( ) function, 198

sqlite3_value_blob( ) function, 199

sqlite3_value_bytes( ) function, 199

sqlite3_value_text( ) function, 208

sqlite3_value_type( ) function, 199

sqlite3_value_xxx( ) functions, 198–199

sqlite3_vmprintf( ) method, 240

sqlite3_wal_autocheckpoint( ) function, 320–321

sqlite3_wal_checkpoint( ) function, 320–321

sqlite3_wal_hook( ) function, 320

sqlite3BtreeBeginStmt routine, 309

sqlite3BtreeBeginTrans routine, 308

sqlite3BtreeClearTable routine, 309

sqlite3BtreeClose routine, 308

sqlite3BtreeCloseCursor function, 309

sqlite3BtreeCommit routine, 308

sqlite3BtreeCommitStmt routine, 309

sqlite3BtreeCreateTable routine, 309

sqlite3BtreeCursor function, 309

sqlite3BtreeData function, 310

sqlite3BtreeDataSize function, 310

sqlite3BtreeDelete function, 310

sqlite3BtreeDropTable routine, 309

sqlite3BtreeFirst function, 309

sqlite3BtreeGetAutoVacuum function, 310

sqlite3BtreeGetPageSize function, 310

sqlite3BtreeInsert function, 310

sqlite3BtreeKey function, 310

sqlite3BtreeKeySize function, 310

sqlite3BtreeLast function, 309

sqlite3BtreeMoveto function, 309

sqlite3BtreeNext function, 309

sqlite3BtreeOpen routine, 308

sqlite3BtreePrevious function, 309

sqlite3BtreeRollback routine, 309

sqlite3BtreeRollbackStmt routine, 309

sqlite3BtreeSetAutoVacuum function, 310

sqlite3BtreeSetBusyHandler function, 310

sqlite3BtreeSetCacheSize function, 310

sqlite3BtreeSetPageSize function, 310

sqlite3BtreeSetSafetyLevel function, 310

sqlite3.def file, 21, 26

sqlite3.dll file, 21

sqlite3.exe file, 19–20, 30

sqlite3.lib library, 21–22, 28

SQLite.Callback interface, 237, 239

sqliteCreateAggregate( ) method, 251

sqliteCreateFunction( ) method, 251

SQLiteDatabase class, 286–290

convenience methods for, 288–289

executing queries with, 287–288

implementing, 290–292

opening and closing databases with, 286–287

transactions with, 289

useful methods for, 290

SQLiteDatabase::compile( ) method, 238

SQLite.Database.create_function( ) function, 241

SQLiteDatabase.CursorFactory class, 285

SQLite.Database::exec( ) method, 240

SQLite.Database.function_type( ) function, 241

SQLiteDatabase::open( ) method, 238

sqlite.fossil file, 23

SQLite.Function interface, 240–241

sqlite.jar file, 237, 241

SQLite.JDBCDriver class, 241

SQLiteJDBCExample.java file, 242

SQLiteJNIExample.java file, 238

SQLiteman tool, 45

SQLiteManager tool, 46

SQLiteOpenHelper( ) method, 287

SQLiteOpenHelper class, for Android development, 285–286, 290–292

SQLiteQueryBuilder class, for Android development, 293–294

sql.sql file, 49

src folder, 25

stable source distribution, compiling source code on Windows, 22

stackable flags parameter, 287

startup, accessing database upon, 269–272

Statement::bind_params( ) method, 234

Statement.close( ) method, 234

statements, data structures of SQLite, 126–127

Statically linked command-line program, 31

step( ) function, 131, 137, 146–148, 151, 198, 231, 241

storage classes

for SQLite, 101–104

and type affinity, 314

and type conversions, 315–318

str_agg( ) function, 206–207, 209

str_agg_finalize( ) function, 206–208

str_agg_step( ) function, 206–208

strcat( ) function, 206

subqueries, 79–80

subselects, 79

sum( ) function, 67, 137, 195

sum_agg( ) finalize function, 208

Synaptic utility, 31

synchronous pragma, 121–122, 143

syncs( ) function, 310

syntax for SQL in SQLite, 50–53

commands, 51–52

comments, 53

identifiers, 53

keywords, 53

literals, 52

System Variables list, 19

T

t parameter, 266

table functions, for B-tree module API, 309

table parameter, 293

table_info pragma, 121, 242

table_name attribute, 54

table_name variable, 106

table_name.column_name notation, 73

tables

aliases for, 77–79

altering, 54–55

creating, 53–54

joining, 72–77

cross joins, 75–76

inner joins, 74–75

natural joins, 77

outer joins, 76

preferred syntax for, 77

.tables [pattern] command, 37

tables variable, 58

TakeOffGW network installer, 28–29

TakeOffGW package, 28–30

tar -xzvf sqlite-amalgamation-3.6.23.1.tar.gz command, 30

Tcl

language extension for, 243–247

connecting to, 244

installation of, 243–244

query processing with, 244–246

user-defined functions with, 247

parameters in C API, 173–174

Tcl Extension Architecture (TEA), 18

tclsqlite.c file, 26

TEA (Tcl Extension Architecture), 18

temp database, 118

temp keyword, 54

temp_store pragma, 122, 131

temporary keyword, 54

temporary table, 54

test table, 36–38, 40–42, 114

test_idx index, 38

test2 table, 41

test2.db database, 42

test3.db database, 42

test.csv file, 41

test.db database, 35, 42

test.db file, 36, 39, 43

test.sql file, 42–43, 49

text class, 102–103, 196

Text Field elements, 267

threading

with SQLite API, 136

support in C API, 190–193

and memory management, 193

shared cache mode, 190–193

THREADSAFE preprocessor flag, 21

three-value logic, 85

/tmp folder, 33

tokens, 52

Transaction method, 246

transactions, 111–118, 138–149

and conflict resolution, 112–115

and deadlocks, 116–117

and exclusive state, 145

life cycles for, 138–139

lock states, 139–141

and locks, 115–116

read transactions, 141

scope for, 111–112

sizing page cache, 145–146

with SQLiteDatabase class, 289

types of, 117–118

using busy handler, 146–147

write transactions, 141–145

and autocommit, 143–145

exclusive state, 142–143

and page cache, 145

pending state, 142

reserved state, 141–142

triggers, 108–111

error handling with, 110

and updatable views, 110–111

update triggers, 109–110

tristate logic, 85

type affinity, 313–315

and column types, 313

example of, 314–315

and storage classes, 314

type conversions, and storage classes, 315–318

type_id attribute, 48, 65, 68, 71

type_id column, 72, 76, 87–88, 100

type_id group, 68

type_id values, 68, 71–72, 83, 89

typeof( ) function, 102, 317

typeof(b) column, 315

types( ) method, 239

types subquery, 80

U

UIViewController class, 266

unary operators, 60

union all operation, 81

union keyword, 81

union operation, 81

UNION statement, 12

unique constraints, 54, 91–93, 96–97, 106, 112–113, 115

unique keyword, 106

unlock notification, shared cache mode, 192–193

UNLOCKED state, 140–144

UnlockFile( ) function, 148

updatable views, and triggers, 110–111

update command, 91–92, 98, 108, 115, 141–142, 145, 147, 149

update filter, 183

update operation, 135, 179–180

UPDATE statement, 10–12, 110, 112

update triggers, 109–110

update_hook( ) function, in C API, 179–180

update_list column assignment, 91

upper( ) function, 66

UPSERT statement, 10

USB Driver for Windows component, 282

user interface, linking data with, 298–299

User Interfaces group, 266

user-defined functions, with language extensions

for Java, 240–241

for Perl, 224–225

for PHP, 251–252

for Python, 231

for Ruby, 236

for Tcl, 247

utilities, 8

V

vacuum command, 119, 122

VACUUM operation, 189

value argument, 199

value field, 36, 41

value_list variable, 87

values

for Extension C API, 198–199

for where clause, 60

varchar(100) column, 9

VDBE (virtual database engine), 6, 161

Venn diagram, 74

version parameter, 285

views, in SQLite, 104–106

virtual database engine (VDBE), 6, 161

VM (virtual machine), 6–7, 238

VM::step( ) method, 239

void method, 288

W

WAL (Write Ahead Log), 318–322

activation of, 319

advantages and disadvantages of, 320

and checkpoints, 318

and concurrency, 319

configuration of, 320

operational issues with, 321–322

overview, 318

when condition, 83–84

where clause, 59–64

binary operators, 60–62

glob operator, 64

like operator, 63

logical operators, 63

operators, 60

values, 60

where keyword, 57

where predicate, 84

while loop, 149

.width option, 49

Win 32 Application Wizard, 28

Win32 Project template, 27

Windows, 18–30

building dynamically linked SQLite client with Visual C++, 27–28

building SQLite DLL with Microsoft Visual C++, 25–27

building SQLite with MinGW, 28–30

compiling SQLite source code on Windows, 22–25

installing command-line program, 18–21

installing SQLite DLL, 21–22

Windows command shell, 19

windowssystem32 folder, 19

--with-jardir=DIR option, 237

wrapped queries, executing, 132–133

wrapper functions, in C API, 153–160

for connecting and disconnecting, 153–155

exec() function, 155–159

get_table() function, 159–160

Write Ahead Log. See WAL

write transactions, 141–145

and autocommit, 143–145

exclusive state, 142–143

and page cache, 145

pending state, 142

reserved state, 141–142

Write-Ahead Log mode, 192

X

xCallback function, 179

xcode_3.2.4_and_ios_sdk_4.1.dmg file, 254

xCompare argument, 213

xFinal argument, 197, 206

xFunc argument, 197, 206

xNotify function, 192

xStep argument, 197, 206

Y

<yourdb>-shm file, 320

<yourdb>-wal file, 320

Z

zero configuration, features of SQLite, 8

zFunctionName argument, 197

zVfs parameter, 155