From 6b7317c91fb949e3f0af0039e0f99f15d9c60c91 Mon Sep 17 00:00:00 2001 From: Max Semenik Date: Tue, 27 Dec 2011 12:29:36 +0000 Subject: [PATCH] Added support for stored procedures/functions to MySQL: * Refactored DatabaseBase::sourceStream(), made it possible for descendant classes to alter its behaviour w/o having to redo it completely like Oracle does. * MySQL class now supports specifying DELIMITER. * Thrown away the mess of catering for double semicolon. If it's a problem, fix your .sql files! * Haven't actually touched Oracle. * Tests! --- includes/db/Database.php | 50 ++++++++++---------- includes/db/DatabaseMysql.php | 9 ++++ includes/db/DatabasePostgres.php | 13 +++++ tests/phpunit/data/db/mysql/functions.sql | 12 +++++ tests/phpunit/data/db/postgres/functions.sql | 12 +++++ tests/phpunit/includes/db/DatabaseTest.php | 29 +++++++++++- 6 files changed, 99 insertions(+), 26 deletions(-) create mode 100644 tests/phpunit/data/db/mysql/functions.sql create mode 100644 tests/phpunit/data/db/postgres/functions.sql diff --git a/includes/db/Database.php b/includes/db/Database.php index bddce2b3af..02938727fe 100644 --- a/includes/db/Database.php +++ b/includes/db/Database.php @@ -228,6 +228,8 @@ abstract class DatabaseBase implements DatabaseType { protected $htmlErrors; + protected $delimiter = ';'; + # ------------------------------------------------------------------------------ # Accessors # ------------------------------------------------------------------------------ @@ -3156,19 +3158,17 @@ abstract class DatabaseBase implements DatabaseType { function sourceStream( $fp, $lineCallback = false, $resultCallback = false, $fname = 'DatabaseBase::sourceStream' ) { - $cmd = ""; + $cmd = ''; $done = false; - $dollarquote = false; - while ( ! feof( $fp ) ) { + while ( !feof( $fp ) ) { if ( $lineCallback ) { call_user_func( $lineCallback ); } $line = trim( fgets( $fp ) ); - $sl = strlen( $line ) - 1; - if ( $sl < 0 ) { + if ( $line == '' ) { continue; } @@ -3176,31 +3176,15 @@ abstract class DatabaseBase implements DatabaseType { continue; } - # # Allow dollar quoting for function declarations - if ( substr( $line, 0, 4 ) == '$mw$' ) { - if ( $dollarquote ) { - $dollarquote = false; - $done = true; - } - else { - $dollarquote = true; - } - } - elseif ( !$dollarquote ) { - if ( ';' == $line[$sl] && ( $sl < 2 || ';' != $line[$sl - 1] ) ) { - $done = true; - $line = substr( $line, 0, $sl ); - } - } - if ( $cmd != '' ) { $cmd .= ' '; } + $done = $this->streamStatementEnd( $cmd, $line ); + $cmd .= "$line\n"; - if ( $done ) { - $cmd = str_replace( ';;', ";", $cmd ); + if ( $done || feof( $fp ) ) { $cmd = $this->replaceVars( $cmd ); $res = $this->query( $cmd, $fname ); @@ -3221,6 +3205,24 @@ abstract class DatabaseBase implements DatabaseType { return true; } + /** + * Called by sourceStream() to check if we've reached a statement end + * + * @param $sql String: SQL assembled so far + * @param $newLine String: New line about to be added to $sql + * @returns Bool: Whether $newLine contains end of the statement + */ + protected function streamStatementEnd( &$sql, &$newLine ) { + if ( $this->delimiter ) { + $prev = $newLine; + $newLine = preg_replace( '/' . preg_quote( $this->delimiter, '/' ) . '$/', '', $newLine ); + if ( $newLine != $prev ) { + return true; + } + } + return false; + } + /** * Database independent variable replacement. Replaces a set of variables * in an SQL statement with their contents as given by $this->getSchemaVars(). diff --git a/includes/db/DatabaseMysql.php b/includes/db/DatabaseMysql.php index 7054c8bb25..95d3968be7 100644 --- a/includes/db/DatabaseMysql.php +++ b/includes/db/DatabaseMysql.php @@ -670,6 +670,15 @@ class DatabaseMysql extends DatabaseBase { } } + protected function streamStatementEnd( &$sql, &$newLine ) { + if ( strtoupper( substr( $newLine, 0, 9 ) ) == 'DELIMITER' ) { + preg_match( '/^DELIMITER\s+(\S+)/' , $newLine, $m ); + $this->delimiter = $m[1]; + $newLine = ''; + } + return parent::streamStatementEnd( $sql, $newLine ); + } + /** * @param $lockName string * @param $method string diff --git a/includes/db/DatabasePostgres.php b/includes/db/DatabasePostgres.php index 3506664ba7..222e3c11b9 100644 --- a/includes/db/DatabasePostgres.php +++ b/includes/db/DatabasePostgres.php @@ -1050,4 +1050,17 @@ SQL; public function getSearchEngine() { return 'SearchPostgres'; } + + protected function streamStatementEnd( &$sql, &$newLine ) { + # Allow dollar quoting for function declarations + if ( substr( $newLine, 0, 4 ) == '$mw$' ) { + if ( $this->delimiter ) { + $this->delimiter = false; + } + else { + $this->delimiter = ';'; + } + } + return parent::streamStatementEnd( $sql, $newLine ); + } } // end DatabasePostgres class diff --git a/tests/phpunit/data/db/mysql/functions.sql b/tests/phpunit/data/db/mysql/functions.sql new file mode 100644 index 0000000000..9e5e470f59 --- /dev/null +++ b/tests/phpunit/data/db/mysql/functions.sql @@ -0,0 +1,12 @@ +-- MySQL test file for DatabaseTest::testStoredFunctions() + +DELIMITER // + +CREATE FUNCTION mw_test_function() +RETURNS int DETERMINISTIC +BEGIN + SET @foo = 21; + RETURN @foo * 2; +END// + +DELIMITER // diff --git a/tests/phpunit/data/db/postgres/functions.sql b/tests/phpunit/data/db/postgres/functions.sql new file mode 100644 index 0000000000..3086d4d5a6 --- /dev/null +++ b/tests/phpunit/data/db/postgres/functions.sql @@ -0,0 +1,12 @@ +-- Postgres test file for DatabaseTest::testStoredFunctions() + +CREATE FUNCTION mw_test_function() +RETURNS INTEGER +LANGUAGE plpgsql AS +$mw$ +DECLARE foo INTEGER; +BEGIN + foo := 21; + RETURN foo * 2; +END +$mw$; diff --git a/tests/phpunit/includes/db/DatabaseTest.php b/tests/phpunit/includes/db/DatabaseTest.php index d480ac6e00..8592de9911 100644 --- a/tests/phpunit/includes/db/DatabaseTest.php +++ b/tests/phpunit/includes/db/DatabaseTest.php @@ -2,12 +2,20 @@ /** * @group Database + * @group DatabaseBase */ class DatabaseTest extends MediaWikiTestCase { - var $db; + var $db, $functionTest = false; function setUp() { - $this->db = wfGetDB( DB_SLAVE ); + $this->db = wfGetDB( DB_MASTER ); + } + + function tearDown() { + if ( $this->functionTest ) { + $this->dropFunctions(); + $this->functionTest = false; + } } function testAddQuotesNull() { @@ -90,6 +98,23 @@ class DatabaseTest extends MediaWikiTestCase { $sql ); } + function testStoredFunctions() { + if ( !in_array( wfGetDB( DB_MASTER )->getType(), array( 'mysql', 'postgres' ) ) ) { + $this->markTestSkipped( 'MySQL or Postgres required' ); + } + global $IP; + $this->dropFunctions(); + $this->functionTest = true; + $this->assertTrue( $this->db->sourceFile( "$IP/tests/phpunit/data/db/{$this->db->getType()}/functions.sql" ) ); + $res = $this->db->query( 'SELECT mw_test_function() AS test', __METHOD__ ); + $this->assertEquals( 42, $res->fetchObject()->test ); + } + + private function dropFunctions() { + $this->db->query( 'DROP FUNCTION IF EXISTS mw_test_function' + . ( $this->db->getType() == 'postgres' ? '()' : '' ) + ); + } } -- 2.20.1