Re: convert text file to database - Mailing list pgsql-novice

Your method looks fine I think, but do without the numbers in the moves
field.

IE: c4,c5;b1c3,g7f6;...

Then perhaps write some functions to deal with them on searches maybe turn
them into:

[Step],[Move1],[Move2]
1,c4,c5
2,b1c3,g7f6
...

And at the same time insert them into 2 caching tables to speed up future
searches:

[Game],[Step],[Move1],[Move2]
0001,1,c4,c5
0001,2,b1c3,g7f6
...

And,

[Game]
0001
...


Nick


-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of e-letter
Sent: Sunday, April 13, 2008 2:59 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] convert text file to database

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?

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

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?".

Yours,

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice




--
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008
1:45 PM


No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008
1:45 PM


No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.13/1376 - Release Date: 4/13/2008
1:45 PM