-
Tareas a realizar:
-
Crear la Base de Datos.
-
Crear las tablas respetando los nombres de tablas, atributos y tipos de datos especificados.
-
Agregar las PK’s y FK’s de todas las tablas, por separado de la creación de las tablas.
-
Permitir que el usuario pueda borrar todas las PK’s y FK’s, si así lo desea.
-
-
Se deberán cargar 20 clientes y 20 comercios. Todos los clientes tendrán una tarjeta, excepto dos clientes que tendrán dos tarjetas cada uno.
-
Una tarjeta deberá estar expirada en su fecha de vencimiento.
-
La tabla cierre deberá tener los cierres de las tarjetas para todo el año 2020.
El trabajo práctico deberá incluir los siguientes stored procedures ó triggers:
-
Autorización de compra se deberá incluir la lógica que reciba los datos de una compra—número de tarjeta, código de seguridad, número de comercio y monto—y que devuelva true si se autoriza la compra ó false si se rechaza. El procedimiento deberá validar los siguientes elementos antes de autorizar:
-
Que el número de tarjeta sea existente, y que corresponda a alguna tarjeta vigente. En caso de que no cumpla, se debe cargar un rechazo con el mensaje tarjeta no válida ó no vigente.
-
Que el código de seguridad sea el correcto. En caso de que no cumpla, se debe cargar un rechazo con el mensaje código de seguridad inválido.
-
Que el monto total de compras pendientes de pago más la compra a realizar no supere el límite de compra de la tarjeta. En caso de que no cumpla, se debe cargar un rechazo con el mensaje supera límite de tarjeta.
-
Que la tarjeta no se encuentre vencida. En caso de que no cumpla, se debe cargar un rechazo con el mensaje plazo de vigencia expirado.
-
Que la tarjeta no se encuentre suspendida. En caso que no cumpla, se debe cargar un rechazo con el mensaje la tarjeta se encuentra suspendida.
-
Si se aprueba la compra, se deberá guardar una fila en la tabla compra, con los datos de la compra.
-
-
Generación del resumen el trabajo práctico deberá contener la lógica que reciba como parámetros el número de cliente, y el periodo del año, y que guarde en las tablas que corresponda los datos del resumen con la siguiente información: nombre y apellido, dirección, número de tarjeta, periodo del resumen, fecha de vencimiento, todas las compras del periodo, y total a pagar.
-
Alertas a clientes el trabajo práctico deberá proveer la lógica que genere alertas por posibles fraudes. Existe un Call Centre que ante cada alerta generada automáticamente, realiza un llamado telefónico a el cliente, indicándole la alerta detectada, y verifica si se trató de un fraude ó no. Se supone que la detección de alertas se ejecuta automáticamente con cierta frecuencia—e.g. de una vez por minuto. Se pide detectar y almacenar las siguientes alertas:
-
Todo rechazo se debe ingresar automáticamente a la tabla de alertas. No puede haber ninguna demora para ingresar un rechazo en la tabla de alertas, se debe ingresar en el mismo instante en que se generó el rechazo.
-
Si una tarjeta registra dos compras en un lapso menor de un minuto en comercios distintos ubicados en el mismo código postal.
-
Si una tarjeta registra dos compras en un lapso menor de 5 minutos en comercios con diferentes códigos postales.
-
Si una tarjeta registra dos rechazos por exceso de límite en el mismo día, la tarjeta tiene que ser suspendida preventivamente, y se debe grabar una alerta asociada a este cambio de estado.
-
-
Se deberá crear una tabla con consumos virtuales para probar el sistema, la misma deberá contener los atributos: nrotarjeta, codseguridad, nrocomercio, monto. Y se deberá hacer un procedimiento de testeo, que pida autorización para todos los consumos virtuales.
-
Todo el código SQL escrito para este trabajo práctico, deberá poder ejecutarse desde una aplicación CLI escrita en Go.
Para poder comparar el modelo relacional con un modelo no relacional NoSQL, se deberá guardar los datos de clientes, tarjetas, comercios, y compras (tres por cada entidad) en una base de datos NoSQL basada en JSON. Para ello, utilizar la base de datos BoltDB. Este código, también deberá ejecutarse desde una aplicación CLI escrita en Go.
El programa está organizado en una carpeta y un archivo escrito en Go (main.go) que sirve como aplicación CLI:
-
Carpeta sql:
-
Contiene la lógica asociada a los ítems 1.1, 1.2 y 1.3. Las responsabilidades están separadas mediante los archivos: sql.go, tablas.go, datos.go, storedProcedures.go, triggers.go y testConsumo.go.
-
-
Descripción de los métodos del archivo main.go :
-
mostrarMenu(): Imprime el menu con todas las opciones disponibles a ejecutar. -
manejarOpciones(opcion int): Se encarga de ejecutar la función que corresponda a la opción que se ingresa e imprime el resultado por pantalla. -
BDnoSQL(): Se conecta y crea la base de datos no SQL e ingresa los datos de los tipos cliente, tarjeta, comercio y compra. Respetando el órden y tipo de datos de sus atributos. Carga tres filas de cada tipo.
-
func BDnoSQL() {
type Cliente struct {
Nrocliente int
Nombre string
Apellido string
Domicilio string
Telefono string
}
type Tarjeta struct {
Nrotarjeta string
Nrocliente int
Validadesde string
Validahasta string
Codseguridad string
Limitecompra int
Estado string
}
type Comercio struct {
Nrocomercio int
Nombre string
Domicilio string
Codigopostal string
Telefono string
}
type Compra struct {
Nrooperacion int
Nrotarjeta string
Nrocomercio int
Fecha string
Monto int
Pagado bool
}
db, err := bolt.Open("testBolt.db", 0600, nil)
if err != nil {
log.Fatal(err)
}
defer db.Close()
cliente1 := Cliente{33348679, "Sofía", "Godoy", "Av. Senador Morón 1221", "115598342"}
cliente2 := Cliente{44349773, "Abril", "Hernández", "Av. Sourdeaux 1700", "115598342"}
cliente3 := Cliente{14348789, "Ricardo", "Llanos", "Corrientes 183", "119034572"}
comercio1 := Comercio{564, "FOX", "Av Pres. Juan Domingo Perón 907", "1663", "46676777"}
comercio2 := Comercio{523, "47 street", "Paunero 1575", "1663", "47597581"}
comercio3 := Comercio{553, "Disco", "Av. Senador Morón 960", "1661", "08107778888"}
tarjeta1 := Tarjeta{"4000001234567899", 11348773, "201508", "202008", "733", 50000, "vigente"}
tarjeta2 := Tarjeta{"4037001554363655", 12349972, "201507", "202007", "332", 55000, "vigente"}
tarjeta3 := Tarjeta{"4000001355435322", 22648991, "201507", "202007", "201", 60000, "vigente"}
compra1 := Compra{1, "4000001234567899", 501, "2020-04-25 00:00:00", 1500.00, true}
compra2 := Compra{2, "4000001234567899", 513, "2020-04-27 00:00:00", 4500.00, true}
compra3 := Compra{3, "4000001234567899", 523, "2020-04-30 00:00:00", 850.00, true}
dataCliente1, err := json.Marshal(cliente1)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "cliente", []byte(strconv.Itoa(cliente1.Nrocliente)), dataCliente1)
//resultadoCliente1, err := ReadUnique(db, "cliente", []byte(strconv.Itoa(cliente1.Nrocliente)))
//fmt.Printf("%s\n", resultadoCliente1)
dataCliente2, err := json.Marshal(cliente2)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "cliente", []byte(strconv.Itoa(cliente2.Nrocliente)), dataCliente2)
//resultadoCliente2, err := ReadUnique(db, "cliente", []byte(strconv.Itoa(cliente2.Nrocliente)))
//fmt.Printf("%s\n", resultadoCliente2)
dataCliente3, err := json.Marshal(cliente3)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "cliente", []byte(strconv.Itoa(cliente3.Nrocliente)), dataCliente3)
//resultadoCliente3, err := ReadUnique(db, "cliente", []byte(strconv.Itoa(cliente3.Nrocliente)))
//fmt.Printf("%s\n", resultadoCliente3)
dataComercio1, err := json.Marshal(comercio1)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "comercio", []byte(strconv.Itoa(comercio1.Nrocomercio)), dataComercio1)
//resultadoComercio1, err := ReadUnique(db, "comercio", []byte(strconv.Itoa(comercio1.Nrocomercio)))
//fmt.Printf("%s\n", resultadoComercio1)
dataComercio2, err := json.Marshal(comercio2)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "comercio", []byte(strconv.Itoa(comercio2.Nrocomercio)), dataComercio2)
//resultadoComercio2, err := ReadUnique(db, "comercio", []byte(strconv.Itoa(comercio2.Nrocomercio)))
//fmt.Printf("%s\n", resultadoComercio2)
dataComercio3, err := json.Marshal(comercio3)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "comercio", []byte(strconv.Itoa(comercio3.Nrocomercio)), dataComercio3)
//resultadoComercio3, err := ReadUnique(db, "comercio", []byte(strconv.Itoa(comercio3.Nrocomercio)))
//fmt.Printf("%s\n", resultadoComercio3)
dataTarjeta1, err := json.Marshal(tarjeta1)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "tarjeta", []byte(tarjeta1.Nrotarjeta), dataTarjeta1)
// resultadoTarjeta1, err := ReadUnique(db, "tarjeta", []byte(tarjeta1.Nrotarjeta))
//fmt.Printf("%s\n", resultadoTarjeta1)
dataTarjeta2, err := json.Marshal(tarjeta2)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "tarjeta", []byte(tarjeta2.Nrotarjeta), dataTarjeta2)
// resultadoTarjeta2, err := ReadUnique(db, "tarjeta", []byte(tarjeta2.Nrotarjeta))
// fmt.Printf("%s\n", resultadoTarjeta2)
dataTarjeta3, err := json.Marshal(tarjeta3)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "tarjeta", []byte(tarjeta3.Nrotarjeta), dataTarjeta3)
// resultadoTarjeta3, err := ReadUnique(db, "tarjeta", []byte(tarjeta3.Nrotarjeta))
//fmt.Printf("%s\n", resultadoTarjeta3)
dataCompra1, err := json.Marshal(compra1)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "compra", []byte(strconv.Itoa(compra1.Nrooperacion)), dataCompra1)
// resultadoCompra1, err := ReadUnique(db, "compra", []byte(strconv.Itoa(compra1.Nrooperacion)))
// fmt.Printf("%s\n", resultadoCompra1)
dataCompra2, err := json.Marshal(compra2)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "compra", []byte(strconv.Itoa(compra2.Nrooperacion)), dataCompra2)
// resultadoCompra2, err := ReadUnique(db, "compra", []byte(strconv.Itoa(compra2.Nrooperacion)))
//fmt.Printf("%s\n", resultadoCompra2)
dataCompra3, err := json.Marshal(compra3)
if err != nil {
log.Fatal(err)
}
CreateUpdate(db, "compra", []byte(strconv.Itoa(compra3.Nrooperacion)), dataCompra3)
// resultadoCompra3, err := ReadUnique(db, "compra", []byte(strconv.Itoa(compra3.Nrooperacion)))
// fmt.Printf("%s\n", resultadoCompra3)
}-
CreateUpdate(db, bucketName, key, value): Abre una transacción de escritura, crea el bucket si no existe con el nombre indicado. Escribe los datos key, value especificados en el bucket y luego cierra la transacción.
func CreateUpdate(db *bolt.DB, bucketName string, key []byte, value []byte) error {
tx, err := db.Begin(true)
if err != nil {
return err
}
defer tx.Rollback()
b, _ := tx.CreateBucketIfNotExists([]byte(bucketName))
err = b.Put(key, value)
if err != nil {
return err
}
if err := tx.Commit(); err != nil {
return err
}
return nil
}-
ReadUnique(db, bucketName, key): Abre una transacción de lectura con el nombre del bucket pasado como parámetro, y obtiene los datos asociados a la clave especificada.
func ReadUnique(db *bolt.DB, bucketName string, key []byte) ([]byte, error) {
var buf []byte
err := db.View(func(tx *bolt.Tx) error {
b := tx.Bucket([]byte(bucketName))
buf = b.Get(key)
return nil
})
return buf, err
}-
Descripción de las funciones del archivo sql.go :
-
DbConnection(): Conexión a la base de datos. -
CrearDB(): Creación la base de datos tarjeta. Invoca a la funcióncrearDB(). -
BorrarBD(): Borra la base de datos tarjeta. Invoca a la funciónBorrarDB(). -
CrearTablas(): Crea las tablas invocando a la funcióncrearTablas(). -
BorrarTablas(): Borra las tablas invocando a la funciónborrarTablas(). -
CrearPKsyFKs(): Invoca a las funcionescrearPKs()ycrearFKs()para la creación de las PK’s y las FK’s. -
BorrarPKsyFKs(): Invoca a las funcionesborrarFKs()y _borrarPKs()` para eliminar las FK’s y las PK’s. -
CargarDatos(): Invoca a la funcióncargarDatos()y a la funciónInsertarCierres(). -
BorrarDatos(): Invoca a la funciónborrarDatos(). -
ProbarConsumo(): Invoca a las funcionesautorizacionCompra(),crearTriggers(),generarConsumos()ytestFunciones(). -
ProbarResumen(): Invoca a las funcionesgenerarResumen()ytestGenResumen().
-
-
Descripción de las funciones del archivo tablas.go :
-
crearTablas(): Creación de todas las tablas. -
borrarTablas(): Borra todas las tablas. -
crearPKs(): Creación de las PK’s de cada tabla. -
crearFKs(): Creación de las FK’s de cada tabla. -
eliminarPKs(): Elimina las PK’s de cada tabla. -
eliminarFKs(): Elimina las FK’s de cada tabla.
-
-
Descripción de las funciones del archivo datos.go :
-
cargarDatos(): Carga la cantidad de datos requerida en las tablas: cliente , comercio y tarjeta. Genera los cierres para el año 2020, invocando aInsertarCierres(). -
borrarDatos(): Borra todos los datos almacenados en las diferentes tablas. -
InsertarCierres(): Ejecuta la funcióninsertarCierres()que crea la stored procedureinsertCierres()que genera todos los cierres del año 2020. Luego, realiza una consulta a esa Stored Procedure.
-
-
Descripción de las funciones del archivo storedProcedures.go :
-
insertarCierres(): Crea el stored procedure encargado de generar los cierres del año 2020. Recorre las 10 posibles terminaciones de tarjetas, los 12 meses del año y crea los cierres.
-
//generate_series() genera series según el argumento pasado. Para cada ciclo del for genera valores distintos.
func insertarCierres() {
_, err = db.Query(`
CREATE OR REPLACE FUNCTION insertcierres() RETURNS void AS $$
BEGIN
FOR i in 0..9 LOOP
INSERT INTO cierre VALUES(2020,generate_series(1,12),i,
generate_series('2020/01/01'::date,'2020/12/31','1 month'),
generate_series('2020/01/28'::date,'2020/12/31','1 month'),
generate_series('2020/01/28'::date,'2020/12/31','1 month')
);
END LOOP;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
}-
autorizacionCompra(): Contiene la lógica asociada a la autorización de una compra. Invoca a los stored procedurescargar_rechazo(numtarjeta, numcomercio, montocompra, mensaje), yautorizacion_compra(numtarjeta, codseg, numcomercio, montocompra). -
autorizacion_compra(numtarjeta, codseg, numcomercio, montocompra): Crea un stored procedure que toma como parámetros el número de una tarjeta, su código de seguridad, el número de un comercio, y el monto de la compra. Devuelve true si la compra fue autorizada y false si fue rechazada. -
cargar_rechazo(numtarjeta, numcomercio, montocompra, mensaje): Crea un stored procedure que toma como parámetros el número de una tarjeta, el número de un comercio, el monto de la compra y un mensaje. Inserta en la tabla rechazo los valores antes mencionados.
func autorizacionCompra() {
_, err = db.Query(`
CREATE OR REPLACE FUNCTION cargar_rechazo(numtarjeta char(16), numcomercio int, montocompra decimal(7,2), mensaje text) RETURNS void AS $$
BEGIN
INSERT INTO rechazo VALUES(nextval('seq_nrorechazo'), numtarjeta, numcomercio, CURRENT_TIMESTAMP, montocompra, mensaje);
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
_, err = db.Query(`
CREATE OR REPLACE FUNCTION chequear_cantidad_rechazos(numtarjeta char(16)) RETURNS void AS $$
DECLARE
cantidad_rechazos int;
BEGIN
SELECT COUNT(numtarjeta) INTO cantidad_rechazos FROM rechazo WHERE nrotarjeta = numtarjeta AND motivo ='supera limite de tarjeta' AND DATE_PART('day', fecha) = DATE_PART('day', CURRENT_TIMESTAMP);
IF cantidad_rechazos > 1 THEN
UPDATE tarjeta SET estado = 'suspendida' where nrotarjeta = numtarjeta;
INSERT INTO alerta VALUES(nextval('seq_nroalerta'), numtarjeta, CURRENT_TIMESTAMP, null, 32, 'suspencion preventiva');
END IF;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
_, err = db.Query(`
CREATE OR REPLACE FUNCTION autorizacion_compra(numtarjeta char(16), codseg char(4), numcomercio int, montocompra decimal(7,2)) RETURNS boolean AS $$
DECLARE
tarj record;
monto_compras_pendientes int;
monto_total int;
ano_actual char(6);
mes_actual char(6);
fecha_actual char(6);
BEGIN
------------------
-- Caso 1 --
--Numero tarjeta inexistente--
SELECT * INTO tarj FROM tarjeta WHERE nrotarjeta = numtarjeta;
IF not found THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'tarjeta no valida o no vigente');
return false;
END IF;
--Tarjeta no esta vigente--
IF tarj.estado != 'vigente' AND tarj.estado != 'suspendida' THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'tarjeta no valida o no vigente');
return false;
END IF;
-- --
------------------
------------------
-- Caso 5 --
--Tarjeta suspendida--
IF tarj.estado = 'suspendida' THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'la tarjeta se encuentra suspendida');
return false;
END IF;
-- --
------------------
------------------
-- Caso 2 --
-- Codigo de seguridad incorrecto --
IF tarj.codseguridad != codseg THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'codigo de seguridad invalido');
return false;
END IF;
-- --
------------------
------------------
-- Caso 3 --
------------------
-- Caso 4 --
-- Tarjeta vencida --
SELECT DATE_PART('year', (SELECT CURRENT_DATE)) INTO ano_actual;
SELECT DATE_PART('month', (SELECT CURRENT_DATE)) INTO mes_actual;
fecha_actual := ano_actual || mes_actual;
IF tarj.validahasta < fecha_actual THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'plazo de vigencia expirado');
return false;
END IF;
-- --
------------------
-- Limite de compra superado --
SELECT SUM(monto) INTO monto_compras_pendientes FROM compra WHERE tarj.nrotarjeta = numtarjeta AND pagado = false;
monto_total := monto_compras_pendientes + montocompra;
IF tarj.limitecompra < monto_total THEN
PERFORM cargar_rechazo(CAST(numtarjeta AS char(16)), CAST(numcomercio AS int), CAST(montocompra AS decimal(7,2)), 'supera limite de tarjeta');
PERFORM chequear_cantidad_rechazos(CAST(numtarjeta AS char(16)));
return false;
END IF;
-- --
------------------
------------------
--Compra exitosa--
INSERT INTO compra VALUES(nextval('seq_nrocompra'), numtarjeta, numcomercio, CURRENT_TIMESTAMP, montocompra, false);
-- --
------------------
return true;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
}-
crearTriggers(): Invoca a las funcionescargar_alerta()ytriggerstiempo(). -
cargar_alerta(): Crea el stored procedurecargar_alerta()que agrega una alerta con los valores insertados en la tabla rechazo. Con el codalerta correspondiente.
func cargar_alerta() {
_, err = db.Query(`
CREATE OR REPLACE FUNCTION cargar_alerta() RETURNS trigger AS $$
DECLARE
cantidad_rechazos int;
BEGIN
INSERT INTO alerta VALUES(nextval('seq_nroalerta'), new.nrotarjeta, new.fecha, new.nrorechazo, 0, new.motivo);
SELECT COUNT(new.nrotarjeta) INTO cantidad_rechazos FROM rechazo WHERE nrotarjeta = new.nrotarjeta AND motivo ='supera limite de tarjeta' AND DATE_PART('day', fecha) = DATE_PART('day', CURRENT_TIMESTAMP);
IF cantidad_rechazos > 1 THEN
UPDATE tarjeta SET estado = 'suspendida' where nrotarjeta = new.nrotarjeta;
INSERT INTO alerta VALUES(nextval('seq_nroalerta'), new.nrotarjeta, CURRENT_TIMESTAMP, null, 32, 'suspencion preventiva');
END IF;
return new;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
trgCargarAlerta()
}-
triggerstiempos(): Crea el stored procedurecompras_tiempo(). Esta función chequea que la diferencia de tiempo entre las compras de una misma tarjeta. En caso de detectar dos compras en comercios con el mismo código postal y con tiempo menor a un minuto, se inserta una alerta con codalerta 1 y el mensaje 'Compra en menos de 1 minuto en una misma zona'. En caso de detectar dos compras con lapso menor a 5 minutos con diferentes códigos postales se inserta una nueva alerta con codalerta 5 y el mensaje 'Compra en menos de 5 minutos en diferentes zonas'.
func triggerstiempo() {
_, err = db.Query(`
CREATE OR REPLACE FUNCTION compras_tiempo() RETURNS trigger AS $$
DECLARE
ultima_compra record;
diferencia_tiempo decimal;
cod_postal_anterior int;
cod_postal_actual int;
BEGIN
SELECT * INTO ultima_compra FROM compra WHERE nrotarjeta = new.nrotarjeta ORDER BY nrooperacion DESC LIMIT 1;
IF not found THEN
return new;
END IF;
SELECT INTO diferencia_tiempo EXTRACT(EPOCH FROM (new.fecha - ultima_compra.fecha)) / 60;
SELECT codigopostal INTO cod_postal_anterior FROM comercio WHERE nrocomercio = ultima_compra.nrocomercio;
SELECT codigopostal INTO cod_postal_actual FROM comercio WHERE nrocomercio = new.nrocomercio;
--Alerta por compras en menos de 1 minuto comercios con el mismo codigo postal
IF diferencia_tiempo < 1 and ultima_compra.nrocomercio != new.nrocomercio and cod_postal_anterior = cod_postal_actual THEN
INSERT INTO alerta VALUES(nextval('seq_nroalerta'), new.nrotarjeta, CURRENT_TIMESTAMP, null, 1, 'compra en menos de 1 minuto en una misma zona');
return new;
END IF;
--Alerta por compras en menos de 5 minutos en comercios con diferentes codigos postales
IF diferencia_tiempo < 5 and ultima_compra.nrocomercio != new.nrocomercio and cod_postal_anterior != cod_postal_actual THEN
INSERT INTO alerta VALUES(nextval('seq_nroalerta'),new.nrotarjeta, CURRENT_TIMESTAMP, null, 5, 'compra en menos de 5 minutos en diferentes zonas');
return new;
END IF;
return new;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
trgTiempoCompras()
}-
generarresumen(numCliente, mesIN): Crea un stored procedure que toma como parámetros el número que tiene asociado el cliente y el número de mes asociado al período que se quiera generar en el resumen. Inserta en la tabla cabecera el número de resumen, luego nombre, apellido, domicilio y número de tarjeta del cliente, se ingresan los cierres correspondiente a la terminación del numero de tarjeta y el mes pasado por parámetro. Y por último, el monto final que tiene que pagar el cliente en dicho período. Por otra parte, se inserta en la tabla detalle las compras (junto a sus fechas, montos y comercios) realizadas entre las fechas de 'desde' y 'hasta' de la tabla cabecera.
func generarResumen() {
_, err = db.Query(`
CREATE OR REPLACE FUNCTION generarresumen(numCliente int, mesIN int, anioIN int) RETURNS void AS $$
DECLARE
clienteDEC record;
tarjetaDEC record;
contResumen int;
nomComercioDEC record;
compraDEC record;
contLinea int;
montofinal decimal(8,2);
cierreTarjetaDEC record;
BEGIN
contLinea := 1;
montofinal := 0;
SELECT * INTO clienteDEC FROM cliente WHERE nrocliente = numCLiente;
FOR tarjetaDEC IN SELECT * FROM tarjeta WHERE nrocliente = numCLiente LOOP
SELECT * INTO cierreTarjetaDEC FROM cierre WHERE mes = mesIN and año = anioIN and terminacion = substring(tarjetaDEC.nrotarjeta,16)::int;
contResumen := 0;
contResumen := contResumen + count(*) from cabecera;
INSERT INTO cabecera VALUES (contResumen + 1,
clienteDEC.nombre,
clienteDEC.apellido,
clienteDEC.domicilio,
tarjetaDEC.nrotarjeta,
cierreTarjetaDEC.fechainicio,
cierreTarjetaDEC.fechacierre,
cierreTarjetaDEC.fechavto,
montofinal
);
FOR compraDEC IN SELECT * FROM compra WHERE nrotarjeta = tarjetaDEC.nrotarjeta AND pagado = false AND fecha::date >= cierreTarjetaDEC.fechainicio AND fecha::date <= cierreTarjetaDEC.fechacierre
LOOP
SELECT * INTO nomComercioDEC FROM comercio WHERE nrocomercio = compraDEC.nrocomercio;
INSERT INTO detalle VALUES (contResumen + 1,
contLinea,
compraDEC.fecha,
nomComercioDEC.nombre,
compraDEC.monto
);
contLinea := contLinea + 1;
montofinal := montofinal + compraDEC.monto;
UPDATE compra SET pagado = true WHERE nrooperacion = compraDEC.nrooperacion;
END LOOP;
UPDATE cabecera SET total = montofinal WHERE nrotarjeta = tarjetaDEC.nrotarjeta AND desde = cierreTarjetaDEC.fechainicio AND hasta = cierreTarjetaDEC.fechacierre;
END LOOP;
END
$$ LANGUAGE PLPGSQL;`)
if err != nil {
log.Fatal(err)
}
}-
Descripción de los métodos del archivo triggers.go :
-
trgCargarAlerta(): Crea el trigger cargaralerta_trg que luego de cada insert en la tabla rechazo, ejecuta el stored procedurecargar_alerta(). Esto es para que se agreguen todas las alertas de rechazo, con el código de alerta correspondiente.
-
func trgCargarAlerta() {
_, err = db.Query(
` DROP TRIGGER IF EXISTS cargaralerta_trg ON rechazo;
CREATE trigger cargaralerta_trg
AFTER INSERT ON rechazo
FOR EACH ROW
EXECUTE PROCEDURE cargar_alerta();`)
if err != nil {
log.Fatal(err)
}
}-
trgTiempoCompras(): Crea el trigger tiempo_compras_trg que antes de cada insert en la tabla compra, ejecuta el stored procedurecompras_tiempo()para comprobar si se ha generado una alerta de código 1 o 5.
func trgTiempoCompras() {
_, err = db.Query(
` DROP TRIGGER IF EXISTS tiempo_compras_trg ON compra;
CREATE trigger tiempo_compras_trg
BEFORE INSERT ON compra
FOR EACH ROW
EXECUTE PROCEDURE compras_tiempo();`)
if err != nil {
log.Fatal(err)
}
}-
Descripción de los métodos del archivo testConsumo.go :
-
generarConsumos(): Inserta los datos de los distintos consumos en la tabla consumo. -
testFunciones(): Invoca a las funcionesconsumir(),testCompra(),testAutorizaciones(),testAlertas()ytestAll(). -
consumir(): Genera los consumos virtuales tomando los datos de la tabla consumo. -
testAll(): Crea el stored proceduretest_all()que retorna true si todos los tests fueron ejecutados exitosamente. -
testCompra(): Crea el stored proceduretest_compras()que retorna true si las compras generadas por los consumos virtuales fueron exitosas. -
testAutorizaciones(): Crea el stored proceduretest_autorizaciones()que retorna true si los rechazos correspondientes a los consumos virtuales se generaron de la manera esperada. -
testAlertas(): Crea el stored proceduretest_alertas()que retorna true si las alertas correspondientes a los consumos virtuales fueron generadas como se esperaba.
-
-
Descripción de los métodos del archivo testResumen.go :
-
testGenResumen(): Invoca al stored proceduregenerarresumen()pasandole como parámetros el número del cliente y el período que se espera en el resumen. Genera los resumenes a partir de lo que se encuentre en la tabla compras. -
testResultCabecera(): Crea el stored proceduretestCabecera()que retorna true si el completado de la tabla cabecera era como se esperaba. -
testResultDetalle(): Crea el stored proceduretestDetalle()que retorna true si el completado de la tabla detalle era como se esperaba dependiendo de las fechas y si el cliente pago o no el producto.
-
Durante la resolución del trabajo se nos fueron presentando diferentes dificultades a la hora de generar las soluciones en código. Estas fueron solucionadas consultando repetidas veces a la documentacion oficial de postgresql.
Como conclusión final, podemos decir que a pesar de los problemas surgidos durante el desarrollo del trabajo práctico pudimos, con esfuerzo y dedicación, lograr cumplir con todos los puntos planteados.
Se pueden observar algunas diferencias entre las bases de datos SQL y noSQL. En primer lugar, SQL permite combinar de forma eficiente diferentes tablas para extraer información relacionada, mientras que NoSQL no lo permite o muy limitadamente. En segundo lugar, NoSQL permite distribuir grandes cantidades de información, mientras que SQL facilita distribuir bases de datos relacionales. Por último, SQL permite gestionar los datos junto con las relaciones existentes entre ellos, en NoSQL no existe este tipo de utilidades.