~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

<h1 align="center">PRAGMA Statements</h1>

122

123

124

125

The PRAGMA statement is an SQL extension specific to SQLite and used to

126

modify the operation of the SQLite library or to query the SQLite library for

127

internal (non-table) data. The PRAGMA statement is issued using the same

128

interface as other SQLite commands (e.g. <a href="lang_select.html">SELECT</a>, <a href="lang_insert.html">INSERT</a>) but is

129

different in the following important respects:

130

131

<ul>

132

<li>Specific pragma statements may be removed and others added in future

133

releases of SQLite. There is no guarantee of backwards compatibility.

134

<li>No error messages are generated if an unknown pragma is issued.

135

Unknown pragmas are simply ignored. This means if there is a typo in

136

a pragma statement the library does not inform the user of the fact.

137

<li>Some pragmas take effect during the SQL compilation stage, not the

138

execution stage. This means if using the C-language <a href="c3ref/prepare.html">sqlite3_prepare()</a>,

139

<a href="c3ref/step.html">sqlite3_step()</a>, <a href="c3ref/finalize.html">sqlite3_finalize()</a> API (or similar in a wrapper

140

interface), the pragma may run during the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call,

141

not during the <a href="c3ref/step.html">sqlite3_step()</a> call as normal SQL statements do.

142

Or the pragma might run during sqlite3_step() just like normal

143

SQL statements. Whether or not the pragma runs during sqlite3_prepare()

144

or sqlite3_step() depends on the pragma and on the specific release

145

of SQLite.

146

<li>The pragma command is specific to SQLite and is very unlikely

147

to be compatible with any other SQL database engine.

148

</ul>

149

150

The C-language API for SQLite provides the <a href="c3ref/c_fcntl_chunk_size.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a>

151

<a href="c3ref/file_control.html">file control</a> which gives <a href="vfs.html">VFS</a> implementations the

152

opportunity to add new PRAGMA statements or to override the meaning of

153

built-in PRAGMA statements.

154

155

156

157

<h2>PRAGMA command syntax</h2>

158

<h4><a href="syntaxdiagrams.html#pragma-stmt">pragma-stmt:</a></h4><blockquote> <img alt="syntax diagram pragma-stmt" src="images/syntax/pragma-stmt.gif"></img> </blockquote>

159

<h4><a href="syntaxdiagrams.html#pragma-value">pragma-value:</a></h4><blockquote> <img alt="syntax diagram pragma-value" src="images/syntax/pragma-value.gif"></img> </blockquote>

160

161

162

163

A pragma can take either zero or one argument. The argument is may be either

164

in parentheses or it may be separated from the pragma name by an equal sign.

165

The two syntaxes yield identical results.

166

In many pragmas, the argument is a boolean. The boolean can be one of:

167

168

169

170

1 yes true on 0 no false off

171

</center>

172

173

Keyword arguments can optionally appear in quotes.

174

(Example: <tt>'yes' [FALSE]</tt>.) Some pragmas

175

takes a string literal as their argument. When pragma takes a keyword

176

argument, it will usually also take a numeric equivalent as well.

177

For example, "0" and "no" mean the same thing, as does "1" and "yes".

178

When querying the value of a setting, many pragmas return the number

179

rather than the keyword.

180

181

A pragma may have an optional database name before the pragma name.

182

The database name is the name of an <a href="lang_attach.html">ATTACH</a>-ed database or it can be

183

"main" or "temp" for the main and the TEMP databases. If the optional

184

database name is omitted, "main" is assumed. In some pragmas, the database

185

name is meaningless and is simply ignored.

186

187

188

189

190

<h2>List Of PRAGMAs</h2>

191

192

193

194

<li><a href="#pragma_auto_vacuum">auto_vacuum</a>

195

<li><a href="#pragma_automatic_index">automatic_index</a>

196

<li><a href="#pragma_cache_size">cache_size</a>

197

<li><a href="#pragma_case_sensitive_like">case_sensitive_like</a>

198

<li><a href="#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>

199

<li><a href="#pragma_collation_list">collation_list</a>

200

<li><a href="#pragma_compile_options">compile_options</a>

201

<li><a href="#pragma_count_changes"><s>count_changes</s></a>¹

202

<li><a href="#pragma_data_store_directory"><s>data_store_directory</s></a>¹

203

<li><a href="#pragma_database_list">database_list</a>

204

<li><a href="#pragma_default_cache_size"><s>default_cache_size</s></a>¹

205

<li><a href="#pragma_empty_result_callbacks"><s>empty_result_callbacks</s></a>¹

206

<li><a href="#pragma_encoding">encoding</a>

207

<li><a href="#pragma_foreign_key_list">foreign_key_list</a>

208

<li><a href="#pragma_foreign_keys">foreign_keys</a>

209

<li><a href="#pragma_freelist_count">freelist_count</a>

210

<li><a href="#pragma_full_column_names"><s>full_column_names</s></a>¹

211

</ul></td><td valign="top" align="left"><ul>

212

<li><a href="#pragma_fullfsync">fullfsync</a>

213

<li><a href="#pragma_ignore_check_constraints">ignore_check_constraints</a>

214

<li><a href="#pragma_incremental_vacuum">incremental_vacuum</a>

215

<li><a href="#pragma_index_info">index_info</a>

216

<li><a href="#pragma_index_list">index_list</a>

217

<li><a href="#pragma_integrity_check">integrity_check</a>

218

<li><a href="#pragma_journal_mode">journal_mode</a>

219

<li><a href="#pragma_journal_size_limit">journal_size_limit</a>

220

<li><a href="#pragma_legacy_file_format">legacy_file_format</a>

221

<li><a href="#pragma_locking_mode">locking_mode</a>

222

<li><a href="#pragma_max_page_count">max_page_count</a>

223

<li><a href="#pragma_page_count">page_count</a>

224

225

<li><a href="#pragma_parser_trace">parser_trace</a>²

226

<li><a href="#pragma_quick_check">quick_check</a>

227

<li><a href="#pragma_read_uncommitted">read_uncommitted</a>

228

<li><a href="#pragma_recursive_triggers">recursive_triggers</a>

229

</ul></td><td valign="top" align="left"><ul>

230

<li><a href="#pragma_reverse_unordered_selects">reverse_unordered_selects</a>

231

<li><a href="#pragma_schema_version">schema_version</a>

232

<li><a href="#pragma_secure_delete">secure_delete</a>

233

<li><a href="#pragma_short_column_names"><s>short_column_names</s></a>¹

234

<li><a href="#pragma_shrink_memory">shrink_memory</a>

235

<li><a href="#pragma_synchronous">synchronous</a>

236

<li><a href="#pragma_table_info">table_info</a>

237

<li><a href="#pragma_temp_store">temp_store</a>

238

<li><a href="#pragma_temp_store_directory"><s>temp_store_directory</s></a>¹

239

<li><a href="#pragma_user_version">user_version</a>

240

<li><a href="#pragma_vdbe_listing">vdbe_listing</a>²

241

<li><a href="#pragma_vdbe_trace">vdbe_trace</a>²

242

<li><a href="#pragma_wal_autocheckpoint">wal_autocheckpoint</a>

243

<li><a href="#pragma_wal_checkpoint">wal_checkpoint</a>

244

<li><a href="#pragma_writable_schema">writable_schema</a>

245

246

</ul></td></tr></table>

247

Notes:

248

<ol>

249

<li>Pragmas whose names are marked through in the list above

250

are deprecated that are maintained for historical compatibility only.

251

Do not use the deprecated pragmas in new applications.

252

Remove deprecated pragmas

253

from existing applications at your earliest opportunity.</blockquote>

254

<li>These pragmas are used for debugging SQLite itself and

255

are only available when SQLite is compiled using <a href="compile.html#debug">SQLITE_DEBUG</a>.</ol>

256

257

<hr>

258

PRAGMA auto_vacuum;

259

PRAGMA auto_vacuum =

260

0 | NONE | 1 | FULL | 2 | INCREMENTAL;

261

262

Query or set the auto-vacuum status in the database.

263

264

The default setting for auto-vacuum is 0 or "none",

265

unless the <a href="compile.html#default_autovacuum">SQLITE_DEFAULT_AUTOVACUUM</a> compile-time option is used.

266

The "none" setting means that auto-vacuum is disabled.

267

When auto-vacuum is disabled and data is deleted data from a database,

268

the database file remains the same size. Unused database file

269

pages are added to a "<a href="fileformat2.html#freelist">freelist</a>" and reused for subsequent inserts. So

270

no database file space is lost. However, the database file does not

271

shrink. In this mode the <a href="lang_vacuum.html">VACUUM</a>

272

command can be used to rebuild the entire database file and

273

thus reclaim unused disk space.

274

275

When the auto-vacuum mode is 1 or "full", the freelist pages are

276

moved to the end of the database file and the database file is truncated

277

to remove the freelist pages at every transaction commit.

278

Note, however, that auto-vacuum only truncates the freelist pages

279

from the file. Auto-vacuum does not defragment the database nor

280

repack individual database pages the way that the

281

<a href="lang_vacuum.html">VACUUM</a> command does. In fact, because

282

it moves pages around within the file, auto-vacuum can actually

283

make fragmentation worse.

284

285

Auto-vacuuming is only possible if the database stores some

286

additional information that allows each database page to be

287

traced backwards to its referrer. Therefore, auto-vacuuming must

288

be turned on before any tables are created. It is not possible

289

to enable or disable auto-vacuum after a table has been created.

290

291

When the value of auto-vacuum is 2 or "incremental" then the additional

292

information needed to do auto-vacuuming is stored in the database file

293

but auto-vacuuming does not occur automatically at each commit as it

294

does with auto_vacuum=full. In incremental mode, the separate

295

<a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> pragma must

296

be invoked to cause the auto-vacuum to occur.

297

298

The database connection can be changed between full and incremental

299

autovacuum mode at any time. However, changing from

300

"none" to "full" or "incremental" can only occur when the database

301

is new (no tables

302

have yet been created) or by running the <a href="lang_vacuum.html">VACUUM</a> command. To

303

change auto-vacuum modes, first use the auto_vacuum pragma to set

304

the new desired mode, then invoke the <a href="lang_vacuum.html">VACUUM</a> command to

305

reorganize the entire database file. To change from "full" or

306

"incremental" back to "none" always requires running <a href="lang_vacuum.html">VACUUM</a> even

307

on an empty database.

308

309

310

When the auto_vacuum pragma is invoked with no arguments, it

311

returns the current auto_vacuum mode.

312

313

<hr>

314

PRAGMA automatic_index;

315

PRAGMA automatic_index = boolean;

316

317

Query, set, or clear the <a href="optoverview.html#autoindex">automatic indexing</a> capability.

318

<a href="optoverview.html#autoindex">Automatic indexing</a> is enabled by default.

319

320

<hr>

321

PRAGMA cache_size;

322

PRAGMA cache_size = pages;

323

PRAGMA cache_size = -kibibytes;

324

Query or change the suggested maximum number of database disk pages

325

that SQLite will hold in memory at once per open database file. Whether

326

or not this suggestion is honored is at the discretion of the

327

<a href="c3ref/pcache_methods2.html">Application Defined Page Cache</a>.

328

The default page cache that is built into SQLite honors the request,

329

however alternative application-defined page cache implementations

330

may choose to interpret the suggested cache size in different ways

331

or to ignore it all together.

332

The default suggested cache size is 2000 pages.

333

334

If the argument N is positive then the suggested cache size is set

335

to N. If the argument N is negative, then the

336

number of cache pages is adjusted to use approximately N*1024 bytes

337

of memory.

338

339

When you change the cache size using the cache_size pragma, the

340

change only endures for the current session. The cache size reverts

341

to the default value when the database is closed and reopened.

342

343

<hr>

344

PRAGMA case_sensitive_like = boolean;

345

The default behavior of the <a href="lang_expr.html#like">LIKE</a> operator is to ignore case

346

for ASCII characters. Hence, by default 'a' LIKE 'A' is

347

true. The case_sensitive_like pragma installs a new application-defined

348

LIKE function that is either case sensitive or insensitive depending

349

on the value of the case_sensitive_like pragma.

350

When case_sensitive_like is disabled, the default LIKE behavior is

351

expressed. When case_sensitive_like is enabled, case becomes

352

significant. So, for example,

353

'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.

354

355

This pragma uses <a href="c3ref/create_function.html">sqlite3_create_function()</a> to overload the

356

LIKE and GLOB functions, which may override previous implementations

357

of LIKE and GLOB registered by the application.

358

359

<hr>

360

PRAGMA checkpoint_fullfsync

361

PRAGMA checkpoint_fullfsync = boolean;

362

Query or change the fullfsync flag for <a href="wal.html#ckpt">checkpoint</a> operations.

363

