When connecting to a DBMS data source, Manifold automatically generates virtual components, such as folders, to provide a consistent user interface that matches the Manifold .map portion of the project. When uploading data into the database, some real components, such as tables, can be created while other components, such as drawings, that appear to have been uploaded into the database are virtual components that are generated on the fly by Manifold based on metadata information.
Real components are tables, views, and some queries. The only Manifold infrastructure table created as a real table, if the database is read/write, is the mfd_meta table stored in the Manifold System Data folder within the database. All the rest are virtual components created on the fly by Manifold. Some virtual Manifold components, such as folders, drawings and some queries, are created from text stored in the mfd_meta table. Others virtual Manifold components are automatically created on the fly for convenience. For example, if a table contains geometry a virtual "drawing" component is created for it on the fly to enable visualization of that geometry even if no other drawing has been created for it.
When connecting read-only to a DBMS data source, Manifold creates no tables in the DBMS. When connecting read/write, Manifold will create an mfd_meta table within the data source to save style, folder hierarchy, and other information. In both cases, other Manifold system tables that appear to be created in the data source are virtual tables that Manifold constructs on the fly for a consistent user interface.
This topic explores examples using a connection to a PostgreSQL database. PostgreSQL users often refer to PostgreSQL as Postgres for short, a convention used in this topic. The connection is called Postgres, for example.
We connect to a PostgreSQL database that has had the PostGIS spatial extension enabled. See the Configure PostGIS in PostgreSQL topic. Our connection has full read/write privileges. When we have a read/write connection to a DBMS, Manifold can save information needed for style, folder hierarchies, and other Manifold features by creating a mfd_meta table in that database and writing metadata to that table. That allows persistent storage within the database of Manifold user interface enhancements such as style, or a folder hierarchy that Manifold can use to better display, using Manifold user interface conventions, the contents of the DBMS.
When we open the Postgres data source, we see the database contains various tables and queries that are typically added when the PostGIS spatial extension is installed. It also contains components, such as drawings, labels and a System Data folder, that are typically Manifold components. Clearly, someone in the past has connected to this database with Manifold and has copied and pasted Manifold components into the database.
All of the tables and queries illustrated above in the Postgres data source are real tables and queries. Drawings with a name ending in .Geom are virtual drawings created on the fly from a table that contains a geometry field, so the geometry can be visualized. Drawings with a name not ending in .Geom are also virtual drawings, but have been created as Manifold drawings from the subject table, and are generated based on values in the mfd_meta table for the data source. Labels components are also virtual components, created based on values in the mfd_meta table.
Any folders that appear in the Postgres data source are virtual folders, created on the fly based on hierarchy information stored in the mfd_meta table. For example, the System Data folder is a Manifold folder just like folders in the Manifold .map part of the project. No actual "folder" has been created within Postgres, but Manifold gives the appearance that such a folder exists in the database to allow the use of folders to organize the contents of the database as if they were within the Manifold .map part of the project.
Let us see how that works:
We right-click onto the Postgres data source, so what we next command will apply within the Postgres database.
In the context menu, we choose Create - New Folder.
In the New Folder dialog we specify a name PostGIS Stuff and press Create Folder. The box in which we entered the name is captioned Path because we can specify, if desired, in one step a path name creating a folder within deeper folder hierarchies, if they exist.
A new folder appears, called PostGIS Stuff, within the Postgres data source. We can now Ctrl-click to highlight the various items in the data source related to PostGIS and drag and drop them into the new PostGIS folder.
That cleans up the top level of the Postgres data source by storing within a folder the various nuts and bolts infrastructure items that enabling the PostGIS extension adds within a Postgres database. The + box icon next to the PostGIS Stuff folder indicates that there are items within the folder.
We can click the + box icon to expand the folder, to see the items we have dragged and dropped into that folder.
If we like, we can create another folder, called Manifold Stuff, into which we drag and drop the various other items that were created by prior Manifold use in the top level of the database, as seen in the Project pane in Manifold. The illustration above shows the project after we have dragged and dropped the various Manifold items into the new Manifold Stuff folder.
Expanding the Manifold Stuff folder, we can see the items within. Although our Postgres database now appears to be organized neatly into folders, none of these folders actually exist within the Postgres database. They are all generated on the fly, with the contents of the database organized within them, based on metadata that Manifold writes into the single table, the mfd_meta table, that Manifold creates in the Postgres database for Manifold infrastructure use.
We will now explore how the mfd_meta table works.
Closing the Manifold Stuff folder and expanding the System Data folder, we can see tables that Manifold automatically shows when connecting to a database like Postgres. Three of the tables are virtual, read-only tables called mfd_collate, mfd_root and mfd_srid. They are computed on the fly from Manifold's discovery of the Postgres database when the project is opened and Manifold looks into the various data sources enumerated in the System Data folder within the Manifold .map portion of the project.
One of the tables, the mfd_meta table, is stored in the Postgres database as a real table. That table contains metadata table about Manifold components that are stored within the Postgres database.
Opening the mfd_meta table, we see how it contains the names of components in the data source along with properties. For example, we see that the public.geography_columns query that we moved into the PostGIS Stuff folder has a Folder property with the name of that folder. When Manifold sees a Folder property in the mfd_meta table, it knows that the component with that property should be displayed within the specified folder path.
The Text properties for various queries store the text for those queries. Style properties store Manifold style information for drawings, and so on.
Suppose we create a Queries folder within the PostGIS Stuff folder, and then we drag and drop the queries into that sub-folder.
Immediately, the mfd_meta table updates to show the new path for the Folder property values for those queries.
Opening the mfd_root table, a virtual table, we see that public.Mexico is a drawing. We have selected it to highlight it in red color for the above illustration.
In addition to entries providing Style info, a mfd_meta table entry for public.Mexico has a Table property for the drawing that lists the public.mexico table from which the drawing takes its data. The drawing as shown within the Postgres data source in the Project pane is a virtual component: it does not exist as an entity within the Postgres database, but is generated on the fly based on the information in the mfd_meta table.
Drawings as virtual components are defined based on where they are created. Suppose we right-click onto the public.Mexico drawing and choose Copy. Next, we right-click into the .map portion of the project, say, in the blank area at the bottom of the project, and we choose Paste.
That creates a new drawing in the .map portion of the project, also called public.Mexico.
We right-click onto that drawing and choose Properties to open the Properties dialog for that drawing:
The properties dialog shows that the drawing is created based on a table in the Postgres database, the full name of that table being [Postgres]::[public.mexico table]. The properties for this drawing are stored in the mfd_meta table that is within the System Data folder in the .map portion of the project, since that is where we pasted the copy of the drawing.
When we double-click open the drawing, it appears, taking Style information from the local mfd_meta table, but taking the geometry from the table in the Postgres database.
Tech tip: When we open a Manifold project that contains data sources for databases, Manifold will not connect to those databases to see what they contain until we open the data source or otherwise begin working with the data source. Databases can be very large, and it can take minutes for Manifold to connect to a distant database and discover what it contains when a data source is opened, so the hierarchical display of the data source can be populated.
Suppose we have a drawing like public.Mexico above, which is based on a table in the DBMS, and we open the drawing without opening the data source? When we double-click open the drawing, Manifold will begin discovery within the data source, but as soon as the data source retrieves enough data from the database to become operable, it proceeds straight to the table to fetch data for the drawing, while full discovery of the data source ontinues in background.
As a component, except for pulling data from the table that is within the Postgres data source, the new drawing we have pasted into the .map portion of the Project is a component in the .map project and independent from the database. Style information, copied from the properties in the Postgres mfd_meta table, is now stored in the local .map project's mfd_meta table, and not in the database.
We can rename it, for example, changing the name to Mexico from Postgres.
When we open the drawing using the new name, we see it is the same drawing.
How many different mfd_meta tables there will be in a database depends on how the database is organized. mfd_meta tables are per-database schema. When Manifold connects to a database, the system determines which database schemas can be accessed and presents a virtual mfd_meta table, which can be used to access all data from the individual mfd_meta tables stored in the database.
If there are multiple users, with each user having his own database schema with the intent being that each user can only see or change their own data, then each user will have their own mfd_meta table. If in addition, there also is a common database schema which all users can access in addition to their own private schema, then there will be an additional mfd_meta table stored in that schema as well.
When a given user connects to the database, Manifold combines data from mfd_meta table in the common schema and data from mfd_meta in the user's private schema. The user will be able to see and work in both schemas with tables and with Manifold components created from those tables.
If there are multiple users but they all work in the same common database schema, then there will be a single mfd_meta table and everyone can see and work with the same tables and Manifold components.
If we are worried about making accidental changes to a database, we can connect to the database using a limited account which can only write to the mfd_meta table and cannot write anywhere else. This will be enough to browse through everything, style everything, adjust coordinate systems, and even create scripts, drawings, and labels, because none of those components go beyond properties in the mfd_meta table. If we use this strategy, we must use an account with greater permissions to first connect with Manifold so the mfd_meta table can be created, because permissions to write to a table and permissions to create new tables are different.
mfd_meta tables are harmless - When Manifold creates an mfd_meta table in a read/write DBMS data source, that should have no effect on the database. There should be no issues from having an additional mfd_meta table in the database: the mfd_meta table is just one more small table opaque to code outside of Manifold. There are no triggers, no relation to any non-Manifold data, and no use outside of Manifold.
That is true, for example, when connecting to data sources that are ESRI ArcSDE storages within an enterprise DBMS. There is no impact on ArcSDE from having an additional mfd_meta table in the database, which has no connection at all with Arc. Every database with SDE installed necessarily contains dozens of tables that have nothing to do with Arc, all of which are safely ignored by Arc, as they should be. That is one reason why SDE contains its own system tables listing what other tables contain SDE data. Everything not listed within SDE, such as the DBMS's other tables and the mfd_meta table, is not SDE and is ignored.
Virtual Table for Images in GPKG - Linking a GPKG file that contains images exposes an additional virtual table for each image. The virtual table has a fixed structure and includes both a BTREE index on x-y and an RTREE index on x-y-tile. This allows both (a) rendering intermediate levels stored in the database and (b) Alt-clicking into image tiles to see pixel values. The virtual table is read-only. The physical table storing image data is still accessible and writable.
Automatic generation of intermediate levels when pasting into tables - Pasting an image table with an RTREE index on the x-y-tile field automatically generates data for intermediate levels.
File - Create - New Data Source
Real and Virtual Components
Microsoft Access and Manifold ODBC
DBMS Data Sources - Notes
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