CxStudio DataSource Component

Overview

The DataSource component allows you to specify queries and updates to one or more tables within a single database using XML. It also generates output in the form of XML documents which you can design using a drag-and-drop. You may specify a single database operation in a request, or multiple operations executed within a single transaction. This component operates in a batch mode; that is, you make a single request which is completely processed before any new requests are processed.

Database Specification

You begin by entering information in the properties: DSN, User, and Password. These correspond to those settings that would be necessary to make a JDBC/ODBC connection to your database. Once entered, you should immediately see schema information appear in leftmost pane of the DataSource editor window.

Database Mapping

You can drop a database into the right hand pane of the dataSource and a dialog will ask you select a type document. You can select and existing type or press the auto type button and one will be made in the type package you select that matches the structure of the database and with all the fields linked by name.

Once you�ve accessed your database schema, the next step is to create a document type that you will use to map the tables and columns of your database. This document type will both serve to define various pieces of information such as column and table mapping, identity (or primary key) fields, etc. You may nest documents to show relationships, e.g., we created an order type that contained both an item and a customer document. The item document has a �0..N� relationship to show that a single order may have any number of items. The customer document has a �1..1� relationship to show that an order is always associated with one any only one customer.

Nested documents must specify a �join� column. As soon as you drag a column onto a sub-table, a dialogue will popup allowing you to select the join fields. The current implementation supports only a single join field; however, this will be expanded in later releases to support joins on multiple fields.

In addition to the join information, you must specify at least one variable in each document to be an �identity� variable. This is synonymous with �primary key� in that it specifies which variable or variables can be used to uniquely identify a document. The current implementation supports only a single identity field; however, this will be expanded in later releases to support identity on multiple fields.

To have the data source object perform an action, be it a query, insert, delete, or update, you must pass a document which resembles the expected output. The default operation is a query.

Just as you may nest documents, e.g., a 0..N item document nested inside a order document, you may nest data source requests. For example, if you wished to delete an item from a particular order, you might specify it in the following way: 

 
    <order cx:action=�constrain�>
        <order_num>25</order_num>
        <item cx:action=�delete�>
            4</item_num>
        </item>
    </order>

The above statement would delete item 4 from order 25. The outer action specifies that we should constrain the set of order documents by whatever order criteria are specified within the document. The default comparison value (manually specified as cx:comparison=���) is EQ for equals. Once the order set is determined, in this case only order 25, then items fitting the criteria of the inner node are deleted. Again, the default action for a simple variable nested within a delete document is �constrain�; thus, we constrain the set of items to those with item_num equal 4.

Below is a concise list of attributes supported by the data source and acceptable values for each:

cx:action: This attribute determines what actions shall be taken for the document to which it applies.
  • QUERY: has two meanings depending on whether it is being applied to a field or a nested document:
    • Field: indicates the field will be used to constrain the parent document. This action is the default for fields nested within a CONSTRAIN, DELETE, QUERY, ASSIGN, or CONSTRAIN_PARENT document.
    • Document: outputs the set of records specified by the constraints contained within the document.
  • INSERT: adds the document to the data source using whatever field information is supplied within the document.
  • UPDATE: has two meanings depending on whether it is being applied to a field or a nested document:
    • Field: indicates the field value will be set using the data in the field node. This action is the default for fields nested within a INSERT or UPDATE document.
    • Document: modifies the set of documents specified by the constraints contained within the document and using whatever field information is supplied within the document.
  • DELETE: deletes the set of documents specified by the criteria contained within the document.
  • CONSTRAIN: same as query except no records are output. This is used to limit a parent document set when operating on child documents.
  • CONSTRAIN_PARENT: applies a constraint to a parent document based on child critera. This is often used on a child document with a cx:comparison of IFEMPTY or IFANY, etc. This action is not currently implemented.
  • ASSIGN: this builds an associated between a child and parent document, and always applies by binding the child to the parent.
cx:comparison: This attribute determines how a specific piece of data is used to constrain a document, e.g., if we said <age cx:comparison=�GT�>17</age>, we would be requiring the document set containing an age element to include only those documents in which the value of age is greater than 17.
EQ: equal.
NE: not equal.
ISNULL: must be NULL.
ISNOTNULL: must not be NULL.
GT: greater than.
GE: greater than or equal.
LT: less than.
LE: less than or equal.
IFANY: at least one child must satisfy the nested constraints (not currently implemented).
IFEMPTY: no children satisfy the nested constraints (not currently implemented).
IFALL: all children must satisfy the nested constraints (not currently implemented).

When nesting documents, the are various interactions which are described in the table below. Those items with a �NO� have undefined behavior and are not supported.

Interactions

Nested Container QUERY CONSTRAIN CONSTRAIN_PARENT INSERT UPDATE DELETE ASSIGN
QUERY YES-1 NO-3 NO NO NO NO NO
CONSTRAIN YES-2 YES-3 NO YES-6 YES-6 NO NO
CONSTRAIN_PARENT YES YES YES YES YES YES NO
INSERT YES-4 YES NO YES YES NO NO
UPDATE YES-4 YES NO NO YES YES NO
DELETE YES-4 YES NO NO YES YES NO
ASSIGN YES-7 YES-7 YES-7 YES-7 YES-7 NO NO
  1. A QUERY with no nested QUERY action will output all contained elements. A nested QUERY will result in each item satisfying that QUERY being added to the set of items; if there is more than one QUERY, the result is additive. Duplicates are NOT removed.
  2. A CONSTRAIN nested within a QUERY will have no effect on the output of that QUERY but will define a subset of child items to which other actions may apply.
  3. A QUERY may not be nested within a CONSTRAIN, though CONSTRAINS may be nested within other CONSTRAINS.
  4. QUERY output results after all nested operations have been processed. The result is the final set as defined by the QUERY and nested QUERIES. This generally will mean the out QUERY must be executed twice: first to constrain parent records to be operated on, second to generate the final output set.
  5. CONSTRAIN nested within a QUERY has no effect on the output; it merely generates a subset of elements which can contain INSERT, UPDATE, and DELETE documents.
  6. CONSTRAIN inside an INSERT or UPDATE will serve to identify an association between a parent document and the contained document. No change will be directly made to the contained document; however, whatever mapping exists between the parent and child document will be enforced in the newly created or updated record.
  7. ASSIGN contained in any of the supported documents causes each document in the set of containing documents to have assigned to it the first item satisfying the constraints contained in the child document. A good example would be an employee document containing a department document with an ASSIGN action. The result would be that all employees would have assigned to them the department satisfying the criteria.

Mutliple Actions

It is permissible to execute multiple data source operations in a single document. The actions are executed as part of a single transaction. When this is desired, simply enclose all documents inside a single document tag. The documents will be executed in the order which they are specified.

Examples

The best way to illustrate how to use the data source object is with some examples. Let�s take the contacts sample data source which is provided with CxStudio. There are two ways to map the data within this database. You can choose to view it in terms of employees, each having a sub-document which is the company they work for:

Another way would be to view it in terms of the company, each have a set of documents for each employee that works there:

The employeeDS and companyDS components map these two document type to the actual Access databases containing the data. These can be found under the contacts package in the examples/contacts/contacts.cxp project. Please note that the actual data source components are contained within these components. Since the output of a data source component is a stream of records, we combine these into a single XML document for simplicity.

Below are some sample documents, and the output and effects they have on the underlying database. Since the output of a data source component is a stream of elements, we will capture the stream and convert it into a set using the streamToSet component. This is done in in both companyDS and employeeDS.

Example 1:

Adding an employee to an existing company using employeeDS.

Input:

    <employee cx:action="insert">
        <id>7</id>
        <last>Alvarez</last>
        <first>Eugene</first>
        <company cx:action="assign">
            <id>1</id>
        </company>
        <title>Programmer</title>
        <phone>305-238-0012</phone>
        <fax>305-238-0017</fax>
        <email>[email protected]</email>
    </employee>

Output:

    <employees>

Example 2:

Query all employees at a particular company using companyDS.

Input:

     <company>
         <id>1</id>
     </company>

