I should be studying. I have a databases exam
on monday, but in stead, I am scripting again.
Like I said before, it’s the wrong time for
my inspiration to kick in, but it did. lol.
Since I’ve learned some SQL during class I’ve
never seen before, I decided to use it. As, for
what I am trying to do, I needed something like
I’ve learned in class.
Welcome to reality: It doesn’t work in MySQL.
What good is it to be tought something that
doesn’t work like that in the Real World™?
Okay, MySQL is not the only database software
out there, but it’s one of the major SQL implementations
out there, isn’t it?
I am talking about the EXCEPT operator
( http://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator )
which is not supported by MySQL
( http://bugs.mysql.com/bug.php?id=1309 )
The example on Wikipedia doesn’t exactly need
the except operator, could be written by just
changing the where clause.
The problem I am running into, makes it hard
to rewrite without the except operator.
As I am rewriting the site, the internal database
structure changed as well. I am trying to write
a script that imports data from the old database
to the new database.
The affected part is the rights management system.
The old site has only an access list for private photos.
This is stored per-photo. A simple table with three colums
(id, photo, user) where photo is the photo id and user is
the user id. Plain and simple.
The new rights management system will be more complex.
The idea is to create groups of friends, and assign one or
more of there friendsgroups as authorised to access the
item. (As it will not be limited to photos anymore in the
new design)
Now, the conversion. I look at each private photo, and
take it’s ID. I look this ID up in the table I described above,
and retrieve the user IDs of this photo.
Now, I want to check if there exsist a friend list
containing these people AND NOONE ELSE.
select `list` from `friendlist` where `friend`=’14’ or `friend`=’41’ except (select * from `friendlist` where not `friend`=’14’ and not `friend`=’41’)”;
This would be the query to do so, except for the fact
that MySQL doesn’t support EXCEPT.
So, after a bit of trying stuff, I got it working.
The final query turned out to be
select list from `friendlist` as f join `friendlists` as fs on f.list=fs.id where fs.owner=1 and friend = '41' and `list`not in (select list from `friendlist` as f join `friendlists` as fs on f.list=fs.id where fs.owner=1 and not `friend` = '41')
and it appears to do what I inteded it to do.