On Fri, Oct 22, 2004 at 03:27:28PM +0800, Rafael 'Dido' Sevilla wrote: > Anyhow, I finally managed to get PHP binaries that had Oracle support, > and then came the database conversion. With the Oracle Migration > Workbench moving the data from MySQL to Oracle was indeed quite trivial > and nearly painless, but there were a number of interesting and > unsettling differences between Oracle's and MySQL's implementation of > SQL.
Oracle mainly tries to follow the standard, while MySQL famously does not. Of course there will be differences. > First thing I noticed is that Oracle doesn't support the LIMIT clause > for some reason. This is de rigeur for web-based applications that > present paginated table views of data, and looking around revealed that > instead of the simple: > > select email,credit from email limit 0,30 "limit" is not part of the SQL92 standard. Useful, yes, standard, no. > idiom in MySQL, in Oracle you have to make a complex nested subquery > that kind of looks like this: > > select email,credit from (select row_number() over(order by email) > linenum, email, credit from email) where linenum between 0 and 30 > > Ewww... It almost seems as if Oracle's developers gratuitously left out > LIMIT just to convince people that subqueries are actually necessary! Um, first, subqueries are necessary. They're part of the standard for a reason. This isn't a very good use of them, though. If you need to limit in Oracle, you should probably use a cursor. Try doing this without subqueries: delete from table1 where key in (select key from table2) > It looks ugly and inefficient, and I imagine that for tables with many > more rows than the toy database I was using it would be very slow. > Those of you who have had more experience using Oracle than I have, > please show me a better way if there is one... This is a common misconception. If your data is being sorted in any way, the limit clause will do nothing but limit the number of rows sent back to the app. The entire query still has to run, so there's no difference. That's why handling it with a cursor is no different. But take your query above for consideration: select email,credit from email limit 0,30 There is no guarantee that you will get the same data each time you run this query, even if the table doesn't change. So you need to sort it. You're in the same boat. > Another thing that tripped me up while doing this conversion was the > auto incrementing indexes and the NOW() function that MySQL developers > take for granted. All of these had to be done on the client side, which > was particularly painful in the latter case. The more I read this, the more I'm brought back to a point that I used to make around here. Folks who defend MySQL almost invariably are folks who don't have a lot of real RDBMS experience, and so they see the world as how it compares to MySQL. MySQL is the exception, not the rule. There is *no* feature of MySQL that isn't done in a better way elsewhere. That said, Oracle has sequences, as does Postgres. It's like an autonumber field, only it's not a field. It exists as a separate entity. http://www.techonthenet.com/oracle/sequences.htm You can use sequences to number columns. You can do it automatically with a simple stored procedure or as a default value if you want. You're not limited to one per table. And you can use sequences outside of tables. Consider the mysql_insert_id() function in PHP. Instead of that, I just do a couple of queries: $db->query("select nextval('order_id_seq') as order_id"); if ($db->next_record()) { $order_id=$db->f('order_id'); } else { die("I can't get an order id, something is really wrong."); } $db->query("begin"); $db->query(sprintf("insert into orders (order_id,...) values (%d,....)", $order_id)); if ($db->affected_rows()) { foreach ($items as $item) { $db->query(sprintf("insert into order_items (order_id,....) varlues (%d,...)", $order_id)); if (!$db->affected_rows()) { $db->query("rollback"); die("Your order cannot be saved"); } } $db->query("commit"); } else { $db->query("rollback"); die("Your order cannot be saved"); } With minor tweaking of the sequence syntax, it'll work in Oracle as well. Think of how you'd do this in MySQL. Seriously. First, you have to always insert into the "orders" table first. I often do that insert last so that I can total up the rows while I'm writing them. One loop, makes my life easier. Here, it doesn't matter. If something goes wrong, I don't end up with Frankendata, such as there's an orders record but no order_items, or some of the order_items, etc. This is quite simple. When I used to program such in MySQL, I always had to put in a "remove order parts" routine to deal with the "rollback". But that might not be so easy as a few "delete" commands if data is updated in a table. > The experience of porting a simple LAMP application to Oracle nicely > illustrates the gap between these two worlds. There's a long way to go > before the two meet in the middle. *sigh* Actually, all your experience highlights is that you are seeing the DB world through MySQL glasses. Sit down for a day or two and learn Postgres. Write stored procedures. Write real queries with subqueries. Use sequences. Think about your transactions as transactions, not just a bunch of queries. There's no middle to meet in. You just need to drop MySQL and start using Postgres. There's really no excuse not to. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ -- Philippine Linux Users' Group (PLUG) Mailing List [EMAIL PROTECTED] (#PLUG @ irc.free.net.ph) Official Website: http://plug.linux.org.ph Searchable Archives: http://marc.free.net.ph . To leave, go to http://lists.q-linux.com/mailman/listinfo/plug . Are you a Linux newbie? To join the newbie list, go to http://lists.q-linux.com/mailman/listinfo/ph-linux-newbie
