diff options
Diffstat (limited to 'demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql')
| -rw-r--r-- | demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql | 317 |
1 files changed, 204 insertions, 113 deletions
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql index bb120fc4..91481efe 100644 --- a/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql +++ b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql @@ -1,136 +1,227 @@ -DROP TABLE IF EXISTS user_roles;
-CREATE TABLE user_roles (
- UserID varchar(50) NOT NULL,
- RoleType varchar(50) NOT NULL,
- PRIMARY KEY (UserID,RoleType),
- KEY RoleType (RoleType)
+--
+-- Database: `time-tracker`
+--
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `categories`
+--
+
+CREATE TABLE IF NOT EXISTS `categories` (
+ `CategoryID` int(11) NOT NULL auto_increment,
+ `Name` varchar(255) NOT NULL,
+ `ProjectID` int(11) NOT NULL,
+ `ParentCategoryID` int(11) default '0',
+ `Abbreviation` varchar(255) default NULL,
+ `EstimateDuration` float(10,2) default '0.00',
+ PRIMARY KEY (`CategoryID`),
+ UNIQUE KEY `UniqueNamePerProject` (`Name`,`ProjectID`),
+ KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS role_types;
-CREATE TABLE role_types (
- RoleType varchar(50) NOT NULL,
- Description varchar(255) NOT NULL,
- PRIMARY KEY (RoleType)
+--
+-- Dumping data for table `categories`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `project`
+--
+
+CREATE TABLE IF NOT EXISTS `project` (
+ `ProjectID` int(11) NOT NULL auto_increment,
+ `Name` varchar(255) NOT NULL,
+ `Description` varchar(255) default NULL,
+ `CreationDate` datetime NOT NULL,
+ `CompletionDate` datetime NOT NULL,
+ `Disabled` tinyint(1) NOT NULL default '0',
+ `EstimateDuration` float(10,2) NOT NULL default '0.00',
+ `CreatorID` varchar(50) NOT NULL,
+ `ManagerID` varchar(50) default NULL,
+ PRIMARY KEY (`ProjectID`),
+ KEY `Name` (`Name`),
+ KEY `CreatorID` (`CreatorID`),
+ KEY `ManagerID` (`ManagerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS project_members;
-CREATE TABLE project_members (
- UserID varchar(50) NOT NULL,
- ProjectID int(11) NOT NULL,
- PRIMARY KEY (UserID,ProjectID),
- KEY ProjectID (ProjectID)
+--
+-- Dumping data for table `project`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `project_members`
+--
+
+CREATE TABLE IF NOT EXISTS `project_members` (
+ `UserID` varchar(50) NOT NULL,
+ `ProjectID` int(11) NOT NULL,
+ PRIMARY KEY (`UserID`,`ProjectID`),
+ KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS time_entry;
-CREATE TABLE time_entry (
- EntryID int(11) NOT NULL auto_increment,
- EntryCreated datetime NOT NULL,
- Duration float(10,2) NOT NULL default '0.00',
- Description varchar(1000) default NULL,
- CategoryID int(11) NOT NULL default '0',
- EntryDate datetime default NULL,
- CreatorID varchar(50) NOT NULL,
- UserID varchar(50) NOT NULL,
- PRIMARY KEY (EntryID),
- KEY CategoryID (CategoryID),
- KEY CreatorID (CreatorID),
- KEY UserID (UserID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
-DROP TABLE IF EXISTS signon;
-CREATE TABLE signon (
- SessionToken varchar(32) NOT NULL,
- Username varchar(50) NOT NULL,
- LastSignOnDate datetime NOT NULL,
- PRIMARY KEY (SessionToken),
- KEY Username (Username)
+--
+-- Dumping data for table `project_members`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `role_types`
+--
+
+CREATE TABLE IF NOT EXISTS `role_types` (
+ `RoleType` varchar(50) NOT NULL,
+ `Description` varchar(255) NOT NULL,
+ PRIMARY KEY (`RoleType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS categories;
-CREATE TABLE categories (
- CategoryID int(11) NOT NULL auto_increment,
- `Name` varchar(255) NOT NULL,
- ProjectID int(11) NOT NULL,
- ParentCategoryID int(11) default '0',
- Abbreviation varchar(255) default NULL,
- EstimateDuration float(10,2) default '0.00',
- PRIMARY KEY (CategoryID),
- UNIQUE KEY UniqueNamePerProject (`Name`,ProjectID),
- KEY ProjectID (ProjectID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
-DROP TABLE IF EXISTS project;
-CREATE TABLE project (
- ProjectID int(11) NOT NULL auto_increment,
- `Name` varchar(255) NOT NULL,
- Description varchar(255) default NULL,
- CreationDate datetime NOT NULL,
- CompletionDate datetime NOT NULL,
- Disabled tinyint(1) NOT NULL default '0',
- EstimateDuration float(10,2) NOT NULL default '0.00',
- CreatorID varchar(50) NOT NULL,
- ManagerID varchar(50) default NULL,
- PRIMARY KEY (ProjectID),
- KEY `Name` (`Name`),
- KEY CreatorID (CreatorID),
- KEY ManagerID (ManagerID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+--
+-- Dumping data for table `role_types`
+--
+
+INSERT INTO `role_types` (`RoleType`, `Description`) VALUES ('admin', 'Project administrator may additionally view the list of all users.'),
+('consultant', 'Consultant may log time entries only.'),
+('manager', 'Project manager may additionally edit all projects and view reports.');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `signon`
+--
+
+CREATE TABLE IF NOT EXISTS `signon` (
+ `SessionToken` varchar(32) NOT NULL,
+ `Username` varchar(50) NOT NULL,
+ `LastSignOnDate` datetime NOT NULL,
+ PRIMARY KEY (`SessionToken`),
+ KEY `Username` (`Username`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `signon`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `time_entry`
+--
+
+CREATE TABLE IF NOT EXISTS `time_entry` (
+ `EntryID` int(11) NOT NULL auto_increment,
+ `EntryCreated` datetime NOT NULL,
+ `Duration` float(10,2) NOT NULL default '0.00',
+ `Description` varchar(1000) default NULL,
+ `CategoryID` int(11) NOT NULL default '0',
+ `EntryDate` datetime default NULL,
+ `CreatorID` varchar(50) NOT NULL,
+ `UserID` varchar(50) NOT NULL,
+ PRIMARY KEY (`EntryID`),
+ KEY `CategoryID` (`CategoryID`),
+ KEY `CreatorID` (`CreatorID`),
+ KEY `UserID` (`UserID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `time_entry`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `user_roles`
+--
+
+CREATE TABLE IF NOT EXISTS `user_roles` (
+ `UserID` varchar(50) NOT NULL,
+ `RoleType` varchar(50) NOT NULL,
+ PRIMARY KEY (`UserID`,`RoleType`),
+ KEY `RoleType` (`RoleType`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `user_roles`
+--
+
+INSERT INTO `user_roles` (`UserID`, `RoleType`) VALUES ('admin', 'admin'),
+('admin', 'consultant'),
+('consultant', 'consultant'),
+('manager', 'consultant'),
+('admin', 'manager'),
+('manager', 'manager');
-DROP TABLE IF EXISTS users;
-CREATE TABLE users (
- Username varchar(50) NOT NULL,
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `users`
+--
+
+CREATE TABLE IF NOT EXISTS `users` (
+ `Username` varchar(50) NOT NULL,
`Password` varchar(50) NOT NULL,
- EmailAddress varchar(100) NOT NULL,
- Disabled tinyint(1) NOT NULL default '0',
- PRIMARY KEY (Username)
+ `EmailAddress` varchar(100) NOT NULL,
+ `Disabled` tinyint(1) NOT NULL default '0',
+ PRIMARY KEY (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Dumping data for table `users`
+--
+
+INSERT INTO `users` (`Username`, `Password`, `EmailAddress`, `Disabled`) VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@pradosoft.com', 0),
+('consultant', '7adfa4f2ba9323e6c1e024de375434b0', 'consultant@pradosoft.com', 0),
+('manager', '1d0258c2440a8d19e716292b231e3190', 'manager@pradosoft.com', 0);
+
+--
+-- Constraints for dumped tables
+--
+
+--
+-- Constraints for table `categories`
+--
ALTER TABLE `categories`
- ADD CONSTRAINT categories_ibfk_1
- FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
+ ADD CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE;
+--
+-- Constraints for table `project`
+--
ALTER TABLE `project`
- ADD CONSTRAINT project_ibfk_2
- FOREIGN KEY (ManagerID) REFERENCES users (Username),
- ADD CONSTRAINT project_ibfk_1
- FOREIGN KEY (CreatorID) REFERENCES users (Username);
+ ADD CONSTRAINT `project_ibfk_6` FOREIGN KEY (`ManagerID`) REFERENCES `users` (`Username`),
+ ADD CONSTRAINT `project_ibfk_5` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `project_members`
+--
ALTER TABLE `project_members`
- ADD CONSTRAINT project_members_ibfk_1
- FOREIGN KEY (UserID) REFERENCES users (Username),
- ADD CONSTRAINT project_members_ibfk_2
- FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
+ ADD CONSTRAINT `project_members_ibfk_6` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `project_members_ibfk_5` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`) ON DELETE CASCADE;
+--
+-- Constraints for table `signon`
+--
ALTER TABLE `signon`
- ADD CONSTRAINT signon_ibfk_1
- FOREIGN KEY (Username) REFERENCES users (Username);
+ ADD CONSTRAINT `signon_ibfk_1` FOREIGN KEY (`Username`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `time_entry`
+--
ALTER TABLE `time_entry`
- ADD CONSTRAINT time_entry_ibfk_2
- FOREIGN KEY (UserID) REFERENCES users (Username),
- ADD CONSTRAINT time_entry_ibfk_1
- FOREIGN KEY (CategoryID) REFERENCES categories (CategoryID);
+ ADD CONSTRAINT `time_entry_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`),
+ ADD CONSTRAINT `time_entry_ibfk_6` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `time_entry_ibfk_7` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `user_roles`
+--
ALTER TABLE `user_roles`
- ADD CONSTRAINT user_roles_ibfk_2
- FOREIGN KEY (RoleType) REFERENCES role_types (RoleType),
- ADD CONSTRAINT user_roles_ibfk_1
- FOREIGN KEY (UserID) REFERENCES users (Username);
-
-INSERT INTO role_types (RoleType, Description) VALUES
-('admin', 'Project administrator may additionally view the list of all users.'),
-('consultant', 'Consultant may log time entries only.'),
-('manager', 'Project manager may additionally edit all projects and view reports.');
-
-INSERT INTO users (Username, Password, EmailAddress, Disabled) VALUES
-('admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@pradosoft.com', 0),
-('manager', '1d0258c2440a8d19e716292b231e3190', 'manager@pradosoft.com', 0),
-('consultant', '7adfa4f2ba9323e6c1e024de375434b0', 'consultant@pradosoft.com', 0);
-
-INSERT INTO user_roles (UserID, RoleType) VALUES
-('admin', 'admin'),
-('admin', 'manager'),
-('admin', 'consultant'),
-('manager', 'manager'),
-('manager', 'consultant'),
-('consultant', 'consultant');
\ No newline at end of file + ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`RoleType`) REFERENCES `role_types` (`RoleType`),
+ ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`);
|