SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database and Business Logic

    I have some questions about the development of application logic using DB features like stored procedures and triggers. I think if I could integrate those better into the application and the development process I would make more use of these DB features.

    1. Tools: I have the impression there's a lack of tools. Are there IDE like tools (for MySQL or any other RDBMS) for developing and debugging SQL? I'm looking for syntax highlighting, syntax checks, script management, debugging ... Is MySQL workbench any good? (I tried it some time ago and it used to crash.)

    2. Test: Moving business logic into stored procedures means they must be tested. Do you test through you application (PHP) unit tests? Or do you use in-database tests?

    3. VC: During development of stored procedures, triggers, views etc. do you put them into your version control system?

    4. How do you determine what logic goes into SPs and what stays in the application's code? What kind of logic qualifies for SPs?

    5. Where do you document DB logic?

    Thanks for your input!

  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting questions. I have never used SP's much my self, so I too am curious as to what people do.

    Quote Originally Posted by FrlB View Post
    3. VC: During development of stored procedures, triggers, views etc. do you put them into your version control system?
    I'd reckon you deal with it exactly as with schema changes. I used to work on a rather large application where we used views quite a lot. Part of the deployment procedure was to re-load all views. One gotcha with MySql is that you need to load views anew after you have made schema changes, or strange things begin to happen.

    While at the subject of views and Mysql; If you define a view that provides a column which is the result of a function, then MySql won't be able to optimise queries on the view properly. The result can be massive table scans, which absolutely kills performance. So if you use views, you need to either avoid such views or only use it on tables with very few rows. The solution we adapted was to create a layer in PHP, that encapsulates the relevant queries. It basically consisted of a function per view, which would generate the query using the fully expanded SQL, rather than the view name. So the client code would look like:

    PHP Code:
    $stmt $db->prepare("select * from " db_dictionary::customers('customer_id = :customer_id')); 
    Instead of:
    PHP Code:
    $stmt $db->prepare("select * from customers where customer_id = :customer_id"); 
    Not sure what you'll do if you use an ORM. Then again, maybe views are redundant with an ORM?

  3. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another thing I'd like to ask:

    6. Regarding table references using foreign keys and the frequently used (?) ON UPDATE/DELETE constructs: Do you test explicitely or implicitely through PHP unit tests? Where in the application do you document you are using e.g. ON DELETE?

    Quote Originally Posted by kyberfabrikken View Post
    I'd reckon you deal with it exactly as with schema changes. I used to work on a rather large application where we used views quite a lot. Part of the deployment procedure was to re-load all views.
    So you basically version control scripts that are being called during deployment? Sounds sensible. But what do you do in case the application doesn't have complete deployments, but only patches and updates?

    Quote Originally Posted by kyberfabrikken View Post
    While at the subject of views and Mysql; [...]
    Following some people's advice I stay away from MySQL views in the core application, but sometimes they seem useful for admins browsing data in phpMyAdmin. Also I used them for reporting, but as you said: Non-trivial queries do degrade badly performance wise.

    Quote Originally Posted by kyberfabrikken View Post
    Not sure what you'll do if you use an ORM. Then again, maybe views are redundant with an ORM?
    Not using an ORM myself I'd think you could map your objects on views for reading, but updating would require mappings to the real tables anyways.

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by FrlB View Post
    So you basically version control scripts that are being called during deployment? Sounds sensible. But what do you do in case the application doesn't have complete deployments, but only patches and updates?
    If the deploy process is automated, I don't really think there is the need to separate full and patch deploy. Obviously that is something which differs from application to application.

    Quote Originally Posted by FrlB View Post
    Following some people's advice I stay away from MySQL views in the core application, but sometimes they seem useful for admins browsing data in phpMyAdmin. Also I used them for reporting, but as you said: Non-trivial queries do degrade badly performance wise.
    Yes, views are immensely useful - especially for access that comes from outside of the main application. Which is why it's a real pita that they aren't properly implemented in MySql. You can still use them for the cases where there are no calculated fields though.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what's this about no calculated fields? can someone give me an example?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what's this about no calculated fields? can someone give me an example?
    I think kyberfabrikken refers to something like
    Code:
    CREATE VIEW v AS SELECT 
    COMPLEX_MYSQL_FUNCTION(db.table.column0, db.table.column1)  AS A,
    COMPLEX_MYSQL_FUNCTION(db.table.column2) AS B
    FROM db.table;
    I also experienced performance problems with this kind of view and (not surprisingly) Views on Views - there's a chance my queries are badly written (I'm no fancy big city DBA) but also MySQL seems to do a bad job optimizing views.

    Maybe someone cares to share some more details on this?

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    norway
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by FrlB View Post
    4. How do you determine what logic goes into SPs and what stays in the application's code? What kind of logic qualifies for SPs?

    As of which logic to put in an sp I think of it as which logic is best to put in a SQL query. That is not to put any validation, dataintegrity check etc there. Only sorting, order by and similar simple operations.

    I think if you have a DAL layer with SQL code it could be wise to move that SQL to SP's. But I think one thing with SP is that dynamic sql would be harder to achieve, like:
    "SELECT * FROM x WHERE $dynamic ORDER BY $column $desc_or_asc LIMIT $start,$offset "
    So for a pragmatic reason I have stayed away from it since I think it would be to time consuming to solve it in a SP with my limited nkowledge of them.

  8. #8
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What kind of logic qualifies for SPs?
    Calculations, database operations with more steps, dependent queries ...
    E.g. CMS with versioning - if you need store new version of content item, you must move current version into archive tables (maybe with some delta compression of large strings) and replace it with new data. SP is fine here.

    Dependent queries - user login and user rights' fetching without SP (pseudocode):
    Code:
    mysql_query(select userid from users where name = ... and password = ...)
    if mysql_num_rows
       mysql_query(select module, permission from ... where userid = ....)
    But you can use SP returning resultset with permissions (valid user) or empty resultset (invalid user):
    Code:
    mysql_query(CALL user_login('name', 'password'))
    if mysql_num_rows
      // valid user, processing result
    else
      // invalid login
    And you save one query. Is it worth the effort?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i can save you one query outside the stored proc, too
    Code:
    SELECT module
         , permission 
      FROM ... 
     WHERE userid = 
           ( SELECT userid 
               FROM users 
              WHERE name = ... 
                AND password = ...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you're right, but SP can also do some logging, updating statistics ...

  11. #11
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mastodont View Post
    Yes, you're right, but SP can also do some logging, updating statistics ...
    Quote Originally Posted by Mastodont View Post
    Calculations, database operations with more steps, dependent queries ...
    E.g. CMS with versioning - if you need store new version of content item, you must move current version into archive tables (maybe with some delta compression of large strings) and replace it with new data. SP is fine here.
    In combination with triggers this seems to be the best use. In one application I was using an ON DELETE trigger to create an archive of old items (which would later be used for reporting) - at that time I thought it would be best not to complicate the application with this, because the archive (and teh reporting) is not at the core of that application's business logic.

    Regarding r937's and Mastodont's example:
    7. Would you use SPs to avoid joins in a highly normalized DB? Would you use SPs to simplify complicated queries?

    8. Does anyone use SPs for complex computing can not be done in a standard query?

    Quote Originally Posted by Mastodont View Post
    And you save one query. Is it worth the effort?
    Yeah, that's what it all comes down to:
    Is it worth the effort?
    Is it worth the disadvantages?

    9. What general disadvantages do you see using triggers and SPs?

  12. #12
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would you use SPs to avoid joins in a highly normalized DB?
    If you dont like too many joins, you have to AFAIK denormalize tables... this is not SP-dependant.

    Is it worth the effort?
    As the case may be. I wrote it as a question, because it depends on many aspects ...

    What general disadvantages do you see using triggers and SPs?
    Worse portability. "Database abstraction" is common buzzword now, that's why we recruited to move all logic into application.

  13. #13
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Disclaimer: I am by no means a database expert, but I do work with them on regular basis (doesn't everyone?).

    Quote Originally Posted by Mastodont View Post
    Would you use SPs to avoid joins in a highly normalized DB?
    If you dont like too many joins, you have to AFAIK denormalize tables... this is not SP-dependant.
    Or simply use a view, that's what they're there for. By using a view you can "abstract" the underlying database structure, no matter how "normalised" it is. Denormalising your database solely based on the fact that "you don't like joins" is a bit odd in my book.

    Quote Originally Posted by Mastodont View Post
    Is it worth the effort?
    As the case may be. I wrote it as a question, because it depends on many aspects ...
    I'm unsure if it is really going to improve the performance of your applications. When an application is programmed well, I guess you can do almost everything you want with "simple" queries instead of stored procedures so then the only difference would be the amount of data you send from the app to the database. I don't think the bandwith between the webserver and the database server would be a bottleneck these days, so that benefit is probably negligible.

    It does seem to make for cleaner code though: CALL yourprocedure( 'foo', 'bar' ) is easier to read than a 20 line query, especially if you name your SP after what it is supposed to do. There is another added benefit using SP's: you encapsulate database access, which means that you can change the *body* of your SP without changing the client code. When changing the parameters or the name of the SP however, you'll still have to update the client code, as the signature has changed and the client code should use the new signature.

    Also, by using only SP's and views from within your application, you can change the underlying structure of the database, update the views/SP's and you're done with. As a plus, you can deny access to the database's tables for everyone but the maintainer and you'd be "safer" against hackers. I wrote "safer" on purpose: you'd still have to think about security, it doesn't come automatically, just like with plain ol' handwritten queries.

    Quote Originally Posted by Mastodont View Post
    What general disadvantages do you see using triggers and SPs?
    Worse portability. "Database abstraction" is common buzzword now, that's why we recruited to move all logic into application.
    I somewhat disagree. It all depends in what you wish to have "portable". I can see the point in saying that bussiness logic should be in the application: in the unlikely event you'd have to switch to another database, you don't have to worry about the bussiness logic being lost, as that is all in the application.

    Then again, if you want multiple clients connecting to the same database, you'd want to put the bussiness logic in the database as much as you possibly can, as putting it in the client code would mean you're duplicating work and I'm sure every programmer likes to do as little work as possible (I do). Keeping it in the database means less work. It's a decision you'll have to make based on your assumptions on the future usage of the database.

    I'm actually using SP's, triggers and views for my latest and greatest project. I'm still not convinced about benefits, although I have concluded that it makes my code easier to read, which is a huge benefit from my point of view, that it "feels" a bit quicker (haven't done any benchmarking), and that it makes it easier to change the underlying database structure, without affecting or breaking the client code. Affecting the client code will probably happen once in a while though, but that's just the result of working with a database.
    Yes, I blog, too.

  14. #14
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webaddictz View Post
    It does seem to make for cleaner code though: CALL yourprocedure( 'foo', 'bar' ) is easier to read than a 20 line query, especially if you name your SP after what it is supposed to do.
    I don't see a real benefit here: while call procedure(..) is sure nice to read the SP probably isn't. You're just shifting the dirty stuff into the SP.

    Quote Originally Posted by webaddictz View Post
    Also, by using only SP's and views from within your application, you can change the underlying structure of the database, update the views/SP's and you're done with.
    [...] and that it makes it easier to change the underlying database structure, without affecting or breaking the client code. Affecting the client code will probably happen once in a while though, but that's just the result of working with a database.
    Good Point. Has anyone else used views/SPs in such situations, e.g. during Refactoring?

    Quote Originally Posted by webaddictz View Post
    I'm actually using SP's, triggers and views for my latest and greatest project.
    Can you classify what kind of logic goes in your SPs and triggers? Or have you generally rewritten all queries to SPs for the reasons you mentioned?

    Thanks for all replies so far!

  15. #15
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by FrlB View Post
    I don't see a real benefit here: while call procedure(..) is sure nice to read the SP probably isn't. You're just shifting the dirty stuff into the SP.
    Yes and no. You're right in that I just move the query to the database instead of the application, but keep in mind that the database can communicate with itself much easier than an application can: an application tends to do a few queries and use the result in a different query, which the database can do in a single call.

    Also, when putting it into a stored procedure, you don't have the query, prepare, bind and execute stuff: you just execute, which makes the code easier to read from my point of view. You're right though: it doesn't make a lot of difference, so if you say this is a non-argument, that's fine by me. I think it's an improvement of readability, you don't have to.

    Quote Originally Posted by FrlB View Post
    Good Point. Has anyone else used views/SPs in such situations, e.g. during Refactoring?
    There are books written about it. Don't take my word for it

    Quote Originally Posted by FrlB View Post
    Can you classify what kind of logic goes in your SPs and triggers? Or have you generally rewritten all queries to SPs for the reasons you mentioned?
    Well, I haven't given up on using queries, but I tend to query a view nowadays, not a table. As my database becomes increasingly normalised, it's just easier having a view instead of copy/pasting a query. For most operations that change data, I use a stored procedure, although I'm still not completely converted: I sometimes still use ordinary insert or update statements.

    It's like I said: I'm just trying it out, see how it fits. You asked us about the pro's and con's and these are the pro's and con's I've found thusfar.
    Yes, I blog, too.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webaddictz View Post
    the database can communicate with itself much easier than an application can
    nicely put
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rather than allow someone insert,update,delete privileges on tables, far more secure to just allow them to execute a set of stored procedures. Views can be used to hide away columns people shouldn't need to see, revoke the select privilege on the base table, and things like passwords or cc numbers, even if encrypted, can be left out of the view.

    Also things like managing nested sets, where all the left/right/update maintenance makes sense as stored procedures, as its not business logic, but a technique purely for hierarchical structures with RDBMs.

  18. #18
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webaddictz View Post
    Also, when putting it into a stored procedure, you don't have the query, prepare, bind and execute stuff: you just execute, which makes the code easier to read from my point of view. You're right though: it doesn't make a lot of difference, so if you say this is a non-argument, that's fine by me. I think it's an improvement of readability, you don't have to.
    I'm not sure about that at the moment. That's why I'm asking. Thanks for sharing these thoughts.

    Quote Originally Posted by webaddictz View Post
    Well, I haven't given up on using queries, but I tend to query a view nowadays, not a table. As my database becomes increasingly normalised, it's just easier having a view instead of copy/pasting a query. For most operations that change data, I use a stored procedure, although I'm still not completely converted: I sometimes still use ordinary insert or update statements.
    Ok, now Views start making sense. I've always wondered why use Views for SELECTs when I can't use it to change data. Calling an SP here makes sense. I'll have to try that in praxi.

  19. #19
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just fell over this post at a competing site, which shall not be named:

    http://stackoverflow.com/questions/1...cs-versus-code


Bookmarks

Posting Permissions

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