If this flag is set, then the F_FULLFSYNC syncing method is used

364

during checkpoint operations on systems that support F_FULLFSYNC.

365

The default value of the checkpoint_fullfsync flag

366

is off. Only Mac OS-X supports F_FULLFSYNC.

367

368

If the <a href="pragma.html#pragma_fullfsync">fullfsync</a> flag is set, then the F_FULLFSYNC syncing

369

method is used for all sync operations and the checkpoint_fullfsync

370

setting is irrelevant.

371

372

<hr>

373

PRAGMA collation_list;

374

Return a list of the collating sequences defined for the current

375

database connection.

376

377

<hr>

378

PRAGMA compile_options;

379

This pragma returns the names of <a href="compile.html">compile-time options</a> used when

380

building SQLite, one option per row. The "SQLITE_" prefix is omitted

381

from the returned option names. See also the

382

<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ interface and the

383

<a href="lang_corefunc.html#sqlite_compileoption_get">sqlite_compileoption_get()</a> SQL functions.

384

385

<hr>

386

PRAGMA count_changes;

387

PRAGMA count_changes = boolean;

388

389

390

Do not use this pragma! This pragma is deprecated and exists

391

for backwards compatibility only. New applications

392

should avoid using this pragma. Older applications should discontinue

393

use of this pragma at the earliest opportunity. This pragma may be omitted

394

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

395

396

397

398

Query or change the count-changes flag. Normally, when the

399

count-changes flag is not set, <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements

400

return no data. When count-changes is set, each of these commands

401

returns a single row of data consisting of one integer value - the

402

number of rows inserted, modified or deleted by the command. The

403

returned change count does not include any insertions, modifications

404

or deletions performed by triggers, or any changes made automatically

405

by <a href="foreignkeys.html#fk_actions">foreign key actions</a>.

406

407

Another way to get the row change counts is to use the

408

<a href="c3ref/changes.html">sqlite3_changes()</a> or <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces.

409

There is a subtle different, though. When an INSERT, UPDATE, or

410

DELETE is run against a view using an <a href="lang_createtrigger.html#instead_of_trigger">INSTEAD OF trigger</a>,

411

the count_changes pragma reports the number of rows in the view

412

that fired the trigger, whereas <a href="c3ref/changes.html">sqlite3_changes()</a> and

413

<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> do not.

414

415

<hr>

416

PRAGMA data_store_directory;

417

PRAGMA data_store_directory = 'directory-name';

418

Query or change the value of the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global

419

variable, which windows operating-system interface backends use to

420

determine where to store database files specified using a relative

421

pathname.

422

423

424

Do not use this pragma! This pragma is deprecated and exists

425

for backwards compatibility only. New applications

426

should avoid using this pragma. Older applications should discontinue

427

use of this pragma at the earliest opportunity. This pragma may be omitted

428

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

429

430

431

432

Changing the data_store_directory setting is not threadsafe.

433

Never change the data_store_directory setting if another thread

434

within the application is running any SQLite interface at the same time.

435

Doing so results in undefined behavior. Changing the data_store_directory

436

setting writes to the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global

437

variable and that global variable is not protected by a mutex.

438

439

This facility is provided for WinRT which does not have an OS

440

mechanism for reading or changing the current working directory.

441

The use of this pragma in any other context is discouraged and may

442

be disallowed in future releases.

443

444

<hr>

445

PRAGMA database_list;

446

This pragma works like a query to return one row for each database

447

attached to the current database connection.

448

The second column is the "main" for the main database file, "temp"

449

for the database file used to store TEMP objects, or the name of the

450

ATTACHed database for other database files.

451

The third column is the name of the database file itself, or an empty

452

string if the database is not associated with a file.

453

454

<hr>

455

PRAGMA default_cache_size;

456

PRAGMA default_cache_size = Number-of-pages;

457

458

This pragma queries or sets the suggested maximum number of pages

459

of disk cache that will be allocated per open database file.

460

The difference between this pragma and <a href="pragma.html#pragma_cache_size">cache_size</a> is that the

461

value set here persists across database connections.

462

The value of the default cache size is stored in the 4-byte

463

big-endian integer located at offset 48 in the header of the

464

database file.

465

466

467

468

Do not use this pragma! This pragma is deprecated and exists

469

for backwards compatibility only. New applications

470

should avoid using this pragma. Older applications should discontinue

471

use of this pragma at the earliest opportunity. This pragma may be omitted

472

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

473

474

475

476

<hr>

477

PRAGMA empty_result_callbacks;

478

PRAGMA empty_result_callbacks = boolean;

479

480

481

Do not use this pragma! This pragma is deprecated and exists

482

for backwards compatibility only. New applications

483

should avoid using this pragma. Older applications should discontinue

484

use of this pragma at the earliest opportunity. This pragma may be omitted

485

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

486

487

488

489

Query or change the empty-result-callbacks flag.

490

491

The empty-result-callbacks flag affects the <a href="c3ref/exec.html">sqlite3_exec()</a> API only.

492

Normally, when the empty-result-callbacks flag is cleared, the

493

callback function supplied to the <a href="c3ref/exec.html">sqlite3_exec()</a> is not invoked

494

for commands that return zero rows of data. When empty-result-callbacks

495

is set in this situation, the callback function is invoked exactly once,

496

with the third parameter set to 0 (NULL). This is to enable programs

497

that use the <a href="c3ref/exec.html">sqlite3_exec()</a> API to retrieve column-names even when

498

a query returns no data.

499

500

<hr>

501

PRAGMA encoding;

502

PRAGMA encoding = "UTF-8";

503

PRAGMA encoding = "UTF-16";

504

PRAGMA encoding = "UTF-16le";

505

PRAGMA encoding = "UTF-16be";

506

In first form, if the main database has already been

507

created, then this pragma returns the text encoding used by the

508

main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16

509

encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main

510

database has not already been created, then the value returned is the

511

text encoding that will be used to create the main database, if

512

it is created by this session.

513

514

The second through fifth forms of this pragma

515

set the encoding that the main database will be created with if

516

it is created by this session. The string "UTF-16" is interpreted

517

as "UTF-16 encoding using native machine byte-ordering". It is not

518

possible to change the text encoding of a database after it has been

519

created and any attempt to do so will be silently ignored.

520

521

Once an encoding has been set for a database, it cannot be changed.

522

523

Databases created by the <a href="lang_attach.html">ATTACH</a> command always use the same encoding

524

as the main database. An attempt to <a href="lang_attach.html">ATTACH</a> a database with a different

525

text encoding from the "main" database will fail.

526

527

<hr>

528

PRAGMA foreign_key_list(table-name);

529

530

This pragma returns one row for each foreign key that references

531

a column in the argument table.

532

533

<hr>

534

PRAGMA foreign_keys;

535

PRAGMA foreign_keys = boolean;

536

Query, set, or clear the enforcement of <a href="foreignkeys.html">foreign key constraints</a>.

537

538

This pragma is a no-op within a transaction; foreign key constraint

539

enforcement may only be enabled or disabled when there is no pending

540

<a href="lang_transaction.html">BEGIN</a> or <a href="lang_savepoint.html">SAVEPOINT</a>.

541

542

Changing the foreign_keys setting affects the execution of

543

all statements prepared

544

using the database connection, including those prepared before the

545

setting was changed. Any existing statements prepared using the legacy

546

<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="c3ref/c_abort.html">SQLITE_SCHEMA</a> error

547

after the foreign_keys setting is changed.

548

549

As of SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a>, the default setting for foreign

550

key enforcement is OFF. However, that might change in a future

551

release of SQLite. The default setting for foreign key enforcement

552

can be specified at compile-time using the <a href="compile.html#default_foreign_keys">SQLITE_DEFAULT_FOREIGN_KEYS</a>

553

preprocessor macro. To minimize future problems, applications should

554

set the foreign key enforcement flag as required by the application

555

and not depend on the default setting.

556

557

<hr>

558

PRAGMA freelist_count;

559

Return the number of unused pages in the database file.

560

561

<hr>

562

PRAGMA full_column_names;

563

PRAGMA full_column_names = boolean;

564

565

566

Do not use this pragma! This pragma is deprecated and exists

567

for backwards compatibility only. New applications

568

should avoid using this pragma. Older applications should discontinue

569

use of this pragma at the earliest opportunity. This pragma may be omitted

570

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

571

572

573

574

Query or change the full_column_names flag. This flag together

575

with the <a href="pragma.html#pragma_short_column_names">short_column_names</a> flag determine

576

the way SQLite assigns names to result columns of <a href="lang_select.html">SELECT</a> statements.

577

Result columns are named by applying the following rules in order:

578

<ol>

579

<li>If there is an AS clause on the result, then the name of

580

the column is the right-hand side of the AS clause.</li>

581

<li>If the result is a general expression, not a just the name of

582

a source table column,

583

then the name of the result is a copy of the expression text.</li>

584

<li>If the <a href="pragma.html#pragma_short_column_names">short_column_names</a> pragma is ON, then the name of the

585

result is the name of the source table column without the

586

source table name prefix: COLUMN.</li>

587

<li>If both pragmas <a href="pragma.html#pragma_short_column_names">short_column_names</a> and <a href="pragma.html#pragma_full_column_names">full_column_names</a>

588

are OFF then case (2) applies.

589

</li>

590

<li>The name of the result column is a combination of the source table

591

and source column name: TABLE.COLUMN</li>

592

</ol>

593

594

<hr>

595

PRAGMA fullfsync

596

PRAGMA fullfsync = boolean;

597

Query or change the fullfsync flag. This flag

598

determines whether or not the F_FULLFSYNC syncing method is used

599

on systems that support it. The default value of the fullfsync flag

600

is off. Only Mac OS X supports F_FULLFSYNC.

601

602

See also <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>.

603

604

<hr>

605

PRAGMA ignore_check_constraints = boolean;

606

607

This pragma enables or disables the enforcement of CHECK constraints.

608

The default setting is off, meaning that CHECK constraints are

609

enforced by default.

610

611

<hr>

612

PRAGMA incremental_vacuum(N);

613

The incremental_vacuum pragma causes up to N pages to

614

be removed from the <a href="fileformat2.html#freelist">freelist</a>. The database file is truncated by

615

the same amount. The incremental_vacuum pragma has no effect if

616

the database is not in

617

<a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode

618

or if there are no pages on the freelist. If there are fewer than

619

N pages on the freelist, or if N is less than 1, or

620

if N is omitted entirely, then the entire freelist is cleared.

621

622

<hr>

623

PRAGMA index_info(index-name);

624

This pragma returns one row each column in the named index.

625

The first column of the result is the rank of the column within the index.

626

The second column of the result is the rank of the column within the

627

table. The third column of output is the name of the column being indexed.

628

629

630

<hr>

631

PRAGMA index_list(table-name);

632

This pragma returns one row for each index associated with the

633

given table. Columns of the result set include the

634

index name and a flag to indicate whether or not the index is UNIQUE.

635

636

637

<hr>

638

PRAGMA integrity_check;

639

PRAGMA integrity_check(integer)

640

This pragma does an integrity check of the entire database. It

641

looks for out-of-order records, missing pages, malformed records, and

642

corrupt indices.

643

If any problems are found, then strings are returned (as multiple

644

rows with a single column per row) which describe

645

the problems. At most integer errors will be reported

646

before the analysis quits. The default value for integer

647

is 100. If no errors are found, a single row with the value "ok" is

648

returned.

649

650

<hr>

651

PRAGMA journal_mode;

652

PRAGMA database.journal_mode;

653

PRAGMA journal_mode

654

655

PRAGMA database.journal_mode

656

657

658

This pragma queries or sets the journal mode for databases

659

associated with the current <a href="c3ref/sqlite3.html">database connection</a>.

660

661

The first two forms of this pragma query the current journaling

662

mode for database. When database is omitted, the

663

"main" database is queried.

664

665

The last two forms change the journaling mode. The 4th form

666

changes the journaling mode for a specific database connection named.

667

Use "main" for the main database (the database that was opened by

668

the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or

669

<a href="c3ref/open.html">sqlite3_open_v2()</a> interface call) and use "temp" for database

670

that holds TEMP tables. The 3rd form changes the journaling mode

671

on all databases attached to the connection.

672

The new journal mode is returned. If the journal mode

673

could not be changed, the original journal mode is returned.

674

675

The DELETE journaling mode is the normal behavior. In the DELETE

676

mode, the rollback journal is deleted at the conclusion of each

677

transaction. Indeed, the delete operation is the action that causes

678

the transaction to commit.

679

(See the documented titled <a href="atomiccommit.html">

680

Atomic Commit In SQLite</a> for additional detail.)

681

682

The TRUNCATE journaling mode commits transactions by truncating

683

the rollback journal to zero-length instead of deleting it. On many

684

