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);") 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
)
76 # Change an Answer `ansid` for someone with an id `pid` to `resp`
78 # Returns `true` if the request was sucessful, false otherwise
79 fun change_answer
(pid
: Int, ansid
: Int, resp
: Bool): Bool do
82 var rq
= execute
("INSERT OR REPLACE INTO part_answers(id_part, id_ans, value) VALUES({pid},{ansid},{rsp});")
84 print
"Error while updating answer {ansid}:{pid}"
85 print error
or else "Unknown error"
91 # Removes a person in the Database by its `id`
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};")
97 print
"Cannot delete people {id}"
98 print error
or else "Unknown error"
105 # Any kind of Database Object that can be persisted to the database
106 abstract class DBObject
108 # Commits the modifications done to the Object in the database
109 fun commit
(db
: OpportunityDB): Bool is abstract
112 # A Meetup participant, linked to the DB
116 # ID in the Database, -1 if not set
118 # Name of the participant
120 # Surname of the participant
122 # Map of the answers of a Meetup and the answers of the participant
123 var answers
: Map[Answer, Bool] = new HashMap[Answer, Bool]
125 # To be used internally when fetching the `People` in Database
126 private init from_db
(id
: Int, name
, surname
: String) do
131 # Changes an answer `ans` (or adds it)
132 fun answer
=(ans
: Answer, resp
: Bool) do
136 # Loads the answers for a Meetup
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;")
143 var ans
= new Answer.from_db
(i
[0].to_i
, i
[1].to_s
)
145 if i
[2].to_i
== 1 then answers
[ans
] = true
149 redef fun to_s
do return "{surname.capitalized} {name.capitalized}"
151 redef fun commit
(db
) do
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"
158 id
= db
.last_insert_rowid
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"
166 for i
,j
in answers
do
167 if i
.id
== -1 then i
.commit
(db
)
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"
180 # A `Meetup` is an opportunity of meeting, linked to the database
184 # ID of the meetup, SHA-1 of the informations that are contained
186 # Name for the meetup
188 # SQLite-formatted date : YYYY:DD:MM HH:MM:SS
190 # Place of the meetup
193 # Builds the object with all the informations found in the database
194 private init from_db
(id
, name
, date
, place
: String) do
201 # Gets the answers bound to the current `Meetup`
202 fun answers
(db
: OpportunityDB): Array[Answer] do
204 return new Array[Answer]
206 var res
= db
.select
("id, name FROM answers WHERE meetup_id={id.to_sql_string}")
207 var ans
= new Array[Answer]
209 ans
.add
new Answer.from_db
(i
[0].to_i
, i
[1].to_s
)
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]
219 arr
.add
(new People.from_db
(i
[0].to_i
, i
[1].to_s
, i
[2].to_s
))
224 redef fun commit
(db
) do
227 var tmpid
= (name
+ date
+ place
+ time
.to_s
).sha1_to_s
228 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
229 print
"Error recording entry Meetup {self}"
230 print db
.error
or else "Null error"
236 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};")
241 return "Event : {name}\nWhen : {date}\nWhere : {place}"
245 # An answer linked to a Meetup in the database
249 # Name of the answer (title)
251 # Id in the database, -1 if not set
253 # Meetup the answer is linked to (null while it is not added in the database or set via API)
254 var meetup
: nullable Meetup = null is writable
256 # To be used internally when fetching the object from Database
257 private init from_db
(id
: Int, name
: String) do
262 # Loads the Meetup associated to `self`
264 # REQUIRE: is loaded in database
265 fun load_meetup
(db
: OpportunityDB): Meetup do
267 var res
= db
.select
("meetups.* FROM meetups, answers WHERE answers.id={id} AND answers.meetup_id=meetups.id;")
269 return new Meetup.from_db
(i
[0].to_s
, i
[1].to_s
, i
[2].to_s
, i
[3].to_s
)
271 # If no Meetup could be loaded, the contract was not respected
275 redef fun commit
(db
) do
277 if m
== null then return false
279 if not m
.commit
(db
) then
280 print
"Error when creating meetup {m}"
285 if not db
.execute
("INSERT INTO answers (name, meetup_id) VALUES({name.to_sql_string}, {m.id.to_sql_string});") then
286 print
"Cannot create {self} in database"
287 print db
.error
or else "Unknown error"
290 id
= db
.last_insert_rowid
292 if not db
.execute
("UPDATE answers (name) VALUES ({name.to_sql_string}) WHERE meetup_id={m.id.to_sql_string};") then
293 print
"Error updating {self} in database"
294 print db
.error
or else "Unknown error"
301 redef fun to_s
do return name