Freitag, 11. Dezember 2020

Schema & Data Browser - Part 4

A new Beta Release Version 1.9.10 of the Schema & Data Browser APEX application has been released on https://github.com/dstrack/Schema_Data_Browser


Many of the concepts of the data browser that were described in the previous blogpost are now implemented and can be demonstrated.

Data Browser Improvements:

  • Improved query generation for import/export views and for history, counters, links, totals, and folder path LOVs.
  • Better joins descriptions for Import / Export forms in the Actions / Columns modal dialog.
  • Improved navigation in the Actions / Show Query modal dialog. The view mode can be chosen in a tab-pane.
  • Improved trigger generation for updatable views. Added a key lookup for composite keys with a file path.
  • Faster installation and refresh of materialized views.
  • Improved page load performance for home page and data browser.
  • Improved progress bar display for scheduler jobs.
  • Improved query generation for LOVs. Up to 4 levels of recursion are now resolved to build hierarchical and path descriptions.
  • Manage Schema Tasks: The processes to add primary and natural keys have been improved. New buttons enable you to generate and download updatable views and UI-defaults.
  • Improved refresh of report regions.
  • Improved Dialog for Sorting and Grouping
  • Early validation of entered data against the schema constraints and size limits via AJAX calls is now enabled.
  • On the home page, the list of My Databases is now displayed when the application is using APEX authentication. This list informed you about the accessible schemas and their space usage and it enables you to switch the active schema by clicking on an entry. Multiple schemas have to be assigned to the APEX workspace and the supporting objects of the application have to be installed in those schemas to make them accessible for this application.

Sources:

  • The PL/SQL packages weco_mail, weco_auth_mgr, and table app_preferences have been removed.
  • The Pre-Installation-Validations for the privileges to execute dbms_lock, dbms_tcp, and dbms_smtp have been removed.
  • In the custom authorization scheme, the package apex_mail is used instead of weco_mail.
  • All of the PL/SQL and javascript source files that are installed by the application are listed in the repository sources directory of the git repository.
  • Removed references to apex_ajax and instead use apex.server.process and plugin apex-plugin-clob-load.
  • the demo time period is now 2 months.

