Re: How to separate data having the same model but belonging to different users?

[Mayukh Mukherjee]

As I understand it: (And im fairly new to django too)

A model corresponds to a single table (not multiple).
The question to you is what is different between User1 and User2 that you need different tables?

On Fri, Sep 21, 2012 at 1:35 PM, Rohit Banga <iamroh...@gmail.com> wrote:

Hi

I am a django #n00b. I came across the django model documentation and found it pretty interesting. (https://docs.djangoproject.com/en/dev/topics/db/models/).

Now my usecase requires I have a set of Models and each model has multiple tables corresponding to it.

For example when user1 registers I can create a table user1_t1, user1_t2, user1_t3.

When user2 registers I can create a table user2_t1, user2_t2, user2_t3.

I really like the Model abstraction but can't find a way to create these multiple tables conveniently without creating new models. I could not find clear solutions to this on the internet.

I just want clear separation between t1, t2, t3 for the all users. Depending on the logged in user, I want to use the relevant table. What is the cleanest way to achieve this with Django?

If it is not possible to do this with tables I can think about different databases one for each user with the same set of tables. Is it possible to do the same with multiple databases?

Thanks
Rohit Banga

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/tl8qKhJb-_cJ.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

--
Mayukh Mukherjee
http://www.linkedin.com/in/mayukhmmukherjee

[Rohit Banga]

Just HAVE to separate data - requirement.

[Nikolas Stevenson-Molnar]

If I understand correctly, what you really need is object (or row) level permissions. It doesn't make a lot of sense to create a table for each user, especially when the data model is exactly the same (and could get you a huge, messy database really quick). Rather, what you want to control is the access to that data. E.g., all data is stored in the same table, but user 1 can access objects (rows) 1, 2, 3 but not 4, 5, 6 and user 2 can access 4, 5, 6, but not 1, 2, 3.

Maybe take a look at http://pypi.python.org/pypi/django-object-permissions. I haven't used it, but it seems to address this issue. Or roll your own object permission model. It's conceptually quite simple: create a permissions model/table with a foreign key relationship to your other model. The permission model should record the user, the object, and (optionally) what level of permission they have to that object (read,write,etc.)

_Nik

[Nikolas Stevenson-Molnar]

If you absolutely have to use separate tables per user (again, I do not recommend this), then you'll need to implement some form of dynamic models (models which can be constructed at run-time rather than needing to be defined in the application code) such as discussed here: https://code.djangoproject.com/wiki/DynamicModels (see link at the top of the page for newer approaches and full implementations of dynamic models).

_Nik

On 9/21/2012 11:07 AM, Rohit Banga wrote:

[Joel Goldstick]

Why not add a user as a field in your models. Then, when the user
logs in, make sure the queries filter only that user's data

--
Joel Goldstick

[Rohit Banga]

Thanks for your comments. I agree that technically it is feasible to achieve the same affect with row level permissions or filtering the rows by user.

The requirement is to keep the data separate using different tables, databases while still using the same model. May be user is not the right metaphor to use here. Lets just "imagine" that we have the same schema to use for different departments in a college but one department does not want to house their data in the same tables as another department.

I don't want to filter rows by "userid" since one place we forget the filter in the code and there is an unauthorized data access. 

I will look into dynamic models though I am not sure if it is well supported.

What about routing to different databases based on user id?

Thanks

Rohit Banga
http://iamrohitbanga.com/

[Rohit Banga]

I just saw this example:

http://django.readthedocs.org/en/1.4/topics/db/models.html#multi-table-inheritance

Since it is possible for me to have a few number of users (now called departments), I can define a create a python file which subclasses all the models and then run syncdb to update the database for creating the new tables.

But what is different is I need to fetch the Place subclass (eg. Restaurant or School page above) at runtime based on the logged in user. Assuming I have a map from id to class name can I just load it dynamically?

Thanks

Rohit Banga
http://iamrohitbanga.com/

[Joel Goldstick]

Look here: https://docs.djangoproject.com/en/dev/topics/db/models/#model-inheritance

You can create your model as an abstract model. Then subclass it for
each department. Since you say your model will be identical, there is
really no code to write except to create each subclassed model. Run
syncdb and you will see all of your department models.

--
Joel Goldstick

[Nikolas Stevenson-Molnar]

I would still argue that the best solution is to use a robust permissions model which would preclude this. Wherever there is code, you invariably have the potential for security flaws. The more complicated you make that code, the more chances for mistakes. On the other hand, simpler code with well-defined methods for data access (e.g., maybe you never use MyModel.objects, but rather have a custom function for filtering objects based on permissions constraints; then you only have to ensure security in one place) make for fewer mistakes and a code base which is easier to maintain.

_Nik

[Rohit Banga]

Sounds good Joel... Thanks.

What if I want to dynamically create the object of the subclass so that I don't have to duplicate the code.

If I have

class Department(models.Model):

    someField =...

class PhysicsDepartment(Department):

     pass

Then how do I instantiate the objects dynamically so I do not have to do

Department.objects.filter()

PhysicsDepartment.objects.filter()

I want to share the same code for all departments. Can I instantiate them dynamically given that I have the string name and the module name available in a dictionary.

Thanks

Rohit Banga
http://iamrohitbanga.com/

--
Joel Goldstick

[Rohit Banga]

Sure Nikolas I will reconsider your solution.

In case I go for model inheritance then can I use the following solution to load the class dynamically?

mod = __import__('mysite.departments', fromlist=[form.getDepartment()])


klass = getattr(mod, 'form.getDepartment()')

Thanks

Rohit Banga
http://iamrohitbanga.com/

[Nikolas Stevenson-Molnar]

If I understand correctly, that's not the type of dynamic loading you need. That statement can be the much simpler:

>>> from mysite.departments.form import getDepartment

Rather, if you need models (tables) mapped to users at runtime, you need to load the those dynamically (normally you would define the model in your code, which includes--either implicitly or explicitly--the table name).

At the risk of sounding like a broken record: simpler is better, and multiple, dynamically loaded models with the same schema is not simple ;)

_Nik

[Rohit Banga]

Thanks Nikolas. I think my example was not clear.

But all the code is shared between the departments (another reason for you to say use the same tables!).

I do not need to have the explicit department name in the code. I don't know the name of the departments yet! (It is just a metaphor)
I just want to add behavior like PhysicsDepartment.objects.filter() or create(), save()  anywhere I want.

I want to work with the base class while loading the data from the subclass at runtime. Simple polymorphism but with different database tables in the backend.

Thanks

Rohit Banga
http://iamrohitbanga.com/

[Rohit Banga]

Hi Dennis

Thanks for summarizing the contents of the mails.

Do you foresee any problems with the Model Inheritance scheme? At my end I am stuck at getting a way to achieve dynamic polymorphism - which is probably because I am new to Python.

I can create a new subclass for every Department I add. manage.py syncdb creates the new table for every new department I add. Now I want to write the application logic agnostic of the specific department. I want to load the appropriate subclass dynamically at runtime. I haven't been able to do this right now.

I can afford to create a new subclass everytime and run manage.py syndb after that but cannot rewrite all the application logic all over again.

I feel given the subclass name there should be an easy way in python to get the subclass itself which I should use for the current user.

At this point I should also mention there are a set of about 5-6 tables (department being just one example) that I need to replicate for each new department. I am thinking of doing it all via subclassing. That is I would need to load as many subclass objects dynamically given the name of the department.

If I can get the set of subclasses to use with a not too ugly looking code is it still a terrible idea?

Thanks

Rohit Banga
http://iamrohitbanga.com/

On Fri, Sep 21, 2012 at 9:34 PM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:

On Fri, 21 Sep 2012 17:54:06 -0400, Rohit Banga

<iamroh...@gmail.com> declaimed the following in
gmane.comp.python.django.user:

> Thanks Nikolas. I think my example was not clear.
>
> But all the code is shared between the departments (another reason for you
> to say use the same tables!).
> I do not need to have the explicit department name in the code. I don't
> know the name of the departments yet! (It is just a metaphor)
> I just want to add behavior like PhysicsDepartment.objects.filter() or
> create(), save()  anywhere I want.
> I want to work with the base class while loading the data from the subclass
> at runtime. Simple polymorphism but with different database tables in the
> backend.
>

        I expect that over 95% of the responses will all emphasize using
single set of tables, and a filter by the department (which is retrieved
from the log-in authorization information)

        Anything else means you have to somehow dynamically:

1)      use one database for authorization and; somehow OPEN a department
database connection that the models will hook into instead of using a
"load-time" database. That probably means you have to replace the Django
database connection system with one that you call at run-time. IOW, you
do not have a database configured in the normal manner -- but EVERY
Django function that issues an SQL operation would have to do something
like:

        if not dbConnection:
                dbName = authuserdepartmentname
                dbConnection = whateverapi(database=dbName,...)

where dbConnection is whatever Django normally uses to track the
database connection.


2)      not use Django models defined at build time (ie, in code), but
dynamically build the models at run-time so that you can specify the
table name when accessed (sort of using code templates into which you
substitute the needed table names which are then imported later -- good
luck getting Django to recognize the validity of the models).


3)      operate separate "servers" for each department. Each server would
have its own location for datafiles, but you can probably share the
application code via soft-links or maybe even one shared code directory.
This is workable for those databases that allow connections defined by
file name/path (SQLite, embedded Firebird) but not for those using a
dedicated database server (MySQL, PostgreSQL) -- because the file "name"
will be fixed, but the data path would be relative to the "web server"
URL. Of course, if you have many departments, you end up with many
"servers" on different ports:

        http://server.localhost:8080/   Physics
        http://server.localhost:8081/   Mathematics

