Skip to content
Snippets Groups Projects
Select Git revision
  • b8feea22c9021b235ac3df935809b49c1a9fab12
  • main default protected
  • dev protected
  • f-fix-accent-sensitivity
  • f-filesystem-import
  • f-update-acl
  • f-filesystem-link
  • f-filesystem-directory
  • f-filesystem-core
  • f-filesystem-cleanup
  • f-string-ids
  • f-filesystem-main
  • f-multipart-encoding
  • f-trigger-advanced-user-tools
  • f-real-rename-test-pylibsolo2
  • f-real-rename-test-pylibsolo
  • f-real-rename-test
  • f-linkahead-rename
  • f-reference-record
  • f-xml-serialization
  • f-xfail-server-181
  • linkahead-pylib-v0.18.0
  • linkahead-control-v0.16.0
  • linkahead-pylib-v0.17.0
  • linkahead-mariadbbackend-v8.0.0
  • linkahead-server-v0.13.0
  • caosdb-pylib-v0.15.0
  • caosdb-pylib-v0.14.0
  • caosdb-pylib-v0.13.2
  • caosdb-server-v0.12.1
  • caosdb-pylib-v0.13.1
  • caosdb-pylib-v0.12.0
  • caosdb-server-v0.10.0
  • caosdb-pylib-v0.11.1
  • caosdb-pylib-v0.11.0
  • caosdb-server-v0.9.0
  • caosdb-pylib-v0.10.0
  • caosdb-server-v0.8.1
  • caosdb-pylib-v0.8.0
  • caosdb-server-v0.8.0
  • caosdb-pylib-v0.7.2
41 results

test_issues_server.py

