lunes, 15 de diciembre de 2014

Usuario de solo lectura

A veces puedes necesitar dar acceso a tu base de datos a una herramienta de terceros, de la cual no te fias o solo quieres que haga consultas pero no toque nada. Una forma rápida es crear un usuario de solo lectura:

create user 'readonly' password 'laquesea';
alter user readonly set default_transaction_read_only = on;

Luego puedes darle permiso sobre todas las tablas de los esquemas que necesites:






select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema1');
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema2');

Y por último dale permisos de acceso a los esquemas:

grant usage on schema cromos to readonly;
grant usage on schema coleccion to readonly;

lunes, 6 de octubre de 2014

Bloque anónimo

A veces necesitas hacer uso de las estructuras de plpgsql, por ejemplo, en scripts .sql que llamas desde tareas del cron, etc. Para ello tenemos los bloques DO.

A continuación un bloque sencillo, que comprueba si es primeros de mes y añade un mes de antigüedad a los usuarios registrados. Como puedes ver podemos utilizar variables y ejecutar comandos de los cuales no necesito conocer el resultado (perform).

do $$
declare
  dia integer;
begin
  select extract ('day' from current_date) into dia from editorial;
  if dia = 1 then
    perform 'update usuario set months=months+1';
  else
    raise notice 'Hoy no es día 1';
  end if;
end$$;

martes, 12 de agosto de 2014

Cambiar atributos default o not null a una columna existente

Si quieres quitar la propiedad default o not null a una columna existente;

alter table  alter column  drop not null;

alter table  alter column  drop default;


Por el contrario, si quieres añadir el atributo not null o la propiedad default:

alter table  alter column  set not null;

alter table  alter column  set default current_timestamp;



Ultimo id insertado

Cuando queremos recuperar el último id insertado en una tabla, para por ejemplo asignarlo en los registros de sus hijas. Lo habitual es consultar el último valor de la secuencia asociada al campo clave. Por ejemplo:
 
select currval('cambio_id_seq');
 
En este caso tenemos que conocer el nombre de la secuencia, si lo desconociésemos podríamos utilizar la función pg_get_serial_sequence que nos proporcionaría el nombre de la secuencia asociado a un campo determinado de una tabla:

select currval(pg_get_serial_squence('cambio','id'));

Esta opción tiene un problema. Solo nos funcionará si nadie más utiliza dicha secuencia en otra sesión. Es decir en entorno de multiples conexiones no es recomendable.

Por el contrario LASTVAL(), está aislado a nivel de sesión, por lo que no tiene dichos efectos indeseados

Pero si la tabla tuviese un trigger que insertará nuevos registros o modificara la secuencia por algún motivo, LASTVAL() no funcionaría correctamente. 

Por todo lo anterior, el mejor método posible es utilizar la clausula RETURNING después de la inserción. Por ejemplo:

    insert into cambio (id,usuario1,usuario2)
    values (DEFAULT,usuario1_id,usuario2_id)
    RETURNING id into cambio_id;


En la variable cambio_id tendremos almacenado seguro el valor de la inserción que estamos haciendo en nuestra sesión, sin efecto colateral alguno.

miércoles, 16 de julio de 2014

OpenXava combos

Construyendo administradores backend para una web. Busqué información para la construcción de los típicos combos que facilitan la selección de un valor de una tabla maestra para copiarlo en el campo de una tabla o mostrar su descripción.


Por ejemplo, teniendo una tabla card con varios campos de referencia a otra tabla skill. Y teniendo campos card1,card2 que hacen referencia a la propia tabla la clase quedaría así:



