Amalgamation compilation with SQLITE_THREADSAFE=0

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

Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
Hi All,
I am currently doing amalgamation compilation of SQLite and target for low
computational power, portable, single thread devices.
The OS version used in these devices are the very simplified version of
Linux OS. More specifically, it does not have pthread, mutex and
mutli-threading related functions. it has its own specific file system.

Hence to let the SQLite compiles successfully, I set the following MACRO
during compilation:
SQLITE_OS_OTHER=1
SQLITE_THREADSAFE=0
SQLITE_OMIT_LOAD_EXTENSION=1
SQLITE_OMIT_MEMORYDB=1

I would like to know
1. Can I call sqlite3_open more than one times before calling sqlite3_close
and sqlite3_free?
2. If the above mentioned devices change to mutli-thread setting but no
thread safe functions such as mutex, is this setting still fine?
     If not, how can I make it thread safe with lack of mutex support in
the system?

Thanks.

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Simon Slavin-3


On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:

> 1. Can I call sqlite3_open more than one times before calling sqlite3_close
> and sqlite3_free?

Call sqlite3_open() for each database you want to open.  You can have any number of databases open at the same time.  Call sqlite3_close() for each database you have open when you no longer need it.  After closing the last connection call sqlite3_shutdown() as described in

<https://sqlite.org/c3ref/initialize.html>

(The above ignores use of SQL's ATTACH command.)

You are not expected to ever call sqlite3_free() unless you are using SQLite to do other memory-handing tasks for you.  Most people who use SQLite never call sqlite3_free().

> 2. If the above mentioned devices change to mutli-thread setting but no
> thread safe functions such as mutex, is this setting still fine?

You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will arrange that only one thread will be doing SQLite calls at once.  As long as you can ensure this, SQLite will function correctly.

>     If not, how can I make it thread safe with lack of mutex support in
> the system?

Do any of the following:

A) Implement your own mutex system.

B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/mutex_alloc.html> )

C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections using sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html> .

Please note that the above is a top-of-the-head answer and I have not personally tries each of the options to make sure it works.

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
Hi All,
While using my own implemented file system, the db file size will only
expand and not prune even remove record from the db.
Could anyone advise me that what I am missing in order to pruning the db
size when removing a number of records?
Jacky

On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
>
> > 1. Can I call sqlite3_open more than one times before calling
> sqlite3_close
> > and sqlite3_free?
>
> Call sqlite3_open() for each database you want to open.  You can have any
> number of databases open at the same time.  Call sqlite3_close() for each
> database you have open when you no longer need it.  After closing the last
> connection call sqlite3_shutdown() as described in
>
> <https://sqlite.org/c3ref/initialize.html>
>
> (The above ignores use of SQL's ATTACH command.)
>
> You are not expected to ever call sqlite3_free() unless you are using
> SQLite to do other memory-handing tasks for you.  Most people who use
> SQLite never call sqlite3_free().
>
> > 2. If the above mentioned devices change to mutli-thread setting but no
> > thread safe functions such as mutex, is this setting still fine?
>
> You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
> arrange that only one thread will be doing SQLite calls at once.  As long
> as you can ensure this, SQLite will function correctly.
>
> >     If not, how can I make it thread safe with lack of mutex support in
> > the system?
>
> Do any of the following:
>
> A) Implement your own mutex system.
>
> B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/mutex_alloc.html>
> )
>
> C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections using
> sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html> .
>
> Please note that the above is a top-of-the-head answer and I have not
> personally tries each of the options to make sure it works.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Stephen Chrzanowski
You'll want to vacuum the database.

https://sqlite.org/lang_vacuum.html

Deleting records from a SQLite database only changes the pages that already
exist within the file.  It doesn't prune anything automatically.  It can do
so, though, if you set the appropriate pragma.

On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam <[hidden email]> wrote:

