Code Spatter

Baron Schwartz, the lead author of High Performance MySQL 2nd Edition, has just announced OpenSQL Camp 2008 over at his blog.

Despite the name, this will be different from other Camp conferences you’ve been to. This is a combination of a planned event (with great speakers and sessions), semi-planned spontaneity (sessions to be decided by attendees the night before), and a hackfest. It’s the best elements cherry-picked from all the conferences (and un-conferences) you’ve been to.

The event is in Charlottesville, Virginia on Nov 14 to 16 which is Friday to Sunday. Travel and other important information is over at the event’s site.

I don’t know if I’ll be able to make this one since I already have a few trips planned, but it sounds awesome.

I ran across this skin while looking for new ones for a different blog. It happened to look similar to what I was hoping to create someday so I thought I would use it for a while. I’ve already modified a few small things to get it to look a little more to my liking, but it was overall very nice.

A while ago I posted about using Haloscan for a commenting system. There were many reasons I didn’t want to use it on here so I tested it out on a project site . A few weeks later, I heard about Disqus and it seemed to do everything I could ask for. I’ve implemented their system on Code Spatter to see how I like it. The best thing is I can stop using it and go back to wordpress comments with no hassle since they are stored in my database as well as on the disqus server.

Maybe I’m not the first to think of this, but it just came to me. Instead of using a single string to season a whole site or saving each new salt with the salted hash, try using this method for simplicity. Take the string and concatenate itself after it. This way, both the value being encrypted and the salt are never known and it’s still simple to validate input. Might be helpful if you actually fear someone building new dictionaries for each salt… or quantum computers.

And Why I Love Comments

Smart people keep you from doing something you didn’t think through. So, don’t use this.

TINYINT can be set to length of 4, but that doesn’t mean the max value is 9999. A tiny int will always have the same range no matter what size you specify since that isn’t what it is for with integers. It is the display width for the command line output.

This is what I was able to find in the MySQL manual:

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

Reading the manual for things like this isn’t something people normally do. I had no idea about this until recently and I’ve been using MySQL for a long time.

Reference Chart for Max Signed Integers

However, CHAR and VARCHAR work as expected with these limits.

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 More MySQL Tips

Check out these other posts:

• Tips for MySQL to PostgreSQL Switch
• How To Use Triggers to Track Changes in MySQL
• How to Break a MySQL Left Join