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?
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.
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 simpe query like (select * from table where id = 1), SQLite returns a message that the table is locked. Is there any research for this? Is there a active project roadmap? Thanks!
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?
Q) Is there an expression (inside SELECT) for checking if a column exists?
Q) Can CREATE ... (column_def) be combined with CREATE ... AS SELECT..., as in CREATE ... (column_def) AS SELECT...(makes a merged table)?
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 and url is the field.
Q) Is there a decent Report Writer that can be used with SQLite. I would like to use it in a browser based applicaiton and possibly call it using Javascript/PHP?
Q) Is the built-in SQL function list under {link:http://www.sqlite.org/lang_expr.html http://www.sqlite.org/lang_expr.html} complete? What about String functions like REPLACE?
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
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.
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.
Q) Is it possible to output queries and associated results into an output text file?
Q) Is there a simple way to figure out the primary key(s) of a table through sql?
A) Only by doing a select on the master table.
Q) Does SQLite have a bulk loading tool?
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) ?
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.
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 {link: http://www.geocities.com/SiliconValley/Lab/6888/top.htm 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?
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) What is the roadmap planed for the future of Sqlite ?
A)
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)
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.
Q) Where can I download fts2.dll?