SQLite

Check-in [0ce2092a59]
Login

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

Overview
Comment:Update doc/shared_schema.md to describe the shell tool ".shared-schema" dot-command.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: 0ce2092a5968d75d014fdc238391c362a9600c28bf18cdc631e98259a5278712
User & Date: dan 2019-02-26 16:36:21.259
Context
2019-03-04
07:25
Merge latest trunk changes into this branch. (check-in: 97a9604d85 user: dan tags: reuse-schema)
2019-02-26
16:36
Update doc/shared_schema.md to describe the shell tool ".shared-schema" dot-command. (check-in: 0ce2092a59 user: dan tags: reuse-schema)
16:13
Add new test file reuse5.test. For testing the shell tool ".shared-schema" command. (check-in: fa6008d547 user: dan tags: reuse-schema)
Changes
Unified Diff Ignore Whitespace Patch
Changes to doc/shared_schema.md.
︙︙
26
27
28
29
30
31
32



























































33
34
35
36
37
38
39

objects, vacuuming the database, or running ANALYZE when the
sqlite_stat\[14\] tables do not exist.

For SQLITE_OPEN_SHARED_SCHEMA connections, the
SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_status() verb
distributes the memory used for a shared schema object evenly between all
database connections that share it.




























































## Implementation Notes

A single Schema object is never used by more than one database simultaneously,
regardless of whether or not those databases are attached to the same or
different database handles. Instead, a pool of schema objects is maintained 
for each unique sqlite_master-contents/schema-cookie combination








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








26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

objects, vacuuming the database, or running ANALYZE when the
sqlite_stat\[14\] tables do not exist.

For SQLITE_OPEN_SHARED_SCHEMA connections, the
SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_status() verb
distributes the memory used for a shared schema object evenly between all
database connections that share it.

## The ".shared-schema" Command

The shell tool on this branch contains a special dot-command to help with
managing databases. The ".shared-schema" dot-command can be used to test
whether or not two databases are similar enough to share in-memory schemas,
and to fix minor problems that prevent them from doing so. To test if
two or more database are compatible, one database is opened directly using 
the shell tool and the following command issued:

        .shared-schema check <database-1> [<database-2>]...

where &lt;database-1;&gt; etc. are replaced with the names of database files
on disk. For each database specified on the command line, a single line of
output is produced. If the database can share an in-memory schema with the
main database opened by the shell tool, the output is of the form:

        <database> is compatible

Otherwise, if the database cannot share a schema with the main db, the output
is of the form:

        <database> is NOT compatible (<reason>)

where &lt;reason&gt; indicates the cause of the incompatibility. &lt;reason&gt;
is always one of the following.

<ul>
  <li> <b>objects</b> - the databases contain a different set schema objects
  (tables, indexes, views and triggers).

  <li> <b>SQL</b> - the databases contain the same set of objects, but the SQL
  statements used to create them were not the same.

  <li> <b>root pages</b> - the databases contain the same set of objects created
  by the same SQL statements, but the root pages are not the same.

  <li> <b>order of sqlite&#95;master rows</b> - the databases contain the same
  set of objects created by the same SQL statements with the same root pages,
  but the order of the rows in the sqlite&#95;master tables are different.

  <li> <b>schema cookie</b> - the database schemas are compatible, but the 
  schema cookie values ("PRAGMA schema&#95;version") are different.
</ul>

The final three problems in the list above can be fixed using the
.shared-schema command. To modify such a database so that it can share a 
schema with the main database, the following shell command is used:

        .shared-schema fix <database-1> [<database-2>]...

If a database can be modified so that it may share a schema with the main
database opened by the shell tool, output is as follows:

        Fixing <database>... <database> is compatible

If a database does not require modification, or cannot be modified such that
it can share a schema with the main database, the output of "fix" is identical
to that of the "check" command.

## Implementation Notes

A single Schema object is never used by more than one database simultaneously,
regardless of whether or not those databases are attached to the same or
different database handles. Instead, a pool of schema objects is maintained 
for each unique sqlite&#95;master-contents/schema-cookie combination
︙︙