Skip to content
February 12, 2011 / Alex Nedoboi

Building a database engine of our own. Part One. Connectivity.


Once upon a time (long time ago) I was consulting a small start-up. The nature of their business needed an SQL-like database with some specific features.

The choice was between -

1. using an RDBMS for simple data storage and then developing those specific features on top of it, or

2. building an application that implements the business logic, and then just incorporating those simple RDBMS features into the application.

Guess what I chose.

Features aside (they were very specific and far from the RDBMS world), the SQL-like functionality would be limited to trivial select’s, frequent insert’s, not too frequent update’s, and very rare delete’s.

All joins would be inner, some basic grouping by, no need for transactions as such.

Now I think you understand why I chose the second path.

Because it would be immense fun!

Really?

Yes.

Next 10-15 post in this blog are going to be about developing a database engine of our own.

The engine that would implement only the core RDBMS functionality, as described above.

Part 1 – the interface

Some databases offer command line interfaces. Some are mostly GUI oriented. Since this has nothing to do with RDBMS functionality, we will limit our DB engine to telnet-like communication.

We’re going to type in commands, and the engine would be printing its responses and result sets on the screen.

Ah, forgot to mention. We are going to develop our database engine in Java. Java, as a programming language, is very good, descriptive, and efficient for educational purposes.

As a first step, let’s put together the connectivity part.

## anSQLdb.java 

import java.net.*;
import java.io.*;
import java.util.*;

public class anSQLdb
{
   static int port = 55555;

   public static void main (String [] args)
   {
      ServerSocket ss = null;

      try
      {
         ss = new ServerSocket(port);
      }
      catch (IOException e)
      {
         System.err.println("BAD-0001 // Could not start : " + e);
         System.exit(1);
      }

      System.out.println("GOOD-0001 // anSQLdb Server started on port " + port + ". Telnet 'help' for help.\n\n");

      while (1<2)
      {
         Socket uc = null; /* user connection */

         try
         {
            uc = ss.accept(); /* user connection to server socket */

            BufferedReader req = new BufferedReader(new InputStreamReader(uc.getInputStream())); /* request */
            OutputStream os = new BufferedOutputStream(uc.getOutputStream());
            PrintStream res = new PrintStream(os); /* response (or, result set) */

            String st = req.readLine(); /* only read the first line */

            if (st == null) continue;

            System.out.println("Request: " + st);

            if (st.equals("help"))
            {
               res.print("Try this:\n\r\n\r select * from all_tables\n\r desc t1\n\r");
            }

            if (st.equals("select * from all_tables")) /* else-if's or readability? */
            {
               res.print("TABLE_NAME\r\n==========\n\rt1\n\rt2\r\nt3\n\r");
            }

            if (st.equals("desc t1"))
            {
               res.print("c1\tint\n\rc2\tvarchar\n\rc3\tdate\n\r");
            }

            res.flush();

            try
            {
               res.close();
               os.close();
               req.close();
            }
            catch (Exception e)
            {
               System.err.println("BAD-003C // Cannot close : " + e);
            }
         }
         catch (Exception e)
         {
            System.err.println("BAD-9999 // Lazy catch : " + e);
         }
      }
   }
}

Now compile and run it –

$ javac anSQLdb.java
$ java anSQLdb

GOOD-0001 // anSQLdb Server started on port 55555. Telnet 'help' for help.

In another window, let’s connect to it and run some commands.

$ telnet dev2 55555

help
Try this:

 select * from all_tables
 desc t1

$ telnet dev2 55555

select * from all_tables
TABLE_NAME
==========
t1
t2
t3

$ telnet dev2 55555

desc t1
c1      int
c2      varchar
c3      date

$

In the first window you should see something like this -

GOOD-0001 // anSQLdb Server started on port 55555. Telnet 'help' for help.

Request: help
Request: select * from all_tables
Request: desc t1

Wow. Our engine already has (data dictionary | information schema) functionality. Well, almost. We shall get there shortly.

About these ads

2 Comments

Leave a Comment
  1. Jiri / Aug 6 2011 01:12

    Thanks for an interesting topic, looking for next articles!

    • Alex Nedoboi / Aug 8 2011 19:19

      No worries. I had drafts for the next two parts, but thought the pace was somewhat uneven – e.g. the second part introduced the concept of a database block, and it seemed too fast. The “new” second part doesn’t come anywhere near database blocks, and it now seems too slow :)

      Hopefully I can find a good trade off some time soon.

      Cheers,
      Alex.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: