Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Products storage at Akeneo PIM

Products storage at Akeneo PIM

How do we store products at Akeneo PIM?
#symfony #doctrine #mysql #eav #mongodb

Julien Janvier

April 22, 2016
Tweet

More Decks by Julien Janvier

Other Decks in Programming

Transcript

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

    View full-size slide

  2. Community Edition
    Enterprise Edition

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  6. We could store our products like that...

    View full-size slide

  7. We could split into sheets...

    View full-size slide

  8. Need to update the schema
    each time an attribute is added

    View full-size slide

  9. Flexible data into fixed schema

    View full-size slide

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

    View full-size slide

  11. “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

    View full-size slide

  12. Our EAV model - products
    Table pim_catalog_product
    Table pim_catalog_category_product

    View full-size slide

  13. Our EAV model - attributes
    Table pim_catalog_attribute

    View full-size slide

  14. Our EAV model - simple values
    Table pim_catalog_product_value

    View full-size slide

  15. Our EAV model - complex values
    Table pim_catalog_product_value
    Table pim_catalog_product_metric

    View full-size slide

  16. Our EAV model - localizable and scopable attributes
    Table pim_catalog_product_value

    View full-size slide

  17. You said 1M products?

    View full-size slide

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

    View full-size slide

  19. There is another path...

    View full-size slide

  20. MongoDB
    ---
    Flexible data model &
    elastic scalability

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  23. Transparent for end users
    Straightforward for developers

    View full-size slide

  24. 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(),
    ];
    }

    View full-size slide

  25. 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) { //... }

    View full-size slide

  26. 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,

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  29. Products stored in MongoDB

    View full-size slide

  30. {
    "_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" : [ ]

    View full-size slide

  31. {
    "_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" : [ ]
    }

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  34. 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;
    }
    }

    View full-size slide

  35. 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)
    )
    ;
    }
    }
    }

    View full-size slide

  36. MySQL << 5M << MongoDB

    View full-size slide

  37. So far and tomorrow...

    View full-size slide

  38. Products storage
    at Akeneo PIM
    Questions?
    Julien Janvier
    @jujanvier

    View full-size slide