Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Port MySQL Compatible PHP Applications to CUBRID


Do you have a PHP application that you really love but currently it cannot be used with CUBRID? If so, this tutorial is created for you! You will learn how to easily change your MySQL compatible data and PHP code so that you can use them to work with CUBRID. It is very interesting and challenging. What you need is just little experience in PHP programming and Database development, and there is one more thing... You need a little bit of patience. Let's GO!

Contents

 

Preparing the Environment

Install CUBRID

At this point it is highly likely that you have CUBRID installed on your machine. In case CUBRID is not installed yet, here are very comprehensive and easy-to-grasp tutorials you can follow.

  1. Download CUBRID
  2. Install CUBRID
  3. First Steps in CUBRID

Install PHP & PHP Library

Before we begin, let's make sure you have PHP correctly installed and configured to work with CUBRID.

  1. We have to Download and Install PHP. This tutorial will explain where we can download PHP from and how we can easily install on either Windows or Linux operating system.
  2. In order to be able to connect to CUBRID using PHP, we have to Install PHP Library for CUBRID.

Getting Started

There are several things that are different for CUBRID and MySQL.

  1. Some data types are different
  2. Some database functions and operators are different
  3. Sometimes queries are slightly different
  4. And, PHP Library function names are a bit different

Below we will help you to get familiar with both of these differences. Don't be afraid. The differences are logical, therefore they are easy to understand. For instance, in most cases it is the matter of adding the prefix "cubrid_" to the existing MySQL compatible PHP function names. That much easy! So, let's move to the most exciting part of this tutorial!

Changing MySQL data types

Here we will explain the first difference, i.e. which data have to be converted into CUBRID compatible data types when moving your data from MySQL to CUBRID. Here is how we convert MySQL key data types to CUBRID. If you have created any column in your MySQL database which are of types indicated under MySQL column in the table below, you need to change those column types to the corresponding types indicated under CUBRID column.

MySQLCUBRIDRemarks
1.BOOLEANBIT
2.int(11)INTEGER
3.TINYBLOB(n)(n <= 256)BIT VARYING(n) or GLOIt is recommended to use BIT VARYING(n) for 1G or less, and GLO for over 1G.
4.BLOB(n) (<= 4G)
5.MEDUIMBLOB (<= 16M)
6.LONGBLOB (<= 4G)
7.YEAR-VARCHAR(4)
8.TEXT(n)STRING
9.SET(n)SETMySQL: n<= 64
CUBRID : No limit for quantity and data type
10.ENUM(..)-MySQL unique type
11.DATETIMETIMESTAMP

Let's illustrate how we should convert data types when migrating data from MySQL to CUBRID. Assume the following examples when we create a table in MySQL.

CREATE TABLE 'ma_table' (
'ma_id' int(11) NOT NULL AUTO_INCREMENT,
'ma_subject' varchar(255) NOT NULL DEFAULT '',
'ma_content' mediumtext NOT NULL,
'ma_time' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
'ma_ip' varchar(255) NOT NULL DEFAULT '',
'ma_last_option' STRING NOT NULL,
PRIMARY KEY (ma_id),
UNIQUE KEY 'fkey1' ('ma_subject','ma_content')
);
CREATE INDEX ma_index ON ma_table (ma_ip);

In CUBRID we have to write slightly different query as shown below in order to create a table.