import java.io.Serializable;
import java.util.Collection;
import javax.persistence.*;
import org.openxava.annotations.*;
@Entity
@Table(name = "card", catalog = "soccer", schema = "master")
@View(members=
"Otros datos [#id,name;description;];"+
 " [rarity, type, subtype;" +
"price, points, cost, dust;" +
"health, ability, level, collectible;" +
"image, frame, cardset;" +
"skill1; skill2; skill3;" +
"card1Id; card2Id;" +
"classId;" +
"];" +
"cardPackCollection")
public class Card implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    @DisplaySize(10)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "name")
    @DisplaySize(40)
    private String name;
    @Basic(optional = false)
    @Column(name = "description")
    @DisplaySize(120)
    private String description;
   
    @ManyToOne(fetch=FetchType.LAZY)   
    @JoinColumn(name="skill1")
    @DescriptionsList(
      descriptionProperties="id,name",
      order="name"
    )
    private Skill skill1;
   
    @ManyToOne(fetch=FetchType.LAZY)   
    @JoinColumn(name="skill2")
    @DescriptionsList(
      descriptionProperties="id,name",
      order="name"
    )   
    private Skill skill2;
   
    @ManyToOne(fetch=FetchType.LAZY)   
    @JoinColumn(name="skill3")
    @DescriptionsList(
      descriptionProperties="id,name",
      order="name"
    )
    private Skill skill3;
    @ManyToOne(fetch=FetchType.LAZY)   
    @JoinColumn(name="card1_id")
    @DescriptionsList(
      descriptionProperties="id,name",
      order="name"
    )   
    private Card card1Id;
   
   
    @ManyToOne(fetch=FetchType.LAZY)   
    @JoinColumn(name="card2_id")
    @DescriptionsList(
      descriptionProperties="id,name",
      order="name"
    )         
    private Integer card2Id;
      
    @OneToMany(mappedBy = "cardId", fetch = FetchType.LAZY)
    private Collection cardPackCollection;
    public Card() {
    }
    public Card(Integer id) {
        this.id = id;
    }
    public Card(Integer id, String name, String description, Card card1Id, int card2Id ) {
        this.id   = id;
        this.name   = name;
        this.description= description;
        this.card1Id = card1Id;
        this.card2Id = card2Id;
    }
    public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public Skill getSkill1() {
        return skill1;
    }
    public void setSkill1(Skill skill1) {
        this.skill1 = skill1;
    }
    public Skill getSkill2() {
        return skill2;
    }
    public void setSkill2(Skill skill2) {
        this.skill2 = skill2;
    }
    public Skill getSkill3() {
        return skill3;
    }
    public void setSkill3(Skill skill3) {
        this.skill3 = skill3;
    }
   
    public Card getCard1Id() {
 return card1Id;
    }
    public void setCard1Id(Card card1Id) {
 this.card1Id = card1Id;
    }
    public Integer getCard2Id() {
 return card2Id;
    }
    public void setCard2Id(Integer card2Id) {
 this.card2Id = card2Id;
    }

    public Collection getCardPackCollection() {
        return cardPackCollection;
    }
    public void setCardPackCollection(Collection cardPackCollection) {
        this.cardPackCollection = cardPackCollection;
    }
}




martes, 27 de mayo de 2014

Script migración tablas sueltas

Script de apoyo para mover el contenido una serie de tablas de una base de datos a otra.
Toma el contenido de las tablas tabla1..tabla4 y lo vuelca en /tmp/fichero.dump sin tomar la estructura de la tabla.
Utiliza unas tablas auxiliares para guardar el contenido de las tablas que tienen relaciones con tabla1..tabla4.
Vacía las tablas tabla2_related1..tabla2_related3 con relaciones sobre las tablas que estamos moviendo.
Posteriormente vacía las tablas destino tabla1..tabla4 y despues las rellena con el contenido del fichero /tmp/fichero.dump
Por último restaura el contenido de las tablas con relaciones, utilizando la cópia almacenada en las tablas auxiliares.
Espero que os pueda servir de ayuda en alguna ocasión.


#Volcado de las tablas originales deseadas (primero las maestras luego las dependientes)
pg_dump -U -h -p --inserts --data-only -t tabla1  >  /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla2 >> /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla3 >> /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla4 >> /tmp/fichero.dump

#Borrado de las auxiliares
psql -h -U -p -c "drop table schema.aux_tabla2_related1"
psql -h -U -p -c "drop table schema.aux_tabla2_related2"
psql -h -U -p -c "drop table schema.aux_tabla2_related3"

#Relleno de las auxiliares
psql -h -U -p -c "create table schema.aux_tabla2_related1 as select * from schema.tabla2_related1"
psql -h -U -p -c "create table schema.aux_tabla2_related2 as select * from schema.tabla2_related2"
psql -h -U -p -c "create table schema.aux_tabla2_related3 as select * from schema.tabla2_related3"

