Tag Archive : sqlite

/ sqlite

SQLite Manager on Mozilla Firefox

December 9, 2015 | Article | 1 Comment

Addons are one of Mozilla Firefox’s interesting feature. One of interesting addons would be SQLite Manager which can be used for managing SQLite database.

In this article we will discuss about SQLite Manager on Mozilla Firefox. For this purpose I use:

  1. Slackware64 14.0
  2. Mozilla Firefox 20.0

However, unless you are not using Mozilla Firefox and not using SQLite, the Operating System should give no problem.

Prerequisite

  1. SQLite, latest version is recommended
  2. Mozilla Firefox

If you have not installed SQLite yet, please read related article first. You can read specific instruction on how to install SQLite for your OS (Linux, Windows, or Mac OS X).

Installation

There are two ways to install add-ons (Well, you should have know it) and both has same result.

By Firefox Menu

  1. Open Firefox
  2. Click Tools -> Add ons
  3. on bar Search add ons, type in sqlite manager
  4. Click on Install.
  5. Wait for download process and installation finished.
  6. Restart firefox

By Firefox Add-Ons site

  1. Go to https://addons.mozilla.org
  2. on search bar, type in sqlite manager and click the most matching one
  3. Click on Add to Firefox.
  4. Wait for download process and installation finished.
  5. Restart firefox

Both way you will have SQLite Manager installed. The add-on itself can be accessed as Tools -> SQLite Manager.

SQLiteMan

Now, let’s see how we can do task in previous article using SQLite Manager.

Create a New SQLite Database

To create a new database with filename xathrya.db, click on Database -> New database which give use following:

SQLiteMan1

And save the file in your preferred location, for example your home folder. Note that any database name will be appended with .sqlite

Create Table

To create a table, you can use Table -> New Table which will give following result:

SQLiteMan2

Now let’s see the result:

SQLiteMan3

Insert Record

Let’s insert 5 records there. For example we want to have records which are equivalent to this:

insert into projects values(101,'GSM Tracker using Raspberry Pi','2013-08-01');
insert into projects values(102,'GUNDAM OS','2014-08-01');
insert into projects values(103,'Project Mikan','2015-08-01');
insert into projects values(104,'Project Freedom','2016-08-01');
insert into projects values(105,'Low Orbit Unmanned Plane using Arduino','2017-08-01');

Unfortunately, we have to insert it one by one by click button Add. For example we will insert record with id=101.

SQLiteMan4

Verify Record

Well, when you see tab Browse & Search you can see the result immediately.

SQLiteMan5

Delete a Record

To delete a record, click on the record you want to delete then click Delete button on Browse & Search tab.

You will be asked for confirmation, click on Yes if you wish to delete the record

Update a Record

To update a record, click on the record you want to update then click Edit button on Browse & Search tab.

SQLiteMan6

Exit

Just close the window.

Reopen Database

By default, SQLite manager will try to open last database you open. If you want to open other database, click Database -> Connect Database and find the file you want to open (for this article’s example would be xathrya.db.sqlite)

Execute SQL

A fancy feature of SQLite Manager has (well, it should has, fortunately) is Execute SQL. We can execute SQL command directly. To do so, click on tab Execute SQL. There would be two areas: one is textbox for inserting command, and other to displaying the result.

For example, we want to list the record which have id greater than 102. Enter the command “select * from projects where id>102” and click Run SQL.

SQLiteMan7

First Interaction with SQLite

December 9, 2015 | Article | 1 Comment

In the previous article we have discussed about how to install SQLite. Whether you have installed it for Linux, Windows, or Mac OS X, there should be a working SQLite. If you don’t have it yet, please proceed to installation first depend on you system.

In this article we will discuss about SQLite and cover following topics:

  • Create a new SQLite database called “xathrya.db”.
  • Create “projects” table with three fields 1) Project ID 2) Name and 3) Deadline
  • Insert 5 records into the project tables.
  • Verify the records
  • Delete a record
  • Update a record
  • Exit SQLite3
  • Reopen database

Actually the operating system is not the matter here, means you can use SQLite on any operating system (whether Linux, Windows, or Mac OS X). This article should be generic enough to cover all of them.

Create a New SQLite Database

Like any SQL DBMS, data is stored on a database. However, SQLite will store all the information (tables, and metadatas) on a single file instead of creating differet files.

In this section we will create a new database with filename xathrya.db. Use following command:

sqlite3 xathrya.db

You would be prompted by something like this:

SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

At this point, we have entered the SQLite’s command shell. Using this shell we can enter SQL command and manage our database.

Create Table

Creating a table in SQLite is like using any Relational DMBS. The underlying language is similiar, SQL.

Now let’s create a table “projects” having three fields:

  1. Project ID, denoted by field id as integer.
  2. Name, denoted by field name as varchar
  3. Deadline, denoted as deadline as datetime

The command (within SQLite shell) would be:

create table projects(id integer primary key,name varchar(20),deadline date);

If you see on current working directory, we have newly created file xathrya.db as result of our command.

Insert Record

To insert a record we use familiar SQL command

insert into tableT values(list of values);

Which in our case the the tableT would be projects and the list of values will be the value of id,name,deadline respectively. Let’s insert 5 records there.

insert into projects values(101,'GSM Tracker using Raspberry Pi','2013-08-01');
insert into projects values(102,'GUNDAM OS','2014-08-01');
insert into projects values(103,'Project Mikan','2015-08-01');
insert into projects values(104,'Project Freedom','2016-08-01');
insert into projects values(105,'Low Orbit Unmanned Plane using Arduino','2017-08-01');

Verify Record

Let’s see whether our records has been saved by SQLite by invoking following command:

select * from projects;<br>

And you should be given following result.

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2014-08-01
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01
105|Low Orbit Unmanned Plane using Arduino|2017-08-01

Delete a Record

To delete a record, for example on with id 105, use following command:

delete from projects where id=105;<br>

Now let’s verify the tables. At this point, SQLite should give you following result:

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2014-08-01
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01

Update a Record

SQLite can be used to update an individual record. For example we want to change project with id 102 and set the deadline to 2013-07-02. Use following command;

update projects set deadline='2013-07-02' where id=102;<br>

Now verify the table. The output should be like this:

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2013-07-02
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01

Exit

To exit, press CTRL+D (CTRL and D at same time).

Reopen Database

When we create a database, it is nothing but a file. To access an existing database and query the records, do the following. i.e When you do “sqlite3 xathrya.db”, if the database doesn’t exist it’ll create it. If it already exists, it’ll open it. As simple as that 😀

sqlite3 xathrya.db

If you dont believe it, try to see the tables projects.

Social media & sharing icons powered by UltimatelySocial