systems, truncating a file is much faster than deleting the file since

685

the containing directory does not need to be changed.

686

687

The PERSIST journaling mode prevents the rollback journal from

688

being deleted at the end of each transaction. Instead, the header

689

of the journal is overwritten with zeros. This will prevent other

690

database connections from rolling the journal back. The PERSIST

691

journaling mode is useful as an optimization on platforms where

692

deleting or truncating a file is much more expensive than overwriting

693

the first block of a file with zeros. See also:

694

<a href="pragma.html#pragma_journal_size_limit">PRAGMA journal_size_limit</a> and <a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a>.

695

696

The MEMORY journaling mode stores the rollback journal in

697

volatile RAM. This saves disk I/O but at the expense of database

698

safety and integrity. If the application using SQLite crashes in

699

the middle of a transaction when the MEMORY journaling mode is set,

700

then the database file will very likely go corrupt.

701

702

The WAL journaling mode uses a <a href="wal.html">write-ahead log</a> instead of a

703

rollback journal to implement transactions. The WAL journaling mode

704

is persistent; after being set it stays in effect

705

across multiple database connections and after closing and

706

reopening the database. A database in WAL journaling mode

707

can only be accessed by SQLite version 3.7.0 or later.

708

709

The OFF journaling mode disables the rollback journal completely.

710

No rollback journal is ever created and hence there is never a rollback

711

journal to delete. The OFF journaling mode disables the atomic

712

commit and rollback capabilities of SQLite. The <a href="lang_transaction.html">ROLLBACK</a> command

713

no longer works; it behaves in an undefined way. Applications must

714

avoid using the <a href="lang_transaction.html">ROLLBACK</a> command when the journal mode is OFF.

715

If the application crashes

716

in the middle of a transaction when the OFF journaling mode is

717

set, then the database file will very likely go corrupt.

718

719

Note that the journal_mode for an <a href="inmemorydb.html">in-memory database</a>

720

is either MEMORY or OFF and can not be changed to a different value.

721

An attempt to change the journal_mode of an <a href="inmemorydb.html">in-memory database</a> to

722

any setting other than MEMORY or OFF is ignored. Note also that

723

the journal_mode cannot be changed while a transaction is active.

724

725

<hr>

726

727

PRAGMA journal_size_limit

728

PRAGMA journal_size_limit = N ;

729

730

If a database connection is operating in

731

<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> or in

732

<a href="pragma.html#pragma_journal_mode">persistent journal mode</a>

733

(PRAGMA journal_mode=persist) then

734

after committing a transaction the <a href="lockingv3.html#rollback">rollback journal</a> file may remain in

735

the file-system. This increases performance for subsequent transactions

736

since overwriting an existing file is faster than append to a file,

737

but it also consumes

738

file-system space. After a large transaction (e.g. a <a href="lang_vacuum.html">VACUUM</a>),

739

the rollback journal file may consume a very large amount of space.

740

741

Similarly, in <a href="wal.html">WAL mode</a>, the write-ahead log file is not truncated

742

following a <a href="wal.html#ckpt">checkpoint</a>. Instead, SQLite reuses the existing file

743

for subsequent WAL entries since overwriting is faster than appending.

744

745

The journal_size_limit pragma may be used to limit the size of

746

rollback-journal and WAL files left

747

in the file-system after transactions or checkpoints.

748

Each time a transaction is committed or a WAL file resets, SQLite

749

compares the size of the rollback journal file or WAL file left in

750

the file-system to the size limit

751

set by this pragma and if the journal or WAL file is larger

752

it is truncated to the limit.

753

754

The second form of the pragma listed above is used to set a new limit

755

in bytes for the specified database. A negative number implies no limit.

756

To always truncate rollback journals and WAL files to their minimum size,

757

set the journal_size_limit to zero.

758

Both the first and second forms of the pragma listed above return a single

759

result row containing a single integer column - the value of the journal

760

size limit in bytes. The default journal size limit is -1 (no limit). The

761

<a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a> preprocessor macro can be used to change

762

the default journal size limit at compile-time.

763

764

This pragma only operates on the single database specified prior

765

to the pragma name (or on the "main" database if no database is specified.)

766

There is no way to change the journal size limit on all attached databases

767

using a single PRAGMA statement. The size limit must be set separately for

768

each attached database.

769

770

<hr>

771

PRAGMA legacy_file_format;

772

PRAGMA legacy_file_format = boolean

773

This pragma sets or queries the value of the legacy_file_format

774

flag. When this flag is on, new SQLite databases are created in

775

a file format that is readable and writable by all versions of

776

SQLite going back to 3.0.0. When the flag is off, new databases

777

are created using the latest file format which might not be

778

readable or writable by versions of SQLite prior to 3.3.0.

779

780

When the legacy_file_format pragma is issued with no argument,

781

it returns the setting of the flag. This pragma does not tell

782

which file format the current database is using; it tells what format

783

will be used by any newly created databases.

784

785

The legacy_file_format pragma is initialized to OFF when an existing

786

database in the newer file format is first opened.

787

788

The default file format is set by the

789

<a href="compile.html#default_file_format">SQLITE_DEFAULT_FILE_FORMAT</a> compile-time option.

790

791

<hr>

792

PRAGMA locking_mode;

793

PRAGMA locking_mode = NORMAL | EXCLUSIVE

794

This pragma sets or queries the database connection locking-mode.

795

The locking-mode is either NORMAL or EXCLUSIVE.

796

797

In NORMAL locking-mode (the default unless overridden at compile-time

798

using <a href="compile.html#default_locking_mode">SQLITE_DEFAULT_LOCKING_MODE</a>), a database connection

799

unlocks the database file at the conclusion of each read or

800

write transaction. When the locking-mode is set to EXCLUSIVE, the

801

database connection never releases file-locks. The first time the

802

database is read in EXCLUSIVE mode, a shared lock is obtained and

803

held. The first time the database is written, an exclusive lock is

804

obtained and held.

805

806

Database locks obtained by a connection in EXCLUSIVE mode may be

807

released either by closing the database connection, or by setting the

808

locking-mode back to NORMAL using this pragma and then accessing the

809

database file (for read or write). Simply setting the locking-mode to

810

NORMAL is not enough - locks are not be released until the next time

811

the database file is accessed.

812

813

There are three reasons to set the locking-mode to EXCLUSIVE.

814

<ol>

815

<li>The application wants to prevent other processes from

816

accessing the database file.

817

<li>The number of system calls for filesystem operations is reduced,

