Cache Metadata Using DdlUtils

Visual JSF Database design-time Performance hit in 6.0

• Component binding tries to get a connection from CachedRowSet in the following scenarios
  • Dragging and dropping a database table onto a Visual JSF page, component, or page bean in the Navigator
  • Opening a page that has components bound to rowsets
  • Refreshing a page that has components bound to rowsets
• (Re)Binding components to column keys of data provider
• Opening the Visual Query Editor from the rowset icon in the Navigator to edit a query

Proposal(s) to improve the design-time performance

• Retrieve the metadata the first time a database table is used and persist it in a file
• Subsequent retrievals of metadata by way of the persisted file, not by opening a connection and re-retrieving
• Persist metadata in an XML or Java format
• Implement a mapping from the metadata file to a data provider
• Support some consistency mechanism if the schema changes after persisting the metadata
• Modify CachedRowSet to check for persisted metadata file.
• Use CachedRowSet only for retrieving metadata if persisted file doesn't exist
• If metadata file exists then use metadata from file in the data provider

Functional Spec

Metadata caching

• Use DdlUtils Apache library for retrieving/writing metadata
• Create a new NetBeans API that wraps DdlUtils
  • API description

Summarized

"At the core of DdlUtils lies the database model in package org.apache.ddlutils.model. It consists of classes that represent the database schema ... Using the appropriate methods on these classes, you can build the database model manually, or you read it from XML or from the database ... Writing a model to XML is just as easy as reading from XML"

Features for the new NetBeans API that wraps DdlUtils

• write metadata as XML file in the project
• read metadata from XML
• compare 2 metadata instance in memory
• replace metadata XML file
• map metadata to dataprovider

Consistency

• Add a consistency option (disabled by default) to check metadata for consistency with database
• At project open, the consistency option is checked.
• Add an action on the Data Source References node to check for consistency
• If metadata file is inconsistent with the rowset from live connection then replace the metadata file

Use Cases

1. Manual binding to a non-Table component
   1. Drag a database table and drop on the background of a Visual JSF page.
      1. if metadata XML or Java file persisted then use metadata from file, otherwise use CRS to retrieve the metadata and generate a persisted file
   2. Drag a Visual JSF component (excludes the Table component) onto the Visual JSF page
   3. Right-click on the component and choose Bind to Data. Metadata is retrieved locally from file.
2. Automatic binding to a non-Table component
   1. Drag a Visual JSF component (excludes the Table component) onto the Visual JSF page
   2. Drag a database table and drop on top of a Visual JSF component to automatically bind to a column of the database metadata
      • metadata XML or Java file persisted and dataprovider created
3. Manual binding to Table component
   1. Drag a database table and drop on the background of a Visual JSF page.
      • if metadata XML or Java file persisted then use metadata from file, otherwise use CRS to retrieve the metadata and generate a persisted file
   2. Drag a Visual JSF component (excludes the Table component) onto the Visual JSF page
   3. Right-click on the table component and choose Table Layout then select a dataprovider to bind.
• Metadata is available from local file
4. Automatic binding to Table component
   1. Drag a Visual JSF Table component onto the Visual JSF page
   2. Drag a database table and drop on top of the Table component to automatically bind to a column of the database metadata
      • metadata XML or Java file persisted and dataprovider created
5. Open existing Visual JSF database project
   • if metadata XML or Java file persisted then use metadata from file, otherwise use CRS to retrieve the metadata and generate a persisted file
   • cachedRowSet indirectly uses metadata XML or Java to restore/maintain componenet bindings