Information Schema

In This Topic

When you connect to QuickBooks Online the first time, dotConnect for QuickBooks Online needs to retrieve metadata and map objects to the relational tables. This process is complex and can take some time, so to avoid retrieving the metadata each time when connecting, dotConnect for QuickBooks Online caches the metadata and stores it in an SQLite database.

Objects are mapped to tables; their fields, including ID, system, and calculated fields, are mapped to the table columns. ID field is mapped as the table primary key. Master-Detail relationships between objects are mapped to foreign keys between tables. Note that two Relationship fields - on the master side and on the detail side - correspond to one foreign key.

The schema of the metadata database has tables, compliant with the SQL-92 information schema standard. The location of this metadata database can be specified in the Metadata Cache connection string parameter. If it is not specified, the metadata is stored in the user Application Data folder by default. The metadata database is shared between different connections to the same QuickBooks Online account. You may even transfer it from the developer computer to the end-users' ones in order to save the time for querying metadata.

If the metadata is not found when connecting, the metadata is queried again, objects are remapped to the tables and a new metadata database is created. You can refresh metadata using the RefreshMetadata method of the QuickBooksConnection.Cache object. The metadata database contains the following tables:

SYS_TABLES - lists available tables and their parameters
SYS_COLUMNS - lists table columns, determines to which tables they belong, their position in tables, data type and other parameters.
SYS_TABLE_CONSTRAINTS - lists constraints and specifies their types and tables they belong to.
SYS_REFERENTIAL_CONSTRAINTS - contains details on foreign keys.
SYS_KEY_COLUMN_USAGE - contains information to match columns with constraints.

You may query these tables in the same way you query the QuickBooks Online tables (objects) to retrieve the metadata. However, you should not use both metadata tables and QuickBooks Online objects in one query. If you query both in a single SELECT statement, you will get an exception.

Here are examples of such queries:

The following query returns the list of tables and their columns, ordered in the same way, they are ordered in their tables:
```
SELECT TABLE_NAME, COLUMN_NAME FROM SYS_COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION
```

The following query displays the tables with their foreign key constraints, the delete rule of these foreign key constrains, the related tables, and the foreign key columns:

SELECT C.TABLE_NAME, C.CONSTRAINT_NAME, R.DELETE_RULE, U.COLUMN_NAME
  FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U, SYS_REFERENTIAL_CONSTRAINTS AS R 
  WHERE C.CONSTRAINT_NAME = R.CONSTRAINT_NAME AND R.CONSTRAINT_NAME = U.CONSTRAINT_NAME
  ORDER BY C.TABLE_NAME, C.CONSTRAINT_NAME

The following query displays the tables having the foreign key, referencing the table, specified in the Object parameter:

SELECT DISTINCT C.TABLE_NAME
  FROM SYS_TABLE_CONSTRAINTS AS C, SYS_REFERENTIAL_CONSTRAINTS AS R 
  WHERE C.CONSTRAINT_NAME = R.CONSTRAINT_NAME AND R.RELATED_OBJECT = :Object

See Also