Subversion-Projekte lars-tiefland.em_wm

-- phpMyAdmin SQL Dump

-- version 2.8.0.4

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Erstellungszeit: 24. Juni 2006 um 12:49

-- Server Version: 5.0.22

-- PHP-Version: 5.1.4-pl3-gentoo

SET FOREIGN_KEY_CHECKS=0;

SET AUTOCOMMIT=0;

START TRANSACTION;

--

-- Datenbank: `wm2006`

--

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `auth_user`

--

DROP TABLE IF EXISTS `auth_user`;

CREATE TABLE IF NOT EXISTS `auth_user` (

  `u_id` int(11) NOT NULL auto_increment,

  `u_name` varchar(255) collate latin1_german1_ci NOT NULL default '',

  `u_email` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,

  `u_password` varchar(50) collate latin1_german1_ci NOT NULL default '',

  `u_type` int(11) unsigned NOT NULL default '0',

  PRIMARY KEY  (`u_id`),

  UNIQUE KEY `u_name` (`u_name`),

  UNIQUE KEY `u_email` (`u_email`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=3 ;

--

-- Daten für Tabelle `auth_user`

--

INSERT INTO `auth_user` (`u_id`, `u_name`, `u_email`, `u_password`, `u_type`) VALUES (1, 'LTiefland', 'ltiefland@gmail.com', '77eeef4a06967c4bfcb83391a690aa13', 1),

(2, 'test', 'ltiefland@lycos.de', '68eacb97d86f0c4621fa2b0e17cabd8c', 0);

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `challenge`

--

DROP TABLE IF EXISTS `challenge`;

CREATE TABLE IF NOT EXISTS `challenge` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `challenge_id` varchar(255) collate latin1_general_ci NOT NULL,

  `u_id` int(11) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `challenge_id` (`challenge_id`,`u_id`),

  KEY `u_id` (`u_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--

-- Daten für Tabelle `challenge`

--

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `config`

--

DROP TABLE IF EXISTS `config`;

CREATE TABLE IF NOT EXISTS `config` (

  `config_name` varchar(255) collate latin1_general_ci NOT NULL,

  `config_value` varchar(255) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`config_name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Daten für Tabelle `config`

--

INSERT INTO `config` (`config_name`, `config_value`) VALUES ('cookie_name', 'cookie_wm'),

('groups', '8'),

('host', 'Germany'),

('logo', 'WC06_Logo.png'),

('max_anz', '16'),

('session_length', '1200'),

('teams_per_group', '4'),

('type', 'WM'),

('version', '0.1.0'),

('year', '2006');

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `games`

--

DROP TABLE IF EXISTS `games`;

CREATE TABLE IF NOT EXISTS `games` (

  `g_id` int(10) unsigned NOT NULL auto_increment,

  `g_name` varchar(255) collate latin1_general_ci default NULL,

  `g_m1` int(10) unsigned NOT NULL,

  `g_m2` int(10) unsigned NOT NULL,

  `g_g1` int(10) unsigned NOT NULL,

  `g_g2` int(10) unsigned NOT NULL,

  `g_location` int(10) unsigned NOT NULL,

  `g_date` int(11) NOT NULL default '0',

  `g_type` int(10) unsigned NOT NULL,

  PRIMARY KEY  (`g_id`),

  KEY `g_location` (`g_location`),

  KEY `g_m1` (`g_m1`,`g_m2`),

  KEY `g_m2` (`g_m2`),

  KEY `g_type` (`g_type`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=57 ;

--

-- Daten für Tabelle `games`

--

INSERT INTO `games` (`g_id`, `g_name`, `g_m1`, `g_m2`, `g_g1`, `g_g2`, `g_location`, `g_date`, `g_type`) VALUES (1, 'Spiel 1', 1, 2, 4, 2, 10, 1149868800, 1),

(2, 'Spiel 2', 3, 4, 0, 2, 2, 1149879600, 1),

(3, 'Spiel 3', 5, 6, 1, 0, 4, 1149944400, 1),

(4, 'Spiel 4', 7, 8, 0, 0, 1, 1149955200, 1),

(5, 'Spiel 5', 9, 10, 2, 1, 5, 1149966000, 1),

(6, 'Spiel 6', 11, 12, 0, 1, 9, 1150030800, 1),

(7, 'Spiel 7', 13, 14, 3, 1, 11, 1150041600, 1),

(8, 'Spiel 8', 15, 16, 0, 1, 8, 1150052400, 1),

(9, 'Spiel 12', 23, 24, 3, 1, 7, 1150117200, 1),

(10, 'Spie 10', 19, 20, 0, 3, 2, 1150128000, 1),

(11, 'Spiel 9', 17, 18, 2, 0, 6, 1150138800, 1),

(12, 'Spiel 14', 27, 28, 2, 1, 4, 1150203600, 1),

(13, 'Spiel 13', 25, 26, 0, 0, 12, 1150214400, 1),

(14, 'Spiel 11', 21, 22, 1, 0, 3, 1150225200, 1),

(15, 'Spiel 15', 29, 30, 4, 0, 9, 1150290000, 1),

(16, 'Spiel 16', 31, 32, 2, 2, 10, 1150300800, 1),

(17, 'Spiel 17', 1, 3, 1, 0, 1, 1150311600, 1),

(18, 'Spiel 18', 4, 2, 3, 0, 5, 1150376400, 1),

(19, 'Spiel 19', 5, 7, 2, 0, 11, 1150387200, 1),

(20, 'Spiel 20', 8, 6, 1, 0, 3, 1150398000, 1),

(21, 'Spiel 21', 9, 11, 6, 0, 2, 1150462800, 1),

(22, 'Spiel 22', 12, 10, 2, 1, 12, 1150473600, 1),

(23, 'Spiel 23', 13, 15, 0, 0, 6, 1150484400, 1),

(24, 'Spiel 24', 16, 14, 2, 0, 4, 1150549200, 1),

(25, 'Spiel 26', 20, 18, 0, 2, 8, 1150560000, 1),

(26, 'Spiel 25', 17, 19, 1, 1, 7, 1150570800, 1),

(27, 'Spiel 28', 24, 22, 0, 0, 11, 1150635600, 1),

(28, 'Spiel 27', 21, 23, 2, 0, 10, 1150646400, 1),

(29, 'Spiel 29', 25, 27, 1, 1, 9, 1150657200, 1),

(30, 'Spiel 30', 28, 26, 0, 2, 1, 1150722000, 1),

(31, 'Spiel 32', 32, 30, 0, 4, 5, 1150732800, 1),

(32, 'Spiel 31', 29, 31, 3, 1, 12, 1150743600, 1),

(33, 'Spiel 33', 4, 1, 0, 3, 3, 1150812000, 1),

(34, 'Spiel 34', 2, 3, 1, 2, 6, 1150812000, 1),

(35, 'Spiel 35', 8, 5, 2, 2, 8, 1150830000, 1),

(36, 'Spiel 36', 6, 7, 2, 0, 7, 1150830000, 1),

(37, 'Spiel 39', 16, 13, 2, 1, 2, 1150898400, 1),

(38, 'Spiel 40', 14, 15, 1, 1, 9, 1150898400, 1),

(39, 'Spiel 37', 12, 9, 0, 0, 4, 1150916400, 1),

(40, 'Spiel 38', 10, 11, 3, 2, 10, 1150916400, 1),

(41, 'Spiel 41', 20, 17, 0, 2, 5, 1150984800, 1),

(42, 'Spiel 42', 18, 19, 2, 1, 11, 1150984800, 1),

(43, 'Spiel 43', 24, 21, 1, 4, 1, 1151002800, 1),

(44, 'Spiel 44', 22, 23, 2, 2, 12, 1151002800, 1),

(45, 'Spiel 47', 32, 29, 0, 1, 7, 1151071200, 1),

(46, 'Spiel 48', 30, 31, 1, 0, 3, 1151071200, 1),

(47, 'Spiel 45', 28, 25, 0, 2, 8, 1151089200, 1),

(48, 'Spiel 46', 26, 27, 2, 0, 6, 1151089200, 1),

(49, 'Achtelfinalspiel 1', 1, 8, 0, 0, 10, 1151161200, 2),

(50, 'Achtelfinalspiel 2', 9, 13, 0, 0, 9, 1151175600, 2),

(51, 'Achtelfinalspiel 3', 5, 4, 0, 0, 12, 1151247600, 2),

(52, 'Achtelfinalspiel 4', 16, 12, 0, 0, 11, 1151262000, 2),

(53, 'Achtelfinaspiel 5', 17, 23, 0, 0, 7, 1151334000, 2),

(54, 'Achtelfinalspiel 6', 26, 30, 0, 0, 8, 1151348400, 2),

(55, 'Achtelfinalspiel 7', 21, 18, 0, 0, 1, 1151420400, 2),

(56, 'Achtelfinalspiel 8', 29, 25, 0, 0, 6, 1151434800, 2);

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `groups`

--

DROP TABLE IF EXISTS `groups`;

CREATE TABLE IF NOT EXISTS `groups` (

  `g_id` int(10) unsigned NOT NULL auto_increment,

  `g_name` char(1) collate latin1_general_ci default NULL,

  PRIMARY KEY  (`g_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=9 ;

--

-- Daten für Tabelle `groups`

--

INSERT INTO `groups` (`g_id`, `g_name`) VALUES (1, 'A'),

(2, 'B'),

(3, 'C'),

(4, 'D'),

(5, 'E'),

(6, 'F'),

(7, 'G'),

(8, 'H');

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `locations`

--

DROP TABLE IF EXISTS `locations`;

CREATE TABLE IF NOT EXISTS `locations` (

  `l_id` int(10) unsigned NOT NULL auto_increment,

  `l_name` varchar(255) collate latin1_general_ci default NULL,

  `l_name2` varchar(255) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`l_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

--

-- Daten für Tabelle `locations`

--

INSERT INTO `locations` (`l_id`, `l_name`, `l_name2`) VALUES (1, 'Dortmund', 'FIFA WM-Stadion'),

(2, 'Gelsenkirchen', 'FIFA WM-Stadion'),

(3, 'Berlin', 'Olympiastadion'),

(4, 'Frankfurt', 'FIFA WM-Stadion'),

(5, 'Hamburg', 'FIFA WM-Stadion'),

(6, 'Hannover', 'FIFA WM-Stadion'),

(7, 'Kaiserslautern', 'Fritz-Walter-Stadion'),

(8, 'Köln', 'FIFA WM-Stadion'),

(9, 'Leipzig', 'Zentralstadion'),

(10, 'München', 'FIFA WM-Stadion'),

(11, 'Nürnberg', 'Frankenstadion'),

(12, 'Stuttgart', 'Gottlieb-Daimler-Stadion');

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `log`

--

DROP TABLE IF EXISTS `log`;

CREATE TABLE IF NOT EXISTS `log` (

  `id` int(11) unsigned NOT NULL auto_increment,

  `user_id` int(11) default '0',

  `datum` int(11) unsigned NOT NULL default '0',

  `message` varchar(255) collate latin1_german1_ci NOT NULL default '',

  PRIMARY KEY  (`id`),

  KEY `user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=1 ;

--

-- Daten für Tabelle `log`

--

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `teams`

--

DROP TABLE IF EXISTS `teams`;

CREATE TABLE IF NOT EXISTS `teams` (

  `t_id` int(10) unsigned NOT NULL auto_increment,

  `t_name` varchar(255) collate latin1_general_ci default NULL,

  `t_group` int(10) unsigned NOT NULL,

  `t_pos` tinyint(1) unsigned NOT NULL,

  `t_diff` tinyint(3) NOT NULL,

  `t_points` tinyint(2) unsigned NOT NULL,

  `t_goals` varchar(10) collate latin1_general_ci NOT NULL,

  `t_flag` varchar(255) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`t_id`),

  KEY `t_name` (`t_name`),

  KEY `t_group` (`t_group`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=33 ;

--

-- Daten für Tabelle `teams`

--

INSERT INTO `teams` (`t_id`, `t_name`, `t_group`, `t_pos`, `t_diff`, `t_points`, `t_goals`, `t_flag`) VALUES (1, 'Deutschland', 1, 0, 6, 9, '8 : 2', 'Germany.png'),

(2, 'Costa Rica', 1, 0, -6, 0, '3 : 9', 'Costa_Rica.png'),

(3, 'Polen', 1, 0, -2, 3, '2 : 4', 'Poland.png'),

(4, 'Equador', 1, 0, 2, 6, '5 : 3', 'Ecuador.png'),

(5, 'England', 2, 0, 3, 7, '5 : 2', 'England.png'),

(6, 'Paraguay', 2, 0, 0, 3, '2 : 2', 'Paraguay.png'),

(7, 'Trinidad/Tobago', 2, 0, -4, 1, '0 : 4', 'Trinidad_Tobago.png'),

(8, 'Schweden', 2, 0, 1, 5, '3 : 2', 'Sweden.png'),

(9, 'Argentinien', 3, 0, 7, 7, '8 : 1', 'Argentina.png'),

(10, 'Elfenbeinküste', 3, 0, -1, 3, '5 : 6', 'Cote_dIvoire.png'),

(11, 'Serbien/Montenegro', 3, 0, -8, 0, '2 : 10', 'Serbia_Montenegro.png'),

(12, 'Niederlande', 3, 0, 2, 7, '3 : 1', 'Netherlands.png'),

(13, 'Mexiko', 4, 0, 1, 4, '4 : 3', 'Mexico.png'),

(14, 'Iran', 4, 0, -4, 1, '2 : 6', 'Iran.png'),

(15, 'Angola', 4, 0, -1, 2, '1 : 2', 'Angola.png'),

(16, 'Portugal', 4, 0, 4, 9, '5 : 1', 'Portugal.png'),

(17, 'Italien', 5, 0, 4, 7, '5 : 1', 'Italy.png'),

(18, 'Ghana', 5, 0, 1, 6, '4 : 3', 'Ghana.png'),

(19, 'USA', 5, 0, -4, 1, '2 : 6', 'USA.png'),

(20, 'Tschechien', 5, 0, -1, 3, '3 : 4', 'Czech_Republic.png'),

(21, 'Brasilien', 6, 0, 6, 9, '7 : 1', 'Brazil.png'),

(22, 'Kroatien', 6, 0, -1, 2, '2 : 3', 'Croatia.png'),

(23, 'Australien', 6, 0, 0, 4, '5 : 5', 'Australia.png'),

(24, 'Japan', 6, 0, -5, 1, '2 : 7', 'Japan.png'),

(25, 'Frankreich', 7, 0, 2, 5, '3 : 1', 'France.png'),

(26, 'Schweiz', 7, 0, 4, 7, '4 : 0', 'Switzerland.png'),

(27, 'Südkorea', 7, 0, -1, 4, '3 : 4', 'Korea_Republic.png'),

(28, 'Togo', 7, 0, -5, 0, '1 : 6', 'Togo.png'),

(29, 'Spanien', 8, 0, 7, 9, '8 : 1', 'Spain.png'),

(30, 'Ukraine', 8, 0, 1, 6, '5 : 4', 'Ukraine.png'),

(31, 'Tunesien', 8, 0, -3, 1, '3 : 6', 'Tunisia.png'),

(32, 'Saudi-Arabien', 8, 0, -5, 1, '2 : 7', 'Saudi_Arabia.png');

-- --------------------------------------------------------

--

-- Tabellenstruktur für Tabelle `types`

--

DROP TABLE IF EXISTS `types`;

CREATE TABLE IF NOT EXISTS `types` (

  `t_id` int(10) unsigned NOT NULL auto_increment,

  `t_name` varchar(255) collate latin1_general_ci NOT NULL,

  `t_max` int(11) NOT NULL,

  PRIMARY KEY  (`t_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

--

-- Daten für Tabelle `types`

--

INSERT INTO `types` (`t_id`, `t_name`, `t_max`) VALUES (1, 'Vorrunde', 48),

(2, 'Achtelfinale', 8),

(3, 'Viertelfinale', 4),

(4, 'Halbfinale', 2),

(5, 'Spiel um Platz 3', 1),

(6, 'Finale', 1);

--

-- Constraints der exportierten Tabellen

--

--

-- Constraints der Tabelle `challenge`

--

ALTER TABLE `challenge`

  ADD CONSTRAINT `challenge_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `auth_user` (`u_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints der Tabelle `games`

--

ALTER TABLE `games`

  ADD CONSTRAINT `games_ibfk_16` FOREIGN KEY (`g_m1`) REFERENCES `teams` (`t_id`) ON UPDATE CASCADE,

  ADD CONSTRAINT `games_ibfk_17` FOREIGN KEY (`g_m2`) REFERENCES `teams` (`t_id`) ON UPDATE CASCADE,

  ADD CONSTRAINT `games_ibfk_18` FOREIGN KEY (`g_location`) REFERENCES `locations` (`l_id`) ON UPDATE CASCADE;

--

-- Constraints der Tabelle `log`

--

ALTER TABLE `log`

  ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`u_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--

-- Constraints der Tabelle `teams`

--

ALTER TABLE `teams`

  ADD CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`t_group`) REFERENCES `groups` (`g_id`) ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS=1;

COMMIT;