Moving TYPO3 CMS to Doctrine DBAL

Introduction to the Doctrine DBAL Integration in the TYPO3 CMS Core

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
    • QueryContext
    • QueryRestrictionBuilder
    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
    • Automatically takes query context into account
    • Neither a magic bullet against SQL injections
    QueryBuilder
    

    View Slide

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
    

    View Slide

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
    

    View Slide

20. How-To Migrate
    

    View Slide

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

    View Slide

22. Migrating to Doctrine DBAL Morton Jonuschat
    22
    Common Commands
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

31. Migrating to Doctrine DBAL Morton Jonuschat
    31
    ExpressionBuilder
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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)
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

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

    View Slide

39. Migrating to Doctrine DBAL Morton Jonuschat
    39
    Helpers
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

49. Migrating to Doctrine DBAL Morton Jonuschat
    49
    Common Patterns
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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)
    

    View Slide

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

    View Slide

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

    View Slide