Am I not understanding how database hooks work?

Ask the community
, ,
1

TLDR;
I am running a hook that concatenates a bunch of columns together and saves it into another field in the table. After running the hook, the field doesn’t stay populated, but instead reverts back to a VERY OLD version of the field that was populated by a VERY OLD test hook that I wrote a long time ago.

Am I not understanding how database hooks work? If I set a field on a row inside the hook, and then return the row (with the field populated), should that get written into the database? Or are hooks somehow only temporary? Is there something I have to do to explicitly save the item with the computed field?

Preface:
I should preface this by saying that I am experiencing VERY BIZARRE behavior where the Wix editor repeatedly tells me that I have made edits in more than one window (I HAVE NOT. I have confirmed dozens of times that there is only one editor open, and there are NO OTHER COLLABORATORS on this project. It’s just me). I have tried force reloading the editor, closing all Wix tabs and reopening, quitting out of Chrome altogether and re-launching, and even rebooting my computer. Nothing works to fix this. When this error happens, changes that I have made in the editor are randomly lost. I have lost new database table definitions, javascript code, an onReady implementation that I keep changing reverts back to a very old version, and random other edits, including the data.js file, which occasionally reverts the hooks back to a very old version.

I have no idea if what I am experiencing wrt hooks not saving and reverting back to a value that was set many weeks ago is due to this problem, or if I am simply not understanding how database hooks, preview mode, content manager, and sandbox database are supposed to work. (BTW, I have 3 separate tickets open with Wix support. Every answer they’ve given me is unhelpful. Basically, they just punt it back to me and give me some non-answer like “I was able to save your site”)

Details:
I am working on a site that is not published yet. I am working in the Sandbox database, and looking at the site in Preview mode. I have a dynamic page that is connected to the table in question. I have tried setting the dataset to Read-Write, and it makes no difference.

I have a table: Plants. Plants has several fields that are concatenated in various ways to create the Botanic Name. When I initially populated the database, the botanic name column was not defined. There was no column for it, and I had not written the code to concatenate the existing columns together to produce it. I figured I would use a database hook.

A long time ago, I wrote some test code just to figure out how database hooks worked. In that code, I created an afterQuery hook and just did this:

export function Plants_afterQuery(item, context) {
item.botanicName = ‘BOTANIC NAME’;
return item;
}

I created a text element to display botanicName on the dynamic page that is connected to this table. Then I previewed the site, saw the string ‘BOTANIC NAME’ and then went back to the editor. When I looked at the database table, there was a new field called Botanic Name, and it was populated with ‘BOTANIC NAME’. It was undefined (had a little yellow box icon and an ! telling me the field isn’t defined), so I defined the field so that it would actually exist in the database. The field, now defined, is populated with the old value ‘BOTANIC NAME’

I don’t need to run this hook after every query (The value of this field only needs to be re-computed on insert/update), so I deleted the hook and went on with other tasks. This was weeks ago. In the intervening weeks, the column in the table stayed defined most of the time…occasionally, after the save error, I would have to redefine it again. Regardless of whether or not I had to redefine the column, all rows stayed populated with ‘BOTANIC NAME’.

Sometime later (weeks), I wrote the actual code to create the botanic name and set it to run on afterInsert, afterUpdate, and again, I created a temporary hook afterQuery to test it out.

The actual code follows. I wrote/ran this code in a js fiddle tool and confirmed that it behaves as I want. (I’ve left out some of the private functions for brevity, but described in comments what they do)

export function Plants_afterQuery(item, context) {
item.botanicName = calculateBotanicName(item);
return item;
}

function calculateBotanicName(item) {
// ensureStr - returns the original string on truthy value, empty string on a falsey value
let genus = ensureStr(item.genus);
let species = ensureStr(item.species);
let subspecies = ensureStr(item.subspecies);
let variety = ensureStr(item.variety);
let cultivar = ensureStr(item.cultivar);

    let tokens = []; 
    if (genus) { 
            tokens.push(ensureFirstLetterUpperCase(genus)); 
    } 

    if (species) { 
            tokens.push(ensureFirstLetterLowerCase(species)); 
    } 

// subspeciesOrVariety - Only one of these fields will be populated. 
// Determine which field is populated. 
// Pick the right one and do some minor formatting depending on which was populated. 
    var ssv = subspeciesOrVariety(subspecies, variety); 
    if (ssv) { 
            tokens.push(ssv); 
    } 
    if (cultivar) { 
            tokens.push(cultivar); 
    } 
    return tokens.join(" ");

}

I should mention that at this time (several weeks later) the database table STILL had the column for BotanicName defined, and it was still populated to that old, initial test value ‘BOTANIC NAME’. Possibly due to the bugs I described above, sometimes the BotanicName field thinks it’s not defined (it still has the value ‘BOTANIC NAME’ in all the rows). I once again define the field. Once again, all the rows are still populated with ‘BOTANIC NAME’.

Next, I did the same steps as before, but this time with the actual code.

  1. Create 3 hooks. afterInsert, afterUpdate, afterQuery and set them all to run the same code.

  2. Preview the site

  3. Return to the editor

  4. Note that the column is populated with the actual botanic name, and not the old test ‘BOTANIC NAME’

  5. Return to the Content Manager to look at what values are in the Plants table for the column BotanicName.

  6. At this point, the computed columns are still set.

  7. Next, I comment out the hook for afterQuery

  8. Return to the content manager

  9. The value in the column has reverted to the OLD test value ‘BOTANIC NAME’

Another thing I have tried is that I go to the Content Manager and manually enter a value into that field (by typing in something dumb, like asdf). When I hit enter and then move the cursor out of the row, the afterUpdate hook runs and the correct (hook generated) value gets set. However, when I go to Preview, it is NOT set to the hook value. It is set to asdf. When I return to the editor and content manager, it has reverted to asdf. If I modify the field value and tab out to enter the new value, the hook is again run, but the value never stays around. It always reverts back to what I manually entered, or the very old hook generated value ‘BOTANIC NAME’.

I cannot for the life of me figure out how to actually get the value generated by the hook to PERSIST.

Do I have to manually save the row inside the hook?

Am I doing this correctly, and this may be due to the plethora of other site builder saving/multiple edits bugs I have been experiencing?

I have been stuck on this for more than a week now, waiting for something coherent to come out of Wix tech support wrt all the saving errors I am experiencing. So far, they have been unable to make any progress on my issues. I can’t keep waiting around, so I thought I would ask in this forum to see if anyone has ever experienced what I am experiencing, and whether or not I am doing something totally wrong.

Thank you