contrib/opportunity/src/opportunity_model.nit

   1 # This file is part of NIT ( http://www.nitlanguage.org ).
   2 #
   3 # Licensed under the Apache License, Version 2.0 (the "License");
   4 # you may not use this file except in compliance with the License.
   5 # You may obtain a copy of the License at
   6 #
   7 #     http://www.apache.org/licenses/LICENSE-2.0
   8 #
   9 # Unless required by applicable law or agreed to in writing, software
  10 # distributed under the License is distributed on an "AS IS" BASIS,
  11 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  12 # See the License for the specific language governing permissions and
  13 # limitations under the License
  14
  15 # Model for the data of Opportunity
  16 module opportunity_model
  17
  18 import sqlite3
  19 import sha1
  20 import serialization
  21
  22 # A SQLiteDB object for `Opportunity`
  23 class OpportunityDB
  24         super Sqlite3DB
  25
  26         init open(x) do
  27                 super
  28
  29                 create_db
  30         end
  31
  32         # Creates the tables and triggers for Opportunity (SQLite3 DB)
  33         fun create_db do
  34                 assert create_table("IF NOT EXISTS meetups (id CHAR(40) PRIMARY KEY, name TEXT, date TEXT, place TEXT, answer_mode INTEGER DEFAULT 0);") else
  35                         print error or else "?"
  36                 end
  37                 assert create_table("IF NOT EXISTS people(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, surname TEXT);") else
  38                         print error or else "?"
  39                 end
  40                 assert create_table("IF NOT EXISTS answers(id INTEGER PRIMARY KEY AUTOINCREMENT, meetup_id CHAR(40), name TEXT, FOREIGN KEY(meetup_id) REFERENCES meetups(id));") else
  41                         print error or else "?"
  42                 end
  43                 assert create_table("IF NOT EXISTS part_answers(id_part INTEGER, id_ans INTEGER, value INTEGER, FOREIGN KEY(id_part) REFERENCES people(id), FOREIGN KEY(id_ans) REFERENCES answers(id));") else
  44                         print error or else "?"
  45                 end
  46                 #NOTE: The following triggers could be replaced by ON DELETE CASCADE clauses
  47                 # Thing is, SQLite does not seem to support those operations (well, not by default, it seems
  48                 # we must re-compile the lib to support it. So, well, let's just create triggers heh.
  49                 assert execute("CREATE TRIGGER IF NOT EXISTS answers_clean AFTER DELETE ON meetups BEGIN DELETE FROM answers WHERE answers.meetup_id=OLD.id;END;") else
  50                         print error or else "?"
  51                 end
  52                 assert execute("CREATE TRIGGER IF NOT EXISTS ans_clean AFTER DELETE ON answers BEGIN DELETE FROM part_answers WHERE OLD.id=part_answers.id_ans;END;") else
  53                         print error or else "?"
  54                 end
  55                 assert execute("CREATE TRIGGER IF NOT EXISTS ppl_clean AFTER DELETE ON people BEGIN DELETE FROM part_answers WHERE OLD.id=part_answers.id_part;END;")
  56         end
  57
  58         # Find a `People` by its id, or `null` if it could not be found
  59         fun find_people_by_id(id: Int): nullable People do
  60                 var req = select("* from people where id={id};")
  61                 for i in req do
  62                         return new People.from_db(i[0].to_i, i[1].to_s, i[2].to_s)
  63                 end
  64                 return null
  65         end
  66
  67         # Find a `Meetup` by its id or `null` if it could not be found
  68         fun find_meetup_by_id(id: String): nullable Meetup do
  69                 var req = select("* FROM meetups where id={id.to_sql_string};")
  70                 for i in req do
  71                         return new Meetup.from_db(i[0].to_s, i[1].to_s, i[2].to_s, i[3].to_s, i[4].to_i)
  72                 end
  73                 return null
  74         end
  75
  76         # Find an `Answer` by its `id` or `null` if it could not be found
  77         fun find_answer_by_id(id: Int): nullable Answer do
  78                 var req = select("* FROM answers WHERE id={id};")
  79                 for i in req do
  80                         return new Answer.from_db(i[0].to_i, i[2].to_s)
  81                 end
  82                 return null
  83         end
  84
  85         # Change an Answer `ansid` for someone with an id `pid` to `resp`
  86         #
  87         # Returns `true` if the request was sucessful, false otherwise
  88         fun change_answer(pid: Int, ansid: Int, resp: Int): Bool do
  89                 var p = find_people_by_id(pid)
  90                 if p == null then
  91                         print "Error while updating answer {ansid}:{pid}"
  92                         return false
  93                 end
  94                 var a = find_answer_by_id(ansid)
  95                 if a == null then
  96                         print "Error while updating answer {ansid}:{pid}"
  97                         return false
  98                 end
  99                 p.answers[a] = resp
 100                 if p.commit(self) then return true
 101                 return false
 102         end
 103
 104         # Removes a person in the Database by its `id`
 105         #
 106         # Returns true if sucessful, false otherwise
 107         fun remove_people_by_id(id: Int): Bool do
 108                 var rq = execute("DELETE FROM people WHERE id = {id};")
 109                 if not rq then
 110                         print "Cannot delete people {id}"
 111                         print error or else "Unknown error"
 112                         return false
 113                 end
 114                 return true
 115         end
 116 end
 117
 118 # Any kind of Database Object that can be persisted to the database
 119 abstract class DBObject
 120
 121         # Commits the modifications done to the Object in the database
 122         fun commit(db: OpportunityDB): Bool is abstract
 123 end
 124
 125 # A Meetup participant, linked to the DB
 126 class People
 127         super DBObject
 128
 129         # ID in the Database, -1 if not set
 130         var id: Int = -1
 131         # Name of the participant
 132         var name: String
 133         # Surname of the participant
 134         var surname: String
 135         # Map of the answers of a Meetup and the answers of the participant
 136         # 0 = No
 137         # 1 = Maybe
 138         # 2 = Yes
 139         var answers: Map[Answer, Int] = new HashMap[Answer, Int]
 140
 141         # To be used internally when fetching the `People` in Database
 142         private init from_db(id: Int, name, surname: String) do
 143                 init(name, surname)
 144                 self.id = id
 145         end
 146
 147         # Changes an answer `ans` (or adds it)
 148         fun answer=(ans: Answer, resp: Int) do
 149                 answers[ans] = resp
 150         end
 151
 152         # Loads the answers for a Meetup
 153         #
 154         # NOTE: If `self` does not exist in the Database, no answers will be fetched
 155         fun load_answers(db: OpportunityDB, meetup: Meetup) do
 156                 self.answers = new HashMap[Answer, Int]
 157                 var req = db.select("answers.id, answers.name, part_answers.value FROM part_answers, answers WHERE part_answers.id_part={id} AND answers.id=part_answers.id_ans AND answers.meetup_id={meetup.id.html_escape.to_sql_string} GROUP BY answers.id;")
 158                 for i in req do
 159                         var ans = new Answer.from_db(i[0].to_i, i[1].to_s)
 160                         answers[ans] = i[2].to_i
 161                 end
 162         end
 163
 164         redef fun to_s do return "{surname} {name}"
 165
 166         redef fun commit(db) do
 167                 if id == -1 then
 168                         if not db.execute("INSERT INTO people (name,surname) VALUES ({name.html_escape.to_sql_string}, {surname.html_escape.to_sql_string});") then
 169                                 print "Error while adding people {self}"
 170                                 print db.error or else "Unknown error"
 171                                 return false
 172                         end
 173                         id = db.last_insert_rowid
 174                 else
 175                         if not db.execute("UPDATE people SET name={name.html_escape.to_sql_string}, surname={surname.html_escape.to_sql_string} WHERE ID={id};") then
 176                                 print "Error while updating people {self}"
 177                                 print db.error or else "Unknown error"
 178                                 return false
 179                         end
 180                 end
 181                 for i,j in answers do
 182                         if i.id == -1 then i.commit(db)
 183                         var val = j
 184                         var s = db.select("* FROM part_answers WHERE id_part={id} AND id_ans={i.id}")
 185                         if s != null and s.iterator.is_ok then
 186                                 if not db.execute("UPDATE part_answers SET value={j} WHERE id_part={id} AND id_ans={i.id};") then
 187                                         print "Error while updating part_answers {id}|{i.id} = {j}"
 188                                         print db.error or else "Unknown error"
 189                                         return false
 190                                 end
 191                                 continue
 192                         end
 193                         if not db.execute("INSERT INTO part_answers(id_part, id_ans, value) VALUES ({id},{i.id},{val});") then
 194                                 print("Error while adding part_answers {id}|{i.id}|{j}")
 195                                 print db.error or else "Unknown error"
 196                                 return false
 197                         end
 198                 end
 199                 return true
 200         end
 201 end
 202
 203 # A `Meetup` is an opportunity of meeting, linked to the database
 204 class Meetup
 205         super DBObject
 206
 207         # ID of the meetup, SHA-1 of the informations that are contained
 208         var id: String = ""
 209         # Name for the meetup
 210         var name: String
 211         # SQLite-formatted date : YYYY:DD:MM HH:MM:SS
 212         var date: String
 213         # Place of the meetup
 214         var place: String
 215         # Mode of answering to the meetup (atm supports with or without Maybe)
 216         var answer_mode: Int
 217
 218         # Builds the object with all the informations found in the database
 219         private init from_db(id, name, date, place: String, mode: Int) do
 220                 self.id = id
 221                 init(name, date, place, mode)
 222         end
 223
 224         # Gets the answers bound to the current `Meetup`
 225         fun answers(db: OpportunityDB): Array[Answer] do
 226                 if id == "" then
 227                         return new Array[Answer]
 228                 end
 229                 var res = db.select("id, name FROM answers WHERE meetup_id={id.to_sql_string}")
 230                 var ans = new Array[Answer]
 231                 for i in res do
 232                         ans.add new Answer.from_db(i[0].to_i, i[1].to_s)
 233                 end
 234                 return ans
 235         end
 236
 237         # Gets the list of the participants of a `Meetup`
 238         fun participants(db: OpportunityDB): Array[People] do
 239                 var resp = db.select("people.* FROM people, meetups, answers, part_answers WHERE meetups.id={id.to_sql_string} AND answers.meetup_id={id.to_sql_string} AND part_answers.id_ans=answers.id AND people.id=part_answers.id_part GROUP BY people.id;")
 240                 var arr = new Array[People]
 241                 for i in resp do
 242                         arr.add (new People.from_db(i[0].to_i, i[1].to_s, i[2].to_s))
 243                 end
 244                 return arr
 245         end
 246
 247         redef fun commit(db) do
 248                 if id == "" then
 249                         var time = get_time
 250                         var tmpid = (name + date + place + time.to_s).sha1.hexdigest
 251                         if not db.execute("INSERT INTO meetups (id, name, date, place, answer_mode) VALUES({tmpid.to_sql_string}, {name.html_escape.to_sql_string}, {date.html_escape.to_sql_string}, {place.html_escape.to_sql_string}, {answer_mode});") then
 252                                 print "Error recording entry Meetup {self}"
 253                                 print db.error or else "Null error"
 254                                 return false
 255                         end
 256                         id = tmpid
 257                         return true
 258                 else
 259                         return db.execute("UPDATE meetups SET name={name.html_escape.to_sql_string}, date={date.html_escape.to_sql_string}, place={place.html_escape.to_sql_string}, answer_mode={answer_mode} WHERE ID={id.to_sql_string};")
 260                 end
 261         end
 262
 263         redef fun to_s do
 264                 return "Event : {name}\nWhen : {date}\nWhere : {place}"
 265         end
 266 end
 267
 268 # An answer linked to a Meetup in the database
 269 class Answer
 270         super DBObject
 271
 272         # Name of the answer (title)
 273         var name: String
 274         # Id in the database, -1 if not set
 275         var id: Int = -1
 276         # Meetup the answer is linked to (null while it is not added in the database or set via API)
 277         var meetup: nullable Meetup = null is writable
 278
 279         # To be used internally when fetching the object from Database
 280         private init from_db(id: Int, name: String) do
 281                 init name
 282                 self.id = id
 283         end
 284
 285         redef fun hash do
 286                 if id != -1 then return id
 287                 return super
 288         end
 289
 290         # Loads the Meetup associated to `self`
 291         #
 292         # REQUIRE: is loaded in database
 293         fun load_meetup(db: OpportunityDB): Meetup do
 294                 assert id != -1
 295                 var res = db.select("meetups.* FROM meetups, answers WHERE answers.id={id} AND answers.meetup_id=meetups.id;")
 296                 for i in res do
 297                         return new Meetup.from_db(i[0].to_s, i[1].to_s, i[2].to_s, i[3].to_s, i[4].to_i)
 298                 end
 299                 # If no Meetup could be loaded, the contract was not respected
 300                 abort
 301         end
 302
 303         # Counts the number of positive or maybe answers
 304         fun count(db: OpportunityDB): Int do
 305                 if id == -1 then return -1
 306                 var count = 0
 307                 var res = db.select("part_answers.value FROM part_answers WHERE part_answers.id_ans={id};")
 308                 if meetup == null then meetup = load_meetup(db)
 309                 for i in res do
 310                         if meetup.answer_mode == 0 then
 311                                 count += i[0].to_i
 312                         else
 313                                 if i[0].to_i == 2 then count += 1
 314                         end
 315                 end
 316                 return count
 317         end
 318
 319         # Counts the score for this particular answer
 320         fun score(db: OpportunityDB): Int do
 321                 if id == -1 then return -1
 322                 var score = 0
 323                 var res = db.select("part_answers.value FROM part_answers WHERE part_answers.id_ans={id};")
 324                 for i in res do
 325                         score += i[0].to_i
 326                 end
 327                 return score
 328         end
 329
 330         redef fun commit(db) do
 331                 var m = meetup
 332                 if m == null then return false
 333                 if m.id == "" then
 334                         if not m.commit(db) then
 335                                 print "Error when creating meetup {m}"
 336                                 return false
 337                         end
 338                 end
 339                 if id == -1 then
 340                         if not db.execute("INSERT INTO answers (name, meetup_id) VALUES({name.html_escape.to_sql_string}, {m.id.to_sql_string});") then
 341                                 print "Cannot create {self} in database"
 342                                 print db.error or else "Unknown error"
 343                                 return false
 344                         end
 345                         id = db.last_insert_rowid
 346                 else
 347                         if not db.execute("UPDATE answers SET name=({name.html_escape.to_sql_string}) WHERE meetup_id={m.id.to_sql_string};") then
 348                                 print "Error updating {self} in database"
 349                                 print db.error or else "Unknown error"
 350                                 return false
 351                         end
 352                 end
 353                 return true
 354         end
 355
 356         redef fun to_s do return name
 357 end