From 2c221ea67d0512961beea8fbcb30b23865c16bb0 Mon Sep 17 00:00:00 2001
From: wei <>
Date: Wed, 20 Dec 2006 03:15:04 +0000
Subject: Add quickstart docs for Active Record and SqlMap

---
 .../protected/App_Data/MySQL4/projects.xml         |   9 +-
 .../App_Data/MySQL4/time-tracker-mysql.sql         | 317 +++++++++++++--------
 .../protected/App_Data/mysql4-sqlmap.xml           |  36 +--
 3 files changed, 221 insertions(+), 141 deletions(-)

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

diff --git a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml
index 099e7fef..bcac9480 100644
--- a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml
+++ b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml
@@ -5,9 +5,9 @@
 	<result property="ID" column="ProjectID" type="integer"/>
 	<result property="Name" column="Name" />
 	<result property="Description" column="Description" />
-	<result property="DateCreated" column="CreationDate" typeHandler="DateTime" />
+	<result property="DateCreated" column="CreationDate" typeHandler="DateTimeTypeHandler" />
 	<result property="EstimateDuration" column="EstimateDuration" type="float" />
-	<result property="CompletionDate" column="CompletionDate" typeHandler="DateTime" />
+	<result property="CompletionDate" column="CompletionDate" typeHandler="DateTimeTypeHandler" />
 	<result property="CreatorUserName" column="CreatorID" />
 	<result property="ManagerUserName" column="ManagerID" />
 	<result property="ActualDuration" column="ActualDuration" type="float" />
@@ -22,7 +22,7 @@
 	(Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
 	VALUES
 	(#Name#, #Description#, NOW(), #EstimateDuration#,
-		#CompletionDate, typeHandler=DateTime#, 
+		#CompletionDate, typeHandler=DateTimeTypeHandler#, 
 		#CreatorUserName#, #ManagerUserName#)
 	<selectKey property="ID" type="post" resultClass="int">
 		select LAST_INSERT_ID() as value
@@ -62,6 +62,7 @@
 		project.CompletionDate as CompletionDate,
 		project.CreatorID as CreatorID,
 		project.ManagerID as ManagerID,
+		project.ManagerID as ManagerUserName,
 		SUM(time_entry.Duration) as ActualDuration
 	FROM project
 		LEFT JOIN categories ON project.ProjectID = categories.ProjectID
@@ -144,7 +145,7 @@
 <update id="UpdateProject" parameterClass="ProjectRecord">
 	UPDATE project
 	SET 
-		CompletionDate = #CompletionDate, typeHandler=DateTime#,
+		CompletionDate = #CompletionDate, typeHandler=DateTimeTypeHandler#,
 		Description = #Description#,
 		EstimateDuration = #EstimateDuration#,
 		ManagerId =#ManagerUserName#,
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`);
diff --git a/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
index 84468044..ff7f636e 100644
--- a/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
+++ b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml
@@ -1,29 +1,17 @@
 <?xml version="1.0" encoding="UTF-8" ?>
 <sqlMapConfig>
-	
-	<typeHandlers>
-		<typeHandler type="TimeTrackerUser" 
-			callback="TimeTrackerUserTypeHandler"/>
-		<typeHandler type="DateTime" 
-			callback="DateTimeTypeHandler" />
-	</typeHandlers>
+		
+	<typeHandler type="TimeTrackerUser" class="TimeTrackerUserTypeHandler"/>
+	<typeHandler type="DateTime" class="DateTimeTypeHandler" />
 
-	
-    <provider class="TAdodb">
-        <datasource 
-        	driver="mysql"
-        	username="time-tracker"
-        	password="prado"
-        	host="localhost"
-        	database="time-tracker" />
-    </provider>
-    
-    <sqlMaps>
-        <sqlMap resource="MySQL4/users.xml"/>
-		<sqlMap resource="MySQL4/projects.xml"/>
-		<sqlMap resource="MySQL4/category.xml" />
-		<sqlMap resource="MySQL4/time-entry.xml" />
-		<sqlMap resource="MySQL4/reports.xml" />
-    </sqlMaps>
+	<connection class="TDbConnection"
+		ConnectionString="mysql:host=localhost;dbname=time-tracker"
+		Username="time-tracker" Password="prado" />
+
+	<sqlMap resource="MySQL4/users.xml"/>
+	<sqlMap resource="MySQL4/projects.xml"/>
+	<sqlMap resource="MySQL4/category.xml" />
+	<sqlMap resource="MySQL4/time-entry.xml" />
+	<sqlMap resource="MySQL4/reports.xml" />
     
 </sqlMapConfig>
\ No newline at end of file
-- 
cgit v1.2.3