I wonder a bit at the flexibility and extensibility of it, at least in regards to what I would like to do. Because I would like to create a core software for a CMS and allow others to be able to extend it for themselves or create modules for it for others, I'd like to make it as easy for them to do so as possible (for market share reasons as well). Wouldn't they have to create a new class for every query they would like to make that doesn't exactly match one that's already included in the software?

I would say that this is more related to OOP (Object Oriented Programming) than ORM.
But, if you can abstract the visualization of your object enough, you could provide "core" functions advanced enough to allow your 3rd developers to simply extend or implement interfaces in your base class(es) with the methods and properties they need to describe their own object(s).

I don't know how used you are to OOP, so I won't sink you in a lot of theory right now, but this looks more like a way to follow in my opinion.

And what if they wanted to extend the users table by adding something like DOB or gender?

Why not implement them as a concept of meta-datas ?
You have a core element, which is your user.
Have a table somewhere that allow you to "attach" attributes and values to that user. I use this method for describing the users in the web sites I've done in the latest months. It allow me to reuse a standardized "user" object with the login/update/signup procedures.
I have a core user representation (postgresql syntax and data types):

create table users(
  i_uid integer not null default nextval('seq_user'),
  x_username varchar(255) not null,
  x_email varchar(255) not null,
  h_pwd varchar(255) not null,
  d_creadt timestamp not null default NOW(),
  primary key (i_uid)
);

And I have a table to extend that representation:

create table userAttr(
  i_useratt integer not null default nextval('seq_userattr'),
  i_uid integer not null references users(i_uid),  --Foreign key to the user record
  c_type varchar(50) not null,  --This will hold the name of the property
  x_val varchar(255),  -- string data value
  n_val integer(12),  --integer data value
  f_val numeric,  --float data value
  b_val boolean,  --boolean data value
  d_val , --if any combination of date or time
  primary key (i_userattr)
);
create index i_userAttr_uid_type on userAttr(i_uid, c_type);

That way, every new attributes I want to attach to a user record, I can put it into the userAttr table, and fetch them relative to the user id and/or the data type.

Abstracting your objects might make them harder to understand at first, but once that you are used to the concept, you will see that it gives your code a real flexibility and allow your code to be reused in many other part of your application, or other projects.
The key is, don't loose yourself too much in details.
Comment your code a lot, try to represent your objects (using uml, visio, dia or a sheet of paper) and it will make you easier to see how you could abstract them.

Regarldess of all this, I just want to say that I love this thread.
Possibly one of the most interessant one I've crossed here, and learning from Learning Newbie is fun.
John, you really are a gold mine of informations, you just push me further every time I read your explaination.
Just a off track question: for how long time are you programming ?
I feel almost ignorant every time I read your posts when they get in the details...

Originally Posted by tripy

Possibly one of the most interessant one I've crossed here, and learning from Learning Newbie is fun.

Now if that's not a paradox, I don't know what is. Except, you know, that old Borland DBMS.

Originally Posted by tripy

John, you really are a gold mine of informations, you just push me further every time I read your explaination.
Just a off track question: for how long time are you programming ?
I feel almost ignorant every time I read your posts when they get in the details...

Thanks so much, Tripy! This means a great deal considering who it's coming from!

I started programming in Basic in 1987 or 1988, but nothing all too serious. I had an IBM PC that used Basic as its operating system, so I'd SHELL out to call other programs, but I'd also make little ones of my own. Calculate sales tax, and the like.

I think I started programming seriously around 1991, using C++ for DOS. So I guess that'd be around 17 years. Ugh, I feel old. I've also been doing a great deal of SQL since SQL Server 6. I think for the past several years, SQL has been my speciality, but I've done a lot of application programming to, so I like to think I have a good idea of application and database interoperability.

Now if that's not a paradox, I don't know what is. Except, you know, that old Borland DBMS.

Haha ! No, I don't know Borland, except by it's name. I was not so much interestd in programming at that time...

Thanks so much, Tripy! This means a great deal considering who it's coming from!

:-) And a thank you in reply too. Although I'm not so sure I'm that much competent. But as so many people keep telling me not to bury myself, so, well, thanks...

Ugh, I feel old.

