⌈⌋ branch:  freshcode

Artifact [7b651be9ab]

Artifact 7b651be9ab0215382e2b4527177743a84dbaaf27:

  • File db.sql — part of check-in [2e1e35664b] at 2014-06-25 06:10:43 on branch trunk — Initial website implementation: project submit, index listing, projects/ page, and moderation flags. (user: mario size: 1003)

# title: freshcode database schema
# version: 0.2

CREATE TABLE [release] ([name] VARCHAR (100) NOT NULL, [title] TEXT NOT
  NULL, [homepage] TEXT, [description] TEXT NOT NULL, [license] VARCHAR (100),
  [tags] VARCHAR (200), [version] VARCHAR (100) NOT NULL, [state] VARCHAR
  (20), [scope] VARCHAR (20), [changes] TEXT, [download] TEXT, [urls] TEXT,
  [autoupdate_module] VARCHAR (20), [autoupdate_url] TEXT, [autoupdate_regex]
  TEXT, [t_published] INT, [t_changed] INT, [flag] INT DEFAULT(0), [deleted]
  BOOLEAN DEFAULT(0), [submitter_openid] TEXT, [submitter] VARCHAR (0, 50),
  [lock] TEXT, [hidden] BOOLEAN DEFAULT(0), [image] TEXT);

CREATE INDEX idx_release ON [release] ( name , version COLLATE NOCASE ,
  t_changed DESC );

CREATE VIEW [release_view] AS SELECT * FROM [release] WHERE NOT deleted AND
  NOT hidden AND flag < 5 GROUP BY version , t_changed ORDER BY t_published

CREATE TABLE flags (name TEXT, reason TEXT, note TEXT, submitter_openid
  TEXT, submitter_ip TEXT);