Output:

    <companies>
        <company>
            <id>1</id>
            <name>Data Access Corporation</name>
            <employee>
                <id>1</id>
                <last>Reeves</last>
                <first>Andrew</first>
                <title>Programmer</title>
                <phone>305-238-0012</phone>
                <fax>305-238-0017</fax>
                <DOB>1967-01-23 00:00:00.0</DOB>
                <email>[email protected]</email>
            </employee>
            <employee>
                <id>5</id>
                <last>Casanave</last>
                <first>Cory</first>
                <title>President</title>
                <phone>305-238-0012</phone>
                <fax>305-238-0017</fax>
                <DOB/>
                <email>[email protected]</email>
            </employee>
            <employee>
                        <id>6</id>
                        <last>Ricci</last>
                        <first>James</first>
                        <title>Programmer</title>
                        <phone>305-238-0012</phone>
                        <fax>305-238-0017</fax>
                        <DOB>1967-01-23 00:00:00.0</DOB>
                        <email>[email protected]</email>
            </employee>
            <address1>14000 SW 119th Ave</address1>
            <address2>N/A</address2>
            <city>Miami</city>
            <state>FL</state>
            <zip>33186</zip>
            <zip4>0</zip4>
        </company>
    </companies>

Example 3:

Query all employees with the title �Programmer� at a particular company using companyDS.

Input:

    <company>
        <id>1</id>
        <employee>
            <title>Programmer</title>
        </employee>
    </company>

Output:

    <companies>
        <company>
            <id>1</id>
            <name>Data Access Corporation</name>
            <employee>
                <id>1</id>
                <last>Reeves</last>
                <first>Andrew</first>
                <title>Programmer</title>
                <phone>305-238-0012</phone>
                <fax>305-238-0017</fax>
                <DOB>1967-01-23 00:00:00.0</DOB>
                <email>[email protected]</email>
            </employee>
            <employee>
                <id>6</id>
                <last>Ricci</last>
                <first>James</first>
                <title>Programmer</title>
                <phone>305-238-0012</phone>
                <fax>305-238-0017</fax>
                <DOB>1967-01-23 00:00:00.0</DOB>
                <email>[email protected]</email>
            </employee>
            <address1>14000 SW 119th Ave</address1>
            <address2>N/A</address2>
            <city>Miami</city>
            <state>FL</state>
            <zip>33186</zip>
            <zip4>0</zip4>
        </company>
    </companies>

Example 4:

Deleting a specific employee at a particular company using companyDS.

Input:

    <company cx:action="constrain">
        <id>1</id>
        <employee cx:action="delete">
            <id>1</id>
        </employee>
    </company>

Output:

    <employees>

Example 5:

Changing an employee�s associated company using employeeDS. Output the final employee record when done.

Input:

    <cx:dbrequest>
        <employee cx:action="update">
            <id cx:action="constrain">1</id>
            <company cx:action="assign">
            <id>3</id>
            </company>
        </employee>
        <employee>
            <id>1</id>
        </employee>
    </cx:dbrequest>

Output:

    <employees>
        <employee>
            <id>1</id>
            <last>Reeves</last>
            <first>Andrew</first>
            <company>
                <name>Dim Bulbs Inc.</name>
                <id>3</id>
                <address1>3233 NW 8th Street</address1>
                <address2>N/A</address2>
                <city>Miami</city>
                <state>FL</state>
                <zip>33133</zip>
                <zip4>1234</zip4>
            </company>
            <title>Programmer</title>
            <phone>305-238-0012</phone>
            <fax>305-238-0017</fax>
            <DOB>1967-01-23 00:00:00.0</DOB>
            <email>[email protected]</email>
        </employee>
    </employees>

Example 6:

Changing an employee�s title using employeeDS. Output the final employee record when done.

Input:

    <cx:dbrequest>
        <employee cx:action="update">
            <id cx:action="constrain">1</id>
            <title>Manager</title>
        </employee>
        <employee>
            <id>1</id>
        </employee>
    </cx:dbrequest>

Output:

    <employees>
        <employee>
            <id>1</id>
            <last>Reeves</last>
            <first>Andrew</first>
            <company>
                <name>Data Access Corporation</name>
                <id>1</id>
                <address1>14000 SW 119th Ave</address1>
                <address2>N/A</address2>
                <city>Miami</city>
                <state>FL</state>
                <zip>33186</zip>
                <zip4>0</zip4>
            </company>
            <title>Manager</title>
            <phone>305-238-0012</phone>
            <fax>305-238-0017</fax>
            <DOB>1967-01-23 00:00:00.0</DOB>
            <email>[email protected]</email>
        </employee>
    </employees>

Copyright 2000,2001 Data Access Technologies