Ok - I had to install MySQL on my Mac for the first time - using a tar of the binaries none the less. And it has been interesting. These are notes mostly for myself but if others find them helpful - great.

1. instructions I used - http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html
2. to create a group in 10.5 - use dscl. In my case ‘dscl . -create /groups/mysql’
3. to create a user in 10.5 - again use dscl. ‘dscl .-create /Users/mysql’
4. I had to make /usr/local with mkdir -m 775
5. I did not have to decompress things since the Mac did it for me - but I did have to place it in my Library dir and make the ln to it in my /usr/local dir
6. DO NOT FORGET to give your root user a password and either drop the anonymous user or put a password on it!

Other notables (yes I am a server noob):

• start the deamon with ’sudo bin/mysqld_safe –user=mysql &’
• If you forget the ampersand at the end - use cntl+z to escape then use ‘bg’ to put the deamon in the background
• stop the deamon with ‘bin/mysqladmin -u root shutdown’

Other notes to self on having multiple versions available:

1. option 1 - have multiple binary installs in their own folders and just change the ln.. or have multiple ln with individual names. since I do not need to have all of them running at one time - I can start and stop the deamons as I need.
2. option 2 - is provided in the manual at http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html

I am pleased and proud to say that come Monday - I will be working for MySQL (*sigh* - Ok Sun). I will be working for a Dream Company of mine - doing a Dream Job. Life does not get better then that.

So to everyone who kept telling me I would get the job when I swore there was no way (you know who you are) - feel free to tell me “I told you so” next time we talk - and first beer is on me when we meet.

It has been a really long time since I wrote anything - luckily the folks at phparchitect gave me just the push I needed to get back to my writing. Took me a while to get back in the hang of it again - but I did it - and even finished by my deadline (even if it was turned in a day late).

So if you feel the need to learn a bit more about foreign keys and what they can do for you the coder - be sure to check out my article on them at c7y

I was asked to post this after explaining it to someone on IRC.

If you have tried to understand how joins work and constantly get confused about what join to use, you just need to keep a simple picture in mind ( I like pictures). I will be explaining joins by referencing a Venn diagram. Have no fear - I won’t get into any of the set theory/math involved. This is just a basic overview to give you an idea the data a particular join will return to you. This is not a technical discussion - just concepts.

We will start with just an empty diagram:

The T1 circle represents all the records in table 1. The T2 circle represents all the records in table 2. Notice how there is a bit of overlap of the 2 circles in the middle. Simple right?

I will use red to signify the records that will be returned by a particular join.

INNER JOIN
An inner join only returns those records that have “matches” in both tables. So for every record returned in T1 - you will also get the record linked by the foreign key in T2. In programming logic - think in terms of AND.

OUTER JOIN
An outer join is the inverse of the inner join. It only returns those records not in T1 and T2. “Give me the records that DON’T have a match.” In programming logic - think in terms of NOT AND.

LEFT JOIN
A left join returns all the records in the “left” table (T1) whether they have a match in the right table or not.

If, however, they do have a match in the right table - give me the “matching” data from the right table as well. If not - fill in the holes with null.

It should be noted that the same thing is possible with a right join - most people just use a left one.

LEFT OUTER JOIN
A left outer join combines the ideas behind a left join and an outer join. Basically - if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table.

Again it is noted that the same thing is possible with a right outer join - most people just use a left one.

Theta JOIN
A theta join is the Cartesian product of the 2 tables and not normally what people are looking for - but what they sometimes get by mistake. How many of us have written a join similar to this only to get way more then we were ever expecting.

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = 5;


So there you have the basic concepts of joins. Next time you need to use a join and have no clue what to do to get the data you need from the database, draw a picture. It may help you figure out what join to use. Least it helps me.

Important:Please be sure to read the comments below. There are definite situations where my simplistic drawings are invalid and brought to light.

Disclaimer: I am by no means a database guru. Please be sure to learn more about joins from the many online articles available.

MySQL User Defined Functions

Did you know you could make custom functions for use in your SQL queries? You could make an entire library of custom functions to work and manipulate your data on your database making them application and language independant. A few possible functions you could make -

• Calculate an Employee’s Accumulated Vacation Time - maybe pass in the Employee ID and return the number of Hours they have in vacation time available.
• Customer Address - Pass in the Customer ID or name and return their mailing address in a standardized format.
• Account Balance - Pass in the Account number and return the Current Balance (assuming you have a table of debits and credits)

So now you would not have to worry about doing the calculations in the application - you can leave it to the database. Pretty Cool.

MySQL’s user defined function syntax is very similar to stored procedures and kinda self explainatory. Below is a simple example.

Example

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT
    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

This is example of a user defined function simply tells us TRUE(1) or FALSE(0) if a number we pass to it is odd.

Create

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT

This line states that we are going to create a user defined function by the name of isOdd. The function isOdd will take in an integer that we will call value, and return out a tinyint. Makes sense when we say it like that, but can you point to all the parts? As we said - it is similar to a stored procedure that you feel like you should know it - but syntatically different enough that you can trip and fall a couple of timesif you don’t pay attention.

Return

    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

Here is where we actually calculate what to return and then return that value, just like in a PHP user defined function.

Usage

mysql> select isOdd(13);
+-----------+
| isOdd(13) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select isOdd(14);
+-----------+
| isOdd(14) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

