From ed600b0d55dd7c38e4416cf4bd6df43a71244b81 Mon Sep 17 00:00:00 2001
From: wei <>
Date: Sun, 16 Jul 2006 06:27:25 +0000
Subject: Rename APP_CODE directory name again.

---
 .../protected/App_Data/mysql-reset.sql             |  26 ++++
 .../protected/App_Data/mysql-sqlmap.xml            |  21 ++++
 demos/time-tracker/protected/App_Data/project.xml  |   5 +
 .../protected/App_Data/time-tracker-mysql.sql      | 135 +++++++++++++++++++++
 demos/time-tracker/protected/App_Data/users.xml    | 131 ++++++++++++++++++++
 5 files changed, 318 insertions(+)
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-reset.sql
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
 create mode 100644 demos/time-tracker/protected/App_Data/project.xml
 create mode 100644 demos/time-tracker/protected/App_Data/time-tracker-mysql.sql
 create mode 100644 demos/time-tracker/protected/App_Data/users.xml

(limited to 'demos/time-tracker/protected/App_Data')

diff --git a/demos/time-tracker/protected/App_Data/mysql-reset.sql b/demos/time-tracker/protected/App_Data/mysql-reset.sql
new file mode 100644
index 00000000..e9581952
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/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/mysql-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
new file mode 100644
index 00000000..fe4ebedc
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml
@@ -0,0 +1,21 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<sqlMapConfig>
+	
+	<typeHandler type="TimeTrackerUser" 
+		callback="TimeTrackerUserTypeHandler"/>
+	
+    <provider class="TAdodb">
+        <datasource 
+        	driver="mysql"
+        	username="time-tracker"
+        	password="prado"
+        	host="localhost"
+        	database="time-tracker" />
+    </provider>
+    
+    <sqlMaps>
+        <sqlMap resource="users.xml"/>
+		<sqlMap resource="project.xml"/>
+    </sqlMaps>
+    
+</sqlMapConfig>
\ 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
new file mode 100644
index 00000000..0592f195
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/project.xml
@@ -0,0 +1,5 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+
+</sqlMap>
\ 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
new file mode 100644
index 00000000..89de378a
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/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/users.xml b/demos/time-tracker/protected/App_Data/users.xml
new file mode 100644
index 00000000..78de588a
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/users.xml
@@ -0,0 +1,131 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap id="time-tracker-user" class="TimeTrackerUser" GroupBy="Name">
+	<result property="Name" column="Name" />
+	<result property="EmailAddress" column="EmailAddress" />
+	<result property="Roles" Type="array" resultMapping="user-roles-result" />
+</resultMap>
+
+<resultMap id="user-roles-result">
+	<result column="role" />
+</resultMap>
+
+<!--
+<select id="GetUserByName" parameterClass="string" resultMap="TimeTrackerUser">
+	SELECT 
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Username = #value#
+	AND 
+		Disabled = 0
+</select>
+-->
+
+<select id="GetUserByName" parameterClass="string" resultMap="time-tracker-user">
+	SELECT 
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Username = #value#
+	AND 
+		Disabled = 0
+</select>
+    
+<select id="GetAllUsers" resultMap="time-tracker-user">
+	SELECT
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Disabled = 0
+</select>
+
+<select id="ValidateUser" resultClass="boolean">
+	SELECT 
+		count(Username)
+	FROM
+		users
+	WHERE
+		username = #username# AND password = #password#
+	AND
+		Disabled = 0
+</select>
+
+<insert id="AddNewUser" parameterClass="array">
+	INSERT INTO 
+		users (Username, Password, EmailAddress)
+	VALUES
+		(#user.Name#, #password#, #user.EmailAddress#)
+</insert>
+
+<update id="DeleteUserByName">
+	UPDATE users SET Disabled = 1  WHERE username = #value#
+</update>
+
+<insert id="RegisterAutoSignon" parameterClass="array">
+	INSERT INTO 
+		signon (SessionToken, Username, LastSignOnDate)
+	VALUES
+		(#token#, #username#, NOW())
+</insert>
+
+<select id="ValidateAutoSignon" resultClass="TimeTrackerUser">
+	SELECT
+		users.Username as Name,
+		users.EmailAddress as EmailAddress
+	FROM
+		users, signon
+	WHERE
+		users.Username = signon.Username
+		AND signon.SessionToken = #value#
+		AND users.Disabled = 0
+</select>
+
+<update id="UpdateSignon">
+	UPDATE signon SET LastSignOnDate = NOW() 
+	WHERE SessionToken = #value#
+</update>
+
+<delete id="DeleteUserRoles" parameterClass="TimeTrackerUser">
+	DELETE FROM user_roles WHERE UserID = #Name#
+</delete>
+
+<update id="AddUserRole" parameterClass="array">
+	INSERT INTO user_roles (UserID, RoleType)
+	VALUES(#username#, #role#)
+</update>
+
+<update id="UpdateUserDetails" parameterClass="TimeTrackerUser">
+	UPDATE users 
+	SET EmailAddress = #EmailAddress#
+	WHERE Username = #Name#
+</update>
+
+<update id="UpdateUserDetailsAndPassword" parameterClass="array">
+	UPDATE users 
+	SET EmailAddress = #user.EmailAddress#, Password=#password#
+	WHERE Username = #user.Name#
+</update>
+
+<delete id="DeleteAutoSignon">
+	DELETE FROM signon WHERE Username = #value#
+</delete>
+
+<delete id="DeleteAllSignon">
+	DELETE FROM signon
+</delete>
+
+</sqlMap>
\ No newline at end of file
-- 
cgit v1.2.3