<?php

/**

* DB Engine: PostgreSQL Support

* DB URI: https://github.com/ozh/yourls-postgresql

* Description: Adds support for PostgreSQL databases in YOURLS. ⚠ PLACE THIS FILE IN THE USER/ DIRECTORY.

* Version: 0.1

* Author: Ozh

* Author URI: https://ozh.org/

*/

// PostgreSQL hooks

yourls_add_filter('db_connect_custom_dsn', 'ozh_yourls_pgsql_dsn');

yourls_add_filter('html_footer_text', 'ozh_yourls_pgsql_footer');

yourls_add_filter('fetch_wrapper_statement', 'ozh_yourls_pgsql_rewrite_query');

yourls_add_filter('shunt_yourls_create_sql_tables', 'ozh_yourls_pgsql_create_tables');

// Now load the rest of the database code, which will use the above filters to modify behavior for PostgreSQL

require_once YOURLS_ABSPATH.'/includes/class-mysql.php';

/**

* Modify the DSN to use PostgreSQL instead of MySQL

*

* @param $in

* @return string

*/

function ozh_yourls_pgsql_dsn($in): string {

$in = str_replace("mysql:","pgsql:",$in);

$in = str_replace("charset=utf8mb4","",$in);

// If running in a PHPUnit test context, add a note about using PostgreSQL

if (class_exists('PHPUnit\Framework\TestCase')) {

echo "Using PostgreSQL Engine 🐘\n";

}

return $in;

}

/**

* Add a footer note to indicate we're using PostgreSQL

*

* @param $in

* @return string

*/

function ozh_yourls_pgsql_footer($in): string {

return $in . ' on PostgreSQL 🐘';

}

/**

* Rewrite MySQL query to PostgreSQL compatible syntax

*

* @param string $query The original MySQL query

* @return string The rewritten PostgreSQL query

*/

function ozh_yourls_pgsql_rewrite_query(string $query): string {

// Store original for debugging

$original_query = $query;

// Replace backticks with double quotes

$query = str_replace('`', '"', $query);

// Convert DATE_ADD/DATE_SUB to PostgreSQL interval syntax

$query = pgsql_convert_date_add($query);

// Convert DATE_FORMAT to TO_CHAR

$query = pgsql_convert_date_format($query);

// Convert "LIMIT offset, count" to "LIMIT count OFFSET offset"

$query = preg_replace(

'/LIMIT\s+(\d+)\s*,\s*(\d+)/i',

'LIMIT $2 OFFSET $1',

$query

);

// Replace current_timestamp() with CURRENT_TIMESTAMP (no parentheses)

$query = preg_replace(

'/current_timestamp\(\)/i',

'CURRENT_TIMESTAMP',

$query

);

// Replace RAND() with RANDOM()

$query = preg_replace('/\bRAND\s*\(/i', 'RANDOM(', $query);

// Handle DATETIME to TIMESTAMP conversion

$query = preg_replace('/\bDATETIME\b/i', 'TIMESTAMP', $query);

// Remove BINARY keyword (case-sensitive comparison)

$query = preg_replace('/\bBINARY\b/i', '', $query);

// Remove UNSIGNED keyword that doesn't exist in PostgreSQL

$query = preg_replace('/\s+UNSIGNED/i', '', $query);

// Handle AUTO_INCREMENT conversions in CREATE TABLE

$query = preg_replace(

'/\bBIGINT\s*(?:\(\d+\))?\s*(?:UNSIGNED\s+)?(?:NOT\s+NULL\s+)?AUTO_INCREMENT/i',

'BIGSERIAL',

$query

);

// Pattern for INT with AUTO_INCREMENT

$query = preg_replace(

'/\bINT\s*(?:\(\d+\))?\s*(?:UNSIGNED\s+)?(?:NOT\s+NULL\s+)?AUTO_INCREMENT/i',

'SERIAL',

$query

);

// Clean up any remaining AUTO_INCREMENT

$query = preg_replace('/\bAUTO_INCREMENT\b/i', '', $query);

// Remove MySQL type size hints like INT(10), VARCHAR preserves its size

$query = preg_replace('/\bINT\(\d+\)/i', 'INTEGER', $query);

$query = preg_replace('/\bBIGINT\(\d+\)/i', 'BIGINT', $query);

// Replace CHARACTER SET and COLLATE clauses

$query = preg_replace('/CHARACTER\s+SET\s+\w+/i', '', $query);

$query = preg_replace('/COLLATE\s+\w+/i', '', $query);

// Remove ENGINE, DEFAULT CHARSET, and similar MySQL-specific table options

$query = preg_replace('/ENGINE\s*=\s*\w+/i', '', $query);

$query = preg_replace('/DEFAULT\s+CHARSET\s*=\s*\w+/i', '', $query);

$query = preg_replace('/AUTO_INCREMENT\s*=\s*\d+/i', '', $query);

// Replace SHOW TABLES with PostgreSQL equivalent

if (preg_match('/SHOW\s+TABLES(?:\s+LIKE\s+["\']([^"\']+)["\'])?/i', $query, $matches)) {

if (isset($matches[1])) {

$table_pattern = $matches[1];

$query = "SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE '$table_pattern'";

} else {

$query = "SELECT tablename FROM pg_tables WHERE schemaname = 'public'";

}

}

// Replace SHOW COLUMNS with PostgreSQL equivalent

if (preg_match('/SHOW\s+COLUMNS\s+FROM\s+"?(\w+)"?/i', $query, $matches)) {

$table = $matches[1];

$query = "SELECT column_name, data_type, is_nullable

FROM information_schema.columns

WHERE table_name = '$table'";

}

// Handle KEY to INDEX conversion (both work, but INDEX is clearer)

$query = preg_replace('/\bKEY\s+"/i', 'INDEX "', $query);

// Fix PRIMARY KEY syntax issues (remove quotes around constraint name)

$query = preg_replace('/PRIMARY\s+KEY\s+"([^"]+)"/i', 'PRIMARY KEY', $query);

// Clean up extra whitespace and commas

$query = preg_replace('/,\s*\)/', ')', $query);

$query = preg_replace('/\s+/', ' ', $query);

return trim($query);

}

