CREATE DATABASE  IF NOT EXISTS `inlamningsuppgift1` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `inlamningsuppgift1`;
-- MySQL dump 10.13  Distrib 5.6.17, for Win32 (x86)
--
-- Host: adellestrand.ddns.net    Database: inlamningsuppgift1
-- ------------------------------------------------------
-- Server version	5.6.21-log

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `klubbar`
--

DROP TABLE IF EXISTS `klubbar`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `klubbar` (
  `klubbID` int(11) NOT NULL AUTO_INCREMENT,
  `clubName` varchar(45) NOT NULL,
  `seasonID` int(11) NOT NULL,
  `divID` int(11) NOT NULL,
  PRIMARY KEY (`klubbID`),
  UNIQUE KEY `klubbID_UNIQUE` (`klubbID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matcher`
--

DROP TABLE IF EXISTS `matcher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `matcher` (
  `matchID` int(11) NOT NULL AUTO_INCREMENT,
  `divID` int(11) NOT NULL,
  `seasonID` int(11) NOT NULL,
  `hLagID` int(11) NOT NULL,
  `bLagID` int(11) NOT NULL,
  `dag` int(11) DEFAULT NULL,
  `scoreHome` int(11) DEFAULT NULL,
  `scoreAway` int(11) DEFAULT NULL,
  `editable` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`matchID`),
  UNIQUE KEY `matchID_UNIQUE` (`matchID`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`admin`@`%`*/ /*!50003 TRIGGER `matcher_AUPD` AFTER UPDATE ON `matcher` FOR EACH ROW
begin
if old.scoreHome < old.scoreAway
	then update `tabell`
		set antalVinster = antalVinster-1,
			antalMatcher = antalMatcher-1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif old.scoreHome > old.scoreAway
	then UPDATE `tabell`
		set antalVinster = antalVinster-1,
			antalMatcher = antalMatcher-1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.hLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif old.scoreHome = old.scoreAway
	then UPDATE `tabell`
		set antalOavgjorda = antalOavgjorda-1,
			antalMatcher = antalMatcher-1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.hLagID = `klubbID`
OR new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
end if;
if new.scoreHome > new.scoreAway
	then UPDATE `tabell`
		set antalVinster = antalVinster+1,
			antalMatcher = antalMatcher+1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.hLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif new.scoreHome < new.scoreAway
	then UPDATE `tabell`
		set antalVinster = antalVinster+1,
			antalMatcher = antalMatcher+1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif new.scoreHome = new.scoreAway
	then UPDATE `tabell`
		set antalOavgjorda = antalOavgjorda+1,
			antalMatcher = antalMatcher+1,
			poang = (antalVinster*2)+antalOavgjorda
WHERE new.hLagID = `klubbID`
OR new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
END if;
if old.scoreHome > old.scoreAway
	then UPDATE `tabell`
		set antalForluster = antalForluster-1,
			antalMatcher = antalMatcher-1
WHERE new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif old.scoreHome < old.scoreAway
	then UPDATE `tabell`
		set antalForluster = antalForluster-1,
			antalMatcher = antalMatcher-1
WHERE new.hLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
end if;
if new.scoreHome > new.scoreAway
	then UPDATE `tabell`
		set antalForluster = antalForluster+1,
			antalMatcher = antalMatcher+1
WHERE new.bLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
elseif new.scoreHome < new.scoreAway
	then UPDATE `tabell`
		set antalForluster = antalForluster+1,
			antalMatcher = antalMatcher+1
WHERE new.hLagID = `klubbID`
AND new.seasonID = `seasonID`
AND new.divID = `divID`;
END if;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `season`
--

DROP TABLE IF EXISTS `season`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `season` (
  `seasonID` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) NOT NULL,
  PRIMARY KEY (`seasonID`),
  UNIQUE KEY `seasonID_UNIQUE` (`seasonID`),
  UNIQUE KEY `year_UNIQUE` (`year`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `serier`
--

DROP TABLE IF EXISTS `serier`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `serier` (
  `divID` int(11) NOT NULL,
  `seasonID` int(11) NOT NULL,
  `divName` varchar(45) NOT NULL,
  PRIMARY KEY (`divID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tabell`
--

DROP TABLE IF EXISTS `tabell`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tabell` (
  `tabellID` int(11) NOT NULL AUTO_INCREMENT,
  `divID` int(11) NOT NULL,
  `seasonID` int(11) NOT NULL,
  `klubbID` int(11) NOT NULL,
  `antalMatcher` int(11) DEFAULT '0',
  `antalVinster` int(11) DEFAULT '0',
  `antalOavgjorda` int(11) DEFAULT '0',
  `antalForluster` int(11) DEFAULT '0',
  `poang` int(11) DEFAULT '0',
  PRIMARY KEY (`tabellID`),
  UNIQUE KEY `tabellID_UNIQUE` (`tabellID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `fName` varchar(45) NOT NULL,
  `eName` varchar(45) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `password` varchar(45) NOT NULL,
  `klubbID` int(11) DEFAULT NULL,
  PRIMARY KEY (`userID`),
  UNIQUE KEY `userID_UNIQUE` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping events for database 'inlamningsuppgift1'
--

--
-- Dumping routines for database 'inlamningsuppgift1'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-12-28 12:54:55