SynApp2 Setup Guide

SynApp2 Setup Guide – A Human Resources Application for Oracle® Database

Review this guide for a detailed discussion of the steps involved with preparing your Oracle Database environment with the elements needed to exercise the tutorial yourself. The setup should take less than 5 minutes.

Briefly, the significant steps of the setup include:

· Downloading the tutorial archive

· Extracting the archive files to your web server document area

· Creating a database user

· Using SQL*Plus to run PL/SQL scripts that establish the example tables, indexes, sequences and triggers, and then, populate the database with sample records

Requirements for the tutorial include the following software, properly installed and functioning:

· Oracle Database (Express Edition 10g was used to create the tutorial)

· HTTP web-server software (Apache, IIS, etc.)

· PHP 5 or later

· SynApp2 version 1.8.0 or later (configured to connect with your Oracle Database instance)

· Web browser – any recent version of (Chrome, Firefox, Internet Explorer, Opera, Safari, etc.)

The setup begins with downloading the tutorial kit from SynApp2.org.

http://www.synapp2.org/documentation/synapp2_tutorial_1_hrv2.zip

http://www.synapp2.org/documentation/synapp2_tutorial_1_hrv2.tar.gz

Choose whichever archive format is easiest for you to work with.

Save the downloaded archive file to a convenient location on your computer (or server).

Extracting the tutorial kit archive and copying the files to the web server document tree

The following sequence reflects a configuration where the web server software is running on a Linux machine, and where an Apache HTTP Server, and Per-user web directory is established. Directory and file sharing is enabled with Samba.

It is impossible to provide precise step-by-step details for your exact situation. Hopefully you’ll find these illustrations sufficient to accomplish this part of the setup for your particular configuration.

Do whatever you need to do to, to extract the archive files. Copy or move them into your web server document tree where you have SynApp2 installed. You must also have sufficient privileges to create directories and files.

Figure 1 - Extracting the archive files to the SynApp2 applications directory

From Windows, a Compressed (zipped) Folder can be opened and the files copied with the drag and drop.

Figure 2 - the tutorial files are in place

Creating a new database user

Once we have extracted files from the HRV2 example kit, we’ll login to Oracle Database 10g Express Edition (XE) to establish the database user.

Figure 3 - Logging in to Oracle Database as the SYSTEM user

Figure 4 - the HRV2 database user is established

Note that the next 2 figures are provided for illustration and are not required to complete the tutorial setup. Logging out and then back in to Oracle Database, using the newly created HRV2 user, serves only to create a backdrop for subsequent steps.

Once logged in as HRV2, navigate to the Object Browser.

Figure 5 - A view before any tables have been created

Setting up the HRV2 database structure and populating the tables

Your environment is sure to be different than what you see in the following figures. And again, it’s impossible to create step-by-step examples for your exact configuration. The steps and illustrations are intended to convey generally what needs to be done. It’s up to you to interpret them for your specific situation – Windows, Linux, Mac, Apache, IIS, etc.

Once a database user has been created, we’ll use it to establish the tables and populate them with records.

We’ll be working from the command line. Open a command/console window and navigate to the SynApp2 installation/applications directory on your server.

The illustration below shows the "cd" command and a listing of the files and directories that should be in place by now.

Figure 6 - Using the "cd" command to change directories to the SynApp2 applications directory and then listing the contents

Here the command "sqlplus HRV2@localhost" does the trick.

Figure 7 - Logging in with SQL*Plus and entering the command to run the hrv2.ddl.txt script

Once you’re logged in, run the PL/SQL script "hrv2.ddl.txt" to establish all of the tables, indexes, sequences and triggers for the HRV2 database.

Next, run the "hrv2.sql.txt" script to populate the tables with sample data records.

Figure 8 - The database/schema objects have been created and the tables are ready to be populated

Running the PL/SQL script "hrv2.sql.txt" inserts records into all of the tables.

Refreshing the Objects Browser from the Oracle Database instance, shown as the backdrop, confirms the establishment of tables and displays data from the EMPLOYEES table. This is not a required step, as mentioned earlier, but it does reflect expected results.

Figure 9 - The database objects have been created and the EMPLOYEES has 13 rows of data

At this point the tutorial setup is complete.

You can proceed to the tutorial.

SynApp2 Tutorial - A Human Resources Application for Oracle® Database

http://www.synapp2.org/patterns/synapp2_tutorial_1_hrv2.htm

If you’d like to learn about how SynApp2 sees the database/schema and data - continue with the discussion that follows.

A first look at SynApp2

Use your web browser to navigate to the SynApp2 index/welcome page.

Again, you’ll have to interpret the instructions for your particular configuration.

Typically, if you direct your browser to the directory where SynApp2 is installed, the index.html page will automatically redirect to the SynApp2 application welcome page: synapp2/synapp2.htm.

The following illustrates this for a server named "linus" and an Apache UserDir located in the home directory: "~richard"

The address entered in the browser was: "http://linus/~richard/synapp2_oci"

Even though this address won’t work for you, it shows the general form of a link suitable for a browser bookmark.

Once you get to the proper page, you’ll see the Login prompt.

Figure 10 - The Login prompt from the SynApp2 welcome page

Case sensitivity is an issue here, so please login with all UPPERCASE characters.

Figure 11 - The SynApp2 Login page with credentials for HRV2

After a successful login, the SynApp2 application navigation tabs appear.

Navigate to the Tools tab with a single click.

Figure 12 - The SynApp2 Tools page and the Users sub-tab, before an AppID (or database) has been specified

Note: As of SynApp2 version 1.8.0. Beta 4, the Tools/Users sub-tab is not used for the Oracle Database direct connection access model implemented by SynApp2.

Enter a value for the AppID (or database): HRV2

The value is the same as the Oracle Database username. Again, case sensitivity is an issue. Please use all uppercase characters.

Aside: SynApp2 is designed to make good use of the keyboard and many efficient shortcuts are available. The Tab and Enter keys figure prominently into the flow of most data entry forms. Cursor and page navigation keys streamline record manipulation on any Select Form presented. Experiment as you learn. The keyboard is often much more efficient than the mouse.

Enter the value and click the checkmark icon or hit the Enter key, whichever seems most natural to you.

Navigate to the Structure sub-tab.

Figure 14 - The SynApp2 Tools/Structure sub-tab shows information about all of the tables and columns

The complete display of Database Structure for HRV2 appears below:

hrv2.synapp2_tools_database_structure.png

Figure 15 - The full display of the database/schema table structure for HRV2

About the HRV2 database design

The design of the HRV2 (i.e., HR version 2) database/schema varies somewhat from the traditional HR example database that is used for countless Oracle Database demonstrations and tutorials. The self-referencing foreign key EMPLOYEES.MANAGER_ID has been eliminated. This design also avoids the UPDATE_JOB_HISTORY trigger, and the ADD_JOB_HISTORY stored procedure, to indirectly affect the JOB_HISTORY table. Rest assured that stored procedures and SynApp2 get along perfectly well. They’re just being left out for this presentation.

All historically significant changes are applied directly to the JOB_HISTORY table, and include:

· Start Date

· Job

· Department

· Reports To/Manager

· Pay Rate/Salary

· Pay Period/Frequency

Numerous one-to-many and many-to-many table record relationships are modeled by the HRV2 design. The implementation details appear in the table below:

Figure 16 - using SynApp2 KeyMap to declare table relations

Each key declaration takes only two clicks – one to choose the [child/many] Col Name, and another to select the (parent/one) Join Table-Join Col.

Foreign key constraints declared as part of the table structure definition are optional. If they are present, they must be in agreement with the SynApp2 declarations and vice versa.

For those who are wondering why foreign key and other database-level constraints are omitted – it’s because they don’t add value to this tutorial. Therefore, all the related effort it takes to deal with them falls into the category of pure overhead. That means they’re not invited to the party. It’s as simple as that.

But, if you’d like a more detailed discussion about how and why database-level constraints can impact project development – please read on.

For this tutorial, foreign key constraints have been deliberately omitted from the database definition to eliminate order dependency issues that complicate the creation and management of the DDL and SQL scripts used to create and populate the database/schema.

As soon as dependencies are enforced at the database level, scripts –or any other interactions - that alter structure, or introduce data, must do so in accordance with all of the rules. That’s fine for mature designs, but relentlessly enforced constraints add unwelcome hoops to jump through during project development.

Oracle Database XE has a utility to generate DDL. It’s useful, but it doesn’t emit DDL scripts that have any respect for dependency issues. So, in many cases, the scripts will not run unless you carefully re-order them. Who’s got time for that? And, it’s quite easy to define complex, or even circular, constraint dependencies that will utterly prevent tables and/or data from being established in a single pass when all of the bells and whistles are in force.

The more dependencies you have to deal with when you know you should make a change, the less likely you will be to actually make a change. This can be paralyzing, and it creates legacy issues even before your project has gotten off the ground. It happens all the time. Keep an eye on the constraint requirements, but don’t put the rope around your neck until you absolutely have to.

At some point, you can add the database-level constraints along with the scripts and the overhead it takes to maintain everything. But, don’t go and get all religious about constraints before you’re in position to have them pay off. Give yourself a break.

Now you’re cooking with gas

SynApp2 is about keeping things simple, minimizing dependencies and effort, and moving along your development timeline with terrific agility and solid results. Rely on the consistency of automation wherever you can. Protect the simplicity that makes it all possible.

SynApp2 has a learning curve – to be sure. But, don’t add to it by swimming upstream. When you find the groove, you’ll be able to fly. If you’re struggling or finding that things seem awkward; step back and review your design. Reflect on the basic goals related to the business problem you’re trying to solve. How does the information need to flow? What dependencies are imposed by the real data and the existing business processes? If you attack the problem by trying to figure out how to create GUI to do 'this' or 'that', then you’re making work for yourself. Forget about the UI. Focus on the business problem.

Let SynApp2 make the application happen.

Updated: 18-NOV-10

http://www.synapp2.org

SynApp2 – Web Application Generator and MVC Framework