Porting from PHP MySQL
Introduction: When porting MySQL, which is providing service, to CUBRID, it may be needed much effort if there are too much to modify, but it is usually not so difficult. If you develop new CUBRID codes, you may feel somewhat unpracticed at the beginning, but you will get familiar with CUBRID soon.
This document discribes how to generally port from php-based MySQL to CUBRID.
(Make sure to read " Comparison of CUBRID and MySQL " before proceeding.)
Table of Contents
- Back to Top
- Applicable Environments
- Changing MySQL Data Type
- Changing Data Type
- An Example of Converting Table Creation Query
- Changing MySQL Function and Operator
- How to Convert MySQL Key Functions
- An Example of Converting Functions and Operators
- Changing MySQL Query
- How to Convert MySQL Key Queries
- An Example of Converting MySQL Key Queries
- Changing MySQL PHP API
- How to Convert MySQL Key PHP API
- An Example of Converting PHP API
Applicable Environments
This document is applicable to the below environments.
CUBRID 2008 or higher
Configuring PHP Extension to Use PHP API
Changing MySQL Data Type
Changing Data Type
How to convert MySQL key data types is described below. (See the reference for the rest.)
|
MySQL |
CUBRID |
Remarks |
|
BOOLEAN |
BIT |
|
|
TINYBLOB(n)(n <= 256) |
BIT VARYING(n) or GLO |
It is recommended to use BIT VARYING(n) for 1G or less. It is recommended to use GLO for 1G or more. |
|
BLOB(n) (<= 4G) |
||
|
MEDUIMBLOB (<= 16M) |
||
|
LONGBLOB (<= 4G) |
||
|
YEAR |
- |
VARCHAR(4) |
|
TEXT(n) |
STRING |
|
|
SET(n) |
SET |
MySQL: n<= 64 CUBRID : No limit for quantity and data type |
|
ENUM(..) |
- |
MySQL unique type |
|
DATETIME |
TIMESTAMP |
An Example of Converting Table Creation Query
To show various conversion examples, a virtual table is created.
MySQL table creation query
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);
Table creation query converted to CUBRID
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. Change single quotation marks ( ' ), which enclose a table name, to double quotation marks ( " ) or delete it.
2. Change single quotation marks ( ' ), which enclose a column name, to double quotation marks ( " ) or delete it.
3. Change int(11) to integer.
4. Move not null behind auto_increment.
5. Change mediumtext and text to string.
6. Change datetime to timestamp and change its default to the following format:
TIMESTAMP '09:00:00 AM 01/01/1970'
7. Delete key from the unique key.
Changing MySQL Function and Operator
How to Convert MySQL key functions
(See the reference for the rest.)
|
MySQL |
CUBRID |
|
NOW |
SYSTIMESTAMP |
|
IFNULL() SELECT IFNULL(NULL, 10); |
NVL() SELECT NVL(NULL, 10) FROM db_root; |
|
LEFT() SELECT LEFT('CUBRID', 5); |
SUBSTRING() SELECT SUBSTR ('CUBRID', 1, 5) FROM db_root; |
|
CONCAT SELECT CONCAT('CU','B','RID'); |
Use + operator or || operator SELECT 'CU' || 'B' || 'RID' FROM db_root; |
|
LIMIT order by ... limit 1 |
ORDERBY_NUM() Order by ... ORDERBY_NUM() |
Convert existing MySQL functions by referring to the above conversion method.
An Example of Converting Functions and Operators
An Example of Converting Functions and Operators
... order by ... limit $start , $lines
=> ... order by ... FOR ORDERBY_NUM() BETWEEN $start AND $lines +$start
... order by ... limit 1
=> ... order by ... FOR ORDERBY_NUM()=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 Query
How to convert MySQL key queries is described below. (See the reference for the rest.)
How to Convert MySQL Key Queries
|
MySQL |
CUBRID |
|
INSERT INTO ... SET |
INSERT INTO ... VALUES |
|
WHERE 1 |
WHERE 1=1 |
|
WHERE 1 != 2 |
WHERE 1 <> 2 |
|
WHERE num_col = '1' |
WHERE num_col = 1 |
|
SELECT 1 ; |
SELECT 1 FROM db_root; |
An Example of Converting MySQL Key Queries
An Example of Converting Query
insert into table_a ... set col_1 = 'ok'
=> insert into table_a(col_1) values('ok')
... where po_id = '5' // po_id When an properties is a number
=> ... where po_id = 1 // Delete single quotation marks
... where 1
=> ... where 1=1
... where col_1 <> 1
=> ... where col_1 <> 1
Changing MySQL PHP API
How to Convert MySQL Key PHP API
How to Convert MySQL Key PHP API
|
MySQL |
CUBRID |
|
mysql_affected_rows() |
cubrid_affected_rows() |
|
mysql_close() |
cubrid_disconnect() |
|
mysql_connect() |
cubrid_connect() |
|
mysql_error_num() |
cubrid_error_code() |
|
mysql_error() |
cubrid_error_msg() |
|
mysql_fetch_array mysql_fetch_field mysql_fetch_row |
cubrid_fetch() |
An Example of Converting PHP API
cubrid+php
1. mysql_connect()
mysql_connect($host, $user, $pass); => cubrid_connect($host, $port, $db, $user, $pass);
2. my_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($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.