#Borrado de las relacionadas
psql -h -U -p -c "delete from schema.tabla2_related1"
psql -h -U -p -c "delete from schema.tabla2_related2"
psql -h -U -p -c "delete from schema.tabla2_related3"

#Borramos el contenido de las tablas deseadas
psql -h -U -p -c "delete from schema.dictionary"
psql -h -U -p -c "delete from schema.tabla4"
psql -h -U -p -c "delete from schema.tabla2"
psql -h -U -p -c "delete from schema.tabla1"

#Rellenamos las tablas deseadas
psql -h -U -p <  /tmp/fichero.dump
#Restauramos las relacionadas
psql -h -U -p -c "insert into schema.tabla2_related1 select * from schema.aux_tabla2_related1"
psql -h -U -p -c "insert into schema.tabla2_related2 select * from schema.aux_tabla2_related2"
psql -h -U -p -c "insert into schema.tabla2_related3 select * from schema.aux_tabla2_related3"


lunes, 5 de mayo de 2014

Problemas de recepción Wifi Dell XPS 9530

Si estando en modo batería, tenéis problemas de cobertura wifi con vuestro Wifi Dell XPS 9530. Probar a conectaros a la corriente.

Si enchufados los problemas desaparecen, es conveniente que cambiéis el modo en que funciona el adaptador inalámbrico.

En la siguiente ventana, probad con "Ahorro de Energía Bajo", si persisten los problemas cambiar a "Rendimiento máximo".



Activar sensor de iluminación en Dell XPS 9530

Para ahorrar batería o para no tener que modificar manualmente el brillo, podemos hacer uso del sensor de iluminación cuya activación está un poco oculta en las opciones de energía del equipo de Windows 8.
 
 

miércoles, 30 de abril de 2014

JSON Java Tomcat7

Desarrollando webservices JAX-RS que reciben JSON como parámetros de entrada y que entregan a su vez JSON, me encontré con la necesidad de parsear los JSON de entrada.


En anteriores servidores de aplicación y versiones anteriores de Java, utilicé Jackson, GSON y otros parseadores externos


Pero J2EE 7 proporciona una API JSON de forma nativa y no es necesario tener un "proveedor" externo.


Pero el API solo proporciona interfaces, sin implementación. Y depende cada servidor de aplicaciones la correcta implementación, en mi caso utilizo Tomcat 7 y parseando JSON recibía el siguiente error:


java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:297)
        at java.lang.Thread.run(Thread.java:724)
Caused by: javax.json.JsonException: Provider org.glassfish.json.JsonProviderImp
l not found        at javax.json.spi.JsonProvider.provider(JsonProvider.java:97)
        at javax.json.Json.createArrayBuilder(Json.java:257)
...


Dicho error se solucionó con la siguiente configuración de Maven:




    javax
    javaee-api
    7.0
    provided


    javax.json
    javax.json-api
    1.0


    org.glassfish
    javax.json
    1.0
    runtime

Aquí dejo una función de ejemplo, que  parsea un token de Facebook y extrae el campo id:


public int idTokenFacebook ( String token ) throws IOException {
 Integer id=-1;
 String  aux;
 try (InputStream is = url.openStream();
 JsonReader rdr = Json.createReader(is)) {
  JsonObject obj = rdr.readObject();
  if ( obj.containsKey("id")) {
   aux = obj.getString("id");
   id = Integer.valueOf(aux);
  } else {
   id =0;
  }
  System.out.print("id: "+id);
  return id;
 }
}


Indices únicos de campos de gran tamaño

Cuando queremos que una clave no esté duplicada entre las filas de alguna de nuestras tablas, es sencillo hacerlo, creamos una clave única (unique key) y nuestro gestor de bbdd tendrá cuidado de que no ocurra la duplicidad.


Pero que ocurre cuando la clave que queremos que sea única, mide más de 3.000 caracteres. Pues tenemos un problema porque en Postgresql, el máximo permitido son 2.000 y pico.


Se nos podría ocurrir, dividir el campo en dos campos y montar el índice sobre dichos campos. Pero obtendremos el mismo error.


La solución sería hacer un índice calculado, que fuese el hash del campo. El problema es que al ser un campo de más de 3.000 puede que tengamos colisiones indeseadas o falsos positivos.


CREATE UNIQUE INDEX uk_tabla
  ON esquema.tabla
  USING btree
  (
   md5(campo_unico)
  );


Para tratar de minorar el problema, podemos hacer un índice con un hash de parte de la clave (caracteres del 1-1500), compuesto por otro hash, con la parte restante de la clave (caracteres del 1501 al 3000). Así podríamos dividir cuantas veces queramos la clave, segmentando el índice, para hacer más difícil la colisión del índice.




CREATE UNIQUE INDEX uk_tabla_campo_unico
  ON esquema.tabla
  USING btree
  (
   md5(substr(campo_unico::text, 1, 1500)),
   md5(substr(campo_unico::text, 1501, 3000))
  );




Y en mysql. En este gestor tenemos un problema, no podemos aplicar la solución directamente, porque no existen los "índices calculados".


Por tanto la única solución para simularlos, es crear los campos calculados en la propia tabla. Y serán rellenados a través de triggers.


alter table tabla add trozo1 varchar(1500);
alter table tabla add trozo2 varchar(1500);


DELIMITER //
CREATE TRIGGER `tabla_insert` BEFORE INSERT ON `tabla` FOR EACH ROW BEGIN
 set NEW.trozo1  = sha1( SUBSTRING(new.campo_unico,1,1500));
 set NEW.trozo2 = sha1( SUBSTRING(new.campo_unico,1501));
end//
DELIMITER ;


CREATE UNIQUE INDEX uk_tabla_campo_unico
ON tabla
( trozo1, trozo2 );


Resumiendo si tenemos el problema de indexar de forma única un campo excesivamente largo, en Postgres tenemos la inestimable ayuda de la potencia de los índices calculados.


Por contra en Mysql tendremos que cambiar la estructura de la tabla y crear triggers que mantenga la información actualizada, y por último crear el índice, lo cual es bastante más incómodo a la vez que tedioso en la programación.













lunes, 17 de marzo de 2014

Busqueda de Esquemas en Postgres por defecto

Si teneis una base de datos con varios esquemas, llega un momento en que es complicado encontrar una tabla y es farragoso tener que escribir el nombre del esquema delante de cada tabla para acceder al contenido.

Una solución es modificar el search_path del usuario con el que te conectas a la base de datos, por ejemplo del usuario user_connect, conectado como superusuario:

alter user "user_connect" set search_path to "$user",public,master,soccer;

Pero es mucho mejor indicar el camino de acceso a nivel de base de datos, en lugar de hacerlo a nivel de usuario.

alter database "league" set search_path to "$user",public,master,soccer;

De esta forma todos los usuarios que se conecten a esta base de datos, podrán encontrar las tablas de forma cómoda, en los esquemas public, master, soccer y en un esquema con su propio nombre de usuario con el que se ha conectado.

jueves, 27 de febrero de 2014

Exportar una instancia



Comando para exportar mi instancia amazon a una imagen que poder cargar en un HyperV:

ec2addixt i-0cd6fd4c -e Microsoft -b export-ami-crom -f vhd -d "export i-0cd6fd4c -e Microsoft -b export-ami-crom" -O ...AKIA... -W ...feuC... --region eu-west-1 -U http://ec2.amazonaws.com/


Error
Client.NotExportable: Only imported instances can be exported.

Resulta que amazon no deja exportar ninguna instancia.

Solo puedes exportarla si la importastes en primer lugar sobre una máquina EC2.

No me ha gustado nada saber que solo puedo exportar lo que yo importé primeramente. Por lo tanto todas las AMIS del market de Amazon son un callejón sin salida, así que ojo.

domingo, 12 de enero de 2014

Bloque de comandos sin crear una función

Si necesitas hacer pruebas con un bloque de comandos de pgsql, sin tener que crear una función, esta es la forma:

do
$$declare
intervalo varchar;
fecha     timestamp;
begin
--fecha := ;
intervalo:= 1 || ' days';
fecha := current_timestamp + intervalo::interval;

insert into acumulado(usuario_id,f_fin,texto) values ( 1, fecha, 'texto'  );

end$$;