A couple of data model design questions

I’m working on an application where all the data belongs to a given User. A User, unless they are an admin, should only be able to see their data.

  • In your akkount example, if someone enters a currency, any user can see that currency. If someone enters a category, anyone can see that category. I would like to setup something where User has their own set of currencies and categories. What is the best way to accomplish this?

  • One approach I’ve played around with is giving each table a many_to_one link to USER_ID of sec$User, like the UserData class in akkount does. However, that is the only table in akkount with that feature. To get all data in the application to belong to a particular user, is that the best approach?

  • Also, how can you do a unique constraint with this setup? If each currency has to be unique, there can be duplicates across users. How do you do a unique constraint across two fields? Does this become very inefficient as you have more users?

  • How can you have a custom database error, for example, “That currency already exists” instead of “Unique Constraint Violation Occurred [index name]”?

  • How can you have a field be read-only and mandatory? I would like the User that a currency belongs to, to be set at creation of the currency and to not be changeable thereafter. However, the earliest I can seem to get to it is onBeforeInsert in the EntityListener, at which point you cannot set the User because it is read only.

Hi Jonathan,

here are the answers to some of your questions:

  1. you can use the “createdBy” column in StandardEntity and add a constraint that you can only see entities where the condition is: {E}.createdBy = :session$userLogin
    Another option is to look at the sample-saas example which basically uses a tenant approach to the problem.

  2. you don’t need that if you use “createdBy”, but if it makes sense from a business point of view (because sometimes the person who created it is not necessary the person that should see it or something along those lines) you of course can do that. Just use the security contstraints to filter the data accordingly.

  3. You can define a unique constraint on the entity that is a combination of two fields: createdBy and categorylabel e.g. You can find an example here: https://doc.cuba-platform.com/manual-6.3/soft_deletion_unique_constr.html

  4. You can (see https://www.cuba-platform.com/discuss/t/unique-index-custom-exception-handler for more information about it)

Bye,
Mario

Thx Mario. Very helpful.

Regarding unique constraints, you can create multi-column constraints in Studio, see Unique constraints tab in the entity designer.

As for read-only mandatory attribute like “owner” - don’t select the Read only checkbox in Studio attribute editor. When you do this, Studio does not generate setter for the attribute, so of course you cannot set any value. Such completely read-only attributes are usually used for transient calculated attributes and very rarely for persistent ones. Instead, make the attribute read-only using security permissions, so you will be able to set its value in an EntityListener.

Thx. A couple of additional questions:

  • RE, Mario’s suggestion to use a constraint, should that an in-memory constraint, a DB constraint or both?
  • Once you get the roles and access groups set up correctly, how do you persist this setup so that the next time you create the DB from scratch, you don’t have to do it all over again?
  • If I use the {E}.createdBy = :session$userLogin constraint, how could I have an admin create an entity on behalf of a user. When the admin creates a currency, for example, createdBy is set to admin and not the user so it won’t show up given the constraint. Is there a way to change the createdBy value? The entity inspector doesn’t allow it. Would you have to change it in something like Squirrel SQL? Would changing this value break anything else?

should that an in-memory constraint, a DB constraint or both?

In-memory constraint is needed only for filtering collections that are loaded as a part of object graph of another entity. So whether you need in-memory constraints for a particular entity depends on your data model and usage.

Once you get the roles and access groups set up correctly, how do you persist this setup

Good question. The straightforward way is to convert your setup to SQL INSERTs and put them to 30.create-db.sql. The platform can help you in it: select a Role, and select System information from the context menu, then click Script for insert. The text area below will contain SQL code for creation of the role and all its permissions.

A more sophisticated way is to export the roles and other required entities to JSON using the Export button and then use EntityImportExportAPI to import data on the application start.

how could I have an admin create an entity on behalf of a user

Better use a special many-to-one attribute like user with the corresponding USER_ID column in the database. Then you’ll be free to give access to such entities as you need.

Thx. That ‘System Information’ option is useful functionality.

  • Am I correct that an issue with the “straightforward” way is that changes to the table or entity structure may require a manual update to 30.create-db.sql?

  • Where is the Export button?

  • Is there any downside in specifying a constraint as both in-memory and DB? i.e. if it requires an in-memory constraint, the DB constraint won’t do anything and if it requires a DB constraint, the in-memory constraint won’t do anything. I’d like to specify both in case I’m unsure about what part of the object graph gets loaded when.

Hi,

> Am I correct that an issue with the “straightforward” way is that changes to the table or entity structure may require a manual update to 30.create-db.sql?

This is one of the drawbacks. I wrote a blog post about the topic of test data while ago, which talks about this topic a little more.

> Where is the Export button?

You can find the export functionality e.g. in the Entity inspector (Administration > Entity Inspector) or in different other concrete screens (like Roles browser).

Bye
Mario

Thx Mario.

You can set the value also within the Entity with @PostConstruct? What is the benefit of using and EntityListener versus doing it within the entity?

When you initialize an attribute in @PostConstruct, it actually happens on the client tier and the value will be passed to the middleware later. If you set the attribute to read-only using attribute security permission, the saving will be rejected. However, you can set cuba.entityAttributePermissionChecking app property to false to avoid this (attribute security will still be used in UI components).

In an entity listener, you just have more freedom in calculating a value and no any security restrictions.
Perhaps in the case of just assigning a current user the PostConstruct approach is simpler and it also gives the ability to show the value in UI before saving. I would also recommend switching off the cuba.entityAttributePermissionChecking property, especially since it will be off in 6.4 by default - see PL-8274.