DPC2007 MySQL Stored Routines for PHP Developers (Roland Bouman)

Notes:	Hide Notes

Slide 1: Amsterdam, June 16 2007 ● MySQL: Quick Introduction ● MySQL Stored Routines for PHP developers ● Questions and Discussion Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 1 Slide 2: Roland Bouman (rpbouman.blogspot.com) Certification Developer MySQL AB, Leiden ● MySQL Community Contributor since 2005 ● Joined MySQL AB in July 2006 ● Certification Developer: ● MySQL 5.1 Cluster DBA exam (CMCDBA) ● MySQL Associate exam (CMA) ● Attained: ● CMDEV ● CMDBA ● Formerly: Consultant & Application Developer (mostly Oracle, some MS SQL) Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 2 Slide 3: Amsterdam, June 16 2007 ● MySQL: Quick Introduction ● MySQL Stored Routines for PHP developers ● Questions and Discussion Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 3 Slide 4: MySQL: Quick Introduction ● MySQL Software Products ● ● ● ● ● ● ● ● ● RDBMS (Database) Monitoring and Advisory Service Drivers and APIs Client (GUI) Tools & Utilities Technical Support Consulting Training Certification Indemnification ● MySQL Professional Services Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 4 Slide 5: MySQL Open Source RDBMS Products ● Generally Available, Stable Releases ● MySQL 5.0 Server Community Edition ● Patches from community ● MySQL 5.0 Server Enterprise Edition ● Stablility and Robustness, Early Bugfixes ● Monitoring and Advisory Service ● Binaries ● Development Releases: ● MySQL 5.1 (New: Events, Partitioning) ● MySQL 6.0 (New: Falcon) ● Other RDBMS Products: ● MySQL Cluster / Carrier Grade Edition ● Embedded ● MaxDB (SAP Certified) Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 5 Slide 6: MySQL Monitoring and Advisory Service Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 6 Slide 7: MySQL Drivers and APIs ● For PHP ● ext/mysql: “MySQL Functions” ● ext/mysqli: “MySQL Improved Extension” ● mysqlnd: “MySQL native driver for PHP” ● Beta 5. ● Built Into PHP 5; PHP 6 ● Replaces libmysql ● Other: ● ODBC: Connector/ODBC ● JDBC: Connector/J ● ADO.NET: Connector/.NET Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 7 Slide 8: Amsterdam, June 16 2007 ● MySQL: Quick Introduction ● MySQL Stored Routines for PHP developers ● Questions and Discussion Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 8 Slide 9: MySQL Stored Routines ● ● ● ● ● Overview of MySQL Stored Routines MySQL Stored Routine Language PHP Techniques Use cases Best Practices Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 9 Slide 10: MySQL Stored Routines ● Overview of MySQL Stored Routines: ● Terminology, Purpose, Application ● MySQL Stored Routine Language ● Block Structure, Parameters and Variables, Flow Control Constructs, SQL inside stored routines. ● PHP Techniques ● Creating and Calling Stored Procedures, Processing Result set, Handling Multiple Result sets. ● Use cases ● Best Practices ● What to do on the client, and what on the server ● What to do in PHP, and what in Stored Routines. ● Performance: how MySQL Stored Routines can help or hurt performance. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 10 Slide 11: MySQL Stored Routines: Overview ● Programs as Database Schema Objects ● Executed in-process with the Database ● Types of Stored Routines: ● ● ● ● Procedures Functions Triggers Events (Temporal triggers; new in MySQL 5.1) ● Language: ● Subset of Standard SQL:2003 SQL/PSM ● Procedural, Block structured ● Do not confuse with User Defined Functions (UDF)! ● Available as of MySQL 5.0 (October 2005) Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 11 Slide 12: MySQL Stored Routine Types: Overview ● Stored Procedures & Functions ● ● ● ● ● ● ● ● Encapsulate tasks or Calculations for reuse Single point of definition for Business Logic Source Safely stored and backed up Added layer of Security Data-Driven Enforce Data quality through Basic validation Enforce complex Business Rules Automatically Update Aggregate tables ● Triggers ● Events (MySQL Server 5.1 beta) ● Schedule Code Execution in time. ● Use instead of cron or windows event scheduler ● Automatically Update Aggregate tables Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 12 Slide 13: MySQL Stored Routines: Purpose / Advantages ● Performance ● Save network roundtrips, lower latency ● Portability and Reuse ● Single point of definition ● Reusable from many application contexts ● Security ● DEFINER versus INVOKER ● Grant only Execution Privilege ● Ease of Maintenance ● Code stored in the database ● Browse using information_schema database ● 'Headless' administrative tasks ● No additional runtime environment required Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 13 Slide 14: MySQL Stored Routines: Caveat / Disadvantages ● Performance ● Overhead may result in higher latency ● Increased usage of database server computing power may negatively affect throughput ● Portability and Reuse ● Which point of view? ● Database portability ? ● Or Application portability? Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 14 Slide 15: MySQL Stored Routines ● Overview of MySQL Stored Routines: ● Terminology, Purpose, Application ● MySQL Stored Routine Language ● Block Structure, Parameters and Variables, Flow Control Constructs, SQL inside stored routines. ● PHP Techniques ● Creating and Calling Stored Procedures, Processing Result set, Handling Multiple Result sets. ● Use cases ● Best Practices ● What to do on the client, and what on the server ● What to do in PHP, and what in Stored Routines. ● How MySQL Stored Routines can help or hurt performance. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 15 Slide 16: MySQL Stored Routine Language ● Subset of Standard SQL “Persistent Stored Modules” (SQL/PSM) ● Procedural constructs with embedded SQL ● Parameters and (Local) variables ● Manipulate values ● Statement Sequence ● execute statements in order ● Choice ● conditionally execute a particular sequence ● Repetition ● execute a particular sequence multiple times ● A bit like Pascal with embedded SQL statements ● Valid inside all stored routine types ● Can be mixed with most SQL statements Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 16 Slide 17: Creating a MySQL Stored Procedure • Prerequisite: CREATE ROUTINE and ALTER ROUTINE privileges CREATE PROCEDURE sp_hello( p_who VARCHAR(32) ) SELECT CONCAT('Hello, ',v_what,'!!'); • CREATE PROCEDURE DDL statement • Created in the current schema (= database) • Name (sp_hello) must be unique with in the schema, may be qualified (my_db.sp_hello) • Parameter (p_who): IN parameter by default • Procedure body is one single statement, in this case, an ordinary SQL SELECT statement. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 17 Slide 18: Calling a MySQL Stored Procedure • Prerequisite: EXECUTE ROUTINE privilege CALL sp_hello('PHP'); • CALL statement • Name identifies the procedure within the schema, and maybe qualified: CALL my_schema.sp_hello('PHP') • Must pass a parameter value Result: Hello, PHP! • Result set returned to the client Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 18 Slide 19: Generic Statement Sequence: BEGIN...END CREATE PROCEDURE sp_greating( p_who VARCHAR(32) , p_what VARCHAR(32) ) BEGIN SELECT CONCAT('Hello ', p_who); SELECT CONCAT(p_what,'!'); END • BEGIN...END is a compound statement; it may contain multiple other statements. • Contained statements executed Sequentially (in order of appearance) • (Sidenote: 2 result sets are returned to the client) Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 19 Slide 20: Variables and Parameters CREATE PROCEDURE sp_fibonacci( INOUT p_m INT, INOUT p_n INT, OUT p_s DOUBLE) BEGIN DECLARE v_m INT DEFAULT COALESCE(p_m,0); DECLARE v_n INT DEFAULT COALESCE(p_n,1); SET p_m := v_n; single assignment SET p_n := v_m + v_n multiple , p_s := p_m/p_n; assignments SELECT p_m, p_n, p_s; END; • IN, OUT and INOUT parameters • DECLARE local variables, optionally assign a default value • Use SET to assign values to one or more variables Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 20 Slide 21: Variables and Parameters call sp_fibonacci(@m,@n,@s); ++++ \| p_m \| p_n \| p_s \| ++++ \| 1 \| 1 \| 1 \| ++++ call sp_fibonacci(@m,@n,@s); ++++ \| p_m \| p_n \| p_s \| ++++ \| 1 \| 2 \| 0.5 \| ++++ call sp_fibonacci(@m,@n,@s); ++++ \| p_m \| p_n \| p_s \| ++++ \| 2 \| 3 \| 0.666666666 \| ++++ Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 21 Slide 22: Variable Scope / Visibility BEGIN DECLARE v_script, v_http VARCHAR(32); SET v_script := 'PHP', v_http := 'Apache'; SELECT v_script, v_http; BEGIN DECLARE v_http VARCHAR(32); SET v_http := 'lighttpd'; SELECT v_script, v_http; END; SELECT v_script, v_http; END; • Variables are visible only inside the declaring block • Nearest Scope: inner declarations mask outer ones Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 22 Slide 23: Choice Constructs ● Compound statements ● IF...END IF statement ● ● ● ● Simple test of a single condition Conditionally start a sequence of statements (branch) Optionally, chooses between two branches Don't confuse with the IF() function! ● CASE...END CASE statement ● Conditionally starts one out of multiple branches ● Simple CASE statement ● Just like switch in PHP ● Searched CASE statement ● Just like nested if...elseif...else in PHP ● Don't confuse with the CASE..END expression! Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 23 Slide 24: Syntax: IF...END IF ● Tests condition, branches when TRUE ● Conditional branch can contain a sequence //main, unconditional branch IF <condition> THEN <statements> “true” branch END IF; ● Optionally, include a branch for the other case: IF <condition> THEN <statements> ”true” branch ELSE <statements> ”false” branch END IF; Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 24 Slide 25: IF statement vs IF function ● An IF statement chooses between sequences of statements IF CURRENT_TIME < '12:00:00' THEN SELECT 'Good Morning'; ELSE SELECT 'Good Afternoon'; END IF; ● IF function chooses between expressions SELECT IF(CURRENT_TIME < '12:00:00' , 'Good Morning' , 'Good Afternoon' ); Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 25 Slide 26: Syntax: Simple CASE..END CASE ● Evaluate expression and compare ● Conditional branch can contain a sequence ● Optional ELSE branch ● Just like switch...case in PHP CASE <expression> WHEN <expression1> THEN <statements> WHEN <expression2> THEN <statements> ELSE <statements> END CASE; Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 26 Slide 27: Syntax: Searched CASE..END CASE ● Search first TRUE condition, then branch ● Conditional branch can contain a sequence ● Optional ELSE branch ● Just like if...elsif...else in PHP CASE WHEN <condition> THEN <statements> WHEN <expression> THEN <statements> ELSE <statements> END CASE; Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 27 Slide 28: Repetition ● Unstructured Loop ● No explicit logic to end the loop LOOP <statement> END LOOP; ● Structured ● Logic to end the loop is part of the construct REPEAT WHILE <statement> <condition> UNTIL DO <condition> <statement> END REPEAT; END WHILE; ● Iterate: ● Leave: exit the current block Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 28 Slide 29: Triggers CREATE [DEFINER = { <username> \| CURRENT_USER }] TRIGGER <triggername> {BEFORE \| AFTER} {INSERT \| UPDATE \| DELETE} ON <tablename> FOR EACH ROW <singlestatement> ● Automatically executed in response to rowlevel events occurring on table ● Can refer to OLD and NEW pseudo-records ● INSERT: NEW ● DELETE: OLD ● UPDATE: both OLD and NEW ● Executed as part of transaction Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 29 Slide 30: Event Scheduler (Temporal Triggers) ● New in MySQL 5.1 ● Automatically executed according to time schedule ● Can be recurring ● Can be scheduled to start in the future ● Can be instructed to clean itself up Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 30 Slide 31: MySQL Stored Routines ● Overview of MySQL Stored Routines: ● Terminology, Purpose, Application ● MySQL Stored Routine Language ● Block Structure, Parameters and Variables, Flow Control Constructs, SQL inside stored routines. ● PHP Techniques ● Creating and Calling Stored Procedures, Processing Result Set, Handling Multiple Result Sets. ● Use cases ● Best Practices ● What to do on the client, and what on the server ● What to do in PHP, and what in Stored Routines. ● How MySQL Stored Routines can help or hurt performance. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 31 Slide 32: PHP and MySQL Stored Routines ● Two relevant PHP extensions ● MySQL Functions (ext/mysql) ● CREATE PROCEDURE and CALL work fine ● Just use the PHP function mysql_query() ● However, obtaining a result set is impossible ● MySQL Improved extension (ext/mysqli) ● use mysqli_query() for one result set ● For multiple resultsets, use ● ● ● ● ● mysqli_multi_query() mysqli_use_result() mysqli_store_result() mysqli_next_result() mysqli_more_results() Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 32 Slide 33: mysql_query() and Stored Routines <?php $db = mysql_connect($host,$usr,$pwd); mysql_select_db('test',$db); $result = mysql_query( "CALL sp_hello('PHP')",$db ); $num_rows = mysql_affected_rows($db); echo '<br/>num: ', $num_rows; echo '<br/>msg: ', mysql_error($db); echo '<br/>no: ', mysql_errno($db); ?> num: 1 msg: PROCEDURE test.sp_hello can't return a result set in the given context no: 1312 Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 33 Slide 34: mysqli_query() and Stored Routines <?php $db = mysqli_connect($host,$usr,$pwd); $db>select_db('test'); $result = mysqli_query( $db,"CALL sp_hello('PHP')" ); $row = mysqli_fetch_row($result); echo $row[0]; ?> Hello, PHP! Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 34 Slide 35: Handling Multiple result sets <?php $db = mysqli_connect($host,$usr,$pwd); $db>select_db('test'); $db>multi_query( "CALL sp_greating('PHP','Good Morning')" ); while($result = $db>store_result()) { while ($row = $result>fetch_row()) { echo $row[0]; } $result>close(); $db>next_result(); } ?> Hello PHP, Good Morning! Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 35 Slide 36: MySQL Stored Routines ● Overview of MySQL Stored Routines: ● Terminology, Purpose, Application ● MySQL Stored Routine Language ● Block Structure, Parameters and Variables, Flow Control Constructs, SQL inside stored routines. ● PHP Techniques ● Creating and Calling Stored Procedures, Processing Result set, Handling Multiple Result sets. ● Use Cases ● Best Practices ● What to do on the client, and what on the server ● What to do in PHP, and what in Stored Routines. ● How MySQL Stored Routines can help or hurt performance. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 36 Slide 37: MySQL Stored Routine Use Cases ● Stored Procedures ● subtypes and vertical partitioning ● data intensive transformation ● Stored Functions ● domain specific calculations ● data transformation ● Triggers ● Auditing ● Automatically Aggregate tables ● Events ● Logging status ● Updating aggregate tables “Materialized views” ● ETL processes Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 37 Slide 38: MySQL Stored Routines ● Overview of MySQL Stored Routines: ● Terminology, Purpose, Application ● MySQL Stored Routine Language ● Block Structure, Parameters and Variables, Flow Control Constructs, SQL inside stored routines. ● PHP Techniques ● Creating and Calling Stored Procedures, Processing Result set, Handling Multiple Result sets. ● Use Cases ● Best Practices ● What to do on the client, and what on the server ● What to do in PHP, and what in Stored Routines. ● How MySQL Stored Routines can help or hurt performance. Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 38 Slide 39: MySQL Stored Procedures: Best Practices ● Use pure SQL when you can ● Use stored procedures for data-intensive operations ● Don't use stored procedures for complex computation ● Don't use stored procedures for single layer encapsulation ● Simple CRUD layers don't scale ● Stored Procedure should add significant functionality ● Return multiple result sets from stored procedures to reduce network roundtrips Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 39 Slide 40: MySQL Triggers: Best Practices ● Use triggers to enforce integrity of data ● Using triggers does not mean the application can forget about validation Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 40 Slide 41: Amsterdam, June 16 2007 ● MySQL: Quick Introduction ● MySQL Stored Routines for PHP developers ● Questions and Discussion Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 41 Slide 42: Copyright 2007 MySQL AB The World’s Most Popular Open Source Database 42