SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Consistent SQL?

  1. #1
    SitePoint Enthusiast C.Smith WebSites's Avatar
    Join Date
    Oct 2005
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Consistent SQL?

    Hi, I'm going to make a database abstraction layer in php so that I can make my applications much more portable. I'm wondering how to make it and I have some questions that if anybody knows the answer to i'd be very great full if you could share it with me/everyone.
    1. Is SQL Consistent with all database systems, for example will a query used on one database system do the same thing on another database system?
    2. What is the actual difference between database systems? Mysql and PostgreSQL for example.
    3. Any important things to remember when making a database abstraction layer?
    Thanks, Chris Smith

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    1. not even close

    2. plenty

    3. yes -- save yourself some grief and don't


    let the flames begin
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    1. not even close

    2. plenty

    3. yes -- save yourself some grief and don't


    let the flames begin
    i agree with 1 and 2...

    but for #3, it will be a HUGE pain in the a$$... but it will have to be done anyway if he wants to make portable software, or you'll end up coding 3 entirely different versions.

    For example, Invision board comes with 3 drivers- mysql, mssql, and postgre - the latter of the 2 are optional for extra $$.

    during set up, you chose the driver type.


    so, your choice really.
    code to include the proper driver based on set up.
    or
    code 3 different versions
    or
    give up while you're ahead, and make a mysql version that 99.342131% of people on the web will use anyway.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yup. i would do that too -- write a separate module for each database system for each business process

    a business process might involve checking this table for dupes, inserting to that table, getting the auto_increment (or not, for those databases that use sequences and not auto_increments), then inserting several child records with the right FK to another table, etc.

    so you modularize on business process, and call them via passed parameters

    you have to write each business process multiple times, once for each database system

    that's not quite the same thing as "database abstraction" as most people think of the term
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast C.Smith WebSites's Avatar
    Join Date
    Oct 2005
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by briansol
    give up while you're ahead, and make a mysql version that 99.342131% of people on the web will use anyway.
    Is very tempting

    Maybe ill just stick to the two most popular databases used with php. What do you think they are mysql and postgreSql?

    Chris

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oracle and db2

    microsoft sql server and firebird

    informix and ...

    oh, never mind
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it depends what language you are coding it in.

    php/cfm: mysql, postgre
    jsp/asp/net: mssql/oracle

    if you're coding it in anything else, you're probably wasting your time. (not too many servers offer anything else... sure, ruby crap, but whatever... i don't see it ever getting huge)


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
  •