Service Company Tutorial, Chapter 2: Setting Up Your Environment and Creating Your Application

Setting Up Your Environment and Creating Your Application

This chapter of the tutorial describes installation and preparation steps that you need to complete before starting to build the application. You can access the previous chapter of the tutorial through the following link;

You can install the application in your local Formspider installation following the installation guide below:
SRDEMO Installation Guide

It contains the following topics:

  • Downloading the tutorial setup files
    • Description of tutorial schema setup scripts
    • Definition of database objects which will be created
  • Creating SRDEMO schema
  • Downloading and installing Formspider
  • Creating and defining an application for the tutorial
    • Starting Formspider IDE
    • Defining application level font attributes
    • Setting session timeout for the application
  • Creating necessary Formspider API synonyms and grants

Setting Up Your Database

Make sure that DBMS_CRYPTO package is already installed in your database. To check it, connect to your database with your sys user and run the following query;

select *
from user_objects
where object_name = 'DBMS_CRYPTO'

If the DBMS_CRYPTO package is not installed, please connect to your database as sysdba and install this package using the script named dbmsobtk.sql located under $ORACLE_HOME/rdbms/admin directory.

Setting Up Your Environment

In this section, you prepare your working environment to support the tutorial. You examine and install the Service Request schema which owns the data displayed in the application. You perform the following key tasks:

  1. Download the tutorial schema setup.
  2. Create the SRDEMO schema and populate data.
  3. Download and install Formspider.

Downloading the Tutorial Setup Files

To download the setup files for this tutorial, perform the following steps:

  1. Download Formspider_srdemo_tutorial.zip and extract the zip file to a directory on your computer.
  2. Locate to the \dbSchemaScripts subdirectory. This folder contains files used to create the user and schema, populate schema tables data and also drop the schema. Detailed information about these scripts is as the following:
