VWPDataSourceIntegration

VWP and NetBeans Data Source Integration

linked from DatabaseNB6Planning

Contents


Owner: John Baker

This is a high-level design overview for the integration of data sources between Visual Web Pack and NetBeans. The tracking umbrella issue is issue 89438.

A more detailed functional spec and design spec will be provided later; here we are just trying to get a general sense of how things work and what needs to be done to accomplish our goal of integrating the Visual Web's use of data sources with NetBeans.

It's been very helpful to capture all design discussions for this issue in one place. But I would really like to see a pure functional spec that describes all the affected use cases and the changes in functionality visible to the user.

Then we can have a separate design spec (maybe just an update of this page) which takes all of the use cases described, and shows how we plan to implement this.

Right now it's a bit of a mish-mash and it's hard to see what the actual functional changes will be for the user experience.

-- DavidVanCouvering Each feature has an issue assigned, some features may require a functional spec The functional spec or issue will describe the functional changes to be made.

-- JohnBaker


Use cases

Creating a data source

In the visual web project at design time, a data source needs to be created the first time a table from a given database is bound to a visual component.

The JNDI name and resource reference name both need to be defined at this time. We are going to prompt the user to provide the resource reference name, as this is the logical name that is used throughout the application and which should have a reasonable name for the developer.

The JNDI name will be automatically generated. One proposal on the plate that looked promising is extracting a utility routine that is currently part of the Glassfish app server plugin that knows how to parse the JDBC URL for all supported drivers to get the host, port, database name, vendor, etc. We could then use that to generate a JNDI name like <vendor>-<database-name>. Exactly what we do to generate the JNDI name should be defined as part of the functional spec.

The user can change the JNDI name if they choose to by editing the server-specific file where it is defined. We need to document exactly how this is done so that users know how to do this.

The data source is created in the project and will be created in the target server at deployment time.

If a data source of the same JNDI name already exists in the target server with exactly the same connection parameters and URL, then we just use that one (and copy it to the project).

If a data source of the same JNDI name already exists in the target server with a different JDBC URL or different connection parameters, then we generate a different JNDI name and create a data source using that name.

Potential conflict - It is possible for the user to create two resource reference names that map to the same JNDI name. As a result, when a table from a database is dragged onto a component, it is not clear which resource ref name to use. Our suggestion is to pick the last one created; the user can change it if they want to. An alternative approach is to bring up a radio button dialog and let the user pick which res-ref name they want to use

It is also possible for a project to end up with two data sources that point to the same database. In this case again you can have two resource ref names pointing to the same underlying database . Again we are proposing a dialog with a combobox that lets the user choose.

Tracked by issue 88871 Functional spec is DatasourceNaming

-- DavidVanCouvering

Obtaining a data source

In NetBeans a data source can be obtained either from the project using J2eeModuleProvider.getModuleDatasources(), or from the server using J2eeModuleProvider.getServerDatasources().

Getting a connection (a DatabaseConnection object) for a data source

A data source encapsulates the connection parameters (database URL, JDBC driver class, user name and password), thus it is possible to locate a connection for the data source in the Database Explorer. NetBeans should provide a method returning such a connection.

MW: I would also like that data source object to contain optional information about the used driver jar files and possibly additional userdefined properties.

AB: Why are the jar driver files needed? Please describe the use case. It seems to me it doesn't help when the project is moved to another computer, where the connection is not registered, since the drivers may be in another location than on the original computer.

MW2: If we want to copy the driver jar files to the target container, we need that info. I was even thinking about storing the whole driver jar files with the project. But I don't know how far we want to go there. If the path info is not correct on a different computer, we could at least fail the build instead of pretending everything is OK.

AB2: Not clear what is meant by "that info". Anyway, I don't think the driver jars should be stored in the project. If anything, drivers should be shared between the Runtime tab, the Java Libraries customizer and the project. We will get closer to that state when drivers are registered as Java libraries (perhaps in 6.0).

We can still fail the copying of driver jars. Given a data source, at deployment time we will try to locate a connection for it, using the findDatabaseConnections() method below, with a false parameter. If that fails, we will look at the data source's driver class (getDriverClass()) and we will try to locate the driver in the Runtime tab. If that fails, we can fail the build, notify the user, etc.

DVC: I confess I am new to all this, but I am uncomfortable with us copying driver jar files around. You end up with two copies of the same driver in the system, and the user may get unexpected results when they upgrade their driver and do not see behavior change because a copy of the old version is still sitting in the project somewhere. Why can't the project classpath just include the driver jar file?

This method should deal with corner cases such as a matching connection or the needed JDBC driver not being registered in NetBeans. Currently it can create a connection, but in this case it will display the New Database Connection dialog from the Database Explorer, with the database URL, user name and password pre-filled. This is necessary in order to let the user choose the database schema to connect to (which is needed since the data source does not know the schema name). Additionally, if the driver is not registered the New JDBC Driver dialog will be displayed (not yet implemented).

MW: Some of that might ease a bit when we get information about the driver jar files.

