1 # This file is part of NIT ( http://www.nitlanguage.org ).
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
7 # http://www.apache.org/licenses/LICENSE-2.0
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
15 # Model for the data of Opportunity
16 module opportunity_model
22 # A SQLiteDB object for `Opportunity`
32 # Creates the tables and triggers for Opportunity (SQLite3 DB)
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 "?"
37 assert create_table
("IF NOT EXISTS people(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, surname TEXT);") else
38 print error
or else "?"
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 "?"
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 "?"
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 "?"
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 "?"
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;")
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};")
62 return new People.from_db
(i
[0].to_i
, i
[1].to_s
, i
[2].to_s
)
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};")
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
)
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};")
80 return new Answer.from_db
(i
[0].to_i
, i
[2].to_s
)
85 # Change an Answer `ansid` for someone with an id `pid` to `resp`
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
)
91 print
"Error while updating answer {ansid}:{pid}"
94 var a
= find_answer_by_id
(ansid
)
96 print
"Error while updating answer {ansid}:{pid}"
100 if p
.commit
(self) then return true
104 # Removes a person in the Database by its `id`
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};")
110 print
"Cannot delete people {id}"
111 print error
or else "Unknown error"
118 # Any kind of Database Object that can be persisted to the database
119 abstract class DBObject
121 # Commits the modifications done to the Object in the database
122 fun commit
(db
: OpportunityDB): Bool is abstract
125 # A Meetup participant, linked to the DB
129 # ID in the Database, -1 if not set
131 # Name of the participant
133 # Surname of the participant
135 # Map of the answers of a Meetup and the answers of the participant
139 var answers
: Map[Answer, Int] = new HashMap[Answer, Int]
141 # To be used internally when fetching the `People` in Database
142 private init from_db
(id
: Int, name
, surname
: String) do
147 # Changes an answer `ans` (or adds it)
148 fun answer
=(ans
: Answer, resp
: Int) do
152 # Loads the answers for a Meetup
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;")
159 var ans
= new Answer.from_db
(i
[0].to_i
, i
[1].to_s
)
160 answers
[ans
] = i
[2].to_i
164 redef fun to_s
do return "{surname.capitalized} {name.capitalized}"
166 redef fun commit
(db
) do
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"
173 id
= db
.last_insert_rowid
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"
181 for i
,j
in answers
do
182 if i
.id
== -1 then i
.commit
(db
)
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"
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"
203 # A `Meetup` is an opportunity of meeting, linked to the database
207 # ID of the meetup, SHA-1 of the informations that are contained
209 # Name for the meetup
211 # SQLite-formatted date : YYYY:DD:MM HH:MM:SS
213 # Place of the meetup
215 # Mode of answering to the meetup (atm supports with or without Maybe)
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
221 init(name
, date
, place
, mode
)
224 # Gets the answers bound to the current `Meetup`
225 fun answers
(db
: OpportunityDB): Array[Answer] do
227 return new Array[Answer]
229 var res
= db
.select
("id, name FROM answers WHERE meetup_id={id.to_sql_string}")
230 var ans
= new Array[Answer]
232 ans
.add
new Answer.from_db
(i
[0].to_i
, i
[1].to_s
)
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]
242 arr
.add
(new People.from_db
(i
[0].to_i
, i
[1].to_s
, i
[2].to_s
))
247 redef fun commit
(db
) do
250 var tmpid
= (name
+ date
+ place
+ time
.to_s
).sha1_to_s
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"
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};")
264 return "Event : {name}\nWhen : {date}\nWhere : {place}"
268 # An answer linked to a Meetup in the database
272 # Name of the answer (title)
274 # Id in the database, -1 if not set
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
279 # To be used internally when fetching the object from Database
280 private init from_db
(id
: Int, name
: String) do
286 if id
!= -1 then return id
290 # Loads the Meetup associated to `self`
292 # REQUIRE: is loaded in database
293 fun load_meetup
(db
: OpportunityDB): Meetup do
295 var res
= db
.select
("meetups.* FROM meetups, answers WHERE answers.id={id} AND answers.meetup_id=meetups.id;")
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
)
299 # If no Meetup could be loaded, the contract was not respected
303 # Counts the number of positive or maybe answers
304 fun count
(db
: OpportunityDB): Int do
305 if id
== -1 then return -1
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
)
310 if meetup
.answer_mode
== 0 then
313 if i
[0].to_i
== 2 then count
+= 1
319 # Counts the score for this particular answer
320 fun score
(db
: OpportunityDB): Int do
321 if id
== -1 then return -1
323 var res
= db
.select
("part_answers.value FROM part_answers WHERE part_answers.id_ans={id};")
330 redef fun commit
(db
) do
332 if m
== null then return false
334 if not m
.commit
(db
) then
335 print
"Error when creating meetup {m}"
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"
345 id
= db
.last_insert_rowid
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"
356 redef fun to_s
do return name