slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Ibiza, June 4 th – 7 th 2011 PowerPoint Presentation
Download Presentation
Ibiza, June 4 th – 7 th 2011

Loading in 2 Seconds...

  share
play fullscreen
1 / 50
fern

Ibiza, June 4 th – 7 th 2011 - PowerPoint PPT Presentation

137 Views
Download Presentation
Ibiza, June 4 th – 7 th 2011
An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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.