Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > General> Indexed Views - PK, UK, etc.
 

Pages: 1
General
Indexed Views - PK, UK, etc.
Page:1/1 

  Print all messages in this thread  
Poster Message
magic
User



Location:
Waterloo, ON, Canada
Joined on:
24-Nov-2008 19:57:35
Posted:
125 posts
# Posted on: 23-Dec-2009 05:40:52.  
I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

Also, how do I create UKs in LGP that I have created on the indexed views?

And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)?

Will any of this change in v3.0?
LGP v2.6 Final (May 12th, 2009 / v05122009; Adapter), Web Application (.Net 2.0, SqlServer 2005)  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7667 posts
# Posted on: 23-Dec-2009 06:16:09.  
magic wrote:
I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

Indexed views are, after all, views right? AFAIK this is a limitation of the connector.

magic wrote:
If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

No. But you can control this kind of stuff in Project properties


magic wrote:
Also, how do I create UKs in LGP that I have created on the indexed views?

Please elaborate more on what does "create UKs in LGP" means and what you want it for.

magic wrote:
And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)?

this isn't supported yet


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34993 posts
# Posted on: 23-Dec-2009 11:07:58.  
magic wrote:
I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

Technically, there's no PK on the indexed view, or if you mean with PK a clustered index, of course there's this index, but it's not a PK: it's not a referential identifying element, although in indexed views, the index has to be unique.

So let's say you can assume the unique clustered index is something we can use as a PK, a surrogate key. The problem now is: how to determine if the view is an indexed view? If you look in sys.Views, you'll see there's no difference.

The query to obtain the pk fields results in 0 rows for the indexed view (when joined with sys.views instead of sys.tables), the same for unique constraints. In fact, sys.key_constraints has no row for the indexed view. This leaves only one thing: they're not recognizable as an indexed view, at least we don't know where to pull that info from. I find it strange that the info isn't in the sys. schema views btw. No view has the info, also INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE doesn't.

Quote:

If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

If you define a PK on the entity manually and the target is a view, the pk definition will persist in the project, as the view has no pk information, and the refresher knows that.

Quote:

Also, how do I create UKs in LGP that I have created on the indexed views?

In v2.x you can't. This is added in v3 where you can define unique constraints on entities which are then reflected in generated code and also in the meta-data (if the target is a table of course)

Quote:

And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)?
Will any of this change in v3.0?

They're not separated because you can't see they're different. (at least we can't find a way to pull that info from the meta-data. )

In v3 you can define unique constraints in the designer. It will lead to DDL SQL for tables, which you can toss away if you don't want to apply that to the db. You can define in the designer if UCs in the model should follow pk UCs. However with a target on a view, the target (view) is ignored for model element syncing so all model elements are kept as-is as the refresher knows that these elements can't be determined on views.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
magic
User



Location:
Waterloo, ON, Canada
Joined on:
24-Nov-2008 19:57:35
Posted:
125 posts
# Posted on: 23-Dec-2009 15:13:06.  
As always, I appreciate your detailed answers. Regular Smiley Thank you.

I'll try to summarize in my own words: If I want to create an entity based on an indexed view based on the unique clustered index (let's call it a PK for the sake of simplicity), I have to "teach LGP manually" that such an index exists by identifying the PK columns manually. This information can't be retrieved manually, but it also won't get lost if I refresh the indexed view(s).

As for now (v2.x) there is no possibility to also reflect other unique indexes created on the indexed view, but there will be one soon (v3.0 is supposed to come in less than a month, right?).

Besides: MsSQL sucks (in terms of indexed views)! Not only can't they differentiate properly as you outlined, but you can't do so many things you can do with with them in Oracle ...


LGP v2.6 Final (May 12th, 2009 / v05122009; Adapter), Web Application (.Net 2.0, SqlServer 2005)  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
13799 posts
# Posted on: 23-Dec-2009 15:17:35.  
You got it right.
  Top
magic
User



Location:
Waterloo, ON, Canada
Joined on:
24-Nov-2008 19:57:35
Posted:
125 posts
# Posted on: 23-Dec-2009 16:03:53.  
Another thought on the topic: What if LGP offered a possibility to manually identify an index as the base for the PK or UK?

This way the "manual identification" (choosing the columns that belong to it) of such keys would a) be simplified and b) if the index would be changed or dropped, the associated key could be updated automatically by LGP?


LGP v2.6 Final (May 12th, 2009 / v05122009; Adapter), Web Application (.Net 2.0, SqlServer 2005)  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34993 posts
# Posted on: 24-Dec-2009 12:07:38.  
magic wrote:
Another thought on the topic: What if LGP offered a possibility to manually identify an index as the base for the PK or UK?

This way the "manual identification" (choosing the columns that belong to it) of such keys would a) be simplified and b) if the index would be changed or dropped, the associated key could be updated automatically by LGP?

but there's no index / constraint pulled from the meta-data for the view. So it can never detect if it's dropped. Which IMHO comes down to simply specifying what the PK fields are manually, which is what you are doing now as well (no db constraint needed).
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
magic
User



Location:
Waterloo, ON, Canada
Joined on:
24-Nov-2008 19:57:35
Posted:
125 posts
# Posted on: 24-Dec-2009 14:44:07.  
hmmm ... did I already say that MsSQL sucks? Wink


LGP v2.6 Final (May 12th, 2009 / v05122009; Adapter), Web Application (.Net 2.0, SqlServer 2005)  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34993 posts
# Posted on: 24-Dec-2009 18:31:03.  
magic wrote:
hmmm ... did I already say that MsSQL sucks? Wink

haha Laugh you did, but if you feel the need, you can re-state it Wink
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.