Is SQLite Cross-Platform Really?

Question

I use SQLite to store some data. The primary database is on the NAS (Debian Lenny, 2.6.15, armv4l), because the NAS runs a script that updates data every day. A typical "select * from tableX" looks like this:

2010-12-28|20|62.09|25170.0 2010-12-28|21|49.28|23305.7 2010-12-28|22|48.51|22051.1 2010-12-28|23|47.17|21809.9 

When I copy the database to my main computer (Mac OS X) and run the same SQL query, the output is:

 2010-12-28|20|1.08115035175016e-160|25170.0 2010-12-28|21|2.39343503830763e-259|-9.25596535779558e+61 2010-12-28|22|-1.02951149572792e-86|1.90359837597183e+185 2010-12-28|23|-1.10707273937033e-234|-2.35343828462275e-185 

The third and fourth columns are of type REAL. An interesting fact: when the numbers are integers (that is, they end with ".0"), there is no difference between the two bases. In all other cases, the differences ... hmm ... surprising? I can not find the template.

If anyone has a key - please share!

PS: sqlite3 -version Debian output: 3.6.21 (lenny-backports) Mac OS X: 3.6.12 (10.6)

Answer 1

SQLite release 3.4.0 adds a compile-time flag.

• Added SQLITE_MIXED_ENDIAN_64BIT_FLOAT compilation option to support dumb ARM7 processors.

I had the same problem with an Arm920Tid device and my x86-based virtual machine. The hand device was writing data, and I was trying to read it on an x86 virtual machine (or on my Mac).

After adding this compile-time flag to my makefile for my arm assembly, I was able to get normal values ​​when I requested DB on both platforms.

For reference, I am using sqlite 3.7.14

Answer 2

The file format must say that REAL is stored in big-endian format, which would be architecturally invariant if both assemblies were serialized correctly.

A value of 7 stored in the header of the database record indicates that the corresponding database value is real SQL (floating point number). In this case, the data block contains an 8-byte IEEE floating point number , stored in byte byte order .