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 "Prestashop for PostgreSQL"
In order to support PostgreSQL to PrestaShop, I made a feasibility study for a few weeks.
Write the conclusion first, I was not able to complete the installation, because there were many difficulties which due to the MySQL's expanded functions. However, I think that supporting PostgreSQL is certainly feasible.
0.1. Source and install
1. Findings and Extracted problems
But not all, I think I got some findings and was able to extract a lot of problems.
1.1. Grave accent (`) problem (non-standard: MySQL expand)
The first difficulty was the huge number of Grave accent.
For example, the following SELECT query does not work on PostgreSQL (also Oracle, SQLite).
[< 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, it was very hard work of rewriting the sheer number of them.
Note:Since this is non-standard notation, I think all should be deleted.
1.2. Table Definitions
I was able to rewrite all CREATE TABLE queries in "install/data/db_structure.sql".
1.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 some 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');
1.2.2. CREATE TABLE
In the table definitions, indices have to 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)
);
1.3. INSERT
I was worried beforehand, but there was no big problem of INSERT statements.
1.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()->execute('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);
}
}
1.3.2. INSERT IGNORE (non-standard: MySQL expand)
INSERT IGNORE statement is also non-standard, and it can be rewritten in the same way as the REPLACE statement in principle.
1.3.3. "INSERT INTO table () VALUES()" query problem (non-standard: MySQL expand)
1.4. REPLACE (non-standard: MySQL expand)
REPLACE statement is useful, but 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()->execute($sql);
foreach($result as $row) {
$sql = 'SELECT count(*) FROM ' ._DB_PREFIX_.'product_sale WHERE id_product = ' .$row['id_product'];
$count = Db::getInstance()->execute($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()->execute($query);
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;
$query .= '('.(int)$profile['id_profile'].', '.(int)$id_tab.', '.(int)$rights.', ';
$query .= (int)$rights.', '.(int)$rights.', '.(int)$rights.'),';
$sql = 'SELECT count(*) FROM ' ._DB_PREFIX_. 'access
WHERE id_profile = ' .(int)$profile['id_profile']. ' AND id_tab = ' . (int)$id_tab;
$count = Db::getInstance()->execute($query);
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.
Since this can not easily be rewritten,
I did not work this time.
It's not easy, but not impossible.
1.5. UPDATE LIMIT statement (non-standard: MySQL expand)
When I was working, I saw some very strange queries.
One kind of them is "UPDATE LIMIT" statement.
This is of course a non-standard statement.
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.
I think rewriting is not impossible. However, I gave it up, in this feasibility study.
1.6. SELECT
Almost SELECT queries did not need to be rewritten.
Only serious problem is how to emulate the last_insert_Id function.
1.6.1. Last_Insert_Id function (non-standard: MySQL expand)
1.6.2. SQL_CALC_FOUND_ROWS (non-standard: MySQL expand)
nine places.
1.6.3. control flow function: IF(), IFNULL()
Control flow functions IF() and IFNULL() are used in some 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)';
1.7. SHOW commands
SHOW TABLES command and SHOW COLUMNS FROM command can be rewritten using PL/pgSQL functions.
1.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\') ');
1.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)');
1.8. Functions
2. Conclusions
I think that more than 95% of queries which executed while PrestaShop is running are SELECT statements.
And then, almost PrestaShop's SELECT statements can be executed in PostgreSQL.
The performance of PostgreSQL has been greatly enhanced.
And according to my feasibility study, it is possible that PrestaShop is able to support PostgreSQL (and maybe Oracle).
If PrestaShop is unable to support PostgreSQL in order to use some MySQL's expanded functions which I have listed above, I think that it is very disappointing.
- Note:Maybe PrestasShop is able to support Oracle. On the other hand, it would be difficult to support SQLite. Because SQLite does not support stored functions and sequences(auto_increment).
Indeed, it is not an easy task. However, I think that it is able to rewrite by a few engineers.
Suzuki hironobu: hironobu _at_ interdb.jp