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|
|17:59||Fix a typo in shared_schema.md. check-in: e47a5aea user: dan tags: reuse-schema|
|17:51||Add documentation file doc/shared_schema.md to describe the change on this branch. check-in: a5f8067d user: dan tags: reuse-schema|
|15:56||Merge latest trunk into this branch. check-in: 577d1638 user: dan tags: reuse-schema|
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 +