Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- const express = require('express');
- const app = express();
- var bodyParser = require('body-parser');
- var multer = require('multer');
- var upload = multer();
- var fs = require("fs");
- const mysql = require('mysql');
- const dauria = require('dauria');
- const con = mysql.createConnection({
- host : "localhost",
- port : "3306",
- user : "root",
- password : "irena",
- database : "apitest"
- });
- con.connect(function(err){
- if(err) throw err;
- console.log("Connected!");
- });
- app.use(bodyParser.json());
- app.use(bodyParser.urlencoded({ extended: true }));
- app.get('/api/post',function(req,res){
- con.query("SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id=image.file_id AND post.c=categories.id",function(err,results,fields){
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- }); //-OK-
- app.post('/api/post',upload.array(),function(req,res,next){
- const title = req.body.title;
- const content = req.body.content;
- const Categories = "\"" + req.body.categories + "\"";
- const uri = req.body.uri;
- const time = Math.floor(new Date().valueOf() / 1000);
- var Cid;
- con.query("SELECT MAX(post_id) AS id FROM post",function(err,results,fields){
- if(err) throw err;
- var id = results[0].id + 1;
- console.log(id);
- con.query("SELECT id FROM categories WHERE categories = ?",Categories,function(err1,results1,fields1){
- if (err1) throw err1;
- if(!results){
- con.query("SELECT MAX(id) AS Cid FROM categories",function(err2,results2,fields2){
- if(err2) throw err2;
- Cid = results2[0].Cid + 1;
- const paramC = [Cid,Categories];
- con.query("INSERT INTO categories (id,categories) VALUES (?,?)",paramC,function(err3,results3,fields3){
- if(err3) throw err3;
- else console.log("categories insert OK!");
- });
- });
- }
- else {
- Cid = results1;
- console.log("categories 已經存在!");
- }
- const paramP = [id,title,content,time,Cid];
- con.query("INSERT INTO post (post_id,title,content,create_on,c) VALUES (?,?,?,?,?)",paramP,function(err1,results1,fields1){
- if(err1) throw err1;
- else console.log("post insert OK!");
- });
- });
- if(uri){
- const path = "image" + id +".png";
- fs.writeFile(path,dauria.parseDataURI(uri).buffer,function(err1){
- if(err1) throw err1;
- const path1 = "\"" +path + "\"" ;
- const uri1 = "\"" + uri + "\"" ;
- const paramI = [id,uri1,path1];
- con.query("INSERT INTO image (file_id,uri,path) VALUES (?,?,?)",paramI,function(err2,results1,fields1){
- if(err2) throw err2;
- else console.log("image insert OK!");
- });
- });
- }
- else {
- console.log("No image!");
- con.query("INSERT INTO image (file_id) VALUES (?)",id,function(err2,results1,fields1){
- if(err2) throw err2;
- else console.log("image insert OK!(only id)");
- });
- }
- });
- });
- app.get('/api/post/:id',function(req,res){
- const sql = "SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";
- con.query(sql,req.params.id,function(err,results,fields){
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- });//-OK-
- app.patch('/api/post/:id',function(req,res){
- const id = req.params.id;
- const title = req.body.title;
- const content = req.body.content;
- const Categories = req.body.categories;
- const uri = req.body.uri;
- if (title) {
- const sql = "UPDATE post SET title = \""+title+"\" WHERE post_id = " + id;
- con.query(sql,function(err,results,fields){
- if(err) throw err;
- console.log("title 更改OK!");
- });
- } else {
- console.log("title 沒有變動!");
- }
- if (content) {
- const sql = "UPDATE post SET content = \""+content+"\" WHERE post_id = " + id;
- con.query(sql,function(err,results,fields){
- if(err) throw err;
- console.log("content 更改OK!");
- });
- } else {
- console.log("content 沒有變動!");
- }
- if (Categories) {
- const sql = "SELECT id FROM categories WHERE categories = \"" + Categories + "\"";
- con.query(sql,function(err,results,fields){
- if(err) throw err;
- if(results){
- console.log(results);
- console.log(results[0].id);
- console.log(typeof results[0].id);
- con.query("UPDATE post SET c = ? WHERE post_id = ?",results[0].id,id,function(err1,results1,fields1){
- if(err1) throw err1;
- console.log("Categories 更改OK!(Categories存在)");
- });
- }
- else{
- con.query("SELECT MAX(id) FROM categories",function(err1,results1,fields1){
- const Cid = results1 +1;
- con.query("INSERT INTO categories (id,categories) VALUES (?,?)",Cid,Categories,function(err2,results2,fields2){
- if(err) throw err;
- console.log("Categories 新增OK!");
- });
- con.query("UPDATE post SET c = ? WHERE id = ?",Cid,id,function(err2,results2,fields2){
- if(err) throw err;
- console.log("Categories 更改OK!");
- });
- });
- }
- });
- } else {
- console.log("categories 沒有變動!");
- }
- if (uri) {
- const path = "image" + id +".png";
- fs.writeFile(path,dauria.parseDataURI(uri).buffer,function(err1){
- if(err1) throw err1;
- const sql = "UPDATE image SET uri = \""+uri+"\",path = \""+path+"\" WHERE file_id = " + id;
- con.query(sql,function(err2,results2,fields2){
- if(err2) throw err2;
- else console.log("image 更改OK!");
- });
- });
- } else {
- console.log("image 沒有變動!");
- }
- const sql = "SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";
- con.query(sql,req.params.id,function(err,results,fields){
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- }); //-少categories-
- app.delete('/api/post/:id',function(req,res){
- const id = req.params.id;
- con.query("DELETE FROM post WHERE post_id =?",id,function(err,results,fields){
- if(err) throw err;
- console.log("delete post!");
- });
- con.query("DELETE FROM image WHERE file_id =?",id,function(err,results,fields){
- if(err) throw err;
- console.log("delete image!");
- });
- });//-OK-
- app.get('/api/page/post',function(req,res){
- con.query("SELECT post_id,title,content,create_on,uri,categories FROM post,image,categories WHERE post.post_id=image.file_id AND post.c=categories.id",function(err,results,fields){
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- });//-OK-
- app.get('/api/page/post/:id',function(req,res){
- const sql = "SELECT post_id,title,content,create_on,uri,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";
- con.query(sql,req.params.id,function(err,results,fields){
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- });//-OK-
- app.get('/api/post/categories',function(req,res){
- con.query("SELECT * FROM categories",function(err,results,fields) {
- if(err) throw err;
- console.log(results);
- res.send(results);
- });
- });
- const server = app.listen(3000, function () {
- console.log("connected localhost:3000");
- });
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement