Re: cases in rules problem

On Oct 26, 2007, at 10:28 AM, Darnell Brawner wrote:

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

The problem here is that CASE statements go in queries, not around
them. That leave two options: either create two rules, one for each
case, or go ahead and create a function that gets fired by either a
rule or a trigger. As far as managing the trigger on a lot of
tables, you can script that and I think you'll find that easier to
manage than multiple rules on each table.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com