SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    SPs tested in sqlite but deployed on mysql, anyone done it?

    There is discussion in other places about the use of SPs - especially with regard to testing.

    Can anyone shed any light on the diffs between SPs in sqlite and SPs in mysql. I realise of course that the entire sql statement would need to be somewhat database agnostic.

    But still, from experience would you recommened the testing/developing of SPs on sqlite and deploying them onto mysql?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    I know nothing about sqlite, so I can't help you with that. But from my experience I can tell you that developing on a system that's different from the 'production' system can lead to all kinds of unpleasant surprises.
    If possible, you should try to develop in MySQL if that's what will be used when you go live.

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Sorry, I did not explain that very well.

    I meant in the event of using TDD methods where you unit test as and develop as you work.

    The code would eventually move to a local mysql database once the code was solidified.

    I only recently started using sqlite and was pretty amazed how portable it all is, from a unit test POV this is quite a nice thing, no worrying about user/password - no permissions table etc, and at the start/end of the unit tests, to clean up, you just delete the file.

    And there is a PDO driver for it too.

    So lets say you have had a first stab at a schema for discrete set of classes which depend on a database, then sqlite is very easy to use.

    I am absolutely new at SPs but if I were to slowly start to move some parts of the domain logic into the database (in the form of SPs) I just wondered to what degree I could be sure that they would transfer to mysql?

    If nobody knows then I will try and remember to report back, it was just me trying to pick brains from the sql side of the fence rather than the PHP side.

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    By SPs I think you mean Stored Procedures here. Not really familiar with them in either MySql or SqlLite. But that is besides the point.

    Anyhow, if you are looking to use TDD, you really shouldn't have any logic in the database. In fact, your unit tests shouldn't even touch a database. Because then it really becomes an integration test, not a unit test because of the external dependencies. Longer term--and I say this having written scary numbers of stored procedures--they become a nightmare. Biggest issue is you actually have two codebases to worry about which can easily diverge.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Thanks wwb_99 and yes, I did mean Stored Procedures.

    I realise that there is a POV which says you shouldn't have logic in the database, but I am unsure how hard and fast that rule applies to each situation.

    Perhaps I should admit I am ignorant of the downsides, I can probably list some reasons but I cannot look back bitterly at that Big Painful Lesson.

    Looking back at Big Painful Lessons seem to be my only compass sometimes.

    Quote Originally Posted by wwb_99
    Longer term--and I say this having written scary numbers of stored procedures--they become a nightmare. Biggest issue is you actually have two codebases to worry about which can easily diverge.
    Seemingly I have a lot to learn, my understanding of unit tests is slightly skewed and I have not yet been burned by keeping some logic in my database.

    That definition of logic in the database could apply to formatting dates I guess as well then?

    Code:
    select date_format( '%e %m %p', daydate) as booking_time ...
    I will do some more reading on unit testing, and look for a small project where I can move as much logic into the database as I can, just so I can feel the pain.

    "Experience is learning from your own mistakes. Intelligence is when you can learn from other peoples' mistakes."

    Perhaps you could elaborate upon some of the downsides of (ab)using SPs, in your experience?

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    one major problem you'll run in to with stored procedures if you're going for a database agnostic approach is you'll never be able to use them. none of the stored procedure syntaxes are compatible with each other.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Thank you.


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
  •