Blame
  • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    test_issues_server.py 56.04 KiB
    # -*- coding: utf-8 -*-
    # This file is a part of the CaosDB Project.
    #
    # Copyright (c) 2020 - 2022 IndiScale GmbH <info@indiscale.com>
    # Copyright (c) 2022 Daniel Hornung <d.hornung@indiscale.com>
    # Copyright (c) 2020 Florian Spreckelsen <f.spreckelsen@indiscale.com>
    # Copyright (c) 2021 - 2022 Timm Fitschen <t.fitschen@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/>.
    
    """Tests for issues on gitlab.com, project caosdb-server."""
    
    import math
    import os
    import tempfile
    import time
    
    import caosdb as db
    import pytest
    
    from caosdb import administration as admin
    from caosdb.exceptions import (TransactionError, HTTPClientError)
    
    CURATOR_ROLE = "curator"
    
    
    def setup_module():
        db.configure_connection()
        try:
            db.execute_query("FIND ENTITY WITH ID > 99").delete()
        except Exception as delete_exc:
            print(delete_exc)
        try:
            admin._delete_user("TestUser")
        except Exception as delete_exc:
            print(delete_exc)
        try:
            admin._delete_role(CURATOR_ROLE)
        except Exception as delete_exc:
            print(delete_exc)
    
    
    def setup_function(function):
        """No setup required."""
        setup_module()
    
    
    def teardown_function(function):
        """Deleting entities again."""
        setup_module()
    
    
    # ########################### Issue tests start here #####################
    
    
    def test_issue_39():
        """Query Language Bug - quotes around year
    
        Test for https://gitlab.com/caosdb/caosdb-server/-/issues/39
        """
        date_str = "2020-01-01"
        prop_name = "Test_Date"
        db.Property(name=prop_name, datatype=db.DATETIME).insert()
        db.RecordType(name="Test_Type").add_property(name=prop_name).insert()
        db.Record(name="Test_Record").add_parent(name="Test_Type").add_property(
            name=prop_name, value=date_str).insert()
        # Quotes around years should work in the query
        ent = db.execute_query("FIND entity WITH A Test_Date IN \"2020\"",
                               unique=True)
        assert ent.get_property(prop_name).value == date_str
        # This should raise a transaction error
        with pytest.raises(TransactionError):
            db.execute_query("FIND entity WITH A Test_Date IN \"abcd\"")
    
    
    @pytest.mark.xfail(reason="to be fixed in server repo")
    def test_issue_62():
        """datatype is not changed when recordtype name changes
    
        Tests for https://gitlab.com/caosdb/caosdb-server/-/issues/62
        """
        db.RecordType(name="Test_RTA").insert()
        db.Property(name="Test_Prop", datatype="Test_RTA").insert()
        db.Record(name="Test_Record").add_parent(
            name="Test_RTA").add_property(name="Test_Prop").insert()
        # rename Test_RTA to Test_RTB
        rtb = db.execute_query("FIND RecordType Test_RTA", unique=True)
        rtb.name = "Test_RTB"
        rtb.update()
        # renaming has to be reflected in Test_Record and Test_Prop
        rec = db.execute_query("FIND Record Test_Record", unique=True)
        assert rec.parents[0].name == rtb.name
        assert rec.get_property("Test_Prop").datatype == rtb.name
        prop = db.execute_query("FIND Property Test_Prop", unique=True)
        assert prop.datatype == rtb.name  # fails; datatype not updated
        # Can't use Test_RTA as datatype anymore
        prop2 = db.Property(name="Test_Prop2", datatype="Test_RTA")
        with pytest.raises(TransactionError) as exc:
            prop2.insert()
        assert "Unknown data type." in str(exc.value)
    
    
    def test_issue_85_a():
        """SQLIntegrityConstraintViolationException for special inheritance patterns.
    
        Tests for https://gitlab.com/caosdb/caosdb-server/-/issues/85
        """
        A = db.RecordType(name="A")
        B = db.RecordType(name="B")
        C = db.RecordType(name="C")
    
        B.add_parent(A)
    
        # This order is important for the test to fail.
        C.add_parent(B)
        C.add_parent(C)
        C.add_parent(A)
    
        c = db.Container()
        # c.extend([C, B, A])  # worked before #86 was fixed
        # c.extend([C, A, B])  # worked before #86 was fixed
        c.extend([B, C, A])    # insert() failed before #86 was fixed
        c.insert()  # Raised java.sql.SQLIntegrityConstraintViolationException:
        #           # Duplicate entry '12345-12346-12345' for key 'PRIMARY'
    
    
    def test_issue_85_b():
        """SQLIntegrityConstraintViolationException for special inheritance patterns.
    
        Tests for https://gitlab.com/caosdb/caosdb-server/-/issues/85
        """
        A = db.RecordType(name="A")
        B = db.RecordType(name="B")
        C = db.RecordType(name="C")
        A.insert()
        B.insert()
        C.insert()
        B.add_parent(A)
        B.update()
        C.add_parent(B)
        C.update()
        C.add_parent(C)
        C.update()
        C.add_parent(A)
        C.update()  # Failed at this step
    
    
    @pytest.mark.local_server
    def test_issue_99():
        """Checksum updating failed with versioning enabled.
        """
    
        # Using files in extroot, because this allows us to update the file
        # content from the outside.
        local_dir = os.path.join(db.get_config().get("IntegrationTests",
                                                     "test_files.test_insert_files_in_dir.local"),
                                 "test_issue_99")
        docker_dir = os.path.join(db.get_config().get("IntegrationTests",
                                                      "test_files.test_insert_files_in_dir.server"),
                                  "test_issue_99")
        os.makedirs(local_dir, exist_ok=True)
        with tempfile.NamedTemporaryFile(dir=local_dir) as file_99:
            # Create File entity in CaosDB
            file_99.write("test 99\n".encode())
            os.fchmod(file_99.fileno(), 0o744)  # make the file world readable
            cont = db.Container()
            cont.insert(unique=False, raise_exception_on_error=False,
                        flags={"InsertFilesInDir": docker_dir})
            dbfile = cont[0]
    
            # Checksum should exist after a short time
            time.sleep(0.1)
            dbfile.retrieve()
            assert dbfile.checksum is not None
    
    
    def test_issue_110():
        """query ignores ID: FIND MusicalInstrument which is referenced by Analysis with ID=124 """
        cont = db.Container()
        A = db.RecordType(name="TypeA")
        B = db.RecordType(name="TypeB")
        prop = db.Property(name="prop_ba", datatype=db.REFERENCE)
    
        # Referenced Records
        a1 = db.Record().add_parent(A)
        a2 = db.Record().add_parent(A)
    
        # Referencing Records
        b1 = db.Record().add_parent(B).add_property(prop, value=a1)
        b2 = db.Record().add_parent(B).add_property(prop, value=a2)
    
        cont.extend([A, B, prop, a1, a2, b1, b2])
        cont.insert()
    
        id_b1 = b1.id
        query = "FIND TypeA WHICH IS REFERENCED BY TypeB WITH ID={}".format(id_b1)
        print(query)
        result = db.execute_query(query)
        print(result)
        assert len(result) == 1
        print(result[0])
        print(a1)
        assert result[0].id == a1.id
    
    
    def test_issue_120():
        """Editing entities that were created with a no longer existing user leads
        to a server error.
    
        The server should throw an error when CHECK_ENTITY_ACL_ROLES_MODE=MUST,
        otherwise a warning.
        """
        # insert an entity
        entity = db.RecordType("TestRT").insert(flags={"ACL": None})
    
        db.administration.set_server_property("CHECK_ENTITY_ACL_ROLES_MODE",
                                              "SHOULD")
        # update with non-existing user, realm and role
        entity.deny(
            realm="CaosDB",
            username="NON_EXISTING_USER",
            permission="USE:AS_REFERENCE")
        entity.update(flags={"ACL": None})
        assert entity.messages["Warning", 1104][0] == "User role does not exist."
    
        entity.deny(
            realm="NON_EXISTING_REALM",
            username="NON_EXISTING_USER",
            permission="USE:AS_REFERENCE")
        entity.update(flags={"ACL": None})
        assert entity.messages["Warning", 1104][0] == "User role does not exist."
    
        entity.deny(
            role="ALSO_NON_EXISTING_ROLE",
            permission="USE:AS_REFERENCE")
        entity.update(flags={"ACL": None})
        assert entity.messages["Warning", 1104][0] == "User role does not exist."
    
    
    def test_issue_134():
        """multiple white space characters after `FROM`"""
        db.execute_query("SELECT pname FROM  ename")
    
    
    def test_issue_131():
        """white space before unit with strange character"""
        rt = db.RecordType(name="TestType").insert()
        prop = db.Property(name="TestProp", datatype=db.INTEGER, unit="").insert()
        rec = db.Record(name="TestRecord").add_property(
            name=prop.name, value=101, unit="")
        rec.add_parent(rt)
        rec.insert()
        result_ids = [ent.id for ent in db.execute_query(
            "FIND Entity WITH {} > 100 €".format(prop.name))]
    
        assert rec.id in result_ids
    
        result_ids = [ent.id for ent in db.execute_query(
            "FIND Entity WITH {} > 100.5 €".format(prop.name))]
    
        assert rec.id in result_ids
    
    
    def test_issue_154_no_versioning():
        """ FIND MusicalInstrument WITH Manufacturer = "Antonio Stradivari" and
        FIND MusicalInstrument WITH Manufacturer != "Antonio Stradivari" """
        rt_man = db.RecordType("Manufacturer")
        rt_inst = db.RecordType("MusicalInstrument").add_property(rt_man)
    
        rec_man = db.Record("Antonio Stradivari").add_parent("Manufacturer")
        rec_man2 = db.Record("The other guy").add_parent("Manufacturer")
        rec_inst = db.Record("Violin").add_parent(
            "MusicalInstrument").add_property("Manufacturer", rec_man)
        rec_inst2 = db.Record("Guitar").add_parent(
            "MusicalInstrument").add_property("Manufacturer", rec_man2)
        rec_inst3 = db.Record("Broken Record").add_parent("MusicalInstrument")
    
        c = db.Container().extend([rt_man, rt_inst, rec_man, rec_inst, rec_man2,
                                   rec_inst2, rec_inst3]).insert()
    
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")]
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")]
        assert len(db.execute_query("FIND ENTITY MusicalInstrument")) == 4
        assert len(db.execute_query("FIND RECORD MusicalInstrument")) == 3
        assert len(db.execute_query(
            "FIND ENTITY MusicalInstrument WITH Manufacturer")) == 3
        assert len(db.execute_query(
            "FIND RECORD MusicalInstrument WITH Manufacturer")) == 2
        assert rec_inst.id == db.execute_query(
            "FIND ENTITY MusicalInstrument WITH Manufacturer = 'Antonio Stradivari'",
            unique=True).id
        assert len(db.execute_query(
            "FIND ENTITY MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 3
        assert len(db.execute_query(
            "FIND RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 2
        assert len(db.execute_query(
            "FIND ENTITY MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 1
        assert len(db.execute_query(
            "FIND RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 1
    
    
    def test_issue_154_with_versioning():
        """ FIND MusicalInstrument WITH Manufacturer = "Antonio Stradivari" and
        FIND MusicalInstrument WITH Manufacturer != "Antonio Stradivari" """
        rt_man = db.RecordType("Manufacturer")
        rt_inst = db.RecordType("MusicalInstrument").add_property(rt_man)
    
        rec_man = db.Record("Antonio Stradivari").add_parent("Manufacturer")
        rec_man2 = db.Record("The other guy").add_parent("Manufacturer")
        rec_inst = db.Record("Violin").add_parent(
            "MusicalInstrument").add_property("Manufacturer", rec_man)
        rec_inst2 = db.Record("Guitar").add_parent(
            "MusicalInstrument").add_property("Manufacturer", rec_man2)
        rec_inst3 = db.Record("Broken Record").add_parent("MusicalInstrument")
    
        db.Container().extend([rt_man, rt_inst, rec_man,
                               rec_inst, rec_man2, rec_inst2, rec_inst3]).insert()
    
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")]
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")]
        assert len(db.execute_query("FIND ANY VERSION OF ENTITY MusicalInstrument")) == 4
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument")) == 3
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer")) == 3
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer")) == 2
        assert rec_inst.id == db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer = 'Antonio Stradivari'",
            unique=True).id
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 3
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 2
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 1
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 1
    
        # now, some updates
        rt_man.description = "Updated Description"
        rt_inst.description = "Updated Description"
    
        rec_man.description = "Updated Description"
        rec_man2.description = "Updated Description"
        rec_inst.description = "Updated Description"
        rec_inst2.description = "Updated Description"
        rec_inst3.description = "Updated Description"
        db.Container().extend([rt_man, rt_inst, rec_man,
                               rec_inst, rec_man2, rec_inst2, rec_inst3]).update()
    
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")]
        assert "Violin" not in [e.name for e in db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")]
        assert len(db.execute_query("FIND ANY VERSION OF ENTITY MusicalInstrument")) == 8
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument")) == 6
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer")) == 6
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer")) == 4
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer = 'Antonio Stradivari'")) == 2
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 6
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH NOT Manufacturer = 'Antonio Stradivari'")) == 4
        assert len(db.execute_query(
            "FIND ANY VERSION OF ENTITY MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 2
        assert len(db.execute_query(
            "FIND ANY VERSION OF RECORD MusicalInstrument WITH Manufacturer != 'Antonio Stradivari'")) == 2
    
    
    def test_issue_127():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/127"""
        p = db.Property(
            name="TestDoubleProperty",
            datatype=db.LIST(
                db.DOUBLE)).insert()
        rt = db.RecordType(name="TestRecordType").add_property(name="TestDoubleProperty",
                                                               value=["nan"]).insert()
    
        test1 = db.execute_query("FIND ENTITY TestRecordType", unique=True)
        assert math.isnan(test1.get_property("TestDoubleProperty").value[0])
    
        test2 = db.execute_query(
            "FIND ENTITY TestRecordType WITH TestDoubleProperty = NaN", unique=True)
        assert math.isnan(test1.get_property("TestDoubleProperty").value[0])
    
    
    def test_issue_170():
        """update scalar data type to list data type"""
        p = db.Property(name="TestProp1", datatype=db.LIST(db.INTEGER))
        p.value = [1, 2]
        p.insert()
    
        p2 = db.execute_query("FIND ENTITY TestProp1", unique=True)
        assert p2.datatype == db.LIST(db.INTEGER)
        assert p2.value == [1, 2]
    
        p.description = "TestDescription"
        p.update()  # this failed
    
        p2 = db.execute_query("FIND ENTITY TestProp1", unique=True)
        assert p2.datatype == db.LIST(db.INTEGER)
        assert p2.value == [1, 2]
        assert p2.description == "TestDescription"
    
        p = db.Property(name="TestProp2", datatype=db.DOUBLE)
        p.insert()
    
        p.datatype = db.LIST(db.INTEGER)
        p.update()  # this worked because no value yet
        p2 = db.execute_query("FIND ENTITY TestProp2", unique=True)
        assert p2.datatype == db.LIST(db.INTEGER)
        p.value = [1, 2]
    
        p.update()  # this failed
        p2 = db.execute_query("FIND ENTITY TestProp2", unique=True)
        assert p2.datatype == db.LIST(db.INTEGER)
        assert p2.value == [1, 2]
    
        p = db.Property(name="TestProp3", datatype=db.DOUBLE)
        p.insert()
    
        p.datatype = db.LIST(db.INTEGER)
        p.value = [1, 2]
        p.update()  # this failed
    
        p2 = db.execute_query("FIND ENTITY TestProp3", unique=True)
        assert p2.datatype == db.LIST(db.INTEGER)
        assert p2.value == [1, 2]
    
    
    def test_issue_181():
        """https://gitlab.indiscale.com/caosdb/src/caosdb-server/-/issues/181"""
        rt = db.RecordType("TestRT").insert()
        assert len(db.execute_query("FIND RECORDTYPE TestRT")) == 1
        assert len(db.execute_query(
            "FIND RECORDTYPE TestRT WHICH HAS BEEN UPDATED TODAY")) == 0
        rt.description = "New description"
        rt.update()
        assert len(db.execute_query(
            "FIND RECORDTYPE TestRT WHICH HAS BEEN UPDATED TODAY")) == 1
    
    
    def test_issue_183():
        """No reasonable error when using bad datetime format.
        https://gitlab.indiscale.com/caosdb/src/caosdb-server/-/issues/183
        """
    
        # Date YYYY-MM-ddThh:mm:ss
        assert db.Property(name="TestDateTime", datatype=db.DATETIME,
                           value="2015-05-05T20:15:00").insert().id is not None
        with pytest.raises(db.TransactionError) as cm:
            # Date YYYY-MM-ddThh:mm
            db.Property(name="TestDateTime2", datatype=db.DATETIME,
                        value="2015-05-05T20:15").insert()
        assert cm.value.errors[0].msg == ("Cannot parse value to datetime format "
                                          "(yyyy-mm-dd'T'hh:mm:ss[.fffffffff][TimeZone]).")
    
    
    def test_issue_130():
        """Test select queries where names contain spaces
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/130
    
        However, this bug was actually about quotation marks
        """
        db.RecordType(name="TestRT_A").insert()
        r1 = db.Record("ReferencedRecord").add_parent("TestRT_A").insert()
        p1 = db.Property(name="TestWrapper", datatype="TestRT_A").insert()
        p2 = db.Property(
            name="TestWrapper With Spaces",
            datatype="TestRT_A").insert()
        db.RecordType(name="TestRT_B"
                      ).add_property(name="TestWrapper"
                                     ).add_property("TestWrapper With Spaces"
                                                    ).insert()
        db.Record().add_parent("TestRT_B"
                               ).add_property("TestWrapper", value=r1
                                              ).add_property("TestWrapper With Spaces",
                                                             value=r1
                                                             ).insert()
    
        query = "SELECT TestWrapper FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper"))
        assert row == [(r1.id,)]
    
        query = "SELECT TestWrapper FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper", "id"))
        assert row == [(p1.id,)]
    
        query = "SELECT 'TestWrapper' FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper", "id"))
        assert row == [(p1.id,)]
    
        query = "SELECT TestWrapper FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper", "name"))
        assert row == [("TestWrapper",)]
    
        query = "SELECT TestWrapper.name FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper", "name"))
        assert row == [("ReferencedRecord",)]
    
        query = "SELECT 'TestWrapper.name' FROM RECORD TestRT_B"
        rec = db.execute_query(query, unique=True)
        assert len(rec.properties) == 0
    
        query = "SELECT 'TestWrapper'.name FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(("TestWrapper", "name"))
        assert row == [("ReferencedRecord",)]
    
        query = "SELECT TestWrapper With Spaces FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces"))
        assert row == [(r1.id,)]
    
        query = "SELECT TestWrapper With Spaces FROM RECORD TestRT_B"
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces", "id"))
        assert row == [(p2.id,)]
    
        query = 'SELECT TestWrapper With Spaces FROM RECORD TestRT_B'
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces", "name"))
        assert row == [("TestWrapper With Spaces",)]
    
        query = 'SELECT "TestWrapper With Spaces" FROM RECORD TestRT_B'
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces", "name"))
        assert row == [("TestWrapper With Spaces",)]
    
        query = 'SELECT TestWrapper With Spaces.name FROM RECORD TestRT_B'
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces", "name"))
        assert row == [("ReferencedRecord",)]
    
        query = 'SELECT "TestWrapper With Spaces".name FROM RECORD TestRT_B'
        row = db.execute_query(query).get_property_values(
            ("TestWrapper With Spaces", "name"))
        assert row == [("ReferencedRecord",)]
    
    
    def test_issue_132():
        """Query: Parenthesis around subproperties.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/132
        """
        db.RecordType("TestRT").insert()
        db.RecordType("TestRT_Foo").insert()
        db.Property("TestP_Bar", datatype=db.TEXT).insert()
        db.Property("TestP_Baz", datatype=db.TEXT).insert()
    
        rt1 = db.Record().add_parent("TestRT_Foo").add_property(
            "TestP_Bar", "val1").add_property(
            "TestP_Baz", "the other baz").insert()
        rt2 = db.Record().add_parent("TestRT").add_property(
            "TestP_Baz", "val2").add_property(
            "TestRT_Foo", rt1).insert()
    
        query = "FIND RECORD TestRT_Foo"
        assert db.execute_query(query, unique=True).id == rt1.id
    
        query = "FIND RECORD TestRT"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH TestRT_Foo"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH TestRT_Foo.TestP_Bar"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH TestRT_Foo.TestP_Bar = val1"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH (TestRT_Foo.TestP_Bar = val1)"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH ( TestRT_Foo.TestP_Bar = val1 )"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH (TestRT_Foo.TestP_Bar = val1) AND TestP_Baz = val2"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH (TestRT_Foo WITH (TestP_Bar = val1 AND TestP_Baz = 'the other baz')) AND TestP_Baz = val2"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        query = "FIND RECORD TestRT WITH TestRT_Foo WITH (TestP_Bar = val1 AND TestP_Baz = 'the other baz') AND TestP_Baz = val2"
        assert db.execute_query(query, unique=True).id == rt2.id
    
        # this one has the wront scope of the conjunction.
        query = "FIND RECORD TestRT WITH TestRT_Foo.TestP_Bar = val1 AND TestP_Baz = 'the other one'"
        assert len(db.execute_query(query)) == 0
    
    
    def test_issue_217():
        """Server gets list property datatype wrong if description is updated."""
        # @review Florian Spreckelsen 2022-03-15
    
        rt = db.RecordType(name="TestRT").insert()
        # prop = db.Property(name="LP", datatype=db.LIST("TestRT")).insert()
        r = db.Record().add_parent(id=rt.id).add_property(name=rt.name,
                                                          datatype=db.LIST(
                                                              db.INTEGER),
                                                          value=[10001, 10002])
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
        r.insert()
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
        r.retrieve()
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
        r.retrieve(flags={"cache": "false"})
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
        r.description = "Changed description"
        r.update()
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
        # This line fails in the bug report with invalid XML.
        r.retrieve()
        assert r.get_property(rt.name).datatype == db.LIST(db.INTEGER)
        assert r.get_property(rt.name).value == [10001, 10002]
    
    
    def test_issue_217_2():
        """Server gets overridden name of property wrong when the description of record is being updated."""
        # @review Florian Spreckelsen 2022-03-15
    
        rt = db.RecordType(name="TestRT").insert()
        # prop = db.Property(name="LP", datatype=db.LIST("TestRT")).insert()
        overridden_name = "TestRT-overridden"
        r = db.Record().add_parent(id=rt.id).add_property(name=overridden_name,
                                                          id=rt.id)
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
        r.insert()
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
        r.retrieve()
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
        r.retrieve(flags={"cache": "false"})
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
        r.description = "Changed description"  # change description of the record
        r.update()
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
        r.retrieve()
        assert r.get_property(overridden_name) is not None
        assert r.get_property(overridden_name).id == rt.id
    
    
    def test_issue_217_3():
        """Server gets overridden description of property wrong when the description of record is being updated."""
        # @review Florian Spreckelsen 2022-03-15
    
        rt = db.RecordType(name="TestRT", description="Desc").insert()
        # prop = db.Property(name="LP", datatype=db.LIST("TestRT")).insert()
        r = db.Record().add_parent(id=rt.id).add_property(description="Desc-overridden",
                                                          id=rt.id)
        r.insert()
        assert r.get_property(rt.name).id == rt.id
        assert r.get_property(rt.name).description == "Desc-overridden"
        r.retrieve()
        assert r.get_property(rt.name).id == rt.id
        assert r.get_property(rt.name).description == "Desc-overridden"
        r.retrieve(flags={"cache": "false"})
        assert r.get_property(rt.name).id == rt.id
        assert r.get_property(rt.name).description == "Desc-overridden"
        r.description = "Changed description"  # change description of the record
        r.update()
        assert r.get_property(rt.name).id == rt.id
        assert r.get_property(rt.name).description == "Desc-overridden"
        r.retrieve()
        assert r.get_property(rt.name).id == rt.id
        assert r.get_property(rt.name).description == "Desc-overridden"
    
    
    def test_issue_221():
        """Unknown error during update of property leaving out datatype"""
    
        rt = db.RecordType("A")
        r = db.Record()
        r.add_parent(rt)
        p = db.Property(name="B", datatype=db.INTEGER)
        r.add_property(name="B", value=5)
        db.Container().extend([rt, r, p]).insert()
    
        r2 = db.Record(id=r.id).retrieve()
        r2.remove_property("B")
        r2.add_property(p, value=7)
        r2.update()
        assert r2.get_property("B").value == 7
        assert r2.get_property("B").datatype == db.INTEGER
        assert r2.get_property("B").id == p.id
    
    
    def test_134_1():
        """CQL: Subproperties are not recognized in list of references.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/134
        """
        p_lng = db.Property(name="longitude", datatype=db.DOUBLE).insert()
        p_lat = db.Property(name="latitude", datatype=db.DOUBLE).insert()
        rt_ev = db.RecordType(name="Event").add_property(
            p_lng).add_property(p_lat).insert()
        p_ev = db.Property(name="events", datatype=db.LIST(rt_ev)).insert()
        rt_ds = db.RecordType(name="DataSet").add_property(p_ev).insert()
    
        r_ev_1 = db.Record().add_parent("Event").add_property("longitude",
                                                              0.1).add_property("latitude",
                                                                                0.1).insert()
        r_ev_2 = db.Record().add_parent("Event").add_property("longitude",
                                                              0.2).add_property("latitude",
                                                                                0.2).insert()
    
        r_ds = db.Record().add_parent("DataSet").add_property(
            "events", value=[r_ev_1, r_ev_2]).insert()
    
        result = db.execute_query("SELECT events.latitude FROM RECORD DataSet",
                                  unique=True)
        print(result)
        assert len(result.get_property("events").value) == 2
        assert result.get_property("events").value[0].get_property(
            "latitude").value == 0.1
        assert result.get_property("events").value[1].get_property(
            "latitude").value == 0.2
    
    
    def test_134_2():
        """CQL: Subproperties are not recognized in list of references.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/134
        """
        p_lng = db.Property(name="longitude", datatype=db.DOUBLE).insert()
        p_lat = db.Property(name="latitude", datatype=db.DOUBLE).insert()
        rt_ev = db.RecordType(name="Event").add_property(
            p_lng).add_property(p_lat).insert()
        rt_ds = db.RecordType(name="DataSet").add_property(name="Event",
                                                           datatype=db.LIST(rt_ev)).insert()
    
        r_ev_1 = db.Record().add_parent("Event").add_property("longitude",
                                                              0.1).add_property("latitude",
                                                                                0.1).insert()
        r_ev_2 = db.Record().add_parent("Event").add_property("longitude",
                                                              0.2).add_property("latitude",
                                                                                0.2).insert()
    
        r_ds = db.Record().add_parent("DataSet").add_property(
            "Event", datatype=db.LIST(rt_ev), value=[r_ev_1, r_ev_2]).insert()
    
        result = db.execute_query("SELECT Event.latitude FROM RECORD DataSet",
                                  unique=True)
        assert len(result.get_property("Event").value) == 2
        assert result.get_property("Event").value[0].get_property(
            "latitude").value == 0.1
        assert result.get_property("Event").value[1].get_property(
            "latitude").value == 0.2
    
    
    def test_136():
        """Faulty creation of a multi-property when updating a non-list property
        with a list value.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/136
    
        """
        # @author Florian Spreckelsen
        # @date 2022-05-23
    
        # Insert data model:
        rt = db.RecordType(name="TestBug")
        p = db.Property(name="TestBugProperty", datatype=db.INTEGER)
        db.Container().extend([rt, p]).insert()
    
        # Insert test record:
        r = db.Record(name="TestRecord")
        r.add_parent(rt)
        r.add_property(p, value=18)
        r.insert()
    
        # Update the record:
        test_r = db.Record(id=r.id).retrieve()
        test_r.add_parent(rt)
        test_r.add_property(id=p.id, value=[18, 12])
        with pytest.raises(db.TransactionError) as err:
            test_r.update()
    
        te = err.value
        assert te.has_error(db.UnqualifiedPropertiesError)
        assert "This data type does not accept collections of values (e.g. Lists)" in str(te)
    
    
    @pytest.mark.xfail(reason="Fix https://gitlab.com/caosdb/caosdb-pylib/-/issues/81")
    def test_136_b():
        """Faulty creation of a multi-property when updating a non-list property
        with a list value.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/136
    
        """
        # @author Florian Spreckelsen
        # @date 2022-05-23
    
        # Insert data model:
        rt = db.RecordType(name="TestBug")
        p = db.Property(name="TestBugProperty", datatype=db.TEXT)
        db.Container().extend([rt, p]).insert()
    
        # Insert test record:
        r = db.Record(name="TestRecord")
        r.add_parent(rt)
        r.add_property(p, value="val1")
        r.insert()
    
        # Update the record:
        test_r = db.Record(id=r.id).retrieve()
        test_r.add_parent(rt)
        test_r.add_property(id=p.id, value=["val1", "val2"])
        with pytest.raises(db.TransactionError) as err:
            test_r.update()
    
        te = err.value
        assert te.has_error(db.UnqualifiedPropertiesError)
        assert "This data type does not accept collections of values (e.g. Lists)" in str(te)
    
    
    def test_141():
        """Roles with `Grant(*)P` permissions still can't update other people's
        entities."""
        admin._insert_role(name=CURATOR_ROLE, description="Desc")
    
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Grant", permission="*", priority=True)
        d = admin.PermissionRule(action="Deny", permission="*", priority=True)
        if g in perms:
            perms.remove(g)
        if d in perms:
            perms.remove(d)
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
        perms = admin._get_permissions(CURATOR_ROLE)
        print(perms)
    
        rt = db.RecordType(name="TestRT", description="Desc1").insert()
    
        admin._insert_user(name="TestUser", password="Password1!", status="ACTIVE")
        admin._set_roles(username="TestUser", roles=[CURATOR_ROLE])
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
        assert db.Info().user_info.name == "TestUser"
        assert db.Info().user_info.roles == [CURATOR_ROLE]
    
        rt.description = "Desc2"
        rt.update()
        assert rt.description == "Desc2"
    
        # switch back to admin user
        db.configure_connection()
        assert db.execute_query("FIND ENTITY TestRT", unique=True).description == "Desc2"
    
    
    def test_145():
        """Searching for large numbers results in wrong results if integer values
        are used.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/145
        """
        db.Property("TestProp", datatype=db.TEXT).insert()
        db.Property("TestPropInt", datatype=db.INTEGER).add_parent(
            "TestProp").insert()
        db.Property("TestPropDouble", datatype=db.DOUBLE).add_parent(
            "TestProp").insert()
    
        db.RecordType("TestRT").insert()
        rec1 = db.Record("TestRec1").add_parent("TestRT").add_property(
            "TestPropInt", 1_000_000_000).insert()
        assert rec1.get_property("TestPropInt").value == 1_000_000_000
        assert isinstance(rec1.get_property("TestPropInt").value, int)
        rec2 = db.Record("TestRec2").add_parent("TestRT").add_property(
            "TestPropDouble", 20_000_000_000).insert()
        assert rec2.get_property("TestPropDouble").value == 20_000_000_000
        assert isinstance(rec2.get_property("TestPropDouble").value, float)
    
        assert db.execute_query(
            "FIND TestRT WITH TestProp = 1000000000", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT WITH TestProp = 1000000000.0", unique=True).id == rec1.id
    
        assert db.execute_query(
            "FIND TestRT WITH TestProp > 1000000000", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT WITH TestProp > 1000000000.0", unique=True).id == rec2.id
    
        assert db.execute_query(
            "FIND TestRT WITH TestProp = 20000000000", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT WITH TestProp = 20000000000.0", unique=True).id == rec2.id
    
        assert db.execute_query(
            "FIND TestRT WITH TestProp < 20000000000", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT WITH TestProp < 20000000000.0", unique=True).id == rec1.id
    
        assert db.execute_query(
            "FIND TestRT WITH TestPropInt < 10000000000000000000000000000000000000000000000000000000000",
            unique=True).id == rec1.id
    
    
    @pytest.mark.xfail(reason="Fix https://gitlab.com/caosdb/caosdb-server/-/issues/147")
    def test_147():
        """Searching for integer numbers results in wrong results if floats are used.
    
        https://gitlab.com/caosdb/caosdb-server/-/issues/147
        """
        db.Property("TestProp", datatype=db.TEXT).insert()
        db.Property("TestPropInt", datatype=db.INTEGER).add_parent(
            "TestProp").insert()
    
        db.RecordType("TestRT1").insert()
        db.RecordType("TestRT2").insert()
        rec1 = db.Record("TestRec1").add_parent("TestRT1").add_property(
            "TestPropInt", 1).insert()
        assert rec1.get_property("TestPropInt").value == 1
        assert isinstance(rec1.get_property("TestPropInt").value, int)
        rec2 = db.Record("TestRec2").add_parent("TestRT2").add_property(
            "TestPropInt", -2).insert()
    
        # Find the records
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp < 1.9", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp < 1.1", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp = 1.0", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp > 0.9", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp > 0.1", unique=True).id == rec1.id
    
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp <= 1.9", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp <= 1.1", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp >= 0.9", unique=True).id == rec1.id
        assert db.execute_query(
            "FIND TestRT1 WITH TestProp >= 0.1", unique=True).id == rec1.id
    
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp < -1.1", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp < -1.9", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp = -2.0", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp > -2.1", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp > 2.9", unique=True).id == rec2.id
    
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp <= -1.1", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp <= -1.9", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp >= -2.1", unique=True).id == rec2.id
        assert db.execute_query(
            "FIND TestRT2 WITH TestProp >= 2.9", unique=True).id == rec2.id
    
        # Don't find the records
        assert len(db.execute_query("FIND TestRT1 WITH TestProp < 0.9")) == 0
        assert len(db.execute_query("FIND TestRT1 WITH TestProp <= 0.9")) == 0
        assert len(db.execute_query("FIND TestRT1 WITH TestProp > 1.1")) == 0
        assert len(db.execute_query("FIND TestRT1 WITH TestProp >= 1.1")) == 0
    
        assert len(db.execute_query("FIND TestRT2 WITH TestProp > -1.9")) == 0
        assert len(db.execute_query("FIND TestRT2 WITH TestProp >= -1.9")) == 0
        assert len(db.execute_query("FIND TestRT2 WITH TestProp < -2.1")) == 0
        assert len(db.execute_query("FIND TestRT2 WITH TestProp <= -2.1")) == 0
    
        # Smaller numbers, but querying across number types.
        rec3 = db.Record("TestRec3").add_parent(
            "TestRT").add_property("TestPropInt", 1).insert()
        assert db.execute_query(
            "FIND TestRT WITH TestPropInt < 2", unique=True).id == rec3.id
        assert db.execute_query(
            "FIND TestRT WITH TestPropInt < 2.5", unique=True).id == rec3.id
    
    
    def test_140():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/140"""
        admin._insert_role(name=CURATOR_ROLE, description="Desc")
    
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Grant", permission="TRANSACTION:*")
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
        admin._insert_user(name="TestUser", password="Password1!", status="ACTIVE")
        admin._set_roles(username="TestUser", roles=[CURATOR_ROLE])
    
        core_model_deny_permissions = [
            "DELETE",
            "UPDATE:*",
            "EDIT:ACL"
        ]
        core_model_grant_permissions = [
            "RETRIEVE:*",
            "USE:*",
            "UPDATE:PROPERTY:ADD"
        ]
    
        prop = db.Property(name="TestProp", datatype=db.TEXT).insert()
        rt = db.RecordType(name="TestRT").insert(flags={"ACL": None})
    
        for d in core_model_deny_permissions:
            # First deny s.th. later the "UPDATE:PROPERTY:ADD" permission can be granted explicitely
            rt.deny(role=CURATOR_ROLE, permission=d)
        rt.update_acl()
    
        # retrieve again to be sure
        rt.retrieve(flags={"ACL": None})
        for g in core_model_grant_permissions:
            rt.grant(role=CURATOR_ROLE, permission=g)
        rt.update_acl()
    
        print(rt.acl)
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
        assert db.Info().user_info.name == "TestUser"
        assert db.Info().user_info.roles == [CURATOR_ROLE]
    
        rt.add_property(prop)
        rt.get_property("TestProp").value = "some value"
    
        # this should succeed because the curator has UPDATE:PROPERTY:ADD
        rt.update()
    
        assert rt.get_property("TestProp").value == "some value"
        rt.get_property("TestProp").value = "some other value"
        with pytest.raises(TransactionError) as cm:
            # this should fail because the curator doesn't have
            # UPDATE:PROPERTY:REMOVE
            rt.update()
        assert cm.value.errors[0].msg == "You are not allowed to do this."
    
    
    def test_142():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/142"""
        valid_names = [
            "with.dot",
            "with-hyphen",
            "with_underbar",
            "with0number",
            "withAcapital",
            ".withleadingdot",
            "Bwithleadingcapital",
            "1withleadingnumber",
            "_withleadingunderbar",
            "withtrailingcapitalC",
            "withtrailingnumber2",
            "withtrailingunderbar_",
            "withtrailinghyphen-",
            "withtrailingdot.",
            "4",
            "_",
            "D",
            "d",
            ".",
        ]
        invalid_names = [
            "-",
            "-leadinghyphen",
            "",
            "%",
            "/",
            "[asdf]",
            "?",
            '"',
        ]
        for name in valid_names:
            admin._insert_user(
                name=name,
                password="Password1!",
                status="ACTIVE",
                email=None,
                entity=None)
            admin._delete_user(name=name)
        for name in invalid_names:
            with pytest.raises(HTTPClientError) as cm:
                admin._insert_user(
                    name=name,
                    password="Password1!",
                    status="ACTIVE",
                    email=None,
                    entity=None)
                admin._delete_user(name=name)
            assert cm.value.status == 400
            assert cm.value.msg.startswith(
                "The user name does not comply with the current policies for user names")
    
    
    @pytest.mark.xfail(reason="Fix https://gitlab.com/caosdb/caosdb-server/-/issues/177")
    def test_177():
        db.RecordType("TestRT").insert()
        db.RecordType("TestRT").insert(unique=False)
        db.Property("TestProp", datatype=db.TEXT).insert()
    
        db.RecordType("TestSubRT").add_property("TestProp").add_parent("TestRT").insert()
    
    
    @pytest.mark.xfail(reason="Fix https://gitlab.com/caosdb/caosdb-server/-/issues/135")
    def test_135():
        db.RecordType("TestRT1").insert()
        db.Property("TestProp", datatype=db.LIST("TestRT1")).insert()
        r1 = db.Record().add_parent("TestRT1").insert()
        r2 = db.Record().add_parent("TestRT1").add_property("TestProp", r1).insert()
        assert len(db.execute_query("FIND ENTITY WHICH IS REFERENCED BY A TestRT1 AS TestProp")) == 1
    
    
    def test_192():
        """Testing queries with Property by name.
    
        See https://gitlab.com/caosdb/caosdb-server/-/issues/192
    
        COUNT Record WHICH HAS price -> Results: 19
        COUNT Record WHICH HAS Property price -> Results: 19
        COUNT Record WITH price -> Results: 19
        COUNT Record WITH Property price -> Results: 0
        """
        db.Property(name="testprop", datatype=db.DOUBLE).insert()
        db.RecordType(name="TestRT").add_property("testprop").insert()
        db.Record(name="Rec1").add_parent("TestRT").add_property("testprop", value=3.1).insert()
    
        query1 = "COUNT RECORD WHICH HAS testprop"
        query2 = "COUNT RECORD WHICH HAS A testprop"
        query3 = "COUNT RECORD WHICH HAS Property testprop"
        query4 = "COUNT RECORD WHICH HAS A Property testprop"
        query5 = "COUNT RECORD WITH testprop"
        query6 = "COUNT RECORD WITH A testprop"
        query7 = "COUNT RECORD WITH Property testprop"
        query8 = "COUNT RECORD WITH A Property testprop"
    
        count1 = db.execute_query(query1)
        count2 = db.execute_query(query2)
        count3 = db.execute_query(query3)
        count4 = db.execute_query(query4)
        count5 = db.execute_query(query5)
        count6 = db.execute_query(query6)
        count7 = db.execute_query(query7)
        count8 = db.execute_query(query8)
    
        assert count1 == 1
        assert count2 == 1
        assert count3 == 1
        assert count4 == 1
        assert count5 == 1
        assert count6 == 1
        assert count7 == 1
        assert count8 == 1
    
    
    def test_196a():
        """See https://gitlab.com/caosdb/caosdb-server/-/issues/196"""
        admin._insert_role(name=CURATOR_ROLE, description="Desc")
    
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Grant", permission="TRANSACTION:*")
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
        admin._insert_user(name="TestUser", password="Password1!", status="ACTIVE")
        admin._set_roles(username="TestUser", roles=[CURATOR_ROLE])
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
        # works
        db.RecordType(name="TestRT1").insert()
        db.Property(name="TestProp1", datatype=db.TEXT).insert()
    
        # Deny TRANSACTION:INSERT:PROPERTY
        db.configure_connection()
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Deny", permission="TRANSACTION:INSERT:PROPERTY")
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
    
        # it is still allowed to insert a record type...
        db.RecordType(name="TestRT2").insert()
    
        # fails
        with pytest.raises(TransactionError) as cm:
            # this should fail because the curator doesn't have TRANSACTION:INSERT:PROPERTY
            db.Property(name="TestProp2", datatype=db.TEXT).insert()
        assert cm.value.errors[0].msg == "You are not allowed to do this."
    
    
    @pytest.mark.parametrize("deny", ["TRANSACTION:INSERT:", "TRANSACTION:INSERT:*"])
    def test_196b(deny):
        """Same as test_196a but we completely deny insertion."""
        admin._insert_role(name=CURATOR_ROLE, description="Desc")
    
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Grant", permission="TRANSACTION:*")
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
        admin._insert_user(name="TestUser", password="Password1!", status="ACTIVE")
        admin._set_roles(username="TestUser", roles=[CURATOR_ROLE])
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
        # works
        db.RecordType(name="TestRT1").insert()
        db.Property(name="TestProp1", datatype=db.TEXT).insert()
    
        # Deny TRANSACTION:INSERT
        db.configure_connection()
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Deny", permission=deny)
        perms.add(g)
        admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
    
        db.configure_connection(username="TestUser", password_method="plain",
                                password="Password1!")
    
        # fails (in contrast to test_196a)
        with pytest.raises(TransactionError) as cm:
            # this should fail because the curator doesn't have TRANSACTION:INSERT:RECORDTYPE
            db.RecordType(name="TestRT2").insert()
        assert cm.value.errors[0].msg == "You are not allowed to do this."
    
        # fails
        with pytest.raises(TransactionError) as cm:
            # this should fail because the curator doesn't have TRANSACTION:INSERT:PROPERTY
            db.Property(name="TestProp2", datatype=db.TEXT).insert()
        assert cm.value.errors[0].msg == "You are not allowed to do this."
    
    
    @pytest.mark.parametrize("num", ["1e+23", "5e22", "2e-323", "2E-323", "5E22", "1E+23", "+1E+23"])
    def test_143(num):
        """https://gitlab.com/caosdb/caosdb-server/-/issues/144"""
        db.Property(name="scientific_notation", datatype=db.DOUBLE).insert()
        db.RecordType(name="RT1").add_property("scientific_notation", value=num).insert()
    
        for query in [
            f"FIND RECORDTYPE RT1 WITH scientific_notation={num}",
            f"FIND RECORDTYPE RT1 WITH scientific_notation='{num}'",
            f"FIND RECORDTYPE RT1 WITH scientific_notation=\"{num}\"",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = {num}",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = '{num}'",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = \"{num}\""
        ]:
            db.execute_query(query, unique=True)
    
    
    @pytest.mark.parametrize("num", ["1 e+23", "- 5e22", "2e -323",
                                     "2E- 323", "5 E 22", "1 E+ 23", "+ 1"])
    def test_143_white_space(num):
        """https://gitlab.com/caosdb/caosdb-server/-/issues/144"""
    
        for query in [
            f"FIND RECORDTYPE RT1 WITH scientific_notation={num}",
            f"FIND RECORDTYPE RT1 WITH scientific_notation='{num}'",
            f"FIND RECORDTYPE RT1 WITH scientific_notation=\"{num}\"",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = {num}",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = '{num}'",
            f"FIND RECORDTYPE RT1 WITH scientific_notation = \"{num}\""
        ]:
            with pytest.raises(TransactionError) as cm:
                db.execute_query(query)
            assert cm.value.msg == f'You typed "{num}". Empty spaces are not allowed in numbers. Did you mean "{num.replace(" ", "")}"?'
    
    
    def test_144():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/144"""
        db.Property(name="scientific_notation", datatype=db.DOUBLE, value="1e23").insert()
    
        value = db.execute_query("FIND PROPERTY scientific_notation", unique=True).value
        assert str(value) == "1e+23"
        assert isinstance(value, float)
        assert value == 1e23
        assert value == 1e+23
    
    
    def test_166():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/166"""
        db.RecordType(name="exists").insert()
        db.Property(name="exists_property", datatype=db.INTEGER).insert()
    
        db.RecordType(name="RT1").add_parent("exists").insert()
        db.RecordType(name="RT2").add_parent("exists").add_property("exists_property", 32453).insert()
    
        with pytest.raises(TransactionError) as cm:
            db.Record(name="RT3").add_parent("notexists").insert()
        assert [e.msg for e in cm.value.errors] == ["Entity has unqualified parents."]
    
        with pytest.raises(TransactionError) as cm:
            db.Record(name="RT4").add_parent("exists").add_property("notexists", 234243).insert()
        assert [e.msg for e in cm.value.errors] == ["Entity has unqualified properties."]
    
        with pytest.raises(TransactionError) as cm:
            db.Record(
                name="RT5").add_parent("notexists").add_property(
                "exists_property",
                234243).insert()
        assert [e.msg for e in cm.value.errors] == ["Entity has unqualified parents."]
    
    
    @pytest.mark.xfail(reason="fix needed")
    def test_195():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/195"""
        admin._insert_role(name=CURATOR_ROLE, description="Desc")
    
        perms = admin._get_permissions(CURATOR_ROLE)
        g = admin.PermissionRule(action="Grant", permission="INVALID_PERMISSION:*")
        perms.add(g)
        with pytest.raises(Exception):
            admin._set_permissions(CURATOR_ROLE, permission_rules=perms)
    
    
    def test_216():
        """https://gitlab.com/caosdb/caosdb-server/-/issues/216"""
        p1 = db.Property(name='p1', datatype=db.DOUBLE).insert()
    
        cont = db.Container()
        cont.append(db.RecordType(name="A")
                    .add_property(id=p1.id, name=p1.name, datatype=db.DOUBLE,
                                  unit="min",
                                  importance=db.RECOMMENDED)
                    .add_property(id=p1.id, name=p1.name,
                                  importance=db.RECOMMENDED)
                    )
        cont.append(db.RecordType(name="B")
                    .add_parent(name="A", inheritance=db.SUGGESTED))
        cont.insert()
    
        assert db.execute_query("FIND RECORDTYPE B", unique=True).name == "B"
    
    
    def test_138():
        """Problems with non-integer ids in query filters, see
        https://gitlab.com/caosdb/caosdb-server/-/issues/138
    
        """
    
        queries = [
            "FIND ENTITY WITH ID={}",
            "FIND ENTITY WITH ID=None",
            "FIND ENTITY WITH ID=\"1 non-existing id\""
        ]
        for query in queries:
            # No error, but of course also no results.
            results = db.execute_query(query)
            assert len(results) == 0
    
    
    @pytest.mark.xfail(reason="Needs fix for parent name change caching, "
                       "see https://gitlab.com/caosdb/caosdb-server/-/issues/220")
    def test_220():
        """Caching of children is not removed.
    
    See https://gitlab.com/caosdb/caosdb-server/-/issues/220"""
        rectype = db.RecordType(name="OldName").insert()
        rec = db.Record(name="rec").add_parent(rectype).insert()
    
        query = db.Query("FIND rec")
        assert query.cached is None
    
        res_1 = query.execute(unique=True)
        assert query.cached is False, "First query should be uncached."
        assert res_1.id == rec.id
    
        res_2 = query.execute(unique=True)
        assert query.cached is True, "Second query should be cached."
    
        rectype.name = "NewName"
        rectype.update()
    
        res_3 = query.execute(unique=True)
        assert res_3.parents[0].name == rectype.name, \
            "The name of the record's parent should be up-to-date."
        assert query.cached is False, "Query after name change of parent should not be cached."
    
    
    @pytest.mark.xfail(reason="Needs fix for keeping datatype, "
                       "see https://gitlab.indiscale.com/caosdb/src/caosdb-server/-/issues/106")
    def test_indiscale_106():
        """Datatype of old properties is changed.
    
    See https://gitlab.indiscale.com/caosdb/src/caosdb-server/-/issues/106
        """
    
        # Create TEXT Property
        p = db.Property("prop", datatype=db.TEXT)
        p.insert()
    
        # Create Record using this Property
        db.RecordType("RT").insert()
        r = db.Record(name="rec")
        r.add_parent("RT")
        r.add_property(id=p.id, value="This is a TEXT property")
        r.insert()
    
        print(db.Record(id=r.id).retrieve())
        assert db.Record(id=r.id).retrieve().get_property("prop").datatype == db.TEXT
    
        # Changing Property's datatype to REFERENCE
        p.datatype = db.REFERENCE
        p.update()
    
        # Existing Property should still be reported as TEXT
        assert db.Record(id=r.id).retrieve().get_property("prop").datatype == db.TEXT
    
    
    @pytest.mark.xfail(reason="https://gitlab.com/caosdb/caosdb-server/-/issues/235")
    def test_235_long_name():
        """Should give an appropriate error, not just unknown server/-/issues."""
        length = 10256
        name = "N" * length
        rt1 = db.RecordType(name=name)
        try:
            rt1.insert()
        except Exception as exc:
            assert not isinstance(exc, db.HTTPServerError)
            # TODO more specific error should be asserted
    
        rt2 = db.RecordType(name="Short")
        rt2.insert()
        rt2.name = name
        try:
            rt2.update()
        except Exception as exc:
            assert not isinstance(exc, db.HTTPServerError)
            # TODO more specific error should be asserted