818

possibly resulting in a small performance increase.

819

<li><a href="wal.html">WAL</a> databases can be accessed in EXCLUSIVE mode without the

820

use of shared memory.

821

(<a href="wal.html#noshm">Additional information</a>)

822

</ol>

823

824

825

When the locking_mode pragma specifies a particular database,

826

for example:

827

828

829

PRAGMA main.locking_mode=EXCLUSIVE;

830

</blockquote>

831

832

Then the locking mode applies only to the named database. If no

833

database name qualifier precedes the "locking_mode" keyword then

834

the locking mode is applied to all databases, including any new

835

databases added by subsequent <a href="lang_attach.html">ATTACH</a> commands.

836

837

The "temp" database (in which TEMP tables and indices are stored)

838

and <a href="inmemorydb.html">in-memory databases</a>

839

always uses exclusive locking mode. The locking mode of temp and

840

<a href="inmemorydb.html">in-memory databases</a> cannot

841

be changed. All other databases use the normal locking mode by default

842

and are affected by this pragma.

843

844

If the locking mode is EXCLUSIVE when first entering

845

<a href="wal.html">WAL journal mode</a>, then the locking mode cannot be changed to

846

NORMAL until after exiting WAL journal mode.

847

If the locking mode is NORMAL when first entering WAL

848

journal mode, then the locking mode can be changed between NORMAL and

849

EXCLUSIVE and back again at any time and without needing to exit

850

WAL journal mode.

851

852

<hr>

853

PRAGMA max_page_count;

854

PRAGMA max_page_count = N;

855

Query or set the maximum number of pages in the database file.

856

Both forms of the pragma return the maximum page count. The second

857

form attempts to modify the maximum page count. The maximum page

858

count cannot be reduced below the current database size.

859

860

861

<hr>

862

PRAGMA page_count;

863

Return the total number of pages in the database file.

864

865

<hr>

866

PRAGMA page_size;

867

PRAGMA page_size = bytes;

868

Query or set the page size of the database. The page

869

size must be a power of two between 512 and 65536 inclusive.

870

871

872

When a new database is created, SQLite assigned a default page size

873

based on information received from the xSectorSize and

874

xDeviceCharacteristics methods of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object

875

of the newly created database file. The page_size pragma will only

876

cause an immediate change in the

877

page size if it is issued while the database is still empty, prior

878

to the first CREATE TABLE statement. If the page_size pragma is

879

used to specify a new page size just prior to

880

running the <a href="lang_vacuum.html">VACUUM</a> command and if the database is not in

881

<a href="wal.html">WAL journal mode</a> then <a href="lang_vacuum.html">VACUUM</a> will change the page

882

size to the new value.

883

884

If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,

885

then the default page size is chosen to be the largest page size

886

less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic

887

write is enabled according to the

888

xDeviceCharacteristics method of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object for

889

the database file. If the SQLITE_ENABLE_ATOMIC_WRITE option is

890

disabled or if xDeviceCharacteristics reports no suitable atomic

891

write page sizes, then the default page size is the larger of

892

SQLITE_DEFAULT_PAGE_SIZE

893

and the sector size as reported by the xSectorSize method of the

894

<a href="c3ref/io_methods.html">sqlite3_io_methods</a> object, but not more than

895

SQLITE_MAX_DEFAULT_PAGE_SIZE. The normal configuration for SQLite

896

running on workstations is for atomic write to be

897

disabled, for the maximum page size to be set to 65536, for

898

SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the

899

maximum default page size to be set to 8192. The default xSectorSize

900

method on unix workstation implementations always reports a sector size

901

of 512 bytes. Hence,

902

the default page size chosen by SQLite on unix is usually 1024 bytes.

903

On windows, the GetDiskFreeSpace() interface is used to obtain the

904

actual device sector size and hence the default page size on windows

905

will sometimes be greater than 1024.

906

907

<hr>

908

PRAGMA parser_trace = boolean;

909

910

911

This pragma is intended for use when debugging SQLite itself. It

912

is only contained in the build when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option

913

is used.

914

915

916

If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time

917

option, then the parser_trace pragma can be used to turn on tracing

918

for the SQL parser used internally by SQLite.

919

This feature is used for debugging SQLite itself.

920

921

<hr>

922

PRAGMA quick_check;

923

PRAGMA quick_check(integer)

924

The pragma is like <a href="pragma.html#pragma_integrity_check">integrity_check</a> except that it does not verify

925

that index content matches table content. By skipping the verification

926

of index content, quick_check is able to run much faster than

927

integrity_check. Otherwise the two pragmas are the same.

928

929

930

<hr>

931

PRAGMA read_uncommitted;

932

PRAGMA read_uncommitted = boolean;

933

Query, set, or clear READ UNCOMMITTED isolation. The default isolation

934

level for SQLite is SERIALIZABLE. Any process or thread can select

935

READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except

936

between connections that share a common page and schema cache.

937

Cache sharing is enabled using the <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> API.

938

Cache sharing is disabled by default.

939

940

941

See <a href="sharedcache.html">SQLite Shared-Cache Mode</a> for additional information.

942

943

<hr>

944

PRAGMA recursive_triggers;

945

PRAGMA recursive_triggers = boolean;

946

Query, set, or clear the recursive trigger capability.

947

948

Changing the recursive_triggers setting affects the execution of

949

all statements prepared

950

using the database connection, including those prepared before the

951

setting was changed. Any existing statements prepared using the legacy

952

<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="c3ref/c_abort.html">SQLITE_SCHEMA</a> error

953

after the recursive_triggers setting is changed.

954

955

Prior to SQLite version 3.6.18, recursive triggers were not

956

supported. The behavior of SQLite was always as if this pragma was

957

set to OFF. Support for recursive triggers was added in version 3.6.18

958

but was initially turned OFF by default, for compatibility. Recursive

959

triggers may be turned on by default in future versions of SQLite.

960

961

962

The depth of recursion for triggers has a hard upper limit set by

963

the <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> compile-time option and a run-time

964

limit set by <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimittriggerdepth">SQLITE_LIMIT_TRIGGER_DEPTH</a>,...).

965

966

<hr>

967

PRAGMA reverse_unordered_selects;

968

PRAGMA reverse_unordered_selects = boolean;

969

When enabled, this PRAGMA causes <a href="lang_select.html">SELECT</a> statements without

970

an ORDER BY clause to emit their results in the reverse order of what

971

they normally would. This can help debug applications that are

972

