web-bugs/sql/schema.sql at 4e5b457bea03bc2d015632becab8dca5bc35f056 · php/web-bugs · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
CREATE TABLE bugdb (
  id int(8) NOT NULL auto_increment,
  package_name varchar(80) default NULL COMMENT 'see also bugdb_pseudo_packages',
  bug_type varchar(32) NOT NULL default 'Bug',
  email varchar(40) NOT NULL default '',
  reporter_name varchar(80) default '',
  sdesc varchar(80) NOT NULL default '',
  ldesc text NOT NULL,
  php_version varchar(100) default NULL,
  php_os varchar(32) default NULL,
  status varchar(16) default NULL,
  ts1 datetime default NULL COMMENT 'created',
  ts2 datetime default NULL COMMENT 'last updated',
  assign varchar(20) default NULL,
  passwd varchar(64) default NULL COMMENT 'hashed bug author password',
  registered tinyint(1) NOT NULL default 0,
  block_user_comment char(1) default 'N',
  cve_id varchar(15) default NULL,
  private char(1) default 'N',
  visitor_ip varbinary(16) NOT NULL,
  PRIMARY KEY (id),
  KEY php_version (php_version(1)),
  KEY status (status),
  KEY package_name (package_name),
  FULLTEXT KEY email (email,sdesc,ldesc)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

CREATE TABLE bugdb_comments (
  id int(8) NOT NULL auto_increment,
  bug int(8) NOT NULL default 0 COMMENT 'bugdb.id',
  email varchar(40) NOT NULL default '',
  reporter_name varchar(80) default '',
  ts datetime NOT NULL default CURRENT_TIMESTAMP,
  comment text NOT NULL,
  comment_type varchar(10) default 'comment',
  visitor_ip varbinary(16) NOT NULL,
  PRIMARY KEY (id),
  KEY bug (bug,id,ts),
  FULLTEXT KEY comment (comment)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

CREATE TABLE bugdb_obsoletes_patches (
  bugdb_id int(8) NOT NULL COMMENT 'bugdb.id',
  patch varchar(80) NOT NULL,
  revision int(8) NOT NULL,
  obsolete_patch varchar(80) NOT NULL,
  obsolete_revision int(8) NOT NULL,
  PRIMARY KEY (bugdb_id,patch,revision,obsolete_patch,obsolete_revision)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_patchtracker (
  bugdb_id int(8) NOT NULL COMMENT 'bugdb.id',
  patch varchar(80) NOT NULL,
  revision int(8) NOT NULL,
  developer varchar(40) NOT NULL,
  PRIMARY KEY (bugdb_id,patch,revision)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_pseudo_packages (
  id int(11) NOT NULL auto_increment,
  parent int(11) NOT NULL default 0 COMMENT 'bugdb_pseudo_packages.id',
  name varchar(80) NOT NULL default '',
  long_name varchar(100) NOT NULL default '',
  project varchar(40) NOT NULL default '',
  list_email varchar(80) NOT NULL default '',
  disabled tinyint(1) NOT NULL default 0 COMMENT 'disabled == read-only (no new reports in these!)',
  PRIMARY KEY (id),
  UNIQUE KEY (name, project)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_resolves (
  id int(11) NOT NULL auto_increment,
  name varchar(100) NOT NULL,
  status varchar(16) default NULL,
  title varchar(100) NOT NULL,
  message text NOT NULL,
  project varchar(40) NOT NULL default '',
  package_name varchar(80) default NULL,
  webonly tinyint(1) NOT NULL default 0,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_subscribe (
  bug_id int(8) NOT NULL default 0 COMMENT 'bugdb.id',
  email varchar(40) NOT NULL default '',
  unsubscribe_date int(11) default NULL,
  unsubscribe_hash varchar(80) default '',
  PRIMARY KEY (bug_id,email),
  KEY unsubscribe_hash (unsubscribe_hash)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_votes (
  bug int(8) NOT NULL default 0 COMMENT 'bugdb.id',
  ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  ip int(10) unsigned NOT NULL default 0,
  score int(3) NOT NULL default 0 COMMENT 'can be 1 through 5',
  reproduced int(1) NOT NULL default 0,
  tried int(1) NOT NULL default 0,
  sameos int(1) default NULL,
  samever int(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE bugdb_pulls (
  bugdb_id int(8) NOT NULL default 0 COMMENT 'bugdb.id',
  github_repo varchar(255) NOT NULL,
  github_pull_id int NOT NULL,
  github_title varchar(255) NOT NULL,
  developer varchar(40) NOT NULL,
  github_html_url varchar(255) NOT NULL,
  PRIMARY KEY (bugdb_id, github_repo, github_pull_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;