[phpBT-dev] CVS: phpbt configure_db,NONE,1.1 createdb.sql.in,NONE,1.1

[Patrick M. <mo...@us...>]

Update of /cvsroot/phpbt/phpbt
In directory usw-pr-cvs1:/tmp/cvs-serv25668

Added Files:
	configure_db createdb.sql.in 
Log Message:
first try of an sql-template-system for dynamic table-names
createdb.sql.in is NOT functional, it's only an partially modified createdb.sql for demonstration purposes!


--- NEW FILE: configure_db ---
#!/bin/bash
# Script to generate database-creation-scripts
# $Id: configure_db,v 1.1 2001/09/07 13:15:37 mohni Exp $

# prefix for tablenames
#TBL_PREFIX="phpbt_"
TBL_PREFIX=""

#------------------------------------------------------------------------------
#parameters:
#  $TBL_PREFIX	prefix for tablenames
#  $1		filename for input
#  $2		filename for output
generate_sql() {
  cat $1 | sed "
    s/TBL_ATTACHMENT/${TBL_PREFIX}attachment/g
    s/TBL_BUG/${TBL_PREFIX}bug/g
    s/TBL_BUG_HISTORY/${TBL_PREFIX}bug_history/g
    s/TBL_COMMENT/${TBL_PREFIX}comment/g
    s/TBL_COMPONENT/${TBL_PREFIX}component/g
    s/TBL_PROJECT/${TBL_PREFIX}project/g
    s/TBL_RESOLUTION/${TBL_PREFIX}resolution/g
    s/TBL_SAVED_QUERY/${TBL_PREFIX}saved_query/g
    s/TBL_SEVERITY/${TBL_PREFIX}severity/g
    s/TBL_STATUS/${TBL_PREFIX}status/g
    s/TBL_AUTH_USER/${TBL_PREFIX}auth_user/g
    s/TBL_VERSION/${TBL_PREFIX}version/g
    s/TBL_OS/${TBL_PREFIX}os/g
    s/TBL_AUTH_GROUP/${TBL_PREFIX}auth_group/g
    s/TBL_AUTH_PERM/${TBL_PREFIX}auth_perm/g
    s/TBL_USER_GROUP/${TBL_PREFIX}user_group/g
    s/TBL_USER_PERM/${TBL_PREFIX}user_perm/g
    s/TBL_GROUP_PERM/${TBL_PREFIX}group_perm/g
    s/TBL_BUG_GROUP/${TBL_PREFIX}bug_group/g
    s/TBL_PROJECT_GROUP/${TBL_PREFIX}project_group/g
    s/^#\ TEMPLATE:.*$//
  " > $2
}

#------------------------------------------------------------------------------

#generate_sql createdb.sql.in createdb.sql
#generate_sql createdb_pg.sql.in createdb_pg.sql
generate_sql createdb.sql.in createdb.sql.out

--- NEW FILE: createdb.sql.in ---
# Database creation script (MySQL)
# If you change the database name, make sure you change it in dbclass in 
# include.php.  Make sure you edit the User insert below.
#
# TEMPLATE: This is a template for configure_db. Don't use it directly to create your database!

create database BugTracker;
use BugTracker;

#
# Table structure for table 'db_sequence'
#

DROP TABLE IF EXISTS db_sequence;
CREATE TABLE db_sequence (
  seq_name varchar(127) DEFAULT '' NOT NULL,
  nextid int(10) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (seq_name)
);

#
# Table structure for table 'User'
#

DROP TABLE IF EXISTS TBL_AUTH_USER;
CREATE TABLE TBL_AUTH_USER (
  user_id int(10) unsigned NOT NULL default '0',
  login char(40) NOT NULL default '',
  first_name char(40) NOT NULL default '',
  last_name char(40) NOT NULL default '',
  email char(60) NOT NULL default '',
  password char(40) NOT NULL default '',
  active tinyint(3) unsigned NOT NULL default '1',
  bug_list_fields char(255) NOT NULL default '',
  created_by int(10) unsigned NOT NULL default '0',
  created_date bigint(20) unsigned NOT NULL default '0',
  last_modified_by int(10) unsigned NOT NULL default '0',
  last_modified_date bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (user_id),
  UNIQUE login (login)
);

