Commit: 16bea4f84aeb39cda9dc3b647c07c4c4f2749433 Author: Peter Kokot <peterko...@gmail.com> Wed, 24 Oct 2018 19:51:47 +0200 Parents: a06f85b0c3b775ed60292b680e7ce47e0c001bbe Branches: master
Link: http://git.php.net/?p=web/bugs.git;a=commitdiff;h=16bea4f84aeb39cda9dc3b647c07c4c4f2749433 Log: Refactor SQL files This separates the SQL files according to its functionality: - database.sql for creating only database - schema.sql for creating only database schema - fixtures.sql for inserting only data fixtures This helps in building project from scratch with empty database with only schema to insert custom development fixtures. Changed paths: M README.md D sql/bugs.sql A sql/database.sql A sql/fixtures.sql D sql/phpbugsdb_create.sql A sql/schema.sql
diff --git a/README.md b/README.md index 97bdf58..20299a8 100644 --- a/README.md +++ b/README.md @@ -31,5 +31,5 @@ pear install --alldeps Text_CAPTCHA_Numeral Text_Diff HTTP_Upload-1.0.0b4 * Database: -Create a new database using `sql/phpbugsdb_create.sql` and insert fixtures using -`sql/bugs.sql`. +Create a new database using `sql/database.sql`, create database schema +`sql/schema.sql` and insert fixtures using `sql/fixtures.sql`. diff --git a/sql/bugs.sql b/sql/bugs.sql deleted file mode 100644 index 12f0b88..0000000 --- a/sql/bugs.sql +++ /dev/null @@ -1,120 +0,0 @@ --- ts1 bug created date --- ts2 bug last updated date --- passwd user password - -CREATE TABLE bugdb ( - id int(8) NOT NULL auto_increment, - package_name varchar(80) default NULL, - 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, - ts2 datetime default NULL, - assign varchar(20) default NULL, - passwd varchar(64) default NULL, - 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', - 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, - 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, - 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', - 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, # 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', - 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; - --- score's value can be 1 through 5 -CREATE TABLE bugdb_votes ( - bug int(8) NOT NULL default '0', - 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', - 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', - 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; - ---- Default pseudo packages (common for all projects) -INSERT INTO bugdb_pseudo_packages SET id = '1', parent = '0', name = 'Web Site', long_name = 'Web Site', project = ''; -INSERT INTO bugdb_pseudo_packages SET id = '2', parent = '1', name = 'Bug System', long_name = 'Bug System', project = ''; diff --git a/sql/database.sql b/sql/database.sql new file mode 100644 index 0000000..b5caf7a --- /dev/null +++ b/sql/database.sql @@ -0,0 +1,2 @@ +CREATE DATABASE phpbugsdb; +GRANT ALL PRIVILEGES on phpbugsdb.* to 'nobody'@'localhost'; diff --git a/sql/fixtures.sql b/sql/fixtures.sql new file mode 100644 index 0000000..c4f3bf7 --- /dev/null +++ b/sql/fixtures.sql @@ -0,0 +1,4 @@ +-- Default pseudo packages (common for all projects) + +INSERT INTO bugdb_pseudo_packages SET id = '1', parent = '0', name = 'Web Site', long_name = 'Web Site', project = ''; +INSERT INTO bugdb_pseudo_packages SET id = '2', parent = '1', name = 'Bug System', long_name = 'Bug System', project = ''; diff --git a/sql/phpbugsdb_create.sql b/sql/phpbugsdb_create.sql deleted file mode 100644 index b5caf7a..0000000 --- a/sql/phpbugsdb_create.sql +++ /dev/null @@ -1,2 +0,0 @@ -CREATE DATABASE phpbugsdb; -GRANT ALL PRIVILEGES on phpbugsdb.* to 'nobody'@'localhost'; diff --git a/sql/schema.sql b/sql/schema.sql new file mode 100644 index 0000000..f47075c --- /dev/null +++ b/sql/schema.sql @@ -0,0 +1,116 @@ +-- ts1 bug created date +-- ts2 bug last updated date +-- passwd user password + +CREATE TABLE bugdb ( + id int(8) NOT NULL auto_increment, + package_name varchar(80) default NULL, + 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, + ts2 datetime default NULL, + assign varchar(20) default NULL, + passwd varchar(64) default NULL, + 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', + 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, + 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, + 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', + 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, # 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', + 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; + +-- score's value can be 1 through 5 +CREATE TABLE bugdb_votes ( + bug int(8) NOT NULL default '0', + 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', + 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', + 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;
-- PHP Webmaster List Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