> Hi All,
> While using my own implemented file system, the db file size will only
> expand and not prune even remove record from the db.
> Could anyone advise me that what I am missing in order to pruning the db
> size when removing a number of records?
> Jacky
>
> On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]>
> wrote:
>
> >
> >
> > On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
> >
> > > 1. Can I call sqlite3_open more than one times before calling
> > sqlite3_close
> > > and sqlite3_free?
> >
> > Call sqlite3_open() for each database you want to open.  You can have any
> > number of databases open at the same time.  Call sqlite3_close() for each
> > database you have open when you no longer need it.  After closing the
> last
> > connection call sqlite3_shutdown() as described in
> >
> > <https://sqlite.org/c3ref/initialize.html>
> >
> > (The above ignores use of SQL's ATTACH command.)
> >
> > You are not expected to ever call sqlite3_free() unless you are using
> > SQLite to do other memory-handing tasks for you.  Most people who use
> > SQLite never call sqlite3_free().
> >
> > > 2. If the above mentioned devices change to mutli-thread setting but no
> > > thread safe functions such as mutex, is this setting still fine?
> >
> > You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
> > arrange that only one thread will be doing SQLite calls at once.  As long
> > as you can ensure this, SQLite will function correctly.
> >
> > >     If not, how can I make it thread safe with lack of mutex support in
> > > the system?
> >
> > Do any of the following:
> >
> > A) Implement your own mutex system.
> >
> > B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/
> mutex_alloc.html>
> > )
> >
> > C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections using
> > sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html>
> .
> >
> > Please note that the above is a top-of-the-head answer and I have not
> > personally tries each of the options to make sure it works.
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Stephen Chrzanowski
err..  Clarification... It can be set to automatically vacuum the database
on connection close when setting a particular pragma.

On Fri, Sep 1, 2017 at 12:47 PM, Stephen Chrzanowski <[hidden email]>
wrote:

> You'll want to vacuum the database.
>
> https://sqlite.org/lang_vacuum.html
>
> Deleting records from a SQLite database only changes the pages that
> already exist within the file.  It doesn't prune anything automatically.
> It can do so, though, if you set the appropriate pragma.
>
> On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam <[hidden email]> wrote:
>
>> Hi All,
>> While using my own implemented file system, the db file size will only
>> expand and not prune even remove record from the db.
>> Could anyone advise me that what I am missing in order to pruning the db
>> size when removing a number of records?
>> Jacky
>>
>> On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]>
>> wrote:
>>
>> >
>> >
>> > On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
>> >
>> > > 1. Can I call sqlite3_open more than one times before calling
>> > sqlite3_close
>> > > and sqlite3_free?
>> >
>> > Call sqlite3_open() for each database you want to open.  You can have
>> any
>> > number of databases open at the same time.  Call sqlite3_close() for
>> each
>> > database you have open when you no longer need it.  After closing the
>> last
>> > connection call sqlite3_shutdown() as described in
>> >
>> > <https://sqlite.org/c3ref/initialize.html>
>> >
>> > (The above ignores use of SQL's ATTACH command.)
>> >
>> > You are not expected to ever call sqlite3_free() unless you are using
>> > SQLite to do other memory-handing tasks for you.  Most people who use
>> > SQLite never call sqlite3_free().
>> >
>> > > 2. If the above mentioned devices change to mutli-thread setting but
>> no
>> > > thread safe functions such as mutex, is this setting still fine?
>> >
>> > You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
>> > arrange that only one thread will be doing SQLite calls at once.  As
>> long
>> > as you can ensure this, SQLite will function correctly.
>> >
>> > >     If not, how can I make it thread safe with lack of mutex support
>> in
>> > > the system?
>> >
>> > Do any of the following:
>> >
>> > A) Implement your own mutex system.
>> >
>> > B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/mute
>> x_alloc.html>
>> > )
>> >
>> > C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections
>> using
>> > sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html>
>> .
>> >
>> > Please note that the above is a top-of-the-head answer and I have not
>> > personally tries each of the options to make sure it works.
>> >
>> > Simon.
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
In reply to this post by Stephen Chrzanowski
What I am understood from the answer is explicit code must be used during
creating db, for example, auto_vacuum=FULL.
If no, the file size will not reduce even deleting a number of records and
this is normal.


