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)