From 623447ffea7a49359c773a0bc3a851397885f319 Mon Sep 17 00:00:00 2001
From: wei <>
Date: Fri, 28 Jul 2006 12:32:01 +0000
Subject: Add sqlite support for time-tracker.
---
.../protected/App_Data/DateTimeTypeHandler.php | 33 +++++
.../protected/App_Data/MySQL4/category.xml | 93 ++++++++++++
.../protected/App_Data/MySQL4/mysql-reset.sql | 26 ++++
.../protected/App_Data/MySQL4/projects.xml | 156 +++++++++++++++++++++
.../protected/App_Data/MySQL4/reports.xml | 87 ++++++++++++
.../protected/App_Data/MySQL4/time-entry.xml | 93 ++++++++++++
.../App_Data/MySQL4/time-tracker-mysql.sql | 135 ++++++++++++++++++
.../protected/App_Data/MySQL4/users.xml | 137 ++++++++++++++++++
.../App_Data/TimeTrackerUserTypeHandler.php | 54 +++++++
.../protected/App_Data/mysql-reset.sql | 26 ----
.../protected/App_Data/mysql-sqlmap.xml | 27 ----
.../protected/App_Data/mysql4-sqlmap.xml | 29 ++++
demos/time-tracker/protected/App_Data/project.xml | 5 -
.../protected/App_Data/sqlite-sqlmap.xml | 26 ++++
.../protected/App_Data/time-tracker-mysql.sql | 135 ------------------
.../protected/App_Data/time_tracker.db | Bin 17408 -> 0 bytes
16 files changed, 869 insertions(+), 193 deletions(-)
create mode 100644 demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/category.xml
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/projects.xml
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/reports.xml
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql
create mode 100644 demos/time-tracker/protected/App_Data/MySQL4/users.xml
create mode 100644 demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php
delete mode 100644 demos/time-tracker/protected/App_Data/mysql-reset.sql
delete mode 100644 demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
create mode 100644 demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
delete mode 100644 demos/time-tracker/protected/App_Data/project.xml
create mode 100644 demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml
delete mode 100644 demos/time-tracker/protected/App_Data/time-tracker-mysql.sql
delete mode 100644 demos/time-tracker/protected/App_Data/time_tracker.db
(limited to 'demos/time-tracker/protected/App_Data')
diff --git a/demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php b/demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php
new file mode 100644
index 00000000..17f3e14b
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php
@@ -0,0 +1,33 @@
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/category.xml b/demos/time-tracker/protected/App_Data/MySQL4/category.xml
new file mode 100644
index 00000000..e9248e6d
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/category.xml
@@ -0,0 +1,93 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+ INSERT INTO categories
+ (Name, ProjectID, Abbreviation, EstimateDuration)
+ VALUES(#Name#, #ProjectID#, #Abbreviation#, #EstimateDuration#)
+
+ select LAST_INSERT_ID() as value
+
+
+
+
+
+
+
+
+ DELETE FROM categories WHERE CategoryID = #value#
+
+
+
+
+
+
+
+ UPDATE categories SET
+ Abbreviation = #Abbreviation#,
+ EstimateDuration = #EstimateDuration#,
+ Name = #Name#,
+ ProjectId = #ProjectID#
+ WHERE
+ CategoryID = #ID#
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql b/demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql
new file mode 100644
index 00000000..e9581952
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql
@@ -0,0 +1,26 @@
+TRUNCATE `user_roles`;
+TRUNCATE `role_types`;
+TRUNCATE `project_members`;
+TRUNCATE `time_entry`;
+TRUNCATE `signon`;
+TRUNCATE `categories`;
+TRUNCATE `project`;
+TRUNCATE `users`;
+
+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
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml
new file mode 100644
index 00000000..099e7fef
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml
@@ -0,0 +1,156 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ INSERT INTO project
+ (Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
+ VALUES
+ (#Name#, #Description#, NOW(), #EstimateDuration#,
+ #CompletionDate, typeHandler=DateTime#,
+ #CreatorUserName#, #ManagerUserName#)
+
+ select LAST_INSERT_ID() as value
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ UPDATE project SET Disabled = 1 WHERE ProjectID = #value#
+
+
+
+
+
+ INSERT INTO project_members (UserID, ProjectID)
+ VALUES(#username#, #project#)
+
+
+
+ DELETE FROM project_members WHERE ProjectID = #project# AND UserID = #username#
+
+
+
+ UPDATE project
+ SET
+ CompletionDate = #CompletionDate, typeHandler=DateTime#,
+ Description = #Description#,
+ EstimateDuration = #EstimateDuration#,
+ ManagerId =#ManagerUserName#,
+ Name = #Name#
+ WHERE
+ ProjectID = #ID#
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/reports.xml b/demos/time-tracker/protected/App_Data/MySQL4/reports.xml
new file mode 100644
index 00000000..4b537708
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/reports.xml
@@ -0,0 +1,87 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml b/demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml
new file mode 100644
index 00000000..4838e625
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml
@@ -0,0 +1,93 @@
+
+
+
+
+ INSERT INTO time_entry(
+ EntryCreated,
+ Duration,
+ Description,
+ CategoryID,
+ EntryDate,
+ CreatorID,
+ UserID
+ )
+ VALUES(
+ NOW(),
+ #Duration#,
+ #Description#,
+ #Category.ID#,
+ #ReportDate, typeHandler=DateTime#,
+ #CreatorUserName#,
+ #Username#
+ )
+
+ select LAST_INSERT_ID() as value
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ DELETE FROM time_entry WHERE EntryID = #value#
+
+
+
+
+
+ UPDATE time_entry SET
+ Duration = #Duration#,
+ Description = #Description#,
+ CategoryID = #Category.ID#,
+ EntryDate = #ReportDate, typeHandler=DateTime#,
+ UserID = #Username#
+ WHERE
+ EntryID = #ID#
+
+
+
\ No newline at end of file
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
new file mode 100644
index 00000000..89de378a
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql
@@ -0,0 +1,135 @@
+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)
+) 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)
+) 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)
+) 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)
+) 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,
+ 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 ;
+
+DROP TABLE IF EXISTS users;
+CREATE TABLE 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+ALTER TABLE `categories`
+ ADD CONSTRAINT categories_ibfk_1
+ FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
+
+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);
+
+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);
+
+ALTER TABLE `signon`
+ ADD CONSTRAINT signon_ibfk_1
+ FOREIGN KEY (Username) REFERENCES users (Username);
+
+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);
+
+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
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/users.xml b/demos/time-tracker/protected/App_Data/MySQL4/users.xml
new file mode 100644
index 00000000..6053c7ea
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/MySQL4/users.xml
@@ -0,0 +1,137 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ INSERT INTO
+ users (Username, Password, EmailAddress)
+ VALUES
+ (#user.Name#, #password#, #user.EmailAddress#)
+
+
+
+ UPDATE users SET Disabled = 1 WHERE username = #value#
+
+
+
+ INSERT INTO
+ signon (SessionToken, Username, LastSignOnDate)
+ VALUES
+ (#token#, #username#, NOW())
+
+
+
+
+
+ UPDATE signon SET LastSignOnDate = NOW()
+ WHERE SessionToken = #value#
+
+
+
+ DELETE FROM user_roles WHERE UserID = #Name#
+
+
+
+ INSERT INTO user_roles (UserID, RoleType)
+ VALUES(#username#, #role#)
+
+
+
+ UPDATE users
+ SET EmailAddress = #EmailAddress#
+ WHERE Username = #Name#
+
+
+
+ UPDATE users
+ SET EmailAddress = #user.EmailAddress#, Password=#password#
+ WHERE Username = #user.Name#
+
+
+
+ DELETE FROM signon WHERE Username = #value#
+
+
+
+ DELETE FROM signon
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php b/demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php
new file mode 100644
index 00000000..07c46acc
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php
@@ -0,0 +1,54 @@
+
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2006 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Revision: $ $16/07/2006: $
+ * @package Demos
+ */
+
+/**
+ * SQLMap type handler for TimeTrackerUser.
+ * The TimeTrackerUser requires an instance of IUserManager in constructor.
+ *
+ * @author Wei Zhuo
+ * @version $Revision: $ $16/07/2006: $
+ * @package Demos
+ * @since 3.1
+ */
+class TimeTrackerUserTypeHandler implements ITypeHandlerCallback
+{
+ /**
+ * Not implemented.
+ */
+ public function getParameter($object)
+ {
+ throw new TimeTrackerException('Not implemented');
+ }
+
+ /**
+ * Not implemented.
+ */
+ public function getResult($string)
+ {
+ throw new TimeTrackerException('Not implemented');
+ }
+
+ /**
+ * Creates a new instance of TimeTrackerUser
+ * @param array result data
+ * @return TimeTrackerUser new user instance
+ */
+ public function createNewInstance($row=null)
+ {
+ $manager = Prado::getApplication()->getModule('users');
+ if(is_null($manager))
+ $manager = new UserManager();
+ return new TimeTrackerUser($manager);
+ }
+}
+
+?>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql-reset.sql b/demos/time-tracker/protected/App_Data/mysql-reset.sql
deleted file mode 100644
index e9581952..00000000
--- a/demos/time-tracker/protected/App_Data/mysql-reset.sql
+++ /dev/null
@@ -1,26 +0,0 @@
-TRUNCATE `user_roles`;
-TRUNCATE `role_types`;
-TRUNCATE `project_members`;
-TRUNCATE `time_entry`;
-TRUNCATE `signon`;
-TRUNCATE `categories`;
-TRUNCATE `project`;
-TRUNCATE `users`;
-
-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
diff --git a/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
deleted file mode 100644
index 3f850955..00000000
--- a/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
+++ /dev/null
@@ -1,27 +0,0 @@
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
new file mode 100644
index 00000000..84468044
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
@@ -0,0 +1,29 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/project.xml b/demos/time-tracker/protected/App_Data/project.xml
deleted file mode 100644
index 0592f195..00000000
--- a/demos/time-tracker/protected/App_Data/project.xml
+++ /dev/null
@@ -1,5 +0,0 @@
-
-
-
-
-
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml b/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml
new file mode 100644
index 00000000..9590b506
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml
@@ -0,0 +1,26 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/time-tracker-mysql.sql b/demos/time-tracker/protected/App_Data/time-tracker-mysql.sql
deleted file mode 100644
index 89de378a..00000000
--- a/demos/time-tracker/protected/App_Data/time-tracker-mysql.sql
+++ /dev/null
@@ -1,135 +0,0 @@
-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)
-) 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)
-) 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)
-) 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)
-) 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,
- 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 ;
-
-DROP TABLE IF EXISTS users;
-CREATE TABLE 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)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-ALTER TABLE `categories`
- ADD CONSTRAINT categories_ibfk_1
- FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
-
-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);
-
-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);
-
-ALTER TABLE `signon`
- ADD CONSTRAINT signon_ibfk_1
- FOREIGN KEY (Username) REFERENCES users (Username);
-
-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);
-
-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
diff --git a/demos/time-tracker/protected/App_Data/time_tracker.db b/demos/time-tracker/protected/App_Data/time_tracker.db
deleted file mode 100644
index e7adaec3..00000000
Binary files a/demos/time-tracker/protected/App_Data/time_tracker.db and /dev/null differ
--
cgit v1.2.3