postgresql draft script.sql

CREATE TABLE leagues(id serial primary key, name varchar(100), drafttype smallint, scoringtype smallint, roundvalues smallint, leaguetype char(5), salary integer, benchplayers smallint);
INSERT INTO leagues VALUES(1, 'Demo - Roto (Auction)', 1, 1, 1, 'demo', 260, 0);
INSERT INTO leagues VALUES(2, 'Demo - Roto (Draft)', 3, 1, 1, 'demo', 260, 0);
INSERT INTO leagues VALUES(3, 'Demo - Points (Auction)', 1, 2, 1, 'demo', 260, 0);
INSERT INTO leagues VALUES(4, 'Demo - Points (Draft)', 3, 2, 1, 'demo', 260, 0);
INSERT INTO leagues VALUES(5, 'Test', 3, 3, 3, 'test', 1000, 0);
CREATE TABLE owners(ownerid integer primary key, id integer, ownername varchar(100),foreign key(id) references leagues(id));
INSERT INTO owners VALUES(70,5,'abc');
INSERT INTO owners VALUES(71,5,'def');
INSERT INTO owners VALUES(72,5,'ghi');
INSERT INTO owners VALUES(73,5,'jkl');
INSERT INTO owners VALUES(74,5,'mno');
INSERT INTO owners VALUES(75,1,'Team 1');
INSERT INTO owners VALUES(76,1,'Team 2');
INSERT INTO owners VALUES(77,1,'Team 3');
INSERT INTO owners VALUES(78,1,'Team 4');
INSERT INTO owners VALUES(79,1,'Team 5');
INSERT INTO owners VALUES(80,1,'Team 6');
INSERT INTO owners VALUES(81,1,'Team 7');
INSERT INTO owners VALUES(82,1,'Team 8');
INSERT INTO owners VALUES(83,1,'Team 9');
INSERT INTO owners VALUES(84,1,'Team 10');
INSERT INTO owners VALUES(85,1,'Team 11');
INSERT INTO owners VALUES(86,1,'Team 12');
INSERT INTO owners VALUES(87,2,'Team 1');
INSERT INTO owners VALUES(88,2,'Team 2');
INSERT INTO owners VALUES(89,2,'Team 3');
INSERT INTO owners VALUES(90,2,'Team 4');
INSERT INTO owners VALUES(91,2,'Team 5');
INSERT INTO owners VALUES(92,2,'Team 6');
INSERT INTO owners VALUES(93,2,'Team 7');
INSERT INTO owners VALUES(94,2,'Team 8');
INSERT INTO owners VALUES(95,2,'Team 9');
INSERT INTO owners VALUES(96,2,'Team 10');
INSERT INTO owners VALUES(97,3,'Team 1');
INSERT INTO owners VALUES(98,3,'Team 2');
INSERT INTO owners VALUES(99,3,'Team 3');
INSERT INTO owners VALUES(101,3,'Team 4');
INSERT INTO owners VALUES(101,3,'Team 5');
INSERT INTO owners VALUES(102,3,'Team 6');
INSERT INTO owners VALUES(103,3,'Team 7');
INSERT INTO owners VALUES(104,3,'Team 8');
INSERT INTO owners VALUES(105,3,'Team 9');
INSERT INTO owners VALUES(106,3,'Team 10');
INSERT INTO owners VALUES(107,4,'Team 1');
INSERT INTO owners VALUES(108,4,'Team 2');
INSERT INTO owners VALUES(109,4,'Team 3');
INSERT INTO owners VALUES(110,4,'Team 4');
INSERT INTO owners VALUES(111,4,'Team 5');
INSERT INTO owners VALUES(112,4,'Team 6');
INSERT INTO owners VALUES(113,4,'Team 7');
INSERT INTO owners VALUES(114,4,'Team 8');
INSERT INTO owners VALUES(115,4,'Team 9');
INSERT INTO owners VALUES(116,4,'Team 10');


