[Zope-DB] looping through

Tom Jenkins tjenkins@devis.com
Fri, 31 Aug 2001 11:20:51 -0400

Sorry about dumping alot of code on you, especially in the grades table. 
  the foreign key constraints in the grades table will handle deleting 
any grades for students or assignments that have been deleted.  you may 
or may not want that.  if not then take out the constraint lines.

just to reiterate:
you don't have to do anything special from zope to get the grades 
inserted.  just do a normal zsqlmethod and insert into assignments

Tom Jenkins wrote:
> 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