making invalid assumptions about the result order.SQLite makes no

973

guarantees about the order of results if a SELECT omits the ORDER BY

974

clause. Even so, the order of results does not change from one

975

run to the next, and so many applications mistakenly come to depend

976

on the arbitrary output order whatever that order happens to be. However,

977

sometimes new versions of SQLite will contain optimizer enhancements

978

that will cause the output order of queries without ORDER BY clauses

979

to shift. When that happens, applications that depend on a certain

980

output order might malfunction. By running the application multiple

981

times with this pragma both disabled and enabled, cases where the

982

application makes faulty assumptions about output order can be

983

identified and fixed early, reducing problems

984

that might be caused by linking against a different version of SQLite.

985

986

987

<hr>

988

PRAGMA schema_version;

989

PRAGMA schema_version = integer ;

990

PRAGMA user_version;

991

PRAGMA user_version = integer ;

992

993

994

The pragmas schema_version and user_version are used to set or get

995

the value of the schema-version and user-version, respectively. The

996

schema-version and the user-version are big-endian 32-bit signed

997

integers stored in the database header at offsets 40 and 60,

998

respectively.

999

1000

The schema-version is usually only manipulated internally by SQLite.

1001

It is incremented by SQLite whenever the database schema is modified

1002

(by creating or dropping a table or index). The schema version is

1003

used by SQLite each time a query is executed to ensure that the

1004

internal cache of the schema used when compiling the SQL query matches

1005

the schema of the database against which the compiled query is actually

1006

executed. Subverting this mechanism by using "PRAGMA schema_version"

1007

to modify the schema-version is potentially dangerous and may lead

1008

to program crashes or database corruption. Use with caution!

1009

1010

The user-version is not used internally by SQLite. It may be used by

1011

applications for any purpose.

1012

1013

<hr>

1014

PRAGMA secure_delete;

1015

PRAGMA database.secure_delete;

1016

PRAGMA secure_delete = boolean

1017

PRAGMA database.secure_delete =

1018

boolean

1019

Query or change the secure-delete setting. When secure-delete

1020

on, SQLite overwrites deleted content with zeros. The default

1021

setting is determined by the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a>

1022

compile-time option.

1023

1024

1025

When there are <a href="lang_attach.html">attached databases</a> and no database

1026

is specified in the pragma, all databases have their secure-delete

1027

setting altered.

1028

The secure-delete setting for newly attached databases is the setting

1029

of the main database at the time the ATTACH command is evaluated.

1030

1031

1032

When multiple database connections share the same cache, changing

1033

the secure-delete flag on one database connection changes it for them

1034

all.

1035

1036

1037

<hr>

1038

PRAGMA short_column_names;

1039

PRAGMA short_column_names = boolean;

1040

1041

1042

Do not use this pragma! This pragma is deprecated and exists

1043

for backwards compatibility only. New applications

1044

should avoid using this pragma. Older applications should discontinue

1045

use of this pragma at the earliest opportunity. This pragma may be omitted

1046

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

1047

1048

1049

1050

Query or change the short-column-names flag. This flag affects

1051

the way SQLite names columns of data returned by <a href="lang_select.html">SELECT</a> statements.

1052

See the <a href="pragma.html#pragma_full_column_names">full_column_names</a> pragma for full details.

1053

1054

1055

<hr>

1056

PRAGMA shrink_memory

1057

1058

This pragma causes the database connection on which it is invoked

1059

to free up as much memory as it can, by calling

1060

<a href="c3ref/db_release_memory.html">sqlite3_db_release_memory()</a>.

1061

1062

1063

<hr>

1064

PRAGMA synchronous;

1065

PRAGMA synchronous =

1066

0 | OFF | 1 | NORMAL | 2 | FULL;

1067

1068

Query or change the setting of the "synchronous" flag.

1069

The first (query) form will return the synchronous setting as an

1070

integer. When synchronous is FULL (2), the SQLite database engine will

1071

use the xSync method of the <a href="vfs.html">VFS</a> to ensure that all content is safely

1072

written to the disk surface prior to continuing.

1073

This ensures that an operating system crash or power failure will

1074

not corrupt the database.

1075

FULL synchronous is very safe, but it is also slower.

1076

When synchronous is NORMAL (1), the SQLite database

1077

engine will still sync at the most critical moments, but less often

1078

than in FULL mode. There is a very small (though non-zero) chance that

1079

a power failure at just the wrong time could corrupt the database in

1080

NORMAL mode. But in practice, you are more likely to suffer

1081

a catastrophic disk failure or some other unrecoverable hardware

1082

fault.

1083

With synchronous OFF (0), SQLite continues without syncing

1084

as soon as it has handed data off to the operating system.

1085

If the application running SQLite crashes, the data will be safe, but

1086

the database might become corrupted if the operating system

1087

crashes or the computer loses power before that data has been written

1088

to the disk surface. On the other hand, some

1089

operations are as much as 50 or more times faster with synchronous OFF.

1090

1091

1092

In <a href="wal.html">WAL</a> mode when synchronous is NORMAL (1), the WAL file is

1093

synchronized before each <a href="wal.html#ckpt">checkpoint</a> and the database file is

1094

synchronized after each completed <a href="wal.html#ckpt">checkpoint</a> and the WAL file

1095

header is synchronized when a WAL file begins to be reused after

1096

a checkpoint, but no sync operations occur during most transactions.

1097

With synchronous=FULL in WAL mode, an additional

1098

sync operation of the WAL file happens after each transaction commit.

1099

The extra WAL sync following each transaction help ensure that

1100

transactions are durable across a power loss, but they do not aid

1101

in preserving consistency.

1102

If durability is not a concern, then synchronous=NORMAL is normally

1103

all one needs in WAL mode.

1104

1105

The default setting is synchronous=FULL.

1106

1107

See also the <a href="pragma.html#pragma_fullfsync">fullfsync</a> and <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a> pragmas.

1108

1109

<hr>

1110

PRAGMA table_info(table-name);

1111

This pragma returns one row for each column in the named table.

1112

Columns in the result set include the column name,

1113

data type, whether or not the column can be NULL, and the default

1114

value for the column.

1115

1116

<hr>

1117

PRAGMA temp_store;

1118

PRAGMA temp_store =

1119

0 | DEFAULT | 1 | FILE | 2 | MEMORY;

1120

1121

Query or change the setting of the "temp_store" parameter.

1122

When temp_store is DEFAULT (0), the compile-time C preprocessor macro

1123

<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> is used to determine where temporary tables and indices

