was there a particular bit of functionality that you'd like to see that postgres currently supports, or was it just a general preference?
Ok, Google Cloud SQL service, exciting! Must port my project from PostgreSQL to MySQL so I can use it! ...
Wait, what do you mean it wants backticks instead of apostrophes for escaping just certain things??
*OK, whatever, fix all the table name escaping*.
CREATE TABLE mytable (
mycolumn type NOT NULL REFERENCES othertable(othercolumn) ON DELETE CASCADE
);
Why aren't any of the foreign keys I defined listed?
Wait, what do you mean I have to index all the columns first??
Eww...
CREATE TABLE mytable (
mycolumn type NOT NULL,
INDEX (mycolumn),
FOREIGN KEY (mycolumn) REFERENCES othertable(othercolumn) ON DELETE CASCADE
);
So, now, every foreign key column needs 3 lines instead of 1? Ugly!
It's a good thing I don't have gargantuan datasets, given all the indexes all my foreign keys are gonna require!
Wait, what do you mean I can't have DEFERRABLE foreign keys??
But the "access control" table for my stuff references (ON DELETE RESTRICT) the "user" table to specify who can use the stuff, so when you delete a user, that will eventually cascade it's way down the content tree to delete all their stuff and the access controls giving them permission to see it, but without the access control restriction being deferrable, isn't it gonna freak out before it even gets that far? Ehh, does that mean I have to write a bunch of ugly trigger code to manually delete all a user's stuff before the user record? Eww!
Wait, what do you mean cascaded foreign key actions don't activate triggers??
But doesn't that mean that, err, how the heck am I supposed to...is this even ACID compliant?!?
Ok, whatever, I can figure out how to (somehow) delete all the stuff later, let's just concentrate on getting it in there properly first! ...
Wait, what do you mean the CHECK clause is parsed but ignored by all storage engines??
How the heck do I validate that one of either the binary_value (clob) or the text_value (varchar) columns were sent?? What about checks validating numeric ranges??
AHH! OK, maybe I just need to relax regarding the data integrity for now. Gah, I can't believe I just said that when talking about my database! I guess it's the "MySQL way"?
Ok, so, my basic tables are there, let's port one of the INSERT triggers that does the calculation of default column values when not specified!
Wait, shouldn't my trigger SET a default value, why is it still freaking out that there is none specified?? Who knows what's going on there, I will ignore that for now...
Wait, what do you mean I can only have *one* trigger for a given event on a table??
So, the triggers that serve totally different logical purposes need to have all their code combined and mixed into *one*? AHH!
Wait, what do you mean I can't create a single trigger for both INSERT and UPDATE??
Please tell me I don't have to duplicate all that calculation code into two triggers?!?
Oh, I can call a shared procedure? That's cool.
Wait, what do you mean there is no %ROWTYPE??
But how do I pass the 'NEW' record from a trigger into a shared procedure so that it can SET calculated values on the NEW row then??
I can't!?! AHH! Gah, OK, this isn't a deal breaker, I just have to copy/paste *all* the code for *all* the triggers, and as long as I remember to duplicate any fixes, maybe it won't be that bad?
Wait, but, without %ROWTYPE, how are the cursors gonna work??
I have to DECLARE a variable for *every* column I need?!?! AHH! That's a mountain of declarations! How the hell am I supposed to keep all the data types straight??
Wait, what do you mean I can't UPDATE WHERE CURRENT OF mycursor??
I have to manually restrict the update? But even just this one primary key is a union of three columns, which means that, because there is no %ROWTYPE, every time I want to update I need to DECLARE yet three more variables, on top of the gazillion I already added for the FETCH?!?
Wait, what do you mean if the SELECT inside the cursor LOOP returns null it causes the loop to abort??
And what the heck is a HANDLER? My declared handler is in scope even inside CALL'd procedures too?? But I have tons of select statements spread through a hundred functions and procedures... and how does this work when I have nested cursor loops four deep? How can I possibly manage... AHH!
Ok, so, my whole database is essentially a 6 level deep tree structure, with a table corresponding to each depth containing child nodes with pointers to their parent rows in the table "above". Each table has a column with a floating point number (0 < n < 1) specifying the child row's order with respect to it's peers under their parent node. Each table calls a trigger after new child rows are inserted, which updates all peer nodes in that table to redistribute their ordering numbers evenly under their parent.
Wait, what do you mean I can't update a table from within it's trigger because it's already being used by the statement which invoked that trigger??
I GIVE UP!! :(
Does that answer the question? ;)
--
Chris Hubick
mailto:chris@hubick.com
http://chris.hubick.com/
No comments:
Post a Comment