vendredi 27 novembre 2015

Query "AND" Sqlite 3 REST

I'm trying to make a Restfull server using node.js and sqlite3; I have this 2 tables:

CREATE TABLE contact (
    id                   INTEGER       AUTO_INCREMENT,
    names                VARCHAR(20)   NOT NULL,
    last_name            VARCHAR(20),
    email VARCHAR(20),

    PRIMARY KEY(id)
);

CREATE TABLE phone(
    id                   INTEGER        AUTO_INCREMENT,
    id_contact           INTEGER,
    number               INTEGER,
    description          VARCHAR(20),

    PRIMARY KEY(id),
    FOREIGN KEY(id_contact) REFERENCES contact(id)
);

And this 2 inserts...

INSERT INTO contact(names, last_name, email) VALUES
('Brian', 'Cardona', 'brian.cardonas@autonoma.edu.co');

INSERT INTO phone(id_contact, number, description) VALUES
(1, 3105056245, 'Móvil');

INSERT INTO phone(id_contact, number, description) VALUES
(1, 8714396, 'Fijo');

The REST Nodejs Server:

/* Require modules */
var express = require('express');
var sqlite3 = require('sqlite3').verbose();
var bodyParser = require('body-parser');

/* Global objects */
var app = express();
var port = process.env.PORT || 8080;
var db = new sqlite3.Database('contactos.sqlite');

//////////////////////////////////////////////////////////////////

/* Config (request) and (response) */
app.use(bodyParser.json()); // Body parser use JSON data
app.use(bodyParser.urlencoded({ extended: false }));

/* Support for CORS */
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE');
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With,    Content-Type, Accept");
next();
});

//////////////////////////////////////////////////////////////////
/* Init db */
db.serialize(function() {
db.run("CREATE TABLE IF NOT EXISTS contact (id INTEGER PRIMARY KEY  AUTOINCREMENT, names  VARCHAR(20) NOT NULL, last_name VARCHAR(20), email  VARCHAR(20));");
db.run("CREATE TABLE IF NOT EXISTS phone (id INTEGER PRIMARY KEY AUTOINCREMENT, id_contact INTEGER, number  INTEGER NOT NULL, description VARCHAR(20), FOREIGN KEY(id_contact) REFERENCES contact(id));");
});

//////////////////////////////////////////////////////////////////
// My routes                                                    //
//////////////////////////////////////////////////////////////////

app.get('/', function(req, res){
res.send('Agenda de contactos!');
});

//////////////////////////////////////////////////////////////////

/* List ALL phones from an specifict contact */
app.get('/phones/contacts/:id_contact', function(req, res, next) {
// MIME Answer
res.setHeader("Content-Type", "application/json");
// Query
db.all("SELECT * FROM phone WHERE id_contact = ?", [req.params.id_contact],    function(err, rows) {
    // If error
    if (err) {
    console.error(err);
    res.status(500);    // Server Error
    res.json({ "error" : err });
    } else {
        // Success
        res.status(200);    // OK
        // Return query
        res.json({ "phones" : rows });
    }
  });
});

//////////////////////////////////////////////////////////////////

/* Get an specifict phone from an specifict contact */
app.get('/phones/:id_phone/contacts/:id_contact', function(req, res, next) {
// MIME answer
res.setHeader("Content-Type", "application/json");
// Query
db.get("SELECT * FROM phone WHERE id = ? AND id_contact = ?",  [req.params.id_phone], [req.params.id_contact], function(err, row) {
    // If error
    if (err) {
    console.error(err);
    res.status(500);        // Server Error
    res.json({ "error" : err });
    } else {
        // Correct answer
        if(row == undefined) {
        // If Resource not found
        res.status(404);    // Registro no encontrado
        res.json({ "error" : "Resource not found" });
        } else {
          // Success
          res.status(200);  // OK
          // Return query
          res.json({ "phone" : row });
        }
      }
     res.end();
   });
});

//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////

/* Begin the server */

app.listen(port);

console.log('Server listening on port ' + port);

//////////////////////////////////////////////////////////////////

Well...The problem is that when I try to get an specifict number of "Brian"; I mean I try to access the route: http://localhost:8080/phones/2/contacts/1 I got an error and it says: {"error":"Resource not found"} I don't understand why because when I access the route: http://localhost:8080/phones/contacts/1 then I got all the phone numbers the contact with id=1 in this case all the numbers with id_contact = 1 came to me...

{"phones":[{"id":1,"id_contact":1,"number":3105056245,"description":"Móvil"},{"id":2,"id_contact":1,"number":8714396,"description":"Fijo"}]}

Thanks for helping me.

Aucun commentaire:

Enregistrer un commentaire