DBMS Data Sources - Notes

This topic that captures technical notes from engineering on using DBMS packages such as Oracle, PostgreSQL, SQL Server, and DB2, including ESRI SDE utilized within databases.  Notes may also refer to other, non-server DBMS packages, such as GPKG.  Notes provided here may be difficult to find in other topics or not yet incorporated into other topics.  They are provided here so search engines can find them.

Specifying ports when connecting to DBMS data sources

When connecting to a DBMS we can specify the port to use either in the connection string or within the name of the server after a colon.   For example, when connecting to a PostgreSQL server we can write for the name of server:

 

localhost:5433

 

The dialog will know we are connecting to PostgreSQL, a database that allows explicit specification of a port to use for the connection, and it will split out the port number from the name automatically.   Alternatively we could explicitly specify the port as part of the connection string, in the form:

 

{ "Source": "host=localhost port=5433 user=postgres password=xxxx dbname=xxxx" }

 

Identity Fields

Views on PostgreSQL and other databases allow specifying a field to use as an identity field. Doing this allows records in the view table to be selected, and, if the view supports that, edited or deleted. To specify a field to use as an identity field, right-click the field header and choose Use as Identity. The system will scan all values in the field to make sure there are no duplicates or NULLs and if the values are clean, the table will expose a BTREE index on the field. The information about the field used as an identity field is saved into the database, so attempting to use the table in future Manifold sessions will automatically expose a BTREE index on that field without further scans. The identity option can be cleared or moved to a different field. The view can have only one identity field specified in this way.

 

Using a view with the identity field specified via the Use as Identity command in the table window monitors reads via the index. If the system detects that some of the values in the identify field are duplicates or NULLs, it automatically removes the identity option from the field and reports the removal in the log window.

 

VARCHAR and NVARCHAR Data Types for Databases

Dataports for databases expose all text fields as NVARCHAR (Unicode) even if they are stored as VARCHAR.   Attempting to create a VARCHAR field will create it as VARCHAR on the database, but the field will look like an NVARCHAR data type in Manifold.   What VARCHAR means varies between data sources and converting between different meanings frequently loses data, so this convention by Manifold helps to preserve data.  

 

Consider an example:  In Manifold, VARCHAR means 'characters in the currently active codepage, whatever that currently active codepage may be.'   However, in databases VARCHAR usually means 'characters in the codepage associated with the field / table / database'.    If we connect to a database, we can ask it to return data as VARCHAR, and the database might return data in a German codepage if a German code page was used for that field.  If we then try to use that data as VARCHAR in Manifold on an English system, the characters will be interpreted wrongly.  That will affect both correct display of the characters as well as comparisons and orderings.   

 

Different client systems use different ways of dealing with such issues.   Manifold 8, for example, allows setting a codepage for the field.  However, the end result of such adaptations is to convert the data in all codepages different from the default code page into Unicode and then handle data as Unicode.  If Unicode ends up being the intermediate form, it is better, as Manifold now does, to simply do the conversion as close to the data as possible.  

Geometry Collections

Reading geometry collection values automatically merges individual values of the same underlying type used in Manifold geometry, such as area, line or point, with differences between subtypes such as line and multiline being ignored.   The result of the merge is returned.   This applies to all data which support geometry collection values, including WKB, GeoJSON, JSON, native geometry in database-specific formats, and so on.

 

Reading geometry collection values with individual values of mixed underlying types automatically converts areas to lines and lines to points in order to return all coordinates. Example: reading a geometry collection with an area and several points will return a multipoint containing all coordinates of all individual values.

Collations

Manifold supports collations used within ICU, a library for all things Unicode developed and maintained by IBM.  ICU is used by many, if not all, significant applications that support Unicode, including some DBMS products.  Manifold implements custom collations currently used by the more common and more popular DBMS products, such as: 

 

 

System Data Table within Data Sources

When creating a data source on an external database, for example, in PostgreSQL or Oracle or some other DBMS, Manifold creates a virtual System Data folder that hosts two to four virtual tables, created on the fly for housekeeping.  These tables appear to be part of the data source, but do not exist within the data source and instead are created on the fly for our convenience as if they were in the data source.  They allow treating various external databases as if they were native Manifold components.  

 

An mfd_meta and mfd_root table are always in such a virtual System Data folder. Usually an mfd_srid table listing coordinate systems as spatial reference identifiers also appears, if the database uses SRIDs.    In addition, if the database supports collations an mfd_collate table appears, listing all the collations available in the database.

The mfd_collate System Data Table

When connecting to a DBMS, the database dataport will list available external collations in an mfd_collate table appears within the System Data folder in the data source.  Each collation has a unique name and a definition that can be used in a query. Unsupported collations have NULL definitions.

 

eg_mfd_collate01_01.png

 

For example, creating a new data source from a new PostgreSQL installation we will see the System Data folder as above. In addition to the usual mfd_meta and mfd_root tables, it also contains an mfd_collate table.  These are virtual tables maintained by the dataport and not actually created within the PostgreSQL database, even though they appear within the PostgreSQL data source.

 

eg_mfd_collate01_02.png

 

If we open the mfd_collate table we see it lists three available external collations, named pg_catalog.POSIX, pg_catalog.C, and pg_catalog.default.   The collation definitions we can use in Manifold queries are given in the Value field for each collation.  The table uses gray background to indicate it is read-only.

 

See the Collations topic for details on collations.

The mfd_srid System Data Table

When connecting to a DBMS that uses spatial reference identifiers also appears, an mfd_srid table listing coordinate systems as spatial reference identifiers will appear within the System Data folder in the data source.

 

eg_mfd_srid01_01.png

 

For example, creating a new data source from a new PostgreSQL installation that has had PostGIS extension installed, we will see an mfd_srid table within the System Data folder as shown above. This is a virtual table maintained by the dataport and not actually created within the PostgreSQL database, even though it appears within the PostgreSQL data source.

 

eg_collation_postgresql_mfd_srid.png

 

If we open the mfd_srid table we see it lists SRIDs within the database, many of which have been derived from EPSG codes and definitions.  The table uses gray background to indicate it is read-only.

 

See the Projections topic to start drilling into coordinate systems / projections.  

 

Miscellaneous

 

Notes

'Database' vs 'data source' - In Manifold documentation and build notes, these two terms are frequently more or less the same thing. Data source is a collective name for files,  databases, or web servers which provide data.   When we start Manifold and create a new .map file, we set that .map file to be an unnamed root data source, 'root' because it is a root of a potential data source tree. When we link a SHP file, we do so by creating a new data source component, that is, a new data source.   That data source behaves like a database in that it exposes data through tables, and it has system tables with reserved names which describe its contents. The object representing an opened data source for a script is usually called Database, and in other ways data sources are treated as databases within Manifold.

 

Compacting Data Sources - If a data source can be compacted, right-clicking on that data source in the Project pane will show a Compact context menu command.  If the data source does not support compacting, the command will not appear.  In addition to the context menu Compact command that can be used manually, the Manifold object model includes means to compact a data source, to check whether a data source supports compacting, to check whether a data source has data to compact or has unsaved changes.

 

Constraints - The Constraint dialog in the Add - Constraint choice of the Schema dialog allows choosing a not-NULL template for a field, as long as the database hosting the table supports not-NULL constraints, as most databases do.

 

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.  See the Schema topic.

 

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.

See Also

Collations

 

Schema

 

Project Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

Microsoft Access and Manifold ODBC

 

Connect to MySQL

 

Connect to SQL Server

 

Connect to PostgreSQL

 

Connect to Oracle

 

Connect to Db2

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines