Schema

The Schema dialog lets us change the structure of tables, to add new fields, or to rename or delete old fields.    We can add indexes or constraints as well.    

 

The Schema dialog is enabled in the Edit menu when the focus is on a table window.   We can also launch the Schema dialog by right-clicking on a table or a query in the Project pane and choosing Schema in the context menu.   Please read this topic in conjunction with the Data Types, Indexes and Tables topics.  

 

The same Schema dialog is used to manage tables entirely within Manifold .map projects,  and also to manage tables within external database servers, like an Oracle or PostgreSQL DBMS, that are linked into a Manifold project.  The Schema dialog automatically configures options to match Manifold's own native database engine as well as the  varying capabilities of the most popular DBMS packages.  

Schemas Show Table Structure

A table contains:

 

 

All of these aspects of a table's structure taken together are referred to as a table schema. , pronounced "skee ma" in English.   The Schema dialog allows us to add, to delete fields (including computed fields), indexes and constraints.   We can also rename fields.

Make Provisional Changes and then Save Changes

The Schema dialog uses a two-step workflow for safety and for efficiency:  to alter a table's schema we must first make changes and then second apply them by pressing the Save Changes button.   We can abandon changes by pressing Close.

 

When we press the Save Change button, Manifold will change the infrastructure in whatever database hosts that table, which could be Manifold's own, internal Radian database in the case of tables that are stored in the .map portion of a Manifold project, or which could be tables stored within an external database, such as an Oracle, MySQL, PostgreSQL or other DBMS, in the case of tables that are stored in data sources created from external databases.  If we have access permissions to such databases that allow us to modify table schemas, the Schema dialog can change table schemas in external databases as well.

 

Tech tip:  Changing schemas in databases is a big deal that should be thought through carefully, especially when making changes to big tables that host billions of records.  Changing the schema of a big table hosted in an external DBMS, like MySQL or SQL Server or Oracle, can require significant processing in the DBMS, so it is not something database administrators take lightly, regardless of what tool they use.  As the saying goes, "measure twice, cut once."  

 

We can abandon any provisional changes made in a schema right up to pressing the Save Changes button, but once we press that button the changes go into effect.  To "undo" a mistake when adding a field, such as using the wrong data type, we will have to delete that field, save changes, and then relaunch the Schema dialog to start over.

 

For example, if we add a computed field that uses an erroneous expression, such as a wrong number in a multiplication, we cannot simply open the dialog and re-edit the expression.  We must delete the field (copy the old expression first to avoid re-keyboarding it again), save changes to make the deletion take effect, launch the Schema dialog again and then add the field again with the corrected expression.  

Names

Everything in a table's schema has a name, that is, an identifier in SQL terminology.   Manifold's rules for names are broader than in some databases, so as a practical matter the names we use should be chosen for better SQL portability to other databases.   For example, Manifold allows names to be of effectively unlimited length, but it is wise to keep them to 128 characters or less for broader DBMS portability, or even to 64 characters or less for compatibility with MySQL.    The following rules are practical advice (often ignored in Manifold examples), and are not necessarily the limits of Manifold:

 

 

Example: We can have a field named x in one table and an index named x in a different table, but we cannot have both a field named x and an index named x in the same table.   We can also have indexes named x within several different tables in the same Manifold .map database.  The names of indexes do not have to be unique within a .map database in Manifold.  We cannot name a field select in Manifold.  We cannot name one field Sales and a different field SALES, because names are case-insensitive so those are both the same field name when compared case-insensitive.

 

See the Identifiers, Constants and Literals topic for Manifold's rules within Manifold.

Change the Name of a Field, Index, or Constraint

We can change the names of fields, indexes, and constraints by double-clicking the item, changing the name and then saving changes.   However, if a field is being used in an expression for some computed field, or if a field has an index on it, then we cannot change that field's name without deleting the computed field or index first, renaming the field, and then recreating the computed field or index using the renamed field's new name.

 

The names of fields, indexes, and constraints are case-insensitive in SQL and almost all databases, so attempting to change the name of a field in a way that just involves case, such as changing myfield to Myfield, will not work, as both versions are considered the same.  See the Notes below for a workaround.

Schema Dialog

With the focus on an opened table window, choose Edit - Schema to launch the Schema dialog.   Ctrl-E is the keyboard shortcut to launch the Schema dialog.

 

 

Launching the Schema dialog for the table above will show:

 

 

The schema shows the name of each field in the table and the data type of that field.   If a field is a computed field, the schema will show the expression used to compute that field.   The schema will also show parts of the table's infrastructure that do not appear in the table window, such as indexes and constraints.   The table above has five fields that appear in the table, and in addition it also has two indexes, a btree index on the mfd_id field and an rtree index on the Geom field.

 

If the table schema is read-only, all commands are disabled and rows will be shown in gray background color.  If the table is in an external database and that database does not support adding indexes or constraints, relevant commands will be disabled.

 

To alter a table's schema, for example, by adding or deleting fields, indexes or constraints, we make the changes we like and they appear using a provisional, bluish background.  Any new fields, indexes or constraints we add will have a + icon in the row handle to indicate they have been added.   If we like the changes we have made, we can commit them to the table and close the dialog by pressing the Save Changes button.  If not, we can make more changes, or we can simply press the Close button to close the dialog without making any changes.

 

