April 2018, Budapest
Model / Persistance
Application
Data source
query
result
-- 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
);
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?
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?
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?
<?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
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
$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();
/**
* @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
$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();
Map the database schema to an object-oriented structure in a customizable, parameterized mode
Examples:
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 ...
}
-- 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])
);
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;
}
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;
}
$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:
-- 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`)
);
/**
* @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;
}
-- 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`)
);
In such scenario the creation of the mapping type is unavoidable.
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;
// ...
}