Downloading the Tutorial Setup Files
  • 01_createSchema.sql: This script contains all commands necessary to create the database user named “SRDEMO” and grant necessary privileges to this user.
  • 02_createSchemaObjects.sql:This script contains all necessary commands to create schema tables and constraints described in Data Model section of the previous chapter. In addition, this script creates four sequences which are used while populating data and implementing a data concurrency control mechanism. These sequences are as follows:

    USERS_SEQ: Populates the ID for new users
    PRODUCTS_SEQ: Populates the ID for each product
    SERVICE_REQUESTS_SEQ: Populates the ID for new service request(SR)
    SERVICE_REQUESTS_VERSION_SEQ: Populates a unique version number for each SR. Each time an existing SR is updated, this version number will also be updated. This version number is used to provide concurrency control mechanism on SRs.

    Note: In the following chapters you will discover how to implement concurrency control mechanism in Formspider in great details.

  • 03_createSequenceTriggers.sql: This script contains necessary commands to create before insert triggers on the SERVICE_REQUESTS, PRODUCTS and USERS tables. The main goal of these triggers is filling the ID columns with valid values in case of they are not properly populated already.
  • 04_populateSchemaTables.sql: This script contains necessary commands to populate sample data in USERS, SERVICE_REQUESTS, SERVICE_HISTORIES, PRODUCTS and EXPERTISE_AREAS tables. It also prepares the security mechanism by inserting necessary data in SEC_ROLES, SEC_RESOURCES, SEC_ROLERESOURCES and SEC_PROPERTIES tables.
  • 05_createPackagesViews.sql: This script creates all packages, views and triggers used by the application. Detailed information about these objects is as following:

    Packages

    SRDEMO: Contains all procedures used in Formspider actions, meaning all procedures which are triggered as a result of user interactions with application interface. Therefore this is your main package holding business logic and managing the application flow.
    SRDEMO_SEC: Contains necessary validation functions and procedures adjusting user interfaces for providing role based access mechanism, meaning the application security.
    SRDEMO_MULTILINGUAL: Contains constant definitions and utility functions used with the purpose of implementing Formspider multilingual support mechanism.
    SRDEMO_UTILITY: Contains generic utility functions used by other objects.
    SRDEMO_CONSTANTS: Contains constant definitions for static values used and referenced by the application (session variable names, constants used in table data and so on). The goal of this package is increasing code maintainability.
    SRDEMO_OBJ_CONSTANTS: Contains constant definitions for Formspider object (datasource, panel, component, cell, alert, dialog) name values referenced in other packages. The goal of this package is increasing code maintainability.

    EXPERTISE_AREAS_API: Contains necessary DML procedures for inserting, updating and deleting records from EXPERTISE_AREAS table.
    SERVICE_HISTORIES_API: Contains necessary DML procedures for inserting, updating and deleting records from SERVICE_HISTORIES table.
    SERVICE_REQUESTS_API: Contains necessary DML procedures for inserting, updating and deleting records from SERVICE_REQUESTS table.

    SERVICE_HISTORIES_QRY: Contains getter and validation functions querying SERVICE_HISTORIES table.
    SERVICE_REQUESTS_QRY: Contains getter and validation functions querying SERVICE_REQUESTS table.
    PRODUCTS_QRY: Contains getter and validation functions querying PRODUCTS table.
    USERS_QRY: Contains getter and validation functions querying USERS table.

    Views & Triggers

    In Formspider views can be used as a source of DML operations as well as database tables. While table based DML operations are simpler to implement, you may sometimes have complex requirements and may need complex queries for displaying data which will be the source of DML operations. In such cases you will use views and triggers.

    EXPERTISEAREAS_V: This view is used for listing expertise areas of service technicians and performing DML operations through following triggers:
    EXPERTISEAREAS_V_IOI: Instead of insert trigger handling new expertise areas additions to a service technician.
    EXPERTISEAREAS_V_IOU: Instead of update trigger handling edition of an existing expertise areas of a technician..
    EXPERTISEAREAS_V_IOD: Instead of delete trigger handling deletion of an existing expertise area from a techician.

    SERVICEHISTORIES_V: This view is used for listing service histories and performing DML operations through following triggers:
    SERVICEHISTORIES_V_IOI: Instead of insert trigger handling new service history creations.
    SERVICEHISTORIES_V_IOD: Instead of delete trigger handling deletion of an existing service history.

    SERVICEREQUESTS_V: This view is used for listing service requests and performing DML operations through following triggers:
    SERVICEREQUESTS_V_IOI: Instead of insert trigger handling new SR creations.
    SERVICEREQUESTS_V_IOU: Instead of update trigger handling edition of an existing SR.
    SERVICEREQUESTS_V_IOD: Instead of delete trigger handling deletion of an existing SR.

  • clearSchemaTables.sql: This script contains necessary commands to delete data contained in all tables.
  • dropSchemaObjects.sql: This script contains necessary commands to drop all database objects created and used by this tutorial.
  • dropSchema.sql: This script contains necessary commands to drop SRDEMO schema from your database.

Creating the SRDEMO Schema and Populating Data

The SRDEMO user owns the data displayed in the application. Access to an Oracle SYS user or equivalent is required to create the user account and assign the appropriate privileges. To create the schema owner and schema, perform the following steps:

  1. Open your favorite PL/SQL editor tool and log on as SYS or another DBA level user. (You may need to ask your DBA to run this script for you.) Execute the script named 01_createSchema.sql to create the SRDEMO schema owner.
  2. Log on to SRDEMO schema. Execute first the script named 02_createSchemaObjects.sql and then the script named 03_createSequenceTriggers.sql to create all the tables, constraints, and database sequences.
  3. Eexecute the script named 04_populateSchemaTables.sql to insert sample data into tables.
  4. Finally, execute the script 05_createPackagesViews.sql to create all packages, views and triggers.

You may anytime execute clearSchemaTables.sql to clear all data available in the tables. In addition to this, you can drop all database objects created for this tutorial by executing dropSchemaObjects.sql and even drop the schema itself by executing dropSchema.sql.

Downloading and Installing Formspider

If you are not already installed Formspider you can download the installer from the following link:
theformspider.com/download.php

You can find detailed information about how to use the installer and install Formspider from:
theformspider.com/gettingstarted.php

Defining an Application for the Tutorial

In Formspider the application is the highest level in the control structure. All other Formspider objects and components are contained by an application and can be only used by the application where they are defined.

In this part of the tutorial you will discover how to create a new application, how to define application level font attributes and a session timeout value for your newly created application using Fomspider IDE.

Starting Formspider IDE

