DatabaseConnectionMssqlTest.php

Go to the documentation of this file.

<?php
namespace TYPO3\CMS\Dbal\Tests\Unit\Database;

class DatabaseConnectionMssqlTest extends AbstractTestCase {

    protected $subject;

    public function setUp() {
        $configuration = array(
            'handlerCfg' => array(
                '_DEFAULT' => array(
                    'type' => 'adodb',
                    'config' => array(
                        'driver' => 'mssql',
                    ),
                ),
            ),
            'mapping' => array(
                'tx_templavoila_tmplobj' => array(
                    'mapFieldNames' => array(
                        'datastructure' => 'ds',
                    ),
                ),
                'Members' => array(
                    'mapFieldNames' => array(
                        'pid' => '0',
                        'cruser_id' => '1',
                        'uid' => 'MemberID',
                    ),
                ),
            ),
        );
        $this->subject = $this->prepareSubject('mssql', $configuration);
    }

    public function runningADOdbDriverReturnsTrueWithMssqlForMssqlDefaultDriverConfiguration() {
        $this->assertTrue($this->subject->runningADOdbDriver('mssql'));
    }

    public function findInSetIsProperlyRemapped() {
        $expected = 'SELECT * FROM "fe_users" WHERE \',\'+"usergroup"+\',\' LIKE \'%,10,%\'';
        $result = $this->subject->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)');
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function canParseSingleQuote() {
        $parseString = 'SELECT * FROM pages WHERE title=\'1\'\'\' AND deleted=0';
        $components = $this->subject->SQLparser->_callRef('parseSELECT', $parseString);

        $this->assertTrue(is_array($components), $components);
        $this->assertEmpty($components['parseString']);
    }

    // Tests concerning remapping with
    // external (non-TYPO3) databases

    public function canRemapPidToZero() {
        $selectFields = 'uid, FirstName, LastName';
        $fromTables = 'Members';
        $whereClause = 'pid=0 AND cruser_id=1';
        $groupBy = '';
        $orderBy = '';

        $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
        $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
        $expected = 'SELECT "MemberID", "FirstName", "LastName" FROM "Members" WHERE 0 = 0 AND 1 = 1';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    // Tests concerning advanced operators

    public function locateStatementIsProperlyQuoted() {
        $result = $this->subject->SELECTquery('*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' . ' ELSE 1' . ' END AS scope', 'tx_templavoila_tmplobj', '1=1');
        $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function locateStatementWithPositionIsProperlyQuoted() {
        $result = $this->subject->SELECTquery('*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope', 'tx_templavoila_tmplobj', '1=1');
        $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function locateStatementIsProperlyRemapped() {
        $selectFields = '*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope';
        $fromTables = 'tx_templavoila_tmplobj';
        $whereClause = '1=1';
        $groupBy = '';
        $orderBy = '';
        $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);

        $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
        $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function locateStatementWithExternalTableIsProperlyRemapped() {
        $selectFields = '*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope';
        $fromTables = 'tx_templavoila_tmplobj';
        $whereClause = '1=1';
        $groupBy = '';
        $orderBy = '';
        $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);

        $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
        $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "tx_templavoila_tmplobj"."ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function IfNullIsProperlyRemapped() {
        $result = $this->subject->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)');
        $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

    public function singleQuotesAreProperlyEscaped() {
        $result = $this->subject->SELECTquery(
            'ISEC.phash',
            'index_section ISEC, index_fulltext IFT',
            'IFT.fulltextdata LIKE \'%' . $this->subject->quoteStr("Don't worry", 'index_fulltext')
            . '%\' AND ISEC.phash = IFT.phash',
            'ISEC.phash'
        );
        $expected = 'SELECT "ISEC"."phash" FROM "index_section" "ISEC", "index_fulltext" "IFT" WHERE "IFT"."fulltextdata" LIKE \'%Don\'\'t worry%\' AND "ISEC"."phash" = "IFT"."phash" GROUP BY "ISEC"."phash"';
        $this->assertEquals($expected, $this->cleanSql($result));
    }

}