chanmix51

CREATE SCHEMA company;

SET search_path TO company, public;

CREATE TABLE department (
    department_id       serial PRIMARY KEY,
    name                varchar NOT NULL,
    parent_id           int REFERENCES department (department_id)
    );

chanmix51

Get the most of psql

Psql, the CLI postgreSQL client, is a powerful tool. Sadly, lot of developers are not aware of the features and instead look for a GUI to provide what they need. Let's fly over what can psql do for you.

Feel yourself at home

One of the most common misconception people have about CLI is «They are a poor user interface». C'mon, the CLI is the most efficient user interface ever. There is nothing to disturb you from what you are doing and you are by far fastest without switching to your mouse all the time. Let's see how we can configure psql at our convenience.

chanmix51

<?php

class Entity extends \Pomm\Object\BaseObject
{
    public function getMeFive()
    {
        return 5;
    }
}

chanmix51

$ sudo apt-get install postgresql
$ sudo -i -u postgres

CREATE USER elcaro;
ALTER ROLE elcaro WITH CREATEDB;
CREATE DATABASE "ElCaro" OWNER elcaro;
ALTER USER elcaro WITH ENCRYPTED PASSWORD 'elcaro';
\q

$ postgres@computer:~$ exit

chanmix51

Feature highlight: The Where query builder.

It often happens you can not know in advance what is the condition of your query going to be. Take by example the search form of an electronic part store web site. Just for the resistor component, the possible search criteria are:

• value in ohms from 0.1 to 10 million with more than 20 possible values in every power of ten.
• precision (0.1%, 1%, 5% and 10%)
• power dissipation from 0.125 to 100W
• type (carbon, metal, wire wound, cement, dissipated)

chanmix51

*--------------------------------------------------------------------------
* Model generated by Motega software: 
*
*              Modeling Of Tubes Employing Genetic Algorithms
*
* Models contain 1G resistors from all nodes to earth in order to avoid
* floating nodes. Triode and tetrode/pentode models contain a diode for
* simulating grid current.
*
* Non-commercial use is permitted, but at your own risk... This model

chanmix51

<?php
// This file listen to notifications from the Postgresql server.
// To send a notification with a payload you can either issue a NOTIFY SQL command from a psql console
// or use the file below.
// pomm https://github.com/chanmix51/Pomm

$database = new \Pomm\Connection\Database([ "dsn" => "pgsql://user:pass@host:port/name" ]);
$observer = $database->getConnection()
    ->createObserver()
    ->listen('plop');

chanmix51

<?php
 
namespace YourApp\Shop;
 
use YourApp\Shop\Base\CustomerMap as BaseCustomerMap;
use Pomm\Exception\Exception;
use Pomm\Query\Where;
 
class CustomerMap extends BaseCustomerMap
{

chanmix51

<?php

$where1 = Pomm\Query\Where::createWhereIn('customer_id', array(1, 2, 3, 5, 7, 11, 13))
  ->andWhere('first_name ~* $*', array('^jo[aeiouy]'))
  ->orWhere('age >= $*', array('21'))
  ;

$where2 = Pomm\Query\Where::create('location <@ cast($* AS circle)', array('<(44.234, -0.643), 3.44>'))
  ->orWhere($where1);

chanmix51

<?php // CustomerMap.php

// ...

  public function findAllWithOrderCount()
  {
    return $this->queryWithOrderCount(new Where());
    // SELECT ... WHERE true;
  }