Manipulate the SQLite database

 

Abstract

The purpose of this document is to demonstrate how a sqlite database for a Ruby on Rails application can be manipulated using Sqlite Browser tool. The procedure explained in the document can be extremely helpful for creating and debugging any Ruby on Rails’ application.

Motivation:

This document explains how Sqlite database for any Ruby on Rails application can be manipulated using DB browser for Sqlite Browser tool.

Introduction:

We need to directly manipulate the Sqlite database created for one of the web application mytodolist. Any changes in the database should reflect in the web application to validate that changes have been done permanently to the database. To accomplish the task, we would use DB Browser for Sqlite tool.

Prerequisite:

You must have an existing Sqlite database on the machine. In this document , we would use the database created for mytodolist application as an example.

Note: mytodolist web application is created using Ruby on Rails.

Method/measurement:

Step1- Download and Install Sqlite Browser:

First step is to download the Sqlite browser tool. Go to http://sqlitebrowser.org/ and click on download link to download the installable exe.

Once download finishes successfully, you can double click the .exe file to start the standard installation process. Once the installation has finished succussfully , you would be able to see sqlite program option under start menu as shown in below screen shot.

Step 2-Open the database using Sqlite Browser:

Now,Open the Sqlite browser by clicking on the icon in start->menu option. You would be able to see following screen on startup.

Now, Click on the open database button present in the toolbar and provide the database location to open sqlite database as shown below.

This would open the selected database and you would be able to see following screen. As you can see all the components of the database i.e Tables, views and triggers etc are listed in the tree structure.

Step 3-Manipulate database using sqlite browser:

In this step , we would use the opened database to do select, insert,update and delete operations directly through the tool.We would also observe the impact of each of these operations on the data being displayed in our web application.

Select Operation:

In order to view the content of any table in the database , click on the browse Data tab and select the table from the dropdown as shown in the below screenshot. This would open the all the data in the table.

Alternatively, you can also go the Execute SQL Tab and run  “ Select * from <<table name>> “ query to retrieve data from the table as shown in below screenshot.

Insert Operation:

So far, we have only viewed the existing data in the table. In this step, we would insert some rows in the database by using Sqlite browser tool. For the selected database table, you can add the values for new rows under Browse data tab. To do this, select the required table and click on the New Record button as shown in below screen shot.

This would add a blank row at the bottom and you can enter the values for the columns. We have added 3 new rows in this example as shown in below screenshot.

Click on the Write changes button in the tool bar to write these changes to the database permanently.

Now, let’s open the web application to see the changes. In the below screenshot, we can see the changes have been reflected in our web application.

  Alternatively, you can also go to execute sql tab and run “insert into mytodos values (10,"insert op-4","insert location 4", f, "2015-02-27 22:56:00.000000", "2015-02-02 22:56:00.000000", "2015-02-02 22:56:00.000000")” query as shown below

Update Operation:

In this step, we would use an existing row and update any column values of the row using sqlite browser tool. We will also verify that the changes are reflected in our web application interface.

To do so, open the Browse data tab and select the required table. Now, click on any column to modify its value. In this example we will use the last inserted row and will update the value for item column from insert op-4 to update operation -1 as shown below.

You can make as many changes as you want. After doing all the required changes, click on the write changes button in the toolbar to save the changes in the database permanently.

Now, let’s open the web application to see the changes. In the below screenshot, we can see the changes have been reflected in our web application.

Alternatively, you can also go to Execute sql tab and run “update mytodos set item="udpate operation 1" where id=10” as shown below

Delete operation:

In this step, we will delete an existing row from the table using sqlite browser tool. We will also observe the changes in the web application interface.

To do so, open the browse data tab and select the required table. Now, select any row by clicking on the row number in the left most portion of the window as shown below. Once the required record has been select, hit Delete Record button as shown below . This would delete the selected record

You can delete as many rows as you want. After doing all the required changes, click on the write changes button in the toolbar to save the changes in the database permanently.

Now, let’s open the web application to see the changes. In the below screenshot, we can see the changes have been reflected in our web application.

Alternatively, you can also go to execute sql tab and run “delete from mytodos where id=10” as shown below

Results:

By following the steps mentioned in the above section, we have successfully selected, inserted , updated and deleted database records using SQLite browser tool.

Conclusion:

In the document, we have gone through the process to

1. Downloading and installing Sqlite browser.

2. Opening the database using Sqlite browser

3. Manipulating database directly using Sqlite browser.

4. Understand the structure of the important files generated by Rails.

5. Customizing MVC components.

6. Stop the Rails server.