SQL machinations

Recently, when performing a slew of alter table statements, I had to do some checking to verify, that I had not completely destroyed my database.

From unix commandline
$ mysqlshow --status my_table

From mysql commandline
> SHOW TABLE STATUS FROM my_database;
> FLUSH TABLES;
> SHOW INDEX FROM my_table;
> ANALYZE TABLE my_table;
> CHECK TABLE my_table;
> OPTIMIZE TABLE my_table;
> DESCRIBE my_table

PHP's filter extension

When I read about PHP's filter functions, http://php.net/filter, I had to try them out.

the install promises to be short and sweet: $ pecl install filter

in reality, it was anything but sweet, after a little searching around, here are the commands that I used with help from pecl bug 9680, for installation on feisty ubuntu:

> sudo apt-get install php5-dev
> sudo apt-get install libpcre3-dev
> sudo pecl install channel://pecl.php.net/filter-0.9.2

Vim of Course!

When I voted and lobbied enthusiastically to have Andrei Zmievski talk about Vim at the Vancouver PHP Conference, I had no idea just how fun the talk was going to be. As a user of vi, it is my editor of choice. Previously I was wondering if I was the only one, and a bit cuckoo. My boyfriend used emacs, as well as many others, if not using the Zend IDE or Komodo.

However, at the session of Vi, the good attendance heartened my spirits to realize that I may not be the only old-fashioned coder around.

Also, when the final votes were tallied up, surprise, surprise, Andrei received the highest average, beating out even Rasmus's keynote speech.

Check out Andrei's slides, and download the tarball, Vim of course!

MySQL benchmark left join vs not in

To decide whether to use 'NOT IN' or a left join, to find out all the rows in a table that did not have a relationship to another table, e.g. find all houses with no kittens.

I used the benchmark() mysql command to compare, in summary there was not much difference at all, using the mysql command prompt from localhost.

Statement 1
select benchmark(10000000000,'select * from houses where id not in (select house_id from kittens) ORDER BY id');
18.47s
18.51s
18.49s
18.52s
18.52s

Statement 2
select benchmark(10000000000,'select houses.* from houses left join kittens on houses.id=kittens.house_id where kittens.house_id is NULL ORDER BY houses.id');
18.51s
18.49s
18.50s
18.50s
18.51s

MySPL stored procedures in MySQL

In 2004, stored procedures were introduced to MySQL 5 with a team under the direction of Mr Per-Erik Martin.

Here is the example Peter Gulutzan listed

DELIMITER //

CREATE PROCEDURE payment [2]
(payment_amount DECIMAL(6,2),
payment_seller_id INT)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = payment_amount - 1.00;
INSERT INTO Moneys VALUES (n, CURRENT_DATE);
IF payment_amount > 1.00 THEN
UPDATE Sellers
SET commission = commission + 1.00
WHERE seller_id = payment_seller_id;
END IF;
END;
//

Looping
Statement labels can be used before BEGIN, WHILE, REPEAT OR LOOP. The statement 'LEAVE {labelname}' will jump out of the compound statement.
ITERATE used before the label name, is like 'CONTINUE'

Variable Limit clause in Select
Stored procedures do not handle the 'LIMIT 0,30' yet.
However, a few work-arounds have been suggested in this bug.

One of the ideas suggest to use a prepared statement, which can handle the LIMIT clause just fine, inside the stored procedure.

PHP 5 calling Stored Procedures
Using MySQLi:
* Create a new mysqli ocnnection
$dbh = new mysqli($hostname, $username, $password, $database);
* Check that the mysqli connection is properly connected using mysqli_connect_errno()
* Call the stored procedure using $dbh->query("call procedure_name( $variables )") and assign it to a result set else, print use errno to print
* Iterate over the result set with fetch_object

Using PDO:
$pdo = new PDO('mysql:localhost;dbname=pdotest', 'root', '123182');
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
$stmt = $pdo->query('CALL
pdotest.spIsStringComparisonCaseInsensitive()');

foreach($stmt as $row)
{
echo $row['isInsensitive'];
}
$stmt->closeCursor();
$stmt = null;

Recommended Resources
I highly recommend reading the book:
MySQL Stored Procedure Programming
By Guy Harrison, Steven Feuerstein

Myself, I admit have not touched the book, except download the excellent examples, which includes numerous sql examples, as well as php, java, python, amongst others.

Another excellent resource is:
MySQL 5.0 Stored Procedures
Peter Gulutzan

Useful commands
List of all procedures: SHOW PROCEDURE STATUS;
Narrowed down: SHOW PROCEDURE STATUS LIKE '%Test%';
List of all functions: SHOW FUNCTION STATUS;
Display details of a sp: SHOW CREATE PROCEDURE sprocTest;
Change name of a sp: ALTER PROCEDURE sp_myfirst NAME sp_second;


Restrictions on Stored Routines and Triggers
Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:
* The locking statements LOCK TABLES, UNLOCK TABLES.
* LOAD DATA and LOAD TABLE.
* SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE), only applies to versions pre 5.0.13
Read about more restrictions here:
Stored routine restrictions

Reference:
MySQL Stored Procedure Programming
Mysql.org Reference on Stored Procedures
Stored Procedures in MySQL
Stored Procedures in MySQL 5.0
MySQL and Stored Procedures
A DIY approach to Stored Procedures in MySQL
Stored Procedure Programming for MySQL 5

Bugs
http://phpbuilder.com/board/showthread.php?t=10332897
http://bugs.mysql.com/bug.php?id=11918