Documentation Source Text

Check-in [227fb14d50]
Login

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

Overview
Comment:Add instructions for how to do arbitrary schema changes to the ALTER TABLE documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 227fb14d50bf3a2305bdfbe42c92eac8348b2e1c
User & Date: drh 2014-05-25 21:54:57
Context
2014-05-25
22:00
Revisions to the arbitrary table schema change text in ALTER TABLE. check-in: b730943d19 user: drh tags: trunk
21:54
Add instructions for how to do arbitrary schema changes to the ALTER TABLE documentation. check-in: 227fb14d50 user: drh tags: trunk
20:11
Another accidental fork merge. check-in: b1b9dd90bb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

176
177
178
179
180
181
182


























































183
184
185
186
187
188
189
<p> The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier.</p>



























































<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

RecursiveBubbleDiagram analyze-stmt
</tcl>







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







176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
<p> The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier.</p>

<tcl>hd_fragment otheralter</tcl>
<h3>Making Other Kinds Of Table Schema Changes</h3>

<p> The only schema altering commands directly supported by SQLite are the
"rename table" and "add column" commands shown above.  However, applications
can make other arbitrary changes to the format of a table using a simple
sequence of operations.
The steps to make arbitrary changes to the schema design of some table X
are as follows:

<ol>
<li><p>
Drop all indexes, triggers, and views associated with table X using 
appropriate [DROP INDEX], [DROP TRIGGER], and [DROP VIEW] commands.

<li><p>
Rename table X to some alternative name that does not collide with 
any other table in the schema.  
For example: ALTER TABLE X RENAME TO old_X.

<li><p>
Use the [CREATE TABLE] command to recreate table X in the new desired format.

<li><p>
Run an [INSERT | INSERT INTO X SELECT ... FROM old_X] command to transfer
content from the old version of X into the new X.

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

<li><p>
Recreate all the indexes, triggers, and views that were dropped in step 1
above and that remain relevant after the table alteration
using approprate [CREATE INDEX], [CREATE TRIGGER], and [CREATE VIEW]
commands.
</ol>

<p>If some future version of SQLite adds new ALTER TABLE capabilities, it
will probably do so using the steps 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
# with the altered schema for table X in step 6.  It is a tricky but solvable
# problem to create code that will perform the steps above 
# for "reasonable" schemas commonly found in practice.  
# But there exist malevolent schemas for which
# these steps are maddeningly difficult to do correctly.  Rather than create
# ALTER TABLE features that work correctly 99.9% of the time but possibly
# corrupt the schema on the other 0.1%, the SQLite developers have decided
# to push the problem into the application domain, where it is much easier
# to solve.
</tcl>


<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

RecursiveBubbleDiagram analyze-stmt
</tcl>