Re: [Zope] Zope Pgsql 7.0/SQL Schema HeLp!

2000-05-22 Thread Richard Moon

Here is a ZSQL Method (called insert_author) which inserts into a 
PostgreSQL table which has a serial column called author_id.

Arguments

surname
search_name
initials
known_name
user_id
notes

Query Template
--
insert into author
(surname,search_name,initials,
known_name,user_id,notes)
values(
'dtml-var surname sql_quote',
'dtml-var search_name sql_quote',
'dtml-var "_.string.upper(initials)" sql_quote',
'dtml-var "_.string.capwords(known_name)" sql_quote',
'dtml-var user_id',
'dtml-var notes')
dtml-var sql_delimiter
select currval('author_author_id_seq')

Note that we don't insert anything into the serial column author_id since 
PostgreSQL puts that in for you.

Here is the schema for that table

CREATE TABLE author ( author_id serial, surname text, search_name text, 
initials text, known_name text, user_id int4, notes text

And here is a snippet from a dtml method which performs the insert and 
retrieves the value of author_id for the row just inserted.

dtml-in insert_author
   dtml-call expr="REQUEST.set('author_id',currval)"
/dtml-in
Author_id is dtml-var author_id

This assumes all the arguments to insert_author (that is surname, user_id 
etc) have already been set up from a form or in some other way.

HTH

Richard


At 23:22 20/05/00 -0400, you wrote:
This is going to be a long winded questions.

Ok I have been hacking around with Zope and Pgsql for awhile now.
I am trying to learn both at the same time, not any easy task, I don't
know SQL that well. I learn by doing ,and this is really fun, kinda like
a
digital puzzle of sorts. So any way I set up a database in pgsql to hold
contact information. This was no small task for me, like I said, I am
new to SQL.
I did discover a neat way to use CREATE VIEW foo AS SELECT that is not
in the book though. Anyway the PRIMARY KEY for the table of names is a
SERIAL type, all other tables such as phone numbers and addresses use
REFERENCES to this KEY. The database works quite well, I think I have it
normalize as much as need be (bare with me I am still learning). Now on
to Zope

I have Zope all set, and I can run all your standard SELECT, INSERT,
UPDATE stuff.
But I have one hitch,  I can't seem to figure out how to make it so an
end user
could add a name and email address without knowing about the PKEY
number. This is no problem for a DBA but I want to make it easy for
anyone to INSERT and UPDATE records. Did I make a bad design choice in
use unique numbers to glue all the tables together. Is this an interface
problem. Am I missing something. I need some help. Can someone point me
in the right direction please?

Richard

___
Zope maillist  -  [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists -
  http://lists.zope.org/mailman/listinfo/zope-announce
  http://lists.zope.org/mailman/listinfo/zope-dev )



Richard Moon
[EMAIL PROTECTED]


___
Zope maillist  -  [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists - 
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )




Re: [Zope] Zope Pgsql 7.0/SQL Schema HeLp!

2000-05-22 Thread ozric

--snip--
I got help from one of our DBA's today here is what I was missing.  This
in not in Bruce's Book.

INSERT INTO address (per_id,street_num,city,state,zip) 
SELECT per_id,'$3','$4','$5','$6' 
FROM person
WHERE last = '$1' 
AND first = '$2'; 

$1-6 will be supplied by user input from Zope,  I just wanted to isolate
the per_id from person during an Insert so that end users would not need
to
know it was there.  I know I might have a problem with getting more then
one
return for just first and last,  I might add more WHERE statements in
there. I am
just happy to get moving on with my little project.

Thanks for the help
Richad

___
Zope maillist  -  [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists - 
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )