class CheckinsDB
Public Class Methods
new(db)
click to toggle source
# File lib/checkins_db.rb, line 2 def initialize(db) @db = db @db.execute_batch(create_tables_cmd) # Check if the emotions and states tables have been populated, and, if not, do so. unless @db.execute("SELECT name FROM emotions;").any? { |row| row[0] == "joy" } @db.execute_batch(populate_emotional_states_cmd) end end
Public Instance Methods
add(checkin_entry)
click to toggle source
Add a checkin to the database
# File lib/checkins_db.rb, line 91 def add(checkin_entry) # replace emotion name with matching id emotion_id = lookup_emotion_id(checkin_entry[:emotion]) checkin_entry[:emotion] = emotion_id # replace state name with matching id, if exists if checkin_entry[:state] state_id = lookup_state_id(checkin_entry[:state]) checkin_entry[:state] = state_id end # turn note to self into blob, if exists if checkin_entry[:note] checkin_entry[:note] = checkin_entry[:note].to_blob end @db.execute("INSERT INTO checkins (time, emotionID, stateID, intensity, trigger, noteToSelf) VALUES (?, ?, ?, ?, ?, ?)", checkin_entry[:time], checkin_entry[:emotion], checkin_entry[:state], checkin_entry[:intensity], checkin_entry[:trigger], checkin_entry[:note]) end
create_tables_cmd()
click to toggle source
# File lib/checkins_db.rb, line 11 def create_tables_cmd # SQL command to make the tables if they don't exist. return <<-SQL CREATE TABLE IF NOT EXISTS emotions( id INT PRIMARY KEY, name VARCHAR(32) ); CREATE TABLE IF NOT EXISTS states( id INT PRIMARY KEY, name VARCHAR(32), emotionID INT, FOREIGN KEY (emotionID) REFERENCES emotions(id) ); CREATE TABLE IF NOT EXISTS checkins( time VARCHAR(32) PRIMARY KEY, emotionID INT, stateID INT, intensity INT, trigger VARCHAR(255), noteToSelf BLOB, FOREIGN KEY (emotionID) REFERENCES emotions(id), FOREIGN KEY (stateID) REFERENCES state(id) ); SQL end
log(limit)
click to toggle source
generate SQL query that can pull the log, replace the ids from each table with the emotion/state names, etc
# File lib/checkins_db.rb, line 121 def log(limit) qry_checkins_cmd = <<-SQL SELECT time, emotions.name, states.name, intensity FROM (checkins LEFT JOIN emotions ON checkins.emotionID = emotions.id) LEFT JOIN states ON checkins.stateID = states.id; SQL rows = @db.execute(qry_checkins_cmd) result = "" # make header row result += "Checkin time | Emotion | Emotional state | Intensity \n" result += "---------------------|--------------|------------------|-----------\n" # initialize incrementer to test if going past limit i = 1 # print that SQL query for each row, going back as long as incrementer is less than limit rows.reverse_each do |row| next if i > limit # turn all null or nil values and all numbers into strings row.map!(&:to_s) result += row[0].ljust(21) + "| " + row[1].ljust(13) + "| " + row[2].ljust(17) + "| " + row[3].ljust(9) + "\n" i += 1 end return result end
lookup_emotion_id(emotion)
click to toggle source
look up the emotion ID in this DB for a given emotion name
# File lib/checkins_db.rb, line 111 def lookup_emotion_id(emotion) emotion_id = @db.get_first_value("SELECT id FROM emotions WHERE name = ?", emotion) end
lookup_state_id(state)
click to toggle source
look up the state ID in this DB for a given state name
# File lib/checkins_db.rb, line 116 def lookup_state_id(state) state_id = @db.get_first_value("SELECT id FROM states WHERE name = ?", state) end
populate_emotional_states_cmd()
click to toggle source
# File lib/checkins_db.rb, line 36 def populate_emotional_states_cmd return <<-SQL INSERT OR IGNORE INTO emotions (id, name) VALUES (1, "anger"); INSERT OR IGNORE INTO emotions (id, name) VALUES (2, "joy"); INSERT OR IGNORE INTO emotions (id, name) VALUES (3, "sadness"); INSERT OR IGNORE INTO emotions (id, name) VALUES (4, "hurt"); INSERT OR IGNORE INTO emotions (id, name) VALUES (5, "fear"); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (1, "bothered", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (2, "annoyed", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (3, "bitter", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (4, "angry", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (5, "irritated", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (6, "disgusted", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (7, "frustrated", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (8, "exasperated", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (9, "furious", 1); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (10, "content", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (11, "peaceful", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (12, "relaxed", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (13, "cheerful", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (14, "satisfied", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (15, "joyous", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (16, "excited", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (17, "ecstatic", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (18, "happy", 2); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (19, "sad", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (20, "depressed", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (21, "distraught", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (22, "despair", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (23, "melancholy", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (24, "grief", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (25, "helpless", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (26, "hopeless", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (27, "miserable", 3); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (28, "lonely", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (29, "homesick", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (30, "abandoned", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (31, "embarrassed", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (32, "shame", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (33, "guilt", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (34, "foolish", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (35, "humiliated", 4); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (36, "uncertain", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (37, "worried", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (38, "anxious", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (39, "frightened", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (40, "scared", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (41, "nervous", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (42, "afraid", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (43, "terrified", 5); INSERT OR IGNORE INTO states (id, name, emotionID) VALUES (44, "overwhelmed", 5); SQL end
review_notes()
click to toggle source
# File lib/checkins_db.rb, line 147 def review_notes # generate SQL queries that pull the times and all from the noteToSelf attribute, numbered pull_notes_cmd = <<-SQL SELECT time, emotions.name, states.name, trigger, noteToSelf FROM (checkins JOIN emotions ON checkins.emotionID = emotions.id) LEFT JOIN states ON checkins.stateID = states.id; SQL entries = @db.execute(pull_notes_cmd) result = "" entries.each do |entry| next unless entry[3] or entry[4] result += "Date and time: " + entry[0] + "\n" result += "Emotional state: " + entry[1] result += ", " + entry[2] if entry[2] # checking if state is nil result += "\n" result += "Trigger: " + entry[3] + "\n" if entry[3] result += "\n" result += "Note to self: \n\n" + entry[4] + "\n" if entry[4] result += "\n" result += " -=- -=- -=- -=- -=- -=- -=- -=- \n" result += "\n" end return result end