Documentation Source Text

Check-in [be73465c0f]
Login

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

Overview
Comment:Fix typos in the new ALTER TABLE documentation and in the queryplanner.html document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be73465c0f260008ca8205b38c935bd3b1658059
User & Date: drh 2014-05-26 13:58:41.917
Context
2014-05-26
15:38
Talk about partial index sorting in the queryplanner.html document. (check-in: f81d69fb6a user: drh tags: trunk)
13:58
Fix typos in the new ALTER TABLE documentation and in the queryplanner.html document. (check-in: be73465c0f user: drh tags: trunk)
13:10
Fix typos and clarify the text in the TH3 document. (check-in: 8e68e11794 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
<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 1 above as a guide, making changes as appropriate
for the alteration.

<li><p>
If foreign key constraints were originally turned on using (prior to
step 4) then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key contraints, and run
[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to reenable foreign key
constraints.

<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 accomodate the schema change
using [CREATE VIEW].

</ol>

<p>If desired, all of the steps above can be carried out inside of a
[BEGIN | transaction] so that the schema change is atomic.

<p>The procedure above is completely general and will work even if the
schema change causes the information stored in the table to change.
So the full procedure above is appropriate for dropping a column,
changing the order of columns, adding or removing a UNIQUE constraint
or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
or changing the datatype for a column, for example.  However, a simpler
and faster procedure can optionally be used for
some changes that do no affect the on-disk content in any way.
The following simpler procedure is appropriate for removing
CHECK or FOREIGN KEY or NOT NULL constraints,
renaming columns, or adding or removing or changing default values on
a column.

<ol>
<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 5 below.

<li><p> Active schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_master table]: 
UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_master table like this will
render the database corrupt and unreadable if the change contains







|












<
<
<

















|







231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
<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 1 above as a guide, making changes as appropriate
for the alteration.

<li><p>
If foreign key constraints were originally enabled (prior to
step 4) then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key contraints, and run
[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to reenable foreign key
constraints.

<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 accomodate the schema change
using [CREATE VIEW].

</ol>




<p>The procedure above is completely general and will work even if the
schema change causes the information stored in the table to change.
So the full procedure above is appropriate for dropping a column,
changing the order of columns, adding or removing a UNIQUE constraint
or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
or changing the datatype for a column, for example.  However, a simpler
and faster procedure can optionally be used for
some changes that do no affect the on-disk content in any way.
The following simpler procedure is appropriate for removing
CHECK or FOREIGN KEY or NOT NULL constraints,
renaming columns, or adding or removing or changing default values on
a column.

<ol>
<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 5 below.

<li><p> Activate schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_master table]: 
UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_master table like this will
render the database corrupt and unreadable if the change contains
Changes to pages/queryplanner.in.
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
</p>

<p>
The last two queries take the same amount of time, in our example.
So which index, Idx1 or Idx2, will SQLite choose?  If the
[ANALYZE] command has been run on the database, so that SQLite has
had an opportunity to gather statistics about the available indices,
then SQLite will know that the Idx1 table usually narrows the search
down to a single item (our example of fruit='Orange' is the exception
to this rule) where as the Idx table will normally only narrow the 
search down to two rows.  So, if all else is equal, SQLite will
choose Idx1 with the hope of narrowing the search to as small
a number of rows as possible.  This choice is only possible because
of the statistics provided by [ANALYZE].  If [ANALYZE] has not been
run then the choice of which index to use is arbitrary.
</p>








|

|







285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
</p>

<p>
The last two queries take the same amount of time, in our example.
So which index, Idx1 or Idx2, will SQLite choose?  If the
[ANALYZE] command has been run on the database, so that SQLite has
had an opportunity to gather statistics about the available indices,
then SQLite will know that the Idx1 index usually narrows the search
down to a single item (our example of fruit='Orange' is the exception
to this rule) whereas the Idx2 index will normally only narrow the 
search down to two rows.  So, if all else is equal, SQLite will
choose Idx1 with the hope of narrowing the search to as small
a number of rows as possible.  This choice is only possible because
of the statistics provided by [ANALYZE].  If [ANALYZE] has not been
run then the choice of which index to use is arbitrary.
</p>