| Subject: | MSSQL 'timestamp' columns should be treated like blobs |
MSSQL 'timestamp' columns are not actually timestamps, they are 8-byte blobs which store an incrementing version value whenever the row is changed, but do not actually store any date/time information whatsoever (in fact, in later versions of MSSQL they have started calling these 'RowVersion').
Currently S::L reads these as data_type => 'timestamp' but ribasushi suggested that it might be better for these to be treated as blobs since this is closer to what they actually are. He asked me to open this ticket and paste the below IRC chat log...
** scrollback from IRC **
<--snip-->
<ribasushi> vanstyn: are you certain it is a blob and it's not DBI/S::L doing switching on you?
<vanstyn> well, navicat shows it as a BLOB ...
<vanstyn> but... that might just be how it is exposed... yeah, this seems very much like what it is... thanks!
<ribasushi> vanstyn: I'd run S::L against it to make sure - it doesn't seem to have a special handling to do timestamp => blob (by a cursory browse of source)
<ribasushi> vanstyn: but if it *is* the timestamp thing - it is rather very common, especially among .net/asp software
<vanstyn> yeah, I did run it through S::L, and it shows data_type => 'timestamp'
<ribasushi> then that's what it is ;)
<ribasushi> navicat is DWIMing for you (because it is effectively a blob)
<ribasushi> in fact...
<vanstyn> and it also set inflate_datetime => 0, which makes me think S::L knows this
* robsco (~robsco@cpc65125-nmal16-2-0-cust242.19-2.cable.virginm.net) has joined #dbix-class
<vanstyn> so, yeah, mystery solved...
<ribasushi> ilmari: ^^ perhaps S::L should s/timestamp/blob/ and shove an extra metadatakey of "was such and such datatype"
<ribasushi> ilmari: so that this confusion doesn't happen again
<ribasushi> ( for MSSQL obv. )
* stelf|pc (~stelf@188-254-163-177.sf.ddns.bulsat.com) has joined #dbix-class
* stelf|pc_ has quit (Ping timeout: 360 seconds)
<ribasushi> vanstyn: and yes, Caelum did consider it, but added support in an ass-backwards way: https://github.com/dbsrgits/dbix-class-schema-loader/commit/81ade4d9da9b861849c51894b7e8f380e74192d4#diff-c3693ab39251ec52efb7839c3c0ca409R122
<vanstyn> ribasushi: thanks again!
<ribasushi> vanstyn: can you open a ticket against S::L with the conversation above so it doesn't get lost?