On Sat, Sep 2, 2017 at 12:47 AM, Stephen Chrzanowski <[hidden email]>
wrote:

> You'll want to vacuum the database.
>
> https://sqlite.org/lang_vacuum.html
>
> Deleting records from a SQLite database only changes the pages that already
> exist within the file.  It doesn't prune anything automatically.  It can do
> so, though, if you set the appropriate pragma.
>
> On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam <[hidden email]> wrote:
>
> > Hi All,
> > While using my own implemented file system, the db file size will only
> > expand and not prune even remove record from the db.
> > Could anyone advise me that what I am missing in order to pruning the db
> > size when removing a number of records?
> > Jacky
> >
> > On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]>
> > wrote:
> >
> > >
> > >
> > > On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
> > >
> > > > 1. Can I call sqlite3_open more than one times before calling
> > > sqlite3_close
> > > > and sqlite3_free?
> > >
> > > Call sqlite3_open() for each database you want to open.  You can have
> any
> > > number of databases open at the same time.  Call sqlite3_close() for
> each
> > > database you have open when you no longer need it.  After closing the
> > last
> > > connection call sqlite3_shutdown() as described in
> > >
> > > <https://sqlite.org/c3ref/initialize.html>
> > >
> > > (The above ignores use of SQL's ATTACH command.)
> > >
> > > You are not expected to ever call sqlite3_free() unless you are using
> > > SQLite to do other memory-handing tasks for you.  Most people who use
> > > SQLite never call sqlite3_free().
> > >
> > > > 2. If the above mentioned devices change to mutli-thread setting but
> no
> > > > thread safe functions such as mutex, is this setting still fine?
> > >
> > > You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
> > > arrange that only one thread will be doing SQLite calls at once.  As
> long
> > > as you can ensure this, SQLite will function correctly.
> > >
> > > >     If not, how can I make it thread safe with lack of mutex support
> in
> > > > the system?
> > >
> > > Do any of the following:
> > >
> > > A) Implement your own mutex system.
> > >
> > > B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/
> > mutex_alloc.html>
> > > )
> > >
> > > C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections
> using
> > > sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html
> >
> > .
> > >
> > > Please note that the above is a top-of-the-head answer and I have not
> > > personally tries each of the options to make sure it works.
> > >
> > > Simon.
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Darko Volaric
Maybe you misunderstood that sentence:  "auto_vacuum=FULL" mode will reduce the file size but so will a "VACUUM" command, independent of the auto_vacuum setting. It makes that crystal clear by detailing how it works later on that page:

> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file.


So it will by definition use the minimum possible amount of space for the resulting database file.



