sqlite

How can I list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

How to delete or add column in SQLITE?

I want to delete or add column in sqlite database

I am using following query to delete column.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

But it gives error

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
Any good ORM tools for Android development?

Anyone working on the Android ('gPhone') have or know of a place where I can find a good ORM tool for it? The code is written in Java, and the database is SQLite. What I would like to find is a tool that given the object definition, can auto-generate the tables and the CRUD functions (that would be awesome), or, barring that, a tool that can take the table definition, the object definition, and auto-generate the CRUD functionality. The rub is that all of this must happen within the Android framework, which has its own conventions as to how database access works.

INSERT IF NOT EXISTS ELSE UPDATE?

I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.

I have a table defined as follows:

CREATE TABLE Book 
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER

What I want to do is add a record with a unique Name. If the Name already exists, I want to modify the fields.

Can somebody tell me how to do this please?

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I've got a purchases table that looks like this:

SELECT * FROM purchases;

My Output:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

Expected Output:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 3
How do I check in SQLite whether a table exists?

How do I, reliably, check in SQLite, whether a particular user table exists?

I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not (is this even a good idea?).

The reason is like this:

In my program, I need to create and then populate some tables if they do not exist already.

If they do already exist, I need to update some tables.

Should I take some other path instead to signal that the tables in question have already been created - say for example, by creating/putting/setting a certain flag in my program initialization/settings file on disk or something?

Or does my approach make sense?

Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2');

However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?

UPSERT *not* INSERT or REPLACE

http://en.wikipedia.org/wiki/Upsert

Insert Update stored proc on SQL Server

Is there some clever way to do this in SQLite that I have not thought of?

Basically I want to update three out of four columns if the record exists. If it does not exist, I want to INSERT the record with the default (NULL) value for the fourth column.

The ID is a primary key, so there will only ever be one record to UPSERT.

(I am trying to avoid the overhead of SELECT in order to determine if I need to UPDATE or INSERT obviously)

Suggestions?


I cannot confirm that syntax on the SQLite site for TABLE CREATE. I have not built a demo to test it, but it doesn't seem to be supported.

If it was, I have three columns so it would actually look like:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

but the first two blobs will not cause a conflict, only the ID would So I assume Blob1 and Blob2 would not be replaced (as desired)


UPDATEs in SQLite when binding data are a complete transaction, meaning Each sent row to be updated requires: Prepare/Bind/Step/Finalize statements unlike the INSERT which allows the use of the reset function

The life of a statement object goes something like this:

  1. Create the object using sqlite3_prepare_v2()
  2. Bind values to host parameters using sqlite3_bind_ interfaces.
  3. Run the SQL by calling sqlite3_step()
  4. Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
  5. Destroy the statement object using sqlite3_finalize().

UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?

Perhaps I should use the select to read the 4th column (Blob3) and then use REPLACE to write a new record blending the original 4th Column with the new data for the first 3 columns?

What are the performance characteristics of sqlite with very large database files?

2020 update, about 11 years after the question was posted and later closed, preventing newer answers.

Almost everything written here is obsolete. Once upon a time sqlite was limited to the memory capacity or to 2 GB of storage (32 bits) or other popular numbers... well, that was a long time ago.

Official limitations are listed here. Practically sqlite is likely to work as long as there is storage available. It works well with dataset larger than memory, it was originally created when memory was thin and it was a very important point from the start.

There is absolutely no issue with storing 100 GB of data. It could probably store a TB just fine but eventually that's the point where you need to question whether SQLite is the best tool for the job and you probably want features from a full fledged database (remote clients, concurrent writes, read-only replicas, sharding, etc...).


Original:

I know that sqlite doesn't perform well with extremely large database files even when they are supported (there used to be a comment on the sqlite website stating that if you need file sizes above 1GB you may want to consider using an enterprise rdbms. Can't find it anymore, might be related to an older version of sqlite).

However, for my purposes I'd like to get an idea of how bad it really is before I consider other solutions.

I'm talking about sqlite data files in the multi-gigabyte range, from 2GB onwards. Anyone have any experience with this? Any tips/ideas?

How can one see the structure of a table in SQLite?

How can I see the structure of table in SQLite as desc was in Oracle?

How can I avoid concurrency problems when using SQLite on Android?

What would be considered the best practices when executing queries on an SQLite database within an Android app?

Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I suppose that database queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an Application Not Responding (ANR).

If I have several AsyncTasks, should they share a connection or should they open a connection each?

