⌈⌋ branch:  freshcode

database scheme

freshcode.club currently uses SQLite (and the VCS just so happens to be sqlite-based fossil). That seems entirely sufficient for the prototype, for mirror sites, possibly even for the foreseeable future. (FM had, if I may say so, a peewee database of just 20MB.)

The scheme is almost self-explanatory. Most submission fields end up in their according column:

CREATE TABLE [release] (
     -- General project description
     [name] VARCHAR (100) NOT NULL,
     [title] TEXT NOT NULL,
     [summary] VARCHAR (160),
     [homepage] TEXT,
     [description] TEXT NOT NULL,
     [license] VARCHAR (100),
     [tags] VARCHAR (200),
     [image] TEXT,
     -- Per-release entries
     [version] VARCHAR (100) NOT NULL,
     [state] VARCHAR (20),
     [scope] VARCHAR (20),
     [changes] TEXT,
     [download] TEXT,
     [urls] TEXT,
     [submitter] VARCHAR (0, 50),
     [submitter_image] TEXT,
     [submitter_openid] TEXT,
     -- Autoupdate fields
     [autoupdate_module] VARCHAR (20),
     [autoupdate_url] TEXT,
     [autoupdate_regex] TEXT,
     [autoupdate_delay] FLOAT,
     -- Control meta
     [t_published] INT,
     [t_changed] INT,
     [flag] INT DEFAULT(0),
     [deleted] BOOLEAN DEFAULT(0),
     [hidden] BOOLEAN DEFAULT(0),
     [lock] TEXT,
     [via] TEXT,            -- "form" or "api" or "autoupdate"
     [social_links] INT,    -- counter
     [editor_note] TEXT,    -- custom notification to submitter
     -- Scheme
     CONSTRAINT 'release_revisions' UNIQUE
     ( name, version COLLATE 'NOCASE', t_published, t_changed )

One interesting point is that this database scheme is implicitly versioned. The t_published timestamp notes the first time a version release got added. While subsequent t_changed entrys add revisions onto each version/t_published.

Originally I also planned to separate general project and release data into two tables. But it's much simpler to manage this way. And implicitly revisions the general project infos along. Which is another reason we don't need a full moderation system; there's always a full history anyway.