hidden versioning system - Mailing list pgsql-sql

From Darnell Brawner
Subject hidden versioning system
Date
Msg-id 9E9F87EB-6EEB-4479-9BC2-4740839754C5@smackdabstudios.com
Whole thread Raw
List pgsql-sql
I am trying to make a sql based versioning system.
I am working on a Ruby on Rails project and am using a plugin called  
hobo the plugin can do some nice things but over all its lame but  
thats what i got  to work with.
The problem is hobo does a lot of work for you but the database most  
be in a standard format to use it.
so my idea for a sql versioning  work around was this.

CREATE TABLE main(id serial CONSTRAINT firstkey PRIMARY KEY,parent_id int,title varchar(30),public boolean default
false);

INSERT INTO main(parent_id,title,public)
VALUES(1,'blah',true),(1,'tah',false),(1,'blah2',false),(1,'blah3',false),(2,'tah2',false),(2,'tah3',true);

CREATE VIEW  vmain as
(SELECT * FROM main       WHERE public=true       ORDER BY id DESC)       UNION
(SELECT *       FROM main       WHERE id IN (select max(id) from main group by parent_id)       ORDER BY id DESC)

CREATE OR REPLACE RULE main_up AS ON UPDATE TO vmain
DO INSTEAD
INSERT INTO main(parent_id,title,public)
VALUES(NEW.parent_id,NEW.title,false);

the result of the view should be all rows with public as true and one  
false for each new parent_id  if any that must have a higher id than  
the true one.

So on the web server, someone of level writer can edit something a  
superuser has created but what happens is it puts the update into the  
view hits the rule and makes a dup in the main table with public set  
to false so no one on the outside can see it. And basically the most  
rows that show up will be the public on and the highest id private  
one i don't really care about them rolling back versions.

My problem is when the admin wants to approve the private row.  I tryed
CREATE OR REPLACE RULE main_up AS ON UPDATE TO vmain
DO INSTEAD
CASE NEW.public = true and OLD.public = false
THENUPDATE main set public=true where id=NEW.id
ELSE
INSERT INTO main(parent_id,title,public)
VALUES(NEW.parent_id,NEW.title,false);

But i can't seem to put CASE statements in a rule is there any why i  
can do then with out having to create a function and rule that fires it?
This has to  go on alot of table.



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: JOINing based on whether an IP address is contained within a CIDR range?
Next
From: "Fernando Hevia"
Date:
Subject: Re: request for help with COPY syntax