<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN"
          "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">

<jbosscmp-jdbc>
    <defaults>
        <datasource>java:/DefaultDS</datasource>
        <datasource-mapping>Hypersonic SQL</datasource-mapping>
        <create-table>true</create-table>
        <remove-table>true</remove-table>
        <pk-constraint>true</pk-constraint>
        <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
    </defaults>
    
    <enterprise-beans>
        
        <entity>
            <ejb-name>OrganizationEJB</ejb-name>
            <table-name>organization</table-name>
            
            <cmp-field>
                <field-name>name</field-name>
                <column-name>name</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>description</field-name>
                <column-name>desc</column-name>
                <not-null/>
            </cmp-field>
            <query>
                <query-method>
                    <method-name>ejbSelectOperatingZipCodes_declaredsql</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <declared-sql>
                    <select>
                        <distinct/>
                        <ejb-name>LocationEJB</ejb-name>
                        <field-name>zipCode</field-name>
                        <alias>hangout</alias>
                    </select>
                    <from><![CDATA[ , organization o, gangster g ]]></from>
                    <where><![CDATA[
                    LCASE(o.name) = {0} AND o.name = g.organization AND
                    g.hangout = hangout.id
                    ]]></where>
                    <order><![CDATA[ hangout.zip ]]></order>
                </declared-sql>
            </query>
        </entity>
        
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <table-name>gangster</table-name>
            
            <cmp-field>
                <field-name>gangsterId</field-name>
                <column-name>id</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>name</field-name>
                <column-name>name</column-name>
                <not-null/>
            </cmp-field>
            <cmp-field>
                <field-name>nickName</field-name>
                <column-name>nick_name</column-name>
                <jdbc-type>VARCHAR</jdbc-type>
                <sql-type>VARCHAR(64)</sql-type>
            </cmp-field>
            <cmp-field>
                <field-name>badness</field-name>
                <column-name>badness</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>contactInfo</field-name>
                <property>
                    <property-name>cell.areaCode</property-name>
                    <column-name>cell_area</column-name>
                </property>
                <property>
                    <property-name>cell.exchange</property-name>
                    <column-name>cell_exch</column-name>
                </property>
                <property>
                    <property-name>cell.extension</property-name>
                    <column-name>cell_ext</column-name>
                </property>
                
                <property>
                    <property-name>pager.areaCode</property-name>
                    <column-name>page_area</column-name>
                </property>
                <property>
                    <property-name>pager.exchange</property-name>
                    <column-name>page_exch</column-name>
                </property>
                <property>
                    <property-name>pager.extension</property-name>
                    <column-name>page_ext</column-name>
                </property>
                
                <property>
                    <property-name>email</property-name>
                    <column-name>email</column-name>
                    <jdbc-type>VARCHAR</jdbc-type>
                    <sql-type>VARCHAR(128)</sql-type>
                </property>
            </cmp-field>
            
            <load-groups>
                <load-group>
                    <load-group-name>basic</load-group-name>
                    <field-name>name</field-name>
                    <field-name>nickName</field-name>
                    <field-name>badness</field-name>
                </load-group>
                <load-group>
                    <load-group-name>contact info</load-group-name>
                    <field-name>nickName</field-name>
                    <field-name>contactInfo</field-name>
                    <field-name>hangout</field-name>
                </load-group>
                
                <!-- 
                     This group is used in place of the default star group
                     to make readahead test sql easier to read.  It contains
                     all fields except the contactInfo field.
                -->
                <load-group>
                    <load-group-name>most</load-group-name>
                    <field-name>name</field-name>
                    <field-name>nickName</field-name>
                    <field-name>badness</field-name>
                    <field-name>hangout</field-name>
                    <field-name>organization</field-name>
                </load-group>
            </load-groups>
            
            <eager-load-group>most</eager-load-group>
            
            <lazy-load-groups>
                <load-group-name>basic</load-group-name>
                <load-group-name>contact info</load-group-name>
            </lazy-load-groups>
            
            <query>
                <query-method>
                    <method-name>findBadDudes_jbossql</method-name>
                    <method-params><method-param>int</method-param></method-params>
                </query-method>
                <jboss-ql><![CDATA[
                SELECT OBJECT(g)
                FROM gangster g
                WHERE g.badness > ?1
                ORDER BY g.badness DESC
                ]]></jboss-ql>
            </query>
            
            <query>
                <query-method>
                    <method-name>findBadDudes_declaredsql</method-name>
                    <method-params><method-param>int</method-param></method-params>
                </query-method>
                <declared-sql>
                    <where><![CDATA[ badness > {0} ]]></where>
                    <order><![CDATA[ badness DESC ]]></order>
                </declared-sql>
            </query>
            
            <query>
                <query-method>
                    <method-name>ejbSelectBoss_declaredsql</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <declared-sql>
                    <select>
                        <distinct/>
                        <ejb-name>GangsterEJB</ejb-name>
                        <alias>boss</alias>
                    </select>
                    <from><![CDATA[, gangster g, organization o]]></from>
                    <where><![CDATA[
                    (LCASE(g.name) = {0} OR LCASE(g.nick_name) = {0}) AND 
                    g.organization = o.name AND o.the_boss = boss.id
                    ]]></where>
                </declared-sql>
            </query>
            
            <query>
                <query-method>
                    <method-name>ejbSelectGeneric</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                        <method-param>java.lang.Object[]</method-param>
                    </method-params>
                </query-method>
                <dynamic-ql/>
            </query>
            
            <query>
                <query-method>
                    <method-name>findAll_onfind</method-name>
                    <method-params/>
                </query-method>
                <jboss-ql><![CDATA[
                SELECT OBJECT(g)
                FROM gangster g
                ORDER BY g.gangsterId
                ]]></jboss-ql>
                <read-ahead>
                    <strategy>on-find</strategy>
                    <page-size>4</page-size>
                    <eager-load-group>basic</eager-load-group>
                </read-ahead>
            </query>
            
            <query>
                <query-method>
                    <method-name>findAll_onload</method-name>
                    <method-params/>
                </query-method>
                <jboss-ql><![CDATA[
                SELECT OBJECT(g)
                FROM gangster g
                ORDER BY g.gangsterId
                ]]></jboss-ql>
                <read-ahead>
                    <strategy>on-load</strategy>
                    <page-size>4</page-size>
                    <eager-load-group>basic</eager-load-group>
                </read-ahead>
            </query>
            
            <query>
                <query-method>
                    <method-name>findAll_none</method-name>
                    <method-params/>
                </query-method>
                <jboss-ql><![CDATA[
                SELECT OBJECT(g)
                FROM gangster g
                ORDER BY g.gangsterId
                ]]></jboss-ql>
                <read-ahead>
                    <strategy>none</strategy>
                </read-ahead>
            </query>
            
            <query>
                <query-method>
                    <method-name>findFour</method-name>
                    <method-params/>
                </query-method>
                <jboss-ql><![CDATA[
                SELECT OBJECT(g)
                FROM gangster g
                WHERE g.gangsterId < 4
                ORDER BY g.gangsterId
                ]]></jboss-ql>
                <read-ahead>
                    <strategy>on-find</strategy>
                    <page-size>1000</page-size>
                    <eager-load-group>basic</eager-load-group>
                </read-ahead>
            </query>
        </entity>
        
        <entity>
            <ejb-name>JobEJB</ejb-name>
            <table-name>job</table-name>
            
            <cmp-field>
                <field-name>name</field-name>
                <column-name>name</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>score</field-name>
                <column-name>score</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>setupCost</field-name>
                <column-name>setup_cost</column-name>
            </cmp-field>
        </entity>
        
        <entity>
            <ejb-name>LocationEJB</ejb-name>
            <pk-constraint>false</pk-constraint>
            <table-name>location</table-name>
            
            <cmp-field>
                <field-name>locationID</field-name>
                <column-name>id</column-name>
                <auto-increment/>
            </cmp-field>
            <cmp-field>
                <field-name>description</field-name>
                <column-name>description</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>street</field-name>
                <column-name>street</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>city</field-name>
                <column-name>city</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>state</field-name>
                <column-name>st</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>zipCode</field-name>
                <column-name>zip</column-name>
            </cmp-field>
            
            <load-groups>
                <load-group>
                    <load-group-name>quick info</load-group-name>
                    <field-name>city</field-name>
                    <field-name>state</field-name>
                    <field-name>zipCode</field-name>
                </load-group>
            </load-groups>
            <eager-load-group/>
            <!--
                <unknown-pk>
                <unknown-pk-class>java.lang.Integer</unknown-pk-class>
                <field-name>locationID</field-name>
                <column-name>id</column-name>
                <jdbc-type>INTEGER</jdbc-type>
                <sql-type>INTEGER</sql-type>
                <auto-increment/>
                </unknown-pk>
            -->
            <entity-command name="hsqldb-fetch-key"/>
            
        </entity>
    </enterprise-beans>
    
    <relationships>
        <ejb-relation>
            <ejb-relation-name>Organization-Gangster</ejb-relation-name>
            <foreign-key-mapping/>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>org-has-gangsters</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>name</field-name>
                        <column-name>organization</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-belongs-to-org</ejb-relationship-role-name>
                <key-fields/>
            </ejb-relationship-role>
        </ejb-relation>
        
        <ejb-relation>
            <ejb-relation-name>Gangster-Jobs</ejb-relation-name>
            <relation-table-mapping>
                <table-name>gangster_job</table-name>
            </relation-table-mapping>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-has-jobs</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>gangsterId</field-name>
                        <column-name>gangster</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>job-has-gangsters</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>name</field-name>
                        <column-name>job</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
        </ejb-relation>
        
        <ejb-relation>
            <ejb-relation-name>Organization-Boss</ejb-relation-name>
            <foreign-key-mapping/>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>organization-has-a-boss</ejb-relationship-role-name>
                <key-fields/>
            </ejb-relationship-role>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>boss-runs-an-organization</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>gangsterId</field-name>
                        <column-name>the_boss</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
        </ejb-relation>
        
        <ejb-relation>
            <ejb-relation-name>Gangster-Enemies</ejb-relation-name>
            <relation-table-mapping>
                <table-name>gangster_enemies</table-name>
            </relation-table-mapping>
            
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-has-enemies</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>gangsterId</field-name>
                        <column-name>gangster_1</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>enemy-to-gangsters</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>gangsterId</field-name>
                        <column-name>gangster_2</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
        </ejb-relation>
        
        <ejb-relation>
            <ejb-relation-name>Gangster-Hangout</ejb-relation-name>
            <foreign-key-mapping/>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-has-a-hangout</ejb-relationship-role-name>
                <key-fields/>
                
                <read-ahead>
                    <strategy>on-find</strategy>
                    <page-size>4</page-size>
                    <eager-load-group>quick info</eager-load-group>
                </read-ahead>
            </ejb-relationship-role>
            
            <ejb-relationship-role>
                <ejb-relationship-role-name>hangout-for-a-gangster</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>locationID</field-name>
                        <column-name>hangout</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
        </ejb-relation>
    </relationships>
    
    <dependent-value-classes>
        <dependent-value-class>
            <description>A phone number</description>
            <class>org.jboss.cmp2.crimeportal.PhoneNumber</class>
            <property>
                <property-name>areaCode</property-name>
                <column-name>area_code</column-name>
            </property>
            <property>
                <property-name>exchange</property-name>
                <column-name>exchange</column-name>
            </property>
            <property>
                <property-name>extension</property-name>
                <column-name>extension</column-name>
            </property>
        </dependent-value-class>
        
        <dependent-value-class>
            <description>General contact info</description>
            <class>org.jboss.cmp2.crimeportal.ContactInfo</class>
            <property>
                <property-name>cell</property-name>
                <column-name>cell</column-name>
            </property>
            <property>
                <property-name>pager</property-name>
                <column-name>pager</column-name>
            </property>
            <property>
                <property-name>email</property-name>
                <column-name>email</column-name>
                <jdbc-type>VARCHAR</jdbc-type>
                <sql-type>VARCHAR(128)</sql-type>
            </property>
        </dependent-value-class>
    </dependent-value-classes>
    
    
</jbosscmp-jdbc>
