database.sql

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;

CREATE TABLE `chatlog` (
  `id`      int(11)  NOT NULL AUTO_INCREMENT,
  `userId`  int(11)  NOT NULL,
  `date`    datetime NOT NULL,
  `message` text     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `chatlog_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `garages` (
  `id`     int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `price`  int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `garages_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = latin1;

CREATE TABLE `hiddenpackages` (
  `id`        int(11) NOT NULL AUTO_INCREMENT,
  `userId`    int(11) NOT NULL,
  `packageId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId_packageId` (`userId`, `packageId`),
  KEY `userId` (`userId`),
  CONSTRAINT `hiddenpackages_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `users` (
  `id`           int(11)           NOT NULL AUTO_INCREMENT,
  `username`     varchar(24)       NOT NULL,
  `password`     varchar(300)      NOT NULL,
  `language`     enum ('de', 'en') NOT NULL,
  `isAdmin`      tinyint(1)        NOT NULL DEFAULT '0',
  `posX`         float                      DEFAULT NULL,
  `posY`         float                      DEFAULT NULL,
  `posZ`         float                      DEFAULT NULL,
  `angle`        float                      DEFAULT NULL,
  `interior`     int(11)                    DEFAULT NULL,
  `skin`         int(11)                    DEFAULT '0',
  `money`        int(11)           NOT NULL DEFAULT '0',
  `level`        int(11)           NOT NULL DEFAULT '1',
  `registerDate` datetime          NOT NULL,
  `lastLogin`    datetime                   DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `vehiclecomponents` (
  `id`          int(11) NOT NULL AUTO_INCREMENT,
  `vehicleId`   int(11) NOT NULL,
  `slot`        int(11) NOT NULL,
  `componentId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vehicleId_slot` (`vehicleId`, `slot`),
  KEY `vehicleId` (`vehicleId`),
  CONSTRAINT `vehiclecomponents_vehicleId` FOREIGN KEY (`vehicleId`) REFERENCES `vehicles` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `vehicletuningparts` (
  `id`        int(11)                                                                         NOT NULL AUTO_INCREMENT,
  `vehicleId` int(11)                                                                         NOT NULL,
  `type`      ENUM ('frontBumber', 'hood', 'lights', 'neon', 'rearBumber', 'roof', 'spoiler') NOT NULL,
  `value`     int(11)                                                                         NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vehicleId_type` (`vehicleId`, `type`),
  KEY `vehicleId` (`vehicleId`),
  CONSTRAINT `vehicletuningparts_vehicleId` FOREIGN KEY (`vehicleId`) REFERENCES `vehicles` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `vehicles` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `modelId`     int(11)     NOT NULL,
  `userId`      int(11)     NOT NULL,
  `garageId`    int(11)              DEFAULT NULL,
  `posX`        float       NOT NULL DEFAULT '0',
  `posY`        float       NOT NULL DEFAULT '0',
  `posZ`        float       NOT NULL DEFAULT '0',
  `angle`       float       NOT NULL DEFAULT '0',
  `health`      float       NOT NULL DEFAULT '1000',
  `mileage`     int(11)     NOT NULL DEFAULT '0',
  `numberPlate` varchar(32) NOT NULL DEFAULT '',
  `color1`      int(11)     NOT NULL DEFAULT '0',
  `color2`      int(11)     NOT NULL DEFAULT '0',
  `paintjobId`  int(11)     NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `garageId` (`garageId`),
  CONSTRAINT `vehicles_garageId` FOREIGN KEY (`garageId`) REFERENCES `garages` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `vehicles_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;