Thread: Rules in views, how to?

Hi,

I'am using rules in views, but I'am not sure about how the rules work... let me explain...

For example, I have this table:

[code]
CREATE TABLE "atau_utilizadores" (
    "id" int4 NOT NULL,
    "group_id" int4 NOT NULL,
    "ip_address" char(16) NOT NULL,
    "username" varchar(50) NOT NULL,
    "password" varchar(40) NOT NULL,
    "salt" varchar(40),
    "email" varchar(40) NOT NULL,
    "activation_code" varchar(40),
    "forgotten_password_code" varchar(40),
    "remember_code" varchar(40),
    "created_on" timestamp NOT NULL,
    "last_login" timestamp,
    "active" int4,
    "coment" varchar(2000),
    "id_utiliz_ins" varchar(45),
    "id_utiliz_upd" varchar(45),
    "data_ult_actual" timestamp,
  PRIMARY KEY("id"),
  CONSTRAINT "check_id" CHECK(id >= 0),
  CONSTRAINT "check_group_id" CHECK(group_id >= 0),
  CONSTRAINT "check_active" CHECK(active >= 0)
);
[/code]

And I have also a view to this table with a rule do the user be able to do INSERTS in views:

[code]
CREATE OR REPLACE VIEW "aau_utilizadores" AS
    select * from atau_utilizadores;

CREATE OR REPLACE RULE "ins_aau_utilizadores" AS
    ON INSERT TO "aau_utilizadores"
    DO INSTEAD
        (insert into atau_utilizadores
(id, group_id, ip_address, username, password, salt, email, activation_code,
forgotten_password_code, remember_code, created_on, last_login, active)
values (NEW.id, NEW.group_id, NEW.ip_address, NEW.username, NEW.password,
NEW.salt, NEW.email, null, null, null, NEW.created_on, null, NEW.active));
[/code]

Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain...

Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when "WHERE email = 'X' " or "WHERE id = 'Y' ".

Question: How can I deal with this?

The update rule should be:

[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
[/code]

or

[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
where
(email = OLD.email or id = OLD.id)

???

PS: Sorry for my bad english.


Best Regards,
André
[/code]

On 6 Jul 2010, at 12:28, Andre Lopes wrote:

> Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me
explain...
>
> Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE
rule.For example the UPDATE could be done when "WHERE email = 'X' " or "WHERE id = 'Y' ". 
>
> Question: How can I deal with this?


In the WHERE-clause you use the columns from the OLD record that uniquely identify that record.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c330b82286211968020069!

Hi Alban,

But in my application I have more than one way of uniquely identify the record. Could be by the email field or by the id field.

Thera are update that are done by the WHERE email clause and other by the WHERE id clause.

It is possible to deal with this?

Best Regards,

Ok, I have done the UPDATE RULE like this and works!

[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
where
(id = OLD.id or username = OLD.username or email = OLD.email)
[/code]

Best Regards,

On Tue, Jul 06, 2010 at 12:28:35PM +0100, Andre Lopes wrote:
> Ok, I have done the UPDATE RULE like this and works!

> where
> (id = OLD.id or username = OLD.username or email = OLD.email)

I'm pretty sure you just want to be using the id column above.  Using an
OR expression as you're doing could have some strange side effects.

You may also want to consider a UNIQUE constraint on the username (and
maybe email) fields as well, especially as you've said they should be
able to be used to uniquely determine a user.

--
  Sam  http://samason.me.uk/

Hi,

Thanks for the reply.

In the application there are two kinds of UPDATES to this table.

[code]
        update aau_utilizadores
        set group_id = 3
        where email = pEMAIL;
[/code]

and

[code]
        update aau_utilizadores
        set password = 3
        where id = pNEWPASSWORD;
[/code]

If I use the clause WHERE only in "id" will not work fot both cases, or will work?

Best Regards,

On Tue, Jul 06, 2010 at 12:55:22PM +0100, Andre Lopes wrote:
>         update aau_utilizadores
>         set group_id = 3
>         where email = pEMAIL;
[..]
> If I use the clause WHERE only in "id" will not work fot both cases, or will
> work?

Yes, it'll do the "right thing".  OLD always refers to the previous
version of the row and NEW refers to the new version of the row, you can
use as many or few of the columns as you want.

--
  Sam  http://samason.me.uk/

On 6 Jul 2010, at 13:03, Andre Lopes wrote:

> Hi Alban,
>
> But in my application I have more than one way of uniquely identify the record. Could be by the email field or by the
idfield. 

Unique is unique. There is no other record that could possibly be identified by the same unique identifier. It doesn't
matterif you can identify the same record using other identifiers that are also unique, it's still a unique identifier.
Soif you have a unique identifier for your table you can use on the OLD row in the WHERE clause. 

For example, if you have a primary key on that table you can use it to identify the records for the update. Whether
thatPK uses a simple unique index or a composite unique index doesn't matter at all either. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c33391e286211703874864!