[Zope-DB] Two questions (was Re: looping through)
Sun, 16 Sep 2001 16:33:58 -0400
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.
> 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
> create table students (student_id serial, student_name varchar(100) not
> 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 '
> student RECORD;
> for student in select * from students;
> insert into grades (assign_id, student_id) values (NEW.assign_id,
> end loop;
> return NEW;
> ' 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)