119 lines
7.5 KiB
JavaScript
119 lines
7.5 KiB
JavaScript
'use strict';
|
|
const db = require('../db')
|
|
const crypto = require('crypto')
|
|
const dayjs = require('dayjs')
|
|
const insertHashedPwd = (UserID, password) => {
|
|
return new Promise((resolve, reject) => {
|
|
const hashLen = 64;
|
|
const salt = crypto.randomBytes(16).toString("hex");
|
|
const sql = "UPDATE User SET password=?, salt=? WHERE User.ID=?"
|
|
crypto.scrypt(password,salt, hashLen, function(err,hash){
|
|
if(err) reject(err);
|
|
db.run(sql,[hash.toString("hex"),salt,UserID], function(err){
|
|
if(err)
|
|
reject(err);
|
|
resolve();
|
|
});
|
|
});
|
|
});
|
|
}
|
|
const createDB = () => {
|
|
return new Promise((resolve, reject) => {
|
|
db.serialize(() => {
|
|
db.run(`CREATE TABLE "User" (
|
|
"ID" INTEGER NOT NULL UNIQUE,
|
|
"Username" TEXT NOT NULL,
|
|
"Password" TEXT NOT NULL,
|
|
"Salt" TEXT NOT NULL,
|
|
"Secret" TEXT NULL,
|
|
"Type" TEXT NOT NULL,
|
|
PRIMARY KEY("ID" AUTOINCREMENT));`)
|
|
/* At least five users, 2 are Administrators*/
|
|
.run(`INSERT INTO User(Username, Password, Salt, Secret, Type) VALUES('AdminUno', 'placeholder', 'placeholder', 'LXBSMDTMSP2I5XFXIYRGFVWSFI', 'administrator')`)
|
|
.run(`INSERT INTO User(Username, Password, Salt, Secret, Type) VALUES('AdminDue', 'ph', 'ph', 'LXBSMDTMSP2I5XFXIYRGFVWSFI', 'administrator')`)
|
|
.run(`INSERT INTO User(Username, Password, Salt, Type) VALUES('UtenteUno', 'ph','ph', 'viewer')`)
|
|
.run(`INSERT INTO User(Username, Password, Salt, Type) VALUES('UtenteDue', 'ph','ph', 'viewer')`)
|
|
.run(`INSERT INTO User(Username, Password, Salt, Type) VALUES('UtenteTre', 'ph','ph', 'viewer')`)
|
|
.run(`CREATE TABLE "Post" (
|
|
"Title" TEXT NOT NULL UNIQUE,
|
|
"ID" INTEGER NOT NULL UNIQUE,
|
|
"AuthorID" INTEGER NOT NULL,
|
|
"MaxComments" INTEGER,
|
|
"Publication" INTEGER NOT NULL,
|
|
"Text" TEXT NOT NULL,
|
|
PRIMARY KEY("ID" AUTOINCREMENT),
|
|
FOREIGN KEY("AuthorID") REFERENCES "User"("ID"));`)
|
|
/* Four users including two administrators should have published two posts each */
|
|
/*Post of AdminUno*/
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 1',1,'2025-06-06 12:20:05','Lorem ipsum dolor sit amet consectetur adipiscing elit. ', -1)`)
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 2',1,'2025-06-12 10:59:01','Quisque faucibus ex sapien vitae pellentesque sem placerat.', -1)`)
|
|
/*Post of AdminDue*/
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 3',2,'2025-06-18 08:23:12','In id cursus mi pretium tellus duis convallis.', -1)`)
|
|
.run(`INSERT INTO Post (Title, AuthorID, MaxComments, Publication, Text) VALUES('Example post 4',2,2,'2025-06-01 23:20:11','Tempus leo eu aenean sed diam urna tempor.')`)
|
|
/*Post of UtenteUno */
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 5',3,'2025-06-09 07:20:05','Pulvinar vivamus fringilla lacus nec metus bibendum egestas.', -1)`)
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 6',3,'2025-06-20 07:25:59','Iaculis massa nisl malesuada lacinia integer nunc posuere.', -1)`)
|
|
/*Post of UtenteDue */
|
|
.run(`INSERT INTO Post (Title, AuthorID, MaxComments, Publication, Text) VALUES('Example post 7',4, 3, '2025-06-03 13:20:05','Ut hendrerit semper vel class aptent taciti sociosqu.')`)
|
|
.run(`INSERT INTO Post (Title, AuthorID, Publication, Text, MaxComments) VALUES('Example post 8',4,'2025-06-15 16:20:00','Ad litora torquent per conubia nostra inceptos himenaeos.', -1)`)
|
|
.run(`CREATE TABLE "Comment" (
|
|
"ID" INTEGER NOT NULL UNIQUE,
|
|
"Text" TEXT NOT NULL,
|
|
"Publication" TEXT NOT NULL,
|
|
"AuthorID" INTEGER,
|
|
"PostID" INTEGER NOT NULL,
|
|
PRIMARY KEY("ID" AUTOINCREMENT),
|
|
FOREIGN KEY("AuthorID") REFERENCES "User"("ID"),
|
|
FOREIGN KEY("PostID") REFERENCES "Post"("ID"));`)
|
|
// Comments for PostID 4 (AdminDue's post with MaxComments = 2)
|
|
// This post should have 1 comment (one less than the limit)
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('This post has a comment limit, interesting!', '2025-06-05 14:35:10', 3, 4);`) // UtenteUno comments on AdminDue's post
|
|
// Comments ensuring each user has comments from other users (2-3 comments each)
|
|
//Comments from AdminUno (ID: 1) on other users' posts
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Good point!', '2025-06-18 09:02:45', 1, 5);`) // AdminUno on UtenteUno's post
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Enjoyed reading this.', '2025-06-01 23:59:01', 1, 7);`) // AdminUno on UtenteDue's post
|
|
// Comments from AdminDue (ID: 2) on other users' posts
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Nice work!', '2025-06-11 04:17:30', 2, 6);`) // AdminDue on UtenteUno's post
|
|
// Comments from UtenteUno (ID: 3) on other users' posts (already one on PostID 4)
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Totally agree!', '2025-06-14 10:05:00', 3, 2);`) // UtenteUno on AdminUno's post
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Great thoughts.', '2025-06-03 01:50:55', 3, 3);`) // UtenteUno on AdminDue's post
|
|
// Comments from UtenteDue (ID: 4) on other users' posts
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Well said.', '2025-06-19 16:30:20', 4, 1);`) // UtenteDue on AdminUno's post
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('This is very helpful!', '2025-06-02 21:12:05', 4, 5);`) // UtenteDue on UtenteUno's post
|
|
// Comments from UtenteTre (ID: 5) on other users' posts
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Appreciate your perspective.', '2025-06-16 12:08:50', 5, 3);`) // UtenteTre on AdminDue's post
|
|
.run(`INSERT INTO Comment(Text, Publication, AuthorID, PostID) VALUES('Inspiring content.', '2025-06-10 06:25:30', 5, 8);`) // UtenteTre on UtenteDue's post
|
|
// Anonymous comments
|
|
.run(`INSERT INTO Comment(Text, Publication, PostID) VALUES('Appreciate my anonymous perspective.', '2025-06-04 17:00:00', 1);`)
|
|
.run(`INSERT INTO Comment(Text, Publication, PostID) VALUES('Dont appreciate my anonymous perspective.', '2025-06-17 22:45:10', 1);`)
|
|
.run(`INSERT INTO Comment(Text, Publication, PostID) VALUES('Dont appreciate my anonymous perspective.', '2025-06-13 03:00:00', 3);`)
|
|
.run(`INSERT INTO Comment(Text, Publication, PostID) VALUES('Totally an anonymous comment, you will never know the author!', '2025-06-13 03:00:00', 7);`)
|
|
// Create Interesting table
|
|
.run(`CREATE TABLE "Interesting" (
|
|
"UserID" INTEGER NOT NULL,
|
|
"CommentID" INTEGER NOT NULL,
|
|
PRIMARY KEY("UserID", "CommentID"),
|
|
FOREIGN KEY("CommentID") REFERENCES "Comment"("ID"),
|
|
FOREIGN KEY("UserID") REFERENCES "User"("ID"));`).run(`INSERT INTO Interesting(UserID,CommentID) VALUES(1,1)`)
|
|
.run(`INSERT INTO Interesting(UserID,CommentID) VALUES(2,1)`)
|
|
.run(`INSERT INTO Interesting(UserID,CommentID) VALUES(1,2)`)
|
|
.run(`INSERT INTO Interesting(UserID,CommentID) VALUES(1,3)`)
|
|
.run(`INSERT INTO Interesting(UserID,CommentID) VALUES(2,2)`);
|
|
});
|
|
resolve();
|
|
});
|
|
}
|
|
//Ugly but ensures that the passwords are updated after the creation of db
|
|
createDB().then(
|
|
insertHashedPwd(1,"PasswordAdmin1").then(
|
|
insertHashedPwd(2,"PasswordAdmin2").then(
|
|
insertHashedPwd(3,"PasswordUtente1").then(
|
|
insertHashedPwd(4,"PasswordUtente2").then(
|
|
insertHashedPwd(5,"PasswordUtente3")
|
|
)
|
|
)
|
|
)
|
|
)
|
|
);
|
|
|