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 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 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 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]))
