Monthly Archives: October 2010

Cómo utilizar JOIN en sentencias UPDATE o DELETE.

Una práctica muy recomendable antes de lanzar una sentencia UPDATE o DELETE sobre un subconjunto de tuplas, es probar a realizar la proyección, afinando las condiciones de restricción para el subconjunto (mediante una sentencia WHERE). Cuando nos encontramos ante una condiciones sencillas, reutilizar la misma clausula WHERE es trivial. Pero qué sucede cuando queremos utilizar funciones de composición (JOIN) para restringir el conjunto de datos.

Ejemplo para UPDATE, donde se puede ver como se han utilizado varios JOINs en el bloque de referencia a la table (table_reference en el manual de MySql):

UPDATE table1 T1 INNER JOIN table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category SET T1.readed = 0 WHERE T1.type = 'X' AND T1.category  = 'A';

Ejemplo para DELETE. En este caso después del DELETE hay que especificar la tabla o tablas (multi-delete):

DELETE T2 FROM table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category WHERE T1.type = 'X' AND T1.category  = 'A';

Espero que estos ejemplos os sirvan de ayuda.

NOTA: Todos los ejemplos se han ejecutado en un servidor MySql version 5.1.

Actualización

Ejemplo para MS SQL 2005:

UPDATE table1 SET T1.readed = 0 FROM table1 T1 INNER JOIN table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category  WHERE T1.type = 'X' AND T1.category  = 'A';

Zope and Apache HTTP Server working as reverse proxy

I folks,

If you want to create an scenario with a Zope working as production web site and other/s Zope working as application server, probably you are looking for the way to configure Apache as reverse proxy between the front row (web site and content) and the rear row (web applications). In this case, working with Zope makes the configuration be easier because it has a product (VirtualHostMonster) that does part of the work; “A Virtual Host Monster’s only job is to change the URLs which your Zope objects generate. This allows you to customize the URLs that are displayed within your Zope application, allowing an object to have a different URL when accessed in a different way“. This is very useful because in other way we should use a third-party module called ProxyHtml. mod_proxy_html is used to rewrite HTML code, specially links. But in our case, we only need to use the rewrite module (mod_rewrite) and the proxy module (mod_proxy).

Let’e get to work:

To redirect an application from a public server to a private server (private server does not need to has an Apache running) we should add the following code in the virtual host configuration file (/etc/apache2/vhosts.d/myhost.conf).

RewriteRule ^/Intranet/Apps/app1(.*) http://private.com:8080/VirtualHostBase/http/<strong>%{HTTP_HOST}</strong>:80/DOM/VirtualHostRoot/Intranet/Apps/app1/$1 [L,P]
ProxyPassReverse / http://private.com:8080/VirtualHostBase/http/<strong>%{HTTP_HOST}</strong>:80/DOM/VirtualHostRoot/

Note than in the second part of both rules the URL is compliant with the VHM requirements. First we found the rewrite and redirection to the private server (http://private.com:8080/VirtualHostBase/) and after the /http/ we find the host (here I use the Apache’s variale HTTP_HOST) where the VirtualHostMonster will do the reverse proxy. %{HTTP_HOST} is an Apache’s variable, and we can use it here or directly the name of the public server.

And this is all 🙂 If you have any other experience or comment please don’t hesitate to comment here!