Re^4: Conditional many to many relationships with Class::DBI

It isn't difficult to keep the versioning in the application level, and I believe that's the rigth thing to do.

There's right, in terms of architecture, and right, in terms of performance and maintainability. While data versioning, according to some architectural viewpoints, belongs in the application, it's still, imho, a data issue. Consider how CVS and other source control applications work - the versioning is done in the engine (database, in our case), not the client (application, in our case).

Furthermore, there is only one place that actually knows when data is changed - the database. Not all changes are guaranteed to occur through the application. In fact, most databases support more than one application, often written by different teams. By putting versioning in the common place, you gain a lot more certainty in how versioning will occur. "Write Once, Use Everywhere."

Plus, there's the issue of how do you pull the versioned data out. If you could put it in your SQL statement, you reduce your network and application-side processing overhead, potentially by up to 90% in a heavily-used system. Plus, the RDBMS is written in highly-optimized C. Your client, in this case, is in (un)optimized Perl. I know where I'd prefer the decisions to be made.

Additionally, this is a problem you want to have solved forever, no matter what you do. I hate having to solve the same problem over and over in an application when it should be solved in some service I'm using, whether it's in the database, Apache, mod_perl, or something else. And you should use a real, transactioned RDMS of course to avoid inconsistencies ;)

MySQL actually has stronger default transaction support than Oracle, Sybase, and DB2. What's your point?

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Comment on Re^4: Conditional many to many relationships with Class::DBI

Replies are listed 'Best First'.
Re^5: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 01, 2004 at 16:05 UTC
My point is that as long as MySQL ignores the SQL standard and changes my data behind my back it's only good for blogs and nothing else. And I disagree about your point on overhead and reducing database load by putting the "versioned data" on the SQL statement. As long as I'm specifying ... AND VERSION=xxx ... it's on the SQL statement, no need to have a new SQL dialect. Furthermore that's a case where the performance bottleneck is the database (for large databases), not the application or versioning code, so writing it in C won't make it noticeably faster. Of course you're right about only having to "solve the problem once", but that's easy. Write the version control code as stored procedures. In PostgreSQL and Oracle you can even do that in Perl. But, oh, you're using MySql, sorry. `ESC[78;89;13p ESC[110;121;13p`	[reply]
Re^6: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 16:29 UTC
My point is that as long as MySQL ignores the SQL standard and changes my data behind my back it's only good for blogs and nothing else. Please provide examples to support this statement. MySQL 4.x does not ignore the SQL standard - it does what every other vendor does and that is to augment the standard. Oracle is actually the biggest culprit in this. Postgres isn't far behind, frankly. As for changing data ... I have never heard of this. Unless, you're talking about the fact that MySQL silently converts data to the correct datatype. First off, there is nothing said about this in the standard, other than to say that it is implementation-dependent. (I've read all 1000 pages of the SQL-92 standard ... have you?) Second, Oracle and Sybase also both do something similar in certain cases. MySQL simply chose to do it in more cases than others. The places that it does this are all documented in an extremely easy-to-read manual (which Oracle doesn't have). Not to mention that Oracle's exceptions are NOT documented in an easy-to-search fashion. Having been bitten by them time and again, I much prefer MySQL's many documented exceptions over Oracle's few undocumented exceptions. Write the version control code as stored procedures. In PostgreSQL and Oracle you can even do that in Perl. But, oh, you're using MySql, sorry. You're absolutely correct - MySQL does not have triggers or stored procedures ... yet. The plan is to have enterprise-ready triggers by Q1/Q2 next year. Oh, and it will also have seamless clustering around the same time. You can actually use an alpha version of clustering today - I'm setting it up this afternoon. When is PG supposed to have that? deep breath We're starting to get into some hot conversation ... We can continue here or via email, if you prefer. I'd rather not start a flamewar on PM, if possible. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^7: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 02, 2004 at 15:36 UTC
I never meant to flame, sorry. When I mentioned MySQL changing data and ignoring the SQL standard I meant the kind of stuff pointed out here. I expected you to know abut that. For instance, if a column has a default value in Mysql, you can't insert a null value into it. If I attempt to do so Mysql will substitute the NULL for the column's default. And yes I know that's all fully documented, but it's still well documented wrong behaviour IMHO. Of course both Postgres and Oracle deviate from the SQL standard but Mysql does things that have no logical explanation. Another example: if a table has a timestamp column, that column (or the first timestamp if there's more than one) in each row is automatically updated every time the row is changed. How is that supposed to help me? How am I supposed to guess that that will happen when I create a timestamp I don't expect to be automatically updated? Why not name the datatype "autoupdate_timestamp" instead? And now Mysql is stuck with these wrong behaviours because they can't fix them without breaking compatibility with existing applications. PostgreSQL does have replication and clustering solutions. Several, actually. I know of at least two that are open-source and free, though I've never tried them: 1 2 `ESC[78;89;13p ESC[110;121;13p`	[reply]
Re^8: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 02, 2004 at 15:46 UTC
Re^5: Conditional many to many relationships with Class::DBI by mpeppler (Vicar) on Nov 02, 2004 at 07:28 UTC
MySQL actually has stronger default transaction support than Oracle, Sybase, and DB2. Could you expand on that a bit? Thanks! Michael	[reply]
Re^6: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 02, 2004 at 14:01 UTC
Sybase¹ operates by default on Isolation Level 1, which corresponds (roughly) to MySQL's 'READ COMMITTED'. MySQL², by default, operates on 'REPEATABLE READ', which corresponds (roughly) to Sybase's Isolation Level 2. Oracle³ operates by default on something similar to Sybase's default isolation level, as does Postgres⁴. Now, all four RDBMSes can be set to operate at the level that the SQL92 standard recommends, which is Sybase's Isolation Level 3, or MySQL's 'SERIALIZABLE'. However, only Sybase and MySQL offer the ability to operate at any of the four isolation levels. Oracle8i and Postgres only offer READ COMMITTED and SERIALIZABLE (levels 1 and 3 in Sybase). the Sybase manual the MySQL manual the Oracle manual the Postgres manual Note: I couldn't find the official Oracle manuals through Google. I figured something by O'Reilly would be good enough. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^7: Conditional many to many relationships with Class::DBI by demerphq (Chancellor) on Nov 02, 2004 at 14:54 UTC
Considering that MySQL only supports transactions on certain (new) table types dont you think that saying its default behaviour is more sophisiticated than the others is a bit misleading? Especially as Sybase handles all of the required levels as well as supporting much more sophisticated locking schemes than MySQL (afaik, MySQL doesnt support row level locking). So you have to admit you were a bit strong in that original claim no? :-p --- demerphq	[reply]
Re^8: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 02, 2004 at 15:26 UTC
Re^7: Conditional many to many relationships with Class::DBI by mpeppler (Vicar) on Nov 02, 2004 at 15:30 UTC
Thanks - I wasn't sure what you were hinting at. The higher the isolation level the more concurrency issues you're going to have - unless the system uses some form of multi-version journaling (Sybase doesn't so I tend to use the default isolation level). Michael	[reply]