Relational database management

Object relational mapping

Máté Cserép

April 2018, Budapest

Database management

  • Native connection
  • Logical relational model
  • Simple object relational model
  • Entity-based object relational model

Model / Persistance

Application

Data source

query

result

Sample database

-- MySQL
CREATE TABLE `products` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `description` text NOT NULL
);

-- MSSQL
CREATE TABLE [Products] (
  [id] int NOT NULL PRIMARY KEY IDENTITY,
  [name] varchar(100) NOT NULL,
  [price] decimal(10,2) NOT NULL,
  [description] text NOT NULL
);

Native conncetion

Executing direct SQL commands on a physical database

$conn = mysql_connect("localhost", "username", "password");
if(!$conn) exit("Could not connect: " . mysql_error());
mysql_select_db("db_name");

$result = mysql_query("SELECT id, name FROM products");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("ID: %s  Name: %s", $row["id"], $row["name"]);
}

mysql_free_result($result);
mysql_close($conn);
$mysqli = mysqli("localhost", "username", "password", "db_name");
if($mysqli->connect_errno)
    exit("Could not connect: " . $mysqli->connect_error);

$result = $mysqli->query("SELECT id, name FROM products");

while ($row = $result->fetch_assoc()) {
    printf("ID: %s  Name: %s", $row["id"], $row["name"]);
}

$result->free();
$mysqli->close();

What are the benefits?

 

What are the drawbacks?

Logical relational model

Replicate the physical database model in the memory

$db = Zend_Db::factory('PDO_MYSQL', [ 
  'host'     => 'localhost',
  'username' => 'username',
  'password' => 'password',
  'dbname'   => 'db_name'
]);
Zend_Db_Table::setDefaultAdapter($db);

$products = new Zend_Db_Table('products');
$bike = $products->fetchRow([ 'name' => 'Bike' ]);
$bike->price += 100;
$products->update($bike);

What are the benefits?

What are the drawbacks?

Simple ORM

Map the database schema to an object-oriented structure

class Product
{
  public $id;
  public $name;
  public $price;
  public $description;
}

Model / Persistance

Application

Data source

object relational mapping

object relational mapping

What are the benefits? What are the drawbacks?

ORM Frameworks

How to define the mapping?

Database first

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" 
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                  xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="AppBundle\Entity\Products" table="products">
    <id name="id" type="int" column="id">
      <generator strategy="IDENTITY"/>
    </id>
    <field name="name" type="string" column="name" length="50" nullable="false"/>
    <field name="price" type="decimal" column="price" scale="2" nullable="false"/>
    <field name="description" type="text" column="description" nullable="false"/>
  </entity>
</doctrine-mapping>
php bin/console doctrine:mapping:import --force AppBundle xml
php bin/console doctrine:mapping:convert annotation ./src

How to define the mapping?

Code first

namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
 * @ORM\Entity
 * @ORM\Table(name="products")
 */
class Product {
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;
    /**
     * @ORM\Column(type="decimal", scale=2)
     */
    private $price;
    /**
     * @ORM\Column(type="text")
     */
    private $description;
}
php bin/console doctrine:schema:validate
php bin/console doctrine:schema:update --force

How to define the mapping?

  • Database first
  • Code first
  • Model first

Building object-oriented quries

$context = $this->getDoctrine()->getManager();
$products = $context->getRepository(Product::class);

$searched = $products->find(42);
$searched->setPrice(500);
$context->flush();
$qb = $products->createQueryBuilder('p');
$query = $qb
    ->add('where', $qb->expr()->like('p.name', ':name'))
    ->add('orderBy', $qb->expr()->orderBy('p.price', 'ASC'))
    ->setParameter('name', 'computer')
    ->getQuery();

$result = $query->getResult();

Different languages - Same concept

/**
 * @ORM\Entity
 * @ORM\Table(name="products")
 */
class Product {
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;
    /**
     * @ORM\Column(type="decimal", scale=2)
     */
    private $price;
    /**
     * @ORM\Column(type="text")
     */
    private $description;
}
[Table("products")] // could be omitted
public class Product
{
    [Key] // could be omitted
    [Column("id")] // could be omitted
    public int Id { get; set; }

    [Column("name")] // could be omitted
    [StringLength(100)]
    public string Name { get; set; }
 
    [Column("price")] // could be omitted
    public decimal Price { get; set; }

    [Column("description")] // could be omitted
    public string Description { get; set; }
}

PHP with Doctrine

C# with Entity Framework

Different languages - Same concept 

$context = $this->getDoctrine()->getManager();
$products = $context->getRepository(Product::class);

$searched = $products->find(42);
$searched->setPrice(500);
$context->flush();

$qb = $products->createQueryBuilder('p');
$query = $qb
    ->add('where',
        $qb->expr()->like('p.name', ':name'))
    ->add('orderBy', 
        $qb->expr()->orderBy('p.price', 'ASC'))
    ->setParameter('name', 'computer')
    ->getQuery();
$result = $query->getResult();

PHP with Doctrine

C# with Entity Framework & LINQ

MyDbContext context = new MyDbContext();
DbSet<Product> products = context.Products;

Product searched = Products.Find(42);
searched.Price = 500;
context.SaveChanges();

IQueryable<Product> query = products
    .Where(p => p.Name.Contains('computer'))
    .OrderBy(p => p.Price);

