FreeTDS Development Group

[LacaK <lacak AT zoznam.sk>]

Frediano Ziglio  wrote / napísal(a):
> 2014-04-17 6:26 GMT+01:00 LacaK <lacak AT zoznam.sk>:
>   
> > Frediano Ziglio  wrote / napísal(a):
> >
> >     
> > > 2014-04-16 12:44 GMT+01:00 LacaK <lacak AT zoznam.sk>:
> > >
> > >       
> > > > 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;

>  Once decided we could
> define a new DBDATETIMEALL and check for future ABI changes.
>
>   
> > > > - add support to copy_data_to_host_var() function in dblib.c ?
> > > >
> > > >
> > > >         
> > > Ehmm... what do you mean?
> > >
> > >
> > >       
> > In db-lib is function copy_data_to_host_var() which is called when next
> > record is fetched to store fetched column values to local program variables
> > for bind columns.
> > If you look at this function there are performed type conversions very
> > simlar to dbconvert().
> > So we must add there also cases for SYBMSDATE...SYBMSDATETIMEOFFSET (like we
> > did for dbconvert())
> >
> >     
>
> Looks like extending dbconvert. Looks like a lot of duplicated code.
> The base key is that dbconvert should convert between dblib type to
> dblib type while copy_data_to_host_var should be conversion from
> libTDS to dblib. The fact that dbdata returns mainly libTDS make the
> functions very similar. Well... we could on the other way instead of
> returning TDS_DATETIMEALL return different part of the structure for
> different type, so for MSTIME we return the time, for MSDATE return
> just date, MSDATETIME return first two fields and for MSDATETIMEOFFSET
> return first 3 fields. There would still be the problem of getting
> precision for the type.
>   

Yes it is possible, but it complicates things.
Instead of one "struct" we will need define 4 new structs for each new 
"datetime" data type (f.e. DBDATE, DBTIME, DBDATETIME2, DBDATETIMEOFFSET)
So user can declare appropriate variables of this new types in his 
program, which will receive values of bind columns.

>   
> > > > If you give me direction I can prepare patch ...
> > > > (if it is good idea add support for BINDing ?)
> > > >
> > > >         
> > > Not good, excellent! And much easier as you don't have to store a
> > > buffer but you can provide what do you want to the client.
> > >
> > >       
> > Yes
> >
> > So only think we must agree on is : what "struct" we will use for new date
> > time data types ?
> > If it will be TDS_DATETIMEALL then it makes things easy and compact.
> > If it must be something else then my proposal is use same struct in libTDS
> > instead of TDS_DATETIMEALL.
> >
> >     
>
> That is change TDS_DATETIMEALL or use as is !
>   
Exactlly! ;-)
From me point of view and for me needs is existing TDS_DATETIMEALL good 
(as it is understandable and easy for use).
But if you think, that it will be good change it, then I will of course 
accept you decision.

Thanks
-Laco.