SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast elmonty's Avatar
    Join Date
    Jan 2004
    Location
    Michigan
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Creating database objects in PHP: include file hell

    After doing some work in both Java and ASP.NET, I came back to add some major enhancements to a PHP 4 application I built several years ago. A lot of the database access is currently sprinkled throughout the application.

    In the Java and .NET worlds, I would do data access code by creating classes that would handle all of the data access for each of the application data entities. This worked great and kept all the data access in one clearly designated set of classes.

    I thought I would apply the same concept to PHP. However, it isn't working well, and here's why:

    - Because I'm using MySQL 4.1 and MyISAM tables (InnoDB is too darn big), I have to manage all the relationships myself.

    - If I need to delete an entity that has a lot of relationships, I have to delete the records from all the related tables. I can do this by either (a) making direct calls to MySQL to delete records from all the related tables, or (b) if this was Java or .NET, I would simply make calls to the related data objects. Since some of the related tables also had related operations to perform, I started revising my PHP code to use the latter method. So my code started to look like this:

    PHP Code:
    class HcsDBO
    {
        
    // Delete an HCS and all its related records
        
    function Delete($hcs)
        {
            if (!empty(
    $hcs))
            {
                
    HospitalsDBO::DeleteHCSHospitals($hcs);            
                
    UsersDBO::DeleteHCSUsers($hcs);
                
    BillToDBO::Delete($hcs);
                
    DuesDBO::Delete($hcs);            
                
                
    // Now delete the HCS record
                
    global $db;
                
    $sql "DELETE FROM HCS WHERE EntityID=$hcs";            
                
    $db->Query($sql);
            }
        }

    - In order to call these other data object methods, I need to include their files. This means that whenever I need to perform any kind of HCS operation, whether or not related tables are involved, I need to include all the data access code for all the related tables. That's going to be a LOT of code.

    - Compiled (or pseudo-compiled) languages such as Java and .NET can pull in called objects as needed. PHP doesn't. All that code is read and parsed for every page that requires any of it.

    I can't see that this approach is very efficient for PHP, but I don't want to continue the "data access code is sprinkled everywhere" approach. Is there a better solution?

  2. #2
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    unfortunately if you're stuck on php4 you can't take advantage of autoload. If you can upgrade I would highly recommend it since it sounds like you come from an OO background and php5 added a LOT of OO improvement...

  3. #3
    SitePoint Enthusiast elmonty's Avatar
    Join Date
    Jan 2004
    Location
    Michigan
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, the web host does not support PHP 5.

  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 elmonty View Post
    That's going to be a LOT of code.

    - Compiled (or pseudo-compiled) languages such as Java and .NET can pull in called objects as needed. PHP doesn't. All that code is read and parsed for every page that requires any of it.
    Most likely, it isn't as bad as you think. PHP is very efficient to parse/compile files. I suggest that you try it out, and measure how much it actually takes. You can install xdebug, which allows you to profile the code. If you do find that it takes unacceptable long time, you can install an opcode cache, such as pecl:apc.

  5. #5
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elmonty
    - Because I'm using MySQL 4.1 and MyISAM tables (InnoDB is too darn big), I have to manage all the relationships myself.
    Are you concerned about filesize? I just tried to check this but MySQL 5 seems to throw all innodb data into one ibdata file. Could you specify the difference? I would be very interested if you had some numbers.
    But back on topic: Is the size difference really worth all the hassle? Why not let the RDBMS do all the work?

  6. #6
    SitePoint Wizard dreamscape's Avatar
    Join Date
    Aug 2005
    Posts
    1,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elmonty View Post
    Unfortunately, the web host does not support PHP 5.
    If a web host is all that prevents you from moving to PHP 5, I'd say it's high time to find a new host.

    It's rather silly, IMO, to not upgrade to developing with PHP 5 if it has features you desire and want just because some web host doesn't support it. There are millions of web hosts, and many do support PHP 5, so it's not as if reliable decently priced PHP 5 hosting is hard to find either.
    <.smarter.web.development.>
    PHP Stuff: Plexus | Chocolate (BDD Framework... coming soon)
    Graphite

  7. #7
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happened to the use of foreign keys in database modeling?

    They are there for a reason and should be used!

    With foreign key relationships and a carefully designed database and proper use of the "ON DELETE CASCADE" the database will do all this for you. It's called "data integrity". You can even set rules that will make impossible to add data to a table if it doesn't exist in another table.

    simple example

    table users:
    user_id | other fields

    table groups:
    group_id | other fields

    table users2groups
    user_id | group_id

    user_id = Foreign Key on table users(user_id) ON DELETE CASCADE <-- means that if a row in "users" is deleted, then all the rows containing that user_id in users2groups will also be deleted. Same thing for the group_id linked to the groups table.

    Then you don't need to worry about what is going on in the DB as with correct rules, data won't be in an orphan state (having rows in users2groups where the user_id doesn't exist anymore in users for example).

    Also you don't need to worry about it, the DB manages everything for you and will do it better than you could in your application!
    David

  8. #8
    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 ClickHeRe View Post
    What happened to the use of foreign keys in database modeling?
    It's not supported by MyISAM. (Which of course is a good argument for upgrading to a newer version of MySQL)

  9. #9
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > It's not supported by MyISAM.

    Makes no difference to me personally, as I default to InnoDb anyways. Another relational example would be where you have a customers table, and an orders table.

    You could enter a record into the customers table, without an order although you couldn't enter a record in the orders table, without first entering one to match in the customers table.

    During your schema design, understand the relationship between parent and child first, else you'll give yourself grieve

  10. #10
    SitePoint Wizard dreamscape's Avatar
    Join Date
    Aug 2005
    Posts
    1,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    It's not supported by MyISAM. (Which of course is a good argument for upgrading to a newer version of MySQL)
    Not sure exactly what you mean about upgrading. MyISAM is probably never going to be ACID compliant, and MySQL has supported InnoDB since version 3.23. And it has shipped with InnoDB by default since version 4.0.

    So unless you are running a really old version, there is no specific reason you need to upgrade in order to use InnoDB.
    <.smarter.web.development.>
    PHP Stuff: Plexus | Chocolate (BDD Framework... coming soon)
    Graphite

  11. #11
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    It's not supported by MyISAM. (Which of course is a good argument for upgrading to a newer version of MySQL)
    I moved to PostgreSQL for all my apps. I was a bit tired of all the non standard stuff in MySQL.

    I've even integrated my apps and modules to use the roles system (users+groups) from the database. No more superuser database stored on the web server to access the DB.

    When you login, you are checked against the credentials stored on the database server directly (not in a table, but in the DB role system). Then each group is assigned access to only the stored procs that it needs to access. The users are assigned to groups which define in turn their possibilities and everything is synced with the application permissions.

    Even if the web server falls (db server stored on a separate machine), they can't access the data without db credentials.

    And if any DB credentials are found, the DB already has this user restricted to a subset of stored procs available which doesn't include anything other than stored procs calls (no direct SELECT, INSERT, DELETE, DROP, etc).

    By default the app ships with 2 roles (Administrators and Guests) and 2 users (administrator and guest). The guest account has no password, but it is limited to only retrieving data for the public portion of the site.

    And a nice admin panel to enable the 'administrator' to add/delete/edit users/groups in the system a bit like the OS systems for users/groups and privileges.
    David


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
  •