Plugin database and schema name

Hello everyone,
I looked at many examples for plugins with data access and they all create their tables in the dbo schema. I am used to use multiple schemas in my databases to organize things. Is there a reason NOT to use different schemas for my plugin's tables? As an example, in my "Map" files, I would use "ToTable("MyTable", "MyPluginName");" Am I limited to dbo? If so, why?

Thanks

Dany Latulippe

I was wondering the exact same thing.

This is a very legitimate question and the fact that no-one has answered it is worrisome.

I have a plugin which creates a few tables and I would like to create them in their own schema to keep them seperated. In my map class I use

ToTable("MySpecificSchemaName.TableName_WithSomeMoreExplanation");

and it works fine when installing the plugin but when uninstalling the table it does not find the table since it drops the schema for some reason.

Would love to hear from one of the developers of nopCommerce on this.

On a bit further inspection I can see that there is code commented out in

Nop.Data.DbContextExtensions.GetTableName<T>(this IDbContext context) where T : BaseEntity

which looks like it grabs the schema name. I had problems uninstalling my plugin because the schema name was missing in the table name.

To fix it I created my own extension class and copied that method into my own extension class. Then I simply uncommented that part and changed it to use the appropriate variable to look for the schema name and now it works.

Here is the extension class if someone is interested:

using Nop.Core;
using Nop.Data;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace MyAwesomeNamespace.Extensions
{
    public static class DbContextExtensions
    {
        /// <summary>
         /// Get table name of entity
         /// </summary>
         /// <typeparam name="T">Entity type</typeparam>
         /// <param name="context">Context</param>
         /// <returns>Table name with the schema name and a dot appended.</returns>
        public static string GetTableNameWithSchema<T>(this IDbContext context) where T : BaseEntity
        {
            //var tableName = typeof(T).Name;
            //return tableName;

            //this code works only with Entity Framework.
            //If you want to support other database, then use the code above (commented)

            var adapter = ((IObjectContextAdapter)context).ObjectContext;
            var storageModel = (StoreItemCollection)adapter.MetadataWorkspace.GetItemCollection(DataSpace.SSpace);
            var containers = storageModel.GetItems<EntityContainer>();
            var entitySetBase = containers.SelectMany(c => c.BaseEntitySets.Where(bes => bes.Name == typeof(T).Name)).First();

            // Here are variables that will hold table and schema name
            string tableName = entitySetBase.MetadataProperties.First(p => p.Name == "Table").Value.ToString();
            string schemaName = entitySetBase.MetadataProperties.First(p => p.Name == "Schema").Value.ToString();
            return schemaName + "." + tableName;
        }
    }
}

I would absolutely LOVE for a developer to look at this and either "approve" it or tell me what is wrong with it and potentially tell me what COULD go wrong.

For now I have only tested it by installing a plugin without the changes and then uninstall it. Then I get the error that it cannot find the tablename. The reason for that is because the schema name is missing.
Then I add the extension and it works fine with uninstalling the plugin.

Cheers!

I have already run into a minor problem.

For some reason I managed to uninstall my previous tables, or so I thought. Anyways, When you uninstall your plugin there is an extension method in the same place as the other one called DropPluginName which does not expect a schema name. This will not fail the installation but instead it will silently leave the tables in the database without doing anything.

There are probably more places where this affects the plugin. Use with caution.