Schema & Data Browser – Part 2

Schema & Data Browser – Part 2

A new release of the Schema & Data Browser app has been published on GitHub.com 

https://github.com/dstrack/Schema_Data_Browser 

here is are links to the demo with an apex account
https://yboieokpjit8ejk-strackdev02.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=2000:101
https://apex.oracle.com/pls/apex/f?p=48950:101

A large number of visitors to the springy diagram demo and the peasant feedback and constructive critics encouraged me to put more effort into this project.

`Cyberspace. A consensual hallucination experienced daily by billions of legitimate operators, in every nation, by children being taught mathematical concepts... A graphic representation of data abstracted from the banks of every computer in the human system. Unthinkable complexity. Lines of light ranged in the nonspace of the mind, clusters, and constellations of data. Like city lights, receding...' –William Gibson; Neuromancer (Chapter 3); 1984

After two decades of collecting experiences in financial and process controlling apps in the Oracle domain, I like to break out of the box. I want to produce some art that visualizes what's in my mind. I like the idea behind the springy.js diagram renderer because, with just 3 natural forces (Stiffness, Repulsion, Gravitation), the automatic layout of complex networks can be calculated with pleasant results.

Gravitation – pulls all objects to the center and is the cause that the silhouette of the diagram is an oval shape.

In a social context – let's stay together and build a group.

Stiffness – defines the force that pulls two nodes together when they are connected via an edge.

You could also, name that force attraction or empathy.

Repulsion – defines the force that is repelling other nodes nearby, like the electric negative charge.

You could also, name that force Respect – don't step on me.

Directed Graphs

• are used to model all kinds of relationships in social graphs.
• Dependencies in database object constructs.
• Foreign key relationships and dimensions in business models.
• Object enclosure / encapsulation via parent/child relationships.
• Module separation – recognize independent groups of interconnected objects.
• Signal paths of events on a web page – what happens, when you click that button.
• Cascading or Domino effects – what will break, if you change that object.

The diagram renderer

• Besides the diagrams a control panel for all diagram parameters. The adjustments are performed with immediate visual feedback.
• Zoom: lets you fluently zoom the canvas viewport in a factor range from 1 to 12 X.
• Font size: lets you adjust the size of the nodes relative to the canvas and edges. 
• Initial font size is calculated for new diagrams based on the square root of the count of nodes.
• You can adjust the font size, to either put the focus on the nodes or on the edges. With a very small font size, the automatic layout is performed at the highest frame rate.
• min. Energy: defines a threshold level where the program stops the calculation of updates for node positions.
• Set 'min. Energy' to 0 or greater, when you want to stop the processing-intensive calculations.
• max. Speed: defines the maximal speed of the moving nodes during automatic layout.
• Stiffness: defines the force that pulls two nodes together when they are connected via an edge – Rubber band effect.
• Repulsion: defines the force that is repelling other nodes nearby.
• Damping: defines a factor that is reducing the applied forces, to slow down the movements.
• The performance of the algorithms to calculate the forces are improved by a factor of 10 so that you can now render diagram of up to 2000 nodes and edges fluently.
• In order to avoid shaking nodes when the stiffness is cranked up, a counterweight algorithm has been added. The algorithm automatically adjusts the weight of the node depending on the degree of connections.

The diagram editor

• On the page 'Springy Diagram' you can alter individual node properties (Shape, Text, Color, Active) with immediate visual feedback.
• Node colors can be chosen from a popup dialog window of HTML color names.
• There are now buttons to control the zoom factor and font size when no mouse wheel is available.
• There is now an auto-focus button to give you an optimal view of a selected node.
• Resizing of the diagram canvas and multiple canvases are now supported. 
• The legends of the database diagrams are now active springy diagrams.
• Many graphical and processing errors have been fixed.
• Support for touchpad: select node, double tab, move node, move the canvas, zoom canvas, zoom font size
• A smart selection method enables you, to logically select related nodes via their directed graph's edges using one of the methods: 

• Selected highlights just the clicked node and its edges.
• Downstream highlights all nodes that a connected via edges in arrow direction; useful for following a signal in an APEX Dynamic Actions diagram and to select child nodes in a dependencies diagram.
• Upstream highlights all nodes that a connected via edges in the reverse arrow direction; useful to select parent nodes in a dependencies diagram.
• Connected highlights all nodes that a connected via edges in both arrows directions; useful to select independent modules in the pool of objects in a database schema. You can crank up the stiffness parameter to make the module separation more obvious. 

• Pin Button – a flip state button

• Active: sets a heavy weight in touched nodes, with the effect that the nodes stick where they are. (weight=10000)

• Inactive: sets a light weight in touched nodes, with the effect that the nodes float to their optimal position. (weight=10)

• The Actions Menu has now function to
• Save as:, for schema diagrams this action enables you the save the current diagram as a named springy diagram for later use.
• Exempt Selected: hide all nodes that are not selected. (active=N)
• Hide Selected: hide all nodes that are selected. (active=N)
• Show All: sets all nodes to visible (active=Y)
• Freeze / Lock All: set a heavy weight in all nodes, with the effect that the nodes stick where they are. (weight=10000)
• Melt / Unlock All: sets a light weight in all nodes, with the effect that the nodes float to their optimal position. (weight=10)
• Edit Diagram properties: lets you edit the numerical diagram parameter and lets you delete a diagram.

Data Browser – Import View

used to import Database Object Dependencies and other diagrams as CSV files.

• the software package includes the file Object_Dependencies_Export.sql Install the views on an oracle instance via the SQL developer application and then execute the following statements:
• select * from APP_OBJECT_DIAGRAM_EDGES_V;
• select * from APP_OBJECT_DIAGRAM_NODES_V;
• Save the output of the query results as two CSV files on your desktop.
• Navigate in this app to the data browser (page 30),
• Click on the table name 'Diagram Nodes' or 'Diagram Edges' below the table 'Springy Diagrams',
• Click on the Import View tab in the report-modes navigation bar and then begin the import by clicking on the Import button.

• The import processing has been improved. When the importer displays data validation errors, You can now rearrange the columns in the proper order. That will trigger a re-validation and you can continue the import of nodes and edges then no validation errors when found.
• A Navigation bar with view-modes simplify the Navigation to the Springy Diagram – editor page.

Entity Relations Diagram

The kind of foreign key relation is distinguished  by edge colors and labels.

• The Actions Menu has now function to
• Edit Table Data (link to the Data Browser)
• Alter Schema Object (Add a table, column, reference; Alter Rules & Constraints, Comments)
• Edit Schema Object (in SQL Workshop)
• A double click on an object node opens the data browser for that table.

Object Dependencies Diagram

• The object-types-checklist contains separate checkmarks for Key Constraint, Ref Constraint, Check Constraint, Not Null Constraint
• A single click or touch on an object node updates the object info side panel and popup LOV fields for the current object type and name.
• A link to display the SQL Text of the selected object is displayed on the right side panel.
• A click on that link will open a popup dialog to display the SQL text. (Be patient, because that can take a while).
• when you want to find an individual node by type and name, you can use the LOV fields to enter a search term and choose a name from the list.
• After choosing a name, the diagram is auto-focused on the selected node with that name.
• A double click on an object node opens the SQL Workshop page with that object when you have access privileges the APEX workspace.
• The legend is now a springy diagram with interactive functionality for zooming the canvas or font size.

Dynamic Actions Diagram

 The diagram renders nodes for all

• Dynamic actions: with nodes for event source, action name, true and false branches, code steps, request name, affected items, regions, buttons.
• Menus: (shown on the diagrams on pages 0 and 1),
• Lists: with nodes for request names and Link target page.
• Processing points: with nodes for request name, page process names, page branch names and target page #no.
• Buttons: with nodes for request names and links to the target page.
• Classic Report links
• Interactive Report links
• Interactive Grids links

Usage

• A single click or touch on an object node updates the DA info side panel and a popup LOV field for the current DA name.
• For selected links or branches, a link to load the diagram of the target page is displayed on the right-side panel.
• A click on that link will save the current diagram and then open the diagram of the link target page.
• To find an individual dynamic action by name, you can use the LOV field to choose a name from the list.
• After choosing a name, the diagram is auto-focused on the selected node with that name.
• A double click on a dynamic action node opens the APEX page designer with that object, when you have access privileges the APEX workspace.
• The legend is now a springy diagram with interactive functionality for zooming the canvas or font size.
• When the downstream selection method is active, nodes for buttons, regions stop the propagation of the signal, because they are passive objects.

Data Browser

• improved page load performance
• improved rendering of nested reports with pagination.
• improved formatting and processing for numbers and boolean fields.
• new functions for import views: copy to the collection, paste from the collection

Home Page

• new functions to: Add a Schema, Remove Schema, Duplicate Schema, Upgrade supporting Objects

Manage Schema

• new wizards to: Add Serial Primary Keys, Add Natural Keys, Set Mandatory Key Columns

Print pages as HTML

• A new top menu icon allows you to produce a printable version of the current page than can produce the whole report without empty pages and store the output as a PDF document.

This app is for Oracle APEX developers, a collection of tools to accelerate your work.
I request you to install the trial version in your oracle cloud instance or
your local oracle virtual box to explore features that can't be shown online.

- the methods to add, duplicate, remove, and switch schemas at runtime.
- the methods to add tables and columns, alter columns and constraints, drop tables at runtime.
- the wizards to improve your unique key definitions.
- the option to update the APEX 'UI defaults' with Heading, LOVs, number & date formatting, field length, max length, item help.
- the usage of the schema diagrams to find objects and code modules and then edit them with a double click in APEX.
- the Show Query Dialog with tons of options and then copy useful code for form rendering, change detection, early validation, key lookup, saving in DB, and lost update detection.
- see the source of page 30 of the APEX app with 94 dynamic actions as an extreme example.
- see the self-test module that automatically tests the data browser.
- see what well-prepared relations can do for you!