hugobessa / django-shared-schema-tenants Goto Github PK

The dependencies by now are Django 1.11, django rest framework and django model utils

Due to some misconfiguration, Travis build is broken. Any new code will break on the CI. I'll probably have some time to investigate this after July.

I looked through the source code and I don't see anything that uses any of the view URLs by their names. Sometimes the Tenant system is going to be abstracted heavily away from end users, in this sort of case, I don't see a lot of use for having URLs that show details about any of the Tenant model instances, even to the users that are assigned to the Tenants. In this scenario the easy way is to just not use the provided URLs, I'm just not 100% sure this won't break something I spotted yet.

TL;DR: Are the the provided Tenant Views and their URLs ( shared_schema_tenants.urls.* ) optional?

When we do filters with related fields, the database is making queries considering the whole related table, instead of only the portion of data from the current tenant. This makes queries very slow when there're many tenants.

One solution is implicitly changing the queryset to filter by the related table tenant too.

Eg.:

MyModel.objects.filter(my_related_field__field1=value1, my_related_field__field2=value2)

sql is something like:

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
    INNER JOIN "my_app_myrelatedmodel"
    ON "my_app_myrelatedmodel"."mymodel_id" = "my_app_mymodel"."id"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id AND
    "my_app_myrelatedmodel"."field1" = value1 AND
    "my_app_myrelatedmodel"."field2" = value2

If we add the related field tenant's filter like this we solve the problem

MyModel.objects.filter(
    my_related_field__tenant_id=tenant_id, my_related_field__field1=value1, 
    my_related_field__field2=value2)

the sql with the fix should something like:

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
    INNER JOIN "my_app_myrelatedmodel"
    ON "my_app_myrelatedmodel"."mymodel_id" = "my_app_mymodel"."id"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id AND
    "my_app_myrelatedmodel"."tenant_id" = tenant_id AND
    "my_app_myrelatedmodel"."field1" = value1 AND
    "my_app_myrelatedmodel"."field2" = value2

For excludes the situation is even worse because it always uses NOT IN operator and subqueries.

Eg.:

MyModel.objects.exclude(my_related_field__field1=value1, my_related_field__field2=value2)

sql is something like:

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE "my_app_myrelatedmodel"."field1" = value1
    ) AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE "my_app_myrelatedmodel"."field2" = value2
    );

If we use the same strategy as in filter the result would be something like

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE  
            "my_app_myrelatedmodel"."tenant_id" = tenant_id
    ) AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE 
            "my_app_myrelatedmodel"."field1" = value1
    ) AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE  
            "my_app_myrelatedmodel"."field2" = value2
    );

For it to be optimized, the generated code should be something like this:

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE 
            "my_app_myrelatedmodel"."tenant_id" = tenant_id AND 
            "my_app_myrelatedmodel"."field1" = value1
    ) AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE  
            "my_app_myrelatedmodel"."tenant_id" = tenant_id AND 
            "my_app_myrelatedmodel"."field2" = value2
    );

The only way I could generate something that looks like the expected result was:

MyModel.objects.exclude(
    my_related__id__in=MyRelatedModel.objects.filter(field__field1=value1, field2=value2))

The resulting sql looks like this:

SELECT "my_app_mymodel"."field1", "my_app_mymodel"."field2"
FROM "my_app_mymodel"
WHERE
    "my_app_mymodel"."tenant_id" = tenant_id AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE 
            "my_app_myrelatedmodel"."tenant_id" = tenant_id AND 
            "my_app_myrelatedmodel"."field1" = value1
    ) AND
    "my_app_mymodel"."my_related_field_id" NOT IN (
        SELECT "my_app_myrelatedmodel"."field1", "my_app_myrelatedmodel"."field2" 
        FROM "my_app_myrelatedmodel"
        WHERE "my_app_myrelatedmodel"."id" IN (
            SELECT "my_app_myrelatedmodel"."id" 
            FROM "my_app_myrelatedmodel"
            WHERE  
                "my_app_myrelatedmodel"."tenant_id" = tenant_id AND 
                "my_app_myrelatedmodel"."field2" = value2
        )
    );

There's only a create_relationship method. We could add:

• add_permissions_on_current_tenant(user, perms)
• remove_permissions_on_current_tenant(user, perms)
• add_groups_on_current_tenant(user, groups)
• remove_groups_on_current_tenant(user, groups)
• remove_user_from_current_tenant(user)

Description

Today, if you want to add new table fields to a tenant you will have to add this field to all tenants. Would be good if we could add specific fields only to some tenants.

Proposed solution

1. Hard coded of tenant specific fields schemas: A meta class attribute that receives a dict with the tenant slugs pointing to its respective specific fields schema for the model. You also would have to add the CodebaseSchemaSpecificFieldsMixin to the model class.

2. Client customizable tenant specific fields schemas: Creation of this two tables to store the specific fields metadata:

   TenantSpecificField
   - tenant: foreign key to a tenant, 
   - content_type: foreign key to django ContentType table
   - name: char field, 
   - data_type: choices char field
   - is_required: boolean, 
   - default_value: text_field, 
   - validators: many to many field
   

   TenantSpecificFieldsValidator
   - module_path: string
   - tenants: many to many field to tenants
   

   You would have to add the DatabaseSchemaSpecificFieldsMixin to the model class. It would add a JSONField (or TextField if JSONField is not supported by the database) in the original table where we should have the specific fields values.

3. Client customizable tenant specific tables: Creation of this three tables to store the specific tables and its fields metadata:

   TenantSpecificTable
   - tenant: foreign key to a tenant,
   - name: char field,
   

   TenantSpecificTableField
   - tenant: foreign key to a tenant, 
   - tenant_specific_table: foreign key to TenantSpecificTable
   - name: char field, 
   - data_type: choices char field
   - is_required: boolean, 
   - default_value: text_field, 
   - validators: many to many field
   

   TenantSpecificFieldsValidator
   - module_path: string
   - tenants: many to many field to tenants
   

Other requirements

• Some helpers to validate, create and update these specific fields.
• Some REST API endpoints to list/create/retrieve/update/delete TenantSpecificTable and its fields
• Some REST API endpoints to list/create/retrieve/update/delete TenantSpecificField
• Some generic form class to handle models with specific fields like ModelForm does
• Some generic serializer class to handle models with specific fields like ModelSerializer does
• A form to handle TenantSpecificTables data create/update
• A serializer and view to handle TenantSpecificTables data

Eg.: https://github.com/filipeximenes/multitenancy/blob/master/multitenancy/threadlocal.py

• Django Shared Schema Tenants version: 0.1.1
• Django version: 3.2.7
• Python version: 3.8.10
• Operating System: Ubuntu

Description

Tried to browse the admin to create a tenant

This is already implemented but needs documentation