P'unk Avenue Window

You created a MySQL table with a foreign key reference… for instance, a user_id field that refers to a separate table of users.

Then you realized you should have specified “ON DELETE CASCADE” when creating the table. Now, when you try to delete a user, you can’t because objects in the other table are still referencing that user object. Ouch.

What to do, what to do!

Do this (substitute your table and column names, of course):

SHOW CREATE TABLE pk_context_cms_access;

Note the name of the foreign key constraint, which is usually automatically generated, and looks like this:

CONSTRAINT `pk_context_cms_access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`)...

Now drop the foreign key constraint. Relax! You’re not dropping the column involved, so you’re not losing any data. You’re just giving up the integrity check that makes sure foreign IDs refer to stuff that really exists and prevents you from deleting records unless you have set ON DELETE CASCADE… which is probably why you are reading this. (This will temporarily relax referential integrity requirements. In English, that means you should probably temporarily disable the site while blasting through these steps, if it serves billyuns of people every micro-moment.)

alter table pk_context_cms_access drop foreign key pk_context_cms_access_ibfk_1;

Now add the foreign key constraint back again, this time with ON DELETE CASCADE:

alter table pk_context_cms_access add foreign key(user_id) references sf_guard_user(id) on delete cascade;

Boom. Check “show create table pk_context_cms_access” again to see that it worked:

CONSTRAINT `pk_context_cms_access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE...

Now buy me a beer.

We make heavy use of Google Calendar here at work. Unfortunately I rarely remember to look at it unless an alert has specifically been set up to get my attention.

This morning I set up my Mac to open Google Calendar in my web browser every day at 10:15am.

How do you do that? Turns out it’s not hard.

You can open a URL at the terminal command line—

“Whoa! Did you just say ‘terminal command line?’ I thought you said this wasn’t hard?”

Well, it isn’t! All you really need to know about the terminal application is this:

1. It’s not hard.
2. Go to the Finder, click “Applications” (at lower left), open up “Utilities” in the pane at right, and then double-click “Terminal.” Now you have a terminal window to type commands in.
3. After each terminal command, one generally presses the Enter (return) key. So if I say nothing to the contrary, assume you’re meant to do that after each command.

You can open a URL in your web browser from the terminal command line by typing a command like this. Just copy and paste your Google Calendar’s URL from the address bar:

open 'https://www.google.com/calendar/hosted/punkave.com/render?tab=mc'

(Note that if your URL actually contains the ' character you’ll need to escape it with a \ in front.)

OK, you’ve opened a web page from the command line. Now, how do you schedule that to happen every day?

Luckily the Mac, like any good Unix box, offers a utility called cron which does things for you at scheduled times. To set that up, all you have to do is edit your cron jobs with the crontab command…

But this part is a little bit of a PITA: by default, the crontab command opens your cron jobs file with vi, an old-school Unix text editor. I’m happy with it, but you don’t need to be. So I’m going to first walk you through how to switch things around so that crontab opens things in good old TextEdit.

I’ll also show you how to set up a convenient command you can use in the terminal window to edit any file with TextEdit from the command line.

We’ll start by editing your ~/.profile file. This file contains commands that the terminal window runs when you first start it up.

First, use the touch command to ensure the file exists. TextEdit doesn’t like being asked to edit files that don’t yet exist:

touch ~/.profile

Now try this command (hint: don’t suffer, copy and paste it into terminal):

/Applications/TextEdit.app/Contents/MacOS/TextEdit ~/.profile

Your .profile file is open in TextEdit. This file contains Unix shell commands that should be run every time a new shell window opens for you. At first it will probably be empty.

Add these lines at the end of the file (which might be empty to start):

# Use TextEdit for crontab and similar commands
# that want to use your default editor
export VISUAL=/Applications/TextEdit.app/Contents/MacOS/TextEdit
# Use TextEdit when you type: edit filename
alias edit='/Applications/TextEdit.app/Contents/MacOS/TextEdit'

The lines that begin with # are comments and the command shell will ignore them. I provide them so you can remember why you did this stuff later.

A word of warning: when launched this way, TextEdit tends to open behind the terminal window. You might have to apple-tab around a bit to find it.

Finally, close your terminal window and open a new one. This ensures that the commands in ~/.profile have been run. Alternatively you can use this command to run the commands in ~/.profile in your current terminal window:

source ~/.profile

Now we're ready to edit your cron jobs... in TextEdit, not vi. At the terminal prompt, type:

crontab -e

And paste in this line (but substitute your Google Calendar URL of course, copied and pasted from your browser):

15 10 * * * open 'http://your.url.here/'

Then save your work and quit TextEdit. The crontab command can't continue until you quit TextEdit.
Now the URL you've pasted in will open every day at 10:15am. If Firefox is your default browser, it will open in a new tab. I haven't tried this with Safari as the default browser. Freel free to enlighten me as to how that works out.
What are those numbers and stars about? They represent minutes, hours, days of the month, months, and days of the week. We are only concerned with the first two: we want the job to run at 10:15am. If you want to run the job in the afternoon, be sure to use 24-hour time (for instance, set the second number to 13 for 1pm).
cron can do quite a bit more. If you want to know more about scheduling cron jobs, try this command:

man 5 crontab