Documentation Source Text

<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>

<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>

<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
................................................................................
<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>
................................................................................

<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

<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
................................................................................
<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>
................................................................................

<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