Importing a Data Dictionary - Stonefield Query SDK

This feature is only available in the Ultimate version of Studio.

The Import Data Dictionary function in the File menu updates the data dictionary from the contents of a Microsoft Excel spreadsheet or XML file containing information about an application's tables, fields, relations, and application views. You must have already added the database to the data dictionary; this function then adds or updates tables, fields, relations, and application views in the data dictionary.

Microsoft Excel Spreadsheet

The first row of the Excel spreadsheet must contain headings for the columns. Acceptable column headings are (case is unimportant):

"Alias" or "Table": the table name.
"Table Desc" or "Table Caption": the table caption.
"Field", "Field Name", or "Fieldname": the field name.
"Field Desc" or "Field Caption": the field caption.
"SubtableOf": specifies that this is a subtable of the specified table.
"SubtableFilter": specifies the subtable filter for the subtable.
"DataType" or "Type": the field's data type (see the Field Object topic for a list of the single character codes representing the possible data types).
"Size" or "Length": the field length.
"Decimals" or "Dec": the number of decimals for the field.
"Picture" or "Mask": the picture for the field. Use "#" as a placeholder for a digit, a leading "" to indicate the currency symbol should be used, and commas as necessary. For example, "###,###.##" will set the field's Format property to "$" and its Picture to "999,999.99".
"Format": the format code for the field; this value is put into the Format property.
"Comment": the field comment.
"Heading": the field heading; if this isn't specified or is blank, the field caption is used.
"Enumeration": a set of the values for an enumerated field. Specify values as Value=Description;Value=Description;...
"Calculated" or "Calc": "T" or "Y" if this is a calculated field.
"Expression" or "Expr": the expression used for the calculated field.
"Data Group" or "Datagroup": the data group or groups the table belongs to.
"Reportable": the Reportable status of the field.
"Parent Table": the name of the parent table in a join.
"Child Table": the name of the child table in a join.
"Parent Field": the name of the parent field in a join (only for simple joins where a single field in the parent table matches a single field in the child table).
"Child Field": the name of the child field in a join (only for simple joins)
"Join Expr": the join expression (that is, the JOIN clause of a SQL statement) for a complex join.
"Join Type": the join type: 1 for inner join, 2 for a left outer join, 3 for a right outer join, and 4 for an inner join.
"Join Weight": the join weight to use (see Relation Properties for a discussion of join weight).

All but the first four column headings are optional. The order of the columns is unimportant.

Here's an example of such a spreadsheet:

XML File

Three types of XML import files are supported: one that contains table and field information, one that contains relationship information, and one that contains application view information.

The format for an XML file used for importing table and field information is as follows:

<settings>
  <setting>
    <Table>Table name</Table>
    <TableCaption>Table caption</TableCaption>
    <Field>Field name</Field>
    <FieldCaption>Field caption</FieldCaption>
    <SubtableOf>Original table name</SubtableOf>
    <SubtableFilter>Subtable filter</SubtableFilter>
    <Type>Field data type</Type>
    <Size>Field length</Size>
    <Decimals>Number of decimals in the field</Decimals>
    <Picture>Field picture</Picture>
    <Calculated>"true" for a calculated field</Calculated>
    <Expression>The expression for a calculated field</Expression>
    <DataGroup>Data group or groups the table belongs to</DataGroup>
    <Reportable>Reportable status of the field</Reportable>
    <Comment>Field comment</Comment>
    <Heading>Field heading</Heading>
    <Enumeration>Value=Description;Value=Description;...</Enumeration>
  </setting>
  ...
</settings>

All but the first four elements are optional, although Type and Size should also be present if the fields are being added to the data dictionary. See the Field Object topic for a list of the single character codes representing the possible data types. The order of the elements is unimportant. Here's an example of such an XML file:

<settings>
  <setting>
    <Table>Customers</Table>
    <TableCaption>Customer Information</TableCaption>
    <Field>CustomerID</Field>
    <FieldCaption>Customer Number</FieldCaption>
  </setting>
  <setting>
    <Table>Customers</Table>
    <TableCaption>Customer Information</TableCaption>
    <Field>CompanyName</Field>
    <FieldCaption>Company Name</FieldCaption>
  </setting>
</settings>

The format for an XML file used for importing relation information is as follows:

<relations>
  <relation>
    <ParentTable>Parent table name</ParentTable>
    <ParentField>Parent field name (simple joins only)</ParentField>
    <ChildTable>Child table name</ChildTable>
    <ChildField>Child field name (simple joins only)</ChildField>
    <JoinExpr>The join expression (complex joins only)</JoinExpr>
    <JoinType>Join type</JoinType>
    <JoinWeight>Join weight</JoinWeight>
  </relation>
  ...
</relations>

The JoinType and JoinWeight elements are optional. The order of the elements is unimportant. Here's an example of such an XML file:

<relations>
  <relation>
    <ParentTable>Customers</ParentTable>
    <ParentField>CustomerID</ParentField>
    <ChildTable>Orders</ChildTable>
    <ChildField>CustomerID</ChildField>
  </relation>
  <relation>
    <ParentTable>Orders</ParentTable>
    <ParentField>OrderID</ParentField>
    <ChildTable>[Order Details]</ChildTable>
    <ChildField>OrderID</ChildField>
  </relation>
  <relation>
    <ParentTable>Orders</ParentTable>
    <ChildTable>[Order Details]</ChildTable>
    <JoinExpr>Orders.OrderID=[Order Details].OrderID</JoinExpr>
  </relation>
</relations>

The format for an XML file used for importing application view information is as follows:

<AppViews>
  <AppView>
    <DataGroup>Data group name</DataGroup>
    <Key>Unique key for the application view</Key>
    <Caption>Application view caption</Caption>
    <ParentKey>The key of the application view this one is under or blank if it's a
        top-level</ParentKey>
    <MainList>1 if the application view appears in the main list or 0 if not</MainList>
    <Order>The order of the application view in the list</Order>
    <AppViewField>
      <Caption>Caption of the field</Caption>
      <Heading>Heading of the field</Heading>
      <FieldName>Aliased field name</FieldName>
      <Order>The order of the field in the field list</Order>
    </AppViewField>
    <AppViewField>
      <Caption>Caption of the field</Caption>
      <Heading>Heading of the field</Heading>
      <FieldName>Aliased field name</FieldName>
      <Order>The order of the field in the field list</Order>
    </AppViewField>
    ...
  </AppView>
  ...
</AppViews>

The order of the elements is unimportant. Here's an example of such an XML file:

<AppViews>
  <AppView>
    <DataGroup>Accounts Receivable</DataGroup>
    <Key>AAA</Key>
    <Caption>Customers</Caption>
    <ParentKey />
    <MainList>1</MainList>
    <Order>1</Order>
    <AppViewField>
      <Caption>Customer Number</Caption>
      <Heading>Customer #</Heading>
      <FieldName>Customers.CustomerID</FieldName>
      <Order>1</Order>
    </AppViewField>
    <AppViewField>
      <Caption>Company Name</Caption>
      <Heading>Company</Heading>
      <FieldName>Customers.CompanyName</FieldName>
      <Order>2</Order>
    </AppViewField>
  </AppView>
  <AppView>
    <DataGroup>Accounts Receivable</DataGroup>
    <Key>BBBB</Key>
    <Caption>Contact Information</Caption>
    <ParentKey>AAA</ParentKey>
    <MainList>0</MainList>
    <Order>1</Order>
    <AppViewField>
      <Caption>Contact Name</Caption>
      <Heading>Contact</Heading>
      <FieldName>Customers.ContactName</FieldName>
      <Order>1</Order>
    </AppViewField>
  </AppView>
</AppViews>