My name is CUBRID and I talk TCP/IP with Python!

Hi!

Today it’s Python time! :)

If you had a chance to take a look at the latest CUBRID Node.js driver, you probably noticed that it is a 100% pure native driver! What does this mean...? It means that everything is implemented in JavaScript, without the need of any (CUBRID) API libraries... ...Meaning also that there is no need to compile the driver, have separate OS releases and so on...

And how can this be done? Simple - the driver speaks TCP/IP directly to the DBMS...! Is that cool or what…?! :)

Immediately a question crossed my mind: Is this a general CUBRID policy, or it just happened with the Node.js driver?

A simple check of the CUBRID SVN revealed the followings:

CUBRID Drivers which uses TCP/IP to communicate directly with the database engine:

• Node.js
• .NET
• JDBC

CUBRID Drivers which relies on CUBRID API CCI library background calls:

• Python
• PHP
• PDO
• Ruby
• Perl
• OLE DB

Hmm... Interesting - so there are a few other drivers out there using TCP/IP...!

...And the BIG question is: Why not also Python...? I mean, Python does feature a nice TCP/IP module (socket), so why not...?!

OK – it’s time to go to work! Let's see what we need to try this:

1. A tryout plan… :)
2. The inner CUBRID communication protocol

The plan is actually quite simple:

• Connect to the demodb database
• Do something very simple: get a database parameter, for example he database engine version
• Disconnect

…And now the bad news... :( The CUBRID communication protocol is not (yet?) documented!!

Yes – exactly!! The CUBRID communication protocol is not documented!!

…There is absolutely no documentation available about how to talk to a CUBRID database using just TCP/IP... So what can we do...? 

...Well, there is a way – the hard way!! …I decided I will simply reverse engineer the Node.js driver and extract the information I need from its source code!

I’ll be honest with you - I spent quite some time on this… …but the good news is that I did it! :D

To cut it short, this is how it works with CUBRID:

• You connect first to the CUBRID Broker, using the standard ports 30000 or 33000
• Next you connect to the database, using the (sometimes new) port returned by the CUBRID Broker
• Once the connection is successful, you send commands and interpret the results
• Finally, you disconnect from the database

Hey,  this is a small disclaimer for the Python die-hard fans (greetings, btw!) :) I will not do some fancy-heavy-optimized-best-Python code below, just the bare minimum to test a TCP/IP communication with CUBRID...

Let’s see...

This is how you can connect to the CUBRID Broker:

print 'Requesting CAS port...'

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)

s.connect((HOST, PORT))

s.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)

send_buffer = 'CUBRK%c%c%c%c%c' % (chr(3), chr(CAS_VER), chr(0), chr(0), chr(0))

s.sendall(send_buffer)

response = s.recv(INT_SIZEOF)

port = 0

for i, c in enumerate(response):

    port += ord(c) * int(math.pow(256, 3 - i))

print 'Got a CAS port: %d' % port

This is how you can connect to the standard demodb database:

print 'Connecting...'

database = DATABASE.ljust(32, chr(0))

user = USER.ljust(32, chr(0))

password = PASSWORD.ljust(32, chr(0))

url = ''.ljust(512, chr(0))

filler = ''.ljust(20, chr(0))

send_buffer = '%s%s%s%s%s' % (database, user, password, url, filler)

s.sendall(send_buffer)

...And this is how we request the database version:

print 'Requesting database engine version...'

packetLen = BYTE_SIZEOF + INT_SIZEOF + BYTE_SIZEOF

packetLenArr = num_to_chr_array(packetLen, INT_SIZEOF)

send_buffer = '%c%c%c%c%c%c%c%c%c%c%c%c%c%c' % (

    packetLenArr[0], packetLenArr[1], packetLenArr[2], packetLenArr[3],

    CAS_INFO[0], CAS_INFO[1], CAS_INFO[2], CAS_INFO[3],

    CAS_FC_GET_DB_VERSION,

    0, 0, 0, BYTE_SIZEOF,

    1)

s.sendall(send_buffer)

Let’s skip the rest of the Python code, and just show the final application output:

Requesting CAS port...

Got a CAS port: 33001

