TEXT storage

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

TEXT storage

slowpoison
Hi,

I was unable to find a reference to how TEXT data types are stored in
the db files. The file format comment in btreeInt.h is too complex (or
may be the wrong place) for me to deduce what I'm looking for.

I want to know whether a TEXT field, when stored, will always take the
exact amount of space allocated for it in the schema definition. So,
when I say TEXT(1024), is the field guaranteed to take 1024 bytes on
disk per record or is there a non-trivial storage scheme at work
similar to VARCHAR types, which tries to optimize space usage.

thanks!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: TEXT storage

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/17/2010 11:57 AM, slowpoison wrote:
> I was unable to find a reference to how TEXT data types are stored in
> the db files.

2.3.1 and 2.3.2 answer that:

  http://www.sqlite.org/fileformat.html#varint_format

> I want to know whether a TEXT field, when stored, will always take the
> exact amount of space allocated for it in the schema definition.

What makes you think the schema "allocates" space for strings?

  http://www.sqlite.org/datatype3.html

> So,
> when I say TEXT(1024), is the field guaranteed to take 1024 bytes on
> disk per record or is there a non-trivial storage scheme at work
> similar to VARCHAR types, which tries to optimize space usage.

The numbers have no effect.  "CHAR" and "TEXT" merely affect column affinity
as described in the previous link.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvKH6gACgkQmOOfHg372QTWswCggv3U/PY8t43HY48i2Kc+/y7V
tW8Anjf0XTXCmpBhwZGNe2IBFksc71YT
=V4tj
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: TEXT storage

D. Richard Hipp
In reply to this post by slowpoison

On Apr 17, 2010, at 2:57 PM, slowpoison wrote:
>
> I want to know whether a TEXT field, when stored, will always take the
> exact amount of space allocated for it in the schema definition. So,
> when I say TEXT(1024), is the field guaranteed to take 1024 bytes on
> disk per record or is there a non-trivial storage scheme at work
> similar to VARCHAR types, which tries to optimize space usage.


The latter.  SQLite stores all strings as if they were in a  
VARCHAR(1000000000).  (That's VARCHAR(one-billion).)  A 5-byte string  
requires 6 bytes of disk (one byte for the string size and 5 for the  
string itself).  A one-billion byte string requires one-billion-and-
five bytes of disk (5 bytes for the string size and one billion bytes  
to hold the string itself.)

D. Richard Hipp
[hidden email]



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users