[baseten-users] Plans for PITA Integration

[Ben Einstein]

Hi Tuukka,

> 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.

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?

How would you view the database at any other time than current? How  
about rolling back?

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.

> 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.
> );

Ended by and ended reason are both critical to determine who and why  
the record was deleted.

> 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;

I can see why this could work, but what are its advantages over adding  
a few lines to the query builder that does one (relatively simple)  
thing: adding either "WHERE "endedOn" < aDate AND "endedOn > aDate" or  
"WHERE "endedOn" IS NULL" to each relation in a query.

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?

Thanks,
Ben