# -- EDIT THIS --
#insert into TBL_AUTH_USER values (1, 'System', 'Admin', 'ro...@yo...', 'somepassword', 15, unix_timestamp(now()));
insert into db_sequence values ('User', 1);

#
# Table structure for table 'Bug'
#

DROP TABLE IF EXISTS TBL_BUG;
CREATE TABLE TBL_BUG (
  BugID int(10) unsigned DEFAULT '0' NOT NULL,
  Title varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  URL varchar(255) DEFAULT '' NOT NULL,
  Severity tinyint(3) unsigned DEFAULT '0' NOT NULL,
  Priority tinyint(3) unsigned DEFAULT '0' NOT NULL,
  Status tinyint(3) unsigned DEFAULT '0' NOT NULL,
  Resolution tinyint(3) unsigned DEFAULT '0' NOT NULL,
  AssignedTo int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedBy int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  LastModifiedBy int(10) unsigned DEFAULT '0' NOT NULL,
  LastModifiedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  Project int(10) unsigned DEFAULT '0' NOT NULL,
  Version varchar(5) DEFAULT '' NOT NULL,
  Component int(10) unsigned DEFAULT '0' NOT NULL,
  OS tinyint(3) unsigned DEFAULT '0' NOT NULL,
  BrowserString varchar(255) DEFAULT '' NOT NULL,
  PRIMARY KEY (BugID)
);

#
# Table structure for table 'Comment'
#

DROP TABLE IF EXISTS TBL_COMMENT;
CREATE TABLE TBL_COMMENT (
  CommentID int(10) unsigned DEFAULT '0' NOT NULL,
  BugID int(10) unsigned DEFAULT '0' NOT NULL,
  Text text DEFAULT '' NOT NULL,
  CreatedBy int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (CommentID)
);

#
# Table structure for table 'Component'
#

DROP TABLE IF EXISTS TBL_COMPONENT;
CREATE TABLE TBL_COMPONENT (
  ComponentID int(10) unsigned DEFAULT '0' NOT NULL,
  ProjectID int(10) unsigned DEFAULT '0' NOT NULL,
  Name varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  Owner int(10) unsigned DEFAULT '0' NOT NULL,
  Active char(1) binary DEFAULT '1' NOT NULL,
  CreatedBy int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  LastModifiedBy int(10) unsigned DEFAULT '0' NOT NULL,
  LastModifiedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (ComponentID)
);

#
# Table structure for table 'OS'
#

DROP TABLE IF EXISTS TBL_OS;
CREATE TABLE TBL_OS (
  OSID int(10) unsigned DEFAULT '0' NOT NULL,
  Name char(30) DEFAULT '' NOT NULL,
  SortOrder tinyint(3) unsigned DEFAULT '0' NOT NULL,
  Regex char(40) DEFAULT '' NOT NULL,
  PRIMARY KEY (OSID)
);

#
# Dumping data for table 'OS'
#