The method is currently in a friend API in j2ee/utilities. It should be made public and probably put in a support package in j2eeserver.


public class DatasourceHelper {

    public static List<DatabaseConnection> findDatabaseConnections(Datasource datasource, boolean tryRegister);
}

This method will try to find the database connections (there can be more of them) matching the given data source. If the tryRegister parameter is false, it will return an empty list if no such database connections are registered in the DB Explorer. Otherwise it will try to register such a connection by asking the user.

This methods returns a list because while a data source only has a database URL, a driver class name and an username, a DB Explorer connection also has a database schema, and there can be more connections with the same triple (database URL, driver class name, username) registered -- they have different schemas. This also means that when working with such a connection fully-qualified database object names must be used (which is a good practice anyway).

Tracked by issue 90207.

It also requires API changes in the Database Explorer:

  • a JDBCDriverManager.showAddDriverDialog() method to open the New JDBC Driver driver with some pre-filled data, such as the driver class. Tracked by issue 90209.

Another thing we may want to do is notify the user that the project is broken (e.g. a connection or a driver cannot be found for a data source). This is doable already by listening on the ConnectionManager and JDBCDriverManager and checking a DatabaseConnection and JDBCDriver can be resolved for each data source used by the project. Actually the toughest thing may be determining the "data sources used by the project".

Tracked by issue 93949.

Making a physical connection (obtaining a java.sql.Connection object)

There is already the ConnectionManager.showConnectionDialog() method. The method's behavior depends on whether the password is known:

  • If the password is known, a modal progress dialog is displayed while the connection is being made
  • If the password is not known, the Connect dialog appears asking the user for the user name and password

A VWP requirement is to be able to save the connection password between IDE sessions and to save it automatically for the current session in order to avoid displaying the Connect dialog too often. We can change the semantics of the Remember Password checkbox in the Connect dialog of the Database Explorer to save the password to disk. When the checkbox is not checked the password will be stored just for the current session. We will also need to change the progress dialog which is currently displayed when connecting a connection whose password is stored to a modal dialog allowing the user to connect with another user name, otherwise this would not be possible.


When the Remember Password checkbox is checked a warning message appears informing the user that the password will be saved to disk in a way which makes it easy to retrieve and that this is insecure. We will have a simple obfuscation scheme to avoid accidental disclosure, but no encryption.

Tracked by issue 87600.

Deploying to the server

Discuss what happens here with data sources.

Q What if the data source doesn't exist on the server?

A The data source is created on the server when the project is deployed.

Q What if the data source already exists on the server (has the same JNDI name)?

A If the existing data source has the same connection parameters as the project data source, no action is needed.

For conflicts see the next section, "Opening a project on another computer"

Opening a project on another computer

In NetBeans when a data source from the server is used in a project, the definition of that data source is not added to the project. Thus, when the project is checked into source control and checked out on another computer the designer will display the Component Error page indicating that connection to the database throught the dataprovider failed. The requirement is to save the definitions of any server data sources to the project. Also, the other computer may not have the JDBC driver and Database connection needed by the application, registered in the IDE. The user can fix this by registering the two (driver and connection) or using the Resolve Datasource(s) . If the driver and connection had not been registered then even with the server specific info included with the project, the Component Error page will still open as a "live" connection to the database is required. See section below, Project Migration for projects containing data sources


Resolution

The datasource defined on the server will be added to the project, for all projects. This way we keep the datasources in-sync by keeping the data source information on both the project and in the server. This does mean that potential conflicts can occur when the project uses a different server, either because the user changes servers or because a different user opens the project on a different machine, and the target server has different data sources defined on it.

There are three types of scenarios:

  • A data source exists on the server with the same JNDI name, JDBC URL and connection parameters as the data source defined in the project. In this case we just use the data source in the server (not a conflict)
  • A data source exists on the server with the same JNDI name and the database vendor is the same, but the connection parameters or other parts of the URL differ. We use the data source and generate a warning. The warning contains detailed information about the conflict and to suggest to the user to change the Datasource name (there may be such a dialog available from Creator that can be reused).
  • A data source exists in the server with the same JNDI name but the database vendor is different. What we had discussed in this case is that the project is marked as "broken" and a "broken" indicator is displayed by the data source showing that it could not be mapped to a data source in the server, and the user has to manually fix this before the project will work. We won't support this rare scenario and this can be documented.

The 2nd part of the solution is explained in the section below, "Project Migration for projects containing data sources" - JWB

-- DavidVanCouvering

I don't understand the last scenario. How can a data source exist on the server "with a different resource reference"? A resource reference is used in the project, not on the server. -- AndreiBadea

Andrei - I have updated the text to say what I meant, I got it mixed up -- DavidVanCouvering

-- JohnBaker

__Tracked by Issue 93159

Deleting a data source from a project

Does this mean the data source is removed from the server too?

Deleting a data source from a server

