Class: CheckinsDB
- Inherits:
-
Object
- Object
- CheckinsDB
- Defined in:
- lib/checkins_db.rb
Instance Method Summary collapse
-
#add(checkin_entry) ⇒ Object
Add a checkin to the database.
- #create_tables_cmd ⇒ Object
-
#initialize(db) ⇒ CheckinsDB
constructor
A new instance of CheckinsDB.
-
#log(limit) ⇒ Object
generate SQL query that can pull the log, replace the ids from each table with the emotion/state names, etc.
-
#lookup_emotion_id(emotion) ⇒ Object
look up the emotion ID in this DB for a given emotion name.
-
#lookup_state_id(state) ⇒ Object
look up the state ID in this DB for a given state name.
- #populate_emotional_states_cmd ⇒ Object
- #review_notes ⇒ Object
Constructor Details
#initialize(db) ⇒ CheckinsDB
Returns a new instance of CheckinsDB.
2 3 4 5 6 7 8 9 |
# 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 |
Instance Method Details
#add(checkin_entry) ⇒ Object
Add a checkin to the database
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 |
# 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 ⇒ Object
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
# 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) ⇒ Object
generate SQL query that can pull the log, replace the ids from each table with the emotion/state names, etc
121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
# 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) ⇒ Object
look up the emotion ID in this DB for a given emotion name
111 112 113 |
# 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) ⇒ Object
look up the state ID in this DB for a given state name
116 117 118 |
# 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 ⇒ Object
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
# 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 ⇒ Object
147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
# 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 |