Corregir nicks de usuarios con caracteres no válidos

Aquí podeis observar como arreglar la típica tabla de usuarios en la que se se han introducidos nicks con caracteres extraños que luego dan problemas al hacer login. La tabla principal que almacena los usuarios es USUARIO La tabla aux_usuario almacena los usuarios con caracteres no admitidos La tabla aux_usuario_bueno almacena los usuarios que realizada la traducción de un caracter incorrecto a otro, no supone nick duplicado en la tabla principal
-- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario;
-- Rellenamos la tabla con los usuarios problemáticos
create table aux_usuario as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________'); 
-- Actualizamos el nick substituyendo los caracteres problematicos  
update aux_usuario set nick=translate(nick,'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') ;
-- Creamos una tabla excluyendo los duplicados
drop table if exists aux_usuario_bueno;
create table aux_usuario_bueno as select * from aux_usuario aux where aux.nick not in ( select nick from usuario) and aux.nick not in ( select nick from (
-- Actualizamos la tabla principal
update usuario set nick=aux_usuario_bueno.nick from aux_usuario_bueno where aux_usuario_bueno.id=usuario.id ; ===================================================================== --- PARA LOS DUPLICADOS
--- Los usuarios que no al ser traducido el nick colisiona con otro
--- existente le añadimos el identificador para hacerlo único.
--- En el caso de nombres mayores de 10 caracteres añadimos
--- el identificador por segunda vez. ===================================================================== -- Borramos la tabla de apoyo si existe  
drop table if exists aux_usuario2;
-- Rellenamos la tabla con los usuarios problemáticos create table aux_usuario2 as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=id::varchar where length(nick) >=10; update aux_usuario2 set nick=nick||id::varchar where length(nick) <10;
update usuario set nick=aux_usuario2.nick from aux_usuario2 where aux_usuario2.id=usuario.id ;

selects favoritas con variables en psql

Puedes tener tus scripts de ayuda preparados, con esas consultas que sueles usar continuamente, y que te pregunten los parametros para después ejecutarlos.

Por ejemplo me voy a crear un script en /home/postgres que se llama nick.sql, que me va a servir para buscar a un usuario por su nick:
\set nick 'pepe'
\prompt NICK nick \set nick_comillas '\'' :nick '\'' \echo select * from usuario where nick= :nick_comillas;
\prompt seguro seguro
select * from usuario where nick= :nick_comillas;

Para ejecutarlo dentro de psql \i nick.sql y nos pedirá el parametro del nick y ejecutará la sentencia. Aquí se muestra como poner una variable de tipo cadena ya que las comillas son complicadas de formatear.

Crear una instancia de prueba de Amazon

Tutorial muy completo para crear una instancia gratuita de prueba en Amazon. Muy bien explicado: http://alanta.info/como-crear-un-servidor-gratuito-en-amazon.html

Monitorización Linux

Quien está conectado y que está haciendo

w [user_name]

17:24:24 up 88 days, 17:12, 5 users, load average: 0,09, 0,07, 0,08

USER TTY   FROM         LOGIN@ IDLE   JCPU  PCPU  WHAT

gme  pts/0 paco.bit.mad 09:35  0.00s  0.61s 0.00s w

wdev pts/1 fe.bit.mad   Fri10  5days  0.01s 0.01s -bash

gme  pts/2 fe.bit.mad   Fri11  27:06m 6.31s 6.28s ./gmeser

wdev pts/5 paco.bit.mad 09:35  7:48m  0.01s 0.01s -bash

gme  pts/7 fe.bit.mad   12:23  4:58m  0.04s 0.04s -bash

Otras herramientas de monitorización de actividad, memoria, uso de disco, cpu... http://www.cyberciti.biz/tips/top-linux-monitoring-tools.html

Llamadas repetitivas a Web Services con wget

Necesitaba hacer multiples llamadas a webservices, y luego verificar los resultados devueltos. En lugar de hacerlo enlace a enlace, preparé un fichero llamadas.url donde almacene las llamadas a realizar en forma de proceso por lotes:

wget -S -i llamadas.url -O resultados_http.txt -a info_llamadas.txt

Los resultados de las llamadas se guardarán en resultados_http.txt La información de errores y tiempos se guardará en: info_llamadas.txt

Vacuum de tablas con muchas actualizaciones

Si revisando los logs de tu Base de datos postgres, te encuentras a menudo con la siguiente entrada:

2012-02-06 18:44:20 CETLOG: vacuum automático de la tabla «esquema.tabla»: recorridos de índice: 1

Esto se debe a que esa tabla tiene muchas inserciones o updates y constantemente tiene que aprovisionar nuevas páginas, el proceso de vacuum autoático se lanza para liberar las páginas inservibles, etc. Para solucionar este problema lo mejor es indicar un Factor de relleno (Fillfactor) para la tabla. Este factor representa cuanto espacio se deja en una página para el alojamiento de nuevas inserciones o actualizaciones de tuplas de esa página. Por defecto este valor es 100, por lo que todas la actualizaciones/inserciones irán a páginas nuevas y no se modificará la página actual. Para empezar un buen valor es 80, pero si la tabla tiene un ínidice muy alto de actualización tendrás que probar con valores por debajo de 50. Despues de hacer la modificación, no tendrá efecto hasta que hagas un Vacuum full (Ojo esta operación bloqueará la tabla, por lo que es mejor que no haya conexiones a la base de datos):

ALTER TABLE rankings.ranking SET ( fillfactor = 80 ); VACUUM FULL rankings.ranking;

Este factor también afecta a los índices que sean modificados con mucha frecuencia, la sintaxis para modificarlos es:

ALTER INDEX "tabla_idx" ON "esquema"."tabla" USING btree ("campo_a_indexar"); DROP INDEX.tabla_idx; CREATE INDEX "tabla_idx" ON "esquema"."tabla" USING btree ("campo_a_indexar") WITH (fillfactor = 80);

Referencias: http://www.postgresql.org/docs/9.0/static/sql-createtable.html

Update basado en select

Si un día necesitas guardar algunos campos de una tabla antes de que la tabla sea machacada por un proceso, puedes guardar los datos en una tabla auxiliar:

drop table if exists aux_coleccion; create table aux_coleccion as select * from coleccion;

Despues de que hayamos guardado una copia de la tabla podemos machacar su contenido. Ahora podemos recuperar el contenido original de los campos que queramos:

update coleccion set precio=a.precio,nivel=a.nivel,puntos=a.puntos,estado=a.estado, nombre=a.nombre,descripcion=a.descripcion, from aux_coleccion a where a.id=coleccion.id;