29.Oct.2015: Note
Thanks for your reading of my old contents.
It was written in a few years ago, so I think that there is no value already.
If you want new information, please send me e-mail.
If there are many requests, I'll try it again.
And if you are interested in PostgreSQL,
please read my new document about PostgreSQL internals.
Regards,
Feasibility study of PostgreSQL support
In order to support PostgreSQL, I made a feasibility study for about couple of weeks.
I write the conclusion first:
I do not complete the installation yet,
because there are many difficulties which due to the MySQL's expanded functions.
However, I think that supporting PostgreSQL is certainly feasible.
Findings and Problems
But not all, I think I got some findings and was able to extract a lot of problems.
I used PrestaShop Version 1.5.3.1.
1. Back quote (`) problem (non-standard: MySQL expand)
The first difficulty was a huge number of back quote(`).
For example, the following SELECT query does not work on PostgreSQL (also Oracle).
[< MySQL >] SELECT `language_code` FROM `'._DB_PREFIX_.'lang` WHERE `iso_code` = \''.pSQL(strtolower($iso_code)).'\''
I had to rewrite this as follow:
[< PostgreSQL >] SELECT language_code FROM '._DB_PREFIX_.'lang WHERE iso_code = \''.pSQL(strtolower($iso_code)).'\''
It's simple. However, deletion of a huge number of back quote was really hard work.
I think all should be deleted.
2. Table Definitions
I was able to rewrite all CREATE TABLE queries in "install/data/db_structure.sql".
There was no problem.
2.1. Data Type
- UNSIGNED attribute
PostgreSQL does not support UNSIGNED attribute. Therefore, I was emulated using check constraint.
[< MySQL >]
CREATE TABLE unsigned_table (
col int unsigned
);
[< PostgreSQL >]
CREATE TABLE unsigned_table (
col int check(0 <= col)
);
All auto_increment integer type have been rewritten to Serial type.
[< MySQL >]
CREATE TABLE test_table (
col int auto_increment,
data int
);
[< PostgreSQL >]
CREATE TABLE test_table (
col serial,
data int
);
/* for last_insert_id() function */
ALTER SEQUENCE IF EXISTS test_table_col_seq RENAME TO test_table_auto_increment;
PostgreSQL does not support ENUM type.
I made new types using CREATE TYPE queries.
For instance,
MySQL’s CREATE TABLE query below is rewritten to PostgreSQL's CREATE TABLE and CREATE TYPE queries.
[< MySQL >]
CREATE TABLE `new_type_test` (
`id` ENUM('red', 'green', 'blue') NOT NULL DEFAULT 'red'
);
[< PostgreSQL >]
CREATE TYPE color_type AS ENUM ('red', 'green', 'blue');
CREATE TABLE new_type_test (
id color_type NOT NULL DEFAULT 'red'
);
I made nine types below:
CREATE TYPE group_type AS ENUM ('select', 'radio', 'color');
CREATE TYPE type AS ENUM('products', 'categories', 'attributes', 'manufacturers', 'suppliers');
CREATE TYPE status AS enum('open','closed','pending1','pending2');
CREATE TYPE redirect_type AS ENUM('', '404', '301', '302');
CREATE TYPE condition AS ENUM('new', 'used', 'refurbished');
CREATE TYPE visibility AS ENUM('both', 'catalog', 'search', 'none');
CREATE TYPE reduction_type AS ENUM('amount', 'percentage');
CREATE TYPE method AS enum('GET','POST','PUT','DELETE','HEAD');
CREATE TYPE management_type AS ENUM('WA', 'FIFO', 'LIFO');
2.2. CREATE TABLE
In the table definitions, indices have to be defined separately.
I show two examples:
[< MySQL >]
CREATE TABLE `PREFIX_accessory` (
`id_product_1` int(10) unsigned NOT NULL,
`id_product_2` int(10) unsigned NOT NULL,
KEY `accessory_product` (`id_product_1`,`id_product_2`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8;
[< PostgreSQL >]
CREATE TABLE PREFIX_accessory (
id_product_1 int NOT NULL check(0 <= id_product_1),
id_product_2 int NOT NULL check(0 <= id_product_2)
);
CREATE INDEX PREFIX_accessory_accessory_product1 ON PREFIX_accessory (id_product_1,id_product_2);
[< MySQL >]
CREATE TABLE `PREFIX_attribute_impact` (
`id_attribute_impact` int(10) unsigned NOT NULL auto_increment,
`id_product` int(11) unsigned NOT NULL,
`id_attribute` int(11) unsigned NOT NULL,
`weight` DECIMAL(20,6) NOT NULL,
`price` decimal(17,2) NOT NULL,
PRIMARY KEY (`id_attribute_impact`),
UNIQUE KEY `id_product` (`id_product`,`id_attribute`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8;
[< PostgreSQL >]
CREATE TABLE PREFIX_attribute_impact (
id_attribute_impact serial NOT NULL ,
id_product int NOT NULL check(0 <= id_product),
id_attribute int NOT NULL check(0 <= id_attribute),
weight DECIMAL(20,6) NOT NULL,
price decimal(17,2) NOT NULL,
PRIMARY KEY (id_attribute_impact),
CONSTRAINT id_product UNIQUE (id_product,id_attribute)
);
3. INSERT
There are some problems. Especially the Db::insert method is troublesome.
3.1. INSERT ON DUPLICATE KEY UPDATE (non-standard: MySQL expand)
INSERT ON DUPLICATE KEY UPDATE queries are used in about 10 places.
This statement is non-standard, but those were able to rewrite easily.
I show one example:
[< Original: classes/Product.php:3146 >]
public static function addFeatureProductImport($id_product, $id_feature, $id_feature_value)
{
return Db::getInstance()->execute('
INSERT INTO `'._DB_PREFIX_.'feature_product` (`id_feature`, `id_product`, `id_feature_value`)
VALUES ('.(int)$id_feature.', '.(int)$id_product.', '.(int)$id_feature_value.')
ON DUPLICATE KEY UPDATE `id_feature_value` = '.(int)$id_feature_value
);
}
[< After change >]
public static function addFeatureProductImport($id_product, $id_feature, $id_feature_value)
{
$count = Db::getInstance()->getValue('SELECT count(*) FROM '._DB_PREFIX_.'feature_product
WHERE id_feature = ' . (int)$id_feature . ' AND id_product = '. (int)$id_product);
if ($count == 0) {
return Db::getInstance()->execute('INSERT INTO '._DB_PREFIX_.'feature_product (id_feature, id_product, id_feature_value)
VALUES ('.(int)$id_feature.', '.(int)$id_product.', '.(int)$id_feature_value.')');
}
else {
return Db::getInstance()->execute('UPDATE '._DB_PREFIX_.'feature_product
SET id_feature_value = ' . (int)$id_feature_value . '
WHERE id_deature = ' . (int)$id_feature . ' AND id_product = ' . (int)$id_product);
}
}
3.2. INSERT IGNORE (non-standard: MySQL expand)
First of all, I can not understand why MySQL supports INSERT IGNORE statement.
INSERT IGNORE statement is also non-standard, and it can be rewritten in the same way as the REPLACE statement in principle.
- Explicit INSERT IGNORE
INSERT IGNORE queries are used in more than twenty places, they are rewritable.
I show two examples.
[< Original: classes/Country.php:358 >]
$sql = false;
foreach ($shops as $id_shop)
foreach ($countries as $country)
foreach ($modules as $module)
$sql .= '('.(int)$module['id_module'].', '.(int)$id_shop.', '.(int)$country['id_country'].'),';
if ($sql)
{
$sql = 'INSERT IGNORE INTO `'._DB_PREFIX_.'module_country` (`id_module`, `id_shop`, `id_country`) VALUES '.rtrim($sql, ',');
return Db::getInstance()->execute($sql);
}
else
return true;
[< After change>]
foreach ($shops as $id_shop) {
foreach ($countries as $country) {
foreach ($modules as $module) {
$sql = 'INSERT INTO '._DB_PREFIX_.'module_country (id_module, id_shop, id_country) VALUES ';
$sql .= '('.(int)$module['id_module'].', '.(int)$id_shop.', '.(int)$country['id_country'].')';
Db::getInstance()->execute($sql);
}
}
}
return true;
[< Original: classes/shop/Shop.php:1039 >]
foreach()
{
...
$keys = implode('`, `', array_keys($res));
$sql = 'INSERT IGNORE INTO `'._DB_PREFIX_.$table_name.'` (`'.$keys.'`, `id_category_default`, '.$id.')
(SELECT `'.$keys.'`, '.(int)$categories[0].', '.(int)$this->id.' FROM '._DB_PREFIX_.$table_name.'
WHERE `'.$id.'` = '.(int)$old_id.')';
...
}
Db::getInstance()->execute($sql);
[< After change >]
foreach()
{
...
$keys = implode(', ', array_keys($res));
$sql = 'SELECT '.$keys.', '.(int)$categories[0].', '.(int)$this->id.'
FROM '._DB_PREFIX_.$table_name.' WHERE '.$id.' = '.(int)$old_id;
$result = Db::getInstance()->executeS($sql);
foreach($result as $row) {
$sql = 'INSERT INTO '._DB_PREFIX_.$table_name.' ('.$keys.', id_category_default, '.$id.') VALUES(';
foreach($keys as $key)
$sql .= '\'' . $row[$key] . '\', ';
$sql .= '\'' .(int)$categories[0]. '\', ' . (int)$this->id . '\')';
Db::getInstance()->execute($sql);
....
}
- Note: I omitted error handling of both.
INSERT IGNORE statement is used in the Db::insert method.
To support PostgreSQL, we have to fundamentally rethink how to use this method.
3.3. "INSERT INTO table () VALUES ()" query problem (non-standard: MySQL expand)
When installing PrestaShop, the INSERT statement with no value is executed. I show database's log file:
15 Query INSERT INTO `ps_profile` () VALUES ()
Of course, this is non-standard INSERT statement,
only MySQL is able to execute this.
I have no idea how to change, but it is not impossible, I think.
4. REPLACE (non-standard: MySQL expand)
REPLACE statement is useful, but this is non-standard. Fortunately, it has not been used much.
- Explicit REPLACE statement
There are two explicit REPLACE statements in the source code. Rewriting was somewhat complicated.
[< Original classes/ProductSale.php:33 >]
public static function fillProductSales()
{
$sql = 'REPLACE INTO '._DB_PREFIX_.'product_sale
(`id_product`, `quantity`, `sale_nbr`, `date_upd`)
SELECT od.product_id, COUNT(od.product_id), SUM(od.product_quantity), NOW()
FROM '._DB_PREFIX_.'order_detail od GROUP BY od.product_id';
return Db::getInstance()->execute($sql);
}
[< After change >]
public static function fillProductSales()
{
$ret = true;
$sql = 'SELECT od.product_id as id_product, COUNT(od.product_id) as quantity,
SUM(od.product_quantity) as sale_nbr, NOW() as date_upd FROM '._DB_PREFIX_.'order_detail od GROUP BY od.product_id';
$result = Db::getInstance()->executeS($sql);
foreach($result as $row) {
$sql = 'SELECT count(*) FROM ' ._DB_PREFIX_.'product_sale WHERE id_product = ' .$row['id_product'];
$count = Db::getInstance()->getValue($sql);
if ($count == 0) {
$sql = 'INSERT INTO '._DB_PREFIX_.'product_sale (id_product, quantity, sale_nbr, date_upd) VALUES (';
$sql .= '\''.$row['id_product'].'\', \''.$row['quantity'].'\', \''.$row['sale_nbr'].'\', \''.$row['date_upd'].'\')';
$ret &= Db::getInstance()->execute($sql);
}
else {
$sql = 'UPDATE '._DB_PREFIX_.'product_sale SET id_product = \''
.$row['quantity']. '\', sale_nbr = \'' .$row['sale_nbr']. '\', date_upd = \\'' .$row['date_upd']
. '\ WHERE id_product = \'' .$row['id_product'];
$ret &= Db::getInstance()->execute($sql);
}
}
return $ret;
[< Original classes/Tab.php:125 >]
/* Query definition */
$query = 'REPLACE INTO `'._DB_PREFIX_.'access` (`id_profile`, `id_tab`, `view`, `add`, `edit`, `delete`) VALUES ';
$query .= '(1, '.(int)$id_tab.', 1, 1, 1, 1),';
foreach ($profiles as $profile)
{
$rights = $profile['id_profile'] == $context->employee->id_profile ? 1 : 0;
$query .= '('.(int)$profile['id_profile'].', '.(int)$id_tab.', '.(int)$rights.', '
.(int)$rights.', '.(int)$rights.', '.(int)$rights.'),';
}
$query = trim($query, ', ');
return Db::getInstance()->execute($query);
[< After change >]
$ret = true;
$sql = 'SELECT count(*) FROM ' ._DB_PREFIX_. 'access WHERE id_profile = 1 AND id_tab = ' . (int)$id_tab;
$count = Db::getInstance()->getValue($sql);
if ($count == 0) {
$sql = 'INSERT INTO '._DB_PREFIX_.'access (id_profile, id_tab, view, add, edit, delete) VALUES ';
$sql .= '(1, '.(int)$id_tab.', 1, 1, 1, 1),';
$ret &= Db::getInstance()->execute($sql);
}
else {
$sql = 'UPDATE '._DB_PREFIX_.'access SET view = 1, add = 1, edit = 1, delete = 1 WHERE id_profile = 1 AND id_tab = ';
$sql.= (int)$id_tab;
$ret &= Db::getInstance()->execute($sql);
}
foreach ($profiles as $profile)
{
$rights = $profile['id_profile'] == $context->employee->id_profile ? 1 : 0;
$sql = 'SELECT count(*) FROM ' ._DB_PREFIX_. 'access
WHERE id_profile = ' .(int)$profile['id_profile']. ' AND id_tab = ' . (int)$id_tab;
$count = Db::getInstance()->getValue($sql);
if ($count == 0) {
$sql = 'INSERT INTO '._DB_PREFIX_.'access (id_profile, id_tab, view, add, edit, delete) VALUES ';
$sql .= '('.(int)$profle['id_profile'].','.(int)$id_tab.',';
$sql .= (int)$rights.','.(int)$rights.','.(int)$rights.','.(int)$rights.'),';
$ret &= Db::getInstance()->execute($sql);
}
else {
$sql = 'UPDATE '._DB_PREFIX_.'access SET view = '.(int)$rights.', add = '.(int)$rights.', edit = ';
$sql .= (int)$rights.', delete = '.(int)$rights;
$sql .= ' WHERE id_profile = ' .(int)$profile['id_profile'] . ' AND id_tab = ' . (int)$id_tab;
$ret &= Db::getInstance()->execute($sql);
}
}
return $ret;
REPLACE statement is used when data of access.xml is populated in the database (See install/classes/xmlLoader.php:404, and next, the Db::insert method is invoked.).
Since this can not easily be rewritten,
I did not try this time.
It's not easy, but not impossible.
5. SELECT
Almost SELECT queries did not need to be rewritten.
Only serious problem is how to emulate the last_insert_Id function.
5.1. Last_Insert_Id function (non-standard: MySQL expand)
MySQL's last_insert_id function is wrapped in Db::Insert_ID method.
This method is used in over forty places, I was able to rewrite almost queries by expand this method.
I rewrote this method and source code. I show one example.
[< Original classes/db/Db.php:131 >]
abstract public function Insert_ID();
[< After change >]
abstract public function Insert_ID($table = null);
[< Original classes/db/DbPDO.php:104 >]
public function Insert_ID()
{
return $this->link->lastInsertId();
}
[< After change >]
public function Insert_ID($table = null)
{
if ($table == null)
return $this->link->lastInsertId();
else
return $this->getValue('SELECT currval\'' . _DB_PREFIX_. $table . '_auto_increment\')');
}
[< Original classes/Product.php:3396 >]
if (!Db::getInstance()->execute('
INSERT INTO `'._DB_PREFIX_.'customization_field` (`id_product`, `type`, `required`)
VALUES ('.(int)$this->id.', '.(int)$type.', 0)') ||
!$id_customization_field = (int)Db::getInstance()->Insert_ID())
return false;
[< After change >]
if (!Db::getInstance()->execute('
INSERT INTO `'._DB_PREFIX_.'customization_field` (`id_product`, `type`, `required`)
VALUES ('.(int)$this->id.', '.(int)$type.', 0)') ||
!$id_customization_field = (int)Db::getInstance()->Insert_ID('customization_field'))
return false;
This method is effective in most places. However, I do not know how to rewrite the following seven places.
- classes/Cart.php:3379
- modules/blocklayered/blocklayered.php:1431
- controller/admin/AdminAddressesController.php:395
- classes/Carrier.php:804
- classes/modules/Modules.php:235
- classes/ObjectModel.php:468
- classes/ObjectModel.php:543
5.2. "SELECT SQL_CALC_FOUND_ROWS" query (non-standard: MySQL expand)
"SELECT SQL_CALC_FOUND_ROWS" queries are used in nine places. This is also non-standard statement.
(There is a problem in terms of efficiency, but)
I think "SELECT count(*)" query may be used instead of this query.
Fortunately, those queries are run by administrator only.
5.3. control flow function: IF(), IFNULL()
Control flow functions IF() and IFNULL() are used in seviral places in the source code. These can be rewritten in CASE statement which is supported by MySQL and PostgreSQL.
[< Original: classes/Configuration.php:113 >]
$sql = 'SELECT c.`name`, cl.`id_lang`, IF(cl.`id_lang` IS NULL, c.`value`, cl.`value`) AS value,
c.id_shop_group, c.id_shop
FROM `'._DB_PREFIX_.'configuration` c
LEFT JOIN `'._DB_PREFIX_.'configuration_lang` cl ON (c.id_configuration = cl.id_configuration)';
[< After change >]
$sql = 'SELECT c.name, cl.id_lang, CASE WHEN cl.id_lang = NULL THEN c.value ELSE cl.value END AS value,
c.id_shop_group, c.id_shop
FROM `'._DB_PREFIX_.'configuration` c
LEFT JOIN `'._DB_PREFIX_.'configuration_lang` cl ON (c.id_configuration = cl.id_configuration)';
6. UPDATE LIMIT, DELETE LIMIT statements (non-standard: MySQL expand)
"UPDATE LIMIT" statement is of course non-standard.
I show database's log file.
30 Query UPDATE `ps_configuration` SET `value` = '1',`date_upd` = '2013-03-17 22:50:15' WHERE `name` = 'MOD_BLOCKTOPMENU_SEARCH' AND id_shop_group IS NULL AND id_shop IS NULL LIMIT 1 30 Query UPDATE `ps_configuration` SET `value` = '2',`date_upd` = '2013-03-17 22:50:15' WHERE `name` = 'PRODUCTS_VIEWED_NBR' AND id_shop_group IS NULL AND id_shop IS NULL LIMIT 1 30 Query UPDATE `ps_configuration` SET `value` = 'graphartichow',`date_upd` = '2013-03-17 22:50:15' WHERE `name` = 'PS_STATS_RENDER' AND id_shop_group IS NULL AND id_shop IS NULL LIMIT 1 30 Query UPDATE `ps_configuration` SET `value` = 'graphgooglechart',`date_upd` = '2013-03-17 22:50:15' WHERE `name` = 'PS_STATS_RENDER' AND id_shop_group IS NULL AND id_shop IS NULL LIMIT 1 ... to be continued ..
I have no idea how to rewrite this, because I do not understand this logic.
I think rewriting is not impossible. However, I gave it up, in this feasibility study.
7. SHOW commands
SHOW TABLES and SHOW COLUMNS FROM commands can be emulated by PL/pgSQL functions.
7.1. SHOW TABLES
SHOW TABLE command is used in about ten places in the source code.
I defined two functions called "show_tables", and rewrote SHOW TABLE command using those functions.
CREATE OR REPLACE FUNCTION show_tables (text) RETURNS SETOF name AS
$$ /* $1 = pattern */
SELECT c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
AND c.relname LIKE $1 ;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION show_tables () RETURNS SETOF name AS
$$
SELECT c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);
$$ LANGUAGE SQL;
I show two examples:
[< Original classes/Language.php:472 >]
$result = Db::getInstance()->executeS('SHOW TABLES FROM `'._DB_NAME_.'`');
[< After change >]
$result = Db::getInstance()->executeS('SELECT SHOW_TABLES()');
[< Original classes/Language.php:391 >]
$tables = Db::getInstance()->executeS('SHOW TABLES LIKE \''.str_replace('_', '\\_', _DB_PREFIX_).'%\_lang\' ');
[< After change >]
$tables = Db::getInstance()->executeS('SELECT SHOW_TABLES(\''.str_replace('_', '\\_', _DB_PREFIX_).'%\_lang\') ');
7.2. SHOW COLUMNS FROM
SHOW COLUMNS (also SHOW FIELDS) command is used in about ten places in the source code.
I defined a function called "show_columns_from", and rewrote SHOW COLUMNS command using this function.
CREATE OR REPLACE FUNCTION show_columns_from (name) RETURNS SETOF record AS
$$ /* $1 = relname */
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid
= (SELECT c.oid FROM pg_catalog.pg_class c
WHERE c.relname = $1 AND pg_catalog.pg_table_is_visible(c.oid))
AND a.attnum > 0 AND NOT a.attisdropped;
$$ LANGUAGE SQL;
I show one example:
[< Original classes/Language.php:412 >]
$columns = Db::getInstance()->executeS('SHOW COLUMNS FROM `'.$name.'`');
[< After change >]
$columns = Db::getInstance()->executeS('SELECT * FROM SHOW_COLUMNS_FROM(\'' .$name. '\') AS (Field name, Type text)');
Conclusions
I think that more than 95% of queries which are executed while PrestaShop is running are SELECT statements.
And then, almost PrestaShop's SELECT statements can be executed in PostgreSQL (if back quotes are deleted).
According to my feasibility study, it is possible that PrestaShop is able to support PostgreSQL.
- Note:Maybe PrestasShop is able to support Oracle. Support for SQLite would be more difficult. I do not know whether it can be do.
I would like to show three important issues:
- How to change Db::insert method.
- How to emulate last_insert_id() function.
- How to rewrite UPDATE/DELETE LIMIT logic.
Indeed, those are not easy tasks.
However, I think that it is able to finish by a few engineers.
I would like to say again, almost SELET statements can be executed in PostgreSQL.
The performance of PostgreSQL has been greatly enhanced.
If PrestaShop is unable to support PostgreSQL in order to use a few MySQL's expanded functions which I have listed above, I think that it is very disappointing.
Suzuki Hironobu
mail: hironobu _a_ interdb.jp
APPENDIX: Source Code and Installation
Warning:This source code is junk. This is not work, and is only for confirmation.
- Check pdo-pgsql module.
- Download source from here. (13Mbyte)
- Check PostgreSQL version. (9.1 or later)
- Set "standard_comforming_strings = off" in postgresql.conf
- Create database with "-E utf8" option.
- Run prestashop/func.sql script.
- Install PrestaShop.
- and then.... Halt :-)
# createdb prestashop_db -E utf8
# psql prestashop_db -f installpath/prestashop/func.sql
Remark: connect to 127.0.0.1 or your server's IP address. Don't connect to "localhost".