pgn-to-sqlite's Introduction

👋 Hi friend!

I'm Ricky and I came into tech from a non-traditional background. I'm an enthusiastic problem solver with passion for creating and building, from software and websites to books and bonsai. I consider myself a lifelong learner and try to learn something new each day.

I'm currently working at The Migus Group where I wear multiple hats as the Director of Technology and Operations.

A few other things about me:

📝 I'm a writer, who has authored both fiction and non-fiction books.

👨‍⚕️ I used to be a registered nurse.

🥋 I'm also a martial artist, holding the rank of Nidan (second-degree black belt) in Shaolin Kempo.

🌲 I collect and maintain a modest Bonsai collection

💬 Favorite quote:

"Once you stop learning, you start dying" — Albert Einstein

If you want to say hi, you can get in touch over here.

pgn-to-sqlite's People

Contributors

Stargazers

Watchers

pgn-to-sqlite's Issues

save_game_to_db escaping needed for certain characters

Great utility btw. I am just trying this on chess.com today and Let's play is a valid event name on there which is causing a syntax error on insert via save_game_to_db.

Example PGN

[Event "Let\\'s Play!"]
[Site "Chess.com"]
[Date "2023.02.10"]
[Round "?"]
[White "DonAlanBrito"]
[Black "ross-spencer"]
[Result "0-1"]
[ECO "C23"]
[WhiteElo "821"]
[BlackElo "971"]
[TimeControl "1/86400"]
[EndDate "2023.02.11"]
[Termination "ross-spencer won by checkmate"]

1. e4 e5 2. Bc4 Nc6 3. Qf3 f6 4. Nc3 Be7 5. Qh5+ g6 6. Qf3 Bc5 7. Qd3 d6 8. Qd5
Qd7 9. Nf3 Nge7 10. Qf7+ Kd8 11. Qg7 Qe8 12. Qf7 Rf8 13. Qxe8+ Rxe8 14. O-O f5
15. d3 Rf8 16. Ng5 f4 17. Nxh7 Rh8 18. Nf6 Rf8 19. Ncd5 g5 20. g3 f3 21. Bxg5
Bh3 22. Rfd1 Bxf2+ 23. Kxf2 Bg2 24. Nh7 Rh8 25. Ndf6 a6 26. c3 b5 27. Be6 Rb8
28. d4 b4 29. dxe5 Rb5 30. Bf4 bxc3 31. bxc3 Rb2+ 32. Ke1 f2# 0-1

Example Insert

