Excel export not import-compatible

[ibresslerBAM]

I am in progress of setting up a separate OpenBIS instance (with regular tear-down/setup) for testing our data models of further instruments to integrate but also for testing specific upload code against new models and adjustments during development.

For replicating the production environment as closely as possible, one idea is to use this repo and its tools as source for the masterdata already in place (ground-truth). The command bam_masterdata export_to_excel comes in handy for this. Next would be to go into the admin interface -> Tools -> Import. Turns out, the generated excel files are not compatible with OpenBIS, the version we run currently (20.10.11) does not like it:

To sum it up, for reference and for those who try to attempt a similar thing, there are multiple issues. I did not propose fixes yet, since, for making it work, I changed some parts for which I do not fully understand their purpose in other parts of the project, therefore I'd just like to document & discuss, what needs changed (if there is interest to make it compatible, of course ;) ):

1. In metadata/definitions.py I have to filter object attributes which are not known to OpenBIS server, they must not show up in the generated excel file:

   --- a/bam_masterdata/metadata/definitions.py
   +++ b/bam_masterdata/metadata/definitions.py
   @@ -162,7 +162,7 @@ class EntityDef(BaseModel):
            fields = [
                k
                for k in self.model_fields.keys()
   -            if k not in ["iri", "id", "row_location"]
   +            if k not in ["iri", "id", "row_location", "object_code", "internal_assignment", "unique", "main_dataset_path", "main_dataset_pattern", "url_template", "official"]
            ]
            headers: dict = {}
            for f in fields:
   

2. The dynamic/validation scripts are not exported, e.g. DEFAULT_EXPERIMENT.date_range_validation does not exist here, right? -> Are scripts to be covered by bam-masterdata?

   It gives an error during import, saying UserFailureException: sheet: 1 line: 11 message: Object with PluginPermId = [DEFAULT_EXPERIMENT] has not been found. (Context: [setting validation script (1/1)

   If it were available, a folder scripts containing them alongside the .XLSX in a .ZIP archive should be provided for upload to OpenBIS (see also ). As quick fix, it should not be mentioned in the exported Excel file:

   --- a/bam_masterdata/cli/entities_to_excel.py
   +++ b/bam_masterdata/cli/entities_to_excel.py
   @@ -46,7 +46,11 @@ def entities_to_excel(
            excel_headers = []
            header_values = []
            for field, excel_header in obj_definitions.excel_headers_map.items():
   -            header_values.append(getattr(obj_definitions, field))
   +            value = getattr(obj_definitions, field)
   +            if "validation" in excel_header.lower() and value is not None:
   +                print(f"Skipping {obj_definitions.code}, {excel_header}, {value=}")
   +                value = None
   +            header_values.append(value)
                excel_headers.append(excel_header)
            worksheet.append(excel_headers)
            worksheet.append(header_values)
   

3. Datatype OBJECT can not be imported by OpenBIS, it seems that it's just not supported and thus throws an error: UserFailureException: sheet: 1 line: 39 message: No enum constant ch.ethz.sis.openbis.generic.asapi.v3.dto.property.DataType.OBJECT (Context: []).
   To make it work anyway, for testing, I replaced all occurrences by VARCHAR:

   +++ b/bam_masterdata/cli/entities_to_excel.py
   @@ -63,6 +63,8 @@ def entities_to_excel(
                    for field in prop.excel_headers_map.keys():
                        if field == "data_type":
                            val = prop.data_type.value
   +                        if "OBJECT" in val:
   +                            val = "VARCHAR"
                        else:
                            val = getattr(prop, field)
                        row.append(val)
   

   This is kind of a show-stopper for the intended purpose (restoring main masterdata). Perhaps, someone knows a workaround in the context of excel-imports? For my purpose, this points me to other methods of syncing masterdata, perhaps server-side scripts.

4. Another hard-to-solve issue on this side and version are internal properties (beginning with $). They can be modified (and created) only by system user and not instance admin. Afaik, you can't login as system user, but only server-side scripts run with this level of privileges can modify those properties (same as previous issue).

   For example, when importing with Update if exists mode, it complains early at the $NAME property with UserFailureException: sheet: 1 line: 5 message: Authorization failure: Property assignments created by the system user for internal property types can be managed only by the system user.

   There is one new internal property defined by our masterdata here ($ANNOTATIONS_STATE) that does not exist in the default installation of OpenBIS and therefore it can not be imported by Excel files. To make it work, I filtered this property from all objects, and thus the associated functionality will be missing after import:

   --- a/bam_masterdata/cli/entities_to_excel.py
   +++ b/bam_masterdata/cli/entities_to_excel.py
   @@ -59,6 +59,8 @@ def entities_to_excel(
                    list(obj_instance.properties[0].excel_headers_map.values())
                )
                for prop in obj_instance.properties:
   +                if "$ANNOTATIONS_STATE" in prop.code:
   +                    continue
                    row = []
                    for field in prop.excel_headers_map.keys():
                        if field == "data_type":
   

5. Since other already existing internal properties get exported as well and by importing the file with ignore if exists update mode, it will not complain. But of course, this does not update other (non-internal) properties that might have changed.
   

With those changes, I could restore masterdata partially :)

[JosePizarro3]

@carlosmada22 please read this issue.

Interesting, we've never used this process, as the Excel import is very buggy from the openBIS side, so I would recommend not to do it this way. That's why we coded a push_to_openbis with the idea of pushing back the changes in the masterdata to the openBIS instance (fill_masterdata would then be the complementary to this) by using PyBIS and the object/properties creation instead.

One caveat is that push_to_openbis is not prone to errors, as I found some bugs after the Data Store Days, but didn't finish fixing them: #133
Maybe I can revisit them, or we can do it together to make it more robust?

[ibresslerBAM]

Thanks for your quick reply!

I tested the proposed push_to_openbis command but it seems to be designed for writing new definitions to OpenBIS which do not exist in bam-masterdata yet, because it complains about lots of already existing entities (obviously) if I use an Excel file created by export_to_excel first (which contains everything of bam-masterdata).

However, since I do not want to add new definitions to bam-masterdata (yet), I tried to provide an empty XLSX file to push_to_openbis. But it troubles it a bit, giving:

File "/home/ingo/code/datastore/bam-masterdata/bam_masterdata/cli/cli.py", line 542, in push_to_openbis
validation_results = checker.check(mode="individual")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ingo/code/datastore/bam-masterdata/bam_masterdata/checker/checker.py", line 85, in check
return validator.validate(mode)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ingo/code/datastore/bam-masterdata/bam_masterdata/checker/masterdata_validator.py", line 76, in validate
self._validate_model(self.new_entities)
File "/home/ingo/code/datastore/bam-masterdata/bam_masterdata/checker/masterdata_validator.py", line 223, in _validate_model
return entity_data
^^^^^^^^^^^
UnboundLocalError: cannot access local variable 'entity_data' where it is not associated with a value

While I'd love have a cmdline alternative to UI Excel import,, push_to_openbis does not seem to be designed/work (yet) for pushing bam-masterdata to an empty/default OpenBIS instance.

[JosePizarro3]

So push_to_openbis should work independently whether it is new or a modified entity. The workflow is to actually use the checker to check if some the entities exists or not, pass some messages to the logger (depending if the modifications of existing entities might be breaking stuff), and then push the changes if there are no errors.

@ibresslerBAM I will test this myself as well in my own openBIS instance and put up a fix so you can try.

Please, also note I won't be fixing the issues with the Excel, as this is a problem for the ETH and their import system.