DECLARE
   CURSOR emp_cur
   IS
      SELECT first_name
        FROM employees
       WHERE employee_id = 100;

   l_emp_name employees.first_name%TYPE;
BEGIN
   IF emp_cur%ISOPEN
   THEN
      CLOSE emp_cur;
   END IF;

   OPEN emp_cur;

   FETCH emp_cur
    INTO l_emp_name;

   CLOSE emp_cur;

END;

DECLARE
   l_emp_name   employees.first_name%TYPE;
BEGIN
   SELECT first_name
     INTO l_emp_name
     FROM employees
    WHERE employee_id = 100;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Not found');
END;

The Smallest Database Management System Is Just 359 Kilobytes

As you may already know, Google recently released Google Gears, an open source browser extension that lets developers create web applications that can run offline using JavaScript APIs.

Google Gears provides three key features:

• A local server, to cache and serve application resources (HTML, JavaScript, images, etc.) without needing to contact a server.
• A database, to store and access data from within the browser.
• A worker thread pool, to make web applications more responsive by performing expensive operations in the background.

Apart from the coolness and usefulness of Gears (I can now read my Google Reader feeds offline), what also caught my attention was its use of a database to store and access data. After all, that’s what databases are good at, data storage and management. I was curious to know what type of a database Gears uses. Well, according to its database module API, Google Gears uses the open source SQLite database system.

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

SQLite is really interesting. There is no set up procedure to initialize it before using it. Databases need minimal or no administration. There is no need to maintain a separate server process dedicated to SQLite. It supports ACID and is thread-safe.

SQLite supports a large subset of SQL-92 data definition and manipulation features. You can create tables, indexes, triggers, and views. You can manipulate stored information using INSERT, DELETE, UPDATE, and SELECT SQL constructs. However, some SQL-92 features are not yet supported.

SQLite stores an entire database in a single, ordinary native file that can reside anywhere in a directory of the native file system. Any user who has a permission to read the file can read anything from the database.

What’s also interesting about SQLite is its size. The whole code footprint ranges from 224KB up to 513KB depending on what compiler optimizations are used. I downloaded the pre-compiled SQLite binaries for windows, it is just one 359KB file, sqlite3.exe.

sqlite3 is somewhat similar to Oracle’s SQL*Plus. You can use it to enter SQL commands. For example, from the DOS command prompt, the following creates a database file named eddie.db:

C:\Users\Eddie\Documents\sqlite>sqlite3 eddie.db
SQLite version 3.3.17
Enter “.help” for instructions
sqlite>

Now, let’s do some DML and DDL:

sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values(‘hello!’,10);
sqlite> insert into tbl1 values(‘goodbye’, 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite> CREATE TABLE tbl2 (
   …>    f1 varchar(30) primary key,
   …>    f2 text,
   …>    f3 real
   …>    );
sqlite>

Does SQLite have a data dictionary? Yes it does, and it’s only one table called sqlite_master:

sqlite> select * from sqlite_master;
table|tbl1|tbl1|2|CREATE TABLE tbl1(one varchar(10), two smallint)
table|tbl2|tbl2|3|CREATE TABLE tbl2 (
   f1 varchar(30) primary key,
   f2 text,
   f3 real
   )
index|sqlite_autoindex_tbl2_1|tbl2|4|
sqlite>

Interesting. Going back to Gears, if you are wondering where the location of the database file is, it basically depends on the browser and the platform you are using.

If you are wondering what can SQLite be used for, of course, you have Google Gears as a perfect example. But, there are other appropriate uses as well.

By now, you may be thinking about Oracle Database Express Edition as also being an entry-level, small-footprint database, but hey, SQLite does not even come close to Oracle XE. Each one serves and is suitable for different types of applications. Moreover, do you really think that a 359KB RDBMS can put a dent into a 165MB RDBMS?

Filed in Oracle, Technology with Comments Off | Tags: sql, sqlite, sqlplus, xe

---