Re: [sqlite] Booleans in SQLite

[Rich Shepard]

On Mon, 7 Sep 2009, Jim Showalter wrote:

> Oracle doesn't have a native boolean type. You have to use INTEGER and
> interpret it.
>
> MySQL doesn't have a boolean type (it's just a synonym for TINYINT).
>
> SQL Server doesn't have a boolean type. You have to use BIT and
> interpret it.

   PostgreSQL has a boolean type. It can be 'true' or 'false'; unknown is
represented by the standard NULL type.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Jim Showalter]

Oracle doesn't have a native boolean type. You have to use INTEGER and 
interpret it.

MySQL doesn't have a boolean type (it's just a synonym for TINYINT).

SQL Server doesn't have a boolean type. You have to use BIT and 
interpret it.

- Original Message - 
From: "Mark Hamburg" <m...@grubmah.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, September 07, 2009 10:27 AM
Subject: Re: [sqlite] Booleans in SQLite


> The real argument for adding boolean support is not about space but
> about compatibility with dynamic languages with a boolean type that
> are exploiting SQLite's dynamic typing of values. Without a boolean
> type in SQLite, a glue layer has to guess whether a 0 means zero or
> false or a "NO" means the string "NO" or false or...
>
> Mark
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Mark Hamburg]

The real argument for adding boolean support is not about space but  
about compatibility with dynamic languages with a boolean type that  
are exploiting SQLite's dynamic typing of values. Without a boolean  
type in SQLite, a glue layer has to guess whether a 0 means zero or  
false or a "NO" means the string "NO" or false or...

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Mark Spiegel]

D. Richard Hipp wrote:
> Most varints are "type varints" and type varints are almost always a  
> single byte (the only exceptions being for large blobs or strings).   
> Varints are also used to store the total number of bytes in a row  
> (also usually one byte).  Most varints are a single byte.
>
> We, too, have profiled, and we agree that a lot of time is spent  
> decoding varints.  As you have already observed, the common case of a  
> single-byte varint is usually handled by by a macro and so never calls  
> the sqlite3GetVarint() decoder function.  And sqlite3GetVarint() is  
> very carefully coded to be fast even when it is called.  The varint  
> decoder is one of the more carefully scrutinized parts of SQLite.
>
> I'm scanning through some profiling output now and I'm seeing that  
> some varints are almost always a single byte (only 60 multibyte  
> varints out of 474350, in one example) while others are multibyte  
> about half the time.  I'm not seeing any cases where more then half  
> the varints are multibyte.
>
>
>   
Sorry I missed the obvious.  Multi-byte about half the time.  Read the 
numbers, skipped the words.  My grade school teachers would not be 
surprised. 

Question already answered.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Mark Spiegel]

D. Richard Hipp wrote:
> Most varints are "type varints" and type varints are almost always a  
> single byte (the only exceptions being for large blobs or strings).   
> Varints are also used to store the total number of bytes in a row  
> (also usually one byte).  Most varints are a single byte.
>
> We, too, have profiled, and we agree that a lot of time is spent  
> decoding varints.  As you have already observed, the common case of a  
> single-byte varint is usually handled by by a macro and so never calls  
> the sqlite3GetVarint() decoder function.  And sqlite3GetVarint() is  
> very carefully coded to be fast even when it is called.  The varint  
> decoder is one of the more carefully scrutinized parts of SQLite.
>
> I'm scanning through some profiling output now and I'm seeing that  
> some varints are almost always a single byte (only 60 multibyte  
> varints out of 474350, in one example) while others are multibyte  
> about half the time.  I'm not seeing any cases where more then half  
> the varints are multibyte.
>
>   
Right.  I observed that in the single byte case, the macro prevents the 
calling of the varint funtions.  The last time I profiled this was after 
Shane worked it over last year.  I believe the actual functions (not 
code generated by the macro) accounted for about 6% of the time spent in 
SQLite during our performance test suite.  Obviously, that is going to 
be highly variable depending on the type of data contained, and the 
types of  operations performed, etc.   Clearly the varints in some 
cases, will be almost exclusively single byte.  What I'm more curious 
about is their overall usage.  Are we looking at 60/474350 for the 
entire database (in your example) or just one particular use within the 
database?

FWIW, I experimented with several different encoding schemes that 
preserved the single byte properties and was quickly able to cut the 
time consumed in our profiling test in half.  Unfortunately, they all 
broke compatibility.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[D. Richard Hipp]

On Sep 3, 2009, at 8:25 PM, Mark Spiegel wrote:

