Schema

Notes on tables

Legacy, unknown, unused, or future use columns withheld. Key information is not shown. It may be out of date; when in doubt, consult the source code or install script in deploy/install.sql to see how objects from the database are used.

BoolEnum is a non-nullable (unless stated otherwise) Enum(True, False).

Triggers

Set up triggers like this so INSERTing new items will give them proper IDs. Do it for downloads, flags, mirrors, products, screenshots, releases, and users.

CREATE DEFINER=`root`@`localhost` TRIGGER `BeforeCreateDownload` BEFORE INSERT ON `Downloads` FOR EACH ROW BEGIN
    SET New.DLUUID = UUIDBIN(UUID());
END

UDFs

CREATE DEFINER=`root`@`localhost` FUNCTION `UUIDBIN`(
	`_uuid` TINYTEXT
)
RETURNS binary(16)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	RETURN UNHEX(REPLACE(_uuid, '-', ''));
END

Products

Name	Type	Purpose
ProductUUID	Binary(16) Not Null	A product UUID.
Name	Varchar(150) Not Null	The product's human-readable name.
Slug	Varchar(100)	The product's URL-friendly name.
Notes	Text Not Null	The product description, in Markdown.
Type	Enum('OS', 'Game', 'Application', 'DevTool', 'System') Not Null Default Application	The category of the product.
ProductCreated	Timestamp	The date the product was added to the database.
DefaultRelease	Binary (16)	The UUID of the release to default to when none is selected.
ApplicationTags	Set('Word Processor','Spreadsheet',[...])	If an application, what kind it is.

Releases

Name	Type	Purpose
ProductUUID	Binary(16) Not Null	The associated product's UUID.
ReleaseUUID	Binary(16) Not Null	A release UUID.
Name	Varchar(50) Not Null	The release's name.
VendorName	Varchar(50)	The release vendor's name.
Slug	Varchar(100)	The release's URL friendly name.
ReleaseOrder	Int(11) Not Null	How releases should be ordered when displayed in a list.
ReleaseDate	Timestamp	The release date of the product.
EndOfLife	Timestamp	The date the product had support ended.
FuzzyDate	BoolEnum Default False	If the date should only have the year displayed.
RAMRequirement	Int(10)	How much RAM, in bytes, is required to run this release.
CPURequirement	Varchar(50)	What CPU is required to run this release.
DiskSpaceRequired	Int(10)	How much free disk space, in bytes, is required to run this release.
Type	Enum('GUI', Text') Default 'GUI'	What interface this release has.
InstallInstructions	LongText	Notes on how to install the release.
Notes	LongText	Notes on the release.
Platform	Set('DOS','CPM','Windows','OS2','Unix','Linux','MacOS','Mac OS X','DOSShell','Other')	What OSes this release runs on.

Serials

Name	Type	Purpose
ReleaseUUID	Binary(16)	The associated release UUID.
Serial	Varchar(500)	The serial.

Screenshots

Name	Type	Purpose
ScreenshotUUID	Binary(16) Not Null	The screenshot's UUID.
ReleaseUUID	Binary(16)	The associated release's UUID.
Title	Varchar(750)	A caption associated.
ScreenshotFile	Varchar(350)	The screenshot's filename, without path.

Downloads

Name	Type	Purpose
DLUUID	Binary(16) Not Null)	The UUID of the download.
Name	Varchar(150) Not Null	The download's name.
Version	Varchar(40) Not Null	The download's version.
RTM	EnumBool Default True	If the download is of a finished version.
SHA1Sum	Binary(20) Not Null	The SHA1 of the file.
DownloadPath	Text Not Null	Where the file is.
ImageType	Enum('Archive','35Floppy','525Floppy','CDISO','DVDISO','VPC','VMWARE','VBOX') Not Null Default 'Archive'	What type the file is.
Arch	Set('x86','x86-32','m68k','ppc','amd64','mos6502','ppc64','SPARC','SPARC64','MIPS','MIPS64','Alpha','Other') Not Null Default 'x86'	What CPUs the download runs on.
Information	Text	Notes about the download.
ReleaseUUID	Binary(16)	The associated release.
Upgrade	EnumBool Default False	If the download requires a previous version of the product to be installed.
Language	Varchar(50) Not Null Default English	The language of the download.
FileSize	BigInt(20)	The size of the file, in bytes.
FileName	VarChar(250)	The file's name.
ContributionUUID	Binary(16)	The association contribution.
CreatedDate	Timestamp	When the download was added.
LastUpdated	Timestamp	When the download was last changed.

MirrorContents

Name	Type	Purpose
MirrorUUID	Binary(16)	The ID of the mirror.
DownloadUUID	Binary(16)	The ID of the download.

DownloadMirrors

Name	Type	Purpose
MirrorUUID	Binary(16) Not Null	The mirror's UUID.
MirrorName	Varchar(50) Not Null	The human-readable name of the mirror.
Hostname	Varchar(50)	The hostname of the mirror.
IsOnline	EnumBool Null	If the mirror is online and working.
Location	Varchar(50)	The mirror's location.
Country	Enum('FR','UK','US','JP','EU','CA')	What country or region the mirror is in.

Users

Name	Type	Purpose
UserID	Binary(16) Not Null	The user's UUID.
Email	Varchar(80) Not Null	The user's email.
AccountEnabled	EnumBool Default True	If the user can log in.
Password	Varchar(64) Not Null	The user's password, in SHA-1, or bcrypt. (prefixed by $)
Salt	Varchar(64)	If using SHA-1, the salt applied to the user's password before hashing. Not used for bcrypt.
ShortName	Varchar(64)	The user's name.
RegistrationTime	Timestamp Not Null	When the user registered.
LastSeenTime	Timestamp Not Null	When the user last logged in.
RegistrationIP	Varchar(45) Not Null	The IP the account was created from.
ThemeName	Varchar(50) Not Null Default "default"	A theme name. Reserved for future use.

UserFlagHolders

Name	Type	Purpose
FlagUUID	Binary(16) Not Null	The UUID of the flag.
UserUUID	Binary(16) Not Null	The UUID of the user.
Added	Timestamp Not Null	When the right was added.

UserFlags

Name	Type	Purpose
FlagUUID	Binary(16) Not Null	The UUID of the flag.
FlagName	Varchar(15) Not Null	The short name of the flag. ("sa" and "vip" are used for now.)