1 / 73

Database Developers Forum – APEX 20.05.2014

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 Management in the Beams Department. Database Developers Forum – APEX 20.05.2014. Contents.

sauda
Download Presentation

Database Developers Forum – APEX 20.05.2014

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related