I use parameterized queries in PHP, but I use PostgreSQL rather than MySQL. Isn't there a mysql version of pg_query_params?
I never could stand MySQL, its fine for really quick and dirty stuff but last time I used it it didn't even support foreign keys properly, let alone triggers and sprocs. Has this changed yet? I really haven't had the ambition to even find out with all the Sun-Oracle insanity going on.
I use parameterized queries in PHP, but I use PostgreSQL rather than MySQL. Isn't there a mysql version of pg_query_params?
I never could stand MySQL, its fine for really quick and dirty stuff but last time I used it it didn't even support foreign keys properly, let alone triggers and sprocs. Has this changed yet? I really haven't had the ambition to even find out with all the Sun-Oracle insanity going on.
there is a thing that I looked at, but I decided that it was unnecessary. After I get it live I may go back on the test server and clean it up. I am rapid prototyping it so as soon as the client is happy we stop. I just finished it a few minutes ago, and it's going live tomorrow. It's ahead of schedule since the deadline is friday so I'll do some reading on the mySQL implementation of parametrized queries and see if it's worth it. If it is I will play with it in the sandbox then make it live on friday.
I never could stand MySQL, its fine for really quick and dirty stuff but last time I used it it didn't even support foreign keys properly, let alone triggers and sprocs. Has this changed yet? I really haven't had the ambition to even find out with all the Sun-Oracle insanity going on.
MySQL gives you a choice of many database engines. If you want you can use InnoDB instead of MyISAM and it will do all those things like supporting foreign keys and transactions.
Personally I use MyISAM, and I like it. A blog is not a bank. It doesn't matter if foreign keys don't match up. It does't matter if it's not perfectly transactional. If you're doing something for serious, like a financial institution or something ultra secure and reliable for a hospital or government, then you better damn well use Postgres. If you are doing something that is for normal people, then I see it as very foolish not to use MySQL and the "crappy" MyISAM.
Two examples. I was working on turnflict the other day, and I had a problem. I had two database connections open. On connection one I did an update statement. I then did a select statement on connection one to verify. The update statement worked. On connection two I did the same select statement. The data wasn't updated. WTF? Apparently on Arch Linux they default to InnoDB. Because it was transactional, and I didn't commit the transaction, this weird behavior happened. I could have done the transaction stuff, but that would be more work for ZERO benefit. It was easier to switch to MyISAM, which is not only less work, but also has much better performance. It's crazy fast compared to Postgres or InnoDB because it just doesn't give a shit.
Second example, I have table X with a foreign key to table Y. I delete a row in table Y. There are some rows in table X that were referring to that row in table Y. Postgres or InnoDB start shitting themselves because now there is an integrity error. They want me to fix the FK on table X or delete all the rows in table X with the bad FK. You know what MyISAM does? Nothing. It just leaves the bad FK hanging around. That's exactly what I want! A bad FK like that isn't going to cause me any problems on an unimportant web application. Even if it's a big time web application like a Facebook or a Twitter, it doesn't matter for something like frontrowcrew.com
Trying to be all ACID compliant is a tradeoff. You create more work for the developer and you decrease performance. In exchange you get perfect data integrity. If you do not need perfect data integrity you should purposefully avoid Postgres or InnoDB. The overwhelming majority of applications in the world do not need such serious data integrity, if any at all.
That being said, if you are working on one of the few applications that does need data integrity. Serious applications that human lives, money, or other serious things depend upon, you better damn well be using Postgres and doing that extra work. It's just that blogs and social media sites are none of those things.
MySQL gives you a choice of many database engines. If you want you can use InnoDB instead of MyISAM and it will do all those things like supporting foreign keys and transactions.
Everything Scott said. We use InnoDB at work, but that's because we provide contingency communication services for emergency situations. Most people should never need something that restrictive. It's not really worth the trouble.
Given 95% of my work is in situations where money is at stake, I'll stick with PostgreSQL.
It really has to be some sort of rigid bit of my psychology that pushes me to do things like keeping ACID compliance in all my databases. Orphaned foreign keys make me twitch! I am more than understanding of blogs or some social site where every once in a while I'll stumble upon an entry that just doesn't exist. it's fine. It's certainly not the way I'd do it.
This is probably why I get twitchy with stuff like CouchDB. I know its a trade off for performance and flexibility, I can't tell you the number of times when I've sat here going "I wish, for just one second, I could ignore the schema here, not for long ... "
Maybe one day i'll get into lighter work and abandon the rigidness of my ways!
They want me to fix the FK on table X or delete all the rows in table X with the bad FK. You know what MyISAM does? Nothing. It just leaves the bad FK hanging around. That's exactly what I want!
won't a normal schema enforce referential integrity and kill everything? I'm not saying that it's the right thing, I'm just asking under most conditions won't referential integrity force a cascading update when you delete something?
won't a normal schema enforce referential integrity and kill everything? I'm not saying that it's the right thing, I'm just asking under most conditions won't referential integrity force a cascading update when you delete something?
This depends on many factors. In my experience with Postgres I usually just get Integrity Errors, but I think you can tell it to cascade delete or cascade set null. But cascade set null only works if the FK has null=True.
The point is that it's a pain in the ass, and it's not worth dealing with unless you have an application that needs it.
I do everything transactionally via postgres, and it's saved my bacon numerous times.
I can be absolutely sure, say, in a large set of linked updates, that if any part of it fails, the whole thing is rolled back and an exception is generated for me to handle gracefully without having to write anything special. ;^)
When I have control over the database end I will remember that. Unfortunately most of these clients hire a web developer to make a bare ass minimum product, and then realize they need a data base so they hire another developer to do that and hack some code together on their existing site to make it work. Then they want some new feature so they hire us to do that feature, however we aren't allowed to touch the database or the base site since that would cost them extra money. so in the end you get this frankenstein monster site that is just a bunch of hacks.
I can be absolutely sure, say, in a large set of linked updates, that if any part of it fails, the whole thing is rolled back and an exception is generated for me to handle gracefully without having to write anything special. ;^)
Yeah, that's pretty awesome when you're doing your kind of work. Now imagine someone trying to just write a blog post and there are integrity errors because they try to attach a video to the blog post that another user just deleted. It's a fucking blog, that shit doesn't matter, you want the blog post to save no matter what without disrupting the user. What's a failsafe exception and a rollback for you is a complete failure for me. Cascading deletes by default are even worse.
Hey, I deleted a blog post. Wow, now you've also deleted everything that had a foreign key pointing at that blog post! How awesome is that? Not awesome at all! It's a catastropic loss of data!
ACID only when needed.
When I have control over the database end I will remember that. Unfortunately most of these clients hire a web developer to make a bare ass minimum product, and then realize they need a data base so they hire another developer to do that and hack some code together on their existing site to make it work. Then they want some new feature so they hire us to do that feature, however we aren't allowed to touch the database or the base site since that would cost them extra money. so in the end you get this frankenstein monster site that is just a bunch of hacks.
I do not understand using this approach outside of incredibly large systems. For the vast majority of systems one person or team can and should build the entire thing from the bottom up. Having people who understand and control the entire stack ensures smoothness. Everything I have ever done I am in full control of the entire system, and I design everything with the entire system in mind.
Hey, I deleted a blog post. Wow, now you've also deleted everything that had a foreign key pointing at that blog post! How awesome is that?
That's not the default behavior. ;^) Even on a blog, I'd just write out my database update in a transaction. If it fails at any point, it rolls only that transaction back and throws an exception for me to than handle and re-attempt the transaction. No cascading deletes.
Hey, I deleted a blog post. Wow, now you've also deleted everything that had a foreign key pointing at that blog post! How awesome is that? Not awesome at all! It's a catastropic loss of data!
I usually set up systems like that (with important foreign key relations) to never delete. Instead, old things are flagged "inactive" or something like that, and still have a reference. Or, I allow null references (which actually works just fine in most cases).
Every web site I have ever made I have completely ignored all ACID compliance. Lack of data integrity has never once in all my years caused a single problem. On the other hand, when I was using Postgres, I had many problems because the application would return an error from unhandled exceptions from Postgres complaining. Same exact data in MySQL probably had tons of integrity problems, but because no exception was thrown the page loads and displays perfectly. Handling exceptions and rollbacks is extra work. It's only worth doing that extra work if you really need it.
A single transaction failing rolls only itself back and happily generates an easy-to-handle exception.
But I don't want it to roll back. I want it to execute the query despite it being bad.
Think of it this way. I create a web form for writing a blog post. A wild exception appears! Now I have to write a whole block of code to figure out to handle the fact that the user clicked the save button and the blog post wasn't saved. Do I display an error message and make them save again? Do I try to figure out what was wrong with the data so I can massage it and try again while the user waits for the next page to load? Or do I use MySQL and not have to answer these questions.
I want it to execute the query despite it being bad.
Even if it doesn't actually write anything to the database? It's so bad that nothing gets in there? The user is still wondering what the hell happened to his post: he didn't get any errors or anything.
Even if it doesn't actually write anything to the database? It's so bad that nothing gets in there? The user is still wondering what the hell happened to his post: he didn't get any errors or anything.
But it does write something to the database. That's what you don't get!
Let's say the table looks like this.
id, blog title, blog post, foreign key to video
Let's say the foreign key to video is bad. Postgres will abandon ship and roll back forcing you to fix the FK. MySQL with MyISAM will gladly perform the insert. Your blog title, post and even your bad FK value will be stored. The blog post will now appear on the site, just without any video. The horror!
I do not understand using this approach outside of incredibly large systems. For the vast majority of systems one person or team can and should build the entire thing from the bottom up. Having people who understand and control the entire stack ensures smoothness. Everything I have ever done I am in full control of the entire system, and I design everything with the entire system in mind.
I agree bro, it's bad, but you have small business trying to get shit on the cheap. They are short sighted, don't understand IT project management, and only want to pay the very least they possibly can and don't realize that they are making a mess of their stuff. I don't choose the projects, I'm a lowly unpaid intern I just find solutions for the issues that I am given.
on another note, PHP's inheritance and pseudo-polymorphism makes me angry so I'm not implementing it.
But infinitely better user interface would be to kick the user back to their submission with an error saying that the video doesn't exist, repopulating all of their input, and allowing them to either select an existing video or omit the video altogether. ;^)
But infinitely better user interface would be to kick the user back to their submission with an error saying that the video doesn't exist, repopulating all of their input, and allowing them to either select an existing video or omit the video altogether. ;^)
Risk of losing the data is too great. Store it while you got it.
Risk of losing the data is too great. Store it while you got it.
The application can store it. Of course, that adds complexity.
Or, have the exception handler just re-enter the query without the broken FK relation at all, rather than implicitly allowing it via the clearly malformed query. Then you can kick an error back to the user saying the submission succeeded, but the video link did not (and was removed).
Risk of losing the data is too great. Store it while you got it.
The application can store it. Of course, that adds complexity.
Or, have the exception handler just re-enter the query without the broken FK relation at all, rather than implicitly allowing it via the clearly malformed query. Then you can kick an error back to the user saying the submission succeeded, but the video link did not (and was removed).
shouldn't you hold it in the model until you determine the success or failure of the SQL and then drop it from the model upon success or repopulate the view from the model for the user incase of failure so the user can correct their issue?
couldn't you do validation in the model before even posting to the DB?
Or, have the exception handler just re-enter the query without the broken FK relation at all, rather than implicitly allowing it via the clearly malformed query. Then you can kick an error back to the user saying the submission succeeded, but the video link did not (and was removed).
Ah, but here in lies the problem. Quite often all you get back is an Integrity Error. How do you know the exact cause of the integrity error? Writing code that can gracefully handle all possible combinations of integrity errors is quite complex indeed. And you have to do that complex task for each and every single instance of updating or inserting.
With the unreliable database you still have the option of doing these graceful behaviors, but they aren't mandatory. You can get off the ground and have something that works with invalid data, and then slowly but surely build up to something better. If you use something ACID compliant you MUST do all that work, otherwise you will just have error and fail. You can't build up to it gradually. You either do the whole thing, or everything is completely broken.
Comments
I never could stand MySQL, its fine for really quick and dirty stuff but last time I used it it didn't even support foreign keys properly, let alone triggers and sprocs. Has this changed yet? I really haven't had the ambition to even find out with all the Sun-Oracle insanity going on.
Personally I use MyISAM, and I like it. A blog is not a bank. It doesn't matter if foreign keys don't match up. It does't matter if it's not perfectly transactional. If you're doing something for serious, like a financial institution or something ultra secure and reliable for a hospital or government, then you better damn well use Postgres. If you are doing something that is for normal people, then I see it as very foolish not to use MySQL and the "crappy" MyISAM.
Two examples. I was working on turnflict the other day, and I had a problem. I had two database connections open. On connection one I did an update statement. I then did a select statement on connection one to verify. The update statement worked. On connection two I did the same select statement. The data wasn't updated. WTF? Apparently on Arch Linux they default to InnoDB. Because it was transactional, and I didn't commit the transaction, this weird behavior happened. I could have done the transaction stuff, but that would be more work for ZERO benefit. It was easier to switch to MyISAM, which is not only less work, but also has much better performance. It's crazy fast compared to Postgres or InnoDB because it just doesn't give a shit.
Second example, I have table X with a foreign key to table Y. I delete a row in table Y. There are some rows in table X that were referring to that row in table Y. Postgres or InnoDB start shitting themselves because now there is an integrity error. They want me to fix the FK on table X or delete all the rows in table X with the bad FK. You know what MyISAM does? Nothing. It just leaves the bad FK hanging around. That's exactly what I want! A bad FK like that isn't going to cause me any problems on an unimportant web application. Even if it's a big time web application like a Facebook or a Twitter, it doesn't matter for something like frontrowcrew.com
Trying to be all ACID compliant is a tradeoff. You create more work for the developer and you decrease performance. In exchange you get perfect data integrity. If you do not need perfect data integrity you should purposefully avoid Postgres or InnoDB. The overwhelming majority of applications in the world do not need such serious data integrity, if any at all.
That being said, if you are working on one of the few applications that does need data integrity. Serious applications that human lives, money, or other serious things depend upon, you better damn well be using Postgres and doing that extra work. It's just that blogs and social media sites are none of those things.
It really has to be some sort of rigid bit of my psychology that pushes me to do things like keeping ACID compliance in all my databases. Orphaned foreign keys make me twitch! I am more than understanding of blogs or some social site where every once in a while I'll stumble upon an entry that just doesn't exist. it's fine. It's certainly not the way I'd do it.
This is probably why I get twitchy with stuff like CouchDB. I know its a trade off for performance and flexibility, I can't tell you the number of times when I've sat here going "I wish, for just one second, I could ignore the schema here, not for long ... "
Maybe one day i'll get into lighter work and abandon the rigidness of my ways!
The point is that it's a pain in the ass, and it's not worth dealing with unless you have an application that needs it.
I can be absolutely sure, say, in a large set of linked updates, that if any part of it fails, the whole thing is rolled back and an exception is generated for me to handle gracefully without having to write anything special. ;^)
Hey, I deleted a blog post. Wow, now you've also deleted everything that had a foreign key pointing at that blog post! How awesome is that? Not awesome at all! It's a catastropic loss of data!
ACID only when needed. I do not understand using this approach outside of incredibly large systems. For the vast majority of systems one person or team can and should build the entire thing from the bottom up. Having people who understand and control the entire stack ensures smoothness. Everything I have ever done I am in full control of the entire system, and I design everything with the entire system in mind.
BEGIN;
DO stuff WHERE stuff needs to be done;
COMMIT;
BEGIN;
DO stuff WHERE stuff needs to be done;
COMMIT;
etc...
A single transaction failing rolls only itself back and happily generates an easy-to-handle exception.
Think of it this way. I create a web form for writing a blog post. A wild exception appears! Now I have to write a whole block of code to figure out to handle the fact that the user clicked the save button and the blog post wasn't saved. Do I display an error message and make them save again? Do I try to figure out what was wrong with the data so I can massage it and try again while the user waits for the next page to load? Or do I use MySQL and not have to answer these questions.
Let's say the table looks like this.
id, blog title, blog post, foreign key to video
Let's say the foreign key to video is bad. Postgres will abandon ship and roll back forcing you to fix the FK. MySQL with MyISAM will gladly perform the insert. Your blog title, post and even your bad FK value will be stored. The blog post will now appear on the site, just without any video. The horror!
on another note,
PHP's inheritance and pseudo-polymorphism makes me angry so I'm not implementing it.
Or, have the exception handler just re-enter the query without the broken FK relation at all, rather than implicitly allowing it via the clearly malformed query. Then you can kick an error back to the user saying the submission succeeded, but the video link did not (and was removed).
couldn't you do validation in the model before even posting to the DB?
With the unreliable database you still have the option of doing these graceful behaviors, but they aren't mandatory. You can get off the ground and have something that works with invalid data, and then slowly but surely build up to something better. If you use something ACID compliant you MUST do all that work, otherwise you will just have error and fail. You can't build up to it gradually. You either do the whole thing, or everything is completely broken.