January 18, 2013

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.

2 comments:

  1. Really cool! I've never thought someone would implement a native Python driver for CUBRID Database.

    ReplyDelete
    Replies
    1. Sooner or later, we will introduce in CUBRID a native Python driver. I think it is a great idea since a native driver could be much more faster the a cci based one and this might be a great starting point! Thank you for this suggestion!

      Delete