Monthly Archives: October 2007

mySQL mass update with REPLACE()

A nice shortcut I came across to update content in a mySQL-table without writing a script to do it – which was the first thing that came to my mind…

Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.

This snippet was the elegant way of solving this directly in mySQL without headaches:

UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');

or in case you forgot to switch off magic quotes and have all sorts of noisy data like this “Mary\\\\\’s house” with a random number of backslashes. Use this multiple times:

UPDATE applicant_publication SET title=REPLACE(title, "\\'", "'");
UPDATE applicant_publication SET authors=REPLACE(authors, "\\'", "'");
OR
UPDATE applicant SET research_skills=REPLACE(research_skills, '\\"', '"');

Of course you need one line for each field with that problem.

Sweet!

Sync structure of 2 mySQL databases

Maintaining apps with database backends often involves creating new tables or extending new fields in your existing tables. I have always kept a record of all CREATE and ALTER TABLE etc statements to apply them as a whole when going live with new features.

I found a useful tool to do a diff of the mysql structure of 2 databases (before and after)

Download: http://www.adamspiers.org/computing/mysqldiff/

mysqldiff requires Perl 5.

Install file says:

MySQL-Diff-0.30# perl Makefile.PL
MySQL-Diff-0.30# make
MySQL-Diff-0.30# make test
MySQL-Diff-0.30# make install

Which did not work since I’m not a Perl-guy and have nothing perl related installed on my boxes…

So you also need to install another Perl module using the CPAN shell interface:

MySQL-Diff-0.30# perl -MCPAN -e 'install Class::MakeMethods::Template'

Afterwards you have the ‘mysqldiff’-command ready to be used.
Usage: http://www.adamspiers.org/computing/mysqldiff/#usage

I use it this way having both databases to be compared on my dev-box:

MySQL-Diff-0.30# perl ./mysqldiff.pl --user=root --password=xxx before_dbname after_dbname > my_diff.sql

Be careful to generate the diff in the right direction.

You can then use the SQL-statements in my_diff.sql to update your database ‘before_dbname’. If not needed, delete unnecessary statements like AUTO_INCREMENTS etc manually.

Blogposts: Kevin Barnes’ Code Craft

Kevin Barnes and his amusing – slightly cynical – view on the software lifecycle.

Write software because you’re in panic.

Blogposts:

- “The code garden (an analogy that sucks less)”
‘Basically, code is like a garden. (…) Giving people a better analogy is one of the best ways to quickly change how they think about things.’

- Agile processes, are they killing our children?”
‘I doubt anyone could accurately diagram my development process. (…) Every line of code they don’t write is another line that can’t break.’

Think about your ‘Cyclomatic Complexity’

If you have asked yourself: When can you safely say you’re done writing unit tests. The answer lies in the ‘Cyclomatic Complexity’ of your code.

When you have written tests that exercise each possible path through your code, you are having a code coverage of 100%. For your unit-testing efforts this means: you’re done.

Thinking about your code in terms of ‘Cyclomatic Complexity’ as you write it may help you write more testable code.

A simple explanation: http://www.litfuel.net/plush/?postid=137

http://en.wikipedia.org/wiki/Cyclomatic_complexity

Coding Style Guides for different Languages

I found it interesting to skim coding style guidelines of different programming languages. Here are some links:

It all boils down to make code/architectures more readable and understandable no matter what the programming language. Remember: You will be reading code much more often than writing new code.

A nice quote: “Programs must be written for people to read, and only incidentally for machines to execute.” (Abelson & Sussman)