Code Spatter

When selecting count, sum, or some other aggregate function, the value isn’t determined until after the WHERE clause so a condition can’t be placed there. There is still syntax for specifying a condition for this, it is to use HAVING.

The following example is to count all of the logs an active user has made only if they have more than two logs.

SELECT users.user_id, count(logs.log_id) AS count
FROM users
JOIN logs
ON users.user_id = logs.user_id
WHERE user.deleted = 0
GROUP BY user.user_id
HAVING count > 2

It works the same for sum. Example shows users who have logged less than an hour of time.

SELECT users.user_id, sum(logs.log_time) AS sum
FROM users
JOIN logs
ON users.user_id = logs.user_id
WHERE user.deleted = 0
GROUP BY user.user_id
HAVING sum < 60

Common aggregate functions this works with:

• AVG
• COUNT
• MAX
• MIN
• STD
• SUM

For some time now, I’ve been using a Magic the Gathering card database for a testing bed for various things. Earlier today I learned about a service that will provide commenting functionality for your website. HaloScan was easy to set up even for a non-blog type website. It only required a few lines of pasted JavaScript code in two places to get it going.

My test page shows what the free service has to offer (rating, comments, trackbacks). With a small donation more services would be available, which might be something I would consider if I ever take the mtg website seriously.

This quick-to-set-up service will be another tool to help those that are trying to get webservices up quickly, like a few articles I’ve read recently.

If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get into any reasons why to move to PostgreSQL or not. There are already many discussions on the topic.

Create Syntax

The first five listed need to be done in order; the rest can be in any.

• Replace mediumint with int
• Replace tinyint with smallint
• Replace int\([0-9]+\) with int. (This is a regular expression that will find any instance of int() where there is at least one number between the parenthesis)
• Remove all instances of NOT NULL I don’t know what I was thinking here.
• Replace int\s+auto_increment with SERIAL (Another regular expression that will catch a case with multiple spaces between int and auto_increment).
  1. UPDATE: try int(\s+|\s+NOT NULL\s+)auto_increment instead to catch when not null is in between int and auto.
  2. UPDATE: try (smallint|int)(\s+|\s+NOT NULL\s+)auto_increment so it will catch the case when it is smallint as well.
• Remove ENGINE=MyISAM (Change if you use a different engine, i.e. innodb)
• Remove DEFAULT CHARSET=latin1 (Change if you use a different character set)
• Remove AUTO_INCREMENT=[0-9]+ (Another regular expression to find all cases of an auto increment number)
• Remove IF NOT EXISTS
• Remove all `

phpMyAdmin

phpMyAdmin has options when exporting that may make some of these steps unnecessary. If you keep the create sql in a separate file from the insert sql, you save yourself from changing any data that may look like some of the above when it isn’t actual sql syntax.

Auto Increment and Serial

After running the create table SQL, a sequence table will be created for the serial column. This sequence will need to be altered if the auto increment won’t be starting at one. A query like the one below will need to be done for each table.

ALTER SEQUENCE tab_name_col_name_seq RESTART WITH #;

Indexes

To define a column to be indexed, it is done in a separate query. Remove anything that looks like KEY username (username). The query to run after the create table query should look like

CREATE INDEX index_name ON table_name (column_name);

Insert Syntax

The syntax here is pretty close, just one thing needs to be changed.

• Replace “” with \”. (MySQL and PostgreSQL use different methods of escaping these quotes.)

Make sure the complete inserts option is enabled and extended inserts isn’t. PosgreSQL doesn’t support multiple inserts with one query the same way. (As far as I know at the moment)

Your Mileage May Vary

This was tested using phpMyAdmin export and phpPgAdmin SQL executing. The regular expressions and other replace commands were done in jEdit. With my few test cases I probably didn’t catch everything. Let me know how it works out for you.

Going on an amazing road trip used to mean that you couldn’t update your stay-at-home friends about the stupid things you’ve done on your adventures until you return. I am going on the BABE Rally with an old, ‘71 VW Van. My friends at home will all remain updated throughout the trip since with current technologies it is now possible to blog about long tales, twitter about quick stories, and keep friends updated on your location with GPS and Google Maps.

[Read the rest of this entry...]

Setting constraints and rules in the database is better than writing special code to handle the same task since it will prevent another developer from writing a different query that bypasses all of the special code and could leave your database with poor data integrity.

[Read the rest of this entry...]