IList<Product> result = query.ToList();

Entity-based ORM

Map the database schema to an object-oriented structure in a customizable, parameterized mode

  • Omit certain fields
  • Add aggregated fields
  • Add navigation / reference fields for relations
  • Replace "linking tables" with OOP concepts
  • Introduce inheritance into model

Examples:

Entity-based ORM

Map the database schema to an object-oriented structure in a customizable, parameterized mode

/**
 * @ORM\Entity
 * @ORM\Table(name="users")
 */
class User
{
    // ...

    /**
     * @ORM\Column(type="string", length=100, name="first_name")
     */
    private $firstName;
    /**
     * @ORM\Column(type="string", length=100, name="last_name")
     */
    private $lastName;

    public getFirstName() { return $this->firstName; }
    public getLastName() { return $this->lastName; }
    public getFullName() { 
        return $this->firstName . " " .  $this->lastName;
    }
    // setters ...
}

Mapping relations

-- MySQL
CREATE TABLE `reviews` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `review` text NOT NULL,
  INDEX `product_ind` (`product_id`),
  FOREIGN KEY (`product_id`)
    REFERENCES `products`(`id`)
);

-- MSSQL
CREATE TABLE [Reviews] (
  [id] int NOT NULL PRIMARY KEY IDENTITY,
  [product_id] int NOT NULL,
  [reviews] text NOT NULL,
  CONSTRAINT [FK_Reviews_Products] FOREIGN KEY([product_id])
    REFERENCES [Products] ([id])
);

Mapping relations

namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
 * @ORM\Entity
 * @ORM\Table(name="reviews")
 */
class Review {
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @ORM\Column(type="integer", name="product_id")
     */
    private $productId;
    /**
     * @ORM\Column(type="text")
     */
    private $review;

    /**
     * @ORM\ManyToOne(targetEntity="Product", inversedBy="reviews")
     * @ORM\JoinColumn(name="product_id", referencedColumnName="id")
     */
    private $product;
}

Mapping relations - Lazy loading

namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
 * @ORM\Entity
 * @ORM\Table(name="products")
 */
class Product
{
    // ...

    /**
     * @ORM\OneToMany(targetEntity="Review, mappedBy="product")
     */
    private $reviews;

    public function getReviews() { return $this->reviews; }
}
$context = $this->getDoctrine()->getManager();
$products = $context->getRepository(Product::class);

$bike = $products->findOneBy([ 'name' => 'Bike']);
$bikeReviews = $bike->getReviews();

foreach($bikeReviews as $review)
{
	// process ...
}
/**
 * @ORM\Entity @ORM\Table(name="products")
 */
class Product
{
    /**
     * @ORM\OneToMany(targetEntity="Review, mappedBy="product", fetch="EAGER")
     */
    private $reviews;
}

Mapping relations - Eager loading

$context = $this->getDoctrine()->getManager();
$result = $context->createQueryBuilder()
    ->select('p')
    ->from('AppBundle:Product', 'p')
    ->innerJoin('p.reviews','r')
    ->where('p.name = :name')
    ->setParameter('name', 'Bike')
    ->getQuery()
    ->getResult();

$bike = $result[0];

Temporary enforce:

Mapping many-to-many relations

-- MySQL
CREATE TABLE `categories` (
  `id` int(11) NOT NULL 
    PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL
);

CREATE TABLE `products_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (product_id, category_id),
  FOREIGN KEY (`product_id`)
    REFERENCES `products`(`id`),
  FOREIGN KEY (`category_id`)
    REFERENCES `categories`(`id`)
);

Mapping many-to-many relations

Object oriented concept

Mapping many-to-many relations

/**
 * @ORM\Entity
 * @ORM\Table(name="products")
 */
class Product {
    // ...

    /**
     * @ORM\ManyToMany(targetEntity="Category", inversedBy="products")
     * @ORM\JoinTable(name="products_categories")
     */
    private $categories;
}
/**
 * @ORM\Entity
 * @ORM\Table(name="categories")
 */
class Category {
    // ...

    /**
     * @ORM\ManyToMany(targetEntity="Product", mappedBy="categories")
     */
    private $products;
}

Mapping many-to-many relations

-- MySQL
CREATE TABLE `categories` (
  `id` int(11) NOT NULL 
    PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL
);

CREATE TABLE `products_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `order` tinyint NOT NULL,
  PRIMARY KEY (product_id, category_id),
  FOREIGN KEY (`product_id`)
    REFERENCES `products`(`id`),
  FOREIGN KEY (`category_id`)
    REFERENCES `categories`(`id`)
);

Mapping many-to-many relations

Object oriented concept

In such scenario the creation of the mapping type is unavoidable.

Introducing inheritance concept

User

Student

Lecturer

/** @ORM\MappedSuperclass */
class User {
    /** @ORM\Column(type="string") */
    protected $username;

    /** @ORM\Column(type="string") */
    protected $passwordHash;
}

/** @ORM\Entity */
class Student extends User {
    /** @ORM\Column(type="datetime") */
    private $enrollmentDate;

    // ...
}

Doctrine ORM

By Cserép Máté

Made with Slides.com

Doctrine ORM

Relational database management, Object relational mapping, PHP, Doctrine

  • 91

More from Cserép Máté