POMM

#TarnovoConf2017

@zecho23

About me

Me VarnaLab
  • Developer at Taylor & Hart
  • Problem solving enthusiast
  • Member of VarnaLab
  • Father of 2 kids
  • Dreamer

What I do for living

  • Working with databases for 15+ years

    PostgreSQL, DBase3, Interbase, MySQL, MongoDB, Firebird

  • Using ORMs for 8+ years

    Propel (ActiveRecord based), Doctrine (Hybernate based)

  • Developing with PHP for 13+ years

    Plain, Symfony1, Symfony2, other frameworks

Choose the right database for your project

MySQL?

Mongo?

  • We are talking about databases, aren't we?

Mongo-benchmarks.png

PostgreSQL FTW!

  • Data is exactly as it should be

    CREATE TABLE xxx (id int not null);
    INSERT INTO xxx VALUES ('4555test');
    -- ERROR:  invalid input syntax for integer: "4555 test"
    
  • DDL can be transactional too

    BEGIN;
    DROP TABLE xxx;
    ROLLBACK;
    
  • Triggers are working (Unlike in MySQL triggers can't update the table which they are assigned to.)

More PostgreSQL features!

  • Extensions (PostGIS, HStore, ….)
  • Data types: JSONB (MySQL json type has nothing in common), XML, text, UUID, timestamps with time zone, interval, timestamp ranges, HStore, GIS related, inet4, inet6, custom types

What is ORM?

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

Usage example

Without ORM

$stmt = mysql_query('SELECT * FROM orders WHERE id=1');
$order = mysql_fetch_assoc($stmt);
$date = new DateTime($order['date']);

mysql_query(strtr(
  'UPDATE orders SET payment_type = ":paymentType" WHERE id = :id', [
    ':paymentType' => Payment::PAYMENT_TYPE_CASH,
    ':id' => 1
));

With PDO is better

$db = new PDO($dsn);
$db = mysql_query();
$order = $db->query('
  SELECT *
  FROM orders
  WHERE id=1', PDO::FETCH_ASSOC);
$date = new DateTime($order['date']);

$stmt = $db->prepare('
  UPDATE orders
  SET payment_type = ":paymentType"
  WHERE id = :id');
$stmt->execute([
    ':paymentType' => Payment::PAYMENT_TYPE_CASH,
    ':id' => 1
]);

But still …

there are a lot of strings to be parsed

Pros of using ORM

DB abstraction

  • No more raw SQL queries
  • No more "How to write the query for this db?"
  • No more "Is this syntax available in this db?"

Object oriented instead of using arrays and manual hydration

$order = OrderTable::findByPK(1);
$date = $order->getDate();

$order->setPaymentType(Payment::PAYMENT_TYPE_CASH);
$order->save();

Why an ORM is not suitable?

Limited features because it should support various databases

  • How many people start a new project with the idea to switch later to another database?
  • Why I can't use feature X of the DB?
  • What else does the DB provide but I have to implement at the app level?

The cost of database abstraction (DBAL)

  • Another abstraction layer
  • Using DSL to query the database
  • Tightening to common supported DBs functions

What to choose then?

  • Choose the proper tool for your needs
  • Knowing the database you can benefit from it instead of tying yourself to an ORM

What is POMM?

http://www.pomm-project.org

"Imagine an ORM but upside down. That's what POMM is."

Some details about POMM

Version 1 is 10900 loc

Version 2 is 22809 loc

It's main purpose is to give object oriented interface on PostgreSQL, not to abstract the work with the database.

Why POMM instead of a standard ORM?

  • PDO is not needed anymore
  • It has all from ORM functionalities

    $where = \Pomm\Query\Where::create(
        'birth_date >= $*', [
            new \DateTime('1994-01-01')
          ]
        )
        ->andWhere('gender = $*', ['M']);
    
    $students = $studentModel->findWhere($where);
    

But it has a lot more

RETURNING

$student = $studentModel->deleteByPK(['student_id' => 2]);

Delete a row and get the result in a single query

Custom projections

public function getNearestFrom($name)
{
  $sql = 'SELECT :projection
          FROM :station_table station
          WHERE station.name = $* AND near_station.available_slots > 0
          ORDER BY distance ASC';

  $projection = $this->createProjection()
    ->setField('distance', '%:coord:% <-> near_station.coord', 'float4');

  $sql = strtr($sql, [
    ':projection'    => $projection->formatFieldsWithFieldAlias('station')
    ':station_table' => $this->getStructure()->getRelation()
  ]);

  return $this->query($sql, [$name], $projection);
}

Partial objects?

Why have to select all columns every time when you need only bunch of them?

CREATE TABLE very_large_table_with_lots_of_columns (
  pk bigserial primary key,
  name text,
  ... 50+ columns ...
  -- Don't tell me you don't have such tables :)
);
CREATE INDEX idx_vltwloc_for_selects
  ON very_large_table_with_lots_of_columns (pk, name);

With ORM you always do

SELECT * FROM very_large_table_with_lots_of_columns WHERE something

Yes, I know Doctrine has partial objects support

POMM in practice

Basic select

use MyDb\PublicSchema\ComputerModel;

$computers = $pomm['my_db']
    ->getModel(ComputerModel::class)
    ->findWhere('$* >> any (interfaces)', ['192.168.0.0/20'])
    ;

Custom field types

setField(
                'is_public',
                "not '192.168.0.0/16'::inet >> all(%:interfaces:%)",
                'bool'
            );
    }
//…
SELECT
    c."computer_id" AS "computer_id",
    …,
    c."interfaces" AS "interfaces",
    NOT '192.168.0.0/12'::inet >> ALL(c."interfaces") AS "is_public"
FROM
  public.computer c
WHERE
  …

Complex queries

Using CTE (recursive too)

WITH RECURSIVE
  sub_department AS (
    SELECT d.*
    FROM department d
    WHERE d.department_id = 9
    UNION ALL
    SELECT d.*
    FROM department d
      JOIN sub_department sd ON d.department_id = sd.department_parent_id
)
SELECT sd.* FROM sub_department sd;

You don't need tables

$integers = $pomm['my_db']
    ->getQueryManager()
    ->query("select generate_series(1, $*)", [ 3 ])
    ;

WINDOW functions

select
  name,
  country,
  production,
  rank() over (partition by country order by production desc)
from
  factory
order by
  country,
  rank asc
;
┌───────┬──────────┬────────────┬──────┐
│ name  │ country  │ production │ rank │
├───────┼──────────┼────────────┼──────┤
│ edno  │ bulgaria │       1512 │    1 │
│ un    │ france   │       2063 │    1 │
│ deux  │ france   │       1821 │    2 │
│ eins  │ germany  │       2211 │    1 │
....

Just use another custom projection

LISTEN/NOTIFY support

PostgreSQL support LISTEN/NOTIFY events

SELECT FROM pg_notify('my_event', 'some data here');

And you can very easily attach handler for them:

$session
  ->getListener('my_event')
  ->attachAction(function($event_name, $data, $session) {
    // do something
  })
;

Can I use it?

There is already integration for some of the most used frameworks:

Questions?

Thank you!

You can reach me at:

@zecho23

yanko@zecho.info

http://zecho.info