[MODULE] Automatic SQLite DB Optimizer

Search This thread

73sydney

Senior Member
Automatically optimizes SQLite databases on boot, on schedule, every X days.

Just a quick and dirty adaptation of an old SQLite3 optimizer script from init.d days to a Magisk Module using a service.sh script instead, with an internal date offset mechanism to run the script every X days (default: 3 - which is plenty, and specifically to stop silliness).

Update And credit note: finally after a fair bit of digging, i think i located the original author of the original init.d version of the main vacuum code snippet ive repurposed (stolen) for this module. It appears to be from user @mcbyte_it (doesnt seem to be current or active) and from a post here. Although i only saw derivative reworkings of this script, and used only the main sqllite script function, i arrived at a different method of tracking last run date, when his is arguably more mainstream/concise. Im fairly certain this is the originator especially as the post also has a zipalign script that was also suggested to me to do....and which i have been fiddling with for months....

The script will wait until boot is completed AND then until avg CPU usage is under 30%, to minimise the risk of possible corruption.

Disclaimer: As always any use of any 3rd party script/software/advice is at the users discretion. All reasonable efforts have been made to make this as safe as possible, but the responsibility ultimately falls to the user whether to use and run the script.

What it does:​

It:
  • Reindexes
  • Vacuums
  • Analyzes
all .db files under /data.

It runs a 1st run optimize after install (temp file optimsql_first_run on sdcard is used to enable this, and removed after first run), and then on schedule after that.

By default it logs just script progress to /storage/emulated/0/autosqlite.log, but you can choose to enable more detail in the log if you wish

User Configurable Options:​

The schedule and loglevel can be changed by an external file on sdcard:
  1. Create a file named autosqlite_options on sdcard (/storage/emulated/0/)
  2. Inside create the follow key=value pairs to suit your preference:
interval=x (where x is the number of days between script runs, for the love of god do not put 1 (this goes out especially to the kind of people who put every app on their Magisk magiskhide/deny list) there is no benefit and you just heighten the possibility of corruption)

loglevel=x (where x is either 1 (detailed logging) or 0 (basic logging - default))

Requirements:​

This module requires a working SQLite3 binary. If your ROM does not provide one (you can check via typing sqlite3 into a terminal), you can choose to use my SQLite3UniversalBinaries module located here:

https://github.com/stylemessiah/SQLite3UniversalBinaries

Dont forget you need to download a named SQLite3UniversalBinaries.vx.x.zip file from the Releases page under Assets. Do not try installing the source code with Magisk Manager, it will not go as you expect

All the modules action takes place in the service.sh file, its commented reasonably well for those wanting to check how it works

* See... now theres something else to use with my SQLite3 Universal Binaries module other than the GPay SQlite Fix Module <- dont use that anymore, it makes me stabby. It is literally the last resort for getting Google Pay/Wallet to work ,

Please note: the included LICENSE only covers the module components provided by the excellent work of @Zackptg5 's Magisk Module Extended, which is available for here for module creators

https://github.com/Zackptg5/MMT-Extended/

All other work is credited above and no one may fork or re-present this module as their own for the purposes of trying to monetize this module or its content without all parties permission. The module comes specifically without an overall license for this intent.

Download:


Repo: https://github.com/stylemessiah/AutomaticSQLiteDBOptimizer

Release: https://github.com/stylemessiah/AutomaticSQLiteDBOptimizer/releases/latest

Powered by Ponkle
 
Last edited:

73sydney

Senior Member

73sydney

Senior Member
Nice
how about no schedule, i want to execute it whenever i want
possible ?

I refer the gentleman to the name of the module and title of the thread:

"Automatic SQLite DB Optimizer"

the distinct lack of the word "manual" should be a clue :) :)

what are they teaching in school these days? :) :)

i made this to be automatic and made special note about the intervals because theres people who would abuse this and run it every day if they could, both designed to avoid misuse/abuse:

interval=x (where x is the number of days between script runs, for the love of god do not put 1 (this goes out especially to the kind of people who put every app on their Magisk magiskhide/deny list) there is no benefit and you just heighten the possibility of corruption)

im not really interested in making a manual version option unless you can convince me where there is a use case benefit, because all i see is potential for misuse/abuse that i specifically made this to avoid....

running it by default it runs every 3 days which is twice a week and probably still overkill, but trying to cover all bases

your other option is to ask me for permission (sadly because of abuse in the past from certain entities trying to monetize my and others work, my contributions are not GPL etc) to fork and alter the code yourself....
 
Last edited:

loopypalm

Senior Member
Jul 8, 2016
1,147
1
330
Osaka
I refer the gentleman to the name of the module and title of the thread:

"Automatic SQLite DB Optimizer"

the distinct lack of the word "manual" should be a clue :) :)

what are they teaching in school these days? :) :)

i made this to be automatic and made special note about the intervals because theres people who would abuse this and run it every day if they could, both designed to avoid misuse/abuse:

interval=x (where x is the number of days between script runs, for the love of god do not put 1 (this goes out especially to the kind of people who put every app on their Magisk magiskhide/deny list) there is no benefit and you just heighten the possibility of corruption)

im not really interested in making a manual version option unless you can convince me where there is a use case benefit, because all i see is potential for misuse/abuse that i specifically made this to avoid....

running it by default it runs every 3 days which is twice a week and probably still overkill, but trying to cover all bases

your other option is to ask me for permission (sadly because of abuse in the past from certain entities trying to monetize my and others work, my contributions are not GPL etc) to fork and alter the code yourself....
i was planing to use it once a week
categorizing me with people who do dumb stuff is an 'insult'
i asked a simple question man, don't jump to level 3 mind game please ...
 

73sydney

Senior Member
i was planing to use it once a week
categorizing me with people who do dumb stuff is an 'insult'
i asked a simple question man, don't jump to level 3 mind game please ...

Im sorry, did you miss the double smileys intended to completely avoid any misunderstanding that i was being jovial. the other tip off that i wasnt burning you was taking the time to write several paragraphs

Can i suggest you just set the options file interval to 6 or 7 and let the module do what its described in the title as doing, it would make no functional difference, other than saving you a trip to terminal?

As i said, give me a valid reason for adding a manual option and ill gladly consider it, choosing a reason that fits completely within the actual parameters of the existing module doesnt really fit that offer...

ill go further to why i specifically didnt put in an option to run it manually (really folks are lucky i didnt block the option to run it every day) because you should understand what the sqlite vacuuming and other processes do:

* They copy the data from the db file to temp file before optimising it and writing it back, at any point in this theres the potential for corruption (sudden power loss, other processes accessing the file etc). Ideally any such corruption would be minimised by journaling but anyone who had used Linux for long enough knows not to count on such things.

* If any db is being written to, then the optimization process will NOT optimize that db...for the above possible corruption reasons. Hence why i not only put in a wait till boot completed function BUT also added a function to block the script from running unless avg cpu load over 5 minutes is less than 30%. all this to try and make things as safe as possible. So im not overly fond of a manual option which bypasses these safeties i specifically put there to hopefully safeguard people as best as possible. I dont want to get messages about how my module hosed someones apps.

Perhaps that better addresses my reasons?

As said, you can also ask me to fork the code, and ill even tell you how to add a manual function, but i personally wont be releasing such a version without a better reason than i want to do it every 7 days, when the script as it stands can do that AND at a far more predictably safe time and way than you manually likely can or will use it....

BTw, how is actually responding, even taking the time to do so and being jovial about it "level 3 mind games?", whatever the hell that even is?
 
Last edited:

loopypalm

Senior Member
Jul 8, 2016
1,147
1
330
Osaka
i was planing to make a recovery backup of "DATA" after the optimization of DB files ...

if you want better result add a comand to kill the coresponding app, then it would be more stable
or tell the people to use FlushRam or something like that before the proccess start, to make sure the result will be good, or make a 2nd module for advanced users ...

even with the condition you said some system apps stays working in the background and messing with their data can lead to corruption

i don't know you or your previous work and i don't have time to care for that
i just asked a simple question
answer with "NO, my module will not do that"
this will save your time and other people time
 

73sydney

Senior Member
i was planing to make a recovery backup of "DATA" after the optimization of DB files ...

if you want better result add a comand to kill the coresponding app, then it would be more stable
or tell the people to use FlushRam or something like that before the proccess start, to make sure the result will be good, or make a 2nd module for advanced users ...

even with the condition you said some system apps stays working in the background and messing with their data can lead to corruption

i don't know you or your previous work and i don't have time to care for that
i just asked a simple question
answer with "NO, my module will not do that"
this will save your time and other people time


1) whether before or after backup, the files will eventually get optimized - if you want a more manual version use SD Maid or as offfered you can ask me how to alter the code as it sits and make your own version.

2) Im not ever releasing a script that kills anyones apps in the background, i would consider that suboptimal, and basically malware

3) I never said that some "system apps stays working in the background and messing with their data can lead to corruption". What i said was i designed the script to best avoid corruption by running only after boot completed and when avg CPU usage over 5 minutes was less than 30%. The sqlite3 itself generally wont allow access to a file in use. Im just adding what i think is a reasonable level of extra safety by deciding when the optimization routine starts to run, and that it isnt abused/misused by running multiple times in an unreasonable timeframe

4) How does

I refer the gentleman to the name of the module and title of the thread:

"Automatic SQLite DB Optimizer"

the distinct lack of the word "manual" should be a clue


not equal "NO, my module will not do that", only with some attempt at humour and a long explanation (soon followed by a more reasoned explanation) because i didnt want to appear dismissive. Yet it looks like you’d have preferred dismissive??

You seem impossible to please anyway someone tried to do it....

To be honest Im tired of this circular conversation. Everyone else knew from the word automatic in the title how things was going to work. Ive made suggestions of alternatives (SD Maid) and even offered to give you the info to change the script for yourself, and cant do more than that.... please choose one and lets move on, please?
 
Last edited:
  • Like
Reactions: Stillhard

