Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2009
    Posts
    8

    Question Unanswered: cross-table business validation check at END of transaction

    Hi,

    Is it possible in mysql to implement a cross-table Business Rule check that is applied whenever a record changes in any of tables involved, BUT only have the check occur just prior to the overall database COMMIT?

    That is, within the transaction there would be record changes going on that in themselves may cause the business rule to break, but by the time all is done and the commit comes along (in this case from Ruby on Rails) THEN apply the cross table check (e.g. sum to totals in Table X linked to Table Y should be equal, or something like that), and if it doesn't pass trigger a rollback & exception.

    Thanks in advance
    Greg

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with stored procedures and/or triggers, yes, it's likely possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    8
    any pointers re which is the stored proc/trigger function to use to get access to the "end-of-transaction" point? ie where to look in doco for this

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the end of the transaction would occur just prior to the COMMIT statement, right?

    that's the thing about stored procs, you just have a whole bunch of statements in a row, and at the end you do a COMMIT

    perhaps i misunderstood your question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    8
    So in summary my application would be issuing effectively the following things:
    - start transaction
    - change record A on Table X
    - change record B on Table Y
    - change record C on Table Y
    - commit

    So what I want to do is within the Database (e.g. stored procedure / trigger / whatever) is to be able to have some code that only triggers once all the record changes are in place, BUT before the final commit. That is, it'll only be at this point where the cross-table business rule would make sense to apply.

    So my question is within the code for a mysql StoredProc or Trigger (or whatever), what would be the Function or Syntax that you would use to hook into this point?

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    - start transaction
    - change record A on Table X
    - change record B on Table Y
    - change record C on Table Y
    - run the cross-table check
    - commit

    all of the above steps should be within a stored proc

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    That is the exactly the reason why deferred constraints were invented. I don't understand why MySQL doesn't support such an important thing.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat
    That is the exactly the reason why deferred constraints were invented. I don't understand why MySQL doesn't support such an important thing.
    Deferred Constraints are a great application level concept, but based on the mathematical model behind Referential Integrity a Deferred Constraint can't exist. The declaritive model only considers one operation (set of operators and operands) which SQL implements as a single statement at a time. Each operation needs to either succeed or fail on its own, without regard to other operations.

    Programming languages such as C++, Delphi, VB, and C# can legitimately address the issues of deferred constraints. Database engines can abandon Relational Algebra and include whatever they want (which MySQL had done repeatedly in the past). If a tool (such as MySQL) does that, the feature can be a great boon to the users of that tool, but that tool clearly can't support/respect/use Relational Algebra.

    As r937 points out, creating a stored procedure within the database is one way to bridge this gap, allowing the database engine to store and manage the code that implements the functionality that the developer wants.

    -PatP

  9. #9
    Join Date
    Jan 2009
    Posts
    8
    thanks guys, if I've understood correctly what this means for me:

    noting the code to change data in tables will reside in the application code (Ruby on Rails in this case)

    that

    there is no real way for my MySql database to provide a solid way to enforce my business logic rule in case of a mistake being made in the Ruby on Rails code.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan
    Deferred Constraints are a great application level concept, but based on the mathematical model behind Referential Integrity a Deferred Constraint can't exist. The declaritive model only considers one operation (set of operators and operands) which SQL implements as a single statement at a time. Each operation needs to either succeed or fail on its own, without regard to other operations.
    I beg to differ
    From my point of view, primary and foreign key constraints have to be consistent at the end of the transaction not (necessarily) at the end of the statement.

    To my understanding that's what the ACID principle implies as well (at least the "A" and "C" in there). A transaction (not a statement) takes the data from one consistent state into another.

    The problem with the stored procedure solution is that you can get invalid data into the database if you don't call the stored procedure.
    Data integrity should be the responsibility of the DBMS not the user.

    Btw: the existence of deferred constraints is part of the ANSI SQL standard (has been in there at least since SQL 92 as far as I can tell)

  11. #11
    Join Date
    Jan 2009
    Posts
    8
    Hi shammat,

    I'm not quite with you, but would love to find a way to have mysql do the double-check for my business rule at the end. I'm still not sure what to do at the mysql end to make this happen.

    Here is what roughly my Ruby on Rails application behavior does:
    [1] Application code roughly
    Book.transaction do
    @b.save! # FAILS HERE
    @c.save!
    @a1 = Allocation.create!(<book & chapter params here>)
    end
    [2] In Rails I have a "just after save, but before end of transaction, do the business logic check - HOWEVER this gets trigger on a per model (per table) basis, so at first line of code inside the transaction (see above) it fails.
    [3] I haven't found a way at application level to do a "just_before_commit"

    So I was hoping that what I could do is remove the application checks (as they aren't working as I need them to anyway), and then have mysql somehow do a "just_before_commit_but_after_statements" check.

    Do you know how I could achieve this? How would I do this in mysql?

    thanks

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by callagga
    So I was hoping that what I could do is remove the application checks (as they aren't working as I need them to anyway), and then have mysql somehow do a "just_before_commit_but_after_statements" check.

    Do you know how I could achieve this? How would I do this in mysql?
    I don't have any solution in MySQL for you.
    I think the best workaround is the stored procedure solution suggested by r937.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat
    The problem with the stored procedure solution is that you can get invalid data into the database if you don't call the stored procedure.
    Data integrity should be the responsibility of the DBMS not the user.

    Btw: the existence of deferred constraints is part of the ANSI SQL standard (has been in there at least since SQL 92 as far as I can tell)
    As long as the database allows a stored procedure to be invoked with the security credentials of its creator (or delegated credentials), then the procedure can act using privledges that aren't available to its caller. This allows the DBA to create a procedure with the ability to change data via INSERT, UPDATE, DELETE, etc when the caller may not have those privledges. This allows the stored procedure to do things on behalf of the application that the application is not permitted to do on its own.

    Deferred constraints violate the math behind relational algebra. To implement them would require a meta-operator (or an implicit chain of normal operators) that took operations (operator/operand sets) as the operands for the new operator. Nelson and Shirrazi published a paper thirty years ago which proved that all known approaches at that time for doing this failed mathematically. Has something been published since then that provides a mathematically acceptable solution for this problem?

    While vendors and even standards bodies can design and implement syntax for all kinds of features, they can rarely retrofit those changes into the underlying mathematical theory... You have to work from theory to practice for that to work well. I wish that deferred constraints could be relationally sound, and my developer side would probably use them in some cases even if they weren't relationally sound, but mathematically I can't see how they could become part of relational algebra.

    -PatP

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan
    ...This allows the stored procedure to do things on behalf of the application that the application is not permitted to do on its own.
    I completely agree with you, but that was not my point.
    The problem with the stored procedure solution is, that the procedure still needs to be called manually to ensure data integrity.
    So a user can (accidently or intended) store data that is not valid according to the business integrity constraints.

    Deferred constraints violate the math behind relational algebra. To implement them would require a meta-operator (or an implicit chain of normal operators) that took operations (operator/operand sets) as the operands for the new operator. Nelson and Shirrazi published a paper thirty years ago which proved that all known approaches at that time for doing this failed mathematically.
    I have to admit that my understanding of relational algebra is probably very - hmmm - minimalistic. And I have never bothered to dig deeper into that topic. So I can't really appreciate (or understand) the base of your criticism.

    From a "dumb" (non-mathematical) point of view I can only say that this is working fine in Oracle and Postgres since a long time. Oracle introduced this with version 8 in 1997 and Postgres with 6.4 in 1998.
    And I know for sure that Vax/RDB supported this as well when I was using it in the beginning of the 90's.

    I'm sure the developers behind those products would have removed that feature the meantime if it wasn't working

    I respect your mathematical background knowledge, but if mathematics "force" me to create a data model that allows invalid data, I'd rather ignore the mathematical background
    Last edited by shammat; 01-25-09 at 13:50.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm curious about your distinction between calling a stored procedure versus the actions of an application. Both a call to a stored procedure and an equivalent sequence of data changes can be accomplished cleanly and easily using any of the programming tools that I know well. The stored procedure actually takes less code and network I/O, and usually takes less execution time.

    What difference do you see? How do you see a stored procedure as a "manual" operation compared to having the application execute the same operations?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •