Multi-Tenant Database - DevForce Community Forum

We have recently had one customer develop an application with multi-tenant capabilities. They did this using a capability that you describe in your post. This capability allows you to modify every outgoing query to the database. In the example below from the "Best Practices" section of the Developer's Guide, this capabability can be used to implement "Soft Deletes", bur as I said earlier, we successfully used this same capability to implement mult-tenant database queries:

Implementing �Soft Deletes�

Question: �For some of our Entities, we want the corresponding database records not to be physically deleted when a User deletes the Entity and saves changes. Instead, the corresponding record should just be flagged as deleted in the database.

�To accomplish this, we have a Status column that contains an enum value stating whether the row is deleted or not. When querying tables that include such columns, the Status always needs to be specified as a parameter to filter out the deleted records.

�I did not find an obvious way to implement this delete behaviour in our Entity classes. Do you have any samples?

�Also, how can we extend or override DevForce�s GetEntity() methods so they filter out the deleted entities by default, without any additional clause?�

Answer: This is a pattern we often see. There are a number of ways to go about this:

1. Add a "Fetching" handler to the PersistenceManager that examines the query and adds this clause if necessary. The Fetching event is raised just before the PersistenceManager sends a query to the server; the query is included in the event args for the Fetching handler, and you can modify it before DevForce applies it.

2. Add a "Fetched" handler to the PersistenceManager that removes "inactive" entities from the cache if they happen to be picked up (e.g., through a span). This is essential if you add spans that could retrieve an "inactive/deleted" record. You will want to use the overload of PersistenceManager.RemoveEntities() method that does not disturb the query cache.

3. Create a custom subclass of RdbQuery that always adds this clause. Then always use your class instead of the raw RdbQuery.

4. Write your own PersistenceManager adapter that covers GetEntities, etc.

The "Fetching" and "Fetched" event handlers are strong candidates because they work even within Relation Properties. Consider, for example, Order.OrderDetails. If the OrderDetail object has an " ActiveFlag column, you have to intercept the hidden query either by the use of a "Fetching" handler, or by overriding the "OrderDetails" property itself.

Let's assume that all business objects that have an "ActiveFlag" property descended from an abstract entity class (e.g., "ActiveEntity").

You might initialize your PersistenceManager as follows:

private void InitializePm() {

mManager = PersistenceManager.DefaultManager;

// other stuff

mManager.Fetching += new EventHandler<EntityFetchingEventArgs>(mManager_Fetching);

mManager.Fetched += new EventHandler<EntityFetchedEventArgs>(mManager_Fetched);

}

static void mManager_Fetching(object sender, EntityFetchingEventArgs e) {

IdeaBlade.Persistence.Rdb.RdbQuery q = e.Query;

if ( q.EntityType is ActiveEntity ) {

q.AddClause("ActiveFlag", EntityQueryOp.EQ, true); // '1' == true

}

static void mManager_Fetched(object sender, EntityFetchedEventArgs e) {

List<Entity> removeList = new List<Entity>();

foreach ( Entity obj in e.Entities ) {

ActiveEntity ae = obj as ActiveEntity;

if (ae != null && !ae.IsActive) {

removeList.Add(obj);

}

mManager.RemoveEntities(removeList, false); // remove but don't clear query cache.

}

There is another approach that might tempt you, but which should be avoided. There is an "SQL Where Clause" mapping option which lets you put a filtering constraint in the object definition itself. This is great if the value of the filter column is invariant. It is not good if the value of the filter column can change -- as is the case with the "inactive" or "deleted" column. Therefore, do not consider this option.

Edited by davidklitzke - 09-Jul-2008 at 8:20am