Don't think too much about that, my friend. You are never older than your thoughts.
And yours seem to be still plain young!

I like to think I have a good idea of application and database interoperability.

I think you have more than a good idea... You know what you are talking about, and it's really entertaining to discuss with you from programming concept.
I do have learned several thing from this thread, on the ORM subject.

I never looked much into it, but I think I'll concentrate a bit more in the next weeks. I have the feeling that it could be useful in some part of a new project I'm planning.

Speaking of ORM, I had promised to introduce another programming practice a page or 2 ago, and haven't yet. This one is called the domain object model, or, at least, this is how I've always heard of it. I'm going to explain it with the User object we've been talking about, not because it's only useful for that or even more than usual - just that it's been the example we've stuck with in this thread, and I think it's easiest to use the same concept to look at different ideas on how to treat it.

Most people or companies or systems or whatever, implement their objects with a Load and Save method. So the p(seudo) code might be

PHP Code:

class User
{
string m_username, m_passwordHash;
int m_database_user_ID;

public User(int dbID)
{
m_database_user_ID = dbID;
Load();
}

string Username { get { return m_username; } set {} }

string PasswordHash { get { return m_passwordHash; } set {} }

void Load() { }

void Save() { }

} 


The Load() and Save() methods would open a database connection, and, ideally, call a stored procedure (also sproc and proc, so we can talk less tediously) with some parameter values, and either fetch or save database information. This is so common in the OOP world you'd spend less time counting the programs or programmers who don't use the above pattern, than ones who do.

The domain object model says your business objects should be ignorant. They're objects. They should have only the methods and properties that describe that object. In other words, nix the Load() and Save() methods.

"What? That's crazy talk! Why would you want to do that?" Well, think of the limitation in the p code I wrote out. Even without seeing the implementation, we can see the limit. Either the User object can be persisted in one location, or those two methods are going to be really nasty. What if I want my Users to get saved to a SQL Server database, and also to an XML file? Sure, I could run a SQL Agent Job every 5 minutes to query the User table and write new records to the filesystem as XML, but then I have to wait 5 minutes to see a new User, and also it's adding needless complexity to the whole system, and it's pushing work from the best place to do it, off to some hack way. Not good.

So we have a User object that only has the instance data it needs to represent a user.

And we have a UserStore object, or maybe a static class instead, that has no instance fields, but knows how to load and save User objects. From wherever they may be.

PHP Code:

class UserStore
{
void SaveToDatabase(User usr) {}
void SaveToWebService(User usr) {}
void SaveToXml(User usr) {}

void Save(User usr)
{
SaveToDatabase(usr);
SaveToWebService(usr);
SaveToXml(usr);
}
} 


It takes some down in the trenches time to see the value of this approach. And I've bored everyone enough as it is, so I won't tell y'all about the time when blah blah blah. I'll just point out that this is an added layer of abstraction that's proved valuable even when I'm only using SQL Server 2005 for an entire project.

Say you add a column to the User table. You don't need to update the User class, or any of the calling code. (Well maybe you do, if that new column translates to a new field in the object, but say it's just an audit column.) Any data persistence change gets carried out in the app, in the store. You've got a User store, maybe a Thread store and a Post store if it's a forum, or basically a store for every object you have.

From your calling code, you would just write UserStore.Save(m_user); and be done with it. Your business rules dictate where and how the user is saved. Maybe only people in a certain role get saved beyond just the database, for some kind of management reporting, or whatever you need.

Back to the original question of this thread. I still recommend stored procedures, and recommend against dynamic SQL. But however you do that part, if you want to support MySQL, SQL Server, Oracle, and MS Access, you do that by writing four Save methods. Your general Save method, typically the only public one, can have an if statement to decide which ones to call.

Ok, so I'm going to try to put an oversimplified example into semi-pseudo code. I would need to sit down and actually plan out the methods and arguments, but this is just off the top of my head. Something like this?

PHP Code:

class DatabaseObject {
    
    public $tableName; 
    
    public function addColumn ($name, $type, $value, $collation, $attributes, $null, $default, $increment, $unique){

        public $tableToWriteTo;

        //Add a column to the user table with this code
    }
    
    public function update ($id, $field, $value) {
        //Update the user table with this code
    }
    
