Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Database Developers Forum – APEX 20.05.2014 PowerPoint Presentation
Download Presentation
Database Developers Forum – APEX 20.05.2014

Database Developers Forum – APEX 20.05.2014

112 Views Download Presentation
Download Presentation

Database Developers Forum – APEX 20.05.2014

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Antonio Romero Marin, Aurelien Fernandes, Jose Rolland Lopez De Coca, Nikolay Tsvetkov, Zereyakob Makonnen, Zory Zaharieva BE-CO APEX Experience from the Accelerators Controls and Operations Data Managementin the Beams Department Database Developers Forum – APEX 20.05.2014

  2. Contents • Introduction to the Controls environment and some of the services using APEX • Interesting features for developers • APEX_COMMON Framework • Plugins & custom components, look and feel features, etc. • Best Practices • Conclusion Z.Zaharieva 22.04.2013 Controls Configuration Service - LS1 Plans EDMS doc: 1281984 2

  3. Introduction to the Accelerators Data Management • Complex and challenging area • Federation of various databases, e.g. • Accelerators Controls Configuration • Diagnostics and Monitoring (DIAMON) • Alarms (LASER) • Accelerators Entities and Signals Naming • Logging and Measurements, etc. • Strategy • Relational DB technology – Oracle DBs for all technical data • Data-driven applications and APIs - Oracle technology stack • Reliable database services and user interfaces (24/7/365) • On-line usage of database services for the accelerators Control and Operation

  4. APEX Development throughout the years • Starting with HTMLDB • beginning of 2005, HTMLDB v.1.6 • Need for rapid application development (Agile programming) • fast prototyping and short time to production deployment • quickly respond to new user requirements • Ready to use templates, widgets and page components • professional look-and-feel with minimum effort on design – concentrate on the application itself • The nature of the interfaces to be implemented • web-interfaces, database-centric • APEX supports client-side scripting for more dynamic / interactive / feature-rich interfaces

  5. First Trials of APEX • Data browsing interfaces and portal for the Accelerators Entities and Signals Naming Database • The Naming DB – dictionary for the accelerators complexes • Equipment types • Equipment positions in the accelerators • Settings and observables (signals) • Read-only interfaces to the data • LHC Equipment catalogue • SPS equipment catalogue • PS equipment and layout components catalogue • Signals Simple Extraction Interface • Signals Experts Extraction Interface • Naming Domain Portal

  6. Accelerators Naming DB Interfaces • Data-browsing Interfaces (window-on-data) – development from scratch • low level of complexity • no transactions • some business logic to get the data out • user authentication and simple user authorization • easy extraction of data into XML/CSV files

  7. Portal Pages and News feeds • Simple portal pages • Access to other related interfaces, documentation, help pages • Dynamically constructed using data stored in tables • News, RSS feeds, News Archive, etc.

  8. History Log Browsers • History logging in our dbs • All data modifications are recorded (to know who did what and when) • History Log Browser objectives • Access the history log for any table in the database for which history is enabled • Provide simple search capabilities for the history logs • Provide a summary for the changes of a given record based on the PK • Completely dynamic configuration • List of tables, list of columns per table, primary keys for tables (USER_ tables) • Dynamic PL/SQL embedded in APEX regions • Portable solution • Deployed to several of the databases, which are using the same mechanism for keeping history without any changes to the interface components

  9. History Log Browsers

  10. Controls Configuration Service • The heart of the Controls System – the basis for the Configuration Management of all Controls System components in the entire accelerator complex • Provides data for all configuration items and their relationships, required for the correct functioning of the Controls System • All controls devices (>105,000) and parameters (> 2,000,000) • Hardware and software configuration of all Front-End Computers (>3,500), Drivers generation • The Accelerators Timing System, Operators Consoles, etc. • Service with 30 years of history • Supporting the requirements of the entire accelerator complex – LHC, SPS and PS complex

  11. Controls Configuration Data Browser • An existing PL/SQL OWA application with ~ 100 reports • existing PL/SQL code base in the CCDB • a complete re-engineering of the application was undertaken in 2006 • possibility to reuse some of the PL/SQL code • ‘thick’ database layer – PL/SQL packages in the database • The first big scale data browsing application to use APEX • Nowadays >220 reports used by a user community of >300 people - thanks to • the ease of APEX reports development • Introduction of APEX user interactive reports

  12. Alarms (LASER) Database • LASER (Alarms) - capturing, storing and notification of anomalies for the whole accelerator chain and technical infrastructure • Alarms DB - 3 different database areas • Pre-defined alarm definitions • User configurations for the alarms displays • Time-stamped run-time alarms events • First APEX data editing interfaces - 2010 • fast development • evolution of APEX • suite of Data Management Tools (7 Editors) • developed from scratch • give users the possibility to explore their data and maintain it

  13. Controls Configuration Data Editors • Data-manipulation interfaces • complexity of the applications due to transaction management • complex business logic to handle modifications of the data • stringent data access rights – e.g. specific authorization modules, virtual private db, etc. • Existing web-deployed, rich clients, using Oracle ADF • Renovation of the existing interfaces and providing new ones with APEX since beginning of 2011

  14. Controls Configuration Data Editors • The first relatively big scale and complex editing applications to be developed / migrated to APEX • So far - 15 Data Editors (> 100 forms), ~ 250 user community • 4 editors remaining to be renovated

  15. Internal developers applications • Due to the ease of development, the developers are using APEX to create applications for managing some of the developers specific processes • Admin Editor for Service Interventions • DB Schema Change Management Editor • APEX _COMMON Metadata Editor

  16. APEX in the Controls Environment nowadays • Specific reporting tools • used for CCS, Laser, Diamon, Accelerators Naming, Measurements • > 300 reports • Generic reporting modules and applications for managing the developers tasks • History Browser; Schema Change Management; Admin Interventions Config, etc. • Portals and news pages with RSS feeds • Data editors • used for CCS, Laser, Diamon, Layout, R2E, SM18 Data Analysis • > 25 editors (> 200 editing forms)

  17. Using APEX for the accelerators data management • Flexible environment for development • Allows you to do a lot of things • It is up to the developers to provide a structured approach to application design • Complete integration with our databases (Oracle, PL/SQL) • Provides a lot of features and components ‘out of the box’ • Fast and easy development • We can do even better • Custom libraries and components • Best practices, guidelines and coding standards • Speed and Scalability

  18. APEX_COMMON framework • APEX built-in tabular forms • APEX manual tabular forms • APEX_COMMON framework • Examples and demo • Evolution of the framework Z.Zaharieva 22.04.2013 Controls Configuration Service - LS1 Plans EDMS doc: 1281984 18

  19. Tabular forms – what is it? • Grid to edit multiple rows or records at once

  20. Built-in tabular forms in APEX • Very easy creation with APEX wizard • Provide built-in functionality for insert/update/delete • Other built-in features (validations, partial refresh, CSV export…)

  21. Limitations of the APEX built-in tabular forms • Really easy to create, but there are important limitations • Just ONE tabular form per page • No Parent-Child tabular forms • Conditional rendering is not possible • Does not keep changes across page submits

  22. APEX manual tabular forms • APEX report using the APEX_ITEM package • Usually using also APEX collections • temporary storage of data within a user session • Require more work than normal tabular forms but overcomes the built-in tabular form limitations • It also adds new features and possibilities • e.g. use of interactive reports

  23. Manual tabular forms – workflow processing • Steps to process a manual tabular form - using collections • Create and load data into collection from the table • Create query using APEX_ITEM package • Update the collection • Commit changes in the DB Client-side Report on Collection 2 3 Server-side APEX Collection Page Processing Page Rendering 1 4 Tables

  24. APEX manual tabular forms – creation process • You have to define many components for each collection • APEX global processes, JavaScript functions, PLSQL procedures, etc. … • Error prone • Collection columns names like c002, page item names like f01 • If you want to use AJAX it adds more complexity • Difficult to maintain the page and to follow its evolution, specially when there are multiple developers

  25. Our environment • Large number of applications and pages • Need of rapid development • Many parent-child tabular forms in the same application page • Custom logic that APEX standard components do not cover • e.g. conditional item rendering • So we created APEX_COMMON to make those tasks easier

  26. What is APEX_COMMON? • A framework to ease the development of manual tabular forms • Creation • Manipulation • Maintenance • Modify behaviour of multiple pages in a systematic and fast way • Reusability of components • Set of generic components (JavaScript libraries, PL\SQL packages, APEX processes, etc.) • Generates code for the manual tabular form • Use standard APEX libraries

  27. APEX_COMMON Architecture Overview APEX Application Database User Schema APEX_COMMON JavaScript API APEX_COMMON metadata • AJAX OnDemandprocesses and POST submits APEX_COMMON PL/SQL API

  28. Architecture and Components • DB side • Metadata in tables for the manual tabular form definitions • APEX_COMMON PL/SQL package • create collections • manipulate collections • commit changes in DB target tables • Global APEX Application processes • Interfacing with the db

  29. Architecture and Components • Client side • Global APEX dynamic actions (functions) • Global JavaScript library • Genericfunctions to interactwiththe data (upload data from page to collections, duplicaterows in tabular form…). • Othergenericfeatures (highlightrows) APEX Application APEX_COMMON JavaScript API

  30. APEX COMMON CCDB Tool • Tool to support APEX_COMMON • Declare tabular forms definitions • Automatic code generation

  31. Examples and DEMO

  32. Controls Configuration Hardware Editor • Front-End Computer configuration form (Crates and Modules) • multiple child forms

  33. Controls Configuration Hardware Editor • Hardware Type Properties • custom logic

  34. Controls Devices Editor • Virtual Classes form • 3 level parent-child forms

  35. Demo Create amanual tabular form 1. Without APEX_COMMONS 2. With APEX_COMMON

  36. Evolution of the APEX_COMMON • During the last 9 months since the creation of the APEX COMMON • Optimizing performance when using collections and large data sets • save only the changes in the collections • Management of APEX collection • 18 => 48 columns per manual tabular form • Shared code in Page 0 • Highlighting, Check all, Duplicate, Add Row…

  37. Features under development – part1 • Editable Interactive Report • All common dynamic actions in Page 0 (e.g. load collection, update collection)=> Less code in the pages

  38. Features under development – part2 • Summarize user actions before commit • More auto generation => Fill information from database dictionary • Capture page exit event and warning user about pending modification • Optimization of server calls=> JSON manipulation in PL/SQL and JavaScript

  39. APEX_COMMON Summary • Multiple Tabular Form per page • APEX 5.0 will NOT have support for multiple tabular forms • APEX 5.1 will introduce a new region type that allows it • It is in the statement of direction but… will it really be there? • Low cost of code evolution and maintenance(code standardization) • Easy to introduce custom logic in the pages • It is a portable solution – you can use it • APEX_COMMON Wiki tutorial • https://wikis/display/config/Create+manual+tabular+form+with+APEX_COMMON+package

  40. Interesting Features for APEX Developers • Cascading LOVs in tabular form • Plugins • Advanced manual tabular form with dynamic number of columns • Bulk data loading features Z.Zaharieva 22.04.2013 Controls Configuration Service - LS1 Plans EDMS doc: 1281984 40

  41. Custom made components for APEX • What do we gain from them? • make the application more user friendly • prevent the users of making mistakes • Why is it interesting to present them today? • they are not built-in APEX components • could be useful for other APEX developers

  42. Cascading LOVs in tabular form • What does it mean cascading List Of Values? • the selection of the second LOV depends on the first one

  43. Cascading LOVs in tabular form • How does it look in our data editors?

  44. Cascading LOVs in tabular form • How do we do it ? • JavaScript • jQuery • JSON • OnDemand PL/SQL process • Find the Wiki page on the following link: https://wikis.cern.ch/display/config/Create+a+cascading+LOV+Parent+columns+in+a+Tabular+Form

  45. Plugins – usage and observations about them • The most interesting and often used ones • Custom pop-up plugins for selection • Save before exit plugin • Advantages of the custom pop-up plugins • possibility of quick search in multiple columns • shows structured data based on a custom SQL query • possibility of multiple columns update • doesn’t open new browser window • Find “Custom Pop-up plugin for selection” Wiki pagehttps://wikis.cern.ch/pages/viewpage.action?pageId=70681028

  46. Custom pop-up plugin for data selection APEX provided one

  47. Custom selection pop-up

  48. Save before exit plugin • Transactional model in our data editors • auto-commit is not an option • the user decides when to commit or rollback the changes • Why do we need to check for changes? • prevent losing data modifications • ignore changes in the search region • Find “Save Before Exit” Wiki page https://wikis.cern.ch/display/config/Prompt+user+when+leaving+or+closing+a+page+without+saving+the+form

  49. Save before exit plugin

  50. Save before exit