Ordering of schema items is important for computed fields and for indexes, so we should think ahead when adding new fields:

 

 

Important: Changes we make within the Schema dialog are not saved to the table automatically.  We must press the Save Changes button to apply those changes to the table.  This gives us the chance to change our minds, abandon edits, or to correct errors before altering the schema of a table.   If we want to abandon changes, we press the Close button.

 

Double-click

Edit an item.   Double-click an item in the Schema grid to edit it.  For example, to rename a field, double-click into the field name cell.  Double-clicking a computed field or constraint is a quick way to see the full expression in use.

Ctrl-click

Select or deselect a row.  Ctrl-click on a row in the schema to select / de-select it.  We can also Shift-Ctrl-Click to select a swath of rows. The usual selection commands for grids (like tables) work in the Schema dialog as well.

(mouse hover)

Hovering the mouse over a cell in the Schema dialog will show expressions in a tooltip.

Add

Add a new field, index or constraint.   A drop down menu allows choice of sub-dialogs:

Shortcut: Pressing the Insert keyboard key launches the Field dialog, equivalent to pressing Add and then choosing Field from the drop down menu.

Add Identity

Add an identity field and index.  For tables within a .map file,if no mfd_id field yet exists,  the Add Identity command adds mfd_id and mfd_id_x regardless of which indexes the table already contains.  If the table already contains an mfd_id field and/or an mfd_id_x index, the Add Identity command selects those rows in the schema.

 

When the table is In a database, if the table already contains a btree index allowing no duplicates and no nulls, the Add Identity command selects that index and the field or fields it uses. If the table contains no btree index, the Add Identity command add a new field and a new btree index on that field. If the database supports creating autogenerated fields, the new field will be created as an autogenerated field.

 Delete

Delete selected items. Disabled if the table is read-only.  Deleting a field upon which an index or a not-null constraint depends will, after a confirmation dialog, also delete the index or not-null constraint.  If both the index or constraint and the field upon which it depends are selected, pressing Delete will delete both without needing confirmation.  When a confirmation dialog is raised, not approving the confirmation means nothing will be deleted.

 

Shortcut: Pressing the Delete keyboard key deletes selected items.

Filter Box

Enter text to be matched, case not significant. to filter the list of schema items using their name.   The list of schema items will be reduced to show only those which match the text.    For example, entering geo will reduce the list to items such as Geom and Geom_x, both of which have the three letter sequence geo in their names, but will not include items like Place name or Latitude.   Filtering by name automatically disables Add Field, Add Index, Add Constraint, and Add Identity commands to prevent newly added items from being immediately hidden due to not passing the filter. Editing an existing item and changing its name lets the item stay visible even if the new name no longer matches the filter.

 Filter  

Provides a drop down menu to filter schema items using their type: All, Fields, Indexes, or Constraints. Filtering by type automatically disables Add Field, Add Index, Add Constraint, and Add Identity commands for items that will not pass the filter. For example, when setting the filter to only show fields, we can still add new fields, but we cannot add indexes and constraints until we change the filter to show indexes and constraints.

(gray background color)

Rows in white color may be changed.  Rows in gray background color are read-only and cannot be changed.

Field icon.

Computed field icon.

Coordinate system icon for geom and tile fields.  Shown in black for coordinate systems that have been assigned.  Shown in red for coordinate systems not yet assigned.

Index icon.  Appears for any field that participates in an index.  The icon is dark blue when the field is a geom or tile field in an rtree index or the sole filed in any btree index.  The icon is light gray when the field participates in a btree index with other fields.

Primary key icon in databases.

Constraint icon.

Edit Query

The Edit Query button appears when we make a change to the schema.  Press the button to launch the Command Window loaded with an SQL query that will make the equivalent changes the dialog would make.   For example, if double-clicked a field called Place Name in the schema and then used the Field dialog to change the name of the field to Name, if we pressed the Edit Query button the following query would appear in the Command Window:

 

-- $manifold$

--

-- Auto-generated

-- Schema

--

ALTER TABLE [Cities] (

  RENAME [Place name] [Name]

);

 

The Edit Query button is a great way to learn how to use SQL to alter the structure of tables, and to make more elaborate changes or to save changes in easily repeatable form (as queries) for future use.

Save Changes

Commit any changes made, changing the table's schema, and close the dialog.

Close

Close the dialog without making any changes to the table's schema.

Filter Box and Filter Button

Use the Filter box and Filter button to manage schemas that have very many items.  Consider the Schema dialog opened to the same Natural Earth table illustrated in the Layers Pane and Tables topic, a table with over 100 fields:

 

 

Finding all of the fields that have name in them using the filter box is easy:

 

 

As soon as we enter name into the filter box the list is automatically filtered to show only those items that include that four letter sequence in their names.

 

 

 If we would like to see a list of only indexes in the schema, we can clear the text in the Filter box and then we can choose Indexes in the Filter button menu.

 

 

We can combine use of the filter box and the filter button.

 

 

For example, entering geo into the filter box while also choosing Indexes in the filter button will show only those indexes that have geo in their names.

Add Identity

Tables that do not have an identity field, that is, a key field, and btree index on that identity field cannot be edited and do not allow selection.   Drawings and tables imported from most GIS and from sophisticated data sources will usually have a key field and btree index, but when importing tables from simple formats like CSV the tables thus imported might not have a key field and btree index.  

 

Adding the usual Manifold mfd_id key field and mfd_id_x btree index to such tables is such a frequent task that Manifold provides a one-click Add Identity button to do so.   We show an example using a table within the .map project.

 

 

Launching the Schema dialog for a table that does not have a key field and btree index, we click the Add Identity button.

 

 

Instantly, an mfd_id field and mfd_id_x are added to the schema.  To commit the changes we click Save Changes.

Field Dialog

Press the Add button and choose Field in the drop down menu to launch the Field dialog.  

 

Controls that appear depend on the Type we choose for the new field, and the data source which hosts the table.  For example, some databases can autogenerate values for fields, while others require advance notice if a geometry field will contain Z values in addition to X and Y values.  The Edit Expression button is disabled for tables hosted in external databases, since computed fields as created by the Expression dialog are available only in tables hosted within Manifold .map projects.

 

The initial default data type in the Type field is int32, for 32-bit integer numbers.   The Field dialog remembers the last used Type and presents that as the default until a different Type has been picked.  That makes it easy to repeatedly add new fields of the same data type.

 

Following are illustrations showing typical configurations of the Field dialog when adding fields to tables in various different data sources.  Illustrations below have been marked in green color with the name of the DBMS used when they show an example of a Field dialog for a table hosted in an external database, using PostgreSQL/PostGIS and Microsoft SQL Server as examples.  

 

 

 Name

Name to use for the new field.

 Type

The data type for the new field.   Additional controls will appear as required for different data types.  The initial default data type in the Type field is int32, for 32-bit integer numbers.   The Field dialog remembers the last used Type and presents that as the default until a different Type has been picked.  That makes it easy to repeatedly add new fields of the same data type.

Autogenerate values

Appears for integer type fields in tables within external databases that support autogenerated fields (most databases).  Check the box to create the field as an autogenerated field.  Does not appear for tables within .map files, as .map files do not provide an autogenerated type other than the special case of an mfd_id identity field.

 Coord system

Appears for geom and tile types.  Coordinate systems are created using Pseudo-Mercator by default.   Press the coordinate picker button to launch the usual coordinate system drop down menu to choose a different coordinate system.

Pixels

The data type of pixels within the tile, including vector types for multi-channel images.

(tile size)

Appears for tile types.  The size of the tile in pixels.  

Reduce

Appears for tile types.  A drop down menu that allows choosing either average or indexed as the method to be used to compute pixel values when interpolating larger tile sizes down to the specified tile.  For example, if the tile will be created as a computed field that reduces a 256 x 256 sized tile to a 128 x 128 tile, four pixels from the larger tile size will be interpolated down into a single pixel in the 128 x 128 tile.  

 

  • average: create the value for the interpolated pixel by averaging values of source pixels that are interpolated into the pixel.  

  • indexed: create the color for a pixel by choosing the most frequently occurring value in the source pixels that are interpolated into the pixel.  If no pixel values repeat, choose one at random.

 

The indexed choice ensures that no new values will appear in the image that are not already used in the source image, an important characteristic when specific values are used to classify pixels. The average choice may generate new values (the average of existing values) which do not appear in the source image, but which might provide a better visual appearance or smoother interpolation of elevation data.

Bounds

Appears for geom type in databases that require specifications of the minimum and maximum bounds to allow creating a spatial index, such as Oracle, and SQL Server.  Bounds for the X and Y ranges are specified as [ xmin, ymin, xmax, ymax ].  Bounds use whatever units of measure are used by the coordinate system.  Therefore, a bounds specification such as [ -180, -90, 180, 90 ] works for Latitude / Longitude projection, as those values make sense as bounds in degrees, but likely would be wrong for a meter-based system such as Pseudo-Mercator, where native coordinates will be long numbers in meters.    The Bounds option does not appear for geometry types, geommfd and geomwkb, that are not native to the database.

Geometry

Appears for geom type in databases that allow specifying type restrictions for geometry fields, for example, PostgreSQL with PostGIS.   Choices are:

 

  • any - default, allowing points, lines or areas.

  • area - restrict geometry to areas only.

  • line - restrict geometry to lines only.

  • point - restrict geometry to points only.

 

Z values

Appears for geom type in databases, such as PostgreSQL/PostGIS and GPKG,  that require specifying if a geometry field will have just X and Y values or X, Y, and Z values.  Check this box if Z values will be stored in the geometry field.   If a database requires both advance notice of Z values and bounds, the bounds may (but are not required to) include the Z range, specified as [ xmin, ymin, zmin, xmax, ymax, zmax ].  The Z option does not appear for geometry types, geommfd and geomwkb, that are not native to the database.

 Expression

Used to create computed fields.  The Expression tab contains an SQL expression that is evaluated for each record to compute the value of the computed field for that record.  Expressions can only refer to fields which appear in the schema above, that is before, the computed field. Appears for tables within .map files only.  Disabled for table schemas within external database tables.  Expression text can be scrolled, selected and copied even if the expression cannot be edited.

   Expression Context

