tackling sqlite byte-order problem (was: sqlite byte-order)

[David Relson]

On Wed, 09 Feb 2005 00:14:39 +0100
Matthias Andree wrote:

> Fletcher reported an endianness bug vs. the sqlite backend.
> 
> > Shouldn't a sqlite database created on a sparc be readable from an intel?
> > It looks to me like something in the sqlite interface is not byte swapping.
> >
> > gaos.cs.utexas.edu$ bogofilter </dev/null
> > Invalid robx value (2379.220992).  Must be between 0.0 and 1.0
> > gaos.cs.utexas.edu$ 
> >
> > gaos.cs.utexas.edu$ bogoutil -d wordlist.db | head
> > .WORDLIST_VERSION 885731585 0 535900417
> 
> [...]
> 
> This should have been .WORDLIST_VERSION 20040500 0 20050207.
> 
> The bug is that sqlite3 always returns false when datastore queries the
> "is swapped" state, but this problem is not sqlite specific and may also
> apply to TDB (I haven't checked if the TDB on-disk structures are
> independent of endianness).
> 
> How do we best tackle this?
> 
> I wonder if we should add a new token .ENDIAN32 (from datastore) that
> stores the hex value 0x01020304 in natural byte order and use that to
> figure if the database is byteswapped.

.ENDIAN32 sounds fine to me.  Clearly sqlite needs it.  Is it of value
for the other DB's we support?

Alternatively, since we _know_ that .WORDLIST_VERSION is of form
YYYYMMDD, we can split it into YYYY and MMDD and know that YYYY>MMDD is
correct while MMDD>YYYY indicates is_swapped.