A few recent books:
"Foundation for Future Database Systems. The Third Manifesto"
by C.J. Date and Hugh Darwen.
An interesting and controversial book. In many cases completely
rejects some practical arguments for the sake of purity of the
model. A funny book too, as according to it, SQL is not a true
relational language, which is a good reference to use when
fingers are pointed at MySQL not being a relational database.
Also gave a valuable insight why Bug#5719 should
not be fixed. Despite this high appraisal, I don't think the book
matches its title.
"C++ Coding Standards" by Herb Sutter and Andrei Alexandrescu.
A must-have book for C++ programmers, 200 pages of best
programming style practices: basically, a summary of well-known
sources, …
I'd like to give you my thoughts about the ENUM data type, which
has strongly been discussed in various blog articles
recently.
Ronald
Bradford showed a nice way how to set up a reference table
that's combined with the original table via foreign key
constraints. I think, he's right that this is a more proper way
to restrict a field to a range of allowable values - read here:
http://blog.arabx.com.au/?p=87.
However, I still believe, if you only have up to maybe 5 or 6
values that very likely never change, the ENUM data type does a
great job. It's easy to handle and it does exactly what it's
supposed to do.
But even though more than 65,000 different values are allowed in
an ENUM field, I think it shouldn't be used for many different
values - in that case it's probably a better choice to set …
After trying to emulate check constraints with a trigger
yesterday, I have now tried out if it works to enforce data
integrity with views, as Arjen Lentz described it in this
article: http://arjen-lentz.livejournal.com/49881.html.
I created a little table and a view with a WHERE clause that
defined the conditions - and I added the WITH CHECK OPTION
clause:
mysql> CREATE TABLE checktest ([Read more]
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> val INT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW v_checktest AS
-> SELECT id, val FROM checktest
-> WHERE val >= 1 AND val <= 5
-> WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO v_checktest (val) VALUES (2);
Query OK, 1 row affected …
I'm not sure how I missed Danny O'Brien's presentation at Oscon last year "On Evil." At any rate, it's now available on the ITConversations podcast. The whole thing is about 15 minutes long and is a tongue in cheek look at good and evil in the open source world including mentions of all the hot topics: GPL licensing, Google, Maureen O'Gara, Ruby, Perl, Linus Torvalds and the ultimate evil: Software Patents.
For those who aren't yet into podcasts, consider giving ITConversations a listen. It's got some of the best content out there on a wide range of tech topics. Next to Engadget and Phedippidations (don't ask) it's one of my favorites. I'm hoping our Community guys will take up the mantle and develop a regular MySQL podcast in …
[Read more]I guess it was punishment for falling off the wagon and letting my inbox get so many messages which were not filed away. Yesterday the email server had a database crash and every single email in every single one of my inboxes was marked as unread. I use IMAP with server-side filters (currently sieve, used to be procmail) to sort my incoming mail into about 40 different mailboxes, so you can imagine how I suddenly feel a bit lost and unsure about which things I’ve already read.
In happier news, quickfile is another reason to love thunderbird. Now I just need to find a way to back up all my IMAP mailboxes into a MySQL database so that I can have better backups and analysis, and I’ll be happy(I’ve realized that I’m unconditionally happy).
We’re getting a bunch of new users signed up to the MySQL user group in …
[Read more]
Today, I got yet another interesting question from a friend who
has 30,000 rows with 10,000 unique values in a table and needs to
query the data in a random order. He asks:
If I change my column type to enum, Will it make any difference
in performance of my application?
"I don't think so," was my immediate reaction.
I know enum, though not standard in SQL, makes it efficient to
store data but I am not sure whether it would have any effect on
the query processing speed. Enum, I believe is limited to 65,536
possibilities.
On a side note, I recently noticed while playing around with Ruby
on Rails that enum fields were not supported. Need to make it a
part of standard SQL.
While looking for further clarification, I also found Ronald's
post titled "To enum or not to enum." He presents his three
reasons on why he doesn't …
Today, Mike opened my eyes as to why not having open comments can
be costly, especially if you seek an
answer.
So Mike, from now on you don't have to penalize me by not posting
a comment. I am pleased to announce that comments are now open to
anyone, not just the registered blogger.com users.
Big thanks to the following for their almost immediate
support.
That's amazing - almost like a chain reaction! Suddenly,
everybody at Planet MySQL is writing something on triggers.
Well, to all those that want to try it too - that is - have a
trigger perform an insert or update in anohter table here's a
little tip that might be of use.
Suppose our trigger table has got an auto_increment column, and
you'd want to be able to get your hands on the value of that
item, what should you do? The answer was a bit of a surprise to
me...
Suppose we have to tables, A and B and we want to have a trigger
on A that inserts into B:
So, we need to someway transfer the generated id value for A into
the corresponding field in B. Normally, you'd resort to the
LAST_INSERT_ID, but this does
tnot work inside a trigger.
Instead, use the new. pseudocolumn. That …
So I am listening to a talk by Rusty on talloc(). Its a memory
allocation system.
Sounds great.
My question is, why do people keep writing these things? Apache
has the same thing, its called APR. MySQL has mysys, GNOME has
one as well (which MySQL uses for its GUI tools), and I've seen
several others over the years.
Oh well... perhaps I will go write a string class, or yet another
CMS system.
Why isn't this a completely annoying talk? Because Rusty is a
great presenter :)
I'm just listening to a talk by Rusty Russell (an excellent
speaker anyway) about Tridge's talloc. It extends the traditional malloc()
mechanism by making every returned pointer effectively be a
memory pool. You can allocate memory that is "attached" to an
existing pointer. When a parent pointer is freed, so are its
children.
There are additional functions for stealing (re-attaching)
a pointer to a different parent, optional destructors, and other
useful trickery.
Tridge uses talloc in Samba4, and Rusty has also uses it in
various places. Good stuff.