Are there any best practices for these scenarios?

Create table in SQLite only if it doesn't exist already

I want to create a table in a SQLite database only if doesn't exist already. Is there any way to do this? I don't want to drop the table if it exists, only create it if it doesn't.

Store boolean value in SQLite

What is the type for a BOOL value in SQLite? I want to store in my table TRUE/FALSE values.

I could create a column of INTEGER and store in it values 0 or 1, but it won't be the best way to implement BOOL type.

Is there a way?

How to use an existing database with an Android application

I have already created an SQLite database. I want to use this database file with my Android project. I want to bundle this database with my application.

Instead of creating a new database, how can the application gain access to this database and use it as its database?

How to set Sqlite3 to be case insensitive when string comparing?

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive?

Is there an SQLite equivalent to MySQL's DESCRIBE [table]?

I'm just getting started learning SQLite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic information (for example, it doesn't show if a column is a field of some sort or not). Does SQLite have a way to do this?

Improve INSERT-per-second performance of SQLite

Optimizing SQLite is tricky. Bulk-insert performance of a C application can vary from 85 inserts per second to over 96,000 inserts per second!

Background: We are using SQLite as part of a desktop application. We have large amounts of configuration data stored in XML files that are parsed and loaded into an SQLite database for further processing when the application is initialized. SQLite is ideal for this situation because it's fast, it requires no specialized configuration, and the database is stored on disk as a single file.

Rationale: Initially I was disappointed with the performance I was seeing. It turns-out that the performance of SQLite can vary significantly (both for bulk-inserts and selects) depending on how the database is configured and how you're using the API. It was not a trivial matter to figure out what all of the options and techniques were, so I thought it prudent to create this community wiki entry to share the results with Stack Overflow readers in order to save others the trouble of the same investigations.

The Experiment: Rather than simply talking about performance tips in the general sense (i.e. "Use a transaction!"), I thought it best to write some C code and actually measure the impact of various options. We're going to start with some simple data:

  • A 28 MB TAB-delimited text file (approximately 865,000 records) of the complete transit schedule for the city of Toronto
  • My test machine is a 3.60 GHz P4 running Windows XP.
  • The code is compiled with Visual C++ 2005 as "Release" with "Full Optimization" (/Ox) and Favor Fast Code (/Ot).
  • I'm using the SQLite "Amalgamation", compiled directly into my test application. The SQLite version I happen to have is a bit older (3.6.7), but I suspect these results will be comparable to the latest release (please leave a comment if you think otherwise).

Let's write some code!

The Code: A simple C program that reads the text file line-by-line, splits the string into values and then inserts the data into an SQLite database. In this "baseline" version of the code, the database is created, but we won't actually insert data:

/*************************************************************
    Baseline code to experiment with SQLite performance.

    Input data is a 28 MB TAB-delimited text file of the
    complete Toronto Transit System schedule/route info
    from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"

#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * db;
    sqlite3_stmt * stmt;
    char * sErrMsg = 0;
    char * tail = 0;
    int nRetCode;
    int n = 0;

    clock_t cStartClock;

    FILE * pFile;
    char sInputBuf [BUFFER_SIZE] = "\0";

    char * sRT = 0;  /* Route */
    char * sBR = 0;  /* Branch */
    char * sVR = 0;  /* Version */
    char * sST = 0;  /* Stop Number */
    char * sVI = 0;  /* Vehicle */
    char * sDT = 0;  /* Date */
    char * sTM = 0;  /* Time */

    char sSQL [BUFFER_SIZE] = "\0";

    /*********************************************/
    /* Open the Database and create the Schema */
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    /* Open input file and import into Database*/
    cStartClock = clock();

    pFile = fopen (INPUTDATA,"r");
    while (!feof(pFile)) {

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "\t");     /* Get Route */
        sBR = strtok (NULL, "\t");            /* Get Branch */
        sVR = strtok (NULL, "\t");            /* Get Version */
        sST = strtok (NULL, "\t");            /* Get Stop Number */
        sVI = strtok (NULL, "\t");            /* Get Vehicle */
        sDT = strtok (NULL, "\t");            /* Get Date */
        sTM = strtok (NULL, "\t");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

    printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

The "Control"

Running the code as-is doesn't actually perform any database operations, but it will give us an idea of how fast the raw C file I/O and string processing operations are.

Imported 864913 records in 0.94 seconds

Great! We can do 920,000 inserts per second, provided we don't actually do any inserts :-)


The "Worst-Case-Scenario"

We're going to generate the SQL string using the values read from the file and invoke that SQL operation using sqlite3_exec:

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

This is going to be slow because the SQL will be compiled into VDBE code for every insert and every insert will happen in its own transaction. How slow?

Imported 864913 records in 9933.61 seconds

Yikes! 2 hours and 45 minutes! That's only 85 inserts per second.

Using a Transaction

By default, SQLite will evaluate every INSERT / UPDATE statement within a unique transaction. If performing a large number of inserts, it's advisable to wrap your operation in a transaction:

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    ...

}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

Imported 864913 records in 38.03 seconds

That's better. Simply wrapping all of our inserts in a single transaction improved our performance to 23,000 inserts per second.

Using a Prepared Statement

Using a transaction was a huge improvement, but recompiling the SQL statement for every insert doesn't make sense if we using the same SQL over-and-over. Let's use sqlite3_prepare_v2 to compile our SQL statement once and then bind our parameters to that statement using sqlite3_bind_text:

/* Open input file and import into the database */
cStartClock = clock();

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sRT = strtok (sInputBuf, "\t");   /* Get Route */
    sBR = strtok (NULL, "\t");        /* Get Branch */
    sVR = strtok (NULL, "\t");        /* Get Version */
    sST = strtok (NULL, "\t");        /* Get Stop Number */
    sVI = strtok (NULL, "\t");        /* Get Vehicle */
    sDT = strtok (NULL, "\t");        /* Get Date */
    sTM = strtok (NULL, "\t");        /* Get Time */

    sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);

    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    n++;
}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

Imported 864913 records in 16.27 seconds

Nice! There's a little bit more code (don't forget to call sqlite3_clear_bindings and sqlite3_reset), but we've more than doubled our performance to 53,000 inserts per second.

PRAGMA synchronous = OFF

By default, SQLite will pause after issuing a OS-level write command. This guarantees that the data is written to the disk. By setting synchronous = OFF, we are instructing SQLite to simply hand-off the data to the OS for writing and then continue. There's a chance that the database file may become corrupted if the computer suffers a catastrophic crash (or power failure) before the data is written to the platter:

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

Imported 864913 records in 12.41 seconds

The improvements are now smaller, but we're up to 69,600 inserts per second.

PRAGMA journal_mode = MEMORY

Consider storing the rollback journal in memory by evaluating PRAGMA journal_mode = MEMORY. Your transaction will be faster, but if you lose power or your program crashes during a transaction you database could be left in a corrupt state with a partially-completed transaction:

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

Imported 864913 records in 13.50 seconds

A little slower than the previous optimization at 64,000 inserts per second.

PRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY

Let's combine the previous two optimizations. It's a little more risky (in case of a crash), but we're just importing data (not running a bank):

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

Imported 864913 records in 12.00 seconds

Fantastic! We're able to do 72,000 inserts per second.

Using an In-Memory Database

Just for kicks, let's build upon all of the previous optimizations and redefine the database filename so we're working entirely in RAM:

#define DATABASE ":memory:"

Imported 864913 records in 10.94 seconds

It's not super-practical to store our database in RAM, but it's impressive that we can perform 79,000 inserts per second.

Refactoring C Code

Although not specifically an SQLite improvement, I don't like the extra char* assignment operations in the while loop. Let's quickly refactor that code to pass the output of strtok() directly into sqlite3_bind_text(), and let the compiler try to speed things up for us:

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Time */

    sqlite3_step(stmt);        /* Execute the SQL Statement */
    sqlite3_clear_bindings(stmt);    /* Clear bindings */
    sqlite3_reset(stmt);        /* Reset VDBE */

    n++;
}
fclose (pFile);

Note: We are back to using a real database file. In-memory databases are fast, but not necessarily practical

Imported 864913 records in 8.94 seconds

A slight refactoring to the string processing code used in our parameter binding has allowed us to perform 96,700 inserts per second. I think it's safe to say that this is plenty fast. As we start to tweak other variables (i.e. page size, index creation, etc.) this will be our benchmark.


Summary (so far)

I hope you're still with me! The reason we started down this road is that bulk-insert performance varies so wildly with SQLite, and it's not always obvious what changes need to be made to speed-up our operation. Using the same compiler (and compiler options), the same version of SQLite and the same data we've optimized our code and our usage of SQLite to go from a worst-case scenario of 85 inserts per second to over 96,000 inserts per second!


CREATE INDEX then INSERT vs. INSERT then CREATE INDEX