/**

* Convert MySQL DATE_FORMAT to PostgreSQL TO_CHAR

*/

function pgsql_convert_date_format($query): string {

// Pattern to capture DATE_FORMAT(column, 'format')

$pattern = '/DATE_FORMAT\s*\(\s*([^,]+?)\s*,\s*([\'"])([^\2]*?)\2\s*\)/i';

$query = preg_replace_callback($pattern, function($matches) {

$column = $matches[1];

$mysql_format = $matches[3];

// Convert MySQL format to PostgreSQL

$pgsql_format = strtr($mysql_format, [

'%Y' => 'YYYY', // Year, 4 digits

'%y' => 'YY', // Year, 2 digits

'%m' => 'MM', // Month, 2 digits

'%d' => 'DD', // Day of the month, 2 digits

'%H' => 'HH24', // Hour (24-hour), 2 digits

'%h' => 'HH12', // Hour (12-hour), 2 digits

'%i' => 'MI', // Minute, 2 digits

'%s' => 'SS', // Second, 2 digits

'%W' => 'Day', // Full weekday name

'%M' => 'Month', // Full month name

'%a' => 'Dy', // Abbreviated weekday name

'%b' => 'Mon', // Abbreviated month name

'%w' => 'D', // Day of the week (0=Sunday, 1=Monday...)

]);

return "TO_CHAR($column, '$pgsql_format')";

}, $query);

return $query;

}

/**

* Convert MySQL DATE_ADD/DATE_SUB to PostgreSQL interval syntax

* @param string $query The original MySQL query

* @return string The rewritten PostgreSQL query

*/

function pgsql_convert_date_add($query): string {

// Pattern to capture DATE_ADD(column, INTERVAL x UNIT)

$pattern = '/DATE_ADD\s*\(\s*([^,]+?)\s*,\s*INTERVAL\s+([^\)]+?)\s+(\w+)\s*\)/i';

$query = preg_replace_callback($pattern, function($matches) {

$column = trim($matches[1]);

$value = trim($matches[2]);

$unit = strtoupper(trim($matches[3]));

// Convert MySQL units to PostgreSQL units

$unit_map = [

'YEAR' => 'year',

'MONTH' => 'month',

'DAY' => 'day',

'HOUR' => 'hour',

'MINUTE' => 'minute',

'SECOND' => 'second',

];

$pg_unit = $unit_map[$unit] ?? strtolower($unit);

// PostgreSQL: column + INTERVAL 'x unit'

return "($column + INTERVAL '$value $pg_unit')";

}, $query);

// Pattern for DATE_SUB (substraction)

$pattern = '/DATE_SUB\s*\(\s*([^,]+?)\s*,\s*INTERVAL\s+([^\)]+?)\s+(\w+)\s*\)/i';

$query = preg_replace_callback($pattern, function($matches) {

$column = trim($matches[1]);

$value = trim($matches[2]);

$unit = strtoupper(trim($matches[3]));

$unit_map = [

'YEAR' => 'year',

'MONTH' => 'month',

'DAY' => 'day',

'HOUR' => 'hour',

'MINUTE' => 'minute',

'SECOND' => 'second',

];

$pg_unit = $unit_map[$unit] ?? strtolower($unit);

// PostgreSQL: column - INTERVAL 'x unit'

return "($column - INTERVAL '$value $pg_unit')";

}, $query);

// Convert interval substraction like "CURRENT_TIMESTAMP - INTERVAL 1 DAY"

$query = preg_replace('/INTERVAL\s+(\d+)\s+(\w+)/i', "INTERVAL '$1 $2'", $query);

return $query;

}

