View Datasource
Last updated
Last updated
A View Foundset Datasource represents a custom table, where the data is not stored in a physical database but instead exists as a definition. The data for this table is loaded at runtime, often based on a QBSelect query object. This can be highly useful for creating data sets that span multiple tables, improving performance over traditional methods like relations, valuelists, or aggregates.
There are two ways to create view foundset datasources:
Using the Solution Explorer context menu under Datasources -> View foundsets -> Create view foundset. This opens a dialog where you can specify view foundset name.
After adding the datasource name, the Table Editor will open, allowing you to define the structure of your table, just like any other table.
View Foundset can also be created using the following method: databaseManager.getViewFoundSet(String name, QBSelect query)
This returns a ViewFoundSet object with the datasource: "view:[name]". The ViewFoundSet object has a limited API compared to the regular JSFoundSet. It supports essential operations like getSize(), getRecord(), forEach(), and loadAllRecords(), but does not support find() or search(). For changes in the data, a new ViewFoundSet should be created with an updated QBSelect.
To use a View Foundset on a form, you can register it with the system by using the following method: databaseManager.registerViewFoundSet(ViewFoundSet foundset)
This keeps the instance in memory for forms that use that datasource. If it is no longer needed, you can unregister it: databaseManager.unregisterViewFoundSet(String datasource)
By default, a View Foundset does not react to databroadcast changes, but you can enable this feature by calling: viewfoundset.enableDatabroadcastFor(QBTableClause queryTable, int flags)
This enables the View Foundset to listen for changes in specific tables within the query. Depending on the flags provided, you can monitor columns, join conditions, where conditions, inserts, deletes, or even aggregates.
ViewFoundSet.MONITOR_COLUMNS: Monitors column changes for the table/datasource. This flag is used by default if no other flags are specified. It listens for changes in the columns of the selected primary key (PK) for the datasource and updates the affected columns without requiring a full query. Note: The PK must be included in the result set for this flag to work.
ViewFoundSet.MONITOR_JOIN_CONDITIONS: Monitors changes in the join conditions of the table/datasource. For example, if there’s a join between order_lines.productid and orders, and productname is displayed, any changes to order_lines.productid will trigger a query to detect and apply the change.
ViewFoundSet.MONITOR_WHERE_CONDITIONS: Monitors column changes in the WHERE clause of the query. If any column in the WHERE condition, such as order_lines.unit_price > 100, is changed, a full query is executed to detect and update the affected records.
ViewFoundSet.MONITOR_INSERT: Monitors new inserts into the table/datasource. This flag triggers a full query whenever a new record is inserted, ensuring that the ViewFoundSet reflects the new data.
ViewFoundSet.MONITOR_DELETES: Monitors deletions in the table/datasource. A full query is executed whenever a record is deleted, updating the ViewFoundSet to reflect the changes.
ViewFoundSet.MONITOR_DELETES_FOR_PRIMARY_TABLE: Monitors deletions specifically for the primary table of the query. When a record is deleted, the ViewFoundSet only removes records that match the deleted primary key, avoiding the need for a full query. Note: Only one delete monitor (either MONITOR_DELETES or MONITOR_DELETES_FOR_PRIMARY_TABLE) should be used per table/datasource, and the PK must be included in the results.
ViewFoundSet.MONITOR_AGGREGATES: Monitors changes in columns that affect aggregates in the query. This flag triggers a full re-query when there are inserts, updates, or deletes on the columns that influence aggregate values, such as SUM or GROUP BY. It’s essential for queries that group by columns or use aggregates on selected columns. For instance, in a query like:
SELECT orders.customerid, orders.orderdate, SUM(order_details.unitprice) FROM orders LEFT JOIN order_details ON orders.orderid = order_details.orderid GROUP BY orders.customerid, orders.orderdate
Changes in orders.customerid, orders.orderdate, or order_details.unitprice will affect the aggregates and require a re-query to update the data.
Flags like MONITOR_COLUMNS or MONITOR_DELETES_FOR_PRIMARY_TABLE typically have minimal impact on performance, as they don't require full re-queries. However, other flags, such as MONITOR_WHERE_CONDITIONS or MONITOR_AGGREGATES, can significantly affect performance since they may trigger full queries whenever a relevant change is detected in the data, in order to update the ViewFoundSet.
Besides monitoring for databroadcast changes, View Foundsets can be made editable by using one of the two save() functions. The key requirement is that for any column you change in a ViewRecord, you must also have the primary key (PK) selected for that table in the QBSelect. This allows Servoy to generate an update statement with the changed columns for that PK.
Any data changes in a View Foundset will temporarily pause the databroadcast refreshes (including loading more data) until the records are saved. This ensures that no refreshes or queries are made until the data is committed, avoiding potential conflicts between new data and existing records.
Expanding this node will display a list of all View Foundsets created for the active solution. These are also displayed in the contextual list.
Below is a summary of commands available via the right-click context menu for View Foundset Datasources:
Opens the Table Editor to edit the table
Deletes the View Foundset definition
Renames the View Foundset definition
Creates a view foundset datasource with a specified name (table name). Opens the Table Editor to define and edit the table like any other database table.
Opens the Table Editor to edit the View Foundset table’s structure and column information (metadata stored by Servoy Developer).
Deletes the View Foundset definition.
Renames the View Foundset definition.