Before we start measuring SELECT performance, we know that we'll be creating indices. It's been suggested in one of the answers below that when doing bulk inserts, it is faster to create the index after the data has been inserted (as opposed to creating the index first then inserting the data). Let's try:

Create Index then Insert Data

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

Imported 864913 records in 18.13 seconds

Insert Data then Create Index

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

Imported 864913 records in 13.66 seconds

As expected, bulk-inserts are slower if one column is indexed, but it does make a difference if the index is created after the data is inserted. Our no-index baseline is 96,000 inserts per second. Creating the index first then inserting data gives us 47,700 inserts per second, whereas inserting the data first then creating the index gives us 63,300 inserts per second.


I'd gladly take suggestions for other scenarios to try... And will be compiling similar data for SELECT queries soon.

How to get Top 5 records in SqLite?

I have tried this which did not work.

select top 5 * from [Table_Name]
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied
def insert(array):
    connection=sqlite3.connect('images.db')
    cursor=connection.cursor()
    cnt=0
    while cnt != len(array):
            img = array[cnt]
            print(array[cnt])
            cursor.execute('INSERT INTO images VALUES(?)', (img))
            cnt+= 1
    connection.commit()
    connection.close()

When I try insert("/gifs/epic-fail-photos-there-i-fixed-it-aww-man-the-tire-pressures-low.gif"), I get an error message like in the title (the string is indeed 74 characters long).

What is wrong with the code, and how do I fix it?


The same problem occurs with MySQLdb and many other popular SQL libraries. See Why do I get "TypeError: not all arguments converted during string formatting" when trying to use a string in a parameterized SQL query? for details.

Java and SQLite

I'm attracted to the neatness that a single file database provides. What driver/connector library is out there to connect and use SQLite with Java.

I've discovered a wrapper library, http://www.ch-werner.de/javasqlite, but are there other more prominent projects available?

How do I add a new column in between two columns?

I have a table with columns name, qty, rate. I need to add a new column COLNew in between the name and qty columns. How do I add a new column in between two columns?

What 'additional configuration' is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?

I have a project in which I'd like to use some of the .NET 4.0 features but a core requirement is that I can use the System.Data.SQLite framework which is compiled against 2.X. I see mention of this being possible such as the accepted answer here but I don't see how to actually achieve this.

When I just try and run my 4.0 project while referencing the 2.X assembly I get:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

What "additional configuration" is necessary?

How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.

This Stackoverflow entry suggests doing the select

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

and parse the result.

Is that the common way? Alternatives?

Sqlite primary key on multiple columns

What is the syntax for specifying a primary key on more than 1 column in SQLITE ?

How do I rename a column in a SQLite database table?

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.

From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a single ALTER TABLE statement).

I was wondering someone knew of a generic SQL way of doing such a thing with SQLite.

Core Data vs SQLite 3

I am already quite familiar with relational databases and have used SQLite (and other databases) in the past. However, Core Data has a certain allure, so I am considering spending some time to learn it for use in my next application.

Is there much benefit to using Core Data over SQLite, or vice versa? What are the pros/cons of each?

I find it hard to justify the cost of learning Core Data when Apple doesn't use it for many of its flagship applications like Mail.app or iPhoto.app - instead opting for SQLite databases. SQLite is also used extensively on the iPhone.

Can those familiar with using both comment on their experience? Perhaps, as with most things, the question is deeper than just using one over the other?

Is there a .NET/C# wrapper for SQLite?

I'd sort of like to use SQLite from within C#.Net, but I can't seem to find an appropriate library. Is there one? An official one? Are there other ways to use SQLite than with a wrapper?

Version of SQLite used in Android?

What is the version of SQLite used in Android?

Reason: I'm wondering how to handle schema migrations. The newer SQLite versions support an "ALTER TABLE" SQL command which would save me having to copy data, drop the table, recreate table and re-insert data.

How do I unlock a SQLite database?

When I enter this query: sqlite> DELETE FROM mails WHERE (id = 71);

SQLite returns this error:

SQL error: database is locked

How do I unlock the database so this query will work?

When does SQLiteOpenHelper onCreate() / onUpgrade() run?

I have created my tables in my SQLiteOpenHelper onCreate() but receive

SQLiteException: no such table

or

SQLiteException: no such column

errors. Why?

NOTE:

(This is the amalgamated summary of tens of similar questions every week. Attempting to provide a "canonical" community wiki question/answer here so that all those questions can be directed to a good reference.)

sqlite