D 2015-04-20T23:08:21.763
L database\sscheme
N text/x-markdown
P 04c2bfcae9a0545340f32591d99c14b09ad3212d
U mario
W 2345
freshcode.club currently uses [SQLite](http://www.sqlite.org/) (and the VCS just so happens to be [sqlite-based fossil](http://www.fossil-scm.org/)). 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.
Z eb6d4e421ca2166ae5387c262dff6443