This introductory topic provides a brief overview of how Manifold approaches tables and databases. It is intended for readers already familiar with database concepts such as tables and queries. See the Tables topic and subsequent topics for a complete guide to working with tables. See the Working with Queries topic for an introduction to queries. See the Query toolbar topic for a fast way to select records in tables.
Tables show data organized into rows and columns. Every row in a table is a record. Every column in a table is a field. We will use the words "row" and "record" interchangeably. We will also use the words "column" and "field" interchangeably.
Tables and Drawings
Every drawing in Manifold has a table associated with it. Each object in the drawing (that is, every point, line or area in the drawing) is linked to a row in the table. Drawings have a table even if there are no data fields saved for each object. In that case, the drawing's table is mostly empty. It has only one field, the object ID field, for each row. The object ID field is used to link each row to its associated object and cannot be edited.
Deleting an object in a drawing will delete the object's record in the drawing's table. Deleting a record in a drawing's table will delete the associated object in the drawing.
Other Tables
Tables may also be database tables having nothing to do with drawings that are imported into a Manifold project or linked into the project from an external database source. New tables may also be created with Manifold.
Tables may be native tables (also called local tables) that have at least one column that exists entirely within the project, or they may be linked tables that are created from other tables. Linked tables are created from other tables, which could be within the same project or which could come from data sources outside of Manifold. Native tables can also use data from other tables if they include relations with other tables.
Tables may be imported or linked into projects from almost any database, even from those that have nothing to do with drawings or maps. We can import or link tables from a vast range of different types of data files and data sources, from ordinary text files such as CSV files, from applications files such as Excel XLS files that contain data, and from a very wide range of database connections and database providers such as Access, SQL Server, Oracle, IBM DB2, MySQL and the like. It's actually very difficult to find a source of data that a knowledgeable Manifold user cannot utilize.
We will often work with tables that have no geographic context. We may wish to prepare data for later use together with drawings and maps, or we might simply wish to use Manifold as a general-purpose means of viewing, analyzing, exploring and managing databases.
The illustration above, for example, shows a project with tables imported from the Microsoft Northwind Traders example database shipped with Microsoft Access.
If we open the Customers table we see the same data familiar to many Access users who have worked with this sample database.
There are two types of sources for database tables that may be imported or linked into a Manifold project:
· Tables from files and file types that may be opened directly by Manifold's own database engines and so require no other database software. These include .mdb, .dbf, .udl, .wkX, .xls, .db, and .html files as well as ASCII or text database files organized as comma separated value (.csv) form. Such file-based data storage options appear directly within the Files of type box in dialogs when importing or linking.
· Tables from data sources that are provided by external database engines via OLE DB, ODBC, ADO .NET, Oracle Call Interface (in the case of Manifold Enterprise Edition) or similar connections to database providers. Some such sources may require external database software. The default Manifold installation process will include a default set of standard Windows drivers for popular databases such as SQL Server and Oracle. Such data sources are accessed using the Data Source dialog, which appears when the Data Sources () choice is used within the Files of type box in dialogs when importing or linking.
To import or link a table into a Manifold project
1. Choose File - Import - Table or File - Link - Table depending on whether you wish to import the table or to link it.
2. In the Files of type box choose the access method desired. Choose one of the standard file types, such as MDB or XLS to connect directly to common file types. Choose the Data Sources () item to connect via ODBC, OLE DB, ADO .NET or a native connection to a DBMS server.
3. Browse over to the database file and open it. See the Data Source dialog topic for examples connecting using that dialog.
4. Choose the tables to be imported. Choose more than one table if desired.
5. When linking tables, check Read Only if you wish a read-only link. Some connection technologies such as ADO .NET or some ODBC providers may allow only a read-only link.
6. Press OK.
After the initial choice of File - Import or File - Link, all dialogs are the same. The only difference is whether the data in the table is copied into the project or if it is left outside in an external file or data source.
Alternate method to import or link a table
1. Open the Tools - Database Console dialog.
2. Next to the Data source box, click on the [...] button to launch the Data Source dialog. Connect using that dialog as shown in the the Data Source dialog topic.
3. The upper pane will show the contents of that data source, including any tables and the fields they contain. Click on a table to highlight it.
4. Click on the Import button in the dialog's toolbar to import the highlighted table. Click on the Link button to link the table.
5. Press Close.
In all cases, tables that are imported or linked will appear to be part of the project like any other table. Manifold can import data from or link to a very wide range of database files and database providers.
See the Data Source dialog topic for information on using that dialog to connect to databases.
See Importing and Linking Tables for more information on using existing tables.
See Creating New Tables for information on creating new tables.
See the Database Console topic for a useful console for browsing databases and importing and linking tables. The database console can also be used to execute SQL commands within an external database provider, such as SQL Server or Oracle.
Table Windows
Clicking open a table will display it in a table window, a classic row and column text database table presentation. A table may be opened in more than one window simultaneously. Opening an SQL query will also result in a table showing the results of the SQL query.
Table windows show many records at once. Selected records appear highlighted in red selection color. Records will be shown in selection color whether they were selected within the table window or (in the case of drawing tables) if they were selected by clicking on the corresponding objects in the drawing. Records may also be selected using the query toolbar or queries.
Table windows are controlled using a mix of menu command choices, context menus, selection and use of the transform toolbar . Try right clicking onto a column head, a record handle, and a cell to see the different context menus that appear with tables. See the Tables - Context Menus topic for a summary guide to context menus in tables.
By default, table windows do not use horizontal or vertical gridlines between rows and columns. Gridlines may be turned on in Tools - Options .
Columns
Columns are headed with the name of the field they show. We can reorder, show or hide columns in a table using the View - Columns command. Using the View - Columns command, we can also display intrinsic fields as columns in the table. Intrinsic fields are computed quantities, such as the length of lines, or system quantities such as the latitude and longitude position of a point. Right click onto a column head to see a context menu of many additional commands that work with columns.
The boundary lines between column heads may be moved left or right to make a column wider or narrower. If you are using Windows 2000 or Windows XP columns may be dragged and dropped into different left to right arrangements. We can also use Best Fit or Best Fit All to automatically set the width of columns.
Click on a column head to sort it in ascending (A to Z) order. SHIFT-click on a column head to sort it in descending (Z to A) order.
Specifying Languages for Columns
Columns in tables can have different languages specified. This allows one column to show text in one language while another shows text using a different language character set. Several different languages can be used in different columns within the same table. By default, tables use the Windows locale setting for the current user. If desired, any text column in a table can be set to a different language by right clicking on the column head and choosing Language.
Rows
Records have a record handle at their left margin. A black triangular arrow marks the current record in its record handle.
The current record will be specified by a current record arrow in its handle as well as by outlining the active cell.
When selected, the current cell is highlighted by a slightly lighter shade of the red selection color.
Selection Methods
Selecting records in the Table window is part of many tasks in Manifold. Selection in tables, like drawings, uses selection modes to specify whether the selection command should replace, add to, subtract from, invert with or intersect any previously existing record. The default mode is Replace.
· Click on a cell to make it the current cell and to make that record the current record.
· Click on a record handle to select that record.
· SHIFT-click on a record handle to select the record and to also select all the records between it and another selected record. This is often used to select a series of records: click on the top record and then SHIFT-click on the bottom record and all the records in between will also be selected.
· CTRL-click on a cell to select that record.
· SHIFT-CTRL-click on a cell to select a range of records.
See the Selection in Tables topic for more details.
Copy and Paste Operations with Columns
Copy and Paste are handy commands for copying data between fields. Suppose we have a numeric field that is a double, and we would like to convert the field into an integer. We do this by first creating a new field that is an integer. A new column will appear. Next, we Copy the contents of the double numeric field's column and then Paste into the new integer field's column.
This method works between reasonably compatible types. For example, numbers like 3.1415 may be converted into text strings such as "3.1415", but it does not make sense to attempt to convert "Main Street" into a number.
We can also use the Transform toolbar to copy values from one column to another, also with automatic conversion whenever possible.
Editing Cells
See the topic Editing Data in Tables for details on changing values inside cells and adding new records to tables.
See the Editing Intrinsic Fields in Tables topic for a discussion of how we can move objects by editing their locations as they appear in intrinsic fields in a table.
Bookmarks
Bookmarks are icons in the left margin of text components such as comments, queries and scripts. They allow fast navigation within lengthy text. Bookmarks are also available within tables, comments, scripts and queries. Within tables they are very useful for marking particular records when jumping back and forth between records in a table. When tables or queries are sorted the bookmarks correctly travel with the records into the new sort order.
To set a bookmark in a table, click anywhere in the row to be marked.
Press F8 or choose Edit - Bookmarks - Toggle to set the bookmark. To clear the bookmark, press F8 or choose Edit - Bookmarks - Toggle again.
Add other bookmarks as desired. We can now use Ctrl-F8 to jump to the bookmark below the current row or Shift-Ctrl-F8 to jump to the bookmark above the current row. See the Edit - Bookmarks topic more on bookmark.
Effect of Tools - Options Settings
When importing tables, keep in mind that the default setting in Tools - Options is to not import empty columns. Check the Import empty columns in tables option to import tables with columns that do not contain any values in the originating table.
Note also the setting of Trim strings imported from external databases (on by default). This will delete leading and trailing whitespace characters from imported table strings.
The Trim strings option (just like the Trim table transform operators) removes characters listed in the Tool Properties pane's list of token separators. By default, these are the "white space" characters consisting of the space character, tab, newline and carriage return. Note that adding any other characters to the separator list in Tool Properties will subject them to removal as well if they occur as leading or trailing characters.
ViewBots
ViewBots are one-line analytic instruments used to dynamically compute a statistical or comparative measure over a subset of records and are extremely useful for analyzing tables. They are one of the most popular functions in Manifold for experienced users. See the ViewBots topic.
Transform Toolbar
The Transform toolbar is an extremely powerful tool for managing and editing tables. It is easy to use and should be mastered by every Manifold user doing serious work with tables. New users and experts alike will experience dramatic productivity gains by learning to use the transform toolbar.
Active Columns
Active Columns are a way of embedding short (or long) snippets of scripts within tables to automatically accomplish calculations. The idea is similar to that of spreadsheets, but appearing in tables in a much more powerful way.
Decision Support System
Manifold's Decision Support System provides remarkable data mining capabilities by applying fuzzy inferences in an easy to use way.
Primary Keys a Must with DBMS
When working with server-based OLE DB providers such as DB2, SQL Server and Oracle, users are strongly encouraged to maintain primary keys in all tables linked into the Manifold project. A side effect of how such servers interact through OLE DB is that if the table does not have a primary key, performance will be greatly reduced.
ODBC and .map Files
Manifold includes an ODBC driver. This driver allows other applications to work with data in tables and queries in a Manifold .map project file. See the Using the Manifold ODBC Driver topic.
Notes
Tables within Manifold have many powerful capabilities within the context menus that appear when right clicking on different parts of the table. For example, one can change field types using the Change Type choice that appears when right clicking onto a column head. In another example, one can right click on a record and choose More Like This to automatically see more records like that one sorted to the top of the table. Take the time to read through the Tables topic and subsequent topics in the Tables book to learn about these and many other powerful capabilities.
Many of the illustrations for tables are screen shots made using the Nwind.mdb sample database provided by Microsoft. This is a version of the Northwind.mdb sample database distributed with Access and thus familiar to many Microsoft Office users. Nwind.mdb is provided on the Manifold System CD in the Help examples folder.