Writing though views seems to be quite different in different database servers. In Postgresql, it's done using something they call rules. Rules rewrite the queries early on, before they are sent off to the planner. Views are implemented as rules, so we actually already used them in the previous post, even though it didn't show.
If we have a table for forum threads like this:
CREATE TABLE impl.threads
id serial not null,
forum integer not null,
locked integer not null default 0,
sticky integer not null default 0,
CONSTRAINT pk_threads PRIMARY KEY (id),
CONSTRAINT fk_threads_forum FOREIGN KEY ("forum")
REFERENCES impl.fora (id) ON DELETE CASCADE
and we want users to be able to start new threads, but not to be able to specify the
sticky values, we can create a rule on the view like this:
CREATE OR REPLACE RULE posts_insert AS
ON INSERT TO kroll_user.threads
INSERT INTO impl.threads (forum, locked, sticky) VALUES (NEW.forum, 0, 0);
For the private messages view, we can set the sender to the current user, regardless of what the user tried:
create or replace rule messages_insert as
on insert to kroll_user.messages do instead
insert into impl.messages ("to", "from", posted, subject, text)
NEW."to", kroll_user.get_current_app_user(), NEW.posted,
Conditionals get a bit trickier. Posts should only be permitted to threads that are not locked. An insert to a locked thread should be ignored. Due to a limitation in Postgresql, it's impossible for it to figure out that if it shouldn't do anything then it should do nothing, so we have to explicitly tell it:
CREATE VIEW kroll_user.posts as select * from impl.posts;
grant select, insert, update on kroll_user.posts to kroll_role_user;
create or replace rule posts_insert as
on insert to kroll_user.posts
WHERE (select locked from impl.threads where threads.id=NEW.thread)<>1
insert into impl.posts (thread, author, subject, body, created)
NEW.thread, kroll_user.get_current_app_user(), NEW.subject,
create or replace rule posts_insert_default as
on insert to kroll_user.posts
DO INSTEAD NOTHING;
Postgresql runs all rules that have matching
WHERE clauses (and if there is no
WHERE, then the rule always matches), so for a post to a locked thread, it (like the goggles) does
NOTHING, while for a post to an unlocked thread, it both inserts the row and does
Updates are handled almost identically:
create or replace rule posts_update as
ON UPDATE TO kroll_user.posts
select locked from impl.threads
where threads.id=(select thread from impl.posts where id = OLD.id)
SET subject=NEW.subject, body=NEW.body, edited=now()
WHERE id=NEW.id and author=kroll_user.get_current_app_user();
Writing to sequences can be done in a manner similar to how we did reading: by virtualizing the accessor functions:
create function kroll_user.nextval_threads_id_seq() returns bigint as
language sql security definer;
/* ... */
create function kroll_user.nextval(unknown) returns bigint as
when CAST($1 as text)=''messages_id_seq''
when CAST($1 as text)=''posts_id_seq''
when CAST($1 as text)=''threads_id_seq''
else pg_catalog.nextval(CAST($1 as text))
As stated in the beginning, this is how things work in Postgresql, and other database servers will require things to be done in different ways. For Oracle, MS-SQL, and DB2, the views are updatable automatically, without any extra rules. In some cases (like with the
sticky columns), you may have to use triggers to make certain parts non-writeable.
Next post will move back into vendor-neutral theory again. I'll discuss role-based access control and how the database can get involved in enforcing application roles.
Post a Comment
Note: only a member of this blog may post a comment.