Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Moving TYPO3 CMS to Doctrine DBAL

Moving TYPO3 CMS to Doctrine DBAL

Introduction to the Doctrine DBAL Integration in the TYPO3 CMS Core

Morton Jonuschat

April 23, 2016
Tweet

More Decks by Morton Jonuschat

Other Decks in Programming

Transcript

  1. Morton Jonuschat MoJo Code Doctrine DBAL

  2. TYPO3 Core Team Member twitter.com/yabawock github.com/yabawock Haltern am See, Germany

    Morton Jonuschat Migrating to Doctrine DBAL Morton Jonuschat 2
  3. Migrating to Doctrine DBAL Morton Jonuschat 3 Architecture ConnectionPool, QueryBuilder,

    Context, ExpressionBuilder !? How-To How-To migrate from DatabaseConnection to Doctrine. Background Why did we switch to Doctrine DBAL when we already have EXT:dbal Agenda
  4. Background

  5. Migrating to Doctrine DBAL Morton Jonuschat 5 Why switch?

  6. Migrating to Doctrine DBAL Morton Jonuschat 6 • Default database

    connection handling is MySQL only • Behavior dependent on MySQL features • Non-Standard SQL data types in use • Hard to implement features • Interface doesn’t follow modern conventions Why switch?
  7. Migrating to Doctrine DBAL Morton Jonuschat 7 • Not a

    first class citizen • Depends on ADOdb library, questionable maintenance state • Implements the smallest common denominator for DBMS • Hopeless codebase, little support in core team BUT • …it kinda sorta works™ • …nice unique features EXT:dbal
  8. Migrating to Doctrine DBAL Morton Jonuschat 8 • multiple connections

    in parallel • remapping tables to non-default databases • mapping too long table names • rewriting queries dependent on database platform Unique features
  9. Migrating to Doctrine DBAL Morton Jonuschat 9 How to proceed?

  10. Migrating to Doctrine DBAL Morton Jonuschat 10 • Usage of

    MySQL specific functions like FIND_IN_SET() • No proper quoting of SQL identifiers • Implicit data type conversions • Features that have been „tacked“ on as an afterthought Challenges
  11. Migrating to Doctrine DBAL Morton Jonuschat 11 • Replace homegrown

    DB abstraction layer • Remove dependency on ADOdb • Replace MySQL specific connection handling • Use standard compliant SQL • Keep unique features • Implement database agnostic schema migrations Solution
  12. Architecture

  13. Migrating to Doctrine DBAL Morton Jonuschat 13 • ConnectionPool •

    Connection • ExpressionBuilder • QueryBuilder • QueryContext • QueryRestrictionBuilder The Players
  14. Migrating to Doctrine DBAL Morton Jonuschat 14 • Manages all

    database connections • Keeps connections available once opened • Main entry point for everything DB • Replaces $GLOBALS['TYPO3_DB'] • Only two public methods • getConnectionForTable($tableName) • getQueryBuilderForTable($tableName) ConnectionPool
  15. Migrating to Doctrine DBAL Morton Jonuschat 15 • Database independent

    connection interface • Behaves the same, independent of DBMS or driver • Provides the most direct interface to the database • Provides convenience methods for all common operations Connection
  16. Migrating to Doctrine DBAL Morton Jonuschat 16 • Builds SQL

    expressions (conditions / constraints) • Object oriented interface • Knows differences between DBMS • Ensures proper quoting of table and column names • Supports all common expressions • Not a magic bullet against SQL injections ExpressionBuilder
  17. Migrating to Doctrine DBAL Morton Jonuschat 17 • Builds SQL

    statements specific to the DBMS • Object oriented interface • Ensures proper quoting of table and filenames • Supports INSERT, SELECT, UPDATE, DELETE & TRUNCATE • Automatically takes query context into account • Neither a magic bullet against SQL injections QueryBuilder
  18. Migrating to Doctrine DBAL Morton Jonuschat 18 • Backend, Backend

    + Versioning, Frontend & Unrestricted • Control for additional constraints based on • … frontend user groups • …access time • …enable fields • …versioning • …workspaces • …deleted flag QueryContext
  19. Migrating to Doctrine DBAL Morton Jonuschat 19 • Builds SQL

    constraints for SELECT statements • Automatically enabled in Backend & Frontend • Less cross cutting concerns than enableFields() • Tries to provide intelligent defaults • …but gets out of your hair for special queries QueryRestrictionBuilder
  20. How-To Migrate

  21. Migrating to Doctrine DBAL Morton Jonuschat 21 If you are

    using Extbase: DONE* *Unless you use $query->statement() Migrating for Extension Authors
  22. Migrating to Doctrine DBAL Morton Jonuschat 22 Common Commands

  23. Migrating to Doctrine DBAL Morton Jonuschat 23 BEFORE $sql =

    $db->SELECTquery(
 '*', 
 'sys_refindex', 
 'ref_table='. $db->fullQuoteStr(‚_FILE', 'sys_refindex') . ' AND ' . 'ref_string LIKE ' . $db->fullQuoteStr(‚%/RTEmagic%‘,'sys_refindex') .
 ' AND softref_key=' . $db->fullQuoteStr('images', 'sys_refindex'), '', 'sorting DESC’ ); AFTER $sql = $queryBuilder->select('*') ->from('sys_refindex') ->where($query->expr()->eq('ref_table', $queryBuilder->quote('_FILE'))) ->andWhere( $query->expr()->like('ref_string', $queryBuilder->quote('%/RTEmagic%'))) ->andWhere( $query->expr()->eq('softref_key', $queryBuilder->quote('images'))) ->orderBy('sorting', 'DESC'); SELECTquery()
  24. Migrating to Doctrine DBAL Morton Jonuschat 24 BEFORE $result =

    $db->exec_SELECTquery(
 '*', 
 'sys_refindex', 
 'ref_table='. $db->fullQuoteStr(‚_FILE', 'sys_refindex') . ' AND ' . 'ref_string LIKE ' . $db->fullQuoteStr(‚%/RTEmagic%‘,'sys_refindex') .
 ' AND softref_key=' . $db->fullQuoteStr('images', 'sys_refindex'), '', 'sorting DESC’ ); AFTER $result = $queryBuilder->select('*') ->from('sys_refindex') ->where($query->expr()->eq('ref_table', $queryBuilder->quote('_FILE'))) ->andWhere($query->expr()->like('ref_string', $queryBuilder->quote('%/RTEmagic%'))) ->andWhere( $query->expr()->eq('softref_key', $queryBuilder- >quote('images'))) ->orderBy('sorting', 'DESC') ->execute(); exec_SELECTquery()
  25. Migrating to Doctrine DBAL Morton Jonuschat 25 BEFORE $rows =

    $db->exec_SELECTgetRows(
 '*', 
 'sys_refindex', 
 'ref_table='. $db->fullQuoteStr(‚_FILE', 'sys_refindex') . ' AND ' . 'ref_string LIKE ' . $db->fullQuoteStr(‚%/RTEmagic%‘,'sys_refindex'), '', 'sorting DESC’ ); AFTER $rows = $queryBuilder->select('*') ->from('sys_refindex') ->where($query->expr()->eq('ref_table', $queryBuilder->quote('_FILE'))) ->andWhere($query->expr()->like('ref_string', $queryBuilder->quote('%/RTEmagic%'))) ->orderBy('sorting', 'DESC') ->execute()
 ->fetchAll(); exec_SELECTgetRows()
  26. Migrating to Doctrine DBAL Morton Jonuschat 26 BEFORE $row =

    $db->exec_SELECTgetSingleRow(
 '*', 
 'sys_refindex', 
 'ref_table='. $db->fullQuoteStr(‚_FILE', 'sys_refindex') . ' AND ' . 'ref_string LIKE ' . $db->fullQuoteStr(‚%/RTEmagic%‘,'sys_refindex'), '', 'sorting DESC’ ); AFTER $row = $queryBuilder->select('*') ->from('sys_refindex') ->where($query->expr()->eq('ref_table', '_FILE')) ->andWhere($query->expr()->like('ref_string', $queryBuilder->quote('%/RTEmagic%'))) ->orderBy('sorting', 'DESC') ->setMaxResults(1) ->execute()
 ->fetch(); exec_SELECTgetSingleRow()
  27. Migrating to Doctrine DBAL Morton Jonuschat 27 BEFORE $count =

    $db->exec_SELECTcountRows(
 '*', 
 'sys_refindex', 
 'ref_table='. $db->fullQuoteStr(‚_FILE', 'sys_refindex') . ' AND ' . 'ref_string LIKE ' . $db->fullQuoteStr(‚%/RTEmagic%‘,'sys_refindex') ); AFTER $count = $queryBuilder->count('*') ->from('sys_refindex') ->where($query->expr()->eq('ref_table', '_FILE')) ->andWhere($query->expr()->like('ref_string', $queryBuilder->quote('%/RTEmagic%'))) ->execute()
 ->fetchColumn(); exec_SELECTcountRows()
  28. Migrating to Doctrine DBAL Morton Jonuschat 28 BEFORE $result =

    $db->exec_INSERTquery(
 'test_table', ['aField' => 'aValue']
 ); AFTER $affectedRows = $queryBuilder->insert('test_table') ->values(['aField' =>'aValue']) ->execute(); exec_INSERTquery()
  29. Migrating to Doctrine DBAL Morton Jonuschat 29 BEFORE $result =

    $db->exec_UPDATEquery(
 'test_table', 'uid = 7', ['aField' => 'aValue', 'anotherField' => 2]
 ); AFTER $count = $queryBuilder->update('test_table') ->set('aField', 'aValue') ->set('anotherField', 2) ->where($queryBuilder->expr()->eq('uid', 7)) ->execute(); exec_UPDATEquery()
  30. Migrating to Doctrine DBAL Morton Jonuschat 30 BEFORE $result =

    $db->exec_DELETEquery(
 'test_table', 'uid >= 7'
 ); AFTER $count = $queryBuilder->delete('test_table') ->where($queryBuilder->expr()->gte('uid', 7)) ->execute(); exec_DELETEquery()
  31. Migrating to Doctrine DBAL Morton Jonuschat 31 ExpressionBuilder

  32. Migrating to Doctrine DBAL Morton Jonuschat 32 'aField = '

    . $aValue; ↪ aField = 1 'aField != ' . $aValue; ↪ aField != 1 'aField IS NULL'; ↪ aField IS NULL 'aField IS NOT NULL'; ↪ aField IS NOT NULL Equals / Not Equal $expr->eq('aField', $aValue); ↪ `aField` = 1 $expr->neq('aField', $aValue); ↪ `aField` <> 1 $expr->isNull('aField'); ↪ `aField` IS NULL $expr->isNotNUll('aField'); ↪ `aField` IS NOT NULL
  33. Migrating to Doctrine DBAL Morton Jonuschat 33 'aField < '

    . $aValue; ↪ aField < 1 'aField <= ' . $aValue; ↪ aField <= 1 'aField > ' . $aValue; ↪ aField > 1 'aField > ' . $aValue; ↪ aField >= 1 Less Than and friends $expr->lt('aField', $aValue); ↪ `aField` < 1 $expr->lte('aField', $aValue); ↪ `aField` <= 1 $expr->gt('aField', $aValue); ↪ `aField` > 1 $expr->gte('aField', $aValue); ↪ `aField` >= 1
  34. Migrating to Doctrine DBAL Morton Jonuschat 34 'aField LIKE '

    . $aValue; ↪ aField LIKE '%aValue%' 'aField NOT LIKE ' . $aValue; ↪ aField NOT LIKE '%aValue%' Like / Not Like $expr->like('aField', $aValue); ↪ `aField` LIKE '%aValue%' $expr->notLike('aField', $aValue); ↪ `aField` NOT LIKE '%aValue%'
  35. Migrating to Doctrine DBAL Morton Jonuschat 35 'aField IN ('

    . implode(',', $aValue) . ')'; ↪ aField IN (1,2,3) 'aField NOT IN (' . implode(',', $aValue) . ')'; ↪ aField NOT IN (1,2,3) Multi Valued $expr->in('aField',[1,2,3]);
 
 ↪ `aField` IN (1, 2, 3) $expr->notIn('aField', $aValue);
 
 ↪ `aField` NOT IN (1, 2, 3)
  36. Migrating to Doctrine DBAL Morton Jonuschat 36 'FIND_IN_SET(' . $aValue

    . ', aField)' ↪ FIND_IN_SET(1, aField) FIND_IN_SET() $expr->inSet('aField',1);
 
 ↪ FIND_IN_SET(1, `aField`) ↪ any(string_to_array("aField", ',')) = 1
  37. Migrating to Doctrine DBAL Morton Jonuschat 37 'page_perms & '

    . $aValue ↪ page_perms & 1 Bitwise AND $expr->bitAnd('page_perms',1);
 
 ↪ `page_perms` & 1 ↪ BITAND(`page_perms`, 1)
  38. Migrating to Doctrine DBAL Morton Jonuschat 38 'MIN(aField)' ↪ MIN(aField)

    'MAX(aField)' ↪ MAX(aField) 'SUM(aField)' ↪ SUM(aField) 'AVG(aField)' ↪ AVG(aField) 'COUNT(aField)' ↪ COUNT(aField) Aggregations (WIP) $expr->min('aField');
 ↪ MIN(`page_perms`) $expr->max('aField');
 ↪ MAX(`page_perms`) $expr->sum('aField');
 ↪ SUM(`page_perms`) $expr->avg('aField');
 ↪ AVG(`page_perms`) $expr->count('aField');
 ↪ COUNT(`page_perms`)
  39. Migrating to Doctrine DBAL Morton Jonuschat 39 Helpers

  40. Migrating to Doctrine DBAL Morton Jonuschat 40 • searchQuery() •

    $expressionBuilder->search()* • listQuery() • $expressionBuilder->inSet() • prepare_SELECTquery() • prepare_PREPAREDquery() • Gone, Doctrine always uses prepared Statements • cleanIntList() • GeneralUtility::intExplode() • escapeStrForLike() • To be determined • fullQuoteStr() • $queryBuilder->quote() • fullQuoteArray() • Gone, done behind the scenes DatabaseConnection Helpers
  41. Migrating to Doctrine DBAL Morton Jonuschat 41 Security (User Input)

  42. Migrating to Doctrine DBAL Morton Jonuschat 42 • NOT SAFE

    for use in table- and fieldnames • NOT SAFE for use in raw SQL fragments • NOT SAFE to blindly insert into the database • NOT SAFE AT ALL!! User Input is...
  43. Migrating to Doctrine DBAL Morton Jonuschat 43 • Connection operates

    with named parameters • QueryBuilder is supportive • ->set(), setValue() and ->values() use named parameters • all methods quote identifiers by default • ExpressionBuilder helps writing clean SQL Having said that
  44. Migrating to Doctrine DBAL Morton Jonuschat 44 • Use positional

    or named parameters • Quote the value for the database • Cast it to the expected type • Perform extra sanitization if required (HTML, Javascript etc.) When in doubt
  45. Migrating to Doctrine DBAL Morton Jonuschat 45 BEFORE $result =

    $db->fullQuoteStr('aValue', 'test_table);
 AFTER $result = $queryBuilder->quote('aValue'); Quoting Data
  46. Migrating to Doctrine DBAL Morton Jonuschat 46 $queryBuilder->select('*') ->from('aTable') ->where(

    $query->expr()->eq( 'aField', $queryBuilder->createNamedParameter('aValue') ) ) ->getSQL() ↪ SELECT * FROM `aTable` WHERE `aField` = :dcValue1 Named Parameters
  47. Migrating to Doctrine DBAL Morton Jonuschat 47 $queryBuilder->select('*') ->from('aTable') ->where(

    $query->expr()->eq( 'aField', $queryBuilder->createPositionalParameter('aValue') ) ) ->getSQL() ↪ SELECT * FROM `aTable` WHERE `aField` = ? Positional Parameters
  48. Migrating to Doctrine DBAL Morton Jonuschat 48 • Use the

    QueryBuilder as your preferred tool • Use the ExpressionBuilder to build your query conditions • Use named parameters for data, especially in expressions Migrating for Extension Authors
  49. Migrating to Doctrine DBAL Morton Jonuschat 49 Common Patterns

  50. Migrating to Doctrine DBAL Morton Jonuschat 50 $expressionBuilder->andX( $expressionBuilder->eq('uid', 7),

    $expressionBuilder->eq('pid', 1), ) ↪ (`uid` = 7 AND `pid`= 1) $expressionBuilder->orX( $expressionBuilder->eq('uid', 7), $expressionBuilder->eq('pid', 1), ) ↪ (`uid` = 7 OR `pid`= 1) Composite Expression
  51. Migrating to Doctrine DBAL Morton Jonuschat 51 $expressionBuilder->andX( $expressionBuilder->eq('uid', 7),

    $expressionBuilder->eq('pid', 1), $expressionBuilder->orX( $expressionBuilder->eq('group', 0) $expressionBuilder->isNull('group') ) ) ↪ (`uid` = 7 AND `pid`= 1 AND (`group` = 0 OR `group` IS NULL)) Composite Expression (cont.)
  52. Migrating to Doctrine DBAL Morton Jonuschat 52 $constraint = $expressionBuilder->andX();

    $constraint->add($expressionBuilder->eq('uid', 7)); $constraint->add($expressionBuilder->eq('pid', 1)); $constraint->add( $expressionBuilder->orX( $expressionBuilder->eq('group', 0) $expressionBuilder->isNull('group') ) ); ↪ (`uid` = 7 AND `pid`= 1 AND (`group` = 0 OR `group` IS NULL)) Composite Expression (cont.)
  53. Migrating to Doctrine DBAL Morton Jonuschat 53 $queryBuilder->select('aField AS anotherName')

    -> from('aTable'); ↪ SELECT `aField` AS `anotherName` FROM `aTable` Column Aliases (WIP)
  54. Migrating to Doctrine DBAL Morton Jonuschat 54 $queryBuilder->selectLiteral( $queryBuilder->expr()->max('aField', 'maximum'),

    ) ->addSelectLiteral($queryBuilder->expr()->min('aField')) -> from('aTable'); ↪ SELECT MAX(`aField`) AS `maximum`, MIN(`aField`) FROM `aTable` Selecting Expressions (WIP)
  55. Migrating to Doctrine DBAL Morton Jonuschat Q&A ? 55

  56. Migrating to Doctrine DBAL Morton Jonuschat Thanks For Listening 56