Re: [sqlite] Size limits

[Andy]

I try create NNTP server. For text groups blobs will small - 5-80 kB, for
binary  - 0.8 MB

wt., 7 sty 2020 o 13:31 David Raymond  napisał(a):

> Along with the other replies already mentioned I'll pipe in with a
> reminder that any large blob fields should be the final field in a table's
> definition. Due to how the data is stored, to get the data for any
> particular field, SQLite has to go through/decode the data for all previous
> fields in a record.
>
> So if you have a layout of...
>
> id integer primary key,
> big_blob_field blob,
> some_field_you_want text
>
> ... then if you "select some_field_you_want from table;" and a record has
> a 1GB blob in big_blob_field, then it'll have to go through 1GB of linked
> list overflow pages to get the value for some_field_you_want. (Some
> optimizations may apply)
>
> So be sure to define it as
>
> id integer primary key,
> little_field_1 text,
> little_field_2 int,
> little_field_3 float,
> big_blob_at_the_end blob
>
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Andy
> Sent: Tuesday, January 7, 2020 5:30 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Size limits
>
> What are reasonable limits for size Sqlite3 database file and large blobs?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size limits

[David Raymond]

Along with the other replies already mentioned I'll pipe in with a reminder 
that any large blob fields should be the final field in a table's definition. 
Due to how the data is stored, to get the data for any particular field, SQLite 
has to go through/decode the data for all previous fields in a record.

So if you have a layout of...

id integer primary key,
big_blob_field blob,
some_field_you_want text

... then if you "select some_field_you_want from table;" and a record has a 1GB 
blob in big_blob_field, then it'll have to go through 1GB of linked list 
overflow pages to get the value for some_field_you_want. (Some optimizations 
may apply)

So be sure to define it as

id integer primary key,
little_field_1 text,
little_field_2 int,
little_field_3 float,
big_blob_at_the_end blob



-Original Message-
From: sqlite-users  On Behalf Of 
Andy
Sent: Tuesday, January 7, 2020 5:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Size limits

What are reasonable limits for size Sqlite3 database file and large blobs?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size limits

[Andy Bennett]

Hi Andy,


What are reasonable limits for size Sqlite3 database file and large blobs?


Here are some benchmarks on BLOB performance:

https://sqlite.org/intern-v-extern-blob.html


However, see the note here in section 4.1 about using the incremental BLOB 
I/O routines so that you don't thrash your memory allocator:


https://sqlite.org/malloc.html#_computing_and_controlling_parameters_m_and_n


limits.html gives you the actual hard SQLite limits:

https://sqlite.org/limits.html

The default limit for BLOBs is 1GB, but you can raise or lower that value 
at compile-time. The maximum is 2GiB.



The overall maximum database size is 140TB (128TiB).


Here are some more reports on using SQLite for BLOB storage:

https://sqlite.org/fasterthanfs.html

https://sqlite.org/affcase1.html


In reality you will be limited by your ability to find the BLOBs 
efficiently. i.e. what other metadata your store with them and how you 
index them. If your individual data items are larger than 1GB then you'll 
probably need to look at storing the underlying data across several BLOBs.





Best wishes,
@ndy

--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size limits

[Rob Willett]

Andy,

I can state that SQLite easily went up to 100GB with zero issues for us. 
We decided to change the structure of our database and we reduced our 
database size as we didn't want to incur extra VPN costs. We also 
realised we designed the database wrong and it had a lot of useless data 
in...but thats another different story :)


I have heard people talking about 300GB SQLite databases and I have no 
reason to doubt that SQLite would work. As Simon says, environmental 
issues may come into affect before you hit SQLite limits.


I can't comment on large blobs as not sure what large is, 1MB, 100MB, 
1GB?


Rob

On 7 Jan 2020, at 11:21, Simon Slavin wrote:


On 7 Jan 2020, at 10:29am, Andy  wrote:

What are reasonable limits for size Sqlite3 database file and large 
blobs?


Unfortunately, recommendations (rather than hard limits) are closely 
tied to your OS and hardware.  This is because the things you want to 
avoid are things like busting your cache, or memory faults, and the 
sizes of those are set by your own computer and OS.


Experience of this list has shown that we really can't make any firm 
limits without knowledge of your setup, and you'd have to find someone 
with an identical setup to have a useful conversation.  We can only 
urge you to test things out and see for yourself where your 
performance drops off.


In terms of hard limits, you might like to read this:


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

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

Re: [sqlite] Size limits

[Simon Slavin]

On 7 Jan 2020, at 10:29am, Andy  wrote:

> What are reasonable limits for size Sqlite3 database file and large blobs?

Unfortunately, recommendations (rather than hard limits) are closely tied to 
your OS and hardware.  This is because the things you want to avoid are things 
like busting your cache, or memory faults, and the sizes of those are set by 
your own computer and OS.

Experience of this list has shown that we really can't make any firm limits 
without knowledge of your setup, and you'd have to find someone with an 
identical setup to have a useful conversation.  We can only urge you to test 
things out and see for yourself where your performance drops off.

In terms of hard limits, you might like to read this:


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

[sqlite] Size limits

[Andy]

What are reasonable limits for size Sqlite3 database file and large blobs?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Simon Slavin]

On 7 Jun 2018, at 5:25am, Dianne Dunn  wrote:

> Hey there do you know how I can get off this list.??

Click the link that appears at the bottom of every post.

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

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

Have you tried the link at the end of every message?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dianne Dunn
>Sent: Wednesday, 6 June, 2018 22:25
>To: SQLite mailing list
>Subject: Re: [sqlite] Size of the SQLite library
>
>Hey there do you know how I can get off this list.??
>
>Sent from my iPad
>
>> On Jun 5, 2018, at 3:50 AM, Robert M. Münch
> wrote:
>>
>>> On 31 May 2018, at 19:15, Richard Hipp wrote:
>>>
>>> But more recently, mobile phone designers are telling me things
>like
>>> "try to keep the size under 5 megabytes, if you can, please."
>>>
>>> Based on those more recent conversations, I'm thinking that we
>have
>>> more headroom that we have had historically, and so I have
>recently
>>> been allowing new features to start creeping into the core.
>>
>> Size matters IMO, it’s a sign of good design and less is more WRT
>errors etc.
>>
>>
>>> Size is still important.  But having useful features is important
>too.
>>
>> True, and we all know that most features are not used. Do you have
>an idea what features are used by ratio? Maybe adding a „report back
>feature collector“ might be an idea, for those wanting to support the
>feature selection process.
>>
>>
>>> I'm continuing to work to find the right balance between these
>>> competing goals.
>>
>> Keeping things configurable as it is, is a very good approach,
>please keep it.
>>
>> --
>>
>> Robert M. Münch, CEO
>> M: +41 79 65 11 49 6
>>
>> Saphirion AG
>> smarter | better | faster
>>
>> http://www.saphirion.com
>> http://www.nlpp.ch
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Size of the SQLite library

[Dianne Dunn]

Hey there do you know how I can get off this list.??

Sent from my iPad

> On Jun 5, 2018, at 3:50 AM, Robert M. Münch  
> wrote:
> 
>> On 31 May 2018, at 19:15, Richard Hipp wrote:
>> 
>> But more recently, mobile phone designers are telling me things like
>> "try to keep the size under 5 megabytes, if you can, please."
>> 
>> Based on those more recent conversations, I'm thinking that we have
>> more headroom that we have had historically, and so I have recently
>> been allowing new features to start creeping into the core.
> 
> Size matters IMO, it’s a sign of good design and less is more WRT errors etc.
> 
> 
>> Size is still important.  But having useful features is important too.
> 
> True, and we all know that most features are not used. Do you have an idea 
> what features are used by ratio? Maybe adding a „report back feature 
> collector“ might be an idea, for those wanting to support the feature 
> selection process.
> 
> 
>> I'm continuing to work to find the right balance between these
>> competing goals.
> 
> Keeping things configurable as it is, is a very good approach, please keep it.
> 
> -- 
> 
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
> 
> Saphirion AG
> smarter | better | faster
> 
> http://www.saphirion.com
> http://www.nlpp.ch
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 06/06/18 09:24, Bob Friesenhahn wrote:
> A local tool which makes it easy to configure sqlite from local files
> sounds useful ...

