Know How To Execute Command Line Shell for Sqlite

In our last blog we shed the light on Atomic Commit in Sqlite Database Sqlite holds out the ability to access the interface of a number of Operating System through shell commands. It extends out the facilities to provide a user interface for interacting with Windows, Linux, and Mac Operating Systems. The command line shell is available for download from the following website

By using the command line shell, users can run queries to access the database files of Sqlite as well as get access to the database related transactions.

For Windows Platform

Download the program mentioned below. This program is compatible with all the Sqlite versions, latest being 3.8.10.2 .The distribution file consists of a sqlite3 .exe file that facilitates in providing a command-line shell interface for modifying the Sqlite database.

Get Started with Sqlite Command Line shell

Once Sqlite is installed, the first step involves running Sqlite3 to interact with the Operating System. The Sqlite3 utility takes in SQL commands from command prompt interface which form an interactive medium to pass on the commands to the core Sqlite.

For the creation of a database, specify Sqlite3 with a database argument. Here, we will create a database file of the employee.db.

The creation of the database is confirmed by the presence of the employee.db file .

Note: Any command beginning with Sqlite> implies that the command is executed in Command –Line prompt of SQL.

You have a database file called as employee.db that consists of a table called emp_details. This table has four columns:

• A primary key column known as emp_id. This has an auto increment value and helps n uniquely identifying the record in a table. The purpose of mentioning the emp_id as primary key is that whenever a user defines a column of a table as integer primary key, Sqlite automatically applies the auto-increment mode for that column.
• Next, for adding some rows to the table we make use of insert commandTo fetch them back we use the command given below

The output for the above command is as follows

 

Get Clear on Autoincrement columns

Moving to the topic of auto increment columns, if a user is interested to know the value for the last inserted autoincrement value then this can be obtained by using the SQL command last_insert_rowid()

Adding Index or View to the Created Database

For adding a view or index to the database employee.db, execute the commands given below

Creation of view

Addition of Index to the Database

Extracting Database Schema Related Information

There are many shell commands which are used for retrieving information about databases. A user can retrieve the no of tables by using .tables. All tables will be fetched by the query.

Therefore, we can see that a table emp_details and the corresponding view named as schema are returned back. Similarly, to check for the indexes for a specific table, one can use the following syntax

Here, one can notice that the index created on the table emp_details known as emp_details_idx

Obtaining DDL /SQL Definition for a Specific Table

To obtain Data Definition Language pertaining to a particular table we can use the following shell command

Suppose there is no table name then in this case we use the command .schema only and this will fetch back all the objects related to the database.

Some More Dot Commands

Points That Need To Be Followed While Using Dot Commands

• The dot commands start with “.” At the life margin of the command line interface with no space left preceding it.
• It should be fitted straight into one single line
• It can occur amidst the SQL statements
• Comments are not comprehendible by dot commands
• Dot commands are restrictive in the sense that they do not get free flowed over the interface unlike SQL statements

Dot commands

The .help command when executed on the Command Line shell of Sqlite puts up the display of the commands and functionalities that has been discusses below.

Conclusion

Whatever is the platform; users can easily install and operate on Sqlite. Windows and Linux users have the facility for downloading the binaries. By far,the most common way of using Sqlite is by employing Command Line Shell. Through Command Line Shell, you can perform all activities related to the database. Sqlite has been designed with the flexibility to integrate into several tools while extending its compatibility over array of platforms. If you are a beginner, then you just need a copy of the Sqlite3 shell. You can download from the Sqlite website. The amalgamation source code available at the website is useful and can be used for getting integrated to majority of large projects.