Hello,
I work for a company that utilizes PeopleSoft
(http://en.wikipedia.org/wiki/PeopleSoft) which is a large ERP system. My
job is to write applications which interface with PeopleSoft, specifically
connecting to its database and using its data. We utilize PeopleSoft on
MSSQL. I have been using Doctrine, PeopleSoft, and MSSQL for almost two
years, but in order to make it work I have kind of swept some things under
the bed and pretended they weren't there. Before I begin, remember, I have
no control over the schema. This is delivered software.
PeopleSoft does not use a generated id field. The primary key is always a
composite id made up of several other fields. One field which is almost
always a key is called the effective date. The effective date tells
PeopleSoft when that particular row comes into service. For example, if
there is a table that keeps tracks of rooms, it might have two fields, a
room name, and effective date. If two rows have the same room name, then
you know which one is the correct row by examining the effective dates.
Whichever effective date is in the past, but closest to now, is the correct
one. In SQL, this is expressed in terms of a subquery:
rooms.effective_date = (
SELECT MAX(effective_date)
FROM rooms rooms_effective_date
WHERE rooms_effective_date.room_name = rooms.room_name
AND rooms_effective_date.effective_date <= GETDATE() )
The subquery says basically get me biggest effective date which is also in
the past. The join fields for the subquery are based on the keys of the
table. Pretty painful already, right? So when I query to find say a room, I
don't punch in room name = X and effective date = Y, I have to write a
subquery for the effective date. Most of the time GETDATE() is used, but if
I want historical data, I put in whatever date I am concerned with.
Up until now, I have been hiding this nastiness from Doctrine by pretending
that effective dates are never ids, even though they most certainly are. I
have used a Doctrine filter to perform the subquery based on a date I
input, and I generally get the correct data.
But, this solution has its problems, mainly related to the identity map.
For instance, I can't ever see the historical data for something, because
once doctrine has put a room name of X into the identity map, it will load
it from memory instead of querying for a different effective date. Because
of that, I can't take advantage of the second level cache, which I would
would very much like to do.
In my ideal world, I would love to notify Doctrine that effective date is a
key, but be able to omit it when querying. I'd love to be able to find
rooms that are equal to X, and if I omit the effective date, have Doctrine
perform the subquery to find the most recent one. But if I provide it, then
go fetch that particular one.
I have briefly tried letting Doctrine know that it is a key, and poke my
way around internals, but I don't believe that it is going to be a viable
solution. There are several places in Doctrine that quite quickly, and
correctly, complain that I have not provided all of the key data when doing
fetches or proxies.
I have considered writing a custom DBAL type for the effective date, which
would transform a given date into the subquery needed, but the problem is
that types aren't provided metadata or information about what entity they
belong to, so I have no way of knowing the other identifiers to write the
subquery.
I am all out of ideas, and would love it if anyone out there has any.
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
