[baseten-users] Plans for PITA Integration

[Tuukka Norri]

Ben Einstein kirjoitti 9.6.2009 kello 17.12:
> Sorry, I left out a field! There's also a "pitaPrimaryKey" field in  
> all the tables that holds the reference to the current/original  
> record. As of now, the format for that column is dynamically pulled  
> from the existing primary key in the DB. In the databases I've  
> worked on before, there's usually just a single primary key, but I'm  
> starting to worry that could be an issue here? Maybe we should  
> discuss the design a bit more? Is that appropriate for this mailing  
> list?

Hi!

I think it's quite appropriate. I mentioned this to my colleagues and  
we thought that the events could possibly be stored into their own  
table with an integer primary key. The data table could then have a  
single-column foreign key that would reference the event information.  
Adding six or seven fields to every table that you need seems somewhat  
complex.

Also, if you want to get by without rewriting parts of BaseTen, you  
could implement PTA using SQL views. There could be a view for the  
current data, which would always fetch the most recent version of the  
rows. History would be accessible by fetching from the actual table or  
by fetching from the event table and accessing a relationship. Here's  
a quickly written (and incomplete) example:

CREATE TABLE event (
	id SERIAL PRIMARY KEY,
	"startedOn" TIMESTAMP,
	"endedOn" TIMESTAMP,
	"startedBy" VARCHAR (255),
	"startedReason" TEXT
	-- endedBy and endedReason seemed superfluous, so left them out.
);

CREATE TABLE item (
	id INTEGER,
	eid INTEGER REFERENCES event (id),
	name VARCHAR (255),
	PRIMARY KEY (id, eid)
);
-- Triggers should be added to rewrite UPDATE as an INSERT and to add  
the event.


CREATE VIEW current_item AS
	SELECT
		i.id, i.eid, i.name,
		e."startedOn", e."endedOn", e."startedBy", e."startedReason"
	FROM item i
	INNER JOIN event e ON (i.eid = e.id)
	WHERE e."endedOn" IS NULL;

-- 
Best regards,
Tuukka Norri
MK&C