> On Sep 1, 2017, at 6:53 PM, Jacky Lam <[hidden email]> wrote:
>
> What I am understood from the answer is explicit code must be used during
> creating db, for example, auto_vacuum=FULL.
> If no, the file size will not reduce even deleting a number of records and
> this is normal.
>
>
> On Sat, Sep 2, 2017 at 12:47 AM, Stephen Chrzanowski <[hidden email]>
> wrote:
>
>> You'll want to vacuum the database.
>>
>> https://sqlite.org/lang_vacuum.html
>>
>> Deleting records from a SQLite database only changes the pages that already
>> exist within the file.  It doesn't prune anything automatically.  It can do
>> so, though, if you set the appropriate pragma.
>>
>> On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam <[hidden email]> wrote:
>>
>>> Hi All,
>>> While using my own implemented file system, the db file size will only
>>> expand and not prune even remove record from the db.
>>> Could anyone advise me that what I am missing in order to pruning the db
>>> size when removing a number of records?
>>> Jacky
>>>
>>> On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]>
>>> wrote:
>>>
>>>>
>>>>
>>>> On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
>>>>
>>>>> 1. Can I call sqlite3_open more than one times before calling
>>>> sqlite3_close
>>>>> and sqlite3_free?
>>>>
>>>> Call sqlite3_open() for each database you want to open.  You can have
>> any
>>>> number of databases open at the same time.  Call sqlite3_close() for
>> each
>>>> database you have open when you no longer need it.  After closing the
>>> last
>>>> connection call sqlite3_shutdown() as described in
>>>>
>>>> <https://sqlite.org/c3ref/initialize.html>
>>>>
>>>> (The above ignores use of SQL's ATTACH command.)
>>>>
>>>> You are not expected to ever call sqlite3_free() unless you are using
>>>> SQLite to do other memory-handing tasks for you.  Most people who use
>>>> SQLite never call sqlite3_free().
>>>>
>>>>> 2. If the above mentioned devices change to mutli-thread setting but
>> no
>>>>> thread safe functions such as mutex, is this setting still fine?
>>>>
>>>> You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will
>>>> arrange that only one thread will be doing SQLite calls at once.  As
>> long
>>>> as you can ensure this, SQLite will function correctly.
>>>>
>>>>>    If not, how can I make it thread safe with lack of mutex support
>> in
>>>>> the system?
>>>>
>>>> Do any of the following:
>>>>
>>>> A) Implement your own mutex system.
>>>>
>>>> B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/
>>> mutex_alloc.html>
>>>> )
>>>>
>>>> C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections
>> using
>>>> sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/open.html
>>>
>>> .
>>>>
>>>> Please note that the above is a top-of-the-head answer and I have not
>>>> personally tries each of the options to make sure it works.
>>>>
>>>> Simon.
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
Ok, they are two individual ways to vacuum the db file size. Thanks.

On Sat, Sep 2, 2017 at 1:17 AM, Darko Volaric <[hidden email]> wrote:

> Maybe you misunderstood that sentence:  "auto_vacuum=FULL" mode will
> reduce the file size but so will a "VACUUM" command, independent of the
> auto_vacuum setting. It makes that crystal clear by detailing how it works
> later on that page:
>
> > The VACUUM command works by copying the contents of the database into a
> temporary database file and then overwriting the original with the contents
> of the temporary file.
>
>
> So it will by definition use the minimum possible amount of space for the
> resulting database file.
>
>
>
> > On Sep 1, 2017, at 6:53 PM, Jacky Lam <[hidden email]> wrote:
> >
> > What I am understood from the answer is explicit code must be used during
> > creating db, for example, auto_vacuum=FULL.
> > If no, the file size will not reduce even deleting a number of records
> and
> > this is normal.
> >
> >
> > On Sat, Sep 2, 2017 at 12:47 AM, Stephen Chrzanowski <
> [hidden email]>
> > wrote:
> >
> >> You'll want to vacuum the database.
> >>
> >> https://sqlite.org/lang_vacuum.html
> >>
> >> Deleting records from a SQLite database only changes the pages that
> already
> >> exist within the file.  It doesn't prune anything automatically.  It
> can do
> >> so, though, if you set the appropriate pragma.
> >>
> >> On Fri, Sep 1, 2017 at 12:41 PM, Jacky Lam <[hidden email]> wrote:
> >>
> >>> Hi All,
> >>> While using my own implemented file system, the db file size will only
> >>> expand and not prune even remove record from the db.
> >>> Could anyone advise me that what I am missing in order to pruning the
> db
> >>> size when removing a number of records?
> >>> Jacky
> >>>
> >>> On Wed, Aug 9, 2017 at 11:02 AM, Simon Slavin <[hidden email]>
> >>> wrote:
> >>>
> >>>>
> >>>>
> >>>> On 9 Aug 2017, at 3:31am, Jacky Lam <[hidden email]> wrote:
> >>>>
> >>>>> 1. Can I call sqlite3_open more than one times before calling
> >>>> sqlite3_close
> >>>>> and sqlite3_free?
> >>>>
> >>>> Call sqlite3_open() for each database you want to open.  You can have
> >> any
> >>>> number of databases open at the same time.  Call sqlite3_close() for
> >> each
> >>>> database you have open when you no longer need it.  After closing the
> >>> last
> >>>> connection call sqlite3_shutdown() as described in
> >>>>
> >>>> <https://sqlite.org/c3ref/initialize.html>
> >>>>
> >>>> (The above ignores use of SQL's ATTACH command.)
> >>>>
> >>>> You are not expected to ever call sqlite3_free() unless you are using
> >>>> SQLite to do other memory-handing tasks for you.  Most people who use
> >>>> SQLite never call sqlite3_free().
> >>>>
> >>>>> 2. If the above mentioned devices change to mutli-thread setting but
> >> no
> >>>>> thread safe functions such as mutex, is this setting still fine?
> >>>>
> >>>> You have explicitly declared SQLITE_THREADSAFE=0 .  That means you
> will
> >>>> arrange that only one thread will be doing SQLite calls at once.  As
> >> long
> >>>> as you can ensure this, SQLite will function correctly.
> >>>>
> >>>>>    If not, how can I make it thread safe with lack of mutex support
> >> in
> >>>>> the system?
> >>>>
> >>>> Do any of the following:
> >>>>
> >>>> A) Implement your own mutex system.
> >>>>
> >>>> B) Use SQLite’s mutex system ( <https://sqlite.org/c3ref/
> >>> mutex_alloc.html>
> >>>> )
> >>>>
> >>>> C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections
> >> using
> >>>> sqlite3_open_v2(), as described in <https://sqlite.org/c3ref/
> open.html
> >>>
> >>> .
> >>>>
> >>>> Please note that the above is a top-of-the-head answer and I have not
> >>>> personally tries each of the options to make sure it works.
> >>>>
> >>>> Simon.
> >>>> _______________________________________________
> >>>> sqlite-users mailing list
> >>>> [hidden email]
> >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>>>
> >>> _______________________________________________
> >>> sqlite-users mailing list
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

