Example: Add a UUID-based Index to a Table

The most frequent way of adding an index to a table that does not yet have one is with a single click of the Add Identity command in the Schema dialog, to instantly create an mfd_id field and to then create an mfd_id_x index on that field.  The mfd_id field is treated specially by Manifold to always contain unique values in a table within a project.   That process is described step by step in the Adding an Index to a Table topic.


However, if we want to copy records between tables from different projects there is no guarantee there will not be duplicate values in the mfd_id fields for different records from different projects.  An mfd_id value is unique per table, but not between different tables in the same project, and not between tables in different projects.   We can deal with that using a variety of easy SQL methods, but sometimes it is more convenient to, as Ken Thompson, the genius who created UNIX once remarked, "When in doubt, use brute force."


One way to guarantee that an identity field that is used for an index will always have a unique value for each record no matter where in the universe it may go is to use a UUID field.  A UUID field is a Universally Unique IDentifier, a 128 bit value represented by a text string of lower-case hexadecimal digits in standard form of groups of digits separated by hyphens.   As a practical matter, we can count on a UUID field value being unique no matter how many tables we create.   We can add a UUID field to our table, that is created as a computed field so that it is filled with guaranteed unique values on creation.  If we want a guaranteed unique identity field, using a UUID is definitely a brute force way of getting our way.


Tech tip: In most cases we will add an index by adding an mfd_id field.  The process in this topic is not frequently done, but because it is useful sometimes and because it teaches more about the system it is worth studying.

Using a UUID Field with an Index

We can use any field that contains non-NULL, unique values as the basis of a btree index.   When we have a table that has no such fields in it we must add such a field to the table, both adding the field and also populating it with unique, non-NULL values at the same time.  


We must create the field and also populate it with unique, non-NULL values at the same time because if we only created a new field using the Edit - Schema dialog without also populating it, the new field would be added to the table with NULL values for all records.  Because of the NULL values it would not be usable as the basis of an index.   Because of the continued lack of an index the new field would not editable either, so we could not replace the NULL values with unique, non-NULL values in each record.


The resulting dilemma is nothing new to DBMS people who routinely must deal with tables in almost all standard DBMS products that cannot be edited for lack of an index, but which do not have a suitable field in them to use for an index.   The solution is to add a suitable field and to simultaneously fill it with unique, non-NULL values.


Manifold makes that easier than some systems because the Edit - Schema dialog allows us to create a new field and also at the same time to fill it with the value of a computed expression, that is, to add a computed field.  In this case we want to use a computed expression that will fill the field with a guaranteed unique, non-NULL value for each record.   See also the Example: Add a Computed Field to a Table topic.


One approach might be to use a computed expression that evaluates to a random number.  However, even though random numbers might indeed be so pseudo-random they are not likely to repeat there is no guarantee that the output of a random number generator will not repeat.    A better way is to use UUID (Universally Unique Identifier)  values, which will be unique.   Manifold makes that easy by providing the UuidMakeNew() function in SQL.   


This example shows how to create an expression that uses an SQL function.   That is fine for teaching how to use the Expression template, but in real life if we wanted to load a table with random numbers we would just use the Transform - Numbers: Random  template.


 Another approach is to fill the UUID field with random UUID values.   To do that, we can use the Transform - UUID: Random  template.  This topic was written before the Random template was available for UUID values, but as written it is still a useful example of creating computed fields and using them.

Add a UUID Computed Field


Consider the above table, which we have imported from a CSV file, a format that provides no indexes.   As imported the table has no index so the light gray background indicates that the field values cannot be edited and records cannot be selected.   To allow either selection or edits we must add an identity field and a btree index.  The easiest way to do that is the method shown in the Adding an Index to a Table topic.    We will do it differently in this example, adding and using a UUID field.


If the table had a field with unique, non-NULL values already in it we could use that field to create an index.  But this table contains only two fields and both of those fields have NULL values for some records, which means neither of the two fields can be used to create an index.  We must add a new field that contains non-NULL values and only then can we use it to create an index.  


We choose Edit - Schema to launch the Schema dialog.   We will add a new field of type UUID to the table and simultaneously populate it using the UuidMakeNew() function.



 Click the Add command in the Schema dialog toolbar.



Choose Field in the drop down menu.



We give the new field the name U.   We choose uuid in the Type box.   To enter an expression which will make the new field a computed field, we press the Edit Expression button.



The Expression dialog provides an expression builder that simplifies building expressions.   We enter UuidMakeNew() into the expression pane.  This tells Manifold to create the field as a computed field, automatically computing that expression to get the value of that field for each record in the table.   We press OK.



Back in the Field dialog we see the new expression has been added to the Expression pane.  We press OK.

Add an Index on the New Field

The new field, called U, appears in the Schema dialog using provisional, bluish color to indicate the change has not yet been committed.   The new field U appears in the schema listing with a small f as part of its icon to indicate it is computed with an expression.  The field is type uuid, and the expression appears in the field row.  



We now add a new index using the newly created field.  Press Add in the toolbar.



We choose Index from the drop down menu.



In the Index dialog we give the new index the name U_x.   We choose regular index (tree) in the Type box.   We choose U in the drop down menu for the first Field box.  Click OK.



The Schema dialog shows the proposed field and index to add in provisional, bluish color.  If we like what we see, we can commit the changes to the table by pressing OK.



The result is that our table acquires a new computed field, called U, which in the act of creation was also populated with unique, non-NULL values for each record.   The index we created on that field makes the table editable and selectable, as the white backgrounds of the DARMC and F3 columns indicate.  The U column retains a light gray background indicating it is not editable since the values in that column are automatically created by a computed expression using the UuidMakeNew() function.


That those values are long and meaningless UUID values does not matter: that field's sole purpose is to provide a convenient, guaranteed unique, identity field for an index so the table is editable and interactive selections can be made.    For that purpose a btree index using the U computed field is fine.  If we do not want to look at the U field in our table we can hide it using the Layers pane.


Why special treatment of mfd_id?   Manifold is often used with spatial data that originated in GIS or other systems which do not take as rigorous an approach to database matters as do enterprise class DBMS applications.   It cannot be taken for granted that such data has indexes.  Much data used in the GIS world does not.  To make it easier to apply sensible DBMS capabilities within Manifold, the system makes use of a special mfd_id field to provide a unique identifier for records.  Manifold also uses an index on mfd_id called mfd_id_x.   This makes it convenient always to have an automatically-created unique identifier for records and an index on that identifier.


Why use an UUID field for an index instead of mfd_id?  UUID values are guaranteed to be unique always so that records can be transferred between projects and tables even on different machines.   A mfd_id value is unique per table, but not between different tables in the same project, and not between tables in different projects.


How to find the UuidMakeNew() function? If we do not remember the name of the function we can always look it up in the Query Builder tab of the Command Window. Enter uuid into the filter box for the list of functions and right away the list will be reduced to make the function obvious.


Is a UUID really unique?  Close enough.  If we generated a UUID value every second for one billion years, we would have a 99.9% chance of not creating even a single duplicate.  If we plan on living for a billion years, our odds of getting wiped out by an extinction event asteroid impact are greater.  Billion year old people should worry more about losing life the dinosaur way than about experiencing a UUID collision.


See Also



Data Types


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.


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.