php - mostrar - ¿Cómo manejas las consultas SQL?
multiples consultas mysql php (10)
Por el momento, mi código (PHP) tiene demasiadas consultas SQL. p.ej...
// not a real example, but you get the idea...
$results = $db->GetResults("SELECT * FROM sometable WHERE iUser=$userid");
if ($results) {
// Do something
}
Estoy buscando utilizar procedimientos almacenados para reducir esto y hacer las cosas un poco más robustas, pero tengo algunas preocupaciones ..
Tengo cientos de consultas diferentes en uso en el sitio web, y muchas de ellas son bastante similares. ¿Cómo debo gestionar todas estas consultas cuando se eliminan de su contexto (el código que usa los resultados) y se colocan en un procedimiento almacenado en la base de datos?
Estábamos en una situación similar al mismo tiempo. Hemos consultado una tabla específica en una variedad de formas, más de 50+.
Lo que terminamos haciendo fue crear un único procedimiento almacenado Fetch que incluye un valor de parámetro para WhereClause. El WhereClause se construyó en un objeto Provider, utilizamos el patrón de diseño Facade, donde pudimos eliminar cualquier ataque de inyección SQL.
Por lo que respecta al mantenimiento, es fácil de modificar. SQL Server también es bastante chum y almacena en caché los planes de ejecución de consultas dinámicas para que el rendimiento general sea bastante bueno.
Tendrás que determinar los inconvenientes de rendimiento en función de tu propio sistema y tus necesidades, pero en general, esto nos funciona muy bien .
Hay algunas bibliotecas, como MDB2 en PEAR, que hacen que las consultas sean más fáciles y seguras.
Desafortunadamente, pueden ser un poco prolíficos para configurar, y a veces tienes que pasarles la misma información dos veces. Utilicé MDB2 en un par de proyectos, y tendí a escribir una fina capa alrededor, especialmente para especificar los tipos de campos. Generalmente hago un objeto que conoce una tabla en particular y sus columnas, y luego una función auxiliar que llena los tipos de campo cuando llamo a una función de consulta MDB2.
Por ejemplo:
function MakeTableTypes($TableName, $FieldNames)
{
$Types = array();
foreach ($FieldNames as $FieldName => $FieldValue)
{
$Types[] = $this->Tables[$TableName][''schema''][$FieldName][''type''];
}
return $Types;
}
Obviamente, este objeto tiene un mapa de nombres de tablas -> esquemas que conoce, y solo extrae los tipos de los campos que especifique, y devuelve una matriz de tipos coincidentes adecuada para su uso con una consulta MDB2.
MDB2 (y bibliotecas similares) manejan la sustitución de parámetros por usted, por lo que para actualizar / insertar consultas, simplemente construye un hash / map desde el nombre de la columna al valor, y usa las funciones ''autoExecute'' para construir y ejecutar la consulta relevante.
Por ejemplo:
function UpdateArticle($Article)
{
$Types = $this->MakeTableTypes($table_name, $Article);
$res = $this->MDB2->extended->autoExecute($table_name,
$Article,
MDB2_AUTOQUERY_UPDATE,
''id = ''.$this->MDB2->quote($Article[''id''], ''integer''),
$Types);
}
y MDB2 construirá la consulta, escapando todo correctamente, etc.
Sin embargo, recomendaría medir el rendimiento con MDB2, ya que saca un poco de código que podría causarle problemas si no está ejecutando un acelerador de PHP.
Como digo, la configuración general parece desalentadora al principio, pero una vez que se hace, las consultas pueden ser más simples / más simbólicas para escribir y (especialmente) modificar. Creo que MDB2 debería saber un poco más acerca de su esquema, lo que simplificaría algunas de las llamadas de API comúnmente utilizadas, pero puede reducir la molestia de esto al encapsular el esquema usted mismo, como mencioné anteriormente, y proporcionar funciones simples de acceso que generen el arrays MDB2 necesita realizar estas consultas.
Por supuesto, puede hacer consultas SQL planas como una cadena utilizando la función query () si lo desea, por lo que no está obligado a cambiar al modo completo ''MDB2''; puede probarlo por partes y verifique si lo odio o no
Primero, debe usar marcadores de posición en su consulta en lugar de interpolar las variables directamente. PDO / MySQLi le permite escribir sus consultas como:
SELECT * FROM sometable WHERE iUser = ?
La API sustituirá de forma segura los valores en la consulta.
También prefiero tener mis consultas en el código en lugar de la base de datos. Es mucho más fácil trabajar con un RCS cuando las consultas son con su código.
Tengo una regla de oro cuando trabajo con ORM: si estoy trabajando con una entidad a la vez, usaré la interfaz. Si estoy informando / trabajando con registros en conjunto, generalmente escribo consultas SQL para hacerlo. Esto significa que hay muy pocas consultas en mi código.
Use un paquete ORM, cualquier paquete medio decente le permitirá
- Obtenga conjuntos de resultados simples
- Mantenga su SQL complejo cerca del modelo de datos
Si tiene SQL muy complejo, las vistas también son agradables para hacerlo más presentable a las diferentes capas de su aplicación.
Tuve que limpiar un proyecto con muchas consultas (duplicadas / similares) plagadas de vulnerabilidades de inyección. Los primeros pasos que tomé fueron usar marcadores de posición y etiquetar cada consulta con el objeto / método y la línea de origen de la consulta. (Inserte las constantes de PHP METHOD y LINE en una línea de comentario SQL)
Se veía algo como esto:
- @Line: 151 UserClass :: getuser ():
SELECT * FROM USERS;
El registro de todas las consultas durante un tiempo corto me proporcionó algunos puntos de partida sobre los cuales se fusionan las consultas. (¡Y donde!)
Use un marco ORM como QCodo: puede mapear fácilmente su base de datos existente
Me gustaría mover todo el SQL a un módulo de Perl por separado (.pm) Muchas consultas podrían reutilizar las mismas funciones, con parámetros ligeramente diferentes.
Un error común para los desarrolladores es sumergirse en bibliotecas ORM, consultas parametrizadas y procedimientos almacenados. Luego trabajamos durante meses seguidos para hacer que el código sea "mejor", pero solo es "mejor" en un tipo de desarrollo. ¡No estás haciendo nuevas funciones!
Use la complejidad en su código solo para atender las necesidades del cliente.
Intento usar funciones bastante genéricas y solo paso las diferencias en ellas. De esta forma, solo tienes una función para manejar la mayoría de tus SELECT de base de datos. Obviamente, puede crear otra función para manejar todos sus INSERTOS.
p.ej.
function getFromDB($table, $wherefield=null, $whereval=null, $orderby=null) {
if($wherefield != null) {
$q = "SELECT * FROM $table WHERE $wherefield = ''$whereval''";
} else {
$q = "SELECT * FROM $table";
}
if($orderby != null) {
$q .= " ORDER BY ".$orderby;
}
$result = mysql_query($q)) or die("ERROR: ".mysql_error());
while($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
Esto está fuera de mi cabeza, pero entiendes la idea. Para usarlo simplemente pase la función los parámetros necesarios:
p.ej.
$blogposts = getFromDB(''myblog'', ''author'', ''Lewis'', ''date DESC'');
En este caso $ blogposts será una matriz de matrices que representan cada fila de la tabla. Luego puede usar un foreach o referirse a la matriz directamente:
echo $blogposts[0][''title''];
El mejor curso de acción para usted dependerá de cómo se acerque a su acceso a los datos. Hay tres enfoques que puede tomar:
- Usa procedimientos almacenados
- Mantenga las consultas en el código (pero ponga todas sus consultas en funciones y arregle todo para usar PDO para los parámetros, como se mencionó anteriormente)
- Use una herramienta ORM
Si desea pasar su propio SQL en bruto al motor de la base de datos, los procedimientos almacenados serían el camino a seguir si lo único que desea hacer es extraer el código SQL de su código PHP, pero mantenerlo relativamente sin cambios. Los procedimientos almacenados versus el debate de SQL sin formato es una especie de guerra santa, pero K. Scott Allen presenta un excelente punto, aunque sea descarado, en un artículo sobre las bases de datos de versiones :
En segundo lugar, los procedimientos almacenados han caído en desgracia a mis ojos. Venía de la escuela de adoctrinamiento WinDNA que decía que los procedimientos almacenados deberían usarse todo el tiempo. Hoy, veo procedimientos almacenados como una capa API para la base de datos. Esto es bueno si necesita una capa API en el nivel de la base de datos, pero veo muchas aplicaciones incurriendo en la sobrecarga de crear y mantener una capa API adicional que no necesitan. En esas aplicaciones, los procedimientos almacenados son más una carga que un beneficio.
Tiendo a inclinarme hacia no usar procedimientos almacenados. He trabajado en proyectos en los que el DB tiene una API expuesta a través de procedimientos almacenados, pero los procedimientos almacenados pueden imponer algunas limitaciones propias, y esos proyectos tienen, en diversos grados, el código SQL bruto generado dinámicamente para acceder al DB.
Tener una capa de API en la base de datos proporciona una mejor delineación de responsabilidades entre el equipo de DB y el equipo de desarrollo a expensas de la flexibilidad que tendrías si la consulta se mantuviera en el código, sin embargo, es menos probable que los proyectos de PHP tengan un tamaño considerable. suficientes equipos para beneficiarse de esta delineación.
Conceptualmente, probablemente debería tener su base de datos versionada. Hablando en términos prácticos, sin embargo, es mucho más probable que solo tenga el código versionado que la versión de su base de datos. Es probable que cambie sus consultas cuando realice cambios en su código, pero si está cambiando las consultas en procedimientos almacenados almacenados en la base de datos, entonces probablemente no los verifique cuando revise el código y pierda. muchos de los beneficios del control de versiones para un área importante de su aplicación.
Independientemente de si elige o no utilizar procedimientos almacenados, al menos debe asegurarse de que cada operación de la base de datos se almacena en una función independiente en lugar de estar incrustado en cada uno de los scripts de su página, esencialmente una capa API para su DB que se mantiene y versiona con tu código. Si está utilizando procedimientos almacenados, esto significará que tiene dos capas de API para su base de datos, una con el código y otra con la base de datos, que puede sentir que complica innecesariamente las cosas si su proyecto no tiene equipos separados. Ciertamente lo hago
Si el problema es la limpieza del código, hay maneras de hacer que el código con SQL atascado sea más presentable, y la clase UserManager que se muestra a continuación es una buena manera de comenzar: la clase solo contiene consultas relacionadas con la tabla ''usuario''. cada consulta tiene su propio método en la clase y las consultas se sangran en las instrucciones de preparación y se formatean como lo haría en un procedimiento almacenado.
// UserManager.php:
class UserManager
{
function getUsers()
{
$pdo = new PDO(...);
$stmt = $pdo->prepare(''
SELECT u.userId as id,
u.userName,
g.groupId,
g.groupName
FROM user u
INNER JOIN group g
ON u.groupId = g.groupId
ORDER BY u.userName, g.groupName
'');
// iterate over result and prepare return value
}
function getUser($id) {
// db code here
}
}
// index.php:
require_once("UserManager.php");
$um = new UserManager;
$users = $um->getUsers();
foreach ($users as $user) echo $user[''name''];
Sin embargo, si sus consultas son bastante similares, pero tiene un gran número de permutaciones en las condiciones de consulta, como paginación complicada, clasificación, filtrado, etc., una herramienta de asignación de objetos / relacionales es probablemente el camino a seguir, aunque el proceso de revisión de su código existente hacer uso de la herramienta podría ser bastante complicado.
Si decides investigar las herramientas de ORM, deberías consultar Propel , el componente ActiveRecord de Yii o el King-daddy PHP ORM, Doctrine . Cada uno de estos le da la capacidad de generar consultas mediante programación a su base de datos con todo tipo de lógica complicada. Doctrine es la más completa, lo que le permite crear una plantilla de su base de datos con elementos como el patrón de árbol Conjunto anidado de la caja.
En términos de rendimiento, los procedimientos almacenados son los más rápidos, pero generalmente no demasiado en bruto sql. Las herramientas ORM pueden tener un impacto significativo en el rendimiento de varias maneras: consultas ineficientes o redundantes, IO de archivos grandes al cargar las bibliotecas ORM en cada solicitud, generación de SQL dinámico en cada consulta ... todas estas cosas pueden tener un impacto, pero el uso de una herramienta ORM puede aumentar drásticamente la potencia disponible para usted con una cantidad de código mucho menor que la creación de su propia capa DB con consultas manuales.
Sin embargo, Gary Richardson tiene toda la razón, si va a continuar usando SQL en su código, siempre debe usar las declaraciones preparadas de PDO para manejar los parámetros independientemente de si está usando una consulta o un procedimiento almacenado. La limpieza de la entrada se realiza por usted por PDO.
// optional
$attrs = array(PDO::ATTR_PERSISTENT => true);
// create the PDO object
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", $attrs);
// also optional, but it makes PDO raise exceptions instead of
// PHP errors which are far more useful for debugging
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare(''INSERT INTO venue(venueName, regionId) VALUES(:venueName, :regionId)'');
$stmt->bindValue(":venueName", "test");
$stmt->bindValue(":regionId", 1);
$stmt->execute();
$lastInsertId = $pdo->lastInsertId();
var_dump($lastInsertId);
Advertencia: suponiendo que la ID sea 1, la secuencia de comandos anterior dará salida a la string(1) "1" . PDO->lastInsertId() devuelve el ID como una cadena independientemente de si la columna real es un entero o no. Esto probablemente nunca será un problema para usted ya que PHP realiza el envío de cadenas a enteros automáticamente.
Lo siguiente arrojará bool(true) :
// regular equality test
var_dump($lastInsertId == 1);
pero si tiene un código que espera que el valor sea un número entero, como is_int o el operador de PHP "es realmente, verdaderamente, 100% igual a" :
var_dump(is_int($lastInsertId));
var_dump($lastInsertId === 1);
podría encontrarse con algunos problemas.
Editar: una buena discusión sobre los procedimientos almacenados aquí
Esta otra pregunta también tiene algunos enlaces útiles en ella ...