Thread: cases in rules problem

cases in rules problem

From

Darnell Brawner

Date:

26 October 2007, 15:28:18

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.

Re: cases in rules problem

From

Erik Jones

Date:

26 October 2007, 16:05:15

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

Re: cases in rules problem

From

Darnell Brawner

Date:

26 October 2007, 18:52:59

Ok i gave up just keeping this in  rules so i did this

CREATE OR REPLACE RULE version AS ON UPDATE TO
vmain
DO INSTEAD
    select version2
(OLD.id,NEW.title,OLD.parent_id,NEW.public,OLD.public);

CREATE OR REPLACE FUNCTION version2(int,varchar,int,bool,bool)
RETURNS VOID
LANGUAGE 'plpgsql' AS '
DECLARE
     _id         ALIAS FOR $1;
     _title      ALIAS FOR $2;
     _parent_id  ALIAS FOR $3;
     _n_public   ALIAS FOR $4;
     _o_public   ALIAS FOR $5;
BEGIN
     IF _n_public <> true or _o_public <> false
         THEN update main set public=true where id=_id;
     ELSE
          INSERT INTO main(parent_id,title,public) VALUES
(_parent_id,_title,false);
     END IF;
RETURN;
END
';

But when i run
update vmain set title='tah4' where id=6
which in theory should insert a new row with parent_id=2,
title='tah4' and public = false
instead i get this error:
  [UPDATE - 0 row(s), 0.003 secs]  [Error Code: 0, SQL State: 23505]
ERROR: duplicate key violates unique constraint "firstkey"


On Oct 26, 2007, at 12:04 PM, Erik Jones wrote:

> 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@myemma.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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster