moneymanagerex / general-reports

Bunch of general reports for Money Manager Ex

http://moneymanagerex.org/

MIT License

76 stars 47 forks source link

Report versioning framework #52

Open slodki opened 5 years ago

slodki commented 5 years ago

Problem description

There are users of different MMEX versions. All of them wants to download new reports for their MMEX installations. We must supply working reports to users. This is complicated with changes introduced in new DB schemas and new MMEX features implemented (like two-letter separate statuses for transfers). SQL queries must be updated to work with newer MMEX versions and executing GRM report against wrong DB version will not work.

Requirement

We must do report versioning and tag them with compatible DB schema version.

Proposal

Store GRM reports inside following JSON structure:

{
  "title": "New report",
  "description": "bla bla bla...",
  "version": 5,
  "database": {
    "min": 13,
    "max": 17
  },
  "sql": "SELECT * FROM ...",
  "lua": "local ...",
  "template": "<html>..."
}

slodki commented 5 years ago

Proposal 2

One report with multiple SQL queries sharing the same template and lua:

{
  "title": "New report",
  "description": "bla bla bla...",
  "version": 5,
  "sql": [
    {
      "database": {
        "min": 7,
        "max": 12
      },
      "query": "SELECT * FROM ACCOUNT_V1 ..."
    },
    {
      "database": {
        "min": 13,
        "max": 17
      },
      "query": "SELECT * FROM ACCOUNT ..."
    }
  ],
  "lua": "local ...",
  "template": "<html>..."
}

version is report logic/layout version, updated when new features/fixes are implemented and user should download new version of report used. database/min and database/max are filters for retrieving report data from different DB schema.

siowena commented 5 years ago

Just a suggestion for the "changes introduced in new DB schemas" - I was taught previously that code should read a table via a View, even if it's 1:1 for the table. That way a table can be changed and a new view created and the existing code/reports will continue to work. In 90% of cases, it doesn't require changes to the previous views but even if it does, most simple changes can be done on the view without everyone having to re-write their code/reports. (e.g. changing values in columns and their meanings, some 'Case' statement can be put in the old views mapping new values to old values, or defaulting deleted columns, etc.). It's not 100%, pretty good at ensuring an upgrade doesn't break everything user custom (e.g. GRM) on day 1.

vomikan commented 5 years ago

We can't (because resources restriction) to maintenance old schemas. main goal is to upgrade all users to new mmex ver >= 1.4.0

siowena commented 5 years ago

We can't (because resources restriction) to maintenance old schemas. main goal is to upgrade all users to new mmex ver >= 1.4.0

Related issue: #56

OK, understood.

siowena commented 5 years ago

We can't (because resources restriction) to maintenance old schemas. main goal is to upgrade all users to new mmex ver >= 1.4.0

Related issue: #56

HI @vomikan, given SQL is one thing I might be able to assist with, if I spot issues with the reports in the master repository (and the download v1.1.1) of reports, should I submit issues & fixes or have things moved on from this main repository? Just looking to help out if I can.

vomikan commented 5 years ago

IMHO this repository is outdated. I'll prefer to recreate this from scratch and collect only bugs free working reports for new DB structure.