def post(self, act_str): if act_str == 'submit': leader = self._attr_map['leader'].get() party = self._attr_map['party'].get() tourney = self._attr_map['tourney'].get() team = self._attr_map['name'].get() from germ.erm.helper import sql_query sql_query("INSERT INTO team_members SET party = '%s', " \ "username = '******', tourney = '%s', team = '%s', " \ "active = 1" % \ (party, leader, tourney, team), self._session, self._globals)
def check_pwd(self, aid): from germ.erm.helper import sql_query rset = sql_query("SELECT %s FROM %s WHERE username = '******'" % \ (cf.pwd_str, self.__user_table, self._attr_map['username'].sql_str()), self._session, self._globals) if len(rset) > 1: # TODO: Make this an invalid_key exception. This could very well # occur by a user 'mistake'. On the other hand, can it still occur # if ref_group.generate_keylist did not complain? from germ.error.error import error raise error(error.fail, "Ambiguous primary key: result has " + \ "multiple records", "number of records: %s" % \ len(rset)) if len(rset) == 0: invalid_auth = True else: passwd = rset[0][0] invalid_auth = not self._attr_map[aid].check(passwd) if invalid_auth: from germ.error.invalid_parm import invalid_parm raise invalid_parm('Wrong username/password')
def get_rec_explicit(self, table, key, attrs="*"): from germ.erm.helper import sql_query rset = sql_query("SELECT %s FROM %s WHERE %s" % (attrs, table, key), self._session, self._globals) if len(rset) != 1: # TODO: Make this an invalid_key exception. This could very well # occur by a user 'mistake'. On the other hand, can it still occur # if ref_group.generate_keylist did not complain? from germ.error.error import error raise error(error.fail, "Invalid key", "number of records: %s" % len(rset)) return rset[0]
def has_paid(self): # TODO: This is very similar to ent_table.get_rec(). from germ.erm.helper import sql_query rset = sql_query("SELECT paid FROM %s WHERE %s" % \ (self._name, self.get_attr_sql_pk()), self._session, self._globals) if len(rset) != 1: from germ.error.error import error raise error(error.fail, "Invalid primary key: result is empty " + \ "or has multiple records", "number of records: %s" % \ len(rset)) return rset[0][0] == 1
def get_teams(cls, party, tourney, size, session, glob): import cf require_activation = cf.require_activation and size > 1 query_str = "SELECT team FROM %s WHERE " \ "party = '%s' AND tourney = '%s' %s " \ "GROUP BY team %s" % \ (cls.__name__, party, tourney, require_activation and "AND active = TRUE" or '', require_activation and "HAVING COUNT(1) >= %s" % size or '') from germ.erm.helper import sql_query rset = sql_query(query_str, session, glob) return [i[0] for i in rset]
def edit(self, act_str, do_exec = True): self.check_exec(do_exec) self.require_parms(act_str) self.check_pwd(cf.pwd_str + 'old') from germ.attr.passwd import passwd attr = passwd(label.passwd, [ 'submit' ], '', 30) attr.set(self._attr_map[cf.pwd_str].get()) from germ.erm.helper import sql_query rset = sql_query("UPDATE %s SET %s = '%s' WHERE username = '******'" % \ (self.__user_table, cf.pwd_str, attr.sql_str(), self._attr_map['username'].sql_str()), self._session, self._globals)
def __query(self, col_spec, table_ref, cond, sort_spec = None): if len(cond) == 0: search_str = '' else: search_str = '\nWHERE ' + ' AND\n '.join(cond) if sort_spec is None: sort_spec_str = '' else: sort_spec_str = '\nORDER BY ' + sort_spec sql_str = "SELECT DISTINCT %s\nFROM %s%s%s" % \ (col_spec, table_ref, search_str, sort_spec_str) from germ.erm.helper import sql_query res = sql_query(sql_str, self.__session, self.__ent.get_globals()) return res
def insert_round(cls, teams, party, tourney, rnd, bracket, stage, session, glob): recs = [] for i in xrange(len(teams)/2): team1 = teams[2*i] team2 = teams[2*i+1] if team1 == 'bye': status = 2 elif team2 == 'bye': status = 1 else: status = 0 recs.append('(' + ', '.join(["'" + str(i) + "'" for i in [party, tourney, rnd, stage, bracket, i, team1, team2, status]]) + \ ')') from germ.erm.helper import sql_query return sql_query("INSERT INTO %s " \ "(party, tourney, round, stage, bracket, id, team1, team2, " \ "status) VALUES %s" % (cls.__name__, ', '.join(recs)), session, glob)
def post(self, act_str): if act_str == 'edit': party = self._attr_map['party'].get() tourney = self._attr_map['tourney'].get() query_str = "SELECT bracket, MAX(round), stage, MIN(status) " \ "FROM %s " \ "WHERE party = '%s' AND tourney = '%s' " \ "GROUP BY bracket, stage" % \ (self._name, party, tourney) from germ.erm.helper import sql_query rset = sql_query(query_str, self._session, self._globals) round = [ [0, 0], [0, 0] ] for bracket, rnd, stage, status in rset: if status > 0: round[bracket][stage] = rnd else: round[bracket][stage] = -rnd if abs(round[1][0]) > abs(round[0][0]): # Tourney is finished. sql_query("UPDATE tourney SET phase = 4 WHERE " \ "party = '%s' AND name = '%s'" % \ (party, tourney), self._session, self._globals) return final = False if round[0][0] > 0: # Create new winner round. rnd = round[0][0] rset = sql_query("SELECT CASE status - 1 " \ "WHEN 1 THEN team1 " \ "WHEN 2 THEN team2 " \ "ELSE NULL END " \ "FROM %s WHERE party = '%s' AND tourney = '%s' AND " \ "round = %s AND bracket = 0 " \ "ORDER BY id" % \ (self._name, party, tourney, rnd), self._session, self._globals) final = len(rset) == 1 if not final: self._create_round(rset, party, tourney, rnd) if round[1][0] > 0 and abs(round[1][1]) < round[1][0] and \ (round[0][0] >= round[1][0] or -round[0][0] > round[1][0]): # Create new loser stage. rnd = round[1][0] # Align losers of winner bracket and winners of loser # bracket. Also swap neighbouring loser positions to reduce # the chance of rematches. rset = sql_query("SELECT CASE (status - 1) XOR bracket " \ "WHEN 0 THEN team2 " \ "WHEN 1 THEN team1 " \ "ELSE NULL END " \ "FROM %s WHERE " \ "party = '%s' AND tourney = '%s' AND " \ "round = %s " \ "ORDER BY id - bracket * (2 * (id %% 2) - 1), " \ "bracket" % (self._name, party, tourney, rnd), self._session, self._globals) final = False self._create_round(rset, party, tourney, rnd, 2, 1) elif round[1][1] > 0 and abs(round[1][0]) == round[1][1]: # Create new loser round. rnd = round[1][1] rset = sql_query("SELECT CASE status " \ "WHEN 1 THEN team1 " \ "WHEN 2 THEN team2 " \ "ELSE NULL END " \ "FROM %s WHERE " \ "party = '%s' AND tourney = '%s' AND " \ "round = %s AND bracket = 1 AND stage = 1 " \ "ORDER BY id" % \ (self._name, party, tourney, rnd), self._session, self._globals) if len(rset) > 1: final = False self._create_round(rset, party, tourney, rnd, 2) elif round[1][0] == 0 and round[0][0] != -1: # Create first loser round. rnd = 1 rset = sql_query("SELECT CASE status " \ "WHEN 1 THEN team2 " \ "WHEN 2 THEN team1 " \ "ELSE NULL END " \ "FROM %s WHERE " \ "party = '%s' AND tourney = '%s' AND " \ "round = %s AND bracket = 0 " \ "ORDER BY id" % \ (self._name, party, tourney, rnd), self._session, self._globals) final = False self._create_round(rset, party, tourney, rnd, 2) else: final = False if final: # Create final round. rset = sql_query("SELECT CASE status " \ "WHEN 1 THEN team1 " \ "WHEN 2 THEN team2 " \ "ELSE NULL END " \ "FROM %s WHERE " \ "party = '%s' AND tourney = '%s' AND " \ "round = %s AND " \ "(bracket = 0 OR (bracket = 1 AND stage = 1)) " \ "ORDER BY bracket" % \ (self._name, party, tourney, rnd), self._session, self._globals) self._create_round(rset, party, tourney, rnd, 2, 0)