$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
abort policy, 113
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
cleaning databases, 119
database file information, 44–45
exporting data, 39
getting schema information, 37–39
importing data, 40
explain query plan command, 123
auto_vacuum, 122
cache size, 120
integrity_check, 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
with language extensions
user-defined, 137
ALTER COLUMN command, 12
alter table statement, 286, 288
ambiguity, of column names, 77
aName variable, 272
AND operator, 61
Android DDMS, 280
Android Developer Tools, 280–281
backup service for databases, 300
additional components, 281–284
downloading Android Developer Tools, 280–281
downloading Android SDK Starter Package, 280
JDK (Java Development Kit), 280
adding database to project, 296
linking data and user interface, 298–299
convenience methods for, 288–289
executing queries with, 287–288
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
configuration functions, 310
cursor functions, 309
record functions, 310
table functions, 309
Appcelerator Titanium framework, 258, 284
applicationDidFinishLaunching event, 276
applicationDidFinishLaunching function, 269
applicationWillTerminate event, 272
apt utility, 31
apt-get utility, 31
compiler, 6
interface, 6
utilities, 8
Arithmetic operators, 62
as keyword, 79
asc sort order, 65
atomic principle, 111
attach command, 118
ATTACH event, 182
attach statement, 184
auto_vacuum pragma, 119, 122, 128
autocommit mode, and write transactions, 143–145
autoincrement column, 95
autoincrement constraint, 95–96
available_drivers( ) function, 222
AVD (Android Virtual Device), 283
avg aggregate, 67
b column, 317
back end, architecture of SQLite, 7–8
BackupAgentHelper API, 300
BackupManager API, 300
Bakery row, 64
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
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 columns, 315
API for
configuration functions, 310
cursor functions, 309
record functions, 310
table functions, 309
data structures of SQLite, 127
and database file format
hierarchical data organization in, 307
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
fetching records with, 164–169
column information for, 165–166
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
prepared queries with, 161–164
execution of, 162
and memory management, 193
for connecting and disconnecting, 153–155
Cache partition size parameter, 284
Cartesian join, 75
cascade action, 101
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
limit clause, 64
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
cnx argument, 197
importance of finalizing, 150–151
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
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
columns( ) method, 239
columns variable, 106
command-line program. See CLP
commands
delete command, 92
explain query plan command, 123
syntax for SQL in SQLite, 51–52
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
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 unique, 93
contacts database, 93
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(*)<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 product, 75
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
data definition language (DDL), 38, 53
and collations, 101
default values, 98
foreign key constraints, 100–101
primary key constraints, 94–97
unique constraints, 93
and collations for columns, 107
storage classes for SQLite, 101–104
error handling with, 110
Data Manipulation Language (DML), 39, 53, 87
data source name (DSN), 248
B-tree and pager, 127
connections and statements, 126–127
database administrator (DBA), 3
database file format, and B-tree module
hierarchical data organization in, 307
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
cleaning, 119
connecting to, 128
importing data into, 40
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 keyword, 98
default values, and domain integrity, 98
default_cache_size pragma, 120
deferrable clause, 101
deferred transaction type, 117
delete operation, 135, 179–180
Delete SQLITE_BUSY value, 146
delete statement, 12, 132, 159, 184
desc sort order, 65
description value, 83
DETACH event, 182
detach statement, 184
details view, 105
Developer Program, membership of, 254
Developer Tools, Android, 280–281
difference operation, 82
disconnect method, 222
distinct clause, 72
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
and collations, 101
default values, 98
foreign key constraints, 100–101
double data type, 241
dpkg utility, 31
DROP COLUMN command, 12
drop table command, 193
drop table statement, 286
DROP VIEW command, 106
dselect utility, 31
DSN (data source name), 248
dylib file, 262
.dylib shared library, 17
Dynamic-link library (DLL), installing on Windows, 21–22, 25–27
.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
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
with triggers, 110
eTextRep argument, 197
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
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
types of, 212
error conditions for, 203
returning input values, 203–204
values for, 202
registering functions in, 196–197
step function for, 198
f parameter, 266
factory parameter, 287
fcntl( ) implementation, 12
compactness, 9
flexibility, 9
liberal licensing, 9
portability, 8
reliability, 10
simplicity, 9
zero configuration, 8
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
fillData( ) method, 298
final( ) function, 198
finalize( ) method, 137, 150–151, 231
find utility, 3
finish( ) method, 224
Fink package management system, 31
flexibility of SQLite, 9
FLOAT class, 196
float data type, 241
FlushFileBuffers( ) function, 143
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
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
full mode, 122
full outer join, 76
FULL OUTER JOIN statement, 12
full synchronous pragma, 121
functions
for B-tree module API
configuration functions, 310
cursor functions, 309
record functions, 310
table functions, 309
error conditions for, 203
returning input values, 203–204
values for, 202
SQL for SQLite, 66
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_concat( ) function, 206
.h command, 34
.h file, 26
Häring, Gerhard, 226
having clause, 71
having predicate, 70
hello_newman( ) method, 136–137, 200, 225, 231, 236, 240–241, 247, 251
-help switch, 34
Hewlett-Packard Unix (HP-UX), 3
hot journal, 145
HP-UX (Hewlett-Packard Unix), 3
.htaccess controls, 248
ICL (imperative chef language), 51
id column, 37, 54, 76, 87, 89, 94–95, 110, 124
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
index_info schema pragma, 121
index_list schema pragma, 121
index_name variable, 106
and collations for columns, 107
.indices [table name] command, 37
.indices shell command, 107
init option, 42
initialValues ContentValue map, 289
initialValues map, 289
initWithName function, 265–266
inline views, 80
insert( ) method, 289
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
Install New Software screen, 280
Installation Type screen, 256
instead of keywords, 110
INSTEAD OF triggers, 12
int data type, 241
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
intersection, 74
inTransaction( ) method, 289
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
FoodViewController class in, 266–268
and large databases, 276
Developer Program membership, 254
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
FoodViewController class in, 266–268
iSeinfeldAppDelegate class, 269
iSeinfeldAppDelegate.h file, 269
iSeinfeldAppDelegate.m file, 269–270, 274
Java, language extension for, 236–243
connecting to, 238
installation of, 237
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
natural joins, 77
outer joins, 76
preferred syntax for, 77
journal_mode PRAGMA, 320
JRE (Java runtime environment), 280
justincase savepoint, 112
keywords, 53
Label elements, 267
connecting to, 238
installation of, 237
query processing with, 238–240
user-defined functions with, 240–241
connecting to, 222
parameters for, 224
query processing with, 222–224
user-defined functions with, 224–225
connecting to, 248
installation of, 248
query processing with, 248–251
user-defined functions with, 251–252
APSW as alternative, 232
connecting to, 227
query processing with, 227–229
user-defined functions with, 231
connecting to, 233
query processing with, 233–234
user-defined functions with, 236
connecting to, 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
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
log table, 109
log_sql( ) function, 201
logical operators, for where clause, 63
lower( ) function, 66
compiling SQLite from source, 31–32
MacPorts package management system, 31
main database, 119
main.xml file, 297
make command, 30
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
Module Definition File property page, 26
multiple connections, using in code, 149–150
myDatabaseAdapter class, 298
myDatabaseHelper class, 294, 296, 300
n parameter, 266
name column, 54–55, 87–88, 93, 98, 101, 109, 115, 123
name table, 104
named parameters, in C API, 173
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
nullColumnHack parameter, 289
nullif function, 86
.nullvalue command, 40
numbered parameters, in C API, 172
ODBC (Open Database Connectivity), 4
off synchronous pragma, 122
offset clause, 64
oid value, 94
on delete restrict option, 100
onCreate( ) method, 285–286, 298
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
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->zResult field, 208
package require directive, 244
packages
page cache, and write transactions, 145–146
page_size pragma, 128
pager, 127
pages, and database file format
reuse of, 304
parameters
named parameters, 173
numbered parameters, 172
for language extensions
for Perl, 224
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
and limitations of SQLite, 11–13
and WAL, 321
Perl, language extension for, 221–226
connecting to, 222
parameters for, 224
query processing with, 222–224
user-defined functions with, 224–225
perlsum( ) aggregate, 225
perlsum.pm package, 225
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
compiling SQLite from source, 31–32
power consumption, 12
ppDb argument, 155
ppStmt parameter, 161
PRAGMA commands, 39
PRAGMA journal_mode=WAL setting, 319
auto_vacuum, 122
cache size, 120
integrity_check, 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
execution of, 162
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
Developer Program membership, 254
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
Python, language extension for, 226–232
APSW as alternative, 232
connecting to, 227
query processing with, 227–229
user-defined functions with, 231
pzTail parameter, 164
queries, with SQLiteDatabase class, 287–288
query( ) method, 239, 248–249, 287–288
executing prepared queries, 129–131
executing wrapped queries, 132–133
queryWithFactory( ) method, 288
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
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
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_hook( ) function, in C API, 179
rootpage column, 123
Row class, 239
ROW_NUMBER( ) function, 13
rowid argument, 180
rows, and insert command
rsync utility, 3
RTRIM collation, 138
Ruby, language extension for, 232–236
connecting to, 233
query processing with, 233–234
user-defined functions with, 236
SAggCtx struct, 208
Samples component, 282
savepoint command, 112
.schema [table name] command, 38
.schema foods shell command, 88
schema information, administration of databases, 37–39
.schema shell command, 55
scope, for transactions, 111–112
SDK Starter Package, prerequisite for Android development, 280
adding database to project, 296
linking data and user interface, 298–299
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
shared cache mode
threading support in C API, 190–193
read-uncommitted isolation level, 191–192
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
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 POSIX systems, 31–32
stable source distribution, 22
Source Code section, 22
spam_mother_in_law( ) function, 147
sprintf( ) function, 134
sql column, 123
SQL for SQLite
cleaning databases, 119
explain query plan command, 123
of pragmas, 122
sqlite_master table, 123
aggregates, 67
and ambiguity of column names, 77
storage classes for SQLite, 101–104
delete command, 92
distinct clause, 72
functions, 66
and collations for columns, 107
natural joins, 77
outer joins, 76
preferred syntax for, 77
limit clause, 64
null in SQLite, 84
comments, 53
identifiers, 53
keywords, 53
literals, 52
and conflict resolution, 112–115
error handling with, 110
glob operator, 64
like operator, 63
logical operators, 63
operators, 60
values, 60
SQLite
for administrators, 3
embedded database, 1
tools for, 45
connecting to database, 128
executing prepared queries, 129–131
executing wrapped queries, 132–133
formatting SQL statements, 134–135
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
using threads, 136
sqlite directory, 33
sqlite_analyzer view, 44
SQLITE_ATTACH event, 182
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_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_sequence table, 95
SQLITE_TRANSACTION event, 182
sqlite3 command, 244
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.dll file, 21
sqlite3.lib library, 21–22, 28
SQLite.Callback interface, 237, 239
sqliteCreateAggregate( ) method, 251
sqliteCreateFunction( ) method, 251
convenience methods for, 288–289
executing queries with, 287–288
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.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
and type affinity, 314
str_agg( ) function, 206–207, 209
str_agg_finalize( ) function, 206–208
str_agg_step( ) function, 206–208
strcat( ) function, 206
subselects, 79
sum_agg( ) finalize function, 208
Synaptic utility, 31
synchronous pragma, 121–122, 143
syncs( ) function, 310
syntax for SQL in SQLite, 50–53
comments, 53
identifiers, 53
keywords, 53
literals, 52
System Variables list, 19
t parameter, 266
table functions, for B-tree module API, 309
table parameter, 293
table_name attribute, 54
table_name variable, 106
table_name.column_name notation, 73
tables
natural joins, 77
outer joins, 76
preferred syntax for, 77
.tables [pattern] command, 37
tables variable, 58
TakeOffGW network installer, 28–29
tar -xzvf sqlite-amalgamation-3.6.23.1.tar.gz command, 30
Tcl
language extension for, 243–247
connecting to, 244
query processing with, 244–246
user-defined functions with, 247
Tcl Extension Architecture (TEA), 18
tclsqlite.c file, 26
TEA (Tcl Extension Architecture), 18
temp database, 118
temp keyword, 54
temporary keyword, 54
temporary table, 54
test_idx index, 38
test2 table, 41
test2.db database, 42
test3.db database, 42
test.csv file, 41
Text Field elements, 267
threading
with SQLite API, 136
and memory management, 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 exclusive state, 145
read transactions, 141
with SQLiteDatabase class, 289
and page cache, 145
pending state, 142
error handling with, 110
tristate logic, 85
and column types, 313
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(b) column, 315
types( ) method, 239
types subquery, 80
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
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_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 Python, 231
for Ruby, 236
for Tcl, 247
utilities, 8
VACUUM operation, 189
value argument, 199
value_list variable, 87
values
for where clause, 60
varchar(100) column, 9
VDBE (virtual database engine), 6, 161
Venn diagram, 74
version parameter, 285
virtual database engine (VDBE), 6, 161
VM (virtual machine), 6–7, 238
VM::step( ) method, 239
void method, 288
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
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
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
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
Write Ahead Log. See WAL
and page cache, 145
pending state, 142
Write-Ahead Log mode, 192
xCallback function, 179
xcode_3.2.4_and_ios_sdk_4.1.dmg file, 254
xCompare argument, 213
xNotify function, 192
<yourdb>-shm file, 320
<yourdb>-wal file, 320
zero configuration, features of SQLite, 8
zFunctionName argument, 197
zVfs parameter, 155