JPA TestCase for MultiTenancy


Andy
 

Some column is NULL on an SQL statement (INSERT/UPDATE) and shouldn't be. Only you can see the SQL statement issued that causes that SQL exception, and a simple look at the table DDL would reveal why that exception would be thrown


stephane
 

Hi,
 
As you can see on other topics, I'm facing some difficulties with MultiTenancy in JPA. I haven't diagnose yet why settings are not applied but I translated the JDO TestCase to JPA. 
These tests doesn't run with this error:
java.sql.BatchUpdateException: integrity constraint violation: NOT NULL check constraint; SYS_CT_10117 table: TENANTEDOBJECT column: TENANT
 
Can you have a look ? 

thanks
 

Here is a Patch for JPA general testsuite:


Index: jpa/general/src/java/META-INF/persistence.xml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/jpa/general/src/java/META-INF/persistence.xml b/jpa/general/src/java/META-INF/persistence.xml
--- a/jpa/general/src/java/META-INF/persistence.xml (revision 77f14e53f198cbf4cf728da2a88cc41188470547)
+++ b/jpa/general/src/java/META-INF/persistence.xml (date 1614931959201)
@@ -139,6 +139,7 @@
         <class>org.datanucleus.samples.typeconversion.ComplicatedType2</class>
         <class>org.datanucleus.samples.annotations.one_many.map_keyclass.MapHolderWithKeyClass</class>
         <class>org.datanucleus.samples.annotations.one_many.map_keyclass.MapKeyClassTarget</class>
+        <class>org.datanucleus.samples.jpa.multitenancy.TenantedObject</class>
     </persistence-unit>
 
 </persistence>