You can open the Formspider IDE by clicking the desktop shortcut created by the Formspider installer. Alternatively, you can start it by using your web browser. The URL is going to be the application server URL + context root name provided during installation. For example if your application server URL is http://127.0.0.1:8080 and your context root name is formspider, enter following URL in your browser: http://127.0.0.1:8080//formspider/

The following web page will appear after you visit the URL.

Starting Formspider IDE

Clicking the “IDE” link, provided in this page, will lead you to the Formspider IDE where the login screen will show up. The default user “admin” is created with every Formspider installation. Type admin as “User” and enter the “Password” that you have defined for admin user during installation process.

Formspider IDE Login

After logon to Formspider IDE click the “New Application” menu item under “File” menu in the menu bar to create a new application. The “New Application” dialog will show up.

Creating a new application.

In the “New Application” dialog, enter SRDEMO as “Name”, this is the unique identifier value of your application.
Then, enter SRDEMO as “Datasource Schema” value. The “Datasource Schema” is the name of the database schema holding your business code and database objects which will be used by your application.
Leave “CSS Filename” field empty for using the default CSS. You can access to the default CSS by clicking “Default.css” link next to the “CSS Filename” field. You may use your custom CSS in a Formspider application.

Notice that at the bottom of this dialog there are four tabs titled as “Font”, “Events”, “Browser Settings” and “Session”.

“Font” is the tab where you can change and define application level font attributes.When not specified, the default font attributes of a Formspider application are as follows:

Attribute Value
Font Color Black
Font Family Arial
Font Size 11

In SRDEMO application you will use Tahoma 13 as your default font. For this purpose enter Tahoma as “Font Family” and 13 as “Font Size”.

Defining application level font attributes

“Events” is the tab where you can assign Formspider actions to various application level events (Post Open, Close ..etc.). Notice that since the application creation process is not completed yet the comboboxes existing on this screen are temporarily disabled, preventing you from using these events.

“Browser Settings” is the tab where you can define a message which will be displayed on a confirm box when the application is about unloading (when browser’s Refresh, Back or Next button is pressed or when browser’s close icon is clicked). This confirm box contains two options; staying in the current application or leaving it, giving a last chance to user for staying in the application. When a message is not defined, a confirm box will not be displayed.

“Session” is the tab where you can define a specific session timeout period for your application, in seconds. If any user request does not occur within the timeout period, the session will be killed. Enter 1800 as “Session Timeout” value.

Defining session timeout period

Click “OK”, notice that Formspider IDE creates the SRDEMO application and then opens it for editing.

Creating Formspider API Synonyms and Grants

The last step before starting building your application is creating necessary synonyms for Formspider API’s in your datasource (SRDEMO) schema and granting execution privileges on these API packages. By performing these two steps you will be able to use Formspider API’s in your datasource schema.

Creating API Synonyms

Click the “API Synonyms and Grants” menu item under “Tools” menu in the menu bar, the “API Synonyms and Grants” dialog will show up. Notice that “Synonym Scripts” tab is selected.

Opening the “API Synonyms and Grants” dialog

“Schema” combobox lists names of the all schemas which are available in your database. Choose SRDEMO and press “Create Synonyms” button. Notice that necessary synonyms for Formspider API’s are created in your SRDEMO schema.

Creating necessary Formspider API's synonyms

Grants

Select the tab titled “Grant Scripts”, this tab contains necessary script for granting execution privileges on Formspider API’s to SRDEMO user. Copy this script and execute it after logged on to your database as SYS or another DBA level user.

Grant scripts for Formspider API

You are now ready to start building your application.

Summary

In this chapter you completed remaining steps before starting to build your application by setting up your development environment and creating your application. You performed the following key tasks:

  • Downloaded the tutorial setup scripts
  • Created SRDEMO schema
  • Downloaded and installed Formspider
  • Created a Formspider application
  • Created necessary Formspider API synonyms and grants
  • Dusan Djuric

    You’re welcome guys

  • Dusan Djuric

    srdemo package bodu line 1177
    when API_EXCEPTION.E_CONCURRENCYVIOLATION then — concurrency validation occured “e_concurrencyVialotion” MISTYPE in 05_createPackagesViews.sql!

    • http://www.gerger.co Yalim K. Gerger

      Hi Dusan,
      Thank you for the feedback. We’ll fix the issue ASAP.

    • İbrahim Sandallı

      Hi Dusan,

      We updated the script you mentioned, thank you for your feedback again.