SHARE
TWEET

model.js (bcrypt)

a guest Jun 11th, 2019 106 in 1 day
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. "use strict";
  2. const sqlite3 = require('sqlite3').verbose();
  3. const db = new sqlite3.Database("./data/script.db");
  4. const bcrypt = require('bcryptjs');
  5. // Encryption power:
  6. const saltRounds = 10;
  7.  
  8. /** Querys **/
  9. const sql_posts_combined = "SELECT * FROM posts JOIN users ON posts.userId = users.userId JOIN codingLanguages ON posts.languageId = codingLanguages.languageId";
  10. const sql_getPost = "SELECT * FROM posts JOIN users ON posts.userId = users.userId JOIN codingLanguages ON posts.languageId = codingLanguages.languageId WHERE posts.postId = $id";
  11. const sql_getPostCreator = "SELECT userId FROM posts WHERE posts.postId = $id";
  12. const sql_getComments = "Select * FROM comments JOIN users ON comments.userId = users.userId WHERE comments.postId = $id";
  13. const sql_getComment = "Select * FROM comments JOIN users ON comments.userId = users.userId WHERE comments.commentId = $id";
  14. const sql_post_post = "INSERT into posts(title, code, description, author, date, userId, languageId) VALUES ($title, $code, $description, $author, $date, $userId, $languageId)";
  15. const sql_update_post = "UPDATE posts SET title = $title, code = $code, description = $description, author = $author, date = $date, languageId = $languageId WHERE posts.postId = $id";
  16. const sql_post_comment = "INSERT into comments(title, text, date, userId, postId) VALUES ($title, $text, $date, $userId, $id)";
  17. const sql_update_comment = "UPDATE comments SET title = $title, text = $text, date = $date WHERE comments.commentId = $id";
  18. const sql_getUser = "SELECT * FROM users WHERE users.username = $name";
  19.  
  20. /** Deletes **/
  21. const sql_delete_post = "DELETE FROM posts WHERE posts.postId = $id";
  22. const sql_delete_comment = "DELETE FROM comments WHERE comments.commentId = $id";
  23. const sql_delete_comments_from_post = "DELETE FROM comments WHERE comments.postId = $id";
  24.  
  25. /** Filters **/
  26. const sql_filter_lang = " WHERE posts.languageId = $lang";
  27. const sql_filter_desc = " ORDER BY posts.postId DESC";
  28. const sql_filter_limit = " LIMIT $limit";
  29.  
  30.  
  31.  
  32. /*** Overview of all Posts ***/
  33. function getPosts(sortingBy, filterLang, limit, callback) {
  34.     let myQuery = buildSql(sql_posts_combined, sortingBy, filterLang, limit);
  35.     let placeholder = buildPlaceholder(filterLang, limit);
  36.     db.all(myQuery, placeholder, (err, rows) => {
  37.         callback(rows);
  38.     });
  39. }
  40.  
  41. /*** Single Post (but as array) ***/
  42. function getPost(id, callback) {
  43.     db.all(sql_getPost, {$id:id}, (err, row) => {
  44.         callback(row);
  45.     });
  46. }
  47.  
  48. function getPostCreator(id, callback) {
  49.     db.all(sql_getPostCreator, {$id:id}, (err, row) => {
  50.         callback(row[0]);
  51.     });
  52. }
  53.  
  54. function getCommentsForPost(id, callback) {
  55.     db.all(sql_getComments, {$id:id}, (err, rows) => {
  56.         callback(rows);
  57.     });
  58. }
  59.  
  60. function getComment(id, callback) {
  61.     db.all(sql_getComment, {$id:id}, (err, row) => {
  62.         callback(row[0]);
  63.     });
  64. }
  65.  
  66. /*** Add Post ***/
  67. function addPost(post) {
  68.     db.run(sql_post_post,
  69.         {
  70.             $title: post.title,
  71.             $code: post.code,
  72.             $description: post.description,
  73.             $author: post.author,
  74.             $date: post.date,
  75.             $userId: post.userId,
  76.             $languageId: post.languageId
  77.         });
  78. }
  79.  
  80. /*** Update Post ***/
  81. function updatePost(id, post) {
  82.     db.run(sql_update_post,
  83.         {
  84.             $id: id,
  85.             $title: post.title,
  86.             $code: post.code,
  87.             $description: post.description,
  88.             $author: post.author,
  89.             $date: post.date,
  90.             $languageId: post.languageId
  91.         });
  92. }
  93.  
  94. // Adds a comment for a Post with ID 'id'
  95. function addComment(id, comment) {
  96.     db.run(sql_post_comment,
  97.         {
  98.             $id: id,
  99.             $title: comment.title,
  100.             $text: comment.text,
  101.             $date: comment.date,
  102.             $userId: comment.userId,
  103.             $postId: comment.postId
  104.         });
  105. }
  106.  
  107. function updateComment(id, comment) {
  108.     db.run(sql_update_comment,
  109.         {
  110.             $id: id,
  111.             $title: comment.title,
  112.             $text: comment.text,
  113.             $date: comment.date
  114.         });
  115. }
  116.  
  117.  
  118. /*** Get User By Name***/
  119. function getUserByName(name, callback) {
  120.     db.all("SELECT username, userId, rankId FROM users WHERE users.username = ?", [name], (err, row) => {
  121.         console.log("row: " + row);
  122.         callback(row);
  123.     });
  124. }
  125.  
  126. /*** Delete Post ***/
  127. function deletePost(id) {
  128.     db.run(sql_delete_post, {$id:id});
  129.     deleteCommentsFromPost(id);
  130. }
  131.  
  132. function deleteComment(id) {
  133.     db.run(sql_delete_comment, {$id:id});
  134. }
  135.  
  136. function deleteCommentsFromPost(id) {
  137.     db.run(sql_delete_comments_from_post, {$id:id});
  138. }
  139.  
  140. function isValidUser(username, callback) {
  141.     db.get(sql_getUser, {$name:username}, (err, row) => {
  142.         if(row != null) {
  143.             console.log("user found: " + username);
  144.             callback(true);
  145.         } else {
  146.             console.log('\x1b[33m%s\x1b[0m', "user not found: " + username);
  147.             callback(false);
  148.         }
  149.     });
  150. }
  151.  
  152. function hashPassword(plainPW) {
  153.     bcrypt.hash(plainPW, saltRounds, function(err, hash) {
  154.         //console.log("Password Hash: " + hash);
  155.         return hash;
  156.     });
  157. }
  158.  
  159. function comparePassword(plainPW, username, callback) {
  160.     db.get(sql_getUser, {$name:username}, (err, row) => {
  161.         if(row != null) {
  162.             bcrypt.compare(plainPW, row.password, function (err, res) {
  163.                 console.log("Login for " + username + " was " + res);
  164.                 callback(res);
  165.             });
  166.         }
  167.         else {
  168.             console.log("User " + username + " doesn't exist.");
  169.             callback(false);
  170.         }
  171.     });
  172. }
  173.  
  174. function buildSql(initialQuery, sortingBy, filterLang, limit) {
  175.     let outputQuery = initialQuery;
  176.  
  177.     // Filter Language
  178.     if(filterLang > 0) {
  179.         outputQuery += sql_filter_lang;
  180.     }
  181.  
  182.     // Sorting Method
  183.     switch(sortingBy) {
  184.         case "1":
  185.             outputQuery += sql_filter_desc;
  186.             break;
  187.         case "2":
  188.             break;
  189.         default:
  190.             outputQuery += sql_filter_desc;
  191.     }
  192.  
  193.     // Limit Output
  194.     if(limit > 0) {
  195.         outputQuery += sql_filter_limit;
  196.     }
  197.  
  198.     return outputQuery;
  199. }
  200.  
  201. function buildPlaceholder(filterLang, limit) {
  202.     let output = {};
  203.  
  204.     if(filterLang > 0)
  205.         output.$lang = filterLang;
  206.     if(limit > 0)
  207.         output.$limit = limit;
  208.  
  209.     return output;
  210. }
  211.  
  212.  
  213. /*** Export Functions ***/
  214. exports.getPosts = getPosts;
  215. exports.getPost = getPost;
  216. exports.getPostCreator = getPostCreator;
  217. exports.getCommentsForPost = getCommentsForPost;
  218. exports.getComment = getComment;
  219. exports.addPost = addPost;
  220. exports.addComment = addComment;
  221. exports.updatePost = updatePost;
  222. exports.updateComment = updateComment;
  223. exports.getUserByName = getUserByName;
  224. exports.deletePost = deletePost;
  225. exports.deleteComment = deleteComment;
  226.  
  227. exports.isValidUser = isValidUser;
  228. exports.hashPassword = hashPassword;
  229. exports.comparePassword = comparePassword;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top