Xathrya Sabertooth

SQLite Manager on Mozilla Firefox
First Interaction with SQLite
Installing SQLite on Windows
Installing SQLite on Linux
Installing Firebird on Linux

Posted: 29 Jun 2013 11:17 PM PDT

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:

Slackware64 14.0
Mozilla Firefox 20.0

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

Prerequisite

SQLite, latest version is recommended
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

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

By Firefox Add-Ons site

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

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

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:

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:

Now let’s see the result:

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.

Verify Record

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

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.

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.

First Interaction with SQLite

Posted: 29 Jun 2013 10:13 PM PDT

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:

Project ID, denoted by field id as integer.
Name, denoted by field name as varchar
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;

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;

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;

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.

Installing SQLite on Windows

Posted: 29 Jun 2013 09:28 PM PDT

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.Unlike most other SQL databases, SQLite is and embedded SQL database engine. It does not have separate server process.SQLite reads and writes directly to ordinary disk files. All power of database is contained in a single disk file. The file format is cross-platform which can be adopted to 32-bit and 64-bit systems or between big-endian and little-endian architectures. SQLite is not as a replacement for complex database, but as replacement for fopen() primitive.

In this article we will discuss about installing SQLite for Windows operating system. For this purpose I use Windows 7 32-bit as test machine. The method we use will be binary installation.

Obtain the Materials

SQLite latest version is 3.7.17. Download the source code from SQLite’s main download page. You need to download following:

A command-line shell for accessing and modifying SQLite databases. This program is compatible with all versions of SQLite through 3.7.17 and beyond. Download here.
This ZIP archive contains a DLL for the SQLite library version 3.7.17 for 32-bit x86 processors using the Win32 API. The DLL is built using SQLITE_ENABLE_COLUMN_METADATA so that it is suitable for use with Ruby on Rails. Download here.
(optional) An analysis program for database files compatible with all SQLite versions through 3.7.17 and beyond. Download here.

Installation

Assuming we have downloaded the above files:

Create a directory (folder) C:\>sqlite and unzip the zipped files into this folder. This should gives us sqlite3.exe, sqlite3.def, and sqlite3.dll. If you have download the analysis program, then you will get sqlite3_analyzer.exe.
Add C:\>sqlite in your PATH environment variable
open command prompt (cmd) and issue sqlite3 command which should display a result something as below.

  C:\>sqlite3  SQLite version 3.7.15.2 2013-01-09 11:53:05  Enter ".help" for instructions  Enter SQL statements terminated with a ";"  sqlite>

At this point we have installed the SQLite.

To explore / interact with SQLite for first time, see this article.

Installing SQLite on Linux

Posted: 29 Jun 2013 09:13 PM PDT

In this article we will discuss about installing SQLite in Linux. For this purpose I use Slackware64 14.0 as test machine. However, it can also be installed to other Linux distribution than Slackware. The method we use will be source installation.

Obtain the Materials

SQLite latest version is 3.7.17. Download the source code from SQLite’s main download page. The one we use in this article is this. Later we will refer this as SQLite archive.

Installation

Assuming we have downloaded the SQLite archive (sqlite-autoconf-30717.tar.gz):

  tar xvfz sqlite-autoconf-3070717.tar.gz  cd sqlite-autoconf-3070717  ./configure  make  make install

Note that to do installation, you should have enough privilege.

At this point we have installed the SQLite. The default installation would take /usr/local as prefix, thus the header will be located on /usr/local/include and the library will be at /usr/local/lib as well.

To explore / interact with SQLite for first time, see this article.

Installing Firebird on Linux

Posted: 29 Jun 2013 04:02 AM PDT

Firebird is a relational database offering many ANSI SQL standard features that runs on various operating system. It offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers.

In this article we will discuss about installing Firebird in Linux. For this purpose I use Slackware64 14.0 as test machine. However, it can also be installed to other Linux distribution than Slackware. The method we use will be source installation.

Obtain the Materials

Firebirdsql site use Sourceforge to host their source code, can be found here. The latest version we can find is Firebird 2.5.2 which can be downloaded here.

Installation

Assuming we have extract the archive and locate ourselves at source code’s directory, do following to do compilation and installation:

  ./configure  make  make install

Note that to do installation, you should have enough privilege.

At this point, we have installed Firebird SQL standard installed.

Proyek Teknologi

Minggu, 30 Juni 2013