CODEWITHSUNDEEP
linuxmacossqlite
Johnny
Johnny

Reputation: 43

Sqlite data file on Linux and OS X incompatible?

I create a table and fill it by executing the following on an ARM Linux machine

~ # sqlite3 /mnt/mmc/test.db
SQLite version 3.6.12
sqlite> create table a (d);
sqlite> insert into a values (1.5);
sqlite> select * from a;
1.5

I then transfer the file to my Mac and execute the following

themac:~ me$ sqlite3 test.db 
SQLite version 3.6.12
sqlite> select * from a;
5.30239915051991e-315

Whaaat? I thought the data file was platform independent.

Upvotes: 4

Views: 754

Answers (2)

Matthew Slattery
Matthew Slattery

Reputation: 47058

I have no particular knowledge of SQLite, but this is indicative of a problem where two 32-bit words of a 64-bit IEEE 754 format double are swapped over, as you can see in this example (which was run using gcc on an x86 machine):

$ cat test.c
#include <stdio.h>

int main(void)
{
    union {
        double d;
        unsigned long long ull;
    } u;

    u.d = 1.5;
    printf("%016llx\n", u.ull);

    u.d = 5.30239915051991e-315;
    printf("%016llx\n", u.ull);

    return 0;
}

$ gcc -Wall -o test test.c
$ ./test
3ff8000000000000
000000003ff80000
$

Upvotes: 6

Robᵩ
Robᵩ

Reputation: 168836

The file format is platform independent, at least according to http://www.sqlite.org/onefile.html

A database in SQLite is a single disk file. Furthermore, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.

Is it possible that the file was corrupted during transfer? Can you run md5sum in both environments and confirm the files are identical?

Upvotes: 1

Related Questions