CREATE TABLE "ma_table" (
"ma_id" INTEGER AUTO_INCREMENT NOT NULL,
"ma_subject" VARCHAR(255) DEFAULT '' NOT NULL,
"ma_content" STRING NOT NULL,
"ma_time" TIMESTAMP DEFAULT timestamp '09:00:00 AM 01/01/1970'    NOT NULL,
"ma_ip" VARCHAR(255) DEFAULT '' NOT NULL,
"ma_last_option" STRING NOT NULL,
PRIMARY KEY ( "ma_id" ),
UNIQUE ( "ma_subject", "ma_content" )
);
CREATE INDEX ma_index ON ma_table ("ma_ip");
  1. In MySQL database/table/column names are enclosed in single quotation marks ( ' ), while CUBRID has double quotation (") convention. For this reason, change single quotation marks ( ' ), which enclose a table name, to double quotation marks ( " ) or remove them at all.
  2. Change int(11) to INTEGER.
  3. Move NOT NULL behind AUTO_INCREMENT.
  4. Change mediumtext and text to STRING.
  5. Change datetime to TIMESTAMP and change its default value to the following format: 09:00:00 AM 01/01/1970
  6. Delete the word key from the unique key so that you have only UNIQUE.

Changing MySQL database functions and operators

CUBRID 2008 R3.0 introduces many new operators and functions identical to those of MySQL. The full MySQL compatibility is expected by the end of 2010. See the following SQL functions.

MySQLCUBRID
1.NOWNOW
2.IFNULL()
SELECT IFNULL(NULL, 10);
IFNULL()
SELECT IFNULL(NULL, 10);
3.LEFT()
SELECT LEFT('CUBRID', 5);
LEFT()
SELECT LEFT('CUBRID', 5);
4.CONCAT
SELECT CONCAT('CU','B','RID');
CONCAT
SELECT CONCAT('CU','B','RID');
5.LIMIT
order by ... limit 1
LIMIT
order by ... limit 1

See below examples how to use MySQL-compatible database functions and operators in CUBRID.

... ORDER BY ... LIMIT $start , $lines
... ORDER BY ... LIMIT 1
DELETE FROM $g4[board_new_table] WHERE (TO_DAYS('$g4[time_ymdhis]')
 - TO_DAYS(bn_datetime)) > '$config[cf_new_del]'

=> DELETE FROM $g4[board_new_table] WHERE (TO_DATE(SUBSTRING('$g4[time_ymdhis]' FROM 1 FOR 10)) - 
TO_DATE(TO_CHAR(bn_datetime,'YYYY-MM-DD'))) > $config[cf_new_del]

Changing MySQL Queries

The third difference among these databases is SQL Queries. However, SQL syntax in CUBRID 2008 R3.0 has also much improvements. There are 25 New SQL Syntax Extensions in R3.0. So most MySQL queries will not need any modifications.

MySQLCUBRID
1.INSERT INTO ... SETINSERT INTO ... SET
2.WHERE 1WHERE 1=1
3.WHERE 1 != 2WHERE 1 != 2
4.WHERE num_col = '1'WHERE num_col = 1
5.SELECT 1 ;SELECT 1 ;

Here is how you can use MySQL-Compatible SQL queries in CUBRID.

INSERT INTO table_a ... SET col_1 = 'ok'
... WHERE po_id = '5' // IF po_id value IS a number, NOT text...
=> ... WHERE po_id = 1 // ... then DELETE single quotation marks.
... WHERE 1
=> ... WHERE 1=1
... WHERE col_1 <> 1
=> ... WHERE col_1 <> 1

Changing PHP Code

The last fourth thing we have to modify when moving data from MySQL to CUBRID, is the way how we connect to CUBRID database using PHP. Let's see what difference might be between PHP Library for MySQL and for CUBRID.

  1. The 'names of the functions might be different: mysql_query vs. cubrid_connect.
  2. The order of the arguments might be different: mysql_connect($host, $user, $pass); vs. cubrid_connect($host, $port, $db, $user, $pass);
  3. Sometime there is no corresponding functionality between MySQL and CUBRID: mysql_insert_id has no CUBRID equivalent.

Look at the table below function differences.

MySQLCUBRID
1.mysql_connect()cubrid_connect()
2.mysql_fetch_array()
mysql_fetch_field()
mysql_fetch_row()
cubrid_fetch()
3.mysql_affected_rows()cubrid_affected_rows()
4.mysql_error_num()cubrid_error_code()
5.mysql_error()cubrid_error_msg()
5.mysql_free_result()cubrid_close_request()
5.mysql_commit()cubrid_commit()
5.mysql_close()cubrid_disconnect()

Here is how you can use PHP Library functions to connect to CUBRID database.

1. mysql_connect()

mysql_connect($host, $user, $pass);
=> cubrid_connect($host, $port, $db, $user, $pass);

2. mysql_insert_id()

$po_id = mysql_insert_id();
=> create serial po_serial;
select po_ serial.next_value from db_root;
insert into ... // use serial value

3. mysql_free_result();

mysql_free_result($result);
=> cubrid_close_request($result);

4. mysql_close()

mysql_commit($conn_handle);
=> cubrid_commit($conn_handle);        // Caution! Connection handle might be disconnected.
comments powered by Disqus
Page info
viewed 4624 times
translations en
Author
posted 3 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
tagged
Share this article