CS411-Project-Misc

es.json

PUT /comments/
{
  "settings": {
    "analysis": {
      "filter": {
        "autocomplete_filter": {
          "type": "edge_ngram",
          "min_gram": 1,
          "max_gram": 20
        }
      },
      "analyzer": {
        "autocomplete": { 
          "type": "custom",
          "tokenizer": "whitespace",
          "filter": [
            "lowercase",
            "autocomplete_filter"
          ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "teacherID": {
        "type": "keyword"
      },
      "id": {
        "index": true,
        "type": "keyword"
      },
      "legacyId": {
        "index": true,
        "type": "keyword"
      },
      "firstName": {
        "index": true,
        "type": "text",
        "similarity": "BM25",
        "analyzer": "autocomplete", 
        "search_analyzer": "autocomplete" 
      },
      "lastName": {
        "index": true,
        "type": "text",
        "similarity": "BM25",
        "analyzer": "autocomplete", 
        "search_analyzer": "autocomplete" 
      },
      "fullName": {
        "index": true,
        "type": "text",
        "similarity": "BM25", 
        "analyzer": "autocomplete", 
        "search_analyzer": "autocomplete" 
      },
      "class": {
        "index": true,
        "type": "keyword",
        "similarity": "BM25"
      },
      "tags": {
        "index": true,
        "type": "text",
        "similarity": "BM25", 
        "analyzer": "autocomplete", 
        "search_analyzer": "autocomplete" 
      },
      "isAttendanceMandatory": {
        "type": "boolean"
      },
      "clarity": {
        "type": "integer"
      },
      "difficultyRating": {
        "type": "integer"
      },
      "helpfulRating": {
        "type": "integer"
      },
      "wouldTakeAgain": {
        "type": "boolean"
      },
      "isForCredit": {
        "type": "boolean"
      },
      "isOnline": {
        "type": "boolean"
      },
      "grade": {
        "type": "text"
      },
      "comment": {
        "index": true,
        "type": "text",
        "similarity": "BM25", 
        "analyzer": "autocomplete", 
        "search_analyzer": "autocomplete" 
      },
      "date": {
        "type": "date"
      }
    }
  }
}

GET /comments/_count
GET /ratings/_count

queries.sql

DROP PROCEDURE SearchCourse;

DELIMITER ;;

CREATE PROCEDURE SearchCourse(IN CRN INT, CourseName VARCHAR(255), SubjectID VARCHAR(5), ID INT, IsCurrentTerm BOOL, NumRecords INT)
BEGIN
    DECLARE CurrentTerm INT;
    SELECT TermID INTO CurrentTerm FROM Terms WHERE AttendingTerm = TRUE;

    SET NumRecords = IFNULL(NumRecords, 50);
    SET IsCurrentTerm = IFNULL(IsCurrentTerm, TRUE);

    IF CRN IS NULL AND CourseName IS NULL AND SubjectID IS NULL AND ID IS NULL THEN
        SIGNAL SQLSTATE VALUE '23333'
        SET MESSAGE_TEXT = 'Don\'t even try to blow up the database! You must specify at least one search field!';
    END IF ;

    SELECT c.TermID, t.TermName, s.CRN, c.SubjectID, c.CourseID, c.CourseName,
           m.TypeName, i.FullName, m.DaysOfWeek, m.StartTime, m.EndTime, s.StartDate
    FROM Terms t NATURAL JOIN Sections s NATURAL JOIN Meetings m NATURAL JOIN Courses c NATURAL JOIN Instructors i
    WHERE s.CRN = IFNULL(CRN, s.CRN) AND c.CourseName LIKE CONCAT('%', IFNULL(CourseName, c.CourseName), '%') AND
          c.SubjectID = IFNULL(SubjectID, c.SubjectID) AND c.CourseID = IFNULL(ID, c.CourseID) AND
          (NOT IsCurrentTerm OR c.TermID = CurrentTerm)
    ORDER BY TermID DESC
    LIMIT NumRecords;

END ;;

DELIMITER ;

SELECT COUNT(CourseID), TermID, TermName FROM Courses NATURAL JOIN Terms GROUP BY TermID;

CALL SearchCourse(NULL, NULL, 'CS', NULL, FALSE, NULL);

SELECT * FROM Subjects WHERE TermID = 120198;
DELETE FROM Subjects WHERE TermID = 120198;

CALL SearchCourse(NULL, NULL, 'CS', NULL, FALSE, NULL);

SELECT COUNT(CRN), TermName FROM Meetings NATURAL JOIN Terms GROUP BY TermID;

SELECT TermName, COUNT(SubjectID) FROM Subjects NATURAL JOIN Terms GROUP BY TermID;
SELECT TermName, COUNT(CourseID) FROM Courses NATURAL JOIN Terms GROUP BY TermID;

DROP TABLE Remarks;

CREATE TABLE Remarks(
    RID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    UID VARCHAR(255) NOT NULL,
    CRN INT NOT NULL,
    TermID INT NOT NULL,
    Remark VARCHAR(255) NOT NULL,
    FOREIGN KEY (CRN) REFERENCES Enrollments(CRN),
    FOREIGN KEY (TermID) REFERENCES Enrollments(TermID),
    FOREIGN KEY (UID) REFERENCES Enrollments(UUID) ON UPDATE CASCADE ON DELETE CASCADE
);

DROP PROCEDURE AddRemark;

DELIMITER ;;

CREATE PROCEDURE AddRemark(IN Email VARCHAR(255), CRN INT, TermID INT, Remark VARCHAR(255))
BEGIN
    DECLARE UID VARCHAR(255);

    IF Email IS NULL OR CRN IS NULL OR TermID IS NULL OR Remark IS NULL THEN
        SIGNAL SQLSTATE VALUE '23333'
        SET MESSAGE_TEXT = 'Missing field!';
    END IF ;

    SELECT UUID INTO UID FROM Users u WHERE u.Email = Email;

    INSERT INTO Remarks(UID, CRN, TermID, Remark) VALUE (UID, CRN, TermID, Remark);
    SELECT RID, UID, CRN, TermID, Remark FROM Remarks WHERE RID = LAST_INSERT_ID();
END ;;

DELIMITER ;

CALL AddRemark('jyh@zch.com', 40317, 120205, 'tql');

DROP PROCEDURE ModifyRemark;

DELIMITER ;;

CREATE PROCEDURE ModifyRemark(IN TargetRID INT, userEmail VARCHAR(255), TargetCRN INT, TargetTermID INT, NewRemark VARCHAR(255))
BEGIN
    DECLARE userID VARCHAR(255);

    IF TargetRID IS NULL THEN
        SIGNAL SQLSTATE VALUE '23333'
        SET MESSAGE_TEXT = 'Missing field!';
    END IF ;

    SELECT UUID INTO userID FROM Users u WHERE u.Email = userEmail;

    UPDATE Remarks SET Remark = NewRemark WHERE RID = TargetRID;
    SELECT RID, UID, CRN, TermID, Remark FROM Remarks WHERE RID = TargetRID;
END ;;

DELIMITER ;

CALL ModifyRemark(7, NULL, NULL, NULL, 'wtf');

DROP PROCEDURE IF EXISTS GetRemark;

DELIMITER ;;

CREATE PROCEDURE GetRemark(IN UserEmail VARCHAR(255), TargetCRN INT, TargetTermID INT, SearchString VARCHAR(255))
BEGIN
    DECLARE UserID VARCHAR(255) DEFAULT NULL;

    IF UserEmail IS NULL AND TargetCRN IS NULL AND TargetTermID IS NULL AND SearchString IS NULL THEN
        SIGNAL SQLSTATE VALUE '23333'
        SET MESSAGE_TEXT = 'You must specify at least one search field!';
    END IF ;

    SELECT UUID INTO UserID FROM Users u WHERE u.Email = UserEmail;

    SELECT RID, Remark, TermID, CRN, CourseName, SubjectID, CourseID
    FROM Remarks NATURAL JOIN Courses NATURAL JOIN Sections
    WHERE CRN = IFNULL(TargetCRN, CRN) AND TermID = IFNULL(TargetTermID, TermID) AND
          UID = IFNULL(UserID, UID) AND
          Remark LIKE IFNULL(CONCAT('%', SearchString, '%'), Remark);
END ;;

DELIMITER ;

CALL AddRemark('jyh@zch.com', 10005, 120205, 'tql');
CALL GetRemark('jyh@zch.com', NULL, NULL, NULL);

CREATE TABLE GPA(
    TermID INT NOT NULL,
    TermName VARCHAR(20) NOT NULL,
    CRN INT NOT NULL,
    SubjectID VARCHAR(10) NOT NULL,
    CourseID INT NOT NULL,
    CourseName VARCHAR(255) NOT NULL,
    TypeCode VARCHAR(5) NOT NULL,
    Ap INT,
    A INT,
    Am INT,
    Bp INT,
    B INT,
    Bm INT,
    Cp INT,
    C INT,
    Cm INT,
    Dp INT,
    D INT,
    Dm INT,
    F INT,
    W INT,
    PrimaryInstructor VARCHAR(255)
);

DROP PROCEDURE IF EXISTS GetDetailGPAInfo;

DELIMITER ;;

CREATE PROCEDURE GetDetailGPAInfo(IN Term INT, Subject VARCHAR(10), Course INT,
                                  Name VARCHAR(255), CRN INT, Instructor VARCHAR(255), Num INT)
BEGIN
    SET Num = IFNULL(Num, 50);

    IF Term IS NULL AND Subject IS NULL AND Course IS NULL AND
       CRN IS NULL AND Instructor IS NULL AND Name IS NULL THEN
        SIGNAL SQLSTATE '23333'
        SET MESSAGE_TEXT = 'You must specify at least one search field!!!';
    END IF ;

    SELECT *
    FROM GPA
    WHERE TermID = IFNULL(Term, TermID) AND SubjectID = IFNULL(Subject, SubjectID) AND
          CourseID = IFNULL(Course, CourseID) AND CourseName LIKE CONCAT('%', IFNULL(Name, CourseName), '%') AND
          PrimaryInstructor LIKE CONCAT('%', IFNULL(Instructor, PrimaryInstructor), '%')
    GROUP BY TermID, SubjectID, CourseID, CRN
    ORDER BY TermID DESC
    LIMIT Num;

END ;;

DELIMITER ;

CALL GetDetailGPAInfo(120198, 'CS', NULL, NULL, NULL, NULL, 100);

SELECT * FROM Enrollments NATURAL JOIN Sections NATURAL JOIN Courses WHERE UUID = 'ec1a5acf-ccf4-11ea-8f1c-06f7f522912b';

CALL GetDefaultTerm();

INSERT INTO Enrollments VALUE ('ec1a5acf-ccf4-11ea-8f1c-06f7f522912b', 120205, 40317);

SELECT DISTINCT CourseID, SubjectID, Credits
FROM Enrollments NATURAL JOIN Sections
WHERE uuid = 'ec1a5acf-ccf4-11ea-8f1c-06f7f522912b' AND TermID = 120205;

SELECT * FROM GPA WHERE SubjectID = 'MICR' AND CourseID = 599;

CREATE TABLE Ratings(
    TeacherID INT NOT NULL PRIMARY KEY,
    NumRatings INT NOT NULL,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    AvgRating REAL NOT NULL
);

SELECT * FROM GPA WHERE SubjectID = 'MICR' AND CourseID = 599;

DROP TABLE IF EXISTS Comments;

CREATE TABLE Comments(
    CID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ID VARCHAR(63) NOT NULL,
    LegacyID INT NOT NULL ,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Class VARCHAR(255) NOT NULL,
    Tags VARCHAR(255) NOT NULL,
    IsAttendanceMandatory BOOL,
    Clarity INT,
    DifficultyRating INT,
    HelpfulRating INT,
    WouldTakeAgain BOOL,
    IsForCredit BOOL,
    IsOnline BOOL,
    Grade VARCHAR(7),
    Comment TEXT,
    Date DATETIME
);

CREATE TABLE WeChatInfo(
    wxid VARCHAR(255) NOT NULL PRIMARY KEY,
    Email VARCHAR(255) NOT NULL,
    FOREIGN KEY (Email) REFERENCES Users(Email)
);


CREATE TABLE Chatrooms(
    RoomID VARCHAR(255) NOT NULL,
    RoomTopic VARCHAR(255) NOT NULL,
    TermID INT NOT NULL,
    SubjectID VARCHAR(255) NOT NULL,
    CourseID VARCHAR(255) NOT NULL,
    PRIMARY KEY (TermID, SubjectID, CourseID),
    FOREIGN KEY (TermID) REFERENCES Terms(TermID)
);

INSERT INTO Users VALUE (UUID(), 'awsl@cs411.com', 'xswl', PASSWORD('passwd'));

SELECT TermName, COUNT(CourseID) FROM Courses NATURAL JOIN Terms GROUP BY TermID;