Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

FAQ

For any questions not answered here, please read the SQL language reference manual, the documentation, the C-language interface reference, the static FAQ and the rest of this wiki first.

If your question remains unanswered, feel free to post it on the sqlite-users mailing list.

Another resource for assistance, in the form of live interactive chat, can be attained by visiting Freenode's #SQLite channel. There are usually 30-100 people logged onto the channel at any one instant. (IRC users typically access the network via an IRC client. However, Freenode has a browser-based UI that can be used: http://webchat.freenode.net/ )


Q) Are there bitwise operators in SQLite ? I'm thinking of storing some info in bit fields instead of a field for each piece of data.

A) Here are the details of the operators listed on the expression page

precedence operator operation
1 || concatenation
2 * multiplication
2 / division
2 % modulo
3 + addition
3 - subtraction
4 << bitwise shift left
4 >> bitwise shift right
4 & bitwise AND
4 | bitwise OR
5 < less than
5 <= less than or equal
5 > greater than
5 >= greater than or equal
6 = equal
6 == equal
6 != not equal
6 <> not equal
6 IN subset of
6 LIKE case insensitive equality with % and _ wildcards
6 GLOB case sensitive equality with Unix style file globbing syntax
6 MATCH reserved for user C function match()
6 REGEXP reserved for user C function regexp()
7 AND boolean (logical) AND
8 OR boolean (logical) OR


Q) Where can I find sample C code?

A) There is some sample code in the wiki. See:

Of course you can have a look at the source of the sqlite3 command line tool itself in the cvs repository:

  • /sqlite/src/shell.c

Also of interest is the source of the

  • fossil project by drh, a Distributed Revision Control, Wiki, and Bug-tracking System.


Q) Why can't I define triggers across multiple, attached, databases?

A) Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work and the schema would turn invalid. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database?

The same goes for foreign key constraints. There are severe implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one.


Q) Is there a way to ATTACH more than the default maximum of 10 databases?

A) Have a look in the language description for the ATTACH DATABASE statement. It points to a compile time option.


Q) Which func could get the number of rows?

A) There is no function to retrieve the number of rows in a result set. SQLite doesn't know the number in advance, but returns row by row while iterating through the tables. The application can increment a row counter as needed at every successful sqlite3_step() .

Some wrappers are able to collect all rows in a resultset in a in-memory table, so they can return the number of rows.

You can always get the number of rows that a certain SELECT statement would return at the cost of some performance:

   BEGIN IMMEDIATE TRANSACTION;
   SELECT COUNT(*) FROM x WHERE y;
   SELECT a,b,c FROM x WHERE y;
   ROLLBACK TRANSACTION;

You have to wrap this in a transaction to prevent other connections from inserting / deleting rows between the two SELECT statements.


Q) I just installed a PHP script on my webhost using sqlite. Where's sqlite file/database located?

A) Where your PHP script chooses to put it. It should be documented in the application you installed.


Q) how can i tell if my webhost support sqlite?

A) Use the PHP function `function_exists`

   bool function_exists ( string $function_name )


Q) How can I compare the speed of SQLite writing and reading from a table to doing roughly the same thing with a flat file?

A) Depends on your platform. On Unix and look-alikes use the time command.


Q) Does SQLIte have a syntax equivalent to:

   SHOW TABLES

Or how do I find out what tables a SQLite database has?

A) If you are using the SQLite command line program, try this:

   .tables

You can also use a pattern that matches tables using a LIKE pattern:

   .tables ?PATTERN?

If using another utility to access the database (or programmatically), this should work:

   SELECT name FROM sqlite_master WHERE type = "table";

A) Pragmas used to query the schema of the current database.

A) Information Schema


Q) Are there any plans for Concurrent Queries Improvements on SQLite?

I mean, when i'm hypothetically inserting 100000 records in a table throguh a PHP PDO Query, and I try to access this table for a simple query like (select * from table where id = 1), SQLite returns a message that the table is locked. Is there any research for this?

A) This is better discussed on the mailing list.


Q) Is there a active project roadmap?

A) The roadmap is in the ToDo wiki page.


Q) How many characters can a query have? (TCL: dbHandle eval $Query)

A) The lesser of the amount of available memory and 2147483648 bytes.


Q) How atomic are the SQL statements? Are UPDATE/INSERT/SELECT/DELETE atomic (e.g, can you modify multiple cross-dependant rows, under the assumption that no one of them could be read by a SELECT thread from another connection)? What about nested queries? What about triggers? For example: If a trigger renumbers a sequencial integer column on INSERT/DELETE, can it be assumed a SELECT statement from another connection won't be capable of reading a non-sequencial temporary column state between the INSERT/DELETE and the trigger action?

