Note to self
It is never a good idea to put in an infinite retry loop and tell yourself “Oh, this’ll never happen”.
February 2009
4 posts
In praise of appropriate simplicity
Recently on a mailing list, an inexperienced programmer asked for help with constructing a set of relational tables to store a list of users with a category.
I suggested keeping it simple, with a single relational field and only two tables:
CREATE TABLE categories (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(100) DEFAULT '',
last_name varchar(100) DEFAULT '',
category_id int(11),
PRIMARY KEY (`id`),
CONSTRAINT `user_category_ibfk_1` FOREIGN KEY
(`category_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
With this table structure, you can do:
mysql> insert into categories (name) values ("one");
Query OK, 1 row affected (0.00 sec)
mysql> insert into categories (name) values ("two");
Query OK, 1 row affected (0.00 sec)
mysql> insert into categories (name) values ("three");
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (first_name, last_name, category_id)
values ('joe', 'blogs', 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (first_name, last_name, category_id)
values ('harry', 'potts', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (first_name, last_name, category_id)
values ('miner', 'niner', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (first_name, last_name, category_id)
values ('jeebus', 'saves', 3);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from users order by last_name desc;
+----+------------+-----------+-------------+
| id | first_name | last_name | category_id |
+----+------------+-----------+-------------+
| 4 | jeebus | saves | 3 |
| 2 | harry | potts | 2 |
| 3 | miner | niner | 2 |
| 1 | joe | blogs | 1 |
+----+------------+-----------+-------------+
4 rows in set (0.00 sec)
mysql> SELECT u.*, c.name from users u left join categories c on
u.category_id = c.id order by last_name desc;
+----+------------+-----------+-------------+-------+
| id | first_name | last_name | category_id | name |
+----+------------+-----------+-------------+-------+
| 4 | jeebus | saves | 3 | three |
| 2 | harry | potts | 2 | two |
| 3 | miner | niner | 2 | two |
| 1 | joe | blogs | 1 | one |
+----+------------+-----------+-------------+-------+
4 rows in set (0.00 sec)
Some people immediately commented that I was wrong in not telling her to plan for the future requirements and start with the user/category relationships as a separate mapping table. And often, I’d agree with that, but in this particular case, I think that’s a premature optimization that puts a lot more work up front, especially since she wasn’t even comfortable enough to figure out how to code the simpler relationship herself without help. There are gotchas when dealing with n:n relationship data (layout and prepopulation issues with the multiple select box, figuring out how to properly update relationships in the list, etc…), and it helps to have gotten to that point yourself. Using a mapping table and limiting it with a unique index on the mapping table will push off some of the UI issues, but I pointed out that making the db change later is the least difficult part of this migration, and it can be done with two sql statements that don’t affect any existing data:
mysql> create table user_categories (
-> id int auto_increment primary key,
-> category_id int,
-> user_id int
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into user_categories (category_id, user_id)
(select category_id, id from users);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
At the point at which you need to do this, it’s a trivial change shrouded in the other more complicated things you’ll be doing at the same time to support real n:n relations, and the code you need to use to interact with it in the meantime can be simpler. And, of course, if the requirement to have more than one category per user never materializes, you’ve saved yourself some work upfront.
Sometimes planning for the future is a good idea, and you should always keep it in mind, but that doesn’t mean it’s always the right way to design your data structures. Sometimes the value of the simpler shortsighted case outweighs its potential drawbacks.
Easy sharing of ical calendars on a local server
I’ve known for a while that iCal has the ability to share calendars using WebDAV, but I’ve never bothered to set it up locally. I looked into it recently, and it’s really easy to do, since the OSX Apache install comes with the dav module already installed.
These instructions assume some basic familarity with Apache configuration and the unix shell. I’d be happy to write up a more detailed guide if someone asks. This configuration took me about 10 minutes.
In /etc/apache2/http.conf, uncomment this line:
Include /private/etc/apache2/extra/httpd-dav.conf
In that file, change the paths for DavLockDB, the /uploads alias, the Directory tag, and the AuthUserFile to where you want those files to live, and give write permissions to the _www user on the directories you choose (I made them owned and writable by the _www group). Add users to the LimitExcept block, and add those users to the file you chose as the AuthUserFile with htdigest (as indicated in the comments, without the -c option after the first one).
Restart the web server in System Preferences > Sharing.
Select the calendar you want in iCal, and select Calendar > Publish. Enter the url for your webserver (it will be something like “http://thatserver.local/uploads”) and the authentication details. It should tell you that it was published successfully. If not, examine /var/log/apache2/error_log to see what went wrong. If it worked, go to another iCal and choose Subscribe, and enter the url to the new ics file (something like “http://thatserver.local/uploads/Adam.ics”), and set the options you want - it’s most useful if you have it auto-update.
That’s it!
Two very helpful links on understanding git
As it turns out, my problem with git was that I was using push instead of pull to propagate my changes, and I think it had nothing to do with the fact that I was mounting the directory improperly. I’ve switched over to ssh because it’s a bit more transparent to me in daily usage, but I don’t think that was the issue. The issue was that push is not the opposite of pull, as you might expect, and the crash course for git for svn users doesn’t really make that clear:
http://git.or.cz/course/svn.html
Of course, IRC is still the best place to find help with these kinds of problems, so I turned there.
In addition to helping me out with that specific problem, user doerner sent me these two very helpful links which explain some of the git internals:
http://www.newartisans.com/blog/2008/04/git-from-the-bottom-up.html
http://www.gnome.org/~federico/news-2008-11.html#pushing-and-pulling-with-git-1