R Smith
In reply to this post by Jacky Lam

On 2017/09/01 6:53 PM, Jacky Lam wrote:
> What I am understood from the answer is explicit code must be used during
> creating db, for example, auto_vacuum=FULL.
> If no, the file size will not reduce even deleting a number of records and
> this is normal.

Yes. If you create the database and run the SQL (among your other set-up
statements) "auto_vacuum=FULL;" then it will automatically vacuum the DB
when appropriate (such as when you close a connection after deleting stuff).

If however you haven't done that, you can at any time simply run the SQL
statement "VACUUM;" and it will vacuum the DB right on the spot.

Note also that the saving in disk space might not correlate 100% with
the deleted rows. Vacuum re-packs the rows (remaining in the DB) and get
rids of empty (aka "Free") pages. Pages are acquired and freed in the
DB's page-size (see pragma page_size setting) and such size may or may
not encompass one, less than one, or several rows depending on the row
data sizes, and as such any specific page may or may not be completely
filled with a data payload after vacuuming.


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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

David Raymond
In reply to this post by Jacky Lam
Other notes in addition to the previous responses.

File size will never go down without some sort of vacuum operation. Basically since you can only truncate files, and not snip out sections in the middle. If you delete from the front of a large file, in order to shrink it you'd have to re-write the entire file from that point to the end. So instead of doing that it just recycles pages when possible, and only expands the file when it runs out of free space and needs more.

Here's the blurb on autovacuum: http://www.sqlite.org/pragma.html#pragma_auto_vacuum

INCREMENTAL mode will allow you free up unused pages when you explicitly tell it to, FULL mode will do it automatically.

Autovacuum does not do the extra nifty steps that a regular vacuum does. From the above linked blurb:

"When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse."

A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will re-write the entire file (twice actually) and do extra things, for example re-ordering all of the records of a table so that they're all nicely packed together in a continguous section, already in primary key order, and no longer semi-randomly scattered throughout.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jacky Lam
Sent: Friday, September 01, 2017 12:42 PM
To: SQLite mailing list
Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

