Child pages
  • Defining a Data Model

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0

...

Servoy will call a named sequence in the database to populate the value. The column will be populated and available prior to inserting the record

...

Database Identity

...

The sequential values are managed and populated by the database. The column is not populated until after the record is inserted

UUID Generation

Servoy will automatically populate a text column with a textual representation of a UUID. Be sure that the column's UUID Flag is also enabled.

Calculations

A Calculation is very much like a database column, except that its value, rather than being stored, is dynamically computed each time it is requested. This is achieved by creating a JavaScript function which is bound to a database table. The function takes no arguments and returns a value, which like a real database column, is declared to be one of the five general data types. The scope of the JavaScript function is an individual record object. Therefore any of the record's other data providers, and related foundsets are immediately available, as well as global variables and globally related foundsets.

A calculation is declared at the solution level, and is available throughout the solution in which it is declared, as well as any modules containing it. To support this, there is one JavaScript file per table, per solution, which holds calculation functions. For example, the database tables 'orders' may have a corresponding file 'orders_calculations.js' in any solution. And this file could contain many individual calculation functions.

...

Other than programming scope, the only difference between the two is that globals can be used as keys in relations.

Relations

Relations, at design-time model associations between two tables by joining on key data providers. At runtime, a relation becomes a context-sensitive programming reference to related data. Thus, relations are simple, but powerful mechanisms to display, edit and search for data from any context. They can be used, not only to model simple database relations, but also to create sophisticated filters and searches.

...

Note

Text-Based Expressions

Expressions which contain the SQL Like or SQL NOT Like operators should be used in conjunction with values that contain wildcards (%).

Code Block
customers.city like New% 	// Starts with: i.e. New York, New Orleans
customers.city like %Villa% 	// Contains: i.e. Villa Nova, La Villa Linda
customers.city like %s      	// Ends with: i.e. Athens, Los Angeles
Join Type

A relation can specify one of two SQL Join Types. A SQL join used when a find or a sort is performed using related criteria and thus, the join type will affect behavior in these situations.

Inner Join

SQL Inner Join does not return any rows for parent records which have no related records. Therefore, if a sort or a find is performed when a related data provider is used for criterion, the related foundset may have records omitted due parents with no child records.

Left Outer Join

SQL Left Outer Join will return always return a row for the parent record even if there are no related records. Therefore, if a sort or a find is performed when a related data provider is used for a criterion, the related foundset will include all matching records, regardless of the presence of related records.

Example: Assume that the user chooses to sort a customer list containing 50 records. The sort is based on the account manager's last name, which is in the employees table. However, 3 of the customers don't have an employee listed to manage the account.

Code Block
foundset.sort('customers_to_employees.last_name asc');
foundset.getSize(); //	returns 50 if the customers_to_employees relation specifies left outer join, 47 if the relation specifies inner join.

...

By simply changing the value of the global variables (either programmatically or through the GUI), the related foundset for a customer's orders is updated instantly.

Global Relation

Global relations are simply relations that use only global variables for source data providers. The key difference between global relations and regular table relations is that the related foundset exists in a global context, having no source record as a context. The obvious benefit is that the globally related foundset will be available ubiquitously, instead of being limited to the context of records based on a source table.

Example: Assume that a customer service rep should have a dashboard of all of their orders that are due today. This view could be accomplished using a global relation on the orders table, which could be used anywhere in the application, such as a form in tabpanel to show a dashboard view.

Source

Operator

Destination

globals.currentUserID

=

sales_rep_id

globals.today

=

orderdate

Self Relation

Relations may have the same source and destination table. This is called a Self Relation and has a variety of applications, such as showing data which is hierarchical in nature, or simply showing other records in the same table, which have similar attributes.

Example: When looking at a particular order record, the user may like to see a portal containing a list of all of the other orders made by the same customer as the current order. This could be expressed using a Self Relation, orders_to_orders_by_customer, containing two relation items. The first specifies the same customer, the next ensures that the current order is omitted from the foundset.

Source

Operator

Destination

customerid

=

customerid

orderid

!=

orderid

...