Re: convert text file to database

e-letter wrote:
> Readers,
>
> I play chess and occasionally (i.e. when I win!) I save the moves to a
> text file, of the following format:
>
> [Event "Online Game"]
> [Site "www.flyordie.com"]
> [Date "yyyy.mm.dd"]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "time forfeit"]
> [UTCDate "yyyy.mm.dd"]
> [TimeControl "1200"]
>
> 1. c4 e5 2. d4 Nc6...8. Bg5
> 1-0
>
> and:
>
> [Event "Online Game"]
> [Site "www.flyordie.com"]
> [Date "yyyy.mm.dd"]
> [Round "1"]
> [White "player1"]
> [Black "player2"]
> [Result "1-0"]
> [Termination "normal"]
> [UTCDate "yyyy.mm.dd"]
> [TimeControl "1200"]
>
> 1. c4 e5 2. d4 f6 3. dxe5...25. Qd8# 1-0
>
> There are various other termination options such as 'abandoned',
> 'draw', etc. My text file, now numbering about 4000 lines consists of
> each game saved in this format, each game separated by a carriage
> return.
>
> Presumeably I would have to edit the text file to create fields
> separated via commas?

At 4000 lines I would probably come up with a small program (or perl
script etc) that scans the file and inserts each piece of data into the
db where it belongs. But you can manually turn this into a csv file to
do the import. (a few find and replace's will make light work of it)

> I guess I could create a database 'chess games' with a table for each
> field: event; site; date; termination; etc., correct?

A database called chessmatches one table called chessgames with many
columns. One column being one piece of information about the game.

Something like -

CREATE TABLE chessgames (
id serial PRIMARY KEY,
event text,
site text,
gamedate date,
round integer,
whitename text,
blackname text,
result text,
termination text,
utcdate date,
timecontrol time
);

From the above game data each line would go into the corresponding
column as one row of data - being one game recorded.

gamedate could be a timestamp to also record the time of day the game
was played. utcdate and timecontrol may be able to be joined in a
similar way.

Most dba's would make termination an integer column linking to a table
of termination choices. This could also be applied to event, site and
player names.

> What about the moves? Should I create a table column called 'moves',
> in which case how to ensure data is placed in this database column? I
> would also like to perform searches such as: "find me all games played
> during the month of January"; or "show me the game with the fewest
> number of moves"; or "how many games have I won via time forfeit?".

This will depend mostly on what you want to do with this data. One
option is to add another text column called moves to the above table.
From that you can view the list of moves when you wish.

Another option could be to have a second table to store the list of
moves. This example will probably be overkill as I doubt you will be
doing any kind of analysis where you want to get stats on how many games
you won where the third last move was to e4.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz