Re: db-lib: support for new MS SQL 2008 data types - part 3

2014-04-22 7:10 GMT+01:00 LacaK <[email protected]>:
> Frediano Ziglio  wrote / napísal(a):
>>
>> 2014-04-17 6:26 GMT+01:00 LacaK <[email protected]>:
>>
>>>
>>> Frediano Ziglio  wrote / napísal(a):
>>>
>>>
>>>>
>>>> 2014-04-16 12:44 GMT+01:00 LacaK <[email protected]>:
>>>>
>>>>
>>>>>
>>>>> Hi again,
>>>>> Now I am personally happy with patched db-lib in regards of support new
>>>>> DATE
>>>>> - TIME data types.
>>>>>
>>>>>
>>>>
>>>> Great! Yesterday I discovered a problem with BCP adding some tests for
>>>> these new types, still to fix.
>>>>
>>>>
>>>>
>>>
>>> May be, I have never used bcp_* functions ;-)
>>>
>>>
>>
>>
>> I'll fix it. It's a quite different code path compared to the one you
>> are working on. But is still related to same data types.
>>
>>
>>>
>>>
>>>>>
>>>>> But there are still missing some parts, which may be interesting to
>>>>> somebody
>>>>> else.
>>>>> I meant dbbind() family of API with corresponding *BIND constants and
>>>>> binary
>>>>> structure used to store this types.
>>>>>
>>>>> How to handle binding of new date, time types ?
>>>>>
>>>>> - introduce new DBDATETIMEALLBIND (or DBDATETIME2BIND) constant in
>>>>> sybdb.h ?
>>>>>
>>>>>
>>>>
>>>> could work
>>>>
>>>>
>>>>
>>>>>
>>>>> - introduce new DBDATETIMEALL struct (==TDS_DATETIMEALL struct) in
>>>>> sybdb.h
>>>>> ?
>>>>>
>>>>>
>>>>
>>>> Mmm... well... could be or not. Microsoft for ODBC defined quite
>>>> different structures (one more similar to DBDATEREC). TDS_DATETIMEALL
>>>> is neither from TDS protocol neither intended to be presented to
>>>> clients. It's a mix of TDS protocols, numeric, old dates (values are
>>>> the same as dtdays).
>>>>
>>>
>>>
>>>>
>>>>  On the other end I could understand that
>>>> providing dbdata different from libTDS is far from easy.
>>>>
>>>>
>>>>
>>>
>>> Exactlly!
>>> It is main reason why I am still speaking about TDS_DATETIMEALL ;-)
>>> And as I already wrote because of similarity of:
>>> SQL Server       libTDS                          DB-Lib
>>> ------------------------------------------------------------
>>> datetime        -> TDS_DATETIME   == DBDATETIME
>>> smalldatetime ->TDS_DATETIME4 == DBDATETIME4
>>>
>>> I will be happy also with SQL_TIMESTAMP_STRUCT (or other struct), to be
>>> public structure for these new date time data types, but IMO then this
>>> struct must be used also internaly by libTDS to store values in record
>>> buffer. Because if libTDS will continue use TDS_DATETIMEALL then it will
>>> significantly complicate things on db-lib level. (as there will be
>>> required
>>> conversion in many places)
>>>
>>>
>>
>>
>> Well.. TDS_DATETIME4 and TDS_DATETIME have same representation of wire
>> bytes (unless bit endian is different) and are documented in dblib.
>> TDS_DATETIMEALL is neither wire neither documented (so no ABI).
>> Unfortunately dbdata wants a binary representation of each data.
>> ctlib... I don't remember. ODBC either wants a bind or data get read
>> into user provided buffers (SQLGetData). Actually ODBC have separate
>> types for each MS type. The reason I added this libTDS type is that is
>> easier during the conversion to have a single type to work with.
>>
>
> I agree with this "single type"
>
>
>> Another reason is while wire for all date types are quite easy to put
>> directly into a structure these new types are quite different. The
>> size of date is 3 bytes so computers cannot handle directly (you need
>> to stick the 3 bytes into a single 32 bit integer) while seconds and
>> fraction size are from 3 to 5 bytes (same problem). This is the reason
>> for the two time and date fields. Obviously to store 5 bytes we need
>> at least a 8 byte integer. Somebody could say that an 8 bytes integer
>> is enough (3+5 = 8) and it's true but all datetime structure keeps
>> date and time separate and mostly of the time this would lead to just
>> some extra multiplication/division. Another thing about date. The zero
>> from the wire represent a date like 1-1-0... now, gregorian calendar
>> (the one we use) was introduced in 1592 so before they have different
>> calendar (month days and months order changed). So this zero is quite
>> artificial. This is why I preferred to set zero for this structure to
>> 1-1-1900. About seconds wire send the number with precision so
>> 00:00:01 is 1 for TIME(0) and is 100 for TIME(2). Actually the
>> structure always set this number as precision was 7. About bit fields.
>> These mainly are reduntant as they came directly from the type. They
>> are not on the data wire (precision is a field in the metadata), this
>> is similar to scale/precision for numeric data (which are in metadata
>> while we copy in libTDS data).
>>
>
> I have no objections, as I wrote I am also now perfectly happy with
> TDS_DATETIMEALL
> All reasons you mentioned are from me POV valid and logical.
>
>
>> Well... all these looks quite paranoid but external ABI needs to stay
>> so is better to decide what to stick into the dbdata structure!
>> date: perhaps would be better to just store the number from wire
>> (converted to 32 bit) without bias;
>>
>
> may be
>
>
>> time: perhaps would be better to just store the number from wire
>>
>
> for me is better solution have time "normalized" to fixed precision -
> TIME(7)
> in other cases I will must evaluate on each access "time_prec" to obtain
> information if f.e. "1" means 1 second or 1 millisecond or so.
>
>
>> time_spec: use 3 bit instead of 4 ? We just need a range from 0 to 7.
>>
>
> may be
>
>
>> Another idea could be to use a single byte instead and separate all
>> other flags. As compiler usually reserve bits from the bottom and as
>> this bitfield is the first is much easier for the cpu to extract this
>> number. Personally I would keep the bitfield reducing to 3 bits.
>> has_time, has_date and has_offset: they are fine. The only change I
>> would insert a TDS_USMALLINT _res:10 before. In such was all the
>> single bits will occupy the top position leaving space for extensions.
>> Order of the fields are optimized to reduce structure size.
>>
>> Do you think these changes are reasonable?
>
> :-)) hm, so what will be the final form ?
> typedef struct
> {
>    TDS_UINT8   time;
>    TDS_INT      date;
>    TDS_SMALLINT offset;
>    TDS_USMALLINT _res:10;    // <-- NEW (so total count of bits will be 16)
> ?
>    TDS_USMALLINT time_prec:3; // <-- CHANGED ?
>    TDS_USMALLINT has_time:1;
>    TDS_USMALLINT has_date:1;
>    TDS_USMALLINT has_offset:1;
> } TDS_DATETIMEALL;
>

Quite similar:

typedef struct
{
   TDS_UINT8   time;
   TDS_INT      date;
   TDS_SMALLINT offset;
   TDS_USMALLINT time_prec:3;
   TDS_USMALLINT _res:10;
   TDS_USMALLINT has_time:1;
   TDS_USMALLINT has_date:1;
   TDS_USMALLINT has_offset:1;
} TDS_DATETIMEALL;

I think I'll go with this. time with fixed precision is ok for me.
I think we agree to:
- have a single structure
- add _res field and change precision bits (structure above)
- have time with fixed precision

I'm not quite sure about date offset.

Frediano
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds