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: |
df84d97d7bef3af952f906ece94572f8 |
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
Changes to pages/fileformat2.in.
1 2 | <title>Database File Format</title> <alt-title>On-Disk Format</alt-title> | | > | 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 | <li><p> If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF]. <li><p> Start a transaction. <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 |
︙ | ︙ |