Scripts to cleanup null meal size in production database

[lamontfr]

Expected Behaviour

• ~In the production database at SRoulant, the duplicate referents (instances of model Member having identical firstname+lastname and which are pointed to by model Referencing) should be removed and their foreign keys in related instances of model Referencing should be updated.~ (Moved to issue #749)
• In the production database at SRoulant, when the main dish size in the meal defaults has a NULL value, it should be set to "Regular". This is stored in model Client, column meal_default_week which is a json field (see field "size_ddddd" where ddddd is monday ... sunday).

Actual Behaviour

• The SousChef production database contains some data that violates its integrity rules because this data was imported from the previous database "Feast" that was used at SRoulant for many years and which had duplicate records and missing values in some tables.

[lingxiaoyang]

@lamontfr

the duplicate referents (instances of model Member having identical firstname+lastname and which are pointed to by model Referencing)

Do you know why this happened? Is there a problem with the application logic?

[lamontfr]

@lingxiaoyang This happened in late January 2017, during data conversion from the Feast system since the database integrity constraints were probably deactivated. I believe that SousChef's logic is correct. I updated the Issue description to explain the reason.

[lamontfr]

I edited the title and the expected behavior to take into account the new approach of "Related persons" that will be more general than just referents, see issue #740.

[lamontfr]

@kousu @JeanFrancoisFournier I wrote the script below to detect the null and wrong (H, D) main dish sizes, and I copied the file nullmealsize.py then executed it in the Test environment to fix the data. Nick, please ask Jean-Francois when he wants to run the script in Production.

"""
script to fix null or wrong (H, D) main dish sizes in SousChef database

Francois R Lamontagne - 2017-08-24

(copy file nullmealsize.py into sous-chef/src)
(copy file nullmealsize.py into container code/src)
docker cp nullmealsize.py souschef_web_1:code/src/nullmealsize.py

(must run in docker bash shell, from code/src directory)
docker-compose exec web bash
cd src

(to preview changes)
python3 manage.py shell --command="from nullmealsize import go; go('preview')"

(to make changes in database)
python3 manage.py shell --command="from nullmealsize import go; go('save')"

"""
from member.models import Client
from order.models import SIZE_CHOICES_REGULAR, SIZE_CHOICES_LARGE

def go(mode=None):
    if mode not in ['preview', 'save']:
        print("*** Usage : go('preview') or go('save')")
        exit()

    clients = Client.objects.all().order_by('id')
    sizetypes = {}  # to find all existing size values
    for c in clients:
        try:
            defaults = c.meals_default
        except AttributeError:
            pass
        else:
            # find if client always wants the same main dish size
            #   and count existing size types
            preferred_size = None
            for dayinfo in defaults:
                data = dayinfo[1]
                if data['size']:
                    # size not null
                    # add size to count of existing size values
                    sizetypes.setdefault(data['size'], 0)
                    sizetypes[data['size']] += 1
                    if not preferred_size:
                        # first time we see a size, assume it is preferred
                        preferred_size = data['size']
                    elif data['size'] != preferred_size:
                        # size varies, we will use regular when missing
                        preferred_size = SIZE_CHOICES_REGULAR
                elif data['main_dish']:
                    # add size to count of existing size values
                    sizetypes.setdefault('NULL', 0)
                    sizetypes['NULL'] += 1
            if not preferred_size:
                # client had never given a size, will use regular
                preferred_size = SIZE_CHOICES_REGULAR
            elif preferred_size == 'H':
                # Half
                preferred_size = SIZE_CHOICES_REGULAR
            elif preferred_size == 'D':
                # Double
                preferred_size = SIZE_CHOICES_LARGE

            # find size types that we must fix
            daysizes = []  # [(day, old size, new size) ...]
            for dayinfo in defaults:
                day = dayinfo[0]
                data = dayinfo[1]
                if data['size'] == 'H':
                    # Half
                    daysizes.append((day, data['size'], SIZE_CHOICES_REGULAR))
                elif data['size'] == 'D':
                    # Double
                    daysizes.append((day, data['size'], SIZE_CHOICES_LARGE))
                elif not data['size'] and data['main_dish']:
                    # null with quantity
                    daysizes.append((day, data['size'], preferred_size))

            # fix sizes
            if daysizes:
                print("\nFix null or wrong meal size for id =", c.id,
                      "name =", c.member.firstname, c.member.lastname,
                      "Preferred_size=", preferred_size,
                      "(day, old size, new size) =", daysizes)
                # update meal defaults
                for ds in daysizes:
                    c.meal_default_week['size_' + ds[0]] = ds[2]
                if mode == 'save':
                    # save to database
                    c.save()

                # Next line for debugging only
                # print('new defaults', c.meals_default, '\n')

    print("\n*** existing size values", sizetypes,
          "total=", sum(list(sizetypes.values())))
    # Count size types AFTER fixing
    sizetypes = {}  # to find all fixed size values
    for c in clients:
        try:
            for dayinfo in c.meals_default:
                data = dayinfo[1]
                if data['size']:
                    # size not null
                    # add size to count of existing size values
                    sizetypes.setdefault(data['size'], 0)
                    sizetypes[data['size']] += 1
                elif data['main_dish']:
                    # add size to count of existing size values
                    sizetypes.setdefault('NULL', 0)
                    sizetypes['NULL'] += 1
        except:
            pass
    print("\n*** resulting size values", sizetypes,
          "total=", sum(list(sizetypes.values())))

    if mode == 'preview':
        print("\nTHE ABOVE IS A PREVIEW ONLY, CHANGES NOT SAVED\n")
    else:
        print("\nTHE ABOVE CHANGES WERE SAVED TO THE DATABASE\n")

    # List clients with no defaults for manual correction
    for c in clients:
        try:
            defaults = c.meals_default
        except AttributeError:
            print ("*** client has no meal defaults, must correct manually :",
                   "id =", c.id,
                   "name =", c.member.firstname,
                   c.member.lastname)

[kousu]

I've run this in PROD. It seems to have gone off fine there.

[lamontfr]

@kousu Great ! This script was for fixing incorrect values in the database. It should be not be required in the future.