Bug 363081

Comment 1 Will Smythe 2011-12-12 23:22:31 EST

Ortwin - I have moved your request related to EGL Runtime Data Source into a separate bug (Bug 366478 - Support for EGL Runtime Data Source project capability). This enhancement will only deal with the 'data access application' request.

Comment 2 Will Smythe 2011-12-12 23:35:21 EST

Ortwin - a few questions:

1) Are you primarily looking for a wizard to create a Service or Library that contains functions for getting, updating, deleting, creating data from a database table?

2) If so, what (if any) additional functions should be generated by the wizard? Or is the current set of functions sufficient?

2) Are there functions of the existing RBD Data Access App wizard that you view as 'critical'?

=========================

Assuming the answer to question #1 is "yes", I think this could be implemented as an extension to the New EGL Service wizard (and optionally New EGL Librazy wizard), much like the New EGL Record wizard has a set of 'templates' you can choose from. The flow might look like this:

1) Developer launches the New EGL Service wizard
2) Developer supplies an EGL part name (MyNewService) and selects a package.
3) Developer chooses the 'Database access' template from the list of templates, and clicks Next.
4) Developer selects a database connection and then selects 1 or more database tables (this wizard panel would resemble the wizard panel that already exists in the New EGL Record Wizard) and clicks Next.
5) The source for the new service (and new Entity record) is displayed in the Preview page. The developer clicks Finish to create the new service file, which includes the service part and entity record(s).

The source for the service would look nearly identical to the service code generated by the RBD Data Access Application wizard today (except there would be 'repeated' functions if more than 1 table is selected). For example: if the developer had selected the ORDER and CUSTOMER tables, 1 service part would be created (and it would be named whatever it was named in the first page of the wizard), but it would contain functions like getAllOrders() and getAllCustomers() and deleteOrder() and deleteCustomer().

The service should support a StatusRec record (to provide status of a functional call back to the caller) and should also provide a function to fetch x number of rows starting at a specific index (in order to support paging through a long table).

Comment 3 Ortwin Pepermans 2011-12-13 17:04:07 EST

Hi Will,

In the current 'Data Access Application' wizard one can choose between two types of generated code: 'Service' and 'Library'.

When you choose 'Service' a Service part is created supporting CRUD functions which accept SQL commands for input. This option is bad practise for the generated service can easily be taken advantage of by a hacker submitting his own SQL commands.

When you choose 'Library' a Library part is created supporting the same CRUD functions. This is the option we use to choose, but it has some drawbacks. We manually have to create a Service part to wrap the generated Library part and create the same CRUD functions only they do not take an SQL command, but rather only the key fields of the database table. Also we have to create some code for error handling, since the SQL errors are not typical the messages you want to present to the user (eg. in stead of 'EGL0500E SELECT: record not found[sqlstate:00000][sqlcode:-100] EGL0002I The error occurred in PfplpdLib processing the GetPfplpdList function.' we like to show 'No orders found that match your selection' to the user). The code we now manually create could easily be generated, so that could be an enhancement of the wizard.

There are yet another number of 'nice to haves':
- The generated routine that determines the number of records that matches the query fails in certain situations. We opened a RFE for this (#9526) but it was rejected (for the wrong reasons I think).
- We usually create the Service Interface part in a separated project (a RUI project). On top of this Interface part we manually create a DAO part. This part, even more than the interface, abstracts the service call and can take care of data conversion, data buffering (so the number of subsequent service calls can be reduced by returning buffered data), error handling, etc. We generate those DAO's using snippets, but it also could be part of the service generation proces to simplify things for the developer.
- When using the 'Data Access Application' wizard for generating EGL JSF artifacts the wizard would create a complete maintenance function with screens and all. It would be nice if this also could be generated for EGL RUI. And then ofcourse the wizard has to be configurable to how the screens would be generated.

You see we do not have a lot to ask for, just the most common functionality, haha.

Ortwin

Comment 4 Will Smythe 2011-12-13 23:16:57 EST

Ortwin - excellent feedback. Thanks for taking the time.

I think we should add this functionality in stages. Stage 1 could be to add the wizard extensions to create a database library or service (as described in comment #2). The source code produced could initially be identical to what is produced in RBD (to keep things simple). Stage 2 could be to enhance the code that is created based on feedback (like Ortwin's below). Stage 3 could be to create a full RUI maintenance app around the service/library.

From the beginning, this SQL service/library wizard extension should be extensible such that Ortwin and others can add additional wizards pages and configuration parameters, such as parameters to control creation of an interface or specialized data access part. This will allow others create extensions that produce custom code and custom artifacts.

Comment 5 Brian Svihovec 2011-12-14 15:20:48 EST

Removing the target milestone from this 'theme' to indicate that it is a place holder for a discussion and will drive the creation of individual tasks.

This theme is currently being considered for the .8 release, which means that some content from this theme will most likely be developed in this time frame.

Comment 6 Brian Svihovec 2011-12-14 15:21:41 EST

Should the name of this enhancement be changed to the following? 

Service and Library wizards for database access

Comment 7 Will Smythe 2011-12-20 21:39:53 EST

I broke the first requirement (wizard extensions to create a database library or service) into a separate bug and targeted for 0.8 - Bug 367261