CUBRID – Open Source RDBMS Highly Optimized for the Web

Esen Sagynov, NHN Corp., www.cubrid.org

CUBRID is a comprehensive open source relational database management system highly optimized for Web Applications. Implemented in C programming language, CUBRID provides great scalability and High Availability features recognized by industry leaders, in addition to almost full SQL compatibility with MySQL.

Web Site: http://www.cubrid.org

Latest Version: CUBRID 8.4.1 Beta (2012/01/31)

License & Pricing: GPL v2+ (Server), BSD (APIs, Tools).

Community Support: http://www.cubrid.org/questions, http://www.cubrid.org/forum

Overview

CUBRID Database project was started in 2006 by NHN Corporation, the leading IT services provider in Korea. Developed from scratch the first public release was announced in October 2008. As of today there have been 15 releases announced in the interval of two to six months. It is one of the most active and dedicated open source projects available under the terms of GPL v2.0 and higher.

• The primary goal of CUBRID project is to develop a relational DBMS optimized for Web services. Thus, Web optimizations are in the core of CUBRID which has the following key features:
• Fully transactional, ACID compliant
• ANSI SQL
• Referential Integrity
• B+tree, covering index
• Table joins (INNER, LEFT, RIGHT, OUTER, UNION, MERGE)
• Nested and Hierarchical queries
• LOB data support (BLOB/CLOB)
• True VARCHAR
• Conditional Regular Expressions
• Over 90% SQL compatibility with MySQL
• Query plan caching
• Cursors, Triggers and Stored Procedures
• Unicode support0-
• Cross-platform
• Multi-process architecture and multi-threaded server implementation
• Native API for PHP, PDO, Python, Ruby, Perl, ODBC, OLEDB, ADO.NET, C.
• Native Database Administration and Migration Tools.
• Command line database management utilities and SQL Interpreter.
• High-Availability feature for 24/7 uninterrupted service availability highly recommended by leading IT and Communication providers. (Master : Slave architecture)
• Native Connection Pooling (can also be used with Oracle and MySQL)
• Load balancing and distribution
• Database Sharding for automatic scale-out – coming in the next version.
• Synchronous/asynchronous/semi synchronous, one-way, transaction-level, schema independent, chained or grouped replication
• Data Import/Export
• Online/Hot, Offline, and Incremental Backup with an opportunity to schedule and adjust based on the needs.
• Unlimited databases, tables and rows

CUBRID is a fully featured Enterprise level RDBMS available completely for free and no licensing fees. It is a reliable solution ideal for Web services.

Installation

Installing CUBRID is pretty straightforward. Below you can see the installation instructions for Ubuntu. Other Linux, Windows, Shell and RPM installation instructions are also available on the community wiki site.

To install CUBRID using apt-get on Ubuntu, we need to add CUBRID's repository so that Ubuntu knows where to download the packages from, and then tell the OS to update its indexes.

sudo add-apt-repository ppa:cubrid/cubrid

sudo apt-get update

Now install the latest version of CUBRID:

sudo apt-get install cubrid

Done. This will automatically create cubrid user and a group to manage CUBRID services. To complete the installation and set the CUBRID PATH variables, restart your OS. CUBRID Service will be set to auto start on system reboot.

Authentication in CUBRID

CUBRID provides two levels of authentication: host and database.

Host Authentication is similar to those of other RDBMS. To administer CUBRID Host Server from CUBRID Manager (CM), you need to enter admin’s username and password. When you try to connect to a host from CM for the first time, you will be prompted to set admin credentials. Once you connect to a host you can manage the Server depending on the permissions you have.

Once you are successfully connected to a host, you cannot simply start looking into each database available on this host even if you are a top-level database server administrator. You need to login to any database you want to access. Thus, an administrator of the database A successfully connected to a host will not have access to a database B existing in the same host, unless granted. This provides independence and added layer of security.

Creating a Database

There are several ways you can create a database. You can use CUBRID Manager (CM), an open source GUI based database administration tool developed with DBAs in mind, or CUBRID Query Browser (CQB), a lightweight version of CM oriented for developers, or CQB plugin for Eclipse IDE. Other options can be using programming APIs like PHP, Ruby, Python, Perl, ADO.NET, etc.

In this tutorial we will use cubrid createdb utility, which provides the necessary means to create a database in the command line. Typing cubrid createdb by itself will display various options you can use with this command.

Now let’s create a database called world_database.

cubrid createdb world_database

Notice, that a database name should not contain "@" symbol since later when referencing the database name, @ will be interpreted as if a host name is specified. So avoid this kind of scenarios.

The default owner of the database, when created, is dba with a blank password.

Starting a Database

CUBRID provides one more convenient feature. You can control which databases need to be started. If you do not use a particular database, you can stop it to save the system resources. This feature is very useful especially in the development environment when you work on several projects simultaneously which use different databases. In this case, when you work on one of the projects, you can start that project’s database(s) only. No need to spare the resources with other databases if you do not use them.

To start a database type the following command in the terminal. Remember that all these operations can also be performed in GUI based CUBRID Manager, if you prefer.

cubrid server start world_database

SQL in CUBRID

SQL syntax in CUBRID is very alike to those of MySQL and even Oracle. You can execute most of your existing SQL statements in CUBRID without any modifications. Though, there are a few important facts to know about CUBRID.

CUBRID is not a fork of MySQL (or any other DBMS, for that matters), therefore it does not have anything similar like ENGINE=InnoDB part in CREATE TABLE statement. So, if your SQL includes it, just remove it.

Take a quick look at the reserved words in CUBRID. If your project uses some of them as table or column names, you need to always quote them using backticks (``), or double quotes (""), or square brackets ([]), whenever they are referenced.

If you ever use Large Objects (BLOB/CLOB), in CUBRID they are stored outside the database on the external storage while the references to those files are stored in database columns. You are highly encouraged to read how LOB data should be used and maintained in CUBRID.

As of version 8.4.1 CUBRID does not support UNSIGNED, BOOL, ENUM data types, if you want to use them. They will be implemented in the next 8.5.0 version. You can find more information on recommended data type mapping at CUBRID community site.

Also pay attention to permitted maximum and minimum values for data types.

CUBRID uses B+tree indexes to improve search performance

• Works great for equality operator (=).
• Since indexes are represented as trees, Key range scan allows to significantly improve the search performance if WHERE clause contains range conditions (<, >, <=, >=, =). If Range conditions are not defined, the Optimizer will attempt to perform sequential table scan.
• Try to avoid using irregular conditions such as <>, !=, or NULL, since the Optimizer will not be able to take the full advantage of the index tree, and instead will perform a sequential scan.
• Covering Index is magic. Use it to improve the search performance.
• If possible, utilize LIMIT clauses to take advantage of Key limit optimizations.
• Learn about In-place Sorting feature in CUBRID to improve the performance of ORDER BY statements.
• Use COUNT(*) instead of COUNT(col_name), unless you really know what you are doing.

Take advantage of CUBRID’s unique Click Counter feature. For example, instead of executing two separate SQLs like:

SELECT article FROM article_table WHERE article_id = 130,987;

UPDATE article_table SET read_count = read_count + 1 WHERE article_id = 130,987;

… in CUBRID you can execute only one:

SELECT article, INCR(read_count) FROM article_table WHERE article_id = 130,987

This will help you avoid expensive lock of the working record generated by UPDATE operation.

There are many resources you can refer to on the community site to learn more about SQL in CUBRID and query optimizations. You are highly encouraged to refer to them.

CUBRID APIs and Tools

A large variety of drivers are available for CUBRID developers. Refer to CUBRID APIs Wiki to download the necessary driver. On the download page you can also find the installation instructions and quick start guides.

CUBRID Manager (CM) is a great and powerful tool to manage databases developed with DBAs in mind. You can use it for both local and remote database administration. If you usually perform just basic database management, you may want to use the lighter version of CM called CUBRID Query Browser (CQB).

Additionally, if you think of migrating your databases from other DBMS to CUBRID, consider using easy-to-use CUBRID Migration Tool (CMT), which provides a smooth bridge between your databases.

All these tools are open sourced and available for download at CUBRID Tools Wiki. You are highly encouraged to visit Resources for CUBRID Developers.

At this point it is fair to wrap up this introduction of CUBRID, an open source relational DBMS highly optimized for Web applications.

If you are interested what other users are asking about CUBRID, feel free to visit CUBRID Q&A site. If you encounter any issues, post your message to CUBRID Forum.

References

Community site http://www.cubrid.org

SF.net project site: http://sourceforge.net/projects/cubrid

---

More Database Knowledge

Database Tutorials and Videos

---

Click here to view the complete list of tools reviews

This article was originally published in the Spring 2012 issue of Methods & Tools