Anexo A. Scripts creados para el caso práctico

PostgreSQL

Creación de tablas

actor

CREATE TABLE actor
(
  actor_id integer NOT NULL DEFAULT nextval('actor_actor_id_seq'::regclass),
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
)

address

CREATE TABLE address
(
  address_id integer NOT NULL DEFAULT nextval('address_address_id_seq'::regclass),
  address character varying(50) NOT NULL,
  address2 character varying(50),
  district character varying(20) NOT NULL,
  city_id smallint NOT NULL,
  postal_code character varying(10),
  phone character varying(20) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT address_pkey PRIMARY KEY (address_id),
  CONSTRAINT fk_address_city FOREIGN KEY (city_id)
      REFERENCES city (city_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

category

CREATE TABLE category
(
  category_id integer NOT NULL DEFAULT nextval('category_category_id_seq'::regclass),
  name character varying(25) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT category_pkey PRIMARY KEY (category_id)
)

city

CREATE TABLE city
(
  city_id integer NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
  city character varying(50) NOT NULL,
  country_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT city_pkey PRIMARY KEY (city_id),
  CONSTRAINT fk_city FOREIGN KEY (country_id)
      REFERENCES country (country_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

country

CREATE TABLE country
(
  country_id integer NOT NULL DEFAULT nextval('country_country_id_seq'::regclass),
  country character varying(50) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT country_pkey PRIMARY KEY (country_id)
)

customer

CREATE TABLE customer
(
  customer_id integer NOT NULL DEFAULT nextval('customer_customer_id_seq'::regclass),
  store_id smallint NOT NULL,
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  email character varying(50),
  address_id smallint NOT NULL,
  activebool boolean NOT NULL DEFAULT true,
  create_date date NOT NULL DEFAULT ('now'::text)::date,
  last_update timestamp without time zone DEFAULT now(),
  active integer,
  CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
  CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film

CREATE TABLE film
(
  film_id integer NOT NULL DEFAULT nextval('film_film_id_seq'::regclass),
  title character varying(255) NOT NULL,
  description text,
  release_year year,
  language_id smallint NOT NULL,
  rental_duration smallint NOT NULL DEFAULT 3,
  rental_rate numeric(4,2) NOT NULL DEFAULT 4.99,
  length smallint,
  replacement_cost numeric(5,2) NOT NULL DEFAULT 19.99,
  rating mpaa_rating DEFAULT 'G'::mpaa_rating,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  special_features text[],
  fulltext tsvector NOT NULL,
  CONSTRAINT film_pkey PRIMARY KEY (film_id),
  CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id)
      REFERENCES language (language_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film_actor

CREATE TABLE film_actor
(
  actor_id smallint NOT NULL,
  film_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
  CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id)
      REFERENCES actor (actor_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film_category

CREATE TABLE film_category
(
  film_id smallint NOT NULL,
  category_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  category category_enum,
  CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
  CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id)
      REFERENCES category (category_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

inventory

CREATE TABLE inventory
(
  inventory_id integer NOT NULL DEFAULT nextval('inventory_inventory_id_seq'::regclass),
  film_id smallint NOT NULL,
  store_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
  CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

language

CREATE TABLE language
(
  language_id integer NOT NULL DEFAULT nextval('language_language_id_seq'::regclass),
  name character(20) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT language_pkey PRIMARY KEY (language_id)
)

payment

CREATE TABLE payment
(
  payment_id integer NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
  customer_id smallint NOT NULL,
  staff_id smallint NOT NULL,
  rental_id integer NOT NULL,
  amount numeric(5,2) NOT NULL,
  payment_date timestamp without time zone NOT NULL,
  CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
  CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES customer (customer_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id)
      REFERENCES rental (rental_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

rental

CREATE TABLE rental
(
  rental_id integer NOT NULL DEFAULT nextval('rental_rental_id_seq'::regclass),
  rental_date timestamp without time zone NOT NULL,
  inventory_id integer NOT NULL,
  customer_id smallint NOT NULL,
  return_date timestamp without time zone,
  staff_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
  CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES customer (customer_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id)
      REFERENCES inventory (inventory_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

staff

CREATE TABLE staff
(
  staff_id integer NOT NULL DEFAULT nextval('staff_staff_id_seq'::regclass),
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  address_id smallint NOT NULL,
  email character varying(50),
  store_id smallint NOT NULL,
  active boolean NOT NULL DEFAULT true,
  username character varying(16) NOT NULL,
  password character varying(40),
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  picture bytea,
  CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
  CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

store

CREATE TABLE store
(
  store_id integer NOT NULL DEFAULT nextval('store_store_id_seq'::regclass),
  manager_staff_id smallint NOT NULL,
  address_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT store_pkey PRIMARY KEY (store_id),
  CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

Consultas

Extracción de lista de films

  SELECT * from film

Extracción de lista de rentals

  SELECT * from rental

Listado de películas con su categoría y su lenguaje

  Select f.film_id, f.title as titulo, f.description as descripcion, 
  f.release_year as anioPublicacion, 
  f.rental_duration as duración, 
  c.name as categoria, l.name as idioma
  from film f
  join film_category fc on fc.film_id = f.film_id
  join category c on c.category_id = fc.category_id
  join language l on l.language_id = f.language_id
  order by f.film_id

Listado de actores con el número de películas

  select COALESCE(a.first_name, ' '), COALESCE(a.last_name, ' '), count(f.film_id) as numeroPeliculas
  from actor a
  join film_actor fa on fa.actor_id = a.actor_id
  join film f on f.film_id = fa.film_id
  group by  a.first_name, a.last_name
  order by  numeroPeliculas desc

MongoDB

Creación de colecciones

Actor

var actorSchema = new Schema({
    actor_id: Number,
    first_name: String,
    last_name: String
});

Address

var addressSchema = new Schema({
    address_id: Number,        
    address: String,
    address2: String,
    district: String,
    phone: String,
    postal_code: String,
    city: String,
    country: String,
    customers: Array,
    staffs: Array,
    stores: Array
});

Customer

var customerSchema = new Schema({
    customer_id: Number,
    first_name: String,
    last_name: String,
    email: String,
    active: Number,
    activebool: Boolean,
    create_date: Date,
    rentals: Array,
    payments: Array
});

Film

var filmSchema = new Schema({
    film_id: Number,
    title: String,
    description: String,
    release_year: Date,
    rental_duration: Number,
    rental_rate: Number,
    length: Number,
    replacement_cost: Number,
    rating: String,
    special_features: String,
    fulltext: String,        
    languages: Array,
    categories: Array,
    actors: Array, 
    stores: Array
});

Payment

var paymentSchema = new Schema({
    payment_id: Number,
    amount: Number,
    payment_date: Date
});

Rental

var rentalSchema = new Schema({
    rental_id: Number,
    rental_date: Date,
    return_date: Date,
    films: Array,
    stores: Array,
    payment: Array
});

Staff

var staffSchema = new Schema({
    staff_id: Number,
    first_name: String,
    last_name: String,
    email: String,
    active: Boolean,
    username: String,
    password: String,
    picture: String,
    rentals: Array,
    payments: Array,
    store: Array
});

Store

var storeSchema = new Schema({
    store_id: Number,
    manager_staff_id: Number,
    staffs: Array,
    customers: Array
});

Consultas

Extracción de lista de films

Films.find()

Extracción de lista de rentals

Rentals.find()

Listado de películas con su categoría y su lenguaje

  Films.find()

Listado de actores con el número de películas

  var resultado = Actor.find();

  var peliculas = Film.find({'actors':resultado.actor.id});
  Actor.find({}, function(err, docs){
    var i = -1;
    var actors = docs;
    //Para cada actor se busca su numero de peliculas
    for ( var i = 0; i < actors.length; i++ ){
      Film.find({ actors: { "$in" : [actors[i].actor_id]} } , function(error, docs){
        console.log(actors[i], "peliculas", docs.length)
      });
    }
  });

Script de JavaScript para la migración

'use strict'
const pg = require('pg');
const mongoose = require('mongoose');
const async = require('async');
const Schema = mongoose.Schema;
var mongoCon;
var Store;
var Staff;
var Customer;
var Address;
var Rental;
var Actor;
var Film;
var Payment;

const config = {
        user: 'postgres', //env var: PGUSER 
        database: 'dvdrental', //env var: PGDATABASE 
        password: 'Cuatro1991', //env var: PGPASSWORD 
        host: 'localhost', // Server hosting the postgres database 
        port: 5432, //env var: PGPORT 
        max: 10, // max number of clients in the pool 
        idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed 
};

const pool = new pg.Pool(config);


pool.connect(function(err, client, done) {
    if ( err ){
        console.log("error al conectar con postgre", err)
    }
    else{
        mongoCon = mongoose.createConnection('mongodb://172.17.0.2:27017/dvdrental', function(error) {
            if ( error ){
                console.log("error al conectar con mongo", error)
            }
            else{
                esquemas();
                migraciones();
                consultasSimple();
                consultasComplejas();
                return "ok";
            }

        })
    }
})


function consultasSimple(){
    //Peliculas
    var query = ' SELECT * from film ';
    pool.query(query, function(err, res) {
        console.log(res)
    });
    Film.find({}, function(err, docs){
        console.log(docs)
    });
    //Rental
    query = ' SELECT * from rental ';
    pool.query(query, function(err, res) {
        console.log(res)
    });
    Rental.find({}, function(err, docs){
        console.log(docs)
    });
}

function consultasComplejas(){
    //Peliculas con categoria y lenguaje
    var query = "Select f.film_id, f.title as titulo,f.description as descripcion," +
    "f.release_year as anioPublicacion,f.rental_duration as duración,c.name as categoria, " +
    "l.name as idioma from film f " +
    "join film_category fc on fc.film_id = f.film_id " +
    "join category c on c.category_id = fc.category_id " +
    "join language l on l.language_id = f.language_id order by f.film_id";

    pool.query(query, function(err, res) {
        console.log(res)
    });
    Film.find({}, function(err, docs){
        console.log(docs)
    });


    query = "select COALESCE(a.first_name, ' '), COALESCE(a.last_name, ' '), " +
    "count(f.film_id) as numeroPeliculas from actor a " +
    "join film_actor fa on fa.actor_id = a actor_id " +
    "join film f on f.film_id = fa.film id " +
    "group by a.first_name, a.last_name " +
    "order by numeroPeliculas desc";
    pool.query(query, function(err, res) {
        console.log(res)
    });
    Actor.find({}, function(err, docs){
        var i = -1;
        var actors = docs;
        for ( var i = 0; i < actors.length; i++ ){
            Film.find({ actors: { "$in" : [actors[i].actor_id]} } , function(error, docs){
                console.log(actors[i], "peliculas", docs.length)
            });
        }
    });

}

function migraciones(){
    migrateActor();
    migrateStore();
    migrateStaff();
    migrateCustomer();
    migrateAddress();
    migrateRental();
    migrateFilm();
    migratePayment();
}

function migrateFilm(){
    var queryFilm = 'SELECT f.film_id, title, description, release_year, f.language_id, rental_duration, rental_rate,'+
    ' length, replacement_cost, rating, special_features, fulltext '+
    'from film f join inventory i on i.film_id = f.film_id ';    

    //Cargando los datos de filmes en la coleccion
    pool.query(queryFilm, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryLanguage = 'SELECT l.name from language l where language_id  = $1::int ';
            var queryCategories = 'SELECT c.name from category c join film_category fc on fc.category_id  = c.category_id '+
            ' where fc.film_id = $1::int ';
            var queryActors = 'SELECT a.actor_id from actor a join film_actor fa on fa.actor_id  = a.actor_id '+
            ' where fa.film_id = $1::int ';
            var queryStores = 'SELECT distinct s.store_id from store s join inventory i on i.store_id  = s.store_id '+
            ' where i.film_id = $1::int ';
            var films = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < films.length },
                    function(callback){
                        var languages = [];
                        var categories = [];
                        var actors = [];
                        var stores = [];

                        //Extraccion de languages 
                        pool.query(queryLanguage, [films[i].film_id], function(errorL, responseL) {
                            if(errorL) {
                                console.log('error running query', errorL);
                            }
                            else{
                                for ( var j = 0; j<responseL.rows.length; j++ ){
                                    languages.push(responseL.rows[j].name)
                                }
                            }
                            //Extraccion de category
                            pool.query(queryCategories, [films[i].film_id], function(errorC, responseC) {
                                if(errorC) {
                                    console.log('error running query', errorC);
                                }
                                else{
                                    for ( var j = 0; j<responseC.rows.length; j++ ){
                                        categories.push(responseC.rows[j].name)
                                    }
                                }
                                //Extraccion de actors
                                pool.query(queryActors, [films[i].film_id], function(errorA, responseA) {
                                    if(errorA) {
                                        console.log('error running query', errorA);
                                    }
                                    else{
                                        for ( var j = 0; j<responseA.rows.length; j++ ){
                                            actors.push(responseA.rows[j].actor_id)
                                        }
                                    }
                                    //Extraccion de stores
                                    pool.query(queryStores, [films[i].film_id], function(errorS, responseS) {
                                        if(errorS) {
                                            console.log('error running query', errorS);
                                        }
                                        else{
                                            for ( var j = 0; j<responseS.rows.length; j++ ){
                                                stores.push(responseS.rows[j].store_id)
                                            }
                                        }

                                        var film = new Film ({                                            
                                            film_id: films[i].film_id,
                                            title: films[i].title,
                                            description: films[i].description,
                                            release_year: films[i].release_year,
                                            rental_duration: films[i].rental_duration,
                                            rental_rate: films[i].rental_rate,
                                            length: films[i].length,
                                            replacement_cost: films[i].replacement_cost,
                                            rating: films[i].rating,
                                            special_features: films[i].special_features,
                                            fulltext: films[i].fulltext,        
                                            languages: languages,
                                            categories: categories,
                                            actors: actors, 
                                            stores: stores
                                        })
                                        film.save(callback);
                                        console.log("film saved")
                                    });
                                });
                            });

                        });

                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}


function migrateRental(){
    var queryRental = 'SELECT rental_id, rental_date, return_date, film_id, store_id from rental r join inventory i on i.inventory_id = r.inventory_id ';

    //Cargando los datos de rentales en la coleccion
    pool.query(queryRental, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryPayment = 'SELECT payment_id from payment where rental_id  = $1::int ';
            var rentals = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < rentals.length },
                    function(callback){
                        var payments = [];

                        //Extraccion de payments 
                        pool.query(queryPayment, [rentals[i].rental_id], function(error, response) {
                            if(error) {
                                console.log('error running query', error);
                            }
                            else{
                                for ( var j = 0; j<response.rows.length; j++ ){
                                    payments.push(response.rows[j].payment_id)
                                }                            
                            }
                            var rental = new Rental ({
                                rental_id: rentals[i].rental_id,
                                rental_date: rentals[i].rental_date,
                                return_date: rentals[i].return_date,
                                film_id: rentals[i].film_id,
                                store_id: rentals[i].store_id,
                                payment: payments
                            })
                            rental.save(callback);
                            console.log("rental saved")
                        });

                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}

function migrateAddress(){

    var queryAddress = "SELECT address_id, address, address2, district, phone, postal_code, ci.city, c.country from address a " +
    "join city ci on ci.city_id = a.city_id " +
    "join country c on c.country_id = ci.country_id ";



    //Cargando los datos de addresses en la coleccion
    pool.query(queryAddress, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryCustomers = 'SELECT customer_id from customer where address_id = $1::int ';
            var queryStaffs = 'SELECT staff_id from staff where address_id = $1::int';
            var queryStores = 'SELECT store_id from store where address_id = $1::int';
            var addresses = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < addresses.length },
                    function(callback){
                        //Extraccion de address asociados
                        var customers = [];
                        var staffs = [];
                        var stores = [];
                        pool.query(queryCustomers, [addresses[i].address_id], function(error, responseC) {
                            if(error) {
                                console.log('error running query', error);
                            }
                            else{
                                for ( var j = 0; j<responseC.rows.length; j++ ){
                                    customers.push(responseC.rows[j].customer_id)
                                }                                    
                            }
                            pool.query(queryStaffs, [addresses[i].address_id], function(error, responseSt) {
                                if(error) {
                                    console.log('error running query', error);
                                }
                                else{
                                    for ( var j = 0; j<responseSt.rows.length; j++ ){
                                        staffs.push(responseSt.rows[j].staff_id)
                                    }                            
                                }
                                pool.query(queryStores, [addresses[i].address_id], function(error, responseStore) {
                                    if(error) {
                                        console.log('error running query', error);
                                    }
                                    else{
                                        for ( var j = 0; j<responseStore.rows.length; j++ ){
                                            stores.push(responseStore.rows[j].store_id)
                                        }                                
                                    }
                                    //Guardado de address
                                    var address = new Address ({
                                        address_id: addresses[i].address_id,
                                        address:  addresses[i].address,
                                        address2: addresses[i].address2,
                                        district: addresses[i].address2,
                                        phone: addresses[i].phone,
                                        postal_code: addresses[i].postal_code,
                                        city: addresses[i].city,
                                        country: addresses[i].country,
                                        customers: customers,
                                        Staffs: staffs,
                                        Stores: stores
                                    })
                                    address.save(callback);
                                    console.log("address saved")
                                });

                            });

                        });                        
                    }, function(err) {
                        console.log(err)
                    });
        }
    });

}

function migrateCustomer(){
    var queryCustomer = 'SELECT customer_id, first_name, last_name, email, active, activebool, create_date from customer ';



    //Cargando los datos de customeres en la coleccion
    pool.query(queryCustomer, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryRental = 'SELECT rental_id from rental where customer_id = $1::int ';
            var queryPayment = 'SELECT payment_id from payment where customer_id  = $1::int ';
            var customers = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < customers.length },
                    function(callback){
                        var rentals = [];
                        var payments = [];
                        //Extraccion de rentals 
                        pool.query(queryRental, [customers[i].customer_id], function(error, response) {
                            if(error) {
                                console.log('error running query', error);
                            }
                            else{
                                for ( var j = 0; j<response.rows.length; j++ ){
                                    rentals.push(response.rows[j].rental_id)
                                }                        
                            }

                            //Extraccion de payments 
                            pool.query(queryPayment, [customers[i].customer_id], function(error, responseP) {
                                if(error) {
                                    console.log('error running query', error);
                                }
                                else{
                                    for ( var j = 0; j<responseP.rows.length; j++ ){
                                        payments.push(responseP.rows[j].payment_id)
                                    }
                                }
                                var customer = new Customer ({
                                    customer_id: customers[i].customer_id,
                                    first_name:  customers[i].first_name,
                                    last_name:  customers[i].last_name,
                                    email: customers[i].email,
                                    active: customers[i].active,
                                    activebool: customers[i].activebool,
                                    create_date: customers[i].create_date,
                                    rentals: rentals,
                                    payments: payments
                                })
                                customer.save(callback);
                                console.log("customer saved")                                

                            });
                        });

                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}

function migrateStaff(){
    var queryStaff = 'SELECT staff_id, first_name, last_name, email, active, username, password, picture from staff ';



    //Cargando los datos de staffes en la coleccion
    pool.query(queryStaff, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryRental = 'SELECT rental_id from rental where staff_id = $1::int ';
            var queryPayment = 'SELECT payment_id from payment where staff_id  = $1::int ';
            var staffs = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < staffs.length },
                    function(callback){

                        var rentals = [];
                        var payments = [];

                        //Extraccion de rentals 
                        pool.query(queryRental, [staffs[i].staff_id], function(error, response) {
                            if(error) {
                                console.log('error running query', error);
                            }
                            else{
                                for ( var j = 0; j<response.rows.length; j++ ){
                                    rentals.push(response.rows[j].rental_id)
                                }    
                            }

                            //Extraccion de payments 
                            pool.query(queryPayment, [staffs[i].staff_id], function(error, responseP) {
                                if(error) {
                                    console.log('error running query', error);
                                }
                                else{
                                    for ( var j = 0; j<responseP.rows.length; j++ ){
                                        payments.push(responseP.rows[j].payment_id)
                                    }
                                }
                                var staff = new Staff ({
                                    staff_id: staffs[i].staff_id,
                                    first_name:  staffs[i].first_name,
                                    last_name:  staffs[i].last_name,
                                    email: staffs[i].email,
                                    active: staffs[i].active,
                                    username: staffs[i].username,
                                    password: staffs[i].password,
                                    picture: staffs[i].picture,
                                    rentals: rentals,
                                    payments: payments
                                })
                                staff.save();
                                console.log("staff saved")
                                callback ( null, "saved" );

                            });
                        });
                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}

function migrateStore(){
    var queryStore = 'SELECT store_id, manager_staff_id from store s ';



    //Cargando los datos de storees en la coleccion
    pool.query(queryStore, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{
            var queryStaff = 'SELECT staff_id from staff where store_id = $1::int ';
            var queryCustomer = 'SELECT customer_id from customer where store_id = $1::int ';
            var stores = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < stores.length },
                    function(callback){
                        //Extraccion de staffs asociados
                        var staffs = [];
                        var customers = [];
                        pool.query(queryStaff, [stores[i].store_id], function(error, response) {
                            if(error) {
                                console.log('error running query', error);
                            }
                            else{
                                for ( var j = 0; j<response.rows.length; j++ ){
                                    staffs.push(response.rows[j].staff_id)
                                }    
                            }
                            //Extraccion de customers asociados
                            pool.query(queryCustomer, [stores[i].store_id], function(error, responseC) {
                                if(error) {
                                    console.log('error running query', error);
                                }
                                else{
                                    for ( var j = 0; j<responseC.rows.length; j++ ){
                                        customers.push(responseC.rows[j].customer_id)
                                    }
                                }
                                //Guardado de store
                                var store = new Store ({
                                    store_id: stores[i].store_id,
                                    manager_staff_id:  stores[i].manager_staff_id,
                                    staffs:  staffs,
                                    customers: customers
                                })
                                store.save(callback);
                                console.log("store saved")
                            });
                        });                        
                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}

function migrateActor(){
    var queryActor = 'SELECT actor_id, first_name, last_name from actor ';



    //Cargando los datos de actores en la coleccion
    pool.query(queryActor, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{            
            var actors = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < actors.length },
                    function(callback){
                        var actor = new Actor ({
                            actor_id: actors[i].actor_id,
                            first_name:  actors[i].first_name,
                            last_name:  actors[i].last_name
                        })
                        actor.save(callback);
                        console.log("actor saved")
                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}

function migratePayment(){
    var queryPayment = 'SELECT payment_id, amount, payment_date from payment ';


    //Cargando los datos de paymentes en la coleccion
    pool.query(queryPayment, function(err, res) {
        if(err) {
            console.log('error running query', err);
        }
        else{            
            var payments = res.rows;
            var i = -1;
            async.whilst(
                    function(){ i++; return i < payments.length },
                    function(callback){
                        var payment = new Payment ({
                            payment_id: payments[i].payment_id,
                            amount:  payments[i].amount,
                            payment_date:  payments[i].payment_date
                        })
                        payment.save(callback);
                        console.log("payment saved")
                    }, function(err) {
                        console.log(err)
                    });
        }
    });
}
function esquemas(){

    var filmSchema = new Schema({
        film_id: Number,
        title: String,
        description: String,
        release_year: Date,
        rental_duration: Number,
        rental_rate: Number,
        length: Number,
        replacement_cost: Number,
        rating: String,
        special_features: String,
        fulltext: String,        
        languages: Array,
        categories: Array,
        actors: Array, 
        stores: Array
    });

    Film = mongoCon.model('film', filmSchema);

    var rentalSchema = new Schema({
        rental_id: Number,
        rental_date: Date,
        return_date: Date,
        films: Array,
        stores: Array,
        payment: Array
    });

    Rental = mongoCon.model('rental', rentalSchema);

    var addressSchema = new Schema({
        address_id: Number,        
        address: String,
        address2: String,
        district: String,
        phone: String,
        postal_code: String,
        city: String,
        country: String,
        customers: Array,
        staffs: Array,
        stores: Array
    });

    Address = mongoCon.model('address', addressSchema);

    var customerSchema = new Schema({
        customer_id: Number,
        first_name: String,
        last_name: String,
        email: String,
        active: Number,
        activebool: Boolean,
        create_date: Date,
        rentals: Array,
        payments: Array
    });

    Customer = mongoCon.model('customer', customerSchema);

    var staffSchema = new Schema({
        staff_id: Number,
        first_name: String,
        last_name: String,
        email: String,
        active: Boolean,
        username: String,
        password: String,
        picture: String,
        rentals: Array,
        payments: Array,
        store: Array
    });

    Staff = mongoCon.model('staff', staffSchema);

    var storeSchema = new Schema({
        store_id: Number,
        staffs: Array,
        customers: Array        
    });

    Store = mongoCon.model('store', storeSchema);

    var actorSchema = new Schema({
        actor_id: Number,
        first_name: String,
        last_name: String
    });

    Actor = mongoCon.model('actor', actorSchema);

    var paymentSchema = new Schema({
        payment_id: Number,
        amount: Number,
        payment_date: Date
    });

    Payment = mongoCon.model('payment', paymentSchema);
}