once:radix Documentation

• once:radix
  • Overview
  • Getting started
  • Fundamentals
  • oADMIN
  • oCLI
  • oED
  • Database
  • Scripting
  • Models
  • Directories
  • Technical Notes

once:editor Database Manager

once:radix is based on PostgreSQL – The world's most advanced open source database. There are several excellent texts written about PostgreSQL, so this section will focus only on issues that are specific to the once:radix platform.

The system is distributed with a minimal database, which includes security, system administration and enough contact data to register users. To this basic database you can build a system to be as simple or as complex as you wish.

Before we begin, it is worth noting that you can build quite advanced applications without knowing much about database design, using the power of blocks and subblocks. So a cursory readthrough of the basic concepts introduced here will get you up and running.

The Glossary at the end of this section introduces some basic database terms, as they apply to once:radix:

Database Basics

A single database table is similar in concept to the rows and columns of a spreadsheet. While this can be used for basic forms design, it is limited when building even simple data structures.

Example: To store details for an invoice, one row could hold all of the customer name, address details, invoice number, date, invoice total, etc. But if you want to store the transaction details for each item (e.g. description, quantity, unit cost and total item cost), the spreadsheet needs a set of four extra columns for each line item. This is fine if you know that you will never have more than, say, eight items per invoice. You could add eight sets of extra columns (in this example, a total of 32 extra fields).

There are two obvious disadvantages to this approach: Firstly, you can't have nine (or more) items on an invoice. Secondly, there will be a lot of wasted space. e.g. If the average number of items per invoice is three, that would leave (on average) 20 empty fields per record.

A relational ratabase solves this problem: By creating a second table to hold the transaction lines, ANY number of items can be attached to each invoice. Also, storage is far more efficient; though relational databases do have some small storage overheads: A field is added to each table of the database (the primary key) and in this example, there will also need to be an extra field added to the transaction lines (a foreign key). Despite these extra fields, a relational database is far more compact than a 'flat' data file.

