The data dictionary populated by Stonefield Query Studio is stored in one or more databases. These databases can be SQLite, Microsoft SQL Server, or any other database with a .NET provider. The connection information is stored in Settings.xml for the project; see the Stonefield Query Project Files help topic for information on this file.

The data dictionary database consists of the following tables:

  • AppViewFields: contains the definitions for fields used in application views. This table isn't currently used.

  • AppViews: contains the definitions of the application views. This table isn't currently used

  • Databases: contains information about the logical databases in the data dictionary.

  • Datagroups: contains the data groups.

  • Fields: contains the definitions of the fields.

  • JoinTrees: contains information about the join trees.

  • Joins: contains the definitions of the joins between tables.

  • Roles: contains the roles you defined in Stonefield Query Studio.

  • Tables: contains the tables in the data dictionary.

The structure of each table is discussed next. In all tables, IDs are Globally Unique Identifiers (Guid) stored as char(36).

AppViewFields

ColumnTypePurpose
ID char(36) The unique ID.
FieldID char(36) The ID of the field the application view field is based on.
OrderNum int The order the field appears in the application view it belongs to.
Caption varchar(60) The caption for the field in the application view.
Heading varchar(60) The default column heading for the field in the application view.
UserDefined text Not currently used.
Version varchar(20) Not currently used.
Updated date The date the record was last updated.

AppViews

ColumnTypePurpose
ID char(36) The unique ID.
Parent char(36) The ID of the parent application view for this application view; blank if this is a top-level application view.
OrderNum int The order of the application view in the list of application views.
Caption varchar(60) The caption for the application view.
DataGroups text A carriage return-delimited list of IDs of datagroups the application view belongs to.
Fields text A carriage return-delimited list of IDs of application view fields used in the application view.
MainList bit 1 (true) if this application view appears in the main list of application views, 0 (false) if not.
UserDefined text Not currently used.
Version varchar(20) Not currently used.
Updated date The date the record was last updated.

Databases

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(128) The name of the logical database.
Active bit 1 (true) if this database appears in Stonefield Query, 0 (false) if not.
Delimiters char(2) The name delimiters for the database.
ConnType int The connection type: 0 means Use Default Connection, 1 means Shared, 2 means User Can Choose DSN, and 3 means Scripted.
ConnString text The encrypted connection string for the default connection for the database.
ShareWith char(36) The ID of the database this database shares a connection with; blank if ConnType is not 1.
OrderNum text The order in which to access this database relative to other databases.
UserDefined text Custom properties for the database.
Version varchar(20) Not currently used.
Updated date The date the record was last updated.

Datagroups

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(128) The name of the data group.
DefaultTable char(36) The ID of the table to select by default for a new report when this data group is selected.
DefaultAppView char(36) The ID of the application view to select by default for a new report when this data group is selected. This column isn't currently used.
UserDefined text Not currently used.
Version varchar(20) Not currently used.
Updated date The date the record was last updated.

Fields

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(128) The aliased name of the field (that is, TableName.FieldName), including name delimiters if necessary (such as [Order Details].[Unit Price]).
TableID char(36) The ID of the table this field belongs to.
DataType varchar(40) The full .NET data type name (such as "System.String").
OrderNum int The order of the field in the table.
Caption varchar(60) The caption for the field.
Heading varchar(60) The default column heading for the field in a report.
Expression text The expression for a calculated field if ExpressionType is greater than 0.
ExpressionType int The type of field: 0 means a real field that exists in the database, 1 means an expression sent to the database engine, and 2 means an expression processed by Stonefield Query.
Format varchar(20) The format property for the field.
Reportable bit 1 (true) if the user can query on the field; if 0 (false), the field doesn't appear in Stonefield Query.
Sortable bit 1 (true) if the user can sort on the field.
Filterable bit 1 (true) if the user can filter on this field.
Comments text The comment for the field.
AllowValues bit 1 (true) if the Values button is enabled for this field.
ValueConverter text The name of the value converter plugin used by this field.
PluginData text Data used by a value converter plugin for the field.
ValuesMethod text The name of the plugin to use to display unique values for this field.
ContentType int The type of content this field contains: 0 mean normal (string, numeric values, etc.), 1 means HTML, 2 means RTF, 3 means an image, and 4 means the path to an image.
FieldList text A carriage return-delimited list of IDs of fields involved in the expression for this field.
Roles text A carriage return-delimited list of IDs of the roles that can access this field; blank if it's available to all users.
UserDefined text Custom properties a plugin can use for this field.
Version varchar(20) The version number for the field; blank if it isn't versioned.
Updated date The date the record was last updated.
DefaultSummaryType int The default summary type to use in a report. The values come from the SummaryTypes enum:
  • 0: none
  • 1: count
  • 2: count distinct
  • 3: sum
  • 4: max
  • 5: min
  • 6: average
