Hi All, Here is my situation for writing xsd's had 3 c

Hi All,

Here is my situation for writing xsd's

had 3 cdt's

1. client - xsd created - table created
2. employee - xsd created - table created

3. pay

in this pay cdt PK of client and PK of employee are the foreign keys and they are mapped with "many to one" relationship with those 2 (means many records in pay can have single client or employee )

I already have referred forum.appian.com/.../Defining_a_Custom_Data_Type

and written xsd for pay with following annotations.

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns="urn:appian:example:assocations:many-to-one" elementFormDefault="qualified" targetNamespace="urn:appian:example:assocations:many-to-one" xmlns:xsd="www.w3.org/.../XMLSchema">

<xsd:complexType name="pay">
--
<xsd:element minOccurs="0" nillable="false" maxOccurs="unbounded" name="clientId" type="client">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@ManyToOne
                     ...

OriginalPostID-77431

OriginalPostID-77431

  Discussion posts and replies are publicly visible

  • ...@JoinColumn(name="clientidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>          
              <xsd:element minOccurs="0" nillable="false" maxOccurs="unbounded" name="employeeID" type="employee">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @ManyToOne
                         @JoinColumn(name="employeeidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
    --
    </xsd:complexType>
    <xsd:complexType name="client">
    --
    --
    </xsd:complexType>
    xsd:complexType name="employee">--
    --
    </xsd:complexType>
    </xsd:schema>

    After i upload the xsd appian throws an error, "A type mapping annotation is invalid: The feature/eclass clientId/payDT320 should be a OneToMany but it already has a ManyToMany, OneToOne or ManyToOne annotation (APNX-2-4055-000) "

    1. Do i need to define client and employee types in pay as i already uploaded in appian ? (if no, appian is not recognizing the existing typ...
  • ...es when in upload pay xsd)
    2. In declaring ManyToOne i want to have type as integer but not the client/employee as only their id's are foreign keys but not the whole cdt's. How to declare just their id's as foreign keys in xsd
    3. In formum there is no example for manytoone. please let me know how to do it, especially the schema definitions, xmlns, targetNamespace, etc.,
  • Sarat, I haven't tried your relationship but in general, I tend to keep the reverse relation to a minimum in my data model. Instead of a many to one association within the CDT, you may want to consider holding the PK in the related table. This will allow you to query the data on demand rather than loading the entire data all the time which may or may not be necessary.
  • Unless you need bidirectional relationships, the definition should be in client and employee. Meaning that client/employee should have an XSD element referencing pay with the ManyToOne relationship. Using the PK is the default behavior.
  • Sarat, Try the following structure:
    <?xml version="1.0" encoding="utf-8" ?>
    <xsd:schema xmlns="www.appian.com/.../" elementFormDefault="qualified" targetNamespace="www.appian.com/.../" xmlns:xsd="www.w3.org/.../XMLSchema">
    <xsd:complexType name="pay">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @Table(name="pay")
    </xsd:appinfo>
    </xsd:annotation>
    <xsd:sequence>
    <xsd:element nillable="false" name="clientId" type="client">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @ManyToOne
    @JoinColumn(name="clientidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
              <xsd:element nillable="false" name="employeeID" type="emp">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @ManyToOne
              @JoinColumn(name="employeeidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
    </xsd:sequence>
                         </xsd:complexType>          
    <xsd:complexType name="emp">
    -----
    </xsd:complexType>
               <xsd:complexType name="client">
    --------
    </xsd:complexType>                    
    </xsd:schema>
  • Sarat, I think the problem is with the @ManyToOne annotated element's structure. As far as my knowledge is considered, minoccurs=0 maxoccurs=unbounded is used in combination with either many-many or one-many. This could be a reason for the validation thrown saying that there is an relationship already constructed.
  • sikhivahans suggested format of xsd did worked.
    There were no errors at appian level but when i am trying to create table, it is throwing errors,
    "An error occurred while trying to modify the data source schema:
    1. Identity column 'payidinteger' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.;
    2. Cannot find the object "dproprEmployeePay" because it does not exist or you do not have permissions.;
    3. Cannot find the object "dproprEmployeePay" because it does not exist or you do not have permissions. (APNX-1-4178-000)"

    Then i have examined the .sql script

    create table [dproprEmployeePay] (
    [payidinteger] numeric(19,2) identity not null,
    [clientidinteger] numeric(19,0) null unique,
    [employeeidinteger] numeric(19,0) null unique,
    --
                        --
                        --
    primary key ([payidinteger])
    );
    alter table [dproprEmployeePay]
    add constraint dprprcdtemplypy_mplyidintgr
    foreign key ([employeeidinteger])
    references [dproprEmployee];

    alter table [dproprEmployeePay]
    add constraint dprprcdtmplypy_clintidintgr
    foreign key ([clientidinteger])
    references [dproprClient];

    Among the errors thrown, 1st one may be reg [payidinteger] numeric(19,2) may be it should be numeric(19,0)
    Dont know why it generated 19,2 when i have given
    <xsd:element name="payId_Integer" nillable="false" type="xsd:integer">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @Id
    @GeneratedValue
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>

    And remaining 2 may be bcz that it is trying to execute alter on a non-existing table bcz of 1st error.
  • Also it should be "not null" for

    [clientidinteger] numeric(19,0) null unique,
    [employeeidinteger] numeric(19,0) null unique,

    i have given nillble & nullable false for both elements.

    <xsd:element nillable="false" name="clientId_Integer" type="DPROPR_cdtClient">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @ManyToOne
                         @JoinColumn(name="clientidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
              
              <xsd:element nillable="false" name="employeeID_Integer" type="DPROPR_cdtEmployee">
    <xsd:annotation>
    <xsd:appinfo source="appian.jpa">
    @ManyToOne
                         @JoinColumn(name="employeeidinteger", nullable=false, unique=true)
    </xsd:appinfo>
    </xsd:annotation>
    </xsd:element>
  • @sarat, Are you comfortable with the XSD, or still do you have any issues?
  • Now i have placed
    <xsd:element name="payId_Integer" nillable="false" type="xsd:int">
    in place of integer
    <xsd:element name="payId_Integer" nillable="false" type="xsd:integer">

    and now all the 3 tables are created no problems.

    But still left with the issue,
    why
    [clientidinteger] numeric(19,0) null unique,
    [employeeidinteger] numeric(19,0) null unique,
    instead it should be
    [clientidinteger] numeric(19,0) not null unique,
    [employeeidinteger] numeric(19,0) not null unique,

    As i have given nillable false for element tag and nullable false for @Column