def getMetadata(): global METADATA if METADATA is None: import mydb, library sql = "select ruletype, bggid from metadata" ruleData = mydb.query(sql, []) basegames = [ int(gameId) for (rule, gameId) in ruleData if rule == library.BASEGAME ] # games which are marked wrongly as basegames in BGG! expansions = [ int(gameId) for (rule, gameId) in ruleData if rule == library.EXPANSION ] sql = "select basegame, expansion from expansions" expData = mydb.query(sql, []) expData = [ (b,e) for (b,e) in expData if b not in expansions ] METADATA = basegames, expData return METADATA
def getAllCollectionsForGeek(context, includeGames = False): import mydb sql = "select collectionname, description, collectionindex, ckey from collections where geek = %s order by 3" collectionData = mydb.query(sql, [context.geek]) ckeys = [ row[3] for row in collectionData ] groupData = [] gameData = [] if len(ckeys) > 0: ckeystr = "(%s)" % ",".join([str(ck) for ck in ckeys]) sql = "select groupindex, groupname, groupdesc, display, ckey from collectiongroups where ckey in " + ckeystr + " order by 1" groupData = mydb.query(sql) if includeGames: sql = "select groupindex, bggid, ckey from collectiongames where ckey in " + ckeystr + " order by orderindex" gameData = mydb.query(sql) return buildCollections(context, collectionData, groupData, gameData)
def checkGeek(name): import mydb sql = "select username from geeks where username = %s" data = mydb.query(sql, [name]) if len(data) > 0: return name raise library.NoSuchGeekException(name)
def getGames(ids): import mydb sql = __inlistSQL(GAMES_FIELDS, "games", "bggid", ids) gs = map(__extract(GAMES_FIELDS, "Game", Game), mydb.query(sql)) for g in gs: g.name = g.name.encode('utf-8') return {g.bggid: g for g in gs}
def getAllGeekGamesWithOptions(self, options): """returns GeekGame objects""" import mydb, dbaccess key = optionKey(options) if self.collections.get(key) is not None: return self.collections[key] geekgames = dbaccess.getGeekGamesForGeek(self.geek) sql = "select distinct game from plays where geek = %s and game not in (select distinct game from geekgames where geek = %s)" playedData = [d[0] for d in mydb.query(sql, [self.geek, self.geek])] playedGames = getGames(playedData) for bggid in playedGames.keys(): gg = createEmptyGeekGame(playedGames[bggid]) gg.game = bggid geekgames.append(gg) if options.excludeTrades: geekgames = [gg for gg in geekgames if not gg.trade] for gg in geekgames: gg.bggid = gg.game self.__processGeekGames(geekgames) games = self.getGames([gg.game for gg in geekgames]) games.update(playedGames) for gg in geekgames: gg.game = games[gg.game] if options.excludeExpansions: geekgames = [gg for gg in geekgames if not gg.game.expansion] self.collections[key] = geekgames return geekgames
def getCollectionForGeek(context, index, includeGameData = False): import mydb sql = "select collectionname, description, collectionindex, ckey from collections where geek = %s and collectionindex = %s order by 3" collectionData = mydb.query(sql, [context.geek, index]) ckey = collectionData[0][3] sql = "select groupindex, groupname, groupdesc, display, ckey from collectiongroups where ckey = %s order by 1" groupData = mydb.query(sql, [ckey]) if includeGameData: sql = "select groupindex, bggid, ckey from collectiongames where ckey = %s order by orderindex" gameData = mydb.query(sql, [ckey]) else: gameData = [] result = buildCollections(context, collectionData, groupData, gameData) if len(result) > 0: return result[0] return makeNewCollection(context, index)
def getGames(self, context, opts): import mydb gids = mydb.query("select bggid from games where yearPublished = %d" % self.year) gids = [gid[0] for gid in gids] games = context.substrate.getGames(gids) return context.substrate.getTheseGeekGames(games.values())
def getGames(self, context, opts): import mydb, substrate sql = "select gameId from gameMechanics where mechanic = %s" data = mydb.query(sql, [self.cat]) data = [d[0] for d in data] return context.substrate.getTheseGeekGames( substrate.getGames(data).values())
def getAllGeekGamesWithOptions(self, options): """returns GeekGame objects""" import mydb, dbaccess key = optionKey(options) if self.collections.get(key) is not None: return self.collections[key] geekgames = dbaccess.getGeekGamesForGeek(self.geek) sql = "select distinct game from plays where geek = %s and game not in (select distinct game from geekgames where geek = %s)" playedData = [ d[0] for d in mydb.query(sql, [self.geek, self.geek]) ] playedGames = getGames(playedData) for bggid in playedGames.keys(): gg = createEmptyGeekGame(playedGames[bggid]) gg.game = bggid geekgames.append(gg) if options.excludeTrades: geekgames = [ gg for gg in geekgames if not gg.trade ] for gg in geekgames: gg.bggid = gg.game self.__processGeekGames(geekgames) games = self.getGames([ gg.game for gg in geekgames ]) games.update(playedGames) for gg in geekgames: gg.game = games[gg.game] if options.excludeExpansions: geekgames = [ gg for gg in geekgames if not gg.game.expansion ] self.collections[key] = geekgames return geekgames
def getNextCollectionIndex(context): import mydb sql = "select collectionindex from collections where geek = %s" cindexes = mydb.query(sql, [context.geek]) indexes = [ c[0] for c in cindexes ] newIndex = 0 while newIndex in indexes: newIndex = newIndex + 1 return newIndex
def deleteCollection(context, index): import mydb sql = "select ckey from collections where geek = %s and collectionindex = %s" ckeyData = mydb.query(sql, [context.geek, index]) if len(ckeyData) < 0: return ckey = ckeyData[0][0] mydb.update("delete from collectiongames where ckey = %d" % ckey) mydb.update("delete from collectiongroups where ckey = %d" % ckey) mydb.update("delete from collections where ckey = %d" % ckey)
def saveCollectionFromJson(username, index, model): import library, mydb sql = "select ckey from collections where geek = %s and collectionindex = %s" ckeyData = mydb.query(sql, [username, index]) ckey = None if len(ckeyData) > 0: ckey = ckeyData[0][0] collRow = library.Row() collRow.collectionname = esc(model["name"]) collRow.description = esc(model["description"]) collRow.collectionindex = index collRow.geek = username if ckey is not None: collRow.ckey = ckey mydb.saveRow(collRow, "collections", "ckey = %d" % ckey) else: mydb.saveRow(collRow, "collections", None) ckeyData = mydb.query(sql, [username, index]) ckey = ckeyData[0][0] mydb.update("delete from collectiongroups where ckey = %d" % ckey) for g in model["groups"]: groupRow = library.Row() groupRow.ckey = ckey groupRow.groupname = esc(g["name"]) groupRow.groupindex = g["index"] groupRow.groupname = g["name"] groupRow.groupdesc = esc(g["description"]) groupRow.display = g["display"] mydb.saveRow(groupRow, "collectiongroups", None) mydb.update("delete from collectiongames where ckey = %d" % ckey) db = mydb.get() for g in model["groups"]: order = 0 for game in g["games"]: gameRow = library.Row() gameRow.groupindex = g["index"] gameRow.bggid = game["id"] gameRow.orderindex = order order = order + 1 gameRow.ckey = ckey mydb.saveRowDb(db, gameRow, "collectiongames", None) db.commit() db.close()
def getPlays(geek, start, finish): import mydb sql = "select " + ",".join( PLAYS_FIELDS) + " from plays where geek = %s order by playDate" args = [geek] if start is not None and finish is not None: sql += " and playDate between %s and %s" args += [start, finish] # TODO what if start is None and finish is not, or vice versa? return map(__extract(PLAYS_FIELDS, "Play", library.Thing), mydb.query(sql, args))
def getMetadata(): global METADATA if METADATA is None: import mydb, library sql = "select ruletype, bggid from metadata" ruleData = mydb.query(sql, []) basegames = [ int(gameId) for (rule, gameId) in ruleData if rule == library.BASEGAME ] # games which are marked wrongly as basegames in BGG! expansions = [ int(gameId) for (rule, gameId) in ruleData if rule == library.EXPANSION ] sql = "select basegame, expansion from expansions" expData = mydb.query(sql, []) expData = [(b, e) for (b, e) in expData if b not in expansions] METADATA = basegames, expData return METADATA
def plotCategoryRatings(context, cattype, category): import mydb, library if cattype == "All": table = "" clause = "" params = [] category = "All" elif cattype == "category": table = "gameCategories," clause = "category = %s and bggid = gameId and" params = [category] elif cattype == "mechanic": table = "gameMechanics," clause = "mechanic = %s and bggid = gameId and" params = [category] elif cattype == "designer": table = "gameDesigners, " clause = "designerId = %s and bggid = gameId and" params = [int(category)] elif cattype == "publisher": table = "gamePublishers, " clause = "publisherId = %s and bggid = gameId and" params = [int(category)] sql = "select average, rating from games, %s geekgames where %s bggid = game and geek = %s and rating > 0" % ( table, clause, "%s") data = mydb.query(sql, params + [context.geek]) games = [] for (avg, rating) in data: t = Thing() t.average = avg t.rating = rating games.append(t) (img, draw, xlo, xhi, ylo, yhi) = newImage(250, 250) draw.line([(xlo, yhi), (xhi, ylo)], GREEN, 1) bf = library.BestFit() for gg in games: bf.plot(gg.average, gg.rating) xr = ratingToCoord(xlo, xhi, gg.average) yr = ratingToCoord(yhi, ylo, gg.rating) draw.ellipse([(xr - 2, yr - 2), (xr + 2, yr + 2)], outline=BLACK, fill=BLACK) if bf.valid(): (a, b) = bf.line() y0 = a y10 = a + b * 10.0 draw.line( [(ratingToCoord(xlo, xhi, 0), ratingToCoord(yhi, ylo, y0)), (ratingToCoord(xlo, xhi, 10), ratingToCoord(yhi, ylo, y10))], CYAN, 1) draw.text((25, 0), 'User (Y) vs BGG (X) Rating', fill=BLACK) del draw return img
def recordProfileView(username): import mydb, datetime, library sql = "select profileViews from users where geek = %s" data = mydb.query(sql, [username]) if len(data) == 0: sql = "INSERT INTO users (geek, profileViews) VALUES (%s, 0)" mydb.update(sql, [username]) count = 0 else: count = int(data[0][0]) count += 1 sql = "update users set lastProfileView = %s, profileViews = %s where geek = %s" now = library.dbTime(datetime.datetime.utcnow()) mydb.update(sql, [now, count, username])
def __init__(self, designer): if type(designer) == type([]): designer = designer[0] if type(designer) == type("") or type(designer) == type(u""): designer = int(designer) if type(designer) == type(0): # create a fake designer import library, mydb self.designer = library.Thing() self.designer.bggid = designer self.name = mydb.query("select name from designers where bggid = " + `designer`)[0][0] else: Selector.__init__(self, "Designer %s" % designer.name, DesignerSelector.key + "/%d" % designer.bggid) self.designer = designer self.name = designer.name
def plotCategoryRatings(context, cattype, category): import mydb, library if cattype == "All": table = "" clause = "" params = [] category = "All" elif cattype == "category": table = "gameCategories," clause = "category = %s and bggid = gameId and" params = [category] elif cattype == "mechanic": table = "gameMechanics," clause = "mechanic = %s and bggid = gameId and" params = [category] elif cattype == "designer": table = "gameDesigners, " clause = "designerId = %s and bggid = gameId and" params = [int(category)] elif cattype == "publisher": table = "gamePublishers, " clause = "publisherId = %s and bggid = gameId and" params = [int(category)] sql = "select average, rating from games, %s geekgames where %s bggid = game and geek = %s and rating > 0" % (table, clause, "%s") data = mydb.query(sql, params + [context.geek]) games = [] for (avg, rating) in data: t = Thing() t.average = avg t.rating = rating games.append(t) (img, draw, xlo, xhi, ylo, yhi) = newImage(250, 250) draw.line([(xlo, yhi), (xhi, ylo)], GREEN, 1) bf = library.BestFit() for gg in games: bf.plot(gg.average, gg.rating) xr = ratingToCoord(xlo, xhi, gg.average) yr = ratingToCoord(yhi, ylo, gg.rating) draw.ellipse([(xr-2, yr-2), (xr+2, yr+2)], outline=BLACK, fill=BLACK) if bf.valid(): (a, b) = bf.line() y0 = a y10 = a + b * 10.0 draw.line([(ratingToCoord(xlo, xhi, 0), ratingToCoord(yhi, ylo, y0)), (ratingToCoord(xlo, xhi, 10), ratingToCoord(yhi, ylo, y10))], CYAN, 1) draw.text((25, 0), 'User (Y) vs BGG (X) Rating', fill=BLACK) del draw return img
def getAllSeries(self): import mydb, library if self.series is not None: return self.series sql = "select name, game from series" data = mydb.query(sql, []) self.series = library.DictOfSets() games = [] for (name, bggid) in data: if bggid not in games: games.append(bggid) games = self.getGames(games) for (name, bggid) in data: g = games.get(bggid) if g is None: continue self.series.add(name, g) return self.series
def saveCollection(context, collection): import library, mydb sql = "select ckey from collections where geek = %s and collectionindex = %s" ckeyData = mydb.query(sql, [context.geek, collection.index]) ckey = ckeyData[0][0] collRow = library.Row() collRow.collectionname = collection.name collRow.description = collection.description collRow.collectionindex = collection.index collRow.geek = context.geek collRow.ckey = ckey mydb.saveRow(collRow, "collections", "ckey = %d" % ckey) mydb.update("delete from collectiongroups where ckey = %d" % ckey) for g in collection.groups: groupRow = library.Row() groupRow.ckey = ckey groupRow.groupname = g.name groupRow.groupindex = g.index groupRow.groupname = g.name groupRow.groupdesc = g.description mydb.saveRow(groupRow, "collectiongroups", None) mydb.update("delete from collectiongames where ckey = %d and groupindex not in (select groupindex from collectiongroups where ckey = %d)" % (ckey, ckey))
def createPlaysByPublishedYearGraph(context, data, upsideDown): import time, mydb startYear = context.options.firstYearGames endYear = time.localtime()[0] years = [] year = startYear while year <= endYear: years.append(year) if data.get(year) is None: data.data[year] = [] year += 1 sizes = [len(data[y]) for y in years] sql = "select bggid from users where geek = %s" geekData = mydb.query(sql, context.geek) try: bggid = geekData[0][0] except IndexError: bggid = 0 imgspec = context.imageSpec (img, draw, xlo, xhi, ylo, yhi) = newImage(imgspec.width, imgspec.height) imap = [] highest = max(sizes) year = startYear height = 1.0 * (yhi - ylo) while year <= endYear: x0 = xlo + ((year - startYear) * (xhi - xlo) * 1.0 / (endYear + 1 - startYear)) x1 = xlo + ((year + 1 - startYear) * (xhi - xlo) * 1.0 / (endYear + 1 - startYear)) data[year].sort(lambda g1, g2: cmp(g1.plays, g2.plays)) if upsideDown: data[year].reverse() if highest > 0: count = 0 for g in data[year]: y0 = yhi - int(count * height / highest) count += 1 y1 = yhi - int(count * height / highest) if g.plays == 0: fill = WHITE elif g.plays == 1: fill = RED elif g.plays < 3: fill = ORANGE elif g.plays < 5: fill = YELLOW elif g.plays < 10: fill = YELLOWGREEN elif g.plays < 25: fill = GREEN else: fill = DARKGREEN if y1 == y0: continue draw.rectangle([x0, y0, x1, y1], outline=BLACK, fill=fill) mapRow = Thing() (mapRow.x1, mapRow.y1, mapRow.x2, mapRow.y2) = (int(x0), int(y1), int(x1), int(y0)) mapRow.name = escape(g.name.decode('utf-8')) mapRow.url = None if bggid: mapRow.url = GAME_PLAYS_URL % (g.bggid, bggid) imap.append(mapRow) draw.text((x0 + 10, yhi + 7), str(year), fill=BLACK) draw.text((x0 + 20, ylo - 14), str(len(data[year])), fill=BLACK) year += 1 del draw return img, imap
def getFlorenceCats(): import mydb sql = "select distinct subdomain from games order by 1" cats = mydb.query(sql) return [c[0] for c in cats if c[0] not in IGNORE]
def getGames(self, context, opts): import mydb, substrate sql = "select gameId from gameMechanics where mechanic = %s" data = mydb.query(sql, [self.cat]) data = [ d[0] for d in data ] return context.substrate.getTheseGeekGames(substrate.getGames(data).values())
def getGames(self, context, opts): import mydb gids = mydb.query("select bggid from games where yearPublished = %d" % self.year) gids = [ gid[0] for gid in gids ] games = context.substrate.getGames(gids) return context.substrate.getTheseGeekGames(games.values())
def getPublishers(ids): import mydb, library sql = __inlistSQL(PUBLISHER_FIELDS, "publishers", "bggid", ids) gs = map(__extract(PUBLISHER_FIELDS, "Publisher", library.Thing), mydb.query(sql)) return {g.bggid: g for g in gs}
def getAllGeekNames(): import mydb sql = "select username from geeks" data = mydb.query(sql) return [d[0] for d in data]
def getDesigners(ids): import mydb, library sql = __inlistSQL(DESIGNER_FIELDS, "designers", "bggid", ids) gs = map(__extract(DESIGNER_FIELDS, "Designer", library.Thing), mydb.query(sql)) return {g.bggid: g for g in gs}
def getFlorenceCats(): import mydb sql = "select distinct subdomain from games order by 1" cats = mydb.query(sql) return [c[0] for c in cats if c[0]not in IGNORE]
def getGeekGamesForGeek(geek): import mydb sql = "select " + ",".join(GG_FIELDS) + " from geekgames where geek = %s" data = mydb.query(sql, [geek]) result = map(__extract(GG_FIELDS, "GeekGame", library.Thing), data) return result
def getGameCategories(ids): import mydb, library sql = __inlistSQL(GAME_CATEGORY_FIELDS, "gameCategories", "gameId", ids) gs = map(__extract(GAME_CATEGORY_FIELDS, "GameCategory", library.Thing), mydb.query(sql)) return gs
def getGameMechanics(ids): import mydb, library sql = __inlistSQL(GAME_MECHANIC_FIELDS, "gameMechanics", "gameId", ids) gs = map(__extract(GAME_MECHANIC_FIELDS, "GameMechanic", library.Thing), mydb.query(sql)) return gs
def createPlaysByPublishedYearGraph(context, data, upsideDown): import time, mydb startYear = context.options.firstYearGames endYear = time.localtime()[0] years = [] year = startYear while year <= endYear: years.append(year) if data.get(year) is None: data.data[year] = [] year += 1 sizes = [ len(data[y]) for y in years ] sql = "select bggid from users where geek = %s" geekData = mydb.query(sql, context.geek) try: bggid = geekData[0][0] except IndexError: bggid = 0 imgspec = context.imageSpec (img, draw, xlo, xhi, ylo, yhi) = newImage(imgspec.width, imgspec.height) imap = [] highest = max(sizes) year = startYear height = 1.0 * (yhi - ylo) while year <= endYear: x0 = xlo + ((year - startYear) * (xhi - xlo) * 1.0 / (endYear + 1 - startYear)) x1 = xlo + ((year + 1 - startYear) * (xhi - xlo) * 1.0 / (endYear + 1 - startYear)) data[year].sort(lambda g1, g2: cmp(g1.plays, g2.plays)) if upsideDown: data[year].reverse() if highest > 0: count = 0 for g in data[year]: y0 = yhi - int(count * height / highest) count += 1 y1 = yhi - int(count * height / highest) if g.plays == 0: fill = WHITE elif g.plays == 1: fill = RED elif g.plays < 3: fill = ORANGE elif g.plays < 5: fill = YELLOW elif g.plays < 10: fill = YELLOWGREEN elif g.plays < 25: fill = GREEN else: fill = DARKGREEN if y1 == y0: continue draw.rectangle([x0, y0, x1, y1], outline=BLACK, fill=fill) mapRow = Thing() (mapRow.x1, mapRow.y1, mapRow.x2, mapRow.y2) = (int(x0), int(y1), int(x1), int(y0)) mapRow.name = escape(g.name.decode('utf-8')) mapRow.url = None if bggid: mapRow.url = GAME_PLAYS_URL % (g.bggid, bggid) imap.append(mapRow) draw.text((x0+10, yhi+7), str(year), fill=BLACK) draw.text((x0+20, ylo-14), str(len(data[year])), fill=BLACK) year += 1 del draw return img, imap
def getGames(self, context, opts): import mydb gids = mydb.query("select gameId from gameDesigners where designerId = " + `self.designer.bggid`) gids = [ gid[0] for gid in gids ] games = context.substrate.getGames(gids) return context.substrate.getTheseGeekGames(games.values())
def getGamePublishers(ids): import mydb, library sql = __inlistSQL(GAME_PUBLISHER_FIELDS, "gamePublishers", "gameId", ids) gs = map(__extract(GAME_PUBLISHER_FIELDS, "GamePublisher", library.Thing), mydb.query(sql)) return gs
def getGameDesigners(ids): import mydb, library sql = __inlistSQL(GAME_DESIGNER_FIELDS, "gameDesigners", "gameId", ids) gs = map(__extract(GAME_DESIGNER_FIELDS, "GameDesigner", library.Thing), mydb.query(sql)) return gs
def getFilesForGeek(geek): import mydb sql = "select " + ",".join(FILES_FIELDS) + " from files where geek = %s" return map(__extract(FILES_FIELDS, "File", library.Thing), mydb.query(sql, [geek]))