Schema

icon_schema.pngThe 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.

 

A table contains:

 

 

All of these aspects of a table's structure taken together are referred to as a table schema.  The Schema dialog allows us to add and delete fields, indexes and constraints.  

 

ico_nb_arrow_blue.pngThe Schema dialog uses a two-step workflow for safety:  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.

 

il_schema_dialog.png

 

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.  

 

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.

 

ico_nb_arrow_blue.png 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.

btn_schema_add.png Add

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

mnu_schema_add.png

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

btn_schema_add_identity.png 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.

icon_move_top.png Move to Top

Move all selected, newly-added fields to the top of the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

  icon_move_up.png Move up

Move all selected, newly-added fields up one position in the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

icon_move_down.png  Move down

Move all selected, newly-added fields down one position in the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

icon_move_bottom.png Move to Bottom

Move all selected, newly-added fields to the bottom of the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

  icon_move_up.png Move up

Move a newly-added, selected field up one position in the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

icon_move_down.png  Move down

Move a newly-added, selected field down one position in the group of newly-added fields.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields relative to each other.

Order is important:

Ordering of schema items is important for computed fields and for indexes:

  • A computed field can only reference fields that appear before it in the schema.

  • The order of indexes determines which index is going to be used as a primary key when creating a table on a database: if there are multiple candidates, the system uses the first index (the uppermost) in the schema.

icon_delete.png  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.

(gray background color)

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

icon_field.png

Field icon.

icon_computed_field.png

Computed field icon.

icon_projection.pngicon_projection_red.png

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.

icon_index.pngicon_index_gray.png

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.

icon_primary_key.png

Primary key icon in databases.

icon_constraint.png

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.

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.

 

eg_add_identity01_01.png

 

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

 

eg_add_identity01_02.png

 

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.  The 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.

 

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.  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.

 

il_schema_add_field_dialog.pngil_schema_add_field_autogenerate_dialog.png

il_schema_add_field_geom_dialog.pngil_schema_add_field_geom_bounds_dialog.png

il_schema_add_field_geom_z_dialog.pngil_schema_add_field_tile_dialog.png

 

 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.

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.

btn_coord_sys_picker.png  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.

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 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 ].

 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.

  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.

 

 

btn_coord_sys_picker.png For geom and tile types, clicking the coordinate picker button launches a drop down menu that allows choosing Edit Coordinate System 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.

 

mnu_coord_picker_dropdown.png

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.    The query builder in the Expression dialog works like the query builder in the Command Window.   See the Query Builder topic.

 

il_schema_add_field_expression_dialog.png

 

 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.

  (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.

 

il_schema_add_field_expression_dialog_exp.png  il_schema_add_field_expression_dialog_exp_context.png

 

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

 

il_schema_add_field_inventory_value_dialog.png

 

In the example above, we are using 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.

 

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

 

il_schema_add_field_inventory_value_result.png

 

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] ]]

);

 

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:

 

il_schema_add_index_dialog.png

il_schema_add_index_geom_dialog.png

il_schema_add_index_tile_dialog.png

 

 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.

Descending

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

btn_collation_picker.png

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:

 

  • Edit Collation - Launch the Collation dialog to choose a collation or to alter the current collation, for example, by changing options.
  • (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.
  • Edit 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.

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; that is, it requires all non-NULLs to be unique.

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.

 

il_schema_dialog_postgres.png

 

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.

 

il_schema_add_constraint_dialog.png

il_schema_add_constraint_notnull_dialog.png

 

 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.

  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.

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.    The query builder in the Expression dialog works like the query builder in the Command Window.   See the Query Builder topic.

 

il_schema_add_constraint_expression_dialog.png

 

 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.

  (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.

 

il_schema_add_constraint_expression_dialog_exp.png  il_schema_add_constraint_expression_dialog_exp_context.png

 

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

 

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 broader, SQL portability.   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 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.

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:

 

il_schema_query_results.png

 

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:

 

il_schema_query_results_schema.png

 

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.

 

il_queries01_08.pngil_queries01_09.png

 

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.

 

il_schema_tooltip_dialog.png

 

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.

 

eg_schema_edit_query01_01.png

 

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.  

 

btn_collation_picker.pngWe 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.

 

eg_schema_edit_query01_02.png

 

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.

 

eg_schema_edit_query01_03.png

 

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.

 

il_external_and_mfd_databases.png

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.

tech_ravi_sm.png

 

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.

 

Computed fields - What Manifold calls computed fields are similar to what are called derived fields, calculated fields, or computed columns in some databases.

 

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.

 

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.

 

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.    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 then renaming the new computed field to use the same name as the original computed field used.

 

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.  

 

eg_schema_edit_query_combines01_01.png

 

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

 

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

 

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 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.