Moving TYPO3 CMS to Doctrine DBAL (Version 2)

Moving TYPO3 CMS to Doctrine DBAL (Version 2)

Updated version of the original talk that includes slides about the QueryRestrictions. First held on the TYPO3camp Stuttgart.

C85dc89b5ca024dfd515bb4f3ddcac95?s=128

Morton Jonuschat

June 11, 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 • QueryRestrictions 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 • Neither a magic bullet against SQL injections QueryBuilder
  18. Migrating to Doctrine DBAL Morton Jonuschat 18 • Replace the

    venerable enableFields() and friends • Fine grained control which restrictions to apply • … frontend user groups • …access time • …disable flage • …versioning • …workspaces • …deleted flag QueryRestrictions
  19. Migrating to Doctrine DBAL Morton Jonuschat 19 • Automatically enabled

    in Backend & Frontend • Add constraints to SELECT statements • Active by default: • DeletedRestriction • HiddenRestriction • StartTimeRestriction • EndTimeRestriction QueryRestrictions
  20. Migrating to Doctrine DBAL Morton Jonuschat 20 • Can be

    controlled by predefined containers • DefaultRestrictionContainer • FrontendRestrictionContainer • Additional restrictions can be added incrementally • Active restrictions can be removed one by one • … or as a whole QueryRestrictions
  21. How-To Migrate

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

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

  24. Migrating to Doctrine DBAL Morton Jonuschat 24 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()
  25. Migrating to Doctrine DBAL Morton Jonuschat 25 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()
  26. Migrating to Doctrine DBAL Morton Jonuschat 26 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()
  27. Migrating to Doctrine DBAL Morton Jonuschat 27 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()
  28. Migrating to Doctrine DBAL Morton Jonuschat 28 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()
  29. Migrating to Doctrine DBAL Morton Jonuschat 29 BEFORE $result =

    $db->exec_INSERTquery(
 'test_table', ['aField' => 'aValue']
 ); AFTER $affectedRows = $queryBuilder->insert('test_table') ->values(['aField' =>'aValue']) ->execute(); exec_INSERTquery()
  30. Migrating to Doctrine DBAL Morton Jonuschat 30 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()
  31. Migrating to Doctrine DBAL Morton Jonuschat 31 BEFORE $result =

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

  33. Migrating to Doctrine DBAL Morton Jonuschat 33 '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
  34. Migrating to Doctrine DBAL Morton Jonuschat 34 '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
  35. Migrating to Doctrine DBAL Morton Jonuschat 35 '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%'
  36. Migrating to Doctrine DBAL Morton Jonuschat 36 '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)
  37. Migrating to Doctrine DBAL Morton Jonuschat 37 '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
  38. Migrating to Doctrine DBAL Morton Jonuschat 38 'page_perms & '

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

    'MAX(aField)' ↪ MAX(aField) 'SUM(aField)' ↪ SUM(aField) 'AVG(aField)' ↪ AVG(aField) 'COUNT(aField)' ↪ COUNT(aField) Aggregations $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`)
  40. Migrating to Doctrine DBAL Morton Jonuschat 40 QueryRestrictions

  41. Migrating to Doctrine DBAL Morton Jonuschat 41 • DeletedRestriction •

    HiddenRestriction • RootLevelRestriction • StartTimeRestriction • EndTimeRestriction • FrontendGroupRestriction • BackendWorkspaceRestriction • FrontendWorkspaceRestriction Available Restrictions
  42. Migrating to Doctrine DBAL Morton Jonuschat 42 Restrictions Presets $queryBuilder->setRestrictions(


    GeneralUtility::makeInstance(FrontendRestrictionContainer::class) );
  43. Migrating to Doctrine DBAL Morton Jonuschat 43 Managing Restrictions $queryBuilder->getRestrictions()->add(

    GeneralUtility::makeInstance(DeletedRestriction::class) ) $queryBuilder->getRestrictions()->removeByType( HiddenRestriction::class ) $queryBuilder->getRestrictions()->removeAll() $queryBuilder->getRestrictions()
 ->removeAll() ->add(GeneralUtility::makeInstance(DeletedRestriction::class)) ->add(GeneralUtility::makeInstance(BackendWorkspaceRestriction::class));
  44. Migrating to Doctrine DBAL Morton Jonuschat 44 Helpers

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

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

  47. Migrating to Doctrine DBAL Morton Jonuschat 47 • 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...
  48. Migrating to Doctrine DBAL Morton Jonuschat 48 • 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
  49. Migrating to Doctrine DBAL Morton Jonuschat 49 • 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
  50. Migrating to Doctrine DBAL Morton Jonuschat 50 BEFORE $result =

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

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

    $query->expr()->eq( 'aField', $queryBuilder->createPositionalParameter('aValue') ) ) ->getSQL() ↪ SELECT * FROM `aTable` WHERE `aField` = ? Positional Parameters
  53. Migrating to Doctrine DBAL Morton Jonuschat 53 • 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
  54. Migrating to Doctrine DBAL Morton Jonuschat 54 Common Patterns

  55. Migrating to Doctrine DBAL Morton Jonuschat 55 $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
  56. Migrating to Doctrine DBAL Morton Jonuschat 56 $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.)
  57. Migrating to Doctrine DBAL Morton Jonuschat 57 $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.)
  58. Migrating to Doctrine DBAL Morton Jonuschat 58 $queryBuilder->select('aField AS anotherName')

    -> from('aTable'); ↪ SELECT `aField` AS `anotherName` FROM `aTable` Column Aliases
  59. Migrating to Doctrine DBAL Morton Jonuschat 59 $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
  60. Migrating to Doctrine DBAL Morton Jonuschat Q&A ? 60

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