It already exists.  It is what the SQLite team uses to produce the
amalgamations etc, and is part of the SQLite code base.

> but depending on a "web site" (baby-bird model) ...

Note that behind the scenes the existing tools would be used with the
relevant results zipped up and downloadable.  No one is advocating
getting rid of the command line tools, just a web front end.

> There is already far too much dependence on what what
> happens to get served up at the time and too much dependence on a live
> connection to the "Internet" ...

You and Warren comprehensively describe best practises and why.  You are
both right.  It is what developers *should* do for repeatable reliable
builds.

But it is a lot of friction.  And not every developer follows best
practise.  And developers start out investigating and playing around
with potential solutions, and then adopt the appropriate ones.

If you are trying out a "hello world" quick test, then the best
practises are a lot of friction, and a few web page tickboxes are the least.

The more friction there is, the fewer people will try non-default
configurations.  But that also locks SQLite into a pessimistic legacy
configuration going forward.  For example default enabling STAT4 or
disabling deprecated API could not be done, ever.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Simon Slavin]

I recommend a mixture of the following two solutions:

On 6 Jun 2018, at 5:05pm, Roger Binns  wrote:

> That is why I advocate a web site where the user (un)ticks what they
> want, and the web site provides a correctly configured download.

6 Jun 2018, at 5:24pm, Bob Friesenhahn  wrote:

> A local tool which makes it easy to configure sqlite from local files sounds 
> useful but depending on a "web site" (baby-bird model) does not sound good to 
> me.  There is already far too much dependence on what what happens to get 
> served up at the time and too much dependence on a live connection to the 
> "Internet" with a naive expectation what what was produced yesterday will 
> continue to be produced tomorrow.

To mix them, you put one configuration on the web.  The current one (call it 
"most useful for most people") is fine.  This configuration can be changed by 
editing one file.  For a C project this would be a "sqlite3config.h" file with 
lots of "#define" lines.  This file has nothing in except for configuration 
settings and some (but not long and exhaustive) comments on what they do.  No 
macros or function definitions.

Experts can read the documentation in the file and set the definitions 
themselves.

But you also put up an online configuration web page, which works using 
JavaScript.  The web page has GUI features up top: popup menus, checkboxes, 
radio-buttons, whatever.  At the bottom of the page is a text field containing 
the entire contents for an ".h" file which configures the compilation according 
to those settings.  You can change the GUI settings and see how that changes 
the file in real time.  It's up to the user to copy that text and past it into 
a new "sqlite3config.h" file or whatever it is.

Here's the good part: because the web page works using just JavaScript (rather 
than PHP, ASP, node, whatever) you can include a copy of it with the 
distribution and any user can run it in their favourite browser without needing 
internet access, or perhaps on a smartphone.

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

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

On Wednesday, 6 June, 2018 10:24, Bob Friesenhahn wrote:

> The build strategy for the Python APSW extension is an 
> example of unwanted dependency and loss of control.

> Building of software from source code should always be 
> under the complete control of the person who is performing 
> the build and should inherently support use of local files 
> which may contain local changes.

I build APSW this way and it uses a completely customized
version of the sqlite3.c amalgamation that I also build into
its own sqlite3 executables and DLLs, it is really not that 
difficult.  Mind you, I extract the latest APSW sources 
from the ZIP archive and have built my own build-scripts
to do this.  Because I use the mingw-w64 compiler toolchain
I also have to slightly modify the default Python library
cygwincompiler.py and have made my own customized APSW
setup.py with a different name (based on the distributed 
source setup.py) that automates the whole process.

Fossil is used to manage the local repositories that are 
created from the distribution source (I have another 
Linux machine that builds the amalgamation source from
the full sources and I copy that to use as my base 
amalgamation for generating the executables, DLLs, and 
APSW).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Size of the SQLite library

[Bob Friesenhahn]

On Wed, 6 Jun 2018, Roger Binns wrote:


That is why I advocate a web site where the user (un)ticks what they
want, and the web site provides a correctly configured download.  This
will also tell the SQLite developers what features are configured.  (eg
if everyone turns off virtual tables that is useful feedback, as would
the opposite.)


A local tool which makes it easy to configure sqlite from local files 
sounds useful but depending on a "web site" (baby-bird model) does not 
sound good to me.  There is already far too much dependence on what 
what happens to get served up at the time and too much dependence on a 
live connection to the "Internet" with a naive expectation what what 
was produced yesterday will continue to be produced tomorrow.  The 
build strategy for the Python APSW extension is an example of unwanted 
dependency and loss of control.


Building of software from source code should always be under the 
complete control of the person who is performing the build and should 
inherently support use of local files which may contain local changes.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 05/06/18 15:07, Warren Young wrote:
> All right, so include [multi-component source control and build process] ...

I'm still not sure what point you are trying to make.  Yes *you* can do
that.  Should *every* SQLite user who wants non-default options *have*
to go through a similar amount of friction?

SQLite currently only has one distribution.  This distribution has to
fit most user needs regarding backwards and forwards compatibility
(including query plans), functionality, size etc.

*If* SQLite wants to step away from one size/configuration fits most,
then there needs to be way less friction in getting the alternate
configurations.  One solution is a small number of alternate downloads
("presets"), although it is hard to know what configurations they should
have.

That is why I advocate a web site where the user (un)ticks what they
want, and the web site provides a correctly configured download.  This
will also tell the SQLite developers what features are configured.  (eg
if everyone turns off virtual tables that is useful feedback, as would
the opposite.)

> Thus the need for curated collections of build options, since a jQuery UI 
> like tool that assumes the options are all orthogonal would frequently 
> produce unbuildable output.

Huh?  No one is advocating a SQLite web tool that produces unbuildable
output, or offers every possible combination of options.  It would need
to be useful, and can start simple.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Warren Young]

On Jun 5, 2018, at 2:24 PM, Roger Binns  wrote:
> 
> For example to exclude virtual tables from SQLite, you can't just add a
> compile time option and be done.  You have to regenerate from  the
> grammar (so it is no longer valid SQL syntax and no longer has calls to
> virtual table relevant functions).  And then you almost certainly want
> to use the tool to make the amalgamation from the updated grammar.  And
> then you need to make sure your Makefile or equivalent passes in the
> omit flag too.

All right, so include a clone of the SQLite Fossil repository in your 
application tree instead of the amalgamation and write a script to do all of 
this as part of the build process.  That is, make the existence of sqlite3.c 
dependent on a successful run of this script.

You can find many open source projects built this way, most often seen when the 
top-level configure script calls a dependency’s configure script, then the 
top-level Makefile calls the dependency’s Makefile at a time determined by the 
top-level’s declared deps.

>> Contrast a language like JavaScript, where you can ship a program that has 
>> calls to functions that don’t exist, and as long as you continue to not call 
>> those functions, the JS VM won’t balk.
> 
> You can do lazy runtime linking in some operating systems

…leaving out platforms where it doesn’t work.  I believe macOS and iOS are that 
way, for instance.

Of the platforms I’ve used, the only one I’m sure allows it is Linux, and I 
believe I’m remembering that because of all the times it’s masked problems that 
showed up when I went to port some bit of software to another platform.

> But in any event JS code is not distributed how you think.

Ah, so?

What I actually think is that one of my primary development products is based 
on jQuery UI, which in turn leads me to think I know how JS code is distributed 
in general, and how jQuery UI is distributed specifically. :)

One of the options you have when using jQuery UI’s custom distributions is to 
include the necessary modules in your project’s tree unminified and unmerged so 
that you can do your own local merging and minification.  Which I do.

If you then mistakenly leave out a needed module, you get mysterious run-time 
failures.  (Ask me how I know.)

“Do I need focusable.js?  Hmmm, I guess I’d better just try all of the UI 
functions and see if I get complaints in the JS error console, because the 
nonexistent static linker sure isn’t going to tell me!”

I’ve even seen such run-time failures happen with calls that are *internal* to 
the merged and minified artifact because some bit of dynamic JS was being 
sufficiently clever that the tools couldn’t see that there was an unresolved 
call.

>> 2. There are ways around this with C,
> 
> My point is that it isn't.  You cannot add / remove defines to the
> amalgamation to omit most features.  Heck if you try it just won't
> compile.  More work has to be done.  The mailing list archives have many
> messages where people tried a few compile flags and it didn't work.

Yes, I’ve run into such situations several times myself.  Every time it’s 
happened to me, I just drop the now-problematic OMIT option(s) and move on.

The most recent such regression (?) for me was finding that shell.c wouldn’t 
build to a usable SQLite CLI binary with -DSQLITE_OMIT_AUTOINIT because it is 
now depending on autoinit.

Thus the need for curated collections of build options, since a jQuery UI like 
tool that assumes the options are all orthogonal would frequently produce 
unbuildable output.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 01/06/18 13:46, Warren Young wrote:
> Your jQuery example later on doesn’t much apply here, for several reasons:

Note that I was showing how the site let you choose whatever features
you want, and then gave you a download matching exactly that.

> 1. JavaScript is a dynamic language, while C is a statically-compiled 
> language.  

Your comments while correct don't actually apply to SQLite.  SQLite is
not a C file.  It is many C source files, many headers, a grammar (not
in C), and various tools (typically TCL).

For example to exclude virtual tables from SQLite, you can't just add a
compile time option and be done.  You have to regenerate from  the
grammar (so it is no longer valid SQL syntax and no longer has calls to
virtual table relevant functions).  And then you almost certainly want
to use the tool to make the amalgamation from the updated grammar.  And
then you need to make sure your Makefile or equivalent passes in the
omit flag too.

The web site doing all that work for you, and getting it right every
time does have value IMHO.  It also makes it easier for SQLite to have
bigger or smaller presets to address the varying developer needs.  And
the team will have some idea of what OMITs are used, where testing
should check etc.

> That means that all of the symbols needed to link the program ...

That was nothing to do with the issue.  To be very clear:

* SQLite has a way of omiting functionality

* Other than a few special cases, you must use the SQLite source (not
the amalgamation) to regenerate what you finally use

* Doing this is difficult and error prone

> Contrast a language like JavaScript, where you can ship a program that has 
> calls to functions that don’t exist, and as long as you continue to not call 
> those functions, the JS VM won’t balk.

You can do lazy runtime linking in some operating systems so functions
to calls that don't exist are ok (until you call them).

But in any event JS code is not distributed how you think.  Minified
source is usually used, and works best if run through dead code
elimination first (called "tree shaking" in the JS world).  ie the
distribution isn't that different to SQLite (amalgamation).

> 2. There are ways around this with C,

My point is that it isn't.  You cannot add / remove defines to the
amalgamation to omit most features.  Heck if you try it just won't
compile.  More work has to be done.  The mailing list archives have many
messages where people tried a few compile flags and it didn't work.

> One could write a variant of cpp that would run on the sqlite3.c amalgamation 
> ...

It won't work for anything grammar related.  And the project has tools
like you describe (eg it is how the amalgamation is produced).  Those
tools are in TCL and know about the structure and coding patterns of
SQLite.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Robert M. Münch]

On 31 May 2018, at 19:15, Richard Hipp wrote:

> But more recently, mobile phone designers are telling me things like
> "try to keep the size under 5 megabytes, if you can, please."
>
> Based on those more recent conversations, I'm thinking that we have
> more headroom that we have had historically, and so I have recently
> been allowing new features to start creeping into the core.

Size matters IMO, it’s a sign of good design and less is more WRT errors etc.


> Size is still important.  But having useful features is important too.

True, and we all know that most features are not used. Do you have an idea what 
features are used by ratio? Maybe adding a „report back feature collector“ 
might be an idea, for those wanting to support the feature selection process.


> I'm continuing to work to find the right balance between these
> competing goals.

Keeping things configurable as it is, is a very good approach, please keep it.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Chris Smith]

"You are soo, bloated," said Java.

On Thu, May 31, 2018, 11:58 R Smith  wrote:

>
> On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote:
> > I have to agree with Bob!
> >
> > We have considered SQLITE for our project.  Going over 500Kbytes puts it
> > just beyond the size of our Flash - the current Firmware.
>
> I stand corrected! It seems the embedded systems with still an extremely
> limited memory footprint size may not be as thin on the ground as I
> imagined, and I regret trying to categorize all embedded systems under
> the same ideal - apologies for that.
>
> Towards my point though, both Bob and Vance, would you be especially
> swayed if the marketing slogan had said "under a megabyte" as opposed to
> "under half a megabyte"?  I still feel that this level of embedded
> system is not common, and even where it might be common, I bet that
> slogan is not the catch phrase that got you interested in SQLite (or
> would sway you from choosing it).
>
> It's however clear my view may not be 100% representative, so perhaps
> the  KiB or 0.5 MiB route has its place.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Cheers,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Warren Young]

On May 31, 2018, at 6:32 PM, Roger Binns  wrote:
> 
> On 31/05/18 10:15, Richard Hipp wrote:
>> Size is still important.  But having useful features is important too.
>> I'm continuing to work to find the right balance between these
>> competing goals.
> 
> A pattern used in other projects is to have standard downloads, as well
> as custom ones.

Your jQuery example later on doesn’t much apply here, for several reasons:

1. JavaScript is a dynamic language, while C is a statically-compiled language. 
 That means that all of the symbols needed to link the program need to be 
available at link time in order to produce a binary.  To achieve that with C, 
you’d have to do things like create mock modules that export an API but don’t 
implement it, merely to placate the linker.

Contrast a language like JavaScript, where you can ship a program that has 
calls to functions that don’t exist, and as long as you continue to not call 
those functions, the JS VM won’t balk.

2. There are ways around this with C, such as with the plugin pattern — which 
is in fact already being used in SQLite’s VFS layer — but it carries an 
indirection overhead.

jQuery is a bad exemplar here because it’s a solution for implementing 
user-facing actions, which means that as long as each group of actions 
implemented using it take under 100 ms or so, it’s fast enough.  For SQLite, 
though, adding layers of abstraction merely for the programmer’s convenience 
means slowing it down materially, which can turn a viable solution into failure.

Some sage once opined that any problem in computer science can be solved by 
adding another layer of abstraction, but there is one that can’t: “The software 
is too slow, and we’re unwilling to buy more hardware.”

3. SQLite already has a way to generate multiple versions of the source code in 
a programmatic way: #ifdefs.  That’s precisely what the C preprocessor does.  
JavaScript has no equivalent mechanism, so someone had to go an factor jQuery 
into modules by hand and rely on the user to provide the correct subset of 
modules needed by their software.

One could write a variant of cpp that would run on the sqlite3.c amalgamation 
to produce predigested subsets without bringing in all of the #includes, but 
all that’s really needed here are curated sets of -DSQLITE_* flags, since then 
the end user can use them with the C preprocessor they already have.



I’ve a feeling that I’m missing more reasons, but those will suffice.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[dmp]

1. Define in documentation as < 1Mb. (Don't have to visit again.)

2. Continue to strive to keep in the 0.5-1MB range.

3. Add some information on building a MINIMUM size for those
   concerned that is relatively easy to accomplish without
   a lot of expertise if possible.

danap.

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

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 31/05/18 10:15, Richard Hipp wrote:
> Size is still important.  But having useful features is important too.
> I'm continuing to work to find the right balance between these
> competing goals.

A pattern used in other projects is to have standard downloads, as well
as custom ones.  With the latter you can include or exclude additional
components and features.  You can already do this with SQLite, but it
requires several more command line tools, programming languages, and
comprehensive reading of the documentation.

Perhaps a custom download web page that gives you some presets
(smallest, default, everything) or lets you choose your own settings.
It would then produce known good source files, and users would be happy.

Here is an example page for a Javascript project:

  https://jqueryui.com/download/

On the balance side, STAT4 is a good example.  I think it would benefit
the majority of SQLite users if it was enabled by default.  But making
only that change could change query plans for existing users.  (Many
users also don't compile SQLite itself - they get binaries from the
platform, or language bindings.)

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

>On Thursday, 31 May, 2018 10:19, Dominique Devienne said:

>Given where the conversation is going, let me point out that many do
>not care one bit about the lib's size :)

>I'd much rather have an SQLite with tons of features, than forego
>those in the name saving a few bytes, to save a few bucks on the 
>embedded chip and flash for commercial products that don't even 
>pay for SQLite.

>SQLite is already amazingly small for the value it brings. And if
>people want "smaller", they can still stick
>with older leaner versions of SQLite too. My $0.02... --DD

The custom version of the library that I build which contains *all* features 
and extensions built-in, and then some, compiled with MinGW-w64 GCC 7.1.0 on 
Windows using -m64 -O3, and statically linked with all the GCC libraries 
(__float128, runtime, threading, etc., so no dependancies other than to the 
subsystem runtime (MSVCRT) and the Windows DLLs actually used) comes in around 
2 MB ... APSW a wee bit bigger ...

2018-05-30  16:40 2,173,456 sqlite3.dll
2018-05-30  16:43 2,326,544 apsw.cp36-win_amd64.pyd

Of course, this includes almost all the extensions, all the math library, many 
Windows APIs, and a bunch of aggregates/functions/collations to handle IP 
Addresses (v4 and v6), some running statistics, proper rounding (Half-Even), 
all the hash functions 
(MD4/MD5/SHA/SHA1/SHA2(256/384/512)/SHA3(224/256/384/512)) and a few other odds 
and sods.

If it were for a computer with "limited resources" I would par it down a lot, 
but having everything automatically available is very nice ... and I am not 
CPU/Memory/IO constained (though having all NVMe drives does make me have to 
"fix" things from time to time to stay within the constraints of spinning rust 
(3 GB/s I/O can become very addictive).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Size of the SQLite library

[Thomas Kurz]

I totally agree with that. On most systems it is much more important to have a 
feature-rich library than a very small one. Any application where a few bytes 
more or less matter should be written in pure assembler anyway.


- Original Message - 
From: Dominique Devienne 
To: General Discussion of SQLite Database 
Sent: Thursday, May 31, 2018, 18:18:51
Subject: [sqlite] Size of the SQLite library

On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:

> For many years, we have boasted that the size of the SQLite library is
> "less than half a megabyte".


Given where the conversation is going, let me point out that many do not
care one bit about the lib's size :)

I'd much rather have an SQLite with tons of features, than forego those in
the name saving a few bytes,
to save a few bucks on the embedded chip and flash for commercial products
that don't even pay for SQLite.

SQLite is already amazingly small for the value it brings. And if people
want "smaller", they can still stick
with older leaner versions of SQLite too. My $0.02... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Size of the SQLite library

[Richard Hipp]

On 5/31/18, Simon Slavin  wrote:
>
> Did you know that less than half of SQLite installations are on desktop
> computers ?  My guess is that mobile phones are now the biggest category of
> devices.  They run off battery power.  They have firmware on chips.  The
> more chips they have to keep powered-up, the more battery power they use,
> the physically bigger the phone has to be to hold not just the chips but the
> bigger battery, the heavier and more expensive it is.
>

Back in the day, Motorola and Symbian used to really pressure us to
keep the library footprint down.  Every byte mattered.

But more recently, mobile phone designers are telling me things like
"try to keep the size under 5 megabytes, if you can, please."

Based on those more recent conversations, I'm thinking that we have
more headroom that we have had historically, and so I have recently
been allowing new features to start creeping into the core.

Size is still important.  But having useful features is important too.
I'm continuing to work to find the right balance between these
competing goals.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Simon Slavin]

On 31 May 2018, at 5:18pm, Dominique Devienne  wrote:

> On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:
> 
>> For many years, we have boasted that the size of the SQLite library is
>> "less than half a megabyte".
> 
> Given where the conversation is going, let me point out that many do not
> care one bit about the lib's size :)

Did you know that less than half of SQLite installations are on desktop 
computers ?  My guess is that mobile phones are now the biggest category of 
devices.  They run off battery power.  They have firmware on chips.  The more 
chips they have to keep powered-up, the more battery power they use, the 
physically bigger the phone has to be to hold not just the chips but the bigger 
battery, the heavier and more expensive it is.

Not to mention a whole fleet of handheld safety-testing equipment with 
different configurations for different installations, and those gadgets parking 
inspectors carry around to keep track of which cars parked when.

Might be interesting to find out what proportion of SQLite devices are 
mains-powered vs. battery-powered.  Although whether one should class an Airbus 
A350 XWB as "battery-powered" I am not certain.

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

Re: [sqlite] Size of the SQLite library

[J. King]

On May 31, 2018 12:18:51 PM EDT, Dominique Devienne  wrote:
>On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:
>
>> For many years, we have boasted that the size of the SQLite library
>is
>> "less than half a megabyte".
>>
>
>Given where the conversation is going, let me point out that many do
>not
>care one bit about the lib's size :)
>
>I'd much rather have an SQLite with tons of features, than forego those
>in
>the name saving a few bytes,
>to save a few bucks on the embedded chip and flash for commercial
>products
>that don't even pay for SQLite.
>
>SQLite is already amazingly small for the value it brings. And if
>people
>want "smaller", they can still stick
>with older leaner versions of SQLite too. My $0.02... --DD
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I agree with this sentiment. I mostly use SQLite in PHP, where it is awkward to 
customize SQLite, and all but impossible to rely on features not included in 
the standard build when distributing to others. A more powerful default 
configuration would be very beneficial, and a less powerful one possibly 
crippling. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Dominique Devienne]

On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:

> For many years, we have boasted that the size of the SQLite library is
> "less than half a megabyte".
>

Given where the conversation is going, let me point out that many do not
care one bit about the lib's size :)

I'd much rather have an SQLite with tons of features, than forego those in
the name saving a few bytes,
to save a few bucks on the embedded chip and flash for commercial products
that don't even pay for SQLite.

SQLite is already amazingly small for the value it brings. And if people
want "smaller", they can still stick
with older leaner versions of SQLite too. My $0.02... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Chris Brody]

On Thu, May 31, 2018 at 11:38 AM, Richard Hipp  wrote:
> [...]
> By using multiple SQLITE_OMIT compile-time options to leave out
> features, I can get the size down to 308,189 bytes using gcc-7 -Os
> -m32.

@Richard can you elaborate some more on how you make this kind of a build?

I wouldn't mind if we drop some more less-used options from the
default build to keep the standard size "less than half a megabyte".
Also -1 for kibibyte/mebibyte wording on my part.

On Thu, May 31, 2018 at 11:57 AM, Christian Schmitz
 wrote:
> [...]
> Maybe your graph should have three lines.

+1 would be nice, not major though (I think)

On Thu, May 31, 2018 at 11:58 AM, R Smith  wrote:
> [...]
> Towards my point though, both Bob and Vance, would you be especially swayed
> if the marketing slogan had said "under a megabyte" as opposed to "under
> half a megabyte"?

I think Vance already gave the answer (negative). Would it be an idea
to have size slogans for both regular and embedded builds?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[R Smith]

On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote:

I have to agree with Bob!

We have considered SQLITE for our project.  Going over 500Kbytes puts it
just beyond the size of our Flash - the current Firmware.


I stand corrected! It seems the embedded systems with still an extremely 
limited memory footprint size may not be as thin on the ground as I 
imagined, and I regret trying to categorize all embedded systems under 
the same ideal - apologies for that.


Towards my point though, both Bob and Vance, would you be especially 
swayed if the marketing slogan had said "under a megabyte" as opposed to 
"under half a megabyte"?  I still feel that this level of embedded 
system is not common, and even where it might be common, I bet that 
slogan is not the catch phrase that got you interested in SQLite (or 
would sway you from choosing it).


It's however clear my view may not be 100% representative, so perhaps 
the  KiB or 0.5 MiB route has its place.



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

Re: [sqlite] Size of the SQLite library

[Christian Schmitz]

> 
> See https://sqlite.org/tmp/size-20180531.jpg for the library size
> trend over 5 years. 

Maybe your graph should have three lines.

1. Minimum SQLite with all off
2. Default SQLite
3. Maximum SQLite with all on

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/


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

Re: [sqlite] Size of the SQLite library

[Richard Hipp]

On 5/31/18, ven...@intouchmi.com  wrote:
>
> We have considered SQLITE for our project.  Going over 500Kbytes puts it
> just beyond the size of our Flash - the current Firmware.

By using multiple SQLITE_OMIT compile-time options to leave out
features, I can get the size down to 308,189 bytes using gcc-7 -Os
-m32.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[veneff]

