Otis wrote:

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.

DNN supports multiple databases and versions. Correct me if I'm wrong, but mySQL does not support schema prefixes?

I started this thread and don't care that much anymore, but I have to say your attitude seems strange to me. I'm not sure whether to be insulted that you won't stoop to what is a very common technique, or to applaud you for making a product that doesn't just adhere to best practices, but actually gives the user no alternative.

Well, I do understand that from the user's Point of view (POV) it is dissapointing if a feature which seems so logical from the user's POV is being rejected for a reason that's not logical for the user's POV.

Though just because DNN uses a crappy table naming scheme, doesn't mean I have to encourage crappy naming schemes as well. I do call it crap, because it is silly to use table prefixes / suffixes to group them. A toolkit called 'Navision' does this too. They dump a lot of new tables into the schema for every new company you create. After a while you have literarly thousands of tables in your db. This becomes unmanageable.

Calling this as a 'best practise' is something I simply can't understand because there's nothing good about it. Say you have 5,000 tables in your schema and you want to work with the elements belonging to a given group. You can't, as all elements are given, and your toolkit has to support filtering, and I haven't seen a db management toolkit which can do that. (enterprise manager can't, SSMS can't, toad can't, database studio can't etc.).

Would these tables be placed inside schemas, it would have been another story. That's why schemas are there for: to group db elements which have to be grouped.

MySql doesn't support schemas, but it does support catalogs instead. It works similar to oracle: you have a database instance and inside it you have different schemas or catalogs. SqlServer and DB2 have an extra distinction but that's not really necessary.

Btw, may I ask you why it is me who's to blame for not being able to work with this? What if another toolkit comes up and invents yet another way of artificial grouping of elements in a catalog, like storing all tables which belong to one group in a separate table. If I then say "no, that's not going to be supported", will I then too be the one to blame for that?

Make no mistake: this IS a complicated thing: llblgen pro works with catalogs and inside these their schemas. The designer thus has to have grouping of elements with suffixes and prefixes, then you have to be able at runtime specify these prefixes and suffixes to target at runtime that Customer is for this call now mapped on 'ABC_Customer'.

Just saying I should support it and if I don't you are feeling insulted is IMHO not the way to go here. I personally feel sad about this.

To make this work in DNN, they also require you to copy a lot of stored procedures, am I correct? This has to be, otherwise procs can't refer to the proper tables.

The one thing I won't change is the caching of the names in-memory. This stays, as it's a key thing to proper performance at runtime.

How would you want to specify these prefixes then? (and suffixes, because there are of course also tools who suffix their tables to 'group' them) What if a Customer has to be used with prefix AA_ and its related order entities have to be used with prefix AB_ or no prefix at all?

Perhaps 'DNN' doesn't use different prefixes or suffixes per call, but I can imagine other toolkits will, for example if they have a central group of tables with no prefixes and per customer running on their portal they have a new group of tables with a prefix, having relations with the central group.

This then gives headaches when which prefix to apply. So adding this isn't a walk in the park, as the world is bigger than DNN: if it only works with DNN, it's of no use for others who use a different scheme but also with dyn. prefixes.

You have to understand, it's often not as simple as it might look: adding a feature might help user A but if it is implemented just to help the scenario of user A and with that is useless for user B and C, it has to be implemented so all can use it, right? However that might be a little bit more complex than what was necessary for A, perhaps too complex.

So, to cut a long story short: the route to use this is to add it yourself. It's not that hard for DNN, as the scope is limited (no other scenario's have to be supported so you can write the code which solely targets the DNN scenario).

• Derive a class from DataAccessAdapter
• override the getfieldpersistenceinfo(s) methods.
• in these overrides, use a routine you write which simply checks what the target table is of the field persistence info object retrieved from the base' implementation. With that, you index into a structure you provide which per table or per context the prefixes. You then construct the new name and with the rest of the info of the existing fieldpersistenceinfo you create a new instance. (which is simply calling the CTOr and passing all the values) and return that new object instead.

As you don't have to take care of other scenario's than DNN's, this is pretty straight forward. As I explained above, the usage of this can be broarder and that makes it pretty complex pretty quickly.

I reckon it's ok if in the end we have to do that ourself. I won't argue for that, it may even leave some space for added value in that gap.

Just my other 2 cents to finish with it:

• In DNN the db prefix is defined in the web.config on a per application basis, and it is served by the DNN API so it is fine keeping it in a single property slot. I don't think it's much useful to handle the stored procedures, because they are already correctly served by the DNN provider DAL API.

• That should work similarly for Joomla, Drupal, OpenStar, WordPress, MyBB, Flux, ModX, CMSMS and other CMS using the same mechanism. Accordingly, it seems it's more a php/mysql habit.

• I think that mechanism was driven in those CMS communities by the need to support shared hosting environements with a single DB, without explicit support for schemas. It's probably less popular now, that most cheap providers offer several dbs, and with the modern schema approach, yet I reckon it will stay there for a while because of the many instances around, for which an upgrading path has to be supported.
• EntitySpaces advertises supporting the mechanism
• You only have to account for it if you want to publish your modules/extensions to the community. Obviously, targeted developement only requires that you generate your LLBLGen codebase against the already prefixed target dbase

Cheers

Otis wrote:

You have to understand, it's often not as simple as it might look: adding a feature might help user A but if it is implemented just to help the scenario of user A and with that is useless for user B and C, it has to be implemented so all can use it, right? However that might be a little bit more complex than what was necessary for A, perhaps too complex.

I understand now that there are technical limitations preventing this from making it into the product. Is there an appropriate 3rd party section of the web site where a DNN-compatible DataAccessAdapter can be posted? I have attached some source code samples to this message.

