Creating databases from SQLite shell

A simple file

Code Listing 1. Creating a contacts database

The Created DB file

Code Listing 2. The SQLite db file

How does it related to other DBMSs?

Code Listing 3. The API used for creating an SQLite db

Creating database from the Android Shell

Attach to an Android device where you want to create the database

Code Listing 4. Attaching to an Android device

Move to a data directory

Code Listing 5. A directory for creating the databases

Go to the SQLite shell and create db

Create a table

The Created file

Reopen the database

A simple file

In SQLite, the database is stored in one file. Creating a database is as simple as passing the name of the file to the SQLite command line program (for example: sqlite3.exe).

Code Listing 1. Creating a contacts database

D:\Research\sqlite\sqlite-3_5_7>sqlite3 contactsext.db
SQLite version 3.5.7
Enter ".help" for instructions

sqlite> CREATE TABLE contactsext (_id INTEGER, name TEXT);
sqlite> .tables
contactsext

sqlite> .exit

In the above code listing, sqlite3 is the command line program that can be downloaded from the SQLite site. I have installed (or ‘copied’/’unzipped’ the sqlite3.exe file into D:\Research\sqlite\... directory. When starting the program, contacts is provided as the parameter for the name of the database I would like to create.

The Created DB file

After the above command, if we look in the filesystem, we will see a contactsext.db file.

Code Listing 2. The SQLite db file

Directory of D:\Research\sqlite\sqlite-3_5_7

03/24/2008 11:19 AM 2,048 contactsext.db
03/17/2008 02:48 PM 440,651 sqlite3.exe

As you can see above, the contactsext.db file is created in the directory.

How does it related to other DBMSs?

The above database creation is vastly simplified, when you compare it with the full-fledged DBMSs like SQLServer, Oracle, etc. For example, there we have a ‘CREATE DATABASE’ command, which lets us specify one or more database files. This command will also let us specify initial and maximum sizes for these files, as well as specifying the file growth. In addition to the database files (which hold the actual data), we can also specify the log files, which log various database related events and changes.

SQLite simplifies the database creation and none of the above options are available. Of course, the above method is for creating an SQLite database from the command prompt. You can also create a new db programmatically by using the function sqlite3_open() (or, other related functions) from the SQLite API.

Code Listing 3. The API used for creating an SQLite db

SQLITE_API int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
SQLITE_API int sqlite3_open16(
const void *filename, /* Database filename (UTF-16) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
SQLITE_API int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb, /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs /* Name of VFS module to use */
);

Creating database from the Android Shell

The above example uses creating SQLite database from a Windows Shell (‘DOS’ shell). The example below uses the Android shell to create an SQLite database.

Attach to an Android device where you want to create the database

Code Listing 4. Attaching to an Android device

C:\Users\Administrator>adb devices
List of devices attached
1 emulator-tcp-5555 device 0


C:\Users\Administrator>adb -d 1 shell
#

As shown above, first find out what Android devices are out there by using the ‘adb devices’ command. Then Attach to a particular device and start the shell on it (adb –d 1 shell).

Move to a data directory

Code Listing 5. A directory for creating the databases

# pwd
pwd
/data/data/com.infinitezest.contactsext/databases

As an example, I have a contactsext application, which stores its data in the /data/data//databases directory. Now I am still in the Android/Linux/OS shell.

Go to the SQLite shell and create db

Just like before, opening the sqlite3 command line and creating a contactsext database takes one command. The version here (in Android) is slightly behind (3.5.7 vs. 3.5.0). That’s probably going to continue as it might take a bit of time before the latest SQLite is incorporated into Android.

# sqlite3 contactsext.db
sqlite3 contactsext.db
SQLite version 3.5.0
Enter ".help" for instructions

Create a table

Then create a table and verify it has been created.

sqlite> CREATE TABLE contactsext (_id INTEGER, name TEXT);
CREATE TABLE contactsext (_id INTEGER, name TEXT);

sqlite> .tables
.tables
contactsext

sqlite> select * from sqlite_master;
select * from sqlite_master;
table|contactsext|contactsext|2|CREATE TABLE contactsext (_id INTEGER, name TEXT
)

The Created file

Then get out of the SQLite shell to get back into the OS shell, and see that the contactsext.db file has been created.

sqlite> .exit
.exit
# ls -l
ls -l
-rw-r--r-- root root 2048 2008-03-24 18:13 contactsext.db

Reopen the database

If you open the contactsext.db again, you will see the previous table in there.

# sqlite3 contactsext.db
sqlite3 contactsext.db
SQLite version 3.5.0
Enter ".help" for instructions

sqlite> .tables
.tables
contactsext