PHP and Database Management Systems Martin Kruli by
PHP and Database Management Systems Martin Kruliš by Martin Kruliš (v 1. 2) 15. 3. 2017 1
My. SQL � My. SQL Revision ◦ Original mysql API is deprecated (as of PHP 5. 5) ◦ My. SQL Improved (mysqli) API �Dual object/procedural interface �Procedural interface is similar to original (deprecated) API �Advanced connectivity features �Persistent connections, compression, encryption �Directly supports transactions ◦ My. SQL Native Driver (mysqlnd) extension �More direct access to My. SQL server �Additional features (e. g. , asynchronous queries) by Martin Kruliš (v 1. 2) 15. 3. 2017 2
My. SQL � My. SQLi API ◦ mysqli class – the connection to the DBMS �Manage controls, settings, info, stats, … �Issue queries and multi-queries �Performs transaction control ◦ mysqli_stmt class – SQL statement representation �SQL statement preparation and configuration �Argument bindings ◦ mysqli_result class – SELECT result wrapper �Various methods for accessing data in the result by Martin Kruliš (v 1. 2) 15. 3. 2017 3
My. SQL � My. SQLi Query Example $mysqli = new mysqli('server', 'login', . . . ); $stmt = $mysqli->prepare('SELECT * FROM subjects WHERE students = ? AND credits >= ? '); $stmt->bind_param('si', 'I 2', 3); $stmt->execute(); $res = $stmt->get_result(); while ($subj = $res->fetch_object()) { echo "$subj->fullnamen"; } $res->close(); by Martin Kruliš (v 1. 2) 15. 3. 2017 4
My. SQL � Extensions ◦ mysqlnd_qc �Transparent cache for My. SQL queries ◦ mysqlnd_memcache �Special extension that utilizes Inno. DB Memcache �Translates SQL statements to Memcache protocol ◦ mysqlnd_uh �Allows user to insert hooks for low-level calls �Can be used for monitoring or auditing ◦ mysqlnd_mux �Transparent client-side connection multiplexing by Martin Kruliš (v 1. 2) 15. 3. 2017 5
Other Database Systems � Postrgre. SQL ◦ Quite powerful alternative for My. SQL ◦ Similar API to My. SQL �pg_connect(), pg_query(), … � SQLite ◦ SQL engine running directly on filesystem ◦ Small projects, where regular DBMS is not available � Commercial Giants ◦ MSSQL, Oracle, d. Base, IBM DB 2, … by Martin Kruliš (v 1. 2) 15. 3. 2017 6
Database Abstraction � Database Abstraction Layers ◦ Solving the problem of portability �If the application refrains from using specific SQL statements or DBMS APIs � PHP Data Objects (PDO) ◦ Generic interface for various RDBMS ◦ Extension php_pdo, individual DB systems are in separate extensions (php_pdo_mysql, …) ◦ Simple object oriented API �PDO and PDOStatement classes �Minimalistic, no special functions by Martin Kruliš (v 1. 2) 15. 3. 2017 7
Database Abstraction � Database Abstraction Library (Di. Bi) ◦ Simpler work with statements, result extraction, … dibi: : connect([ 'driver' => 'mysqli', … ]); $r = dibi: : query('SELECT name FROM [users] Automated WHERE [login] = %s', $login); sanitization $username = $r->fetch. Single(); // $r->fetch. All(), $r->fetch. Pairs(), foreach($r. . . ) dibi: : query('INSERT INTO [table]', [ 'number' => 1, 'str' => "What’s up? " ]); Data fetching INSERT INTO `table` (`number`, `str`) VALUES (1, 'What's up? ') by Martin Kruliš (v 1. 2) 15. 3. 2017 8
ORM � Object-relational Mapping (ORM) ◦ Technique which creates object-oriented API over (relational) database ◦ Benefits �Much simpler for the programmer (no need for SQL) �Much less error prone ◦ Object-relational impedance mismatch �Set of difficulties which are encountered when matching relational DB and OOP API �E. g. , how to save a list of objects of a class User or derived classes (Admin, Editor, …) into relational database? �Mapping IS-A hierarchy, private members, references, … by Martin Kruliš (v 1. 2) 15. 3. 2017 9
ORM � Doctrine ◦ Very popular database framework for PHP �Contains both DB abstraction layer and O/R mapping ◦ Provides transparent persistence of PHP objects �The mapping must be provided by the means of XML, YAML, or PHP annotations �A DB schema can be generated from the PHP classes ◦ The Entity. Manager frontend �Provides methods to load/store objects of specific type �Allows utilizing SQL for more complex cases and converts results into mapped objects by Martin Kruliš (v 1. 2) 15. 3. 2017 10
ORM � Doctrine Example /** @Entity @Table(name="subjects") **/ class Lecture { /** @Id @Column(type="integer") @Generated. Value **/ protected $id; /** @Column(type="string") **/ protected $fullname; /** @Many. To. One(target. Entity="User", inversed. By="teach_lectures") **/ protected $teacher; . . . public function get. Description. String() {. . . } public function get. Students() {. . . } } by Martin Kruliš (v 1. 2) 15. 3. 2017 11
ORM � Doctrine Example $entity. Manager = Entity. Manager: : create($conn, $config); $subj = $entity. Manager->find('Lecture', (int)$id); $subj->set. Name('Web Applications'); $entity. Manager->flush(); $subjs = $entity. Manager->get. Repository('Lecture') ->find. By([ 'programme' => 'I 2' ]); foreach ($subjs as $subj) { echo $subj->get. Description. String(); foreach ($subj->get. Students() as $student) {. . . } } by Martin Kruliš (v 1. 2) 15. 3. 2017 12
Not. ORM � Not-ORM Abstraction ◦ Stands somewhere between simple abstraction like Di. Bi and full ORM system ◦ SQL queries are not written in strings, but semiautomatically procedurally composed �Programmer has to be familiar with SQL ◦ Unlike ORM, the API does not have to know complete DB schema �But knowing/inferring FK relations is very useful in order to generate table joins properly ◦ Original API was proposed and implemented by Jakub Vrána by Martin Kruliš (v 1. 2) 15. 3. 2017 13
Not. ORM � Not. ORM Example $db = new Not. ORM(. . . ); $subj = $db->lectures[42]; $subj->fullname = 'Web Applications'; $subj->update(); foreach ($db->lectures() ->where('programme', 'I 2') ->order('teacher. name') as $subj) { echo $subj->teacher['name'], ' ', $subj['fullname']; } by Martin Kruliš (v 1. 2) 15. 3. 2017 14
Files and Filesystem Martin Kruliš by Martin Kruliš (v 1. 2) 15. 3. 2017 15
Working with Files � Low Level C-like API ◦ fopen(), fread(), fwrite(), fclose(), … ◦ Data are treated as strings �Note that per-byte processing is extremely slow � File-wide Operations ◦ fpassthru(), readfile() – whole file is spilled to the output ◦ file_get_contents(), file_put_contents() �Read/write the entire file to/from a string ◦ file() – reads entire file as an array of lines by Martin Kruliš (v 1. 2) 15. 3. 2017 16
File System � POSIX ◦ ◦ ◦ Based API Only partial functionality on non-POSIX OSes touch() – sets current time to a file tmpfile() – creates temporary file rename(), unlink() – unlink ~ delete mkdir(), rmdir() – make/remove dir � Synchronization Issues ◦ POSIX atomic operations (append, mkdir, …) ◦ Advisory file locking mechanism – flock() �Locks are bounded to processes, multi-threaded web server need not guarantee sync. between PHP scripts by Martin Kruliš (v 1. 2) 15. 3. 2017 17
File Properties and Rights � File Properties (Size, Type, Creation Time, …) ◦ Dedicated functions for some properties �filesize(), filetype(), filectime() ◦ Reading all properties at once �stat(), lstat(), fstat() � POSIX Access Rights (rwx) ◦ Testing functions �is_file(), is_readable(), is_writeable(), … ◦ Modification of rights and owner �chmod(), chown(), chgrp() by Martin Kruliš (v 1. 2) 15. 3. 2017 18
Directories and Paths � Traversing Directories ◦ Iterative – opendir(), readdir(), closedir() ◦ scandir() – returns array with directory contents ◦ dir() – returns object of Directory class � Directory ◦ ◦ Paths glob() – expanding wildcards dirname(), basename() – get part of pathinfo() – parses path and return components realpath() – converts symbolic to real path �Absolute path without links, . /, and. . / by Martin Kruliš (v 1. 2) 15. 3. 2017 19
Formatted Files � CSV Files ◦ Comma (semicolon) separated values ◦ API uses file handlers from fopen() �fgetcsv() – reads one CSV line �fputcsv() – writes one CSV line � INI Files ◦ Configuration files with “name = value” items ◦ parse_ini_file() – reads the entire file and yields an associative array �Optionally divides the data to sections by Martin Kruliš (v 1. 2) 15. 3. 2017 20
Compression � ZIP Files (*. zip) ◦ Rather complicated (ZIP is multi-file container) ◦ Represented by Zip. Archive class � Zlib – gzip library (*. gz) ◦ Similar functions to standard C-like API �gzopen(), gzread(), gzwrite(), gzclose(), … � BZip Library (*. bz 2) ◦ Similar to Zlib (bzopen(), …) � Other libraries are available (RAR, LZF, …) ◦ TAR only as PECL extension by Martin Kruliš (v 1. 2) 15. 3. 2017 21
XML and JSON Martin Kruliš by Martin Kruliš (v 1. 2) 15. 3. 2017 22
Parsing XML � Parsing Process ◦ Loading XML from text file into memory structures � Simple API for XML (SAX) ◦ The document is processed sequentially ◦ Important “events” are reported to the user �Opening/closing an element, attribute found, … � Document Object Model (DOM) ◦ Tree-based structure with object oriented API ◦ Nodes of the tree represent elements, attributes, text content, …. ◦ More suitable for mutable documents by Martin Kruliš (v 1. 2) 15. 3. 2017 23
XPath and XSLT � XPath Query Language ◦ Inspired by filesys. paths (elements ~ directories) ◦ Various types of queries that could yield �Boolean, number, string, or set of DOM nodes ◦ Quite expressive (num. operations, conditions, …) � XSLT Transformations ◦ XSLT describes transformation of one XML document to another (or to HTML, or to plain-text) �XSLT rules are stored in XML document ◦ Uses XPath for search queries ◦ Turing complete language by Martin Kruliš (v 1. 2) 15. 3. 2017 24
XML and PHP � XML Utilities in PHP ◦ SAX parser �Procedural interface ◦ DOM API and parser �Object-oriented interface ◦ XPath query engine (over a DOM API) ◦ XSLT processor ◦ PHP-specific Simple XML API (simplified DOM) �Simple XML is based on specific properties of PHP classes (e. g. , iterators or magic methods) by Martin Kruliš (v 1. 2) 15. 3. 2017 25
PHP SAX Parser � Event Driven Parsing ◦ The document is processed as a stream �In a single call of xml_parse() ◦ Important encounters are reported via callbacks �xml_set_element_handler() �xml_set_character_handler() �… Example 1 by Martin Kruliš (v 1. 2) 15. 3. 2017 26
PHP DOM Implementation � Document Object Model in PHP ◦ Set of PHP-native classes ◦ Implementing DOM 3 as defined by W 3 C �DOMDocument – entire XML document �DOMElement – tree node of one XML element �DOMAttr – attribute of an element �DOMNode – base class for all tree classes �DOMCharacter. Data – plain text contents �… by Martin Kruliš (v 1. 2) 15. 3. 2017 27
PHP XML Validation and XPath � Document Validation ◦ DOMDocument methods for validation �validate(), schema. Validate() �Automatic validation when the document is parsed �If validate. On. Parse == true � DOMXPath Class for XPath Search ◦ Operates on DOMDocument ◦ Search is performed by query() or evaluate() ◦ The result is either basic PHP type (boolean, float, or string) or DOMNode. List object Example 2 by Martin Kruliš (v 1. 2) 15. 3. 2017 28
PHP and XSLT � XSLTProcessor Class ◦ Uses libxslt external library ◦ Transformation rules are loaded from a DOM document or from a Simple XML Element ◦ Transformation input is a DOMDocument object ◦ The result is yielded �As a new DOMDocument object �String with serialized XML/HTML/text document �Directly to a file by Martin Kruliš (v 1. 2) 15. 3. 2017 29
Simple XML in PHP � Simplified DOM API ◦ Also loads the document into tree structure ◦ All nodes are Simple. XMLElement objects �Tree traversal is implemented by overloading class iterators and magic methods __get(), __set(), … ◦ The structure can be modified and saved ◦ Some advanced functions are also available �Integrated XPath query processor �Conversions to/from DOM structure Example 3 by Martin Kruliš (v 1. 2) 15. 3. 2017 30
JSON - Revision � Java. Script Object Notation (JSON) ◦ Lightweight interchange format for structured data ◦ Based on subset of Java. Script language ◦ Otherwise language independent �Many parsers exist with frontends for many languages ◦ Intended for replacing XML in simple scenarios � Syntax ◦ Two basic structures: collections and lists ◦ Supports strings, numbers, bools, and null type ◦ Unicode safe by Martin Kruliš (v 1. 2) 15. 3. 2017 31
JSON – Revision � JSON Example [ Ordered list { Number (int) "Student. Id": 42, "Name": "John Smith" }, { } Unicode string Named collection "Student. Id": 54, "Name": "Jane Johnson", "Graduated": true Boolean literal ] by Martin Kruliš (v 1. 2) 15. 3. 2017 32
JSON in PHP � JSON Functions ◦ json_decode() – JSON string -> object/array struct. ◦ json_encode() – serialize any PHP type (except resource) into JSON string ◦ json_last_error(), json_last_error_msg() � Customizing Serialization Process ◦ Json. Serializable interface ◦ Abstract method json. Serialize() that converts object into anything that can be processed by json_encode() by Martin Kruliš (v 1. 2) 15. 3. 2017 33
Image Processing in PHP Martin Kruliš by Martin Kruliš (v 1. 2) 15. 3. 2017 34
Non-textual Data � PHP: Hypertext Preprocessor ◦ Not restricted only to (hyper)text, despite the name ◦ HTTP with MIME can accommodate any content �Compressed packages (zip, tar. gz, …) �Application documents (PDF, …) �Images � Caveats ◦ PHP is text oriented �Binary data are represented as strings ◦ PHP is interpreted �Processing binary data might be (very) slow by Martin Kruliš (v 1. 2) 15. 3. 2017 35
Images in PHP � GD Library ◦ The php_gd 2 module ◦ Supports various formats (JPEG, GIF, PNG, …) � Working with Images ◦ Image is a resource $image = imagecreate(400, 300); ◦ Large variety of functions for manipulation ◦ Image is not compressed in internal representation ◦ Can be saved or written to output �imagepng(), imagejpg(), imagegif() �Corresponding MIME type needs to be set by Martin Kruliš (v 1. 2) 15. 3. 2017 36
GD Library � Functions ◦ Loading/saving supported formats ◦ Basic pixel operations ◦ Drawing simple geometric shapes �Lines, circles, polygons, … ◦ Text rendering �Built-in fonts, Post. Script fonts, True. Type fonts ◦ Transformation and resampling ◦ Copying, alpha blending � Functions are version specific ◦ And time/memory demanding by Martin Kruliš (v 1. 2) Example 4 15. 3. 2017 37
EXIF � Exchangeable Image File Format ◦ Metadata for photographs ◦ Extension of JPEG and TIFF containers � PHP ◦ ◦ API Read only functions exif_imagetype() – returns image type exif_read_data() – reads meta-data from image exif_thumbnail() – returns the image thumbnail (if present) as binary string Example 5 by Martin Kruliš (v 1. 2) 15. 3. 2017 38
Other Libraries � Cairo ◦ Fast 2 D drawing library written in C ◦ Supports transformations and Bezier curves � Gmagic ◦ A Swiss army knife for image manipulation ◦ Large variety of supported formats � Image. Magic ◦ Well established library for image manipulation ◦ Designed even for more complex operations by Martin Kruliš (v 1. 2) 15. 3. 2017 39
Discussion by Martin Kruliš (v 1. 2) 15. 3. 2017 40
- Slides: 40