Editable Results Tables

Results tables produced by an SQL query in most database systems are usually read only, but in Manifold results tables from a query often can be interactively edited, with changes made to values in the results table propagating back to the tables used to create the results table.  They can even have columns managed using the Layers Pane with Filters applied and other Manifold facilities working as well.

 

If a results table includes an indexed field from a source table that participates in the query, as is often the case with a SELECT or a JOIN statement, we can edit the fields from that source table that appear in the results table and the edits made to field values automatically will be written back into the source table.   This capability is also called writeback in Manifold release notes and technical documentation.   

 

To be interactively editable, any  table in Manifold must have an index (normally a BTREE or BTREENULL index).  If an editable table is used in a query and one or more indexed fields from that table appear in the results table of the query, then Manifold will expose the indexes for those fields, that is, make the indexes available for use, in the results table as well.   The fields from the indexed source table which appear in the results table normally will be editable.   Fields from other tables that appear in the results table, for example, as the result of a JOIN statement, will not be editable unless they too have an index exposed from their source tables.

 

Whether or not we have writeback to fields in a results table depends on the indexes exposed by that table.   We can summarize how indexes are exposed and the effect on writeback as follows:

 

 

 

As can be seen from the above summary, the simplest example of writeback is when fields from a single source table, including the indexed field, appear in the results table.   The results table is then editable throughout.  A more complex situation arises when a results table includes fields from multiple different tables, which may have multiple indexes each.   

 

Let us take a look at tutorial examples for both the simple and the more complex cases, starting with a simple SELECT.

SELECT Example

Consider a small table called Prices, which is a simplified version of the Products table in the example nwind database.

 

The table has ten records listing products and prices and contains an mfd_id column on which a btree index has been built.   That is a typical arrangement for Manifold tables and as a result the table is editable, as indicated by the white background color of the table cells.

 

 

In the Command Window we can write a simple query:

 

SELECT * FROM Prices;

 

The results table for the query will also be editable, because it contains the mfd_id field from the Prices table and Manifold exposes in the results table the btree index for that field.  by exposed we mean that the index is available for use, appearing in the schema for the results table.

 

 

With the focus on the Command Windows if we choose Edit - Schema we can see the schema for the results table, and see that it contains a btree index on mfd_id.  The Schema dialog for the results table uses light gray background color, since it is a read-only display: we cannot change the fields or schema, which were generated by the query.

 

Viewing the Prices table along with the Command Window with query and results table, we can see how editing a cell in the results table automatically writes that edit to the source table for that cell.

 

 

In the results table we edit the table by double-clicking into the Name field of the second record and changing Tibetan Barley Beer to Tibetan Barley Drink.   The illustration above shows the edit just after we have pressed Enter to commit the edit.

 

 

As soon as we press Enter to commit the edit, the cell in the results table is edited, but also the new value for the cell is written into the Name field of the second record in the Prices table.  The Prices table is automatically updated.

 

The results table will not be editable if it does not contain a field with an index.    Consider the query:

 

SELECT [Name], [Quantity Per Unit], [Price]

  FROM Prices;

 

That query does not SELECT the mfd_id field so no field with an index is in the results table.

 

 

The results table appears in the Command Window with shaded cell backgrounds, indicating no fields are editable.

 

 

With the focus on the Command Window if we choose Edit - Schema we see that the schema of the results table does not include any index.

JOIN Examples

We now take a look at a series of examples that use two tables and various forms of JOIN statements.  The two tables used are the same two tables, Parts and Products, used as example tables in the JOIN Statements topic.   It is a good idea to review that topic before proceeding.

 

   

 

The Products table at right has a list of products where each Product has a product number in the ProdNum field.   The Parts table at left has a list of parts where each Part also has a product number in a ProdNum field, giving the number of the product in which that part is used.  Both the Parts table and the Products table have mfd_id fields and both tables have a btree index built on their mfd_id field.

 

Let us begin with a LEFT JOIN, where indexes are exposed for fields from the left table, but indexes are lost from the right table.  Consider the query:

 

SELECT Parts.mfd_id, Part, Supplier, Parts.ProdNum, Products.mfd_id, Product

  FROM Parts LEFT JOIN Products

  ON Parts.mfd_id = Products.mfd_id;

 

 

That generates a results table where fields from the left table are writable but fields from the right table are read only.

 

 

We can double-click into the Part field for the second record to edit the table, changing the value of Oil to Grease.

 

We press Enter to exit editing, and to commit the edit.

 

 

The moment we do that the Parts table is updated as well.   Editing the results table from the query also edited that same cell in the source table that participated in the query.

 

Consider the same query without the Parts.mfd_id field appearing in the SELECT list:

 

SELECT Part, Supplier, Parts.ProdNum, Products.mfd_id, Product

  FROM Parts LEFT JOIN Products

  ON Parts.mfd_id = Products.mfd_id;

 

 

Without the indexed field there is no index exposed for the left table's fields in the results table and so neither the fields from the left nor the right source table that appear in the results table are editable.

 

Let us take a look at a RIGHT JOIN.  Consider the query:

 

SELECT Parts.mfd_id, Part, Supplier, Parts.ProdNum, Products.mfd_id, Product

  FROM Parts RIGHT JOIN Products

  ON Parts.mfd_id = Products.mfd_id;

 

 

That generates a results table where fields from the right table are writable but fields from the left table are read only.

 

We can double-click into the Product field for the third record to edit the table, changing the value of Saw to Buzz Saw.  We press Enter to exit editing, and to commit the edit.

 

 

The moment we commit the edit,  the Products table is updated as well.   Editing the results table from the query also edited that same cell in the source table that participated in the query.

 

As before, if we do not include the mfd_id field for the right table in the SELECT list:

 

SELECT Parts.mfd_id, Part, Supplier, Parts.ProdNum, Product

  FROM Parts RIGHT JOIN Products

  ON Parts.mfd_id = Products.mfd_id;

 

Then the table will not be writable for either the left or the right side:

 

 

Consider a situation where we might be surprised to see that the results table is not edited on either the left or the right table fields.  Recall that our first LEFT JOIN example:

 

SELECT Parts.mfd_id, Part, Supplier, Parts.ProdNum, Products.mfd_id, Product

  FROM Parts LEFT JOIN Products

  ON Parts.mfd_id = Products.mfd_id;

 

Produced editable fields from the left side table but not from the right side table:

 

 

Suppose we change the join condition so instead of matching mfd_id values in the left and right tables it instead matches ProdNum values:

 

SELECT Parts.mfd_id, Part, Supplier, Parts.ProdNum, Products.mfd_id, Product

  FROM Parts LEFT JOIN Products

  ON Parts.ProdNum = Products.ProdNum;

 

It produces a results table that is not editable either for left side fields or right side fields:

 

 

Why is that?   With the focus on the Command Window we can choose Edit - Schema to see why:

 

 

The schema shows that the btree index was automatically converted into a btreedup index for the results table, because the join condition now can result in duplicate mfd_id values.  Without a btree index exposed in the results table it is not editable at all.

 

Notes

Source tables must be writable - A reminder: for fields in a results table to be writable in addition to indexed fields being present the source table itself should be writable.   For example, if we create a data source using an Oracle database that is read-only then, of course, tables we see from that read-only Oracle database will not be writable.  If we use tables from that read-only Oracle data source in a query and fields from those tables appear in a results table, those fields, of course, will still be read-only.   

 

See Also

Tables

 

Data Types

 

Indexes

 

Queries

 

Filters

 

Layers Pane

 

Command Window

 

Command Window - Query Builder

 

SQL

 

SQL Statements

 

JOIN Statements