File based Industry Model - how to modify views

When you use a file based Map Industry Model (IM) you might want to extend the data model and add attributes to one of the tables. If you are not familiar with databases, SQL and the concept of "table" and "view" this blog post will explain some basic concepts behind Map IMs and also show how you can add attributes to both tables and views. 

Tables and views 

AutoCAD Map comes with ready-to-use Industry Models. These IMs contain many tables, views, forms, labels and so on. Tables actually store data, views only provide a "view" on one or more tables and do not contain any data. Look here for more details: 

https://en.wikipedia.org/wiki/View_(SQL) 

Nothing prevents you from storing all your data in just one big table. But there are disadvantages to it and therefore data is usually split across multiple tables. Here is more information on why: 

https://en.wikipedia.org/wiki/Database_normalization 

When you have data across multiple tables you need to combine data in order to get the information you need - "views" are one way to achieve that. 

In Autodesk Infrastructure Administrator (AIA) tables and views appear the same at first glance. But if the Data Model was set up properly a view will have a display name ("caption") containing the word "view". Whenever you create a new table or attribute in AIA you need to enter a name but you can also enter a "caption" - a more descriptive name:

Feature class name and caption 

The caption will be used in AIA and Map, not the name of the object: 

 

List of all tables/views in "Point" - column "Feature Class" shows captions

Whenever you need to work directly in the database you first need to find out the name of the table/view. The names are shown in AIA as well - just look below the tree view where the caption and the name of the table/view (in brackets) are displayed: 

For a selected item the name and caption is shown in the status bar below the Data model tree view.

You will also notice another convention - in a databases a view name often contains "_v_" to indicate that it is a view and not a table. 

Example: in WA Industry Model there is a table called: "Fitting" (see screenshot above):

"Fitting" - display name for table "WA_FITTING" shown in AIA and Map 
"WA_FITTING" - name of the table in the database (WA indicating that the table belongs to the WA Industry Model) 
"Fitting View" - display name for view "WA_V_FITTING" shown in AIA and Map 
"WA_V_FITTING" - name of the view in the database  

In AIA you can easily add attributes to a table. If you add an attribute you usually want to have the attribute shown on your form as well in order to be able to enter data. So you need to open the form for the table in AIA Form Designer and add the new attribute there as well. In case the new attribute is also needed to style features in your drawing you need to incorporate it in your layer definition. 

But layers in Display Models often are based on views and not on tables. In this case adding an attribute to a table is not sufficient - the attribute also needs to be added to the view in question. Now, here is a limitation of AIA - you cannot create or modify an existing view the same way you can create or modify a table. For a view the context menu function "Add attribute" is not available: 

 

For a view "Add Attribute" is not available

In order to do that you need to use a tool called "SQL Sheet" which comes with AIA - we will come to that in a second.

How do you know whether a layer in Display Model is based on a table or on view? 
After generating graphics using the Display Model in question you can check either by hovering the mouse on top of the layer name in Display Manager - a flyout will show the layer's data source (table/view name, not the caption) - 

Layer data source name is shown in fly out for layer in Display Manager

or open the StyleEditor for the layer and the table/view name is displayed there as well on the top: 

Layer data source name is shown in Style Editor

 If the name contains "_V_" you can be quite certain its a view.  

By the way - the layer name as shown in Display Manager is often different from the table/view names the layer is based on. You have three different names by now: 
- the actual table/view name as used in the database 
- the caption for a table/view as shown in AIA and Map 
- the layer name in DisplayManager  
All point to the same thing - a certain table or view - but might have (slightly) different names. 

Modify an existing view 

Example - add a new attribute to the Fitting table and also use the attribute when styling the layer in Map. As the layer in Map is based on a view (see screenshot above) we need to modify it as well. 

1) add new attribute to table 

2) in Form Designer drag and drop attribute to the form 

3) save drawing (important!) 

4) open SQL Sheet (File menu, choose "SQL Sheet"),  

5) choose "SqLite"  and your drawing file (containing the file based IM, drawing should not be opened in Map at the same time) 

4) in top right corner choose "Views" to get a list of all views in your current IM 

5) select the view to modify - WA_V_FITTING -  on the right hand side below the list you will see the attributes of the view 

6) right-click on view name and choose "Modify View" - in the left side panel the view definition will be shown 

CREATE VIEW WA_V_FITTING AS select g.GEOM,g.ORIENTATION, g.FID, a.NAME_NUMBER 
from WA_POINT g, WA_FITTING a 
where g.FID_ATTR = a.FID 

As you can see the view takes data from two tables - WA_POINT and WA_FITTING. 
WA_POINT stores the geometry of a feature, WA_FITING stores all attributes which are useful for fittings. We added a new attribute to WA_FITTING earlier. This attribute needs to be added to the view as well. 

You can also notice a letter after each table name (g and a) - they are called "alias" in order to shorten then whole expression. Without alias (which could be a word not just a single letter) the statement would read like this: 

CREATE VIEW WA_V_FITTING AS select WA_POINT.GEOM,WA_POINT.ORIENTATION, WA_POINT.FID, WA_FITTING.NAME_NUMBER 
from WA_POINT, WA_FITTING 
where WA_POINT.FID_ATTR = WA_FITTING.FID 

The alias or table name is given to indicate from which table a certain attribute comes from (required in cases where the same attribute name is used in more than one table). 

Now you only need to add your attribute name (including the alias) - don't forget to add an additional comma between the last attribute and the new attribute: 

CREATE VIEW WA_V_FITTING AS select g.GEOM,g.ORIENTATION, g.FID, a.NAME_NUMBER 
, a.MA_SIZE 
from WA_POINT g, WA_FITTING a 
where g.FID_ATTR = a.FID 

Attribute name needs to be upper cases. 

Click on the green triangle to execute the statement - you will get the following error message: 

... 
SQL execution error number 0, table WA_V_FITTING already exists 
... 

We cannot overwrite a view - we have to delete the old one first and re-create it afterwards. Add the following line before the CREATE VIEW statement: 

DROP VIEW WA_V_FITTING; 
CREATE VIEW WA_V_FITTING AS select g.GEOM,g.ORIENTATION, g.FID, a.NAME_NUMBER 
, a.MA_SIZE 
from WA_POINT g, WA_FITTING a 
where g.FID_ATTR = a.FID 

Execute the query again, result should be:

SQL> DROP VIEW WA_V_FITTING 
Command executed (0). 


SQL> CREATE VIEW WA_V_FITTING AS select g.GEOM,g.ORIENTATION, g.FID, a.NAME_NUMBER 
, a.MA_SIZE 
from WA_POINT g, WA_FITTING a 
where g.FID_ATTR = a.FID 
Command executed (0). 


If you receive an error message like this: 

SQL execution error number 0, no such column: a.MA_SIZE 
 'CREATE VIEW WA_V_FITTING AS select g.GEOM,g.ORIENTATION, g.FID, a.NAME_NUMBER 
, a.MA_SIZE 

Did you save your drawing after adding the attribute to your table?  If you haven't saved the drawing SQLSheet doesn't seem to "see" the new attribute. 

7. close SQL Sheet 

8. Close AIA 

9. open AIA and reload your drawing, check the view in Data Model tree view - the attribute is now available in the view as well: 

Before modifying your data base with SQL Sheet - back up your drawing file.