Dietpi: [Wish List] Convert DietPi-Software into SQL database

[Fourdee]

• Create SQL database (I currently lack the knowledge to do this)
• Move all the installation flags/methods/descriptions into a clean global SQL database.
• Rewrite dietpi-software to use an array system and read directly from SQL database.

[xenfomation]

I am also a DBA -- I will assign to me as so I can ask some questions.

[xenfomation]

Updating all tickets I am affiliated with.

I will have a database overview done tonight. We can then take the concept, expand and document as needed.

Key item will be fine tuning of any database and, imho, keeping more tables than fancy associative/heavily indexed and constrained databases to ensure lowest impact on CPU (sorts, joins, finding cardinality, etc)

I do also think this would be great as a time to add the ability to keep an active version database and during updates, changes, use an export for version rollbacks, etc with reference pointers to online repo as opposed to doubling files by accident, etc.

More to come.

[xenfomation]

One item of benefit I see is the ability to:

• remotely host optimized software references, etc
• remotely host Linux software references
• reduce level of effort to add packages to release by making installations as sql text type entries.

[xenfomation]

Attaching a very basic, high-level overview of how a database could be leveraged within DietPi.

The objective

• Keep running version-specific information of Optimized/Linux software for version XXXX of DietPi
• Keep track of scripts, configs, and other information for roll-backs
• Hold system configuration information for comparison/overrides
• Support templates for software installation, removal, and allowing modular additions as DietPi evolves
• Support more logging (if desired, such as Development)
• Support exporting of database as to mimic configuration on new hosts

Considerations

• API/Single Point of Entry will need to be designed to leverage DB contents
• Database of Choice?
• Command line tools to easily export, backup, report, or "clone" data

Ideas of where to start

• Per revision, a database schema should be created for DietPi version XXXX
• Within this schema, basic tables such as host_config, software_optimized, software_linux, scripts, logging, and more should be considered and stubbed in

So, @Fourdee and @rhkean -- hope this finds you both well and any feedback on a database of choice would be great! The lighter, the better and considering the choice, even if file-based.

Cheers!

[rhkean]

I think that a database backend is a fabulous idea.

If we go RDBMS, I think SQLite is the smallest choice that we already have support for. It'll add about 1.5MB to the base image.
if we go noSQL, we already have REDIS support... that'll add 1.1MB

I don't have any noSQL experience, but I do have RDBMS experience (mysql, postgresql, sqlite, oracle, ms sql, etc)

but, it'll be a major rewrite....

[Fourdee]

@xenfomation
Sounds good. I'd personally like to start with dietpi-software and a simple SQL DB so we can test it.

Heres my thinking:

DietPi SQL DB
          |
          DietPi-Software
                       |
                       SOFTWARE (for each peice of software in DietPi-Software):
                       - Software Index (eg: unique ID): int [default, automatic index++)
                       - Software Name (eg: OpenBazaar): string
                       - Software Description (eg: Open Peer to Peer market): string
                       - Software Category (eg: 0=Media Systems): short int [default 0]
                       - Software menu location (eg: DietPi, or Linux: short int [default 0]
                       - Available for HW_MODEL (0-100): bool [default true]
                       - Available for DISTRO_Wheezy: bool [default true]
                       - Available for DISTRO_Jessie: bool [default true]
                       - Available for HW_ARCH_ARM6: bool [default true]
                       - Available for HW_ARCH_ARM7: bool [default true]
                       - Available for HW_ARCH_ARM64: bool [default true]
                       - Available for HW_ARCH_x86_64: bool [default true]
                       - Installed state (eg: 0=no,1=tobe,2=installed): short int [default 0]
                      Choice System (To store the users choice selection)
                       - Webserver Preference: short int [default 0]
                       - File Server Choice : short int [default 0]
                       - SSH Server Choice : short int [default 0]

Once thats available, we can add a few entries to the SQL DB. Then code DietPi-Software to use the unique ID for each peice of software. And see how it would be coded, most likley arrays eg:

if (( $SQL_SOFTWARE_INDEX == 0 )); then
Kodi

if (( $SQL_SOFTWARE_INDEX == 1 )); then
HiFi

    if (( ${SQL_INSTALLED[1]} == 1 )); then
    install hifi
    SQL_INSTALLED[1]=2

If the SQL DB and changes are benefical to our devolpment, we can always add more entries (As per JK's suggestions of dietpi-config settings etc) at a later date. But 1st, we need a simple SQL db that we can add entries and test from, and I believe DietPi-Software is the best place to start.

@rhkean

but, it'll be a major rewrite....

Yep lol. But I think the dietpi-software DB would be worth it. Everything would be arrayed and simpler to work with, no more need for Pine64 software list, Odroid c2 software list etc. Cleaner code.

[xenfomation]

Thanks for the feedback, gents -- this is the stuff I wanted to flesh out!

I to prefer a RDBMS as noSQL is kinda... table style. I am already sorting out SQLite right now based on your suggestions, Dan.

Since Rob is cool with SQL/RDBMS' this should make for a good checks-n-balances situation... especially if we ever host remote repos, etc (a boy can dream, but he has to grow up)...

[xenfomation]

I have a simple start to a sqlite schema. Should I post it here or is there reason to consider a third/prototype branch? So much to do and glad to do it!

[Fourdee]

@xenfomation

I have a simple start to a sqlite schema.

Excellent :+1:

should I post it here or is there reason to consider a third/prototype branch?

I think your right, probably best to create a new fork/branch for this so we can dig in and play :+1: .
I'd recommend copying the testing branch (https://github.com/Fourdee/DietPi/tree/testing), stable at the moment and upto date.

[xenfomation]

Will do in 30 minutes and setup a playground for you & @rhkean to dive into for hands on sql over exposure. :)

[rhkean]

nice!
yeah, I'd definitely create a new branch

[xenfomation]

Thanks gents -- shot you an email as I am working on my backlog right now:

361 - cli grouping of software?

357 - DietPi-Software | GNUStep Desktop Environment #357

347 - No Machine & stock configs

353 - haproxy fails to start #353

325 - DietPi Idea | Sourceable script with pre-defined color codes #325

309 - xenserver docs

247 - DietPi-Software | Add Friendica: A Personal Social Networking Platform #247

246 - NFS

245 - Full Disk Encryption #245

271 - General | Add support for 2+ USB drives #271

195 - DietPi-Software | Add QuiteRSS installation option #195

191 - DietPi-Software | Add xrdp installation option #191

151 - DietPi-Software | Add MATE desktop installation option #151

41 - [Wish List] Convert DietPi-Software into SQL database #41

Whew -- done, but about to check in! ;-)

[xenfomation]

So, just to foster the discussion -- I've been breaking out more scripts into database objects (SQLite, albeit MSSQL/MariaDB compatible for the most part).

The Goal is DietPi-Software

To break out dietpi-software, we need a primary database, such as: DIETPI_DB_VXXX.
Therein, we need a series of tables to contain a DIRECT "DIETPI_SOFTWARE" table, but also associative tables to reduce overhead on UNIQUE/PRIMARY keys.

One consideration - from a programming point of view - are variable names, especially for UNIQUE IDentifiers or PRIMARY KEYs. Where possible, I avoid PRIMARY KEYS for UNIQUE IDs on tables that can be equated to "drop-down options", such as HW_MODEL, OS_DISTRO, etc.

The Database Prototyping I Have

The basic database should have a build-related name with a DIETPI_DB_INFO table for basic build information, resources (build notes on the distro or online, URLs to GIT, etc), version superseded, build date, etc:

CREATE TABLE `DIETPI_DB_INFO` (
    `DIETPI_VERSION`    INTEGER NOT NULL DEFAULT 0 UNIQUE,
    `DIETPI_MD` TEXT NOT NULL DEFAULT 'https://github.com/Fourdee/DietPi/blob/master/README.md',
    `BUILD_DATE`    TEXT NOT NULL DEFAULT 'DD-MM-YY HH:MM:SS',
    `BUILD_AUTHOR`  TEXT NOT NULL DEFAULT '@Fourdee',
    `BUILD_CONTACT` INTEGER NOT NULL DEFAULT '@Fourdee',
    `GIT_URL`   TEXT NOT NULL DEFAULT 'https://github.com/Fourdee/DietPi',
    `BUILD_INFO_LOCAL`  TEXT NOT NULL DEFAULT '/DietPi/build.md',
    `BUILD_INFO_URL`    TEXT NOT NULL DEFAULT 'https://github.com/Fourdee/DietPi/blob/master/CHANGELOG.txt'
);

Second, we layout the OS_DISTRO table. This ties into dietpi-software and will help ensure that only SOFTWAREXXX is listed for the OS_DISTRO, HW_MODEL, and HW_ARCH (the latter two which are referenced as their own tables):

CREATE TABLE `OS_DISTRO` (
    `ID`    INTEGER NOT NULL DEFAULT 0 UNIQUE,
    `DISTRO`    INTEGER NOT NULL DEFAULT 'Unknown',
    `DISTRO_INFO`   TEXT NOT NULL DEFAULT 'Unknown',
    `ROOT_FS_PATH`  TEXT NOT NULL DEFAULT '/dev/mmc01',
    `UUID`  TEXT NOT NULL
);

Third, we add the obtain-hardware-model script into "HW_MODEL". This defines the hardware that the build is for as well as SOFTWARE options. This table also should be associated with OS_DISTRO and HW_ARCH.

CREATE TABLE "HW_MODEL" (
    `ID`    INTEGER NOT NULL UNIQUE,
    `DISTRO_ID` INTEGER NOT NULL DEFAULT 0,
    `HW_ARCH_ID`    INTEGER NOT NULL DEFAULT 0,
    `HW_MODEL_DESCRIPTION`  TEXT NOT NULL DEFAULT 'Unknown',
    `UUID`  TEXT NOT NULL
);

Fourth, we will need the HW_ARCH table. This gives context to the architecture for SOFTWARE, such as NoMachine (and others), that offer ready-to-go-packages for different architectures:

CREATE TABLE `HW_ARCH` (
    `ID`    INTEGER NOT NULL UNIQUE,
    `HW_ARCH`   TEXT NOT NULL DEFAULT 'Unknown',
    `HW_ARCH_DESCRIPTION`   TEXT NOT NULL DEFAULT 'Unknown'
);

Fifth, we will need a MENU_ID table to breakup the current install menu (to be done) so software can be presented under a specific MENU/CONTEXT.

Sixth, the software table:

CREATE TABLE `DIETPI_SOFTWARE` (
    `ID`    INTEGER NOT NULL UNIQUE,
    `MENU_ID`   INTEGER NOT NULL DEFAULT 0 UNIQUE,
    `SOFTWARE_NAME` TEXT NOT NULL DEFAULT 'Unknown',
    `SOFTWARE_VERSION`  TEXT NOT NULL DEFAULT 0.0,
    `SOFTWARE_AUTHOR`   TEXT NOT NULL DEFAULT 'Unkown',
    `SOFTWARE_RESOURCE` TEXT NOT NULL DEFAULT 'URL',
    `SOFTWARE_DESCRIPTION`  TEXT NOT NULL DEFAULT 'Software',
    `USER_SELECTED` INTEGER NOT NULL DEFAULT 0 UNIQUE,
    `SCRIPTS_INSTALL`   INTEGER NOT NULL DEFAULT '/path/to/install',
    `SCRIPTS_CONFIG`    INTEGER NOT NULL DEFAULT '/path/to/conf',
    `MANDATORY_INSTALL` INTEGER NOT NULL DEFAULT 0 UNIQUE,
    `BUILD_MINIMUM` INTEGER NOT NULL DEFAULT 115,
    `BUILD_MAXIMUM` INTEGER NOT NULL DEFAULT 999,
    `HW_MODEL_ID`   INTEGER NOT NULL DEFAULT 0,
    `HW_ARCH_ID`    INTEGER NOT NULL DEFAULT 0
);

For now, I've used UNIQUE identifiers to prevent too much overlap. I imagine for software we will want primary keys in the long run, but I am thinking of the goal: how best to sort, export, and merge tables with rolling builds. Primary Keys = not fun for those experiences!

[Fourdee]

As of v128, DietPi-Software has been re-written to use a self contained array/DB system : https://github.com/Fourdee/DietPi/issues/453.
SQL DB is no longer required.