Tag Archives: Sqlite - Page 2

My SQLite3 cheat sheet

Posted by on February 20, 2013 3 comments

Start SQLite

sqlite3 <filename>

Show all tables

.tables

Show whole database as SQL (kind of a “describe” function in MySQL)

.schema

Create new table example

CREATE TABLE users (id INTEGER PRIMARY KEY,login TEXT,pass TEXT);

The ‘id’ column is here what you call autoincrement in MySQL – if you assign NULL to this column the value will be incremented by 1 from last value

Valid columntypes:
TEXT
INTEGER
REAL
BLOB

Rename table example

ALTER TABLE users RENAME TO client_users

This renames table users to client_users

SELECT example

SELECT * FROM table_name WHERE column_name = value

Selects all rows in table table_name where column column_name is equal to value

UPDATE example

UPDATE table_name SET column_name = update_value WHERE some_column = some_value

Updates column_name with the value update_value in table table_name, on row/s where some_column is equal to some_value

DELETE example

DELETE FROM table_name WHERE column_name < 4

Deletes all rows from table_name where column_name is less than 4

INSERT example

INSERT INTO table_name (column1, column4, column7) VALUES (value1, 'value2', value3)

Inserts a new row into table table_name with values value1 in column1, value2 in column4 and value3 in column7 (other columns in table are left null or with default values, if set)

Read sql from file

sqlite3 my.db < filewithsql

This will read and execute the sql statements from the file filewithsql.

How to drop a column in SQLite 3

Posted by on September 25, 2012 2 comments

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11

SQLite check integrity and fix common problems

Posted by on August 8, 2011 2 comments

Every now and then you need to check the integrity of an SQLite database. The command for this is:

sqlite>PRAGMA integrity_check;

This will either report the problems or just return “ok”

If it does report problems one solution I always try first is to do an export/import of the database:

>sqlite3 database.db
sqlite>.output backup.db
sqlite>.dump
sqlite>.quit

>sqlite3 database_fixed.db
sqlite>.read backup.db
sqlite>.quit

>mv database_fixed.db database.db

This usually fix many of the common problems like “disk image is malformed” and “database is locked”

Tested on OSX 10.6.8 and SQLite v3.7.6 (MacPorts)