Re: Ejecución automática por tiempo de procedimientos.

Pablo Braulio dijo [Tue, Dec 11, 2007 at 06:14:36PM +0100]:
> > ¿Permites una sóla sesión por usuario, o varias? Bueno, te muestro acá
> > lo que yo haría para una sóla - extenderlo es trivial (y recomendable)
> > ;-)
> >
> > CREATE TABLE usuario (
> > id serial PRIMARY KEY,
> > login text NOT NULL UNIQUE,
> > passwd char(32) NOT NULL,
> > nombre text,
> > sesion char(32),
> > sesion_inicio timestamp default now()
> > );
> Hola.
>
> Lo que pretendo es limitar el acceso de los usuarios a una sóla sesión.
> Es decir que un usuario sólo pueda iniciarla una sóla vez
> simultaneamente, que el mismo usuario no esté trabajando en dos sitios
> al mismo tiempo.
>
> Espero haberme explicado bien.

Ok, perfecto - Veo que eres novato, escribo un poquito más a detalle.

Podría funcionarte tal como lo escribí, pero podría ser deseable que
la información la tuvieras en una tabla aparte - el principal
escenario donde habría diferencia es en la eficiencia al eliminar
registros expirados - pero bueno, como según mi función vas a hacer
esto cada que recibas una sesión, se vuelve asunto crítico. Puede que
esté equivocado, pero creo que es más barato marcar registros para su
eliminación (con DELETE) que realmente actualizarlos para marcar los
campos como nulos (como te lo había escrito en el ejemplo
anterior). Entonces, sugiero que la definición de las tablas quede:

CREATE TABLE usuario (
id serial PRIMARY KEY,
login text NOT NULL UNIQUE,
passwd char(32) NOT NULL,
nombre text
);

CREATE TABLE sesion (
sesion char(32) PRIMARY KEY,
id_usuario integer NOT NULL REFERENCES usuario(id),
ultimo_uso timestamp NOT NULL default now()
);
CREATE INDEX ultimo_uso_sesion ON sesion (ultimo_uso);
CREATE UNIQUE INDEX usuario_sesion ON sesion(id_usuario);

Con todo y el índice sobre la hora de último uso de sesión (sí, cambió
la nomenclatura: No importa en realidad el último inicio de sesión,
sino que la última vez que la usaste). Además, notarás un segundo
índice, usuario_sesion. ¿Para qué? Porque tienes el requisito de que
cada usuario tenga una sóla sesión.

Y la función queda:

CREATE OR REPLACE FUNCTION sesion_valida(text) RETURNS integer AS
$$
DECLARE
la_sesion ALIAS FOR $1;
duracion interval;
user_id integer;
BEGIN
duracion = '10 min'::interval;
-- Antes de otra cosa, vaciamos todas las sesiones expiradas
DELETE FROM sesion WHERE ultimo_uso+duracion < now();
-- Buscamos a qué usuario pertenece esta sesión
SELECT INTO user_id id_usuario FROM sesion WHERE sesion = la_sesion;
IF NOT FOUND THEN
RETURN NULL;
END IF;
UPDATE sesion SET ultimo_uso=now() WHERE sesion = la_sesion;
RETURN user_id;
END;
$$ LANGUAGE 'plpgsql';

¿Sí notas la direfencia? Acá sólo operao sobre la tabla sesion, ya no
sobre la tabla usuario. (y disculpa la errata: En la vuelta anterior,
estaba dándote un «RETURN la_sesion» cuando debe ser claramente
«RETURN user_id»).

Le agregué además una cláusula (¡todo en nombre del encapsulamiento!
Evita que la lógica de autenticación y manejo de sesiones esté regada
por todos lados) para que, al validar la sesión, automáticamente la
refresque.