    public function delete ($id){
        //Delete a row in the user table with this code
    }
    public function insert ($fields, $values){
        //Insert a row in the user table with this code
    }
    public function getRows ($id, $where, $sort){
        //Get rows from the user table with this code
    }
    
} 


PHP Code:

class users extends DatabaseObject {
    
    public $tableName = 'users';

} 


PHP Code:

class articles extends DatabaseObject {
    
    public $tableName = 'articles';
    
} 


The addColumn method would enable 3rd parties to extend the table or an add-on table, if I wanted. They would also be able to create a new data object by extending the DatabaseObject class and assigning the table name variable.

I'm learning more OOP by the day, but I haven't had much of a chance to put it to practice yet. I'm still at the strategizing phase of development.

This thread has been great for me as well. I've learned a ton and I'm having to do a research for just about every post you guys have put up. I found two articles by IBM describing software development patterns (for PHP in this case) that I'm trying to digest at the moment, here and here. This is all going to help me a lot for designing my software. I'd like to do it the right way instead of just throwing something together. I also want to push the boundaries a little if I can and do something innovative, but I can't do that without knowing what can already be done and how best to do it.

I'll look at the articles, but that will have to wait, possibly until Monday.

Already the p code you've got looks like it should be a lot more manageable and extendable. I hope you don't think I'm being a jerk, but something jumps out at me.

Most people these days will advise against CRUD. I gotta go, would love to clarify, but they're actually making me work!

http://www.ibm.com/developerworks/li...p-designptrns/

I like the quote this starts off with, "Design patterns are just for Java™ architects -- at least that's what you may have been led to believe. In fact, design patterns are useful for everyone." It's just true, and adopting the best practice patterns and recognizing the "anti patterns" is helpful to anyone who has other things they want to do than programming.

After reading it, sitting for a day, then skimming back through, I think a lot of the patterns they're describing are either building up to or parts of the MVC, the holy grail of decoupling. It's interesting to see them broken out like this, in a use what you need fashion.

The factory pattern doesn't fit into MVC, and while it's getting extremely popular, I don't tend to use it in most of the dev work I do. Part of that is because Microsoft's DAAB is one of the examples it's been prominently introduced in, but also because, well, it just hasn't applied to the stuff they've been paying me to look at.

Which brings up a good point. You should play with DAAB. It's a friendly wrapper for ADO.NET that provides a level of database connectivity abstraction. It's not something you can use in your PHP, but it may have a few concepts you might want to use.

http://msdn2.microsoft.com/en-us/magazine/cc163766.aspx
http://www.microsoft.com/downloads/d...displaylang=en

PHP Code:

 string sSql = "SELECT CustomerID, CompanyName, City, Region " +
    "FROM Customers WHERE City LIKE 'M%'";
Database dbNorthwind = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmdCust = dbNorthwind.GetSqlStringCommandWrapper(sSql);
DataSet dsCust = dbNorthwind.ExecuteDataSet(cmdCust);
IDataReader rdrCust = dbNorthwind.ExecuteReader(cmdCust); 


A factory pattern code sample

PHP Code:

private void Foo(string sSql)
{
    string sDatabaseInstance = "NorthwindDBInstance";
    Database db = DatabaseFactory.CreateDatabase(sDatabaseInstance);
    DBCommandWrapper cmdCust = db.GetSqlStringCommandWrapper(sSql);
    IDataReader rdrCust = db.ExecuteReader(cmdCust);
    ...
} 


This allows the key to be swapped out, ideally though an input parameter, but they give this example

PHP Code:

string sDatabaseInstance = "MyOracleDBInstance"; 


Originally Posted by Learning Newbie

Most people these days will advise against CRUD.

I was gone last week, so I'm trying to catch up a little. I'm still processing through the DAAB stuff right now. Could you elaborate a little on why people are advising against CRUD? And are you talking specifically about CRUD in an object-oriented environment or just in general?

I'm looking at the way Joomla and Drupal, and Wordpress handle database abstraction. It seems like none of them use ORM, but they all use an abstract database class of some sort.

I don't know Joomla or Drupal, but I don't think WordPress is coded to ever work against anything but MYSQL? I could be wrong on that, but I don't see any abstraction value, because I don't think it was written to realistically ever be ported to a different backend?

Now onto CRUD. I don't even have time to cover all the reasons people consider CRUD an "anti pattern" or "worst practice" if I took all year. But let's ask a rhetorical question instead. Why even implement CRUD at all? Let people Create aka Insert, Read aka Select, Update aka Update, and Delete aka Delete records using SQL directly! Why would you write code to mimic functionality that exists already in the system you're using?

One reason is you don't want to make it too hard to develop modules for your software, right? A 3rd party shouldn't have to know the details of your persistance layer. They shouldn't have to know the names of tables, columns, and the like. All they really need to know is how to accomplish something useful that adds value to the overall system.

A closely related reason is that not all people will know, and not all people will do a good job. If doing a task requires more than 1 update, what if they code things wrong? Do you write extra code to recover, or allow your data to be corrupted? What if an error stops their code halfway through a series of updates? How do you recover?

You could answer all of that saying "Well I provided an interface, it's up to them to use it properly". That might be convenient for you as a programmer (I still think otherwise) but surely it's bad for the program.

CRUD is too simplistic to describe your model. It works great on tables, key value pairs, and the like. Data instead of information or knowledge. But any non trivial system, which I think software has to be, to sell these days, is likely to need more. For example if you're writing bank software, you wouldn't code a withdrawl using CRUD. Read the current value, say $400, and update it to $250. What if bill pay comes along and does something to the account in between the read and the update? What if you're depositing Pesos and the exchange rate changes? What if you want to withdraw more than is in the account?

Here's a quote

In contrast, most order processing is not CRUD, or at least not according to my definition. For example, an order can be created offline and then sent (replicated if you will) to a service for processing. Processing of that order will affect many of the related entities. The service may update the customer information, potentially changing more than just the year-to-date totals. For instance, the customer might have reached the critical order mass and be upgraded, updating properties used for price and discount calculations; products may or may not be available; delivery dates may or may not have been realistic; and so forth. These changes are important to both parties, but with CRUD, the customer's copy of the order would not reflect them.

Regardless of the physical implementation, an order has the semantics of requesting the service accept and process a new order. Such an order is a complex request for a complex business transaction and I regard such a request as a "journal." A journal being a set of business actions combined in a single request document.

Another

Anti-Pattern #1: CRUDy Interface

• Symptoms & Consequences:
  • The interface design encourages RPC-like behavior, calling Create, MoveNext, and so on, instead of sending a well-defined message that dictates the action to be taken. This is a violation of the first (Well Defined Boundaries) and third (Share only Schema) tenets.
  • Interface is likely to be overly chatty, since consumers may need to call two or three methods to accomplish their work.
  • Using a Sub for Create means that the consumer will have no idea if the operation succeeds or fails. When designing a service always keep the consumer's expectation in mind—what does the consumer need to know?
  • CRUD operations are the wrong level of factoring for a Web service. CRUD operations may be implemented within or across services, but should not be exposed to consumers in such a fashion. This is an example of a service that allowed internal (private) capabilities to bleed into the service's public interface.
  • The interface implies stateful interactions such as enumeration (see the MoveNext and Current functions).
  • Abstract types (such as the Object returned by the Current function) result in a weak contract. This is another example of violating the third tenet (Share only Schema).
  • This is a very dangerous service since it could leave the underlying data in an inconsistent state. What would happen if a consumer added a new Contact (or updated an existing Contact) and never called the CommitChanges function? As stated earlier, service providers cannot trust consumers to "do the right thing."

I thought I saw looking through the Drupal docs an example using PostgreSQL, but I don't think Joomla or Wordpress are anything but MySQL specific.

I'm going back and taking a good hard look at stored procedures. It seems to me that they are very similar in concept to an ORM, with the main difference being that stored procedures are stored as part of the database and an ORM is stored in the code layer. Am I far off the mark in thinking that?

Again, thank you both. I'm getting closer to a decision. I know at the very least I'm going to have a PDO layer, but now I have to decide how thick of a layer I want on top of that, be it dynamic SQL, stored procedures, an ORM, or just the PDO layer. What you said about the CRUD layer replicating existing functionality made sense to me.