Products storage at Akeneo PIM A story about a laptop and a mug

No content

Community Edition Enterprise Edition

Products storage at Akeneo PIM A story about a laptop and a mug Julien Janvier @jujanvier

Akeneo mug --- SKU: AKNMUG_WP Name: Akeneo mug Main color: white Secondary color: purple Container material: ceramic Description: A mug with akenes inside --- Status: disabled Created at: 04/09/2016 Family: mugs Categories: goodies Toshiba KIRA-102 --- SKU: LAPTOSHKIRA102 CPU: Intel Core i7 Memory size: 8GB Memory slots: 1 Memory type: DDR3L Screen size: 13’’ Matte display: no Description: The ultrabook Toshiba KIRA-102 is truly exceptional![...] Price: 1200€ / 1350$ Release date: 06/10/2015 Picture: kira102.jpg Advertising: kira_add.webm Activities: development, office work --- Status: enabled Created at: 04/11/2016 Family: laptops Categories: toshiba, office

What do users use when they don’t have a PIM?

We could store our products like that...

We could split into sheets...

Need to update the schema each time an attribute is added

Flexible data into fixed schema

Akeneo mug --- SKU: AKNMUG_WP Name: Akeneo mug Main color: white Secondary color: purple Container material: ceramic Description: A mug with akenes inside --- Status: disabled Created at: 04/09/2016 Family: mugs Categories: goodies Entity Attribute Value

“Entity–attribute–value model (EAV) is a data model to encode entities where the number of attributes that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest.” - Wikipedia

Our EAV model - products Table pim_catalog_product Table pim_catalog_category_product

Our EAV model - attributes Table pim_catalog_attribute

Our EAV model - simple values Table pim_catalog_product_value

Our EAV model - complex values Table pim_catalog_product_value Table pim_catalog_product_metric

Our EAV model - localizable and scopable attributes Table pim_catalog_product_value

You said 1M products?

Query products among 31M values Get me the products where screen size between 13’’ and 14’’ SELECT product p JOIN product_value pv JOIN attribute a JOIN product_value_metric pvm WHERE attribute.code = “screen_size” AND (pvm.data >= 13 OR pvm.data <= 14) AND pvm.unit = “inch” Get me the products where screen size between 13’’ and 14’’, and where release date > 01/01/2015, and where memory type in (ddr2, ddr3, ddr4), and where description en-US ecommerce like “%such a description%”, and where memory slots > 1, and where activities not in (development, gaming), and where picture is not empty...

There is another path...

MongoDB --- Flexible data model & elastic scalability

Classic storage - full MySQL MySQL --- Products Product values Attributes Users Categories Scopes Locales Families Product completenesses Product associations Versions ...

Hybrid storage - MySQL/MongoDB MongoDB --- Products Product values Product completenesses Product associations Versions MySQL --- Attributes Users Categories Scopes Locales Families ...

Transparent for end users Straightforward for developers

Hybrid storage - Setup # app/config/parameters.yml parameters: database_driver: pdo_mysql database_host: localhost database_port: null database_name: pim_mysql_database database_user: pim database_password: pim locale: en secret: ThisTokenIsNotSoSecretChangeIt pim_catalog_product_storage_driver: doctrine/mongodb-odm mongodb_server: 'mongodb://localhost:27017' mongodb_database: pim_mongodb_database # app/AppKernel.php class AppKernel extends Kernel { public function registerBundles() { return [ // ... new Doctrine\Bundle\MongoDBBundle\DoctrineMongoDBBundle(), ]; }

