Loading ...
Sorry, an error occurred while loading the content.

How to build an ProductCatalog DataExchange Functionality

Expand Messages
  • Peter Burri
    Hello, I have a relational database which contains something like a product catalog and I would like to create a data-exchange functionality via
    Message 1 of 2 , Sep 8, 2003
    • 0 Attachment
      Hello,
      I have a relational database which contains something like a product
      catalog and I would like to create a data-exchange functionality via
      XML-Datafiles.

      I tried to use XMLDBMS but I have som problems to use this middleware
      - different structure between tables and the desired xml-structure
      output and
      - importing data without knowing the (datbase internal) object-id's
      (needs lookup?)

      ------------------------------------------------------------------------
      ---------------
      The Situation :
      ------------------------------------------------------------------------
      ---------------

      Naming:
      PK = Primary Key/Object ID
      CK = Conceptual Key, must be unique values
      <1---n> = a Relation

      Database Tables and Relations (not complete model) :

      +--------------+
      ! TFEATGROUP !
      !--------------!
      ! FTG_OID (PK) !<1-+
      ! FTG_NAME (CK)! !
      +--------------+ !
      !
      !
      +--------------+ +--------------+ +--------------+ !
      ! TNODE ! ! TNODFEAVAL ! ! TFEATURE ! !
      !--------------! !--------------! !--------------! !
      +---------------+
      ! NOD_OID (PK) !<1---n>! ITM_OID (FK) ! +-1>! FEA_OID (PK) ! ! !
      TFEATTEMPLATE !
      ! NOD_NAME(CK) ! ! FEA_OID (FK) !<n--+ ! FTG_OID (FK) !<n-+
      !---------------!
      +--------------+ ! VALUE,SEQNO ! ! FTT_OID (FK) !<n--1>!
      FTT_OID (PK) !
      +--------------+ +--------------+ !
      FTT_NAME (CK) !

      +---------------+
      Section Naming:
      <--ProductNodes> <--ProductFeatures--> <-----------
      FeatureCatalog------------------>

      TNODE : contains the ProductNodes
      TNODFEAVAL: contains mapped Features from the FeatureCatalog with the
      ProductNode specific
      value ( ProductFeature or InstanceOf a FEATURE)

      TFEATURE: contains all possible Features which can be assigned to a
      TNODE.
      A Feature has a Type/Template (TFEATTEMPLATE) and
      resides in a FeatureGroup (TFEATGROUP).
      ------------------------------------------------------------------------
      ---------------

      XMLDBMS V2 Database Mapping looks like this:

      <Table Name="TNODE">
      <Column Name="NOD_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="NOD_NAME" DataType="VARCHAR" Length="50"
      Nullable="No"/>
      <PrimaryKey KeyGenerator="Database">
      <UseColumn Name="NOD_OID"/>
      </PrimaryKey>
      <UniqueKey Name="NODE.CK">
      <UseColumn Name="NOD_NAME"/>
      </UniqueKey>
      </Table>
      <Table Name="TNODFEAVAL">
      <Column Name="ITM_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FEA_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="SEQNO" DataType="SMALLINT" Nullable="No"/>
      <Column Name="VALUE" DataType="VARCHAR" Length="200"
      Nullable="No"/>
      <UniqueKey Name="NODFEAVAL.CK">
      <UseColumn Name="ITM_OID"/>
      <UseColumn Name="FEA_OID"/>
      </UniqueKey>
      <ForeignKey Name="NODFEAVAL.FK">
      <UseColumn Name="ITM_OID"/>
      <UseTable Schema="&SCHEMA;" Name="TNODE"/>
      <UseUniqueKey Name="PrimaryKey"/>
      </ForeignKey>
      <ForeignKey Name="FEATURE.FK">
      <UseColumn Name="FEA_OID"/>
      <UseTable Schema="&SCHEMA;" Name="TFEATURE"/>
      <UseUniqueKey Name="PrimaryKey"/>
      </ForeignKey>
      </Table>
      <Table Name="TFEATURE">
      <Column Name="FEA_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FTG_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FTT_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FEA_NAME" DataType="VARCHAR" Length="50"
      Nullable="No"/>
      <PrimaryKey KeyGenerator="Database">
      <UseColumn Name="FEA_OID"/>
      </PrimaryKey>
      <UniqueKey Name="FEATURE.CK">
      <UseColumn Name="FTG_OID"/>
      <UseColumn Name="FEA_NAME"/>
      </UniqueKey>
      <ForeignKey Name="FEATEMPLATE.FK">
      <UseColumn Name="FTT_OID"/>
      <UseTable Schema="&SCHEMA;" Name="TFEATTEMPLATE"/>
      <UseUniqueKey Name="PrimaryKey"/>
      </ForeignKey>
      <ForeignKey Name="FEAGROUP.FK">
      <UseColumn Name="FTG_OID"/>
      <UseTable Schema="&SCHEMA;" Name="TFEATGROUP"/>
      <UseUniqueKey Name="PrimaryKey"/>
      </ForeignKey>
      </Table>
      <Table Name="TFEATGROUP">
      <Column Name="FTG_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FTG_NAME" DataType="VARCHAR" Length="50"
      Nullable="No"/>
      <PrimaryKey KeyGenerator="Database">
      <UseColumn Name="FTG_OID"/>
      </PrimaryKey>
      <UniqueKey Name="FEAGROUP.CK">
      <UseColumn Name="FTG_NAME"/>
      </UniqueKey>
      </Table>
      <Table Name="TFEATTEMPLATE">
      <Column Name="FTT_OID" DataType="INTEGER" Nullable="No"/>
      <Column Name="FTT_NAME" DataType="VARCHAR" Length="50"
      Nullable="No"/>
      <Column Name="CHECK_RULE" DataType="LONGVARCHAR" Length="4096"
      Nullable="Yes"/>
      <PrimaryKey KeyGenerator="Database">
      <UseColumn Name="FTT_OID"/>
      </PrimaryKey>
      <UniqueKey Name="FEATEMPLATE.CK">
      <UseColumn Name="FTT_NAME"/>
      </UniqueKey>
      </Table>
      ------------------------------------------------------------------------
      ---------------
      Problems / Questions:
      ------------------------------------------------------------------------
      ---------------
      1. EXPORT Functionality: How should I write the XMLDBMX-Maps for
      generating an output XML-Document like
      the following example? ( flatten structure of the feature catalog)
      Can I do this with XMLDBMS ?

      <NodeList>
      <Node Name="NODE-A">
      <FeatureList>
      <ProductFeature seq="1">
      <FeatureGroup>UserData</FeatureGroup>
      <FeatureName>ComputerName</FeatureName>
      <Value>SF4711</Value>
      </ProductFeature>
      </FeatureList>
      </Node>
      <Node Name="NODE-B">
      <FeatureList>
      <ProductFeature seq="1">
      <FeatureGroup>UserData</FeatureGroup>
      <FeatureName>ComputerName</FeatureName>
      <Value>SF4712</Value>
      </ProductFeature>
      <ProductFeature seq="2">
      <FeatureGroup>UserData</FeatureGroup>
      <FeatureName>HWType</FeatureName>
      <Value>DELL</Value>
      </ProductFeature>
      </FeatureList>
      </Node>
      </NodeList>


      2. IMPORT Functionality: Assume I have an inputfile like the example
      above.
      The InputFile contains no PrimaryKeys (objectId's) but it must
      contain
      the Conceptual keys!
      The Import should only import Data for the Nodes (Table TNODE)
      and the ProductFeatures (Table TNODFEAVAL) with lookup to
      the FeatureCatalog (Table: TFEATURE)

      Can I realize such an import functionality with XMLDBMS ?
      or
      Where can I extend the XMLDBMS Framework to include LookupCode ?


      Is XMLDBMS the right product to do this? Alternatives ? Or Manual Coding
      ?

      Thanks for your help
      Regards
      Peter Burri
    • Ronald Bourret
      1) Sorry about taking so long to answer. I ve been very busy with other work. 2) One questions about your data structure. Why are value and sequence in
      Message 2 of 2 , Sep 23, 2003
      • 0 Attachment
        1) Sorry about taking so long to answer. I've been very busy with other
        work.

        2) One questions about your data structure. Why are value and sequence
        in TNODFEAVAL? It appears this is a join table joining products (TNODE)
        to features (TFEATURE).

        3) You can use XML-DBMS to export data to a document that is very close
        to the one you want. You will need to use XSLT to finish the job. More
        about this later.

        4) You cannot use XML-DBMS to import data, since it cannot do lookups.
        (I assume you want to look up the value of FEA_OID based on the value of
        the FeatureName element.) You can, however, modify the code to do
        lookups. More about this later.

        5) Here are the ClassMaps you can use. This uses the tables, columns,
        and keys you declared in your original email.

        <ClassMap>
        <ElementType Name="Node" />
        <ToClassTable Name="TNODE" />
        <PropertyMap>
        <Attribute Name="Name" />
        <ToColumn Name="NOD_NAME" />
        </PropertyMap>
        <InlineMap>
        <ElementType Name="FeatureList" />
        <RelatedClass KeyInParentTable="Unique">
        <ElementType Name="ProductFeature" />
        <UseUniqueKey Name="PrimaryKey" />
        <UseForeignKey Name="NODFEAVAL.FK" />
        </RelatedClass>
        </InlineMap>
        </ClassMap>
        <ClassMap>
        <ElementType Name="ProductFeature" />
        <ToClassTable Name="TNODFEAVAL" />
        <PropertyMap>
        <Attribute Name="Seq" />
        <ToColumn Name="SEQNO" />
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="Value" />
        <ToColumn Name="VALUE" />
        <FixedOrder Value="3" />
        </PropertyMap>
        <RelatedClass KeyInParentTable="Foreign">
        <ElementType Name="TFEATURE" />
        <UseUniqueKey Name="PrimaryKey" />
        <UseForeignKey Name="FEATURE.FK" />
        <FixedOrder Value="2" />
        </RelatedClass>
        </ClassMap>
        <ClassMap>
        <ElementType Name="TFEATURE" />
        <ToClassTable Name="TFEATURE" />
        <PropertyMap>
        <ElementType Name="FeatureName" />
        <ToColumn Name="FEA_NAME" />
        <FixedOrder Value="2" />
        </PropertyMap>
        <RelatedClass KeyInParentTable="Foreign">
        <ElementType Name="TFEATGROUP" />
        <UseUniqueKey Name="PrimaryKey" />
        <UseForeignKey Name="FEAGROUP.FK" />
        <FixedOrder Value="1" />
        </RelatedClass>
        </ClassMap>
        <ClassMap>
        <ElementType Name="TFEATGROUP" />
        <ToClassTable Name="TFEATGROUP" />
        <PropertyMap>
        <ElementType Name="FeatureGroup" />
        <ToColumn Name="FTG_NAME" />
        </PropertyMap>
        </ClassMap>

        Note the use of the InlineMap to create the FeatureList element. When
        creating XML documents, you will specify a wrapper element of NodeList
        in your filter document -- this will create the NodeList element. (When
        storing XML documents, the NodeList element is simply ignored.)

        6) The ClassMaps in (5) work with an XML document that looks like this:

        <NodeList>
        <Node Name="NODE-A">
        <FeatureList>
        <ProductFeature seq="1">
        <TFEATURE>
        <TFEATGROUP>
        <FeatureGroup>UserData</FeatureGroup>
        </TFEATGROUP>
        <FeatureName>ComputerName</FeatureName>
        </TFEATURE>
        <Value>SF4711</Value>
        </ProductFeature>
        </FeatureList>
        </Node>
        </NodeList>

        When creating XML documents, XML-DBMS will return this document. You can
        convert this to the document you need with XSLT. (You can also use a SAX
        XMLFilter, which would be much faster.) When storing data, you will need
        to reverse this process -- convert from the incoming document to this
        document, then pass it to XML-DBMS.

        7) Here's a rough guess of the changes you will need to do lookups:

        a) Add a Lookup action. This means modifying the action DTD, adding a
        LOOKUP constant to the Action class, and modifying the ActionCompiler
        and ActionConst classes.

        b) Modify DataHandlers. Add a lookup method to the DataHandler
        interface. Extend GenericHandler to implement this method. What happens
        is that the normal code with copy the value of FeatureName into the Row
        object for the TFEATURE table. The lookup method will do a SELECT on
        TFEATURE and populate the rest of the Row object -- specifically with
        the FEA_OID value. (lookup will need to know to do the lookup on the
        FEA_NAME column -- this information will probably need to be in the
        action document.) The normal code will then transfer the FEA_OID value
        to the Row object for the TNODFEAVAL table, which is then inserted.

        c) Modify DOMToDBMS. I think all that needs to be done here is to modify
        storeRow to call DataHandler.lookup.

        Hope this helps,

        -- Ron

        Peter Burri wrote:
        >
        > Hello,
        > I have a relational database which contains something like a product
        > catalog and I would like to create a data-exchange functionality via
        > XML-Datafiles.
        >
        > I tried to use XMLDBMS but I have som problems to use this middleware
        > - different structure between tables and the desired xml-structure
        > output and
        > - importing data without knowing the (datbase internal) object-id's
        > (needs lookup?)

        [diagram snipped]

        > XMLDBMS V2 Database Mapping looks like this:
        >
        > <Table Name="TNODE">
        > <Column Name="NOD_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="NOD_NAME" DataType="VARCHAR" Length="50"
        > Nullable="No"/>
        > <PrimaryKey KeyGenerator="Database">
        > <UseColumn Name="NOD_OID"/>
        > </PrimaryKey>
        > <UniqueKey Name="NODE.CK">
        > <UseColumn Name="NOD_NAME"/>
        > </UniqueKey>
        > </Table>
        > <Table Name="TNODFEAVAL">
        > <Column Name="ITM_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FEA_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="SEQNO" DataType="SMALLINT" Nullable="No"/>
        > <Column Name="VALUE" DataType="VARCHAR" Length="200"
        > Nullable="No"/>
        > <UniqueKey Name="NODFEAVAL.CK">
        > <UseColumn Name="ITM_OID"/>
        > <UseColumn Name="FEA_OID"/>
        > </UniqueKey>
        > <ForeignKey Name="NODFEAVAL.FK">
        > <UseColumn Name="ITM_OID"/>
        > <UseTable Schema="&SCHEMA;" Name="TNODE"/>
        > <UseUniqueKey Name="PrimaryKey"/>
        > </ForeignKey>
        > <ForeignKey Name="FEATURE.FK">
        > <UseColumn Name="FEA_OID"/>
        > <UseTable Schema="&SCHEMA;" Name="TFEATURE"/>
        > <UseUniqueKey Name="PrimaryKey"/>
        > </ForeignKey>
        > </Table>
        > <Table Name="TFEATURE">
        > <Column Name="FEA_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FTG_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FTT_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FEA_NAME" DataType="VARCHAR" Length="50"
        > Nullable="No"/>
        > <PrimaryKey KeyGenerator="Database">
        > <UseColumn Name="FEA_OID"/>
        > </PrimaryKey>
        > <UniqueKey Name="FEATURE.CK">
        > <UseColumn Name="FTG_OID"/>
        > <UseColumn Name="FEA_NAME"/>
        > </UniqueKey>
        > <ForeignKey Name="FEATEMPLATE.FK">
        > <UseColumn Name="FTT_OID"/>
        > <UseTable Schema="&SCHEMA;" Name="TFEATTEMPLATE"/>
        > <UseUniqueKey Name="PrimaryKey"/>
        > </ForeignKey>
        > <ForeignKey Name="FEAGROUP.FK">
        > <UseColumn Name="FTG_OID"/>
        > <UseTable Schema="&SCHEMA;" Name="TFEATGROUP"/>
        > <UseUniqueKey Name="PrimaryKey"/>
        > </ForeignKey>
        > </Table>
        > <Table Name="TFEATGROUP">
        > <Column Name="FTG_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FTG_NAME" DataType="VARCHAR" Length="50"
        > Nullable="No"/>
        > <PrimaryKey KeyGenerator="Database">
        > <UseColumn Name="FTG_OID"/>
        > </PrimaryKey>
        > <UniqueKey Name="FEAGROUP.CK">
        > <UseColumn Name="FTG_NAME"/>
        > </UniqueKey>
        > </Table>
        > <Table Name="TFEATTEMPLATE">
        > <Column Name="FTT_OID" DataType="INTEGER" Nullable="No"/>
        > <Column Name="FTT_NAME" DataType="VARCHAR" Length="50"
        > Nullable="No"/>
        > <Column Name="CHECK_RULE" DataType="LONGVARCHAR" Length="4096"
        > Nullable="Yes"/>
        > <PrimaryKey KeyGenerator="Database">
        > <UseColumn Name="FTT_OID"/>
        > </PrimaryKey>
        > <UniqueKey Name="FEATEMPLATE.CK">
        > <UseColumn Name="FTT_NAME"/>
        > </UniqueKey>
        > </Table>
        > ------------------------------------------------------------------------
        > ---------------
        > Problems / Questions:
        > ------------------------------------------------------------------------
        > ---------------
        > 1. EXPORT Functionality: How should I write the XMLDBMX-Maps for
        > generating an output XML-Document like
        > the following example? ( flatten structure of the feature catalog)
        > Can I do this with XMLDBMS ?
        >
        > <NodeList>
        > <Node Name="NODE-A">
        > <FeatureList>
        > <ProductFeature seq="1">
        > <FeatureGroup>UserData</FeatureGroup>
        > <FeatureName>ComputerName</FeatureName>
        > <Value>SF4711</Value>
        > </ProductFeature>
        > </FeatureList>
        > </Node>
        > <Node Name="NODE-B">
        > <FeatureList>
        > <ProductFeature seq="1">
        > <FeatureGroup>UserData</FeatureGroup>
        > <FeatureName>ComputerName</FeatureName>
        > <Value>SF4712</Value>
        > </ProductFeature>
        > <ProductFeature seq="2">
        > <FeatureGroup>UserData</FeatureGroup>
        > <FeatureName>HWType</FeatureName>
        > <Value>DELL</Value>
        > </ProductFeature>
        > </FeatureList>
        > </Node>
        > </NodeList>
        >
        > 2. IMPORT Functionality: Assume I have an inputfile like the example
        > above.
        > The InputFile contains no PrimaryKeys (objectId's) but it must
        > contain
        > the Conceptual keys!
        > The Import should only import Data for the Nodes (Table TNODE)
        > and the ProductFeatures (Table TNODFEAVAL) with lookup to
        > the FeatureCatalog (Table: TFEATURE)
        >
        > Can I realize such an import functionality with XMLDBMS ?
        > or
        > Where can I extend the XMLDBMS Framework to include LookupCode ?
        >
        >
        > Is XMLDBMS the right product to do this? Alternatives ? Or Manual Coding
        > ?
      Your message has been successfully submitted and would be delivered to recipients shortly.