Re: [Bug-gnubg] A crude proposal for a database schema

[Joern Thyssen]

On Wed, Sep 03, 2003 at 08:04:08PM +0200, Jim Segrave wrote
> On Wed 03 Sep 2003 (17:48 +0000), Joern Thyssen wrote:
> > On Tue, Sep 02, 2003 at 08:18:24PM +0200, Jim Segrave wrote
> 
> [A lot that needs reviewing]
> 
> > > Table: Moves ID GameID PlayerID on roll Dice int(2) move int(8),
> > >     movetype enum (double, pass, drop, take, beaver, raccoon, normal,
> > >     set position), boardID, isbest (boolean), analysis stats
> > >     bestmovetype enum (double, pass...), bestmove int(8), analysis
> > >     stats
> > 
> > I'd say that we "drop" the Moves table for now, since I guess most
> > people would want to query game data rather than move data. Also, the
> > Moves table is the most complicated one. We'll probably learn a lot
> > implementing the other tables, so I think we should leave out the most
> > complex one for now.
> 
> As long as one keeps in mind that people are going to want it - in
> particular to find mistakes. 

Yes, I know. But let's start with the other tables first and return to
this one later on once we've gathered some experience and corrected
flaws (if any) in the schema.

> 
> > > gnubg could write the records out easily (we'd need some method of
> > > filling in data like player name to nickname mappings and environments
> > > I want to change the GAME_INFO record to have a datestamp (I get
> > > annoyed if I play two matches against gnubg and analyse them that
> > > gnubg wants to overwrite the first matche's .sgf file).
> > 
> > Using IDs (env, player, match/session) requires us to have direct access
> > to the RDBMS so we can query the database for next "free" ID unless we
> > find some other way to generate unique IDs.
> 
> MySql has auto-generated IDs - very convenient. I don't know about
> other RDBs though. I'll ask the local experts tomorrow about
> postgres. I believe MSql does not.

IBM DB2 has them as well, but probably uses another syntax.

Still, when you add statistics for existing players you would need
access to the DBMS to get the IDs for environment and the players.

Jørn

pgpLBSL7wMDnC.pgp
Description: PGP signature