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