add sqlite mapping

7 files changed, 592 insertions, 0 deletions

diff --git a/demos/time-tracker/protected/App_Data/Sqlite/category.xml b/demos/time-tracker/protected/App_Data/Sqlite/category.xml
new file mode 100644
index 00000000..d89a517f
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/category.xml
@@ -0,0 +1,109 @@
+<?xml version="1.0" encoding="utf-8" ?>

+<sqlMap>

+

+<resultMap id="category-result" class="CategoryRecord">

+	<result property="Name" column="Name" />

+	<result property="EstimateDuration" column="EstimateDuration" type="float" />

+	<result property="ProjectID" column="ProjectID" type="integer" />

+	<result property="Abbreviation" column="Abbreviation" />

+	<result property="ID" column="CategoryID" type="integer" />

+	<result property="ActualDuration" column="ActualDuration" type="float" />

+</resultMap>

+

+<insert id="AddNewCategory" parameterClass="CategoryRecord">

+	INSERT INTO categories 

+	(Name, ProjectID, Abbreviation, EstimateDuration)

+	VALUES(#Name#, #ProjectID#, #Abbreviation#, #EstimateDuration#)

+	<selectKey property="ID" type="post" resultClass="int">

+		select LAST_INSERT_ROWID() as value

+    </selectKey>

+</insert>

+

+<select id="GetCategoryByID" parameterClass="integer" resultMap="category-result">

+	SELECT 

+		categories.Name as Name,

+		categories.EstimateDuration as EstimateDuration,

+		categories.ProjectID as ProjectID,

+		categories.Abbreviation as Abbreviation,

+		categories.CategoryID as CategoryID,

+		sum(time_entry.Duration) as ActualDuration

+	FROM

+		categories

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE

+		categories.CategoryID = #value#

+	GROUP BY

+		categories.CategoryID

+	ORDER BY

+		categories.Name

+</select>

+

+<select id="GetAllCategories" resultMap="category-result">

+	SELECT 

+		categories.Name as Name,

+		categories.EstimateDuration as EstimateDuration,

+		categories.ProjectID as ProjectID,

+		categories.Abbreviation as Abbreviation,

+		categories.CategoryID as CategoryID,

+		sum(time_entry.Duration) as ActualDuration

+	FROM

+		categories

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	GROUP BY

+		categories.CategoryID

+	ORDER BY

+		categories.Name

+</select>

+

+<delete id="DeleteCategory" parameterClass="integer">

+	DELETE FROM categories WHERE CategoryID = #value#

+</delete>

+

+<select id="GetCategoriesByProjectID" resultMap="category-result">

+	SELECT 

+		categories.Name as Name,

+		categories.EstimateDuration as EstimateDuration,

+		categories.ProjectID as ProjectID,

+		categories.Abbreviation as Abbreviation,

+		categories.CategoryID as CategoryID,

+		sum(time_entry.Duration) as ActualDuration

+	FROM

+		categories

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE

+		categories.ProjectID = #value#

+	GROUP BY

+		categories.CategoryID

+	ORDER BY

+		categories.Name

+</select>

+

+<select id="GetCategoryByNameInProject" parameterClass="array" resultMap="category-result">

+	SELECT 

+		categories.Name as Name,

+		categories.EstimateDuration as EstimateDuration,

+		categories.ProjectID as ProjectID,

+		categories.Abbreviation as Abbreviation,

+		categories.CategoryID as CategoryID,

+		sum(time_entry.Duration) as ActualDuration

+	FROM

+		categories

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE

+			categories.ProjectID = #project#

+		AND categories.Name = #category#

+	GROUP BY

+		categories.CategoryID

+</select>

+

+<update id="UpdateCategory" parameterClass="CategoryRecord">

+	UPDATE categories SET

+		Abbreviation = #Abbreviation#,

+		EstimateDuration = #EstimateDuration#,

+		Name = #Name#,

+		ProjectId = #ProjectID#

+	WHERE 

+		CategoryID = #ID#

+</update>

+

+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/projects.xml b/demos/time-tracker/protected/App_Data/Sqlite/projects.xml
new file mode 100644
index 00000000..e5f52887
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/projects.xml
@@ -0,0 +1,159 @@
+<?xml version="1.0" encoding="utf-8" ?>

+<sqlMap>

+

+<resultMap id="project-result" class="ProjectRecord">

+	<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="EstimateDuration" column="EstimateDuration" type="float" />

+	<result property="CompletionDate" column="CompletionDate" typeHandler="DateTime" />

+	<result property="CreatorUserName" column="CreatorID" />

+	<result property="ManagerUserName" column="ManagerID" />

+	<result property="ActualDuration" column="ActualDuration" type="float" />

+</resultMap>

+

+<select id="ProjectNameExists" resultClass="boolean">

+	SELECT COUNT(Name) FROM projects WHERE Name = #value#

+</select>

+

+<insert id="CreateNewProject" parameterClass="ProjectRecord">

+	INSERT INTO projects

+	(Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)

+	VALUES

+	(#Name#, #Description#, php('date', 'Y-m-d H:i:s'), #EstimateDuration#,

+		#CompletionDate, typeHandler=DateTime#, 

+		#CreatorUserName#, #ManagerUserName#)

+	<selectKey property="ID" type="post" resultClass="int">

+		select LAST_INSERT_ROWID() as value

+    </selectKey>

+</insert>

+

+<select id="GetProjectByID" parameterClass="integer" resultMap="project-result">

+	SELECT 

+		projects.ProjectID as ProjectID,

+		projects.Name as Name,

+		projects.Description as Description,

+		projects.CreationDate as CreationDate,

+		projects.EstimateDuration as EstimateDuration,

+		projects.CompletionDate as CompletionDate,

+		projects.CreatorID as CreatorID,

+		projects.ManagerID as ManagerID,

+		SUM(time_entry.Duration) as ActualDuration

+	FROM projects

+		LEFT JOIN categories ON projects.ProjectID = categories.ProjectID

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE 

+			projects.ProjectID = #value# 

+		AND Disabled = 0

+	GROUP BY

+		projects.ProjectID

+	ORDER BY

+		projects.Name

+</select>

+

+<select id="GetAllProjects" resultMap="project-result">

+	SELECT 

+		projects.ProjectID as ProjectID,

+		projects.Name as Name,

+		projects.Description as Description,

+		projects.CreationDate as CreationDate,

+		projects.EstimateDuration as EstimateDuration,

+		projects.CompletionDate as CompletionDate,

+		projects.CreatorID as CreatorID,

+		projects.ManagerID as ManagerID,

+		SUM(time_entry.Duration) as ActualDuration

+	FROM projects

+		LEFT JOIN categories ON projects.ProjectID = categories.ProjectID

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE 

+		Disabled = 0

+	GROUP BY

+		projects.ProjectID

+	ORDER BY

+		projects.ProjectID ASC

+</select>

+

+<select id="GetAllProjectsOrdered" resultMap="project-result" extends="GetAllProjects">

+	ORDER BY $sort$ $order$

+</select>

+

+<select id="GetProjectsByManagerName" resultMap="project-result">

+	SELECT 

+		projects.ProjectID as ProjectID,

+		projects.Name as Name,

+		projects.Description as Description,

+		projects.CreationDate as CreationDate,

+		projects.EstimateDuration as EstimateDuration,

+		projects.CompletionDate as CompletionDate,

+		projects.CreatorID as CreatorID,

+		projects.ManagerID as ManagerID,

+		SUM(time_entry.Duration) as ActualDuration

+	FROM projects

+		LEFT JOIN categories ON projects.ProjectID = categories.ProjectID

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE 

+			Disabled = 0 

+		AND projects.ManagerID = #value#

+	GROUP BY

+		projects.ProjectID

+	ORDER BY

+		projects.Name

+</select>

+

+<select id="GetProjectsByUserName" resultMap="project-result">

+	SELECT 

+		projects.ProjectID as ProjectID,

+		projects.Name as Name,

+		projects.Description as Description,

+		projects.CreationDate as CreationDate,

+		projects.EstimateDuration as EstimateDuration,

+		projects.CompletionDate as CompletionDate,

+		projects.CreatorID as CreatorID,

+		projects.ManagerID as ManagerID,

+		SUM(time_entry.Duration) as ActualDuration

+	FROM projects

+		LEFT JOIN categories ON projects.ProjectID = categories.ProjectID

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID,

+		project_members

+	WHERE 

+			project_members.ProjectID = projects.ProjectID

+		AND project_members.UserID = #value#

+		AND projects.Disabled = 0

+	GROUP BY

+		projects.ProjectID

+	ORDER BY

+		projects.Name

+</select>

+

+<update id="DeleteProject" parameterClass="integer">

+	UPDATE projects SET Disabled = 1 WHERE ProjectID = #value#

+</update>

+

+<select id="GetProjectMembers" parameterClass="integer">

+	SELECT UserID FROM project_members WHERE ProjectID = #value#

+	ORDER BY UserID

+</select>

+

+<insert id="AddUserToProject" parameterClass="array">

+	INSERT INTO project_members (UserID, ProjectID)

+	VALUES(#username#, #project#)

+</insert>

+

+<delete id="RemoveUserFromProject" parameterClass="array">

+	DELETE FROM project_members WHERE ProjectID = #project# AND UserID = #username#

+</delete>

+

+<update id="UpdateProject" parameterClass="ProjectRecord">

+	UPDATE projects

+	SET 

+		CompletionDate = #CompletionDate, typeHandler=DateTime#,

+		Description = #Description#,

+		EstimateDuration = #EstimateDuration#,

+		ManagerId =#ManagerUserName#,

+		Name = #Name#

+	WHERE 

+		ProjectID = #ID#

+</update>

+

+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/reports.xml b/demos/time-tracker/protected/App_Data/Sqlite/reports.xml
new file mode 100644
index 00000000..b5997f1f
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/reports.xml
@@ -0,0 +1,87 @@
+<?xml version="1.0" encoding="utf-8" ?>

+<sqlMap>

+

+<resultMap id="project-category-user" class="ProjectReport" GroupBy="ProjectID">

+	<result property="ProjectName" column="ProjectName" />

+	<result property="EstimateHours" column="ProjectEstimate" type="float"/>

+	<result property="EstimateCompletion" column="ProjectCompletion" type="DateTime" />

+	<result property="Categories" type="TList" resultMapping="category-user-report" />

+</resultMap>

+

+<resultMap id="category-user-report" class="CategoryReport" GroupBy="CategoryID" >

+	<result property="CategoryName" column="CategoryName" />

+	<result property="EstimateHours" column="CategoryEstimate" type="float" />

+	<result property="members" type="array" resultMapping="member-report" />

+</resultMap>

+

+<resultMap id="member-report" class="array">

+	<result property="username" column="Username" />

+	<result property="hours" column="ActualDuration" type="float" />

+</resultMap>

+

+<select id="GetTimeReportByProjectIDs" resultMap="project-category-user">

+	SELECT

+		categories.Name as CategoryName,

+		categories.CategoryID as CategoryID,

+		projects.ProjectID as ProjectID,

+		categories.EstimateDuration as CategoryEstimate,

+		projects.Name as ProjectName,

+		projects.EstimateDuration as ProjectEstimate,

+		projects.CompletionDate as ProjectCompletion,

+		time_entry.UserID as Username,

+		SUM(time_entry.Duration) as ActualDuration

+	FROM

+		projects

+		LEFT JOIN categories ON categories.ProjectID = projects.ProjectID

+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID

+	WHERE

+		projects.ProjectID IN ( $value$ )

+	GROUP BY

+		categories.ProjectID,

+		categories.CategoryID,

+		time_entry.UserID

+	ORDER BY

+		projects.ProjectID

+</select>

+

+

+<resultMap id="time-entry-user-report" class="UserReport" GroupBy="Username">

+	<result property="Username" column="Username" />

+	<result property="Projects" resultMapping="project-user-report" />

+</resultMap>

+

+<resultMap id="project-user-report" class="UserProjectReport">

+	<result property="ProjectName" column="ProjectName" />

+	<result property="CategoryName" column="CategoryName" />

+	<result property="Duration" column="Duration" type="float" />

+	<result property="Description" column="Description" />

+	<result property="ReportDate" column="EntryDate" type="DateTime" />

+</resultMap>

+

+<select id="GetTimeReportByUsername" resultMap="time-entry-user-report">

+	SELECT

+		users.Username as Username,

+		projects.Name as ProjectName,

+		categories.Name as CategoryName,

+		time_entry.Duration as Duration,

+		time_entry.Description as Description,

+		time_entry.EntryDate as EntryDate

+	FROM 

+   		users 

+   		LEFT JOIN time_entry ON time_entry.UserID = users.Username

+			AND time_entry.EntryDate BETWEEN 

+				#startDate, typeHandler=DateTime# AND 

+				#endDate, typeHandler=DateTime#

+   		LEFT JOIN categories ON time_entry.CategoryID = categories.CategoryID

+   		LEFT JOIN projects ON categories.ProjectID = projects.ProjectID 

+			AND projects.ProjectID in ($projects$) 

+	WHERE

+   		users.Username in ($members$)

+	ORDER BY

+  		users.Username ASC,

+  		time_entry.EntryDate ASC,

+  		projects.Name ASC,

+  		categories.Name ASC

+</select>

+

+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml b/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml
new file mode 100644
index 00000000..1d7fec37
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml
@@ -0,0 +1,100 @@
+<?xml version="1.0" encoding="utf-8" ?>

+<sqlMap>

+

+<insert id="AddNewTimeEntry" parameterClass="TimeEntryRecord">

+	INSERT INTO time_entry(

+		EntryCreated, 

+		Duration, 

+		Description, 

+		CategoryID,

+		EntryDate, 

+		CreatorID, 

+		UserID

+	)

+	VALUES(

+		php('date', 'Y-m-d H:i:s'), 

+		#Duration#, 

+		#Description#, 

+		#Category.ID#,

+		#ReportDate, typeHandler=DateTime#, 

+		#CreatorUserName#, 

+		#Username#

+	)

+  <selectKey property="ID" type="post" resultClass="int">

+		select LAST_INSERT_ROWID() as value

+  </selectKey>

+</insert>

+

+<resultMap id="time-entry-result" class="TimeEntryRecord">

+	<result property="ID" column="EntryID" type="integer" />

+	<result property="DateCreated" column="EntryCreated" typeHandler="DateTime" />

+	<result property="Duration" column="Duration" type="float" />

+	<result property="Description" column="Description" />

+	<result property="Category" column="CategoryID" />

+	<result property="ReportDate" column="EntryDate" typeHandler="DateTime" />

+	<result property="CreatorUserName" column="CreatorID" />

+	<result property="Username" column="UserID" />

+</resultMap>

+

+<resultMap id="time-entry-category-result" class="TimeEntryRecord">

+	<result property="ID" column="EntryID" type="integer" />

+	<result property="DateCreated" column="EntryCreated" typeHandler="DateTime" />

+	<result property="Duration" column="Duration" type="float" />

+	<result property="Description" column="Description" />

+	<result property="Category" resultMapping="entry-category" />

+	<result property="ReportDate" column="EntryDate" typeHandler="DateTime" />

+	<result property="CreatorUserName" column="CreatorID" />

+	<result property="Username" column="UserID" />

+</resultMap>

+

+<resultMap id="entry-category" class="CategoryRecord">

+	<result property="ID" column="CategoryID" />

+	<result property="Name" column="CategoryName" />

+</resultMap>

+

+<select id="GetTimeEntryByID" resultMap="time-entry-result">

+	SELECT 

+		*

+	FROM 

+		time_entry

+	WHERE

+		EntryID = #value#

+</select>

+

+<delete id="DeleteTimeEntry" parameterClass="integer">

+	DELETE FROM time_entry WHERE EntryID = #value#

+</delete>

+

+<select id="GetAllTimeEntriesByProjectIdAndUser" resultMap="time-entry-category-result">

+	SELECT 

+		time_entry.EntryID as EntryID,

+		time_entry.EntryCreated as EntryCreated,

+		time_entry.Duration as Duration,

+		time_entry.Description as Description,

+		time_entry.EntryDate as EntryDate,

+		time_entry.CreatorID as CreatorID,

+		time_entry.UserID as UserID,

+		time_entry.CategoryID as CategoryID,

+		categories.Name as CategoryName

+	FROM

+		time_entry, categories

+	WHERE

+			time_entry.UserID = #username#

+		AND time_entry.CategoryID = categories.CategoryID

+		AND categories.ProjectID = #project#

+	ORDER BY 

+		EntryID ASC

+</select>

+

+<update id="UpdateTimeEntry" parameterClass="TimeEntryRecord">

+	UPDATE time_entry SET

+		Duration = #Duration#,

+		Description = #Description#,

+		CategoryID = #Category.ID#,

+		EntryDate = #ReportDate, typeHandler=DateTime#,

+		UserID = #Username#

+	WHERE

+		EntryID = #ID#

+</update>

+

+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db
new file mode 100644
index 00000000..124bbeba
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak
new file mode 100644
index 00000000..124bbeba
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/users.xml b/demos/time-tracker/protected/App_Data/Sqlite/users.xml
new file mode 100644
index 00000000..74b33b2c
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/Sqlite/users.xml
@@ -0,0 +1,137 @@
+<?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="UsernameExists" parameterClass="string" resultClass="boolean">

+	SELECT COUNT(Username) FROM users WHERE Username = #value#

+</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#, php('date', 'Y-m-d H:i:s'))

+</insert>

+

+<select id="ValidateAutoSignon" 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,

+		signon

+	WHERE

+		users.Username = signon.Username

+		AND signon.SessionToken = #value#

+		AND users.Disabled = 0

+</select>

+

+<update id="UpdateSignon">

+	UPDATE signon SET LastSignOnDate = php('date', 'Y-m-d H:i:s') 

+	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