A) Very atomic, if you use transactions in a careful way. A SELECT thread will wait for the atomic transaction to be complete if it acquires a lock by using BEGIN IMMEDIATE or BEGIN EXCLUSIVE. It will wait until the modifying transaction has been committed or rolled back. See the discussion on the transaction page.


Q) Is there an expression (inside SELECT) for checking if a column exists?

A) The closest I can think of is

   PRAGMA table_info(test);


Q) Can CREATE ... (column_def) be combined with CREATE ... AS SELECT..., as in CREATE ... (column_def) AS SELECT...(makes a merged table)?

A) No.


Q) I am using a column named id integer primary key. I also have a url column. Is there a prefered way to check if a url is a duplicate before inserting a new record?

A) Create a UNIQUE index on your url column.

eg. CREATE UNIQUE INDEX urlunique ON urltable (url)

where

  • urlunique is an index name,
  • urltable is the name of your table
  • url is the column.


Q) Is there a decent Report Writer that can be used with SQLite. I would like to use it in a browser based application and possibly call it using Javascript/PHP?


Q) Is the built-in SQL function list under {link:http://www.sqlite.org/lang_expr.html expression} complete? What about String functions like REPLACE?

A) Read the bottom of that page, it links to the core functions.


Q) Is there a Wiki engine that uses SQLite instead of, for example, MySQL?

A) This wiki uses SQLite. See http://www.cvstrac.org/

A) PhpWiki supports SQLite via PEAR::DB

A) Fossil, a Distributed Revision Control, and Bug-Tracking system, uses SQLite for everything and has a built in Wiki.


Q) What is the calling convention for sqlite3.dll API functions (for Windows) and for SQLite callback functions (for sqlite3_exec function)?

A) cdecl (caller pushes parameters right to left on stack, caller cleans up)


Q) On sqlite3_Open(), when the file with given filename doesn't exist, SQLite creates new database. But usually, the application needs to do some initialization work to be able to use this new database (create tables etc.)

So, what is the most natural way to determine, that Open() created new database instead of opening the existing one?

A) Use some system API funtion like IsFileExists(DB_filename) before calling sqlite_open(DB_filename)

A) or, use the "user_version" pragma. Right after calling open(), do "pragma user_version". If it returns "0", then assume that this is a new file -- create your tables and do "pragma user_version=1" to mark that you've created your tables in this db. The next time you do "pragma user_version", it will return 1, signifying that you've previously set up this db.


Q) Is it possible to let the programmer choose - commit or rollback the transaction that was begun with BEGIN and some INSERTS/UPDATES, but was not finished due to system hangup or power failure, instead of automatically rollback it on opening the DB??? (assume the transaction journal file integrity is fully correct)

A) No.

A) If you leave out BEGIN / COMMIT, every statement is a transaction by itself, and SQLite will only rollback the last unfinished statement.


Q) Is there a standard way of backing up SQlite databases aside from simply copying the database file to another location

A) Yes. In the command line tool, the ".dump" command will output the schema and data in the form of sql statements. E.g.:

        sqlite3  mydatabase  .dump  >Mybackup.sql

You can pipe this file back into sqlite3 should you need to restore.

A) SQLite v3.6.11 introduced a backup API, which is exposed to the SQLite command line tool as the .backup command.


Q) Is it possible to output queries and associated results into an output text file?

A) Yes, use the command line tool and study the result of its .help command. (hint: .output)


Q) Is there a simple way to figure out the primary key(s) of a table through sql?

A) Use the last column of

   PRAGMA table_info(tablename);

A) Interpret the result of a select on the master table.


Q) Does SQLite have a bulk loading tool?

A) Yes, use the command line tool and study the result of its .help command. (hint: .import).


Q) How can I use SQLite in combination with Qt from Trolltech ?

A) QT4.1: http://doc.trolltech.com/4.1/qtsql.html and http://doc.trolltech.com/4.1/sql-driver.html#qsqlite-for-sqlite-version-3-and-above

A) QT3.3: http://doc.trolltech.com/3.3/sql.html and http://doc.trolltech.com/3.3/sql-driver.html#QSQLITE


Q) Where do I get the header file (i.e. sqlite3.h) matching the precompiled binary (i.e. sqlite-3.2.1.so.gz) ?

A) The download page contains both precompiled binaries and a source tarball of the same version. sqlite3.h is also easily extractable from the amalgamated source. And then there is the CVS source repository. Its usage is documented at the bottom of the download page.


