<?xml version="1.0" encoding="Shift_JIS" standalone="no"?>

<database name="chiffon" defaultIdMethod="idbroker">

	<aspect name="must_have_update_member_id" where="page.before.save" >
		<set-value columnName="update_member_id" type="bean" value="visit.loginUser.memberId" />
	</aspect>
	<aspect name="must_have_updated" where="peer.before.save" >
		<set-value columnName="updated" value="getCurrentTimestampAsDate()" />
	</aspect>
	
	<tableAspect name="general_aspect" >
		<column name="updated"  label="更新日時" required="true"  type="DATE"     description="when the record was updated">
			<format format="yyyy/MM/dd hh:mm" className="java.text.SimpleDateFormat" />
		</column>
		<column name="update_member_id" label="更新者" required="true"  type="BIGINT"  description="who updated the record" >
			<property name="update_member_name" type="String" label="更新者名" valueToGet="member.party.name" foreignProperty="name" readonly="true" />
		</column>
		<foreign-key foreignTable="member">
			<reference local="update_member_id" foreign="member_id"/>
		</foreign-key>
		<aspect-ref name="must_have_update_member_id"/>
		<aspect-ref name="must_have_updated"/>
		<!-- include/exclude patterns -->
		<include pattern="*" />
		<exclude pattern="party" />
		<exclude pattern="member" />
	</tableAspect>
	
	<tableAspect name="partical_aspect" >
		<column name="updated"  label="更新日時" required="true"  type="DATE"     description="when the record was updated">
			<format format="yyyy/MM/dd hh:mm" className="java.text.SimpleDateFormat" />
		</column>
		<column name="update_member_id" label="更新者" required="true"  type="BIGINT"  description="who updated the record" />
		<aspect-ref name="must_have_update_member_id"/>
		<aspect-ref name="must_have_updated"/>
		<!-- include/exclude patterns -->
		<include pattern="party" />
		<include pattern="member" />
	</tableAspect>
	
	<table name="party" label="グループ・個人など" idMethod="idbroker" description="person or group">
		<column name="party_id" label="ID" required="true"  type="BIGINT" primaryKey="true" description="party id"/>
		<column name="name"     label="氏名" required="true"  type="VARCHAR" size="50" description="name"/>
		<index name="idx_party_name">
			<index-column name="name"/>
		</index>
		<view-join table="contact" type="oneToMany" foreignKey="fk_contact_party" />
	</table>
	
	<enum name="MemberType" valueType="int" label="member type" >
		<enum-entry name="viewer" value="1" />
		<enum-entry name="editor" value="2" />
		<enum-entry name="admin" value="3" />
	</enum>

	<table name="member" label="メンバー" idMethod="idbroker" loginUser="true" loginNamePropertyName="login_name" description="dev team member">
		<column name="member_id"      required="true"  type="BIGINT" primaryKey="true" description="member id"/>	
		<column name="member_type_id" required="true"  type="INTEGER" visible="false" description="type of member">
			<property name="memberType" type="MemberType" label="member type" />
		</column> 	
		<column name="party_id"       required="true"  type="BIGINT" description="party id" >
			<property name="partyName" type="String" label="party name" valueToGet="party.name" foreignProperty="name" readonly="true" />
		</column>
		<column name="login_name"     required="true"  type="VARCHAR" size="20" description=" Login user name"/>
		<column name="password"       required="false" type="VARCHAR" size="20" description="Password"/>

		<foreign-key name="fk_member_party" foreignTable="party">
			<reference local="party_id" foreign="party_id"/>
		</foreign-key>
		<unique name="unique_member_login_name">
			<unique-column name="login_name"/>
		</unique>
		<index name="idx_member_party">
			<index-column name="party_id"/>
		</index>
		<index name="idx_member_login_name">
			<index-column name="login_name"/>
		</index>
		<view-join table="party" type="oneToOne" foreignKey="fk_member_party" >
			<view-join table="contact" type="oneToMany" foreignKey="fk_contact_party" />
		</view-join>
	</table>
	
	<enum name="ContactType" valueType="int" label="contact type" >
		<enum-entry name="e_mail" value="1" />
		<enum-entry name="mail" value="2" />
		<enum-entry name="tel" value="3" />
		<enum-entry name="mobile" value="4" />
	</enum>

	<table name="contact" idMethod="idbroker" description="how to contact with party">
		<column name="contact_id"      required="true"  type="BIGINT" primaryKey="true" description="contact Id"/>
		<column name="party_id"        required="true"  type="BIGINT" description="party id" />
		<column name="contact_type_id" required="true"  type="INTEGER" visible="false" description="the kind fo way to contace ">
			<property name="contactType" type="ContactType" label="contact type" />
		</column>
		<column name="address"         required="true"  type="VARCHAR" size="255" description="Actual way to contact (090-4135-4471)"/>

		<foreign-key name="fk_contact_party" foreignTable="party">
			<reference local="party_id" foreign="party_id"/>
		</foreign-key>
		<index name="idx_contact_person">
			<index-column name="party_id"/>
		</index>
		<index name="idx_contact_address">
			<index-column name="contact_type_id"/>
			<index-column name="address"/>
		</index>
	</table>
	  
	<table name="note" idMethod="idbroker" description="how to contact with party">
		<column name="note_id"          required="true"  type="BIGINT"     primaryKey="true" description="note id"/>
		<column name="descriptions"     required="false" type="LONGVARCHAR" description="Descriptions"/>
	</table>
	
	<table name="note_keys" idMethod="none" description="modify about any tables">
		<column name="target_table" required="true"  type="VARCHAR" primaryKey="true" size="50"  description="name"/>
		<column name="key_name"     required="true"  type="VARCHAR" primaryKey="true" size="50"  description="key"/>
		<column name="key_value"    required="true"  type="VARCHAR" primaryKey="true" size="100" description="key"/>
		<column name="note_id"      required="true"  type="BIGINT" description="modify_id"/>
		<foreign-key foreignTable="note">
			<reference local="note_id" foreign="note_id"/>
		</foreign-key>
		<unique name="unique_note_keys_1">
			<unique-column name="target_table"/>
			<unique-column name="key_name"/>
			<unique-column name="key_value"/>
			<unique-column name="note_id"/>
		</unique>
		<index name="idx_note_keys_id" >
			<index-column name="note_id" />
		</index>
	</table>
	
	<table name="project" idMethod="idbroker" description="Project">
		<column name="project_id"   required="true"  type="BIGINT"     primaryKey="true" description="project Id"/>
		<column name="name"         required="true"  type="VARCHAR"     size="50" description="Name"/>
		<column name="descriptions" required="false" type="LONGVARCHAR" description="Descriptions"/>
		<index name="idx_project_name">
			<index-column name="name"/>
		</index>
		<view-join table="iteration" type="oneToMany" foreignKey="fk_iteration_project" >
			<view-join table="story" type="oneToMany" foreignKey="fk_story_iteration_release" >
				<view-join table="task" type="oneToMany" foreignKey="fk_task_story" />
			</view-join>
			<view-join table="story" type="oneToMany" foreignKey="fk_story_iteration_test" >
				<view-join table="task" type="oneToMany" foreignKey="fk_task_story" />
			</view-join>
			<view-join table="story" type="oneToMany" foreignKey="fk_story_iteration_impl" >
				<view-join table="task" type="oneToMany" foreignKey="fk_task_story" />
			</view-join>
		</view-join>
		<view-join table="project_member_assign" type="oneToMany" foreignKey="fk_project_member_assign_project" >
			<view-join table="member" type="oneToOne" foreignKey="fk_project_member_assign_project_member" >
				<view-join table="party" type="oneToOne" foreignKey="fk_member_party" >
					<view-join table="contact" type="oneToMany" foreignKey="fk_contact_party" />
				</view-join>
			</view-join>
		</view-join>
	</table>
	
	<table name="project_member_assign" idMethod="none" description="member assigned project" >
		<column name="project_id" required="true"  type="BIGINT" primaryKey="true" description="project Id"/>
		<column name="member_id"  required="true"  type="BIGINT" primaryKey="true" description="member Id"/>
		<column name="join_date"  required="true"  type="DATE"   primaryKey="true" description="when the member joins"/>
		<column name="quit_date"  required="false" type="DATE"    description="when the member quits"/>
		<foreign-key name="fk_project_member_assign_project" foreignTable="project">
			<reference local="project_id" foreign="project_id"/>
		</foreign-key>
		<foreign-key name="fk_project_member_assign_project_member" foreignTable="member">
			<reference local="member_id" foreign="member_id"/>
		</foreign-key>
		<unique name="unique_project_member_assigned_1">
			<unique-column name="project_id"/>
			<unique-column name="member_id"/>
		</unique>
		<index name="idx_pm_assign_project">
			<index-column name="project_id"/>
		</index>
		<index name="idx_pm_assign_member">
			<index-column name="member_id"/>
		</index>
	</table>
	
	<table name="iteration" idMethod="idbroker" description="iteration">
		<column name="iteration_id" required="true"  type="BIGINT"     primaryKey="true" description="id"/>
		<column name="project_id"   required="true"  type="BIGINT"     description="Foreign Key Project"/>
		<column name="iteration_no" required="true"  type="INTEGER"     description="number in the project"/>
		<column name="descriptions" required="true"  type="LONGVARCHAR" description="Descriptions"/>
		<column name="start_date"   required="true"  type="DATE"        description="when the iteration starts"/>
		<column name="end_date"     required="true"  type="DATE"        description="when the iteration ends"/>
		<foreign-key name="fk_iteration_project" foreignTable="project">
			<reference local="project_id" foreign="project_id"/>
		</foreign-key>
		<unique name="unique_iteration_1">
			<unique-column name="project_id"/>
			<unique-column name="iteration_no"/>
		</unique>
		<index name="idx_iteration_project">
			<index-column name="project_id"/>
		</index>
		<index name="idx_iteration_period">
			<index-column name="start_date"/>
			<index-column name="end_date"/>
		</index>
	</table>
	
	<table name="story" idMethod="idbroker" description="Story">
		<column name="story_id"        required="true"  type="BIGINT"     primaryKey="true" description="Story Id"/>
		<column name="project_id"      required="true"  type="BIGINT"     description="Foreign Key Project"/>
		<column name="name"            required="true"  type="VARCHAR"     size="255" description="Name"/>
		<column name="priotity"        required="true"  type="REAL"        description="Priotity to sort"/>
		<column name="descriptions"    required="true"  type="LONGVARCHAR" description="Descriptions"/>
		<column name="tested"          required="false" type="BIT"         description="tested"/>
		<column name="task_assigned"   required="false" type="BIT"         description="task assigned"/>
		<column name="iter_id_release" required="false" type="BIGINT"     description="Foreign Key Iteration"/>
		<column name="iter_id_test"    required="false" type="BIGINT"     description="Foreign Key Iteration"/>
		<column name="iter_id_impl"    required="false" type="BIGINT"     description="Foreign Key Iteration"/>
		<foreign-key name="fk_story_project" foreignTable="project">
			<reference local="project_id" foreign="project_id"/>
		</foreign-key>
		<foreign-key name="fk_story_iteration_release" foreignTable="iteration">
			<reference local="iter_id_release" foreign="iteration_id"/>
		</foreign-key>
		<foreign-key name="fk_story_iteration_test" foreignTable="iteration">
			<reference local="iter_id_test" foreign="iteration_id"/>
		</foreign-key>
		<foreign-key name="fk_story_iteration_impl" foreignTable="iteration">
			<reference local="iter_id_impl" foreign="iteration_id"/>
		</foreign-key>
		<index name="idx_story_project">
			<index-column name="project_id"/>
		</index>
		<index name="idx_story_name">
			<index-column name="name"/>
		</index>
		<index name="idx_story_release">
			<index-column name="project_id"/>
			<index-column name="iter_id_release"/>
		</index>
		<index name="idx_story_test">
			<index-column name="project_id"/>
			<index-column name="iter_id_test"/>
		</index>
		<index name="idx_story_impl">
			<index-column name="project_id"/>
			<index-column name="iter_id_impl"/>
		</index>
	</table>
	
	<table name="task" idMethod="idbroker" description="Task">
		<column name="task_id"      required="true"  type="BIGINT"     primaryKey="true" description="task Id"/>
		<column name="story_id"     required="true"  type="BIGINT"     description="Foreign Key story"/>
		<column name="name"         required="true"  type="VARCHAR"     size="255" description="Name"/>
		<column name="descriptions" required="true"  type="LONGVARCHAR" description="Descriptions"/>
		<column name="weight"       required="false" type="REAL"        description="weight"/>
		<column name="estimate"     required="false" type="REAL"        description="estimate time (hour)"/>
		<column name="actual"       required="false" type="REAL"        description="actual time to work (hour)"/>
		<foreign-key name="fk_task_story" foreignTable="story">
			<reference local="story_id" foreign="story_id"/>
		</foreign-key>
		<index name="idx_task_story">
			<index-column name="story_id"/>
		</index>
		<index name="idx_task_name">
			<index-column name="name"/>
		</index>
	</table>
	  
	<table name="work" idMethod="idbroker" description="the work which a member did actually">
		<column name="work_id"      required="true"  type="BIGINT"     primaryKey="true" description="work id"/>
		<column name="member_id"    required="true"  type="BIGINT"     description="member id"/>
		<column name="task_id"      required="false" type="BIGINT"     description="task id"/>
		<column name="descriptions" required="false" type="LONGVARCHAR" description="Descriptions"/>
		<foreign-key foreignTable="member">
			<reference local="member_id" foreign="member_id"/>
		</foreign-key>
		<foreign-key foreignTable="task">
			<reference local="task_id" foreign="task_id"/>
		</foreign-key>
		<index name="work_member">
			<index-column name="member_id"/>
		</index>
		<index name="idx_member_task">
			<index-column name="task_id"/>
		</index>
	</table>

</database>