Used to create computed fields.  The Expression Context tab contains SQL expressions that set the context for the expression that is executed in the Expression tab.  For example, the Expression Context may execute queries that set the context, run PRAGMA directives, define a function that the expression calls, or may use VALUE statements to define constants that the expression uses.  SQL in the Expression Context is evaluated once per field, not once per record, so SQL in the Expression Context tab cannot contain the names of any fields. Appears for tables within .map files only.  Disabled for table schemas within external database tables. Expression Context text can be scrolled, selected and copied even if the expression cannot be edited.   See the Example: Expression Context and Computed Fields  topic for a step by step example using the Expression Context tab.

  Edit Expression

Launch the Expression dialog, providing a Query Builder to edit the expressions that can appear within Expression and Expression Context tabs.  Appears for tables within .map files only.  The Edit Expression button is disabled for tables hosted within external database tables.

 OK

Apply changes and exit dialog.

   Cancel

Exit dialog without applying changes.

 

 

For geom and tile types, clicking the coordinate picker button launches a drop down menu that allows choosing More... for the full Coordinate System dialog, choosing from a list of favorites (which include Latitude / Longitude and Pseudo-Mercator by default), or editing the Favorites list to add or remove favorites.

 

Expression Dialog

Clicking Edit Expression in the Field dialog launches the Expression dialog, which provides a query builder to more rapidly construct Expression tab and Expression Context tab  expressions.    

 

In this example we will use the Products table from the Nwind example database.  We will create a new computed field called Inventory Value that multiplies the number of units in stock with the price per unit, to get a total value for that product.

 

 

 For example, in the Schema dialog for a table we can press the Add button to add a field.  In the Field dialog we enter the name Inventory Value and a Type of float64.   Press Edit Expression to launch the Expression dialog.

 

The query builder in the Expression dialog works like the query builder in the Command Window.   See the Query Builder topic.

 

 

 Expression

The Expression tab contains an SQL expression that is evaluated for each record to compute the value of the computed field for that record.  Expressions can only refer to fields which appear in the schema above, that is before, the computed field.

   Expression Context

The Expression Context tab contains SQL expressions that set the execution context for the expression that is executed in the Expression tab.   For example, the Expression Context may execute queries that set the context, run PRAGMA directives, define a function that the expression calls, or may use VALUE statements to define constants that the expression uses.  SQL in the Expression Context is evaluated once per field, not once per record, so SQL in the Expression Context tab cannot contain the names of any fields.    See the Example: Expression Context and Computed Fields  topic for a step by step example using the Expression Context tab.

  (Expression)

The expression being built.

 (Filter)

Filter the long list of templates to only those which contain the text string in the filter, case insensitive.   For example, entering geom will reduce the long list to only Functions that contain geom in their names, such as BinaryWkbGeom and GeomArea.

   Templates

For the Expressions tab, provides a long list of fields in the schema, SQL operators and SQL functions.  For the Expressions Context tab, provides a long list of SQL facilities that allow setting execution context.  Double-click on a template to add it to the expression being built at the current cursor position.

 OK

Apply changes and exit dialog.

   Cancel

Exit dialog without applying changes.

 

Click a tab to switch between building an Expression and building the Expression Context.

 

 

 

Pressing OK in the above display loads what is built into the Field dialog, to be used for a computed field.

 

 

Pressing OK to exit the Expression dialog we can see the new computed field added to the schema.

 

 

The new computed field appears in provisional, blue preview background color, with a + symbol in the row handle to indicate it has been added.   To commit the change to the schema and to add this computed field to the table, we press Save Changes.     If we would like to learn a bit of SQL, we can press the Edit Query button to launch a Command Window with an SQL query that implements the same change to the schema, when the query is run.  The SQL to add such a computed field is remarkably simple and clear:

 

-- $manifold$

--

-- Auto-generated

-- Schema

--

ALTER TABLE [Products] (

  ADD [Inventory Value] FLOAT64

    AS [[ [Unit Price] * [Units In Stock] ]]

);

Expression Context

The Expression Context tab provides a place to write SQL that is executed just once to establish the context in which expressions written in the Expression tab are evaluated.   For example, we can place function definitions or the definitions of global values in the Expression Context tab.   This is particularly a good place to put global values that are computed, so the computation of the global value happens just once and then it can be used for each record by the expression in the Expression tab.  

 

 See the Example: Expression Context and Computed Fields  topic for a step by step example using the Expression Context tab.

Changing Expressions for Computed fields

Once a new computed field has been committed to the table by pressing Save Changes, the expression used for that field cannot be changed by launching the Schema dialog again and then editing the computed field row in the schema.

 

If we would like to alter the expression, we can do that by creating a new computed field with the desired expression, deleting the old computed field, and saving the schema. Next, launch the schema dialog again to rename the new computed field to use the same name as the original computed field used.

Index Dialog

Press the Add button and choose Index in the drop down menu to launch the Index dialog.   See general notes in the Indexes topic. The controls that appear depend on the Type we choose for the new index:

 

 

 Name

Name to use for the new index.  The recommended Manifold convention is to name an index using the name of the main field on which the index is based plus _x, so that an index based on a field called Geom would be called Geom_x.

 Type

The type of index, one of the following three with options for each:

 

  • regular index (btree) - For all fields except geometry and tile fields. Available in Manifold and all external databases.

  • spatial index for geometry (rtree) - For geometry fields as used with vector.  Available in Manifold and most external databases.

  • spatial index for tiles (rtree) - For tile fields as used with raster data.  Available in Manifold only.

 

See the Indexes topic with more details on index types.

btree index

 

Allow duplicate values

Configure the index to allow duplicate values in the fields it indexes.

Allow null values

Configure the index to allow NULLs in the fields it indexes.

Field

Four boxes marked Field allow choice of up to four fields to participate in the index.  At least one box of the four must have a field chosen.  The drop down menu is loaded with fields within the table.  Choose a field to use that field and to enable the Descending and collation controls for that field.   We can even create an index on a computed field, so long as the table for the computed field is stored in the .map project (and not in an external database).

Descending

Indexes are computed in ascending order by default.  Check this option to create the index computed on this field in descending order.

Collation picker button, enabled for text fields. The neutral language collation is used by default.  Click the collation picker button to choose a different collation from the drop down menu:

 

  • (list of favorites) - A list provides one-click choice of any collations in the Favorites list.    The neutral and neutral, nocase collations are listed by default.
  • More - Launch the Collation dialog to choose a collation or to alter the current collation, for example, by changing options.
  • Favorites - Manage the Favorites list of collations, for example, by adding a collation to the list, deleting a favorite, or renaming a favorite.

 

  See the Collations topic for details on collations, including the use of the above controls.   

geometry spatial index

 

Geometry

Choose the geometry field for which the spatial index will be computed. We can create a spatial index on geometry fields of type geom, that is, the native geometry type of the database.  The dialog does not allow creating spatial indexes on geometry fields that are a geometry type, such as geommfd and geomwkb, that are not native to the database.

tile spatial index

 

X

Choose the X value field for tile position in the image.

Y

Choose the Y value field for tile position in the image.

Tile

Choose the tile field for which the spatial index will be computed.

Pixels

The data type of pixels within the tile, including vector types for multi-channel images.

(tile size)

The size of the tile in pixels.  

Reduce

A drop down menu that allows choosing either average or indexed as the method to be used to compute pixel values when interpolating larger tile sizes down to the specified tile.  For example, if the tile will be created as a computed field that reduces a 256 x 256 sized tile to a 128 x 128 tile, four pixels from the larger tile size will be interpolated down into a single pixel in the 128 x 128 tile.  

 

  • average: create the value for the interpolated pixel by averaging values of source pixels that are interpolated into the pixel.  

  • indexed: create the color for a pixel by choosing the most frequently occurring value in the source pixels that are interpolated into the pixel.  If no pixel values repeat, choose one at random.

 

The indexed choice ensures that no new values will appear in the image that are not already used in the source image, an important characteristic when specific values are used to classify pixels. The average choice may generate new values (the average of existing values) which do not appear in the source image, but which might provide a better visual appearance or smoother interpolation of elevation data.

 OK

Apply changes and exit dialog.

   Cancel

Exit dialog without applying changes.

 

When creating an index of type regular index (btree) the actual index type which is created depends upon the options checked:

 

btree

A classic B-tree, that is a balanced tree, index.  A btree index allows no duplicates and no NULLs.

btreedup

A B-tree index that allows duplicates but does not allow NULLs.

btreedupnull

A B-tree index that allows duplicates and also allows NULLs.

btreenull

A B-tree index that allows NULLs but does not allow duplicates.

Constraints

Primarily used for data integrity checks, constraints are boolean expressions which evaluate to true for all table records.   Every time we try to insert a record into a table the system evaluates all constraints for the data about to be inserted and allows the insertion only if all constraints are satisfied. The system also checks constraints whenever we try to update a record.   If the attempted update fails one or more constraints it is rejected in full and the record is not modified.    See the Example: Create a Table with a Constraint topic for a step by step look at constraints in a table.

 

 

The illustration above shows the Schema dialog for a typical table in PostgreSQL, that was copied and pasted into PostgreSQL from Manifold.  mfd_id is the key field for the index mfd_id_x, and the mfd_id_notnull constraint requires that mfd_id is not NULL.    The Constraint dialog (see below) allows specifying a not-NULL constraint, so long as the database in which the table resides supports not-NULL constraints, as is supported in PostgreSQL.

Constraint Dialog

Press the Add button and choose Constraint in the drop down menu to launch the Constraint dialog.

 

 

 Name

Name to use for the new constraint.

 Template

Choice of expression or, if the database hosting the table supports not-NULL constraints, not null together with a choice of field.  

(field)

A box allowing choice of a field from a drop down menu appears when a template using a field, such as not null, is chosen.

 Expression

The Expression tab contains an SQL expression that is evaluated for each record to compute the value, true or false, of the constraint for that record.  Expressions for constraints can refer to any field in the table, and not just those which appear in the schema above, that is before, the constraint.

   Expression Context

The Expression Context tab contains SQL expressions that set the context for the expression that is executed in the Expression tab.  For example, the Expression Context may execute queries that set the context, run PRAGMA directives, define a function that the expression calls, or may use VALUE statements to define constants that the expression uses.  SQL in the Expression Context is evaluated once per constraint, not once per record, so SQL in the Expression Context tab cannot contain the names of any fields.  See the Example: Expression Context and Computed Fields  topic for a step by step example using the Expression Context tab.

  Edit Expression

Launch the Expression dialog, providing a Query Builder to edit the expressions that can appear within Expression and Expression Context tabs.  Appears for tables within .map files only.  Disabled for table schemas within external database tables.

 OK

Apply changes and exit dialog.

   Cancel

Exit dialog without applying changes.

Using the Expression Dialog to Build a Constraint

Clicking Edit Expression in the Field dialog launches the Expression dialog, which provides a query builder to more rapidly construct Expression tab and Expression Context tab  expressions.    The query builder in the Expression dialog works like the query builder in the Command Window.   See the Query Builder topic.

 

 

 Expression

The Expression tab contains an SQL expression that is evaluated for each record to compute the value of the computed field for that record.  Expressions can only refer to fields which appear in the schema above, that is before, the computed field.

   Expression Context

The Expression Context tab contains SQL expressions that set the execution context for the expression that is executed in the Expression tab.   For example, the Expression Context may execute queries that set the context, run PRAGMA directives, define a function that the expression calls, or may use VALUE statements to define constants that the expression uses.  SQL in the Expression Context is evaluated once per field, not once per record, so SQL in the Expression Context tab cannot contain the names of any fields.   See the Example: Expression Context and Computed Fields  topic for a step by step example using the Expression Context tab.

  (Expression)

The expression being built.

 (Filter)

Filter the long list of templates to only those which contain the text string in the filter, case insensitive.   For example, entering geom will reduce the long list to only Functions that contain geom in their names, such as BinaryWkbGeom and GeomArea.

   Templates

For the Expressions tab, provides a long list of fields in the schema, SQL operators and SQL functions.  For the Expressions Context tab, provides a long list of SQL facilities that allow setting execution context.  Double-click on a template to add it to the expression being built at the current cursor position.

 OK

Apply changes and exit dialog.

   Cancel

Exit dialog without applying changes.

 

Click a tab to switch between building an Expression and building the Expression Context.

 

 

 

Pressing OK in the above display loads what is built into the Constraint dialog, to be used for a constraint.

Schemas and Queries

Consider a query using the Employees table from the Nwind sample database:

 

SELECT [mfd_id], [First Name], [Last Name] FROM [Employees];

 

Running the query produces a results table:

 

 

To see the schema of a results table from a query, when a results table is displayed in the Command Window we can choose Edit - Schema:

 

 

The query has gray background color for rows that cannot be changed.  We cannot change any rows in the schema because the results table is generated by a query.   Opening the schema shows what data types are used for the various fields and it also shows what indexes are in the results table.

 

We can see the schema of most queries, without opening the query in a Command Window, by Right-clicking on the query in the project pane and then choosing Schema.   

 

 

Queries can report their schemas in cases where the schema of the result table can be discovered from the query text without actually running the query.    

 

Consider a query such as:

 

SELECT [mfd_id], [Last Name], [First Name], [Title] FROM [Employees]

WHERE (...more stuff here...)

 

In the above the Employees table exists in the project and there is no mystery about what fields are in the SELECT list and thus will appear in the results table.   The system can report the schema for the table based on those fields without needing to know how many records and which records will be in the results table.

 

In contrast, consider a query such as:

 

