Size and limits in SQLite (32 or 64 Bits ?)

  1. last year

    Emile S

    22 Oct 2018 Europe (France, Strasbourg)

    It is not really clear in sqlite.org what SQLite have as limits and size.

    1. 32 or 64 Bits ?
    SQLite seems to be 32 Bits (whatever that means).

    2. Size and limits
    How many TABLE can I add in a SQLite Data Base ?
    How many Columns in a Record can I add in a SQLite TABLE ?
    I created one that have 81 Columns.

    I suppose that the number of Records is far above that (and depends on the number of TABLEs; and data in the Records are affected by those).

    I suppose that the more TABLE(s) and Columns, the more time the data takes to be read or/and write.

    I also know that a Listbox have a finite number of Columns (64 or 256); and here comes my trouble in mind.

    Usually, my mouth emit strange noises when I read in a book the variables limits (Integer for example), since that value does not change from IDE to another IDE / Language and you get that information in the language reference and in all books about that IDE:(
    But @ sqlite.org , this was not explicitely given (and I had the Listbox max # of column [64 or 256, depends on Xojo version] in mind and that make troubles in my mind).

    All: how do I limit to 64 or 256 columns a read in sqlite ?
    (to avoid errors at Listbox display time ?)

    All
    I have a db with 62 columns nd displaying (data for) that amount of columns in a Listbox is not easy to read.
    I use three windows to fullfil the DB (that is one Cardboard A4 page) and I have three other pages to work with (mainly CheckBox / RadioButtons).
    Do you have experience of this kind of amount of Columns (or more) ?

    Dave / DB Schema Reader providers:
    How do you deal with a 50 TABLEs or 129 Columns in a TABLE in your Tadpole / “Application” software ?
    (SQLite DB Graphic Design)

  2. Christian S

    22 Oct 2018 Pre-Release Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    SQLite can be build for 32 and 64bit.
    Integers in SQLite are 64-bit.

    Records is not limited. Column limit is 2000 by default, but can be raised.

    see limits website

  3. Emile S

    22 Oct 2018 Europe (France, Strasbourg)

    Hi Christian,

    that is the location I read earlier. I do not recall where it says it is 32 bits (I read a lot of pages).

    Thanks for the tips.

    2000 Columns: i am dreaming… how one can display that amount of columns ;)

  4. Tim L

    29 Oct 2018 Pre-Release Testers, Xojo Pro Phoenix, AZ, USA
    Edited last year

    @ChristianSchmitz ...Column limit is 2000 by default, but can be raised...

    Yikes! That reminds me of the saying, "Just because you can, it doesn't mean you should, but someone will."

  5. Neil B

    29 Oct 2018 Pre-Release Testers

    @Emile S how one can display that amount of columns

    With a 296" Super-Duper wide screen... No seriously it would for an end user app a single record would be edited in a form or several forms. For a db manager there's a thing called horizontal scroll bar. For several thousand columns you would definitely want to use some type of virtualization.
    https://stackoverflow.com/questions/5337112/wpf-datagrid-1000-columns-x-1200-rows

  6. Emile S

    29 Oct 2018 Europe (France, Strasbourg)

    Thank you Tim for the smile.

    Neil:
    It was nearly a joke. When I have a low number of Columns, I can display them all in a Listbox.

    Actually, I have - say - 64 Columns: too many columns (IMHO) for a single Listbox, but since I have three windows full of Controls (for these 64 columns), I can think to devide the db contents / 3, and display them into three different windows/Listboxes…

    I actually have coded a window with two Listbox:
    a. One Listbox to display the db contents sorted by letters (actually a thru z, since no name start with a number 26 “Tabs” are enough). A click in a letter populates the second Listbox.
    In short: a click in the A Tab display all Names that starts with a A. And so on.

    b. Another listbox that displays the people’s first name, family name and birth date.

    A double click in an entry of Listbox b will open that Record ans display its contents into the correspondant windows, so the user can watch its contents.

    Of course, this actually works fine with very few Records: I do not have an idea on how this can be with say >1,000 Records in the RecordSet (the speed to get the data)… This is a bit like terra incognita for me.

    And if the project goes to its end, I will have a bit more columns: I can manage to display portions of the number of columns "sorted" by original sheet of paper Pages. The users only have to know what part of the record they want to read.

    Fortunately, once each record is written to the db, there is only a small number of reasons to access to them (statistics: more often: once a year ?). The more accessed data will come from the history db (one entry for each meeting / visit). Each entry is 6 fields (First Name, Family Name, Birth Date, Today date) plus two Service data fields and one Report text stored in a TextArea that explain what we’ve talked / what to do.

    PS: I am watching on TV a Ridley Scott movie from 2015 with Matt Damon (Single on Mars ? or Alone on Mars ?), so I also have a Terra Incognita there too.

  7. Dave S

    29 Oct 2018 San Diego, California USA

    If you ever find your self designing a 2000 column database table.... Seriously.... take a step back (heck take 3 steps back), and think about relational tables, sparse data and how a data vector of that size is going to impact performance... you most likely will come up with a MUCH better design.

  8. Tim S

    29 Oct 2018 Pre-Release Testers Canterbury, UK

    @ChristianSchmitz SQLite can be build for 32 and 64bit.
    Integers in SQLite are 64-bit.

    On disk, integers take only the number of bytes required (see https://www.sqlite.org/datatype3.html ). However in memory they will be 64 bits.

    I also recall that integer values 0 and 1 take no space at all but can't find a reference to that.

  9. @Tim S

    0 and 1 really DO take space.

    Any information stored will use some physical space; simple mathematics.
    At least one bit ...

  10. Tim S

    30 Oct 2018 Pre-Release Testers Canterbury, UK

    @J HTimmerman @Tim S

    0 and 1 really DO take space.

    Well you might imagine they would take a byte. But IIRC there is already a field indicating to SQLite internally whether a particular integer column takes 1, 2, 3, 4, 6, or 8 bytes (depending on the integer's value). That field is itself a byte and two of its values are dedicated to integer values 0 and 1.

  11. Tim S

    30 Oct 2018 Pre-Release Testers Canterbury, UK

    OK I found it. See here and look at Section 2.1, Record Format. Seems that a NULL "value" also occupies no space.

  12. Dave S

    30 Oct 2018 San Diego, California USA
    Edited last year

    well I'll be darned... :)

    Serial Type Codes Of The Record Format
    Serial Type Content Size Meaning
    0 0 Value is a NULL.
    1 1 Value is an 8-bit twos-complement integer.
    2 2 Value is a big-endian 16-bit twos-complement integer.
    3 3 Value is a big-endian 24-bit twos-complement integer.
    4 4 Value is a big-endian 32-bit twos-complement integer.
    5 6 Value is a big-endian 48-bit twos-complement integer.
    6 8 Value is a big-endian 64-bit twos-complement integer.
    7 8 Value is a big-endian IEEE 754-2008 64-bit floating point number.
    8 0 Value is the integer 0. (Only available for schema format 4 and higher.)
    9 0 Value is the integer 1. (Only available for schema format 4 and higher.)

    10,11 variable Reserved for internal use. These serial type codes will never appear in a well-formed database file, but they might be used in transient and temporary database files that SQLite sometimes generates for its own use. The meanings of these codes can shift from one release of SQLite to the next.
    N≥12 and even (N-12)/2 Value is a BLOB that is (N-12)/2 bytes in length.
    N≥13 and odd (N-13)/2 Value is a string in the text encoding and (N-13)/2 bytes in length. The nul terminator is not stored.

  13. Christian S

    30 Oct 2018 Pre-Release Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    that is good news that boolean values (1 and 0) takes no space beside type.

or Sign Up to reply!