Q) Are there any advantages of using either a static library over the linked library (Performace, Speed, Compatibality)?

A) Depends, but on Windows platforms hardly. It matters startup times and perhaps the ease of deployment.

A) On platforms where the system uses SQLite (like SUN Solaris, Apple OS X) the provided library is usually a bit old. In order to include newer functions you may prefer to link the library statically into your application.


Q) Why do the prototypes from the C api reference and the Quick Start guide not match up?


Q) Could SQLite be used to implement the Table Oriented Programming philosophy efficiently and easily? Can an embedded database using precompiled access functions compete with object-oriented access of object attributes in performance (a slowdown by a factor of 10 could be tolerated, but not by a factor of 100 or 1000)? If not, could this be achieved (by some caching mechanism, for example)?


Q) Would it be possible to run SQLite off a DVD essentially creating a completely self contained system without requiring any installation or modification on the host computer, and would it be prohibitively slow to access the DB to doing so?

A) Do you mean to write a DB file to a ramdisk? This should not be particularly slow.


Q) What conventions should I follow when submitting a patch and where should I send it?

A) File a ticket using the link in the submenu above and attach your patch to the ticket. The patch should be against the most recent source milestone. You also have to include a contribution statement


Q) Is there a limit to the number of prepared statements?

A) No practical limit. You have to have enough memory to hold them all.


Q) Is it possible to modify the way functions are handled in sqlite ? My idea is to allow functions to have their own private data space to save data from row to row like the agregates have, with that we can have functions that remember last row values, create counters and totalizers that return their updated values for each row.

Ex:

select increment(1),* from my_table;

select sum_and_return_row_by_row(row_value_to_sum),* from my_table;

select current_row_value + last_row_value(current_row_value),* from my_table;

The structure for that is already there, in fact is the same used by agregates, I was scratching the code but I could not find easily where to introduce code to push the context and recover for functions that aren't agregates, someone know how to do that ?


Q) How can the strict affinity mode be used which is claimed to exist on http://www.sqlite.org/datatype3.html

A) This has not been implemented as of version 3.3.13.


Q) Does SQLite have a prefered file type? Obviously things like .db are too generic, and file.sqlite seems just a little bit long... .sl2? .sl3? What should we use to be friendly with simple type checkers.

A) File extensions are a predominantly a Windows phenomenon. Be careful with certain extensions as they may trigger Windows XP's system restore systems (eg. .sdb is associated with Appfix packages, which means Win XP will keep a backup copy every time it's changed - thus dramatically affecting your performance). Don't make it one of these: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sr/sr/monitored_file_extensions.asp


Q) are the database files platform independent? i.e. it is possible to transfer them from solaris to linux, or from windows to linux, configure the path and run the platform specific software on it?

A) Yes.


Q) Are there any known sources of ready-to-use collating function code/libraries dealing with European language's collation? (It can't be that there should be so less about this topic in conjunction with SQLite - it would be a toy or a bunch of work without.)

A)


Q) Is possible to make cross database join?

A) Yes. ATTACH the second / third ... databases, and use their symbolic names in database-name.table-name instead of just table-name.

See also: http://www.sqlite.org/syntaxdiagrams.html#join-source .

Note: It's not possible to define cross database joins as a VIEW.


Q) I have a csv file to import that uses double quotes to escape text fields with commas. How can I import this using sqlite3?

A) See the ImportingFiles wiki page.


Q) I get the following error:

    attempt to write a readonly database

But when I try the obvious fix (making it not read-only), I get the following error:

    unable to open database file

What's up with that? I'm accessing through the pysqlite wrapper in a cgi-script.

A) Make sure that the directory containing the database file is also writable to the user executing the CGI script.


Q) Where can I download fts2.dll?

A) FTS3 is included in the sqlite3.dll download. It is a better replacement of FTS2.


Q) Data files made by Sqlite 2.8.x seem a litte bloat. any suggestion on inserting data therefore effectively using sqlite? Please.

We here use PHP4/5 in which sqlite2.8 is embeded by default and the 3.x versions along with PDO are less popular among the web hosting service providers.

As the 2.8-embebed PHP5 versions will be around for next couples of years, where can i find more sqlite 2.8-specific information from this official website?

please (sorry for my bad english)


Q) How can I use memsys5 memory allocator from Tcl? (I want to allocate 2GB of memory beforehand, then insert data there without malloc overhead (Win XP). I'm expirementing with OLAP-alike database.)