Index: jpa/general/src/java/org/datanucleus/samples/jpa/multitenancy/TenantedObject.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/jpa/general/src/java/org/datanucleus/samples/jpa/multitenancy/TenantedObject.java b/jpa/general/src/java/org/datanucleus/samples/jpa/multitenancy/TenantedObject.java
new file mode 100644
--- /dev/null (date 1614931840553)
+++ b/jpa/general/src/java/org/datanucleus/samples/jpa/multitenancy/TenantedObject.java (date 1614931840553)
@@ -0,0 +1,30 @@
+package org.datanucleus.samples.jpa.multitenancy;
+
+import org.datanucleus.api.jpa.annotations.MultiTenant;
+
+import javax.persistence.Entity;
+import javax.persistence.Id;
+
+@MultiTenant(column = "TENANT", columnLength = 24)
+public class TenantedObject {
+ @Id
+ long id;
+ String name;
+
+ public long getId() {
+ return this.id;
+ }
+
+ public void setId(long id) {
+ this.id = id;
+ }
+
+ public String getName() {
+ return this.name;
+ }
+
+ public void setName(String name) {
+ this.name = name;
+ }
+}
Index: jpa/general/src/test/org/datanucleus/tests/MultitenancyTest.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/jpa/general/src/test/org/datanucleus/tests/MultitenancyTest.java b/jpa/general/src/test/org/datanucleus/tests/MultitenancyTest.java
new file mode 100644
--- /dev/null (date 1614934980017)
+++ b/jpa/general/src/test/org/datanucleus/tests/MultitenancyTest.java (date 1614934980017)
@@ -0,0 +1,528 @@
+/**********************************************************************
+ Copyright (c) 2021 Andy Jefferson and others. All rights reserved.
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ Contributors:
+ ...
+ **********************************************************************/
+package org.datanucleus.tests;
+
+import org.datanucleus.PropertyNames;
+import org.datanucleus.api.jpa.NucleusJPAHelper;
+import org.datanucleus.samples.jpa.multitenancy.TenantedObject;
+
+import javax.persistence.EntityManager;
+import javax.persistence.EntityManagerFactory;
+import javax.persistence.EntityTransaction;
+import javax.validation.ConstraintViolationException;
+import java.util.Properties;
+
+/**
+ * Tests for multitenancy with JDO.
+ */
+public class MultitenancyTest extends JPAPersistenceTestCase {
+    private static boolean initialised = false;
+
+    /**
+     * Constructor.
+     *
+     * @param name Name of the test (not used)
+     */
+    public MultitenancyTest(String name) {
+        super(name);
+        if (!initialised)
+        {
+            addClassesToSchema(new Class[]{TenantedObject.class});
+            initialised = true;
+        }
+    }
+
+    /**
+     * Test basic tenantId specification via persistence property
+     */
+    public void testTenantId() {
+        Properties userProps = new Properties();
+        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+        EntityManagerFactory emfTenant1 = getEMF(1, "JPATest", userProps);
+        Properties userProps2 = new Properties();
+        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+        EntityManagerFactory emfTenant2 = getEMF(1, "JPATest", userProps2);
+        try
+        {
+            // Persist object for first tenant
+            EntityManager em1 = emfTenant1.createEntityManager();
+            EntityTransaction tx1 = em1.getTransaction();
+            Object id1 = null;
+            try
+            {
+                tx1.begin();
+                TenantedObject o1 = new TenantedObject();
+                o1.setId(1);
+                o1.setName("First");
+                em1.persist(o1);
+                tx1.commit();
+                id1 = o1.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Persist object for second tenant
+            EntityManager em2 = emfTenant2.createEntityManager();
+            EntityTransaction tx2 = em2.getTransaction();
+            Object id2 = null;
+            try
+            {
+                tx2.begin();
+                TenantedObject o2 = new TenantedObject();
+                o2.setId(2);
+                o2.setName("Second");
+                em2.persist(o2);
+                tx2.commit();
+                id2 = o2.getId();
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+            // Retrieve all objects for first tenant
+            em1 = emfTenant1.createEntityManager();
+            tx1 = em1.getTransaction();
+            try
+            {
+                tx1.begin();
+                // Check basic retrieve of expect object
+                TenantedObject obj1 = (TenantedObject) em1.find(TenantedObject.class, id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 = em1.find(TenantedObject.class, id2);
+                assertNull("Returned object 2 from tenant 1 but shouldn't have", obj2);
+                //                    JDOObjectNotFoundException
+                tx1.commit();
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Retrieve all objects for second tenant
+            em2 = emfTenant2.createEntityManager();
+            tx2 = em2.getTransaction();
+            try
+            {
+                tx2.begin();
+                // Check basic retrieve of expect object
+                TenantedObject obj2 = em2.find(TenantedObject.class, id2);
+                assertNotNull(obj2);
+                TenantedObject obj1 = em2.find(TenantedObject.class, id1);
+                assertNull("Returned object 1 from tenant 2 but shouldn't have", obj1);
+                tx2.commit();
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+        } finally
+        {
+            // Clear data
+            clean(emfTenant1, TenantedObject.class);
+            clean(emfTenant2, TenantedObject.class);
+            // Close PMFs
+            emfTenant1.close();
+            emfTenant2.close();
+        }
+    }
+    /**
+     * Test tenantId specification via MultitenancyProvider
+     */
+    // TODO
+
+    /**
+     * Test tenantReadIds specification via persistence property
+     */
+    public void testTenantIdWithReadIds() {
+        Properties userProps = new Properties();
+        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+        EntityManagerFactory pmfTenant1 = getEMF(1, "JPATest", userProps);
+        Properties userProps2 = new Properties();
+        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+        EntityManagerFactory pmfTenant2 = getEMF(1, "JPATest", userProps2);
+        try
+        {
+            // Persist object for first tenant
+            EntityManager em1 = pmfTenant1.createEntityManager();
+            EntityTransaction tx1 = em1.getTransaction();
+            Object id1 = null;
+            try
+            {
+                tx1.begin();
+                TenantedObject o1 = new TenantedObject();
+                o1.setId(1);
+                o1.setName("First");
+                em1.persist(o1);
+                tx1.commit();
+                id1 = o1.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Persist object for second tenant
+            EntityManager em2 = pmfTenant2.createEntityManager();
+            EntityTransaction tx2 = em2.getTransaction();
+            Object id2 = null;
+            try
+            {
+                tx2.begin();
+                TenantedObject o2 = new TenantedObject();
+                o2.setId(2);
+                o2.setName("Second");
+                em2.persist(o2);
+                tx2.commit();
+                id2 = o2.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+            // Retrieve all objects for first tenant
+            em1 = pmfTenant1.createEntityManager();
+            tx1 = em1.getTransaction();
+            try
+            {
+                tx1.begin();
+                // Check basic retrieve of expect objects
+                TenantedObject obj1 = em1.find(TenantedObject.class,id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 =  em1.find(TenantedObject.class,id2);
+                assertNotNull(obj2);
+                tx1.commit();
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Retrieve all objects for second tenant
+            em2 = pmfTenant2.createEntityManager();
+            tx2 = em2.getTransaction();
+            try
+            {
+                tx2.begin();
+                // Check basic retrieve of expect objects
+                TenantedObject obj1 = em2.find(TenantedObject.class,id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 = em2.find(TenantedObject.class,id2);
+                assertNotNull(obj2);
+                tx2.commit();
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+        } finally
+        {
+            // Clear data
+            clean(pmfTenant1, TenantedObject.class);
+            clean(pmfTenant2, TenantedObject.class);
+            // Close PMFs
+            pmfTenant1.close();
+            pmfTenant2.close();
+        }
+    }
+    /**
+     * Test tenantReadIds via EntityManager
+     */
+    public void testTenantIdViaEntityManager() {
+        Properties userProps = new Properties();
+//        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+        EntityManagerFactory emfTenant1 = getEMF(1, "JPATest", userProps);
+        Properties userProps2 = new Properties();
+//        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+        EntityManagerFactory emfTenant2 = getEMF(1, "JPATest", userProps2);
+        try
+        {
+            // Persist object for first tenant
+            EntityManager em1 = emfTenant1.createEntityManager();
+            em1.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+            EntityTransaction tx1 = em1.getTransaction();
+            Object id1 = null;
+            try
+            {
+                tx1.begin();
+                TenantedObject o1 = new TenantedObject();
+                o1.setId(1);
+                o1.setName("First");
+                em1.persist(o1);
+                tx1.commit();
+                id1 = o1.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Persist object for second tenant
+            EntityManager em2 = emfTenant2.createEntityManager();
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+            EntityTransaction tx2 = em2.getTransaction();
+            Object id2 = null;
+            try
+            {
+                tx2.begin();
+                TenantedObject o2 = new TenantedObject();
+                o2.setId(2);
+                o2.setName("Second");
+                em2.persist(o2);
+                tx2.commit();
+                id2 = o2.getId();
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+            // Retrieve all objects for first tenant
+            em1 = emfTenant1.createEntityManager();
+            em1.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+            tx1 = em1.getTransaction();
+            try
+            {
+                tx1.begin();
+                // Check basic retrieve of expect object
+                TenantedObject obj1 = (TenantedObject) em1.find(TenantedObject.class, id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 = em1.find(TenantedObject.class, id2);
+                assertNull("Returned object 2 from tenant 1 but shouldn't have", obj2);
+                //                    JDOObjectNotFoundException
+                tx1.commit();
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Retrieve all objects for second tenant
+            em2 = emfTenant2.createEntityManager();
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+            tx2 = em2.getTransaction();
+            try
+            {
+                tx2.begin();
+                // Check basic retrieve of expect object
+                TenantedObject obj2 = em2.find(TenantedObject.class, id2);
+                assertNotNull(obj2);
+                TenantedObject obj1 = em2.find(TenantedObject.class, id1);
+                assertNull("Returned object 1 from tenant 2 but shouldn't have", obj1);
+                tx2.commit();
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+        } finally
+        {
+            // Clear data
+            clean(emfTenant1, TenantedObject.class);
+            clean(emfTenant2, TenantedObject.class);
+            // Close PMFs
+            emfTenant1.close();
+            emfTenant2.close();
+        }
+    }
+    public void testTenantIdWithReadIdsViaEntityManager() {
+        Properties userProps = new Properties();
+//        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+//        userProps.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+        EntityManagerFactory pmfTenant1 = getEMF(1, "JPATest", userProps);
+        Properties userProps2 = new Properties();
+//        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+//        userProps2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+        EntityManagerFactory pmfTenant2 = getEMF(1, "JPATest", userProps2);
+        try
+        {
+            // Persist object for first tenant
+            EntityManager em1 = pmfTenant1.createEntityManager();
+                    em1.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+                   em1.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+            EntityTransaction tx1 = em1.getTransaction();
+            Object id1 = null;
+            try
+            {
+                tx1.begin();
+                TenantedObject o1 = new TenantedObject();
+                o1.setId(1);
+                o1.setName("First");
+                em1.persist(o1);
+                tx1.commit();
+                id1 = o1.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Persist object for second tenant
+            EntityManager em2 = pmfTenant2.createEntityManager();
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+            EntityTransaction tx2 = em2.getTransaction();
+            Object id2 = null;
+            try
+            {
+                tx2.begin();
+                TenantedObject o2 = new TenantedObject();
+                o2.setId(2);
+                o2.setName("Second");
+                em2.persist(o2);
+                tx2.commit();
+                id2 = o2.getId();
+                ;
+            } catch (ConstraintViolationException cve)
+            {
+                // expected
+                LOG.info("Exception correctly thrown : " + cve.getMessage());
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+            // Retrieve all objects for first tenant
+            em1 = pmfTenant1.createEntityManager();
+            em1.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID");
+            tx1 = em1.getTransaction();
+            try
+            {
+                tx1.begin();
+                // Check basic retrieve of expect objects
+                TenantedObject obj1 = em1.find(TenantedObject.class,id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 =  em1.find(TenantedObject.class,id2);
+                assertNotNull(obj2);
+                tx1.commit();
+            } finally
+            {
+                if (tx1.isActive())
+                {
+                    tx1.rollback();
+                }
+                em1.close();
+            }
+            // Retrieve all objects for second tenant
+            em2 = pmfTenant2.createEntityManager();
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_ID, "MyID2");
+            em2.setProperty(PropertyNames.PROPERTY_MAPPING_TENANT_READ_IDS, "MyID,MyID2");
+            tx2 = em2.getTransaction();
+            try
+            {
+                tx2.begin();
+                // Check basic retrieve of expect objects
+                TenantedObject obj1 = em2.find(TenantedObject.class,id1);
+                assertNotNull(obj1);
+                TenantedObject obj2 = em2.find(TenantedObject.class,id2);
+                assertNotNull(obj2);
+                tx2.commit();
+            } finally
+            {
+                if (tx2.isActive())
+                {
+                    tx2.rollback();
+                }
+                em2.close();
+            }
+        } finally
+        {
+            // Clear data
+            clean(pmfTenant1, TenantedObject.class);
+            clean(pmfTenant2, TenantedObject.class);
+            // Close PMFs
+            pmfTenant1.close();
+            pmfTenant2.close();
+        }
+    }
+    /**
+     * Test tenantReadIds specification via MultitenancyProvider
+     */
+    // TODO
+
+}
\ No newline at end of file
 
 
 
--
Stephane