I suspect there is a way for a user to manually delete a data source from a server (e.g. by modifying the server's configuration files). What impact, if any, does this have on a project that created that data source at design time and expects it to be there at deploy time?

The project should not create data sources on the server at design time, just at deployment time. If a data source is deleted from the server, it should still be in the project, so it gets recreated when the project is deployed. -- AndreiBadea

I had thought in discussions that the data source is created on the server at design time, I must have misunderstood.

No the data source is only created in the Project in the IDE at design-time - JWB

Removing a project

When a project is removed, does this impact the data sources on the server? I think this is a special case of "Deleting a data source", where all data sources defined in a project are deleted as part of deleting the project

No, there are no hard references and there shouldn't be. No events are fired to notify the server. JohnBaker

Design Issues

These are not use cases, but design issues/discussions

Implementing the design-time JNDI context

At design time VWP needs to render the components in the visual editor. A component will hold a data source name (more correctly a resource reference name) and will attempt to do a JNDI lookup for that name in order to retrieve a DataSource instance (I suppose) and get a connection from that. It is necessary to implement this design-time JNDI name delegating either to the project the component is used in or to all open projects.

The latter solution requires solving duplicates (two projects using the same resource reference name for two different data sources), we would thus prefer the first solution. However, it requires being able to implement a design-time context per project.

__Tracked by issue 93216

Keeping the designer live when a project containing data sources is switched to another server

This looks like an use case to me (switching to another server). -- AndreiBadea I would agree - DavidVanCouvering

In NetBeans newly created data sources are added to the project in a server-specific format. When the project's target server is changed the data sources for the old server are not used in any way, therefore the designer would not be able to find them. The project will also fail at runtime because of missing data sources. The requirement is to save data source definitions in a server-independent format.

After discussing this the decision was taken to generate all data sources to the format of the new server when the target server is changed. This does not require any API changes.

This is a nice-to-have feature, but not required. This wasn't supported in past releases of visualweb/Creator JohnBaker

Tracked by issue 90264.

Preventing conflicts in resource reference / JNDI names

MW: The other thing is the clear distinction between the source/design time JNDI name and the finally deployed JNDI name on the server. Trying to keep them the same makes life soo much harder when switching target containers for instance.

SH: I agree that we should somehow prevent JNDI names conflicts during deployment.

Resolution

In our design we will keep a clear distinction between resource reference name and JNDI name.

  • JNDI name is the logical name used by the server for a physical resource. This allows an administrator of a server to map a JNDI name to a different physical resource in one place, without having to change all references to that resource within the server configuration.
  • resource reference name is the logical name used by the application to refer to a resource, in this case a database connection

Resource reference names are mapped to JNDI names at the time of deployment. Each server may have a different set of JNDI names. In this way a single application can be deployed to multiple server without having to change the code and recompile.

It is important for us to remember this distinction. In our code, we need to make sure we have a clear distinction between these two names and do not "mash" them together.

When developers are looking at names, they should be looking at the resource reference name. They in general do not need to know or care about the JNDI name. Developers do need to deploy to a server for testing, but they don't need to have all the flexibility provided by the two layers of indirection, and we can "take care of" the JNDI name for them.

But there may be times when this distinction becomes important. For example, a developer may want to stage their application from a development server to a test server to a production server. In this case, they need to know that they can map their resource reference name to different JNDI names for each server. So although in general we should hide this distinction for developers so they can just get their job done, we should make it possible for them to adjust the mapping as they need to in those cases where the distinction is important and useful.

For example, when the user drags a table onto a visual component, and this is the first table for a given JDBC connection, we will prompt them for a "name" for the data source. This is the resource reference name. We will then generate the JNDI name automatically, and create the data source on the server automatically.

However, when they deploy to a new server, we may need to ask them to choose the JNDI name they want to use. I thought we agreed not to ask anything on deployoment, just to show a warning message when there is a conflict in data sources. -- AndreiBadea

You're right - see the use case above about deploying to a different server. DavidVanCouvering

Like that...

In NetBeans right now there is no distinction between resource reference name and JNDI name.

Tracked by issue 93815.

-- DavidVanCouvering

Project Migration for projects containing data sources

In Creator 2, users could create data sources using the Data Sources node in the Server Navigator. Also, there was a facility to import/export datasources used by the IDE (by saving datasources used a config file) when migrating projects, whether from release to release or machine to machine.

In VWP 5.5, the Server Navigator was removed for the sake of some redundancy with the Runtime window. Runtime window is where Application Servers and Database connections are made. Also, there was a limitation in that users could not create their own datasources and import/export to a config file. Instead a new facility to resolve data sources used by projects when the corresponding database connection was not installed, in the Runtime window. See Resolving a Data Source; also there's Help topic included with VWP 5.5.

For 6.0 will be much like 5.5. There will be a new Services palette, but only database connections will be listed, not data sources. Data sources will have to be resolved project by project, same as 5.5.

Importing a project includes opening a Creator 2 or VWP 5.5 project or moving a VW 6 project to another computer.


__Tracked by issue 94804 and issue 93949

Covered in ProjectImportDatasourcesSpec

Not logged in. Log in, Register

By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo