Refresh relational model data trap

Posts   
 
    
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 16-Dec-2013 20:43:48   

I'm working in "Database first" mode!! Already since version 1.x if I'm not misstaken.

The problem starts when my database contains a tabel without primary key.

  • I choose to refresh relational model data from database.
  • All works fine.
  • I then press f7 to regenerate code, and that gives me the error: "Project validation and relational model data adjustment result" Ono or more oerror or problems were detected during the project validation and relational model data adjustment process. Please check the error and warnings window for details.

I close the warning ... I go to the database using ssms ... I add primary key ... (problem in db solved!!) I go back to the LLBLGEN designer ... I try to refresh ... Refresh doesnot work, same error as above!

If the problem lies in the database, and the problem is solved in the database, than the refresh and code generation ought to work right?

My workaround now is to delete the entity from the designer. Refresh, Regen ... all fine. To fix one problem, I have to fix it in two places. I'm 100% sure that uptil v2.6 this was not needed. It seems very much so that the database first approach simply is no longer supported properly. I really cant understand why...

Version 4.1

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Dec-2013 07:23:54   

You need to fix errors before perform another refresh. In this case, remove the entity or setup the Identifying Fields manually at the Entity editor.

I you perform that fix, you can export the UPDATE DDL Script that will generate the PK information script you can run into your SSMS. So it's the same thing you did before.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Dec-2013 10:54:44   

To elaborate a bit: if the model has an error, it is not convertible to a new model, as things might be wrong in such a way that existing mappings/elements might not convert at all. So the model has to be OK. Normally this is the case if you work db first and don't change much in the designer. In v2 the designer pretty much followed the db and you couldn't do much else. In v3 and up, you can alter elements in the project which means they have an effect on the db. I don't know what the error is that you got, but it can't be that you create a project from a db with an error, other than perhaps missing type converters or naming issues, which are trivial to correct.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 17-Dec-2013 13:38:18   

Daelmo, Otis,

the flow is completely the other way around. Database first paradigm seems to be abandonded. I just run into it again ... made several adjustments in the database, did a refresh, got three errors (nice error messages ... nothing wrong with that, happy with the error detection) ... I fix them in the database. And now I want to re-refresh again ... Can't do it. I have to delete entities from the designer ...

What I want is what I had in v2.6, and is 100% the opposite of what daelmo is proposing to do and suggesting it is the same. Otis, I dont want to change anything advanced in the model using the designer ... I'm not working model first ...

Have you guys forgotten the so well defined paradigm "DATABASE FIRST"????

Why is it not possible to fully support "Database first" AND "Model first", just like it is possible to support both adapter AND selfservicing, and "one class" AND "two class" patterns. That way you simply acknowledge there are more than one ways that lead to Rome ... Have your opinions all, we support both sides... pick your pick!! That is how LLBLGEN has been conducting their developments up to v2.6.

IMHO, Database First is a strong paradigm and that is not something to be left abandonded!

ps... the other problems I ran into are probably all related (CliRefresher.exe error message, refresh not picking up all tables), and that makes it more annoying than simply removing entities in the designer. Please give it some thought from the perspective of the database first developers.

ps Otis.. the error message pops up when you try to refresh the model data from the database. A refresh of the database can not be performed, because of an error in the model. A classic catch 22.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Dec-2013 13:51:57   

I get that you're frustrated and for that, I'm deeply sorry. It's however essential for me to know what errors you had exactly to fully understand the issue. Could you elaborate on which errors you got, so exactly describe them, so I have a proper idea what you're facing. Thanks.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 17-Dec-2013 14:07:17   

Otis, To reproduce:

remove a PK from table A remove the entity from your model in the designer refresh regenerate -> "One or more errors or problems detected ... check errors and warnings." fix the source of the problem a.k.a. add primary key to table A choose refresh in the desginer (IMHO ought to work cause the root of the problem is fixed) error dialog: "One or more errors or problems detected ... check errors and warnings."

Hope this helps ...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Dec-2013 15:32:41   

Pablo wrote:

Otis, To reproduce:

remove a PK from table A remove the entity from your model in the designer refresh

I assume this re-creates an entity mapped on table A, and thus no pk defined. (it's not by default that this happens, it's configurable that new elements are automatically mapped to entities.)

regenerate -> "One or more errors or problems detected ... check errors and warnings." fix the source of the problem a.k.a. add primary key to table A choose refresh in the desginer (IMHO ought to work cause the root of the problem is fixed) error dialog: "One or more errors or problems detected ... check errors and warnings." Hope this helps ...

