On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) scratched on the wall:
> I'm just curious how difficult it would be to add > support for booleans in SQLite. This would most likely involve adding a > new type affinity as well as adding "true" and "false" keywords to the > lexer. There's much more that could be done but I'm just looking for > rudimentary support. It is easier to just use integers. As most of you know, in SQLite3 the size of the storage data for an integer scales, depending on the size of the integer. Integer values between -128 and 127 use only a single byte of storage above and beyond the header size that all values have. That's not nothing, but it is pretty small. If you want syntax, write a TRUE() function that returns 1, and a FALSE() function that returns 0. You could also write a BOOL(x) function that returns a 'true' or 'false' string (or 'T' and 'F') if passed a 1 or 0 integer value, and returns an integer 1 or 0 if passed the strings 'true', 'T', 'false' or 'F'. That wouldn't be a new native type, exactly, but it would make for some fairly readable SQL and the ability to generate reports with 'T' and 'F' rather than 1 and 0. As for data savings, the Version 4 file format extended the integer data representation to include "zero length" integers. This was done specifically to make Boolean values even less expensive. The integer values 0 and 1 have a specific storage type that requires no additional data, similar to the NULL value type. This saves one data byte per Boolean value. OK, let me see if I can get this straight.... *deep breath* The V4 file was introduced and made the default in 3.3.0. About eight months later, in 3.3.6, the default format for new databases was changed back to the old V1 file format. However, every version of SQLite since 3.3.0 has had the ability to read and write the newer file format-- the only change was to the default format used for new databases. Once a database is created in a specific format, it stays in that format (until you VACUUM it). To build SQLite such that it uses the new format by default, define the build option SQLITE_DEFAULT_FILE_FORMAT=4. You can also specify the default format for new database at runtime via the "PRAGMA legacy_file_format" command. If you want to make a new database in the V4 format, you'd set "PRAGMA legacy_file_format=false" as soon as you open the database, before any I/O is done (e.g. before the first CREATE TABLE). You can convert a V1 database into a V4 database by opening it, setting the legacy PRAGMA to false, and the VACUUMing the database. You can convert back in a similar way. In fact, be cautious of that. If you have a build that defaults to V1, make sure you turn the legacy PRAGMA off before you VACUUM a V4 database, or it will convert it back to V1 (I think...). http://www.sqlite.org/compile.html#default_file_format http://www.sqlite.org/pragma.html#pragma_legacy_file_format I think that's all correct. I'm sure someone will jump in and correct me if I got something mix up. It's a bit confusing. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
