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.

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

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:

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?

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) 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) 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.

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) 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.

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.