Better handling of DB in bad state (X is unexpectedly already used in recipe Y)

[matty0ung]

Transferred from https://github.com/Brewtarget/brewtarget/issues/656. Following reported by @dsheets4. My thoughts in next comment.

Perhaps only for posterity, as I'm not sure what else to do with this. There was one interesting behavior, which I expect would be a different ticket anyway but also could just be specific to my database. I did what amounts to copy/pasting steps from one recipe to another to test. That is, add an instruction to one recipe, click to a different recipe, open that recipe's Brewday tab, copy text from an instruction there, then return to the original recipe's Brewday tab, select the earlier created instruction, paste that text into that instruction, then click back into the instruction list, the display then flips to Recipe tab. I'm not sure all those steps are necessary... When it flips to the Recipe tab there are WARNING messages printed to the log that seem to indicate perhaps something is off with my database (see below). I did the same steps with a fresh BrewTarget database using the Bt: recipes and that works just fine. I'm not sure what the warning means. For example, fermentable 858 only appears in the fermentable_in_recipe table once for recipe 104. Recipe 104 is the one to which I was adding instructions. I also seem to have 6 rows in the database for that recipe, all but 104 flagged as locked. This might be an artifact of moving the recipe between folders.

[00:29:58.271] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Fermentable] Fermentable # 858 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.272] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Fermentable] Fermentable # 859 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.273] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Fermentable] Fermentable # 860 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.273] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Fermentable] Fermentable # 861 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.273] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Fermentable] Fermentable # 863 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.274] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Hop] Hop # 350 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.275] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 402 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.275] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 403 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.276] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 411 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.276] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 412 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.276] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 424 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.277] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 429 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.277] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 435 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.278] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Misc] Misc # 446 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.278] (1dvxtqb7ls) WARNING : bool {anonymous}::isUnusedInstanceOfUseOf(NE&) [with NE = Yeast] Yeast # 276 is unexpectedly already used in recipe # 104  [model/Recipe.cpp:94]
[00:29:58.282] (1dvxtqb7ls) WARNING : QObject::connect: No such signal BrewNote::brewDateChanged(QDateTime) in /home/user/brewtarget.project/brewtarget/src/BtTreeModel.cpp:1411  [:0]
[00:29:58.285] (1dvxtqb7ls) WARNING : QObject::connect: No such signal BrewNote::brewDateChanged(QDateTime) in /home/user/brewtarget.project/brewtarget/src/BtTreeModel.cpp:1411  [:0]

Originally posted by @dsheets4 in https://github.com/Brewtarget/brewtarget/issues/656#issuecomment-1219041392

[matty0ung]

Thanks for the additional info. You're right that it's a separate problem. Your DB shouldn't get in this state. There are two things I'd like to investigate:

• It shouldn't be possible to get into this state and it might lead to unwanted behaviour. Essentially each use of a Fermentable/Hop/Misc/etc in a Recipe should create a copy of that Fermentable/Hop/Misc/etc. (It's a bit of an odd way of doing things in a relational DB but AIUI it reflects the history of the code which originally used XML files rather than a DB for its primary storage.) So, a row in the, say, hop table can mean either "a hop" or "a use of a hop in a recipe". In the latter case, there's also an entry in the hop_in_recipe table plus a link back to the original hop via the hop_children table. If you have one hop row referenced by two separate recipes then you may get odd things happening if/when you change the amount of hop or delete it from one of the recipes.
• If it's a bug in the current code that has got us into this state then we want to find and fix it. Even if it's not, it would be nice, at start-up, to repair the DB if we can detect it is in a bad state.

This probably isn't a super-quick fix unless we can work out a set of actions that reproduce the state on a clean DB.