FilterConditions text Serialized filter condition settings for a grouping calculated field.
GroupingFields text A carriage return-delimited list of IDs of fields used for grouping for a grouping calculated field.
SummaryType int The summary type for a grouping calculated field; uses the same values described in DefaultSummaryType.
AppearsInTables text A carriage return-delimited list of IDs of tables this field should appear in.
OutputDataType varchar(40) The full .NET data type name (such as "System.String") the field will have in the result set. This is normally blank, meaning it's the same as DataType, but may be filled in if there's a value converter for the field.

JoinTrees

ColumnTypePurpose
ID char(36) The unique ID.
Tables text A carriage return-delimited list of IDs of the tables involved in this join tree.
Joins text A carriage return-delimited list of IDs of the joins involved in this join tree.
UserDefined text Not currently used.
Version varchar(20) Not currently used.
Updated date The date the record was last updated.

Joins

ColumnTypePurpose
ID char(36) The unique ID.
ChildTable char(36) The ID of the child table for the join.
ParentTable char(36) The ID of the parent table for the join.
ComplexExpression text The join expression for the join if it's a complex join. This is blank if Expressions contains a value.
Expressions text A carriage return-delimited list of join expressions. Each join expression is the ID of the child field, a comma, and the ID of the parent field. This is blank if ComplexExpression contains a value.
JoinType int The join type: 0 means inner join, 1 means favor child, 2 means favor parent, and 3 means full join.
Weight int The weight of the join.
UserDefined text Not currently used.
Version varchar(20) The version number for the join; blank if it isn't versioned.
Updated date The date the record was last updated.
FilterUnfavored int How to filter the unfavored table in an outer join. These values come from the FilterUnfavoredTableLocation enum: 0 means use the default, 1 means include in the WHERE clause, 2 means including in the JOIN clause, and 3 means use a subquery.

Roles

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(60) A name of the role.
Internal bit 1 (true) if this a built-in role (this is the case for "Administrator").
Updated date The date the record was last updated.

Tables

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(128) The name of the table, including name delimiters if necessary (such as [Order Details]).
DatabaseID char(36) The ID of the database this table belongs to.
TableName varchar(128) The physical name of the table. This is normally the same as Name but in the case of multiple tables with the same names (in different databases), this contains the name of the table in the database (which isn't unique in the entire collection) and Name contains the alias (which is). In a SQL statement, the table is specified as TableName and aliased as Name; for example, FROM TableName Name.
Caption varchar(60) The caption for the table.
Reportable bit 1 (true) if the user can report on the fields in this table; if 0 (false), the table doesn't appear in Stonefield Query.
Virtual bit 1 (true) if this is a virtual table, 0 (false) if it exists in the database.
SchemaName varchar(60) The schema for the table if applicable.
OriginalTable char(36) The ID of the table this table is a subtable of; blank if this isn't a subtable.
SubtableFilter text The subtable filter for the table; blank if this isn't a subtable or doesn't have a filter.
NoRefresh bit 1 (true) if this table should not be refreshed when the data dictionary is refreshed.
DataGroups text A carriage return-delimited list of IDs of data groups the table belongs to.
Roles text A carriage return-delimited list of IDs of the roles that can access this table; blank if it's available to all users.
UserDefined text Custom properties a plugin can use for this field.
Version varchar(20) The version number for the field; blank if it isn't versioned.
Updated date The date the record was last updated.
Plugin text The name of the virtual table plugin used by this table.
PluginData text Data used by a virtual table plugin for the table.