or your server on port 80 rewrites URLs

        http://physics.server.localhost/ => server.localhost:8080
        http://mathematics.server.localhost/ => server.localhost:8081
or
        http://physics.server.localhost/ => http://server.localhost/physics

where the directory structure is something like

physics/
        app1    -> softlink to          common/app1
        data/
                sqlite.db

mathematics/
        app1    -> softlink to          common/app1
        data/
                sqlite.db

and the path to the database is relative

        ./data/sqlite.db


        Option 1 is probably easier to create, since the table names don't
change -- only the "department" database name changes. Creating the
database for each new department means over-riding the admin "sync"
logic since the database name has to be provided rather than extracted
from a configuration field.

        Options 1 and 2 require modifying core Django functionality -- don't
expect to be able to update Django without recreating the changed core,
and don't expect to be able to use a hosting service that provides
Django as you won't be able to change it with your modifications.

        Option 3 restricts the type of database that can be used, and
requires very heavy administration of the web server configuration, not
just Django.


        In contrast, using ONE database with just one set of tables, in
which all records have a foreign key to the authorization data (which
provides the department associated with the login), and having all views
filter the data by that department is the solution that is the most
natural scheme to maintain, and can be done within a single Django
application (I use "application" here to mean the collective set of
models and views -- not some code that handles just one web page)

--
        Wulfraed                 Dennis Lee Bieber         AF6VN
        wlf...@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[Bill Beal]

Question for an expert from a newbie:  Could you decorate each model that has data that needs to be separated by departments?  If so, you wouldn't have to remember to code the department filter on each retrieval.

[Rohit Banga]

Thats interesting Bill.

Are you talking about something like this?

http://stackoverflow.com/questions/3276700/django-model-subclass-without-changing-database-name

Thanks

Rohit Banga
http://iamrohitbanga.com/

To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/_Cn_whm6R50J.

[Bill Beal]

I don't think so.  The question actually had a different point, namely "When I put a decorator on my model, how do I keep the original name for the table?"  It didn't specify what the decorator does.  There is a link to a doc on proxy models, but it appears that they create multiple tables.

I should think that if you have a foreign key to the department in all tables that must be segregated by department, you could create a decorator that would apply a filter with the department key.  But I don't know how to do it.

[Rohit Banga]

How about maintaining one database per department and then using "using" parameter to select the appropriate database?

There are not too many departments. I know it may not scale or seem elegant but keeping data in separate tables or databases is a requirement.

Using the following command I feel I can sync all models to a new database.

./manage.py syncdb --database=department1

Do you foresee any problem with this solution?

I am planning to have a default database with all users and groups and then based on the group select the appropriate database.

I do not want to sync django-admin tables to all databases. Is there a way to restrict models to a specific database?

Let say I have databases 0 ... n.

Departments 1 to n have the same set of tables with different data and database i corresponds to department i.

database 0 is used to store only admin database tables. Is there a reasonable way to restrict this within the same app or are there problems that I cannot foresee?

Thanks

Rohit Banga
http://iamrohitbanga.com/

On Sun, Sep 23, 2012 at 12:56 PM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:

On Sat, 22 Sep 2012 22:04:41 -0700 (PDT), Bill Beal
<b.b...@eximflow.com> declaimed the following in
gmane.comp.python.django.user:

>

> I should think that if you have a foreign key to the department in all
> tables that must be segregated by department, you could create a decorator
> that would apply a filter with the department key.  But I don't know how to
> do it.
>

        Which basically puts it back to what the OP insists they are not
permitted to do -- have a single set of tables with a selection based on
the logged in user's department.

[Joel Goldstick]

I just came across the Section on Managers in the Django book (p 197
in the second edition) here online:
http://www.djangobook.com/en/2.0/chapter10.html

Search for the section on Managers. This would be an excellent way to
solve your problem. Using one model you can use different managers
which filter on Department Name
--
Joel Goldstick

[George Silva]

Use unit tests so you DO NOT FORGET to user the userid filter.

You're going to walk a thousand miles to get something somewhat working and that it won't solve all your problems. At least, resolving the "forgetting the filter" issue is easy to solve.

When you database starts to creep with tables you are going to have a nightmare. Oh, imagine when you need to cross compare these tables...if they were only one table...it would be easy. Imagine reports.´You will need to query n tables instead of one. Thats n - 1 chances to construct the wrong sql.

But that's up to you.

George R. C. Silva

Desenvolvimento em GIS
http://geoprocessamento.net
http://blog.geoprocessamento.net