in reply to Re^4: Conditional many to many relationships with Class::DBI
in thread Conditional many to many relationships with Class::DBI

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

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

Replies are listed 'Best First'.
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]
      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]
        Yes, there are many gotchas in MySQL. There are also many gotchas in Oracle and Postgres. I have two comments on them:
        1. MySQL's gotchas are well-documented. Oracle's aren't. This means that I can learn all the gotchas without losing two days on a production box. I don't know about you, but I never assume that application X will behave just like application Y - I read the manual cover-to-cover at least twice. Every time.
        2. Nearly every single gotcha was the result of a request from a user. And, it's because that behavior was unavailable in the existing systems.

        Now, I would love for a setting that would allow me to force the server to error out in many of those gotchas, such as inserting NULL into a NOT NULL column. But, it's like Perl - why must everyone conform to your ideas of correct behavior? Maybe, I like the incorrect behavior. :-)

        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]

In Section Seekers of Perl Wisdom