Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2008
    Location
    Somerset, England
    Posts
    93
    Thanks
    0
    Thanked 10 Times in 10 Posts

    Database Abstraction

    Okay so I am writing an OOP database abstraction layer that can handle any type of database. (Don't get me started on why I'm not using one of the many perfectly good ones available).

    So far I've basically got a Db class which has a single factory method for returning the correct type of adapter.

    I'm now about to write the Abstract Adapter class and my question is this. From a users point of view which is best:

    a) Seperate functions for different types of db query each returning something based on the type of query e.g.
    PHP Code:
    <?php
    $db
    ->insert_row($sql); //returns last insert id.
    $db->update_row($sql); //returns affected rows.
    ?>
    b) One overall query function that stores results in member varaibles e.g.
    PHP Code:
    <?php
    $db
    ->query(/* insert statement */); sets $this->insert_id
    $db
    ->query(/* update statement */); sets $this->affected_rows
    ?>
    EDIT: And another question should making strings safe for use in an sql query be the job of the coder using the API or the API itself?
    Last edited by RMcLeod; 05-16-2008 at 03:49 PM.

  • #2
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    If I was using it, I'd prefer option (b).

  • #3
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    I think method A is a cleaner abstraction (though I would name them insertQuery and updateQuery, or executeInsert and executeUpdate).

    Though remember that not all databases have an automatically incrementing row (such as Oracle) so you want be able to provide the same service. Which might be confusing.

    The problem with B is that you cannot predict the output of the member variables. (You'd have to parse the $query, which is not a clean abstraction)

    Let the user tell you what they are doing, it is much cleaner.

  • #4
    Regular Coder
    Join Date
    Apr 2008
    Location
    New Jersey
    Posts
    116
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I would prefer that the main database object have a generic query function, with objects representing individual rows having specific-purpose methods for saving (ideally encompassing insert and update as appropriate), deleting, whatever.

    The API should make strings safe if the way you interact with it is suitable for that. If I'm doing:

    Code:
    $row = $table->find(27);
    $row->name = 'Bob';
    $row->save();
    then the string 'Bob' should be sanitized without me having to worry about it. If I'm bypassing all of that kind of functionality by rolling my own SQL string and sending it to the database's generic-query function, then I'm responsible for quoting at that point.

    Just to randomly engage in some holy war, I greatly disagree about the insertQuery(), etc. That case pattern convention is ever so fashionable because of Java, but it's obnoxious, for this reason:

    Code:
    class foo {
        var $someVar;
        function getSomeVar() {
            return $someVar;
        }
    }
    The case pattern of the attribute should not be changing around because of a fashion statement. SomeVar, some_var, SOME_VAR, whatever, take your pick, but use something that stays consistent.
    Chaos
    Lost Souls: text based RPG | MUDseek: MUD gaming search | MUDfind: MUD resource sites | Discordian Quotes

  • #5
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Since my main development is done in Java, I end up using what I'm used to. To make it worse, I actually do it like this:

    PHP Code:
    class Something {
        private 
    $_name;
        public function 
    getName() { return _name; }



    SomeVar, some_var, SOME_VAR, whatever, take your pick, but use something that stays consistent.
    I agree with about 80% of that. It is true that staying consistent matters. But it does not mean that I can't do whatever I feel is right. As long as I apply my naming convention consistently, it is no problem.

    And the changing case is not a problem in my opinion. It doesn't increase my rate of errors, or decrease readability. Which is what conventions are all about.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yes, its a nice day.

    From a developer point of view: your option b is most suitable. We should know how to write queries.
    But, from a user friendly point of view, I prefer to break down no matter how painful it is. I'm so brutal on myself that I don't even allow my methods to use Result sets as a return >.<. Just in case its from a file system (which is another huge advantage)
    One huge advantage to this method is you can control how the parameters come in. This can make things like prepared statements easier, and my favorite - table prefixes can be automagically appended

    Oh, lemme show you what I mean from brutal, this is from a java abstraction layer I created awhile back:
    Code:
    package interfaces;
    
    import java.util.Map;
    
    import exceptions.MyDBException;
    
    /**
     * Contract a Database or storage object to implement these methods.
     * Figured that ResultSet may not always be used, so we are forcing
     * HashMaps
     */
    public interface IMDB
    {
        /**
         * Force this object to open
         * @throws Exceptions.MyDBException
         */
        public void open()
            throws MyDBException;
        
        /**
         * Force this object to close
         * @throws Exceptions.MyDBException
         */
        public void close()
            throws MyDBException;
        
        /**
         * Select from storage given the fields and from location
         * @param fields
         * @param from
         * @return
         * @throws Exceptions.MyDBException
         */
        public Map<Integer, Map<String, String>> select(String fields, String from)
            throws MyDBException;
        
        /**
         * Select from storage given the fields, from and condition
         * @param fields
         * @param from
         * @param condition
         * @return
         * @throws Exceptions.MyDBException
         */
        public Map<Integer, Map<String, String>> select(String fields, String from,
                String condition)
            throws MyDBException;
        
        /**
         * Select from storage given the fields, from, condition, and order
         * @param fields
         * @param from
         * @param condition
         * @param order
         * @return
         * @throws Exceptions.MyDBException
         */
        public Map<Integer, Map<String, String>> select(String fields, String from,
                String condition,
                String order)
            throws MyDBException;
        
        /**
         * Select from storage given the fields, from, condition, order, and other
         * @param fields
         * @param from
         * @param condition
         * @param order
         * @param other
         * @return
         * @throws Exceptions.MyDBException
         */
        public Map<Integer, Map<String, String>> select(String fields, String from,
                String condition,
                String order, String other)
            throws MyDBException;
        
        /**
         * Select from a RAW sql input
         * @param sql
         * @return
         * @throws Exceptions.MyDBException
         */
        public Map<Integer, Map<String, String>> selectRaw(String sql)
            throws MyDBException;
        
        /**
         * Get the last inserted ID
         * @return
         */
        public int lastInsertID();
        
        /**
         * Insert a record into storage given the items to insert and location
         * @param fieldVals
         * @param to
         * @throws Exceptions.MyDBException
         */
        public void insert(Map<String, Object> fieldVals, String to)
            throws MyDBException;
        
        /**
         * Update in storage given the new values and where to write them to
         * @param fieldVals
         * @param to
         * @return
         * @throws Exceptions.MyDBException
         */
        public int update(Map<String, Object> fieldVals, String to)
            throws MyDBException;
        
        /**
         * Update in storage given the new values, where to write, and a condition
         * @param fieldVals
         * @param to
         * @param condition
         * @return
         * @throws Exceptions.MyDBException
         */
        public int update(Map<String, Object> fieldVals, String to,
                String condition)
            throws MyDBException;
        
        /**
         * Delete from a given storage location
         * @param from
         * @return
         * @throws Exceptions.MyDBException
         */
        public boolean delete(String from)
            throws MyDBException;
        
        /**
         * Delete from a given storage location given a condition
         * @param from
         * @param condition
         * @return
         * @throws Exceptions.MyDBException
         */
        public boolean delete(String from, String condition)
            throws MyDBException;
    }
    yummy.
    Last edited by Inigoesdr; 05-17-2008 at 07:49 AM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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