CRUD models

are dead

must die

Before we start I want to make a confession

I am lazy developer that likes reading dumb code

Why I am lazy?

Don't like to write things twice
Don't like writing boilerplate code
Don't like to copy-paste code
Don't like to write a lot of code to get things done

What is the dumb code?

Easy to get without reading it twice
It does only one thing
It hides details behind abstractions

So how is that all is related to CRUD models?

Database related code should be the dumbest thing possible in any system/platform...

But it is NOT in most of the cases

We build our components database centric and forget about our business logic simplicity

A simple example

Quote Table

CREATE TABLE `quote` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
  `converted_at` timestamp NULL DEFAULT NULL COMMENT 'Converted At',
  `is_active` smallint(5) unsigned DEFAULT '1' COMMENT 'Is Active',
  `is_virtual` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Virtual',
  `is_multi_shipping` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Multi Shipping',
  `items_count` int(10) unsigned DEFAULT '0' COMMENT 'Items Count',
  `items_qty` decimal(12,4) DEFAULT '0.0000' COMMENT 'Items Qty',
  `orig_order_id` int(10) unsigned DEFAULT '0' COMMENT 'Orig Order Id',
  `store_to_base_rate` decimal(12,4) DEFAULT '0.0000' COMMENT 'Store To Base Rate',
  `store_to_quote_rate` decimal(12,4) DEFAULT '0.0000' COMMENT 'Store To Quote Rate',
  `base_currency_code` varchar(255) DEFAULT NULL COMMENT 'Base Currency Code',
  `store_currency_code` varchar(255) DEFAULT NULL COMMENT 'Store Currency Code',
  `quote_currency_code` varchar(255) DEFAULT NULL COMMENT 'Quote Currency Code',
  `grand_total` decimal(12,4) DEFAULT '0.0000' COMMENT 'Grand Total',
  `base_grand_total` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Grand Total',
  `checkout_method` varchar(255) DEFAULT NULL COMMENT 'Checkout Method',
  `customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Id',
  `customer_tax_class_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Tax Class Id',
  `customer_group_id` int(10) unsigned DEFAULT '0' COMMENT 'Customer Group Id',
  `customer_email` varchar(255) DEFAULT NULL COMMENT 'Customer Email',
  `customer_prefix` varchar(40) DEFAULT NULL COMMENT 'Customer Prefix',
  `customer_firstname` varchar(255) DEFAULT NULL COMMENT 'Customer Firstname',
  `customer_middlename` varchar(40) DEFAULT NULL COMMENT 'Customer Middlename',
  `customer_lastname` varchar(255) DEFAULT NULL COMMENT 'Customer Lastname',
  `customer_suffix` varchar(40) DEFAULT NULL COMMENT 'Customer Suffix',
  `customer_dob` datetime DEFAULT NULL COMMENT 'Customer Dob',
  `customer_note` varchar(255) DEFAULT NULL COMMENT 'Customer Note',
  `customer_note_notify` smallint(5) unsigned DEFAULT '1' COMMENT 'Customer Note Notify',
  `customer_is_guest` smallint(5) unsigned DEFAULT '0' COMMENT 'Customer Is Guest',
  `remote_ip` varchar(32) DEFAULT NULL COMMENT 'Remote Ip',
  `applied_rule_ids` varchar(255) DEFAULT NULL COMMENT 'Applied Rule Ids',
  `reserved_order_id` varchar(64) DEFAULT NULL COMMENT 'Reserved Order Id',
  `password_hash` varchar(255) DEFAULT NULL COMMENT 'Password Hash',
  `coupon_code` varchar(255) DEFAULT NULL COMMENT 'Coupon Code',
  `global_currency_code` varchar(255) DEFAULT NULL COMMENT 'Global Currency Code',
  `base_to_global_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Global Rate',
  `base_to_quote_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Quote Rate',
  `customer_taxvat` varchar(255) DEFAULT NULL COMMENT 'Customer Taxvat',
  `customer_gender` varchar(255) DEFAULT NULL COMMENT 'Customer Gender',
  `subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal',
  `base_subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal',
  `subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal With Discount',
  `base_subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal With Discount',
  `is_changed` int(10) unsigned DEFAULT NULL COMMENT 'Is Changed',
  `trigger_recollect` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Trigger Recollect',
  `ext_shipping_info` text COMMENT 'Ext Shipping Info',
  `is_persistent` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Quote Persistent',
  `gift_message_id` int(11) DEFAULT NULL COMMENT 'Gift Message Id',
  PRIMARY KEY (`entity_id`),
  KEY `QUOTE_CUSTOMER_ID_STORE_ID_IS_ACTIVE` (`customer_id`,`store_id`,`is_active`),
  KEY `QUOTE_STORE_ID` (`store_id`),
  CONSTRAINT `QUOTE_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sales Flat Quote'

What if EAV quote was not present in early versions of Magento 1.x?

The core of the issue in CRUD models

CRUD Model

Create single model
Read single model
Update single model
Delete single model

CRUD SQL queries


/* Create model */
INSERT INTO table_name (column1, column2, is_active) VALUES ('value1', 'value2', 1);


/* Read model */
SELECT id, column1, column2, is_active FROM table_name WHERE id = 123;


/* Update model */
UPDATE table_name SET column1='value1', column2='value2', is_active = 1 where id = 123;


/* Delete model */
DELETE FROM table_name WHERE id = 123

MultiPLE entities (BatCh)

/* Collection implementation model */
SELECT id, column1, column2 FROM table_name WHERE is_active = 1 ORDER BY column1 LIMIT 1, 20

/* Import multiple items */
INSERT INTO table_name (column1, column2, is_active) VALUES 
    ('value1', 'value2', 1),
    ('value1.2', 'value2.2', 1),
    ('value1.3', 'value2.3', 1);


/* Delete multiple items */
DELETE FROM table_name WHERE is_active = 0;

CRUD models force me to write code for batch operations

But I don't like to write a lot of code to get things done...

But how about combining operations together?

Generalized read

/* Retrieve filtered sorted list of ids */
SELECT id FROM table_name WHERE is_active = 1 ORDER BY column1 LIMIT 1, 20;
/* Retrieve data based on primary key list */
SELECT id, column1, column2, is_active FROM table_name WHERE id IN(1, 2, 3, 4, 5, ...., 20);

/* Single model read */
SELECT id, column1, column2, is_active FROM table_name WHERE id IN(123);

Insert + Update?

Update can be combined into
INSERT ... ON DUPLICATE KEY UPDATE
Aquire missing primary keys
ALTER TABLE ... AUTO_INCREMENT=maxid+batchsize+1

Generalized Batched Insert/Update


/* All other concurrent insert operation will use 4+ auto-increment value */
ALTER TABLE table_name AUTO_INCREMENT=4; 
/* Import multiple items / update duplicated key records*/
INSERT INTO table_name (id, column1, column2, is_active) VALUES 
    (1, 'value1', 'value2', 1),
    (3, 'value1.2', 'value2.2', 1), /* New ID */
    (2, 'value1.3', 'value2.3', 1)
    ON DUPLICATE KEY UPDATE
      SET column1=VALUES(column1), 
        column2=VALUES(column2), 
        is_active=VALUES(is_active);

Generalized delete


/* Retrieve filtered sorted list of ids */
SELECT id FROM table_name WHERE is_active = 0;

/* Delete those records */
DELETE FROM table_name WHERE id IN(21, 23, ...., 30);

That's not all folks

I don't want to create such a class for every entity!

Generalization patterns


SELECT [primary_key] FROM [table_name] WHERE [conditions] ORDER BY [sort order] LIMIT [pagination];


SELECT [primary_key] FROM [table_name] WHERE [conditions] ORDER BY [sort order] LIMIT [pagination];
SELECT [table columns] FROM [table_name] WHERE [primary_key] IN([condition]);

        
/* All other concurrent insert operation will use 4+ auto-increment value */
ALTER TABLE [table_name] AUTO_INCREMENT=4; 

/* Import multiple items / update duplicated key records*/
INSERT INTO [table_name] ([table columns]) VALUES 
    ([values])
    ON DUPLICATE KEY UPDATE
      SET [table columns except primary key];

Entity Metadata can be injected as dependency!

Generalization Summary

4 database opeartions instead of 7+ per entity
Import/Export does not require anymore a separate component
Listing is completely separated from data retrieval
4 database related components vs 600+ CRUD and Collection resource models
Low memory footprint on MySQL with higher query performance

Yeah, sounds cool, but looks like a lot of boilerplate to get thigns done...

But I don't like writing too much boiler plate code either

And I want to have auto-complete...

Code Generation can save us!

But there should be NO MAGIC, only contract you request!

Data Object Contract

 
namespace MyCustom\Module\Api\Data;

interface Entity
{
    public function getId(): int;
    
    public function getColumn1(): string;
    
    public function getColumn2(): string;
}

It means I want something like this:

 
namespace MyCustom\Module\Model;

use MyCustom\Module\Api\Data;

class Entity implements Data\Entity
{
    public function getId() {
    	return $this->value->getData('id');
    }
    
    public function getColumn1()
    {
    	return $this->value->getData('column1');
    }
    
    public function getColumn2()
    {
       	return $this->value->getData('column1');
    }
}

Unit of Work (Entity Manager)

 
namespace MyCustom\Module\Api;

interface EntityManager
{
    public function findById($id): Data\Entity;
    
    public function findMany(SearchCriteria $searchCriteria): Data\EntityResult;
    
    public function persist(Data\Entity $entity): Data\Entity;

    public function remove(Data\Entity $entity): Data\Entity;
    
    public function flush();
}

We shouldn't do anything of these, it is just generic stuff

 
namespace MyCustom\Module\Model;

use MyCustom\Module\Api\Data;

class EntityManager implement Data\EntityManager
{
    public function findById($id): Data\Entity
    {
    	$row = $this->dataReader->read([$id]);
        return $this->entityHydrator->create($row);
    }
    
    public function findMany(SearchCriteria $searchCriteria): Data\EntityResult
    {
    	$ids = $this->listReader->find($searchCriteria);
        $rows = $this->dataReader->read($ids);
        // ... hydration ... etc
        return $result;
    }
    
    // ...
}

Entity Repository?

 
namespace MyCustom\Module\Model;

class EntityRepository implement Data\EntityRepository
{
    public function save(Data\Entity $entity): Data\Entity
    {
    	$entity = $this->entityManager->persist($entity);
        $this->entityManager->flush();
        return $entity;
    }
    
    public function getById($id): Data\Entity
    {
    	return $this->entityManager->findById($id);
    }
    
    public function getList(SearchCriteriaInterface $searchCriteria): Data\EntityResult
    {
    	return $this->entityManager->findMany($searchCriteria);
    }
    
    // ... other methods
}

Yeah, cool, but how about EAV entities?

EAV is just an extended version of regular entity

Talk Summary

CRUD model is not generic implementation
ORM should not dictate our design
We must dictate to ORM by using minimal code (interface)

WOW, can I use it all right now?

You can start writing do-coupled database components
There is no PHP contract based ORM yet

Thank You

Q&A

@IvanChepurnyi

ivan@ecomdev.org