INSERT INTO TBL_OS VALUES (1,'All',1,'');
INSERT INTO TBL_OS VALUES (2,'Windows 3.1',2,'/Mozilla.*\\(Win16.*\\)/');
INSERT INTO TBL_OS VALUES (3,'Windows 95',3,'/Mozilla.*\\(.*;.*; 32bit.*\\)/');
INSERT INTO TBL_OS VALUES (4,'Windows 98',4,'/Mozilla.*\\(Win98.*\\)/');
INSERT INTO TBL_OS VALUES (5,'Windows ME',5,'');
INSERT INTO TBL_OS VALUES (6,'Windows 2000',6,'/Mozilla.*Windows NT 5.*\\)/');
INSERT INTO TBL_OS VALUES (7,'Windows NT',7,'/Mozilla.*\\(Windows.*NT/');
INSERT INTO TBL_OS VALUES (8,'Mac System 7',8,'');
INSERT INTO TBL_OS VALUES (9,'Mac System 7.5',9,'');
INSERT INTO TBL_OS VALUES (10,'Mac System 7.6.1',10,'');
INSERT INTO TBL_OS VALUES (11,'Mac System 8.0',11,'');
INSERT INTO TBL_OS VALUES (12,'Mac System 8.5',12,'/Mozilla.*\\(.*;.*; 68K.*\\)/');
INSERT INTO TBL_OS VALUES (13,'Mac System 8.6',13,'/Mozilla.*\\(.*;.*; PPC.*\\)/');
INSERT INTO TBL_OS VALUES (14,'Mac System 9.0',14,'');
INSERT INTO TBL_OS VALUES (15,'Mac OS X',15,'');
INSERT INTO TBL_OS VALUES (16,'Linux',16,'/Mozilla.*\\(.*;.*; Linux.*\\)/');
INSERT INTO TBL_OS VALUES (17,'BSDI',17,'/Mozilla.*\\(.*;.*; BSD\\/OS.*\\)/');
INSERT INTO TBL_OS VALUES (18,'FreeBSD',18,'/Mozilla.*\\(.*;.*; FreeBSD.*\\)/');
INSERT INTO TBL_OS VALUES (19,'NetBSD',19,'');
INSERT INTO TBL_OS VALUES (20,'OpenBSD',20,'');
INSERT INTO TBL_OS VALUES (21,'AIX',21,'/Mozilla.*\\(.*;.*; AIX.*\\)/');
INSERT INTO TBL_OS VALUES (22,'BeOS',22,'');
INSERT INTO TBL_OS VALUES (23,'HP-UX',23,'/Mozilla.*\\(.*;.*; HP-UX.*\\)/');
INSERT INTO TBL_OS VALUES (24,'IRIX',24,'/Mozilla.*\\(.*;.*; IRIX.*\\)/');
INSERT INTO TBL_OS VALUES (25,'Neutrino',25,'');
INSERT INTO TBL_OS VALUES (26,'OpenVMS',26,'');
INSERT INTO TBL_OS VALUES (27,'OS/2',27,'');
INSERT INTO TBL_OS VALUES (28,'OSF/1',28,'/Mozilla.*\\(.*;.*; OSF.*\\)/');
INSERT INTO TBL_OS VALUES (29,'Solaris',29,'/Mozilla.*\\(.*;.*; SunOS 5.*\\)/');
INSERT INTO TBL_OS VALUES (30,'SunOS',30,'/Mozilla.*\\(.*;.*; SunOS.*\\)/');
INSERT INTO TBL_OS VALUES (31,'other',31,'');

insert into db_sequence values ('OS', 31);

#
# Table structure for table 'Project'
#

DROP TABLE IF EXISTS Project;
CREATE TABLE Project (
  ProjectID int(10) unsigned DEFAULT '0' NOT NULL,
  Name varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  Active char(1) binary DEFAULT '1' NOT NULL,
  CreatedBy int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (ProjectID)
);

#
# Table structure for table 'Resolution'
#

DROP TABLE IF EXISTS Resolution;
CREATE TABLE Resolution (
  ResolutionID int(10) unsigned DEFAULT '0' NOT NULL,
  Name varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  SortOrder tinyint(3) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (ResolutionID)
);

#
# Dumping data for table 'Resolution'
#

INSERT INTO Resolution VALUES (1,'Fixed','Bug was eliminated',1);
INSERT INTO Resolution VALUES (2,'Not a bug','It\'s not a bug -- it\'s a feature!',2);
INSERT INTO Resolution VALUES (3,'Won\'t Fix','This bug will stay',3);
INSERT INTO Resolution VALUES (4,'Deferred','We\'ll get around to it later',4);
INSERT INTO Resolution VALUES (5,'Works for me','Can\'t replicate the bug',5);
INSERT INTO Resolution VALUES (6,'Duplicate','',6);

insert into db_sequence values ('Resolution', 6);

#
# Table structure for table 'SavedQuery'
#

DROP TABLE IF EXISTS SavedQuery;
CREATE TABLE SavedQuery (
   SavedQueryID int(10) unsigned NOT NULL auto_increment,
   UserID int(10) unsigned DEFAULT '0' NOT NULL,
   SavedQueryName varchar(40) NOT NULL,
   SavedQueryString text NOT NULL,
   PRIMARY KEY (SavedQueryID, UserID)
);

