# This is the target program for the form in quotations.tcl. 

# The expected variables here are author_name, category, quote

set_the_usual_form_variables

set exception_count 0 
set exception_text ""

# It's always a good idea to do error checking on information that is typed in by users. 

if { ![info exists author_name] || [empty_string_p $author_name] } {
    incr exception_count
    append exception_text "<li>Posting stuff on the Internet without attribution is rude.
                               Please enter an author name.\n"
}

if { $exception_count > 0 } {
    ad_return_complaint $exception_count $exception_text
    # terminate execution of this thread (a goto!)
    return
}

# At this point the user's input is reasonably good

# Now we'll do the insertion in the quotations table.  We get a handle
# (connection to the database) and use it together with ns_db dml
# ("data manipulation language") to issue commands that lock the table
# to protect against simultaneous updates from multiple users of this
# service.  One almost never needs explicit "lock table" commands in
# Oracle.  This is a rare situation where we are reading information
# from the database and then performing an update relying on the
# information we read not to have changed.

set db [ns_db gethandle]

ns_db dml $db "begin transaction"
ns_db dml $db "lock table quotations in exclusive mode"

# To generate the appropriate quotation_id for the new quote, we
# need one more than the maximum quotation_id currently in the table.
# (Note that if the table is empty, and max(quotation_id) is NULL, the
# sum of NULL and 1 will be NULL.

set selection [ns_db 1row $db "select max(quotation_id)+1 from quotations"]

# The API used here, ns_db 1row, is for getting a single row.  It
# returns a set, just like ns_db select, but unlike ns_db select, this
# set has the values for that row already filled in.

# Now we pull out the first (and only) value from that set, which will
# be the incremented max(quotation_id).

set new_key [ns_set value $selection 0]

# One thing to check:  If the quotations table was empty, then
# max(quotation_id) would be NULL, and max(quotation_id)+1 would be
# NULL. 

if [empty_string_p $new_key] {
    # there were no rows in the database so we got NULL back,
    # represented in Tcl by the empty string (this is necessary
    # because SQL uses three-valued logic, which Tcl does not 
    # support)
    set new_key 1
}

# Now that we have the quotation_id, we do the insertion, end the
# transaction (unlocking the table), and redirect the client browser
# back to the quotations page.

ns_db dml $db \
     "insert into quotations
      (quotation_id, insertion_date, author_name, category, quote)
      values
      ($new_key, sysdate, '$QQauthor_name', '$QQcategory', '$QQquote')
     "

ns_db dml $db "end transaction"

ns_returnredirect quotations.tcl

# Observe the use of the QQ variables above.  This is a machanism that
# our toolkit uses for dealing with an annoying artfact of the interface
# with SQL. The problem is that SQL quotes data using single quotes
# ('). The data here is a string that was suppled by the user.  If that
# string itself has a single quote in it, then the query string to
# Oracle will be malformed unless we "quote the quote" by doubling it.
# I.e., if the author name was O'Conner, our query input needs to
# specify 'O''Conner' rather than 'O'Conner'. The toolkit contains a
# procedure called DoubleApos that will perform this transformation,
# 
# i.e.,
#        [DoubleApos O'Conner] ---> O''Conner
# 
# As a convenience, the procedure set_the_usual_form_variables defines
# two variables for each form element, one with the apostrophes doubled.
# That is, if the form returns a variable called author, whose value is
# O'Conner, then set_the_usual_form_variables will create a variable
# called author whose value is O'Conner, and a variable called QQAuthor
# whose value is O''Conner.  In general, you'll want to use these QQ
# variables in SQL queries that pass names based on data entered by
# users.