Here we demonstrate how we can now use the newly created user defined function in a standard SQL statement. These SQL statements could have been sent from the developers application just like any other MySQL function. Again we made sure to do some basic testing of the function.

Right about now I guess I should note that this is a very simple example and that all the logical controls and looping functions that are available to stored procedures do work in stored user defined functions as well. You can make the functions as simple or complex as you need.

So there you have it. Now go - and create your own functions to use in MySQL.

MySQL: Storing Passwords in MySQL

An interesting write up on a MySQL’s encryption possibilities. I have to admit it - been using PASSWORD() to encrypt my passwords and found out I shouldn’t be. Guess I need to stop that and try out the other possibilities presented here.

He goes over SHA1, MD5, AES and briefly, at the end, touches on DES.

Found this article through planet MySQL. In it he goes over 4 different “types” of abstraction layers that typically are collectively called a “database abstraction layer” - though they are very different from one another. I found it an interesting read.

His 4 types:

1. A software library to connect to a database server and issue queries, fetch results etc.
2. A software library to present a common API to different database servers.
3. A software library to automatically generate portable SQL queries.
4. A software library to map Object-Oriented Programming to a relational database (Object-Relational Mapping, or ORM)

Well I’ll be danged. Didn’t expect the second part of my “Stored Procedure Programming for MySQL” to be put up so quickly on the PHP Architect A/R/T site.

Anyway - now that it is up… do me a favor and read it. Feedback, Criticism and Suggests are always welcome. Can’t improve otherwise.

Well my article for the php|a A/R/T has finally been posted. You can check it out here. Be sure though to come back and tell me what you think of it. Feedback will really help for the second part of it.

Been doing a little research to try and figure out when it is appropriate to use stored procedures in an application. Most of the answers seem to be “It depends”. My question is - “What does it depend on and how much for each?”

Does it depend on the bandwidth limitations, the complexity of the sequences of the SQL statements, the abstraction away from the application/database, the number of languages used to connect to the database, or complexity of the entire system that interacts with the database…

The only situation I can immediately see is if you have a large complex system built from multiple parts in multiple languages and you want a standard API for the data layer. Then I can see all your database interactions being done with stored procedures… but that to me is an extreme case.

Now I realize there are no easy answers to this question - but if anyone would happen to know of any case studies or links on the decision making process involved, I would appreciate it.

Well I did it - I passed the MySQL Core Exam (at least that is what the paper that they gave me at the testing center says). This is going to sound horrible but - it was both harder and easier then I thought it would be. They asked about things I never heard about, things I did hear about but didn’t remember the details, and luckily stuff I did know (thank goodness I had the study guide and used it). The stuff I thought would be on the exam - wasn’t - so the studying saved my butt.

Well thank goodness that is over… Though I still want to go for my Zend cert sometime around the end of the year. But that is a long way away yet. For now - I just want to sit down and read a good book that has nothing to do with computers.

I have just found a pair of really good Database tutorials. They don’t teach SQL - what they teach is Database design (Data Modeling and Relational Modeling)… ERD’s, referential and entity integrity, normalization. All the good stuff. Now I haven’t read both of them in their entirty… skimmed over parts that I already knew, but read parts that I haven’t used/remembered. There were a few parts that I was taught using slightly different terminology… but I quickly realized/recognised them.

I had completely forgotten about super and subtype heirachies, domains, and the term tuple. The INSERT and DELETE integrety rules was a very clear and concise refresher, and the review of data manipulations using set theory definately made me sit up and get the brain thinking (need to go back again and reread it)… I also found a few things I had never heard of - 4th and 5th normal forms for example.

Definately need to remember this site - I’m sure others will want to look it over and learn… and I’m sure I will point others there to learn a few things.

Ever have this situation:

Well on the MySQL mailing list a couple of ways of handling this was pointed out. It was enlightning to me - so I thought I'd pass it on. The interesting part is that they did it with only 1 SQL statement.

Method 1
One method pointed out:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
-> ON DUPLICATE KEY UPDATE c=c+1;

quote from the MySQL manual page

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

There are of course gotcha's to watch out for so be sure to read up in the manual and I don't beleive this is portable to other DB's... but that is a slick little piece of SQL for MySQL.

Method 2
The second way they noted for handling this is with the REPLACE syntax.

Quote from the manual:

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

Sweet! So which do you want to do... update or delete and insert? Anyone have any idea which is faster or uses less resources?

It ha been a very long time (over 2.5 years) since I wrote any stored procedures in PL/SQL. But now the MySQL 5 has them I will be pulling out those old rusty skills and turning my hand to them again.

Why - because *gasp* the boss wants to switch over to MySQL 5 now that it is production ready. I have been told to learn about the new cool features - stored procedures, triggers, views specifically - and how PHP can work with them. Very Cool.

Is it just me - or am I the bosses knowledge bank (not that I'm complaining). First learning about various bug tracking systems, then Perl and now this... When ever he wants to know about something - he sends it to me. Kinda nice since I get to expand my own knowledge with at least all of the subjects basics.

Wouldn't mind playing a little bit more with Perl and it's modules - but I think I pretty good basic grasp of it now. At least - enough of one not to hurt myself very much or often with it. Stored procedures are coming back kinda - again it has been a long while and I really only did basic stuff then - but it is fun learning this stuff.

"Jack of all trades - master of none".