TRAINING (PS-11) - PeopleSoft Application Development - Part8 (Step-4: Build the SQL Table)

Step-4: Build the SQL Table

Using Application Designer, you create several kinds of object definitions that represent database components.  For instance, field definitions represent table columns, and record definitions represent tables, indexes, and views.  The important concept to remember is that the definitions are just the blueprints for the actual, physical database components with which they are associated.  In the context of the whole application development cycle, using the Build process (creating SQL tables, views, and indexes) comes after you've defined new fields and created the record definitions.  After you build the SQL tables you would then begin creating Panel definitions.

After you create and define your field and record definitions, you will need to create the underlying SQL tables that will actually house the application data that your users will enter online in your production environment.

Build uses Data Definition Language (DDL) to construct a physical database component based on the associated record and field definitions you've created.

•  Tables
•  Views
•  Indexes
•  Triggers

Build Menu
You use the Build menu in Application Designer to access the Build dialog by selecting either Current Object or Project.  You can also select the Settings menu option to review the Build Settings dialog to make sure that you have selected all of the appropriate options for your build process.



You use the Build dialog to create and/or run the SQL scripts that define the underlying database components.  Use it to build tables, indexes, views, and to alter existing tables.

Build Scope
There are three different scopes of the Build feature that determine the size of your build project. Depending on what you actually want to build—one record or an entire project—you can determine this by selecting one of the scopes described in the following sections.

• Current Object
  Select Build, Current Object when you want to build or alter the active record definition.
• Build Project
  Select Build, Project when you want to build or alter all the records, indexes, and views in the current project—all the records that appear in the project workspace.  As shown in the following example, you will typically find numerous record definitions in the Build Scope list box.
• Build Selected Objects
  If you do not want to build an entire project or even the current project, you can opt to build just the objects you select in the project workspace.  In some situations you may just want to build a subset of the records in the project that is currently open.  You can select the objects you want to build by pressing the Ctrl button, and selecting multiple records in the project workspace.  After you have selected the appropriate records, then, with a right-mouse click, invoke the popup menu, which offers the Build option. 

To Build all objects in a database, you would create a new project and insert all the appropriate records; you can just insert records of a particular type, such as views.  If  you create another record after the "all records" project is built, you need to manually add the new record to the project.

Build Dialog

You use the Build dialog to create and/or run the SQL scripts that define the underlying database components.  Use it to build tables, indexes, views, and to alter existing tables.

Build Options

The Build Options group allows you to specify what action you want to occur:

• Create Tables
• Create Indexes
• Create Views
• Alter Tables
• Create Triggers

If you select Create Tables or Alter Tables, then, by default, Create Indexes will automatically be selected.

Build Execute Options

The Build Execute Options group offers the following options:

• Build script file: The advantage to using this option is that you can review and update the SQL prior to executing the script.  
• Execute SQL now: The advantage in using this option is it's not necessary to invoke another program to run the SQL; the SQL runs as part of the Build process.  The disadvantage with running the SQL immediately is that you have no opportunity to review the SQL prior to it being committed to the database. 
• Execute and build script: The advantage of this option is that you can review the SQL that the Build process just ran.

Build Settings

Pressing the Settings button from the Build dialog opens the Build Settings tab dialog where you can view or change your options.

The Build Settings dialog offers the following tabs:

• CREATE: Specify your Create Table and Create Views options.
  When you select Create Tables or Create Views, you'll want to make sure that you also specify the desired options on the Create tab on the Build Settings dialog. The following sections describe what the options on the Create tab mean to your build. The following sections describe the options that the Alter tab offers.
  
  
  Table Creation OptionsThese options determine when a table should and should not be created.
  
  (i) Recreate table if it already exists:This options will always drop and recreate a table if it already exists.  Use this option with extreme care because if there is any data in the table that already exists, it will also be dropped. If you select this option, Build prompts you to confirm your specified intention prior to performing any destructive action.  If you don't care about losing the data that resides in the table, then this option is much faster compared to doing an alter.
  
  (ii) Skip table if it already exists:This option will only create those tables that are newly defined. If you want to preserve the data already residing in existing tables or you're just interested in creating the tables that do not already exist, this is the appropriate option.
  
  View Creation OptionsSimilar settings are available for creating views that you use for creating tables.
  
  (i) Recreate view if it already exists:Since views are just logical views of data, it is safe to use this option;  you don't run the risk of losing physical data.  Using this option ensures that all views in the project are rebuilt, whether they need to be or not.
  
  (ii) Skip view if it already exists:If you're only concerned with creating views that do not already exist in the database, this is the appropriate option.  This option is useful if you want to run Build Project on a large project and only a subset of the views in the project need to be created.  This consumes less time than recreating all the views in the project.
  

