Moving TYPO3 CMS to Doctrine DBAL

Moving TYPO3 CMS to Doctrine DBAL

Introduction to the Doctrine DBAL Integration in the TYPO3 CMS Core

C85dc89b5ca024dfd515bb4f3ddcac95?s=128

Morton Jonuschat

April 23, 2016
Tweet

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