mysql로 node.js에서 만든 수업을 옮기다.

아래 코드는 node.js로 파일시스템 하에 만들었던 홈페이지를

database 시스템 아래로 옮긴 것이다.

 

topic이라는 라이브러리를 만들어 각 페이지를 모듈화 하여 간결하게 만들었다.

const http = require('http');
const url = require('url');
const path = require('path');
const topic = require('./lib/topic');

const app = http.createServer(function(request,response){
  var _url = request.url;
  var queryData = url.parse(_url,true/*true는 객체형식으로 받아옴*/).query;
  var pathname = url.parse(_url,true).pathname;

  if(pathname === '/'){
    if(queryData.id === undefined){
      topic.home(response);
    } else {
      topic.desc(request,response)
      };
  } else if(pathname==='/create'){
    topic.create(request,response)
  } else if(pathname === '/create_process'){
    topic.create_process(request, response)
  } else if(pathname === '/update'){
    topic.update(request, response)
  } else if(pathname === '/update_process'){
    topic.update_process(request, response)
  } else if(pathname === '/delete'){
    topic.delete(request, response)
  } else {
    response.writeHead(404);
    response.end('Not Found')
  }
});
app.listen(3000);

topic 모듈 속의 코드는 다음과 같다.

const template = require('./template.js');
const db = require('./db');
const url = require('url');
const qs = require('querystring');

exports.home = function(response){
  db.query(`select * from topic`, function(error,topics){
    var list = template.list(topics);
    var title = 'HOME';
    var html = template.HTML(title,'Welcome!',list,
    `<a href = '/create'>create</a>`);

    response.writeHead(200);
    response.end(html);
  });
}

exports.desc = function(request,response){
  var _url = request.url;
  var queryData = url.parse(_url,true/*true는 객체형식으로 받아옴*/).query;
  db.query(`select * from topic`, function(error,topics){
    if(error){
      throw error;
    }
    db.query(`select * from topic left join author on topic.author_id = author.id where topic.id=?`,[queryData.id], function(error2, topic){
      if (error2){
        throw error2;
      }
      var list = template.list(topics);
      var html = template.HTML(topic[0].title,`${topic[0].description}<p>by ${topic[0].name}</p>`,list,

      `
        <a href = '/create'>create</a>
        <a href = '/update?id=${queryData.id}'>update</a>
        <form action = "/delete" method = 'post'>
          <input type = 'hidden' name = 'trueid' value='${queryData.id}'>
          <input type='submit' value = 'delete'>
        </form>
      `);
      response.writeHead(200);
      response.end(html);
    });
  });
}

exports.create = function(request, response){
  db.query(`select * from topic`, function(error,topics){
    db.query(`select * from author`, function(error2, authors){

      var list = template.list(topics);
      var title = 'create';
      var html = template.HTML(title,
      `<form action = "/create_process" method = 'post'>
      <p><input type='text' name='title' placeholder = 'title'></p>
      <p><textarea name='content' rows='10' cols='30' placeholder = 'insert text here'></textarea>
      </p>
      <p>${template.combo('author',authors)} &nbsp; <input type='submit'>
      </p>

      </form>`,list,
      ''
      );
      response.writeHead(200);
      response.end(html);
    });

  });
}

exports.create_process = function(request, response){
  var body = '';
  request.on('data',function(data){
    body = body + data ;
  });
  request.on('end',function(){
    var post = qs.parse(body)
    db.query(`
      insert into topic(title, description, created, author_id)
      values(?, ?, now(), ?)`,
    [post.title, post.content, post.author],
    function(error, results){
      if (error){
        throw error;
      }
    response.writeHead(302,{
      'Location' : `/?id=${results.insertId}`
    });
    response.end()
    });
  });
}

exports.update = function(request, response){
  var _url = request.url;
  var queryData = url.parse(_url,true/*true는 객체형식으로 받아옴*/).query;
  db.query(`select * from topic`, function(error,topics){
    if(error){
      throw error;
    }
    db.query(`select*from topic where id=?`, [queryData.id], function(error2, topic){
      if(error2){
        throw error2;
      }
      db.query(`select * from author`, function(error3, authors){
        if(error3){
          throw error3;
        }
        var list = template.list(topics);
        var title = `Update`;
        var html = template.HTML(title,
          `<form action = "/update_process" method = 'post'>
        <p><input type='text' name='title' value = '${topic[0].title}'></p>
        <p><input type='hidden' name='trueid' value='${topic[0].id}'></p>
        <textarea name='content' rows='10' cols='30' placeholder = 'insert text here'>${topic[0].description}</textarea>
        <p>${template.combo('author',authors, topic[0].author_id)} &nbsp; <input type='submit'>
        </p>
        </form>`,
        list,
        ``);
        response.writeHead(200);
        response.end(html);
      });
    });
  });
}

exports.update_process = function(request, response){
  var body = '';
  request.on('data',function(data){
    body = body + data ;
  });
  request.on('end',function(){
    var post = qs.parse(body)
    db.query(`update topic set title=?, description = ?, author_id= ? where id=?`,[post.title, post.content, post.author, post.trueid], function(error, results){
      if (error){
        throw error;
      }
      response.writeHead(302, {
        'Location' : `/?id=${post.trueid}`
      });
      response.end('success')
      });
    });
}

exports.delete = function(request, response){
  var body = '';
  request.on('data',function(data){
    body = body + data ;
  });
  request.on('end',function(){

      var post = qs.parse(body)
      db.query(`delete from topic where id = ?`, [post.trueid], function(error, result){
        if(error){
          throw error;
        }
        response.writeHead(302,{
          'Location' : '/'
        });
        response.end()
      })
  });
}

 db 모듈은 다음과 같다.

const mysql = require('mysql');

db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password:'rkfkehfl7300',
  database : 'baby'
});
db.connect();
module.exports = db;

 

mysql.js는 다음과 같다.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'rkfkehfl7300',
  database : 'baby'
});

connection.connect();

connection.query('select * from topic', function (error, results, fields) {
  if (error) {
    console.log(error)
  };
  console.log(results);
});

connection.end();