ah I see. This is indeed one of the small changes in v3 compared to v2 with respect to database first. In v2, there was no validation at all, so if there was no PK on the table, there was no pk in the entity. In v3 we introduced validation and also some stricter rules. One of them is that an entity always has to have a PK defined, or better: one or more fields have to be marked as 'identifying fields'. Normally it chooses the PK constraint on the table for these fields, but if that's not there, it can't select some field for this, so it leaves it to the user (there's no other choice). The reason there's now a requirement for pk fields on an entity (that's something else than a pk on a table) is that too many people run into the problem of saving an entity which doesn't have a pk, so we decided to introduce the rule in v3, with the validator and other rules (e.g. cycles are now also not allowed anymore as they too will make a user run into problems at runtime.)

The one you run into is one of the few situations where there can be a small problem: it requires manual interaction to define the identifying fields on an entity. Furthermore, if you insist not to have a PK on the table, you'll run into the situation where specifying the identifying fields on the entity A will result in a change script.

You decided to fix it in the db, however that indeed runs into the catch 22, where a refresh can only proceed when the model is valid and as your model isn't valid, you can't fix it that way. The way to fix it in v3+ is to define the identifying fields in the entity, then export the ddl sql script and run that on the db. No refresh needed, and the model is correct.

This situation is actually one of a very small set, caused by a rule which wasn't there in v2 (and thus one could get away with it then). I'm sorry you ran into this, there's however little I can do: I can't remove the stricter rule, because a lot of people are actually helped by it as it does prevent issues at runtime where an entity isn't updateable. I also can't skip the check for a valid model to do a refresh as it then can't guarantee that after the migration the model and mappings are correct, so errors creeping up on that stage, e.g. when you generate code, is more cumbersome as it's hard to tell what the root cause is at that point.

The only thing I could do is create a 'error, but ignorable' set for errors which are there but won't prevent a successful migration of the entities to the new relational model data. However it's very hard to determine what errors should be in that set, migration of a model of entities with mappings to a new set of relational model data is already very complex, determining which errors are not harmful for that process makes it even more complex. So we therefore don't allow errors in the model at all for refresh, to be sure the migration is successful.

Is this error the only type of error that prevented you from refreshing or are there other errors as well?

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 17-Dec-2013 15:47:34   

Otis, Thanks for looking into this ....

The one you run into is one of the few situations where there can be a small problem: it requires manual interaction to define the identifying fields on an entity. Furthermore, if you insist not to have a PK on the table, you'll run into the situation where specifying the identifying fields on the entity A will result in a change script.

I don't insist on not having a pk on the table. It was an example step to reproduce. I'm happy to see that llblgen points me to this 'error'. I'm also happy to fix it IN THE DATABASE. And then refresh and regenerate.

The way to fix it in v3+ is to define the identifying fields in the entity, then export the ddl sql script and run that on the db. No refresh needed, and the model is correct.

I understand. But that is 'model first', not 'database first'.

One thing you could do, is to make a project setting "Database First" = True/False . If it is True, it simply allows me to refresh the model, pointing me to any remaining errors etc... Or maybe somewhat less opinionated: "Select Workflow" => Model First / DatabaseFirst.

This solution has some advatages: - I assume it would also fix the problem I ran into with clirefresher.exe - It makes "Database first" a first class citizen again (next to the other first class citizen Model First )

My v2.6 workflow was: I change something in the database. I execute the script to recreate the DAL and continue coding. If I caused an error in the database, the refresh/generation steps would fail, hinting me to the errors. I'd fix those in the database and retry refresh/regenerate and continue.... No Designer involved, no DDL generation/execution involved ...

I appreciate the extra validation rules as you introduced since v3, they guide me in making better database design. They could simply make the v2.6 workflow point to more type of problems, not necessarily another workflow...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Dec-2013 16:04:11   

Pablo wrote:

Otis, Thanks for looking into this ....

The one you run into is one of the few situations where there can be a small problem: it requires manual interaction to define the identifying fields on an entity. Furthermore, if you insist not to have a PK on the table, you'll run into the situation where specifying the identifying fields on the entity A will result in a change script.

I don't insist on not having a pk on the table. It was an example step to reproduce. I'm happy to see that llblgen points me to this 'error'. I'm also happy to fix it IN THE DATABASE. And then refresh and regenerate.

That's ok, and normally you could, however in this specific case you can't as the error is in the model but comes from a missing element in the relational model data and it can't auto-correct it, so it can only conclude: fix it first in the model. In v2 one would have had this same error IF it would have had a rule that every entity should have had a pk defined and models should have no errors.

I can't really think of other errors which might halt a refresh with a catch 22: they're always correctable in the designer. What you want is that adding elements to a project after a refresh always leads automatically to a valid model, but as the tables have to be reverse engineered to entities, it might be that reverse engineering process has to make choices which could lead to a situation where the user has to correct a thing or two. In the end it's often OK and there won't be errors, but sometimes there will.

The way to fix it in v3+ is to define the identifying fields in the entity, then export the ddl sql script and run that on the db. No refresh needed, and the model is correct.

I understand. But that is 'model first', not 'database first'.

We make no distinction between them. We toyed with the idea to add a 'mode', back in 3.0, but that made things complicated as it would mean things in the user interface should be disabled because of a mode, which would be limiting. In this case, you could also define the identifying fields in the entity, and set Identifying fields follow dbprimary key constraints to false in the project settings (under catalog refresher). Then refresh and be done. Or have that setting set to true and toss away the script.

One thing you could do, is to make a project setting "Database First" = True/False . If it is True, it simply allows me to refresh the model. Or maybe somewhat less opinionated: "Select Workflow" => Model First / DatabaseFirst.

Again: I assume it would also fix the problem I ran into with clirefresher.exe

yes.

You have set options that a refresh should auto-add elements to the project. so running a clirefresh will auto-add elements to the project after the refresh. If you then want to generate code it will perhaps not work due to an error, because it added an element which lead to an error that needs correction. In most cases this isn't going to happen but in some cases it might.

My v2.6 workflow was: I change something in the database. I execute the script to recreate the DAL and continue coding. If I caused an error in the database, the refresh/generation steps would fail, hinting me to the errors. I'd fix those in the database and retry refresh/regenerate and continue.... No Designer involved, no DDL generation/execution involved ... All tables with identifiable sets etc...

The idea is that changes made to the entity model is easier: they'll result in the script to execute on the table, and you generate code from that. It's less work: the changes in the script produced by the designer are the same as the ones you'd have performed manually, or in some cases: more optimal.

E.g. when you change a 1:n relationship to 1:1, it takes a small change in the designer, though in the database it requires adding a UC, which isn't obvious.

But like I said: if all you want is to refresh and have the model accepted in all cases and generate code from that in all cases, that's not going to work in all cases: there will be cases which require correction from the user, as the designer can't make the choices for you, or you defined the wrong pattern for example for navigator names so they clash with entity names and you have to manually correct them as the designer can't decide what to pick in case of an error.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 17-Dec-2013 16:13:46   

Otis,

But like I said: if all you want is to refresh and have the model accepted in all cases and generate code from that in all cases, that's not going to work in all cases

If this was the case I see why a solution would not be possible. However ... I don't want that ...

Let me clarify, all I want ... is: a) the ability to refresh the model under any circumstance at any moment. b) the ability of regeneration of DALL code only after the model is validated.

