[civicrm-core.git] / xml / schema / Contact / RelationshipCache.xml

<?xml version="1.0" encoding="iso-8859-1" ?>

<table>
  <base>CRM/Contact</base>
  <class>RelationshipCache</class>
  <name>civicrm_relationship_cache</name>
  <comment>The cache permutes information from the relationship table to facilitate querying. Every relationship is mapped to multiple records in the cache. Joins should begin on the near side and extract info from the far side.</comment>
  <add>5.29</add>
  <log>false</log>
  <icon>fa-handshake-o</icon>
  <title>Related Contact</title>
  <paths>
    <view>civicrm/contact/view/rel?action=view&amp;reset=1&amp;cid=[near_contact_id]&amp;id=[relationship_id]</view>
    <update>civicrm/contact/view/rel?action=update&amp;reset=1&amp;cid=[near_contact_id]&amp;id=[relationship_id]&amp;rtype=[orientation]</update>
    <delete>civicrm/contact/view/rel?action=delete&amp;reset=1&amp;cid=[near_contact_id]&amp;id=[relationship_id]</delete>
  </paths>

  <field>
    <name>id</name>
    <type>int unsigned</type>
    <title>Relationship Cache ID</title>
    <required>true</required>
    <comment>Relationship Cache ID</comment>
    <html>
      <type>Number</type>
    </html>
    <add>5.29</add>
  </field>
  <primaryKey>
    <name>id</name>
    <autoincrement>true</autoincrement>
  </primaryKey>

  <field>
    <name>relationship_id</name>
    <type>int unsigned</type>
    <title>Relationship ID</title>
    <required>true</required>
    <comment>id of the relationship (FK to civicrm_relationship.id)</comment>
    <html>
      <label>Relationship</label>
    </html>
    <add>5.29</add>
    <readonly>true</readonly>
  </field>
  <foreignKey>
    <name>relationship_id</name>
    <table>civicrm_relationship</table>
    <key>id</key>
    <add>5.29</add>
    <onDelete>CASCADE</onDelete>
  </foreignKey>

  <field>
    <name>relationship_type_id</name>
    <type>int unsigned</type>
    <title>Relationship Type ID</title>
    <required>true</required>
    <comment>id of the relationship type</comment>
    <html>
      <label>Relationship Type</label>
    </html>
    <add>5.29</add>
    <readonly>true</readonly>
  </field>
  <foreignKey>
    <name>relationship_type_id</name>
    <table>civicrm_relationship_type</table>
    <key>id</key>
    <add>5.29</add>
    <onDelete>CASCADE</onDelete>
  </foreignKey>

  <field>
    <name>orientation</name>
    <type>char</type>
    <length>3</length>
    <title>Orientation (a_b or b_a)</title>
    <required>true</required>
    <pseudoconstant>
      <callback>CRM_Core_SelectValues::relationshipOrientation</callback>
    </pseudoconstant>
    <comment>The cache record is a permutation of the original relationship record. The orientation indicates whether it is forward (a_b) or reverse (b_a) relationship.</comment>
    <add>5.29</add>
    <readonly>true</readonly>
  </field>

  <field>
    <name>near_contact_id</name>
    <type>int unsigned</type>
    <title>Contact ID (Near side)</title>
    <required>true</required>
    <comment>id of the first contact</comment>
    <add>5.29</add>
    <html>
      <type>EntityRef</type>
      <label>Contact (Near side)</label>
    </html>
    <readonly>true</readonly>
  </field>
  <foreignKey>
    <name>near_contact_id</name>
    <table>civicrm_contact</table>
    <key>id</key>
    <add>5.29</add>
    <onDelete>CASCADE</onDelete>
  </foreignKey>

  <field>
    <name>near_relation</name>
    <type>varchar</type>
    <title>Relationship Name (to related contact)</title>
    <length>64</length>
    <comment>name for relationship of near_contact to far_contact.</comment>
    <add>5.29</add>
    <html>
      <label>Relationship to contact</label>
    </html>
    <pseudoconstant>
      <callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
    </pseudoconstant>
    <readonly>true</readonly>
  </field>

  <field>
    <name>far_contact_id</name>
    <type>int unsigned</type>
    <title>Contact ID (Far side)</title>
    <required>true</required>
    <comment>id of the second contact</comment>
    <add>5.29</add>
    <html>
      <type>EntityRef</type>
      <label>Contact (Far side)</label>
    </html>
    <readonly>true</readonly>
  </field>
  <foreignKey>
    <name>far_contact_id</name>
    <table>civicrm_contact</table>
    <key>id</key>
    <add>5.29</add>
    <onDelete>CASCADE</onDelete>
  </foreignKey>

  <field>
    <name>far_relation</name>
    <type>varchar</type>
    <title>Relationship Name (from related contact)</title>
    <length>64</length>
    <comment>name for relationship of far_contact to near_contact.</comment>
    <add>5.29</add>
    <html>
      <label>Relationship from contact</label>
    </html>
    <pseudoconstant>
      <callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
    </pseudoconstant>
    <readonly>true</readonly>
  </field>

  <index>
    <name>UI_relationship</name>
    <fieldName>relationship_id</fieldName>
    <fieldName>orientation</fieldName>
    <unique>true</unique>
    <add>5.29</add>
  </index>
  <index>
    <!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and near_relation = 'Parent of' -->
    <name>index_nearid_nearrelation</name>
    <fieldName>near_contact_id</fieldName>
    <fieldName>near_relation</fieldName>
    <add>5.29</add>
  </index>
  <index>
    <!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and far_relation = 'Child of' -->
    <name>index_nearid_farrelation</name>
    <fieldName>near_contact_id</fieldName>
    <fieldName>far_relation</fieldName>
    <add>5.29</add>
  </index>
  <index>
    <!-- Ex: select relation, count(*) from relcache group by near_relation -->
    <name>index_near_relation</name>
    <fieldName>near_relation</fieldName>
    <add>5.29</add>
  </index>

  <!-- Passive mirror fields -->

  <field>
    <name>is_active</name>
    <type>boolean</type>
    <title>Relationship Is Active</title>
    <default>1</default>
    <comment>is the relationship active ?</comment>
    <add>5.29</add>
    <html>
      <type>CheckBox</type>
    </html>
    <readonly>true</readonly>
  </field>
  <field>
    <name>start_date</name>
    <uniqueName>relationship_start_date</uniqueName>
    <type>date</type>
    <title>Relationship Start Date</title>
    <comment>date when the relationship started</comment>
    <add>5.29</add>
    <html>
      <type>Select Date</type>
      <formatType>activityDate</formatType>
    </html>
    <readonly>true</readonly>
  </field>
  <field>
    <name>end_date</name>
    <uniqueName>relationship_end_date</uniqueName>
    <type>date</type>
    <title>Relationship End Date</title>
    <comment>date when the relationship ended</comment>
    <add>5.29</add>
    <html>
      <type>Select Date</type>
      <formatType>activityDate</formatType>
    </html>
    <readonly>true</readonly>
  </field>
  <field>
    <name>case_id</name>
    <type>int unsigned</type>
    <component>CiviCase</component>
    <title>Case ID</title>
    <default>NULL</default>
    <comment>FK to civicrm_case</comment>
    <html>
      <label>Case</label>
    </html>
    <add>5.44</add>
    <readonly>true</readonly>
  </field>
  <foreignKey>
    <name>case_id</name>
    <table>civicrm_case</table>
    <key>id</key>
    <add>2.2</add>
    <onDelete>CASCADE</onDelete>
  </foreignKey>
