Linux - chown - Cambiando de propietario

$ sudo chown usr: carpeta

chown - cambia el propietario de un archivo o directorio, por motivos de seguridad, en el ejemplo la "carpeta" es asignada como propietario al usuario "usr".

Linux - wget - Descarga de un sitio completo

sudo wget -c -r -erobots=off  -k  --limit-rate=10k http://www.site_to_download

-c:   para que la descarga continue
-r:   para que descargue recursivamente todos los archivos
-erobots=off:  indica al servidor que no es un robot
-k:   convierte los links en locales

-P:  Carpeta donde se descargara, si no existe la crea

sudo wget  -c http://www.site.com  -P /home/gabito/curso/

-m:  Mirror similar a la descarga recursiva

Postgres - Creando tabla temporal desde una consulta

Las tablas temporales no son visibles desde otra sesión, son eliminadas automáticamente a la conclusión de una sesión.

CREATE TABLE tmp_personas AS (SELECT id, name FROM personas)

Postgres - Postgis - Instalación en Ubuntu

Verificando el "status" de Postgres instalado:

$ sudo /etc/init.d/postgresql status

Instalando:


$ sudo apt-add-repository ppa:ubuntugis/ppa
$ sudo apt-get update
$ sudo apt-get install postgresql-9.1-postgis

Carpeta en la que se instaló:

$ ls /usr/share/postgresql/9.1/contrib/


Habilitando postgis a una bd ya existente:

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/spatial_ref_sys.sql

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis_comments.sql

Con soporte raster:

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/rtpostgis.sql 

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/raster_comments.sql 

Con soporte para topologia:

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/topology.sql 

$ sudo -u postgres psql -d basedatos -f /usr/share/postgresql/9.1/contrib/postgis-2.0/topology_comments.sql 

Asignando privilegios a un usuario para la gestión SIG:

$ psql -d database -c 'GRANT ALL ON geometry_columns TO usuariosig;'
$ psql -d database -c 'GRANT ALL ON spatial_ref_sys TO usuariosig;'

 

 

Postgres - Grant

Creando un usuario:

# create user consulta login password 'consulta';

Asignando permiso de consulta (SELECT) a todas las tablas:

# grant select on all tables in schema public to consulta;

Asignando permiso de consulta (SELECT) a las tablas tabla1, tabla2:
 
# grant select on table tabla1, tabla2  to consulta;

Postgres - psql - cursor for update

Función que actualiza una tabla iterando la misma con números correlativos a partir de un parámetro:

CREATE OR REPLACE FUNCTION seq_id(integer) RETURNS VOID AS
$$
DECLARE
 nrosec integer:= $1;

 curupd CURSOR FOR SELECT * from tabla  for UPDATE;
BEGIN
 for i in curupd LOOP
   UPDATE tabla SET id=nrosec WHERE CURRENT OF curupd;
  nrosec:=nrosec+1;
 END LOOP;

 return;
END;
$$
LANGUAGE 'plpgsql';  

Postgres - Backup / Restore

Backup: (S.O. Linux distr. Ubuntu)

$ sudo -u postgres -i pg_dump -c -Fc database > database.backup

-i:   simula login inicial
-u:  usuario
-c:  clean - elimina los objetos de la base de datos previo a la creación de la bd
-Fc: "custom" Format - genera la salida más flexible, considerando todos los ítems y comprimidos por defecto

Restore: (La bd debe ser creada previamente)

$ pg_restore -h localhost -U postgres -Fc -d database  database.backup >> restore.log

Si la base de datos se encuentra en uso y no es eliminada desde PSQL ejecutar:

# SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'database'

Python - Postgres función para envío de mails

CREATE OR REPLACE FUNCTION py_sendmail()
  RETURNS trigger AS
$BODY$
    import smtplib
    from email.MIMEMultipart import MIMEMultipart
    from email.mime.text import MIMEText
    from email.MIMEImage import MIMEImage

    # parte 1ra.
    sql = 'SELECT  campo1, campo2  FROM table'
    sql += 'WHERE  id = '
    sql +=  str(TD['new']['id'])

    mensaje = plpy.execute(sql)
    for msg in evento:
        txt_mensaje += 'texto del mail']+'\n'
        txt_mensaje += 'Dato recuperado de la tabla: ' + str(msg['campo1']) +'\n'

    # parte 2da.
    usuarios = plpy.execute('select nombre, email from users ')
    for usuario in usuarios:
          msg = MIMEText(txt_mensaje)
          msg['Subject'] = 'Prueba'
          msg['From'] = 'Juancito Pinto <juancitopinto@gmail.com>'
          msg['To'] = str(usuario['email'])
        smtpserver = smtplib.SMTP('smtp.gmail.com',587)
        smtpserver.ehlo()
        smtpserver.starttls()
        smtpserver.ehlo()
        smtpserver.login('juancitopinto@gmail.com','pwd_de_juancitopinto')
         smtpserver.sendmail('juancitopinto@gmail.com',str(usuario['email']),msg.as_string())
        smtpserver.close()
   
$BODY$
  LANGUAGE plpython2u VOLATILE

Descripción:

La función descrita esta previsto ser ejecutada por un trigger (cada vez que se adicione un registro por ejemplo).

En la primera parte realiza una consulta a una "tabla" para luego enviar el restultado por mail (Ejm. campo1).

En la segunda parte realiza la consulta a la tabla "users" obteniendo las direcciones de correo para luego enviar el mail correspondiente a todos los usarios existentes en users. Utiliza el servidor SMTP de gmail.