I hope that makes more sense...??

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 17-Dec-2013 16:31:06   

Just giving a suggestion from another user.

Llblgen says table doesn't have primary key. 1) Add primary key in database 2) delete offending entity from model 3) refresh model from database, selecting offending table to reverse engineer if need be)

Doesn't this do the trick?

If not what if you 1) revert to the backup copy of the model prior to the refresh that made it invalid 2) fix database 3) refresh model from database

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Dec-2013 18:13:51   

Llblgen says table doesn't have primary key. 1) Add primary key in database 2) delete offending entity from model 3) refresh model from database, selecting offending table to reverse engineer if need be)

That's what I do in similar cases.

Pablo
User
Posts: 81
Joined: 21-Mar-2005
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Dec-2013 12:56:58   

Pablo wrote:

Otis,

But like I said: if all you want is to refresh and have the model accepted in all cases and generate code from that in all cases, that's not going to work in all cases

If this was the case I see why a solution would not be possible. However ... I don't want that ... Let me clarify, all I want ... is: a) the ability to refresh the model under any circumstance at any moment.

I wished that was possible, but it isn't always possible: an invalid model might be invalid because it's mappings are invalid, or because its entities are invalid, so how to migrate that to a different set of tables, if these tables can influence your model? It can't fix the root issue by itself (otherwise it would have done so wink ) so refreshing it to a different relational model 'might' correct the issues at hand, but that's unclear when the refresh starts: it might very well be that it won't fix the issues, or better: that because of the problems in the model it can't correct anything because it can't e.g. decide which element to pick to map an entity on etc.

Trust me, if it would have been possible, we wouldn't have this thread here, and you would have been able to refresh the model. As I said before, the particular issue you run into is very specific: it is one of the very few issues which could be ignored (to some extend that is) before a refresh, but that's a guess, I can't prove that that is true. And as I can't prove it, I can 'assume' it will work but I don't want to go there as that will perhaps lead to people with botched projects after refreshes where things went wrong and they now have to correct things by hand.

b) the ability of regeneration of DALL code only after the model is validated.

This is already the case, it first validates the model and adjusts the relational model data accordingly before proceeding with the code generation.

Frans Bouma | Lead developer LLBLGen Pro