Schema

icon_schema.pngThe Schema dialog becomes available in the Edit menu when the focus is on a table window.   We can also launch the Scheme dialog by right-clicking on a table in the Project pane and choosing Schema in the context menu.

 

A table has one or more fields with each field being a particular data type.  A table can also have zero or more indexes and zero or more constraints. All of these aspects of a table's structure taken together are referred to as a table schema.   We can edit schemas using the Edit - Schema dialog.

 

Everything in a schema has a name and each name must be unique in the schema.    For example, we may not have a field named x and an index named x in the same table, but we may have a field named x in one table and an index named x in a different table.   We can also have indexes named x within several different tables in the same database.   The names of indexes do not have to be unique within a database in Manifold.

 

 

il_schema_dialog.png

 

The Schema dialog displays the structure, that is, the schema, of the table.   It provides a list of all fields and indices as well as their types.   It allows us to add new fields, to populate those fields when they are created, and to create new indexes of various types.

Commands

Click on an item in the schema to highlight it.

 

  icon_move_up.png Move up

Move a newly-created, highlighted field up in the list.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields.

icon_move_down.png  Move down

Move a newly-created, highlighted field down in the list.  Enabled only when adding more than one new field, to allow setting the order of newly-added fields.

icon_delete.png  Delete

Delete the highlighted item.

icon_field.png

Icon used to show fields.

<new field>

Click to begin adding a new field.

icon_index.png

Icon used to show indexes.

<new index>

Click to begin adding a new index.

icon_constraint.png

Icon used to show constraints

<new constraint>

Click to begin adding a new constraint.

 

Indexes

In general, a table must have a btree or btreenull index to be editable.   It is a good idea to have a btree index in a table.  In general, if a drawing's table does not have an rtree index on the geometry field the drawing will not be able to display the objects.  See additional notes in the Indexes topic.

 

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.

rtree

A spatial index that is a balanced tree structure utilizing bounding rectangles or boxes.

 

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.

Schemas and Queries

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

 

We cannot change 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 the native data sources.   We can see the schema for such a query by right-clicking the view and choosing Schema.

 

Notes

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.

 

See Also

Tables

 

Data Types

 

Indexes

 

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.