Documentation Source Text

Check-in [df84d97d7b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix to the 12-step procedure for generalized schema modifications. Additional text trying to describe why schema modifications are hard in SQLite.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: df84d97d7bef3af952f906ece94572f800e7216526758a01336b8488645891e6
User & Date: drh 2019-07-02 13:25:06.827
Context
2019-07-09
09:50
Merge documentation fixes from the 3.28 release. (check-in: 02ab9d6bd3 user: drh tags: trunk)
2019-07-02
13:25
Fix to the 12-step procedure for generalized schema modifications. Additional text trying to describe why schema modifications are hard in SQLite. (check-in: df84d97d7b user: drh tags: trunk)
12:47
Add documentation for the SQLITE_DQS compile-time option. Adjust the documentation so that the default is now -DSQLITE_DQS=3. (check-in: e2807b06c2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/fileformat2.in.
1
2
3

4
5
6
7
8
9
10
<title>Database File Format</title>
<alt-title>On-Disk Format</alt-title>
<tcl>hd_keywords {file format} {second edition file format document}</tcl>


<table_of_contents>

<p>This document describes and defines the on-disk database file
format used by all releases of SQLite since 
version 3.0.0 ([dateof:3.0.0]).</p>



|
>







1
2
3
4
5
6
7
8
9
10
11
<title>Database File Format</title>
<alt-title>On-Disk Format</alt-title>
<tcl>hd_keywords {file format} {second edition file format document} \
{SQLite database file format}</tcl>

<table_of_contents>

<p>This document describes and defines the on-disk database file
format used by all releases of SQLite since 
version 3.0.0 ([dateof:3.0.0]).</p>

Changes to pages/lang.in.
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272

273
274
275
276
277
278
279
280
281
282
283
<li><p>
If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF].

<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes and triggers associated with table X.
This information will be needed in step 8 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.

<li><p>
Transfer content from X into new_X using a statement
like: INSERT INTO new_X SELECT ... FROM X.

<li><p>
Drop the old table X:  [DROP TABLE | DROP TABLE X].

<li><p>
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

<li><p>

Use [CREATE INDEX] and [CREATE TRIGGER] to reconstruct indexes and triggers
associated with table X.  Perhaps use the old format of the triggers and
indexes saved from step 3 above as a guide, making changes as appropriate
for the alteration.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accommodate the schema change
using [CREATE VIEW].

<li><p>







|




















>
|
|
|
|







245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
<li><p>
If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF].

<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes, triggers, and views associated with table X.
This information will be needed in step 8 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.

<li><p>
Transfer content from X into new_X using a statement
like: INSERT INTO new_X SELECT ... FROM X.

<li><p>
Drop the old table X:  [DROP TABLE | DROP TABLE X].

<li><p>
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

<li><p>
Use [CREATE INDEX], [CREATE TRIGGER], and [CREATE VIEW]
to reconstruct indexes, triggers, and views
associated with table X.  Perhaps use the old format of the triggers,
indexes, and views saved from step 3 above as a guide, making changes
as appropriate for the alteration.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accommodate the schema change
using [CREATE VIEW].

<li><p>
387
388
389
390
391
392
393
















































394
395
396
397
398
399
400

<li><p> Commit the transaction started on step 1 above.
</ol>

<p>If some future version of SQLite adds new ALTER TABLE capabilities, 
those capabilities will very likely use one of the two procedures
outlined above.

















































<tcl>
#  One of the reasons that
# SQLite does not currently support more ALTER TABLE capabilities is that
# the procedure shown above is difficult to automate for an arbitrary schema.
# Particularly troublesome areas are identifying all views associated with
# table X in step 1 and creating new views and triggers that are compatible







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449

<li><p> Commit the transaction started on step 1 above.
</ol>

<p>If some future version of SQLite adds new ALTER TABLE capabilities, 
those capabilities will very likely use one of the two procedures
outlined above.

<tcl>hd_fragment altertableishard {why ALTER TABLE is so difficult}</tcl>
<h3>Why ALTER TABLE is such a problem for SQLite</h3>

<p>Most SQL database engines store the schema already parsed into
various system tables.  On those database engines, ALTER TABLE merely 
has to make modifications to the corresponding system tables.

<p>SQLite is different in that it stores the schema
in the [sqlite_master] table as the original text of the CREATE
statements that define the schema.  Hence ALTER TABLE needs
to revise the text of the CREATE statement.  Doing
so can be tricky for certain "creative" schema designs.

<p>The SQLite approach of storing the schema as text has advantages
for an embedded relational database.  For one, it means that the
schema takes up less space in the database file.  This is important
since a common SQLite usage pattern is to have many small,
separate database files instead of putting everything in one
big global database file, which is the usual approach for client/server
database engines.
Since the schema is duplicated in each separate database file, it is
important to keep the schema representation compact.

<p>Storing the schema as text rather than as parsed tables also
give flexibility to the implementation.  Since the internal parse
of the schema is regenerated each time the database is opened, the
internal representation of the schema can change from one release
to the next.  This is important, as sometimes new features require
enhancements to the internal schema representation.  Changing the
internal schema representation would be much more difficult if the
schema representation was exposed in the database file.  So, in other
words, storing the schema as text helps maintain backwards 
compatibility, and helps ensure that older database files can be
read and written by newer versions of SQLite.

<p>Storing the schema a text also makes the 
[SQLite database file format] easier to define, document, and 
understand. This helps make SQLite database files a
[recommended storage format] for long-term archiving of data.

<p>The downside of storing schema a text is that it can make
the schema tricky to modify.  And for that reason, the ALTER TABLE
support in SQLite has traditionally lagged behind other SQL
database engines that store their schemas as parsed system tables
that are easier to modify.



<tcl>
#  One of the reasons that
# SQLite does not currently support more ALTER TABLE capabilities is that
# the procedure shown above is difficult to automate for an arbitrary schema.
# Particularly troublesome areas are identifying all views associated with
# table X in step 1 and creating new views and triggers that are compatible