[Zope-DB] Two questions (was Re: looping through)

[Russell Hires]

I've been playing with the schemas below for a little bit, and following the 
examples in the zope book as well, but I've got more questions! :-)

First, I don't know if this is a zope question or an SQL question, but they 
are related....I've added students, and assignments, and gotten the result of 
those adds in the grades table, no problem. When I add an assignment, I get 
the assign_id repeated once for each student_id. This is okay in the table, 
but when I format the information to a "gradebook" -- much like a teacher's 
gradebook (the idea here, after all) -- I want it to look like the sample 
I've set up at: http://www.zope.org/Members/rusty/Example_GradeBook . I just 
don't know how to do that. 

Second, I wonder if I should set up a project either at zope.org or on 
sourceforge.net, since my ambition is way beyond my ability to do the things 
that I'm envisioning. My overall plan is pretty comprehensive, and I want 
help putting it together. What do you guys think? 

Thanks for your input. 

Russell


> 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)