SpaceMUD Database SQL Definition


	DROP TABLE Mud.ObjectModifier;
	DROP TABLE Mud.MobAccount;
	DROP TABLE Mud.UserAccount;
	DROP TABLE Mud.ObjectModifierGroup;
	DROP TABLE Mud.MudObject;
	DROP TABLE Mud.SubSector;
	DROP TABLE Mud.HoldingAccount;
	DROP TABLE Mud.Sector;
	
	CREATE TABLE Mud.Sector (
	       sectorID INT NOT NULL AUTO_INCREMENT
	     , name CHAR(32)
	     , descr TINYTEXT
	     , coordX INT NOT NULL
	     , coordY INT NOT NULL
	     , coordZ INT NOT NULL
	     , PRIMARY KEY (sectorID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.HoldingAccount (
	       holdingID INT NOT NULL AUTO_INCREMENT
	     , PRIMARY KEY (holdingID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.SubSector (
	       subsectorID INT NOT NULL AUTO_INCREMENT
	     , sectorID INT NOT NULL
	     , name CHAR(32)
	     , descr TINYTEXT
	     , coordX INT NOT NULL
	     , coordY INT NOT NULL
	     , coordZ INT NOT NULL
	     , PRIMARY KEY (subsectorID)
	     , INDEX (sectorID)
	     , CONSTRAINT FK_SubSector_1 FOREIGN KEY (sectorID)
	                  REFERENCES Mud.Sector (sectorID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.MudObject (
	       objectID INT NOT NULL AUTO_INCREMENT
	     , aggregateID INT
	     , objectType CHAR(32) NOT NULL
	     , subsectorID INT NOT NULL
	     , holdingID INT NOT NULL
	     , PRIMARY KEY (objectID)
	     , INDEX (aggregateID)
	     , CONSTRAINT FK_MudObject_2 FOREIGN KEY (aggregateID)
	                  REFERENCES Mud.MudObject (objectID)
	     , INDEX (subsectorID)
	     , CONSTRAINT FK_MudObject_3 FOREIGN KEY (subsectorID)
	                  REFERENCES Mud.SubSector (subsectorID)
	     , INDEX (holdingID)
	     , CONSTRAINT FK_MudObject_4 FOREIGN KEY (holdingID)
	                  REFERENCES Mud.HoldingAccount (holdingID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.ObjectModifierGroup (
	       groupID INT NOT NULL AUTO_INCREMENT
	     , groupName CHAR(32) NOT NULL
	     , objectID INT NOT NULL
	     , PRIMARY KEY (groupID)
	     , INDEX (objectID)
	     , CONSTRAINT FK_ObjectModifierGroup_1 FOREIGN KEY (objectID)
	                  REFERENCES Mud.MudObject (objectID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.UserAccount (
	       userID INT NOT NULL AUTO_INCREMENT
	     , username CHAR(24) NOT NULL
	     , password CHAR(14) NOT NULL
	     , permissions INT NOT NULL
	     , holdingID INT NOT NULL
	     , UNIQUE UQ_UserAccount_Holding (holdingID, username)
	     , PRIMARY KEY (userID)
	     , INDEX (holdingID)
	     , CONSTRAINT FK_UserAccount_1 FOREIGN KEY (holdingID)
	                  REFERENCES Mud.HoldingAccount (holdingID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.MobAccount (
	       mobID INT NOT NULL AUTO_INCREMENT
	     , name CHAR(24) NOT NULL
	     , holdingID INT NOT NULL
	     , UNIQUE UQ_MobAccount_1 (name, holdingID)
	     , PRIMARY KEY (mobID)
	     , INDEX (holdingID)
	     , CONSTRAINT FK_MobAccount_1 FOREIGN KEY (holdingID)
	                  REFERENCES Mud.HoldingAccount (holdingID)
	)TYPE=InnoDB;
	
	CREATE TABLE Mud.ObjectModifier (
	       modifierID INT NOT NULL AUTO_INCREMENT
	     , modifierGroup INT NOT NULL
	     , modifierType CHAR(32) NOT NULL
	     , modifierValue DOUBLE PRECISION NOT NULL
	     , PRIMARY KEY (modifierID)
	     , INDEX (modifierGroup)
	     , CONSTRAINT FK_ObjectModifier_1 FOREIGN KEY (modifierGroup)
	                  REFERENCES Mud.ObjectModifierGroup (groupID)
	)TYPE=InnoDB;

<--Back