1""" 

2@file 

3@brief Manages a sqlite3 database to store the results. 

4""" 

5import datetime 

6from uuid import uuid4 

7import numpy 

8import pandas 

9from .dbengine import Database 

10from .options_helpers import read_options, read_users 

11from .competition import Competition 

12 

13 

14class DatabaseCompetition(Database): 

15 """ 

16 Holds the data used for competitions. Tables: 

17 

18 Competitions 

19 

20 * cpt_id 

21 * cpt_name 

22 * metric 

23 * datafile 

24 * description 

25 * expected_values 

26 

27 Teams 

28 

29 * team_id 

30 * team_name 

31 

32 Players 

33 

34 * player_id 

35 * team_id 

36 * player_name 

37 * mail 

38 * login 

39 * pwd 

40 

41 Submission 

42 

43 * cpt_id 

44 * player_id 

45 * date 

46 * filename 

47 * metric_value 

48 """ 

49 

50 def __init__(self, dbfile): 

51 """ 

52 @param dbfile filename or ``:memory:`` 

53 """ 

54 Database.__init__(self, dbfile) 

55 self._init() 

56 

57 def _init(self): 

58 """ 

59 Creates the tables if not present. 

60 """ 

61 self.connect() 

62 tables = self.get_table_list() 

63 adds = dict(competitions=DatabaseCompetition._col_competitions, 

64 teams=DatabaseCompetition._col_teams, 

65 players=DatabaseCompetition._col_players, 

66 submissions=DatabaseCompetition._col_submissions) 

67 for k, v in adds.items(): 

68 if k not in tables: 

69 self.create_table(k, v()) 

70 self.commit() 

71 self.close() 

72 

73 def init_from_options(self, filename): 

74 """ 

75 Initializes the database. It skips a table if 

76 it exists. 

77 

78 @param filename filename 

79 """ 

80 opt = read_options(filename) 

81 if opt is None: 

82 raise ValueError("No option in '{0}'.".format(filename)) 

83 users = read_users(opt["allowed_users"]) 

84 key = "tmpl_competitions" if "tmpl_competitions" in opt else "competitions" 

85 competitions = [(d if isinstance(d, Competition) 

86 else Competition(**d)) for d in opt[key]] 

87 

88 if not self.has_rows("teams"): 

89 teams = map(lambda x: x[1]['team'], users.items()) 

90 tdf = pandas.DataFrame({"team_name": list(teams)}) 

91 tdf.reset_index(drop=False, inplace=True) 

92 tdf.columns = ["team_id", "team_name"] 

93 tdf.to_sql("teams", self.Connection, 

94 if_exists="append", index=False) 

95 

96 if not self.has_rows("players"): 

97 players = list(map(lambda x: x[1], users.items())) 

98 pdf = pandas.DataFrame(players) 

99 pdf.reset_index(drop=False, inplace=True) 

100 tdf = self.to_df("teams") 

101 pdf["player_name"] = pdf["name"] 

102 pdf["player_id"] = pdf["index"] 

103 pdf = pdf.merge(tdf, left_on="team", right_on="team_name") 

104 pdf = pdf.drop(["name", "team_name", "index", "team"], axis=1) 

105 pdf.to_sql("players", self.Connection, 

106 if_exists="append", index=False) 

107 

108 if not self.has_rows("competitions"): 

109 pdf = pandas.DataFrame(Competition.to_records(competitions)) 

110 if "cpt_id" in pdf.columns: 

111 pdf.reset_index(drop=True, inplace=True) 

112 else: 

113 pdf.reset_index(drop=False, inplace=True) 

114 # tdf = self.to_df("competitions") 

115 pdf["cpt_id"] = pdf["index"] 

116 pdf = pdf.drop("index", axis=1) 

117 pdf.to_sql("competitions", self.Connection, 

118 if_exists="append", index=False) 

119 

120 if not self.has_rows("submissions"): 

121 pdf = DatabaseCompetition._dummy_submissions() 

122 pdf.reset_index(drop=True, inplace=True) 

123 pdf.to_sql("submissions", self.Connection, 

124 if_exists="append", index=False) 

125 

126 df = self.to_df("players") 

127 logins = set(df['login']) 

128 if None in logins: 

129 raise ValueError("One login is wrong: {0}".format(logins)) 

130 

131 def get_competitions(self): 

132 """ 

133 Returns the list of competitions as list of ``(cpt_id, cpt_name)``. 

134 """ 

135 return self.execute("SELECT cpt_id, cpt_name FROM competitions") 

136 

137 def to_df(self, table): 

138 """ 

139 Returns the content of a table as a dataframe. 

140 """ 

