Out of curiosity, what's your thinking on avoiding stored procedures?

Does PHP support XML documentation? That's where you can decorate any accessible object or class or method or property or even members of an enum with documentation in XML format. Since you're already making heavy use of comments, that brought this feature to my mind. Apart from the compiler being able to emit documentation in XML format and an XSLT to get it to look like Word or MSDN, whenever you hover the mouse over anything you've written documentation for, right there in teh code editor, you get a mouse over tip that shows you the documentation you wrote for that code piece.

That has nothing to do with writing database software - but in large projects I've found it to be incredibly helpful.

But yeah, it sounds like a workable plan. If it were something I was working on, I'd still be concerned about not using sprocs, but that really comes down to personal preference and comfort, like most coding tasks.

I'm not sure if PHP supports XML documentation, but I'm commenting my code as heavily as I can because other people will probably be working on it at some point in the future. Plus I hate it when I can't figure out what something does that I wrote. Come production time, I might strip it down, but for development, I'm erring on the side of caution.

There are several reasons I think I'm leaning away from sprocs. I'm far more comfortable with the PHP (I'd actually never heard of sprocs before today), but also when I did research on them, I found this article: http://www.tonymarston.net/php-mysql...-are-evil.html
It brought up quite a few valid points that made me think that sprocs might not be the best choice for my application. However, I think I could probably implement them fairly easily with my DAO architecture at a later date, if I wanted to do so, even though it would be another layer.

Wow - that article strikes me as really off base! I mean there are like 2 or 3 sentences that are true, or at least agree with my experience. It's your app, but since you're asking, let's take a look at the specific things that are going to make what you need to do more difficult.

Look at this thread. It's very relevant. In SQL server, the syntax is WHERE SomeDT < '1/1/1954' but in Access it's WHERE SomeDT < #1/1/1954#. That's a huge freaking big deal in that a query won't do what it needs to, and yet it's also a minor detail. If you used a sproc, that is a query that's parameterized and stored in the database, the syntax in more or less all RDBMS's is WHERE SomeDT < ?. You get to eliminate a lot of implementation specific things by treating search values as raw data and not having to encode them. That doesn't help you with LIMIT 10 or With (TabLockX) but it frees you from other issues to apply your time to the ones that really need your attention.

Also, and this ties in but is a much bigger problem, despite what the article says, stored procedures really do make your code a lot easier on you, to write, manage, and especially to debug. It's easier to spot and fix bugs looking at code, than it is looking at code that writes code. All the time in the ASP forum someone will post some VBScript that writes SQL, asking why the SQL isn't working, and even the most experienced of us can't or won't debug through 2 levels like that. If there's a bug in the way you're quoting things, with type specific delimiters (bool is treated different in MySQL and SQL Server) or other things, it's easier, less time consuming, and less error prone to work on the actual database code instead of the application code that writes the database code that isn't working.

Originally Posted by Learning Newbie

Also, and this ties in but is a much bigger problem, despite what the article says, stored procedures really do make your code a lot easier on you, to write, manage, and especially to debug. It's easier to spot and fix bugs looking at code, than it is looking at code that writes code. All the time in the ASP forum someone will post some VBScript that writes SQL, asking why the SQL isn't working, and even the most experienced of us can't or won't debug through 2 levels like that. If there's a bug in the way you're quoting things, with type specific delimiters (bool is treated different in MySQL and SQL Server) or other things, it's easier, less time consuming, and less error prone to work on the actual database code instead of the application code that writes the database code that isn't working.

Hmmm. I guess I'm going to have to think about it some more. I'm still in the design phase, so I'm going to do some more research and explore all possibilities. Third-party integration is a concern because I'm going to allow for the development of third-party modules. How would sprocs do in that type of environment?

I'm also looking into a few different PHP frameworks. Do you have any that you'd recommend? I'm looking at Zend right now, but I'm going to take a look at CakePHP and a few other ones as well.

I use ADODB when i need to chage a system from mysql to oracle for example, and works good.

the other notable benefit to using an abstraction layer (aside from portability which may developers never benefit from) is the fact that you can easily add in security features and clean your input or output without needing to explicitly make that extra step with every query.

Originally Posted by VirtuosiMedia

Hmmm. I guess I'm going to have to think about it some more. I'm still in the design phase, so I'm going to do some more research and explore all possibilities.

Tis the season to get your design as you'd like it!

Originally Posted by VirtuosiMedia

Third-party integration is a concern because I'm going to allow for the development of third-party modules. How would sprocs do in that type of environment?

This is another area where they can make a night and day difference. I'd sound like a zealot to answer, but let's try anyway. In Windows land, it's pretty common to create a user at different levels and run a service or application through a particular user. This is done for reporting, security, and other purposes. We could take any example, but creating a new user is the one that springs to mind.

As a 3rd party developer making a new module for your system, which type of syntax would you prefer to work with?

Exec(ute) AddUser @name='Johnny Boy' @roles='Admin Reporting' @Enabled=1

Or

Begin Transaction
If Not Exists(Select * From Users Where Name='Johnny Boy') Begin
Insert Into Users ( Name, other columns ) Values ( 'Johnny Boy', more )
Declare @uid int
Select @uid = @@Identity
Insert Into UserRoles ( UserID, RoleID ) Values ( @uid, admin_role_ID )
Insert Into UserRoles ( UserID, RoleID ) Values ( @uid, reporting_role_ID )
more code as necessary
End (If)
If @Err = 0 Commit Transaction Else Rollback Transaction

Now that's an over simplified example. In a lot of complex, data backed systems, doing a logical unit of work can be even more cumbersome and complex. On the one hand, the dynamic SQL method causes you to type out gobs and gobs more SQL code, and on the other, it forces any 3rd party developers to have a much more intimate knowledge of your database internals than they otherwise need to. Finally, executing a procedure that's already been written and tested is obviously a lot less error prone than letting somebody who doesn't fully understand the system write PHP that writes SQL that's supposed to interact with the system.

Consider that in Windows, when a 3rd party app has a bug and crashes, a lot of people tend to blame Windows itself. If people buy your product and it's buggy because a 3rd party module isn't playing nice, you're likely to get some blame. A set of tables is the storage mechanism in a database, but the procedures are its API. We've all had to hack systems with no API, and it's always a lot more painful and nerve wracking.

Thanks for the illustration. There is a large difference in the amount of code, but if a 3rd party developer develops a module (which may need to update or access existing tables or create new ones), it would still need to be able to run on the system no matter what database is being used (for instance, if site A is using MySQL and site B is using Oracle). In that scenario, wouldn't a DBA layer be better than a sproc? The 3PD could then just write the code once and not have to worry about developing and testing sprocs for each type of DB. Once the module has been installed, it will detect which kind of database is being used from system settings and will function accordingly.

As a separate but related issue, I'm also exploring offline functionality as well. Do you have any thoughts on Google Gears? Do you know of any similar (and mature) projects?

Thanks, I really appreciate your insights. I only started learning PHP about four months ago, so everything is still pretty new. I'd never even done a website until about a year ago.

Originally Posted by carloncho

I use ADODB when i need to chage a system from mysql to oracle for example, and works good.

I took a look at ADODB and it seemed to do well in benchmarking tests. I'm still weighing options, though.

Originally Posted by VirtuosiMedia

The 3PD could then just write the code once and not have to worry about developing and testing sprocs for each type of DB.

Unless I'm missing something big, this presents zero savings in terms of dev time. The reason is simple. Either way, the 3rd party still has to write and test SQL code. The only difference is how that code is packaged - inside the SQL database, or inside a PHP module?

Originally Posted by VirtuosiMedia

Thanks, I really appreciate your insights. I only started learning PHP about four months ago, so everything is still pretty new. I'd never even done a website until about a year ago.

Well, I've never been a PHP programmer, which is probably why I can't comment on Google Gears. I've done a few different varieties of C, VB, C#, and now ASP.NET and lots of Windows server programming. So I know a lot about theory, but very little about how theory is applied in PHP. (In case you didn't already figure out to take anything I say with a garin of salt.)

In my experience, you should narrow the DB you plan to be able to swap to.
As John stated, there are just way to many variants between all the db engines out there.

And as a postgresql enthusiast, I'd recommend you to test it. It goes more in direction of commercial databases than Mysql (years ahead) and keeps the advantages of open source (in my opinion).

Stored procs are an excellent way to reduce error possibilities, but if you are going to accept 3rd parties modules, I can see a problem about the trust you are about to put in 3rd parties.

Stored procs, in my opinion, are great to get secured, limited and controled access to your database to 3rd party.
But if they need to compile they own stored procs, don't they could bring your server on it's knees (think about a simple recurring loop here, or a missing joint on a big query).
Are you up to give other that level of trust ?
I know would not...

What I usually do, is that I try to plan a schema as generic as possible.
Then, from that generic schema, I look in depth at the functionnalities I'd like to implement, and how each db can implements them.
For example, with time, I resorted to the use of sqlite databases for caching mechanisms, but I would not use it for my main database.
The simple fact that there is no date/time format is a big enough no-no.

The gauge is usually between mysql (almost aways the loser), postgresql (almost always the winner), and oracle PE.
I'm thinking about trying DB2 soon, and I probably will do more and more ms sql in the times to come (I'll probably integrate a ms-sql dev team next month) so maybe I'll come to reconsider it. I do not at the moment because all my servers are running linux.

Anyway, so, when I've planned the needs, I usually stick to the database that can follow them the most closely.
If I have a specific requirement on that level, I usually separate the data retrieving in 2 part:
° Simple queries (that fit the "generic schema") are coded directly.
° More complicated queries, that requires special attention, are coded in specific methods of an object related to the database engine, and take in account theyre specificities.

I have based my framework upon PDO, and I encourage you to do the same. It's C based, and usually as fast (and in some cases, even faster) than native PHP functions, and the methods are more unified.

If you want, I could post the generic class and the specifics class childs (mysql, pgsql and sqlite) I use as barebone components of my projects.

Originally Posted by Learning Newbie

Unless I'm missing something big, this presents zero savings in terms of dev time. The reason is simple. Either way, the 3rd party still has to write and test SQL code. The only difference is how that code is packaged - inside the SQL database, or inside a PHP module?

Would the same still be the case if they were using a tested DAL? Tripy, I think, was able to express my thoughts perhaps better than I could. I'm leaning toward using the Zend framework in general, but I'm not decided yet on how to do the database part yet.

Originally Posted by Learning Newbie

Well, I've never been a PHP programmer, which is probably why I can't comment on Google Gears. I've done a few different varieties of C, VB, C#, and now ASP.NET and lots of Windows server programming. So I know a lot about theory, but very little about how theory is applied in PHP. (In case you didn't already figure out to take anything I say with a garin of salt.)

No problems. I have no experience in other languages, so you're definitely further along than I am. I like the idea of Google Gears, but I hesitate a little because it's still in beta. Do you know of any equivalents?

Originally Posted by tripy

Stored procs are an excellent way to reduce error possibilities, but if you are going to accept 3rd parties modules, I can see a problem about the trust you are about to put in 3rd parties.

Stored procs, in my opinion, are great to get secured, limited and controled access to your database to 3rd party.
But if they need to compile they own stored procs, don't they could bring your server on it's knees (think about a simple recurring loop here, or a missing joint on a big query).
Are you up to give other that level of trust ?
I know would not...

Good point.

Originally Posted by tripy

What I usually do, is that I try to plan a schema as generic as possible.
Then, from that generic schema, I look in depth at the functionnalities I'd like to implement, and how each db can implements them.
For example, with time, I resorted to the use of sqlite databases for caching mechanisms, but I would not use it for my main database.
The simple fact that there is no date/time format is a big enough no-no.

If MySQL is the lowest common denominator, I don't think I'm going to run into any problems because I haven't found anything that I want to do with my CMS that MySQL can't facilitate. (My logic being that if MySQL can do it, most of the other big ones can do it too.)

From what I understand, Google Gears uses SQLite. One thing that I'm a little fuzzy on, though, is how to take the data from SQLite and convert it to another database type once an online connection is made.

Originally Posted by tripy

I have based my framework upon PDO, and I encourage you to do the same. It's C based, and usually as fast (and in some cases, even faster) than native PHP functions, and the methods are more unified.

If you want, I could post the generic class and the specifics class childs (mysql, pgsql and sqlite) I use as barebone components of my projects.

I'm leaning toward Zend_db, which supports PDO, I think, but if you could post your classes, that would be helpful as well. Thanks.

because I haven't found anything that I want to do with my CMS that MySQL can't facilitate

Sure, but several db can do the same thing different ways. That's the problem.

One thing that I'm a little fuzzy on, though, is how to take the data from SQLite and convert it to another database type once an online connection is made.

You have to open 2 db connections.
Make a select on the first, process the results, and send insert on the target.
I never heard of any automated functions for that in the php core engine. But they surely are some classes out there that should (try to) do the job.

but if you could post your classes, that would be helpful as well.

Here they are.
Simply, define what backed you will be using, and make a require on cl_baseDb.php and "mysql.php", for exemple.

The main functions are:
° q() to send a query
° f() fetch the recordset in an array
° fo() fetchthe recordset as an anonymous objects
° n() for the row count. This is not supported by sqlite, so t return a defined value SQLITE_NOT_SUPPORTED
° lastInsert() returns the last autoincremented id or sequence value used as a primary key.

And just to document them:
° xml() needs other parts of my framework, but simply return a XML representation of the query result.
° prepare() / execute() experimental binding functions for now. I don't use it often, but I plan to do so in the future. It allows to define a "query template", and then fire it sequentially with different values. It eases the db work on the query planner level, and is more secure.