⌈⌋ branch:  freshcode


Update of "database scheme"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: 1d7bc98d39431ee3e569efbf040e7b95a20f7f40
Page Name:database scheme
Date: 2015-04-20 23:08:21
Original User: mario
Mimetype:text/x-markdown
Parent: 04c2bfcae9a0545340f32591d99c14b09ad3212d
Content

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.