The History of PHPersistence

Persistence is one of the most important part in a PHP project. Persisting data to a database came with PHPFI and its MySQL support. From native extensions and PHP4 database abstraction libraries to PDO and modern ORM frameworks, you will (re)discover how persistence has evolved during the last decade. This talk will also introduce the future of data persistence with the growing success of alternative storage engines.

Transcript

2. The History of PHPersistence Hugo Hamon - @hhamon OSIDays November 2011
3. PHP/FI Where Everything Begins…
4. 1995 PHP/FI
5. 1995 Files handling
6. $fp = fopen("/counter.txt","w+"); $counter = fgets($fp,1024); $counter++; fputs($fp, $counter); fclose($fp);  
7. 1995 Databases support
8. $db = mysql_connect("localhost", "root", "secret"); $name = "bob"; $result = mysql( $db, "select * from table where firstname='$name'" ); $num = mysql_numrows($result); echo "$num records found!<p>";
9. $i=0; while ($i<$num); echo mysql_result($result,$i,"lcase(fullname)"); echo "\n"; echo mysql_result($result,$i,"address"); echo "\n"; $i++; endwhile;
10. PHP 3/4 Code Reusability
11. User Functions
12. function db_connect($database, $host, $user, $pwd); function db_fetch_single($dbh, $query); function db_fetch_all($dbh, $query); function db_insert($dbh, $table, $values); function db_update($dbh, $table, $values, $pk); function db_delete($dbh, $table, $pk); function db_list_fields($dbh, $table); function db_drop_table($dbh, $table);
13. function db_connect($database, $host, $user, $pwd) { $dbh = mysql_connect($host, $user, $pwd); if (!$dbh) { die('Server unavailable: '. mysql_error()); } if (!mysql_select_db($database, $dbh)) { die("'$database' unavailable: ". mysql_error()); } return $dbh; }
14. /** * Fetches a single record from a database * * @param resource $dbh The database handler * @param string $query A SQL query * @return array A single record as an array */ function db_fetch_single($dbh, $query) { return current(db_fetch_all($dbh, $query)); }
15. function db_fetch_all($dbh, $query) { $result = mysql_query($query, $dbh); if (!$result) { die('Invalid query: '. mysql_error()); } $records = array(); while ($record = mysql_fetch_assoc($result)) { $records[] = $record; } return $records; }
16. function db_insert($dbh, $table, array $values) { $data = array(); foreach ($values as $value) { $data[] = mysql_real_escape_string($value, $dbh); } $sql = sprintf("INSERT INTO `$table` (`%s`) VALUES ('%s')", implode('`, `', array_keys($values)), implode("', '", $data) ); $result = mysql_query($query, $dbh); if (!$result) { die('Invalid data to insert: '. mysql_error()); } return mysql_insert_id($dbh); }
17. include 'lib/database.inc.php'; $dbh = db_connect('osidays'); $query = 'SELECT id, name FROM author'; $authors = db_fetch_all($dbh, $query); $author = db_fetch_single( $dbh, 'SELECT id, name FROM author WHERE id = 3' ); $id = db_insert($dbh, 'author', array( 'name' => 'Jules Verne' ));
18. PHP 4 OOP & Reusability
19. class Database { function Database($database, host, $user, $pwd); function disconnect(); function connect(); function free(); function getErrorMessage(); function getErrorCode(); function getLastInsertId(); function insert($table, array $values); function fetchAll($query); function fetchSingle($query); function query($query); function quote($string); function hasError(); function _collectError(); function _init(); }
20. require 'lib/Database.php'; $db= &new Database('demo', 'localhost', 'root'); $q = 'SELECT id, name FROM author'; foreach ($db->fetchAll($q) as $author) { // ... } $db->free(); $db->disconnect();
21. require 'lib/Database.php'; $db= &new Database('demo', 'localhost', 'root'); $q = 'SELECT id, name FROM author WHERE id = 1'; $author = $db->fetchSingle($q); $db->free(); $db->disconnect();
22. require 'lib/Database.php'; $db= &new Database('demo', 'localhost', 'root'); $res = $db->insert('author', array( 'name' => 'Jules Vernes' )); if ($result) { $id = $db->getLastInsertId(); } $db->disconnect();
23. PHP 4 Professional APIs
24. DBAL
25. •  PEAR::DB •  ADOdb •  Metabase •  MDB •  MDB2 •  Creole
26. §  Multiple databases support §  Uni ed APIs §  Prepared statements supports §  Query caching §  Security against SQL Injections
27. PEAR MDB2
28. $dsn = array( 'phptype' => 'mysql', 'username' => 'root', 'password' => '', 'hostspec' => 'localhost', 'database' => 'demo', ); $db = & MDB2::connect($dsn); $db->setFetchMode(MDB2_FETCHMODE_ASSOC); $rs = $db->queryAll('SELECT id, name FROM author'); $db->disconnect();
29. ADOdb
30. include('adodb.inc.php'); $db = NewADOConnection('mysql'); $db->Connect('localhost', 'root', 'password', 'demo'); $result = $db->Execute('SELECT id, name FROM author'); if ($result) { while (!$result->EOF) { echo 'ID: ', $result->fields[0] ,"\n"; echo 'Name:', $result->fields[1] ,"\n"; $result->MoveNext(); } }
31. 2004 PHP 5
32. 2005 PHP Data Object
33. • PECL extension as of PHP 5.0.0 • Core extension since PHP 5.1.0 (11/2005) • 12 official drivers as of today • Extensible Object Oriented API • Prepared statements & transaction support • Stored procedures support
35. « PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. »
36. « PDO does not provide a database abstraction as it doesn't rewrite SQL or emulate missing features »
37. Transactions / Prepared Statements
38. try { $pdo->beginTransaction(); $query = 'INSERT INTO author (name) VALUES (?)'; $stmt = $pdo->prepare($query); $stmt->bindValue(1, 'Jules Verne'); $stmt->execute(); $id = $pdo->lastInsertId(); $pdo->commit(); } catch (PDOException $e) { $pdo->rollback(); }
39. Stored Procedures
40. DELIMITER | CREATE PROCEDURE coming_events (IN start DATE, OUT events INT) BEGIN SELECT COUNT(*) INTO events FROM events WHERE start_at >= start; END | $query = "CALL coming_events('2011-03-01', @events);"; $pdo->query($query); $stmt = $pdo->query("SELECT @events;"); echo $stmt->fetchColumn() ,' events to come.';
41. PHP 5 Zend_Db
42. Table Data Gateway
43. class AuthorGateway extends Zend_Db_Table_Abstract { protected $_name = 'authors'; protected $_primary = 'author_id'; }
44. $data = array( 'first_name' => 'Jules', 'last_name' => 'Vernes', ); $table = new AuthorGateway(); $table->insert($data);
45. Row Data Gateway
46. $table = new AuthorGateway(); // New empty row $row = $table->createRow(); // Insert a new row $row->firstName = 'Jules'; $row->lastName = 'Verne'; $row->save();
47. Active Query
48. $isbn = '1234567890'; $rows = $db->select() ->from(array('b' => 'books')) ->where('b.isbn = ?', $isbn) ->order(array('b.title ASC')) ->query() ->fetchAll() ;
49. PHP 5 Object Relational Mapping
50. •  Use objects instead of raw SQL queries •  Database abstraction layer (not always) •  Relationships support •  Behaviors support (i18n, timestampable…) •  Querying API •  Error logging
51. 2005 Propel ORM
52. $author = new Author(); $author->setFirstName("Leo"); $author->setLastName("Tolstoy"); $book = new Book(); $book->setTitle("War & Peace"); $book->setIsbn("0140444173"); $book->setAuthor($author); $book->save();
53. $query = BookQuery::create() ->joinWith('Book.Author') ->joinWith('Book.Publisher') ->where('Author.firstName = ?', 'Leo') ->where('Author.lastName = ?', 'Tolstoï') ->orderByTitle() ->filterByPublishYear(2009) ->find() ;
54. 2009 Doctrine 1.x
55. $books = Doctrine_Query::create() ->select('b.*, a.*, p.*') ->from('Book b') ->leftJoin('b.Author a') ->leftJoin('b.Publisher p') ->where('a.firstName = ?', 'Karl') ->andWhere('a.lastName = ?', 'Marx') ->orderBy('b.title ASC') ->execute() ;
56. 2009     PHP  5.3  
57. ORM Frameworks
58. Doctrine2
59. •  Database Abstraction Layer •  Object Relational Mapping •  Schema management •  Migrations support •  XML & NoSQL databases support
60. /** @Entity() */ class Author { /** * @Id() * @GeneratedValue() * @Column(type="integer") */ private $id; /** @Column(type="string", length="30") */ private $name; /** @ReferenceMany(targetDocument="BlogPost") */ private $posts = array(); }
61. $post = new BlogPost(); $post->setTitle('My First Blog Post'); $post->setBody('Some content...'); $author = new Author(); $author->setName('Hugo Hamon'); $author->addPost($post); $em->persist($user); $em->persist($post); $dm->flush();
62. Toward NoSQL…
63. «  Not  Only  SQL  »  
64. abcdef   Hugo  Hamon   Key Value
65. FirstName:  Hugo   LastName:  Hamon   Role:  Speaker   abcdef   Key Column 1 Column 2 Column 3
66. Name:  Hugo  Hamon   Role:  Speaker   abcdef   Skills   0:  PHP   1:  HTML   2:  CSS   Key Document
67. 2009 MongoDB Driver
68. •  Cross-platform support •  Schemaless language •  BSON type support •  Binary le storage support (GridFS) •  Master – slave replication support •  Sharding (horizontal scaling)
69. $mongo = new Mongo(); $col = $mongo->selectDb('osidays')->books; $books = array( array('title' => 'Da Vinci Code'), array('title' => 'The Lost Symbol'), array('title' => 'Digital Fortress'), ); foreach ($books as $book) { $collection->insert($book); }
70. What’s next… … for today and tomorrow?
71. 2011 PHP 5.3 ODM Frameworks
73. •  ORM Layers for Mongodb •  Dealing with objects instead of arrays •  Relationships support •  Query abstraction •  GridFS support •  Query logging & caching
74. $post = new BlogPost(); $post->setTitle('My First Blog Post'); $post->setBody('Some content...'); $author = new Author(); $author->setName('Hugo Hamon'); $author->setEmail('hugo@example.com'); $dm->persist($user); $user->addPost($post); $dm->flush();
75. QuesAons?   92-98, boulevard Victor Hugo 92 115 Clichy Cedex trainings@sensio.com (+33 (0)1 40 99 82 11) sensiolabs.com - symfony.com – trainings.sensiolabs.com