• ALTER: Specify your options when running Alter Tables.
  If you make any changes to your record definitions, you'll need to make sure the tables in your production database reflect the current definitions to maintain your data integrity.  When you perform an alter, make sure you select the appropriate options on the Alter tab. The following sections describe the options that the Alter tab offers.
  
  
  
  
  Drop Column OptionsThe Drop Column Options are referenced whenever a field is deleted from a PeopleTools record definition and data exists in the database for that column.
  
  (i) Drop column if data present:If you have this selected then the column and data will be dropped and a warning will be written to the build log.
  
  (ii) Skip record if data present:If you have this selected, then the alter for that record is aborted and an error message will be written to the log.  Processing continues with the next record.
  
  Change Column Length OptionsThe Change Column length options are used whenever the length of a character column is reduced in PeopleTools and an alter could result in data truncation.
  
  (i) Truncate data if field too short:If you have this selected and the alter could result in data truncation then the record is altered and a warning message is written to the build log.
  
  (ii) Skip record if field too short:If you have this selected in this case then the alter for that record is aborted and an error message is written to the build log.  Processing continues with the next record.
  
  Alter Any
  PeopleTools assumes you want to perform alters for any modifications made to tables, so, by default, all the check boxes in the Alter Any group are selected.  Alter Any allows for custom alter processing regarding:
  
  > Adds
  > Changes
  > Renames
  > Deletes.  
  
  For example, you have the flexibility to add, change, and rename fields, but not delete any removed columns.  When you complete any other custom external conversion processes, you can then enable the delete processing to drop columns that are no longer defined.
  
  The Change Column length options are used whenever the length of a character column is reduced in PeopleTools and an alter could result in data truncation.
  
  Normally you would have all of these options selected, but during upgrades or operations requiring data conversion steps you may choose a subset of actions.
  
  For Example
  - Perform alter with Adds and Changes selected.
  - Perform data conversion routines to populate the new and changed columns (perhaps from columns that are ultimately to be dropped).
  - Perform alter with Rename and Delete selected.
  
  Alter even if no changes

Select this check box to force an alter, even if no changes were made to the tables. This check box is deselected by default. If selected, the Alter by Table Rename option in the Alter Table Options group box is automatically selected and the Alter in Place option is disabled.

Alter Table Option
When altering tables, the alter process takes care of the indexes, regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box.

(i) Alter in Place:
> Only get enabled when Alter even if no changes check box is deselected.
> For the Alter in Place option, the index creation process goes through the Recreated index only if modified option on the Create tab.

(ii) Alter by Table Rename:
> Select to create a temporary table (with changes made to the original table or its fields); the data from the original table is imported into the temporary table. The original table is then dropped and the temporary table is renamed to the original table.
> In the case of Alter by Table Rename, the indexes are dropped automatically when the temporary table is dropped.
> If a table is renamed from the old name to a new name, the indexes that were created for the old table are moved to the new table, but the index names remain in the name of the old table. With Alter by Table Rename selected, the indexes of the old table are dropped before being renamed to the new table name, and the indexes are recreated after the table is renamed to the new name. This way, the index is created in the name of the new table.

• LOGGING: Specify the desired logging level and where you would like the output log file the build process generates.
  
  One way that you can monitor the build process is to review the log files that the build process automatically generates.  Keep in mind that the log file is entirely separate from the script file; do not confuse the two.  How much information that the log file contains is up to you.  You can set up your logging so that all status (both good and bad) appears in the log, or you can specify that just the errors or warnings appear in the log.  This section describes the options you can specify in regards to the Build log file.
  
  You control the settings for the Build log file on the Logging tab on the Build Settings dialog.  Here your specify the desired Logging Level and how you would like to view the log information—from a file or in the output window.
  
  
  
  Logging LevelSelect one of the options in the Logging Level group to specify the detail of information that you want in your Build log output.
  
  (i) Fatal errors only: If you're only interested in seeing the operations that failed.
  (ii) Fatal errors and warnings: To add warnings to that mix by selecting.
  (iii) Fatal errors, warnings and informational: To see everything that processed successfully and all of the errors and warnings.
  
  Logging Output
  The Logging Output group allows you to specify where and in what form you would like to view your log output.  You have the following options:
  
  (i) Log to output window: Writes the same information that would appear in the log file to the Application Designer output window.  Always be aware of the level that you've selected. If there is a lot of detail (high logging level plus many record to process) it may be easier on the eyes to print the logging information to a file, and print it later.
  
  (ii) Log to file: Writes the log to a file.  If you select this option, you have the option to specify a custom location and name for your log file in the Log file name edit box.  If you do not select this option, the edit box will be disabled.
  
  (iii) Log settings: The option writes all of the runtime options to the log.  If you select this option it will not be necessary to rerun a test when you have a problem. Should it be necessary to report a problem with the Build process or troubleshoot, it is important to know the settings that were active.
  

• SCRIPTS: You can choose the format of your script file and the name and location of the file that the build process generates.
  
  If you've elected to build a script file then the build process will generate a script file that contains all of the CREATE and/or ALTER SQL statements so that you can review them prior to running the SQL through another SQL command processor.  If the generated script file meets your requirements a DBA can run the script at a later date.  Build can produce multiple scripts during a single run—one for each build option—depending on the script settings that you specify at run time.  For example, you can specify that the Build process generate a separate script for your tables, views, indexes, and alters, or you can opt to have all the SQL for each action contained in one script.
  
  You use the Scripts tab on the Build Settings dialog to specify where the SQL script is written, whether or not you want multiple scripts generated for each object type, and whether you want previous scripts overwritten.
  
  The following sections describe the options you can specify regarding the build scripts you will generate.
  
  
  
  CommentsThe Write Alter comments to script check box is simply a switch allowing you to either include or suppress alter comments.
  
  Script File Options(i) Output to single file:If you prefer to have all of your statements—CREATE, ALTER, and so on—contained in a single file, then select the Output to single file option.
  
  (ii) Output to separate files:
  If you would like all of your CREATE TABLE statements to be written to one SQL script file and your CREATE INDEX statements to another, then select the Output to separate files option.
  
  File Overwrite OptionsThe File Overwrite Options will indicate whether you want to automatically overwrite or be prompted before script files get overwritten.  Your personal preference determines which technique you employ.
  
  (i) Always Overwrite:
  If you don't mind that your previous build scripts get overwritten, then select Always overwrite.
  
  (ii) Prompt for each overwrite:
  If you are more comfortable being alerted when a script is about to be overwritten, then select Prompt for each overwrite.
  
  The following example shows the type of prompt that appears to alert you of an impending script file overwrite.
  
  
  
  Script File Names
  
  The options available to select in the Script File Names group box depends on your selection in the Script File Options group.
  Output to Single File
  
  When you have Output to single file selected in the Script File Options group, just one edit box will appear in the Script File Names group box:  All Output File Name. The default name for the generated script will be PSBUILD.SQL
  
  Output to Separate Files
  
  If you have Output to separate files selected in the Script File Options group, the following four edit boxes appear—one for each build option—in the Script File Names group box.
  
  Create Table File Name.  The default name for this script is PSTABLE.SQL.
  Create Index File Name. The default name for this script is PSINDEX.SQL.
  Create View File Name. The default name for this script is PSVIEW.SQL.
  Alter Table File Name. The default name for this script is PSALTER.SQL.

The options most likely to change each time you run a build process appear on the Build dialog, and the options that are most likely to remain the same from run-to-run are defined in the Build Settings dialog.  Since most of the settings you choose, due to your preferences, will remain similar from one build to the next, all of the options defined on the Build Settings dialog are retained between sessions, including the Build Execute Options selections from the Build dialog.  The attributes that PeopleTools does not retain are the Build Options selections from the Build dialog.


After execution of Build Script:
The Create Table process creates a new application table based on parameters defined in the record definition.  When a new table is created, the DBMS updates the System Catalog tables to reflect the attributes of the new table.

After you build an updated SQL build script and execute it, the PeopleTools tables and the System Catalog tables are synchronized; the record definition and the Application Data table are synchronized.

Tables:
The Build process generates the appropriate SQL Create statements to build tables based on both your record definition and your platform.  It prefaces each new application SQL table with a PS_ to identify it as an application built using PeopleTools.
For example: PS_ABSENCE_HIST

Inxex:
Build also creates the appropriate indexes.  And, because Long Character fields operate more efficiently in some database environments when they're located at the end of the table, Build automatically positions Long Character fields at the end of the table for each database environment.

View:
When you create views to use online, you must keep the views synchronized with the database.  Like SQL tables, you must have a record definition for online views because the system looks to the record definitions for online processing rules.  And you must build the view before you can use or reference it online.

If you use a view as the basis for a panel, you select existing information to display on the panel.  So, when you create the record definition for your view, you can clone an existing record definition, delete any fields not used in the view, and proceed to define the view.  Query views are processed the same way as view text, which is defined as part of the record.