ZaNiT9Gw

1var mysql = require('mysql');
2var config = require('../config/default.js')
3
4var pool  = mysql.createPool({
5  host     : config.database.HOST,
6  user     : config.database.USERNAME,
7  password : config.database.PASSWORD,
8  database : config.database.DATABASE,
9  port     : config.database.PORT
10});
11
12let query = ( sql, values ) => {
13
14  // 返回一個異步操作(內含操作的事件處理)
15  return new Promise(( resolve, reject ) => {
16    // 使用連接
17    pool.getConnection( (err, connection) => {
18      if (err) {
19        reject( err )
20      } else {
21        // 使用連接執行查詢
22        connection.query(sql, values, ( err, rows) => {
23          if ( err ) {
24            reject( err )
25          } else {
26            resolve( rows )
27          }
28          // 連接不再使用，返回到連接池
29          connection.release()
30        })
31      }
32    })
33  })
34
35}
36
37// 建立用戶資料表
38let users =
39    `create table if not exists users(
40     id INT NOT NULL AUTO_INCREMENT,
41     name VARCHAR(100) NOT NULL COMMENT '用户名',
42     pass VARCHAR(100) NOT NULL COMMENT '密碼',
43     avator VARCHAR(100) NOT NULL COMMENT '頭像',
44     moment VARCHAR(100) NOT NULL COMMENT '注冊時間',
45     PRIMARY KEY ( id )
46    );`
47
48    // 建立文章資料表
49let posts =
50    `create table if not exists posts(
51     id INT NOT NULL AUTO_INCREMENT,
52     name VARCHAR(100) NOT NULL COMMENT '文章作者',
53     title TEXT(0) NOT NULL COMMENT '評論題目',
54     content TEXT(0) NOT NULL COMMENT '評論内容',
55     md TEXT(0) NOT NULL COMMENT 'markdown',
56     uid VARCHAR(40) NOT NULL COMMENT '用户id',
57     moment VARCHAR(100) NOT NULL COMMENT '發表時間',
58     comments VARCHAR(200) NOT NULL DEFAULT '0' COMMENT '文章評論數',
59     pv VARCHAR(40) NOT NULL DEFAULT '0' COMMENT '瀏覽量',
60     avator VARCHAR(100) NOT NULL COMMENT '用户頭像',
61     PRIMARY KEY(id)
62    );`
63
64let comment =
65    `create table if not exists comment(
66     id INT NOT NULL AUTO_INCREMENT,
67     name VARCHAR(100) NOT NULL COMMENT '用户名稱',
68     content TEXT(0) NOT NULL COMMENT '評論内容',
69     moment VARCHAR(40) NOT NULL COMMENT '評論時間',
70     postid VARCHAR(40) NOT NULL COMMENT '文章id',
71     avator VARCHAR(100) NOT NULL COMMENT '用户頭像',
72     PRIMARY KEY(id) 
73    );`
74
75let createTable = ( sql ) => {
76  return query( sql, [] )
77}
78
79// 建表
80createTable(users)
81createTable(posts)
82createTable(comment)
83
84// 注册用户
85exports.insertData = ( value ) => {
86  let _sql = "insert into users set name=?,pass=?,avator=?,moment=?;"
87  return query( _sql, value )
88}
89// 删除用户
90exports.deleteUserData = ( name ) => {
91  let _sql = `delete from users where name="${name}";`
92  return query( _sql )
93}
94// 查找用户
95exports.findUserData = ( name ) => {
96  let _sql = `select * from users where name="${name}";`
97  return query( _sql )
98}
99// 發表文章
100exports.insertPost = ( value ) => {
101  let _sql = "insert into posts set name=?,title=?,content=?,md=?,uid=?,moment=?,avator=?;"
102  return query( _sql, value )
103}
104// 增加文章評論數
105exports.addPostCommentCount = ( value ) => {
106  let _sql = "update posts set comments = comments + 1 where id=?"
107  return query( _sql, value )
108}
109// 減少文章評論數
110exports.reducePostCommentCount = ( value ) => {
111  let _sql = "update posts set comments = comments - 1 where id=?"
112  return query( _sql, value )
113}
114
115// 更新瀏覽數
116exports.updatePostPv = ( value ) => {
117  let _sql = "update posts set pv= pv + 1 where id=?"
118  return query( _sql, value )
119}
120
121// 發表評論
122exports.insertComment = ( value ) => {
123  let _sql = "insert into comment set name=?,content=?,moment=?,postid=?,avator=?;"
124  return query( _sql, value )
125}
126// 通過名字查找用户
127exports.findDataByName =  ( name ) => {
128  let _sql = `select * from users where name="${name}";`
129  return query( _sql)
130}
131// 通過名字查找用户數量判斷是否已經存在
132exports.findDataCountByName =  ( name ) => {
133  let _sql = `select count(*) as count from users where name="${name}";`
134  return query( _sql)
135}
136// 通過文章的名字查找用户
137exports.findDataByUser =  ( name ) => {
138  let _sql = `select * from posts where name="${name}";`
139  return query( _sql)
140}
141// 通過文章id查找
142exports.findDataById =  ( id ) => {
143  let _sql = `select * from posts where id="${id}";`
144  return query( _sql)
145}
146// 通過文章id查找
147exports.findCommentById =  ( id ) => {
148  let _sql = `select * from comment where postid="${id}";`
149  return query( _sql)
150}
151
152// 通過文章id查找評論數
153exports.findCommentCountById =  ( id ) => {
154  let _sql = `select count(*) as count from comment where postid="${id}";`
155  return query( _sql)
156}
157
158// 通過評論id查找
159exports.findComment = ( id ) => {
160  let _sql = `select * from comment where id="${id}";`
161  return query( _sql)
162}
163// 查詢所有文章
164exports.findAllPost = () => {
165  let _sql = `select * from posts;`
166  return query( _sql)
167}
168// 查詢所有文章數量
169exports.findAllPostCount = () => {
170  let _sql = `select count(*) as count from posts;`
171  return query( _sql)
172}
173// 查詢分頁文章
174exports.findPostByPage = ( page ) => {
175  let _sql = ` select * from posts limit ${(page-1)*10},10;`
176  return query( _sql)
177}
178// 查詢所有個人用户文章數量
179exports.findPostCountByName = (name) => {
180  let _sql = `select count(*) as count from posts where name="${name}";`
181  return query( _sql)
182}
183// 查詢个人分頁文章
184exports.findPostByUserPage = (name,page) => {
185  let _sql = ` select * from posts where name="${name}" order by id desc limit ${(page-1)*10},10 ;`
186  return query( _sql)
187}
188// 更新修改文章
189exports.updatePost = (values) => {
190  let _sql = `update posts set title=?,content=?,md=? where id=?`
191  return query(_sql,values)
192}
193// 刪除文章
194exports.deletePost = (id) => {
195  let _sql = `delete from posts where id = ${id}`
196  return query(_sql)
197}
198// 刪除評論
199exports.deleteComment = (id) => {
200  let _sql = `delete from comment where id=${id}`
201  return query(_sql)
202}
203// 刪除所有評論
204exports.deleteAllPostComment = (id) => {
205  let _sql = `delete from comment where postid=${id}`
206  return query(_sql)
207}
208
209// 滾動無限加載數據
210exports.findPageById = (page) => {
211  let _sql = `select * from posts limit ${(page-1)*5},5;`
212  return query(_sql)
213}
214// 評論分頁
215exports.findCommentByPage = (page,postId) => {
216  let _sql = `select * from comment where postid=${postId} order by id desc limit ${(page-1)*10},10;`
217  return query(_sql)
218}