[Zope-DB] looping through

[Tom Jenkins]

Russell Hires wrote:
> I got all of that. I want Zope/Python to do the looping part (I think). The 
> form for adding the new assignment is in Zope, the result will (through a 
> couple of steps) be output in zope on a web page. I just don't know how to 
> cause zope/python/postgresql to add new rows to my db, once for each student 
> per assignment. 
> 

Hi Russell,
You don't you don't give any of your table schemas but i think it would 
be easiest to do this in postgresql using a function and a trigger.

but this is how i would set it up...

------ start cut ------

create table assignments (assign_id serial, assign_name varchar(100) not 
null);

create table students (student_id serial, student_name varchar(100) not 
null);

create table grades (grade_id serial, assign_id int4, student_id int4 
not null, grade int4, constraint assign_fk forei
gn key (assign_id) references assignments(assign_id) on delete cascade, 
constraint student_fk foreign key (student_id)
  references students(student_id) on delete cascade);

create function setup_grades() returns opaque as '
declare
   student RECORD;
begin
   for student in select * from students;
     insert into grades (assign_id, student_id) values (NEW.assign_id, 
student.student_id);
   end loop;
   return NEW;
end;
' language 'plpgsql';


create trigger assign_insert_tr
before insert on assignments
for each row execute procedure setup_grades();
------ stop cut ------

Couple of reasons I like this way is:
1) your database server is doing the work, so the application doesn't 
have to;

2) if something should happen to your database server during this 
process, you won't be stuck with somethings inserted and others not. 
now i think zope's zsqlmethods are all wrapped in transactions,but i 
think you'd then have to do all the inserts in one zsqlmethod.

(note this code _has_ been tested and verified to work on PostgreSQL 7.1)

-- 
Tom Jenkins
devIS - Development Infostructure
http://www.devis.com