Re: [sqlite] How does SQLite store data?
<[EMAIL PROTECTED]> wrote: > > I am not aware of a BOOLEAN type. > The types defined in the website are: NULL, INTEGER, REAL, TEXT and BLOB. > Is BOOLEAN a hidden type? > BOOLEAN is not a different type. What Dennis meant was that integer values 0 and 1 are stored more efficiently in the new format - 1 byte instead of 2. The integer values 0 and 1 are commonly used as booleans, so we use the term "boolean type" in conversation, though in truth a boolean is really just an integer. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
Dennis Cote <[EMAIL PROTECTED]> wrote: > John Stanton wrote: > > It does not have fixed length columns except for the ones which hold > > integer and real numbers and boolean values. > > > Actually, integers are stored in a variable length format as well. It > takes less space to store smaller integer values than it does to store > large values. This allows sqlite to handle full 64 bit integers, but > does not waste space storing unused leading zero bits for most fields > that typically use a much smaller range of integer values. > > It also has a new file format (no longer the default) that stores > booleans more efficiently. Use pragma legacy_file_format=off when > initializing the database to select this format. I believe booleans > values are fixed size in both formats. > > Dennis Cote > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > I am not aware of a BOOLEAN type. The types defined in the website are: NULL, INTEGER, REAL, TEXT and BLOB. Is BOOLEAN a hidden type? Ray - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
P Kishor wrote: interesting. As far as _I_ know, the first implementation of varint! No, this idea has been around for a long time. It was used for ISDN addressing for example. I'm sure it is probably in Knuth somewhere. It is still a very good idea though. is it reasonable to assume that the legacy_file_format is incompatible with the postmodern_file_format? And, which version+ of SQLite has this new format capability? Yes it is incompatible. It was originally introduced in version 3.3.0 and was set as the default format for new databases until version 3.3.7 where the legacy format became the default again so that database file were backwards compatible by default. All versions since 3.3.0 can handle both formats. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
On 3/26/07, Dennis Cote <[EMAIL PROTECTED]> wrote: John Stanton wrote: > It does not have fixed length columns except for the ones which hold > integer and real numbers and boolean values. > Actually, integers are stored in a variable length format as well. It takes less space to store smaller integer values than it does to store large values. This allows sqlite to handle full 64 bit integers, but does not waste space storing unused leading zero bits for most fields that typically use a much smaller range of integer values. interesting. As far as _I_ know, the first implementation of varint! It also has a new file format (no longer the default) that stores booleans more efficiently. Use pragma legacy_file_format=off when initializing the database to select this format. I believe booleans values are fixed size in both formats. is it reasonable to assume that the legacy_file_format is incompatible with the postmodern_file_format? And, which version+ of SQLite has this new format capability? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
John Stanton wrote: It does not have fixed length columns except for the ones which hold integer and real numbers and boolean values. Actually, integers are stored in a variable length format as well. It takes less space to store smaller integer values than it does to store large values. This allows sqlite to handle full 64 bit integers, but does not waste space storing unused leading zero bits for most fields that typically use a much smaller range of integer values. It also has a new file format (no longer the default) that stores booleans more efficiently. Use pragma legacy_file_format=off when initializing the database to select this format. I believe booleans values are fixed size in both formats. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
There are no stupid questions. only stupid answers. Sqlite stores the entire length of the string and never truncates. Its TEXT type handles every string, provided that it is text, otherwise it needs to be a BLOB (e.g. a JPEG). Sqlite is simpler than you can imagine to use because its manifest typing removes almost all of the concern about data types. Migrating from other SQL RDBMS's to Sqlite is straight forward but going the other way is not necessarily easy. Jonathon Blake wrote: John wrote: A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. Stupid question. Does that mean that SQLite: * truncates the field at 80 characters? * only returns the first 80 characters in the field? * stores and returns the entire string? My impression was that it did that third option. [And that it didn't care what the data in that field was. It is up to the client program to verify the data.] xan jonathon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
John wrote: A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. Stupid question. Does that mean that SQLite: * truncates the field at 80 characters? * only returns the first 80 characters in the field? * stores and returns the entire string? My impression was that it did that third option. [And that it didn't care what the data in that field was. It is up to the client program to verify the data.] xan jonathon - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
Sqlite has a concept called "manifest typing" where it makes decisons on how to store data. It does not have fixed length columns except for the ones which hold integer and real numbers and boolean values. A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. [EMAIL PROTECTED] wrote: I created several tables that specified explicitly the size of each column. I put some bogus numbers and text into it but I didn't put numbers in it that would completely fill the column data. I was surprised to find that the resulting database file was smaller than I had expected. This implies that SQLite stores the data in its smallest space on the disk. Is this correct? Ray - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
