Industry models in embedded DWG and SQL Server – Part1

Since AutoCAD® Map 3D 2012 we introduced the embedded DWG that contains the same industry models, as in Oracle, in an embedded SQLite database.  In AutoCAD® Map 3D 2013 we introduced SQL Server industry models.  Both DWG and SQL Server models contain the same data models (water, gas, wastewater and electric) and extensions, and equivalent feature rules and functionality.

The main differences between these models are that embedded DWGs are for single user while Oracle and SQL Server allow a multiuser environment.  The jobs (long transactions) functionality is only available in Oracle given that it is based on the Virtual Private Database technology.  The profile extension and 3D industry models are only available in Oracle. The plot extension is only available for Oracle and SQL Server. Note that plot extension refers to the data model extension for industry models that allows storing pre-configured plot templates in the database and generating plots based on specific display models; the plot capability itself is available in Map 3D for any FDO data source.

In order to maintain a data model, the Autodesk Infrastructure Administrator provides the same look and feel whether you work in embedded DWG, Oracle or SQL Server. 

There are only a couple of considerations when creating a data model that should be considered for both SQLite and SQL Server, and it is mainly the SELECT statements of views and label definitions.  In general, Oracle specific keywords or functions will not be recognized by the other two providers.

A general tip for SELECTs is that FDO is case sensitive so you should use consistent cases i.e. use always upper case column and table names (select p.FID, p.GEOM from MYTABLE p).

Let’s start with SQLite; below you have a list of considerations for SELECT statements:

• Use only INNER JOIN and LEFT OUTER JOIN. This last one is equivalent to the old Oracle’s syntax for outer join (+).  Note that the ISO 99 SQL Standard has suppressed the use of (+), therefore it is recommended that you use LEFT OUTER JOIN also in Oracle.
• There is no DECODE function, you need to use 'SWITCH CASE WHEN .. THEN .. WHEN .. THEN END'
• There is no NVL function, but there is a similar function called IFNULL, or use COALESCE (this one is recommended); see here for a list of SQLite functions
• There is no dual table, but you can omit the 'FROM' part of the SELECT as it is not needed, e.g.: 'select 14 + 3' instead of 'select 14 + 3 from dual'
• Use LIMIT clause at the end of the SELECT instead of ROWNUM (more info here)
• The default behavior of the LIKE operator is to ignore case for ASCII characters, which means the expression ‘a’ LIKE ‘A’ is TRUE (see here)

Regarding data types, the Autodesk Infrastructure Administrator shows in the interface the well-known Oracle data types, when you create attributes with these data types they will be matched internally with the equivalent  SQLite data types. It is important to note that SQLite does not enforce data types.

For more information refer to the following links:

SQLite language diagrams (link here)

Map 3D Help - Working with SQLite data (link here)

 

Comments

[Gorden Kock]

Hi Topobase-Insiders,
thanks for this very helpful post. I tried to use the new option of offline DWG data and failed because of many problems during creating a template from my existing Topobase industry model based on ORACLE. Some of your hints may be part of that trouble. But as the title says "Part 1" I hope that you will add several further hints in the near future.

If you have an industry model already existing for many years you can imagine that it is not exactly compatible to SQLite. But to make it more compatible I can follow your hints with the JOIN part, the DECODE and NVL functions because your hints also work in ORACLE. That's not the case with the DUAL and LIMIT hint. So this can't be used that way and must be replaced by a different statement or must be left out.

But above that I've got problems with so called "non constant DEFAULTS" like sysdate. I use that very often.
Then I have problems with views using simple ORACLE functions like TO_CHAR.

Maybe you can provide information or workarounds on that in "Part 2".

Another great help would be to provide examples for the regular expressions you can use when creating a genx file for the DWT. Because my industry model is even older than Topobase there are several tables that are no feature classes and don't need to be. But I have to use them in the display model views. So I have to include them with that regular expression. At the same time I need to exclude some other tables. How does that work exactly?

Thanks for any further hint on those problems.