· 10 years ago · Sep 11, 2016, 03:28 AM
1import sqlite3 # Get the SQlite package
2import string # For ABC...XYZ list
3from math import *
4#from numpy import transpose
5
6import os
7import subprocess
8from shutil import copyfile
9import datetime
10import time
11import calendar
12
13# At some point, I need to use .upper() on the move string to make sure
14##=============================================================================
15##=============== ----------------- Constants ---------------- ================
16##=============================================================================
17whiteELOadvantage = 45 # White win rate is 56.48% (for "good players" on a 5x5) TO DO: At some point I should not have this hard coded, but calculated base on win rates. See http://www.3dkingdoms.com/chess/elo.htm
18
19letters = list(string.ascii_uppercase) # Returns the list of tiles that belong to that orthant.
20
21botlist = ['AlphaTakBot_5x5', 'BeginnerBot', 'IntuitionBot', 'ShlktBot', 'TakkerBot', 'TakkerusBot', 'TakticBot', 'TakticianBot', 'TakticianBotDev', 'alphabot', 'alphatak_bot', 'alphatak_bot1', 'antakonistbot', 'cutak_bot', 'cutak_bot', 'takkybot']
22
23##=============================================================================
24##=============== ----------------- File Names --------------- ================
25##=============================================================================
26
27folder = os.getcwd()
28games_db = 'games_anon.db'
29trfolder = "takrating-master"
30trjs = "takrating.js"
31node = "\"c:\\Program Files\\nodejs\\node.exe\""
32mydb= "openingsdb.db"
33ratingsfile = "takrating-master/ratings.txt"
34
35
36games_db_temp_loc = trfolder +"\\" + games_db[:-3]+ "_temp.db"
37
38debugging = False
39
40# If getting a 'database locked' error in one of the get_ELO functions, try changing i=500 down to i=100 or i=50 in add_ELO_and_games_played_to_games(,,,)
41##=============================================================================
42##=============================================================================
43##=============== ---------------- Player ELOs --------------- ================
44##=============================================================================
45##=============================================================================
46
47
48##------------------------- + Run takrating.js + ------------------------------
49def runtakratingjs(message):
50 # 1. Navigate to folder the the javascript is in
51 os.chdir(folder + "\\" + trfolder)
52
53 # 2. Run node + <name.js>
54 result = subprocess.check_output(node + " " + trjs, shell=True)
55
56 # 3. Navigate back
57 os.chdir(folder)
58 if len(message) != 0 and not debugging:
59 print(message, end='\r')
60
61##------------- + Take the output and add it to the database + ----------------
62
63def make_table_from_txt(file, tablename):
64 # 1. Make a table that will become the database:
65 inFile = open(file, 'r')
66 filestring = inFile.read().strip() # Removes extra \n newline at end.
67 inFile.close()
68
69 linelist = filestring.split("\n")
70 #print(linelist)
71 for i in range(len(linelist)):
72 line = linelist[i].split("|")
73 #print(line)
74 linelist[i] = [ line[1].strip() , # "Name". The actual entry includes quotation marks. CHANGED so that it does not.
75 int( line[2] ), # ELO
76 int( line[4])] # Number of Games
77 #print(linelist[i])
78 #print(linelist[:10],'...', len(linelist),'total.')
79
80 #2. Make the Database
81 conn = sqlite3.connect(mydb) # Open/create a database
82 c = conn.cursor() # Create a cursor/pointer for navigating through the file.
83
84 # 3. Remove any previous table, if one exists
85 c.execute('DROP TABLE IF EXISTS'+ tablename)
86 c.execute('CREATE TABLE IF NOT EXISTS ' + tablename + '(name TEXT, elo INTEGER, gamesplayed INTEGER)')
87
88
89 for line in linelist:
90 c.execute('INSERT INTO ' + tablename + ' VALUES (?,?,?)', line)
91 #print(line)
92
93 conn.commit() # Anytime you make a change, you need to commit.
94 c.close()
95 conn.close()
96
97##------------------ + Make a Datebase to Store Data and ELOs + ---------------
98def makegamesdbbackup():
99 # 1. Make a backup copy of Playtak games database
100 copyfile(trfolder +"\\" + games_db, games_db_temp_loc)
101 print("Created backup of", games_db, 'at', games_db_temp_loc)
102
103
104def makeopeningsdb():
105 # 2. Make a new database to store ELOs, games, and normalized games.
106 copyfile(trfolder +"\\" + games_db, mydb)
107 print("Created working database called", mydb, 'at', trfolder +"\\" + games_db)
108
109def revertgamesdb():
110 # 4a. Revert the backup of Playtak_games database
111 os.remove(trfolder + "\\" + games_db)
112 copyfile(games_db_temp_loc, trfolder + "\\" + games_db)
113 os.remove(folder + "\\" + games_db_temp_loc)
114 print('Backup deleted and', games_db, 'restored.')
115
116# 0a. Move all of this code into openings.py into a function called setup()
117# 0b. Everything else should be in another function functionname() that runs... actually, no. Yes. There should only be ONE function that runs. main() main() is always called in the script, and asks whether or not to run setup (It should be able to check whether setup NEEDs to be run i.e. whether or not openings.db exists). After asking about setup() (and running it if necessary), main() will ask the parameters for openings analysis and run the analysis()
118
119
120##--------------------- + Compute ELOs and Add to Database + ------------------
121# 3. Find the UNIX timestamp when the most recent game was played
122def findmostrecentgamedate():
123 conn = sqlite3.connect(mydb) # Open/create a database
124 c = conn.cursor() # Create a cursor/pointer for navigating through the file.
125
126 c.execute('SELECT MAX(date) FROM games;')
127 maxunixdate = c.fetchall()[0][0] # c.fetchall returns [(1466141169186,)]
128
129 # Remove the ms digits
130 maxunixdate = maxunixdate // 10**3
131 print('Unix timestamp for the most recent game is', maxunixdate)
132
133 # 3.0 Find the date from the timestamp
134 maxdate = datetime.datetime.utcfromtimestamp(maxunixdate).strftime('%Y-%m-%d')
135
136 #conn.commit() # Do not need to commit since no changes were made.
137 c.close()
138 conn.close()
139 return maxdate
140
141
142def add_ELOs_all_dates():
143 # 3.0.1 Make a list of all the dates that the ELO needs to be calculated for.
144 maxdate = findmostrecentgamedate()
145 datelist=[maxdate]
146 while maxdate > '2016-04-23':
147 maxdate = datetime.date(int(maxdate[0:4]), int(maxdate[5:7]), int(maxdate[8:10]) )
148 maxdate = maxdate - datetime.timedelta(days=1)
149 maxdate = maxdate.strftime('%Y-%m-%d')
150 datelist.append(maxdate)
151 print('About to calculate ELOS for',datelist)
152
153 # 3.1 Run takrating.js on games_anon.db
154 for datestr in datelist:
155 # IF the table doesn't exist THEN do the following: TO DO
156 runtakratingjs("\rRan takratingjs for " + datestr)
157
158 # 3.2 Use make_table_from_txt to add a table "2016-06-18" with the ELOs generated
159
160 make_table_from_txt(ratingsfile, '[ELOs_for_' + datestr+']')
161
162 # 3.3 Delete all games AFTER datestr
163 conn = sqlite3.connect(trfolder +"\\" + games_db)
164 c = conn.cursor()
165
166 #unixdate = calendar.timegm(datetime.date(int(datestr[0:4]), int(datestr[5:7]), int(datestr[8:10])).timetuple())
167 unixdate = datetime.datetime(int(datestr[0:4]), int(datestr[5:7]), int(datestr[8:10]) ).timestamp()*10**3
168 unixdate = str(int(unixdate))
169 #print(datestr, unixdate)
170 c.execute('DELETE FROM games WHERE date>' + unixdate)
171
172 conn.commit()
173 c.close()
174 conn.close()
175
176
177def add_ply_counts(database, table, createColumn=True):
178 conn = sqlite3.connect(database)
179 c = conn.cursor()
180
181 # Add a column to store the number of plies in the game
182 if createColumn:
183 c.execute('ALTER TABLE '+ table +' ADD COLUMN plycount INTEGER;')
184
185 c.execute('SELECT id, notation from games')
186 games_table = c.fetchall() # Looks like (7983, 'opticus', 'Glitches')
187
188 i=0
189 while len( games_table) > 0:
190 game_entry = games_table.pop()
191 #print(game_entry)
192
193 game_id = str( game_entry[0] )
194 notation = game_entry[1]
195 #print('Game:', game_id, 'looked like', notation)
196
197 plycount = notation.count(',') + 1
198
199 c.execute('UPDATE ' + table + ' SET plycount =' + str(plycount) + ' WHERE id=' + game_id)
200
201 #This commit make the program ungodly slow. But it seems like if you make ~5000 changes before a commit, the _journal gets too big, and it locks the database. Best bet is probably to commit every 1000 or so games.
202 i = i+1
203 if i==500:
204 conn.commit()
205 i=0
206 print( "\rAdded plycounts till game", game_id +'.', "Commit sent.")
207
208
209 print('Done. Finished adding plycounts to', table, 'in', database)
210
211 conn.commit()
212 c.close()
213 conn.close()
214
215# THIS BLOCK IS DONE. See add_ELOs_all_dates()
216# 4. While the UNIX timestamp is greater than the UNIX timestamp for 2016-04-25 (It looks like the database did not store usernames until April 23rd)
217 # 4.0 Set the Unix time stamp to that of the previous date
218 # 4.1 Remove all games from games_anon with a greater timestamp
219 # 4.2 Run takrating.js again
220 # 4.3 Use make_table_from_txt to add a table named for that date with the ELOs generated
221
222
223def delete_bad_games(database, table):
224 print('Creating a copy of all games in ', table, ' called games_all')
225 conn = sqlite3.connect(database)
226 c = conn.cursor()
227
228 c.execute('CREATE TABLE games_all AS SELECT id, date, size, player_white, player_black, notation, result, plycount FROM games')
229
230 print("\nDeleting games that can't be analyzed from the table ", table, ' in ', database + ':')
231 # 4b. Delete all games before April 24th (before db logged usernames)
232 #c.execute('DELETE FROM '+ table +' WHERE date<' + unixdate)
233 c.execute('DELETE FROM '+ table +' WHERE player_white="Anon" OR player_black="Anon"')
234 print('\tDeleted', c.rowcount, 'games with username Anon')
235
236 # 4c. Delete all games with Guests
237 c.execute('DELETE FROM '+ table +' WHERE player_white LIKE "Guest%" OR player_black LIKE "Guest%" ')
238 print('\tDeleted', c.rowcount, 'games with Guests')
239
240 # I should make a list of non-ranked players that need to be removed, and remove all of them in a for loop. Right now FriendlyBot is the only one, but there may be more later. I think the <botname>_dev accounts might need to be removed, but idk.
241 # 4c2. Delete all games with FriendlyBot, since it has no ELO
242 c.execute('DELETE FROM '+ table +' WHERE player_white = "FriendlyBot" OR player_black = "FriendlyBot" ')
243 print('\tDeleted', c.rowcount, 'games with FriendlyBot')
244
245 ## 4d. Delete all games with less than 6-ish plies
246 #minlength=10*3
247 #c.execute('DELETE FROM '+ table + ' WHERE LENGTH(notation) <' + #str(minlength))
248 #print('\tDeleted', c.rowcount, 'games with fewer than', minlength, 'characters in the notation. (Approx',minlength//5,'plies)')
249
250 # 4d. Properly deleting games with less than 10 plies
251 minplies = 14
252 c.execute('DELETE FROM '+ table + ' WHERE plycount <' + str(minplies))
253 print('\tDeleted', c.rowcount, 'games with fewer than', minplies, 'plies)')
254
255 # 4d. Delete games that were inconclusive
256 minplies = 14
257 c.execute('DELETE FROM '+ table + ' WHERE result ="0-0"')
258 print('\tDeleted', c.rowcount, 'games with result 0-0')
259
260 # 4d2. Delete all games that are size 3 and 4, since they do not count in NoHatCoder's ELO script. (If a player has ONLY played size 4, then they will not have an ELO)
261 minsize=5
262 c.execute('DELETE FROM '+ table + ' WHERE size <' + str(minsize))
263 print('\tDeleted', c.rowcount, 'games played on boards smaller than', minsize)
264
265 conn.commit()
266 c.close()
267 conn.close()
268
269def get_player_elo_and_games_at_date(player, datestr): # Datestr in "YYYY-MM-DD" format
270 #print('Getting ELO for', player, 'on', datestr + ':')
271 conn = sqlite3.connect(mydb)
272 c = conn.cursor()
273
274 table="[ELOs_for_" + datestr +']'
275 player = '"' + player + '"'
276 c.execute('SELECT elo, gamesplayed FROM '+ table +' WHERE name=' + player)
277 #c.execute('SELECT elo FROM [ELOs_for_2016-05-10] WHERE name="KingSultan"')
278 mylist = c.fetchone()
279
280 #conn.commit() # Do not need to commit since no changes were made.
281 c.close()
282 conn.close()
283
284 return mylist
285
286def unixms_to_datestr(unixtimems):
287 unixtime = unixtimems // 10**3
288 datestr = datetime.datetime.utcfromtimestamp(unixtime).strftime('%Y-%m-%d')
289 return datestr
290
291def datestr_to_unixms(datestr):
292 unixtime = calendar.timegm(datetime.date(int(datestr[0:4]), int(datestr[5:7]), int(datestr[8:10])).timetuple())
293 unixtime = unixtimems * 10**3
294 return datestr
295
296
297# 4e Add ELOs into openings.db/games entries
298def add_ELO_and_games_played_to_games(database, table, createColumns): # This should only be run once.
299 # 4e.0 Add columns 'whiteELO' and 'blackELO' and gameswhite, gamesblack
300 print('Adding columns to games in openings.py')
301 conn = sqlite3.connect(database)
302 c = conn.cursor()
303
304 if createColumns:
305 c.execute('ALTER TABLE '+ table +' ADD COLUMN whiteELO INTEGER;')
306 c.execute('ALTER TABLE '+ table +' ADD COLUMN blackELO INTEGER;')
307 c.execute('ALTER TABLE '+ table +' ADD COLUMN whitegames INTEGER;')
308 c.execute('ALTER TABLE '+ table +' ADD COLUMN blackgames INTEGER;')
309 c.execute('ALTER TABLE '+ table +' ADD COLUMN isbotwhite INTEGER;')
310 c.execute('ALTER TABLE '+ table +' ADD COLUMN isbotblack INTEGER;')
311
312 # 4e.1 for each, game get the ELO of each player on that day and enter them into 'whiteELO/blackELO'
313 c.execute('SELECT id, date, player_white, player_black from games')
314 #print(c.fetchall())
315 games_table = c.fetchall() # Looks like (7983, 'opticus', 'Glitches')
316
317 i=0
318 while len( games_table) > 0:
319 game_entry = games_table.pop()
320 #print(game_entry)
321
322 game_id = str( game_entry[0] )
323 unixdate = game_entry[1]
324 datestr = unixms_to_datestr(unixdate)
325
326 player_white = game_entry[2]
327 player_black = game_entry[3]
328 whiteval = get_player_elo_and_games_at_date(player_white, datestr)
329 blackval = get_player_elo_and_games_at_date(player_black, datestr)
330
331 whiteELO = str( whiteval[0] )
332 blackELO = str( blackval[0] )
333 whitegames = str( whiteval[1] )
334 blackgames = str( blackval[1] )
335 isbotwhite = str( int(player_white in botlist) )
336 isbotblack = str( int(player_black in botlist) )
337 #print('P1=', player_white,'has ELO', whiteELO, 'after', whitegames, 'games. ', "P2=", player_black,'has ELO', blackELO, 'after', blackgames, 'games.')
338
339 #txt = 'UPDATE ' + table + ' SET whiteELO =' + whiteELO + ' WHERE id=' + game_id
340 #print(txt)
341 c.execute('UPDATE ' + table + ' SET whiteELO =' + whiteELO + ' WHERE id=' + game_id)
342 c.execute('UPDATE ' + table + ' SET blackELO =' + blackELO + ' WHERE id=' + game_id)
343 c.execute('UPDATE ' + table + ' SET whitegames =' + whitegames + ' WHERE id=' + game_id)
344 c.execute('UPDATE ' + table + ' SET blackgames =' + blackgames + ' WHERE id=' + game_id)
345 c.execute('UPDATE ' + table + ' SET isbotwhite =' + isbotwhite + ' WHERE id=' + game_id)
346 c.execute('UPDATE ' + table + ' SET isbotblack =' + isbotblack + ' WHERE id=' + game_id)
347
348 #This commit make the program ungodly slow. But it seems like if you make ~5000 changes before a commit, the _journal gets too big, and it locks the database. Best bet is probably to commit every 1000 or so games.
349 i = i+1
350 if i==500:
351 conn.commit()
352 i=0
353 print( "\rAdded ELO entries till game", game_id +'.', "Commit sent.")
354
355
356 print('Done. Finished adding ELOs to', table, 'in', database)
357 conn.commit()
358 c.close()
359 conn.close()
360
361
362 # At this point, I could probably delete all the dated ELO tables, or at very close close them out. Actually, I should be closing them out after each use, even though that might be a bit slow.
363
364##------------------- + Decide which games to keep + -----------------
365# Make a function that prettily prints the board state
366
367def is_int(string):
368 try:
369 int(string)
370 return True
371 except ValueError:
372 return False
373
374def get_int(message, min_allowed = -10**20, max_allowed = 10**20): # Both min and max an inclusive
375 # Ask the user for an input
376 print(message, end=': \t')
377 string = input("").strip()
378
379 string_is_int = is_int(string)
380 while not string_is_int:
381 print('\tPlease enter an integer', end=': \t')
382 string = input("")
383 string_is_int = is_int(string)
384
385 int_entered = int(string)
386 while not(int_entered >= min_allowed and int_entered <=max_allowed ):
387 print('\tInput must be between', min_allowed, 'and', max_allowed)
388 int_entered = get_int(message, min_allowed, max_allowed)
389
390 return int_entered
391
392def get_binary_choice(message, yes_list=['true', 't', 'y', 'yes', '1'], no_list = ['false', 'f' ,'n', 'no', '0']):
393 # Ask the user for an input
394 print(message, end=': \t')
395 choice = input("").lower().strip()
396
397 valid_choice = (choice in yes_list) or (choice in no_list)
398 while not valid_choice:
399 print('\tPlease select from', yes_list, 'or', no_list, end=': \t')
400 choice = input("").lower()
401 valid_choice = (choice in yes_list) or (choice in no_list)
402
403
404 return choice in yes_list
405
406
407def get_date(message, mindate = '2016-04-23', maxdate = '2099-01-01'):
408 # Ask the user for an input
409 print(message, end=': \t')
410 string = input("").strip()
411
412 valid_date = len(string) == 10 and string[0:4].isnumeric() and string[5:7].isnumeric() and string[8:10].isnumeric()
413 while not valid_date:
414 print('\tPlease enter a date in YYYY-MM-DD format', end=': \t')
415 string = input("").strip()
416 valid_date = len(string) == 10 and string[0:4].isnumeric() and string[5:7].isnumeric() and string[8:10].isnumeric()
417
418 date = string[0:4] + '-' + string[5:7] + '-' + string[8:10]
419 # Checking that the date is plausible. If won't catch things like 2016-03-31, but, I'm not about to figure out how to code in leap years.
420 while not (date >= mindate and date <= maxdate and int(date[5:7])<=12 and int(date[8:10])<=31 ):
421 print('\tDate must be between', mindate, 'and' , maxdate)
422 date = get_date(message, mindate, maxdate)
423
424 return date
425
426# 5. Decide which games to keep
427 # 5.0.0 Ask which size
428 # 5.0.1 Ask min ELO
429 # 5.0.1 Ask max ELO
430 # 5.0.1b Ask if both players must meet ELO req, or just one
431 # 5.0.2 Ask min games played
432 # 5.0.2b Ask if both players must meet ELO req, or just one
433 # 5.0.2c Ask whether to inclue bots
434 # 5.0.3 Ask how many plies (1-999). If plies > 50, check how many games there are and warn the player that the average is likely to be low.
435 # 5.0.4 Ask start date (YYYY MM DD). Must be after April 24th
436 # 5.0.5 Ask end date (YYYY MM DD). Must be after start date. (Later I will need to take the min of this and the date of the most recent games)
437
438def ask_settings():
439 useDefaults = get_binary_choice("Use default settings?")
440 if useDefaults:
441 size = 5
442 plycount = get_int('\nHow many plies?[1-999]', 1, 999)
443 minELO = 1400
444 maxELO = 9000
445 bothmeetELO = True
446 mingames = 10
447 includebots = True
448 includetimeends = False
449 startdate = '2016-04-23'
450 enddate = '2099-01-01'
451 else:
452 size = get_int('\nBoard size [5-8]', 5, 8)
453 plycount = get_int('\nHow many plies?[1-999]', 1, 999)
454 minELO = get_int('\nMinimum player ELO [+]', 1, 9000)
455 maxELO = get_int('\nMaximum player ELO [+]', minELO, 9000)
456 bothmeetELO = get_binary_choice('\nMust both players meet ELO minimum?')
457 mingames = get_int('\nMinimum number of games each player must have played', 1)
458 includebots = get_binary_choice('\nInclude games with bots?')
459 includetimeends = get_binary_choice('\nInclude games that ended on time or disconnect?')
460 startdate = get_date('Include games after date [YYYY-MM-DD]')
461 enddate = get_date('Include games before date [YYYY-MM-DD]', startdate)
462
463 return [size, plycount, minELO, maxELO, bothmeetELO, includebots, startdate, enddate, mingames, includetimeends]
464
465 # 5.0 Add all games of size desired to table 'good_games
466
467 # There is definitely a faster way to do these variable assignments.
468
469def make_table_name(settingslist):
470 tablename = 'analyze'
471 for item in settingslist:
472 tablename = tablename + '_' + str(item)
473 return tablename
474
475def create_table_from_settings(settingslist, database):
476 size = settingslist[0]
477 plycount = settingslist[1]
478 minELO = settingslist[2]
479 maxELO = settingslist[3]
480 bothmeetELO = settingslist[4]
481 includebots = settingslist[5]
482 startdate = settingslist[6]
483 enddate = settingslist[7]
484 mingames = settingslist[8]
485 includetimeends = settingslist[9]
486
487 if bothmeetELO: # The negation for deleting messes this up
488 bothmeetELOstr = ' OR '
489 else:
490 bothmeetELOstr = ' AND '
491
492 conn = sqlite3.connect(database)
493 c = conn.cursor()
494
495 newtablename = "[" + make_table_name(settingslist) +"]"
496
497 print('Creating table', newtablename, 'to store normalized boards and openings scores.')
498
499 c.execute("CREATE TABLE IF NOT EXISTS " + newtablename + " AS SELECT * FROM games")
500
501 c.execute('DELETE FROM '+newtablename+' WHERE size!=' + str(size))
502 print('\tDeleted', c.rowcount, 'games that were not of size', size)
503
504
505 c.execute('DELETE FROM '+newtablename+' WHERE whiteELO < ' + str(minELO) + bothmeetELOstr + ' blackELO < ' + str(minELO))
506 print('\tDeleted', c.rowcount, 'games where ' + 'a'*(not bothmeetELO) + 'both'*bothmeetELO + ' player ELOs were less than', minELO)
507
508
509 c.execute('DELETE FROM '+newtablename+' WHERE whiteELO > ' + str(maxELO) + bothmeetELOstr + ' blackELO > ' + str(maxELO))
510 print('\tDeleted', c.rowcount, 'games where ' + 'a'*(not bothmeetELO) + 'both'*bothmeetELO + ' player ELOs were more than', maxELO)
511
512 c.execute('DELETE FROM '+newtablename+' WHERE plycount < ' + str(plycount))
513 print('\tDeleted', c.rowcount, 'games where plycount was less than', plycount)
514
515 c.execute('DELETE FROM '+newtablename+' WHERE whitegames < ' + str(mingames) + ' OR blackgames < ' + str(mingames))
516 print('\tDeleted', c.rowcount, 'games where one player had played less than', mingames, 'games')
517
518 if not includetimeends:
519 c.execute('DELETE FROM '+newtablename+' WHERE result = "1-0" or result = "0-1"')
520 print('\tDeleted', c.rowcount, 'that ended on time or disconnection.')
521
522 c.execute('SELECT count(*) FROM '+newtablename)
523 gamecount = c.fetchone()
524 print('\tThis table has', gamecount, 'games.')
525
526
527 # Deal with bots
528 bottotal=0
529 if not includebots:
530 for bot in botlist:
531 bot = '"' + bot + '"'
532 c.execute('DELETE FROM '+newtablename+' WHERE player_white = ' + bot + ' OR player_black = ' + bot)
533 bottotal += c.rowcount
534 print('\tDeleted', bottotal, 'games played by bots. (The bot list is hard coded.)')
535
536 # Deal with startdate
537 # Deal with enddate
538
539 conn.commit()
540 c.close()
541 conn.close()
542
543 # 5.0b Add a column in good_games called score###, ### for the number of plies
544 # 5.1 Remove all games where either/both players' ELO is lower than min (Maybe define get_player_ELO(player,date) [Still need to do this, but earlier. By now the ELOs should be in the same row.]
545 # 5.2 Remove all games where either/both player have played fewer than min games
546 # 5.3 Remove games with too few plies.
547 # 5.4 Remove games before start date.
548 # 5.5 Remove games after end date.
549 # 5.6 Print out how many games like this were found, and if fewer than 10, quit.
550
551# At this point I have a database with the games I want to analyze, and daily player ELOs.
552# 6. Make a function that scores each game given the player names:
553 # 6.1 score(whiteELO, blackELO, result) = 1/ELO win-probability, where ELO-w-p is P(a)=1/(1+10^d) where d is elo_b - elo_a. The white player gets 110 added to their elo for first player advantange. + for white wins, - for black wins. If it is a tie, take the ?average? of a white and black win
554def win_prob_white(whiteELO, blackELO):
555 diff = blackELO - whiteELO
556 return 1/(1+10**( diff/400 ))
557
558def score_win1(result, whiteELO, blackELO): # This is the inverse opponent win chance score # I probably want to subtract 1 from r-0 and 0-r these. Let's see what happens
559 whiteELO += whiteELOadvantage
560 if result in ['F-0', 'R-0', '1-0']:
561 return 1/win_prob_white(whiteELO, blackELO)
562 elif result in ['0-F', '0-R', '0-1']:
563 return -1/(1-win_prob_white(whiteELO, blackELO))
564 elif result in ['1/2-1/2']:
565 return 0.5 /win_prob_white(whiteELO, blackELO) -0.5/(1-win_prob_white(whiteELO, blackELO))
566 elif result in ['0-0']:
567 print('Asked to calculate score1 for inconclusive game. Were the bad games not removed properly?')
568 time.sleep(1)
569 return 0
570 else:
571 print(result)
572 raise ValueError('score_win1 not given a valid result ', result )
573
574def score_win2(result, whiteELO, blackELO): # The is the opponent win chance score
575 whiteELO += whiteELOadvantage
576 if result in ['F-0', 'R-0', '1-0']:
577 return 1-win_prob_white(whiteELO, blackELO)
578 elif result in ['0-F', '0-R', '0-1']:
579 return win_prob_white(whiteELO, blackELO)
580 elif result in ['1/2-1/2']:
581 return 0.5*(1-win_prob_white(whiteELO, blackELO)) +0.5*win_prob_white(whiteELO, blackELO)
582 elif result in ['0-0']:
583 print('Asked to calculate score2 for inconclusive game. Were the bad games not removed properly?')
584 time.sleep(1)
585 return 0
586 else:
587 print(result)
588 raise ValueError('score_win2 not given a valid game result ', result )
589
590
591# 7. Score each opening
592 # 7.0 For each game, calculate the score and store it in score### column
593
594def add_scores_to_table(database, table, createColumn = True):
595 conn = sqlite3.connect(database)
596 c = conn.cursor()
597
598 # Add a column to store the number of plies in the game
599 if createColumn:
600 c.execute('ALTER TABLE '+ table +' ADD COLUMN score1 REAL;')
601 c.execute('ALTER TABLE '+ table +' ADD COLUMN score2 REAL;')
602 #c.execute('ALTER TABLE '+ table +' ADD COLUMN score3 REAL;')
603
604 c.execute('SELECT id, result, whiteELO, blackELO from games')
605 games_table = c.fetchall() # Looks like (7983, 1430, 1590)
606
607 i=0
608 while len( games_table) > 0:
609 game_entry = games_table.pop()
610 #print(game_entry)
611
612 game_id = str( game_entry[0] )
613 result = game_entry[1]
614 whiteELO = game_entry[2]
615 blackELO = game_entry[3]
616 #print('\rGame:', game_id)
617
618 score1 = score_win1(result, whiteELO, blackELO)
619 score2 = score_win2(result, whiteELO, blackELO)
620
621 c.execute('UPDATE ' + table + ' SET score1 =' + str(score1) + ' WHERE id=' + game_id)
622 c.execute('UPDATE ' + table + ' SET score2 =' + str(score2) + ' WHERE id=' + game_id)
623 #c.execute('UPDATE ' + table + ' SET score3 =' + str(score3) + ' WHERE id=' + game_id)
624
625 #This commit makes the program ungodly slow. But it seems like if you make ~5000 changes before a commit, the _journal gets too big, and it locks the database. Best bet is probably to commit every 1000 or so games.
626 i = i+1
627 if i==500:
628 conn.commit()
629 i=0
630 print( "\rAdded scores till game", game_id +'.', "Commit sent.")
631
632
633 print('Done. Finished adding scores to', table, 'in', database)
634
635 conn.commit()
636 c.close()
637 conn.close()
638 # 7.1.0 Create a column 'normmoves###'
639 # 7.1.1 For each game, find the normalized moves to ### plies and store it in normmoves###
640 # 7.2 Create a new table 'openings###' (moves, boardstate totalscore, num_games) (This is redundant, but worth having to veiw instantly)
641 # 7.3 For each game, check if the normmoves### is in openings###
642 # If it is, add it's score to the total score, and increase num_games by 1
643 # If not, add a new row for it.
644
645def add_norm_moves_and_board(database, table, plycount, createColumn=True):
646 conn = sqlite3.connect(database)
647 c = conn.cursor()
648
649 plycountstr = '0'*(3-len( str(plycount) )) + str(plycount)
650
651 # Add a column to store the number of plies in the game
652 if createColumn:
653 table = '[' + table + ']'
654 c.execute('ALTER TABLE '+ table +' ADD COLUMN nmoves'+ plycountstr +' INTEGER;')
655 c.execute('ALTER TABLE '+ table +' ADD COLUMN nboard'+ plycountstr +' INTEGER;')
656
657 c.execute('SELECT id, size, notation from games')
658 games_table = c.fetchall() # Looks like (7983, 'opticus', 'Glitches')
659
660 i=0
661 while len( games_table) > 0:
662 game_entry = games_table.pop()
663 #print(game_entry)
664
665 game_id = str( game_entry[0] )
666 board_size = game_entry[1]
667 move_string = game_entry[2] # TO DO This needs to be chopped down to just the first plycount plies
668
669 ##nmoves = '"' + normalize_moves(move_string, plycount, board_size) + '"'
670 board_state = build_board_from_moves(move_string, plycount, board_size)
671 nboard = '"' + str(normalize_board(board_state)) + '"'
672
673 #print('Game:', game_id, 'looked like', move_string[:40])
674 #print('Normalized moves:', nmoves)
675 #print('Normalized board:', nboard)
676
677 ##c.execute('UPDATE ' + table + ' SET nmoves'+plycountstr+' = ' + nmoves + ' WHERE id=' + game_id)
678 c.execute('UPDATE ' + table + ' SET nboard'+plycountstr+' = ' + nboard + ' WHERE id=' + game_id)
679
680 #This commit make the program ungodly slow. But it seems like if you make ~5000 changes before a commit, the _journal gets too big, and it locks the database. Best bet is probably to commit every 1000 or so games.
681 i = i+1
682 if i==500:
683 conn.commit()
684 i=0
685 print( "\rAdded normalized notation and boards till game", game_id +'.', "Commit sent.")
686
687
688 print('Done. Finished adding normalized notation and boards to', table, 'in', database)
689
690 conn.commit()
691 c.close()
692 conn.close()
693
694
695
696def aggregate_opening_boards(database, tablename, settingslist):
697 conn = sqlite3.connect(database)
698 c = conn.cursor()
699
700 plycount = str( settingslist[1] )
701 boardcolumnname = 'nboard' + '0'*(3-len(str( plycount ))) + str(plycount)
702 newtablename = '[score_' + tablename + ']'
703 # I should drop the table first, so I don't end up with duplicate entries
704 # TO DO also include columns for how much higher the win rates are above the average win rates
705 c.execute('CREATE TABLE IF NOT EXISTS ' + newtablename + '(board STRING, score1 REAL, score2 REAL, score3 REAL, avwhiteELO REAL, avblackELO REAL, gamecount INTEGER, white_wins INTEGER, black_wins INTEGER, ties INTEGER, white_rate REAL, black_rate REAL, tie_rate REAL)')
706
707 c.execute( "SELECT `"+boardcolumnname+"`\
708 FROM `"+ tablename +"` \
709 GROUP BY `"+boardcolumnname+"` \
710 ORDER BY COUNT(*) DESC \
711 LIMIT 20; ")
712
713 boardstr_list = c.fetchall()
714 print(boardstr_list)
715 # For each Board
716 for boardstr in boardstr_list:
717 boardstr = boardstr[0] #Each board is inside a 1-tuple.
718 board = eval(boardstr)
719 print_board(board)
720
721 #print('tablename ',tablename, 'boardcolumn', boardcolumnname, 'boardstr', boardstr)
722 c.execute('SELECT score1, score2, result, whiteELO, blackELO from ['+tablename+'] WHERE '+boardcolumnname+' = "'+ boardstr+ '"')
723 games_table = c.fetchall()
724
725 total_score1 = 0
726 total_score2 = 0
727 total_whiteELO = 0
728 total_blackELO = 0
729 white_win_count = 0
730 black_win_count = 0
731 tie_count = 0
732 gamecount = 0 # This is redundant, since I can just do win + loss + ties
733
734 while len( games_table) > 0: # There may be a better way to be these counts and sums using purely SQLite
735 game_entry = games_table.pop()
736 print(game_entry)
737
738 gamecount += 1 # This is redundant, as before
739 total_score1 += game_entry[0]
740 total_score2 += game_entry[1]
741 total_whiteELO += game_entry[3]
742 total_blackELO += game_entry[4]
743
744 result = game_entry[2]
745
746 if result in ['R-0', 'F-0', '1-0']:
747 white_win_count += 1
748 elif result in ['0-R', '0-F', '0-1']:
749 black_win_count += 1
750 elif result in ['1/2-1/2']:
751 tie_count += 1
752 else:
753 raise ValueError('Invalid result '+ result + 'in the row ' + str(game_entry) )
754
755
756
757 c.execute('INSERT INTO ' + newtablename + '(board, score1, score2, avwhiteELO, avBlackELO, gamecount, white_wins, black_wins, ties, white_rate, black_rate, tie_rate) VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?)', (boardstr, total_score1, total_score2, total_whiteELO/gamecount, total_blackELO/gamecount, gamecount, white_win_count, black_win_count, tie_count, white_win_count/gamecount, black_win_count/gamecount, tie_count/gamecount))
758
759
760
761 print('Done. Finised aggregating scores from', tablename, 'in', database)
762
763 conn.commit()
764 c.close()
765 conn.close()
766
767
768##------------------- + Make a Datebase to Store Daily ELOs + -----------------
769
770# 0a. Move all of this code into openings.py into a function called setup()
771# 0b. Everything else should be in another function functionname() that runs... actually, no. Yes. There should only be ONE function that runs. main() main() is always called in the script, and asks whether or not to run setup (It should be able to check whether setup NEEDs to be run i.e. whether or not openings.db exists). After asking about setup() (and running it if necessary), main() will ask the parameters for openings analysis and run the analysis()
772
773# 3. Find the UNIX timestamp when the most recent game was played
774 # 3.0 Find the date from the timestamp
775 # 3.1 Run takrating.js on games_anon.db
776 # 3.2 Use make_table_from_txt to add a table "2016-06-18" with the ELOs generated
777
778# 4. While the UNIX timestamp is greater than the UNIX timestamp for 2016-04-25 (It looks like the database did not store usernames until April 23rd)
779 # 4.0 Set the Unix time stamp to that of the previous date
780 # 4.1 Remove all games from games_anon with a greater timestamp
781 # 4.2 Run takrating.js again
782 # 4.3 Use make_table_from_txt to add a table named for that date with the ELOs generated
783
784
785
786# 4b. Delete all games before April 24th (before db logged usernames)
787# 4c. Delete all games with Guests
788# 4d. Delete all games with less than 4 moves
789
790# 4e Add ELOs into openings.db/games entries
791 # 4e.0 Add columns 'whiteELO' and 'blackELO'
792 # 4e.1 for each, game get the ELO of each player on that day and enter them into 'whiteELO/blackELO'
793 # At this point, I could probably delete all the dated ELO tables, or at very closet close them out. Actually, I should be closing them out after each use, even though that might be a bit slow.
794
795
796##------------------- + Decide which games to keep + -----------------
797# Do this in openings.py
798# Make a function that prettily prints the board state
799# 5. Decide which games to keep
800 # 5.0.0 Ask which size
801 # 5.0.1 Ask min ELO
802 # 5.0.1b Ask if both players must meet ELO req, or just one
803 # 5.0.2 Ask min games played
804 # 5.0.2b Ask if both players must meet ELO req, or just one
805 # 5.0.3 Ask how many plies (1-999). If plies > 50, check how many games there are and warn the player that the average is likely to be low.
806 # 5.0 Add all games of size desired to table 'good_games'
807 # 5.0b Add a column in good_games called score###, ### for the number of plies
808 # 5.1 Remove all games where either/both players' ELO is lower than min (Maybe define get_player_ELO(player,date) [Still need to do this, but earlier. By now the ELOs should be in the same row.]
809 # 5.2 Remove all games where either/both player have played fewer than min games
810 # 5.3 Remove games with too few plies.
811
812# At this point I have a database with the games I want to analyze, and daily player ELOs.
813# 6. Make a function that scores each game given the player names:
814 # 6.1 score(whiteELO, blackELO, result) = 1/ELO win-probability, where ELO-w-p is P(a)=1/(1+10^d) where d is elo_b - elo_a. The white player gets 110 added to their elo for first player advantange. + for white wins, - for black wins. If it is a tie, take the ?average? of a white and black win
815
816# 7. Score each opening
817 # 7.0 For each game, calculate the score and store it in score### column
818 # 7.1.0 Create a column 'normmoves###'
819 # 7.1.1 For each game, find the normalized moves to ### plies and store it in normmoves###
820 # 7.2 Create a new table 'openings###' (moves, boardstate totalscore, num_games) (This is redundant, but worth having to veiw instantly)
821 # 7.3 For each game, check if the normmoves### is in openings###
822 # If it is, add it's score to the total score, and increase num_games by 1
823 # If not, add a new row for it.
824
825##=============================================================================
826##=============================================================================
827##=============== -------- Board/Tile Transformations -------- ================
828##=============================================================================
829##=============================================================================
830
831def x_coord(tile): # This is from the usual perspective. The letter axis is the x axis.
832 return letters.index(tile[0].upper()) # A1 corner is (0,0) a1 a2 a3 a4 a5
833 # .upper(makes it no longer case sensitve)
834
835def y_coord(tile):
836 return int(tile[1])-1
837
838##-----------------------------------------------------------------------------
839def coord_to_tile(coord): # Co-ord should be a list. Returns a string.
840 return str(letters[coord[0]]) + str( int(coord[1])+1 )
841
842def tile_to_coord(tile): # Takes the string tile and returns to corresponding co-ordinate as a LIST
843 return [x_coord(tile), y_coord(tile)]
844
845##--------------------------- + Rotations + ----------------------------------
846def rotate_coord(coord,size,number_of_rotations): # CLOCKWISE
847 for i in range(number_of_rotations % 4): # Working modulo 4 allows negative rotations.
848 coord=[ coord[1], (size-1)-coord[0]]
849 return coord
850
851def rotate_tile(tile,size,number_of_rotations):
852 coord = tile_to_coord(tile)
853 newcoord = rotate_coord(coord, size, number_of_rotations)
854 return coord_to_tile(newcoord)
855
856def rotate_board(board_state): #, board_size, number_of_rotations):
857 return list(zip(*board_state[::-1]))
858
859def rotate_board_n(board_state, rotations):
860 for i in range(rotations % 4 + 4): # Rotating changes the data type. Hopefully rotating 4 times instead of 0 fixes the problems that were showing up in the normalization stuff.
861 board_state = list(zip(*board_state[::-1]))
862 return board_state
863
864##-----------------------------------------------------------------------------
865def rotate_move(move, size, number_of_rotations):
866 #1. Extract the tile from the string of the single move.
867 tile = move[2:4]
868 tile = rotate_tile(tile, size, number_of_rotations)
869
870 # 2. Rotate the starting tiles
871 new_move = move[0:2] + tile + move[4:] #"P " + "A1" + "<the rest>"
872
873 # 3. If the stack is being moved, rotate the destination tile.
874 if move[0] == "M":
875 end_tile = move[5:7]
876 end_tile = rotate_tile(end_tile, size, number_of_rotations)
877 new_move = new_move[0:5] + end_tile + new_move[7:] #"M A1 " + "A3" + "2 1 1"
878
879 return new_move
880
881def rotate_move_string(move_string,size, number_of_rotations): # This could possibly done more simply by doing a mass simultanteous replacement of each tile by it's image under rotation.
882 move_list = move_string.split(',')
883 for i in range(len( move_list )):
884 move = move_list[i].strip().upper()
885 new_move = rotate_move(move, size, number_of_rotations)
886
887 #4. Replace the old move with the rotated one
888 move_list[i] = new_move
889
890 #5. Reassemable the moves into a string:
891 new_move_string = ','.join(move_list)
892 return new_move_string
893
894##----------------------------- + Flips + ------------------------------------
895
896def flipped_coord_let(coord,size):
897 coord=[ (size-1)- coord[0], coord[1]]
898 return coord
899
900def flipped_coord_num(coord,size):
901 coord=[ coord[0], (size-1)-coord[1]]
902 return coord
903
904
905def flipped_tile_let(tile,size): #This could also be done with a very simple replacement
906 coord = tile_to_coord(tile)
907 newcoord = flipped_coord_let(coord, size)
908 return coord_to_tile(newcoord)
909
910def flipped_tile_num(tile,size): #This could also be done with a very simple replacement
911 coord = tile_to_coord(tile)
912 newcoord = flipped_coord_num(coord, size)
913 return coord_to_tile(newcoord)
914
915##-----------------------------------------------------------------------------
916
917def flipped_board_let(board): # The letters change, the numbers stay the same.
918 new_board = board[:]
919 new_board.reverse()
920 return new_board
921
922def flipped_board_num(board): # The numbers change, the letters stay the same.
923 newboard = board[:]
924 for i in range(len(newboard)):
925 newboard[i]= newboard[i].reverse()
926 return newboard
927
928
929##-----------------------------------------------------------------------------
930
931# The flipped_move_xxx and flipped_move_string_xxx are both the same as rotate_move, and rotate_move_string repectively, with the specific transformation functions changed appropriately. (rotate_yyyyy with flipped_yyyyy_xxx)
932
933def flipped_move_num(move, size):
934 #1. Extract the tile from the string of the single move.
935 tile = move[2:4]
936 tile = flipped_tile_num(tile, size)
937
938 # 2. Rotate the starting tiles
939 new_move = move[0:2] + tile + move[4:] #"P " + "A1" + "<the rest>"
940
941 # 3. If the stack is being moved, rotate the destination tile.
942 if move[0] == "M":
943 end_tile = move[5:7]
944 end_tile = flipped_tile_num(end_tile, size)
945 new_move = new_move[0:5] + end_tile + new_move[7:] #"M A1 " + "A3" + "2 1 1"
946
947 return new_move
948
949def flipped_move_let(move, size):
950 #1. Extract the tile from the string of the single move.
951 tile = move[2:4]
952 tile = flipped_tile_let(tile, size)
953
954 # 2. Rotate the starting tiles
955 new_move = move[0:2] + tile + move[4:] #"P " + "A1" + "<the rest>"
956
957 # 3. If the stack is being moved, rotate the destination tile.
958 if move[0] == "M":
959 end_tile = move[5:7]
960 end_tile = flipped_tile_let(end_tile, size)
961 new_move = new_move[0:5] + end_tile + new_move[7:] #"M A1 " + "A3" + "2 1 1"
962
963 return new_move
964
965##-----------------------------------------------------------------------------
966def flipped_move_string_num(move_string, size): # This could possibly done more simply by doing a mass simultanteous replacement of each tile by it's image under rotation.
967 move_list = move_string.split(',')
968 for i in range(len( move_list )):
969 move = move_list[i].strip().upper()
970 new_move = flipped_move_num(move, size)
971
972 #4. Replace the old move with the rotated one
973 move_list[i] = new_move
974
975 #5. Reassemable the moves into a string:
976 new_move_string = ','.join(move_list)
977 return new_move_string
978
979def flipped_move_string_let(move_string, size): # This could possibly done more simply by doing a mass simultanteous replacement of each tile by it's image under rotation.
980 move_list = move_string.split(',')
981 for i in range(len( move_list )):
982 move = move_list[i].strip().upper()
983 new_move = flipped_move_let(move, size)
984
985 #4. Replace the old move with the rotated one
986 move_list[i] = new_move
987
988 #5. Reassemable the moves into a string:
989 new_move_string = ','.join(move_list)
990 return new_move_string
991
992
993##=============================================================================
994##=============================================================================
995##=============== ------------- Building a Board ------------- ================
996##=============================================================================
997##=============================================================================
998
999
1000def tiles_inbetween(tile_start,tile_end): # Given A1, A5, would return ["A1", "A2", ..., "A5"], the straight path between them.
1001 tile_path=[tile_start, tile_end]
1002 need_to_reverse = False # 0. It is redundant to write code for both "M A1 A5 1" and "M A5 A1 1" separately. This helps the script convert "A5 A1" to "A1 A5", and then convert back.
1003
1004 # 1a. The Letters are the same
1005 if tile_start[0]==tile_end[0]:
1006 # 2. To avoid 4 cases, I will combine the cases where a1->a3 and a3->a1.
1007 if tile_start[1]>tile_end[1]:
1008 need_to_reverse = True
1009 tile_path.reverse()
1010 tile_start = tile_path[0]
1011 tile_end = tile_path[1]
1012
1013 # 3. At this point, the letters are the same, and the number is increasing.
1014 s = int(tile_start[1])
1015 e = int(tile_end[1])
1016
1017 # 4. This is what actually builds the path
1018 s = s+1
1019 while s < e:
1020 tile_path.insert(-1, tile_start[0] + str(s))
1021 s = s+1
1022 # print(s,e,tile_path)
1023
1024 # 1b. The Numbers are the same
1025 elif tile_start[1] == tile_end[1]:
1026 # 2. To avoid 4 cases, I will combine the cases where a1->c1 and c1->a1.
1027 if tile_start[0]>tile_end[0]:
1028 need_to_reverse = True
1029 tile_path.reverse()
1030 tile_start=tile_path[0]
1031 tile_end=tile_path[1]
1032
1033 # 3. At this point, the numbers are the same, and the letter is increasing.
1034 s = tile_start[0]
1035 e = tile_end[0]
1036 #print(s,e,tile_path, "before")
1037
1038 # 4. This is what actually builds the path
1039 s=chr(ord(s)+1) # This acts like 'A' + 1 ='B'
1040 while s < e:
1041 tile_path.insert(-1, s+ tile_start[1])
1042 s=chr(ord(s)+1)
1043 #print(s,e,tile_path)
1044 else:
1045 raise ValueError('Start and End in tiles_inbetween not in same row or column. Start=', tile_start, "End=", tile_end)
1046
1047 if need_to_reverse:
1048 tile_path.reverse()
1049 return tile_path
1050
1051##-----------------------------------------------------------------------------
1052def print_board(board): # TO DO make this a prettier type of output
1053 for row in board:
1054 print(row)
1055
1056def build_board_from_moves(move_string, plies,board_size):
1057 board = [['' for i in range(board_size)] for j in range(board_size)]
1058 move_list = move_string.split(',')
1059
1060 # 0. If -1 plies are requested, that means build a board using all the moves in the string given.
1061 if plies == -1:
1062 plies = len(move_list)
1063
1064 # 1. Go through each move in the list and place and move peices as specified.
1065 for i in range( len(move_list[:plies]) ):
1066 move=move_list[i].strip()
1067
1068 #2. Placing a peice
1069 if move[0]=="P":
1070 x = letters.index(move[2]) # A1 corner is (0,0) a1 a2 a3 a4 a5
1071 y = int(move[3])-1 # b1 b2 b3 b4 b5 is the layout.
1072 #print(i,move,x,y,"move",move[5:]) # Error diagnostic prints
1073 #[print(row) for row in board] # Error diagnostic prints
1074
1075 #3. First two moves are weird (first a black tile is placed, then a white tile), thereafer even indices are white, odd are black (since indexing starts at 0)
1076 if i==0: board[x][y]= 'b'
1077 if i==1: board[x][y]= 'a'
1078 if i> 1 and i%2==0: board[x][y]= move[5:] + 'a' # move[i][5:6] looks like "P C1 W"[5:6], and get the wall if it is needed.
1079 if i> 1 and i%2==1: board[x][y]= move[5:] + 'b'
1080
1081 # 3. Moving stacks
1082 if move[0]=="M":
1083 #print("About to make the move ", move, " on ply", i, ". The board looks like:")
1084 #for row in board:
1085 # print(row)
1086
1087 # 4. Figure out which line to move the tiles on.
1088 drop_counts = move[8:] # The format of stack-moves is "M A1 A5 # # # # #", where the number of # is how many tiles are between A1 and A5
1089 drop_counts = drop_counts.split()
1090 for j in range( len(drop_counts) ):
1091 drop_counts[j]= int(drop_counts[j])
1092 tile_start = move[2:4]
1093 tile_end = move[5:7]
1094 tile_path = tiles_inbetween(tile_start, tile_end)
1095
1096
1097 # 6. Having a wall or a capstone will change how many characters (i.e stones) need to be placed at the ending tile
1098 hasCapstoneOrWall = board[x_coord(tile_start)][y_coord(tile_start)][0] in ["W","C"]
1099
1100 #print("\n \t Drop counts:", drop_counts, "\n \t Tile path:", tile_path, "\n \t -Tile start:", tile_start, "\n \t -Tile end:", tile_end, "\n \t Has Capstone/Wall:", hasCapstoneOrWall)
1101
1102 # 5. Add stones to each of the tiles on the line, based on the dropcounts (and remove them from the starting tile).
1103 for j in range( len(tile_path)-1 ):
1104 #9. Knock down walls if you move a capstone onto one.
1105 if board[x_coord(tile_start)][y_coord(tile_start)][0]=="C" and board[x_coord(tile_end)][y_coord(tile_end)][:1]=="W":
1106 board[x_coord(tile_end)][y_coord(tile_end)]=board[x_coord(tile_end)][y_coord(tile_end)][1:]
1107
1108 num_pick_up = drop_counts[-(j+1)] + hasCapstoneOrWall*(j==0) #Adds one to the total only if the first character in the pile you pick up from has a capstone or wall on top.
1109 pick_up = board[x_coord(tile_start)][y_coord(tile_start)][:num_pick_up]
1110 #print("\t Number picked up:", num_pick_up, "\n \t Peices picked up:", pick_up)
1111
1112 # 7. Remove picked up stones from the starting tile
1113 board[x_coord(tile_start)][y_coord(tile_start)] = board[x_coord(tile_start)][y_coord(tile_start)][num_pick_up:]
1114 #print("\nAfter removing from ", letters[x_coord(tile_start)], y_coord(tile_start)+1 ," there remain", board[x_coord(tile_start)][y_coord(tile_start)], "also" )
1115 #[print(row) for row in board]
1116
1117 # 8. And place them on the ending tile, and before (j).
1118 board[x_coord(tile_path[-(j+1)])][y_coord(tile_path[-(j+1)])]= pick_up + board[x_coord(tile_path[-(j+1)])][y_coord(tile_path[-(j+1)])]
1119 #print("\nAfter placing, there now are", board[x_coord(tile_path[-j])][y_coord(tile_path[-j])])
1120
1121
1122
1123
1124
1125 #print("Complete. The board is:")
1126 #[print(row) for row in board]
1127 #print("-"*4*board_size)
1128 return board
1129
1130
1131##=============================================================================
1132##=============================================================================
1133##=========== ------- Finding and Fixing Congruent Games ------- ==============
1134##=============================================================================
1135##=============================================================================
1136
1137##------------------------ + Most Populated Corner + --------------------------
1138# The new approach is to build all 8 orientations of the board and checking which one is the heaviest in the bottom left corner (The A1, or [0,0] corner). "Most populated" is not accurate, since the algorithim will rate a quadrant with 1 tile in the corner higher than any quadrant that does not have a tile in the corner. This can cause the normalization of a board to flip abruptly, but I don't think that there is any (computationally easy) normalization algorithim where some boards do not flip at some point.
1139
1140
1141# The order this generates is:
1142# 10
1143# 6 9
1144# 3 5 8
1145# 1 2 4 7
1146def build_diagonal_coord_list(board_size):
1147 coord_list=[]
1148 for xy_sum in range(2*board_size-1):
1149 for y in range(max(0, xy_sum-board_size+1) ,min(board_size,xy_sum+1)):
1150 x = xy_sum-y
1151 coord_list.append( [x,y] )
1152 return coord_list
1153
1154
1155def coord_value(coord, board):
1156 value = 0
1157 pile = board[coord[0]][coord[1]].strip()
1158
1159 # 1. Add value for Capstones, Walls, and flats
1160 # The values as assigned make it possible to read off the composition of the pile.
1161 # This could possible be done more efficiently.
1162 if "Ca" in pile:
1163 value += 60000
1164 pile = pile[2:]
1165 elif "Cb" in pile:
1166 value += 50000
1167 pile = pile[2:]
1168 elif "Wa" in pile:
1169 value += 40000
1170 pile = pile[2:]
1171 elif "Wb" in pile:
1172 value += 30000
1173 pile = pile[2:]
1174
1175 # 2. Add value for Flats
1176 value += 100 * pile.count('a')
1177 value += 1 * pile.count('b')
1178
1179 return value
1180
1181def anyduplicates(thelist):
1182 seen = []
1183 for x in thelist:
1184 if x in seen:
1185 return True
1186 seen.append(x)
1187 return False
1188
1189# This is ugly, but I'm getting weird issues where new_board=board[:] isn't copying the board, it is just making new_board a pointer for board. I think it has to do with board[:] being thought of as [board[0], board[1]], but I don't know.
1190def remove_bottom_layer(board):
1191 size = len(board)
1192 new_board = ['']*size
1193 for i in range(size):
1194 new_board[i]=board[i][:]
1195 #print('new board', new_board)
1196 for x in range(size):
1197 for y in range(size):
1198 new_board[x][y] = new_board[x][y][:-1]
1199 #print('now is', new_board,board)
1200 return new_board
1201
1202
1203##-------------------------- + Normalizing Boards + ---------------------------
1204
1205def find_normal_orientation(board_state):
1206 board_size = len(board_state)
1207
1208 # 1. Make a list of all the possible orientations.
1209 board_list = ['']*8
1210 for i in range(8):
1211 # First flip on the letter axis first to obtain all the odd boards
1212 if i % 2 ==1:
1213 board_list[i] = rotate_board_n( flipped_board_let(board_state) , -(i//2))
1214 else:
1215 board_list[i] = rotate_board_n( board_state , -(i//2))
1216 #print("\n", "-"*40, '\n i=', i)
1217 #[print(row) for row in board_list[i]]
1218
1219 # 2. Remove any duplicate boards, if there are any
1220 candidate_list = [0,1,2,3,4,5,6,7]
1221 if anyduplicates(board_list):
1222 for board_num in range(8-1, 0-1, -1):
1223 if board_list[:board_num].count(board_list[board_num])>0:
1224 #print('Found a duplicate', board_num)
1225 candidate_list.pop(board_num)
1226 #print('The candidate list is now', candidate_list)
1227
1228 # 3. Keep only the boards that are the heaviest.
1229 coord_list = build_diagonal_coord_list(board_size)
1230 for coord in coord_list:
1231 #print('checking', coord_to_tile(coord))
1232 maxseen=0
1233 candidates_to_keep = []
1234
1235 # 4. Go through each tile (following the diagonal path described above), and remove any boards which do not have as many stones as the other boards (Not the actual tile count, but value at that tile.)
1236 for i in range(len( candidate_list)-1,0-1, -1 ):
1237 candidate = candidate_list[i]
1238 c_value = coord_value(coord, board_list[candidate])
1239 #print("\tBoard", candidate, "scored", c_value, end='\t')
1240
1241 # 5. If bigger value is found, drop all previous candidates
1242 if c_value > maxseen:
1243 maxseen = c_value
1244 candidates_to_keep = [candidate]
1245 #print('\tnew max of', maxseen, end='\t')
1246 # 6. If an equally large value is found, keep it.
1247 elif c_value == maxseen:
1248 candidates_to_keep.append(candidate)
1249 #print('\tadded', candidate, end='\t')
1250 #print('\t',candidates_to_keep,'remain')
1251
1252 # 7. When checking values at the next tile, only keep candidates which have had maximal values so far.
1253 candidate_list = candidates_to_keep[:]
1254 #print("\tAfter finishing tile", coord_to_tile(coord), "the candidate list is ", candidate_list)
1255
1256 # 8. Once there is only one candidate left, that is the corrent orientation for the board.
1257 if len(candidate_list)==1:
1258 #print('about to return', candidate_list[0])
1259 return candidate_list[0]
1260
1261 # 9. For any normal board, that should have yielded a unique board orientation. However, it is possible that that two orientations have the same value at every tile, but are not the same, since the stack ordering could be different. For example, the 2x2 board"
1262 # [-, ba]
1263 # [ab, -]
1264 # has 4 unique orientations, but two of them will satisfy the normalization so far.
1265
1266 # In that case, to deal with the composition of the stacks, simply slice off the bottom layer and find the orientation for that. This procedure will always return a unique orientation.
1267 retval = find_normal_orientation(remove_bottom_layer( board_state ))
1268 #print(retval, '='*20)
1269 return retval
1270
1271
1272
1273def normalize_board(board_state):
1274 orientation = find_normal_orientation(board_state)
1275 normal_board = board_state[:]
1276 if orientation % 2 ==1:
1277 normal_board = rotate_board_n( flipped_board_let(board_state) , -(orientation//2))
1278 else:
1279 normal_board = rotate_board_n( board_state , -(orientation//2))
1280 return normal_board
1281
1282
1283## And Finally
1284##-------------------------- + Normalizing Moves + ----------------------------
1285
1286def normalize_moves(move_string, plies, board_size):
1287 board_state = build_board_from_moves(move_string, plies, board_size)
1288 orientation = find_normal_orientation(board_state)
1289
1290 if orientation % 2 ==1:
1291 normal_move_string = rotate_move_string( flipped_move_string_let(move_string, board_size), board_size , -(orientation//2))
1292 else:
1293 normal_move_string = rotate_move_string( move_string, board_size, -(orientation//2))
1294
1295 return normal_move_string
1296
1297
1298
1299
1300
1301
1302#make_database_from_reddit_ELO_file()
1303
1304# Code for making server notation readable and comparable with PTN
1305
1306# c = "<server code>"
1307# c = c.split(',')
1308# for i in range(0,len(c),2):
1309# print(floor(i/2)+1,c[i],"\t",c[i+1])
1310#
1311#
1312
1313
1314
1315#SELECT `nboard003`
1316 #FROM `analyze_5_3_1400_9000_True_True_2016-04-23_2099-01-01_10_False`
1317 #GROUP BY `nboard003`
1318 #ORDER BY COUNT(*) DESC
1319 #LIMIT 10;
1320# SELECT SUM(score) FROM `analyze_5_3_1400_9000_True_True_2016-04-23_2099-01-01_10_False` /*WHERE nboard003="[('a', '', '', '', ''), ('', '', '', '', ''), ('', '', 'a', '', ''), ('', '', '', '', ''), ('b', '', '', '', '')]"*/
1321
1322
1323
1324
1325#/*SELECT * FROM games WHERE notation LIKE 'P A5,P E5%' OR notation LIKE 'P E5,P A5%' OR notation LIKE 'P E1,P A1%' OR notation LIKE 'P A1,P E1%'; */ /* < 4927 >This counts the SAME corner HORIZONTAL*/
1326#/*SELECT * FROM games WHERE notation LIKE 'P A5,P A1%' OR notation LIKE 'P A1,P A5%' OR notation LIKE 'P E1,P E5%' OR notation LIKE 'P E5,P E1%'; */ /* < 3909 > This counts the SAME corner VERTICAL*/
1327
1328# SELECT * FROM games WHERE notation LIKE 'P A1,P E5%' OR notation LIKE 'P E5,P A1%' OR notation LIKE 'P E1,P A5%' OR notation LIKE 'P A5,P E1%'; /* < 7063 >This counts the OPPOSITE corners */
1329
1330
1331#/*SELECT MAX(date) FROM games;*/
1332#/*SELECT MIN(date) FROM games;*/
1333#SELECT * from games where date = 1457864605596
1334
1335#Select * from games Where player_white NOT IN ('AlphaTakBot_5x5', 'BeginnerBot', 'IntuitionBot', 'ShlktBot', 'TakkerBot', 'TakkerusBot', 'TakticBot', 'TakticianBot', 'TakticianBotDev', 'alphabot', 'alphatak_bot', 'alphatak_bot1', 'antakonistbot', 'cutak_bot', 'cutak_bot', 'takkybot') and player_black NOT IN ('AlphaTakBot_5x5', 'BeginnerBot', 'IntuitionBot', 'ShlktBot', 'TakkerBot', 'TakkerusBot', 'TakticBot', 'TakticianBot', 'TakticianBotDev', 'alphabot', 'alphatak_bot', 'alphatak_bot1', 'antakonistbot', 'cutak_bot', 'cutak_bot', 'takkybot') and whiteELO>1600 and blackELO>1600 and size=6 and result like '%-0'
1336
1337
1338##=============================================================================
1339##=============================================================================
1340##=============== ------------- Driver functions ------------- ================
1341##=============================================================================
1342##=============================================================================
1343
1344def calculate_FPA(database, table, sizelist = [5,6,7,8] , minELO=1600, maxELO=9000, excludebots=True):
1345 conn = sqlite3.connect(database)
1346 c = conn.cursor()
1347
1348
1349 if type(sizelist) is int:
1350 sizelist = '(' + str(sizelist) + ')'
1351 elif len(sizelist) == 1:
1352 sizelist = '(' + str(sizelist[0]) + ')'
1353 else:
1354 sizelist= str( tuple(sizelist) ) # Just making the [] in to ()
1355
1356
1357 bottuple = str(tuple(botlist))
1358 botstring = 'player_white NOT IN' + bottuple + ' and player_black NOT IN ' + bottuple
1359
1360
1361
1362 query = 'SELECT * from ' + table + ' WHERE whiteELO > ' + str(minELO) + ' and blackELO > ' + str(minELO) + ' and blackELO < ' + str(maxELO) + ' and blackELO < ' + str(maxELO) + ' and size IN ' + sizelist + (' and ' + botstring)*excludebots
1363
1364 print(query + 'and result LIKE "%-0"')
1365 c.execute(query + 'and result LIKE "%-0"')
1366 whitewins = len(c.fetchall())
1367 print('Counted', whitewins, 'white wins')
1368
1369 c.execute(query + 'and result LIKE "0-%"')
1370 blackwins = len(c.fetchall())
1371 print('Counted', blackwins, 'black wins')
1372
1373 conn.commit()
1374 c.close()
1375 conn.close()
1376 print("done")
1377
1378 #return FPA
1379
1380##=============================================================================
1381##=============================================================================
1382##=========== ---------- Scripts that run the program ---------- ==============
1383##=============================================================================
1384##=============================================================================
1385
1386##----------------------- + Getting Everything Ready + ------------------------
1387
1388
1389
1390def setup():
1391 makegamesdbbackup()
1392 makeopeningsdb()
1393 add_ELOs_all_dates()
1394 add_ply_counts(mydb, 'games')
1395 delete_bad_games(mydb, 'games')
1396 add_ELO_and_games_played_to_games(mydb, 'games', True) # This also adds whether or not each player is a bot
1397 add_scores_to_table(mydb, 'games', True)
1398 #cornerizegames() # This would just make the openings easier to sift through by hand.
1399 revertgamesdb()
1400 print(('='*60+'\n')*5, ' - - step up complete - - '*4)
1401
1402
1403def main():
1404 print("\n\n Welcome to SultanPepper's opening analyizer. This script will take the PlayTak.com games database and analyze which openings have been the most sucessful.\n")
1405 if not os.path.isfile('openingsdb.db'):
1406 print("Looks like this is the first time this script has been run in this location. It'll take a minute or two to set everything up.")
1407 setup()
1408 shouldresetup = False
1409 else:
1410 shouldresetup = get_binary_choice("Would you like to recompute ELOs and update the list of games? (Do this if you've just downloaded the new games_anon database from PlayTak.com)")
1411
1412 if shouldresetup:
1413 setup()
1414
1415 settingslist = ask_settings() # returns [size, plycount, minELO, maxELO, bothmeetELO, includebots, startdate, enddate]
1416
1417 # There is definitely a faster way to do these variable assignments.
1418 size = settingslist[0]
1419 plycount = settingslist[1]
1420 minELO = settingslist[2]
1421 maxELO = settingslist[3]
1422 bothmeetELO = settingslist[4]
1423 includebots = settingslist[5]
1424 startdate = settingslist[6]
1425 enddate = settingslist[7]
1426
1427 create_table_from_settings(settingslist, mydb)
1428 tablename = make_table_name(settingslist)
1429 add_norm_moves_and_board(mydb, tablename, plycount, True)
1430 aggregate_opening_boards(mydb, tablename, settingslist)
1431
1432
1433
1434
1435
1436
1437# game 48648
1438#m0 = "P A5"
1439#m1 = "P A1,P A2,P B2,P B1,M A1 A2 1,M B2 B1 1"
1440#m2 = "P E1,P E5,P D5,P C4,P C5,P B5,P D4,P D3,P E4,P C3,P B4 C,P A5,P A4,M B5 C5 1,M D5 C5 1,M C4 D4 1,P D5,M A5 A4 1,P A5,P B5 C,M C5 C3 1 2,P C5,M D5 D4 1,M D3 D4 1,M E4 D4 1,P D3 W,M D4 E4 4,M D3 D4 1,M E4 E2 2 2,P D3,P D5,P D2,P E4,M D2 E2 1,M C3 D3 3,M D4 D3 2,M A5 A4 1,M D3 D5 1 4,M E3 D3 2,M E2 E4 1 2,M B4 C4 1,M D4 D3 1,M E5 E4 1,M E3 E4 1,M C4 E4 1 1,M D5 D4 3,P E5,P E3 W,M E4 E5 5,M D3 B3 1 2,M E5 C5 1 4,P D2,M C5 C3 3 2,P B4,P A3,P B2,M C3 B3 3,P C5,M B3 D3 1 4,P B3,M A3 B3 1,P E2,P A3,M B2 B3 1,M A3 B3 1,M B4 A4 1,M D5 C5 2,M B5 C5 1,M C4 A4 1 2,M B4 A4 1,P B4,M A4 A1 2 1 2,M B3 A3 2,M A4 A3 2,M B3 A3 2,M A2 A3 1,P B3,M C5 C2 1 1 2,M D3 D2 5,P C1,M D5 C5 1,P B1,P D1 W,P B5,M C5 C4 1,P A2"
1441
1442#b0 = build_board_from_moves(m0, -1,7)
1443#b1 = build_board_from_moves(m1, -1,2)
1444#b2 = build_board_from_moves(m2, -1,5)
1445#n0 = find_normal_orientation(b0)