开发者

Storing SQL Statements in the Database

开发者 https://www.devze.com 2023-02-07 01:01 出处:网络
Some developers in my company think it is a good idea to get the SQL-Statements out of the production code in to some centralised storage. So they came up with the Idea to store the SQL-Statements in

Some developers in my company think it is a good idea to get the SQL-Statements out of the production code in to some centralised storage. So they came up with the Idea to store the SQL-Statements in the Database, so they can call data from the database like this:

$result = GetData(#querynumber, parameters, ...);

Is this good practice? Are there alternatives for centralised storage of the sql-Statements? Prepared statement won't work because we use multiple databases and the database name would be a variab开发者_运维知识库le in the sql-statement.


I've come across ideas like that before and I'm quite sure it's not a good idea.

First of all, if you write reasonably modular code, most of the database stuff should be in the same places anyway, so why get it into the database. Secondly: If you change queries, you should also keep in mind that code might rely on certain details of the query, you will have to start making changes in two different places.

IMHO, there is no point in externalising queries into a database in most cases. Just write decently modular, low-coupling code for your model and you should be better off.


This is how I do it.

commonly used sql to join tables and get results should be in a view. commonly used sql to manipulate data should be in a stored procedure.

specialized sql that is unlikely to be used elsewhere is fine in code.

Personally I don't mind having code like $sql="SELECT field1, field2 FROM $view WHERE id=$param" in code. Its the big blocks of joins and group bys, that are going to be reused, that I think don't belong.

But they don't belong as strings in a query table. Thats what views and stored procedures are for.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号