141 return pandas.read_sql("SELECT * FROM {0}".format(table), self.Connection) 

142 

143 @property 

144 def Connection(self): 

145 """ 

146 Returns the connexion. 

147 """ 

148 self._check_connection() 

149 return self._connection 

150 

151 @staticmethod 

152 def _col_competitions(): 

153 return [('cpt_id', int), ('link', str), ('cpt_name', str), ('description', str), 

154 ('metric', str), ('datafile', str), ('expected_values', str)] 

155 

156 @staticmethod 

157 def _col_teams(): 

158 return [('team_id', int), ('team_name', str)] 

159 

160 @staticmethod 

161 def _col_players(): 

162 return [('player_id', int), ('team_id', int), ('metric', str), ('player_name', str), 

163 ('mail', str), ('login', str), ('pwd', str)] 

164 

165 @staticmethod 

166 def _col_submissions(): 

167 return [('sub_id', str), ('cpt_id', int), ('player_id', int), ('date', str), 

168 ('data', str), ('metric', str), ('metric_value', float)] 

169 

170 @staticmethod 

171 def _dummy_submissions(): 

172 return pandas.DataFrame([dict(sub_id=str(uuid4()), cpt_id=-1, player_id=-1, date=datetime.datetime.now(), 

173 data='', metric='rse', metric_value=numpy.nan)]) 

174 

175 def get_cpt_id(self): 

176 """ 

177 Returns the list of competation id. 

178 """ 

179 return list(_[0] for _ in self.execute("SELECT cpt_id FROM competitions")) 

180 

181 def get_player_id(self): 

182 """ 

183 Returns the list of competation id. 

184 """ 

185 return list(_[0] for _ in self.execute("SELECT player_id FROM players")) 

186 

187 def submit(self, cpt_id, player_id, data, date=datetime.datetime.now()): 

188 """ 

189 Adds a submission to the database. 

190 

191 @param cpt_id competition id 

192 @param player_id player who did the submission 

193 @param data data of the submission 

194 

195 The function computes the metric associated to the submission. 

196 """ 

197 if not isinstance(data, str): 

198 raise TypeError("data must be str not {0}".format(type(data))) 

199 cp = list(self.execute( 

200 "SELECT cpt_id, metric, expected_values FROM competitions WHERE cpt_id={0}".format(cpt_id))) 

201 if len(cp) == 0: 

202 raise ValueError("Unable to find cpt_id={0} in\n{1}".format( 

203 cpt_id, self.get_cpt_id())) 

204 pid = list(self.execute( 

205 "SELECT player_id FROM players WHERE player_id={0}".format(player_id))) 

206 if len(pid) == 0: 

207 raise ValueError("Unable to find player_id={0} in\n{1}".format( 

208 player_id, self.get_player_id())) 

209 metrics = [_[1:] for _ in cp] 

210 

211 sub = [] 

212 for met, exp in metrics: 

213 cp = Competition(cpt_id=0, link='', name='', description='', 

214 metric=met, expected_values=exp) 

215 dres = cp.evaluate(data) 

216 res = dres[met] 

217 if not isinstance(res, float): 

218 res = float(res) 

219 rec = dict(sub_id=str(uuid4()), cpt_id=cpt_id, player_id=player_id, 

220 data=data, metric=met, metric_value=res) 

221 sub.append(rec) 

222 

223 df = pandas.DataFrame(sub) 

224 df.to_sql("submissions", self._connection, 

225 if_exists="append", index=False) 

226 self.commit() 

227 

228 def get_competition(self, cpt_id): 

229 """ 

230 Retrieves a competition. 

231 

232 @param cpt_id competition id 

233 @return @see cl Competition 

234 """ 

235 res = list(self.execute( 

236 "SELECT * FROM competitions WHERE cpt_id=={0}".format(cpt_id))) 

237 if len(res) == 0: 

238 raise KeyError("No competition for cpt_id=={0}".format(cpt_id)) 

239 if len(res) != 1: 

240 raise KeyError( 

241 "Too many competitions for cpt_id=={0}".format(cpt_id)) 

242 args = list(res[0]) 

243 return Competition(*args) 

244 

245 def get_results(self, cpt_id): 

246 """ 

247 Retrieves the results of a competition. 

248 

249 @param cpt_id competition id 

250 @return a data frame 

251 """ 

252 res = pandas.read_sql("""SELECT A.*, B.player_name AS player_name, C.team_name 

253 FROM submissions AS A 

254 INNER JOIN players AS B ON A.player_id == B.player_id 

255 INNER JOIN teams AS C ON B.team_id == C.team_id 

256 WHERE cpt_id == {0} 

257 """.format(cpt_id), self.Connection) 

258 return res