Questions tagged [sqlite]

SQLite is an open-source software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

SQLite is a relational database management system contained in a small (~350 KB) C programming library. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it.

SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.

Making an MRE for SQLite questions on StackOverflow

Providing a minimal reproducible example for an SQLite-related question is most usefully and conveniently done by showing a few lines in SQLite syntax (i.e. some create table ... and insert ... which makes a tailored toy database with appropriate structure and sample data). Also consider making a db fiddle and sharing the link (there are multiple free such services out there; see, for example, this and this).

This way, potential answerers can easily recreate the database you used for demonstrating the problem and quickly and efficiently provide solution proposals that are supported by test runs and test output. Showing pictures of database viewers or table representations (even in ASCII art) does not provide the same benefits.

When seeking assistance with an SQL query, structure your query accordingly (see tips for asking a good SQL question).

If you already have created a database for demonstration purposes, consider using the .dump command of the SQLite commandline tool. It will automatically give you the lines for exactly recreating the database.

Getting familiar with the commandline tool also is a good way of avoiding all potential errors in whatever programming language is used to handle the database. With the commandline tool, you can inspect and analyse data and structure directly.

Mobile Apps

SQlite is commonly used to store data on Android, iOS, and Windows Phone apps since it has a simple implementation, easy to adapt, and quite fast.

Design

Unlike client-server database management systems, the SQLite engine has no standalone processes with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program.

The application program uses SQLite's functionality through simple function calls, which reduce latency in database access: function calls within a single process are more efficient than inter-process communication. SQLite stores the entire database as a single cross-platform file on a host machine.

References

Books

94030 questions
129
votes
8 answers

SQLite UPSERT / UPDATE OR INSERT

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database. There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work…
bgusach
  • 14,527
  • 14
  • 51
  • 68
128
votes
5 answers

SQLiteDatabase.query method

I am using the query method of SQLiteDatabase. How do I use the query method? I tried this: Cursor cursor = sqLiteDatabase.query( tableName, tableColumns, whereClause, whereArgs, groupBy, having, orderBy); tableColumns - columns parameter is…
sree_iphonedev
  • 3,514
  • 6
  • 31
  • 50
127
votes
4 answers

What is the syntax for "not equal" in SQLite?

Cursor findNormalItems = db.query("items", columns, "type=?", new String[] { "onSale" }); I want to return the cursor that points anything that are NOT onSale, what should I change? Thanks!
sammiwei
  • 3,140
  • 9
  • 41
  • 53
127
votes
6 answers

Convert NSData to String?

I am storing a openssl private Key EVP_PKEY as nsdata. For this I am serializing into a byte stream using the code below unsigned char *buf, *p; int len; len = i2d_PrivateKey(pkey, NULL); buf = OPENSSL_malloc(len); p = buf; i2d_PrivateKey(pkey,…
Zach
  • 9,989
  • 19
  • 70
  • 107
127
votes
6 answers

How do I insert datetime value into a SQLite database?

I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error: The SQL statements are: create table myTable (name varchar(25), myDate…
Brad
  • 20,302
  • 36
  • 84
  • 102
126
votes
13 answers

Change from SQLite to PostgreSQL in a fresh Rails project

I have a rails app that's databases are in SQLite (The dev and production). Since I am moving to heroku, I want to convert my database to PostgreSQL. Anyways, I heard that the local, development, database does not need to be changed from SQLite, so…
Vasseurth
  • 6,354
  • 12
  • 53
  • 81
126
votes
1 answer

Efficient paging in SQLite with millions of records

I need to show the SQLite results in a list view. Of course, I need to page the results. The first option is to use the LIMIT clause. For example: SELECT * FROM Table LIMIT 5000, 100 It returns records 5001 to 5100. The problem is that internally…
Dabiel Kabuto
  • 2,762
  • 4
  • 29
  • 45
125
votes
11 answers

Objects created in a thread can only be used in that same thread

I can't find the problem: @app.route('/register', methods=['GET', 'POST']) def register(): form = RegisterForm(request.form) if request.method=='POST' and form.validate(): name = form.name.data email = form.email.data …
Tania
  • 1,263
  • 2
  • 7
  • 5
124
votes
7 answers

Is it possible to access an SQLite database from JavaScript?

I have a set of HTML files and a SQLite database, which I would like to access from the browser, using the file:// scheme. Is it possible to access the database and create queries (and tables) using JavaScript?
Pal Szasz
  • 2,954
  • 3
  • 20
  • 18
124
votes
16 answers

rails install pg - Can't find the 'libpq-fe.h header

$ sudo bundle install Result Fetching gem metadata from https://rubygems.org/........... Fetching gem metadata from https://rubygems.org/.. Using rake (0.9.2.2) Using i18n (0.6.1) Using multi_json (1.3.6) Using activesupport (3.2.8) Using…
Arthur Yakovlev
  • 8,933
  • 8
  • 32
  • 48
123
votes
10 answers

SQLite add Primary Key

I created a table in Sqlite by using the CREATE TABLE AS syntax to create a table based on a SELECT statement. Now this table has no primary key but I would like to add one. Executing ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...) gives a…
Jack Edmonds
  • 31,931
  • 18
  • 65
  • 77
123
votes
3 answers

SQLite - How do you join tables from different databases?

I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a hard time figuring out how to join tables from the…
Jumbala
  • 4,764
  • 9
  • 45
  • 65
121
votes
9 answers

Best practices for in-app database migration for Sqlite

I am using sqlite for my iphone and I anticipate the database schema might change over time. What are the gotchas, naming conventions and things to watch out for to do a successful migration each time? For example, I have thought of appending a…
Boon
  • 40,656
  • 60
  • 209
  • 315
121
votes
18 answers

System.Data.SQLite Close() not releasing database file

I'm having a problem closing my database before an attempt to delete the file. The code is just myconnection.Close(); File.Delete(filename); And the Delete throws an exception that the file is still in use. I've re-tried the Delete() in the…
Tom Cerul
  • 1,773
  • 2
  • 13
  • 27
121
votes
6 answers

Android: upgrading DB version and adding new table

I've already created sqlite tables for my app, but now I want to add a new table to the database. I changed the DB version as below private static final int DATABASE_VERSION = 2; and Added string to create table private static final String…
Jay Mayu
  • 17,023
  • 32
  • 114
  • 148