INSERT INTO
        games (
            event,
            site,
            date,
            round,
            white,
            black,
            result,
            eco,
            white_elo,
            black_elo,
            variant,
            time_control,
            termination,
            moves)
        VALUES
        (
            'Let's Play!',
            'Chess.com',
            '2023.02.10',
            '-',
            'DonAlanBrito',
            'ross-spencer',
            '0-1',
            'C23',
            '821',
            '971',
            '',
            '1/86400',
            'ross-spencer won by checkmate',
            '1. e4 {[%clk 23:58:32]} 1... e5 {[%clk 23:57:54]} 2. Bc4 {[%clk 23:59:20]} 2... Nc6 {[%clk 23:59:37]} 3. Qf3 {[%clk 23:59:34]} 3... f6 {[%clk 23:58:58]} 4. Nc3 {[%clk 23:59:26]} 4... Be7 {[%clk 23:58:32]} 5. Qh5+ {[%clk 23:58:53]} 5... g6 {[%clk 23:58:16]} 6. Qf3 {[%clk 23:58:19]} 6... Bc5 {[%clk 23:59:41]} 7. Qd3 {[%clk 23:59:37]} 7... d6 {[%clk 23:59:19]} 8. Qd5 {[%clk 23:58:47]} 8... Qd7 {[%clk 23:59:18]} 9. Nf3 {[%clk 23:59:20]} 9... Nge7 {[%clk 23:59:42]} 10. Qf7+ {[%clk 23:58:30]} 10... Kd8 {[%clk 23:59:30]} 11. Qg7 {[%clk 23:59:45]} 11... Qe8 {[%clk 23:59:49]} 12. Qf7 {[%clk 23:58:24]} 12... Rf8 {[%clk 23:59:34]} 13. Qxe8+ {[%clk 23:59:27]} 13... Rxe8 {[%clk 23:59:49]} 14. O-O {[%clk 23:59:42]} 14... f5 {[%clk 23:59:22]} 15. d3 {[%clk 23:50:11]} 15... Rf8 {[%clk 23:57:58]} 16. Ng5 {[%clk 23:27:50]} 16... f4 {[%clk 23:59:26]} 17. Nxh7 {[%clk 23:59:34]} 17... Rh8 {[%clk 23:59:19]} 18. Nf6 {[%clk 23:59:27]} 18... Rf8 {[%clk 23:59:12]} 19. Ncd5 {[%clk 23:59:33]} 19... g5 {[%clk 23:55:27]} 20. g3 {[%clk 23:57:59]} 20... f3 {[%clk 21:59:12]} 21. Bxg5 {[%clk 23:30:00]} 21... Bh3 {[%clk 23:59:11]} 22. Rfd1 {[%clk 23:58:24]} 22... Bxf2+ {[%clk 23:59:32]} 23. Kxf2 {[%clk 23:58:52]} 23... Bg2 {[%clk 23:52:34]} 24. Nh7 {[%clk 23:11:16]} 24... Rh8 {[%clk 22:48:33]} 25. Ndf6 {[%clk 23:55:56]} 25... a6 {[%clk 23:42:23]} 26. c3 {[%clk 23:57:59]} 26... b5 {[%clk 23:41:48]} 27. Be6 {[%clk 23:53:23]} 27... Rb8 {[%clk 23:57:01]} 28. d4 {[%clk 23:31:48]} 28... b4 {[%clk 22:47:44]} 29. dxe5 {[%clk 23:26:50]} 29... Rb5 {[%clk 23:27:17]} 30. Bf4 {[%clk 23:57:54]} 30... bxc3 {[%clk 23:32:28]} 31. bxc3 {[%clk 23:45:14]} 31... Rb2+ {[%clk 22:34:03]} 32. Ke1 {[%clk 23:46:16]} 32... f2# {[%clk 11:05:19]} 0-1'
        );

It has been a while since I've worked with SQLite and Python to know the best approach to fix, but this may be one suggestion: https://stackoverflow.com/a/3220028

I can probably take a look at this some time in the near future if you haven't time. I want to do a bit more work with the db output here.

[Feature Request] Create moves table

Currently, each game is stored as a record in the games table. Each record contains a field for each key in the PGN. All moves are stored in moves field. This schema is great for searching games and viewing all moves of a game.

This schema does not enable more advanced analytics of a game such as: position search, centipawn loss evaluation or other analysis based on piece movement. To enable deeper analysis of moves, a table containing all moves is required.

This request is to create a table containing moves for all games. Future feature requests will be created for specific data elements for move analysis.

Request:

Create a new table called moves.
Add each user's move (i.e. ply) to the moves table. Suggested fields include:
GameID (Key for the game in the games table) *
Ply (Ply number)
Move (Move number)
Player (Player name)
Color (Colour)
Piece (Piece moved by user)
Notation (Move notation)
Clock (Time left on the clock h:mm:ss)
Move_Time (Time used for the move in seconds)
FEN (FEN of the position after the user's move) **

Note *: I do not believe the games table includes a key for GameID. This will need to exist in the games table as well.
Note **: FEN be the basis for future feature requests that involve engine evaluations.

Recommend Projects

endlesstrax / pgn-to-sqlite Goto Github PK

pgn-to-sqlite's Introduction

👋 Hi friend!

A few other things about me:

pgn-to-sqlite's People

Contributors

Stargazers

Watchers

pgn-to-sqlite's Issues

save_game_to_db escaping needed for certain characters

[Feature Request] Create moves table

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent