Chess match database manager I
In this series of articles, I will present an application to manage a chess match database, ChesPosDb. You can download it using this link. It is not a program that plays chess; it only stores matches, but it allows queries with a large number of options: searching for games in which a series of moves occur, either one after another or separated by several moves, and even look for partially defined positions, no matter in which place on the board the pieces are located.
Besides installing the program, merely unzipping the files in any directory, installing a database management system is necessary. The application can use Oracle, SQL Server, and MySQL, which you can download for free as long as they are not intended for commercial use. There are many tutorials on the Internet and on YouTube to do it, but here you have a link for Oracle, SQL Server, and MySQL.
Later I will explain how to create the database in each of these RDBMS and configure the application to connect to it for you to use the software. For now, I recommend not installing any database. Please wait until I have explained the requirements to select in the installation. Otherwise, the program would not work properly. In the first few articles, I will give a general review of all the functionalities so that you can see if the effort is worth it.
Loading the matches
Although you can hand-type the games, move by move, it is preferable to do so using files that already contain previously written matches and that you can find and download on many Internet sites devoted to the world of chess. The format accepted by the program is a widespread one, the PGN format, which consists of text files that can contain a multitude of annotated and commented matches. The moves are checked before loading them into the database, for there might be errors because some files do not correctly follow the standard. I have come across some bugs, so sometimes it will be necessary to correct the file before integrating it or discard the wrong match by deleting it from the pgn file. The application will provide a text with the error to easily find it using the notepad search option; you can find it in the pgnlog.txt file located in the same directory where the executable is.
The application also checks that all movements are valid, which can be helpful if we have typed the game by hand in a text file with this format to integrate it into the database.
Once the application is open, you can see the main window with the menu bar:
In the File menu, there are two options to import these files: Import File… and Import Multiple Files… which allows selecting more than one pgn file to integrate them at once.
The standard Windows dialog box for opening files will open. Before selecting the file, it is necessary to make sure that you have chosen the correct file format:
Then, if there are no errors, the import matches window will open:
The window has three panes: a list with the matches in the file, a board to watch the selected game, and a pane showing the game's movements to move quickly between positions. This option is useful if you want to select only some of the games.
The match listing shows some of the fields that are standard annotations in all games in PGN format. There is also a mark that indicates whether or not the match is selected for import to the database:
The games can be only partial and contain some key plays from the end of the match. This format does not require the introduction of complete games, so the number of moves and plys does not refer to the game totals but only to those contained in the file.
To mark the games you want to import, you have to select them first with the mouse in the list, clicking on each line. Holding down the shift key, you can choose more than one. Upon the list, there are three buttons: the second, third, and fourth from the left, with which you can mark this check box. The first button checks the lines selected with the mouse, the second one checks all the boxes without selecting the matches first, and the third one unchecks the selected lines.
Playing a match
To play a match, you have to select it first by clicking on the corresponding row of the list with the mouse, and then press the first button from the left, over the list, with the icon of a chessboard. The moves will appear in the pane below the list:
The red moves indicate a check, the green moves are those in which no piece is captured, and the grey moves are those with capture. With the two buttons on this panel, you can choose to view the moves in columns from top to bottom and from left to right (as in the image), or in rows from left to right and top to bottom. Clicking on any of these moves will reproduce the position on the board in the right pane:
In the toolbar above the board, you will find, from left to right, the following elements:
- Restart: return to the starting position of the game.
- Back: back to the previous move.
- Next: advance to the next move.
- Text of the move: text indicating the ply, with the same color code as in the moves pane.
- Rotate View: Flip the board vertically.
- Mark movement squares: activates or deactivates the marking of the move start and end squares. The initial square always has a green border, while the final square is coloured according to the movement's result: green for movement only, yellow for piece capture, and red for a check.
At the bottom of the board, a text box shows the comments, if any, to the move. The lower and upper rows of letters are coloured in white or black depending on the player's turn.
Integrating the matches in the database
Once the games to be integrated into the database have been marked, you can start the integration process in the database using the fifth button on the toolbar of the match list. Next to this button, there is a bar that fills up as the process progresses. The process can take a while for huge files (some can contain more than 1000 games). Before starting, you will be asked whether to integrate the duplicate games, that is, those already in the database. The application considers two games equal if their starting position and all their movements are the same, regardless of the comments and notes they may contain, including the players' names. With this option, you can avoid inserting twice a match, for instance, if you process several files of the same player.
At the end of the process, a message will be displayed indicating how many games have been integrated and how many ones are duplicates. The duplicated items will remain marked in the list. In contrast, the integrated ones will be unchecked so that you can quickly identify them and analyse if you want to integrate them anyway in another later attempt.
Finally, you will be given the option to delete the processed files. This option can be useful when integrating a large number of files in small batches or one by one to clean the directory safely. It is convenient to use a folder only with the files to integrate, keeping a copy of them in a different folder to recover those that you accidentally delete quickly.
To not make the articles too long, I will continue in the next article in this series, explaining how you can build filters to search for matches in the database.
Thanks for reading. See you next time!