FDO RFC 29 - Redefining Expression Function EXTRACT

This page contains a request for comments document (RFC) for the FDO Open Source project. More FDO RFCs can be found on the RFCs page.

Status

RFC Template Version	(1.0)
Submission Date	Nov 11, 2007
Last Modified	Thomas Knoell Timestamp
Author	Thomas Knoell
RFC Status	Draft
Implementation Status	Pending
Proposed Milestone	3.4.0.0
Assigned PSC guide(s)	Greg Boone
Voting History
+1
+0
-0
-1

Overview

It has been noted that the use of the expression function EXTRACT in its current specification may cause some usability issues. This is related to the result the function returns, a FdoDateTime object in which all properties are set to the default value except the property for which the function was executed. For example, if the user requested the year of a date property, the year property of the returned FdoDateTime object is set to the corresponding value whereas all other properties in the object - month, day, hour, minute and seconds - are set to their default value which is either -1 for month, day, hour and minute or 0 for seconds. It is up to the function caller to select the correct property from the retuned object. In some cases, users were not aware of this and hence experienced unexpected results.

The purpose of this RFC is to make the function easier to use. Instead of returning a FdoDateTime object, the proposed modification will return a numeric value to represent the requested value directly. This will eliminate any misunderstanding related to this expression function. It will also allow the function to be used in a nested function statement like !TOSTRING (EXTRACT ( ...)).

The proposed redefinition of the expression function EXTRACT will also address backward compatibility to avoid cases where any user/application has used this function in the expected manner.

Proposal

It is proposed to add the following new expression functions to the list of supported expression functions:

• ExtractToInt: Extracts the requested part of a date/time property and returns the value as an integer.

• ExtractToDouble: Extracts the requested part of a date/time property and returns the value as a double.

Both functions will have the same signature and differenciate themselves in the return value only:

• !FdoInt16 ExtractToInt ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}, <date_time_prop>);

• FdoDouble ExtractToDouble ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}, <date_time_prop>);

The two functions are required because by default, seconds are defined as a double whereas all other date/time properties are defined as integers. The following describes the expected behavior of the functions when invoked with different options:

FUNCTION	REQUEST	RETURN
ExtractToInt	YEAR	The year portion of the specified date. If the date does not contain year information, the function returns -1
ExtractToInt	MONTH	The month portion of the specified date. If the date does not contain month information, the function returns -1
ExtractToInt	DAY	The day portion of the specified date. If the date does not contain day information, the function returns -1
ExtractToInt	HOUR	The hour portion of the specified date. If the date does not contain hour information, the function returns -1
ExtractToInt	MINUTE	The minute portion of the specified date. If the date does not contain minute information, the function returns -1
ExtractToInt	SECONDS	The seconds portion of the specified date as an INTEGER. In this case the actual value is rounded to the closest integer value. If the date does not contain seconds information, the function returns -1
ExtractToDouble	YEAR	The year portion of the specified date as a DOUBLE. If the date does not contain year information, the function returns -1
ExtractToDouble	MONTH	The month portion of the specified date as a DOUBLE. If the date does not contain month information, the function returns -1
ExtractToDouble	DAY	The day portion of the specified date as a DOUBLE. If the date does not contain day information, the function returns -1
ExtractToDouble	HOUR	The hour portion of the specified date as a DOUBLE. If the date does not contain hour information, the function returns -1
ExtractToDouble	MINUTE	The minute portion of the specified date as a DOUBLE. If the date does not contain minute information, the function returns -1
ExtractToDouble	SECONDS	The seconds portion of the specified date. If the date does not contain seconds information, the function returns -1

For backward-compatibility reasons, the original expression function !Extract will still be supported. However, it will be marked as deprecated and users are expected to switch over to the new functions as the original function will eventually be removed from the list of supported expression functions.

Affected Providers/Components

This enhancement will affect the following providers: ArcSDE, MySQL, ODBC and SQL Server 2008. It will also affect the Common Expression Engine.

Implications

The new functions will be part of the default expression function list provided by the Expression Engine and hence will have a default implementation there. Any provider that relies on the default implementation therefore does not need to be modified. Only providers that map the expression function EXTRACT to a native supported routine (for example some RDBMS providers) should be changed to map the new functions to the corresponding native routine.

Test Plan

Existing unit tests will be enhanced to test those changes.

Funding/Resources

Autodesk to provide resource / funding to implement the changes related to this RFC.