Bases de Datos I: Trabajo Práctico

1. Introducción

1.1. Creación de la Base de Datos

• 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.

1.2. Instancia de los datos

• 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.

1.3. Stored procedures o triggers

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.

1.4. JSON y Base de datos NoSQL

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.

2. Descripción

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.

3. Implementación

• 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ón crearDB().

  • BorrarBD() : Borra la base de datos tarjeta. Invoca a la función BorrarDB().

  • CrearTablas() : Crea las tablas invocando a la función crearTablas().

  • BorrarTablas() : Borra las tablas invocando a la función borrarTablas().

  • CrearPKsyFKs() : Invoca a las funciones crearPKs() y crearFKs() para la creación de las PK’s y las FK’s.

  • BorrarPKsyFKs() : Invoca a las funciones borrarFKs() y _borrarPKs()` para eliminar las FK’s y las PK’s.

  • CargarDatos() : Invoca a la función cargarDatos() y a la función InsertarCierres().

  • BorrarDatos() : Invoca a la función borrarDatos().

  • ProbarConsumo() : Invoca a las funciones autorizacionCompra(), crearTriggers(), generarConsumos() y testFunciones().

  • ProbarResumen() : Invoca a las funciones generarResumen() y testGenResumen().

• 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 a InsertarCierres().

  • borrarDatos() : Borra todos los datos almacenados en las diferentes tablas.

  • InsertarCierres() : Ejecuta la función insertarCierres() que crea la stored procedure insertCierres() 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 procedures cargar_rechazo(numtarjeta, numcomercio, montocompra, mensaje), y autorizacion_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 funciones cargar_alerta() y triggerstiempo().

• cargar_alerta() : Crea el stored procedure cargar_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 procedure compras_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 procedure cargar_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 procedure compras_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 funciones consumir(), testCompra(), testAutorizaciones(), testAlertas() y testAll().

  • consumir() : Genera los consumos virtuales tomando los datos de la tabla consumo.

  • testAll() : Crea el stored procedure test_all() que retorna true si todos los tests fueron ejecutados exitosamente.

  • testCompra() : Crea el stored procedure test_compras() que retorna true si las compras generadas por los consumos virtuales fueron exitosas.

  • testAutorizaciones() : Crea el stored procedure test_autorizaciones() que retorna true si los rechazos correspondientes a los consumos virtuales se generaron de la manera esperada.

  • testAlertas() : Crea el stored procedure test_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 procedure generarresumen() 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 procedure testCabecera() que retorna true si el completado de la tabla cabecera era como se esperaba.

  • testResultDetalle() : Crea el stored procedure testDetalle() 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.

4. Conclusiones

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.