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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
227fb14d50bf3a2305bdfbe42c92eac8 |
User & Date: | drh 2014-05-25 21:54:57.821 |
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
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> |
︙ | ︙ |