New Database Layer

[matty0ung]

This is the new database layer, which separates out:

• mapping objects to/from database storage
• dealing with the specifics of an individual database (credentials, backups, different ways of doing things in SQLite/PostgreSQL, etc)

It also implements export to BeerXML using the same new data structures etc we created to improve reading from BeerXML.

A few other things have come along for the ride because they were minor tidy-ups I'd implemented in Brewken and it would have been more effort to exclude them than to include them.

All database stuff is now in the database subfolder of src. Key files:

• Database.cpp, Database.h handle abstraction of SQLite / PostgreSQL (as before) but no longer do any object mapping
• DatabaseSchemaHelper.cpp, DatabaseSchemaHelper.h handle schema upgrades and migrations, plus default data population (as before). I've disentangled this from the old Schema files that are no longer used. In particular schema changes are now hard-coded. (If we've got the SQL that, say, upgrades from v7 to v8 of the DB, we don't want this to be tied to "current" mapping as you still want to be able to do that exact same v7 to v8 step (and v8 to v9 etc etc) at any later date, including when the "current" schema has evolved and changed from whatever v7 was.) I have also moved "default data" to BeerXML and stopped using the bt tables. (Now that we decided not to try to edit the user's existing data, we only needed the bt tables for duplicate detection - ie not to import the default Recipe/Style/Hop/etc objects that are already in the user's database. But we have such logic already in the BeerXML import code. So we can just reuse that and it will automatically pull only "new" objects out of data/DefaultData.xml. Of course, once we support BeerJSON, we'll switch to that.)
• ObjectStore.cpp, ObjectStore.h Does all the SQL to create/read/update/delete objects in the database. Normally you won't access this directly, but instead via templated functions in the ObjectStoreWrapper namespace
• ObjectStoreTyped.h, ObjectStoreWrapper.h add templated wrappers around ObjectStore to save callers doing lots of tedious casting
• ObjectStoreTyped.cpp contains all the data required to map objects to and from database tables
• DbTransaction.cpp, DbTransaction.h just provide an RAII wrapper around DB transactions

A fair bit of class-specific logic that used to be inside the Database class is now inside the individual subclasses of NamedEntity (in the src/model directory). Eg, more of the Recipe versioning logic now lives inside the Recipe class. I've tidied up a few things with helper functions so that we have less copy-and-paste code on setter member functions.

Generally, you'll see that:

• Dealing with storing and retrieving objects is now much more C++ than SQL. Eg, if you want "all Recipes that match such-and-such condition" then that condition is going to be a lambda that you pass to ObjectStoreWrapper::findAllMatching<Recipe>() rather than a WHERE clause that you tack onto a SQL statement in a new Database member function. Similarly, default data for a newly-created object is always in that object's constructor and not in the database definition.
• The mappings from objects to database tables are more concise and use fewer global constants.
• We have better encapsulation as fewer classes need to be friends with each other, and classes in the src/model directory do not need to have any knowledge of SQL or XML.
• Inside the object model, we do more lazy-loading (eg a Recipe object holds the IDs of the Hops it uses and then turns those IDs into Hop objects if and when needed), but this should be largely invisible to the UI code.
• It should now be less work in future to add support for another SQL database (eg MariaDB) or even a NoSQL (eg CouchDB or Cassandra).

Additionally, I have:

