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