> Viendo la tabla que creas, tengo la duda de que almacenas en el campo
> sesion. ¿el identificador de la sesion de php?. :-(

No sé, no conozco PHP... No sé qué tan confiable sea ese identificador
de sesión, ni cuál sea su semántica. Pero bueno, lo que te sugiero es
que generes las sesiones también desde Postgres. ¿Cómo? Así:

CREATE OR REPLACE FUNCTION crea_sesion(integer) RETURNS char(32) AS
$$
DECLARE
user_id ALIAS FOR $1;
nueva_sesion char(32);
BEGIN
DELETE FROM sesion WHERE id_usuario = user_id;
nueva_sesion = md5(now()::text||user_id::text);
INSERT INTO sesion (sesion, id_usuario, ultimo_uso) VALUES
(nueva_sesion, user_id, now());
RETURN nueva_sesion;
END;
$$ LANGUAGE 'plpgsql';

¿Qué es eso de «md5(now()::text||user_id::text)»? Simplemente una
manera de obtener 32 caracteres pseudoaleatorios, suficientemente
confiable como para usar como llave de sesión. De hecho, probablemente
quieras usar algo similar para guardar la contraseña únicamente
cifrada, no en texto claro - algo así como md5(id, contraseña) - pero
esa queda como ejercicio para el lector. Por ahora, creo una
contraseña (porque la BD la exige), pero no la uso.

> Luego, ¿como ejecutas la función?. Perdona pero estoy muy verde con las
> funciones.

Una vez que creaste las funciones, puedes usarlas tal como usarías
cualquier otra fuente de datos para un SELECT. Va, pues, un ejemplo -
en interactivo desde la consola psql, con las tablas y funciones
recién creadas. Primero creamos un usuario:

| mi_base=# INSERT INTO usuario (login, passwd) VALUES ('fulano', 'a');
| INSERT 0 1
| mi_base=# SELECT * FROM usuario;
| id | login | passwd | nombre
| ----+--------+----------------------------------+--------
| 1 | fulano | a |
| (1 row)

Claro está, la tabla de sesiones está vacía a estas alturas:

| mi_base=# SELECT * FROM sesion;
| sesion | id_usuario | ultimo_uso
| --------+------------+------------
| (0 rows)

¿Cómo puedo crear una sesión?

| mi_base=# SELECT crea_sesion(1);
| crea_sesion
| ----------------------------------
| 8faaabf6d5e192c32dcd54f3fe06021e
| (1 row)

Me regresó el ID de la sesión creada - Éste lo alimento a la lógica de
mi programa, hago lo que tenga que hacer... Podemos verificar ahora el
contenido de la tabla:

| mi_base=# SELECT * FROM sesion;
| sesion | id_usuario | ultimo_uso
| ----------------------------------+------------+---------------------------
| 8faaabf6d5e192c32dcd54f3fe06021e | 1 | 2007-12-11 16:59:32.67608
| (1 row)

Y ya luego, cuando el
usuario mande de nuevo una solicitud, paso de vuelta su sesión:

| mi_base=# SELECT sesion_valida('8faaabf6d5e192c32dcd54f3fe06021e');
| sesion_valida
| ---------------
| 1
| (1 row)

¡Listo! Me entregó el ID del usuario en cuestión. Ahora, ¿qué pasa
después de haber invocado a sesion_valida? Observa al ultimo_uso:

| mi_base=# SELECT * FROM sesion;
| sesion | id_usuario | ultimo_uso
| ----------------------------------+------------+----------------------------
| 8faaabf6d5e192c32dcd54f3fe06021e | 1 | 2007-12-11 17:00:38.711794
| (1 row)

Por último, ¿cómo manejas las sesiones expiradas? Sencillo: Vamos a
actualizar la sesión. Recuerda que la tenemos a 10 minutos.

| mi_base=# UPDATE sesion SET ultimo_uso = now()-'1 hour'::interval;
| UPDATE 1
| mi_base=# SELECT sesion_valida('8faaabf6d5e192c32dcd54f3fe06021e');
| sesion_valida
| ---------------
|
| (1 row)
|
| mi_base=# SELECT * FROM sesion;
| sesion | id_usuario | ultimo_uso
| --------+------------+------------
| (0 rows)

Como la sesión ya expiró, la eliminó del sistema. Y no sólo la de este
usuario, sino que todas las demás sesiones ya expiradas.

Espero que te sea de utilidad.

¡Ah! Claro, faltaba decirte: Para hacer esto, tienes que incluir el
soporte para el lenguaje plpgsql. Para esto (desde cualquier sistema
tipo Unix, desde fuera de psql):

$ createlang plpgsql mi_base

Saludos,

--
Gunnar Wolf - gwolf(at)gwolf(dot)org - (+52-55)5623-0154 / 1451-2244
PGP key 1024D/8BB527AF 2001-10-23
Fingerprint: 0C79 D2D1 2C4E 9CE4 5973 F800 D80E F35A 8BB5 27AF