EGD Database Schema and Sample Data

From US Go Wiki
(Redirected from EGD Database Schema)
Jump to: navigation, search

Contents

Observations

See General Notes

authteam

DROP TABLE IF EXISTS `authteam`;
CREATE TABLE `authteam` (
  `id` int(4) NOT NULL auto_increment,
  `teamname` varchar(25) collate latin1_bin NOT NULL default '',
  `teamlead` varchar(25) collate latin1_bin NOT NULL default '',
  `status` varchar(10) collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `teamname` (`teamname`,`teamlead`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

authuser

DROP TABLE IF EXISTS `authuser`;
CREATE TABLE `authuser` (
  `id` int(11) NOT NULL auto_increment,
  `uname` varchar(25) collate latin1_bin NOT NULL default '',
  `passwd` varchar(32) collate latin1_bin NOT NULL default '',
  `team` varchar(25) collate latin1_bin NOT NULL default '',
  `level` int(4) NOT NULL default '0',
  `status` varchar(10) collate latin1_bin NOT NULL default '',
  `lastlogin` datetime default NULL,
  `logincount` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

biograph

DROP TABLE IF EXISTS `biography`;
CREATE TABLE `biography` (
  `Pin_Player` int(8) NOT NULL default '0',
  `Bio_Type` char(1) NOT NULL default '' COMMENT 'H=Html; T=txt',
  `Biography` mediumtext,
  `photo` mediumblob,
  `Elab_Date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Pin_Player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

biography_old

DROP TABLE IF EXISTS `biography_old`;
CREATE TABLE `biography_old` (
  `Pin_Player` int(8) NOT NULL default '0',
  `Biography` mediumtext collate latin1_bin,
  `photo` mediumblob,
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Pin_Player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

comments_authors

DROP TABLE IF EXISTS `comments_authors`;
CREATE TABLE `comments_authors` (
  `Id` varchar(12) NOT NULL default '',
  `Last_Name` varchar(50) NOT NULL default '',
  `First_Name` varchar(50) NOT NULL default '',
  `Country` char(3) NOT NULL default '',
  `PIN` int(8) NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
('DINER','Dinerstein','Alexandr','RU',10313237)

country

DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
  `Country_Code` char(2) collate latin1_bin NOT NULL default '',
  `Country_Descr` varchar(30) collate latin1_bin NOT NULL default '',
  `Country_short_flag` varchar(4) collate latin1_bin NOT NULL default '',
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Country_Code`),
  KEY `Ind_Country` (`Country_Descr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Elenco dei Paesi';
('UK','United Kingdom','unkg','2005-01-01')

games

DROP TABLE IF EXISTS `games`;
CREATE TABLE `games` (
  `Tournament_Code` varchar(8) collate latin1_bin NOT NULL default '0',
  `Game_Date` date NOT NULL default '0000-00-00',
  `Round` tinyint(2) unsigned NOT NULL default '0',
  `Pin_Player_1` int(8) unsigned NOT NULL default '0',
  `Color_1` char(1) collate latin1_bin NOT NULL default '',
  `Pin_Player_2` int(8) unsigned NOT NULL default '0',
  `Color_2` char(1) collate latin1_bin NOT NULL default '',
  `Handicap` tinyint(2) unsigned NOT NULL default '0',
  `Reconstructed_hp` smallint(1) NOT NULL default '0',
  `Result` char(1) collate latin1_bin NOT NULL default '',
  `Sgf_Code` varchar(26) collate latin1_bin default '0',
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Tournament_Code`,`Game_Date`,`Round`,`Pin_Player_1`,`Pin_Player_2`),
  UNIQUE KEY `Ind_Games` (`Tournament_Code`,`Game_Date`,`Round`,`Pin_Player_1`,`Pin_Player_2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Elenco delle partite giocate';
('T080928A','2008-09-28',1,14686716,'b',10298046,'w',0,0,'b','0','2008-10-06')

map

DROP TABLE IF EXISTS `map`;
CREATE TABLE `map` (
  `Map_code` char(2) collate latin1_bin NOT NULL default '0',
  `Map` mediumblob,
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Map_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
('UK',0x89504E470D0A1A0A0....26082,'2005-07-01')

page_links

DROP TABLE IF EXISTS `page_links`;
CREATE TABLE `page_links` (
  `page_type` varchar(10) NOT NULL default '',
  `page_key` varchar(10) NOT NULL default '',
  `pos` int(11) NOT NULL default '0',
  `link` varchar(100) NOT NULL default '',
  `comments` text NOT NULL,
  `image` blob NOT NULL,
  PRIMARY KEY  (`page_type`,`page_key`,`pos`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
('FRONT_PAGE','',2,'http://www.go4go.net/v2/','Pro Games on Go4Go','')
('PLAYER','10374386',1,'http://www.go4go.net/v2/modules/collection/byplayer2.php?pid=260','More games of Alexej Lazarev are available on Go4Go','')
('TOURNAMENT','T051105A',1,'http://lnx.agi.go.it/GoClubRoma/Roma_front.html','The Rome go-club','')

placements

DROP TABLE IF EXISTS `placements`;
CREATE TABLE `placements` (
  `Pin_Player` int(8) unsigned NOT NULL default '0',
  `Tournament_Code` varchar(8) collate latin1_bin NOT NULL default '0',
  `Country_Code` char(2) collate latin1_bin NOT NULL default '',
  `Club` char(3) collate latin1_bin NOT NULL default '',
  `Category_Ind` int(2) NOT NULL default '0',
  `Placement` int(5) unsigned NOT NULL default '0',
  `Grade_Declared` char(3) collate latin1_bin NOT NULL default '0',
  `Win_Games` tinyint(2) unsigned NOT NULL default '0',
  `Lost_Games` tinyint(2) unsigned NOT NULL default '0',
  `Jigo_Games` tinyint(2) unsigned NOT NULL default '0',
  `Precedent_Gor` int(5) unsigned default '0',
  `Following_Gor` int(5) unsigned default '0',
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Pin_Player`,`Tournament_Code`),
  KEY `Ind_Placement` (`Pin_Player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Qui sono registrati i risultati conseguiti nei tornei';
(10298046,'T080928A','UK','Wan',0,4,'3d',1,2,0,2186,2193,'2008-10-06')
(14686716,'T080928A','UK','Rea',0,1,'5d',3,0,0,2484,2486,'2008-10-06')

players

DROP TABLE IF EXISTS `players`;
CREATE TABLE `players` (
  `Pin_Player` int(8) unsigned NOT NULL default '0',
  `Last_Name` varchar(30) collate latin1_bin NOT NULL default '',
  `Name` varchar(30) collate latin1_bin NOT NULL default '',
  `Country_Code` char(2) collate latin1_bin NOT NULL default '',
  `Club` char(3) collate latin1_bin default NULL,
  `Grade` char(3) collate latin1_bin NOT NULL default '',
  `Grade_n` int(2) unsigned NOT NULL default '0',
  `EGF_Placement` mediumint(5) unsigned default NULL,
  `Gor` int(5) default NULL,
  `DGor` int(5) default NULL,
  `Proposed_Grade` char(3) collate latin1_bin NOT NULL default '',
  `Tot_Tournaments` int(4) unsigned default NULL,
  `Last_Appearance` varchar(8) collate latin1_bin NOT NULL default '0',
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Pin_Player`,`Last_Name`,`Name`),
  KEY `Ind_Players` (`Country_Code`,`Club`,`Last_Name`,`Name`),
  KEY `Reverse_Ind` (`Last_Name`,`Name`,`Pin_Player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Scheda dei dati personali dei giocatori';
(10298046,'Wall','Alistair','UK','Wan','3D',32,619,2193,0,'2D',145,'T080928A','2008-10-06')
(14686716,'Kim','Jaeup','UK','Rea','5D',34,84,2486,0,'--',11,'T080928A','2008-10-06')

sgf

DROP TABLE IF EXISTS `sgf`;
CREATE TABLE `sgf` (
  `Comments_Author` varchar(20) collate latin1_bin default NULL,
  `Sgf_Code` varchar(26) collate latin1_bin NOT NULL default '0',
  `Sgf_File` blob NOT NULL,
  `Elab_Date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Sgf_Code`),
  UNIQUE KEY `Tournament_Code` (`Sgf_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='SGF delle partite ... quando disponibili';
('DINER','1031323710298596E0402064',0x283B0D0A...A5D29290D0A,'2008-01-14 19:30:39')

sys_req

DROP TABLE IF EXISTS `sys_reg`;
CREATE TABLE `sys_reg` (
  `Flag_Update` int(1) NOT NULL default '0' COMMENT 'Se 0 f脿 proseguire .. se 1 avvisa di upgrade in corso ',
  `Warning_Message` varchar(255) collate latin1_bin NOT NULL default '',
  `Update_date` varchar(20) collate latin1_bin NOT NULL default '0000-00-00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
(0,'EGD momentarily is off line for data updatings in progress ... to try later','December 6th 2007')

t_games

DROP TABLE IF EXISTS `t_games`;
CREATE TABLE `t_games` (
  `Tournament_Code` varchar(8) collate latin1_bin NOT NULL default '0',
  `Tournament_Descr` varchar(80) collate latin1_bin NOT NULL default '',
  `Tournament_Date` date NOT NULL default '0000-00-00',
  `City` varchar(30) collate latin1_bin NOT NULL default '',
  `Nation` char(2) collate latin1_bin NOT NULL default '0',
  `Game_Date` date NOT NULL default '0000-00-00',
  `Round` tinyint(2) unsigned NOT NULL default '0',
  `Pin_Player_1` int(8) unsigned NOT NULL default '0',
  `Color_1` char(1) collate latin1_bin NOT NULL default '',
  `Pin_Player_2` int(8) unsigned NOT NULL default '0',
  `Color_2` char(1) collate latin1_bin NOT NULL default '',
  `Handicap` tinyint(2) unsigned NOT NULL default '0',
  `Sgf_Code` varchar(26) collate latin1_bin default '0',
  `Result` int(1) NOT NULL default '0',
  `Country_Code_1` char(2) collate latin1_bin NOT NULL default '',
  `Club_1` char(3) collate latin1_bin NOT NULL default '',
  `Grade_Declared_1` char(3) collate latin1_bin NOT NULL default '0',
  `Gor_1` int(5) unsigned default '0',
  `Last_Name_1` varchar(30) collate latin1_bin NOT NULL default '',
  `First_Name_1` varchar(30) collate latin1_bin NOT NULL default '',
  `Country_Code_2` char(2) collate latin1_bin NOT NULL default '',
  `Club_2` char(3) collate latin1_bin NOT NULL default '',
  `Grade_Declared_2` char(3) collate latin1_bin NOT NULL default '0',
  `Gor_2` int(5) unsigned default '0',
  `Last_Name_2` varchar(30) collate latin1_bin NOT NULL default '',
  `First_Name_2` varchar(30) collate latin1_bin NOT NULL default '',
  `Comments_Author` varchar(20) collate latin1_bin default NULL,
  KEY `T_01` (`Tournament_Date`),
  KEY `T_02` (`Tournament_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
('T080928A','Arundel (UK)','2008-09-28','Arundel','UK','2008-09-28',1,14686716,'b',10298046,'w',0,'0',1,'UK','Rea','5d',2486,'Kim','Jaeup','UK','Wan','3d',2193,'Wall','Alistair',NULL)

tournaments

DROP TABLE IF EXISTS `tournaments`;
CREATE TABLE `tournaments` (
  `Tournament_Code` varchar(8) collate latin1_bin NOT NULL default '0',
  `H_Code` tinyint(1) NOT NULL default '0',
  `Wallist_Date` date NOT NULL default '0000-00-00',
  `Reliability` tinyint(2) default '0',
  `Tournament_Descr` varchar(80) collate latin1_bin NOT NULL default '',
  `Categories_Descr` text collate latin1_bin,
  `Tournament_Date` date NOT NULL default '0000-00-00',
  `City` varchar(30) collate latin1_bin NOT NULL default '',
  `Nation` char(2) collate latin1_bin NOT NULL default '0',
  `Tournament_Class` char(1) collate latin1_bin NOT NULL default '0',
  `Category_Type` char(1) collate latin1_bin default NULL,
  `Rounds` smallint(2) NOT NULL default '0',
  `Total_Players` smallint(3) unsigned default '0',
  `Wallist` text collate latin1_bin,
  `Reconstructed_hp` tinyint(1) unsigned default '0',
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Tournament_Code`),
  UNIQUE KEY `Tournament_Code` (`Tournament_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Elenco dei tornei omologati EGF dal 1996';
('T080928A',1,'2008-10-02',0,'Arundel (UK)','','2008-09-28','Arundel','UK','B','N',3,20,...,0,'2008-10-06')

Other

-- MySQL dump 10.9
--
-- Host: localhost    Database: agagd
-- ------------------------------------------------------
-- Server version	4.1.9-max

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;
...
...
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

American_Go_Assocation_Go_Database

Personal tools