[sqlite] static sqlite database

[Hick Gunter]

Apart from the fact that latitude and longitude have defined ranges +-90 and 
+-180 respectively, why are you choosing SQLite as the storage format for 
densely populated single table of constant <whatever> if all you want to do is 
read the values?

If you are looking for a minimum space, uncompressed format you might as well 
memory-map the C structure.

        typedef struct point_t {
            double lat,lon;
         };

        typedef struct table_t {
            uint64_t cnt;
            point_t  pnt[1];
        };




     -----Urspr?ngliche Nachricht-----
     Von: Igor Stassiy [mailto:istassiy at gmail.com]
     Gesendet: Mittwoch, 10. Juni 2015 14:45
     An: sqlite-users at mailinglists.sqlite.org
     Cc: Kirill Afanasev
     Betreff: [sqlite] static sqlite database

     Hello,

     I have a question about controlling the size of sqlite database containing 
records of the form id -> payload where payload has fixed size and id is an 
integer column.

     I am developing a simple storage for points in an sqlite database, the 
problem is that space consumption on disk is slightly unsatisfying. Here is 
what I mean:

     A program to generate some data for the database:

     #include <cstdlib>#include <cstdint>#include <iostream> using namespace 
std;

     int main() {
       int n;
       cin >> n;

       for(int i = 0; i < n; i++) {
         double lat = 180*(rand()*1.0/RAND_MAX);
         double lon = 180*(rand()*1.0/RAND_MAX);
         int64_t id = rand()*1LL*rand();
         cout << id << "," << lat << "," << lon << "\n";
       }

       return 0;}

     To import the data run:

     g++ filldb.cpp
     echo 100000 | ./a.out > db.text
     sqlite3 test.db
     sqlite> create table points(id UNSIGNED INT, lat REAL, lon REAL);
     sqlite> .separator , .mode csv .import db.text points

     The size of the database right after this shows 3.3M, however the "real"
     size of data is 100000*(8 bytes)*3 = 2.4M and after

     sqlite> create index points_index on points(id);

     the database size more than doubles to 5.1M

     Changing id UNSIGNED INT to INTEGER PRIMARY KEY does help, but is there 
anything else to be done, in particular when the database is static (we know 
there wont be any inserts)?

     Thank you

     Igor
     _______________________________________________
     sqlite-users mailing list
     sqlite-users at mailinglists.sqlite.org<mailto:sqlite-users at 
mailinglists.sqlite.org>
     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


     ___________________________________________
      Gunter Hick
     Software Engineer
     Scientific Games International GmbH
     FN 157284 a, HG Wien
     Klitschgasse 2-4, A-1130 Vienna, Austria
     Tel: +43 1 80100 0
     E-Mail: hick at scigames.at

     This communication (including any attachments) is intended for the use of 
the intended recipient(s) only and may contain information that is 
confidential, privileged or legally protected. Any unauthorized use or 
dissemination of this communication is strictly prohibited. If you have 
received this communication in error, please immediately notify the sender by 
return e-mail message and delete all copies of the original communication. 
Thank you for your cooperation.