SQLite Data Type

SQLite data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQLite.

You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

 

SQLite Storage Classes:

Each value stored in an SQLite database has one of the following storage classes:

 

Storage Class

Description

NULL   The value is a NULL value.

INTEGER   The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL    The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)

BLOB   The value is a blob of data, stored exactly as it was input.

 

SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer data types of different lengths.

 

SQLite Affinity Type:

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities:

Affinity

Description

TEXT This column stores all data using storage classes NULL, TEXT or BLOB.

NUMERIC This column may contain values using all five storage classes.

INTEGER Behaves the same as a column with NUMERIC affinity with an exception in a CAST expression.

REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation

NONE A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

SQLite Affinity and Type Names:

 

Following table lists down various data type names which can be used while creating SQLite3 tables and corresponding applied affinity also has been shown:

 

Data Type

Affinity

INT

INTEGER

TINYINT

SMALLINT

MEDIUMINT

BIGINT

UNSIGNED BIG INT

INT2

INT8

INTEGER

CHARACTER (20)

VARCHAR (255)

VARYING CHARACTER (255)

NCHAR (55)

NATIVE CHARACTER (70)

NVARCHAR (100)

TEXT

CLOB

TEXT

BLOB

no datatype specified

NONE

REAL

DOUBLE

DOUBLE PRECISION

FLOAT

REAL

NUMERIC

DECIMAL (10, 5)

BOOLEAN

DATE

DATETIME

NUMERIC

Boolean Datatype:

 

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

 

Date and Time Datatype:

SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values.

Storage Class

Date Format

TEXT A date in a format like “YYYY-MM-DD HH:MM:SS.SSS”.

REAL The number of days since noon in Greenwich on November 24, 4714 B.C.

INTEGER The number of seconds since 1970-01-01 00:00:00 UTC.

You can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

SQLite CREATE Database

The SQLite sqlite3 command is used to create new SQLite database. You do not need to have any special privilege to create a database.

Syntax:

Basic syntax of sqlite3 command is as follows:

$sqlite3 DatabaseName.db

Always, database name should be unique within the RDBMS.

Example:

If you want to create new database <testDB.db>, then SQLITE3 statement would be as follows:

$sqlite3 testDB.db

SQLite version 3.7.15.2 2013-01-09 11:53:05

Enter “.help” for instructions

Enter SQL statements terminated with a “;”

sqlite>

Above command will create a file testDB.db in the current directory. This file will be used as database by SQLite engine. If you have noticed while creating database, sqlite3 command will provide a sqlite> prompt after creating database file successfully.

Once a database is created, you can check it in the list of databases using SQLite .databases command as follows:

sqlite>.databases

seq  name             file

—  —————  ———————-

0    main             /home/sqlite/testDB.db

 

You will use SQLite .quit command to come out of the sqlite prompt as follows:

sqlite>.quit

$

The .dump Command

You can use .dump dot command to export complete database in a text file using SQLite command at command prompt as follows:

$sqlite3 testDB.db .dump > testDB.sql

Above command will convert the entire contents of testDB.db database into SQLite statements and dump it into ASCII text file testDB.sql. You can do restoration from the generated testDB.sql in simple way as follows:

 

$sqlite3 testDB.db < testDB.sql

At this moment your database is empty, so you can try above two procedures once you have few tables and data in your database.

SQLite ATTACH Database

 

Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DTABASE statement is used to select a particular database, and after this command, all SQLite statements will be executed under the attached database.

 

Syntax:

Basic syntax of SQLite ATTACH DATABASE statement is as follows:

ATTACH DATABASE ‘DatabaseName’ As ‘Alias-Name’;

Above command will also create a database in case database is already not created, otherwise it will just attach database file name with logical database ‘Alias-Name’.

Example:

If you want to attach an existing database testDB.db, then ATTACH DATABASE statement would be as follows:

sqlite> ATTACH DATABASE ‘testDB.db’ as ‘TEST’;

Use SQLite .database command to display attached database.

sqlite> .database

 

seq  name             file

—  —————  ———————-

0    main             /home/sqlite/testDB.db

2    test             /home/sqlite/testDB.db

 

 

The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment, otherwise you will get a warning message something as follows:

 

sqlite> ATTACH DATABASE ‘testDB.db’ as ‘TEMP’;

Error: database TEMP is already in use

sqlite> ATTACH DATABASE ‘testDB.db’ as ‘main’;

Error: database TEMP is already in use

 

Admin has written 171 articles