A070 – Physical data model

What you will learn ?

Using the graphical diagram view to see physical data model.

STEP 1: Create a new diagram

  • Connect with designer account
  • Click the + icon on Diagrams gadget to the right of the screen
  • Select the template ModelPhysicalObject
  • Give a unique name for your diagram
  • Click on Create, an empty model is generated

Exercise:

  • create a model MPDClient to represent the physical model of Sales application

STEP 2: Create a new diagram

Use contextual menus (via right click) to access the actions of the modeller :

  • The working area: actions to insert objects, align them, save the template, add notes or containers, etc.
  • An object: actions to access the object or add contents, change its color, etc.
  • A link actions to access the object of the relationship, change the properties of graphics connector

Exercise:

  • Right-click the blank area
  • Click on insert object
  • Select all the objects starting with App in the list
  • Change the tables position on the diagram
  • Via the action bar, zoom in and save the template
  • Print the template on one or more pages

STEP 3: Using diagram to navigate

The diagram view is interacting with the central working area, and can be opened in its own window:

  • Double-clicking on a table to open the associated business object
  • Double-clicking on a column to open the definition of the associated field
  • An update is automatically updated in the open model
    If an update is performed by another user in another session, it is necessary to use the action Refresh to update the model

Exercise:

  • Open objects, links and attributes from the diagram view

STEP 4: Indexes

To assist the designer, the indexes are generated.

Indexes are determined by:

  • All “functional key” fields of the object (unique index)
  • All foreign keys (single index)
  • All fields with a possible search
  • Some timestamps

In a general way:

  • Any business object contains a technical unique key : the “row_id”.
  • This row_id is used in the joins between tables. When the foreign key is optional, Simplicité platform generates a full outer join, if the join is strictly an inner join

A business object whose property Use of a timestamp is checked will have 4 additional physical columns:
a) created_dt: creation date of the data
b) created_by: login of the user who created the data
c) updated_dt: last updated date
d) updated_by: login of the user who last modified the data

Exercise:

  • View the indexes on the app_client table
  • Change indexes through the Database Access action on Operation / Home_ page (Others page).

STEP 5: SQL requests

The platform allows a DBA to trace and therefore optimise database :

  • Consult the SQL requests generated in the logs by activating probes
  • Easily access to the database from a page
  • To list the 10 longest queries since server startup

To activate probes:

  • Move the “LOG_SQL_USER” system parameter to “yes” and clear all caches
  • Access times will be longer and the logs are large, so you have to activate this probe only in design / test.
  • Server logs will contain all user queries
  • Move “LOG_SQL_USER” parameter to “no” and clear the cache to stop the tracing.

Exercise:

  • Enable the probe LOG_SQL_USER / clear cache
  • Reconnect and open a customer form
  • Consult the server logs
  • Disable the probe and clear cache

 

STEP 6: Simple access to database

Simplicité platform has a very simple page to access the database. The objective is to troubleshoot or allow a simple access :

  • Open the home page of the Operation menu
  • Click the Database Access button from the Others tab
  • Enter your SQL query
  • Run and see the result of the selection or updating

Exercise:

  • Change indexes on your object using this function.
STEP 7: N-N Relationship

STEP 7: Top 10 longest queries

  • Open the Operation / Monitoring menu
  • Go to the Data tab
    The list shows the times of the 10 longest queries, those it is recommended to optimise if possible (analysis and creation Index by a DBA).
    This list remains informative and is not intended to replace the tools DBMS vendors.
    Sometimes very simple queries are slow simply because the server is busy CPU, which has nothing to do with the application itself.
    Simplicité platform can only measure elapsed time of each request between submission and the response of the DBMS.

A060 – Create a state model

How to create a state model on a business object using a wizard:

  • Create state transitions
  • Give access to state transitions to group of rights
  • Translate transition actions

A080 – Create a constraint on an object

How to add a constraint on a business object. A constraint is a business rule (If “condition” then “impacts”) on an object based on its contextual data and user rights.