/**

* Create PostgreSQL tables. Return array( 'success' => array of success strings, 'errors' => array of error strings )

* This is a PostgreSQL-specific version of yourls_create_sql_tables()

*

* @return array An array like array( 'success' => array of success strings, 'errors' => array of error strings )

*/

function ozh_yourls_pgsql_create_tables(): array {

$ydb = yourls_get_db('write-create_sql_tables');

$error_msg = array();

$success_msg = array();

// Create Table Query - PostgreSQL syntax

$create_tables = array();

$create_tables[YOURLS_DB_TABLE_URL] =

'CREATE TABLE IF NOT EXISTS "'.YOURLS_DB_TABLE_URL.'" ('.

'"keyword" VARCHAR(100) NOT NULL DEFAULT \'\','.

'"url" TEXT NOT NULL,'.

'"title" TEXT DEFAULT NULL,'.

'"timestamp" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,'.

'"ip" VARCHAR(41) NOT NULL,'.

'"clicks" INTEGER NOT NULL DEFAULT 0,'.

'PRIMARY KEY ("keyword")'.

');';

$create_tables[YOURLS_DB_TABLE_OPTIONS] =

'CREATE TABLE IF NOT EXISTS "'.YOURLS_DB_TABLE_OPTIONS.'" ('.

'"option_id" BIGSERIAL,'.

'"option_name" VARCHAR(64) NOT NULL DEFAULT \'\','.

'"option_value" TEXT NOT NULL,'.

'PRIMARY KEY ("option_id", "option_name"),'.

'UNIQUE ("option_name")'.

');';

$create_tables[YOURLS_DB_TABLE_LOG] =

'CREATE TABLE IF NOT EXISTS "'.YOURLS_DB_TABLE_LOG.'" ('.

'"click_id" SERIAL,'.

'"click_time" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,'.

'"shorturl" VARCHAR(100) NOT NULL,'.

'"referrer" VARCHAR(200) NOT NULL DEFAULT \'\','.

'"user_agent" VARCHAR(255) NOT NULL DEFAULT \'\','.

'"ip_address" VARCHAR(41) NOT NULL DEFAULT \'\','.

'"country_code" CHAR(2) NOT NULL DEFAULT \'\','.

'PRIMARY KEY ("click_id")'.

');';

// Indexes to create after tables

$create_indexes = array();

$create_indexes[YOURLS_DB_TABLE_URL] = array(

'CREATE INDEX IF NOT EXISTS "ip" ON "'.YOURLS_DB_TABLE_URL.'" ("ip");',

'CREATE INDEX IF NOT EXISTS "timestamp" ON "'.YOURLS_DB_TABLE_URL.'" ("timestamp");',

'CREATE INDEX IF NOT EXISTS "url_idx" ON "'.YOURLS_DB_TABLE_URL.'" ("url");'

);

$create_indexes[YOURLS_DB_TABLE_OPTIONS] = array(

'CREATE INDEX IF NOT EXISTS "option_name" ON "'.YOURLS_DB_TABLE_OPTIONS.'" ("option_name");'

);

$create_indexes[YOURLS_DB_TABLE_LOG] = array(

'CREATE INDEX IF NOT EXISTS "shorturl" ON "'.YOURLS_DB_TABLE_LOG.'" ("shorturl");'

);

$create_table_count = 0;

yourls_debug_mode(true);

// Create tables

foreach ( $create_tables as $table_name => $table_query ) {

try {

$ydb->perform( $table_query );

// PostgreSQL equivalent of SHOW TABLES LIKE

$check_query = "SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = '$table_name'";

$create_success = $ydb->fetchValue( $check_query );

if( $create_success ) {

$create_table_count++;

$success_msg[] = yourls_s( "Table '%s' created.", $table_name );

// Create indexes for this table

if ( isset($create_indexes[$table_name]) ) {

foreach ( $create_indexes[$table_name] as $index_query ) {

try {

$ydb->perform( $index_query );

} catch (Exception $e) {

$error_msg[] = yourls_s( "Error creating index on table '%s': %s", $table_name, $e->getMessage() );

}

}

}

} else {

$error_msg[] = yourls_s( "Error creating table '%s'.", $table_name );

}

} catch (Exception $e) {

$error_msg[] = yourls_s( "Error creating table '%s': %s", $table_name, $e->getMessage() );

}

}

// Initializes the option table

if( !yourls_initialize_options() )

$error_msg[] = yourls__( 'Could not initialize options' );

// Insert sample links

if( !yourls_insert_sample_links() )

$error_msg[] = yourls__( 'Could not insert sample short URLs' );

// Check results of operations

if ( sizeof( $create_tables ) == $create_table_count ) {

$success_msg[] = yourls__( 'YOURLS tables successfully created.' );

} else {

$error_msg[] = yourls__( 'Error creating YOURLS tables.' );

}

return array( 'success' => $success_msg, 'error' => $error_msg );

}