Algo para considerar es que normalmente se recuperan datos de diferentes tipos: enteros, date, time,... etc. de la base de datos, los cuales debén ser previamente convertidos a String para componer una cadena en el mensaje a envíar; una opción si la base de datos es Postgres realizar la conversión con funciones de Postgres Ejm:

SELECT to_char(hora_type, \'HH24:MI:SS\') as hora_string from tabla    

mas referencias: http://www.postgresql.org/docs/9.3/static/functions-formatting.html

Postgres - Triggers invocando a función Python - Ejemplo

Dadas las siguientes tablas:

create table t3 (id int, name varchar);
create table t4 (id int, name varchar);

Con la función en python:

create or replace function py_addpar()
returns trigger as $$
     if int(TD['new']['id'])%2 == 0:
        sql = plpy.prepare(""" INSERT INTO t4 (id, name) values ($1, $2); """, ("INTEGER", "TEXT",))
        ss = plpy.execute(sql, (TD['new']['id'], TD['new']['name'],))
$$ language plpython2u;


y el trigger que lo invoca:

create trigger tr_add
    after insert
    on t3
    for each row
    execute procedure py_addpar();

Verificando:

insert into t3 (id, name) values (1, 'uno');
insert into t3 (id, name) values (2, 'dos');

select * from t4 ;

visualizará:

        id |  name
     ------+-----------
        2  |  'dos'

Es decir el trigger ejecuta la función py_addpar que adiciona en la tabla t4  los valores de la tabla t3  si es que el campo id es par.

Consideraciones:

El lenguaje PL/Python importa automáticamente el módulo plpy. que proporciona dos funciones (execute y prepare). Llamando con plpy.execute con un string de consulta (query) y el argumento (limit) opcional retorna en un objeto resultado. La segunda función "prepare" prepara la ejecución, es llamado con un string de consulta y una lista de tipos de parámetro.

A diferencia de las funciones invocadas en plpgsql, los valores enviados como parámetros  se encuentran en el diccionario [TD] (http://www.postgresql.org/docs/9.1/static/plpython-trigger.html)

Los valores de los parámetros deben ser previamente convertidos (cast) al tipo de dato que corresponde, Ejm:  int(TD['new']['id'])

Ahora con PL/PGSql:

El siguiente código realiza lo mismo con la única diferencia que utiliza el lenguaje PL/PGSql:

Función:

create or replace function  py_addpar()
returns trigger as $$
BEGIN
if NEW.id % 2 = 0 then
    insert into t4 (id, name) values (NEW.id, NEW.name);
end if;
return NULL;
END;
$$ language plpgsql;

Postgres - Creando extensión python

Para usar PL/Python, primero se requier instalar Python en el servidor. Para Linux/Unix, los binarios de Python usualmente están disponibles.

Luego, se debe instalar la extensión PostgreSQL Python:

CREATE EXTENSION plpython2u;       --para python 2.7 por ejemplo
ó
CREATE EXTENSION plpython3u;

 Listando los lenguajes instalados (debería visualizar: plpgsql y plpython2u

 \dL

Linux - Búsqueda de texto en carpetas

Realiza la búsqueda en todos los archivos, visualizando aquellos donde se encuentra "txt_searched".

$ find . -exec grep -l "txt_searched" {} \;

Postgres - update desde otra tabla (three tables)

UPDATE tabla1
SET  campo1 = t2.campo2
FROM  tabla2 t2
WHERE tabla1.campo3 = t2.campo3

three tables:

UPDATE aa  
SET id_unico = ii.id_unico, dist = mm.dist
FROM ii, mm 
WHERE aa.idloc = mm.idloc
          AND ii.id_unico = mm.id_unico

Postgres - Adicionando registros en tabla con campo "id" autoincremental

INSERT tabla (id, nombre, direccion) VALUES (default, 'Juan Loza Castillo', '6 de Agosto #2649')

Para el caso de existir una tabla a la cual se desea adicionar el campo id autoincremental (serial)

ALTER TABLE tabla ADD COLUMN  id SERIAL;

Si se desea adicionar a partir del  valor 101 asignado al campo id:

CREATE SEQUENCE seq_test
  INCREMENT 1
  START  100

CREATE TABLE test (
  id integer DEFAULT NEXTVAL('seq_test') NOT NULL,
  nombre varchar(20)
)


Modificando un campo ya existen (id) a tipo autoincremental:

ALTER TABLE tabla ALTER COLUMN  id SET DEFAULT NEXTVAL('seq_test')

Postgres - reemplazando un substring en un campo

Reemplaza la ocurrencia ","  por "" encontrada en el contenido del campo "campo1", es decir elimina las comas del contenido de este campo.

UPDATE  tabla SET campo1 = REPLACE(campo1, ',', '')
WHERE campo1 ~ '[,]';

Postgres - Cast

Aparentemente no funciona cuando los campos de la tabla a los que se aplica cast se encuentran en mayúsculas, una forma (seguro que no la óptima) de evitar el problemilla es llevar a una tabla temporal con nombres de campo en minúscula.

Postgres - Insert de tablas desde una tabla ...

create or replace function inserta_tablas() returns setof record as $$

declare 

res record;

i integer;

q text;

begin

i:= 0;

for res in select table_name from information_schema.tables 

        where table_schema not in ('information_schema', 'pg_catalog') and table_name <> 'u'

        order by table_name

        loop

        raise notice 'insertando tabla: ----> %', res.table_name;

        q:= 'insert into u select * from "' || res.table_name || '"' ;

        execute q;

        i:= i+1;

return next res;

end loop;

raise notice 'Tablas insertadas: %', i;

end;

$$ language plpgsql

Para ejecutar:

select * from une() as (tabl information_schema.sql_identifier);

Todas las tablas encontradas en la base de datos son insertadas a la tabla 'u'.