Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root
Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root
Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root
Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root
Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root
Choose any three.
|
Overview
FTS3 is an SQLite virtual table module that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified. This document describes the deployment and usage of FTS3.
Portions of the original FTS3 code were contributed to the SQLite project by Scott Hess of Google. It is now developed and maintained as part of SQLite.
1. Introduction to FTS3
The FTS3 extension module allows users to create special tables with a built-in full-text index (hereafter "FTS3 tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more instances specified word (hereafter a "token", even if the table contains many large documents.
For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both the FTS3 table and the ordinary SQLite table created using the following SQL script:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */ CREATE TABLE enrondata2(content TEXT); /* Ordinary table */ |
Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ |
Of course, the two queries above are not entirely equivather than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:
- SQLITE_FULL: database or disk full
- SQLITE_IOERR: disk I/O error
- SQLITE_BUSY: database in use by another process
- SQLITE_NOMEM: out or memory
- SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.
./usr/share/doc/sqlite3-doc/fts3.html 0000644 0000000 0000000 00000301324 11453502254 016271 0 ustar root root