Forum guidelines: As this is not a current problem with LLB, I don't include versions in use etc. We use 2.6 but intend migrating to 3.0 in the near future.
I want to use a view instead of a table for every Entity so that I can pre-filter every entity by the contents of standard columns that exist on - almost - every table.
For example, a table looks like this where the last 5 columns are standard to most tables:
CREATE TABLE [dbo].[v2_Topic_Base](
[IDTopic] [int] NOT NULL,
[CountryCode] nvarchar NOT NULL,
[IDStatus] [int] NULL,
[startDate] [smalldatetime] NOT NULL,
[endDate] [smalldatetime] NOT NULL,
[updateType] [int] NOT NULL,
[publishedStatus] [bit] NOT NULL,
[md5] [varchar](32) NULL,
CONSTRAINT [PK_v2_Topic_Base] PRIMARY KEY CLUSTERED
(
[IDTopic] ASC,
[publishedStatus] ASC,
[startDate] ASC
)
The table's pk is not used as a fk anywhere. The relationships exist in specialised primary key tables:
CREATE TABLE [pk].[v2_Topic_Base](
[IDTopic] [int] IDENTITY(1,1) NOT NULL,
[att1] varchar NOT NULL,
CONSTRAINT [PK_pk_v2_Topic_Base] PRIMARY KEY CLUSTERED
(
[IDTopic] ASC
)
The relationship is then:
ALTER TABLE [dbo].[v2_Topic_Base] WITH CHECK ADD CONSTRAINT [FK_v2_Topic_Base_v2_Topic_Base_1] FOREIGN KEY([IDTopic])
REFERENCES [pk].[v2_Topic_Base] ([IDTopic])
Every time I use this table in the TopicBaseEntity I want it to be filtered like this:
SELECT IDTopic,CountryCode,
IDStatus
FROM v2_Topic_Base
WHERE ( startDate >= CONVERT(DATETIME, '2010-01-28 00:00:00', 102) )
AND ( endDate <= CONVERT(DATETIME, '2010-01-28 00:00:00', 102) )
AND ( publishedStatus = 1 )
(Note: This split in the tables is my current experimental schema and could change if a better one turns up)
The date is supplied at runtime and is either entered by the user or calculated from other tables in the database (e.g. a client might get a certain service from the database as at 6 months ago and another service as at now).
The publishedStatus is at application scope. It would work to have 2 LLB projects - one for work-in-progress and the other for published data as they are never used together.
So, if the generated SQL would normally look like tables joined together, the newly generated sql would look like derived tables joined together. The application developer need not know this is happening and all current code - lots of that already- for LLB will work with the newly designed database schema.
I could ( and currently do for my experimental mini project) create entities from a view like this in the database and supply the dates and published status on every entity fetch, but this isn't practical with 150 entities and almost all requirements need an average 3 to 6 entities to be touched.
Out of interest, this is producing a temporal database where we can query the joined data as at a specific point in time. It also allows wip and and published rows. It is very similar to the approach proposed in this new book:
http://www.amazon.com/Managing-Time-Relational-Databases-Temporal/dp/0123750415
Also available on Safari Online:
http://my.safaribooksonline.com/9780123750419
Could I modify the Adapter template to do this somehow?
Is this something the core SD code would need to do?
Would I need to commission this as a project for SD?
In conclusion, I can do this in a manual and fairly high-maintenance way, but this is a major project that justifies looking at better, more automated approaches.
