[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>
  <field>
    <name>id</name>
    <type>int unsigned</type>
    <title>Relationship Cache ID</title>
    <required>true</required>
    <comment>Relationship Cache ID</comment>
    <add>5.29</add>
  </field>
  <primaryKey>
    <name>id</name>
    <autoincrement>true</autoincrement>
  </primaryKey>

  <field>
    <name>relationship_id</name>
    <type>int unsigned</type>
    <title>Relationship</title>
    <required>true</required>
    <comment>id of the relationship (FK to civicrm_relationship.id)</comment>
    <add>5.29</add>
  </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</title>
    <required>true</required>
    <comment>id of the relationship type</comment>
    <add>5.29</add>
  </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>
  </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>
    </html>
  </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>
    <pseudoconstant>
      <callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
    </pseudoconstant>
  </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>
    </html>
  </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>
    <pseudoconstant>
      <callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
    </pseudoconstant>
  </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>
  </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>
  </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>
  </field>
</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>
e752bdac TO	12	<field>
	13	<name>id</name>
	14	<type>int unsigned</type>
bcf70e08	15	<title>Relationship Cache ID</title>
e752bdac	16	<required>true</required>
bcf70e08	17	<comment>Relationship Cache ID</comment>
e752bdac TO	18	<add>5.29</add>
	19	</field>
	20	<primaryKey>
	21	<name>id</name>
	22	<autoincrement>true</autoincrement>
	23	</primaryKey>
	24
	25	<field>
	26	<name>relationship_id</name>
	27	<type>int unsigned</type>
	28	<title>Relationship</title>
	29	<required>true</required>
	30	<comment>id of the relationship (FK to civicrm_relationship.id)</comment>
	31	<add>5.29</add>
	32	</field>
	33	<foreignKey>
	34	<name>relationship_id</name>
	35	<table>civicrm_relationship</table>
	36	<key>id</key>
	37	<add>5.29</add>
	38	<onDelete>CASCADE</onDelete>
	39	</foreignKey>
	40
	41	<field>
	42	<name>relationship_type_id</name>
	43	<type>int unsigned</type>
	44	<title>Relationship Type</title>
	45	<required>true</required>
	46	<comment>id of the relationship type</comment>
	47	<add>5.29</add>
	48	</field>
	49	<foreignKey>
	50	<name>relationship_type_id</name>
	51	<table>civicrm_relationship_type</table>
	52	<key>id</key>
	53	<add>5.29</add>
	54	<onDelete>CASCADE</onDelete>
	55	</foreignKey>
	56
	57	<field>
	58	<name>orientation</name>
	59	<type>char</type>
	60	<length>3</length>
	61	<title>Orientation (a_b or b_a)</title>
	62	<required>true</required>
bf01b886 CW	63	<pseudoconstant>
	64	<callback>CRM_Core_SelectValues::relationshipOrientation</callback>
	65	</pseudoconstant>
bcf70e08	66	<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 TO	67	<add>5.29</add>
	68	</field>
	69
	70	<field>
	71	<name>near_contact_id</name>
	72	<type>int unsigned</type>
	73	<title>Contact ID (Near side)</title>
	74	<required>true</required>
	75	<comment>id of the first contact</comment>
	76	<add>5.29</add>
bf01b886 CW	77	<html>
	78	<type>EntityRef</type>
	79	</html>
e752bdac TO	80	</field>
	81	<foreignKey>
	82	<name>near_contact_id</name>
	83	<table>civicrm_contact</table>
	84	<key>id</key>
	85	<add>5.29</add>
	86	<onDelete>CASCADE</onDelete>
	87	</foreignKey>
	88
	89	<field>
	90	<name>near_relation</name>
	91	<type>varchar</type>
c5368b3b	92	<title>Relationship Name (to related contact)</title>
e752bdac TO	93	<length>64</length>
	94	<comment>name for relationship of near_contact to far_contact.</comment>
	95	<add>5.29</add>
bf01b886 CW	96	<pseudoconstant>
	97	<callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
	98	</pseudoconstant>
e752bdac TO	99	</field>
	100
	101	<field>
	102	<name>far_contact_id</name>
	103	<type>int unsigned</type>
	104	<title>Contact ID (Far side)</title>
	105	<required>true</required>
	106	<comment>id of the second contact</comment>
	107	<add>5.29</add>
	108	<html>
	109	<type>EntityRef</type>
	110	</html>
	111	</field>
	112	<foreignKey>
	113	<name>far_contact_id</name>
	114	<table>civicrm_contact</table>
	115	<key>id</key>
	116	<add>5.29</add>
	117	<onDelete>CASCADE</onDelete>
	118	</foreignKey>
	119
	120	<field>
	121	<name>far_relation</name>
	122	<type>varchar</type>
c5368b3b	123	<title>Relationship Name (from related contact)</title>
e752bdac TO	124	<length>64</length>
	125	<comment>name for relationship of far_contact to near_contact.</comment>
	126	<add>5.29</add>
bf01b886 CW	127	<pseudoconstant>
	128	<callback>CRM_Core_PseudoConstant::relationshipTypeOptions</callback>
	129	</pseudoconstant>
e752bdac TO	130	</field>
	131
	132	<index>
	133	<name>UI_relationship</name>
	134	<fieldName>relationship_id</fieldName>
	135	<fieldName>orientation</fieldName>
	136	<unique>true</unique>
	137	<add>5.29</add>
	138	</index>
	139	<index>
bcf70e08	140	<!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and near_relation = 'Parent of' -->
e752bdac TO	141	<name>index_nearid_nearrelation</name>
	142	<fieldName>near_contact_id</fieldName>
	143	<fieldName>near_relation</fieldName>
	144	<add>5.29</add>
	145	</index>
	146	<index>
bcf70e08	147	<!-- Ex: select ... from contact inner join relcache on contact.id=relcache.near_contact_id and far_relation = 'Child of' -->
e752bdac TO	148	<name>index_nearid_farrelation</name>
	149	<fieldName>near_contact_id</fieldName>
	150	<fieldName>far_relation</fieldName>
	151	<add>5.29</add>
	152	</index>
	153	<index>
bcf70e08	154	<!-- Ex: select relation, count(*) from relcache group by near_relation -->
e752bdac TO	155	<name>index_near_relation</name>
	156	<fieldName>near_relation</fieldName>
	157	<add>5.29</add>
	158	</index>
	159
	160	<!-- Passive mirror fields -->
	161
	162	<field>
	163	<name>is_active</name>
	164	<type>boolean</type>
	165	<title>Relationship Is Active</title>
	166	<default>1</default>
	167	<comment>is the relationship active ?</comment>
	168	<add>5.29</add>
	169	<html>
	170	<type>CheckBox</type>
	171	</html>
	172	</field>
	173	<field>
	174	<name>start_date</name>
	175	<uniqueName>relationship_start_date</uniqueName>
	176	<type>date</type>
	177	<title>Relationship Start Date</title>
	178	<comment>date when the relationship started</comment>
	179	<add>5.29</add>
	180	<html>
	181	<type>Select Date</type>
	182	<formatType>activityDate</formatType>
	183	</html>
	184	</field>
	185	<field>
	186	<name>end_date</name>
	187	<uniqueName>relationship_end_date</uniqueName>
	188	<type>date</type>
	189	<title>Relationship End Date</title>
	190	<comment>date when the relationship ended</comment>
	191	<add>5.29</add>
	192	<html>
	193	<type>Select Date</type>
	194	<formatType>activityDate</formatType>
	195	</html>
	196	</field>
e752bdac	197	</table>