Brainstorm DB structure, minimize data usage and disk I/O (requires #145)

[spirillen]

Moved this from #145#discussioncomment-10726319, touching #57

---

Brainstorm 🧠 💨 : An entirely different approach to all the above is to more or less rebuild the entire DB (csv,mariadb,mysql,postgresql) structure is to thinks of any local DB as a copy of the platform's db by default (the opt-in above).

Most of this is kind a mentioned above.

For OPT-in only

This means, by any test start we download and update the locate DB with any updates from platform and at the end of the test (small test files) and for every n records push the updated results to the platform. This makes the local DB a cached version, and help finding records that do not need external resources for testing (whois, dns etc.) as others already have found this within the grace period -dbr

Brainstorm (DB) 🧠 💨 : IF you skip the three table.columns pyfunceble.pyfunceble_continue.destination, pyfunceble.pyfunceble_continue.source & pyfunceble.pyfunceble_inactive.destination, you can actually merge the tables into one, again, minimizing the table size in total = less disk I/O = faster search, smaller tmp_table_sizes = faster table rebuilds on changes(update = file copy, delete => rename table file)

Now as mentioned above, make the new table for status and source(destination), but why? I might understand why to keep a pyfunceble.destination to extract and keep status of this data in blacklists search tools like:

• https://github.com/external-sources/hosts-sources
• https://github.com/external-sources/domain-hub (Todo)
• https://hosts.ubuntu101.co.za/app/contents/

But as mentioned reverse this to be a independent table, which is only updated on the test start, as they are 100% unrelated to the test and the results. This is by default not a shared data, only an optional and mainly interesting data for big testers and data source search tools/data extraction.****

[spirillen]

After writing this, I come to think of pyfunceble.pyfunceble_continue.tested_at & pyfunceble.pyfunceble_inactive.tested_at their are no reason to keep the time here, the data should be sufficient as the --dbr are only taking days as value. this again allow us to optimize for db_table file size as this should be a shared value in another separate table, the exsact same goes for the pyfunceble.pyfunceble_inactive.created_at and pyfunceble.pyfunceble_inactive.modified_at and reverse linking, (time to records)

Now we should also shrink the pyfunceble.pyfunceble_inactive.destination by adding a new table here and the reverse the linking (destination to records_id)

This would make the data stored less than the half of the way you store data per records + benefit from the data cache for non CSV data storage.

[spirillen]

Allow me to ping @Nilsonfsilva for thought and comments to this, as I'm unclear about how you thinking of integrating @PyFunceble to the lovely debian universe. You might have some thought of idea's to the defaults for the package?