#
# Table structure for table 'Severity'
#

DROP TABLE IF EXISTS Severity;
CREATE TABLE Severity (
  SeverityID int(10) unsigned DEFAULT '0' NOT NULL,
  Name varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  SortOrder tinyint(3) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (SeverityID)
);

#
# Dumping data for table 'Severity'
#

INSERT INTO Severity VALUES (1,'Unassigned','Default bug creation',1);
INSERT INTO Severity VALUES (2,'Idea','Ideas for further development',2);
INSERT INTO Severity VALUES (3,'Feature Request','Requests for specific features',3);
INSERT INTO Severity VALUES (4,'Annoyance','Cosmetic problems or bugs not affecting performance',4);
INSERT INTO Severity VALUES (5,'Content','Non-functional related bugs, such as text content',5);
INSERT INTO Severity VALUES (6,'Significant','A bug affecting the intended performance of the product',6);
INSERT INTO Severity VALUES (7,'Critical','A bug severe enough to prevent the release of the product',7);

insert into db_sequence values ('Severity', 7);

#
# Table structure for table 'Status'
#

DROP TABLE IF EXISTS Status;
CREATE TABLE Status (
  StatusID int(10) unsigned DEFAULT '0' NOT NULL,
  Name varchar(30) DEFAULT '' NOT NULL,
  Description text DEFAULT '' NOT NULL,
  SortOrder tinyint(3) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (StatusID)
);

#
# Dumping data for table 'Status'
#

INSERT INTO Status VALUES (1,'Unconfirmed','Reported but not confirmed',1);
INSERT INTO Status VALUES (2,'New','A new bug',2);
INSERT INTO Status VALUES (3,'Assigned','Assigned to a developer',3);
INSERT INTO Status VALUES (4,'Reopened','Closed but opened again for further inspection',4);
INSERT INTO Status VALUES (5,'Resolved','Set by engineer with a resolution',5);
INSERT INTO Status VALUES (6,'Verified','The resolution is confirmed by the reporter',6);
INSERT INTO Status VALUES (7,'Closed','The bug is officially squashed (QA)',7);

insert into db_sequence values ('Status', 7);

#
# Table structure for table 'Version'
#

DROP TABLE IF EXISTS Version;
CREATE TABLE Version (
  VersionID int(10) unsigned DEFAULT '0' NOT NULL,
  ProjectID int(10) unsigned DEFAULT '0' NOT NULL,
  Name char(10) DEFAULT '' NOT NULL,
  Active char(1) binary DEFAULT '' NOT NULL,
  CreatedBy int(10) unsigned DEFAULT '0' NOT NULL,
  CreatedDate bigint(20) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (VersionID)
);

#
# Table structure for table 'active_sessions'
#

DROP TABLE IF EXISTS active_sessions;
CREATE TABLE active_sessions (
  sid varchar(32) DEFAULT '' NOT NULL,
  name varchar(32) DEFAULT '' NOT NULL,
  val text,
  changed varchar(14) DEFAULT '' NOT NULL,
  PRIMARY KEY (name,sid),
  KEY changed (changed)
);

CREATE TABLE BugHistory (
  BugID int(10) unsigned NOT NULL default '0',
  ChangedField char(20) NOT NULL default '',
  OldValue char(255) NOT NULL default '',
  NewValue char(255) NOT NULL default '',
  CreatedBy int(10) unsigned NOT NULL default '0',
  CreatedDate bigint(20) unsigned NOT NULL default '0'
);

CREATE TABLE TBL_ATTACHMENT (
  attachment_id int(10) unsigned NOT NULL default '0',
  bug_id int(10) unsigned NOT NULL default '0',
  file_name char(255) NOT NULL default '',
  description char(255) NOT NULL default '',
  file_size bigint(20) unsigned NOT NULL default '0',
  mime_type char(30) NOT NULL default '',
  created_by int(10) unsigned NOT NULL default '0',
  created_date bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (attachment_id)
);