Hi All,
While using my own implemented file system, the db file size will only
expand and not prune even remove record from the db.
Could anyone advise me that what I am missing in order to pruning the db
size when removing a number of records?
Jacky

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
In reply to this post by Jacky Lam
Thanks for the answer. I have a clear picture now.I would also like to know for Android SQLite case;does the default value for auto vacuum is OFF.Is it the same default compiling config with sqlite.c source code?In other words, does it need to do vacuum manually?


從我的 Samsung Galaxy 智能手機發送。
-------- 原始訊息 --------由: David Raymond <[hidden email]> 日期: 17/9/2  上午2:43  (GMT+08:00) 收件人: SQLite mailing list <[hidden email]> 主題: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0
Other notes in addition to the previous responses.

File size will never go down without some sort of vacuum operation. Basically since you can only truncate files, and not snip out sections in the middle. If you delete from the front of a large file, in order to shrink it you'd have to re-write the entire file from that point to the end. So instead of doing that it just recycles pages when possible, and only expands the file when it runs out of free space and needs more.

Here's the blurb on autovacuum: http://www.sqlite.org/pragma.html#pragma_auto_vacuum

INCREMENTAL mode will allow you free up unused pages when you explicitly tell it to, FULL mode will do it automatically.

Autovacuum does not do the extra nifty steps that a regular vacuum does. From the above linked blurb:

"When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse."

A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will re-write the entire file (twice actually) and do extra things, for example re-ordering all of the records of a table so that they're all nicely packed together in a continguous section, already in primary key order, and no longer semi-randomly scattered throughout.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jacky Lam
Sent: Friday, September 01, 2017 12:42 PM
To: SQLite mailing list
Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

Hi All,
While using my own implemented file system, the db file size will only
expand and not prune even remove record from the db.
Could anyone advise me that what I am missing in order to pruning the db
size when removing a number of records?
Jacky

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Simon Slavin-3


On 2 Sep 2017, at 10:55am, Jacky Lam <[hidden email]> wrote:

> Thanks for the answer. I have a clear picture now.I would also like to know for Android SQLite case;does the default value for auto vacuum is OFF.Is it the same default compiling config with sqlite.c source code?In other words, does it need to do vacuum manually?

It’s not a good idea to depend on the default setting for auto_vacuum because it may be set differently in the compilation you’re using.  It’s safer to set it yourself any time you create a new database.  The setting is stored inside the database file and will stick with it unless it is intentionally changed.

And please note that you never /need/ to do any kind of VACUUM in a production setting.  The only situation where it helps is if your database is significantly shrinking and will not grow again soon, and that’s quite rare for a database.  Doing a pointless VACUUM just means you’re wearing out your storage device.

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Rowan Worth-2
On 2 September 2017 at 22:19, Simon Slavin <[hidden email]> wrote:
>
> And please note that you never /need/ to do any kind of VACUUM in a
> production setting.  The only situation where it helps is if your database
> is significantly shrinking and will not grow again soon, and that’s quite
> rare for a database.  Doing a pointless VACUUM just means you’re wearing
> out your storage device.


Good point about superflous VACUUMs, but the situation you mentioned is not
the only one it helps with. VACUUM also has the side effect of rewriting
the tables/indices in row order on disk, and I've observed this
"defragmentation" improving performance significantly when databases are
stored on network file systems (NFS3/NFS4 to be specific). I haven't seen
it make a significant difference on local disk, so not really revelant to
android.

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
In reply to this post by Jacky Lam
Hi All,
The reason I consider to use VACUUM is that:
when I insert 10k and delete 10k records for a number of times, the db file
size keeps constant in each iteration.
On the other hand, if I terminate the program manually and start the
iteration again, the db file size increase once in the first iteration.
Then it keeps file size constant afterwards.
For example,
1. when no manual termination of program, the file size keeps constant 100kB
2. when I terminate the program and start the iteration again, the file
size changes to 200kB in the first iteration.
3. After the first iteration, the file size keeps at 200kB in later
iterations.

Does anyone can advise me that which parts I am missing for the
Amalgamation compilation of USING *** OTHER VFS ***?