CREATE TABLE positions(positionid integer primary key, positionname char(10));
CREATE TABLE positionsforleague(positionid integer,id integer, value integer, foreign key(positionid) references positions(positionid),foreign key(id) references leagues(id));
CREATE TABLE scorehits(scoreid integer primary key, scorename varchar(15));
CREATE TABLE scorepitch(scoreid integer primary key, scorename varchar(15));
CREATE TABLE scorehitsforleague(id integer,scoreid integer, foreign key(id) references leagues(id),foreign key(scoreid) references scorehits(scoreid));
CREATE TABLE scorepitchforleague(id integer,scoreid integer, foreign key(id) references leagues(id),foreign key(scoreid) references scorehits(scoreid));
CREATE TABLE teams(teamid integer primary key,name varchar(50),teamnational char, shortname char(5));
CREATE TABLE teamsforleague(id integer, teamid integer, FOREIGN KEY(id) REFERENCES leagues(id), FOREIGN KEY(teamid) REFERENCES teams(teamid));
CREATE TABLE targetshits(id integer,scoreid integer,value double precision, foreign key(id) references leagues(id),foreign key(scoreid) references scorehits(scoreid));
CREATE TABLE targetspitches(id integer,scoreid integer,value double precision, foreign key(id) references leagues(id),foreign key(scoreid) references scorepitch(scoreid));
CREATE TABLE players(playerid integer primary key, name varchar(70), ishitter char, age integer, value double precision, teamid integer, currvalue double precision, notes varchar(125), isdrafted char(1), isnew char(1), playerrank integer, FOREIGN KEY(teamid) REFERENCES teams(teamid));
CREATE TABLE playerpositioninleague( playerid integer, positionid integer, id integer, foreign key(playerid) references players(playerid), foreign key (positionid) references positions(positionid), foreign key(id) references leagues(id));
CREATE TABLE playerposition(playerid integer, positionid integer, FOREIGN KEY(playerid) REFERENCES players(playerid), FOREIGN KEY(positionid) REFERENCES positions(positionid));
CREATE TABLE playerscorehits(playerid integer, scoreid integer, value real, foreign key(playerid) references players(playerid), foreign key(scoreid) references scorehits(scoreid));
CREATE TABLE playerscorepitches(playerid integer, scoreid integer, value real, foreign key(playerid) references players(playerid), foreign key(scoreid) references scorepitch(scoreid));
CREATE TABLE playersinleague(id integer, playerid integer, ishitter char, age integer, value double precision, currvalue double precision, draft boolean, isnew char(1), current_rank integer, original_rank integer, deleted integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid), foreign key(id) references leagues(id), foreign key(playerid) references players(playerid),foreign key(teamid) references teams(teamid));
CREATE TABLE leaguescorehitter(id integer, playerid integer,  scoreid integer, value double precision, foreign key(id) references leagues(id), foreign key(id, playerid) references playersinleague(id, playerid), foreign key(scoreid) references scorehits(scoreid));
CREATE TABLE leaguescorepitcher(id integer, playerid integer, scoreid integer, value double precision, foreign key(id) references leagues(id), foreign key(id, playerid) references playersinleague(id, playerid), foreign key(scoreid) references scorepitch(scoreid));
CREATE TABLE abcß(id integer primary key, name text);
CREATE TABLE pictures(id INTEGER PRIMARY KEY, date DATE, picture bytea);
CREATE INDEX league ON leagues(id,name);
CREATE INDEX playerpositioninleague_id ON playerpositioninleague(id);
CREATE INDEX teams_teamnational_name ON teams(teamnational,name);
CREATE INDEX playerscorehits_playerid ON playerscorehits(playerid);
CREATE INDEX playerscorepitches_playerid ON playerscorepitches(playerid);
CREATE INDEX playerpositioninleague_playerid ON playerpositioninleague(playerid);
CREATE INDEX leaguescorehitter_playerid ON leaguescorehitter(playerid);
CREATE INDEX leaguescorepitcher_playerid ON leaguescorepitcher(playerid);
CREATE INDEX players_team_age_name ON players(teamid,age,name);
CREATE INDEX players_isnew_teamid ON players(isnew, teamid);
CREATE TABLE playerdraft(playerid integer, id integer, ownerid integer, draftprice integer, draftorder integer, draftposition char(2), foreign key(playerid) references players(playerid), foreign key(id) references leagues(id), foreign key(ownerid) references owners(ownerid));
CREATE INDEX playerid_id ON playerdraft(playerid,id);