/ Check-in [a5f8067d]

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

Comment:Add documentation file doc/shared_schema.md to describe the change on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
User & Date: dan 2019-02-14 17:51:38
Fix a typo in shared_schema.md. check-in: e47a5aea user: dan tags: reuse-schema
Add documentation file doc/shared_schema.md to describe the change on this branch. check-in: a5f8067d user: dan tags: reuse-schema
Merge latest trunk into this branch. check-in: 577d1638 user: dan tags: reuse-schema
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added doc/shared_schema.md.

            1  +
            2  +Shared-Schema Mode Notes
            3  +========================
            4  +
            5  +This branch contains a patch to allow SQLite connections to share schemas
            6  +between database connections within the same process in order to save memory.
            7  +Schemas may be shared between multiple databases attached to the same or
            8  +distinct connection handles.
            9  +
           10  +To activate shared-schemas, a database connection must be opened using the
           11  +sqlite3_open_v2() API with the SQLITE_OPEN_SHARED_SCHEMA
           12  +flag specified.  The main database and any attached databases will then share
           13  +an in-memory Schema object with any other database opened within the process
           14  +for which: 
           15  +
           16  +  * the contents of the sqlite_master table, including all object names,
           17  +    SQL statements and root pages are identical, and
           18  +  * have the same values for the schema-cookie.
           19  +
           20  +Temp databases (those populated with "CREATE TEMP TABLE" and similar
           21  +statements) never share schemas.
           22  +
           23  +Connections opened with the SQLITE_OPEN_SHARED_SCHEMA flag
           24  +specified may not modify any database schema except that belonging to the
           25  +temp database in anyway. This includes creating or dropping database 
           26  +objects, vacuuming the database, or running ANALYZE when the
           27  +sqlite_stat\[14\] tables do not exist.
           28  +
           29  +If the schema of a database attached to an
           30  +SQLITE_OPEN_SHARED_SCHEMA database handle is corrupt, or if
           31  +corruption is encountered while parsing the database schema, then the
           32  +database is treated as empty. This usually means that corruption results in
           33  +a "no such table: xxx" error instead of a more specific error message.
           34  +
           35  +For SQLITE_OPEN_SHARED_SCHEMA connections, the
           36  +SQLITE_DBSTATUS_SCHEMA_USED sqlite3_db_used() distributes
           37  +the memory used for a shared schema object evenly between all database
           38  +connections that share it.
           39  +
           40  +## Implementation Notes
           41  +
           42  +A single Schema object is never used by more than database simultaneously,
           43  +regardless of whether or not those databases are attached to the same or
           44  +different database handles. Instead, a pool of schema objects is maintained 
           45  +for each unique sqlite_master-contents/schema-cookie combination
           46  +opened within the process. Each time database schemas are required by a
           47  +connection, for example as part of an sqlite3_prepare\*(),
           48  +sqlite3_blob_open() or sqlite3_blob_open() call, it obtains
           49  +the minimum number of schemas required from the various schema-pools, returning
           50  +them at the end of the call. This means that a single schema-pool only ever
           51  +contains more than one copy of the schema if:
           52  +
           53  +  * Two threads require schemas from the same pool at the same time, or
           54  +  * A single sqlite3_prepare\*() call requires schemas for two or more
           55  +    attached databases that use the same schema-pool.
           56  +
           57  +The size of a schema-pool never shrinks. Each schema pool always maintains 
           58  +a number of schema objects equal to the highwater mark of schema objects
           59  +simultaneously required by clients.
           60  +
           61  +This approach is preferred to allowing multiple databases to use the same
           62  +Schema object simultaneously for three reasons:
           63  +
           64  +  * The Schema object is not completely read-only. For example, the 
           65  +    Index.zIdxAff string is allocated lazily.
           66  +  * Throughout the statement compiler, SQLite uses variables like 
           67  +    Table.pSchema and Index.pSchema with the sqlite3SchemaToIndex() routine
           68  +    in order to determine which attached database a Table or Index object
           69  +    resides in. This mechanism does not work if the same Schema may be
           70  +    used by two or more attached databases.
           71  +  * It may be easier to modify this approach in order to allow
           72  +    SQLITE_OPEN_SHARED_SCHEMA connections to modify database
           73  +    schemas, should that be required.
           74  +
           75  +SQLITE_OPEN_SHARED_SCHEMA connections do not store their
           76  +virtual-table handles in the Table.pVTable list of each table. This would not
           77  +work, as (a) there is no guarantee that a connection will be assigned the same
           78  +Schema object each time it requests one from a schema-pool and (b) a single
           79  +Schema (and therefore Table) object may correspond to tables in two or more
           80  +databases attached to a single connection. Instead, all virtual-table handles
           81  +associated with a single database are stored in a linked-list headed at
           82  +Db.pVTable.
           83  +
           84  +
           85  +