Hybrid storage - Query products # easily select, filter or sort a products no matter their storage namespace Pim\Component\Catalog\Query ; interface ProductQueryBuilderInterface { public function addFilter($field, $operator, $value, array $context = []); public function addSorter($field, $direction, array $context = []); /** * @return \Akeneo\Component\StorageUtils\Cursor\CursorInterface */ public function execute(); } # product query builder example $pqb->addFilter('groups.code', 'IN', ['shirts']) ->addFilter('sku', 'CONTAINS', 'akeneo') ->addFilter('description', 'STARTS WITH', 'My desc', ['locale' => 'fr_FR', 'scope' => 'mobile']) ->addFilter('price', '>', ['data' => 70, 'currency' => 'EUR']) ->addSorter('family', 'ASC') ->addSorter('completeness' , 'DESC', ['locale' => 'fr_FR', 'scope' => 'mobile']); $productsCursor = $pqb->execute(); foreach ($productsCursor as $product) { //... }

Hybrid storage - Product repositories # business interface, storage agnostic namespace Pim\Component\Catalog\Repository; use Doctrine\Common\Persistence\ObjectRepository; interface ProductRepositoryInterface extends ObjectRepository { public function findAllForVariantGroup(GroupInterface $variantGroup, array $criteria = []); public function findOneByIdentifier($identifier); //… # MongoDB repository, service pim_catalog.repository.product namespace Pim\Bundle\CatalogBundle\Doctrine\MongoDBODM\Repository; use Doctrine\ODM\MongoDB\DocumentRepository; use Pim\Component\Catalog\Repository\ProductRepositoryInterface; class ProductRepository extends DocumentRepository implements ProductRepositoryInterface, # ORM repository, service pim_catalog.repository.product too ;) namespace Pim\Bundle\CatalogBundle\Doctrine\ORM\Repository; use Doctrine\ORM\EntityRepository; use Pim\Component\Catalog\Repository\ProductRepositoryInterface; class ProductRepository extends EntityRepository implements ProductRepositoryInterface,

namespace Pim\Bundle\CatalogBundle\Doctrine\Common\Saver ; use Akeneo\Component\StorageUtils\Saver\SaverInterface ; use Doctrine\Common\Persistence\ObjectManager ; class ProductSaver implements SaverInterface { /** @var ObjectManager */ protected $objectManager ; public function __construct(ObjectManager $om) { $this->objectManager= $om; } public function save($product, array $options = []) { //… $this->objectManager->persist($product); $this->objectManager->flush(); //… Hybrid storage - Save a product

Hybrid storage - Load specific services # src/Acme/Bundle/AppBundle/Resources/config/services.yml services: pim_catalog.repository.product: class: %pim_catalog.repository.product.class% arguments: - ‘@foo’ - ‘@bar’ # src/Acme/Bundle/AppBundle/Resources/config/storage_driver/doctrine/mongodb-odm.yml parameters: pim_catalog.repository.product.class: Pim\Bundle\CatalogBundle\Doctrine \MongoDBODM\Repository\ProductRepository services: pim_catalog.saver.product: class: Pim\Bundle\CatalogBundle\Doctrine\ Common\Saver\ProductSaver arguments: - ‘ @doctrine.odm.mongodb.document_manager ’ # src/Acme/Bundle/AppBundle/Resources/config/storage_driver/doctrine/orm.yml parameters: pim_catalog.repository.product.class: Pim\Bundle\CatalogBundle\Doctrine \ORM\Repository\ProductRepository services: pim_catalog.saver.product: class: Pim\Bundle\CatalogBundle\Doctrine\ Common\Saver\ProductSaver arguments: - ‘ @doctrine.orm.entity_manager ’

Products stored in MongoDB

{ "_id" : ObjectId( "570cba8448177ef1478b4567" ), "created" : ISODate( "2016-04-12T09:06:12Z" ), "updated" : ISODate( "2016-04-12T09:16:23Z" ), "enabled" : true, "family" : NumberLong( 15), "values" : [ { "_id" : ObjectId( "570cba8448177ef1478b4568" ), "attribute" : NumberLong( 1), "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "optionIds" : [ ], "prices" : [ ], "varchar" : "LAPTOSHKIRA102" }, { "_id" : ObjectId( "570cbb2648177ef1478b4571" ), "attribute" : NumberLong( 56), "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "media" : NumberLong( 110), "optionIds" : [ ], "prices" : [ ] } , { "_id" : ObjectId( "570cbb2648177ef1478b4573" ), "attribute" : NumberLong( 3), "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "locale" : "en_US", "optionIds" : [ ], "prices" : [ ], "scope" : "ecommerce" , "text" : "The ultrabook Toshiba KIRA-102 is truly exceptional!" }, { "_id" : ObjectId( "570cbb2648177ef1478b4574" ), "attribute" : NumberLong( 63), "boolean" : false, "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "optionIds" : [ ], "prices" : [ ]

{ "_id" : ObjectId( "570cbb2648177ef1478b4575" ), "attribute" : NumberLong( 60), "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "metric" : { "_id" : ObjectId( "570cbb2648177ef1478b4576" ), "data" : 8, "unit" : "GIGABYTE" , "baseData" : 8589934592 , "baseUnit" : "BYTE", "family" : "Binary" }, "optionIds" : [ ], "prices" : [ ] } , { "_id" : ObjectId( "570cbb2648177ef1478b4579" ), "attribute" : NumberLong( 26), "entity" : DBRef("pim_catalog_product" , ObjectId( "570cba8448177ef1478b4567" )), "optionIds" : [ ], "prices" : [ { "_id" : ObjectId( "570cbb2648177ef1478b457a" ), "data" : 1200, "currency" : "EUR" }, { "_id" : ObjectId( "570cbb2648177ef1478b457b" ), "data" : 1350, "currency" : "USD" } ] } ] , "categoryIds" : [ NumberLong( 122), NumberLong( 96) ] , "groupIds" : [ ] }

Regular Doctrine - MongoDB ODM # regular Doctrine MongoDB mapping file Pim\Component\Catalog\Model\Product: type: document repositoryClass: Pim\Bundle\CatalogBundle\Doctrine\MongoDBODM\ProductRepository collection: pim_catalog_product fields: id: id: true generator: strategy: AUTO enabled: type: boolean created: type: date updated: type: date values: embedded: true type: many targetDocument: Pim\Component\Catalog\Model\ProductValueInterface mappedBy: entity completenesses: embedded: true type: many targetDocument: Pim\Component\Catalog\Model\CompletenessInterface

Hybrid storage - Link entities to documents # Doctrine MongoDB mapping file with hybrid storage special features Pim\Component\Catalog\Model\Product: // ... fields: // ... family: type: entity targetEntity: Pim\Component\Catalog\Model\FamilyInterface categories: type: entities notSaved: true targetEntity: Pim\Component\Catalog\Model\CategoryInterface idsField: categoryIds categoryIds: type: collection

Hybrid storage - Link entities to documents # a new Doctrine MongoDB type: “entity” namespace Akeneo\Bundle\StorageUtilsBundle\Doctrine\MongoDBODM\Types ; use Doctrine\ODM\MongoDB\Types\Type ; /** * Stores an entity identifier */ class Entity extends Type { /** * {@inheritdoc} */ public function convertToDatabaseValue ($value) { return is_object($value) ? $value->getId() : $value; } }

Hybrid storage - Link entities to documents # from $product->getFamily() = 15 to $product->getFamily() = $laptopFamily namespace Akeneo\Bundle\StorageUtilsBundle\EventSubscriber\MongoDBODM ; class EntityTypeSubscriber implements EventSubscriber { public function postLoad(LifecycleEventArgs $args) { $document = $args->getDocument(); $metadata = $args->getDocumentManager ()->getClassMetadata (get_class($document)); foreach ($metadata->fieldMappings as $field => $mapping) { if ('entity' === $mapping['type']) { if (!isset($mapping['targetEntity' ])) { throw new \RuntimeException(...) ; } $value = $metadata->reflFields[$field]->getValue($document); if (null !== $value && !$value instanceof $mapping['targetEntity' ]) { $metadata->reflFields[$field]->setValue( $document, $this->entityManager->getReference($mapping['targetEntity' ], $value) ) ; } } }

MySQL << 5M << MongoDB

So far and tomorrow...

Products storage at Akeneo PIM Questions? Julien Janvier @jujanvier