</table>
Commit	Line	Data
e752bdac TO	1	<?xml version="1.0" encoding="iso-8859-1" ?>
	2
	3	<table>
	4	<base>CRM/Contact</base>
bcf70e08 TO	5	<class>RelationshipCache</class>
	6	<name>civicrm_relationship_cache</name>
	7	<comment>The cache permutes information from the relationship table to facilitate querying. Every relationship is mapped to multiple records in the cache. Joins should begin on the near side and extract info from the far side.</comment>
e752bdac TO	8	<add>5.29</add>
	9	<log>false</log>
	10	<icon>fa-handshake-o</icon>
c5368b3b	11	<title>Related Contact</title>
c5076889 CW	12	<paths>
	13	<view>civicrm/contact/view/rel?action=view&reset=1&cid=[near_contact_id]&id=[relationship_id]</view>
	14	<update>civicrm/contact/view/rel?action=update&reset=1&cid=[near_contact_id]&id=[relationship_id]&rtype=[orientation]</update>
	15	<delete>civicrm/contact/view/rel?action=delete&reset=1&cid=[near_contact_id]&id=[relationship_id]</delete>
	16	</paths>
	17
e752bdac TO	18	<field>
	19	<name>id</name>
	20	<type>int unsigned</type>
bcf70e08	21	<title>Relationship Cache ID</title>
e752bdac	22	<required>true</required>
bcf70e08	23	<comment>Relationship Cache ID</comment>
0481e12c A	24	<html>
	25	<type>Number</type>
	26	</html>
