Subscribe to this thread
Home - Cutting Edge / All posts - Manifold System

9,470 post(s)
#29-Mar-19 17:03

SHA256: bbcf36cc5006b53f0d797621980e34b27a66b8d93d57abe0c4bd68a05f333a59

SHA256: cd0ef5fd9be40a2a79144bee34b92014cfa8c3599f0ca0923bfdae53cc327792


9,470 post(s)
#29-Mar-19 17:07


Simplified vertical query builder used in the Expression tabs in the Select and Transform panes joins available fields and SQL operators / functions into a single list to make better use of screen space.

The New Field button in the Options tab of the Style pane used to add a new field to store custom styles is changed to invoke the Schema dialog. Altering table schema keeps the Style pane in the Options tab (was switching to the tab with formatting).

The Schema dialog is reworked to show fields, indexes and constraints using a grid control with a toolbar on top. Grid row for a field displays field name, field type and an optional expression used to compute the field (computed fields only). Grid row for an index displays index name, index type and a list of used fields. Grid row for a constraint displays constraint name, constraint type (boolean) and an expression used to compute the constraint. To edit an existing field, index or constraint, double-click it.

The Schema dialog allows adding new fields, indexes or constraints using the Add toolbar command. The command displaus a drop-down menu with separate commands for adding a field, index or constraint. If the table schema is readonly, all commands are disabled. If the database does not support adding indexes or constraints, relevant commands are disabled. Shortcut: pressing Insert in the grid starts adding a new field.

The Schema dialog allows deleting selected fields, indexes or constraints using the Delete toolbar command. If the table schema is readonly, the command is disabled. (To select items, use Ctrl-click or Ctrl-Space or any other selection method available in the table window.) Shortcut: pressing Delete in the grid deletes selected items.

The Schema dialog allows reordering selected fields, indexes or constraints using the Move to Top / Move Up / Move Down / Move to Bottom toolbar commands. If the table schema is readonly, the commands are disabled. When editing the schema of an existing table, only new items about to be added to the schema can be reordered. (Reordering of schema items is important for fields and indexes. The order of fields determines which fields can be accessed by computed fields: a computed field can only reference fields that appear before it. The order of indexes determines which index is going to be used a primary key when creating a table on a database: if there are multiple candidates, the system uses the first one.)

The Field dialog displays expression and expression context for a computed field using two multiline edit controls. Expression text can be scrolled, selected and copied even if the expression cannot be edited. The Edit Expression button invokes the Expression dialog which edits expression and expression context using a separate query builder for each. The expression is only allowed to use fields that appear before the edited field in the schema. The expression context is not allowed to use any fields (statements in the expression context are evaluated once per field, not once per record). If the database does not support computed fields (only MAP files do), the Edit Expression button in the Field dialog is disabled.

The Field dialog allows specifying coordinate system for geom and tile fields.

The Field dialog allows specifying bounds for geom fields if requested by the database. (Some databases need to know bounds in advance to allow creating a spatial index.)