• Tidied up logging a bit, including now logging a thread ID (in case we do any multi-threaded stuff in future) and ensuring that errors about logging itself are always logged to the console even if we have disabled other console logging
• Enhanced the GitHub actions for the Mac build so that you can see a stack trace if the automated tests crash. (This is what happens when you don't own a Mac!)
• Separated out persistent settings into a namespace called PersistentSettings
• Created a new class BtStringConst for wrapping compile-time-constant strings. (See class comment in utils/BtStringConst.h for part of the motivation for this but, amongst other things, this made the Windows and Mac builds happy.)
• Combined the above two steps to use proper constants for all access of persistent settings (which along the way uncovered a few minor bugs where, eg, we were using one key to store a value and another one to attempt to read it)

This all turned out to be a bit more work than I imagined back in January, but I'm happy with the result. There's scope to refine things a bit further (eg I think some or all of ObjectStoreTyped could be merged into ObjectStoreWrapper) but I don't see the need to do another mega-change like this one.

As ever, questions and comments most welcome.

[matty0ung]

PS: See https://github.com/Brewtarget/brewtarget/pull/617/commits/94cfd7dbf470ee79400112f199c3962044f3ea5b for a motivation for storing default data in a non-binary format. :)

[mikfire]

Are the boost backtrace libraries really required? cmake is really not happy with them, on either ubuntu 20.04.2 or gentoo.

I am not even certain I can get them installed on gentoo, without extraordinary effort. Unless these libraries provide value greater than that effort, I would prefer not to invoke them.

[matty0ung]

Hmm, that's odd. Ubuntu 20.04.2 is what I run and I don't remember having to do anything special to get it to work. (The Mac build was a bit of another story.) But it's small work to disable it if you want. The backtrace is just there for diagnostics. I'll do a patch.

[mikfire]

Thanks. I could get it working in an Ubuntu VM, but not on my gentoo host systems. It is a little embarrassing, actually. I am not used to being able to get something working on Ubuntu but not gentoo.

I am hesitant to approve this, to be honest. I like the approach. Database.cpp is blessedly short and does what I have wanted it to do for a long time.

There's a lot that needs to be fixed, and it is exactly what I expect when we go playing with the database layers. I am also concerned some of what I am seeing is not repeatable. I had one problem that just mysteriously went away, and I have no idea why.

A simple complexity issue. There four entries when trying to select the database type on the options screen. I see two entries for SQLite and two for Postgresql. That is likely something isolated to the options screen and should be a simple fix.

A medium complexity issue. Change the style on any recipe and then go look at the Style tree. If you are seeing what I see, there are now two of that style listed. If you restart brewtarget, the duplicate goes away. This is likely to be an order of operations problem -- the tree is signaled to update before the "visible" flag is set to false. I am not fond of chasing these problems.

Two large complexity issues. postgresql does not seem to be working. Automatic recipe snapshots are dumping core.

So do we merge into develop, knowing we are breaking things? We can at least open proper tickets, and people can help resolve them. Are there other options?

[matty0ung]

Thanks @mikfire. Let me see if I can fix the Options screen thing. Should be simple, as you say. I'll then see if I can work out the signals order thing on styles.

For the snapshot core dump, do you have a stack trace? (I wanted to talk about automatic snapshots, as after I'd jumped through a few hoops to get it to detect any change to any element of a recipe, I started wondering whether it was really helpful to have a new version of the recipe every time every single field was changed. I wondered if it would be more helpful to say "After a Recipe has been brewed, the next change creates a new version. No further new versions (of the recipe) are created automatically until after its next brew day." What do you think?)

Similarly, for PostgreSQL, are you seeing any useful diagnostics?

[mikfire]

It isn't just on styles. So far, my testing indicates it effects everything. However, odds are good that fixing for one will fix it for all. I think it has to be a signal issue, since restarting brewtarget removes the duplicate entry. The tree is either being signaled before display is set false, or a filter isn't being used that should be.

I am testing on a different VM now, and things are inconsistent.

The automatic snapshot created 30 snapshots in response to me adding one ingredient. My guess for that would be a snapshot was taken for every thing that got copied from the parent to the child. Previously, it just dumped core. They could be the same issue, and I just got lucky I didn't run off the stack this time.

And postgresql is not working. The upgrade fails with a

[15:20:29.865] (1did888j0g) ERROR : bool {anonymous}::executeSqlQueries(QSqlQuery&, const QVector<{anonymous}::QueryAndParameters>&) Error executing database upgrade/set-up query  "ALTER TABLE recipe ADD COLUMN ancestor_id INTEGER REFERENCES recipe(id)" :  "ERROR:  syntax error at end of input\nLINE 1: EXECUTE \n                ^\n(42601) QPSQL: Unable to create query"  [database/DatabaseSchemaHelper.cpp:92]

I am thinking that may be the result of how a QString is being built. I can keep poking at that, as I am the only person who cares about using postgresql.

[mikfire]

If I manually do the upgrade from version 9 -> 10 (add two columns, set some default values and then update the db version to 10), the postgresql access works.

There is definitely something off in the upgrade. I might have some older databases laying about. Let me see if this is just a 9 -> 10 problem, or if it is a general upgrade problem.

To answer your other question. Yes, that was the way I had designed the automatic snapshots. Once a recipe was brewed, it was "soft locked" and the first change would spawn a child. Any subsequent change would not spawn a child until it was brewed again.

There may have been a difference between what I designed and what I delivered.

[matty0ung]

Ah, OK, good. Couple of small fixes above. The versioning thing is my misunderstanding of the functionality. Should be able to get it closer to what you had. (Pulling this logic out of the Database layer involved quite a lot of change, though it also made me clean up all the setters, so it had good side-effects!) There will doubtless be still one or two bugs, but hopefully then more debuggable.

The upgrade stuff will be easy to fix, if that's the only place you're getting errors. I was doing the PostgreSQL-specific stuff without having an instance of PostgreSQL to test on, so I'm hoping it's mostly just typos etc on my part. (Differences in SQL between SQLite and PostgreSQL are mostly OK, just a bit tedious when they need things in a different order.)

[matty0ung]

With the upgrade error, it's probably staring me in the face, but can you say why PostgreSQL is giving SYNTAX ERROR for

ALTER TABLE recipe
ADD COLUMN ancestor_id INTEGER
REFERENCES recipe(id)

The answer from Evan Carroll at https://dba.stackexchange.com/questions/132029/how-to-add-a-column-with-a-foreign-key-constraint-to-a-table-that-already-exists suggested this was an OK way to add a new column with FK constraint to PostgreSQL.

[mikfire]

I found something that claims to be a version 7 database, so I am using that. It is my standard mess of a database, but I think I can make it look more normal.

The first issue is in DatabaseSchemaHelper when creating any auto-incrementing primary key. In SQLite, it has to be "id INTEGER PRIMARY KEY". In postgresql, it has to be "id SERIAL PRIMARY KEY". Including the "INTEGER" is causing postgresql all sorts of heartburn. My preference would to rename Database::getDbNativeIntPrimaryKeyModifier() to be something like "getDbNativePrimaryKeyDeclaration()" return the entire string instead of building it in place.

The second issue I am seeing is that postgresql uses "true/false" for boolean values. SQLite uses "1/0". Trying to use "1/0" in postgresql will cause an error. I see 1 and 0 hard coded in several places in DatabaseSchemaHelper. The reason Database::dbBoolean exists is to handle this problem, and I still find Database::dbTrue() and Database::dbFalse() to be useful shortcuts. I don't know if that problem is more wide spread or not.

I think the problem you are looking at isn't a syntax error in the SQL. As far as I can determine, postgresql does not like it when you prepare a statement that has no bind values. I am uncertain if it doesn't like the fact nothing was bound (ie, sql.prepare, no sql.bind, sql.exec) or it doesn't like to prepare a statement with no ? in it. Regardless, it seems to think this is a syntax error. The fix I used was to modify DatabaseSchemaHelper::executeSqlQueries() to prepare, bind and exec only if there are values. Otherwise, it just execs the string. That seems to resolve this issue.

There are several places in DatabaseSchemaHelper::migrate_to_8 where it says "ALTER TABLE [thingy] ADD COLUMN inventory_id REFERENCES" which needs to have an "INTEGER" between the id and REFERENCES.

I am a little concerned in Database::getDbNativeIntPrimaryKeyModifier (and it's friends) take a DbType parameter, but don't use it. I haven't gotten this deep yet, but that has a real potential to cause problems when copying one database to another. You may be handling it different, but it did catch my eye.

This has gotten long. I still haven't successfully upgraded from 7 -> 8 yet, but I will stop on this thread.

[mikfire]

The migrate_to_8 is painful. I question how I even figured it all out.

migrate_to_8 is failing under the new code with this error:

[11:33:12.773] (1dobw33pmo) ERROR : bool {anonymous}::executeSqlQueries(QSqlQuery&, const QVector<{anonymous}::QueryAndParameters>&) Error executing database upgrade/set-up query  "
INSERT INTO fermentable_in_inventory (fermentable_id) VALUES ( 
   SELECT id FROM fermentable WHERE NOT EXISTS ( 
      SELECT fermentable_children.id FROM fermentable_children 
        WHERE fermentable_children.child_id = fermentable.id ) 
   AND NOT EXISTS ( 
      SELECT fermentable_in_inventory.id FROM fermentable_in_inventory 
        WHERE fermentable_in_inventory.fermentable_id = fermentable.id) 
   )" :  
"ERROR:  syntax error at or near \"SELECT\"\nLINE 1: ...ermentable_in_inventory (fermentable_id) VALUES ( SELECT id ...\n                                                             ^\n(42601) QPSQL: Unable to create query"  [database/DatabaseSchemaHelper.cpp:98]

I believe the problem here is that I don't track any inventory. I don't actually use the inventory subsystem at all. So the inner query returns empty, and trying to INSERT INTO fermentable_in_inventory (fermentable_id) VALUES () throws an error. The while loop in the original code got around this problem by not doing anything if nothing returned from the select. I have no suggestions on how to fix this one.

I am also somewhat blocked on more testing until I can figure out how to get around this.

Side note. Would it be possible to modify DatabaseSchemaHelper::executeSqlQueries to break out of the for-loop as soon as an error has occured? Once there is an error, the entire commit is rolled back so doing more work after the error isn't really useful. It also has the side effect of filling my screen with error messages. Aborting the loop makes it much easier to find the first problem.

[matty0ung]

Hmm, good question. I can't find a way to do it in SQL, but I might have an idea about how to handle this...

[matty0ung]

Going back to your prior points:

Thanks for info on the primary key in PostgreSQL. Should be an easy fix.

On booleans, AIUI, it should be possible to just use true/false everywhere in SQL and the drivers will figure out to translate that into 0/1 for SQLite. So, if we've got hard-coded 0s and 1s, I just need to change them.

On the prepare stuff, I would never have guessed about that limitation from the Qt documentation. Thanks for working it out!

On the getDbNativeIntPrimaryKeyModifier() DbType parameter, you're right, it shouldn't be there. My approach to copying from one type of DB to another was to have a separate instance of Database for the from and the to databases (see DatabaseSchemaHelper::copyDatabase()).

[mikfire]

Matt, I really hope I am not annoying you or otherwise antagonizing you. I am simply running through my standard tests and trying to doc what happened. If I am getting on your nerves or if you need a break, just let me know and I will hold off on more.

I am now trying to copy SQLite->postgresql. It isn't quite as gruesome. We already know most of the fixes. A few small changes and this seems functional. I will, of course, try it the other way. But usually getting it working in one direction almost always works in the other.

Before the usual laundry list of issues, why are the database tables being created without default values? I am not saying it is a good or bad thing. I just want to understand the reasoning.

Also, why is copyDatabase() in DatabaseSchemaHelper? Again, I am not arguing that it shouldn't be. My first thought is that it goes in Database.

The same "INTEGER" problem with the primary keys was found. I think that can be fixed by fixing ObjectStore::createTableWithoutForeignKeys to not use getDatabaseNativeTypeName when firstFieldOutput is false.

ObjectStore::createTableWithoutForeignKeys also has the problem of preparing with no binds.

ERROR: parameter $7 of type integer cannot be coerced to the expected type boolean. The boolean representation in the two databases is a massive pain. I don't claim to know what the QVariant is doing here, but I can promise it is not doing what we need. That is why Database::dbBoolean() exists -- there is no way to get it done. DatabaseSchemaHelper::copyDatabase() probably needs something like this

if ( field.type() == QVariant::Bool ) {
    bindValue = newDatabase.dbBoolean(bindValue.toBool());
}

As a rule, I think in the error case we need to first tell the user something went wrong. We also probably should consider not changing the dbType in the config file, but I think that has always worked that way?

[mikfire]

On the getDbNativeIntPrimaryKeyModifier() DbType parameter, you're right, it shouldn't be there. My approach to copying from one type of DB to another was to have a separate instance of Database for the from and the to databases (see DatabaseSchemaHelper::copyDatabase()).

You know, I saw that once I was digging into that part of the code. Perfectly acceptable solution.

Could we leverage that to just switch to the new database once it's copied, instead of asking the user to restart?

[matty0ung]

Matt, I really hope I am not annoying you or otherwise antagonizing you. I am simply running through my standard tests and trying to doc what happened. If I am getting on your nerves or if you need a break, just let me know and I will hold off on more.

No, it's all good stuff if we're fixing bugs and making the code clearer. :+1: TBH on a change this size it would be surprising if we weren't finding issues. Keep throwing things my way and I'll work through it all.

[matty0ung]

why are the database tables being created without default values?

It's a fair question. My thinking was that we will never use DB default values because defaults are set in the object constructors of Recipe/Hop/Fermentable/etc.

[matty0ung]

Why is copyDatabase() in DatabaseSchemaHelper [rather than Database]?

My thinking here was that Database should not have any knowledge of the schema or the object stores. I did toy with the idea of doing copyDatabase in some schema-agnostic way (ask the database for a list of all its tables, and for each table ask for a list of all its columns etc) but I decided it would be simpler and safer to use ObjectStore (because eg we don't want to pick up special tables the DB uses for sequences).

That said, I think you're right that copyDatabase() isn't sitting in the best place right now. I'll move it into the ObjectStore code.

Edit: Looking more closely, I think some of what's in copyDatabase() should stay in DatabaseSchemaHelper (eg because we don't mention the settings table anywhere else) and some should move to ObjectStore (eg so we don't have to expose ObjectStore::getAllTableNames()).

[matty0ung]

ObjectStore::createTableWithoutForeignKeys also has the problem of preparing with no binds

This was annoying, so I created new class BtSqlQuery (to replace QSqlQuery) that should behave the same on PostgreSQL and SQLite (ie won't mind you preparing a statement that doesn't have any bind placeholders).

[matty0ung]

The same "INTEGER" problem with the primary keys was found. I think that can be fixed by fixing ObjectStore::createTableWithoutForeignKeys to not use getDatabaseNativeTypeName when firstFieldOutput is false.

This should now be fixed (I hope!)

[matty0ung]

ERROR: parameter $7 of type integer cannot be coerced to the expected type boolean. The boolean representation in the two databases is a massive pain. I don't claim to know what the QVariant is doing here, but I can promise it is not doing what we need.

Hmm, yes, I see what you mean. That's annoying because QVariant knows how to coerce an int to a bool (per doco for QVariant::toBool()) but I guess it must just be passing the raw int to PostgreSQL and it's the DB that is complaining about it not being a bool.

I think I may be able to take another approach here. We don't actually necessarily need to read from the old database as we already have all the data cached in memory in the ObjectStore instances. Let me see if I can get it to write everything out to new DB without having to mess the code around too much...

[matty0ung]

Could we leverage that to just switch to the new database once it's copied, instead of asking the user to restart?

Yes, I think we could. It's probably not a huge code change, but would need some careful testing to make sure none of the code was still trying to do something in the old DB after the switchover. Would you be OK for us to do that as a separate enhancement ticket?

As a rule, I think in the error case we need to first tell the user something went wrong. We also probably should consider not changing the dbType in the config file, but I think that has always worked that way?

Agreed. Are you OK to bundle up in the same ticket as above? (I'm happy to do the work, I'm also keen to draw this particular ticket to a conclusion if we can get it to a point where it's not taking any steps backwards! :smile: )

[matty0ung]

OK, I think I've now, one way or another, addressed all the points in the comments. Please let me know if I've missed anything or if you find any other issues.

[mikfire]

I am going to try something new and different and only do one of these.

ObjectStore.h

This may be my system, but I keep having to add a

#include <optional>

in ObjectStore.h

Copying from SQLite to Postgresql

repopulateChildrenOnNextStart

[10:41:17.030] (1dnxxf1v9c) ERROR : bool {anonymous}::executeSqlQueries(BtSqlQuery&, const QVector<{anonymous}::QueryAndParameters>&)
   Error executing database upgrade/set-up query
      "INSERT INTO settings (repopulatechildrenonnextstart, version) VALUES (?, ?)" :
   "ERROR:  parameter $1 of type boolean cannot be coerced to the expected type integer"

Did you know that, for reasons that escape me, the repopulateChildrenOnNextStart column was implemented as an integer and not a boolean?

suggested fix

Probably can just change

{QString("INSERT INTO settings (repopulatechildrenonnextstart, version) VALUES (?, ?)"), {QVariant(true), QVariant(dbVersion)}}

to

{QString("INSERT INTO settings (repopulatechildrenonnextstart, version) VALUES (?, ?)"), {QVariant(1), QVariant(dbVersion)}}

in DatabaseSchemaHelper:754

insertObjectInDb

Once I fix the previous issue, I am seeing this

[11:26:06.691] (1dvxtqy7b4) ERROR : int ObjectStore::impl::insertObjectInDb(QSqlDatabase&, const QObject&, bool)
   Error executing database query  "INSERT INTO brewnote ..." :
   "ERROR:  insert or update on table \"brewnote\" violates foreign key constraint \"brewnote_recipe_id_fkey\"
     DETAIL:  Key (recipe_id)=(31) is not present in table \"recipe\".\n(23503) QPSQL: Unable to create query"  [database/ObjectStore.cpp:747]

My databases run under normal users granted the owner role by the superuser and does not have permission to execute these commands. As you mention in the comments, the other solution requires the keys to be created in a particular way. My quick testing indicates that our keys are not so created.

brew001=*> set constraints brewnote_recipe_id_fkey deferred;
ERROR:  constraint "brewnote_recipe_id_fkey" is not deferrable

We could maybe update the create commands, I don't think that will do us any good. Any existing postgresql database (of which I may be the only user) won't have those definitions, and any process relying on the keys being properly defined simply won't work. To add insult to injury, the SET CONSTRAINTS method has to be done inside the transaction for postgresql but the PRAGMA must be done outside the transaction for SQLite.

suggested fix

Given that we allow the user to store the credentials in a file in plain text, I am really uncomfortable with requiring them to provide a user with the necessary juice. I am not even certain I know the password for my superuser.

One thing I can think to do is to prompt for the superuser privileges before we start the copy and bind to postgresql with that ID instead of the stored one? That way, at least, the password is only ever memory resident? That could simplify setting up the postgresql. Right now, it requires a person to create the database manually. If we had superuser credentials, we could do that as well.

This worked in previous code because we did things in a specific order and made sure we did it in that order. It isn't pretty, but this would work if we deferred the brewnote objects until after the recipes. I know because I deleted all the brewnotes in my test db and this error went away.

Brewtarget tooltip

If you haven't done this, you may not realize there is a pop-up when you hover over the Brewtarget image above the trees. It is supposed to show the database type you are connected to, and some useful information with respect to that database.

Even though I am connected to a sqlite database, it is showing information for a postgresql database. This has confused me several times. This may not be a problem with this PR. I will check older code.

coredump on db transfer

This is causing some fun side effects.

[13:08:15.578] (1dvxtqy7b4) ERROR : int ObjectStore::impl::insertObjectInDb(QSqlDatabase&, const QObject&, bool) After writing Equipment # 26 to database, primary key was changed to 1  [database/ObjectStore.cpp:782]
[13:08:15.578] (1dvxtqy7b4) ERROR : ASSERT: "false" in file /home/mik/brewtarget/matty0ung/src/database/ObjectStore.cpp, line 785  [database/ObjectStore.cpp:785]

Thread 1 "brewtarget" received signal SIGABRT, Aborted.
0x00007ffff5e8029e in raise () from /lib64/libc.so.6
(gdb) bt
#0  0x00007ffff5e8029e in raise () from /lib64/libc.so.6
#1  0x00007ffff5e6a536 in abort () from /lib64/libc.so.6
#2  0x00007ffff63f7b17 in QMessageLogger::fatal(char const*, ...) const () from /usr/lib64/libQt5Core.so.5
#3  0x00007ffff63f6e3d in qt_assert(char const*, char const*, int) () from /usr/lib64/libQt5Core.so.5
#4  0x000055555569c171 in ObjectStore::impl::insertObjectInDb (this=0x555555b92240, connection=..., object=..., writePrimaryKey=true)
    at /home/mik/brewtarget/matty0ung/src/database/ObjectStore.cpp:785
#5  0x000055555569947f in ObjectStore::writeAllToNewDb (this=0x555555b34800 <(anonymous namespace)::ostSingleton<Equipment>>, connectionNew=...)
    at /home/mik/brewtarget/matty0ung/src/database/ObjectStore.cpp:1500
#6  0x00005555556a2a47 in WriteAllObjectStoresToNewDb (newDatabase=..., connectionNew=...) at /home/mik/brewtarget/matty0ung/src/database/ObjectStoreTyped.cpp:804
#7  0x000055555568c694 in DatabaseSchemaHelper::copyToNewDatabase (newDatabase=..., connectionNew=...) at /home/mik/brewtarget/matty0ung/src/database/DatabaseSchemaHelper.cpp:852
#8  0x0000555555681540 in Database::convertDatabase (this=0x555555b33890 <Database::instance(Database::DbType)::dbSingleton_SQLite>, Hostname=..., DbName=..., Username=..., Password=...,
    Portnum=5432, newType=Database::PGSQL) at /home/mik/brewtarget/matty0ung/src/database/Database.cpp:907
#9  0x00005555558281c6 in OptionDialog::transferDatabase (this=0x555556225e80) at /home/mik/brewtarget/matty0ung/src/OptionDialog.cpp:881

I have 35 equipment profiles total in my test database, so the #26 is odd. I've also see this a few times in different places, I've also not seen it a few times which generates the next core dump too.

The net result is that no data gets transferred, as this dump or the next rolls the transaction back.

coredump even when no transfer

If I just switch database types and DO NOT transfer the data, I get this core:

[13:12:25.406] (1dvxtqy7b4) ERROR : bool Database::impl::loadSQLite(Database&) 
Error executing database query  "
SELECT sqlite_version() AS version;" :  
"ERROR:  function sqlite_version() does not exist\n\
LINE 1: SELECT sqlite_version() AS version;\n               ^\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\n(42883) 
QPSQL: Unable to create query"  [database/Database.cpp:273]
WARNING:  there is no transaction in progress
[Thread 0x7ffff4ac5640 (LWP 26030) exited]

Thread 1 "brewtarget" received signal SIGSEGV, Segmentation fault.
0x00007ffff6430e07 in QReadWriteLock::lockForRead() () from /usr/lib64/libQt5Core.so.5
(gdb) bt
#0  0x00007ffff6430e07 in QReadWriteLock::lockForRead() () from /usr/lib64/libQt5Core.so.5
#1  0x00007ffff7f3035a in QSqlDatabase::connectionNames() () from /usr/lib64/libQt5Sql.so.5
#2  0x00005555556803c6 in Database::unload (this=0x555555b338a0 <Database::instance(Database::DbType)::dbSingleton_PostgresSQL>) at /home/mik/brewtarget/matty0ung/src/database/Database.cpp:731
#3  0x000055555567ebc8 in Database::~Database (this=0x555555b338a0 <Database::instance(Database::DbType)::dbSingleton_PostgresSQL>, __in_chrg=<optimized out>)
    at /home/mik/brewtarget/matty0ung/src/database/Database.cpp:515
#4  0x00007ffff5e82a33 in ?? () from /lib64/libc.so.6
#5  0x00007ffff5e82bda in exit () from /lib64/libc.so.6
#6  0x00007ffff5e6b804 in __libc_start_main () from /lib64/libc.so.6
#7  0x00005555555bb7ca in _start ()

suggested fixes

This looks like a query is being run against the new database but still using the old databases information. I haven't a suggested fix just yet.

upgrading existing postgresql

I don't see I can make much headway against copying the databases, so I will now try to upgrade some things. Remember, this is a mostly version 7 upgrade. One hopes the hard part is 7 -> 8, but lets see.

booleans

Yeah. This again. It's a different error, but it's the same root problem.

2021-08-23 13:22:06.397 EDT [29844] STATEMENT:  PREPARE qpsqlpstmt_1 AS
  CREATE TABLE tmpbrewnote (
    id            SERIAL PRIMARY KEY,
    abv DOUBLE    PRECISION DEFAULT 0,
    attenuation   DOUBLE PRECISION DEFAULT 1,
    boil_off      DOUBLE PRECISION DEFAULT 1,
    brewdate      DATE DEFAULT CURRENT_TIMESTAMP,
    brewhouse_eff DOUBLE PRECISION DEFAULT 70,
    deleted       BOOLEAN DEFAULT $1,
    display       BOOLEAN DEFAULT $2,
    ...

I am guessing some prepared values never got bound? I can see other hints of this problem elsewhere.

suggested (partial) solution

Since we are getting rid of the default values everywhere else, it probably makes more sense to get rid of them here as well. I think this is not really fixing the problem but I think it at least delays the problem.

where's the INTEGER

The commands for doing this:

ALTER TABLE fermentable ADD COLUMN inventory_id REFERENCES fermentable_in_inventory (id)

are throwing errors in postgresql.

suggested fix

Modify lines 392, 447, 486, 525 to look like this for postgresql:

ALTER TABLE <table> ADD COLUMN inventory_id INTEGER REFERENCES fermentable_in_inventory (id)

We already did this in Database, although it might be a little cleaner as we've hardcoded INTEGER into the string, and we should likely be using the db.getDbNativeTypeName<int>() instead. We can maybe kick that problem down the road a little, as any database that doesn't use INTEGER won't need upgrading. Yeah, it's a dirty cheat, but I think we can reasonably make some excuses in this bit of the code.

last one for today

Ok. Last one for the day.

[13:51:07.977] (1dkx4g4ygw) ERROR : bool {anonymous}::executeSqlQueries(BtSqlQuery&, const QVector<{anonymous}::QueryAndParameters>&)
   Error executing database upgrade/set-up query
      "INSERT INTO hop_in_inventory (hop_id) VALUES (
         SELECT id FROM hop WHERE NOT EXISTS (
            SELECT hop_children.id FROM hop_children WHERE hop_children.child_id = hop.id )
            AND NOT EXISTS (
               SELECT hop_in_inventory.id FROM hop_in_inventory WHERE hop_in_inventory.hop_id = hop.id
            )
       )" :
   "ERROR:  syntax error at or near \"SELECT\"\nLINE 1:
      INSERT INTO hop_in_inventory (hop_id) VALUES (
      SELECT id FRO...\n                                                       ^\n(42601)
      QPSQL: Unable to create query"  [database/DatabaseSchemaHelper.cpp:94]

Same error as yesterday, but now for hops.

suggested fix

I am uncertain if you were just testing to see if this worked. If you were, then it did work and now we either need to copy/paste that a few more times (hops, miscs and yeasts) or...

If I might humbly suggest, could we reconsider this a little? The one set of queries spans about 320 lines. Doing the simple copy/paste will add about another 40 or so lines. I think 360 lines is way too long for one data structure. At least, I am not finding the readability of this mega-query to be any greater than the readability of a doing this with a few while loops and some reasonable string substitutions?

[mikfire]

Could we leverage that to just switch to the new database once it's copied, instead of asking the user to restart?

Yes, I think we could. It's probably not a huge code change, but would need some careful testing to make sure none of the code was still trying to do something in the old DB after the switchover. Would you be OK for us to do that as a separate enhancement ticket?

As a rule, I think in the error case we need to first tell the user something went wrong. We also probably should consider not changing the dbType in the config file, but I think that has always worked that way?

Agreed. Are you OK to bundle up in the same ticket as above? (I'm happy to do the work, I'm also keen to draw this particular ticket to a conclusion if we can get it to a point where it's not taking any steps backwards! smile )

Definitely no problem doing this as a separate pull request. It's a thing that's annoyed me for a long time, and I was hoping if I mentioned it you would have better ideas :smile:

[matty0ung]

This is good stuff -- thanks Mik.

• ObjectStore.h - My bad, will add the missing include here (and in OptionDialog.cpp)
• repopulateChildrenOnNextStart - Should have spotted that as it even says int on the previous line. Will do a fix
• insertObjectInDb (need for superuser perms) - Should we look at storing passwords securely rather than in plain text? Seems we could use code from https://github.com/frankosterfeld/qtkeychain to do most of the heavy lifting to have the OS store a password in its keychain / credential store / etc. WDYT?
• Brewtarget tooltip - I hadn't spotted this. Looks like the code is in RecipeFormatter::getLabelToolTip() which is not the most obvious location for it! Don't see any reference to SQLite there. My instinct would be for whatever code constructs the tooltip to ask the Database class for a list of name:value credentials. Should be small effort I think, so will include with my other fixes.
• coredump on db transfer - The assert is saying that the primary key on the object we just inserted is not what we think it is. There should be an error logged immediately before the app exited which shows actual and expected values. If you have that to hand, it would give us a good clue as to what's going on. (Might perhaps be that the DB doesn't return a primary key when we specified one rather than having the DB generate one.)
• coredump even when no transfer - Took me a few minutes to work this one out. I think the bug is in dbConnectionNameForThisThread where we're saying "each thread has one DB connection" instead of "each thread has one DB connection per database". Will do a fix.
• upgrading existing postgresql
  • boolean - Ach, I thought I was being very clever using QVariant for the default values, but obviously not clever enough! Your solution of dropping the defaults is much better. Will implement.
  • where's the INTEGER - Yes, having written Database::getSqlToAddColumnAsForeignKey() we should make sure to actually use it when we add a column! Will fix.
  • last one for today - Oh, yes, I see we need a dummy run query for this too and the other places you mention. Your suggestion about loop and substitute sounds the right way to go. I'll do a fix.

[mikfire]

coredump on db transfer - If you look at the first two lines of the output, it is there. We inserted equipment # 26 (out of 35) and got back 1. I've seen this succeed with no error. I also remember another core dump where the code inserted # 8 and got back 1 but I do not remember what table.

I will run that test a few times and see if I can capture a bit more. One thing I do like about postgresql is it has great logs.

[mikfire]

insertObjectInDb (need for superuser perms) - For now, I suggest we mark this as a known bug and I use the admin id and password to further my testing. I want time to do this better, but I don't think my idea of "better" belongs in the scope of this PR.

There should be a new tool, something like "Create new database" which would allow you to specify which type you wanted to create, and then prompt for the required information: path and file name for sqlite; hostname, port, database schema and name, admin id and password for postgresql.

You would be presented with an option on this screen to copy the existing database to the new. If you say "Yes", we will copy the data. If you say "no", you will get an empty database but with the full schema defined. If you say "yes" and there is any hint of an existing schema in the target db, we exit. If you want to just copy data, use the "Copy database" feature.

As part of the process for creating the new postgresql database, we would create a user and a password and store that. Since we are bound as a superuser during this process, we can toggle the flags as we need.

There would be a second new tool, something like "Copy database" which would allow you to specify a source and destination type. If the destination type is postgresql, then we will require the admin user and password. If the schema is not in the new database, then the process fails. If you want a new database, use the "new database" process. I think we also need to abort if there is any hint of data in there.

Once we have this tool, we change the options screen so all it does is swap between different databases.

I think that will make things much cleaner, but I will have some work to do. I will work on this after we get the worst of the issues chased out of this PR.

[matty0ung]

Ah, sorry, somehow missed that. [Edit - I see now that I should have scrolled right!]

Seems there are two possibilities. One is that the data was inserted just fine and the "last primary key inserted" function is giving us "last automatically generated primary key", in which case we remove the assert and we're done.

Alternatively, if it's actually inserting a different primary key than we asked it to, then we need a bit more research to understand why.

M

Télécharger Outlook pour Androidhttps://aka.ms/AAb9ysg

---

From: mikfire @.> Sent: Tuesday, August 24, 2021 2:11:29 PM To: Brewtarget/brewtarget @.> Cc: matty0ung @.>; Author @.> Subject: Re: [Brewtarget/brewtarget] New Database Layer (#617)

• coredump on db transfer - The assert is saying that the primary key on the object we just inserted is not what we think it is. There should be an error logged immediately before the app exited which shows actual and expected values. If you have that to hand, it would give us a good clue as to what's going on. (Might perhaps be that the DB doesn't return a primary key when we specified one rather than having the DB generate one.)

If you look at the first two lines of the output, it is there. We inserted equipment # 26 (out of 35) and got back 1. I've seen this succeed with no error. I also remember another core dump where the code inserted # 8 and got back 1 but I do not remember what table.

I will run that test a few times and see if I can capture a bit more. One thing I do like about postgresql is it has great logs.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/Brewtarget/brewtarget/pull/617#issuecomment-904582628, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AESIX7E66VQGCG3FCIXQHXLT6OD7DANCNFSM5A7SAO6A. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email.

[mikfire]

I have a dirty thought.

There's an odd bit of code in the original copyDatabase that talks about manually resetting the sequences in postgresql, (database.cpp, line 4717). As an example:

select setval(hop_id_seq,(SELECT MAX(id) from hop));

If I've understood, basically the sequence isn't invoked any time we actually supply the id. This is a known problem when doing mass inserts or copying databases. Well enough known that it is in the PostgreSQL official documentation.

If I've understood this correctly, your assertion will always fail. Until we manually update the sequence, the last inserted ID will always be 1. The random numbers I was seeing was that we are accessing a hash, so it always failed on the first insert but the actual ID was quasi-random.

Of course, there's another problem here because I don't see we are currently ever updating the sequences. The first insert done after an import like that will fail. I think this should go in the schema helper part of the code, because this is the sort of meta-database thing SchemaHelper knows about.

Let me do some more testing to see if we can .. borrow .. the example given in the link above can just fix all the problems.

[mikfire]

This one took me too long. There's an issue in ObjectStore::insertIntoJunctionTableDefinintion. We prepare the BtSqlQuery once, and try to reuse it a few times.

The problem is that BtSqlQuery::exec sets the bt_query to "" and the bt_boundValues to false as soon as it is used. This is fine as long as we only ever want to exec the query once. If we try to bind new values to the query, reallyPrepare() is called. Since bt_boundValues is now false, we call QSqlQuery prepare on bt_query. And Qt dumps core complaining that we are preparing an empty query

I think we should not be resetting the member variables.

[mikfire]

My dirty thought was correct. You pretty much want to add this to DatabaseSchemaHelper::copyToNewDatabase(), immediately after the call to WriteAllObjectStoresToNewDb().

   // we need to rebuild all our sequences in postgresql
   if (newDatabase.dbType() == Database::PGSQL) {
      BtSqlQuery q{connectionNew};
      BtSqlQuery sequence{connectionNew};

      // so this query will return a list of queries we need to run. I have
      // taken this from https://wiki.postgresql.org/wiki/Fixing_Sequences and
      // have no idea how it actually works.
      QString resetIndexes = QString(
            "SELECT 'SELECT SETVAL(' ||"
            "       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||"
            "       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||"
            "       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'"
            " FROM pg_class AS S,"
            "     pg_depend AS D,"
            "     pg_class AS T,"
            "     pg_attribute AS C,"
            "     pg_tables AS PGT"
            " WHERE S.relkind = 'S'"
            "    AND S.oid = D.objid"
            "    AND D.refobjid = T.oid"
            "    AND D.refobjid = C.attrelid"
            "    AND D.refobjsubid = C.attnum"
            "    AND T.relname = PGT.tablename"
            " ORDER BY S.relname;");
      if ( ! q.exec(resetIndexes) ) {
         qCritical() << Q_FUNC_INFO << "Could not find list of sequences to rebuild" << q.lastQuery() << q.lastError().text();
         return false;
      }
      while ( q.next() ) {
         QString doMe = q.value(0).toString();
         qInfo() << Q_FUNC_INFO << "doMe =" << doMe;
         if ( ! sequence.exec(doMe) ) {
            qCritical() << Q_FUNC_INFO << "Could not rebuild sequence" << q.lastQuery() << q.lastError().text();
            return false;
         }
      }
   }

or at least something like it. I got the first query from the previously linked Wiki page. It actually returns a list of queries to be run to update the sequences, which surprised me a little. I am willing to be I could be more clever, but this got it done.

The good news is that with promoting my db user to superuser, removing the assertion, modifying BtSqlQuery::exec to not reset the member variables and this change, I actually am able to import a SQLite db into Postgresql and add new things. Yay!

[mikfire]

Unfortunately, something isn't right with respect to the inventory.

[13:34:56.684] (1dkgtd6phc) WARNING : QList<std::shared_ptr<QObject> > ObjectStore::getByIds(const QVector<int>&) const Unable to find object with ID  131  [database/ObjectStore.cpp:1157]

when I try to modify a newly added hop.

It doesn't happen on the existing hops.

The hop_in_inventory row was created, but the id of that row was never put back on the new hop. I haven't been able to understand how all of the insert work is done yet, so I am unable to provide a suggestion

[matty0ung]

There's an issue in ObjectStore::insertIntoJunctionTableDefinintion. We prepare the BtSqlQuery once, and try to reuse it a few times. ... I think we should not be resetting the member variables.

D'oh, of course. Yes, you're absolutely right. Will fix.

I have a dirty thought [about PostgreSQL sequences].

This is the sort of thing that makes you want to have a database abstraction layer. :grin:

The "fix everything" SQL on the PostgreSQL looks pretty painful, but I wonder if we can do something slightly simpler. If we could write a function along the lines of void updatePrimaryKeySequenceIfNecessary(char const * const tablename) then it could be a member function of Database with a no-op implementation for SQLite. WDYT?

insertObjectInDb (need for superuser perms) - For now, I suggest we mark this as a known bug...

OK, that makes sense to me. I like the idea of that QtKeychain library, so we might want to look at it in the future (for regular DB user passwords) but, for the superuser password, your approach of saying we should only ask for it when we need it, and not store it, feels like the right thing to do.

[matty0ung]

Comments crossed in the post! :smile: Will have a look at the inventory thing.

[matty0ung]

Unfortunately, something isn't right with respect to the inventory.

This is now fixed. (I was foolishly trying to get some Hop objects from the InventoryHop store.)

Various other fixes in the last commit. Currently working on Brewtarget tooltip and the magic potion SQL to fix PostgreSQL sequences.

[matty0ung]

Found a way that claims to update a primary key sequence in PostgreSQL that's a bit more comprehensible than the big scary query (which was really making my head hurt trying to decipher). And that tooltip should now be working.

[mikfire]

Oh. I liked the big scary thing that I had no idea how it actually worked. You are taking all the fun out of this :grin:

We are really close on at least converting the database. I have been able to copy from sqlite -> postgresql with these (small) changes and can properly insert new things into postgresql. Ignoring the core dump.

insertObjectInDb -- the assertion is still not "fixed" for postgresql. Until we adjust the sequences, writePrimaryKey will always be 1 and will never equal currentPrimaryKey. I've been cheating and just doing this:

if (writePrimaryKey) {
  writePrimaryKey = currentPrimaryKey;

which I think I could reasonably defend as the "right" answer.

updatePrimaryKeySequenceIfNecessary -- You need to say COALESCE(MAX(%2,1), not COALESCE(MAX(%2,0). 0 isn't an allowed value and causes problems. I also really like the method name. I laugh every time I read it.

dumping core This one is a snot. It seems to happen every time we switch between databases. It doesn't seem to matter if we copy data and it doesn't seem to matter which DB we are switching from.

The backtrace from gdb looks like:

0x00007ffff6425327 in QReadWriteLock::lockForRead() () from /usr/lib64/libQt5Core.so.5
(gdb) bt
#0  0x00007ffff6425327 in QReadWriteLock::lockForRead() () from /usr/lib64/libQt5Core.so.5
#1  0x00007ffff7f26cce in QSqlDatabase::connectionNames() () from /usr/lib64/libQt5Sql.so.5
#2  0x0000555555680ad4 in Database::unload (this=0x555555b388a0 <Database::instance(Database::DbType)::dbSingleton_SQLite>) at /home/mik/brewtarget/matty0ung/src/database/Database.cpp:739
#3  0x000055555567f1ac in Database::~Database (this=0x555555b388a0 <Database::instance(Database::DbType)::dbSingleton_SQLite>, __in_chrg=<optimized out>)
    at /home/mik/brewtarget/matty0ung/src/database/Database.cpp:520
#4  0x00007ffff5e76a33 in ?? () from /lib64/libc.so.6
#5  0x00007ffff5e76bda in exit () from /lib64/libc.so.6
#6  0x00007ffff5e5f804 in __libc_start_main () from /lib64/libc.so.6
#7  0x00005555555bb7ea in _start ()

but that doesn't seem to be the real problem.

Database::unload() is being called twice -- once in Brewtarget::cleanup() and once in the Database object destructor and it is that second call that is dumping core. As the comment in the destructor says, it is called right near the end of process and much of the framework and objects provided by Qt simply aren't there any more. Some of the qInfo() dumps are fascinating.

Using my very rusty gdb skills indicates this->pimpl->loaded is true when it shouldn't be. It almost looks like there are two database objects in existence. Under normal circumstances, this isn't a problem because we only set this->pimpl->loaded = true on the database we are using.

When we are switching between them, however, both database objects are marked with this->pimpl->loaded = true. When unload() is called, it closes all the connections, but can only set one of the two this->pimpl->loaded to false. When the deconstructor fires, it all goes sideways.

I am really thinking we need to just take the unload() out of the deconstructor -- I believe it will never do more than dump core anyway. But I would really like to understand what the difference is between what we did then and what we do now that is causing the core dump.

[matty0ung]

Feels like we're getting there!

insertObjectInDb assertion - D'oh, I knew I'd forgotten something. Yes, you're right. This is now fixed.

updatePrimaryKeySequenceIfNecessary - Makes sense. Have corrected and expanded the comment to explain.

dumping core - I've made some changes to Database::unload() to make it more aware of the possibility that there is more than one Database object and that a Database object might not have been fully initialised. Depending on how things evolve there might be more refinement to do here. (DB connection names are per-database-type-per-thread but might one day need to be per-Database-object-per-thread.) Once we've satisfied our curiosity about whether these corrections stop the core dump :grin:, we can take unload() out of the destructor, as I agree it's not an ideal place for it.

Tooltip - This was making me uncomfortable, so I found a less bad place to put the logo tooltip generator (in MainWindow rather than RecipeFormatter).

[mikfire]

The core dump was far more insidious than that. It took a few hours with gdb, writing to std::cout because it was too late for qInfo(), etc. This is going to take some time to setup. Sorry about that.

The problem starts when we switch databases. As part of OptionDialog::saveDatabaseSettings, we change the PersistentSetting value for the dbType from the old value to the new value, assuming it will be properly picked up on restart of the database. It does not matter what the old value or the new value are, it only matters that they are different.

When the shutdown process starts, Brewtarget::run() calls Database::instance().cleanup() without specifying the dbType parameter when invoking Database::instance(). This causes Database::instance() to read the PersistentSetting value for the dbType and it gets the new value instead of the old.

The singleton methods rightly recognize that we haven't yet called the load for the new dbType, because we haven't. So it calls the proper load method and returns that. Database::Instance().cleanup() then properly closes the database we just opened.

The problem is that the old database is still opened, is still loaded, etc. Somewhere near the end of cleaning everything up, Database::~Database() is called on that connection. Unfortunately, it is far too late in the process for anything to actually work -- the Qt objects we need (QMutexLock, QSqlDatabase,etc) have all been deallocated and simply aren't there. So we dump core.

The reason this worked in the old code is we had ONE singleton for all databases, not a singleton for each. So we could change the persistent settings and not impact any of the running configuration. I strongly suspect the old Database::~Database() code would have exploded just like this if we had ever actually invoked it.

To fix this, we first need to take the call to unload() out of Database::~Database(). It will simply never work and probably never has.

I think the easiest fix is to attempt to revert to something closer to the original semantics. Database::instance() should only ever return the database instance that was opened when the code started.

If we don't want easy, I think the best is to figure out how to change databases midstream. I made a few tries at it, but it looks like another std:call_once in ObjectStoreTyped is going to make refreshing the in-memory caches difficult.

[matty0ung]

Wow, excellent detective work! All makes sense.

I have some (currently nebulous) ideas for a fix (including taking all the work out of the destructor as you recommend). Will have a look over the next couple of days.

[matty0ung]

Database::instance() should only ever return the database instance that was opened when the code started.

I've pushed a fix that pretty much does this, as well as take the call to unload() out of Database::~Database(). (Database code now remembers what it first read out of PersistentSettings and doesn't look there again.) I don't think this would preclude us from, in future, allowing database change without restart. We'd just have to have a call that explicitly tells Database "shut everything down that you're currently using and look again in PersistentSettings".

[mikfire]

Two last problems that I can find, and I swear we can merge this.

In DatabaseSchemaHelper::migrate_to_8(), line 401. It would seem that when doing an insert from a select, you don't use the VALUES construct. It should look like:

migrationQueries.append({
      QString("INSERT INTO %1_in_inventory (%1_id) "
      // Everything has an inventory row now. This will find all the parent items that don't have an
      // inventory row.
      "SELECT id FROM %1 WHERE NOT EXISTS ( "

That took me longer to find that you might expect.

The second issue is in the same method, in the for loop starting at line 440. The yeast_in_inventory table does not have a column called "amount"; it has a column called "quanta".

Fixing those two allows me to upgrade a database from brewtarget 2.3.1 to the current without an error. You can try this by getting the default_database.sqlite from the stable/2.3.1 branch, copying it into the right place and starting brewtarget.

I haven't tried this on postgresql, because I can't find an older backup I actually trust. I am not going to worry about that too hard right now.

[matty0ung]

I am looking at a bug in ObjectStore that blows up when you import a new Style as part of importing a Recipe from XML. I think it may be a transaction thing. Watch this space.

[mikfire]

There seems to be something weird with the mashs and mashsteps as well, but I am willing to handle it as a normal bug/pr.

I am trying to create a new mash. I can create the mash, I can add a mashstep but then trying to use the mash wizard says "You need to add a step". If I restart brewtarget, that one step is gone. When I add the mash step, I see

[09:53:56.405] (1dn7f0iebk) WARNING : QObject::connect: 
No such slot MashStepTableModel::changed(QMetaProperty,QVariant) 
 in /home/mik/brewtarget/matty0ung/src/MashStepTableModel.cpp:66  [:0]

My bet is that the view thinks it did a thing, but the signal wasn't properly caught so that database doesn't see the thing was done.

If I try to adjust an existing mash by deleting a mashstep, I get

[09:51:50.930] (1dn7f0iebk) WARNING : 
  MashStep* Mash::removeMashStep(MashStep*) 
Tried to remove MashStep # 37  (from Mash # 34 ) but couldn't find it  [model/Mash.cpp:340]

I suspect this is because the step has actually been removed, but the table hasn't seen the signal to remove it.

[matty0ung]

Right, I fixed my crash, then found another one and fixed that. Have also made a change that should at least improve the issue you're seeing above.

Ideally, I'd like to go through the code and remove all uses of the SIGNAL and SLOT macros as they take away the compile-time checking you get when you use the address of the signal/slot function.

[mikfire]

Mashes are still not working correctly. These errors do not seem to be in HEAD on develop, so I am thinking they are net new. I am also willing to merge as is, and handle this as a standard bug/pr.

The mash designer is not updating the "total collected wort" as I increase the infusion amount. If I say "Finish" in the mash designer, the steps are not shown in the mash table. If I just add a step, I see the step added in the mash table. Clicking "Mash Wizard" still says "There must be at least one mash step to run the wizard". If I restart brewtarget, there is nothing either in the table or the database.

I am very suspicious of the lack of an objectStoreWrapper::insert in MashStepEditor::saveAndClose. To make it work, I need to put the insert after the addMashStepToMash() which feels really, really weird. If I do it before, my database will suddenly have mashsteps where mash_id IS NULL.

I don't see that calling insert is setting cacheOnly to false, which I think it should be. I also see this not happening in the Equipment editor, but it seems to happen almost everywhere else. MashWizard is likely going to take a lot of work to figure out where inserts need to be called.

Trying the mash wizard after my fixes above generates an assert failure, saying:

(1do8u7eozk) ERROR : 
int ObjectStore::impl::insertObjectInDb(QSqlDatabase&, const QObject&, bool) 
Wrote new MashStep  to database (with primary key  51 ) but it already had primary key 49  
[database/ObjectStore.cpp:794]
(1do8u7eozk) ERROR : ASSERT: "false" in file 
/home/mik/brewtarget/matty0ung/src/database/ObjectStore.cpp, line 797  
[database/ObjectStore.cpp:797]

Trying that same thing in a slightly less messed up database does the same core dump, but with this message:

(1drdj14cg0) ERROR : 
int ObjectStore::impl::insertObjectInDb(QSqlDatabase&, const QObject&, bool) 
Wrote new MashStep  to database (with primary key  41 ) but it already had primary key 40  
[database/ObjectStore.cpp:794]
(1drdj14cg0) ERROR : ASSERT: "false" in file 
/home/mik/brewtarget/matty0ung/src/database/ObjectStore.cpp, line 797  [database/ObjectStore.cpp:797]

[mikfire]

I tried to rework the signal/slot mechanisms a while ago.

The problem was when we used overloaded slots. At that time, the only way to handle that was with the macros. They've since fixed that problem, or at least published a work around.

I agree with fixing it, but that should be a different pull request, though. This one is gnarly enough as it is.

[matty0ung]

Having a look at this. Looks like, with the Mash Designer, we're creating a Mash OK but then inserting a MashStep in the DB without having set its Mash ID.

Similarly, I see what you mean about MashStepEditor::saveAndClose too. It calls Brewtarget::mainWindow()->addMashStepToMash(), which calls Mash::addMashStep() but that doesn't save the MashStep in the DB. Will have a think about where best to do that. As you say, we don't want to try to insert things twice as it causes other problems. (We could bodge round them by calling insertOrUpdate() rather than insert(), but it would be nicer to just call insert once at the right time.)

[matty0ung]

Whilst I'm looking at this, do you know what "Save Mash" (on the Mash tab of the current Recipe) is supposed to do? At the moment, it's saving a copy of the current Mash, but I wondered, if that's what it's for, whether it should be prompting the user to name the new Mash?