These functions are compatible, with the exception that MySQL RAND()
optionally takes a seed argument. If future queries require seed
support, a call to PostgreSQL SETSEED() can be added.
Fixes: #12
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
PostgreSQL does not provide an equivalent to the MySQL FIELD function.
It is possible to create such a function, although the syntax is
version-dependent (and creates a persistent database object, which so
far has not been done by this plugin). So use a generic CASE statement.
Fixes: #12
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
If the connection fails, bail early. Otherwise the processing of the
queued SQL statements will cause an infinite loop by continually pushing
the same statement into the queue due to lack of connection.
Also add a local variable for the connection for clarity and
performance.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Although calling wpsql_select_db from wpsql_connect did set the
connection globally, it also caused early clearing of the connection
string when WP_INSTALLING is defined and ran queued early SQL commands
before select is called by external code (which was dubious). Revert
it, but keep using the best guess at the database name, since this often
saves a connection.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Rather than calling pg_connect directly, call wpsql_select_db with the
best guess at the database name. This sets $GLOBALS['pg4wp_conn'] and avoids reconnecting whenever the guess is correct.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Although I am not a big fan of the existing style, I think consistency
adds enough value to justify keeping the formatting as standardized as
possible.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
If the non-capturing group which contains the last two capture groups of
the expression is not matched, the indexes are not defined in the match
result, causing a warning. Avoid this using isset() before access.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The wpdb constructor makes some queries (e.g. for SQL_MODE) which are
rewritten. This occurs before the constructor returns and therefore
before the $wpdb global is set. Handle this without errors.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
During setup, before wp-content.php is created and DB_USER is defined,
there is no way to instantiate it meaningfully.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
For MySQL compatibility, support using integers as booleans in
expressions. This is an expensive and unreliable check, so limit it to
the cases currently observed in the wild. We can expand the checks
later if more uses appear.
The current appearance is from the query:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND 0
ORDER BY wp_posts.post_date DESC LIMIT 0, 10
made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts,
WP_Query->query, WP_Query->get_posts
I can't determine the exact source of the calls or whether it is
URL-dependent. But for me that is irrelevant, since it is a case that I
need to support.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
It appears that the split function, which has been deprecated since PHP
5.3.0, has finally been removed in PHP 7. Since it is being used to
split on a literal string, use explode() instead.
Fixes#1
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
If the query produces an error, log the query which produced the error
in addition to the insert statement which preceded it. This helped me
track down an issue with a mis-named sequence from a bad
MySQL->PostgreSQL migration.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The pattern added in 785307ee only matches single-character identifiers,
but should match identifiers of any non-zero length. Fix it.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL supports the ORDER BY clause on DELETE statements for use with
the LIMIT clause to choose which rows are deleted. PostgreSQL does
not support this clause, which leads to errors such as:
Error running :
DELETE FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'display_type' AND ((wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft')) ORDER BY wp_posts.post_date DESC
---- converted to ----
DELETE FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'display_type' AND ((wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft')) ORDER BY wp_posts.post_date DESC
----> ERROR: syntax error at or near "ORDER"
LINE 1: ...rash' AND wp_posts.post_status <> 'auto-draft')) ORDER BY w...
When the ORDER BY clause is specified without a LIMIT clause, as it is
in the above error, it does not have any effect that I am aware of (due
to how transactions are handled). In these cases, we remove the ORDER
BY clause to avoid the error.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL supports ordering by columns which do not appear in the field list
for a SELECT DISTINCT statement while PostgreSQL does not. This results
in errors such as:
Error running :
SELECT DISTINCT pid , GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS `extras` FROM `wp_ngg_pictures` LEFT OUTER JOIN `wp_postmeta` ON `wp_postmeta`.`post_id` = `extras_post_id` WHERE (`exclude` = 0) AND (`galleryid` IN (2)) GROUP BY wp_ngg_pictures.pid ORDER BY `sortorder` ASC
---- converted to ----
SELECT DISTINCT pid , string_agg(CONCAT_WS('@@', meta_key, meta_value), ',') AS extras FROM wp_ngg_pictures LEFT OUTER JOIN wp_postmeta ON wp_postmeta.post_id = extras_post_id WHERE (exclude = 0) AND (galleryid IN (2)) GROUP BY wp_ngg_pictures.pid ORDER BY sortorder ASC
----> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...yid IN (2)) GROUP BY wp_ngg_pictures.pid ORDER BY sortorder ...
^
To avoid this error, ensure that the field listed in the ORDER BY
statement also appears in the SELECT statement. To support GROUP BY
statements, ensure that it is aggregated using MIN() to mimic the MySQL
behavior.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL recognizes SIGNED and UNSIGNED in place of SIGNED INTEGER and
UNSIGNED INTEGER. Notice this in calls to CAST() and replace it with
INTEGER (which is signed by default).
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL provides a CONVERT function for doing data type conversion.
This currently results in errors such as:
Error running :
SELECT image_slug, SUBSTR(image_slug, 10) AS 'i' FROM wp_ngg_pictures WHERE (image_slug LIKE 'img_0601-%' AND CONVERT(SUBSTR(image_slug, 10), SIGNED) BETWEEN 1 AND 2147483647) OR image_slug = 'img_0601' ORDER BY i DESC LIMIT 1
---- converted to ----
SELECT image_slug, SUBSTR(image_slug, 10) AS "i" FROM wp_ngg_pictures WHERE (image_slug ILIKE 'img_0601-%' AND CONVERT(SUBSTR(image_slug, 10), SIGNED) BETWEEN 1 AND 2147483647) OR image_slug = 'img_0601' ORDER BY i DESC LIMIT 1
----> ERROR: column "signed" does not exist
LINE 1: ... 'img_0601-%' AND CONVERT(SUBSTR(image_slug, 10), SIGNED) BE...
Recognize this function and replace it with CAST().
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
PostgreSQL does not support the MySQL GROUP_CONCAT() aggregation
function. This results in errors such as:
Error running :
SELECT DISTINCT wp_ngg_gallery.* , GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS `extras` FROM `wp_ngg_gallery` LEFT OUTER JOIN `wp_postmeta` ON `wp_postmeta`.`post_id` = `extras_post_id` GROUP BY wp_ngg_gallery.gid ORDER BY `gid` ASC LIMIT 25
---- converted to ----
SELECT DISTINCT wp_ngg_gallery.* , GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS extras FROM wp_ngg_gallery LEFT OUTER JOIN wp_postmeta ON wp_postmeta.post_id = extras_post_id GROUP BY wp_ngg_gallery.gid ORDER BY gid ASC LIMIT 25
----> ERROR: function group_concat(text) does not exist
LINE 1: SELECT DISTINCT wp_ngg_gallery.* , GROUP_CONCAT(CONCAT_WS('@...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
However, PostgreSQL 9.0 and later provide the STRING_AGG() aggregation
function for the same purpose. Therefore, replace calls to
GROUP_CONCAT() with STRING_AGG().
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL allows quoting identifiers, such as column names, as strings using
single-quotes in addition to quoting as identifiers using grave accents.
PostgreSQL does not, resulting in errors such as:
Error running :
SELECT DISTINCT wp_ngg_pictures.* , GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROM `wp_ngg_pictures` LEFT OUTER JOIN `wp_postmeta` ON `wp_postmeta`.`post_id` = `extras_post_id` GROUP BY wp_ngg_pictures.pid LIMIT 1
---- converted to ----
SELECT DISTINCT wp_ngg_pictures.* , GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROM wp_ngg_pictures LEFT OUTER JOIN wp_postmeta ON wp_postmeta.post_id = extras_post_id GROUP BY wp_ngg_pictures.pid LIMIT 1
----> ERROR: syntax error at or near "'extras'"
LINE 1: ..._CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' F...
^
Fix this by replacing single quotes with grave accents when they occur
after ") AS ". This strategy obviously has both false-positive and
false-negative issues, but suits the current needs and should be
relatively safe from false-positives. Proper replacement would require
parsing the SQL.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The flash-album-gallery plugin defines columns using the tinyint type
without a display width. Convert this to smallint.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Data types are not case-sensitive, and the current data type conversion
assumes that the types are all lower-case. Although this is currently
the case for most (all?) of the Wordpress core, it is not the case for
all extensions (e.g. nextgen-gallery). Make data type conversion
case-insensitive.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The current regex for matching ALTER TABLE CHANGE COLUMN does not match
against a statement where there is nothing after the column data type,
resulting in errors such as:
Error running :
ALTER TABLE wp_ngg_pictures CHANGE COLUMN meta_data meta_data LONGTEXT
---- converted to ----
ALTER TABLE wp_ngg_pictures CHANGE COLUMN meta_data meta_data LONGTEXT
----> ERROR: syntax error at or near "CHANGE"
LINE 1: ALTER TABLE wp_ngg_pictures CHANGE COLUMN meta_data meta_dat...
^
Fix this by making the space before NOT NULL optional.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
MySQL supports the IGNORE modifier on INSERT statements, which ignores
uniqueness errors resulting from the INSERT. This causes syntax errors
in PostgreSQL such as the following:
Error running :
INSERT IGNORE INTO `wp_options` ( `option_name`, `option_value`, `autoload` ) VALUES ('auto_updater.lock', '1433306517', 'no') /* LOCK */
---- converted to ----
INSERT IGNORE INTO wp_options ( option_name, option_value, autoload ) VALUES ('auto_updater.lock', '1433306517', 'no') /* LOCK */
----> ERROR: syntax error at or near "IGNORE"
LINE 1: INSERT IGNORE INTO wp_options ( option_name, option_value, a...
^
Provide support for INSERT IGNORE using a PostgreSQL DO statement with
an exception handler for uniqueness errors.
This has the drawback that it requires PostgreSQL 9.0 or later, support
for plpgsql, and USAGE privileges for plpgsql for the current user. But
these are all common, and it allows us to support INSERT IGNORE
statements generically. If this is later found to be too much of a
problem, it is possible to rewrite the query on a query-specific basis
to an INSERT SELECT statement without a FROM clause.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The quoting of identifiers which contain "ID" can match things other
than identifiers, such as strings which contain "ID" and subsequently
"=". An example of this occurring in practice is:
Error running :
UPDATE `wp_ngg_pictures` SET `pid` = '42', `post_id` = 0, `galleryid` = '3', `filename` = 'img_0683.jpg', `description` = '', `alttext` = 'img_0683', `imagedate` = '2011-02-05 11:52:31', `exclude` = '0', `sortorder` = '0', `meta_data` = 'eyIwIjpmYWxzZSwiYXBlcnR1cmUiOiJGIDUuNiIsImNyZWRpdCI6ZmFsc2UsImNhbWVyYSI6IkNhbm9uIEVPUyBSRUJFTCBUMWkiLCJjYXB0aW9uIjpmYWxzZSwiY3JlYXRlZF90aW1lc3RhbXAiOiJGZWJydWFyeSA1LCAyMDExIDExOjUyIGFtIiwiY29weXJpZ2h0IjpmYWxzZSwiZm9jYWxfbGVuZ3RoIjoiNTUgbW0iLCJpc28iOjQwMCwic2h1dHRlcl9zcGVlZCI6IjFcLzYwIHNlYyIsImZsYXNoIjoiRmlyZWQiLCJ0aXRsZSI6ZmFsc2UsImtleXdvcmRzIjpmYWxzZSwid2lkdGgiOjE2MDAsImhlaWdodCI6MTA2Nywic2F2ZWQiOnRydWUsInRodW1ibmFpbCI6eyJ3aWR0aCI6MTAwLCJoZWlnaHQiOjc1fSwibmdnMGR5bi0weDI1MC0wMGYwdzAxMWMwMTByMTEwZjExMHIwMTB0MDEwIjp7IndpZHRoIjozNzUsImhlaWdodCI6MjUwLCJmaWxlbmFtZSI6ImltZ18wNjgzLmpwZy1uZ2dpZDAyNDItbmdnMGR5bi0weDI1MC0wMGYwdzAxMWMwMTByMTEwZjExMHIwMTB0MDEwLmpwZyIsImdlbmVyYXRlZCI6IjAuNjczMzIxMDAgMTQzMzYyOTQxMiJ9fQ==', `image_slug` = 'img_0683', `extras_post_id` = '310', `updated_at` = '1433629412' WHERE `pid` = '42'
---- converted to ----
UPDATE wp_ngg_pictures SET pid = '42', post_id = 0, galleryid = '3', filename = 'img_0683.jpg', description = '', alttext = 'img_0683', imagedate = '2011-02-05 11:52:31', exclude = '0', sortorder = '0', meta_data = "'eyIwIjpmYWxzZSwiYXBlcnR1cmUiOiJGIDUuNiIsImNyZWRpdCI6ZmFsc2UsImNhbWVyYSI6IkNhbm9uIEVPUyBSRUJFTCBUMWkiLCJjYXB0aW9uIjpmYWxzZSwiY3JlYXRlZF90aW1lc3RhbXAiOiJGZWJydWFyeSA1LCAyMDExIDExOjUyIGFtIiwiY29weXJpZ2h0IjpmYWxzZSwiZm9jYWxfbGVuZ3RoIjoiNTUgbW0iLCJpc28iOjQwMCwic2h1dHRlcl9zcGVlZCI6IjFcLzYwIHNlYyIsImZsYXNoIjoiRmlyZWQiLCJ0aXRsZSI6ZmFsc2UsImtleXdvcmRzIjpmYWxzZSwid2lkdGgiOjE2MDAsImhlaWdodCI6MTA2Nywic2F2ZWQiOnRydWUsInRodW1ibmFpbCI6eyJ3aWR0aCI6MTAwLCJoZWlnaHQiOjc1fSwibmdnMGR5bi0weDI1MC0wMGYwdzAxMWMwMTByMTEwZjExMHIwMTB0MDEwIjp7IndpZHRoIjozNzUsImhlaWdodCI6MjUwLCJmaWxlbmFtZSI6ImltZ18wNjgzLmpwZy1uZ2dpZDAyNDItbmdnMGR5bi0weDI1MC0wMGYwdzAxMWMwMTByMTEwZjExMHIwMTB0MDEwLmpwZyIsImdlbmVyYXRlZCI6IjAuNjczMzIxMDAgMTQzMzYyOTQxMiJ9fQ=" =', image_slug = 'img_0683', extras_post_id = '310', updated_at = '1433629412' WHERE pid = '42'
----> ERROR: syntax error at or near "img_0683"
LINE 1: ...jczMzIxMDAgMTQzMzYyOTQxMiJ9fQ=" =', image_slug = 'img_0683',...
To avoid this, ensure that the matched substring is not part of a SQL
string literal.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
[Work In Progress] MySQL performs prefix indexing on a few columns.
PostgreSQL lacks support for prefix indexing (although it does support
expression indexing, which can be used to similar effect), which results
in the following error message:
Error running :
ALTER TABLE wp_comments ADD KEY comment_author_email (comment_author_email(10))
---- converted to ----
CREATE INDEX wp_comments_comment_author_email ON wp_comments (comment_author_email(10)
----> ERROR: syntax error at end of input
LINE 1: ...omment_author_email ON wp_comments (comment_author_email(10)
Since the prefix indexing does not currently appear to be advantageous
(other than working around MySQL row index size limitations), convert
these to full-column indexes.
FIXME: SHOW INDEX needs to be updated so that it returns the requested
prefix length in order to avoid attempted index recreation during schema
upgrades. This could be accomplished by storing the length as a comment
or some other ancillary metadata. This is not implemented yet.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The order of rows returned from SHOW INDEX is significant, and it is
used to determine the column order in multi-column indexes. Update the
query to return rows in the expected order to avoid errors during
upgrade (e.g. in wp_term_taxonomy_term_id_taxonomy where the column
order is not alphabetical).
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
This appears as both "SELECT @@SESSION.sql_mode" checked from wp-db.php
in Wordpress core during startup and as "SHOW VARIABLES LIKE 'sql_mode'"
in several extensions (all-in-one-seo-pack, flash-album-gallery, and
nextgen-gallery on my system). Support both by returning the empty
string, which reports the default MySQL behavior (and avoids attempts to
change the mode, since all current code expects this).
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Wordpress 4 makes use of SHOW COLUMNS and SHOW TABLES in addition to
SHOW INDEX, both with and without the FULL modifier. Implement support
for these statements using queries against INFORMATION_SCHEMA.
Some of these queries use lower-case versions of these statements,
such as "show tables like 'wp_flag_pictures'", so we make sure to
recognize both the upper- and lower-case versions.
This is based, in part, on the work of raptorz in the support forum at
https://wordpress.org/support/topic/upgrade-to-wp421-fail?replies=3#post-6886123
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
The current rewriting changes "default '0000-00-00 00:00:00'" when it
appears in CREATE TABLE statements, but does not handle the case that a
column default value is modified. This results in errors such as the
following during schema upgrade:
Error running :
ALTER TABLE wp_users ALTER COLUMN user_registered SET DEFAULT '0000-00-00 00:00:00'
---- converted to ----
ALTER TABLE wp_users ALTER COLUMN user_registered SET DEFAULT '0000-00-00 00:00:00'
----> ERROR: date/time field value out of range: "0000-00-00 00:00:00"
Apply the conversion to ALTER TABLE statements as well.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
During schema upgrade, populate_options() deletes all expired transients
and their corresponding transient timeouts. The queries produce the
following errors:
Error running :
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_transient\_%'
AND a.option_name NOT LIKE '\_transient\_timeout\_%'
AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
AND b.option_value < 1433110465
---- converted to ----
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_transient\_%'
AND a.option_name NOT LIKE '\_transient\_timeout\_%'
AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
AND b.option_value < 1433110465
----> ERROR: syntax error at or near "a"
LINE 1: DELETE a, b FROM wp_options a, wp_options b
^
---------------------
Error running :
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_site\_transient\_%'
AND a.option_name NOT LIKE '\_site\_transient\_timeout\_%'
AND b.option_name = CONCAT( '_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) )
AND b.option_value < 1433110465
---- converted to ----
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_site\_transient\_%'
AND a.option_name NOT LIKE '\_site\_transient\_timeout\_%'
AND b.option_name = CONCAT( '_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) )
AND b.option_value < 1433110465
----> ERROR: syntax error at or near "a"
LINE 1: DELETE a, b FROM wp_options a, wp_options b
^
---------------------
Since PostgreSQL does not support multi-table DELETE statements,
significant rewriting must be done. Since I could not think of a good
generic way to perform this rewriting, recognize this statement
specifically and provide an alternative version.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
Wordpress 4 defaults to using mysqli_* functions in preference to
mysql_*. Since pg4wp only provides replacements for mysql_ functions,
we need to ensure that the mysql_* functions are called. Do this by
defining WP_USE_EXT_MYSQL to true.
Signed-off-by: Kevin Locke <kevin@kevinlocke.name>