In this example, the header data (primary key, customer's address details, invoice number, date, invoiced total) are stored in the parent table (invoiceheader). The item details (primary key, foreign key, description, quantity, unit cost and total item cost) are stored in the child table (invoiceitem), as one record per item.

The primary key in each record of a table is unique. By storing a copy of the primary key of the parent record in the foreign key of each child record, a link (or relationship) can be created between the parent and all of its child records.

A typical request to the database could be: "SELECT the UNIQUE invoiceheader record whose primary key = 1001 and SELECT ALL invoiceitem records whose foreign key = 1001".

This example would return one parent record (assuming that there is an invoice header record with a primary key of 1001) appended to each of four child records that have their foreign key fields set to 1001. It would look something like this:

There are several ways to link tables in a relational database, once:radix supports four of these: Inner Join, Left Outer Join, Right Outer Join and Full Outer Join. This example would use a left or right outer join.

Left Outer Join

The PostgreSQL statement for this example would be:

SELECT *
FROM "accounts"."invoiceheader" LEFT OUTER JOIN "accounts"."invoiceitem"
ON ("accounts"."invoiceheader"."primary" = "accounts"." accounts"."invoiceitem"."fkinvoice")
WHERE ("invoiceheader"."primary" = '1001')

SELECT * means: 'Return all fields in the tables invoiceheader and invoiceitem of the records that meet the search criteria of the WHERE clause.'

In a Left Outer Join all records in the table on the left side of the join statement that meet the join criteria are returned, regardless of whether there are related records on the right hand side. So, in this case, if an invoice had been created but no transaction items had been entered, the parent records would still be returned.

FROM ... LEFT OUTER JOIN ... ON ... describes the link between the two tables.

Right Outer Join

The right outer join is a mirror image of the left outer join. This same select example would be:

SELECT *
FROM " accounts"."invoiceitem"RIGHT OUTER JOIN " accounts"."invoiceheader"
ON ("accounts"."invoiceitem"."fkinvoice" = "accounts"."invoiceheader"."primary")
WHERE ("invoiceitem"."fkinvoice" = '1001')

SELECT * means: 'Return all fields in the tables invoiceheader and invoiceitem of the records that meet the search criteria of the WHERE clause.'

In a Right Outer Join all records in the table on the right side of the join statement that meet the join criteria are returned, regardless of whether there are related records on the left hand side. So, in this case, if an invoice had been created but no transaction items had been entered, the parent records would still be returned.

FROM ... RIGHT OUTER JOIN ... ON ... describes the link between the two tables.

While the right join performs exactly the same function as the left join, most database textbooks recommend (for consistency and ease of troubleshooting) to always use the Left Outer Join.

Inner Join

Though less commonly used than the Left Outer Join, the Inner Join can be useful where records in one table are linked to one (and only one) record in another table.

For example: To find all records for every person who is a member of an organisation (while excluding people who are not members of an organization) in the database distributed with the once:radix package , there is an inner join between the person and personmember tables. It is done this way because every person is linked to an organization through the personmember table.

SELECT *
FROM "personmember" INNER JOIN"person"
ON ("personmember"."fkperson" = "person"."primary")
WHERE ("personmember "."fkperson" IS NOT NULL)

SELECT * means: 'Return all fields in the tables personmember and person of the records that meet the search criteria of the WHERE clause.'

That is, only those records in the table on the left side of the join that link to one (and only one) record on the right side are returned. So, in this case, only people who are members of an organisation would be returned.

FROM ... INNER JOIN ... ON ... describes the link between the two tables.

Full Outer Join

Most database textbooks recommend that the Full Outer Join should never be used as it usually indicates a poorly designed database. There may be situations where use of this join is justified, however it is beyond the scope of this introductory text.

Conclusion

once:radix supports four join types. Of these, the Left Outer Join is used more than 90% of the time.

The Inner Join is most often used when filtering is needed (e.g. Only return contact records for people who are members of an organization.)

Avoid using the Right Outer Join and Full Outer Join.

In once:radix database relationships are not required between blocks and subblocks. They are only used to link tables or restrict search results within a block.

In the next section Metamodel Relationships are introduced. It is through their use that systems are constructed. Except when creating new Schema (Refer oADMIN for details.) or the creation of Functions and Triggers (an advanced feature of PostgreSQL), no direct SQL is needed.

Getting Started

The Database Manager palette is opened by selecting Database Manager... in the oED Tools palette.

Metamodels and Models

once:radix employs a system of Metamodels and Models to represent tables, fields and relationships.

Database Relationships are stored in Metamodels. Tables are referred to indirectly in these Metamodel Relationships as Models. Fields are referred to as Aliases. However none of these are direct equivalents as the metadata elements perform extra functions as are described below. Refer to the Glossary at the end of this section for a more detailed description.

The example screen (below) shows many features of a typical metamodel relationship, as viewed in the Database Manager. In particular, it shows models, field aliases and joins.

The main area of the palette shows an example of an Entity-Relationship (E-R) diagram of one of the Metamodel Relationships stored in a metamodel file (../once/model/metamodel.met).

As well as the five links joining models shown on screen, there are four links leading to models that are hidden from view.

The Metamodel.Relationship name is shown on the left-hand side of the command line at the bottom of the screen (in this example: contacts.person).

If a metamodel and relationship were selected in the Block Setup palette prior to opening the Database Manager, the relationship is displayed as shown in this example:

Otherwise, the window will be blank as shown below:

To open an existing metamodel and relationship other than the one currently displayed, click Open on the right-hand side of the control strip at the bottom of the screen.

Select a metamodel name from the list. A full list of relationships from the selected metamodel is displayed. Select a relationship from the list then click Open. The relationship is displayed on screen, as shown in the example above.

Models

Each block in the E-R diagram represents a model. The model name is also its file name, stored on the server (../once/model/data/model.mdl).

Below the model name are three sets of square brackets, representing: Allow Inserts, Allow Updates and Allow Deletes.

[+] means: Allow the operation to be performed.
[-] means: Do not allow the operation.

For example: [+] [+] [-] means allow inserts and updates in the table associated with the model, but do not allow records to be deleted.

The remainder of the model is a list of field aliases and their data types, sorted in alphabetical order. Field aliases in a model are similar to their associated database field names but they are also used to automatically assign labels when fields are added to a layout (using the Insert Datasource palette).

For example: The database field 'faxnumber' might have the alias 'Fax_Number' which would be rendered on the screen as the label 'Fax Number' (as each underscore is automatically replaced by a space). Alias data types and their associated PostgreSQL data types include:

Main Model

Models are added to a Metamodel Relationship in the order they are added to the Database Manager screen. If a model is deleted, other models will be renumbered to take its place. However one model has special characteristics:

The Main Model is usually associated with the parent block. It refers to the Primary Table - the first table to be actioned in INSERT, UPDATE and DELETE operations. It is identified in a metamodel file as 'model1' in the relationship. In the Database Manager, it is coloured green.

Any model can be made the Main Model: Click on the model diagram to select it (Models change colour to blue when selected.), then click the Main Model checkbox on the command line at the bottom of the screen. When this is done, its colour changes to green, highlighting its special place in the Relationship.

Creating a Table

All tables are associated with at least one model. Create a table by first clicking Create Model on the control panel at the bottom of the Database Manager screen. This opens the Model Editor palette,.

Have you made a backup?
Before proceeding, make a backup. Refer oADMIN for details. If you make a mistake and cause irreparable damage to your database, don't say you weren't warned!

The Table Editor palette is arranged in sections: Below the table name and schema is a list of all fields defined for the table. Click to create a new field. Click on a field name in the list then click Edit to alter its attributes or Delete to remove it from the table.

Below the list of fields is the Field Details section. Enter details, as appropriate:

BEGIN TRANSACTION;

CREATE TABLE contacts.jobcount ( );

ALTER TABLE contacts.jobcount ADD COLUMN "primary" int4;
ALTER TABLE contacts.jobcount ADD COLUMN "ownerorganisation" text;
ALTER TABLE contacts.jobcount ALTER COLUMN "primary" SET NOT NULL;
ALTER TABLE contacts.jobcount ADD COLUMN "fkjobcounter" int4;
ALTER TABLE contacts.jobcount ALTER COLUMN "fkjobcounter" SET NOT NULL;
ALTER TABLE contacts.jobcount ADD CONSTRAINT "fkjobcounter"
  FOREIGN KEY ("fkjobcounter") REFERENCES mail.header("primary")
  ON UPDATE RESTRICT
  ON DELETE CASCADE;

COMMIT TRANSACTION;

To complete the operation, click OK. The database is updated, ready for the next stage: Creating a model for the table. The Table Editor palette closes, returning to the Model Editor. The next step is to create one or more models to represent the table.

Create Model

Enter a valid model Name, then select the Table name from the dropdown list. The Add and Import buttons become active. Click Import. All fields in the table that are not yet included in the model are added to the list with [+] in front of the field alias.

Click Save to create the entries in the model. The [+] symbols are removed from the field names to indicate completion of the import process.

Edit Aliases

Aliases not only define field names in a model, they also set default Label names when fields are placed on a layout using the Insert Datasource and Change Datasource palettes.

Aliases created for each imported field use the field name. e.g. A field called 'homephone' would import into a model with the Alias also set to 'homephone'.

If the standard label for this field should be 'Home Phone', change the Alias to 'Home_Phone'. (Spaces are not allowed but underscores convert to spaces.)

To edit an alias name, click on the current name in the Model Editor palette, then click Edit. The Alias Editor palette is displayed with the selected field highlighted.

The Type dropdown list defines the Java data type of the field in the model. It must match the data type in the database. Options include: string, integer, float, date, boolean, binary, password and other. A comparative list of the fields is shown below. The data types should not need to be changed as this is set from the table definition.

The Nature section of the Alias Editor palette allows calculations to be performed on the contents of the field, then to return the result of that calculation, rather than the field's contents. To enable this function, set the Is Calculated checkbox then select one of the following selections from the Calculation function dropdown list:

• Average
• Count
• Maximum
• Minimum
• Sum

The Database Info section of the Alias Editor palette displays the name of the table and its fields. The Edit Table button provides access to the table editor.

Take care when making changes to a table. They are not automatically updated in all models associated with that table.

Edit Model

To amend an existing model, highlight the model on the relationship diagram, then click the Edit Model button on the control strip at the bottom of the Database Manager palette. The Model Editor palette opens. Making changes to existing models is similar to creating a new model. Refer to the previous section for details.

Add Model

To add a model to a metamodel relationship, click Add Model. The Open Model palette is displayed.

Select a model name from the list, then click Open. The model is added to the layout.

Delete Model

To remove a model from a metamodel relationship, click on the model diagram to select it. Its colour changes to blue, showing that it is the active model. Click Delete Model on the control strip at the bottom of the Database Manager palette.to remove the model from the E-R diagram.

Kill Model

To remove a model from a metamodel relationship and to delete the model file completely from the server, highlight the model on the relationship diagram, then click the Kill Model button on the control strip at the bottom of the Database Manager palette..

Take care when executing this command. Models are not automatically updated in all associated metamodels.

Links

In once:radix a link is one or more joins between models. Four types of joins that can be created using the Database Manager were introduced in the Database Basics section: inner, left outer, right outer and full outer join.

This section shows how to apply these joins to metamodel relationships.

Crow's Foot Notation

In once:radix these joins are represented using Crow's Foot notation which represents relationships with connecting lines between entities, and pairs of symbols at the ends of those lines to represent the number of records that can/must be returned when a database SELECT operation is performed:

• The double bar appears at the child end of every inner join, regardless of whether it is a primary or foreign key.
  When a SELECT is made, each record in the parent table that satisfies the search criteria will return one (and only one) record from the linked child record.
  For example: A contact record with an inner join to a driver's licence table would only return records for contacts who have a driver's licence.
• The ring with single bar appears at the child end of a left or right outer join when it is connected to a primary field.
  When a SELECT is made, each record in the parent table that satisfies the search criteria may also return one record from the linked child record (Since each primary key is unique, only one child record can be returned. But being an outer join, parent records with no child record are also returned.
• The ring with bar and crow's foot appears at the child end of a left or right outer join when it is connected to a foreign key field.
  When a SELECT is made, each record in the parent table that satisfies the search criteria may also return any number of records from the linked child record (Since there is no limit to the number of records that can be assigned the same value in the foreign key field, any number of child records can be returned. But being an outer join, parent records with no child record are also returned.
  This also appears at BOTH ends of a full outer join. In this case, records that meet the search criteria at both ends of the join are returned, regardless of whether there are linked records at the other end.

Link Editor

Links are lines joining field aliases in one (self join) or two models. They consist of a line with three drag points, terminated at one or both ends with crows foot symbols, as described above. The join colour indicates its current state:

Single Joins between models are displayed in cyan. Complex Join are shown in yellow. Both single and complex joins change colour to blue when selected.

The simplest way to create a join is to place the cursor over the field alias of the left side of the join (e.g. fk_currency in the model: contacts_personmember), then click and drag to the field alias of the right side of the join (e.g. primary in the model: accounts_currencyadj).

The Link Editor palette will open, displaying the selected model and field alias names. Select the join Type from the drop down list, then click OK to confirm the link. If the models are linked in a Single Join, click OK at the bottom of the palette to save the link.

If the models are linked in a complex join, click Add to append another join. This time, select all details in the Join Info section of the Link Editor by selecting Model 1, Model 2, Alias 1, Alias 2 and Type, then click OK to add the line to the Available Joins section of the Link Editor. Available joins can be changed by selecting the join, then clicking Edit or removed from the link by clicking Delete.

Once all joins are added, click OK at the bottom of the palette to save the link, and to display the changes on the E-R diagram.

Control Strip

Some of the functions of the Control Strip on the bottom of the Database Manager palette have been introduced earlier. This section provides a summary of all functions, including a few not yet covered.
Note: Some of the buttons described here are context sensitive. e.g. The Edit Model button is active only when a model is selected.

Metamodel.	Relationship
Name	This field usually displays the name of the Metamodel Relationship associated with the block currently being edited. However it can be changed using the Open and Save As functions.
Condition	When searching the database, the WHERE clause is constructed from a number of sources: A madatory restriction that limits the data returned to those records associated with the current owner organization. This allows multiple organization to share the same server. A mandatory restiction set by user security access privileges. Restrictions set in this Condition clause. Search parameters entered in Find mode or by scripting (in oCLI). This field displays any condition which is added automatically to all searches on tables in the relationship. e.g. "contacts_person"."Position"IS NOT NULL AND "person_small"."Class"="Staff"
Edit Condition	When this button is clicked, the Condition Editor palette is displayed.
	Build the condition using a combination of model names (from the Models drop down list), field aliases (from the list of Aliases for the selected model) and operators (Valid arithmetic, logical and summary operators are listed in the Operations list.) These include: +, -, *, / Add, subtract, multiply and divide the contents of numeric fields and constants. = Is equal to... <> Is not equal to... <= Is less than or equal to... >= Is greater than or equal to... < Is less than... > Is greater than... AND Logical AND OR Logical AND NOT Logical AND IS Is True IS NOT Is False NULL Field is empty. (...) A metamodel.relationship.model placeholder SUM(...) Calculate the total of a field for all records in the found set. MAX(...) Find the largest value in a field for all records in the found set. MIN(...) Find the smallest value in a field for all records in the found set. AVG(...) Calculate the average of a field for all records in the found set.
Models
Main Model	Displayed when the selected model is the first model in a relationship.
Allow Inserts	Allow the selected model to be included in insert operations for the relationship.
Allow Updates	Allow the selected model to be included in update operations for the relationship.
Allow Deletes	Allow the selected model to be included in delete operations for the relationship.
Create Model
Add Model	Open the Model Editor palette.
Edit Model	Open the Model Editor palette with the currently selected model displayed.
Delete Model	Remove the currently selected model from the relationship.
Kill Model	Remove the currently selected model from the relationship and permanently delete the model file from the server.
Links
Add Link	Open the Link editor to add a link to the relationship.
Edit Link	Open the Link editor to edit the selected link.
Delete Link	Remove the selected link from the relationship.
Misc.
Select All	Select ALL models in the relationships. This is useful when repositioning models on screen.
Undo	Undo the last operation. One level of undo is supported.
Arrange Model	Reposition all models to 'clean up' the layout.
Arrange Links	Reposition all twists from links to take the shortest possible path between models, as shown in the example below:
Before Rearranging Links                                  After Rearranging Links
Actions
New	Create a new relationship in the currently selected metamodel.
Open	Open a new metamodel relationship.
Reopen	Revert to saved version of the currently displayed relationship.
Save	Save the currently displayed relationship, overwriting the version on the server.
Save As	Save the currently displayed relationship with a new relationship name. A new metamodel name may also be specified. See example below:
Cancel	Close the Database Manager palette without saving changes to the currently displayed relationship.

Context Menus

As with other applications in once:radix, the Database Manager features a set of context sensitive menus. Most of these features are also available through the control strip at the bottom of the screen and have been described earlier in this section of the guide. A summary list of the menus and their functions are included here. The first two options are:

Undo	Ctrl+Z	Create a new metamodel.relationship.
Select All	Ctrl+N	Open an existing metamodel.relationship.
File Menu
New	Ctrl+N)	Create a new metamodel.relationship.
Open ...	Ctrl+O	Open an existing metamodel.relationship.
Revert to Saved		Reopen the current metamodel.relationship, after discarding any changes made since it was last loaded.
Save	Ctrl+S	Save the current relationship to its parent metamodel.
Save As...		Save the current relationship to a new parent metamodel.
Insert Menu
New Model...		Create a new model then add it to the relationship.
Model ...		Add an existing model to the current metamodel.relationship.
Link...		Add a Link to the current metamodel.relationship.
Edit Menu
Model...		Open the Model Editor palette to alter the currently selected model.
Link ...		Open the Link Editor palette to alter the currently selected link.
Condition...		Open the Condition Editor palette to alter the condition of the currently displayed relationship.
Delete Menu
Model...		Remove the currently selected model from the displayed relationship.
Link ...		Remove the currently selected Link from the displayed relationship.
Kill Model...		Remove the currently selected model from the displayed relationship and remove the model file permanently from the server.
Arrange Menu
Models		Repositions all of the models on screen to a simplified layout.
Links		Removes all twists from links, taking the shortest path between models.

Glossary of Database Terms

[relationship_name]
model1 = data/model_name
condition =

[relationship_name]
model1 = data/model_name_1
model2 = data/model_name_2
model3 = data/model_name_3
model4 = data/model_name_4
model5 = data/model_name_5
insert1 = data/model_name_1
insert2 = data/model_name_3
insert3 = data/model_name_4
update1 = data/model_name_1
update2 = data/model_name_3
update3 = data/model_name_4
update4 = data/model_name_5
delete1 = data/model_name_1
condition =  "model_name_1.field_alias" = 'inventory'
join = "model_name_1" LEFT OUTER JOIN "model_name_2" 
ON ("model_name_2"."primary" = "model_name_3"."fk_key_1")
LEFT OUTER JOIN "model_name_2"
ON ("model_name_4"."primary" = "model_name_5"."fk_key_2")
designer = 8:8;200:32;411:38;831:257;1023:281;611:30; 
778:202,788:259,798:316;970:372,980:370,990:369; 
970:372,980:370,990:369;583:126,576:120,569:115; 
379:100,368:121,357:142;168:135,158:106,148:78;