1124

are stored. When

1125

temp_store is MEMORY (2) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are kept in

1126

as if they were pure <a href="inmemorydb.html">in-memory databases</a> memory.

1127

When temp_store is FILE (1) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are stored

1128

in a file. The <a href="pragma.html#pragma_temp_store_directory">temp_store_directory</a> pragma can be used to specify

1129

the directory containing temporary files when

1130

FILE is specified. When the temp_store setting is changed,

1131

all existing temporary tables, indices, triggers, and views are

1132

immediately deleted.

1133

1134

It is possible for the library compile-time C preprocessor symbol

1135

<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> to override this pragma setting.

1136

The following table summarizes

1137

the interaction of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> preprocessor macro and the

1138

temp_store pragma:

1139

1140

1141

1142

<tr><th valign="bottom"><a href="compile.html#temp_store">SQLITE_TEMP_STORE</a></th>

1143

<th valign="bottom">PRAGMA temp_store</th>

1144

<th>Storage used for TEMP tables and indices</th></tr>

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

<td align="center">memory</td></tr>

1157

1158

1159

<td align="center">memory</td></tr>

1160

1161

1162

1163

1164

1165

<td align="center">memory</td></tr>

1166

1167

1168

<td align="center">memory</td></tr>

1169

</table>

1170

</blockquote>

1171

1172

<hr>

1173

PRAGMA temp_store_directory;

1174

PRAGMA temp_store_directory = 'directory-name';

1175

Query or change the value of the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global

1176

variable, which many operating-system interface backends use to

1177

determine where to store <a href="inmemorydb.html#temp_db">temporary tables</a> and indices.

1178

1179

1180

Do not use this pragma! This pragma is deprecated and exists

1181

for backwards compatibility only. New applications

1182

should avoid using this pragma. Older applications should discontinue

1183

use of this pragma at the earliest opportunity. This pragma may be omitted

1184

from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.

1185

1186

1187

1188

When the temp_store_directory setting is changed, all existing temporary

1189

tables, indices, triggers, and viewers in the database connection that

1190

issued the pragma are immediately deleted. In

1191

practice, temp_store_directory should be set immediately after the first

1192

database connection for a process is opened. If the temp_store_directory

1193

is changed for one database connection while other database connections

1194

are open in the same process, then the behavior is undefined and

1195

probably undesirable.

1196

1197

Changing the temp_store_directory setting is not threadsafe.

1198

Never change the temp_store_directory setting if another thread

1199

within the application is running any SQLite interface at the same time.

1200

Doing so results in undefined behavior. Changing the temp_store_directory

1201

setting writes to the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global

1202

variable and that global variable is not protected by a mutex.

1203

1204

The value directory-name should be enclosed in single quotes.

1205

To revert the directory to the default, set the directory-name to

1206

an empty string, e.g., PRAGMA temp_store_directory = ''. An

1207

error is raised if directory-name is not found or is not

1208

writable.

1209

1210

The default directory for temporary files depends on the OS. Some

1211

OS interfaces may choose to ignore this variable and place temporary

1212

files in some other directory different from the directory specified

1213

here. In that sense, this pragma is only advisory.

1214

1215

<hr>

1216

PRAGMA vdbe_listing = boolean;

1217

1218

1219

This pragma is intended for use when debugging SQLite itself. It

1220

is only contained in the build when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option

1221

is used.

1222

1223

1224

If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time

1225

option, then the vdbe_listing pragma can be used to cause a complete

1226

listing of the virtual machine opcodes to appear on standard output

1227

as each statement is evaluated.

1228

With listing is on, the entire content of a program is printed

1229

just prior to beginning execution. The statement

1230

executes normally after the listing is printed.

1231

This feature is used for debugging SQLite itself. See the

1232

<a href="vdbe.html#trace">VDBE documentation</a> for more

1233

information.

1234

1235

<hr>

1236

PRAGMA vdbe_trace = boolean;

1237

1238

1239

This pragma is intended for use when debugging SQLite itself. It

1240

is only contained in the build when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option

1241

is used.

1242

1243

1244

If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time

1245

option, then the vdbe_trace pragma can be used to cause virtual machine

1246

opcodes to be printed on standard output as they are evaluated.

1247

This feature is used for debugging SQLite. See the

1248

<a href="vdbe.html#trace">VDBE documentation</a> for more

1249

information.

1250

1251

<hr>

1252

PRAGMA wal_autocheckpoint;

1253

PRAGMA wal_autocheckpoint=N;

1254

1255

This pragma queries or sets the <a href="wal.html">write-ahead log</a>

1256

<a href="wal.html#ckpt">auto-checkpoint</a> interval.

1257

When the <a href="wal.html">write-ahead log</a> is enabled (via the

1258

<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>) a checkpoint will be run automatically whenever

1259

the write-ahead log equals or exceeds N pages in length.

1260

Setting the auto-checkpoint size to zero or a negative value

1261

turns auto-checkpointing off.

1262

1263

This pragma is a wrapper around the

1264

<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface.

1265

1266

Autocheckpointing is enabled by default with an interval

1267

of 1000 or <a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a>.

1268

1269

1270

<hr>

1271

PRAGMA database.wal_checkpoint;

1272

PRAGMA database.wal_checkpoint(PASSIVE);

1273

PRAGMA database.wal_checkpoint(FULL);

1274

PRAGMA database.wal_checkpoint(RESTART);

1275

1276

1277

If the <a href="wal.html">write-ahead log</a> is enabled (via the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>),

1278

this pragma causes a checkpoint operation to run on database

1279

database, or on all attached databases if database

1280

is omitted. If <a href="wal.html">write-ahead log</a> is disabled, this pragma is a

1281

harmless no-op.

1282

1283

Invoking this pragma is equivalent to calling the

1284

<a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> C interface with a

1285

<a href="c3ref/c_checkpoint_full.html">3rd parameter</a>

1286

corresponding to the argument of the PRAGMA. Invoking this

1287

pragma without an argument is equivalent to calling the

1288

<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface.

1289

1290

1291

<hr>

1292

PRAGMA writable_schema = boolean;

1293

1294

When this pragma is on, the SQLITE_MASTER tables in which database

1295

can be changed using ordinary <a href="lang_update.html">UPDATE</a>, <a href="lang_insert.html">INSERT</a>, and <a href="lang_delete.html">DELETE</a>

1296

statements. Warning: misuse of this pragma can easily result in

1297

a corrupt database file.

1298

1299

<hr>

1300