The ORM Foundation

Would it be possible to add in additional data types into NORMA and also to update the data type which they are converted to during the ddl generation (specifically for SQL Server)?

For example, for date data-types we currently have 'Temporal: Auto Timestamp', 'Temporal: Date' and 'Temporal: Date & Time'. However when the ddl is generated for SQL server all three data types map to the same SQL Server data-type 'datetime'.

In SQL Server 2012 there are several date/time datatypes; date, datetime, datetime2, datetimeoffset, time, smalldatetime. It would be great if NORMA has the capability to generate ddl which supported these additional datatypes in addition to other datatypes such as timestamp (rowversion in previous version of SQL Server) etc

If the process to include this functionality isn't too complicated I would be willing to attempt to add this in myself or to work with someone else to bring this functionality into NORMA :)

This all guesswork on my part, but, It should be easy to add in new data types in the style sheets.  I don't think anything is hard coded.  But mapping the data types from orm to sql is the trickey part.  There has to be a type in orm that corresponds to the new data types in the database model you want to generate.  So you need to update at least two style sheets.

 I think the style sheet that names the types in sql standard has the word "sql" in the file name. 

 

Hi Tom,

For a short-term workaround on adding additional data types look at http://www.ormfoundation.org/forums/p/601/1835.aspx#1835. You could also combine this with custom properties (similar to http://www.ormfoundation.org/forums/p/573/1698.aspx#1698), which would give a cleaner user experience. In fact, you could probably go as far as a custom property that simply picks, and ignore the conceptual data type if it is populated.

The bigger question here is a conceptual model vs. physical model issue. From a per conceptual perspective, I can see datetimeoffset as being different from datetime, but it is hard to treat datetime and datetime2 as different types. What I would like to avoid is expanding the list of conceptual data types to be a superset of all of the implementations we target.

The current data types have served marginally for a long time, but they are basically just placeholders for real data types. Sometime this year I will be expanding the data types to allow simple type hierarchies (restrictions on other types) and formal facet definitions (currently, there are simply two slots that are shown and renamed at different times). This would allow, for example, a regex facet on a string data type, which isn't currently handled. It would also allow for extensions to add facets to core data types, including target-specific extensions like the SQL server data types. Basically, you could then add your own 'DataTimeOffset' data type to the model (with the SQL Server type specified as an extension facet) and use your own specialized type as the data type for multiple value types in your model.

Another parallel option is to add a settings file to the SQL generation. A settings file is a 'write once' generated file that the user can edit to affect the contents of other generated files. In the SQL case, you could then pick your target SQL server flavor and we could map the data types better for the newer SQL variants. This one is low hanging fruit (the NORMA generator already supports this concept).

So, while I'm reticent to simply extend the core list of data types, I need to address the broader issue soon.

-Matt

 

I seem to recall that in the period "BN" (Before NORMA  - aka the "Old Days"), the ORM tools required you to choose a target DBMS before starting to create a model. Then, when you created a new object type, your selection of data type was restricted to those that were available in your target DBMS.

Thanks Matt

You make a good point about the conceptual model vs physical model.

Hopefully this weekend I can look into either creating the settings file for the SQL generation or hijacking the datatypes like you suggested :)

On a side note, is there a roadmap for the additional features and changes you are bringing into NORMA?

 

 

 

 I don't have a need for all of the various SQL Server date/time datatypes, but I intend to make use of the Date datatype.  As Tom pointed out, the conceptual model already has three date/time datatypes, my issue is (as he also noted) that they are all mapped to the SQL Server datetime datatype.  All I did - based on the forum links that Matt provided - was to modify DDILtoSQLServer.xslt by expanding:

<xsl:template match="@type[.='DATE' or .='TIME' or .='TIMESTAMP']" mode="ForDataType">

    <xsl:text>datetime</xsl:text>  

</xsl:template>

 

to:

 

<xsl:template match="@type[.='DATE']" mode="ForDataType">

    <xsl:text>date</xsl:text>  

</xsl:template>

<xsl:template match="@type[.='TIME']" mode="ForDataType">

    <xsl:text>time</xsl:text>  

</xsl:template>

<xsl:template match="@type[.='TIMESTAMP']" mode="ForDataType">

    <xsl:text>datetime</xsl:text>  

</xsl:template>

 

Easy peasy: my DDL now makes use of both Date and Datetime column definitions.

 Maybe this was too obvious to have been mentioned, but maybe there are also others out there as dense as I that can make use of it.

- Chris

Hi Chris,

I've made a similar change, but went a little farther than this in some of the other generators by recognizing a difference between DateTime and AutoTimestamp. Although the timestamp data type is the SQL standard, it really isn't a data type at all for SQL Server (it is just a table attribute). It is also deprecated in favor of rowversion. The next release will map TIMESTAMP to rowversion, DATETIME (new possibility) to datetime, TIME to time, and DATE to date (matching your code above).

If anyone objects to these settings please let me know sooner rather than later.

-Matt

PS I'd like to get a major rework of the data types in the public release after this one. You'll likely see fewer main data types with additional implementation-specific facets to get a very specific implementation. You'll then be able to get more specific on your datatypes than you do now and be able to get things like shortdatetime, datetime2, etc. This is a big enough chunk of work that I'll probably branch it in the code base (either formally or just locally in a backed up directory), so I'm not going to promise when it will see the light of day, but it is clear that I can't get to where I need to be with the current approach to data types. This will include the ability to create custom reusable data types with your own names that are based on stock data types but can set additional facet information (length, scale, precision, value range, regex pattern, etc.). So, if you want to model directly against the SQL data types, you'll be able to create a named data type with all of the correct settings, e.g. shortdate, and reuse it as many times as you want.