The Field dialog allows specifying Z values option for geom fields if requested by the database. (Some database need to know whether the geom field will have XY or XYZ values in advance.) If the database requests both Z values and bounds, bounds can (but don't have to) include Z range, specified as '[ xmin, ymin, zmin, xmax, ymax, zmax ]'.

The Field dialog allows specifying pixel type, tile dimensions and tile reduce mode for tile fields.

The Field dialog allows specifying autogenerated option for integer fields if supported by the database. (Most databases support that. MAP files don't, but tables in MAP files can always use MFD_ID field which is a hybrid - it will autoincrement for a new record if the inserted values do not include the value for MFD_ID, and if the 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 is treated as a request to the database. The database is free to ignore the request, eg, if it only allows having a single autogenerated field per table, it may only make a single field autogenerated and make all other fields regular.

The Constraint dialog displays expression and expression context for a computed field similarly to the Field dialog. The expression for a constraint is allowed to use all available fields. The expression context for a constraint is not allowed to use any fields.

The Constraint dialog for a database allows specifying a not-null constraint for a field, as long as the database supports not-null constraints (most databases do). (We only allow specifying constraints as arbitrary expressions for MAP files. However, not-null constraints are very common and very useful, so we started recognizing them separately.)

The Index dialog displays index type as one of three choices: 'regular index (btree)' (supported in MAP files and on all databases), 'spatial index for geometry (rtree)' (supported in MAP files and on most databases), 'spatial index for tiles (rtree)' (MAP files only). Creating a btree index includes explicit options to allow duplicate values and nulls, and allows specifying up to four fields (all used fields have to be different, the order of fields matters, but specifying fields 2 and 3 while leaving field 1 blank works). Creating a btree index on text fields allows specifying the collation for each. Creating an rtree index for geometry allows specifying the geometry field. Creating an rtree index for tiles allows specifying the X and Y fields and the tile field, plus options for the tile field (pixel type, tile dimensions, tile reduce mode). Selecting the tile field automatically loads options specified for that field in metadata, including any changes to it made in the context of the Schema dialog that have not yet been saved.

Editing the collation for a text field in the Index dialog lists collations available on the database in a separate tab. (There are two tabs added: the Database tab lists collations exposed in the MFD_COLLATE table, and the Custom tab allows copy / pasting collation definition as plain text.)

The Schema dialog highlights changed fields, indexes and constraints using bluish background. New fields, indexes and constraints also display an 'added' icon in the row header.

The Schema dialog includes the Edit Query button which shows changes about to be made as a query.

The Schema dialog strips properties with caching directives for fields and indexes (commonly produced by copy and paste from data sources that use .MAPCACHE) on first change to the schema.

Deleting fields in the Schema dialog automatically deletes indexes and not-null constraints referencing these fields, after a confirmation. (When deleting a selected field referenced by an index, if the index is also selected, the dialog silently deletes both. If the index is not selected, the dialog displays a confirmation regarding deleting the index. If the user confirms the deletion, the dialog deletes both the field and the index. If the users cancels the deletion, the dialog does not delete anything, and selects the index.)

The Schema dialog allows adding identity field and index using the Add Identity toolbar command. In a MAP file, the command adds the system MFD_ID field and the system MFD_ID_X index regardless of what other indexes the table already contains (system fields and indexes are 'free' in that they don't add to the space requirements, don't harm the performance in any way, etc). If the table already contains the field and / or the index, the command selects them. In a database, the command checks if the table contains a btree index (no duplicates, no nulls), and if so, selects that index and the field or fields it uses. If the table contains no btree index, the command add a new field and a new btree index on that field. If the database supports creating autogenerated fields, the field is made autogenerated.

The Create Drawing / Create Image / Create Labels / Create Table dialogs ran on a database create autogenerated identity fields if the database supports them.

The Schema dialog displays a coordinate system icon for geom and tile fields: red if the coordinate system is missing, black otherwise.

The Schema dialog displays an index icon for fields that participate in indexes: dark blue if there is an index where the field is the 'main' field (geom or tile in an rtree index, a single field in a btree index of any type), gray if the field is not the 'main' field in any index.

The Schema dialog displays a key icon for a primary key index in a database.

(Fix) Unchecking View - Filter - Filter Fetched Records Only on table with computed fields no longer results in no records.

(Fix) Right-clicking a blank space in the Project pane no longer sometimes (rarely) crashes if there are no selected items.

(Fix) Retrieving schema of a materialized view in PostgreSQL no longer sometimes fails.

(Fix) Exporting to SHP no longer writes incomplete bounding box into the file header (a regression).

(Fix) Exporting to DBF (including as part of SHP) no longer sometimes misplaces data for text fields (a regression).

Exporting to SHP autosizes text fields in DBF. (Previously, the autosizing was working only for exporting standalone DBF.)

Reading DBF interprets all-space text values as NULLs.

Pasting or importing drawings to GDB automatically splits geoms of different types into separate drawings, similarly to export.

Working with Oracle databases supports collations for text fields. Recognized collations are listed in the system MFD_COLLATE table.

Working with DB2 databases supports collations for text fields (can use them during reading, but cannot create indexes with them, due to current limitations of latest versions of DB2 - there is migration to doing everything in Unicode and it is not yet complete). Recognized collations are listed in the system MFD_COLLATE table.

Creating a spatial index on an Oracle database uses SPATIAL_INDEX_V2 (instead of SPATIAL_INDEX) if it is available.

Oracle, PostgreSQL, SQL Server and other database dataports use native names for indexes. (Previously, most index names were synthesized dynamically from field names and the dataport was mapping back and forth.) When creating a new index, if the desired index name is already in use (this happens frequently because index names in MAP files are unique within table and index names on regular databases are unique within database), the dataport synthesizes a new name using a GUID.

End of list.

Also, here are the latest versions of databases that we support: MySQL 8, Oracle 18, PostgreSQL 11. We have a number of small changes related to support for these newer versions, they didn't make the list above because they are too small and too technical. :-)


9,508 post(s)
#29-Mar-19 19:52

I was quite fond of the old Schema dialog, and didn't know whether I would like it to change...

But the new dialog is beautiful, an absolutely fine thing. Looking back at Edit Schema in, the old version immediately feels... old.

In typical Manifold fashion, you seem to have thought of everything. My favourite little thing is this.

The Schema dialog allows adding identity field and index using the Add Identity toolbar command. In a MAP file, the command adds the system MFD_ID field and the system MFD_ID_X index regarding [small typo: should be 'depending'] on which indexes the table already contain[s] ... If the table already contains the field and / or the index, the command selects them.

It's that last thing. Why make a button have just one function when it can have two functions that feel like one? Small things like this really add up.

In the same way the Index dialog goes a long way to making index types feel less mysterious--it does some useful explaining just in the layout and naming of the various tools.

Everything is visually clean and informative, a pleasure to use.

One thing worth mentioning I think is that, while we can add up to four fields to a new BTREE index via the Index dialog, that is not a hard limit. It's rare enough to want to add four fields to an index, but more than four is not necessarily a mistake. (After all, the SQL engine itself will do this behind the scenes, if we GROUP by more than four fields.) If we do that, the easiest way is to set as many options as we like in the Schema and Index dialogs, then press Edit Query, and add the extra fields to the standard query text. Not often needed but simple. Four seems a good balance for the dialog.


9,508 post(s)
#29-Mar-19 21:24

Typo in my last para:

If we want to do that, the easiest way is...


9,470 post(s)
#30-Mar-19 08:12

Thank you, much appreciated!

The typo in the quote is even worse: "regarding on" should be "regardless of", as in, for a table in a MAP file, Add Identity is going to add MFD_ID / MFD_ID_X even if the table already has a btree index that can be used as identity - because MFD_ID are free and in some ways, better. I'll fix the original text.

Also, regarding btree indexes on more than four fields - yes, they can be added using Edit Query, the limit of four fields in the dialog is just a trade-off to make the UI fast and simple for the 99% case - but overall, we find that when you want an index on more than four fields, that's very often a special case of wanting an index on all fields, to make all records unique. We might specifically provide for that special case in the future - maybe add a new choice to the index type in the Index dialog, etc.


9,508 post(s)
#29-Mar-19 19:56

I would be interested to know the reason behind this:

a computed field can only reference fields that appear before it


9,470 post(s)
#30-Mar-19 08:03

This avoids loops. If field A depends on B, field B cannot depend on A, neither directly, nor through other fields. Requiring that a computed field only references fields that appear before it in the schema is a good way to visualize it without really losing flexibility.


9,508 post(s)
#30-Mar-19 20:45

Simple and clever. Like a little ratchet. Thanks.

129 post(s)
#30-Mar-19 15:05

Many thanks for the improvements on the Schema dialog. I love those getting more M8 feeling in the use of space and boxes. Just a question. Why once validated and saved an expression can't be edited. Is there a internal constraint to that?


9,470 post(s)
#01-Apr-19 10:24

You can edit the expression / expression context of a new field as many times as you want but only until you save changes and add the field to the table. Once the field is added to the table, you can no longer edit its expression because:

(a) there might be indexes based on that field (if the expression changes and the field starts returning new values, they will have to be rebuilt and that might not succeed, eg, an index might require no nulls and the new expression might produce nulls for some records),

(b) there might be constraints based on that field (if the expression changes, they will have to be revalidated and that might not succeed either), and

(c) there might be other computed fields based on that field (if the expression changes, they will have to be recomputed and this, in turn, will have to rebuild indexes / revalidate constraints / recompute other fields, recursively, with parts of that potentially failing).

We could still allow changing the expression of an existing field and do all of the above automatically, but since that would work more or less like rebuilding the table, we aren't making this easy, it's just not a very natural operation for the data. We might make some adjustments in the future and, say, allow changing the expression of an existing field when it is not referenced by constraints / indexes / other fields - although in that case it is already easy to just delete an existing field, save changes to allow reusing the old name, then add a new field with the same name as before and a new expression.

129 post(s)
#01-Apr-19 21:02

Clearly explained and obvious on the point of view of the structure integrity, without relying on human thinking of what is clever to do or not. No problem for me with that as I can live with the delete replace approach.

Just a little suggestion that might be discussed here. In the wonderful transform tool, I would take profit of a new way to use it being "add a new static or dynamic field". My explanations: this tool is great help to compose expressions and test dynamically the results. The only gray dots (not that black) along the process today is that we have to manually add the target field and then transform it to get new values but static. My concerns :

1 creating the field as it might be created automatically by the transform, like in any query INTO, and

2 only static fields are possible

Therefore I would love the possibility to use the transform box in order to add a new field with the defined expression, being at the end a dynamic field.

Hope to be clear on the idea.


9,470 post(s)
#02-Apr-19 10:06

The idea is clear. We used to be able to add the result of a transform as a computed field in some of the previous builds. We stripped that out because of the UI concerns - adding a new field and composing a transform are both UI-heavy tasks and we didn't really want to mix them together (didn't want to have too many controls yet didn't want to drop anything either). After changes and additions to the schema dialogs we might get back to this topic and try to allow making the result of a transform a computed field.

Mike Pelletier

1,814 post(s)
#03-Apr-19 19:34

I'm no database wiz but the new schema dialogue is really great. A couple of thoughts.

Could there be a button added that allows copy/paste (name appended automatically) of an entire field, with the idea that there are many settings/expressions involved for each field and it would speed the delete replace approach to editing or for making similar fields.

No rush, but hope the lookup field option (dropdown list of allowed entries) is contemplated for 9 with an easy way to edit the list of allowed entries.

162 post(s)
#04-Apr-19 05:48

It's nice to see that others ask for the lookup field option too. It has been on my wish list (both on tables and on the record pane) for years and a I had send a suggestion for M9 (and M8 years ago). Having it would help save a lot of time and avoid errors when manually entering data, especially when using Manifold on the field on a laptop to collect data.


6,386 post(s)
#04-Apr-19 07:02

Hm, on the DB level constrains are the tool to restrict input of allowed values.

You ask for an early support on the level of input controls. MS-Access has combined both but IMO is restricted to the Access DB and its controls.

Is it possible to analyse constrains of all the DBs that Manifold supports in a way that could be used automatically to suggest valid input? I guess you can implement an event Subobject_Change() for controls in your special application. But I doubt that it's possible to offer a general solution on the UI level.

Politics is the art of making the impossible unavoidable


6,364 post(s)
#04-Apr-19 08:49

You're right in that a constraint is the relevant part of the database, and that something else, a GUI, is how that could be manifested for picking from a drop down list. But the use of GUIs to do something interactively in a DBMS that goes beyond using UPDATE and so on is routine.

For example, the Schema dialog is a GUI feature that implements in a point and click way what one would write in SQL (click Edit Query for an example of such SQL). The Add - Index sub-dialog of the Schema dialog is another GUI feature, where a drop down list can be used to choose a Field to be indexed.

There is no reason why data entry GUI facilities, like the ability to edit field values in a Table grid or in the Record panel grid of the Contents pane, could not add features that present a drop down list of allowed values, if that is the constraint on that field.

The only question is how to automate that in a way where the table can have a constraint that is reasonably portable to other DBMS packages, and how the Manifold-specific GUI feature should read that constraint to create a drop down list of allowed values, possibly within the constraint expression itself or possibly pulled from some lookup table (thinking about normal form, after all..).

You could design that so the Manifold UI would work against any of the data sources closely supported by Manifold. There are a variety of Rube Goldberg ways of doing it that come to mind after a few moment's thought, but no doubt Engineering would come up with a more elegant way.


6,386 post(s)
#04-Apr-19 08:55

I guess that it is possible to create a general function to check if a given value satisfies any constrain. It just returns true or false.

But that is very different from providing a lookup table of all valid items for any type of constrain of every supported DB. Constrains are not restricted to lists of valid values and that is where my limited understanding of engineering hits a wall.

Politics is the art of making the impossible unavoidable


9,470 post(s)
#09-Apr-19 09:10

The values of a field can be restricted to a known subset using a constraint, yes, eg:


CREATE TABLE [cities] (

  [mfd_id] INT64,

  [city] NVARCHAR,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  CONSTRAINT [city_valid]

    AS [[ [city] IN ('London''Berlin''New York') ]]


...however, recognizing constraints of that type in databases is hard (the syntax is different, most of the code is in triggers which frequently include other logic, etc).

We are planning to add means to select field values using a combo, but that won't rely on constraints, it will just be an UI feature.

HMS122 post(s)
#12-Apr-19 14:17

[removed - please follow forum guidelines]


6,386 post(s)
#05-Apr-19 21:34

The german UI-file for version


Politics is the art of making the impossible unavoidable


9,470 post(s)
#09-Apr-19 09:12

Status: we are looking to issue the next build somewhere near the weekend. The focus is on the UI.


6,364 post(s)
#12-Apr-19 07:57

Quick update: This next build is getting expanded to improve the UI to provide much smoother visual refreshes. 9 now blinks when a window gets re-rendered, for example, when you select an object you get a blink before a total redraw that now shows the selected object in red selection color. The blink is a side effect of many different things, one of them, for example, being a sync when possibly many different threads are doing updates.

Altering that for blink-free redraws involves very, very far-reaching work throughout the system, including many different modules that have to get done right to avoid rendering errors or any performance hits. It's going to push back the build a week, maybe a few days more. I think it is worth it.

By the way, why this is being dealt with now arises from groups and legends. Legends are (will be) basically constructions of grouped elements, using the same orthogonal internal structures as grouped frames, grouped layers and so on. So to do Legends right the grouping has to be done right. That's why groups bubbled up to now. But when rendering grouped elements, each such render can trigger a blink: if not done right you could get a lot of blinks to render just one legend... not acceptable. That's why smoother, blink-free (or greatly blink minimized) has become a "now" thing in this build.

The extra time for this build also allows more items from other work to be incorporated, as all other work besides unblink continues to keep steaming on.

626 post(s)
#12-Apr-19 08:33

...arises from groups and legends...

That's great news. Legends and scale bars are much anticipated and will be much appreciated.

438 post(s)
#12-Apr-19 16:17

Very awesome, even as I'd become accustomed to the blink. Good riddance, though.

Interested to see the "groupings" idea in action. Adding "maps" to maps? Manifold still my go-to GIS product, every day. Thank you for continuing to push this product forward.

789 post(s)
#29-Apr-19 15:08

Anticipation is growing for this next release. Well, it's not Game of Thrones level of anticipation, but the way you worded your comment, the longer it takes to resolve the unblink issue, the more features apparently will be added. I'm not sure I can handle the next update.


6,364 post(s)
#29-Apr-19 16:15

Should be today.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.