How to allow a django end-user to create their own table in a controlled and secured way?

Question

This is regarding: Django / Django ORM / POSTGRES

Goal: Allow an end-user to inherit from an existing model, create extra fields in it or create an entirely new model.

pseudo model code Example:

OriginalModel

name = "Main Model"
Value_1 = "First Value"

User created variation

parent = OriginalModel
name = "Custom Model"
Value_1 = "First Value"
Value_2 = "Custom Additional Value"

I tried:

test_choices = (
    ('str', 'str'),
    ('num', 'num'),
)


class App(models.Model):
    owner = models.ForeignKey(User, on_delete=models.CASCADE, related_name='users')
    name = models.CharField(max_length=100)


class AppField(models.Model):
    app = models.ForeignKey(App, on_delete=models.CASCADE)
    type = models.CharField(max_length=100, choices=test_choices, null=True)
    title = models.CharField(max_length=100, default='')

Problem How do you allow a user to create an AppItem that uses fields in AppField as model fields?

Do you know a better way of doing this whole thing?

Clarification comment: **Why would we do this?** a great use of this can be found in the app **[Podio][https://podio.com/]** it allows you to create and customize your own to-do list app [https://i.stack.imgur.com/kcPjC.png ] **then the end-user can simply add an app_item by filling out the fields like this**: [ https://i.stack.imgur.com/Te4s6.png ] Imagine a bunch of spreadsheets, a unique spreadsheet is an **App**, each column is an AppField, and each row is in AppItem — Sam, Dec 29 '18 at 17:35
I'm pretty sure that podio.com uses JSON to form "user tables" (see [JSONField](https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/fields/#jsonfield)) or something with a similar functionality. It would not be wise to allow users to mess with the basic database structure. You'd end up with a database that is a complete mess with thousands if not millions of tables of which most are probably stale / not in use. — Borut, Dec 29 '18 at 18:20

Borut · Accepted Answer · 2018-12-29T19:59:37.063

Here's how I'd start to tackle this challenge with the use of JSONField (and PostgreSQL). It's just to give you a basic idea and I tried to follow Django principles. First two simple models. One to store model definitions as defined by users and one to store data.

models.py

from django.db import models
from django.contrib.postgres.fields import JSONField
from django.utils.text import slugify

class ModelDefinition(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    verbose_name = models.CharField(max_length=50)
    name = models.CharField(max_length=50, unique=True)
    definition = JSONField()

    def save(self, *args, **kwargs):
        self.name = slugify(self.verbose_name)
        super().save(*args, **kwargs)

class Data(models.Model):
    model_definition = models.ForeignKey(ModelDefinition, on_delete=models.CASCADE)
    data = JSONField()

Below is an example how you'd add model definitions, add and return data. I didn't include data validation since it would have taken me at least an hour to write code for basic validation.

views.py

from .models import ModelDefinition, Data
from django.http import HttpResponse, JsonResponse

def create_model_definition(request):
    ''' Handles creation of model definitions '''

    model_definition = {
        'fields': [
            {
                'name': 'automobile',
                'verbose_name': 'Automobile',
                'data_type': 'string',
                'max_length': 50,
                'blank': False,
                'null': False
            },
            {
                'name': 'type',
                'verbose_name': 'Automobile type',
                'data_type': 'string',
                'max_length': 20,
                'blank': False,
                'null': False            
            },
            {
                'name': 'doors',
                'verbose_name': 'Number of doors',
                'data_type': 'integer',
                'blank': False,
                'null': False
            }
        ],
        'global_options': {
            'guest': {
                'verbose_name': 'Allow guests to enter data',
                'option': True
            },
            'public': {
                'verbose_name': 'Data is publicly accessible',
                'option': False
            }
        }

    }

    ModelDefinition.objects.create(
        user=request.user,
        verbose_name='My automobiles',
        definition=model_definition
    )

    return HttpResponse('model definition created')

def add_data(request, table='my-automobiles'):
    ''' Handles data entry  '''

    model_definition = get_object_or_404(ModelDefinition, user=request.user, name=table)
    if not request.user.is_authenticated and not model_definition.definition['global_options']['guest']['option']:
         return HttpResponse('Sorry, only authenticated users can enter data')

    data_rows = [
        {
            'automobile': 'Audi',
            'type': 'limousine',
            'doors': 4
        },
        {
            'automobile': 'Fiat',
            'type': 'hatchback',
            'doors': 3
        },
        {
            'automobile': 'Iveco',
            'type': 'truck',
            'doors': 2
        }      
    ]

    for row in data_rows:
        Data.objects.create(
            model_definition=model_definition,
            data=data
        )

    return HttpResponse('rows saved')

def show_data(request, table='my-automobiles'):
    ''' Returns data in JSON format '''

    model_definition = get_object_or_404(ModelDefinition, name=table)
    if not request.user.is_authenticated and not model_definition.definition['global_options']['public']['option']:
         return HttpResponse('Sorry, only authenticated users can view data')

    data = Data.objects.filter(model_definition__name=table)            
    return JsonResponse(data)

Well, that's how I'd start working on this, no idea what I'd end up with. Of course, I haven't tested any of this code :)

Thanks! I guess that would work I'm going to try this [ https://pastebin.com/bQnsSLEg ]; and store each row in a TestAppData table so that 2 users can edit 2 different App rows at the same time without overriding each other, but we'll have to declare columns on each TestAppData item — Sam, Dec 29 '18 at 23:00
Don't give up too soon on JSONField :) It's a perfect choice where dynamic structure is needed. — Borut, Dec 29 '18 at 23:05
and pretty much do it like this? https://stackoverflow.com/questions/36680691/updating-jsonfield-in-django-rest-framework — Sam, Dec 30 '18 at 04:17
@Sam, yes. Data from JSONField is converted to dictionary in Django. — Borut, Dec 30 '18 at 10:36

How to allow a django end-user to create their own table in a controlled and secured way?

1 Answers1