More information

User Security - The View Generator

Access the View Generator using Tools>System Upkeep>User Security, and then select View Generator from the Group Maintenance section header Edit button context menu.

Important! For Oracle users - only the system administrator logged on as MILLORA can access the View Generator.

This topic provides instructions for the following View Maintenance functions.

Display a List of Views
Display the Details of a View Definition
Check the Availability of a View in Millennium
Create an Individual View
Drop an Individual View
Create a Set of Views
Drop a Set of Views

For background information on the function of Views in Millennium Security, see the User Security topic.

View Generator

The View Generator is an application that is accessed via the context menu from the Group Maintenance section header Edit button on the User Security page. It allows you to perform several tasks related to the creation, editing or dropping of SQL views. These views are then available for use in Group Maintenance. The View Generator allows you to:

Display a list of the views that 'belong' to each data table in the database.
Display the contents (the definition) of an existing view.m
Create individual views for a database.
Drop individual views from a database.
Check a view to ensure that it is available for use with Millennium.
Create a set of views (one for each data table) that have a common naming convention and structure, to assign those views to selected Database Groups, and to grant access to those groups.
Drop a set of views from the database (all that are assigned to a selected set of Millennium groups.

The View Generator contains three main list boxes: the Table Names list, the Existing View list, and the Database Groups list. There is also a list of View Options radio buttons, which allow you to perform differing functions from this form. Below these listings, there are a number of buttons, check boxes, and text boxes for additional functionality. Each of these components is described below.

Table Names

The Table Names list box displays the Millennium data table names as they are named in the database. (Remember that there are a few tables whose names differ somewhat from their 'on screen' name. These are Basic Data/Corebio, Education/School, Employment/Jobs, Summaries/Memories, and Citizenship/Nationality.) You will click on a specific table name to highlight it. Once it is selected, the other functions or activities that you undertake in this form will be focused on that data table.

Existing Views

The Existing Views list box will automatically display the names of all the views that already exist for the data table that is highlighted in the Table Names list box to the left. For certain functions that you may perform in this form, you will need to highlight one of the existing view names. You will do so by clicking the name in the list.

Database Group

The Database Group list box will display the names of all database groups that have been created within Millennium. This serves as a reference when defining new views and a shortcut method of assigning a tag (a name) for a set of views that are to be assigned to a single group. It also serves as a means of selecting a single group or multiple groups for the purpose of creating a complete set of views (Create All), of removing access to a complete set of views (Drop All), or Granting an Access level to a set of Groups for a complete set of views. Group names may be highlighted in the Database Groups list by single clicking the individual names. Once highlighted, they may be de-selected by single clicking again. Multiple Group names may be selected at the same time.

View Options

When the View Generator form is first opened or when you click on the Reset button, the display is automatically set to the View Names option. When switching from one of the View Options to another, you are strongly encouraged to use the Reset button to ensure that the form variables are reset to the default status.

View Names

This option is used to display all of the views that belong to a data table that you have selected from the Table Names list. This is the default option for the View Generator. When the form is first opened and any time that you click on the Reset button, the View Names option is activated. When this option is activated, you may double click the name of a data table to display its view names, OR you may single click a table name and then click on Continue and then Execute to have the view names displayed in the list box to the right.

View Detail

This option is used to display the details of a particular view that you select from the Existing Views list box. If this option is activated when you single click a view name in that list to highlight it and then click Continue, OR when you double click the view name, the large Query/Results text box at the bottom of the display will show the details of the definition of that view. Note that the View Details option is not operational until you have used the View Names option to select a data table and have displayed its existing view names.

Check View

This option is used to check a specific view that is associated with a data table, to verify whether it is or is not available for use in Millennium. Some views may exist on your system that were not created in Millennium. They will be shown in the list of Existing Views for the table to which it 'belongs'. (That ownership will be determined based on the first table that is referenced following the where statement in the view definition.) Such views may not be recognized appropriately in Millennium. To establish a view's availability, activate this option and then click on the view name in the Existing Views list. You will be given a message saying that the view is available to Millennium, OR it will tell you that it is not available and suggesting that you can make it available by clicking the Execute button.

Create

This option is used to create a single view on a single data table in Millennium. To create a new view, you must activate this option, and then highlight the desired data table name from the Table Names list by single clicking the name and then clicking Continue OR by double clicking the table name.

You must create a tag (a name) for the new view. This tag will consist of the table name plus additional identifying text. That additional text is taken from the Tag text box in the form. You may either type in any text that you want into the Tag text box, OR you may double click on the name of a single Database Group name from that list box just above to use that group name as part of the tag for the view. When you double click the group name, it will automatically appear in the Tag text box. (Note that this technique for filling in the Tag text box is only applicable when you highlight a single group name. If you highlight multiple group names, the tags will automatically be created based on the names of the groups.) In either case, you may then choose to have that Tag text attached either before or after the data table name by clicking the desired radio button labeled Tag Loc(ation).

When this option is used to create an individual view, that view is not assigned to any group and no security access privileges are granted. That must be accomplished via Group Maintenance by editing a group and assigning that group the appropriate access to the desired views.

Drop

This option is used to drop a single view on a single data table in Millennium. This option is not operational until you have used the View Names option to display the existing views and have then highlighted the desired view name shown in the Existing Views list. When you have done so, and have activated the Drop radio button, you should click on the Continue button, and then the Execute button to confirm your intention to drop the selected view.

Create All

This option is used to create one view based on each of the Millennium data tables shown in the Table Name list. This option incorporates additional behaviors that the simpler, Create (one view) option does not include. The Create option simply creates one view based on one table. It does not assign it to any Group, nor is any access granted when you use the Create option. Create All, however, creates a view for each data table for each Millennium Group that you select from the group list. It tags each view with the corresponding group name, it assigns that view to that group, and it grants that group the security settings that you select in the View Generator form. When you use this option and choose multiple groups, the Tag text box is disabled, as the system will automatically use the group name as the tag identifier, though you may still choose whether you want it appear before or after the table name.

Once the Create All process has been completed, you may edit any of the individual view assignments or the access settings that have been granted, by exiting the View Generator form and performing the edits via Group Maintenance.

Important! This is a very powerful feature that should only be used with complete understanding of the process, in order to avoid errors which could create significant problems for users on your system.

When Create All executes, each view will be given a Tag, and optionally, a common Where clause. Enter the Where clause by checking the Add Where Clause check box, and typing in a syntactically correct fragment of a SQL statement.

After the Where clause has been added, click Create All. Next, double-click a single Database Group. The double-clicked Database Group will appear in the Tag field. If you want to Create All on multiple groups, single-click each group you want to include in the Create All, or, alternatively click the Select All Groups checkbox. When multiple groups are chosen from Database Groups list box, the Tag field is ignored. After the appropriate group(s) are selected, click Continue to start the View creation.

If more than one group is selected from the Database Group list, each group will be processed through the list of tables. That is, the first group will be processed through the table list, the second group through the table list, in succession until all selected groups have been processed.

After Create All has completed, a Start Time and End Time will display, along with a Completion message. Click the Close button to close Create All.

Drop All

This option is used to drop all of the views that are assigned to the set of Millennium Groups you select. It is very important that you understand that this option will drop from the database, any view that is assigned to any group that you select, even if that view was also assigned to another group that was not selected. Therefore, before using this function, you should verify that the views that will be dropped are not also in use by another group.

From the View Options, click Drop All. Select the groups from the Database Group list box, as necessary. Optionally, click Select All Groups checkbox to remove all views from all Database Groups. Note: You can use Select All to select the entire list, and then scroll through the list, and single-click any groups you with to exclude from the Drop All process.

After you are certain you have the groups selected you want to have the Drop All applied to, click Continue. The Drop All process will start. After Drop All has completed, a Start Time and End Time will display, along with a Completion message. Click the Close button to close Drop All.

Action Buttons

How do I Use This?	This button triggers the display of prompting text for the use of the form, and it also resets that form to the default option, View Names.
Cancel	This button closes the View Generator form and returns you to the User Security display.
Continue	This button is used to advance the process to the next step for those View Options that require multiple steps.
Execute	This button is used to initiate the final step of the process for those View Options that require multiple steps.
Reset	This button is used to cancel the current operation and reset the form to its default option (View Names). Note that Resetting the form does not clear the Tag or the Grant Access text boxes. This allows you to 're-use' those variables when creating multiple views without re-entering that information each time. You are encouraged to use the Reset button when moving between View Options in order to ensure that the appropriate variables are reset.

Miscellaneous Functions

Enable Java Logging

When this check box is activated, the system will send messages to the Java log file. This information may be useful in diagnostic situations - it is not required for the use of the form.

Select all Groups

This check box is a shortcut method of highlighting all Database Groups that are displayed in the Database Groups list box just above. This check box is only available in connection with the Create All or Drop All View Options.

Tag

This text box is used to specify the identifying text that will be used (in combination with the data table name) to tag (name) new view(s) that are being created. This may be any free text that you type into the text box, or it is more likely that you will want to incorporate one of the Group names by double clicking the name in the Database Groups list. This text box is only used in connection with the Create One or Create All View Options.

Tag loc (After/Before)

These radio buttons are used to indicate whether the information in the Tag text box should be placed before or after the data table name as the system constructs the name of a new view that is being created. This text box is only used in connection with the Create One or Create All View Options.

Grant Access

This list box contains the five supported grant statements and is only used in connection with the Create All View Option. Granting access to a group is actually a Group Maintenance function and it is performed on a single group at a time by editing that group using Group Maintenance, not via the View Generator. This functionality is included in the View Generator to facilitate the process of setting up an entire set of views for one or more groups and having the same Grant Access settings applied as part of the same process.

Add Where Clause

When defining a new view, you will often want to use a where statement to define the circumstances in which certain data is included in that view. That clause may be typed directly into the view definition that is shown in the Query/Results text box. But the text box following the Add Where Clause label allows you to create such a clause and re-use it in multiple view definitions without re-typing the clause each time. You must click to activate the check box to activate this option.

A reserved character is available for use in the Add Where Clause text box. The ^ (caret) character is used as a special placeholder. When the system encounters a caret during view generation, it will be replaced with the ID number field that is defined for the current table.

Example:

Create the following where clause:

", corebio where coreid = ^ and coresource = test"

When the address view is generated, the clause would change to:

", corebio where coreid = addrid and coresource = test".

Please notice in this example that the Where Clause is actually a fragment of an SQL statement, and it must be capable of creating a syntactically valid view, when appended to the initial part of the view definition. Views that refer to other tables need to include all relevant table names within the view definition.

The complete view definition would be:

create view addresstest as select * from address, corebio where coreid = addrid and coresource = test

Important! The comma that starts the where clause in this example is needed for the SQL syntax to be correct.

Notes:

When you add a Where Clause to the view, you must adhere to SQL syntax standards. If you do not, the view definition will not pass the SQL syntax check, and the view will not be created.

Also note that to ensure that this where clause is applied when inserting or updating data in the view, use the "with check option'" syntax as part of your where clause.

The Add Where Clause text box is only used in connection with the Create One or Create All View Options.

Query/Results

This text box is used for multiple purpose. It may be used to display the definition of a selected View, or to edit the definition of a view you are creating. It may be used to display error messages, or the results of checking to see whether a view is available to Millennium.

Display a List of Views

Access the View Generator.
Click the View Name View Option if necessary.
Locate the desired data table name (it may be necessary to scroll). Single click on the desired table name to highlight. Click Continue. Alternatively, you may double click on the desired table name.
All existing views for the selected data table will display in the Existing Views list box.

Display the Details of a View Definition

Display the list of existing Views for the desired data table (see above).
Click the View Details View Option.
In the Existing Views list, locate the desired View name and click to highlight. Click Continue.

Alternatively, you may double click the name of the desired View.
The Query/Results box will display the selected View's definition.
If desired, click Reset to return the form to its default state (names of the existing views for the selected data table may remain in the Existing Views list).

Check the Availability of a View in Millennium

Display the list of existing Views for the desired data table (see above).
Click the Check View View Option.
In the Existing Views list, locate the desired View name and click to highlight. Click Continue.
The Message area of the display will indicate whether the selected View is or is not available for use within Millennium. The Query/Results box will display the View Code for the selected View.
If the message indicates that the selected View is not available, you may click on the Execute button to activate the View for use in Millennium.

Create an Individual View

Display the list of existing Views for the desired data table (see above).
Click the Create One View Option.
Use the Tag text box to name the View. The text entered here will be affixed to the data table name to create a unique name for the View.

If you want the View name to include the name of an existing Database Group, highlight the desired name from within the Database Group list and click Continue. (Alternatively, you may double-click the name of the desired Database Group from within the Database Group list). The selected Group name will be copied to the Tag text box.
To affix the tag to the beginning of the data table name, select the before Tag Loc(ation) option. To affix the tag to the end of the data table name, select the after option instead.
The SQL statement(s) that will be used to define the View are entered in the Query/Results box. You can check the Add Where Clause check box to add a condition to your View. The system will retain the clause so that it can then be used when creating subsequent Views. Even if you choose to not include the Where clause when creating the current View, the system will still retain the clause that you entered. It is not necessary to use the Add Where Clause feature. You may type the condition directly into the Query/Results box instead. However, the system will not retain it for future use.
Click Execute to create the View.

Drop an Individual View

Display the existing Views for the desired data table - the table that the View was created for (see above).
Click the Drop One View Option.
In the Existing Views list, locate the name of the View that you want to drop and click to highlight. Click Continue.

Alternatively, you can double click on the desired table name.
The Query/Results box will display the View definition. Verify that this is indeed the View that you want to drop.
Click Execute. You will be given a message that the View has been dropped.
You may repeat this process for another View, switch to another View Option, or click Reset to return the form to its default state.

Create a Set of Views

Access the View Generator.
Click the Create All View Option.
If you want to create a set of Views for a single Group (from the Millennium Groups list), enter text for the Tag, OR double click the name of the Group to use the it as the Tag. If you are creating a set of Views for multiple Groups, any text in the Tag text box will be disregarded and the group name will be automatically be used for the Tags.
Select the Tag Loc(ation) you prefer - either after or before the table name.
Select the Security Access that you want to associate with each of the Views that is being created.
If desired, enter a syntactically correct fragment of an SQL statement to be used as a Where Clause, and click to activate the check box to Add Where Clause, OR click to remove the check in the Add Where Clause check box if you do not want to add the current clause to each of the View definitions.
Click Continue to begin the immediate creation of the Views. A status message will be displayed and will indicate when the creation is complete.

Drop a Set of Views

Access the View Generator.
Click the Drop All View Option.
If you want to drop a set of Views for a single Group (from the Millennium Groups list), enter the corresponding text for the Tag, OR double click the name of the Group to use the it as the Tag. If you are dropping a set of Views for multiple Groups, any text in the Tag text box will be disregarded and the group names will be automatically be used to identify the Views to be dropped.
Select the Tag Loc(ation) - either after or before the table name, that corresponds to the names of the Views to be dropped.
Click Continue to begin the immediate dropping of the Views. A status message will be displayed and will indicate when the drop is complete.

Top of Page