POMM - Why it's not another ORM?

Brief introduction about POMM and what's the difference from the ORMs. POMM uses the full strength of PostgreSQL giving object oriented interface on top of it.

Transcript

1. POMM #TarnovoConf2017 @zecho23 1
2. About me Developer at Taylor & Hart Problem solving enthusiast Member of VarnaLab Father of 2 kids Dreamer 2 . 1
3. 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 2 . 2
4. Choose the right database for your project 3 . 1
5. MySQL? Better watch this first: a lot other examples finally some of them fixed in 5.7 https:/ /www.youtube.com/watch?v=emgJtr9tIME 3 . 2
6. Mongo? We are talking about databases, aren't we? 3 . 3
7. PostgreSQL FTW! Data is exactly as it should be DDL can be transactional too Triggers are working (Unlike in MySQL triggers can't update the table which they are assigned to.) CREATE TABLE xxx (id int not null); INSERT INTO xxx VALUES ('4555test'); -- ERROR: invalid input syntax for integer: "4555 test" BEGIN; DROP TABLE xxx; ROLLBACK; 3 . 4
8. 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 3 . 5
9. 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. 4 . 1
10. Usage example 4 . 2
11. 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 )); 4 . 3
12. With PDO is better But still … there are a lot of strings to be parsed $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 ]); 4 . 4
13. Pros of using ORM 4 . 5
14. 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?" 4 . 6
15. Object oriented instead of using arrays and manual hydration $order = OrderTable::findByPK(1); $date = $order->getDate(); $order->setPaymentType(Payment::PAYMENT_TYPE_CASH); $order->save(); 4 . 7
16. Why an ORM is not suitable? 5 . 1
17. 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? 5 . 2
18. The cost of database abstraction (DBAL) Another abstraction layer Using DSL to query the database Tightening to common supported DBs functions 5 . 3
19. 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 5 . 4
20. What is POMM? "Imagine an ORM but upside down. That's what POMM is." http:/ /www.pomm-project.org 6 . 1
21. 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. 6 . 2
22. 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); 6 . 3
23. But it has a lot more 6 . 4
24. RETURNING Delete a row and get the result in a single query $student = $studentModel->deleteByPK(['student_id' => 2]); 6 . 5
25. 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); } 6 . 6
26. Partial objects? Why have to select all columns every time when you need only bunch of them? With ORM you always do Yes, I know Doctrine has partial objects support 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); SELECT * FROM very_large_table_with_lots_of_columns WHERE something 6 . 7
27. POMM in practice 7 . 1
28. Basic select use MyDb\PublicSchema\ComputerModel; $computers = $pomm['my_db'] ->getModel(ComputerModel::class) ->findWhere('$* >> any (interfaces)', ['192.168.0.0/20']) ; 7 . 2
29. Custom field types // 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 … 7 . 3
30. Complex queries 7 . 4
31. 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; 7 . 5
32. You don't need tables $integers = $pomm['my_db'] ->getQueryManager() ->query("select generate_series(1, $*)", [ 3 ]) ; 7 . 6
33. WINDOW functions Just use another custom projection 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 │ .... 7 . 7
34. LISTEN/NOTIFY support PostgreSQL support LISTEN/NOTIFY events And you can very easily attach handler for them: SELECT FROM pg_notify('my_event', 'some data here'); $session ->getListener('my_event') ->attachAction(function($event_name, $data, $session) { // do something }) ; 7 . 8
35. Can I use it? There is already integration for some of the most used frameworks: Symfony2: Silex: ZF2: You use other framework? Integrate it there too! https:/ /github.com/pomm- project/pomm-bundle https:/ /github.com/pomm-project/pomm- service-provider https:/ /github.com/pomm- project/PommModule 8 . 1
36. Questions? 9 . 1
37. Thank you! You can reach me at: @zecho23 yanko@zecho.info http:/ /zecho.info 10 . 1