Clarification - Symbiota v3.0.9 Custom DB Schema Application

[varuni24]

I am currently working on setting up and validating the symbiota_309_custom database from the summer-2023 branch of se-symbiota-portal-reference-only. My goal is to verify that this database is accurately configured with all the intended custom changes before proceeding with further comparisons.

Observation:
The install.md file in the summer-2023 branch suggests loading db_schema-3.0.sql from /config/schema/3.0/ as the core table structure, followed by 3.x patches, but no such 3.x patches can be found within /config/schema/3.0/patches/. Also, the UPDATE.md file from the same branch indicates a patch sequence ending with db_schema_patch-3.0.sql (which is currently located in /config/schema/1.0/patches/).

My Question:
Given these observations, could you please provide a definite guidance on the correct and complete sequence of SQL schema scripts that should be applied to set up the symbiota_309_custom for the v3.0.9 of Symbiota database from the summer-2023 branch?

More specifically:

• Should I be using db_schema-3.0.sql as a base, followed by any other relevant patches (if so, where are the 3.x patches for this branch, as they are not in 3.0/patches/. Are they config/schema/1.0/patches/db_schema_patch-ocr.sql, config/schema/1.0/patches/db_schema_patch-quick-entry.sql)?
• Or should I follow the UPDATE.md logic, starting with 1.x patches and then applying db_schema_patch-3.0.sql (from the 1.0/patches folder) as the final patch to reach the final version of the database?

[Tian-Tan]

So Symbiota's database has a handy table called schemaversion that allows you to see how were the database patches applied. From what I see currently in that table, the db_schema-3.0.sql patch was applied first, followed by the 1.3 patch, which was what we called the patch that contained all of our previous functions. Now that we separated the database patch by function, you should be applying those function patches on top of the db_schema-3.0.sql patch. Regarding the order of applying those function patches, you should refer to the google doc documentation.

Hope this clears it up!

[varuni24]

Based on what you mentioned, I am encountering some issues when attempting to apply database patches from the summer-2023 branch of se-symbiota-portal-reference-repo-only. I tried what you said and then also tried other different patch application sequences, and each results in an error.

Since you mentioned that you broke up the 1.3 script into feature specific scripts, I referenced this Documentation and infered that the order of feature patches would be db_schema-3.0.sql -> quick-entry-patch -> image-batching-patch -> ai-transcription / ocr-patch -> batch-ingestion-patch.

In the summer-2023 branch of se-symbiota-portal-reference-repo-only, there are only two feature scripts I notice: db_schema_patch-ocr.sql and db_schema_patch-quick-entry.sql

Environment

• Repository Branch: summer-2023 from se-symbiota-portal-reference-repo-only
• Database: MySQL 5.7
• Operating System: macOS

Sequences & Issues

1:

• Sequence:
  1. Apply /schema/3.0/db_schema-3.0.sql
  2. Then, apply /schema/1.0/patches/db_schema_patch-3.0.sql
• Issue:

  ERROR 1050 (42S01) at line 3: Table 'adminconfig' already exists
  

2:

• Sequence:
  1. Apply /schema/3.0/db_schema-3.0.sql
  2. Then, apply /schema/1.0/patches/db_schema_patch-quick-entry.sql
• Issue:

  ERROR 1033 (HY000) at line 257: Incorrect information in file: './symbiota_309_custom_57/dropdown_filedunder_values.frm'
  

3:

• Sequence:
  1. Apply /schema/1.0/db_schema-1.0.sql
  2. Then, apply /schema/1.0/patches/db_schema_patch-quick-entry.sql
• Issue:

  ERROR 1033 (HY000) at line 257: Incorrect information in file: './symbiota_309_custom_57/dropdown_filedunder_values.frm'
  

4:

• Sequence: 1.0 > 1.1 > 1.2 > 1.3 > 3.0 > ocr > quick-entry
• Issue:

  the running 3.0.9 database doesn’t match the created database
  

Expected Behavior

As Prof.Langdon helped me set up the working version of 3.0.9 of the database, it is supposed to match with the output from the sql scripts in the summer-2023 branch

Question

Could you please provide help on the correct and robust sequence for applying these database files/patches? Any insights into the expected base schema for each patch, or potential fixes for the patch files themselves like above, would be appreciated

[Tian-Tan]

I believe that there is no use for you to look at se-Symbiota-portal-reference-only as we have already separated the code by features in the se-symbiota repo. If you look at the branches you should see some named with the prefix "Feature". These branches would contain the code specific for that feature. If you look at the commit history for those branches, you should also be able to see their respective database patches. The sequence for the patches should be /schema/3.0/db_schema-3.0.sql and then the feature patches you get from those respective branches, sequenced according to the documentation. Note that the code and database patches are based on a much older version of symbiota, so if any of our code conflicts with the current newer version of symbiota, it needs to be fixed.

[Tian-Tan]

Our features should also be independent of symbiota's own updates, so our code shouldn't be affected by their upstream changes. However, you should still check to make sure.

We do not have 2 versions of the database, the one in se-symbiota is the same as the reference repo, just separated by features. The one in se-symbiota is only tested minimally as we did not have much time left, so while we can be sure it does not conflict with our own code from other feature branches, we do not know if it will conflict with upstream