Web应用程序：将SQL与代码完全分开

Question

This is a design question, I've been thinking about this for a while.

I would like to separate the SQL queries inside independent files or memory locations (for faster access).

The queries will be stored like this:

SELECT .... WHERE col = ?

When the query is needed, it is fetched from it's location.

A (less maintainable) example alternative would be:

$sql = "select ..... where col=". prevent_injection($val) ." and ....";

Benefits:

• total separation of the queries: the web developers and the DBAs can work specifically to their role
• cleaner queries

Disadvantages:

• requires a bit of coordination on what each ? means, which is necessary anyway in a big team
• sourcing the SQL at runtime may slow things down a bit, but caching can be used

Any feedback on this approach?

Answer 1

You should have a look at the MVC (Model View Controller) architectural pattern. It will allow you to achieve your goal: Separation of data, logic and presentation.

In MVC, your models will handle all SQL related logic, so that the other two layers will never have to deal with queries.

Have a look at PHP Frameworks which implement the MVC pattern, they will greatly simplify applying this pattern to your project. My personal favourite is Laravel 4, but there are lots of others, such as FuelPHP, CodeIgniter, CakePHP et al.

Most of these frameworks also have caching mechanisms as a bonus and they are able to utilize other means of speeding up your web application: Redis and Memcache, for example.