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.

Transcript

1. Morton Jonuschat
   MoJo Code
   Doctrine DBAL
   

   View Slide

2. TYPO3 Core Team Member
   twitter.com/yabawock
   github.com/yabawock
   Haltern am See, Germany
   Morton Jonuschat
   Migrating to Doctrine DBAL Morton Jonuschat
   2
   

   View Slide

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
   

   View Slide

4. Background
   

   View Slide

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

   View Slide

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?
   

   View Slide

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
   

   View Slide

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
   

   View Slide

9. Migrating to Doctrine DBAL Morton Jonuschat
   9
   How to proceed?
   

   View Slide

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
    

    View Slide

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
    

    View Slide

12. Architecture
    

    View Slide

13. Migrating to Doctrine DBAL Morton Jonuschat
    13
    • ConnectionPool
    • Connection
    • ExpressionBuilder
    • QueryBuilder
    • QueryRestrictions
    The Players
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

21. How-To Migrate
    

    View Slide

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

    View Slide

23. Migrating to Doctrine DBAL Morton Jonuschat
    23
    Common Commands
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

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()
    

    View Slide

32. Migrating to Doctrine DBAL Morton Jonuschat
    32
    ExpressionBuilder
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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%'
    

    View Slide

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)
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

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`)
    

    View Slide

40. Migrating to Doctrine DBAL Morton Jonuschat
    40
    QueryRestrictions
    

    View Slide

41. Migrating to Doctrine DBAL Morton Jonuschat
    41
    • DeletedRestriction
    • HiddenRestriction
    • RootLevelRestriction
    • StartTimeRestriction
    • EndTimeRestriction
    • FrontendGroupRestriction
    • BackendWorkspaceRestriction
    • FrontendWorkspaceRestriction
    Available Restrictions
    

    View Slide

42. Migrating to Doctrine DBAL Morton Jonuschat
    42
    Restrictions Presets
    $queryBuilder->setRestrictions(

    GeneralUtility::makeInstance(FrontendRestrictionContainer::class)
    );
    

    View Slide

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));
    

    View Slide

44. Migrating to Doctrine DBAL Morton Jonuschat
    44
    Helpers
    

    View Slide

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
    

    View Slide

46. Migrating to Doctrine DBAL Morton Jonuschat
    46
    Security (User Input)
    

    View Slide

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...
    

    View Slide

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
    

    View Slide

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
    

    View Slide

50. Migrating to Doctrine DBAL Morton Jonuschat
    50
    BEFORE
    $result = $db->fullQuoteStr('aValue', 'test_table);

    AFTER
    $result = $queryBuilder->quote('aValue');
    Quoting Data
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

54. Migrating to Doctrine DBAL Morton Jonuschat
    54
    Common Patterns
    

    View Slide

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
    

    View Slide

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.)
    

    View Slide

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.)
    

    View Slide

58. Migrating to Doctrine DBAL Morton Jonuschat
    58
    $queryBuilder->select('aField AS anotherName')
    -> from('aTable');
    ↪ SELECT `aField` AS `anotherName` FROM `aTable`
    Column Aliases
    

    View Slide

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
    

    View Slide

60. Migrating to Doctrine DBAL Morton Jonuschat
    Q&A
    ?
    60
    

    View Slide

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

    View Slide