POMM

By Yanko Simeonoff

@zecho23

About me

zecho-full-zipped.jpg

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

    In MySQL triggers can't update the table which they are assigned to.

  • Extensions (PostGIS, HStore, ….)
  • Data types: JSON, XML, text, UUID, timestamps with time zone, periods, timestamp ranges, HStore, GIS related, inet4, inet6, custom types
  • What is ORM

    Definition

    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 here?"

    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 = $student_model->findWhere($where);
      

    But it has a lot more

    RETURNING

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

    Custom projections

    public function getNearestFrom($name)
    {
        $sql = <<<SQL
    SELECT
      :projection
    FROM
      :station_table station
        LEFT JOIN :station_table near_station ON near_station.name <> station.name
    WHERE
        station.name = $*
      AND
        near_station.available_slots > 0
    ORDER BY
      distance ASC
    SQL;
    
        $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

    <?php // MyDB/PublicSchema/ComputerModel.php
    // 
        public function createProjection()
        {
            return parent::createProjection()
                ->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 │
    ....
    

    LISTEN/NOTIFY support

    PostgreSQL support LISTEN/NOTIFY events

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

    Can I use it?

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

    Questions?

    Thank you!

    It was my first presentation and I'm still alive!