I have to agree with Bob! 

We have considered SQLITE for our project.  Going over 500Kbytes puts it
just beyond the size of our Flash - the current Firmware.

Vance 

On 2018-05-31 11:04, Bob Friesenhahn wrote:

> On Thu, 31 May 2018, R Smith wrote: 
> 
>> Nice idea, but to be honest, I can't remember when last someone cared about 
>> "Kilobytes", and I mean embedded people, not big OSes.
> 
> I work on embedded projects and we do definitely worry about "kilobytes".  
> This is even though our embedded projects have large resources compared with 
> many other embedded projects.  The firmware image for some of our products is 
> consuming all available Flash pages, (except for spares for 
> wear-leveling/repair).
> 
> Many embedded projects are very cost-sensitive since they sell into 
> hyper-competitive markets where being a bit more expensive than the 
> competition results in a lack of sales.
> 
>> The measure of importance is how expensive the DATA storing is, both in size 
>> and write-frequency, when committed to some hardware NANDs. The code store 
>> section of even the smallest modern embedded system will be designed to fit 
>> things many megabytes more than SQLite requires (exceptions may exist, but 
>> are really thin on the ground). So then, whether the operating code is given 
>> in KB or MiB or KiB is, to my mind, not very relevant - and it too will 
>> become untrue in a non-too-distant future.
> 
> Your experience is different than mine.  What NOR or NAND Flash chip are you 
> using on your PCB?  If you are not using a single soldered chip with a 
> specialized flash filesystem (e.g. JFFS2, UBIFS, squashfs on UBI or bare) 
> then perhaps you are just using a small form factor PC which uses components 
> common in laptop PCs.
> 
> Bob
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Bob Friesenhahn]

On Thu, 31 May 2018, R Smith wrote:


Nice idea, but to be honest, I can't remember when last someone cared about 
"Kilobytes", and I mean embedded people, not big OSes.


I work on embedded projects and we do definitely worry about 
"kilobytes".  This is even though our embedded projects have large 
resources compared with many other embedded projects.  The firmware 
image for some of our products is consuming all available Flash pages, 
(except for spares for wear-leveling/repair).


Many embedded projects are very cost-sensitive since they sell into 
hyper-competitive markets where being a bit more expensive than the 
competition results in a lack of sales.


The measure of importance is how expensive the DATA storing is, both in size 
and write-frequency, when committed to some hardware NANDs. The code store 
section of even the smallest modern embedded system will be designed to fit 
things many megabytes more than SQLite requires (exceptions may exist, but 
are really thin on the ground). So then, whether the operating code is given 
in KB or MiB or KiB is, to my mind, not very relevant - and it too will 
become untrue in a non-too-distant future.


Your experience is different than mine.  What NOR or NAND Flash chip 
are you using on your PCB?  If you are not using a single soldered 
chip with a specialized flash filesystem (e.g. JFFS2, UBIFS, squashfs 
on UBI or bare) then perhaps you are just using a small form factor 
PC which uses components common in laptop PCs.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[R Smith]

On 2018/05/31 3:44 PM, Richard Hipp wrote:

For many years, we have boasted that the size of the SQLite library is
"less than half a megabyte".  That will likely still be true in the
3.24.0 release, though just barely.  Compiling with gcc 5.4.0 and -Os
on ubuntu, I get 499,709 bytes.  With gcc 7.1.0 and -Os I get 496,399
bytes.

The library is, of course, larger if you enable optional features such
as full-text search and/or use compiler optimizations like -O3 which
enable loop unrolling and function inlining.  And most people do
compile it that way.  So "less than a megabyte" might be a more
accurate description of SQLite in practice.  But the default
configuration compiled with -Os is a good metric for comparison.

See https://sqlite.org/tmp/size-20180531.jpg for the library size
trend over 5 years.  The measurements in the graph were done with gcc
5.4.0 and -Os on ubuntu.  As you can see, we have held the line below
500,000 bytes for a long time.  But the recent addition of new
features (ex: UPSERT) has caused a slight uptick in the library size.
As further new features are in the pipeline, the upcoming 3.24.0
release will probably be the last for which the library size comes in
at less than 500,000 bytes.  For this reason, I will probably change
the size bullet point to say "less than 500 kibibytes (KiB)" or "less
than 0.5 mebibytes (MiB)", as "less than 600KB" does not have quite
the same emotional impact.  You will notice that the graph linked
above is calibrated in mebibytes.


Nice idea, but to be honest, I can't remember when last someone cared 
about "Kilobytes", and I mean embedded people, not big OSes.


The measure of importance is how expensive the DATA storing is, both in 
size and write-frequency, when committed to some hardware NANDs. The 
code store section of even the smallest modern embedded system will be 
designed to fit things many megabytes more than SQLite requires 
(exceptions may exist, but are really thin on the ground). So then, 
whether the operating code is given in KB or MiB or KiB is, to my mind, 
not very relevant - and it too will become untrue in a non-too-distant 
future.


May I propose, if changing is on the table, to rather update to a more 
current universal notion (and modern embedded capacities) and make it 
leaner by removing one word and simply call it:


"less than a megabyte"

This has much the same emotional impact, still is downright amazing, 
doesn't require naming shenanigans, is very TRUE, even with a few funny 
switches compiled-in, AND will remain true for many years to come, 
possibly to the end of the SQLite lifecycle ~30 years hence.



My 2c...
Ryan


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

[sqlite] Size of the SQLite library

[Richard Hipp]

For many years, we have boasted that the size of the SQLite library is
"less than half a megabyte".  That will likely still be true in the
3.24.0 release, though just barely.  Compiling with gcc 5.4.0 and -Os
on ubuntu, I get 499,709 bytes.  With gcc 7.1.0 and -Os I get 496,399
bytes.

The library is, of course, larger if you enable optional features such
as full-text search and/or use compiler optimizations like -O3 which
enable loop unrolling and function inlining.  And most people do
compile it that way.  So "less than a megabyte" might be a more
accurate description of SQLite in practice.  But the default
configuration compiled with -Os is a good metric for comparison.

See https://sqlite.org/tmp/size-20180531.jpg for the library size
trend over 5 years.  The measurements in the graph were done with gcc
5.4.0 and -Os on ubuntu.  As you can see, we have held the line below
500,000 bytes for a long time.  But the recent addition of new
features (ex: UPSERT) has caused a slight uptick in the library size.
As further new features are in the pipeline, the upcoming 3.24.0
release will probably be the last for which the library size comes in
at less than 500,000 bytes.  For this reason, I will probably change
the size bullet point to say "less than 500 kibibytes (KiB)" or "less
than 0.5 mebibytes (MiB)", as "less than 600KB" does not have quite
the same emotional impact.  You will notice that the graph linked
above is calibrated in mebibytes.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of IO operations

[Richard Hipp]

On Fri, Aug 9, 2013 at 1:55 PM, Andrew Beal  wrote:

> Hi All,
>
> Is there a way within SQLite to configure how many bytes each interaction
> with the IO layer occur as? According to the
> http://www.sqlite.org/atomiccommit.html doc, SQLITE does sector writes
> and that is configured by the xSectorSize function within the IO Methods
> object. From the behavior I have observed, SQLite is not using that value
> for the RW interactions with the VFS layers. I am seeing it use the size of
> the database page instead for IO operations which is nice, however my
> hardware doesn't like that size of access.
>
> Am I misunderstanding the behavior? Or is sqlite supposed to use the page
> size and the documentation is incorrect?
>

SQLite journals entire "sectors" (whatever the sector size is reported by
from the VFS).  But it writes a page at a time.  If your hardware wants to
be written as sectors and if the page size is less than the sector size,
then you'll need to do appropriate buffering inside your VFS.  (Or, maybe
just make sure all your database files have page sizes that are at least as
big as your sectors.)

Note also that only the database file is written in page-size chunks on
page-size boundaries.  The rollback journal is written sequentially and
with non-page-size chunks and on non-page boundaries.  Again, if your
hardware/OS cannot deal with that, you'll need to buffer the writes in your
VFS.


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

[sqlite] Size of IO operations

[Andrew Beal]

Hi All,

Is there a way within SQLite to configure how many bytes each interaction with 
the IO layer occur as? According to the http://www.sqlite.org/atomiccommit.html 
doc, SQLITE does sector writes and that is configured by the xSectorSize 
function within the IO Methods object. From the behavior I have observed, 
SQLite is not using that value for the RW interactions with the VFS layers. I 
am seeing it use the size of the database page instead for IO operations which 
is nice, however my hardware doesn't like that size of access.

Am I misunderstanding the behavior? Or is sqlite supposed to use the page size 
and the documentation is incorrect?

F. Andrew Beal

Woods Hole Oceanographic Institution
266 Woods Hole Road MS#18
Woods Hole, MA 02543

Email: ab...@whoi.edu



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

Re: [sqlite] Size

[Alami Omar]

Thank you for your answer Mr.Simon Davies, but it seems that in a sqlite
format 3 file (that i have), the offset  28 value is not valid(not equal to
the file size) and surely not equal to offset 92 (00 00 00 00), what i am
trying to do, is extract an SQLite Format 3 file from the hex code of
another file, but i can't extract it correctly sadly, i'll put the file in
attachment if anyone desires to take a look (would really apreciate it).
The header is at offset: 0x00102194.

2012/10/4 Simon Davies 

> On 4 October 2012 14:06, Alami Omar  wrote:
> > Hello, does SQLite format files have any size(fie size of the sqlite
> file)
> > info on the header ? Thank you.
>
> http://www.sqlite.org/fileformat.html#filesize
>
> Regards,
> Simon
> ___
> 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] Size

[Clemens Ladisch]

Alami Omar wrote:
> it seems that in a sqlite format 3 file (that i have), the offset
> 28 value is not valid(not equal to the file size)

Please read the link you were given.  This size is in pages.

> what i am trying to do, is extract an SQLite Format 3 file from
> the hex code of another file [...]
> The header is at offset: 0x00102194.

Just extract everything beginning from there and then run VACUUM.


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

Re: [sqlite] Size

[Alami Omar]

Thank you for your answers Mr.Simon Davies and Mr.Simon Slavin, but it
seems that in a sqlite format 3 file (that i have), the offset  28 value is
not valid(not equal to the file size) and surely not equal to offset 92 (00
00 00 00), what i am trying to do, is extract an SQLite Format 3 file from
the hex code of another file, but i can't extract it correctly sadly, here
is the file  if anyone desires
to take a look (would really apreciate it, couldn't attach it because it'll
wait for moderation approval, but i can truly garatntee, the file is safe
no viruses nothing to be worried about, scan
results).
The header is at offset: 0x00102194.

Thank you, Omar.

2012/10/4 Simon Slavin 

>
> On 4 Oct 2012, at 2:06pm, Alami Omar  wrote:
>
> > Hello, does SQLite format files have any size(fie size of the sqlite
> file)
> > info on the header ? Thank you.
>
> You can find file content information here:
>
> http://www.sqlite.org/fileformat.html
>
> There's no specific location with the length in bytes, you have to
> multiply @16 and @28 and add some header space (I think).
>
> Simon.
> ___
> 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] Size

[Simon Slavin]

On 4 Oct 2012, at 2:06pm, Alami Omar  wrote:

> Hello, does SQLite format files have any size(fie size of the sqlite file)
> info on the header ? Thank you.

You can find file content information here:

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

There's no specific location with the length in bytes, you have to multiply @16 
and @28 and add some header space (I think).

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

Re: [sqlite] Size

[Simon Davies]

On 4 October 2012 14:06, Alami Omar  wrote:
> Hello, does SQLite format files have any size(fie size of the sqlite file)
> info on the header ? Thank you.

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

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

Re: [sqlite] Size of WAL file and cache

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/09/12 01:15, Sebastian Krysmanski wrote:
> 1. I understand that the WAL file is stored on the disk.

During a transaction the old pages and the new pages will both be present
at some point no matter what mode is used.  Consequently you should expect
maximum usage to be around the size of your existing data plus twice the
size of the changes you are undertaking while usage outside of a
transaction will be roughly existing data plus changes.

Worst case would be something like changing a value on every single page.
 Best case would be adding new data with no indices.

> It says in the documentation: 1000 pages. How big is a page?

1kb default.

  http://www.sqlite.org/pragma.html#pragma_page_size

> Also, does WAL has any (significant) memory consumption (per
> connection/thead, or dependent on the WAL file size)?

There is some but not really significant.

> 2. I'm guessing a SQLite cache (connection private 
> [SQLITE_OPEN_PRIVATECACHE] or shared [SQLITE_OPEN_SHAREDCACHE]) is
> stored in memory only (i.e. not on the disk), right? What's its size in
> memory? Or, on what factors does this size depend on?

SharedCache is about sharing the cache memory between connections in the
same process.  It is unlikely you'll ever want to do this, or even benefit
from it if you did use it.  The default cache size is 2MB from sqlite.org
- - I haven't checked to see what value Android changed it to.

Many of the system apps, and a lot of others apps are using SQLite behind
the scenes.  You are not the first developer by a long shot.  Remember
that the first Android phones allocated 16MB per process for everything.

I recommend you just go ahead and use SQLite as the Android system intends
you to.  Actually I recommend you expose things as a ContentProvider and
use SQLite behind the scenes.

It is trivial to use SQLite on Android.  Note that the library code does
some things behind your back such as dealing with collation registrations
to make sorting consistent.  (If you ever .dump a database that was
created on Android you can see that.)

Start with this class that helps manage the schema on your database, as
well as creating the database in the right place:


http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBjbWgACgkQmOOfHg372QQUmACfTE4NyVslAFHDkNfiwPvgBP6w
uO0AoJ/YFlZ1a2y0ne3Lk0GCJy781WqG
=48fo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Size of WAL file and cache

[Sebastian Krysmanski]

Hi,

I was wondering what the size (in MB) of a WAL file and of a shared/private
cache is in SQLite. I'm running SQLite on an Android smartphone so space
(RAM/"HDD") is scarce.

1. I understand that the WAL file is stored on the disk. Provided
auto-checkpointing, how big would the WAL file grow? It says in the
documentation: 1000 pages. How big is a page? Also, does WAL has any
(significant) memory consumption (per connection/thead, or dependent on the
WAL file size)?

2. I'm guessing a SQLite cache (connection private
[SQLITE_OPEN_PRIVATECACHE] or shared [SQLITE_OPEN_SHAREDCACHE]) is stored
in memory only (i.e. not on the disk), right? What's its size in memory?
Or, on what factors does this size depend on?

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

Re: [sqlite] Size query

[Stephen Chrzanowski]

But wouldn't you be aware of what the data is you want to transmit anyways?
Sure, it thinks as 1 as a length of 5, but, you'll know that you'll need
to send 4 or 8 bytes.

On Wed, Nov 3, 2010 at 6:50 PM, Scott A Mintz wrote:

> If x is numeric length(x) returns the length of x expressed as a string.
> So the value of length(1 ) is 5, not 2.
>
> -Scott
>
> sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM:
>
> > Couldn't you do something like:
> >
> > select length(FieldName) from TableName where Condition=True
> >
> > ?
> >
> > The result would be the size.  Otherwise, the only thing I can think of
> is
> > just doing a select to get the results you want, then just keep a
> running
> > tally on what would need to be transmitted, then do your packet math.
> >
> > On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz
> wrote:
> >
> > > Is it possible to construct a query that will tell me the total size
> in
> > > bytes of the result set?  Specifically, in a messaging protocol that
> > > returns data we need to fragment the reply and it would be nice to
> know
> > > how much data or how many packets will be required to send the
> response.
> > >
> > > Thanks,
> > > Scott
> > > ___
> > > 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
>
> ___
> 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] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 19:17:48 -0400, Samuel Adam   
wrote:

> SQLite uses its own variable-length integer representation
> internally, occupying between 1 and 64 bits per value; if this is for a

Sorry to reply to my own post; I wish to be precise.  By “internally”, I  
meant (and should have said) “in the database file format”.  Which raises  
another question:  If you want the byte-length of an SQLite integer, do  
you mean as stored in the database, or as processed in-memory, typically  
via sqlite3_int64 and sqlite3_uint64?  Looking at the typedefs, these  
latter *could* compile to something bigger than 8 bytes on exotic  
platforms (although it would be difficult for them to be smaller).

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 18:24:57 -0400, Scott A Mintz  
 wrote:

> Is it possible to construct a query that will tell me the total size in
> bytes of the result set?  Specifically, in a messaging protocol that
> returns data we need to fragment the reply and it would be nice to know
> how much data or how many packets will be required to send the response.

Per my other post, for TEXT values, you can CAST to BLOB and then use  
length().  Assuming the C API, I am guessing that it’s much more efficient  
play with sqlite3_column_bytes(), sqlite3_column_bytes16(),  
sqlite3_value_bytes(), and/or sqlite3_value_bytes16().  These will return  
byte-counts (with no zero terminator) for both TEXT and BLOB values.

As far as I can tell, you will need to count the bytes in numeric values  
yourself.  (I’ve looked into this before, and just glanced into it  
again.)  SQLite uses its own variable-length integer representation  
internally, occupying between 1 and 64 bits per value; if this is for a  
network protocol, you probably want to count the bytes in protocol  
representation rather than SQLite’s representation.

Counting the bytes (or lack thereof) in NULL values should probably also  
be done in your protocol’s representation.

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 18:35:52 -0400, Stephen Chrzanowski  
 wrote:

> Couldn't you do something like:
>
> select length(FieldName) from TableName where Condition=True
>
> ?
>
> The result would be the size.  Otherwise, the only thing I can think of

Caution:  This will return the size in bytes of a BLOB field, but the size  
in *characters* of a TEXT field.  Except for lobotomized 7-bit-only text,  
the two will almost never be the same in a UTF-8 database.  If the  
database is encoded in UTF-16, there are exactly two bytes for every  
plane-0 character and exactly four bytes for every character in plane 1  
and up.

Casting to a BLOB will result in the text being simply reinterpreted as a  
BLOB, which is probably what the original poster wants.

If applied to a numeric (INTEGER or FLOAT) value, length() will first cast  
input to TEXT, then return the size in characters; since the cast will  
only return values <127, the result will be in bytes for UTF-8 and half  
the byte-size for UTF-16.

Casting to BLOB will not fix this, as the value is still first cast to  
TEXT.

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Scott A Mintz]

If x is numeric length(x) returns the length of x expressed as a string. 
So the value of length(1 ) is 5, not 2.

-Scott

sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM:

> Couldn't you do something like:
> 
> select length(FieldName) from TableName where Condition=True
> 
> ?
> 
> The result would be the size.  Otherwise, the only thing I can think of 
is
> just doing a select to get the results you want, then just keep a 
running
> tally on what would need to be transmitted, then do your packet math.
> 
> On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz 
wrote:
> 
> > Is it possible to construct a query that will tell me the total size 
in
> > bytes of the result set?  Specifically, in a messaging protocol that
> > returns data we need to fragment the reply and it would be nice to 
know
> > how much data or how many packets will be required to send the 
response.
> >
> > Thanks,
> > Scott
> > ___
> > 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

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

Re: [sqlite] Size query

[Stephen Chrzanowski]

Couldn't you do something like:

select length(FieldName) from TableName where Condition=True

?

The result would be the size.  Otherwise, the only thing I can think of is
just doing a select to get the results you want, then just keep a running
tally on what would need to be transmitted, then do your packet math.

On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz wrote:

> Is it possible to construct a query that will tell me the total size in
> bytes of the result set?  Specifically, in a messaging protocol that
> returns data we need to fragment the reply and it would be nice to know
> how much data or how many packets will be required to send the response.
>
> Thanks,
> Scott
> ___
> 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

[sqlite] Size query

[Scott A Mintz]

Is it possible to construct a query that will tell me the total size in 
bytes of the result set?  Specifically, in a messaging protocol that 
returns data we need to fragment the reply and it would be nice to know 
how much data or how many packets will be required to send the response.

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

Re: [sqlite] size control of sqlite database

[Martin Engelschalk]

Hi Roger,

yes, thank you, i did not see this.

Martin

Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Martin.Engelschalk wrote:
>   
>> However, i could not find a way to determine when the empty pages are
>> used up and the file will start to grow again without checking the file
>> size after every insert.
>> 
>
> Doesn't PRAGMA freelist_count help with that?  There is also PRAGMA
> page_count and another to determine page size so you can do all these
> calculations without going outside of SQLite.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAktPZtwACgkQmOOfHg372QRHEgCeKwRMG+DpI1Kq/2jeo/Iw/DO0
> DtoAoIYIqt40E9TIWlebbYPta33S6o9r
> =pY6s
> -END PGP SIGNATURE-
> ___
> 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] size control of sqlite database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Martin.Engelschalk wrote:
> However, i could not find a way to determine when the empty pages are
> used up and the file will start to grow again without checking the file
> size after every insert.

Doesn't PRAGMA freelist_count help with that?  There is also PRAGMA
page_count and another to determine page size so you can do all these
calculations without going outside of SQLite.

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

iEYEARECAAYFAktPZtwACgkQmOOfHg372QRHEgCeKwRMG+DpI1Kq/2jeo/Iw/DO0
DtoAoIYIqt40E9TIWlebbYPta33S6o9r
=pY6s
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] size control of sqlite database

[Martin.Engelschalk]

Hi,

a sqlite database is a file, you can get its size using OS calls.
It is not possible to create a database with an initial size, because
the file grows dynamically when you insert data.

To avoid fragmentation, I also looked for a way to allocate empty space
inside the database file when creating it. Dr. Hipp proposed to create a
table with a single Blob-column, insert a very large, empty blob, and
then dropping the table. The file will then keep its size and afterwards
reuse the empty pages.
However, i could not find a way to determine when the empty pages are
used up and the file will start to grow again without checking the file
size after every insert.

Martin

gujx schrieb:
> Hi, I’d like to ask some question about the interface of the sqlite
> resource.
>
> Whether there is some interface to control the size of a database, for
> example, if I want to create a database with 5M initialized, how can I do
> that? And when I make change to a database, for example, insert a row to a
> table, then can I get the size of the database now?
>
>  
>
> Looking forward to your answer.
>
>  
>
>  
>
> Gu Jinxiang
>
>  
>
> 以上、よろしくお��いします。
>
>  
>
>   
> 
>
> ___
> 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

[sqlite] size control of sqlite database

[gujx]

Hi, I’d like to ask some question about the interface of the sqlite
resource.

Whether there is some interface to control the size of a database, for
example, if I want to create a database with 5M initialized, how can I do
that? And when I make change to a database, for example, insert a row to a
table, then can I get the size of the database now?

 

Looking forward to your answer.

 

 

Gu Jinxiang

 

以上、よろしくお��いします。

 

-- 

 

A new email address of FJWAN is launched from Apr.1 2007. 
The updated address is: g...@cn.fujitsu.com 

Development Dept.I
Nanjing Fujitsu Nanda Software Tech. Co., Ltd.(FNST) 8/F., Civil Defense
Building, No.189 Guangzhou Road, Nanjing, 210029, China
TEL：+86+25-86630566-619
COINS：79955-619 
FAX：+86+25-83317685
email：g...@cn.fujitsu.com 
-- 
This communication is for use by the intended recipient(s) only and may
contain information that is privileged, confidential and exempt from
disclosure under applicable law. If you are not an intended 
recipient of this communication, you are hereby notified that any
dissemination, distribution or copying hereof is strictly prohibited. If you
have received this communication in error, please notify me by 
reply e-mail, permanently delete this communication from your system, and
destroy any hard copies you may have printed. 

 

 

 

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

Re: [sqlite] Size of a memory DB?

[Christophe Leske]

> is there a way to get the size of a :memory: db in the sqlite3.exe 
> command line interpreter?
> Or any other way?
>   
Also, would using a small PRAGMA page_size value decrease the amount of 
memory used?

I think the default size is 4096 bytes, how about 512 bytes for an 
in-memory database with only 3 fields? (string, and two ints?)

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


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

[sqlite] Size of a memory DB?

[Christophe Leske]

Hi,

is there a way to get the size of a :memory: db in the sqlite3.exe 
command line interpreter?

Or any other way?

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


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

Re: [sqlite] Size of Meta data ?

[Kees Nuyt]

Hi Yuva,

On Fri, 4 Jan 2008 15:32:44 +0530, "Yuvaraj Athur Raghuvir"
<[EMAIL PROTECTED]> wrote:

>Hello,
>
>Is there any way to estimate the size of the metadata of a SQLite database?
>
>I am trying to do the following:
>1) Set the DB size using MAX_PAGE_COUNT and PAGE_SIZE parameters
>2) I want to do a bulk insert.
>3) However, I want to insert only those many records such that the insert
>succeeds.
>4) I also want to keep a 30% buffer for the data. I would like to start new
>DB instances when that threshold is reached.
>
>To do step 3 successfully, I have to estimate the size of the meta-data.
>Assuming that there is no more data model changes, is there a algorithm or
>heuristic to determine the amount of meta data present in the database?
>
>Thanks and Regards,
>Yuva

The metadata is mostly in the fileheader and the sqlite_master
table. If your schema is more or less static, a run with
sqlite3_analyzer will tell you all you need to know.

Runs of sqlite3_analyzer on a few sample databases could tell
you something about the overhead for your specific data and
filling pattern.

A more reliable approach is to COMMIT your INSERTs frequently
and measure the size of the database file.

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Size of Meta data ?

[Yuvaraj Athur Raghuvir]

Hello,

Is there any way to estimate the size of the metadata of a SQLite database?

I am trying to do the following:
1) Set the DB size using MAX_PAGE_COUNT and PAGE_SIZE parameters
2) I want to do a bulk insert.
3) However, I want to insert only those many records such that the insert
succeeds.
4) I also want to keep a 30% buffer for the data. I would like to start new
DB instances when that threshold is reached.

To do step 3 successfully, I have to estimate the size of the meta-data.
Assuming that there is no more data model changes, is there a algorithm or
heuristic to determine the amount of meta data present in the database?

Thanks and Regards,
Yuva

Re: [sqlite] Size of INDEX in database

[drh]

Dr Gerard Hammond <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there a SELECT call I can issue to the SQLite engine to determine 
> the number of bytes that INDEXs occupy.

No.

But you can download the sqlite3_analyzer utility from
http://www.sqlite.org/download.html and use it to get
detailed measurements and statistics on the sizes of all
of your tables and indices.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Size of INDEX in database

[Dr Gerard Hammond]

Hi,

Is there a SELECT call I can issue to the SQLite engine to determine 
the number of bytes that INDEXs occupy.

--

Cheers,

Dr Gerard Hammond

PowerPC Mac the world's most advanced obsolete computer.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] MAX SQLite Size?/ Corrupt Database

[Martin Jenkins]

viking2 wrote:

I have a large (1.46 GB!) SQLite database (v2) with two tables:

>[...]
1. I dumped the database into a text file, Data_full.sql= 949 MB 
2. I also deleted the first table and dumped the data into another text file

(Data_TB_SR.sql) which is only 532 MB.


First thing I'd do is split those files up into manageable chunks. I 
assume you're on Windows? If so you'll be lacking the tools that would 
make this a simple job on a Linux box. If you're a programmer I suggest 
writing a simple analysis/import script of your own. If not HJSplit(?) 
will split the files into something you can edit and then it's a case of 
finding which one(s) makes the import fall over. :(


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] MAX SQLite Size?/ Corrupt Database

[viking2]

I have a large (1.46 GB!) SQLite database (v2) with two tables:

CREATE TABLE TB_FileRecords (pszFilename TEXT PRIMARY KEY, nFileSize
INTEGER, nFileSignature TEXT);

CREATE TABLE TB_SignatureRecords (nFileSignature TEXT PRIMARY KEY, nFileDate
INTEGER ); 

1. I dumped the database into a text file, Data_full.sql= 949 MB 
2. I also deleted the first table and dumped the data into another text file
(Data_TB_SR.sql) which is only 532 MB.

I have another small database (new.db3; v3; 4 KB) with 1 table:
CREATE TABLE TB_Signature (Filename TEXT, FileSignature TEXT, FileSize
INTEGER, FileDate INTEGER, PRIMARY KEY (FileSignature,FileSize)); 

The goal is to read the dumped data (e.g. Data_TB_SR.sql) into new.db3;
sqlite3 new.db3
.read data_tb_sr.sql

==> After several hours of reading the data I start getting error messages:
SQL error: column nFileSignature is not unique

I tried to import data_tb_sr.sql in "SQLite Database Browser.exe" to
visualize the data, but got an error at line 66404453 and then the program
crashed. 

I tried to look at the data using a text editor (Professional Notepad),
and the data looked ok, nothing different at line 66404453 (data appears
unique).
Total number of lines in data_tb_sr.sql is 6641026 (including the last
"Commit;")

Maybe there is nothing wrong with the database, but I am running into a
limit of SQLite? Maybe the number of lines in a table is limited to
6640453??

Any suggestions of how to fix this so that I can read in the data in new.db3
w/o errors?

Also, how can I check if the data in the updated new.db3 is OK even though I
got errors?
I opened the updated new.db3 with "SQLite Database Browser.exe", but had
problems when trying to view the large table TB_SignatureRecords as the
program crashes..
-- 
View this message in context: 
http://www.nabble.com/MAX-SQLite-Size---Corrupt-Database-tf2579250.html#a7190256
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] size of sqlite

[Gerhard Häring]

Jiao wrote:

I've build sqlite in x86, its size stripped is 318244bytes, Can it
cut even smaller, I noticed some documents said it can be more
smaller, how to cut down?


See http://www.sqlite.org/compile.html

-- Gerhard

[sqlite] size of sqlite

[Jiao]

all,

I've build sqlite in x86, its size stripped is 318244bytes, Can it cut even 
smaller, I noticed some documents said it can be more smaller, how to cut down?

Best Regard & Thank in advance

Jiao

Re: [sqlite] Size of INSERT and UPDATE in TRANSACTION

[R S]

It would be the other way around, no?
The larger the no of inserts within a Transaction, the better the
performance.


On 10/27/05, Hannes Ricklefs <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I was wondering if anyone has any experience with the number of INSERT
> UPDATE
> statements in one TRANSACTION. For example I have the situation that i
> have to
> do around 20.000 INSERTS in one TRANSACTION, so I am wondering if it has
> any
> performance improvements if I split these up into smaller TRANSACTIONS of
> 1000
> each?
>
> Thanks,
> Hannes
>

Re: [sqlite] Size of INSERT and UPDATE in TRANSACTION

[Martin Engelschalk]

Hello Hannes,

I think no, you need not split your transaction. I do millions on insert 
in one transaction and it works real fast


Martin

Hannes Ricklefs schrieb:


Hello,

I was wondering if anyone has any experience with the number of INSERT UPDATE
statements in one TRANSACTION. For example I have the situation that i have to
do around 20.000 INSERTS in one TRANSACTION, so I am wondering if it has any
performance improvements if I split these up into smaller TRANSACTIONS of 1000
each?

Thanks,
Hannes
 

[sqlite] Size of INSERT and UPDATE in TRANSACTION

[Hannes Ricklefs]

Hello,

I was wondering if anyone has any experience with the number of INSERT UPDATE
statements in one TRANSACTION. For example I have the situation that i have to
do around 20.000 INSERTS in one TRANSACTION, so I am wondering if it has any
performance improvements if I split these up into smaller TRANSACTIONS of 1000
each?

Thanks,
Hannes

[sqlite] size of the cache

[Jérôme VERITE]

Hi, 

I want to optimize requests on a big database which works with sqlite
2.8.14. The first time I make a sql request, is spends 4 minutes before 
returning a result, but the second time, it take only 2 seconds. I think
it’s the use of the cache which permits such performances. S

How can I modify Sqlite source code to keep the integrality of a big
database in the cache? It will permit to respond rapidly to all the 
differents queries I will be able to submit.

Thanks in advance

Jerome.

Re: [sqlite] Size of fields

[Kurt Welgehausen]

SQLite mostly ignores type info.

See  and #q11.

Also .

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Size of fields

[Erik Jälevik]

I've just started out with SQLite and have a basic question I couldn't find an
answer for in the documentation.

Are there any size limits to individual fields? The documentation says that a
valid type is any string followed by one or two integers, e.g. VARCHAR(10). Are
the integers in the brackets taken into account or are they simply ignored by
the engine?

Thanks,
Erik


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]