def __init__(self, handle, progressbar=None):
ChessFile.__init__(self, handle)
self.handle = handle
self.progressbar = progressbar
self.pgn_is_string = isinstance(handle, StringIO)
if self.pgn_is_string:
self.games = [self.load_game_tags(), ]
else:
self.skip = 0
self.limit = 100
self.order_col = game.c.offset
self.is_desc = False
self.reset_last_seen()
# filter expressions to .sqlite .bin .scout
self.tag_query = None
self.fen = None
self.scout_query = None
self.scoutfish = None
self.chess_db = None
self.sqlite_path = os.path.splitext(self.path)[0] + '.sqlite'
self.engine = dbmodel.get_engine(self.sqlite_path)
self.tag_database = TagDatabase(self.engine)
self.games, self.offs_ply = self.get_records(0)
log.info("%s contains %s game(s)" % (self.path, self.count), extra={"task": "SQL"})
def __init__(self, handle, progressbar=None):
ChessFile.__init__(self, handle)
self.handle = handle
self.progressbar = progressbar
self.pgn_is_string = isinstance(handle, StringIO)
if self.pgn_is_string:
self.games = [
self.load_game_tags(),
]
else:
self.skip = 0
self.limit = 100
self.order_col = game.c.offset
self.is_desc = False
self.reset_last_seen()
# filter expressions to .sqlite .bin .scout
self.tag_query = None
self.fen = None
self.scout_query = None
self.scoutfish = None
self.chess_db = None
self.sqlite_path = os.path.splitext(self.path)[0] + '.sqlite'
self.engine = dbmodel.get_engine(self.sqlite_path)
self.tag_database = TagDatabase(self.engine)
self.games, self.offs_ply = self.get_records(0)
log.info("%s contains %s game(s)" % (self.path, self.count),
extra={"task": "SQL"})
def __init__(self, path, games):
PGNFile.__init__(self, path, games)
self.path = path
self.engine = dbmodel.get_engine(path)
self.DB_MAXINT_SHIFT = get_maxint_shift(self.engine)
self.cols = [
game.c.id.label("Id"),
pl1.c.name.label('White'),
pl2.c.name.label('Black'),
game.c.result.label('Result'),
event.c.name.label('Event'),
site.c.name.label('Site'),
game.c.round.label('Round'),
game.c.date_year.label('Year'),
game.c.date_month.label('Month'),
game.c.date_day.label('Day'),
game.c.white_elo.label('WhiteElo'),
game.c.black_elo.label('BlackElo'),
game.c.ply_count.label('PlyCount'),
game.c.eco.label('ECO'),
game.c.time_control.label('TimeControl'),
game.c.fen.label('Board'),
game.c.fen.label('FEN'),
game.c.variant.label('Variant'),
annotator.c.name.label('Annotator')
]
self.from_obj = [
game.outerjoin(pl1, game.c.white_id == pl1.c.id).outerjoin(
pl2, game.c.black_id == pl2.c.id).outerjoin(
event, game.c.event_id == event.c.id).outerjoin(
site, game.c.site_id == site.c.id).outerjoin(
annotator, game.c.annotator_id == annotator.c.id)
]
self.count = self.engine.execute(count_games).scalar()
log.info("%s contains %s game(s)" % (self.path, self.count),
extra={"task": "SQL"})
self.update_count_stats()
self.select = select(self.cols, from_obj=self.from_obj)
self.colnames = self.engine.execute(self.select).keys()
self.query = self.select
self.orderby = None
self.where_tags = None
self.where_bitboards = None
self.ply = None
def init_tag_database(self):
sqlite_path = os.path.splitext(self.path)[0] + '.sqlite'
self.engine = dbmodel.get_engine(sqlite_path)
self.tag_database = TagDatabase(self.engine)
# Import .pgn header tags to .sqlite database
size = self.size
if size > 0 and self.tag_database.count == 0:
if size > 10000000:
drop_indexes(self.engine)
importer = PgnImport(self)
importer.do_import(self.path, progressbar=self.progressbar)
if size > 10000000:
create_indexes(self.engine)
def init_tag_database(self):
""" Create/open .sqlite database of game header tags """
sqlite_path = os.path.splitext(self.path)[0] + '.sqlite'
self.engine = dbmodel.get_engine(sqlite_path)
self.tag_database = TagDatabase(self.engine)
# Import .pgn header tags to .sqlite database
size = self.size
if size > 0 and self.tag_database.count == 0:
if size > 10000000:
drop_indexes(self.engine)
importer = PgnImport(self)
importer.do_import(self.path, progressbar=self.progressbar)
if size > 10000000:
create_indexes(self.engine)
def save(path, model, offset):
game_event = model.tags["Event"]
game_site = model.tags["Site"]
year, month, day = int(model.tags["Year"]), int(model.tags["Month"]), int(
model.tags["Day"])
game_round = model.tags.get("Round")
white = repr(model.players[WHITE])
black = repr(model.players[BLACK])
result = model.status
eco = model.tags.get("ECO")
time_control = model.tags.get("TimeControl")
board = int(model.tags.get("Board")) if model.tags.get("Board") else None
white_elo = int(
model.tags.get("WhiteElo")) if model.tags.get("WhiteElo") else None
black_elo = int(
model.tags.get("BlackElo")) if model.tags.get("BlackElo") else None
variant = model.variant.variant
fen = model.boards[0].board.asFen()
fen = fen if fen != FEN_START else None
game_annotator = model.tags.get("Annotator")
ply_count = model.ply - model.lowply
def get_id(table, name):
if not name:
return None
selection = select([table.c.id], table.c.name == unicode(name))
result = conn.execute(selection)
id_ = result.scalar()
if id_ is None:
result = conn.execute(table.insert().values(name=unicode(name)))
id_ = result.inserted_primary_key[0]
return id_
engine = dbmodel.get_engine(path)
conn = engine.connect()
trans = conn.begin()
try:
event_id = get_id(event, game_event)
site_id = get_id(site, game_site)
white_id = get_id(player, white)
black_id = get_id(player, black)
annotator_id = get_id(annotator, game_annotator)
new_values = {
'offset': offset,
'offset8': (offset >> 3) << 3,
'event_id': event_id,
'site_id': site_id,
'date_year': year,
'date_month': month,
'date_day': day,
'round': game_round,
'white_id': white_id,
'black_id': black_id,
'result': result,
'white_elo': white_elo,
'black_elo': black_elo,
'ply_count': ply_count,
'eco': eco,
'time_control': time_control,
'board': board,
'fen': fen,
'variant': variant,
'annotator_id': annotator_id,
}
if hasattr(model, "game_id") and model.game_id is not None:
result = conn.execute(game.update().where(
game.c.id == model.game_id).values(new_values))
else:
result = conn.execute(game.insert().values(new_values))
model.game_id = result.inserted_primary_key[0]
trans.commit()
except:
trans.rollback()
raise
conn.close()
def save(path, model, offset):
game_event = model.tags["Event"]
game_site = model.tags["Site"]
date = model.tags["Date"]
game_round = model.tags["Round"]
white = repr(model.players[WHITE])
black = repr(model.players[BLACK])
result = model.status
eco = model.tags["ECO"]
time_control = model.tags["TimeControl"]
board = int(model.tags["Board"]) if "Board" in model.tags else 0
white_elo = model.tags["WhiteElo"]
black_elo = model.tags["BlackElo"]
variant = model.variant.variant
fen = model.boards[0].board.asFen()
fen = fen if fen != FEN_START else ""
game_annotator = model.tags["Annotator"] if "Annotator" in model.tags else ""
ply_count = model.ply - model.lowply
def get_id(table, name):
if not name:
return None
selection = select([table.c.id], table.c.name == name)
result = conn.execute(selection)
id_ = result.scalar()
if id_ is None:
result = conn.execute(table.insert().values(name=name))
id_ = result.inserted_primary_key[0]
return id_
engine = dbmodel.get_engine(path)
conn = engine.connect()
trans = conn.begin()
try:
event_id = get_id(event, game_event)
site_id = get_id(site, game_site)
white_id = get_id(player, white)
black_id = get_id(player, black)
annotator_id = get_id(annotator, game_annotator)
new_values = {
'offset': offset,
'offset8': (offset >> 3) << 3,
'event_id': event_id,
'site_id': site_id,
'date': date,
'round': game_round,
'white_id': white_id,
'black_id': black_id,
'result': result,
'white_elo': white_elo,
'black_elo': black_elo,
'ply_count': ply_count,
'eco': eco,
'time_control': time_control,
'board': board,
'fen': fen,
'variant': variant,
'annotator_id': annotator_id,
}
if hasattr(model, "game_id") and model.game_id is not None:
result = conn.execute(game.update().where(
game.c.id == model.game_id).values(new_values))
else:
result = conn.execute(game.insert().values(new_values))
model.game_id = result.inserted_primary_key[0]
trans.commit()
except Exception:
trans.rollback()
raise
conn.close()
def setUp(self):
self.engine = get_engine(None)
def setUp(self):
self.test_db = None # "/home/tamas/test.pdb"
self.engine = get_engine(self.test_db)
moves = []
in_tags = True
tags.append(line)
else:
if not in_tags:
moves.append(line)
else:
in_tags = False
moves.append(line)
if moves:
yield ("".join(tags), "".join(moves))
if __name__ == "__main__":
imp = PgnImport(get_engine(None))
if len(sys.argv) > 1:
arg = sys.argv[1]
with Timer() as t:
if arg[-4:].lower() in (".pgn", ".zip"):
if os.path.isfile(arg):
imp.do_import(arg)
elif os.path.exists(arg):
for file in sorted(os.listdir(arg)):
if file[-4:].lower() in (".pgn", ".zip"):
imp.do_import(os.path.join(arg, file))
print("Elapsed time (secs): %s" % t.elapsed_secs)
else:
path = os.path.abspath(os.path.dirname(__file__))
with Timer() as t:
def save(path, model, offset, flip=False):
game_event = model.tags["Event"]
game_site = model.tags["Site"]
date = model.tags["Date"]
game_round = model.tags["Round"]
white = repr(model.players[WHITE])
black = repr(model.players[BLACK])
result = model.status
eco = model.tags["ECO"]
time_control = model.tags["TimeControl"]
board = int(model.tags["Board"]) if "Board" in model.tags else 0
white_elo = model.tags["WhiteElo"]
black_elo = model.tags["BlackElo"]
variant = model.variant.variant
fen = model.boards[0].board.asFen()
fen = fen if fen != FEN_START else ""
game_annotator = model.tags[
"Annotator"] if "Annotator" in model.tags else ""
ply_count = model.ply - model.lowply
def get_id(table, name):
if not name:
return None
selection = select([table.c.id], table.c.name == name)
result = conn.execute(selection)
id_ = result.scalar()
if id_ is None:
result = conn.execute(table.insert().values(name=name))
id_ = result.inserted_primary_key[0]
return id_
engine = dbmodel.get_engine(path)
conn = engine.connect()
trans = conn.begin()
try:
event_id = get_id(event, game_event)
site_id = get_id(site, game_site)
white_id = get_id(player, white)
black_id = get_id(player, black)
annotator_id = get_id(annotator, game_annotator)
new_values = {
"offset": offset,
"offset8": (offset >> 3) << 3,
"event_id": event_id,
"site_id": site_id,
"date": date,
"round": game_round,
"white_id": white_id,
"black_id": black_id,
"result": result,
"white_elo": white_elo,
"black_elo": black_elo,
"ply_count": ply_count,
"eco": eco,
"time_control": time_control,
"board": board,
"fen": fen,
"variant": variant,
"annotator_id": annotator_id,
}
if hasattr(model, "game_id") and model.game_id is not None:
result = conn.execute(game.update().where(
game.c.id == model.game_id).values(new_values))
else:
result = conn.execute(game.insert().values(new_values))
model.game_id = result.inserted_primary_key[0]
trans.commit()
except Exception:
trans.rollback()
raise
conn.close()
def save(path, model, position=None):
movelist = array("H")
comments = []
walk(model.boards[0].board, movelist, comments)
game_event = model.tags["Event"]
game_site = model.tags["Site"]
year, month, day = int(model.tags["Year"]), int(model.tags["Month"]), int(
model.tags["Day"])
game_round = model.tags.get("Round")
white = repr(model.players[WHITE])
black = repr(model.players[BLACK])
result = model.status
eco = model.tags.get("ECO")
time_control = model.tags.get("TimeControl")
board = int(model.tags.get("Board")) if model.tags.get("Board") else None
white_elo = int(
model.tags.get("WhiteElo")) if model.tags.get("WhiteElo") else None
black_elo = int(
model.tags.get("BlackElo")) if model.tags.get("BlackElo") else None
variant = model.variant.variant
fen = model.boards[0].board.asFen()
fen = fen if fen != FEN_START else None
game_annotator = model.tags.get("Annotator")
ply_count = model.ply - model.lowply
def get_id(table, name):
if not name:
return None
selection = select([table.c.id], table.c.name == unicode(name))
result = conn.execute(selection)
id_ = result.scalar()
if id_ is None:
result = conn.execute(table.insert().values(name=unicode(name)))
id_ = result.inserted_primary_key[0]
return id_
engine = dbmodel.get_engine(path)
DB_MAXINT_SHIFT = get_maxint_shift(engine)
conn = engine.connect()
trans = conn.begin()
try:
event_id = get_id(event, game_event)
site_id = get_id(site, game_site)
white_id = get_id(player, white)
black_id = get_id(player, black)
annotator_id = get_id(annotator, game_annotator)
new_values = {
'event_id': event_id,
'site_id': site_id,
'date_year': year,
'date_month': month,
'date_day': day,
'round': game_round,
'white_id': white_id,
'black_id': black_id,
'result': result,
'white_elo': white_elo,
'black_elo': black_elo,
'ply_count': ply_count,
'eco': eco,
'time_control': time_control,
'board': board,
'fen': fen,
'variant': variant,
'annotator_id': annotator_id,
'movelist': movelist.tostring(),
'comments': "|".join(comments),
}
if hasattr(model, "game_id") and model.game_id is not None:
result = conn.execute(game.update().where(
game.c.id == model.game_id).values(new_values))
# TODO: ?
result = conn.execute(
bitboard.delete().where(bitboard.c.game_id == model.game_id))
else:
result = conn.execute(game.insert().values(new_values))
game_id = model.game_id = result.inserted_primary_key[0]
if not fen:
bitboard_data = []
stat_ins_data = []
stat_upd_data = []
result = model.status
for ply, board in enumerate(model.boards):
if ply == 0:
continue
bb = board.board.friends[0] | board.board.friends[1]
bitboard_data.append({
'game_id': game_id,
'ply': ply,
'bitboard': bb - DB_MAXINT_SHIFT,
})
if ply <= STAT_PLY_MAX:
stat_ins_data.append({
'ply': ply,
'bitboard': bb - DB_MAXINT_SHIFT,
'count': 0,
'whitewon': 0,
'blackwon': 0,
'draw': 0,
'white_elo_count': 0,
'black_elo_count': 0,
'white_elo': 0,
'black_elo': 0,
})
stat_upd_data.append({
'_ply':
ply,
'_bitboard':
bb - DB_MAXINT_SHIFT,
'_count':
1,
'_whitewon':
1 if result == WHITEWON else 0,
'_blackwon':
1 if result == BLACKWON else 0,
'_draw':
1 if result == DRAW else 0,
'_white_elo_count':
1 if white_elo is not None else 0,
'_black_elo_count':
1 if black_elo is not None else 0,
'_white_elo':
white_elo if white_elo is not None else 0,
'_black_elo':
black_elo if black_elo is not None else 0,
})
result = conn.execute(bitboard.insert(), bitboard_data)
conn.execute(insert_or_ignore(engine, stat.insert()),
stat_ins_data)
conn.execute(upd_stat, stat_upd_data)
trans.commit()
except:
trans.rollback()
raise
conn.close()
