# Complete DB Schema for CPANSTATS Database

DROP TABLE IF EXISTS cpanstats;
CREATE TABLE cpanstats (
     id         int(10) unsigned NOT NULL,
     state      varchar(32),
     postdate   varchar(8),
     tester     varchar(255),
     dist       varchar(255),
     version    varchar(255),
     platform   varchar(255),
     perl       varchar(255),
     osname     varchar(255),
     osvers     varchar(255),
     fulldate   varchar(32),
     PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `release_data`;
CREATE TABLE `release_data` (
  `dist`	varchar(255) NOT NULL,
  `version`	varchar(255) NOT NULL,
  `id`		int(10) unsigned NOT NULL,
  `oncpan`	tinyint(4) default '0',
  `distmat`	tinyint(4) default '0',
  `perlmat`	tinyint(4) default '0',
  `patched`	tinyint(4) default '0',
  `pass`	int(10) default '0',
  `fail`	int(10) default '0',
  `na`		int(10) default '0',
  `unknown`	int(10) default '0'
);

DROP TABLE IF EXISTS release_summary;
CREATE TABLE release_summary (
    dist        varchar(255) NOT NULL,
    version     varchar(255) NOT NULL,
    id          int(10) unsigned NOT NULL,
    oncpan      tinyint(4) default 0,
    distmat     tinyint(4) default 0,
    perlmat     tinyint(4) default 0,
    patched     tinyint(4) default 0,
    pass        int(10) default 0,
    fail        int(10) default 0,
    na          int(10) default 0,
    unknown     int(10) default 0
);

DROP TABLE IF EXISTS `uploads`;
CREATE TABLE `uploads` (
  `type`        varchar(10)     NOT NULL,
  `author`      varchar(32)     NOT NULL,
  `dist`        varchar(255)    NOT NULL,
  `version`     varchar(255)    NOT NULL,
  `filename`    varchar(255)    NOT NULL,
  `released`    int(16)		NOT NULL,
  PRIMARY KEY  (`author`,`dist`,`version`)
);

DROP TABLE IF EXISTS `ixlatest`;
CREATE TABLE `ixlatest` (
  `dist`        varchar(255)    NOT NULL,
  `version`     varchar(255)    NOT NULL,
  `released`    int(16)		NOT NULL,
  `author`      varchar(32)     NOT NULL,
  `oncpan`      tinyint(4)      DEFAULT 0,
  PRIMARY KEY  (`dist`)
);

DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
  `type`	varchar(8)	 NOT NULL,
  `name`	varchar(255)     NOT NULL,
  `lastid`	int(10) unsigned NOT NULL,
  `dataset`	blob,
  PRIMARY KEY  (`type`,`name`)
);

DROP TABLE IF EXISTS `page_requests`;
CREATE TABLE `page_requests` (
  `type`	varchar(8)	 NOT NULL,
  `name`	varchar(255)     NOT NULL,
  `weight`	int(2) unsigned  NOT NULL
);

DROP TABLE IF EXISTS osname;
CREATE TABLE osname (
     id         int(10) unsigned NOT NULL auto_increment,
     osname     varchar(255),
     ostitle    varchar(255),
     PRIMARY KEY (id)
);

DROP TABLE IF EXISTS leaderboard;
CREATE TABLE leaderboard (
    postdate    varchar(8)      NOT NULL,
    osname      varchar(255)    NOT NULL,
    tester      varchar(255)    NOT NULL,  
    score       int(10)         DEFAULT 0,
    PRIMARY KEY (postdate,osname,tester),
    KEY IXOS   (osname),
    KEY IXTEST (tester)
);

DROP TABLE IF EXISTS noreports;
CREATE TABLE noreports (
     dist       varchar(255),
     version    varchar(255),
     osname     varchar(255),
     KEY NRIX (dist,version,osname),
     KEY OSIX (osname)
);