Top Liked Posts

  • There are no posts matching your filters.
  • 4
    Automatically optimizes SQLite databases on boot, on schedule, every X days.

    Just a quick and dirty adaptation of an old SQLite3 optimizer script from init.d days to a Magisk Module using a service.sh script instead, with an internal date offset mechanism to run the script every X days (default: 3 - which is plenty, and specifically to stop silliness).

    Update And credit note: finally after a fair bit of digging, i think i located the original author of the original init.d version of the main vacuum code snippet ive repurposed (stolen) for this module. It appears to be from user @mcbyte_it (doesnt seem to be current or active) and from a post here. Although i only saw derivative reworkings of this script, and used only the main sqllite script function, i arrived at a different method of tracking last run date, when his is arguably more mainstream/concise. Im fairly certain this is the originator especially as the post also has a zipalign script that was also suggested to me to do....and which i have been fiddling with for months....

    The script will wait until boot is completed AND then until avg CPU usage is under 30%, to minimise the risk of possible corruption.

    Disclaimer: As always any use of any 3rd party script/software/advice is at the users discretion. All reasonable efforts have been made to make this as safe as possible, but the responsibility ultimately falls to the user whether to use and run the script.

    What it does:​

    It:
    • Reindexes
    • Vacuums
    • Analyzes
    all .db files under /data.

    It runs a 1st run optimize after install (temp file optimsql_first_run on sdcard is used to enable this, and removed after first run), and then on schedule after that.

    By default it logs just script progress to /storage/emulated/0/autosqlite.log, but you can choose to enable more detail in the log if you wish

    User Configurable Options:​

    The schedule and loglevel can be changed by an external file on sdcard:
    1. Create a file named autosqlite_options on sdcard (/storage/emulated/0/)
    2. Inside create the follow key=value pairs to suit your preference:
    interval=x (where x is the number of days between script runs, for the love of god do not put 1 (this goes out especially to the kind of people who put every app on their Magisk magiskhide/deny list) there is no benefit and you just heighten the possibility of corruption)

    loglevel=x (where x is either 1 (detailed logging) or 0 (basic logging - default))

    Requirements:​

    This module requires a working SQLite3 binary. If your ROM does not provide one (you can check via typing sqlite3 into a terminal), you can choose to use my SQLite3UniversalBinaries module located here:

    https://github.com/stylemessiah/SQLite3UniversalBinaries

    Dont forget you need to download a named SQLite3UniversalBinaries.vx.x.zip file from the Releases page under Assets. Do not try installing the source code with Magisk Manager, it will not go as you expect

    All the modules action takes place in the service.sh file, its commented reasonably well for those wanting to check how it works

    * See... now theres something else to use with my SQLite3 Universal Binaries module other than the GPay SQlite Fix Module <- dont use that anymore, it makes me stabby. It is literally the last resort for getting Google Pay/Wallet to work ,

    Please note: the included LICENSE only covers the module components provided by the excellent work of @Zackptg5 's Magisk Module Extended, which is available for here for module creators

    https://github.com/Zackptg5/MMT-Extended/

    All other work is credited above and no one may fork or re-present this module as their own for the purposes of trying to monetize this module or its content without all parties permission. The module comes specifically without an overall license for this intent.

    Download:


    Repo: https://github.com/stylemessiah/AutomaticSQLiteDBOptimizer

    Release: https://github.com/stylemessiah/AutomaticSQLiteDBOptimizer/releases/latest

    Powered by Ponkle
    1
    @73sydney Nice! If you're looking for newer sqlite3 binaries, I have a build script you can use to build it or you can just grab the precompiled ones at my repo: https://github.com/Zackptg5/Cross-Compiled-Binaries-Android
    1
    i was planing to make a recovery backup of "DATA" after the optimization of DB files ...

    if you want better result add a comand to kill the coresponding app, then it would be more stable
    or tell the people to use FlushRam or something like that before the proccess start, to make sure the result will be good, or make a 2nd module for advanced users ...

    even with the condition you said some system apps stays working in the background and messing with their data can lead to corruption

    i don't know you or your previous work and i don't have time to care for that
    i just asked a simple question
    answer with "NO, my module will not do that"
    this will save your time and other people time


    1) whether before or after backup, the files will eventually get optimized - if you want a more manual version use SD Maid or as offfered you can ask me how to alter the code as it sits and make your own version.

    2) Im not ever releasing a script that kills anyones apps in the background, i would consider that suboptimal, and basically malware

    3) I never said that some "system apps stays working in the background and messing with their data can lead to corruption". What i said was i designed the script to best avoid corruption by running only after boot completed and when avg CPU usage over 5 minutes was less than 30%. The sqlite3 itself generally wont allow access to a file in use. Im just adding what i think is a reasonable level of extra safety by deciding when the optimization routine starts to run, and that it isnt abused/misused by running multiple times in an unreasonable timeframe

    4) How does

    I refer the gentleman to the name of the module and title of the thread:

    "Automatic SQLite DB Optimizer"

    the distinct lack of the word "manual" should be a clue


    not equal "NO, my module will not do that", only with some attempt at humour and a long explanation (soon followed by a more reasoned explanation) because i didnt want to appear dismissive. Yet it looks like you’d have preferred dismissive??

    You seem impossible to please anyway someone tried to do it....

    To be honest Im tired of this circular conversation. Everyone else knew from the word automatic in the title how things was going to work. Ive made suggestions of alternatives (SD Maid) and even offered to give you the info to change the script for yourself, and cant do more than that.... please choose one and lets move on, please?