Connecting...

Session id: 1049.

Connect successful.

Requesting database engine version...

Database engine version is: 8.4.1.5002.

Closing connection...

Connection closed.

All done.

Greaaat! We did it – we talked TCP/IP with CUBRID! :)

A final thought - so where does this leaves us...?

I think the answer is to have someday a 100% native CUBRID Python driver, which would use only TCP/IP to communicate with CUBRID! What do you think...?

See you next time – Bye-bye! :)

/Dee

P.S. If you are interested in the full Python code for this post, just let me know.

The Good, The Bad, and The Ugly

Hi!

First of all, HAPPY NEW YEAR! Wish you a year full of great SQL coding! :)

This post is a little different - it is not a "techno-post" as usual, but more of a looking back and gathering about what was good, what was bad and what was ugly last year in the world of some well-known SQL databases… ...Let’s start!

MySQL

The good

• New releases, and the “inner” message that MySQL is (still) alive and doing great!

• The special attention they are giving up lately to the Windows world. Check out the new Excel add-in, the Windows smart installer, the new planned Node.js driver and others.
• LAMP still rulz the world! :)

The bad

• The releases pace is too slow for the #1 open-source database! We need more and more improvements and features, especially when looking at the big community out there!
• Non-linear performance scaling, but the good news is that there are some improvements.

The ugly

• The unknowns about the future of MySQL... Will it still be out there in 3 years from now?
• The too small, quite non-existing improvements on query plans engine…

SQL Server

The good

• The Azure! SQL Server does absolutely great in terms of Cloud support, they are simply #1 in this area!
• The freeware, the open-source support! If this is a trend for the future, than great job, Microsoft!
• This worth mentioning a thousand times – the 100% freeware SQL Server Compact release. Simply amazing for Windows solutions…!
• Their web site! A model of simplicity and great design – I love it!

The bad

• No Linux, no OSX…
• Price… Installing SQL Server 2012 on a Windows Server 2012 and using a domain controller and so on is still too expensive for a small business…
• Windows 8! I know, I know! - this has nothing to do with SQL, but please be kind and indulge me this one rant… ha-ha :)

The ugly

• Any SQL thingy can you run on Microsoft Surface…?!?

PostgreSQL

The good

• The 9.x releases
• Great support for events, trainings, online publishing etc.
• A strong community of die-hard fans
• Geospatial support (PostGIS)
• Cloud support

The bad

• Still too less publicity for what this database true value stands for!
• No Amazon RDS support (yet)
• Still has a reputation of poor Windows support when compared to Linux; actually, this is not true anymore, but PostgreSQL should do much more to advertise their achievements in the Windows arena.

The ugly

• The feeling that “you need to know what you’re doing” if you ever intend to use PostgreSQL… …the feeling that it’s simply not a solution for the casual beginner…

…and we got to CUBRID

The good

• CUBRID had a strong finish last year, with the 9.0 release. Keeping a fast releases pace is really great, especially with so many improvements and new features added!
• Finally, we have proper multilingual support into CUBRID! …long-long awaited…
• We have a Node.js driver!
• Sharding support!

The bad

• The web site search still does not work the way it should… ...too much “not what I searched for”, too many low significant search results, too many “version-oriented” results.
• No published vision about what’s going on in the future…! Hey - what is the plan for 2013 and beyond, guys…? Any NoSQL support…? Any new drivers…? Tell us, please! :)
• No GIS support, no data warehousing (Cubes/OLAP) support
• No Cloud

The ugly

...Well, I have one big time favorite here – the www.cubrid.org website itself! Yes, it looks "old", it looks “outdated”… The CUBRID web site really needs a facelift…! …Take a look here or here, for example, for some modern interface designs.

One last thing – some food for thoughts at the beginning of 20123…   What do you think about Service Oriented Database Architecture? Do you think this is the next big thing after NoSQL? Do you think that maybe database services is the next big step in databases architecture?

That’s it for my first post this year – let me know what do you think!

See you next time – Bye-bye!

/Dee

P.S. If you have some time to spare, consider taking a look at Oracle XE… …You might have a nice surprise (and yes – it’s 100% freeware!)…