如何管理数据层中的SQL语句

Question

in a PHP project we already have separated business logic from database access. All database tasks are encapsulated in different database classes grouped by database and topic. Theses classes look very horrible, half the source are SQL strings, that get filled with params and so on. We thought of putting the SQL in "other" locations like resource files or something. What is considered best practise for this and do you know any supporting tools/libs for PHP?

Kind Regards

Stephan

Answer 1

You should use stored procedures wherever it is possible. That way you enhance performance, security and code maintenance. This should be your first approach.

If you still want to separate the SP queries from the DAL, why not store them in a database? It may seem odd to store SQL queries in the database for abstraction, since a query is needed to extract other queries. This is actually a quite common approach, where you can select queries matching a certain criteria and possibly (if necessary) to build up the queries dynamically.

Another approach may be to create Query-classes where queries are built up dynamically;

class FruitQuery {
    ...
    public function addTypeCriteria($type) {
        $this->internalSQLCriterias[]  = "fruit=:type";
        $this->internalSQLParameters[] = array(':type', $type);
    }
    ...
    public function create() {
        $this->internalSQLQuery = "SELECT ... FROM Fruits";

        if (sizeof($this->internalSQLCriterias) > 0) {
            $this->internalSQLQuery .= " WHERE ";
            $moreThanOne = '';

            foreach ($this->internalSQLCriterias as $criteria) {
                $this->internalSQLQuery .= $moreThanOne . $criteria;
                $moreThanOne = " AND ";
            }
        }
    }
    ...
    public function execute() {
        /* Bind the parameters to the internalSQLQuery, execute and return results (if any) */
    }
...

This class is absolutely not complete in any way, and you might want to rethink the structure of it - but you probably get the point I'm trying to make. :) Of course you have to filter the input to the Query-builder to avoid security breaches!