coderamblings.com

In the first blog on Database Migration this post skimmed some info on starting the journey. This blog posts is a little drier, and more detailed, listing some major steps of database import and a few code samples. Shown below is my partner in crime for the second project, Harry. The current methods used are always open to new methods of improvement.

Image courtesy of Steve Krueger

In an attempt to clear my mind, and challenge myself, I asked Dave Olson if I could volunteer some time to help him blog an item or two. I was expecting to help write up about a local event around town, instead I was challenged a little bit more to my dismay.

Dave suggested to write a weekly blog post, about some of the work that I have been involved with. Of course he had answers to my protests;

I am sure somebody out there knows a better way than I, and I definitely don't know all there is to know on this subject. His answer, that is part of the reason one blogs, to get feedback from the community.

Should I be my blunt self, or put on a good company front, pasted with unicorns and butterflies? No, this is the new era of openness, one can be honest and open.

After my first pirate post as a new employee to RCS, 4 months later, here are some of my high hopes for Raincity Studios, Vancouver and open source everywhere.

Being one of the organizers of the Open Web Vancouver conference, I and other fellow organizers have invested a portion of our time gratis, for hopefully the benefit of the local opensource Vancouver community and neighbours.

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

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

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

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