On Sat, Sep 2, 2017 at 5:55 PM, Jacky Lam <[hidden email]> wrote:

> Thanks for the answer. I have a clear picture now.
> I would also like to know for Android SQLite case;
> does the default value for auto vacuum is OFF.
> Is it the same default compiling config with sqlite.c source code?
> In other words, does it need to do vacuum manually?
>
>
>
> 從我的 Samsung Galaxy 智能手機發送。
>
> -------- 原始訊息 --------
> 由: David Raymond <[hidden email]>
> 日期: 17/9/2 上午2:43 (GMT+08:00)
> 收件人: SQLite mailing list <[hidden email]>
> 主題: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0
>
> Other notes in addition to the previous responses.
>
> File size will never go down without some sort of vacuum operation.
> Basically since you can only truncate files, and not snip out sections in
> the middle. If you delete from the front of a large file, in order to
> shrink it you'd have to re-write the entire file from that point to the
> end. So instead of doing that it just recycles pages when possible, and
> only expands the file when it runs out of free space and needs more.
>
> Here's the blurb on autovacuum: http://www.sqlite.org/pragma.
> html#pragma_auto_vacuum
>
> INCREMENTAL mode will allow you free up unused pages when you explicitly
> tell it to, FULL mode will do it automatically.
>
> Autovacuum does not do the extra nifty steps that a regular vacuum does.
> From the above linked blurb:
>
> "When the auto-vacuum mode is 1 or "full", the freelist pages are moved to
> the end of the database file and the database file is truncated to remove
> the freelist pages at every transaction commit. Note, however, that
> auto-vacuum only truncates the freelist pages from the file. Auto-vacuum
> does not defragment the database nor repack individual database pages the
> way that the VACUUM command does. In fact, because it moves pages around
> within the file, auto-vacuum can actually make fragmentation worse."
>
> A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will
> re-write the entire file (twice actually) and do extra things, for example
> re-ordering all of the records of a table so that they're all nicely packed
> together in a continguous section, already in primary key order, and no
> longer semi-randomly scattered throughout.
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jacky Lam
> Sent: Friday, September 01, 2017 12:42 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0
>
> Hi All,
> While using my own implemented file system, the db file size will only
> expand and not prune even remove record from the db.
> Could anyone advise me that what I am missing in order to pruning the db
> size when removing a number of records?
> Jacky
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Richard Damon
On 9/6/17 9:42 PM, Jacky Lam wrote:

> Hi All,
> The reason I consider to use VACUUM is that:
> when I insert 10k and delete 10k records for a number of times, the db file
> size keeps constant in each iteration.
> On the other hand, if I terminate the program manually and start the
> iteration again, the db file size increase once in the first iteration.
> Then it keeps file size constant afterwards.
> For example,
> 1. when no manual termination of program, the file size keeps constant 100kB
> 2. when I terminate the program and start the iteration again, the file
> size changes to 200kB in the first iteration.
> 3. After the first iteration, the file size keeps at 200kB in later
> iterations.
>
> Does anyone can advise me that which parts I am missing for the
> Amalgamation compilation of USING *** OTHER VFS ***?
>
I'm thinking you don't understand why vacuum is not the default. Your
examples show good reason why this is.

If an application doesn't delete a lot of records, it doesn't leave a
lot of empty space so doesn't need vacuum.

If an application does delete a lot of records, it is very likely in the
near future to create a similar number in the near future, so doing a
vacuum to temporarily free the space is a lot of work for little gain.

Yes, there is a use case where you delete a lot of records and it will
be a long time before more records are created, and perhaps freeing the
space may be important, so in that particular case, the program should
do the vacuum.

--
Richard Damon

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

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Jacky Lam
In reply to this post by Jacky Lam
Hi All,
I would like to know is there a way to test Amalgamation compilation build
like Test Harnesses?

Since I am currently adopting specific vfs for the build, I do want to test
the correctness of my porting to SQLite3.

On Thu, Sep 7, 2017 at 9:42 AM, Jacky Lam <[hidden email]> wrote:

> Hi All,
> The reason I consider to use VACUUM is that:
> when I insert 10k and delete 10k records for a number of times, the db
> file size keeps constant in each iteration.
> On the other hand, if I terminate the program manually and start the
> iteration again, the db file size increase once in the first iteration.
> Then it keeps file size constant afterwards.
> For example,
> 1. when no manual termination of program, the file size keeps constant
> 100kB
> 2. when I terminate the program and start the iteration again, the file
> size changes to 200kB in the first iteration.
> 3. After the first iteration, the file size keeps at 200kB in later
> iterations.
>
> Does anyone can advise me that which parts I am missing for the
> Amalgamation compilation of USING *** OTHER VFS ***?
>
> On Sat, Sep 2, 2017 at 5:55 PM, Jacky Lam <[hidden email]> wrote:
>
>> Thanks for the answer. I have a clear picture now.
>> I would also like to know for Android SQLite case;
>> does the default value for auto vacuum is OFF.
>> Is it the same default compiling config with sqlite.c source code?
>> In other words, does it need to do vacuum manually?
>>
>>
>>
>> 從我的 Samsung Galaxy 智能手機發送。
>>
>> -------- 原始訊息 --------
>> 由: David Raymond <[hidden email]>
>> 日期: 17/9/2 上午2:43 (GMT+08:00)
>> 收件人: SQLite mailing list <[hidden email]>
>> 主題: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0
>>
>> Other notes in addition to the previous responses.
>>
>> File size will never go down without some sort of vacuum operation.
>> Basically since you can only truncate files, and not snip out sections in
>> the middle. If you delete from the front of a large file, in order to
>> shrink it you'd have to re-write the entire file from that point to the
>> end. So instead of doing that it just recycles pages when possible, and
>> only expands the file when it runs out of free space and needs more.
>>
>> Here's the blurb on autovacuum: http://www.sqlite.org/pragma.h
>> tml#pragma_auto_vacuum
>>
>> INCREMENTAL mode will allow you free up unused pages when you explicitly
>> tell it to, FULL mode will do it automatically.
>>
>> Autovacuum does not do the extra nifty steps that a regular vacuum does.
>> From the above linked blurb:
>>
>> "When the auto-vacuum mode is 1 or "full", the freelist pages are moved
>> to the end of the database file and the database file is truncated to
>> remove the freelist pages at every transaction commit. Note, however, that
>> auto-vacuum only truncates the freelist pages from the file. Auto-vacuum
>> does not defragment the database nor repack individual database pages the
>> way that the VACUUM command does. In fact, because it moves pages around
>> within the file, auto-vacuum can actually make fragmentation worse."
>>
>> A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will
>> re-write the entire file (twice actually) and do extra things, for example
>> re-ordering all of the records of a table so that they're all nicely packed
>> together in a continguous section, already in primary key order, and no
>> longer semi-randomly scattered throughout.
>>
>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]]
>> On Behalf Of Jacky Lam
>> Sent: Friday, September 01, 2017 12:42 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0
>>
>> Hi All,
>> While using my own implemented file system, the db file size will only
>> expand and not prune even remove record from the db.
>> Could anyone advise me that what I am missing in order to pruning the db
>> size when removing a number of records?
>> Jacky
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Amalgamation compilation with SQLITE_THREADSAFE=0

Richard Hipp-3
On 9/13/17, Jacky Lam <[hidden email]> wrote:
> Hi All,
> I would like to know is there a way to test Amalgamation compilation build
> like Test Harnesses?
>
> Since I am currently adopting specific vfs for the build, I do want to test
> the correctness of my porting to SQLite3.

If you are able to port the TCL library to your "low computational
power, portable, single thread devices" then you can use the tests
from the canonical SQLite source tree.

We have a set of proprietary test cases for SQLite called TH3 that
will run on minimal hardware.  (https://www.sqlite.org/th3.html)
However, the TH3 test are proprietary.  You can contract with the
SQLite developers to do the testing for you, but that is a pricey
proposition.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users