Ibiza, June 4 th – 7 th 2011

1. Ibiza, June 4th – 7th2011

2. by Andrey Tserkus, Magento Core Team Magento & Multiple RDBMS

3. Do We Want Multiple RDBMS Support?

4. Do We Want Multiple RDBMS Support? Store owners: reduced cost, increased store launch speed • Common infrastructure • No additional licensing • Trained staff

5. Do We Want Multiple RDBMS Support? Developers: more money and clients, more fun • Wider area of customization clients • Bigger market to sell extensions • Automatic cross-DB support • Working with new technologies

6. We Are Not Alone

7. Percentage of Companies, Owning Concrete RDBMS Embarcadero Technologies survey, Dec 2010

8. D&D: Databases Differences

9. Each Vendor Has Its Own Vision • All RDBMS use common language – SQL. But each of them has its own dialect. • Different function names. • Internal architecture differs substantially.

10. Identifiers Length

11. Identifiers Length Magento Core uses identifiers up to 30 symbols • Follow this practice to make your module cross-DB compatible • Do not follow this practice to feel free and target only specific RDBMS

12. Limits There are no “LIMIT” constructions outside MySQL. At all.

13. Limits in Magento Main SELECT Limiting SELECT

14. Limits in Magento Magento automatically wraps SELECT and fetches records by number SELECT * FROM ( SELECT *, ROW_NUMBER() AS n FROM table ) WHERE n >= 5 AND n <= 6 SELECT * FROM table , ROW_NUMBER() AS n

15. Identifiers Quoting

16. Identifiers Quoting Magento has abstract adapter method to quote identifiers $query = 'UPDATE ' . $adapter->quoteIdentifier('table') . ' SET ' . $adapter->quoteIdentifier('col') . ' = 1';

17. Identifiers Quoting

18. Identifiers Quoting Magento automatically quotes data in DDL/DML routines $columns = array('col'=>1); $adapter->update('table',$columns);

19. Empty Strings Empty string in Oracle is NULL UPDATE … WHERE a is NULL

20. Identifiers Length Magento uses NULLs instead of empty strings • Change logic to use NULLs • Form conditions, using adapter

21. Other MySQL Specific Things Adapter and resource helpers emulate specifics • INSERT … ON DUPLICATE $adapter->insertOnDuplicate(); • SELECT a, b … GROUP BY a $helper->getQueryUsingAnalyticFunction(); • and many more...

22. DB Abstraction in Magento

23. DB Abstraction in Magento CE 1.5 / EE 1.10 Model Mysql4 (resource) model MySQL adapter MySQL

24. Support for other RDBMS (predicted) Model Mysql4 (resource) model Oracle (resource) model MySQL adapter Oracle adapter MySQL Oracle

25. Support for Other RDBMS (Predicted) • Worked out with Magento 1.0 in 2008 • Requires implementation of resource models for 70 CE + 30 EE modules (total 500 resource models) • Lot of code/logic duplication

26. DB Abstraction in Magento CE 1.6+ / EE 1.11+ Model Mysql4 resource helper Resource model MySQL adapter MySQL

27. Resource Helper • Holds module’s specifics for concrete DB, too rare to be covered by adapter • Magento automatically chooses resource helper for current DB: $where = Mage::getResourceHelper('catalogsearch') ->chooseFulltext(...);

28. Support for Other RDBMS Model Oracle resource helper Mysql4 resource helper Resource model MySQL adapter Oracle adapter MySQL Oracle

29. Support for Other RDBMS • Requires moving resource models to MMDB-rails only once • No code duplication. Just specific queries syntax is placed in resource helpers (actually, 13 were created)

30. Magento Adapters Concrete Zend_Db_Adapter_* Concrete Varien_Db_Adapter_*

31. Varien_Db_Adapter • Varien_Db_Adapter_*: • Abstracts DDL & DML for Magento • Follows PDO style • Works with query placeholders ('WHERE a = ? AND b = :val') and BLOBs

32. Creating Tables Varien_Db_Ddl_Table Varien_Db_Adapter_* +addColumn() +addIndex() +addForeignKey() CREATE TABLE … RDBMS

33. Module File Structure

34. Module Files Structure Mysql4 Mysql4 /app/code/core/Mage/Module/ Model Resource Helper sql … (Block, Helper, controllers etc.) deprecated

35. Resource Models /app/code/core/Mage/Cms/Model/ Mysql4 Resource Page Service.php Block.php Page Service.php Block.php

36. Resource Models – Backwards Compatibility class Mage_Cms_Model_Mysql4_Page_Service extends Mage_Cms_Model_Resource_Page_Service { // Empty class }

37. Resource Helpers /app/code/core/Mage/Module/Model/ Resource Helper Mysql4.php Oracle.php Mssql.php … …

38. SQL Installs & Upgrades oracle-upgrade-1.0-1.1.php sql install-1.0.php upgrade-1.0-1.1.php /app/code/core/Mage/Module/ … … …

39. Setting Up

40. MySQL Preparations • MySQL 4.1+ • PHP_PDO library • PHP_PDO_MYSQL library

41. Microsoft SQL Server Preparations • SQL Server 2008+ • PHP_PDO library • Windows: • Microsoft SQL Server Native Client • Microsoft PHP PDO Driver for SQL Sever • Linux: • FreeTDS library • PHP_PDO_DBLIB

42. Oracle Preparations • Oracle 10g r2+ (Standard+) • Oracle Instant Client • PHP_OCI8 library

43. Not Yet Frequently Asked Questions

44. FAQ Yes! Does it work? • QA tests • Tests of public extensions • Real big merchant’s store is coming

45. FAQ Yes! Will it be backwards compatible? • Major work: models and methods are the same • The rest: • Compatibility mode for MySQL • Instant query modifications • Manual overwalks

46. FAQ Yes! Can I work as before? • Direct queries can be used, when cross-DB support is not required • Same developers functionality is left

47. FAQ Yes! Will I get cross-DB support by following new development style? • Set of routines to access abstract RDBMS • Adapters and helpers do all low-level work

48. FAQ Definitely! Does it have future? • All new Magento releases, starting with CE 1.6 / EE 1.11 • Magento 2

49. Questions&Answers

50. Thank You! Andrey Tserkus Email: andrey.tserkus@magento.com Twitter: Zerkella Magento Inc.