DROP TABLE IF EXISTS  Architecture
					, Depend
					, DevArch
					, Device
					, DistComponent
					, DistDevice
					, Distribution
					, Package
					, PackageType
					, Session
					, SessionPackage
					, Software;

CREATE TABLE Distribution (
	ID				int NOT NULL auto_increment,
	Name			varchar(50) NOT NULL default '',
	Version			varchar(80) NOT NULL default '0.0.0',
	FPackageType	int,
	ReleaseDate		date NOT NULL default '0000-01-01',
	ReleaseNotes	varchar(255) NOT NULL default '',
	Homepage		varchar(255) NOT NULL default '',
	BugTracker		varchar(255) NOT NULL default '',
	Logo			varchar(255) NOT NULL default '',
	GenMailingList	varchar(255) NOT NULL default '',
	DevMailingList	varchar(255) NOT NULL default '',
	AnnMailingList	varchar(255) NOT NULL default '',

	PRIMARY KEY (ID),
	INDEX (Name,Version,FPackageType),

	CONSTRAINT FPackageType FOREIGN KEY (FPackageType)
		REFERENCES PackageType (ID) ON DELETE SET NULL
);

CREATE TABLE Device (
	ID				int NOT NULL auto_increment,
	Manufacturer	varchar(50) NOT NULL default '',
	Model			varchar(50) NOT NULL default '',
	Picture			varchar(255) NOT NULL default '',

	PRIMARY KEY (ID),
	INDEX (Manufacturer,Model)
);

CREATE TABLE DistDevice (
	FDistribution	int,
	FDevice			int,

	PRIMARY KEY (FDistribution,FDevice),

	CONSTRAINT FDistribution FOREIGN KEY (FDistribution)
		REFERENCES Distribution (ID) ON DELETE SET NULL,
	CONSTRAINT FDevice FOREIGN KEY (FDevice)
		REFERENCES Device (ID) ON DELETE SET NULL
);

CREATE TABLE Architecture (
	ID				int NOT NULL auto_increment,
	Name			varchar(50) NOT NULL default '',

	PRIMARY KEY (ID),
	INDEX (Name)
);

CREATE TABLE DevArch (
	FDevice			int,
	FArchitecture	int,
	Priority		int,

	PRIMARY KEY (FDevice,FArchitecture),

	CONSTRAINT FDevice FOREIGN KEY (FDevice)
		REFERENCES Device (ID) ON DELETE SET NULL,
	CONSTRAINT FArchitecture FOREIGN KEY (FArchitecture)
		REFERENCES Architecture (ID) ON DELETE SET NULL
);

CREATE TABLE PackageType (
	ID				int NOT NULL auto_increment,
	Name			varchar(50) NOT NULL default '',

	PRIMARY KEY (ID),
	INDEX (Name)
);

CREATE TABLE Software (
	ID				int NOT NULL auto_increment,
	Name			varchar(50) NOT NULL default '',
	Version			varchar(80) NOT NULL default '',
	Maintainer		varchar(50) NOT NULL default '',
	Description		text,
	Homepage		varchar(255) NOT NULL default '',
	Source			text,
	Screenshot		varchar(255) NOT NULL default '',

	PRIMARY KEY (ID),
	INDEX (Name,Version)
);

CREATE TABLE DistComponent (
	FDistribution		int,
	FSoftware		int,
	Section			varchar(50) NOT NULL default '',

	PRIMARY KEY (FDistribution,FSoftware),

	CONSTRAINT FDistribution FOREIGN KEY (FDistribution)
		REFERENCES `Distribution` (ID) ON DELETE SET NULL,
	CONSTRAINT FSoftware FOREIGN KEY (FSoftware)
		REFERENCES Software (ID) ON DELETE SET NULL
);

CREATE TABLE Package (
	ID				int NOT NULL auto_increment,
	FSoftware		int,
	FArchitecture	int,
	FPackageType	int,
	Name			varchar(255) NOT NULL default '',
	Size			int,
	MD5Sum			char(32) NOT NULL default '',
	FileList		text,

	PRIMARY KEY (ID),
	INDEX (FSoftware,FArchitecture,FPackageType),

	CONSTRAINT FSoftware FOREIGN KEY (FSoftware)
		REFERENCES Software (ID) ON DELETE SET NULL,
	CONSTRAINT FArchitecture FOREIGN KEY (FArchitecture)
		REFERENCES Architecture (ID) ON DELETE SET NULL,
	CONSTRAINT FPackageType FOREIGN KEY (FPackageType)
		REFERENCES PackageType (ID) ON DELETE SET NULL
);

CREATE TABLE Depend (
	FSoftware			int,
	DepType				enum('HARDDEP','SOFTDEP','PROVIDES','REPLACES','CONFLICT') NOT NULL,
	Modifier			enum('EQ','NE','LT','GT','LE','GE') NOT NULL,
	SoftwareName		varchar(50) NOT NULL default '',
	SoftwareVersion		varchar(80) NOT NULL default '',

	PRIMARY KEY (FSoftware,DepType,Modifier,SoftwareName,SoftwareVersion),

	CONSTRAINT FSoftware FOREIGN KEY (FSoftware)
		REFERENCES Software (ID) ON DELETE SET NULL
);

CREATE TABLE Session (
	ID				int NOT NULL auto_increment,
	Distribution	varchar(50) NOT NULL default '',
	DistroVersion	varchar(80) NOT NULL default '',
	Device			varchar(50) NOT NULL default '',
	PackageType		char(5) NOT NULL default '',
	ClientName		varchar(50) NOT NULL default '',
	ClientVersion	varchar(20) NOT NULL default '',

	PRIMARY KEY (ID)
);

CREATE TABLE SessionPackage (
	FSession		int,
	PackageName		varchar(50) NOT NULL default '',
	PackageVersion	varchar(80) NOT NULL default '',

	PRIMARY KEY (FSession,PackageName,PackageVersion),

	CONSTRAINT FSession FOREIGN KEY (FSession)
		REFERENCES Session (ID) ON DELETE SET NULL
);