I need to rant, I just wasted a couple of hours on stupid crap. I am doing this project for a client and I am using MySQL. I chose InnoDB tables because they support transactions. Along the way I added a table to the system but forgot to specify InnoDB so it used the default (MyISAM). Little did I know that when I ran a bunch of stuff assuming it would never go into the db because I never committed the transaction. MySQL is so brain-dead that it can't even error out when you go into a transaction and do some queries on some tables that don't support transactions.

Next, I'm inserting a bunch of keywords into a table. The column type is VARCHAR(32) or something. I inserted "LPNs" and "lpns" and then later searched for "lpns" and it returns TWO ROWS:

select * from keyword where keyword='lpns';



That is straight moronic. I don't understand why in hell it would store the cases and display the cases but when you do a WHERE with an equals sign it thinks they are the same.

Anyway, it's been a really long time since I've used MySQL and I figured it was better now but clearly I was wrong. Yeah it supports transactions now (but they're busted) and this case sensitivity/case insensitivity cluster**** is just a joke.

Hahaha

SQL Server is like this too. Query on a varchar and it is case insensitive.


Will return all case permutations thereof.

To defeat this monsterous foe you do this

You'll only get "somevalue." Because I hate typing the same thing over and over again...

Now that column is case sensitive.

I'm not much of a database person but the hierarchical data types in SQL Server 2008 look

I don't know anything about SQL Server but any type of native handling of hierarchal data would be really nice.

<3 Oracle

Now, to duly await my company's conversion to CBO... :L

Index ranged skip scan, ahoy!

case insensitivity can be annoying, but it helps more than it hinders in my experience. The obvious one is checking for uniqueness of email address. When you consider that MySQL is most commonly used in web apps, the justification becomes obvious.

That said, i'm not a particular fan of MySQL anyway.

Not to be the Oracle fanboy, but...

http://www.oracle-base.com/articles/8i/FunctionBasedIndexes.php

Yes we all know that Oracle has lots of cool stuff, it's also NOT FREE. For the vast majority of web development uses, there is absolutely no use case that justifies the expense of Oracle.

Case insensitivity is also preferable for many non-English languages.

If you are going with a free one, PostgreSQL is the way to go.

If you want to enforce uniqueness can't you put a unique constraint on the column and specify that you won't it case insensitive?

Anyway, yeah, I generally do prefer Postgres and I have a lot of experience with Oracle. If I had it to do over again I would have definitely stuck with Postgres. The problem is that the people I'm working for are more familiar with MySQL and I didn't feel comfortable pushing Pg for this project -- it's relatively small and I didn't see a big benefit to choosing Pg (now I do -- save my sanity).

I'm still shaking my head about the transaction stuff.

What I like about SQL Server 2008 are the geographic types and spatial data.

Postgres and MySQL both have support for geographic and spatial data (GeoDjango uses this extensively)

I'd rather write my own database with its own API, than learn mysql. Then again most of my needs don't require anything very big so I can't give you an honest opinion.

:carl::carl::carl::carl::carl::carl::psyduck:

Not a surprise, if you were a caveman you'd reinvent the wheel, except it'd be square.

Why I am bothering to reply to you, I do not know. However, you can these alternatives such as the following instead of reinventing the wheel:

• SQLite
• Berkeley DB
• Firebird

You might be on to something there.

You're a proponent of Derby, aren't you?

Apparently Oracle treats empty strings and NULL as the same, this doesn't seem like the right behaviour

How so? When searching for IS NULL I don't get empty strings. Speaking of this, I think it's totally moronic in MySQL how if you have a string column specified as NOT NULL and you try to insert a NULL it happily changes it to an empty string and inserts it, it makes it really hard to enforce data integrity when your db changes your input data to suit itself rather than error out like it's supposed to. It does this for datetimes and other columns, too, translating NULL into 0000-00-00 00:00:00 stupidly.

Apparently there is a strict mode SQL for MySQL nowadays that works better, but I don't think the version I've been using supports it. (yeah, just checked, not added till 5.02 or something, and we're still on 4.x, argh).

to be honest, all sql is crap since it's not a fully relational language

WTF are you serious? That's...horrible.

it does throw a warning when it does it

Yeah but still, if you specify a column as NOT NULL and you're inserting a NULL value, it should say "NO NO NO NED VALUE." This just encourages more bad practices and PHP is filled with them.

How do you mean it throws a warning? Like to an error log or something?

Meh, it's easy for me to get my job(s) done. I've only run into a handful of situations where I couldn't get what I wanted and there are always workarounds.

For purely relational data, relational databases are great. If you have any kind of hierarchical or even slightly complex relationships, object databases are far more intuitive.