e752bdac TO	27	<add>5.29</add>
	28	</field>
	29	<primaryKey>
	30	<name>id</name>
	31	<autoincrement>true</autoincrement>
	32	</primaryKey>
	33
	34	<field>
	35	<name>relationship_id</name>
	36	<type>int unsigned</type>
57e94608	37	<title>Relationship ID</title>
e752bdac TO	38	<required>true</required>
e752bdac TO	39	<comment>id of the relationship (FK to civicrm_relationship.id)</comment>
57e94608 A	40	<html>
	41	<label>Relationship</label>
	42	</html>
e752bdac	43	<add>5.29</add>
ce16fdaa	44	<readonly>true</readonly>
e752bdac TO	45	</field>
	46	<foreignKey>
	47	<name>relationship_id</name>
	48	<table>civicrm_relationship</table>
	49	<key>id</key>
	50	<add>5.29</add>
	51	<onDelete>CASCADE</onDelete>
	52	</foreignKey>
	53
	54	<field>
	55	<name>relationship_type_id</name>
	56	<type>int unsigned</type>
57e94608	57	<title>Relationship Type ID</title>
e752bdac TO	58	<required>true</required>
e752bdac TO	59	<comment>id of the relationship type</comment>
57e94608 A	60	<html>
	61	<label>Relationship Type</label>
	62	</html>
e752bdac	63	<add>5.29</add>
ce16fdaa	64	<readonly>true</readonly>
e752bdac TO	65	</field>
	66	<foreignKey>
	67	<name>relationship_type_id</name>
	68	<table>civicrm_relationship_type</table>
	69	<key>id</key>
	70	<add>5.29</add>
	71	<onDelete>CASCADE</onDelete>
	72	</foreignKey>
	73
	74	<field>
	75	<name>orientation</name>
	76	<type>char</type>
	77	<length>3</length>
	78	<title>Orientation (a_b or b_a)</title>
	79	<required>true</required>
bf01b886 CW	80	<pseudoconstant>
	81	<callback>CRM_Core_SelectValues::relationshipOrientation</callback>
	82	</pseudoconstant>
bcf70e08	83	<comment>The cache record is a permutation of the original relationship record. The orientation indicates whether it is forward (a_b) or reverse (b_a) relationship.</comment>
e752bdac	84	<add>5.29</add>
ce16fdaa	85	<readonly>true</readonly>
e752bdac TO	86	</field>
	87
	88	<field>
	89	<name>near_contact_id</name>
	90	<type>int unsigned</type>
	91	<title>Contact ID (Near side)</title>
	92	<required>true</required>
	93	<comment>id of the first contact</comment>
	94	<add>5.29</add>
bf01b886 CW	95	<html>
bf01b886 CW	96	<type>EntityRef</type>
57e94608	97	<label>Contact (Near side)</label>
bf01b886	98	</html>
ce16fdaa	99	<readonly>true</readonly>
e752bdac TO	100	</field>
	101	<foreignKey>
	102	<name>near_contact_id</name>
	103	<table>civicrm_contact</table>
	104	<key>id</key>
	105	<add>5.29</add>
	106	<onDelete>CASCADE</onDelete>
	107	</foreignKey>
	108
	109	<field>
	110	<name>near_relation</name>
	111	<type>varchar</type>
c5368b3b	112	<title>Relationship Name (to related contact)</title>
e752bdac TO	113	<length>64</length>
	114	<comment>name for relationship of near_contact to far_contact.</comment>
	115	<add>5.29</add>
344c48b1 CW	116	<html>
	117	<label>Relationship to contact</label>
	118	</html>
bf01b886 CW	119	<pseudoconstant>
	120	<callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
	121	</pseudoconstant>
ce16fdaa	122	<readonly>true</readonly>
e752bdac TO	123	</field>
	124
	125	<field>
	126	<name>far_contact_id</name>
	127	<type>int unsigned</type>
	128	<title>Contact ID (Far side)</title>
	129	<required>true</required>
	130	<comment>id of the second contact</comment>
	131	<add>5.29</add>
	132	<html>
	133	<type>EntityRef</type>
57e94608	134	<label>Contact (Far side)</label>
e752bdac	135	</html>
ce16fdaa	136	<readonly>true</readonly>
e752bdac TO	137	</field>
	138	<foreignKey>
	139	<name>far_contact_id</name>
	140	<table>civicrm_contact</table>
	141	<key>id</key>
	142	<add>5.29</add>
	143	<onDelete>CASCADE</onDelete>
	144	</foreignKey>
	145
	146	<field>
	147	<name>far_relation</name>
	148	<type>varchar</type>
c5368b3b	149	<title>Relationship Name (from related contact)</title>
e752bdac TO	150	<length>64</length>
	151	<comment>name for relationship of far_contact to near_contact.</comment>
	152	<add>5.29</add>
344c48b1 CW	153	<html>
	154	<label>Relationship from contact</label>
	155	</html>
bf01b886 CW	156	<pseudoconstant>
	157	<callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
	158	</pseudoconstant>
ce16fdaa	159	<readonly>true</readonly>
e752bdac TO	160	</field>
	161
	162	<index>
	163	<name>UI_relationship</name>
	164	<fieldName>relationship_id</fieldName>
	165	<fieldName>orientation</fieldName>
	166	<unique>true</unique>
	167	<add>5.29</add>
	168	</index>
	169	<index>
bcf70e08	170	<!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and near_relation = 'Parent of' -->
e752bdac TO	171	<name>index_nearid_nearrelation</name>
	172	<fieldName>near_contact_id</fieldName>
	173	<fieldName>near_relation</fieldName>
	174	<add>5.29</add>
	175	</index>
	176	<index>
bcf70e08	177	<!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and far_relation = 'Child of' -->
e752bdac TO	178	<name>index_nearid_farrelation</name>
	179	<fieldName>near_contact_id</fieldName>
	180	<fieldName>far_relation</fieldName>
	181	<add>5.29</add>
	182	</index>
	183	<index>
bcf70e08	184	<!-- Ex: select relation, count(*) from relcache group by near_relation -->
e752bdac TO	185	<name>index_near_relation</name>
	186	<fieldName>near_relation</fieldName>
	187	<add>5.29</add>
	188	</index>
	189
	190	<!-- Passive mirror fields -->
	191
	192	<field>
	193	<name>is_active</name>
	194	<type>boolean</type>
	195	<title>Relationship Is Active</title>
	196	<default>1</default>
	197	<comment>is the relationship active ?</comment>
	198	<add>5.29</add>
	199	<html>
	200	<type>CheckBox</type>
	201	</html>
ce16fdaa	202	<readonly>true</readonly>
e752bdac TO	203	</field>
	204	<field>
	205	<name>start_date</name>
	206	<uniqueName>relationship_start_date</uniqueName>
	207	<type>date</type>
	208	<title>Relationship Start Date</title>
	209	<comment>date when the relationship started</comment>
	210	<add>5.29</add>
	211	<html>
	212	<type>Select Date</type>
	213	<formatType>activityDate</formatType>
	214	</html>
ce16fdaa	215	<readonly>true</readonly>
e752bdac TO	216	</field>
	217	<field>
	218	<name>end_date</name>
	219	<uniqueName>relationship_end_date</uniqueName>
	220	<type>date</type>
	221	<title>Relationship End Date</title>
	222	<comment>date when the relationship ended</comment>
	223	<add>5.29</add>
	224	<html>
	225	<type>Select Date</type>
	226	<formatType>activityDate</formatType>
	227	</html>
ce16fdaa	228	<readonly>true</readonly>
e752bdac	229	</field>
bcd7e8d9 CW	230	<field>
	231	<name>case_id</name>
	232	<type>int unsigned</type>
	233	<component>CiviCase</component>
	234	<title>Case ID</title>
	235	<default>NULL</default>
	236	<comment>FK to civicrm_case</comment>
	237	<html>
	238	<label>Case</label>
	239	</html>
	240	<add>5.44</add>
	241	<readonly>true</readonly>
	242	</field>
	243	<foreignKey>
	244	<name>case_id</name>
	245	<table>civicrm_case</table>
	246	<key>id</key>
	247	<add>2.2</add>
	248	<onDelete>CASCADE</onDelete>
	249	</foreignKey>
e752bdac	250	</table>