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);") 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)
  72                 end
  73                 return null
  74         end
  75
  76         # Change an Answer `ansid` for someone with an id `pid` to `resp`
  77         #
  78         # Returns `true` if the request was sucessful, false otherwise
  79         fun change_answer(pid: Int, ansid: Int, resp: Bool): Bool do
  80                 var rsp = 0
  81                 if resp then rsp = 1
  82                 var rq = execute("INSERT OR REPLACE INTO part_answers(id_part, id_ans, value) VALUES({pid},{ansid},{rsp});")
  83                 if not rq then
  84                         print "Error while updating answer {ansid}:{pid}"
  85                         print error or else "Unknown error"
  86                         return false
  87                 end
  88                 return true
  89         end
  90
  91         # Removes a person in the Database by its `id`
  92         #
  93         # Returns true if sucessful, false otherwise
  94         fun remove_people_by_id(id: Int): Bool do
  95                 var rq = execute("DELETE FROM people WHERE id = {id};")
  96                 if not rq then
  97                         print "Cannot delete people {id}"
  98                         print error or else "Unknown error"
  99                         return false
 100                 end
 101                 return true
 102         end
 103 end
 104
 105 # Any kind of Database Object that can be persisted to the database
 106 abstract class DBObject
 107
 108         # Commits the modifications done to the Object in the database
 109         fun commit(db: OpportunityDB): Bool is abstract
 110 end
 111
 112 # A Meetup participant, linked to the DB
 113 class People
 114         super DBObject
 115
 116         # ID in the Database, -1 if not set
 117         var id: Int = -1
 118         # Name of the participant
 119         var name: String
 120         # Surname of the participant
 121         var surname: String
 122         # Map of the answers of a Meetup and the answers of the participant
 123         var answers: Map[Answer, Bool] = new HashMap[Answer, Bool]
 124
 125         # To be used internally when fetching the `People` in Database
 126         private init from_db(id: Int, name, surname: String) do
 127                 init(name, surname)
 128                 self.id = id
 129         end
 130
 131         # Changes an answer `ans` (or adds it)
 132         fun answer=(ans: Answer, resp: Bool) do
 133                 answers[ans] = resp
 134         end
 135
 136         # Loads the answers for a Meetup
 137         #
 138         # NOTE: If `self` does not exist in the Database, no answers will be fetched
 139         fun load_answers(db: OpportunityDB, meetup: Meetup) do
 140                 self.answers = new HashMap[Answer, Bool]
 141                 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.to_sql_string} GROUP BY answers.id;")
 142                 for i in req do
 143                         var ans = new Answer.from_db(i[0].to_i, i[1].to_s)
 144                         answers[ans] = false
 145                         if i[2].to_i == 1 then answers[ans] = true
 146                 end
 147         end
 148
 149         redef fun to_s do return "{surname.capitalized} {name.capitalized}"
 150
 151         redef fun commit(db) do
 152                 if id == -1 then
 153                         if not db.execute("INSERT INTO people (name,surname) VALUES ({name.to_sql_string}, {surname.to_sql_string});") then
 154                                 print "Error while adding people {self}"
 155                                 print db.error or else "Unknown error"
 156                                 return false
 157                         end
 158                         id = db.last_insert_rowid
 159                 else
 160                         if not db.execute("UPDATE people SET name={name.to_sql_string}, surname={surname.to_sql_string} WHERE ID={id};") then
 161                                 print "Error while updating people {self}"
 162                                 print db.error or else "Unknown error"
 163                                 return false
 164                         end
 165                 end
 166                 for i,j in answers do
 167                         if i.id == -1 then i.commit(db)
 168                         var val = 0
 169                         if j then val = 1
 170                         if not db.execute("INSERT OR REPLACE INTO part_answers(id_part, id_ans, value) VALUES ({id},{i.id},{val});") then
 171                                 print("Error while adding/replacing part_answers {id}|{i.id}|{j}")
 172                                 print db.error or else "Unknown error"
 173                                 return false
 174                         end
 175                 end
 176                 return true
 177         end
 178 end
 179
 180 # A `Meetup` is an opportunity of meeting, linked to the database
 181 class Meetup
 182         super DBObject
 183
 184         # ID of the meetup, SHA-1 of the informations that are contained
 185         var id: String = ""
 186         # Name for the meetup
 187         var name: String
 188         # SQLite-formatted date : YYYY:DD:MM HH:MM:SS
 189         var date: String
 190         # Place of the meetup
 191         var place: String
 192
 193         # Builds the object with all the informations found in the database
 194         private init from_db(id, name, date, place: String) do
 195                 self.id = id
 196                 self.name = name
 197                 self.date = date
 198                 self.place = place
 199         end
 200
 201         # Gets the answers bound to the current `Meetup`
 202         fun answers(db: OpportunityDB): Array[Answer] do
 203                 if id == "" then
 204                         return new Array[Answer]
 205                 end
 206                 var res = db.select("id, name FROM answers WHERE meetup_id={id.to_sql_string}")
 207                 var ans = new Array[Answer]
 208                 for i in res do
 209                         ans.add new Answer.from_db(i[0].to_i, i[1].to_s)
 210                 end
 211                 return ans
 212         end
 213
 214         # Gets the list of the participants of a `Meetup`
 215         fun participants(db: OpportunityDB): Array[People] do
 216                 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;")
 217                 var arr = new Array[People]
 218                 for i in resp do
 219                         arr.add (new People.from_db(i[0].to_i, i[1].to_s, i[2].to_s))
 220                 end
 221                 return arr
 222         end
 223
 224         redef fun commit(db) do
 225                 if id == "" then
 226                         var tmpid = (name + date + place).sha1_to_s
 227                         if not db.execute("INSERT INTO meetups (id, name, date, place) VALUES({tmpid.to_sql_string}, {name.to_sql_string}, {date.to_sql_string}, {place.to_sql_string});") then
 228                                 print "Error recording entry Meetup {self}"
 229                                 print db.error or else "Null error"
 230                                 return false
 231                         end
 232                         id = tmpid
 233                         return true
 234                 else
 235                         return db.execute("UPDATE meetups (name, date, place) VALUES({name.to_sql_string}, {date.to_sql_string}, {place.to_sql_string}) WHERE ID={id.to_sql_string};")
 236                 end
 237         end
 238
 239         redef fun to_s do
 240                 return "Event : {name}\nWhen : {date}\nWhere : {place}"
 241         end
 242 end
 243
 244 # An answer linked to a Meetup in the database
 245 class Answer
 246         super DBObject
 247
 248         # Name of the answer (title)
 249         var name: String
 250         # Id in the database, -1 if not set
 251         var id: Int = -1
 252         # Meetup the answer is linked to (null while it is not added in the database or set via API)
 253         var meetup: nullable Meetup = null is writable
 254
 255         # To be used internally when fetching the object from Database
 256         private init from_db(id: Int, name: String) do
 257                 init name
 258                 self.id = id
 259         end
 260
 261         # Loads the Meetup associated to `self`
 262         #
 263         # REQUIRE: is loaded in database
 264         fun load_meetup(db: OpportunityDB): Meetup do
 265                 assert id != null
 266                 var res = db.select("meetups.* FROM meetups, answers WHERE answers.id={id} AND answers.meetup_id=meetups.id;")
 267                 for i in res do
 268                         return new Meetup.from_db(i[0].to_s, i[1].to_s, i[2].to_s, i[3].to_s)
 269                 end
 270                 # If no Meetup could be loaded, the contract was not respected
 271                 abort
 272         end
 273
 274         redef fun commit(db) do
 275                 var m = meetup
 276                 if m == null then return false
 277                 if m.id == "" then
 278                         if not m.commit(db) then
 279                                 print "Error when creating meetup {m}"
 280                                 return false
 281                         end
 282                 end
 283                 if id == -1 then
 284                         if not db.execute("INSERT INTO answers (name, meetup_id) VALUES({name.to_sql_string}, {m.id.to_sql_string});") then
 285                                 print "Cannot create {self} in database"
 286                                 print db.error or else "Unknown error"
 287                                 return false
 288                         end
 289                         id = db.last_insert_rowid
 290                 else
 291                         if not db.execute("UPDATE answers (name) VALUES ({name.to_sql_string}) WHERE meetup_id={m.id.to_sql_string};") then
 292                                 print "Error updating {self} in database"
 293                                 print db.error or else "Unknown error"
 294                                 return false
 295                         end
 296                 end
 297                 return true
 298         end
 299
 300         redef fun to_s do return name
 301 end