Tag: sql

Pipes, Creative Commons photo by flattop341.

1. The Problem

Internet is full of long urls and meaningless.

Long urls are difficult to remember or print, usually full of redundancy and low semantic. With short and meaningful urls you can avoid thes problems and even achieve profitable goals with SEO
SEO (search engine optimization) technics.

There are services like Tiny URL, Fancy URL, Moo URL and others. Although they solve part of the problems, they bring several others. Another problem is if you have a web site like example.com and use a third-party service for short urls you are losing part of your mind-share with your users and clients.

As an example, if a example.com company wants to promote a open work position would be preferable spread a example.com/jobs instead of a tinyurl.com/examplejobs, or even worst, a tinyurl.com/3i4i592 (meaningless hash).

2. Solution Approach

I created a little program called xort that can be placed on your own server and provide you own short maintening your base url.

I use a pipe abstraction. Each pipe redirects from a key url to an output url.

The idea is that you have xort installed and associated into your domain (preferably on /x). A pipe inside example.com would be like example.com/x/jobs.

3. Tools

All those tools are multi platform, open source and free.

3.1 Glassfish Application Server

Glassfish is an open source application server project led by Sun Microsystems for the Java Enterprise Edition (Java EE) platform. It’s very easy to install and run and have a very nice administration web interface where you can do from simple tasks like deploy a application to more complexes like clustering.

Glassfish Admin Console

To develop the application I’m using NetBeans 6.5 Beta that comes with Glassfish V3 prelude b15b. Netbeans also provides a integration of project, database and web server.

Nevertheless, Glassfish has no dependencies with any IDE and perfectly works by alone. If you need I wrote this post explaining how to install and deploy a application on Glassfish from scratch.

3.2 MySQL Relational Database

MySQL is a relational database management system and probably the most used database on internet (has more than 11 million installations). It’s also very easy to install and administer, through command line or many gui interfaces.

To install MySQL and JDBC driver on Ubuntu just run as root:

# apt-get install mysql-server libmysql-java

After installing and configuring it you can test the jdbc driver throught this servlet code. You can optionally register the MySQL on NetBeans to have a easier access to it thought the service tab.

At the command line you can invoke mysql command line interface and use MySql commands or SQL queries. I’ll login and create a database called xort:

$ mysql -u username -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.51a-3ubuntu5.3 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database xort;
Query OK, 1 row affected (0.06 sec)

You could also create this database by an SQL statement:

CREATE DATABASE xort;

To select the database xort:

mysql> use xort;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Now we create a database called pipes with fields pin (pipe in) and pout (pipe out). They represent the input url and the output url of our pipe abstraction.

CREATE TABLE pipes (
   pin varchar(255) NOT NULL,
   pout varchar(255)
);

As we expect a lot of searches queries on this table, we can optionally create a index for it on pin field. This can reduce ours searches from O(n) to O(logn) (because pin’s will be ordered so don’t need to look all pipes, we can use logn algorithms like binary search).

CREATE INDEX pinindex ON pipes (pin);

Another trick to improve our speed is recycling connections through connection pools.

Creating a pool of MySQL connections on Glassfish is very easy. There’re two good tutorials on this subject:

• Glassfish v2 UR1 and MySQL Connection Pool
• Connection pooling with MySQL Connector/J

And now we populate the database with some initial pipes.

INSERT INTO pipes VALUES ('blog','http://silveiraneto.net');
INSERT INTO pipes VALUES ('cejug','http://cejug.org/display/cejug/Home');
INSERT INTO pipes VALUES ('orkut','http://www.orkut.com.br/Main#Profile.aspx?rl=ls&uid=12443310329436634134');
INSERT INTO pipes VALUES ('glassfish','http://glassfish.dev.java.net');
INSERT INTO pipes VALUES ('mysql','http://dev.mysql.org');
INSERT INTO pipes VALUES ('twitter','http://twitter.com/silveira');
INSERT INTO pipes VALUES ('lab', 'http://maps.google.com/maps?f=q&geocode=&q=campus+do+pici&g=Fortaleza,+Brazil&ie=UTF8&t=h&ll=-3.745978,-38.574023&spn=0.002452,0.004823&z=18');
INSERT INTO pipes VALUES ('videos', 'http://br.youtube.com/user/NetoSilveira');
INSERT INTO pipes VALUES ('photos', 'http://flickr.com/photos/silveiraneto/');

4. Program

Basically we have just a program that implement this simple behavior:

1. separate the key from the url.
2. if the key is a pin from a pipe then redirect to that pout.
3. else provide a way to create a new pipe.
4. list all pipes.
5. provide a way to remove a pipe.

To get the key we need to separate the proper part of the request uri:

String uri = request.getRequestURI();
String key = uri.substring(request.getContextPath().length()+1);

After that we check if it matches with a pin of some pipe. In this case we redirect user for the correspondent pout:

response.sendRedirect(pout);

Notice that using this approach we can connect a url to a extern or intern url (even to another pipe).

5. Download

Check out the xort project and sources at xort.dev.java.net:

• Releases.
• Browse Source Code.

Or grab sources and the current build with:

svn checkout https://xort.dev.java.net/svn/xort/trunk xort

Parameters can be passed by the the web.xml file:

   Set if users can add new pipes using the web interface.
allowNewPipes
true

   JDBC driver to use
driver
com.mysql.jdbc.Driver

   Username to login on the database.
username
root

   Password for the given username.
password
yourpassword

   JDBC path to database.
database
jdbc:mysql://localhost:3306/xort

Xort up and running:

[youtube]b2Mrk8XfvKk[/youtube]