> D. Richard Hipp wrote:
>> You are both right and both wrong.  There are two different integer
>> representations used in SQLite.
>>
>> (1) "varint" or variable length integer is an encoding of 64-bit
>> signed integers into between 1 and 9 bytes. ...
>> (2) When you store an integer into a column (a column other than the
>> rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed
>> integer. ...
> That makes sense.  Thank you for clarifying.  One further question.   
> It
> seems when we profile, that a lot of time is spent encoding and  
> decoding
> varints.  Are there really that many multi-byte varints in use in the
> system?


Most varints are "type varints" and type varints are almost always a  
single byte (the only exceptions being for large blobs or strings).   
Varints are also used to store the total number of bytes in a row  
(also usually one byte).  Most varints are a single byte.

We, too, have profiled, and we agree that a lot of time is spent  
decoding varints.  As you have already observed, the common case of a  
single-byte varint is usually handled by by a macro and so never calls  
the sqlite3GetVarint() decoder function.  And sqlite3GetVarint() is  
very carefully coded to be fast even when it is called.  The varint  
decoder is one of the more carefully scrutinized parts of SQLite.

I'm scanning through some profiling output now and I'm seeing that  
some varints are almost always a single byte (only 60 multibyte  
varints out of 474350, in one example) while others are multibyte  
about half the time.  I'm not seeing any cases where more then half  
the varints are multibyte.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Mark Spiegel]

D. Richard Hipp wrote:
> You are both right and both wrong.  There are two different integer  
> representations used in SQLite.
>
> (1) "varint" or variable length integer is an encoding of 64-bit  
> signed integers into between 1 and 9 bytes.  Negative values use the  
> full 9 bytes as do large positive values.  But small non-negative  
> integers use just one or two bytes.  Varints are used in places where  
> integers are expected to be small and non-negative, such as record  
> sizes in the btree (usually less than 100 bytes) and also for rowids.
>
> (2) When you store an integer into a column (a column other than the  
> rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed  
> integer.  The smallest possible representation is used, depending on  
> the magnitude of the integer.  The size used is actually recorded in a  
> separate varint (the "type" varint) that also determines that the  
> value stored is an integer and not (say) a string or blob or floating  
> point number or NULL.  A type varint of 1 means store a 1-byte  
> integer.  A type varint of 2 means store a 2-byte integer.  And so  
> forth.  A type varint of 8 (I think) means the value is exactly 0 so  
> don't store anything.  9 means the value is exactly 1.  And so forth.   
> Notice that the type varints are all small integers and are thus  
> themselves represented by a single byte.  Every value stored has a  
> type varint.  Additional data is stored as necessary.  A zero-byte  
> string or blob stores uses no space beyond its type varint.  A NULL  
> uses no space beyond its type varint. A numeric 0 or 1 uses no space  
> beyond its type varint.  An integer between -127 and +127 uses 1  
> additional byte beyond its varint.  A 1MB blob uses a million bytes of  
> additional space beyond its type varint.  And so forth.
>
>   
That makes sense.  Thank you for clarifying.  One further question.  It 
seems when we profile, that a lot of time is spent encoding and decoding 
varints.  Are there really that many multi-byte varints in use in the 
system? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[D. Richard Hipp]

On Sep 3, 2009, at 7:25 PM, Mark Spiegel wrote:

> Jay A. Kreibich wrote:
>>  Integer values between -128 and 127 use only a single byte of  
>> storage above and beyond  the header size that all values have.
>>
> Not quite.  Values between 0 & 127 use 1 byte of storage.  Negative
> values use the full 9 bytes in my experience.  (I'm setting aside the
> integer 0 and integer 1 optimizations outlined in
> http://www.sqlite.org/fileformat.html#record_format with that  
> statement.)

You are both right and both wrong.  There are two different integer  
representations used in SQLite.

(1) "varint" or variable length integer is an encoding of 64-bit  
signed integers into between 1 and 9 bytes.  Negative values use the  
full 9 bytes as do large positive values.  But small non-negative  
integers use just one or two bytes.  Varints are used in places where  
integers are expected to be small and non-negative, such as record  
sizes in the btree (usually less than 100 bytes) and also for rowids.

(2) When you store an integer into a column (a column other than the  
rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed  
integer.  The smallest possible representation is used, depending on  
the magnitude of the integer.  The size used is actually recorded in a  
separate varint (the "type" varint) that also determines that the  
value stored is an integer and not (say) a string or blob or floating  
point number or NULL.  A type varint of 1 means store a 1-byte  
integer.  A type varint of 2 means store a 2-byte integer.  And so  
forth.  A type varint of 8 (I think) means the value is exactly 0 so  
don't store anything.  9 means the value is exactly 1.  And so forth.   
Notice that the type varints are all small integers and are thus  
themselves represented by a single byte.  Every value stored has a  
type varint.  Additional data is stored as necessary.  A zero-byte  
string or blob stores uses no space beyond its type varint.  A NULL  
uses no space beyond its type varint. A numeric 0 or 1 uses no space  
beyond its type varint.  An integer between -127 and +127 uses 1  
additional byte beyond its varint.  A 1MB blob uses a million bytes of  
additional space beyond its type varint.  And so forth.


> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Mark Spiegel]

Jay A. Kreibich wrote:
>   Integer values between -128 and 127 use only a single byte of storage above 
> and beyond  the header size that all values have.
>   
Not quite.  Values between 0 & 127 use 1 byte of storage.  Negative 
values use the full 9 bytes in my experience.  (I'm setting aside the 
integer 0 and integer 1 optimizations outlined in 
http://www.sqlite.org/fileformat.html#record_format with that statement.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Jay A. Kreibich]

On Thu, Sep 03, 2009 at 03:47:50PM -0400, Wilson, Ronald scratched on the wall:

> >   You can convert a V1 database into a V4 database by opening it,
> >   setting the legacy PRAGMA to false, and the VACUUMing the database.

> If this is true, the documentation doesn't even hint at the feature:
> "This flag only affects newly created databases. It has no effect on
> databases that already exist."

  I agree that the docs are a bit light on this point.

  I would have never guessed about the VACUUM thing, if it wasn't for
  the fact I ran across this line in the 3.2.8 to 3.3.0 section of
  :
  
 "Once a database file is created, its format is fixed. So
 a database file created by SQLite 3.2.8 and merely modified
 by version 3.3.0 or later will retain the old format. 
 Except, the VACUUM command recreates the database so
 running VACUUM on 3.3.0 or later will change the file
 format to the latest edition."

  That is, the way VACUUM essentially works is that it copies everything
  to a temp database, creates a brand-new-from-scratch database, and
  copies everything back.  When it creates the new database, that
  database takes on the current defaults.

  If you stop and think about it, it isn't that big of a surprise.  You
  can use VACUUM to change the page size and a bunch of other "fixed"
  parameters.  However, it might be nice if the docs were a bit more
  forthcoming about this point.

> One way to test it would be to try opening an up-converted database in a
> version previous to 3.3.0, which I don't have handy.

  The other way (which is what I did) is to just dump the first ~50 
  bytes of the file and see what version is set.  See "44..47" here:
  . 
  
  It would be kind of nice if there was some PRAGMA command that
  dumped all this type of info for the current "main" database.  I
  don't remember if sqlite3_analyzer does or not.

   -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

Re: [sqlite] Booleans in SQLite

[Wilson, Ronald]

> Heh.  But, actually, why doesn't SQLite3 produce an error when unknown
> pragmas are used?  Wouldn't that be the right thing to do?  I would
> thinks so.

I think the docs say unknown pragmas are treated like no-ops.  (yeah, I
read the docs.)

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Nicolas Williams]

On Thu, Sep 03, 2009 at 04:21:28PM -0400, Wilson, Ronald wrote:
> > Wrong pragma.  Try:
> 
> Thanks.  I'm going to stop talking for a few days now.  Enough gaffs for
> one day.

Heh.  But, actually, why doesn't SQLite3 produce an error when unknown
pragmas are used?  Wouldn't that be the right thing to do?  I would
thinks so.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Wilson, Ronald]

> Wrong pragma.  Try:
> 
> sqlite> pragma legacy_file_format;
> 1
> sqlite> pragma legacy_file_format=0;
> sqlite> pragma legacy_file_format;
> 0
> sqlite>

Thanks.  I'm going to stop talking for a few days now.  Enough gaffs for
one day.

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Nicolas Williams]

On Thu, Sep 03, 2009 at 03:57:14PM -0400, Wilson, Ronald wrote:
> Hmm.  I can't get the pragma to return a value at all.
> 
> SQLite version 3.6.10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma default_file_format;

Wrong pragma.  Try:

sqlite> pragma legacy_file_format;
1
sqlite> pragma legacy_file_format=0;
sqlite> pragma legacy_file_format;
0
sqlite> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Wilson, Ronald]

> I would test it, but "When the pragma is issued with no argument, it
> returns the setting of the flag. This pragma does not tell which file
> format the current database is using. It tells what format will be
used
> by any newly created databases."

Hmm.  I can't get the pragma to return a value at all.

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma default_file_format;
sqlite> create table test (i);
sqlite> pragma default_file_format;
sqlite> insert into test values(1);
sqlite> pragma default_file_format;
sqlite>

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma default_file_format;
sqlite> pragma default_file_format=false;
sqlite> pragma default_file_format;
sqlite> pragma default_file_format=true;
sqlite> pragma default_file_format;
sqlite>

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Wilson, Ronald]

>   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

If this is true, the documentation doesn't even hint at the feature:
"This flag only affects newly created databases. It has no effect on
databases that already exist."

I would test it, but "When the pragma is issued with no argument, it
returns the setting of the flag. This pragma does not tell which file
format the current database is using. It tells what format will be used
by any newly created databases."

One way to test it would be to try opening an up-converted database in a
version previous to 3.3.0, which I don't have handy.

RW
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Dan Bishop]

Nicolas Williams wrote:
> On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote:
>   
>> 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.
>> 
> ...
>>   I would just like to reduce some memory
>> overhead as well as use those keywords instead of creating integer
>> fields using 0 and 1.
>> 
>
> SQLite3 uses a variable length encoding for integers on disk, so you
> will gain no space on disk by having a native boolean type.
And according to http://www.sqlite.org/fileformat.html#record_format, 
there's even a special optimization for the integers 0 and 1.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[sub sk79]

>
>                I'm just curious how difficult it would be to add
> support for booleans in SQLite.
>
> as well as use those keywords instead of creating integer
> fields using 0 and 1.
>

Check out StepSqlite PL/SQL compiler for SQLite which supports
BOOLEAN data type among many other goodies.

http://www.metatranz.com/stepsqlite

It lets you script SQLite to do things like:

create table  admissions(fn varchar(20), ln varchar(20), accepted BOOLEAN);
begin
  for student in (select * from admissions where accepted = TRUE)
  loop
dbms_output.put_line('First name:' || student.fn || '  Last name:'
|| student.ln || '  Accepted: '||student.accepted );

 if student.accepted then
  dbms_output.put_line(student.fn ||'  '|| student.ln || '  has
been admitted.' );
 else
  dbms_output.put_line(student.fn ||'  '|| student.ln || '  has
NOT been admitted.' );
 end if;
  end loop;
end;

cheers :-)
SK
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Jay A. Kreibich]

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

[sqlite] Booleans in SQLite

[Shaun Seckman (Firaxis)]

Hello everyone,

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.



I understand that ANSI C doesn't have native support for
booleans and that's fine, I would just like to reduce some memory
overhead as well as use those keywords instead of creating integer
fields using 0 and 1.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Nicolas Williams]

On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote:
> 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.

The SQL Standard does specify and OPTIONAL boolean type.

> I understand that ANSI C doesn't have native support for
> booleans and that's fine,

ANSI C99 most certainly does have a boolean type.

>   I would just like to reduce some memory
> overhead as well as use those keywords instead of creating integer
> fields using 0 and 1.

SQLite3 uses a variable length encoding for integers on disk, so you
will gain no space on disk by having a native boolean type.

Well, if you had a table with a single column and that column was a
boolean, an RDBMS could express the entire table as a sparse bitstring
indexed by row number/ID/OID, with two bits per row (two bits would be
needed to encode: true, false, null/unknown, and "this record doesn't
exist").  But such a thing would be a huge new feature for SQLite3, with
very few uses.

And you're not really going to save memory in SQLite3 by having a
boolean type either -- 31 or 63 bits are a wash in the context of
evaluating a prepared statement.

_Your_ application could save memory by treating booleans as a single
bit and packing them into bitstrings, but you can do that now without
any help from SQLite3.

A group_concat() like aggregation function could be defined that groups
and concatenates boolean inputs into a bitstring -- this might be
helpful to you.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Pavel Ivanov]

> I would just like to reduce some memory
> overhead as well as use those keywords instead of creating integer
> fields using 0 and 1.

I'm curious, what "memory overhead" do you think will be reduced in
case of using booleans instead of integers?

Pavel

On Wed, Sep 2, 2009 at 5:44 PM, Shaun Seckman
(Firaxis)<shaun.seck...@firaxis.com> wrote:
> Hello everyone,
>
>                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.
>
>
>
>                I understand that ANSI C doesn't have native support for
> booleans and that's fine, I would just like to reduce some memory
> overhead as well as use those keywords instead of creating integer
> fields using 0 and 1.
>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users