Setting up a new Schema
This application enables you and other users with an APEX developer account in your workspace to add new schemas at runtime. I believe that it is a got practice to create at least one new schema name for each independent application. When you have watched the video from the fabulous Mr. Connor McDonald 'From Doors Wide Open ... to By Invitation Only!' you may come to the same conclusion.
Besides the advantages of modularization and separation of concerns, you can get rid of prefixes for your table names and can use namespaces instead. For example, the table name oehr_Employees becomes oehr.Employees.
The following video demonstrates how to add a new (database) schema named 'HR Data'. the user has to enter a password for the database user, that can be used in SQL-Developer and a second password for the current user for the schema admin account (that is stored as a hash value in the table app_users in that schema.


The video demonstrated the Installation of a sample dataset and setting of the table prefixes to improve the displayed names. When that is done we click on the entry 'Hr Data' in the 'My Databases' list to activate the database as current. A background job will be started that refreshes the table and columns system statistics and builds a fast access cache from the data dictionary system views in a set of materialized views. 


The next video is about Manage Schema Task: Define recursive natural keys to produce detailed record descriptions and define mandatory key columns to improve the key lookup in the triggers of updateable views. Then see an example updatable view Voehr_Employees_Imp with a trigger to handle the DML operations. 


 CREATE OR REPLACE VIEW VOEHR_EMPLOYEES_IMP  
   ( LINK_ID$, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_JOB_ID, SALARY, COMMISSION_PCT, MANAGER_EMAIL, DEPARTMENT_DEPARTMENT_NAME, DEPARTMENT_LOC_STREET_ADDRESS, DEPARTMENT_LOCATI_POSTAL_CODE, DEPARTMENT_LOCATION_CITY, DEPARTMENT_LOC_STATE_PROVINCE, DEPARTMENT_LOCAT_COUNTRY_NAME, DEPARTMENT_LOCATI_REGION_NAME  
   , CONSTRAINT VOEHR_EMPLOYEES_IMP_PK PRIMARY KEY (LINK_ID$) RELY DISABLE )   
  AS   
 SELECT    
   A.EMPLOYEE_ID LINK_ID$,  
   A.FIRST_NAME,  
   A.LAST_NAME,  
   A.EMAIL,  
   A.PHONE_NUMBER,  
   A.HIRE_DATE,  
   B.JOB_ID JOB_JOB_ID,  
   A.SALARY,  
   A.COMMISSION_PCT,  
   C.EMAIL MANAGER_EMAIL,  
   D.DEPARTMENT_NAME DEPARTMENT_DEPARTMENT_NAME,  
   D_C.STREET_ADDRESS DEPARTMENT_LOC_STREET_ADDRESS,  
   D_C.POSTAL_CODE DEPARTMENT_LOCATI_POSTAL_CODE,  
   D_C.CITY DEPARTMENT_LOCATION_CITY,  
   D_C.STATE_PROVINCE DEPARTMENT_LOC_STATE_PROVINCE,  
   D_C_B.COUNTRY_NAME DEPARTMENT_LOCAT_COUNTRY_NAME,  
   D_C_B_B.REGION_NAME DEPARTMENT_LOCATI_REGION_NAME  
 FROM OEHR_EMPLOYEES A  
 JOIN OEHR_JOBS B ON B.JOB_ID = A.JOB_ID  
 LEFT OUTER JOIN OEHR_EMPLOYEES C ON C.EMPLOYEE_ID = A.MANAGER_ID  
 LEFT OUTER JOIN OEHR_DEPARTMENTS D ON D.DEPARTMENT_ID = A.DEPARTMENT_ID  
 LEFT OUTER JOIN OEHR_LOCATIONS D_C ON D_C.LOCATION_ID = D.LOCATION_ID  
 LEFT OUTER JOIN OEHR_COUNTRIES D_C_B ON D_C_B.COUNTRY_ID = D_C.COUNTRY_ID  
 LEFT OUTER JOIN OEHR_REGIONS D_C_B_B ON D_C_B_B.REGION_ID = D_C_B.REGION_ID;  
   
 CREATE OR REPLACE TRIGGER VOEHR_EMPLOYEES_IMP_TR INSTEAD OF INSERT OR UPDATE OR DELETE ON VOEHR_EMPLOYEES_IMP FOR EACH ROW   
 DECLARE   
   v_row OEHR_EMPLOYEES%ROWTYPE;  
   v_DEPARTMENT_LOCATION_ID OEHR_LOCATIONS.LOCATION_ID%TYPE;  
   v_DEPARTMENT_LOCATION_REGION_ID OEHR_REGIONS.REGION_ID%TYPE;  
   v_DEPARTMENT_LOCATIO_COUNTRY_ID OEHR_COUNTRIES.COUNTRY_ID%TYPE;  
 BEGIN  
   if DELETING then   
     DELETE FROM OEHR_EMPLOYEES A   
     WHERE A.EMPLOYEE_ID = :new.LINK_ID$;  
     return;  
   end if;  
   v_row.EMPLOYEE_ID           := :new.LINK_ID$;  
   v_row.FIRST_NAME            := :new.FIRST_NAME;  
   v_row.LAST_NAME            := :new.LAST_NAME;  
   v_row.EMAIL              := :new.EMAIL;  
   v_row.PHONE_NUMBER           := :new.PHONE_NUMBER;  
   v_row.HIRE_DATE            := :new.HIRE_DATE;  
   v_row.SALARY              := :new.SALARY;  
   v_row.COMMISSION_PCT          := :new.COMMISSION_PCT;  
   if :new.DEPARTMENT_LOCATI_REGION_NAME IS NOT NULL then   
    begin  
     SELECT D_C_B_B.REGION_ID INTO v_DEPARTMENT_LOCATION_REGION_ID  
     FROM OEHR_REGIONS D_C_B_B   
     WHERE D_C_B_B.REGION_NAME = :new.DEPARTMENT_LOCATI_REGION_NAME;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_REGIONS(REGION_NAME)  
     VALUES (:new.DEPARTMENT_LOCATI_REGION_NAME)  
     RETURNING (REGION_ID) INTO v_DEPARTMENT_LOCATION_REGION_ID;  
    end;  
   end if;  
   if :new.MANAGER_EMAIL IS NOT NULL then   
     SELECT C.EMPLOYEE_ID INTO v_row.MANAGER_ID  
     FROM OEHR_EMPLOYEES C   
     WHERE C.EMAIL = :new.MANAGER_EMAIL;  
   end if;  
   if :new.JOB_JOB_ID IS NOT NULL then   
     SELECT B.JOB_ID INTO v_row.JOB_ID  
     FROM OEHR_JOBS B   
     WHERE B.JOB_ID = :new.JOB_JOB_ID;  
   end if;  
   if :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NOT NULL  
   or v_DEPARTMENT_LOCATION_REGION_ID IS NOT NULL then   
     SELECT D_C_B.COUNTRY_ID INTO v_DEPARTMENT_LOCATIO_COUNTRY_ID  
     FROM OEHR_COUNTRIES D_C_B   
     WHERE (D_C_B.COUNTRY_NAME = :new.DEPARTMENT_LOCAT_COUNTRY_NAME OR D_C_B.COUNTRY_NAME IS NULL AND :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NULL)  
     AND (D_C_B.REGION_ID = v_DEPARTMENT_LOCATION_REGION_ID OR D_C_B.REGION_ID IS NULL AND v_DEPARTMENT_LOCATION_REGION_ID IS NULL);  
   end if;  
   if :new.DEPARTMENT_LOC_STREET_ADDRESS IS NOT NULL  
   or :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NOT NULL  
   or :new.DEPARTMENT_LOCATION_CITY IS NOT NULL  
   or :new.DEPARTMENT_LOC_STATE_PROVINCE IS NOT NULL  
   or v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NOT NULL then   
    begin  
     SELECT D_C.LOCATION_ID INTO v_DEPARTMENT_LOCATION_ID  
     FROM OEHR_LOCATIONS D_C   
     WHERE (D_C.STREET_ADDRESS = :new.DEPARTMENT_LOC_STREET_ADDRESS OR D_C.STREET_ADDRESS IS NULL AND :new.DEPARTMENT_LOC_STREET_ADDRESS IS NULL)  
     AND (D_C.POSTAL_CODE = :new.DEPARTMENT_LOCATI_POSTAL_CODE OR D_C.POSTAL_CODE IS NULL AND :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NULL)  
     AND (D_C.CITY = :new.DEPARTMENT_LOCATION_CITY OR :new.DEPARTMENT_LOCATION_CITY IS NULL)  
     AND (D_C.STATE_PROVINCE = :new.DEPARTMENT_LOC_STATE_PROVINCE OR D_C.STATE_PROVINCE IS NULL AND :new.DEPARTMENT_LOC_STATE_PROVINCE IS NULL)  
     AND (D_C.COUNTRY_ID = v_DEPARTMENT_LOCATIO_COUNTRY_ID OR D_C.COUNTRY_ID IS NULL AND v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_LOCATIONS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)  
     VALUES (:new.DEPARTMENT_LOC_STREET_ADDRESS, :new.DEPARTMENT_LOCATI_POSTAL_CODE, :new.DEPARTMENT_LOCATION_CITY, :new.DEPARTMENT_LOC_STATE_PROVINCE, v_DEPARTMENT_LOCATIO_COUNTRY_ID)  
     RETURNING (LOCATION_ID) INTO v_DEPARTMENT_LOCATION_ID;  
    end;  
   end if;  
   if :new.DEPARTMENT_DEPARTMENT_NAME IS NOT NULL  
   or v_DEPARTMENT_LOCATION_ID IS NOT NULL then   
    begin  
     SELECT D.DEPARTMENT_ID INTO v_row.DEPARTMENT_ID  
     FROM OEHR_DEPARTMENTS D   
     WHERE (D.DEPARTMENT_NAME = :new.DEPARTMENT_DEPARTMENT_NAME OR :new.DEPARTMENT_DEPARTMENT_NAME IS NULL)  
     AND (D.LOCATION_ID = v_DEPARTMENT_LOCATION_ID OR D.LOCATION_ID IS NULL AND v_DEPARTMENT_LOCATION_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_DEPARTMENTS(DEPARTMENT_NAME, LOCATION_ID)  
     VALUES (:new.DEPARTMENT_DEPARTMENT_NAME, v_DEPARTMENT_LOCATION_ID)  
     RETURNING (DEPARTMENT_ID) INTO v_row.DEPARTMENT_ID;  
    end;  
   end if;  
   
   if INSERTING then   
     INSERT INTO OEHR_EMPLOYEES VALUES v_row;  
   else   
     UPDATE OEHR_EMPLOYEES SET   
       FIRST_NAME = v_row.FIRST_NAME,  
       LAST_NAME = v_row.LAST_NAME,  
       EMAIL = v_row.EMAIL,  
       PHONE_NUMBER = v_row.PHONE_NUMBER,  
       HIRE_DATE = v_row.HIRE_DATE,  
       JOB_ID = v_row.JOB_ID,  
       SALARY = v_row.SALARY,  
       COMMISSION_PCT = v_row.COMMISSION_PCT,  
       MANAGER_ID = v_row.MANAGER_ID,  
       DEPARTMENT_ID = v_row.DEPARTMENT_ID  
     WHERE EMPLOYEE_ID = :new.LINK_ID$;  
   end if;  
 END VOEHR_EMPLOYEES_IMP_TR;  
 /

When the columns of the natural keys are defined as mandatory (NOT NULL), then the lookup operations require fewer OR conditions to handle NULL cases.
   

CREATE OR REPLACE VIEW VOEHR_EMPLOYEES_IMP  
   ( LINK_ID$, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_JOB_ID, SALARY, COMMISSION_PCT, MANAGER_EMAIL, DEPARTMENT_DEPARTMENT_NAME, DEPARTMENT_LOC_STREET_ADDRESS, DEPARTMENT_LOCATI_POSTAL_CODE, DEPARTMENT_LOCATION_CITY, DEPARTMENT_LOC_STATE_PROVINCE, DEPARTMENT_LOCAT_COUNTRY_NAME, DEPARTMENT_LOCATI_REGION_NAME  
   , CONSTRAINT VOEHR_EMPLOYEES_IMP_PK PRIMARY KEY (LINK_ID$) RELY DISABLE )   
  AS   
 SELECT    
   A.EMPLOYEE_ID LINK_ID$,  
   A.FIRST_NAME,  
   A.LAST_NAME,  
   A.EMAIL,  
   A.PHONE_NUMBER,  
   A.HIRE_DATE,  
   B.JOB_ID JOB_JOB_ID,  
   A.SALARY,  
   A.COMMISSION_PCT,  
   C.EMAIL MANAGER_EMAIL,  
   D.DEPARTMENT_NAME DEPARTMENT_DEPARTMENT_NAME,  
   D_C.STREET_ADDRESS DEPARTMENT_LOC_STREET_ADDRESS,  
   D_C.POSTAL_CODE DEPARTMENT_LOCATI_POSTAL_CODE,  
   D_C.CITY DEPARTMENT_LOCATION_CITY,  
   D_C.STATE_PROVINCE DEPARTMENT_LOC_STATE_PROVINCE,  
   D_C_B.COUNTRY_NAME DEPARTMENT_LOCAT_COUNTRY_NAME,  
   D_C_B_B.REGION_NAME DEPARTMENT_LOCATI_REGION_NAME  
 FROM OEHR_EMPLOYEES A  
 JOIN OEHR_JOBS B ON B.JOB_ID = A.JOB_ID  
 LEFT OUTER JOIN OEHR_EMPLOYEES C ON C.EMPLOYEE_ID = A.MANAGER_ID  
 LEFT OUTER JOIN OEHR_DEPARTMENTS D ON D.DEPARTMENT_ID = A.DEPARTMENT_ID  
 LEFT OUTER JOIN OEHR_LOCATIONS D_C ON D_C.LOCATION_ID = D.LOCATION_ID  
 LEFT OUTER JOIN OEHR_COUNTRIES D_C_B ON D_C_B.COUNTRY_ID = D_C.COUNTRY_ID  
 LEFT OUTER JOIN OEHR_REGIONS D_C_B_B ON D_C_B_B.REGION_ID = D_C_B.REGION_ID;  
   
 CREATE OR REPLACE TRIGGER VOEHR_EMPLOYEES_IMP_TR INSTEAD OF INSERT OR UPDATE OR DELETE ON VOEHR_EMPLOYEES_IMP FOR EACH ROW   
 DECLARE   
   v_row OEHR_EMPLOYEES%ROWTYPE;  
   v_DEPARTMENT_LOCATION_ID OEHR_LOCATIONS.LOCATION_ID%TYPE;  
   v_DEPARTMENT_LOCATION_REGION_ID OEHR_REGIONS.REGION_ID%TYPE;  
   v_DEPARTMENT_LOCATIO_COUNTRY_ID OEHR_COUNTRIES.COUNTRY_ID%TYPE;  
 BEGIN  
   if DELETING then   
     DELETE FROM OEHR_EMPLOYEES A   
     WHERE A.EMPLOYEE_ID = :new.LINK_ID$;  
     return;  
   end if;  
   v_row.EMPLOYEE_ID           := :new.LINK_ID$;  
   v_row.FIRST_NAME            := :new.FIRST_NAME;  
   v_row.LAST_NAME            := :new.LAST_NAME;  
   v_row.EMAIL              := :new.EMAIL;  
   v_row.PHONE_NUMBER           := :new.PHONE_NUMBER;  
   v_row.HIRE_DATE            := :new.HIRE_DATE;  
   v_row.SALARY              := :new.SALARY;  
   v_row.COMMISSION_PCT          := :new.COMMISSION_PCT;  
   if :new.DEPARTMENT_LOCATI_REGION_NAME IS NOT NULL then   
    begin  
     SELECT D_C_B_B.REGION_ID INTO v_DEPARTMENT_LOCATION_REGION_ID  
     FROM OEHR_REGIONS D_C_B_B   
     WHERE D_C_B_B.REGION_NAME = :new.DEPARTMENT_LOCATI_REGION_NAME;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_REGIONS(REGION_NAME)  
     VALUES (:new.DEPARTMENT_LOCATI_REGION_NAME)  
     RETURNING (REGION_ID) INTO v_DEPARTMENT_LOCATION_REGION_ID;  
    end;  
   end if;  
   if :new.MANAGER_EMAIL IS NOT NULL then   
     SELECT C.EMPLOYEE_ID INTO v_row.MANAGER_ID  
     FROM OEHR_EMPLOYEES C   
     WHERE C.EMAIL = :new.MANAGER_EMAIL;  
   end if;  
   if :new.JOB_JOB_ID IS NOT NULL then   
     SELECT B.JOB_ID INTO v_row.JOB_ID  
     FROM OEHR_JOBS B   
     WHERE B.JOB_ID = :new.JOB_JOB_ID;  
   end if;  
   if :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NOT NULL  
   and v_DEPARTMENT_LOCATION_REGION_ID IS NOT NULL then   
     SELECT D_C_B.COUNTRY_ID INTO v_DEPARTMENT_LOCATIO_COUNTRY_ID  
     FROM OEHR_COUNTRIES D_C_B   
     WHERE D_C_B.COUNTRY_NAME = :new.DEPARTMENT_LOCAT_COUNTRY_NAME  
     AND D_C_B.REGION_ID = v_DEPARTMENT_LOCATION_REGION_ID;  
   end if;  
   if :new.DEPARTMENT_LOC_STREET_ADDRESS IS NOT NULL  
   or :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NOT NULL  
   or :new.DEPARTMENT_LOCATION_CITY IS NOT NULL  
   or :new.DEPARTMENT_LOC_STATE_PROVINCE IS NOT NULL  
   or v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NOT NULL then   
    begin  
     SELECT D_C.LOCATION_ID INTO v_DEPARTMENT_LOCATION_ID  
     FROM OEHR_LOCATIONS D_C   
     WHERE (D_C.STREET_ADDRESS = :new.DEPARTMENT_LOC_STREET_ADDRESS OR :new.DEPARTMENT_LOC_STREET_ADDRESS IS NULL)  
     AND (D_C.POSTAL_CODE = :new.DEPARTMENT_LOCATI_POSTAL_CODE OR D_C.POSTAL_CODE IS NULL AND :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NULL)  
     AND (D_C.CITY = :new.DEPARTMENT_LOCATION_CITY OR :new.DEPARTMENT_LOCATION_CITY IS NULL)  
     AND (D_C.STATE_PROVINCE = :new.DEPARTMENT_LOC_STATE_PROVINCE OR D_C.STATE_PROVINCE IS NULL AND :new.DEPARTMENT_LOC_STATE_PROVINCE IS NULL)  
     AND (D_C.COUNTRY_ID = v_DEPARTMENT_LOCATIO_COUNTRY_ID OR v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_LOCATIONS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)  
     VALUES (:new.DEPARTMENT_LOC_STREET_ADDRESS, :new.DEPARTMENT_LOCATI_POSTAL_CODE, :new.DEPARTMENT_LOCATION_CITY, :new.DEPARTMENT_LOC_STATE_PROVINCE, v_DEPARTMENT_LOCATIO_COUNTRY_ID)  
     RETURNING (LOCATION_ID) INTO v_DEPARTMENT_LOCATION_ID;  
    end;  
   end if;  
   if :new.DEPARTMENT_DEPARTMENT_NAME IS NOT NULL  
   and v_DEPARTMENT_LOCATION_ID IS NOT NULL then   
    begin  
     SELECT D.DEPARTMENT_ID INTO v_row.DEPARTMENT_ID  
     FROM OEHR_DEPARTMENTS D   
     WHERE D.DEPARTMENT_NAME = :new.DEPARTMENT_DEPARTMENT_NAME  
     AND D.LOCATION_ID = v_DEPARTMENT_LOCATION_ID;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_DEPARTMENTS(DEPARTMENT_NAME, LOCATION_ID)  
     VALUES (:new.DEPARTMENT_DEPARTMENT_NAME, v_DEPARTMENT_LOCATION_ID)  
     RETURNING (DEPARTMENT_ID) INTO v_row.DEPARTMENT_ID;  
    end;  
   end if;  
   
   if INSERTING then   
     INSERT INTO OEHR_EMPLOYEES VALUES v_row;  
   else   
     UPDATE OEHR_EMPLOYEES SET   
       FIRST_NAME = v_row.FIRST_NAME,  
       LAST_NAME = v_row.LAST_NAME,  
       EMAIL = v_row.EMAIL,  
       PHONE_NUMBER = v_row.PHONE_NUMBER,  
       HIRE_DATE = v_row.HIRE_DATE,  
       JOB_ID = v_row.JOB_ID,  
       SALARY = v_row.SALARY,  
       COMMISSION_PCT = v_row.COMMISSION_PCT,  
       MANAGER_ID = v_row.MANAGER_ID,  
       DEPARTMENT_ID = v_row.DEPARTMENT_ID  
     WHERE EMPLOYEE_ID = :new.LINK_ID$;  
   end if;  
 END VOEHR_EMPLOYEES_IMP_TR;  
 /  

In the case that you want to use this kind of named views in your application, you can press the button 'Generate Updatable Views' to install a set of views with the name V<Table_Name>_IMP as a logical projection layer of your tables. In the next step, you can click the 'Export UI Defaults' button to export detailed properties for the GUI field for new forms on those tables or views. You then have to import the downloaded file hr_data_uidefaults.sql in your workspace. The Demo App EMPS demonstrates how the view Voehr_Employees_Imp can be used in a Facetted Search, edited in an Interactive Grid, Interactive Report, and Data Entry Form. When you use this view in a data loading process, you can not only load data into the table Oehr_Employees, but also load new entries into Oehr_Regions, Oehr_Locations, and Oehr_Departments. Not a single line of SQL code had to be written in this demo app.

Add Calculations: Add a calculated column Netto_Price and define summands to produce totals (Order Items - ... Netto Price, and Employee - ... Salary) in grouped reports of referenced tables.


Add Audit columns to tables with a name that starts with OEHR. The columns Created_At, Created_By,  Last_Modified_At, and Last_Modified_By are added to the tables. The values are managed by default on null function calls in the column definition and with BEFORE UPDATE triggers.

The DDL statements that were performed to alter your schema are stored in the table app_protocol and can be exported with this app.

I hope you find some inspiration and a use case with this app. I welcome contributions and other feedback on github.com like stars and issues. I have got interested in implementing a practical kind of updatable views that were explained by E.F.Codd in the book 'The Relational Model for Database Management Version 2', Page 299, Chapter 17.3 View-updatability Algorithms VU-1 and VU-2. 
When a database application has fast access to the data dictionary constraint definitions, it can provide much more automatic functionality than just expanding a table name to a report or form on the raw data structures.

Regards
Dirk Strack

Keine Kommentare:

Kommentar veröffentlichen

Hinweis: Nur ein Mitglied dieses Blogs kann Kommentare posten.