Otis wrote:

Just saying I should support it and if I don't you are feeling insulted is IMHO not the way to go here.

I completely agree, and I am sorry that it came across that way. I thought you were saying that you would not implement it because it was "bad". As another user mentioned a lot of CMS use the table prefix technique and it's common to see on a mySQL database in a shared hosting environment where you get one database account and no schema prefix support.

Otis wrote:

Calling this as a 'best practise' is something I simply can't understand because there's nothing good about it.

By the way, I think you misread or I miswrote: table prefixing is NOT a best practice, I agree.

I was saying that there is a pragmatic side of me which says LLBL should support it even though it's not a best practice because it's so widely used.

I still love LLBL!

Thanks for clearing that up, Benles

I'll have a closer look at your code and will publish it in the customer area's 3rd party section.

Jessynoo wrote:

I reckon it's ok if in the end we have to do that ourself. I won't argue for that, it may even leave some space for added value in that gap.

Just my other 2 cents to finish with it:

• In DNN the db prefix is defined in the web.config on a per application basis, and it is served by the DNN API so it is fine keeping it in a single property slot. I don't think it's much useful to handle the stored procedures, because they are already correctly served by the DNN provider DAL API.

• That should work similarly for Joomla, Drupal, OpenStar, WordPress, MyBB, Flux, ModX, CMSMS and other CMS using the same mechanism. Accordingly, it seems it's more a php/mysql habit.

• I think that mechanism was driven in those CMS communities by the need to support shared hosting environements with a single DB, without explicit support for schemas. It's probably less popular now, that most cheap providers offer several dbs, and with the modern schema approach, yet I reckon it will stay there for a while because of the many instances around, for which an upgrading path has to be supported.
• EntitySpaces advertises supporting the mechanism
• You only have to account for it if you want to publish your modules/extensions to the community. Obviously, targeted developement only requires that you generate your LLBLGen codebase against the already prefixed target dbase Cheers

That a competitor promotes itself by having DNN support is nice for them, but we tend to focus on being a general purpose framework, not a one-trick pony . I know DNN is big and all, but similar to the entlib: our framework should be flexible enough to use it with these toolkits, but we won't commit ourselves to add features to support them directly. The main reason for this is that a specific feature for a particular framework not only makes you fragile (the particular framework changes -> you have to adapt), it also alienates alternatives most of the time, or you have to add support for these as well. I like to solve things more generally, so it is usable for all, perhaps with some code additions by the developer here and there.

I've tried to explain earlier on that if I add DNN support, it will likely trigger questions why it doesn't work in situations with a central table group and a lot of subgroups. So support for that has to be added as well, pulling it all into a bigger complex problem than DNN needed.

Don't get me wrong: if it's IMPOSSIBLE to support DNN with the framework as it is now, we obviously have to add some flexibility here and there. Luckily that's not the case. I understand that having things out of the box is nicer, after all, having to add code to a framework to make it work in a given situation is never ideal, however it's unavoidable for some cases, simply because a framework will never be able to fit in for 100%, there's no silver bullet . So, the framework then has to offer flexibility to add some code to it to make it work in situations it doesn't support out of the box. It then comes down to: how much code is reasonable: I don't think writing a lot of code, classes etc. is reasonable, as that takes a lot of time etc. However a couple of overrides are imho reasonable.

benles wrote:

Otis wrote:

You have to understand, it's often not as simple as it might look: adding a feature might help user A but if it is implemented just to help the scenario of user A and with that is useless for user B and C, it has to be implemented so all can use it, right? However that might be a little bit more complex than what was necessary for A, perhaps too complex.

I understand now that there are technical limitations preventing this from making it into the product. Is there an appropriate 3rd party section of the web site where a DNN-compatible DataAccessAdapter can be posted? I have attached some source code samples to this message.

The DataAccessAdapter code you've posted is very simple to understand and would be an excellent addition to the 3rd party section in the customer area.

It effectively comes down to 4 overrides, a prefix member and 2 rename methods (1 rename method and one array handling renamer). The code fits in roughly 60 lines :

protected override IFieldPersistenceInfo GetFieldPersistenceInfo(IEntityField2 field)
{
    return Rename(base.GetFieldPersistenceInfo(field));
}

protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntity2 entity)
{
    return Rename(base.GetFieldPersistenceInfos(entity));
}


protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntityFields2 fields)
{
    return Rename(base.GetFieldPersistenceInfos(fields));
}

protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(string entityName)
{
    return Rename(base.GetFieldPersistenceInfos(entityName));
}


private string mPrefix;

public string Prefix
{
    get { return mPrefix; }
    set { mPrefix = value; }
}


IFieldPersistenceInfo Rename(IFieldPersistenceInfo oi)
{
    return new FieldPersistenceInfo(
        oi.SourceCatalogName,
        oi.SourceSchemaName,
        Prefix + oi.SourceObjectName,
        oi.SourceColumnName,
        oi.SourceColumnIsNullable,
        oi.SourceColumnDbType,
        oi.SourceColumnMaxLength,
        oi.SourceColumnScale,
        oi.SourceColumnPrecision,
        oi.IsIdentity,
        oi.IdentityValueSequenceName,
        oi.TypeConverterToUse,
        oi.ActualDotNetType
        );
}

IFieldPersistenceInfo[] Rename(IFieldPersistenceInfo[] l)
{
    List<IFieldPersistenceInfo> nl = new List<IFieldPersistenceInfo>();
    foreach (IFieldPersistenceInfo i in l)
    {
        nl.Add(Rename(i));
    }
    return nl.ToArray();
}

That's all

I'll wrap it up and post it on the 3rd party section. Thanks!

(edit): posted