Is there a way to set a table target for an entity? I have a use case where we have multiple tables with identical schemas and I need to determine at run-time which table to write.

It depends whether you are using SelfServicing or Adapter.

Anyway, please search for SourceObjectName in the LLBLGen Pro Reference manual.

in v2.0, it's not possible anymore to set the SourceObjectName btw. It is in 1.0.2005.1.

Otis wrote:

in v2.0, it's not possible anymore to set the SourceObjectName btw. It is in 1.0.2005.1.

What is the new solution? Applications like DNN install tables with prefixes.

benles wrote:

Otis wrote:

in v2.0, it's not possible anymore to set the SourceObjectName btw. It is in 1.0.2005.1.

What is the new solution? Applications like DNN install tables with prefixes.

What do you mean with prefixes? You map entity Foo on table tblFoo, that's not your concern: llblgen pro takes care of that, you work with 'Foo'. Or are these prefixes per application or something? (I have no idea how DNN works internally .)

Otis wrote:

benles wrote:

Otis wrote:

in v2.0, it's not possible anymore to set the SourceObjectName btw. It is in 1.0.2005.1.

What is the new solution? Applications like DNN install tables with prefixes.

What do you mean with prefixes? You map entity Foo on table tblFoo, that's not your concern: llblgen pro takes care of that, you work with 'Foo'. Or are these prefixes per application or something? (I have no idea how DNN works internally .)

Hi Otis, let me explain.

DNN allows site administrators to install the DNN core system using an object qualifier to avoid naming collisions on shared databases. This way you can install more than one DNN instance and use the same catalog. It seems silly but it is a feature

This is important for module developers that want to remain fully compatible with DNN. When packaging modules for DNN for commercial distribution, you must consider that multiple DNN instances might be running on the same catalog and that your module might be installed by these instances.

A sample correct install script statement looks like this:

CREATE TABLE {databaseOwner}{objectQualifier}NTForums_Attach (
    [AttachID] [int] IDENTITY (1, 1) NOT NULL ,
    [PostID] [int] NULL ,
    [UserID] [int] NULL ,
    [Filename] [varchar] (50) NULL ,
    [DateAdded] [datetime] NULL ,
    [DateUpdated] [datetime] NULL 
) ON [PRIMARY]
GO

When DNN runs the script as part of your module installation process, it parses through first and replaces all the placeholder tokens with the current values defined for the DNN instance.

Thus, at runtime you must query the DNN API to get the databaseOwner and objectQualifier value before you can resolve your schema and object names.

Put a simpler way, LLBL must be able to specify table name prefixes to be compatible with DNN.

I think the solution might be the usage of Catalog Name Overwrites & SchemaName Overwrites. Please refer to the LLBLGen Pro docs: "Using the generated code -> Application configuration through .config files"

Either the user who installs your module, should insert those values into the application configuration file, or you try to find a way to insert them automatically during installation.

What Walaa explains works when the tables are in their own schema. So: CREATE TABLE {databaseOwner}{objectQualifier}NTForums_Attach

means: CREATE TABLE {databaseOwner}.{objectQualifier}NTForums_Attach

so the dbowner is the schema.

If DNN doesn't use schemas in the db, it's design is upside down. Why would anyone prefix table names with the db owner if there's something like a schema in the db model. (Ok, if you're running hte thing on a db without schema support (access, firebird, mysql) it's a bit problematic.

Otis wrote:

Why would anyone prefix table names with the db owner if there's something like a schema in the db model.

A question for the DNN team perhaps. My understanding though is that

MyTable

might appear in the catalog as

DNN_MyTable

I'm surprised you've never seen that before. I've come across numerous web apps whose installation scripts allow a table prefix option.

So back to the question, what can be done to override table names at runtime? It seems logical since catalogs and schemas can be overridden.

benles wrote:

Otis wrote:

Why would anyone prefix table names with the db owner if there's something like a schema in the db model.

