[baseten-users] Plans for PITA Integration

[Tuukka Norri]

Hi!

Ben Einstein kirjoitti 12.6.2009 kello 7.18:
> Interesting. So are you saying that the UPDATE/INSERT/DELETE would  
> all be taking place only on records in the below-mentioned view?  
> Without a whole bunch of joins, I'm not sure how BX knows which  
> records are current and which aren't. In my testing, these joins  
> would be significantly slower than a simple WHERE condition. Do  
> think performance is not an issue with the single PITA table idea?
I don't have much experience on PTA but I think that indices solve  
much of the performance problems. If you think that performance still  
is an issue, you could also use a single table like this:

CREATE TABLE item (
	id INTEGER,
	title VARCHAR (255),
	"startedOn" TIMESTAMP,
	"endedOn" TIMESTAMP,
	"startedBy" VARCHAR (255),
	"endedBy" VARCHAR (255),
	"startedReason" TEXT,
	"endedReason" TEXT
	-- Add a primary key.
);

CREATE VIEW current_item AS
	SELECT *
	FROM item i
	WHERE i."endedOn" IS NULL;

Again, this would work without making any changes to BaseTen, as long  
as the primary key is added.

> How would you view the database at any other time than current?
Depends on your application, but generally you could fetch from the  
original relation instead of the view, or in case of the separate  
event table, you could fetch from it and make use of relationships.

> How about rolling back?
I'd probably write an SQL function for that.

> I hate to make a design decision based on difficulty of  
> implementation. Because the assistant portion of this system is  
> already done, I think we would move forward based on performance and  
> flexibility.
The reason we like to keep BaseTen's internals, well, internal, is  
that we'd rather have no applications relying on them. This way we can  
rewrite things for clarity, flexibility etc. For that reason I'd  
prefer an implementation in SQL and PL/pgSQL instead of Objective-C.

> Ended by and ended reason are both critical to determine who and why  
> the record was deleted.
Right, I only thought about the case where the current row is  
replaced, not deleted.

> I'm completely open to new ideas and suggestions, but this is way  
> I've built PITA systems before and there was no significant hit on  
> performance, even in very large tables. I believe it makes sense to  
> commit the changes I've made so far to a branch on the SVN server.  
> How does one obtain access to do this?
Marko makes the decision, so he'll probably comment on that soon.
-- 
Best regards,
Tuukka Norri
MK&C