Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

The History of PHPersistence Hugo Hamon - @hhamon OSIDays November 2011

Slide 3

Slide 3 text

PHP/FI Where Everything Begins…

Slide 4

Slide 4 text

1995 PHP/FI

Slide 5

Slide 5 text

1995 Files handling

Slide 6

Slide 6 text

$fp = fopen("/counter.txt","w+"); $counter = fgets($fp,1024); $counter++; fputs($fp, $counter); fclose($fp);  

Slide 7

Slide 7 text

1995 Databases support

Slide 8

Slide 8 text

$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!

";

Slide 9

Slide 9 text

$i=0; while ($i<$num); echo mysql_result($result,$i,"lcase(fullname)"); echo "\n"; echo mysql_result($result,$i,"address"); echo "\n"; $i++; endwhile;

Slide 10

Slide 10 text

PHP 3/4 Code Reusability

Slide 11

Slide 11 text

User Functions

Slide 12

Slide 12 text

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);

Slide 13

Slide 13 text

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; }

Slide 14

Slide 14 text

/** * 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)); }

Slide 15

Slide 15 text

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; }

Slide 16

Slide 16 text

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); }

Slide 17

Slide 17 text

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' ));

Slide 18

Slide 18 text

PHP 4 OOP & Reusability

Slide 19

Slide 19 text

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(); }

Slide 20

Slide 20 text

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();

Slide 21

Slide 21 text

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();

Slide 22

Slide 22 text

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();

Slide 23

Slide 23 text

PHP 4 Professional APIs

Slide 24

Slide 24 text

DBAL

Slide 25

Slide 25 text

•  PEAR::DB •  ADOdb •  Metabase •  MDB •  MDB2 •  Creole

Slide 26

Slide 26 text

§  Multiple databases support §  Uni ed APIs §  Prepared statements supports §  Query caching §  Security against SQL Injections

Slide 27

Slide 27 text

PEAR MDB2

Slide 28

Slide 28 text

$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();

Slide 29

Slide 29 text

ADOdb

Slide 30

Slide 30 text

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(); } }

Slide 31

Slide 31 text

2004 PHP 5

Slide 32

Slide 32 text

2005 PHP Data Object

Slide 33

Slide 33 text

• 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

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

« 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. »

Slide 36

Slide 36 text

« PDO does not provide a database abstraction as it doesn't rewrite SQL or emulate missing features »

Slide 37

Slide 37 text

Transactions / Prepared Statements

Slide 38

Slide 38 text

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(); }

Slide 39

Slide 39 text

Stored Procedures

Slide 40

Slide 40 text

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.';

Slide 41

Slide 41 text

PHP 5 Zend_Db

Slide 42

Slide 42 text

Table Data Gateway

Slide 43

Slide 43 text

class AuthorGateway extends Zend_Db_Table_Abstract { protected $_name = 'authors'; protected $_primary = 'author_id'; }

Slide 44

Slide 44 text

$data = array( 'first_name' => 'Jules', 'last_name' => 'Vernes', ); $table = new AuthorGateway(); $table->insert($data);

Slide 45

Slide 45 text

Row Data Gateway

Slide 46

Slide 46 text

$table = new AuthorGateway(); // New empty row $row = $table->createRow(); // Insert a new row $row->firstName = 'Jules'; $row->lastName = 'Verne'; $row->save();

Slide 47

Slide 47 text

Active Query

Slide 48

Slide 48 text

$isbn = '1234567890'; $rows = $db->select() ->from(array('b' => 'books')) ->where('b.isbn = ?', $isbn) ->order(array('b.title ASC')) ->query() ->fetchAll() ;

Slide 49

Slide 49 text

PHP 5 Object Relational Mapping

Slide 50

Slide 50 text

•  Use objects instead of raw SQL queries •  Database abstraction layer (not always) •  Relationships support •  Behaviors support (i18n, timestampable…) •  Querying API •  Error logging

Slide 51

Slide 51 text

2005 Propel ORM

Slide 52

Slide 52 text

$author = new Author(); $author->setFirstName("Leo"); $author->setLastName("Tolstoy"); $book = new Book(); $book->setTitle("War & Peace"); $book->setIsbn("0140444173"); $book->setAuthor($author); $book->save();

Slide 53

Slide 53 text

$query = BookQuery::create() ->joinWith('Book.Author') ->joinWith('Book.Publisher') ->where('Author.firstName = ?', 'Leo') ->where('Author.lastName = ?', 'Tolstoï') ->orderByTitle() ->filterByPublishYear(2009) ->find() ;

Slide 54

Slide 54 text

2009 Doctrine 1.x

Slide 55

Slide 55 text

$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() ;

Slide 56

Slide 56 text

2009     PHP  5.3  

Slide 57

Slide 57 text

ORM Frameworks

Slide 58

Slide 58 text

Doctrine2

Slide 59

Slide 59 text

•  Database Abstraction Layer •  Object Relational Mapping •  Schema management •  Migrations support •  XML & NoSQL databases support

Slide 60

Slide 60 text

/** @Entity() */ class Author { /** * @Id() * @GeneratedValue() * @Column(type="integer") */ private $id; /** @Column(type="string", length="30") */ private $name; /** @ReferenceMany(targetDocument="BlogPost") */ private $posts = array(); }

Slide 61

Slide 61 text

$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();

Slide 62

Slide 62 text

Toward NoSQL…

Slide 63

Slide 63 text

«  Not  Only  SQL  »  

Slide 64

Slide 64 text

abcdef   Hugo  Hamon   Key Value

Slide 65

Slide 65 text

FirstName:  Hugo   LastName:  Hamon   Role:  Speaker   abcdef   Key Column 1 Column 2 Column 3

Slide 66

Slide 66 text

Name:  Hugo  Hamon   Role:  Speaker   abcdef   Skills   0:  PHP   1:  HTML   2:  CSS   Key Document

Slide 67

Slide 67 text

2009 MongoDB Driver

Slide 68

Slide 68 text

•  Cross-platform support •  Schemaless language •  BSON type support •  Binary le storage support (GridFS) •  Master – slave replication support •  Sharding (horizontal scaling)

Slide 69

Slide 69 text

$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); }

Slide 70

Slide 70 text

What’s next… … for today and tomorrow?

Slide 71

Slide 71 text

2011 PHP 5.3 ODM Frameworks

Slide 72

Slide 72 text

No content

Slide 73

Slide 73 text

•  ORM Layers for Mongodb •  Dealing with objects instead of arrays •  Relationships support •  Query abstraction •  GridFS support •  Query logging & caching

Slide 74

Slide 74 text

$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();

Slide 75

Slide 75 text

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