A question for the DNN team perhaps. My understanding though is that

MyTable

might appear in the catalog as

DNN_MyTable

I'm surprised you've never seen that before. I've come across numerous web apps whose installation scripts allow a table prefix option.

Table prefixes are ok, llblgen pro can handle these. What you can't do is change these prefixes in the mapping data at runtime.

So back to the question, what can be done to override table names at runtime? It seems logical since catalogs and schemas can be overridden.

Nothing. You can't do a thing about that anymore, as that data is cached and shared among all instances and therefore readonly. Well... 'nothing' is a bit big. You can in your dataaccessadapter, override getfieldpersistenceinfo(s) and copy the instances in new objects and then alter them.

So if you have a DNN_Table in your catalog, that's fine, you can map an entity onto it. Though that's at design time and the entity you get in your generated code is mapped onto DNN_Table. You can't map it onto another table AT RUNTIME.

There's absolutely no need for changing table prefixes at runtime in any sane application, as there are schemas to do this.

What I find strange is that you don't use your own tables in dnn, why do you go through this wacky DNN table creation scheme?

Hi Otis, thanks for sticking with me.

Again I can't answer for every db developer who has done it, but I've come across many web apps that, at installation time, allow the admin to specify a table prefix before the tables are created for the first time. He can choose any prefix he wants, and I as a developer cannot predit it until my app is running on his system.

In the case of DNN, the "object qualifier" is specified at installation time and is written to web.config after the tables are created. Anyone wishing to access DNN's tables or to fully follow the DNN deployment scenarios must also create his tables using this object qualifier.

In general, the strategy is seen more in mySQL backends and less in SQL Server, but I have seen both. Because this practice is out there (even if it doesn't make sense), I have to work with it and that means not knowing the exact table name until runtime. There may not be a good reason for doing it

In testing, I was able to add a table prefix to the PersistenceInfoProviderCore class and recompile. Could I alter the LLBL templates to expose these mappings through properties for modification at runtime?

Here is a unit test that I would like to make.

[TestFixture]
    public class TablePrefixFixture
    {
        [Test]
        public void TablePrefix()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Main.ConnectionString"]);
            conn.Open();
            SqlCommand c = new SqlCommand("EXEC sp_rename 'BlogEntry', '" + GetPrefix() + "BlogEntry'", conn);
            c.ExecuteNonQuery();

            DataAccessAdapter da = new DataAccessAdapter();
            // da.SetPrefix(GetPrefix());   // Add a call to set a prefix at runtime
            BlogEntryEntity be = new BlogEntryEntity();
            be.Title = "test title";
            be.Body = "test body";
            da.SaveEntity(be);

            c = new SqlCommand("EXEC sp_rename 'DNN_NukeSpace_BlogEntry', 'NukeSpace_BlogEntry'", conn);
            c.ExecuteNonQuery();

            conn.Close();
        }

        // Simulate some API call to retrieve the current table prefix
        string GetPrefix()
        {
            return "TestPrefix_";
        }

    }

Multiple installations sharing a single catalog is a fringe scenario and I can get by without solving it for now, but it is something that occurs out in the wild.

benles wrote:

Hi Otis, thanks for sticking with me.

Again I can't answer for every db developer who has done it, but I've come across many web apps that, at installation time, allow the admin to specify a table prefix before the tables are created for the first time. He can choose any prefix he wants, and I as a developer cannot predit it until my app is running on his system.

Ok, but you are the app-writer, so you can simply not offer that option, as IMHO it's a bit unnecessary. It's a poor-man's schema usage which quickly leads to a mess in a single db.

In the case of DNN, the "object qualifier" is specified at installation time and is written to web.config after the tables are created. Anyone wishing to access DNN's tables or to fully follow the DNN deployment scenarios must also create his tables using this object qualifier.

In general, the strategy is seen more in mySQL backends and less in SQL Server, but I have seen both. Because this practice is out there (even if it doesn't make sense), I have to work with it and that means not knowing the exact table name until runtime. There may not be a good reason for doing it

Hmm... Well, mysql also supports catalogs (not schemas), which they call catalogs but actually work the same as Oracle's schemas. But I understand it's a problem you're facing so simply saying "don't use it" won't do

In testing, I was able to add a table prefix to the PersistenceInfoProviderCore class and recompile. Could I alter the LLBL templates to expose these mappings through properties for modification at runtime?

The problem is that they're cached, so a lot of memory is preserved and creating entities is much much faster. The thing is that if you create a CustomerEntity, the CustomerID field's persistence info is shared with every other CustomerID field instance in other CustomerEntity instances. So changing the persistence info can work, but you've to realize that it changes the destination table for EVERY instance. If that's fine in your project, there's no problem.

Otis wrote:

The thing is that if you create a CustomerEntity, the CustomerID field's persistence info is shared with every other CustomerID field instance in other CustomerEntity instances. So changing the persistence info can work, but you've to realize that it changes the destination table for EVERY instance. If that's fine in your project, there's no problem.

That would be fine. When is it cached? In other words, how early in my code would I need to make my prefix modifications?

benles wrote:

Otis wrote:

The thing is that if you create a CustomerEntity, the CustomerID field's persistence info is shared with every other CustomerID field instance in other CustomerEntity instances. So changing the persistence info can work, but you've to realize that it changes the destination table for EVERY instance. If that's fine in your project, there's no problem.

That would be fine. When is it cached? In other words, how early in my code would I need to make my prefix modifications?

It uses a static constructor so as soon as you enter a routine which accesses the persistenceinfoprovider class, it's initialized. So your code will always have an initialized set of objects.

Otis wrote:

The problem is that they're cached, so a lot of memory is preserved and creating entities is much much faster. The thing is that if you create a CustomerEntity, the CustomerID field's persistence info is shared with every other CustomerID field instance in other CustomerEntity instances. So changing the persistence info can work, but you've to realize that it changes the destination table for EVERY instance. If that's fine in your project, there's no problem.

I'd like to understand this a little better. The example in DNN is as follows:

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchSettings

When DNN runs the above script, it is created in the context of databaseOwner defined in web.config, and the name of the stored procedure is prefixed with objectQualifier value from web.config.

If in web.config the databaseOwner='dbo' and objectQualifier='DNN', then the preceding line would be programatically converted to:

CREATE PROCEDURE dbo.DNN_GetSearchSettings

I think the only way to make LLBL support this is to modify the templates so that I can initialize PersistenceInfo at runtime, correct?

As you use adapter, and if you just want to modify the schema name, you can do so by specifying schema name overwriting through the dataaccessadapter object (see the DataAccessAdapter section in the documentation).

Otis wrote:

As you use adapter, and if you just want to modify the schema name, you can do so by specifying schema name overwriting through the dataaccessadapter object (see the DataAccessAdapter section in the documentation).

Thanks for the quick reply! We discussed schema name overwriting earlier in the thread, it won't work.

DNN does not support schema names. It should. It's a great idea. It doesn't. When it creates the database objects, it prefixes the object name with a prefix specified in web.config followed by an underscore (_).

ObjectName becomes Prefix_ObjectName

LLBL's PersistenceInfo class is of course generated against ObjectName, but I need to tell it that the object's name for this runtime instance is Prefix_ObjectName.

benles wrote:

Otis wrote:

As you use adapter, and if you just want to modify the schema name, you can do so by specifying schema name overwriting through the dataaccessadapter object (see the DataAccessAdapter section in the documentation).

Thanks for the quick reply! We discussed schema name overwriting earlier in the thread, it won't work.

DNN does not support schema names. It should. It's a great idea. It doesn't. When it creates the database objects, it prefixes the object name with a prefix specified in web.config followed by an underscore (_).

ObjectName becomes Prefix_ObjectName

LLBL's PersistenceInfo class is of course generated against ObjectName, but I need to tell it that the object's name for this runtime instance is Prefix_ObjectName.

Ok, I thought the owner (schema) name was also a problem.

Hmm. Ok, to solve it for proc names you have to alter a bit of code in the 'daaAdapter.template' for the database used. (so create a copy, make the modifications, bind it to the same templateid in your own templatebindings file)

The routine in question is CreateCorrectStoredProcedureName and it's almost at the bottom of the class. In there, you should use a string replace for example to replace your fragment, but it's still a bit awkward.

For tables, you should override the GetFieldPersistenceInfos routines. You then can in the overrides first call the base methods, to obtain the original persistence info (table/field etc. info). And then create NEW fieldPersistenceInfo objects with the information obtained and different (manipulated) names for hte tables (i.e. 'DNN_' or other prefix for the table).

Otis wrote:

For tables, you should override the GetFieldPersistenceInfos routines. You then can in the overrides first call the base methods, to obtain the original persistence info (table/field etc. info). And then create NEW fieldPersistenceInfo objects with the information obtained and different (manipulated) names for hte tables (i.e. 'DNN_' or other prefix for the table).

I modified persistenceInfoProviderAdapter.template as follows:

public class PersistenceInfoPrefix
{
   public static string ObjectNamePrefix = "";
}

Then in all the AddElementMapping, I used PersistenceInfoPrefix.ObjectNamePrefix + <[ElementTargetObjectName]>

As long as I initialize my prefix before anything else is used, this works fine. What do you think?

Should work. It's fine IMHO.

Cheers.

Hi,

as I had detailed in that other thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7386&HighLight=1,

coupling LLBLGen with DNN can be quite straightforward and powerful (trivial yet elegant dnn style "DataProviders", see the other thread), except for that prefix issue.

dbowner indeed stands for the schema, which is no pb; it's just then that they came out with that additional prefix which allows for multiple DNN instances in the same schema, and it is a requirement to support that for any public module, which IMHO is a reason why there are so few commercial DNN stuff leveraging LLBLGen, although LLBLGen would be the perfect tool to develop multi sgbd providers as supported by DNN.

We went for the "GetFieldPersistenceInfos" modification, and that's fine, yet I'd definitely apreciate if that could become a native feature.

Again, I know you're not so fond of DNN architecture, Frans, but while DNN keeps improving, and I can see more and more people starting to develop DNN modules, I think that such little additions can really make a difference.

Frankly I have strong objections towards adding a feature to make this possible. I know that this isn't flexible from my part but the fact is that this feature should actually be discouraged. What dnn should do is create a new schema. That's why you have schemas in db's. Table prefixes to distinguish differences in scope isn't what should be done because before you know it you have thousands of tables while you're using actually a fraction of it for a given app.

If people want it, they can use adapter and override that method indeed. it's not trivial nor straightforward, but actually, offering a way to solve this isn't trivial either, because how to implement it? table name overwriting?

Well, I understand your point of view.

Now it's not that dnn does not deal with schemas (dbowner is the schema, the "." gets added at runtime), it's just that it additionally supports those stupid prefixes. Neither is it really hard to implement. As suggested in the previous posts, it only requires prefixing in the persistence provider or in the dataaccessadapter.

It's just then that the customization overhead gets a little "tiring" over the years and multiple projects, and I would hope to find the feature natively; but fair enough, I'll go with it.

Cheers

I wonder if a discussion with shawn over at dnn about the rational for these prefixes, the future dnn releases suporting x.dnn.x schemas etc. would be productive?

arschr wrote:

I wonder if a discussion with shawn over at dnn about the rational for these prefixes, the future dnn releases suporting x.dnn.x schemas etc. would be productive?

I think so. I really think he should consider abandoning these prefixes and go for schemas instead.

Sorry J.S. that I can't proceed with your suggestion at this point.