ENH: xlsx template generator

Summary

Allows to create XLSX templates

Focus

Test Environment

unit test (including one for all dimr schemas which is to be removed)

Check List for the Author

Please, prepare your MR for a review. Be sure to write a summary and a focus and create gitlab comments for the reviewer. They should guide the reviewer through the changes, explain your changes and also point out open questions. For further good practices have a look at our review guidelines

• All automated tests pass
• Reference related issues
• Up-to-date CHANGELOG.md (or not necessary)
• Up-to-date JSON schema (or not necessary)
• Appropriate user and developer documentation (or not necessary)
  • How do I use the software? Assume "stupid" users.
  • How do I develop or debug the software? Assume novice developers.
• Annotations in code (Gitlab comments)
  • Intent of new code
  • Problems with old code
  • Why this implementation?

Check List for the Reviewer

• I understand the intent of this MR
• All automated tests pass
• Up-to-date CHANGELOG.md (or not necessary)
• Appropriate user and developer documentation (or not necessary)
• The test environment setup works and the intended behavior is reproducible in the test environment
• In-code documentation and comments are up-to-date.
• Check: Are there specifications? Are they satisfied?

For further good practices have a look at our review guidelines.

unittests/table_json_conversion/test_table_template_generator.py

+#!/usr/bin/env python3
+# encoding: utf-8
+#
+# This file is a part of the LinkAhead Project.
+#
+# Copyright (C) 2024 Indiscale GmbH <info@indiscale.com>
+# Copyright (C) 2024 Henrik tom Wörden <h.tomwoerden@indiscale.com>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <https://www.gnu.org/licenses/>.
+
+import json
+import os
+import tempfile
+
+import pytest
+from caosadvancedtools.table_json_conversion.table_generator import (
+    ColumnType, XLSXTemplateGenerator)
+from openpyxl import load_workbook
+
+
+def rfp(*pathcomponents):
+    """
+    Return full path.
+    Shorthand convenience function.
+    """
+    return os.path.join(os.path.dirname(__file__), *pathcomponents)
+
+
+def test_generate_sheets_from_schema():
+    # trivial case; we do not support this
+    schema = {}
+    generator = XLSXTemplateGenerator()
+    with pytest.raises(ValueError, match="Inappropriate JSON schema:.*"):
+        generator._generate_sheets_from_schema(schema)
+
+    # top level must be RT with Properties
+    schema = {
+        "type": "string"
+    }
+    with pytest.raises(ValueError, match="Inappropriate JSON schema:.*"):
+        generator._generate_sheets_from_schema(schema)
+
+    # bad type
+    schema = {
+        "type": "object",
+        "properties": {
+            "Training": {
+                "type": "object",
+                "properties": {
+                    "name": {
+                        "type": "str",
+                        "description": "The name of the Record to be created"
+                    },
+                }
+            }
+        }
+    }
+    with pytest.raises(ValueError,
+                       match="Inappropriate JSON schema: The following part "
+                       "should define an object.*"):
+        generator._generate_sheets_from_schema(schema, {'Training': ['a']})
+
+    # bad schema
+    schema = {
+        "type": "object",
+        "properties": {
+            "Training": {
+                "type": "object"
+            }
+        }
+    }
+    with pytest.raises(ValueError,
+                       match="Inappropriate JSON schema: The following part "
+                       "should define an object.*"):
+        generator._generate_sheets_from_schema(schema, {'Training': ['a']})
+
+    # minimal case: one RT with one P
+    schema = {
+        "type": "object",
+        "properties": {
+            "Training": {
+                "type": "object",
+                "properties": {
+                    "name": {
+                        "type": "string",
+                        "description": "The name of the Record to be created"
+                    },
+                }
+            }
+        }
+    }
+    sdef = generator._generate_sheets_from_schema(schema, {'Training': ['a']})
+    assert "Training" in sdef
+    tdef = sdef['Training']
+    assert 'name' in tdef
+    assert tdef['name'] == (ColumnType.SCALAR, "The name of the Record to be created", ["Training", 'name'])
+
+    # example case
+    with open(rfp("model_schema.json")) as sfi:
+        schema = json.load(sfi)
+    with pytest.raises(ValueError, match="A foreign key definition is missing.*"):
+        generator._generate_sheets_from_schema(schema)
+    sdef = generator._generate_sheets_from_schema(
+        schema,
+        foreign_keys={'Training': {"__this__": ['date', 'url']}})
+    assert "Training" in sdef
+    tdef = sdef['Training']
+    assert tdef['date'] == (ColumnType.SCALAR, 'The date of the training.', ["Training", 'date'])
+    assert tdef['url'] == (ColumnType.SCALAR, 'The URL', ["Training", 'url'])
+    assert tdef['supervisor.family_name'] == (ColumnType.SCALAR, None, ["Training", 'supervisor',
+                                                                        'family_name'])
+    assert tdef['supervisor.given_name'] == (ColumnType.SCALAR, None, ["Training", 'supervisor',
+                                                                       'given_name'])
+    assert tdef['supervisor.Organisation'] == (ColumnType.SCALAR, None, ["Training", 'supervisor',
+                                                                         'Organisation'])
+    assert tdef['duration'] == (ColumnType.SCALAR, None, ["Training", 'duration'])
+    assert tdef['participants'] == (ColumnType.SCALAR, None, ["Training", 'participants'])
+    assert tdef['subjects'] == (ColumnType.LIST, None, ["Training", 'subjects'])
+    assert tdef['remote'] == (ColumnType.SCALAR, None, ["Training", 'remote'])
+    cdef = sdef['Training.coach']
+    assert cdef['family_name'] == (ColumnType.SCALAR, None, ["Training", 'coach', 'family_name'])
+    assert cdef['given_name'] == (ColumnType.SCALAR, None, ["Training", 'coach', 'given_name'])
+    assert cdef['Organisation'] == (ColumnType.SCALAR, None, ["Training", 'coach',
+                                                              'Organisation'])
+    assert cdef['date'] == (ColumnType.FOREIGN, "see sheet 'Training'", ["Training", 'date'])
+    assert cdef['url'] == (ColumnType.FOREIGN, "see sheet 'Training'", ["Training", 'url'])
+
+
+def test_get_foreign_keys():
+    generator = XLSXTemplateGenerator()
+    fkd = {"Training": ['a']}
+    assert ['a'] == generator._get_foreign_keys(fkd, ['Training'])
+
+    fkd = {"Training": {"__this__": ['a']}}
+    assert ['a'] == generator._get_foreign_keys(fkd, ['Training'])
+
+    fkd = {"Training": {'hallo'}}
+    with pytest.raises(ValueError, match=r"A foreign key definition is missing for path:\n\["
+                       r"'Training'\]\nKeys are:\n{'Training': \{'hallo'\}\}"):
+        generator._get_foreign_keys(fkd, ['Training'])
+
+    fkd = {"Training": {"__this__": ['a'], 'b': ['c']}}
+    assert ['c'] == generator._get_foreign_keys(fkd, ['Training', 'b'])
+
+    with pytest.raises(ValueError, match=r"A foreign key definition is missing for .*"):
+        generator._get_foreign_keys({}, ['Training'])
+
+
+def test_get_max_path_length():
+    assert 4 == XLSXTemplateGenerator._get_max_path_length({'a': (1, 'desc', [1, 2, 3]),
+                                                            'b': (2, 'desc', [1, 2, 3, 4])})
+
+
+def test_template_generator():
+    generator = XLSXTemplateGenerator()
+    with open(rfp("model_schema.json")) as sfi:
+        schema = json.load(sfi)
+    path = os.path.join(tempfile.mkdtemp(), 'test.xlsx')
+    assert not os.path.exists(path)
+    generator.generate(schema=schema,
+                       foreign_keys={'Training': {"__this__": ['date', 'url']}},
+                       filepath=path)
+    assert os.path.exists(path)
+    generated = load_workbook(path)  # workbook can be read
+    example = load_workbook(rfp("example_template.xlsx"))
+    assert generated.sheetnames == example.sheetnames
+    for sheetname in example.sheetnames:
+        gen_sheet = generated[sheetname]
+        ex_sheet = example[sheetname]
+        for irow, (erow, grow) in enumerate(zip(ex_sheet.iter_rows(), gen_sheet.iter_rows())):
+            assert ex_sheet.row_dimensions[irow].hidden == gen_sheet.row_dimensions[irow].hidden
+            for icol, (ecol, gcol) in enumerate(zip(erow, grow)):
+                assert (ex_sheet.column_dimensions[ecol.column_letter].hidden
+                        == gen_sheet.column_dimensions[ecol.column_letter].hidden)
+                cell = gen_sheet.cell(irow+1, icol+1)
+                assert ecol.value == gcol.value, f"Sheet: {sheetname}, cell: {cell.coordinate}"
+
+    # test some hidden
+    ws = generated.active
+    assert ws.row_dimensions[1].hidden is True
+    assert ws.column_dimensions['A'].hidden is True
+
+    # TODO: remove the following after manual testing
+    di = '/home/professional/CaosDB/management/external/dimr/eingabemaske/crawler/schemas'
+    if not os.path.exists(di):
+        return
+    for fi in os.listdir(di):
+        rp = os.path.join(di, fi)
+        with open(rp) as sfi:
+            schema = json.load(sfi)
+        fk_path = os.path.join(di, "foreign_keys"+fi[len('schema'):])
+        if not os.path.exists(fk_path):
+            print(f"No foreign keys file for:\n{rp}")
+            continue
+        with open(fk_path) as sfi:
+            fk = json.load(sfi)
+        generator.generate(schema=schema, foreign_keys=fk, filepath=path)
+        os.system(f'libreoffice {path}')
+
+    # TODO test collisions of sheet or colnames
+    # TODO test escaping of values
+
+    # TODO finish enum example