CREATE TABLE [Prices] (

  [mfd_id] INT64,

  [Name] NVARCHAR,

  [Cost] INT32,

  [Retail] INT32 AS [Cost]*2,

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

SELECT * FROM [Prices]

WHERE (...more stuff here...)

 

The result table arises from the SELECT, but that depends on a table, Prices, which does not exist if the prior part of the query has not run.   In this case the schema of the result table cannot be discovered without running the query.

Schemas and Database Views

Database views are just queries within an external database that generate results tables.   We can see the schema for such a query by right-clicking the view and choosing Schema.

Tooltips

Hovering the mouse over a cell in the Schema dialog will show expressions in a tooltip.

 

 

That is a handy effect to quickly review an expression too big to be seen within the default display.

Edit Query

The Edit Query button in the Schema dialog launches a Command Window and fills it with SQL that implements proposed changes to a table's schema.  The Edit Query button is a great way to learn how to use SQL to make changes in tables.   We can use it to create SQL that we will keep on hand for repetitive changes, or as a starting point for queries that we modify.

 

Suppose we have opened the table for our example Mexico drawing, called Mexico Table.   We choose Edit - Schema to launch the Schema dialog and then we choose Add - Index to add a new index to the schema.

 

 

We specify the name Name_x, and regular index (btree) as the type of index.  We check the Allow null values box.  For the field to be indexed, we choose Name, a text field in the table.  

 

We press the collation picker button and choose Edit Collation.  In the Collation dialog's Standard tab we choose the es-MX collation for Spanish (Mexico).   We check the ignore case box.  We press OK to exit the Collation dialog to get the Index dialog display shown above, a typical index choice for a non-English language text field with options to allow null values and to ignore case.  We press OK.

 

 

Back in the Schema dialog the proposed new index is shown in provisional, bluish background color.   We can create this new index in the table by pressing Save Changes or, if we want to see the SQL that would make that change, we press Edit Query to launch the Command Window populated with the SQL that will do the job.

 

 

In the Command Window, we see the SQL which would make the proposed change to Mexico Table.   The SQL is remarkably simple, but if we are new to SQL or new to Manifold we might not have gotten all the nuances right on the first try.  Seeing an example above shows us, exactly, how it's done.    If we want to add the index to the table, we simply press the ! Run button in the main toolbar and Manifold will run the query shown in the Command Window.

 

Note that had we made several other changes, such as renaming fields, deleting fields or indexes or adding a constraint, so that there were several rows of provisional changes in the Schema dialog, when we press the Edit Query button the SQL that is created will be the right SQL to perform all those changes.  

 

We can save the SQL in the Command Window by choosing Edit - Save as Query.   If we routinely make similar changes to schemas, we can save the SQL for doing so to a query so that thereafter all we need do to make those changes is to run the query.

Automatic Cache Reset

Changing the schema for a table may involve changing fields or indexes for which Manifold maintains caches. To be on the safe side, Manifold automatically removes caching directives (normally saved in the properties of a table) for fields and indexes when a schema is changed.  Such caching directives are usually produced by Copy and Paste from data sources that use .MAPCACHE.  That ensures cache will not be wrong.

Notes

External database / Manifold database -  When we write "If the table is in a database" we mean if the table is in an external database, and not within the .map project, even though the world of the .map file is very much a database as well, the native Manifold database system built on  Manifold's immensely powerful Radian parallel database engine.

 

In the illustration above we see a project that has four external database sources, including one, the Bing Maps data source, we might not think of as a database.   The rules which apply to tables and their schemas within such external databases are the rules imposed by the DBMS packages that host those external databases.  For example, GPKG allows only one geometry column per table while PostgreSQL with the PostGIS extension can host multiple geometry columns per table, as can the Manifold database that powers components in the .map file.

 

Nomenclature - In English, schema means "an underlying organizational pattern or structure."   Manifold uses the word schema in a more limited and understandable sense than the formal language definition within relational database theory.  Formal standards use the word schema to mean a set of objects (tables, views, etc.) and data owned by a particular user within a catalog.   Users generally understand a schema in such contexts to be the logical layout of the database as the user sees it, with a list of tables, fields, types of data, constraints and so on.   

 

Manifold uses the word schema to mean the logical layout of a specific table: all of the fields, indexes and constraints, together with the types of fields and indexes and other characteristics that define what that table is, the types of data the table can contain, and any rules which either automatically compute data or which constrain that data.  All that is captured and can be managed in the Schema dialog.

 

Unique Index Names - Manifold allows the same name for an index to be used in different tables, while Oracle and DB2 require the name of an index to be unique throughout the entire DBMS.  If we have linked an Oracle database into our project and we are adding indexes to tables within that database, we cannot add an index named cities_x to a table if some other table in that Oracle database already uses that name.  Instead, the Schema dialog will automatically generate a unique name for that index using a GUID.

 

Changing Case in Names -  Change a name by double-clicking an item in the Schema dialog and changing the name.   Changing myname to MyName will not do anything since the two are considered the same.  We can, however, do a hackish trick to force use of a different case name.   First change myname to myname1 or some other different name.  Click Save Changes to save the changes and close the Schema dialog.  Launch the Schema dialog again and change myname1 to MyName.  That will now be recognized as a different name, so when we click Save Changes to save the changes and close the Schema dialog the field will have been renamed to MyName.  It will still be treated the same as myname for SQL and database purposes, but it will be displayed in tables using the new case, as MyName, which we may prefer for aesthetic or readability reasons.

 

Computed fields - What Manifold calls computed fields are what Release 8 referred to as active columns, an idea related to (but different) from what are called derived fields, calculated fields, or computed columns in some databases. In all cases the value put into the field is computed from expressions that may involve other fields.  Derived fields in databases, though, are computed on the fly within a SELECT query to populate a results table. They are not a persistent part of the original table.  Computed fields in Manifold are a persistent part of a table's schema, much as cells that are part of an Excel spreadsheet that contain a formula that is computed and updated.  We can even create an index on a computed field so long as the table for the computed field is stored in the .map project (and not in an external database).

 

Autogenerated fields - The Autogenerate values option appears when adding integer type fields in tables within external databases that support autogenerated fields.  Most databases support autogenerated fields.  .map files do not, but tables in .map files can always use an  MFD_ID field,  which is a hybrid: MFD_ID fields will autoincrement for a new record if inserted records do not specify a value for MFD_ID.  If a valid value for MFD_ID is supplied, it will be accepted.   

 

When creating a new table or adding new fields to an existing table, the option to create a field as autogenerated is treated as a request to the database. The database is free to ignore the request.  For example, if we ask a database to create multiple autogenerated fields but that database allows having only a single autogenerated field per table, the database may create only a single autogenerated field and create all other fields as regular, non-autogenerated fields.

 

Oracle Spatial Index - Creating a spatial index for a table hosted on an Oracle database uses SPATIAL_INDEX_V2, if available, instead of SPATIAL_INDEX.

 

Geometry types in databases - When creating geometry fields in external databases such as Db2, MySQL, Oracle, PostgreSQL/PostGIS, and SQL Server, choosing a type of geom means using whatever is the native geometry type in the database.  Manifold geom type will be automatically mapped into the database's native geometry type, and vice versa.    If we want to use the database's native spatial capabilities, we should use geom type.

 

Choosing geommfd or geomwkb as a type means choosing a geometry type that is not native to the database.  Data in geommfd or geomwkb type is stored in the database as binary data.  It is interpreted by Manifold as spatial data using Manifold's own metadata, but it is opaque to the database as geometry data, being simply generic binary data as far as the database is concerned.

 

Adding mfd_id fields -  System fields and indexes like the mfd_id field and an mfd_id_x index on that field are 'free' in that they do not add to space requirements and they do not harm performance in any way.  There is no harm done if we use the Add Identity toolbar command to add an mfd_id field and an mfd_id_x index to a table that already has a btree index on a suitable field.

 

mfd_ is reserved - All names starting with mfd_ (case not significant) are reserved for use by the system.  Names for fields, indexes, constraints, tables, components other than tables, properties... everything. Do not name anything beginning with mfd_ or MFD_ or in any upper or lower case combination of those characters.   mfd_id is a reserved name for a field in tables, to be used only for the identity field that Manifold maintains as an automatically unique value field.  

 

Works with SELECT results too - Launching Edit - Schema works with the results table returned by SELECT, same as with a regular table.  This allows us to see the schema of the results table.

 

Using more than five fields in an index - To avoid overly-complex/long dialogs the Schema dialog allows up to four fields to participate in a new index.  If we wanted five fields to participate when creating a new index, we could create a new index using four fields, launch Edit Query, change the SQL slightly to add a fifth field to be used by the index, and then run the query to create a new index using five fields.

 

Why not allow editing the expression of an existing computed field? -  If we would like to change the expression used for a computed field in a table, we cannot just simply launch the Schema dialog and then double-click into that computed field to change the expression.  Instead, we must launch the Schema dialog and create a new computed field with the desired expression.  We then delete the old computed field, and save the schema. Next, we launch the schema dialog again to rename the new computed field to use the same name as the original computed field used.

 

So why not simply edit the expression used for the existing computed field?  That runs into the complexities of how computed fields can be used.  For example, a computed field might be referenced in multiple constraints and indexes, and those might impose constraints of uniqueness or  non-NULL values.   The computed field might also be referenced in one or more other computed fields that are in turn referenced in constraints and indexes.  The values generated by the old expression will have worked in all such references, because they would have been created after the old expression.  But the values computed by the new, edited expression might fail some of these constraints.  Allowing the editing of an existing expression would therefore make it possible to create a perfectly valid expression for the computed field that passes the quick check done on expressions by the Expression dialog, but which would fail in some other parts of the infrastructure of the table, resulting in a table structure that is partially valid and partially invalid.  

 

Manifold could provide user interface infrastructure to deal with such circumstances, but that would require considerably more complex dialogs and special cases that would be harder to learn.   It's much easier on users to use the same, simple dialogs to create a new computed field with the desired expression, and then to delete the old one.

 

Edit Query combines all changes - When we press the Edit Query button the query which appears in the Command Window will incorporate all changes we have made to the schema that are pending.  

 

 

For example, suppose we use the Schema dialog to modify the Products table for the Nwind example database by changing the name of the English Name field to Marketing Name.   Before pressing Save Changes, we also have added a computed field as illustrated earlier in this topic.    If we now press Edit Query, the query that is generated and shown in the Command Window will incorporate both changes:

 

-- $manifold$

--

-- Auto-generated

-- Schema

--

ALTER TABLE [Products] (

  RENAME [English Name] [Marketing Name],

  ADD [Inventory Value] FLOAT64

    AS [[ [Unit Price] * [Units In Stock] ]]

);

 

See Also

Tables

 

Data Types

 

Indexes

 

Command Window

 

Query Builder

 

Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.

 

Example: Add a Field to a Table and Fill It - We add a field to a table and simultaneously for all records fill the field with a specified value.   We do that in two examples, first a very simple one and second, a more elaborate example that also shows restriction to a selection as well as use of the Transform pane.

 

Example: Add a Computed Field to a Table - In this example we add a computed field to a table, illustrating how the computed field automatically changes when changes are made in the fields it uses for computation.   We also show how computed fields can use geometry, automatically updating centroids when areas are changed.  Last, we show how geometry can be created using computed fields, to create effective radius circles for antennas based on the power of the antenna.

 

Example: Add a Second Computed Geom Field to a Table  - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields.   This topic shows how to create a second geom that is a computed field based on the first geom.  The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved.    We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.

 

Example: Expression Context and Computed Fields - When creating a computed field in the Schema dialog, using the Expression Context tab can increase efficiency, legibility, and maintainability of the expressions that power the computed field.

 

Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.  

 

Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.

 

Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field.   This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.