(6) Is SQLite thread safe?
Threads are evil. Avoid using them.
SQLite is thread-safe. Since many users ignore the suggestions we gave in the previous paragraph, we made this concession. However, in order to achieve thread safety, SQLite must set the SQLITE_THREADSAFE preprocessing macro to 1 at compile time. This is how it is set up in compiled binary distributions on Windows and Linux. If you are not sure whether the library you are using is thread-safe, you can call the sqlite3_threadsafe() interface to find out.
Before version 3.3.1, a sqlite3 structure could only be used to call the same thread created by sqlite3_open. You cannot open a database in one thread and pass the database handle to another process for use. This is mainly caused by restrictions in many general thread implementations (such as RedHat9) (is it a bug?). In particular, on a problematic system, the fcntl() lock created by a process cannot be cleared or modified by other threads. Therefore, since SQLite uses fcntl() locks extensively for concurrent control, if you move database connections between different threads, serious problems may arise.
On version 3.3.1, restrictions on mobile database connections between threads have become loose. In it and later versions, it is safe to move the handle between threads as long as the connection does not hold the fcntl() lock. If there are no pending transactions and all statements have been executed, you can safely assume that no longer holds any locks.
In UNIX, the open database should not be carried into the child process when executing the fork() system call. Doing that will have problems.
--------------------------------------------------------------------------------
(7) How to list all tables and indexes in SQLite database?
If you run the sqlite3 command line to access your database, you can type ".tables" to get a list of all tables. Alternatively, you can enter ".schema" to see the entire database schema, including the index of all tables. Enter these commands, followed by a LIKE pattern matching to limit the displayed table.
In a C/C++ program (or scripting language uses Tcl/Ruby/Perl/Python, etc.) you can execute a SELECT query on a special name SQLITE_MASTER to get the index of all tables. Each SQLite database has a table called SQLITE_MASTER, which defines the database schema. The SQLITE_MASTER table looks as follows:
CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
For tables, the type field is always 'table', and the name field is always the name of the table. So, to get a list of all tables in the database, use the following SELECT statement:
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
For index, type is equal to 'index', name is the name of the index, and tbl_name is the name of the table to which the index belongs. Whether it is a table or an index, the sql field is the command text that was originally created when they were created using the CREATE TABLE or CREATE INDEX statement. For automatically created indexes (used to implement PRIMARY KEY or UNIQUE constraints), the sql field is NULL.
The SQLITE_MASTER table is read-only. You cannot use UPDATE, INSERT or DELETE on it. It will be automatically updated by the CREATE TABLE, CREATE INDEX, DROP TABLE and DROP INDEX commands.
Temporary tables will not appear in the SQLITE_MASTER table. The temporary table, its index and trigger are stored in another table called SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER is similar to SQLITE_MASTER, but it is only visible to applications that create temporary tables. If you want to get a list of all tables, whether permanent or temporary, you can use a command like the following:
SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name
--------------------------------------------------------------------------------
(8) Is there a known size limit for SQLite database?
Under Windows and Unix, SQLite in version 2.7.4 can reach 2 bytes to the power of 41 (2T bytes). The old version is 31 bytes to the power of 2 (2G bytes).
SQLite version 2.8 limits the capacity of a record to 1M. SQLite version 3.0 has no limit on the capacity of a single record.
There is no length limit for table names, index table names, view names, trigger names, and field names. However, the name of the SQL function (created by the sqlite3_create_function() API function) must not exceed 255 characters.
For a detailed discussion of SQLite restrictions, see .
--------------------------------------------------------------------------------
(9) In SQLite, what is the maximum length of the VARCHAR field?
SQLite does not force the length of VARCHAR. You can declare a VARCHAR(10) in SQLITE, and SQLite can happily allow you to put 500 characters. And these 500 characters are intact and they will never be truncated.
--------------------------------------------------------------------------------
(10) Does SQLite support binary large objects?
SQLite 3.0 and later allow you to store BLOB data in any column. Even if the column is declared as another type.
--------------------------------------------------------------------------------
(11) In SQLite, how to add or delete a column on a table?
SQLite has limited support for ALTER TABLE. You can use it to add a column at the end of the table to change the name of the table. If more complex changes to the table structure are needed, the table must be rebuilt. During reconstruction, you can first put the existing data into a temporary table, delete the original table, create a new table, and then copy the data from the temporary table.
For example, suppose there is a t1 table with three columns "a", "b", "c" and "c". If you want to delete column c, the following process describes how to do it:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
--------------------------------------------------------------------------------
(12) I deleted a lot of data in the database, but the database file has not become smaller. Is it a bug?
no. When you delete data from the SQLite database, unused disk space will be added to an internal "free list". This part of the space can be reused the next time you insert data. Disk space will not be lost, but it will not be returned to the operating system.
If you delete a large amount of data and want to reduce the space occupied by the database file, execute the VACUUM command. VACUUM will reorganize the database from scratch. This will use the database to have an empty "free linked list" and the database file will be minimal. But it should be noted that the execution of VACUUM will take some time (in SQLite development, on Linux, it takes about half a second per M byte), and the execution process requires up to twice the temporary disk space of the original database file.
For SQLite version 3.1, an auto-vacumm mode can replace the VACUUM command. It can be opened using auto_vacuum pragma.
--------------------------------------------------------------------------------
(13) Can I use SQLite in commercial products without paying licensing fees?
Yes. SQLite in public domain. There is no claim for ownership of any part of the code. You can do anything with it.
--------------------------------------------------------------------------------
(14) How to use single quotes (') in strings?
The SQL standard stipulates that in a string, single quotes require the use of escape characters, that is, two single quotes are used in a line. In this regard, SQL is similar to the Pascal language. SQLite follows the standards. like:
INSERT INTO xyz VALUES('5 O''clock');
--------------------------------------------------------------------------------
(15) What is the error in SQLITE_SCHEMA error? Why does this error occur?
When a prepared SQL statement is no longer valid or cannot be executed, a SQLITE_SCHEMA error will be returned. When this error occurs, the SQL statement must be recompiled using the sqlite3_prepare() API. In SQLite 3, a SQLITE_SCHEMA error only occurs when executing SQL with the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API. It does not happen when using sqlite3_exec(). This is not the case in version 2.
The most common reason why prepared statements fail is that after the statement is ready, the database pattern is modified again. Another reason can occur in:
Database offline: DETACHed.
The database was VACUUMed
A user stored procedure definition is deleted or changed.
A collation sequence definition is deleted or changed.
The authentication function is changed.
In all cases, the workaround is to recompile and execute the SQL statement. Because a prepared statement can be invalidated due to other processes changing the database schema, all code using the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API should be prepared to handle the SQLITE_SCHEMA error. Here is an example:
int rc;
sqlite3_stmt *pStmt;
char zSql[] = "SELECT .....";
do {
/* Compile the statement from SQL. Assume success. */
sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);
while( SQLITE_ROW==sqlite3_step(pStmt) ){
/* Do something with the row of available data */
}
/* Finalize the statement. If an SQLITE_SCHEMA error has
** occured, then the above call to sqlite3_step() will have
** returned SQLITE_ERROR. sqlite3_finalize() will return
** SQLITE_SCHEMA. In this case the loop will execute again.
*/
rc = sqlite3_finalize(pStmt);
} while( rc==SQLITE_SCHEMA );
--------------------------------------------------------------------------------
(16) Why ROUND(9.95,1) returns 9.9 instead of 10.0? 9.95 Shouldn’t it be rounded (rounded)?
SQLite uses binary arithmetic. In binary, it is impossible to express 9.95 with a finite number of binary digits. Using 64-bit IEEE floating point (SQLite uses this) The closest binary representation to 9.95 is 9.9499999999999999999289457264239899814128875732421875. Where, when you enter 9.95, SQLite actually thinks it is the number above, and will be discarded when rounded.
This problem occurs any time a binary floating point number is used. The usual rule is to remember that many finite decimal decimal numbers do not have a corresponding binary representation. So, they can only use the closest binary number. They are usually very close, but they can also be slightly different, and sometimes they can lead to different results you expect.
Previous page12Read the full text