% cvs annotate insert-msg.tcl
1.1 (jsc 27-Feb-99): if {[ad_read_only_p]} {
1.1 (jsc 27-Feb-99): ad_return_read_only_maintenance_message
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.4 (philg 19-Apr-99): ad_handle_spammers
1.4 (philg 19-Apr-99):
1.1 (jsc 27-Feb-99): # bboard_already_notified_p
1.1 (jsc 27-Feb-99): # Returns 1 if the user is already queued to be emailed. 0 if not.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # "to_email" is the email address of the user receiving the email
1.1 (jsc 27-Feb-99): # "email_queue" is a list of ns_set id's
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # The target email address for the alert is associated with the
1.1 (jsc 27-Feb-99): # "to" key of each ns_set in $email_queue
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # This proc compares "to_email" with all the
1.1 (jsc 27-Feb-99): # values in the "to" key of each ns_set in email_queue and
1.1 (jsc 27-Feb-99): # returns 1 if there is a match, or 0 if not
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): proc bboard_already_notified_p { to_email email_queue } {
1.1 (jsc 27-Feb-99): foreach email $email_queue {
1.1 (jsc 27-Feb-99): if { [string compare $to_email [ns_set get $email to]] == 0 } {
1.1 (jsc 27-Feb-99): # email matched
1.1 (jsc 27-Feb-99): return 1
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): # we've search all the ns_sets and have not found a match
1.1 (jsc 27-Feb-99): return 0
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # Returns ns_set specifying a message to be sent to a user somewhere
1.1 (jsc 27-Feb-99): # regarding a new message posting on the bboard. We enqueue everything
1.1 (jsc 27-Feb-99): # so that the production thread can release the database handle and not
1.1 (jsc 27-Feb-99): # bring down the Web server if the mail transfer agent is down.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): proc bboard_build_email_queue_entry {to from subject body user_message {extra_headers ""}} {
1.1 (jsc 27-Feb-99): set email_ns_set [ns_set create email_queue_entry]
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set to $to
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set from $from
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set subject $subject
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set body $body
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set user_message $user_message
1.1 (jsc 27-Feb-99): if ![empty_string_p $extra_headers] {
1.1 (jsc 27-Feb-99): ns_set put $email_ns_set extraheaders $extra_headers
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): return $email_ns_set
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # we use notify_if_requested_build to build up a list
1.1 (jsc 27-Feb-99): # of emails to be sent to users who previously posted
1.1 (jsc 27-Feb-99): # a message in this thread
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # new_msg_id is the msg_id of the message we are inserting
1.1 (jsc 27-Feb-99): # notify_msg_id is the msg_id of a previous post
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # email_queue is a list of ns_sets that store the email information
1.1 (jsc 27-Feb-99): # notify_if_requested_build returns email_queue with additional
1.1 (jsc 27-Feb-99): # ns_set's appended if necessary
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # "Called recursively up the list of bboard postings that
1.1 (jsc 27-Feb-99): # are related to a new posting. Returns email queue (list of ns_sets)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): proc notify_if_requested_build { db new_msg_id notify_msg_id from subject_line body email_queue } {
1.1 (jsc 27-Feb-99): set selection [ns_db 1row $db "select users_alertable.email,
1.1 (jsc 27-Feb-99): refers_to, notify, bboard.user_id as prior_poster_user_id
1.1 (jsc 27-Feb-99): from bboard, users_alertable
1.1 (jsc 27-Feb-99): where msg_id = '$notify_msg_id'
1.1 (jsc 27-Feb-99): and users_alertable.user_id(+) = bboard.user_id"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): if { $notify == "t" && ![empty_string_p $prior_poster_user_id] } {
1.1 (jsc 27-Feb-99): # user asked to be notified
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set shut_up_url "[bboard_url_stub]shut-up.tcl?msg_id=$notify_msg_id"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { ![bboard_already_notified_p $email $email_queue] } {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # user isn't queued to receive the email yet
1.1 (jsc 27-Feb-99): set customized_body "$body
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -------------
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): If you are no longer interested in this thread, simply go to the
1.1 (jsc 27-Feb-99): following URL and you will no longer get these notifications:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): $shut_up_url
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -------------
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Note: this message was sent by a robot.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99): set email_queue_entry [bboard_build_email_queue_entry $email $from $subject_line $customized_body "<li>sent a note to $email, to whose message you are responding.\n"]
1.1 (jsc 27-Feb-99): lappend email_queue $email_queue_entry
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { ![empty_string_p $refers_to] } {
1.1 (jsc 27-Feb-99): # recurse with all the same args except NOTIFY_MSG_ID
1.1 (jsc 27-Feb-99): set email_queue [notify_if_requested_build $db $new_msg_id $refers_to $from $subject_line $body $email_queue]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): return $email_queue
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set_the_usual_form_variables
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # body, one_line, notify, html_p
1.1 (jsc 27-Feb-99): # topic was a hidden var
1.1 (jsc 27-Feb-99): # q_and_a_p is an optional variable, if set to "t" then this is from
1.1 (jsc 27-Feb-99): # the Q&A forum version
1.1 (jsc 27-Feb-99): # refers_to is "NEW" or a msg_id (six characters)
1.1 (jsc 27-Feb-99):
1.2 (philg 05-Mar-99): # we MAY get an image or other file along with this msg, which means
1.2 (philg 05-Mar-99): # we'd get the file name in "upload_file" and can get out the temp file
1.2 (philg 05-Mar-99): # with ns_queryget
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # we're going to need to subquery for instant keyword matching
1.1 (jsc 27-Feb-99): # and/or looking around for state and county from tri_id
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set db_pools [ns_db gethandle [philg_server_default_pool] 2]
1.1 (jsc 27-Feb-99): set db [lindex $db_pools 0]
1.1 (jsc 27-Feb-99): set db_sub [lindex $db_pools 1]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if {[bboard_get_topic_info] == -1} {
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set return_url [bboard_raw_backlink $topic $presentation_type 1]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { $presentation_type == "usgeospatial" } {
1.1 (jsc 27-Feb-99): if { $refers_to == "NEW" } {
1.1 (jsc 27-Feb-99): if { [info exists tri_id] && ![empty_string_p $tri_id] } {
1.1 (jsc 27-Feb-99): # we have a tri_id, have to look up epa region
1.1 (jsc 27-Feb-99): set selection [ns_db 1row $db_sub "select st as usps_abbrev, sema_zip as zip_code, fips_county_code from rel_search_fac where tri_id = '$QQtri_id'"]
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): set epa_region [database_to_tcl_string $db_sub "select epa_region from bboard_epa_regions where usps_abbrev = '$usps_abbrev'"]
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): # we'll send them back to the region level
1.1 (jsc 27-Feb-99): set full_anchor "<a href=\"usgeospatial-2.tcl?[export_url_vars topic epa_region]\">the $topic (Region $epa_region) forum</a>"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): # a reply, try to send them back to their thread
1.1 (jsc 27-Feb-99): set full_anchor [bboard_usgeospatial_about_link $db $refers_to]
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set full_anchor "<a href=\"[bboard_raw_backlink $topic $presentation_type 0]\">$topic forum</a>"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ## I moved the helper functions into defs.tcl
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # check the user input first
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set exception_text ""
1.1 (jsc 27-Feb-99): set exception_count 0
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { ![info exists one_line] || [empty_string_p $one_line] } {
1.1 (jsc 27-Feb-99): append exception_text "<li>You need to type a subject line\n"
1.1 (jsc 27-Feb-99): incr exception_count
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { ![info exists message] || [empty_string_p $message] } {
1.1 (jsc 27-Feb-99): append exception_text "<li>You need to type a message; there is no \"Man/woman of Few Words Award\" here. \n"
1.1 (jsc 27-Feb-99): incr exception_count
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.3 (philg 19-Apr-99): set selection [ns_db select $db "select the_regexp, scope, message_to_user
1.3 (philg 19-Apr-99): from bboard_bozo_patterns
1.3 (philg 19-Apr-99): where topic = '$QQtopic'"]
1.3 (philg 19-Apr-99):
1.3 (philg 19-Apr-99): while { [ns_db getrow $db $selection] } {
1.3 (philg 19-Apr-99): set_variables_after_query
1.3 (philg 19-Apr-99): if { $scope == "one_line" || $scope == "both" } {
1.3 (philg 19-Apr-99): # let's check the subject line for this regexp
1.3 (philg 19-Apr-99): if [regexp -nocase $the_regexp $one_line] {
1.3 (philg 19-Apr-99): incr exception_count
1.3 (philg 19-Apr-99): append exception_text "<li>$message_to_user\n"
1.3 (philg 19-Apr-99): # you can only be a bozo once
1.3 (philg 19-Apr-99): break
1.3 (philg 19-Apr-99): }
1.3 (philg 19-Apr-99): }
1.3 (philg 19-Apr-99): if { $scope == "message" || $scope == "both" } {
1.3 (philg 19-Apr-99): if [regexp -nocase $the_regexp $message] {
1.3 (philg 19-Apr-99): incr exception_count
1.3 (philg 19-Apr-99): append exception_text "<li>$message_to_user\n"
1.3 (philg 19-Apr-99): # you can only be a bozo once
1.3 (philg 19-Apr-99): break
1.3 (philg 19-Apr-99): }
1.3 (philg 19-Apr-99): }
1.3 (philg 19-Apr-99): }
1.3 (philg 19-Apr-99):
1.1 (jsc 27-Feb-99): if [catch { set n_previous [database_to_tcl_string $db "select count(*) from bboard
1.1 (jsc 27-Feb-99): where topic = '$QQtopic'
1.1 (jsc 27-Feb-99): and one_line = '$QQone_line'
1.1 (jsc 27-Feb-99): and dbms_lob.instr(message,'$QQmessage') > 0"]} errmsg] {
1.1 (jsc 27-Feb-99): ns_log Notice "failed trying to look up previous posting: $errmsg"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): # lookup succeeded
1.1 (jsc 27-Feb-99): if { $n_previous > 0 } {
1.1 (jsc 27-Feb-99): incr exception_count
1.1 (jsc 27-Feb-99): append exception_text "<li>There are already $n_previous messages in the database with the same subject line and body. Perhaps you already posted this? Here are the messages:
1.1 (jsc 27-Feb-99): <ul>
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99): set selection [ns_db select $db "select u.first_names, u.last_name, u.email, bb.posting_time
1.1 (jsc 27-Feb-99): from bboard bb, users u
1.1 (jsc 27-Feb-99): where bb.user_id= u.user_id
1.1 (jsc 27-Feb-99): and bb.topic = '$QQtopic'
1.1 (jsc 27-Feb-99): and bb.one_line = '$QQone_line'
1.1 (jsc 27-Feb-99): and dbms_lob.instr(message,'$QQmessage') > 0"]
1.1 (jsc 27-Feb-99): while {[ns_db getrow $db $selection]} {
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): append exception_text "<li>$posting_time by $first_names $last_name ($email)\n"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): append exception_text "</ul>
1.1 (jsc 27-Feb-99): If you are sure that you also want to post this message, then back up and change at least one character in the subject or message area, then resubmit."
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { $exception_count> 0 } {
1.1 (jsc 27-Feb-99): ad_return_complaint $exception_count $exception_text
1.1 (jsc 27-Feb-99): return 0
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # if we get here, the user input checked OK
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # before we overwrite all of these user inputs, let's cat them
1.1 (jsc 27-Feb-99): # together so that we can do instant keyword-specific alerts
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): #check for the user cookie
1.3 (philg 19-Apr-99): set user_id [ad_verify_and_get_user_id]
1.3 (philg 19-Apr-99): if { $user_id == 0 } {
1.1 (jsc 27-Feb-99): ns_returnredirect "/register/index.tcl"
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.3 (philg 19-Apr-99): # for people who are looking for a user's name or email,
1.3 (philg 19-Apr-99): # we need to get this from the users table
1.3 (philg 19-Apr-99): set selection [ns_db 1row $db "select first_names, last_name, email
1.3 (philg 19-Apr-99): from users
1.3 (philg 19-Apr-99): where user_id=$user_id"]
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set name "$first_names $last_name"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set indexed_stuff "$name $email $one_line $message"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ReturnHeaders
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_write "[ad_header "Inserting Message"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): <h2>Inserting Message</h2>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): into the $full_anchor
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): <hr>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): We're going to try the insert now...
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): <p>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if [catch { ns_db dml $db "begin transaction"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # this will grab the exclusive lock on the msg_id_generator table
1.1 (jsc 27-Feb-99): # that will keep another copy of this same script from doing anything
1.1 (jsc 27-Feb-99): # more than waiting here.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set last_id [ns_set value [ns_db 1row $db "select last_msg_id from msg_id_generator for update of last_msg_id"] 0]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set new_id [increment_six_char_digits $last_id]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_db dml $db "update msg_id_generator set last_msg_id = '$new_id'"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { $refers_to == "NEW" } {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # this is a new message
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set sort_key $new_id
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set final_refers_to "NULL"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # we are referring to some older message
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set final_refers_to "'$refers_to'"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set sort_key_of_referred_to_msg [database_to_tcl_string $db "select unique sort_key from bboard where msg_id = '$refers_to'"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set new_sort_key_form [new_sort_key_form $sort_key_of_referred_to_msg]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set highest_current_sort_key_at_this_level [database_to_tcl_string $db "select max(sort_key) from bboard where sort_key like '$new_sort_key_form'"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set sort_key [new_sort_key $sort_key_of_referred_to_msg $highest_current_sort_key_at_this_level]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # sometimes an optional $category variable is included
1.1 (jsc 27-Feb-99): if { [info exists category] && $category != "" } {
1.1 (jsc 27-Feb-99): set category_target ",category"
1.1 (jsc 27-Feb-99): set category_value ",'$QQcategory'"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set category_target ""
1.1 (jsc 27-Feb-99): set category_value ""
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # sometimes an optional custom_sort_key and custom_sort_key_pretty
1.1 (jsc 27-Feb-99): if { ([info exists custom_sort_key] && $custom_sort_key != "") || ($custom_sort_key_p == "t" && $custom_sort_solicit_p == "t" && $custom_sort_key_type == "date") } {
1.1 (jsc 27-Feb-99): # there was a form var called "custom_sort_key" or we're looking
1.1 (jsc 27-Feb-99): # for a magically encoded date
1.1 (jsc 27-Feb-99): set custom_target ",custom_sort_key"
1.1 (jsc 27-Feb-99): if { $custom_sort_key_type == "date" } {
1.1 (jsc 27-Feb-99): # have to decode from widget
1.1 (jsc 27-Feb-99): ns_dbformvalue [ns_conn form] custom_sort_key date custom_sort_key
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): set custom_value ",'[DoubleApos $custom_sort_key]'"
1.1 (jsc 27-Feb-99): if { [info exists custom_sort_key_pretty] && $custom_sort_key_pretty != "" } {
1.1 (jsc 27-Feb-99): append custom_target ",custom_sort_key_pretty"
1.1 (jsc 27-Feb-99): append custom_value ",'$QQcustom_sort_key_pretty'"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set custom_target ""
1.1 (jsc 27-Feb-99): set custom_value ""
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set usgeospatial_target ""
1.1 (jsc 27-Feb-99): set usgeospatial_value ""
1.1 (jsc 27-Feb-99): if { $presentation_type == "usgeospatial" } {
1.1 (jsc 27-Feb-99): if { ![info exists usgeospatial_p] || $usgeospatial_p != "t" } {
1.1 (jsc 27-Feb-99): # we only want to accept postings from a specially
1.1 (jsc 27-Feb-99): # constructed form
1.1 (jsc 27-Feb-99): ns_write "<h3>Sorry</h3>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): We're sorry but you've somehow managed to post to a geospatialized
1.1 (jsc 27-Feb-99): forum without using the requisite form. This is almost certainly
1.1 (jsc 27-Feb-99): our programming error. Please send us email to let us know how you
1.1 (jsc 27-Feb-99): got here.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_footer]"
1.1 (jsc 27-Feb-99): # stop execution of this thread
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): if { $refers_to == "NEW" } {
1.1 (jsc 27-Feb-99): if { [info exists tri_id] && ![empty_string_p $tri_id] } {
1.1 (jsc 27-Feb-99): # we have a tri_id, let's fill out everything else from
1.1 (jsc 27-Feb-99): # that (i.e., look up region, state, usps_abbrev, zip)
1.1 (jsc 27-Feb-99): set selection [ns_db 1row $db_sub "select st as usps_abbrev, sema_zip as zip_code, fips_county_code from rel_search_fac where tri_id = '$QQtri_id'"]
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): set epa_region [database_to_tcl_string $db_sub "select epa_region from bboard_epa_regions where usps_abbrev = '$usps_abbrev'"]
1.1 (jsc 27-Feb-99): set usgeospatial_target ", epa_region, usps_abbrev, fips_county_code, zip_code, tri_id"
1.1 (jsc 27-Feb-99): set usgeospatial_value ", [ns_dbquotevalue $epa_region integer], [ns_dbquotevalue $usps_abbrev text], [ns_dbquotevalue $fips_county_code text], [ns_dbquotevalue $zip_code text], [ns_dbquotevalue $tri_id text]"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set usgeospatial_target ", epa_region, usps_abbrev"
1.1 (jsc 27-Feb-99): set usgeospatial_value ", $epa_region, '$QQusps_abbrev'"
1.1 (jsc 27-Feb-99): if [info exists fips_county_code] {
1.1 (jsc 27-Feb-99): append usgeospatial_target ", fips_county_code"
1.1 (jsc 27-Feb-99): append usgeospatial_value ", '$QQfips_county_code'"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): if [info exists zip_code] {
1.1 (jsc 27-Feb-99): append usgeospatial_target ", zip_code"
1.1 (jsc 27-Feb-99): append usgeospatial_value ", '$QQzip_code'"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): # this is a reply
1.1 (jsc 27-Feb-99): # pull all the geospatial columns from the preceding row
1.1 (jsc 27-Feb-99): set selection [ns_db 1row $db_sub "select epa_region, usps_abbrev, fips_county_code, zip_code, tri_id from bboard where msg_id = '$QQrefers_to'"]
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): set usgeospatial_target ", epa_region, usps_abbrev, fips_county_code, zip_code, tri_id"
1.1 (jsc 27-Feb-99): set usgeospatial_value ", [ns_dbquotevalue $epa_region integer], [ns_dbquotevalue $usps_abbrev text], [ns_dbquotevalue $fips_county_code text], [ns_dbquotevalue $zip_code text], [ns_dbquotevalue $tri_id text]"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # to provide some SPAM-proofing, we record the IP address
1.1 (jsc 27-Feb-99): set originating_ip [ns_conn peeraddr]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # Work around inability of Oracle to handle string literals > 4k
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { [string length $QQmessage] < 4000 } {
1.1 (jsc 27-Feb-99): ns_db dml $db "insert into bboard (user_id,msg_id,refers_to,topic,originating_ip,one_line,message,html_p,notify,sort_key,posting_time${category_target}${custom_target}${usgeospatial_target})
1.1 (jsc 27-Feb-99): values ($user_id,'$new_id',$final_refers_to,'$QQtopic','$originating_ip','$QQone_line','$QQmessage', '$html_p','$notify','$sort_key',sysdate${category_value}${custom_value}${usgeospatial_value})"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): ns_ora clob_dml $db "insert into bboard (msg_id,refers_to,topic,originating_ip,user_id,one_line,message,html_p,notify,sort_key,posting_time${category_target}${custom_target}${usgeospatial_target})
1.1 (jsc 27-Feb-99): values ('$new_id',$final_refers_to,'$QQtopic','$originating_ip',$user_id,'$QQone_line',empty_clob(),'$html_p','$notify','$sort_key',sysdate${category_value}${custom_value}${usgeospatial_value})
1.1 (jsc 27-Feb-99): returning message into :1" $message
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # Handle image uploading
1.5 (philg 23-May-99): if {[bboard_file_uploading_enabled_p] && [info exists upload_file] && ![empty_string_p $upload_file]} {
1.2 (philg 05-Mar-99): set tmp_filename [ns_queryget upload_file.tmpfile]
1.1 (jsc 27-Feb-99): set new_upload_id [database_to_tcl_string $db "select bboard_upload_id_sequence.nextval from dual"]
1.2 (philg 05-Mar-99): set local_filename [bboard_generate_upload_filename $new_id $new_upload_id $upload_file]
1.2 (philg 05-Mar-99): set full_local_path "[bboard_file_path]/$local_filename"
1.2 (philg 05-Mar-99): ns_log Notice "Received $upload_file for upload; going to try to put it in $full_local_path"
1.5 (philg 23-May-99): set n_bytes [file size $tmp_filename]
1.5 (philg 23-May-99): if { $n_bytes > 0 } {
1.5 (philg 23-May-99): # we have a real image
1.5 (philg 23-May-99): ns_cp $tmp_filename $full_local_path
1.5 (philg 23-May-99): if { [info exists caption] && ![empty_string_p $caption] } {
1.5 (philg 23-May-99): # we have a photo
1.5 (philg 23-May-99): set extra_uf_columns ", caption"
1.5 (philg 23-May-99): set extra_uf_values ", [ns_dbquotevalue $caption text]"
1.5 (philg 23-May-99): set file_type "photo"
1.5 (philg 23-May-99): } else {
1.5 (philg 23-May-99): set extra_uf_columns ""
1.5 (philg 23-May-99): set extra_uf_values ""
1.5 (philg 23-May-99): set file_type "not a photo"
1.5 (philg 23-May-99): }
1.5 (philg 23-May-99): # make sure to lowercase it so we don't have to
1.5 (philg 23-May-99): # deal with JPG and JPEG
1.5 (philg 23-May-99): set file_extension [string tolower [file extension $upload_file]]
1.5 (philg 23-May-99): # remove the first . from the file extension
1.5 (philg 23-May-99): regsub "\." $file_extension "" file_extension
1.5 (philg 23-May-99): set what_aolserver_told_us ""
1.5 (philg 23-May-99): if { $file_extension == "jpeg" || $file_extension == "jpg" } {
1.5 (philg 23-May-99): catch { set what_aolserver_told_us [ns_jpegsize $full_local_path] }
1.5 (philg 23-May-99): } elseif { $file_extension == "gif" } {
1.5 (philg 23-May-99): catch { set what_aolserver_told_us [ns_gifsize $full_local_path] }
1.5 (philg 23-May-99): }
1.6 (jsc 11-Jul-99): # the AOLserver jpegsize command has some bugs where the height comes
1.6 (jsc 11-Jul-99): # through as 1 or 2
1.6 (jsc 11-Jul-99): if { ![empty_string_p $what_aolserver_told_us] && [lindex $what_aolserver_told_us 0] > 10 && [lindex $what_aolserver_told_us 1] > 10 } {
1.5 (philg 23-May-99): set original_width [lindex $what_aolserver_told_us 0]
1.5 (philg 23-May-99): set original_height [lindex $what_aolserver_told_us 1]
1.5 (philg 23-May-99): } else {
1.5 (philg 23-May-99): set original_width ""
1.5 (philg 23-May-99): set original_height ""
1.5 (philg 23-May-99): }
1.5 (philg 23-May-99): # strip off the C:\directories... crud and just get the file name
1.7 (jsc 07-Sep-99): if ![regexp {([^\\/]+)$} $upload_file match client_filename] {
1.5 (philg 23-May-99): # couldn't find a match
1.5 (philg 23-May-99): set client_filename $upload_file
1.5 (philg 23-May-99): }
1.1 (jsc 27-Feb-99):
1.5 (philg 23-May-99): ns_db dml $db "insert into bboard_uploaded_files
1.5 (philg 23-May-99): (bboard_upload_id, msg_id, file_type, file_extension, n_bytes, client_filename, filename_stub$extra_uf_columns,original_width,original_height)
1.2 (philg 05-Mar-99): VALUES
1.5 (philg 23-May-99): ($new_upload_id, '$new_id', '$file_type', '$file_extension', $n_bytes, '[DoubleApos $client_filename]', '/$local_filename'$extra_uf_values,[ns_dbquotevalue $original_width number],[ns_dbquotevalue $original_height number])"
1.5 (philg 23-May-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_db dml $db "end transaction"
1.1 (jsc 27-Feb-99): } errmsg] {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # something went a bit wrong during the insert
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_db dml $db "abort transaction"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_write "<h3>Ouch!!</h3>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Here was the bad news from the database:
1.1 (jsc 27-Feb-99): <pre>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): $errmsg $QQrefers_to
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): </pre>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Don't quit your browser. You might be able to resubmit your posting
1.1 (jsc 27-Feb-99): five or ten minutes from now.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_footer]
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ns_write "<h3>Success!!</h3>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Your posting is now in the database.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): One of the big points of <a href=\"http://photo.net/wtr/thebook/\">this software</a> is to support collaboration
1.1 (jsc 27-Feb-99): using the best mix of Web and email. Now that we've done the Web part,
1.1 (jsc 27-Feb-99): we will notify the people who have requested an
1.1 (jsc 27-Feb-99): <a href=\"add-alert.tcl?topic=[ns_urlencode $topic]\">email alert</a>.
1.1 (jsc 27-Feb-99): You may move to a different url if you don't want to wait for this process
1.1 (jsc 27-Feb-99): to complete.
1.1 (jsc 27-Feb-99): <P>
1.1 (jsc 27-Feb-99): <ul>
1.1 (jsc 27-Feb-99): <li> Generating alerts...
1.1 (jsc 27-Feb-99): <p>
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # email_queue is a list; each elementof the list is an ns_set
1.1 (jsc 27-Feb-99): # containing information about an email to be sent
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # The keys in each ns_set:
1.1 (jsc 27-Feb-99): # to: to email
1.1 (jsc 27-Feb-99): # from: from email
1.1 (jsc 27-Feb-99): # subject: subject heading
1.1 (jsc 27-Feb-99): # body: body
1.1 (jsc 27-Feb-99): # user_message: message to output to the Web user about this email
1.1 (jsc 27-Feb-99): # extraheaders: ns_set containing header name/content pairs for ns_sendmail
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set email_queue [list]
1.6 (jsc 11-Jul-99): # the WRAP=HARD in our form's TEXTAREA should have wrapped but
1.6 (jsc 11-Jul-99): # let's make sure (in case user's browser wasn't being nice to us)
1.6 (jsc 11-Jul-99): # also, let's try to
1.6 (jsc 11-Jul-99): if { $html_p == "t" } {
1.6 (jsc 11-Jul-99): set message_wrapped [wrap_string [util_striphtml $message]]
1.6 (jsc 11-Jul-99): } else {
1.6 (jsc 11-Jul-99): set message_wrapped [wrap_string $message]
1.6 (jsc 11-Jul-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): if { $notify_of_new_postings_p == "t" } {
1.1 (jsc 27-Feb-99): # administrator has requested notification of every new posting
1.1 (jsc 27-Feb-99): set maintainer_body "$name ($email) added a message to the $topic bboard:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Subject: $one_line
1.1 (jsc 27-Feb-99):
1.6 (jsc 11-Jul-99): $message_wrapped
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ----------
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): If you want to delete the message, come to the administration page:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_url_stub]admin-home.tcl?topic=[ns_urlencode $topic]
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set email_queue_entry [bboard_build_email_queue_entry $maintainer_email $email $one_line $maintainer_body "<li>sent email to the forum maintainer: $maintainer_email"]
1.1 (jsc 27-Feb-99): lappend email_queue $email_queue_entry
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): ns_write "<li>the forum maintainer ($maintainer_email) must be busy because he/she has disabled email notification of new postings\n\n<p>\n\n"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): if { $refers_to != "NEW" } {
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # try to send email to all the previous posters
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # set up top, conditionally
1.1 (jsc 27-Feb-99): # set return_url "[bboard_url_stub]main-frame.tcl?topic=[ns_urlencode $topic]"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set from "$email"
1.1 (jsc 27-Feb-99): if { ![regexp {Response} $one_line] } {
1.1 (jsc 27-Feb-99): set subject_line "Response to your posting: $one_line"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set subject_line $one_line
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): set body "$name ($email) responded to a message you left
1.1 (jsc 27-Feb-99): in the $topic bboard:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Subject: $one_line
1.1 (jsc 27-Feb-99):
1.6 (jsc 11-Jul-99): $message_wrapped
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -----------------
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): To post a response, come back to the bulletin board at
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): $return_url
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99): # kick off recursion
1.1 (jsc 27-Feb-99): set email_queue [notify_if_requested_build $db $new_id $refers_to $from $subject_line $body $email_queue]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # now we have to deal with all of the people who've requested instant notification of new postings
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # comment this out to avoid an AOLserver/Hearst mailer bug
1.1 (jsc 27-Feb-99): # set from "$name <$email>"
1.1 (jsc 27-Feb-99): set from $email
1.1 (jsc 27-Feb-99): if { [string length $topic] < 10 } {
1.1 (jsc 27-Feb-99): set subject_line "$topic forum: $one_line"
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): set subject_line "$one_line"
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): set body "
1.6 (jsc 11-Jul-99): $message_wrapped
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): ---------
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): To post a response, come back to the forum at
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): $return_url
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): (which is also the place to go if you want to edit your alerts and
1.1 (jsc 27-Feb-99): stop these robotically sent messages)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # **** Null/empty string problem for "keywords" (Oracle 9?)
1.1 (jsc 27-Feb-99): set selection [ns_db select $db "select distinct bboard_email_alerts.user_id,bboard_email_alerts.rowid, email from bboard_email_alerts, users_alertable
1.1 (jsc 27-Feb-99): where topic='$QQtopic'
1.1 (jsc 27-Feb-99): and frequency='instant'
1.1 (jsc 27-Feb-99): and valid_p = 't'
1.1 (jsc 27-Feb-99): and keywords is null
1.1 (jsc 27-Feb-99): and bboard_email_alerts.user_id = users_alertable.user_id"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): while {[ns_db getrow $db $selection]} {
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): if { ![bboard_already_notified_p $email $email_queue] } {
1.1 (jsc 27-Feb-99): # user hasn't been queued recieved the note yet
1.1 (jsc 27-Feb-99): set customized_body "$body
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): If you are annoyed by this message then just enter the following URL
1.1 (jsc 27-Feb-99): into a browser and you'll disable the alert that generated this mail:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_hardwired_url_stub]alert-disable.tcl?rowid=[ns_urlencode $rowid]
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set extraheaders [ns_set create extraheaders]
1.1 (jsc 27-Feb-99): ns_set put $extraheaders Reply-To $from
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): lappend email_queue [bboard_build_email_queue_entry $email [bboard_sender_email] $subject_line $customized_body "<li>sent a note to $email \n" $extraheaders]
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set selection [ns_db select $db "select distinct bboard_email_alerts.user_id, keywords, bboard_email_alerts.rowid, email
1.1 (jsc 27-Feb-99): from bboard_email_alerts, users_alertable
1.1 (jsc 27-Feb-99): where topic='$QQtopic'
1.1 (jsc 27-Feb-99): and frequency='instant'
1.1 (jsc 27-Feb-99): and valid_p = 't'
1.1 (jsc 27-Feb-99): and keywords is not null
1.1 (jsc 27-Feb-99): and users_alertable.user_id = bboard_email_alerts.user_id"]
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): while {[ns_db getrow $db $selection]} {
1.1 (jsc 27-Feb-99): set_variables_after_query
1.1 (jsc 27-Feb-99): set keyword_list [split $keywords " "]
1.1 (jsc 27-Feb-99): set found_p 0
1.1 (jsc 27-Feb-99): foreach word $keyword_list {
1.1 (jsc 27-Feb-99): # turns out that "" is never found in a search, so we
1.1 (jsc 27-Feb-99): # don't really have to special case $word == ""
1.1 (jsc 27-Feb-99): if { $word != "" && [string first [string toupper $word] [string toupper $indexed_stuff]] != -1 } {
1.1 (jsc 27-Feb-99): # found it!
1.1 (jsc 27-Feb-99): set found_p 1
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): ns_log Notice "looked for \"$keyword_list\" in $new_id; found_p = $found_p"
1.1 (jsc 27-Feb-99): if { $found_p == 1 && ![bboard_already_notified_p $email $email_queue] } {
1.1 (jsc 27-Feb-99): # word is found and user hasn't been
1.1 (jsc 27-Feb-99): # queued to receive the email yet
1.1 (jsc 27-Feb-99): set customized_body "$body
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): If you are annoyed by this message then just enter the following URL
1.1 (jsc 27-Feb-99): into a browser and you'll disable the alert that generated this mail:
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_hardwired_url_stub]alert-disable.tcl?rowid=[ns_urlencode $rowid]
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): set extraheaders [ns_set create extraheaders]
1.1 (jsc 27-Feb-99): ns_set put $extraheaders Reply-To $from
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): lappend email_queue [bboard_build_email_queue_entry $email [bboard_sender_email] $subject_line $customized_body "<li>sent a note to $email \n" $extraheaders]
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # we release the database handle in case the mailer is down; we
1.1 (jsc 27-Feb-99): # don't want other threads to block waiting for a db handle tied
1.1 (jsc 27-Feb-99): # down by us
1.1 (jsc 27-Feb-99): ns_db releasehandle $db
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): # send out the email
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): foreach email $email_queue {
1.1 (jsc 27-Feb-99): if [catch {ns_sendmail [ns_set get $email to] [ns_set get $email from] [ns_set get $email subject] [ns_set get $email body] [ns_set get $email extraheaders] } errmsg] {
1.1 (jsc 27-Feb-99): # email failed, let's see if it is because mail
1.1 (jsc 27-Feb-99): # service is completely wedged on this box
1.1 (jsc 27-Feb-99): if { [string first "timed out" errmsg] != -1 } {
1.1 (jsc 27-Feb-99): # looks like we couldn't even talk to mail server
1.1 (jsc 27-Feb-99): # let's just give up and return so that this thread
1.1 (jsc 27-Feb-99): # doesn't have around for 10 minutes
1.1 (jsc 27-Feb-99): ns_log Notice "timed out sending email; giving up on email alerts. Here's what ns_sendmail returned:\n$errmsg"
1.1 (jsc 27-Feb-99): ns_write "</ul>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): Something is horribly wrong with the email handler on this computer so
1.1 (jsc 27-Feb-99): we're giving up on sending any email notifications. Your posting
1.1 (jsc 27-Feb-99): will be enshrined in the database, of course.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_footer]"
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): ns_write "Something is horribly wrong with
1.1 (jsc 27-Feb-99): the email handler on this computer so
1.1 (jsc 27-Feb-99): we're giving up on sending any email notifications. Your posting
1.1 (jsc 27-Feb-99): will be enshrined in the database, of course.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): <p>
1.1 (jsc 27-Feb-99): <blockquote>
1.1 (jsc 27-Feb-99): <pre>
1.1 (jsc 27-Feb-99): $errmsg
1.1 (jsc 27-Feb-99): </pre>
1.1 (jsc 27-Feb-99): </blockquote>"
1.1 (jsc 27-Feb-99): return
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): } else {
1.1 (jsc 27-Feb-99): # we succeeding sending this particular piece of mail
1.1 (jsc 27-Feb-99): ns_write [ns_set get $email user_message]
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): }
1.1 (jsc 27-Feb-99): # we're done processing the email queue
1.1 (jsc 27-Feb-99): ns_write "</ul>
1.1 (jsc 27-Feb-99): <p>
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): We're all done with the email notifications now. If any of these
1.1 (jsc 27-Feb-99): folks typed in a bogus/misspelled/obsolete email address, you may get a
1.1 (jsc 27-Feb-99): bounced message in your inbox.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): [bboard_footer]
1.1 (jsc 27-Feb-99): "
1.1 (jsc 27-Feb-99): }
% cvs annotate community-core.sql
1.1 (jsc 27-Feb-99): -- ArsDigita Community System data model
1.1 (jsc 27-Feb-99): -- by philg@mit.edu
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- as distributed, this will load into a user's default tablespace in
1.1 (jsc 27-Feb-99): -- Oracle; you'll get substantially higher transaction performance if
1.1 (jsc 27-Feb-99): -- you put certain tables or their indices into tablespaces that are
1.1 (jsc 27-Feb-99): -- on separate physical disk drives. Search for "****" for things
1.1 (jsc 27-Feb-99): -- that I (philg) think are good candidates. Generally there will
1.1 (jsc 27-Feb-99): -- be a commented-out directive to park something in a photonet tablespace
1.1 (jsc 27-Feb-99): -- you can comment these back in and change the tablespace name to something
1.1 (jsc 27-Feb-99): -- that is meaningful on your system
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- only the US states (and random territories such as Guam)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- no need to define these; the /install/*.dmp files
1.1 (jsc 27-Feb-99): -- create them when you import (you must do that first)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- create table states (
1.1 (jsc 27-Feb-99): -- usps_abbrev char(2) not null primary key,
1.1 (jsc 27-Feb-99): -- fips_state_code char(2),
1.1 (jsc 27-Feb-99): -- state_name varchar(25)
1.1 (jsc 27-Feb-99): -- );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- create table country_codes (
1.1 (jsc 27-Feb-99): -- iso char(2) not null primary key,
1.1 (jsc 27-Feb-99): -- country_name varchar(150)
1.1 (jsc 27-Feb-99): -- );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- create table counties (
1.1 (jsc 27-Feb-99): -- fips_county_code varchar(5) not null primary key,
1.1 (jsc 27-Feb-99): -- fips_county_name varchar(35) not null,
1.1 (jsc 27-Feb-99): -- fips_state_code varchar(2) not null,
1.1 (jsc 27-Feb-99): -- usps_abbrev varchar(2) not null,
1.1 (jsc 27-Feb-99): -- state_name varchar(50) not null
1.1 (jsc 27-Feb-99): -- );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- populating counties from the Scorecard rel_search_co table:
1.1 (jsc 27-Feb-99): -- insert into counties
1.1 (jsc 27-Feb-99): -- (fips_county_code, fips_county_name, fips_state_code, usps_abbrev, state_name)
1.1 (jsc 27-Feb-99): -- select fips_county_code, fips_county_name, fips_state_code, state, state_name from rel_search_co;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create sequence user_id_sequence start with 1;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- in general, users can't be deleted because of integrity constraints
1.1 (jsc 27-Feb-99): -- on content they've contributed; we can pseudo-delete them by setting
1.1 (jsc 27-Feb-99): -- deleted_p to 't'; at this point there is the question of what to do
1.1 (jsc 27-Feb-99): -- if/when they reappear on the site. If they deleted themselves
1.1 (jsc 27-Feb-99): -- then presumably we let them re-enable their registration. If they
1.1 (jsc 27-Feb-99): -- were banned by the administration then we have to play dead or inform
1.1 (jsc 27-Feb-99): -- them of that fact.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table users (
1.1 (jsc 27-Feb-99): user_id integer not null primary key,
1.1 (jsc 27-Feb-99): first_names varchar(100) not null,
1.1 (jsc 27-Feb-99): last_name varchar(100) not null,
1.1 (jsc 27-Feb-99): priv_name integer default 0,
1.1 (jsc 27-Feb-99): email varchar(100) not null unique,
1.1 (jsc 27-Feb-99): priv_email integer default 5,
1.1 (jsc 27-Feb-99): email_bouncing_p char(1) default 'f' check(email_bouncing_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- converted_p means password is bogus; we imported this guy
1.1 (jsc 27-Feb-99): -- from a system where we only had email address
1.1 (jsc 27-Feb-99): converted_p char(1) default 'f' check(converted_p in ('t','f')),
1.1 (jsc 27-Feb-99): password varchar(30) not null,
1.1 (jsc 27-Feb-99): -- we put homepage_url here so that we can
1.1 (jsc 27-Feb-99): -- always make names hyperlinks without having to
1.1 (jsc 27-Feb-99): -- JOIN to users_contact
1.1 (jsc 27-Feb-99): url varchar(200),
1.1 (jsc 27-Feb-99): -- to suppress email alerts
1.1 (jsc 27-Feb-99): on_vacation_until date,
1.1 (jsc 27-Feb-99): -- set when user reappears at site
1.1 (jsc 27-Feb-99): last_visit date,
1.1 (jsc 27-Feb-99): -- this is what most pages query against (since the above column
1.1 (jsc 27-Feb-99): -- will only be a few minutes old for most pages in a session)
1.1 (jsc 27-Feb-99): second_to_last_visit date,
1.3 (teadams 18-Apr-99): -- how many times this person has visited
1.3 (teadams 18-Apr-99): n_sessions integer default 1,
1.1 (jsc 27-Feb-99): registration_date date,
1.1 (jsc 27-Feb-99): registration_ip varchar(50),
1.7 (teadams 17-Jul-99): -- state the user is in in the registration proces
1.14 (teadams 25-Jul-99): user_state varchar(100) check(user_state in ('need_email_verification_and_admin_approv', 'need_admin_approv', 'need_email_verification', 'rejected', 'authorized', 'banned', 'deleted')),
1.7 (teadams 17-Jul-99): -- admin approvale system
1.7 (teadams 17-Jul-99): approved_date date,
1.5 (jsc 25-May-99): approving_user references users(user_id),
1.8 (teadams 17-Jul-99): approving_note varchar(4000),
1.7 (teadams 17-Jul-99): -- email verification system
1.7 (teadams 17-Jul-99): email_verified_date date,
1.7 (teadams 17-Jul-99): -- used if the user rejected before they reach
1.7 (teadams 17-Jul-99): -- the authorized state
1.7 (teadams 17-Jul-99): rejected_date date,
1.7 (teadams 17-Jul-99): rejecting_user integer references users(user_id),
1.8 (teadams 17-Jul-99): rejecting_note varchar(4000),
1.7 (teadams 17-Jul-99): -- user was active and is now delete from the system
1.7 (teadams 17-Jul-99): -- may be revived
1.7 (teadams 17-Jul-99): deleted_date date,
1.7 (teadams 17-Jul-99): deleting_user integer references users(user_id),
1.9 (teadams 19-Jul-99): deleting_note varchar(4000),
1.7 (teadams 17-Jul-99): -- user was active and now not allowed into the system
1.7 (teadams 17-Jul-99): banned_date date,
1.1 (jsc 27-Feb-99): -- who and why this person was banned
1.1 (jsc 27-Feb-99): banning_user references users(user_id),
1.1 (jsc 27-Feb-99): banning_note varchar(4000)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.6 (jsc 11-Jul-99): -- we need this to support /shared/whos-online.tcl and /chat
1.6 (jsc 11-Jul-99): create index users_by_last_visit on users (last_visit);
1.6 (jsc 11-Jul-99):
1.13 (teadams 23-Jul-99): -- we need this index to list number of users in given user_state
1.13 (teadams 23-Jul-99): -- for the admin pages
1.13 (teadams 23-Jul-99): create index users_user_state on users (user_state);
1.13 (teadams 23-Jul-99):
1.1 (jsc 27-Feb-99): -- when Oracle 8.1 comes out, build a case-insensitive
1.1 (jsc 27-Feb-99): -- functional index
1.1 (jsc 27-Feb-99): -- create unique index users_email_idx on users(upper(email));
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace function on_vacation_p (vacation_until IN date) return CHAR
1.1 (jsc 27-Feb-99): IS
1.1 (jsc 27-Feb-99): BEGIN
1.1 (jsc 27-Feb-99): IF (vacation_until is not null) AND (vacation_until >= sysdate) THEN
1.1 (jsc 27-Feb-99): RETURN 't';
1.1 (jsc 27-Feb-99): ELSE
1.1 (jsc 27-Feb-99): RETURN 'f';
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99): END;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99): show errors
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace view users_alertable
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): select *
1.1 (jsc 27-Feb-99): from users
1.1 (jsc 27-Feb-99): where (on_vacation_until is null or
1.1 (jsc 27-Feb-99): on_vacation_until < sysdate)
1.10 (teadams 19-Jul-99): and user_state = 'authorized'
1.1 (jsc 27-Feb-99): and (email_bouncing_p is null or email_bouncing_p = 'f');
1.4 (philg 19-Apr-99):
1.6 (jsc 11-Jul-99):
1.4 (philg 19-Apr-99): --- users who are not deleted or banned
1.4 (philg 19-Apr-99):
1.4 (philg 19-Apr-99): create or replace view users_active
1.4 (philg 19-Apr-99): as
1.4 (philg 19-Apr-99): select *
1.4 (philg 19-Apr-99): from users
1.10 (teadams 19-Jul-99): where user_state = 'authorized';
1.10 (teadams 19-Jul-99):
1.6 (jsc 11-Jul-99): -- users who've signed up in the last 30 days
1.6 (jsc 11-Jul-99): -- useful for moderators since new users tend to
1.6 (jsc 11-Jul-99): -- be the ones who cause trouble
1.6 (jsc 11-Jul-99):
1.6 (jsc 11-Jul-99): create or replace view users_new
1.6 (jsc 11-Jul-99): as
1.6 (jsc 11-Jul-99): select *
1.6 (jsc 11-Jul-99): from users
1.6 (jsc 11-Jul-99): where registration_date > (sysdate - 30);
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- create a system user (to do things like own administrators group)
1.1 (jsc 27-Feb-99): -- and also create an anonymous user (to own legacy content)
1.1 (jsc 27-Feb-99): -- we keep their status in special email addresses because these are indexed
1.1 (jsc 27-Feb-99): -- (constrained unique) and therefore fast to look up
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): declare
1.1 (jsc 27-Feb-99): n_system_users integer;
1.1 (jsc 27-Feb-99): n_anonymous_users integer;
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select count(*) into n_system_users from users where email = 'system';
1.1 (jsc 27-Feb-99): if n_system_users = 0 then
1.1 (jsc 27-Feb-99): insert into users
1.15 (jsc 11-Aug-99): (user_id, first_names, last_name, email, password, user_state)
1.1 (jsc 27-Feb-99): values
1.15 (jsc 11-Aug-99): (user_id_sequence.nextval, 'system', 'system', 'system', 'changeme', 'authorized');
1.1 (jsc 27-Feb-99): end if;
1.1 (jsc 27-Feb-99): -- if moving content from an old system, you might have lots that needs
1.1 (jsc 27-Feb-99): -- to be owned by anonymous
1.1 (jsc 27-Feb-99): select count(*) into n_anonymous_users from users where email = 'anonymous';
1.1 (jsc 27-Feb-99): if n_anonymous_users = 0 then
1.1 (jsc 27-Feb-99): insert into users
1.1 (jsc 27-Feb-99): (user_id, first_names, last_name, email, password)
1.1 (jsc 27-Feb-99): values
1.1 (jsc 27-Feb-99): (user_id_sequence.nextval, 'anonymous', 'anonymous', 'anonymous', 'changeme');
1.1 (jsc 27-Feb-99): end if;
1.1 (jsc 27-Feb-99): end;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace function system_user_id
1.1 (jsc 27-Feb-99): return integer
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): v_user_id integer;
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select user_id into v_user_id from users where email = 'system';
1.1 (jsc 27-Feb-99): return v_user_id;
1.1 (jsc 27-Feb-99): end;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace function anonymous_user_id
1.1 (jsc 27-Feb-99): return integer
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): v_user_id integer;
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select user_id into v_user_id from users where email = 'anonymous';
1.1 (jsc 27-Feb-99): return v_user_id;
1.1 (jsc 27-Feb-99): end;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table users_preferences (
1.1 (jsc 27-Feb-99): user_id integer primary key references users,
1.1 (jsc 27-Feb-99): prefer_text_only_p char(1) default 'f' check (prefer_text_only_p in ('t','f')),
1.6 (jsc 11-Jul-99): -- an ISO 639 language code (in lowercase)
1.6 (jsc 11-Jul-99): language_preference char(2) default 'en',
1.1 (jsc 27-Feb-99): dont_spam_me_p char(1) default 'f' check (dont_spam_me_p in ('t','f'))
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.6 (jsc 11-Jul-99): ---- same as users_alertable but for publisher-initiated correspondence
1.6 (jsc 11-Jul-99):
1.6 (jsc 11-Jul-99): create or replace view users_spammable
1.6 (jsc 11-Jul-99): as
1.6 (jsc 11-Jul-99): select u.*
1.6 (jsc 11-Jul-99): from users u, users_preferences up
1.6 (jsc 11-Jul-99): where u.user_id = up.user_id(+)
1.6 (jsc 11-Jul-99): and (on_vacation_until is null or
1.6 (jsc 11-Jul-99): on_vacation_until < sysdate)
1.11 (teadams 23-Jul-99): and user_state = 'authorized'
1.6 (jsc 11-Jul-99): and (email_bouncing_p is null or email_bouncing_p = 'f')
1.6 (jsc 11-Jul-99): and (dont_spam_me_p is null or dont_spam_me_p = 'f');
1.6 (jsc 11-Jul-99):
1.1 (jsc 27-Feb-99): -- there is a bit of redundancy here with users_contact
1.1 (jsc 27-Feb-99): -- but people may want to do a survey without ever asking
1.1 (jsc 27-Feb-99): -- users for full addresses
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table users_demographics (
1.1 (jsc 27-Feb-99): user_id integer primary key references users,
1.1 (jsc 27-Feb-99): birthdate date,
1.1 (jsc 27-Feb-99): priv_birthdate integer,
1.1 (jsc 27-Feb-99): sex char(1) check (sex in ('m','f')),
1.1 (jsc 27-Feb-99): priv_sex integer,
1.1 (jsc 27-Feb-99): postal_code varchar(80),
1.1 (jsc 27-Feb-99): priv_postal_code integer,
1.1 (jsc 27-Feb-99): ha_country_code char(2) references country_codes(iso),
1.1 (jsc 27-Feb-99): priv_country_code integer,
1.1 (jsc 27-Feb-99): affiliation varchar(40),
1.1 (jsc 27-Feb-99): -- these last two have to do with how the person
1.1 (jsc 27-Feb-99): -- became a member of the community
1.1 (jsc 27-Feb-99): how_acquired varchar(40),
1.1 (jsc 27-Feb-99): -- will be non-NULL if they were referred by another user
1.1 (jsc 27-Feb-99): referred_by integer references users(user_id)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace function user_demographics_summary (v_user_id IN integer)
1.1 (jsc 27-Feb-99): return varchar
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): demo_row users_demographics%ROWTYPE;
1.1 (jsc 27-Feb-99): age integer;
1.1 (jsc 27-Feb-99): pretty_sex varchar(20);
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select * into demo_row from users_demographics where user_id = v_user_id;
1.1 (jsc 27-Feb-99): age := round(months_between(sysdate,demo_row.birthdate)/12.0);
1.1 (jsc 27-Feb-99): IF demo_row.sex = 'm' THEN
1.1 (jsc 27-Feb-99): pretty_sex := 'man';
1.1 (jsc 27-Feb-99): ELSIF demo_row.sex = 'f' THEN
1.1 (jsc 27-Feb-99): pretty_sex := 'woman';
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99): IF pretty_sex is null and age is null THEN
1.1 (jsc 27-Feb-99): return null;
1.1 (jsc 27-Feb-99): ELSIF pretty_sex is not null and age is null THEN
1.1 (jsc 27-Feb-99): return 'a ' || pretty_sex;
1.1 (jsc 27-Feb-99): ELSIF pretty_sex is null and age is not null THEN
1.1 (jsc 27-Feb-99): return 'a ' || age || '-year-old person of unknown sex';
1.1 (jsc 27-Feb-99): ELSE
1.1 (jsc 27-Feb-99): return 'a ' || age || '-year-old ' || pretty_sex;
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99): end user_demographics_summary;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99): show errors
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- contact info for users
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table users_contact (
1.1 (jsc 27-Feb-99): user_id integer primary key references users,
1.1 (jsc 27-Feb-99): home_phone varchar(100),
1.1 (jsc 27-Feb-99): priv_home_phone integer,
1.1 (jsc 27-Feb-99): work_phone varchar(100),
1.1 (jsc 27-Feb-99): priv_work_phone integer,
1.14 (teadams 25-Jul-99): cell_phone varchar(100),
1.12 (teadams 23-Jul-99): priv_cell_phone integer,
1.12 (teadams 23-Jul-99): pager varchar(100),
1.12 (teadams 23-Jul-99): priv_pager integer,
1.1 (jsc 27-Feb-99): -- to facilitate users talking to each other and Web server
1.1 (jsc 27-Feb-99): -- sending instant messages, we keep the AOL Instant Messenger
1.1 (jsc 27-Feb-99): -- screen name
1.1 (jsc 27-Feb-99): aim_screen_name varchar(50),
1.1 (jsc 27-Feb-99): priv_aim_screen_name integer,
1.1 (jsc 27-Feb-99): -- also the ICQ# (they have multi-user chat)
1.1 (jsc 27-Feb-99): -- currently this is probably only a 32-bit integer but
1.1 (jsc 27-Feb-99): -- let's give them 50 chars anyway
1.1 (jsc 27-Feb-99): icq_number varchar(50),
1.1 (jsc 27-Feb-99): priv_icq_number integer,
1.1 (jsc 27-Feb-99): -- Which address should we mail to?
1.1 (jsc 27-Feb-99): m_address char(1) check (m_address in ('w','h')),
1.1 (jsc 27-Feb-99): -- home address
1.1 (jsc 27-Feb-99): ha_line1 varchar(80),
1.1 (jsc 27-Feb-99): ha_line2 varchar(80),
1.1 (jsc 27-Feb-99): ha_city varchar(80),
1.1 (jsc 27-Feb-99): ha_state varchar(80),
1.1 (jsc 27-Feb-99): ha_postal_code varchar(80),
1.1 (jsc 27-Feb-99): ha_country_code char(2) references country_codes(iso),
1.1 (jsc 27-Feb-99): priv_ha integer,
1.1 (jsc 27-Feb-99): -- work address
1.1 (jsc 27-Feb-99): wa_line1 varchar(80),
1.1 (jsc 27-Feb-99): wa_line2 varchar(80),
1.1 (jsc 27-Feb-99): wa_city varchar(80),
1.1 (jsc 27-Feb-99): wa_state varchar(80),
1.1 (jsc 27-Feb-99): wa_postal_code varchar(80),
1.1 (jsc 27-Feb-99): wa_country_code char(2) references country_codes(iso),
1.12 (teadams 23-Jul-99): priv_wa integer,
1.12 (teadams 23-Jul-99): -- used by the intranet module
1.12 (teadams 23-Jul-99): note varchar(4000),
1.12 (teadams 23-Jul-99): current_information varchar(4000)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create or replace function user_contact_summary (v_user_id IN integer)
1.1 (jsc 27-Feb-99): return varchar
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): contact_row users_contact%ROWTYPE;
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select * into contact_row from users_contact where user_id = v_user_id;
1.1 (jsc 27-Feb-99): IF contact_row.m_address = 'w' THEN
1.1 (jsc 27-Feb-99): -- they prefer to receive mail at work
1.1 (jsc 27-Feb-99): return contact_row.wa_line1 || ' ' || contact_row.wa_line2 || ' ' || contact_row.wa_city || ', ' || contact_row.wa_state || contact_row.wa_postal_code || ' ' || contact_row.wa_country_code;
1.1 (jsc 27-Feb-99): ELSE
1.1 (jsc 27-Feb-99): return contact_row.ha_line1 || ' ' || contact_row.ha_line2 || ' ' || contact_row.ha_city || ', ' || contact_row.ha_state || contact_row.ha_postal_code || ' ' || contact_row.ha_country_code;
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99): end user_contact_summary;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99): show errors
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- a table for keeping track of a "commitment" requirement for
1.1 (jsc 27-Feb-99): -- users. This means that we can require that a user give a real
1.1 (jsc 27-Feb-99): -- address, a birthdate, etc... because we think that this user
1.1 (jsc 27-Feb-99): -- needs to commit more to the community.
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table user_requirements (
1.1 (jsc 27-Feb-99): user_id integer primary key references users,
1.1 (jsc 27-Feb-99): demographics char(1) default 'f' check (demographics in ('t','f')),
1.1 (jsc 27-Feb-99): contacts char(1) default 'f' check (contacts in ('t','f'))
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- a PL/SQL function to make life easier, and to abstract out a
1.1 (jsc 27-Feb-99): -- bit the requirements of this data model
1.1 (jsc 27-Feb-99): create or replace function user_fulfills_requirements_p(uid in integer) return char
1.1 (jsc 27-Feb-99): AS
1.1 (jsc 27-Feb-99): requirements user_requirements%ROWTYPE;
1.1 (jsc 27-Feb-99): count_result integer;
1.1 (jsc 27-Feb-99): begin
1.1 (jsc 27-Feb-99): select count(*) INTO count_result from user_requirements where user_id=uid;
1.1 (jsc 27-Feb-99): IF count_result=0
1.1 (jsc 27-Feb-99): THEN RETURN 't';
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): select * INTO requirements from user_requirements where user_id=uid;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): select count(*) INTO count_result from users_demographics where user_id=uid;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): IF requirements.demographics='t' AND count_result=0 THEN
1.1 (jsc 27-Feb-99): RETURN 'f';
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): select count(*) INTO count_result from users_contact where user_id=uid;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): IF requirements.contacts='t' AND count_result=0 THEN
1.1 (jsc 27-Feb-99): RETURN 'f';
1.1 (jsc 27-Feb-99): END IF;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): RETURN 't';
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): end user_fulfills_requirements_p;
1.1 (jsc 27-Feb-99): /
1.1 (jsc 27-Feb-99): show errors
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- we use these for categorizing content, registering user interest
1.1 (jsc 27-Feb-99): -- in particular areas, organizing archived Q&A threads
1.6 (jsc 11-Jul-99): -- we also may use this as a mailing list to keep users up
1.6 (jsc 11-Jul-99): -- to date with what goes on at the site
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create sequence category_id_sequence start with 1;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table categories (
1.6 (jsc 11-Jul-99): category_id integer not null primary key,
1.6 (jsc 11-Jul-99): category varchar(50),
1.6 (jsc 11-Jul-99): enabled_p char(1) default 't' check(enabled_p in ('t','f')),
1.6 (jsc 11-Jul-99): mailing_list_info varchar(4000)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table users_interests (
1.6 (jsc 11-Jul-99): user_id integer not null references users,
1.6 (jsc 11-Jul-99): category_id integer not null references categories,
1.6 (jsc 11-Jul-99): interest_date date,
1.6 (jsc 11-Jul-99): unique(user_id, category_id)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- managing content sections; the major areas of the site
1.1 (jsc 27-Feb-99): -- e.g., one row would be "bboard", "Discussion forums", "/bboard/"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table content_sections (
1.1 (jsc 27-Feb-99): section_key varchar(30) primary key,
1.1 (jsc 27-Feb-99): section_pretty_name varchar(50) not null,
1.1 (jsc 27-Feb-99): section_url_stub varchar(200),
1.1 (jsc 27-Feb-99): -- if we print lists of sections, where does this go?
1.1 (jsc 27-Feb-99): -- two sections with same sort_key will sort
1.1 (jsc 27-Feb-99): -- by upper(section_pretty_name)
1.1 (jsc 27-Feb-99): sort_key integer,
1.1 (jsc 27-Feb-99): enabled_p char(1) default 't' check(enabled_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- set to 't' if this is just a bunch of HTML files
1.1 (jsc 27-Feb-99): static_p char(1) default 'f' check(static_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- show this only to registered users?
1.1 (jsc 27-Feb-99): requires_registration_p char(1) default 'f' check(requires_registration_p in ('t','f')),
1.1 (jsc 27-Feb-99): intro_blurb varchar(4000),
1.1 (jsc 27-Feb-99): help_blurb varchar(4000)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- versioning, indexing, categorization of static content
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- URL_STUB is relative to document root, includes leading /
1.1 (jsc 27-Feb-99): -- and trailing ".html"
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- for collaboration, the software assumes the possible existence
1.1 (jsc 27-Feb-99): -- of a file ending in ".new.html" (presented only to authors)
1.1 (jsc 27-Feb-99): -- this is not handled in the RDBMS
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- draft pages end in ".draft.html" in the Unix file system; they are
1.1 (jsc 27-Feb-99): -- only made available to users who show up in the static_page_authors
1.1 (jsc 27-Feb-99): -- table; the URL_STUB during development does not include the ".draft"
1.1 (jsc 27-Feb-99): -- but is instead the final location where the file will ultimately reside
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- we could key by url_stub but (1) that makes reorganizing
1.1 (jsc 27-Feb-99): -- the static pages on the server even harder, (2) that bloats
1.1 (jsc 27-Feb-99): -- out the referring tables
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- we keep an ORIGINAL_AUTHOR (redundant with static_page_authors)
1.1 (jsc 27-Feb-99): -- when the page was originally created by one particular user
1.1 (jsc 27-Feb-99): -- will be NULL if we don't have the author in our system
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create sequence page_id_sequence start with 1;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table static_pages (
1.1 (jsc 27-Feb-99): page_id integer not null primary key,
1.1 (jsc 27-Feb-99): url_stub varchar(400) not null unique,
1.1 (jsc 27-Feb-99): original_author integer references users(user_id),
1.1 (jsc 27-Feb-99): -- generally PAGE_TITLE will be whatever was inside HTML TITLE tag
1.1 (jsc 27-Feb-99): page_title varchar(4000),
1.1 (jsc 27-Feb-99): -- the dreaded CLOB data type (bleah)
1.1 (jsc 27-Feb-99): page_body clob,
1.1 (jsc 27-Feb-99): draft_p char(1) default 'f' check (draft_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- for a page that is no longer in the file system, but we
1.1 (jsc 27-Feb-99): -- don't actually delete it from the database because of
1.1 (jsc 27-Feb-99): -- integrity constraints
1.1 (jsc 27-Feb-99): obsolete_p char(1) default 'f' check (obsolete_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- force people to register before viewing?
1.1 (jsc 27-Feb-99): members_only_p char(1) default 'f' check (members_only_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- if we want to charge (or pay) readers for viewing this
1.1 (jsc 27-Feb-99): price number,
1.1 (jsc 27-Feb-99): -- for deviations from site-default copyright policy
1.1 (jsc 27-Feb-99): copyright_info varchar(4000),
1.1 (jsc 27-Feb-99): -- whether or not this page accepts reader contributions
1.1 (jsc 27-Feb-99): accept_comments_p char(1) default 't' check (accept_comments_p in ('t','f')),
1.1 (jsc 27-Feb-99): accept_links_p char(1) default 't' check (accept_links_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- do we display comments on the same page?
1.1 (jsc 27-Feb-99): inline_comments_p char(1) default 't' check (inline_comments_p in ('t','f')),
1.1 (jsc 27-Feb-99): inline_links_p char(1) default 't' check (inline_links_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- for sites with fancy navigation, do we want this page to have a menu?
1.1 (jsc 27-Feb-99): menu_p char(1) default 't' check (menu_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- if the menu has an "uplevel" link and it should
1.1 (jsc 27-Feb-99): -- not go to the directory defaults, what the link should be
1.1 (jsc 27-Feb-99): uplink varchar(200),
1.1 (jsc 27-Feb-99): -- filesize in bytes
1.1 (jsc 27-Feb-99): file_size integer,
1.1 (jsc 27-Feb-99): -- determined by the unix file system
1.1 (jsc 27-Feb-99): last_updated date,
1.1 (jsc 27-Feb-99): -- used to prevent minor changes from looking like new content
1.1 (jsc 27-Feb-99): publish_date date
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- if a page has been authored by one or more users, then
1.1 (jsc 27-Feb-99): -- there are rows here (this serves for both credit and update
1.1 (jsc 27-Feb-99): -- permission)
1.1 (jsc 27-Feb-99): --
1.1 (jsc 27-Feb-99): -- also keep track of whether author wants to get email
1.1 (jsc 27-Feb-99): -- notifications of new comments, links, etc.
1.1 (jsc 27-Feb-99): -- (this information will also be available in a summary Web page
1.1 (jsc 27-Feb-99): -- when author logs in)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table static_page_authors (
1.1 (jsc 27-Feb-99): page_id integer not null references static_pages,
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): notify_p char(1) default 't' check (notify_p in ('t','f')),
1.1 (jsc 27-Feb-99): unique(page_id,user_id)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table static_categories (
1.1 (jsc 27-Feb-99): page_id integer not null references static_pages,
1.1 (jsc 27-Feb-99): category_id integer not null references categories,
1.1 (jsc 27-Feb-99): unique(page_id, category_id)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- comments on static pages will reference the above tables
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- comment_type is generally one of the following:
1.1 (jsc 27-Feb-99): -- alternative_perspective
1.1 (jsc 27-Feb-99): -- private_message_to_page_authors
1.1 (jsc 27-Feb-99): -- rating
1.1 (jsc 27-Feb-99): -- unanswered_question
1.1 (jsc 27-Feb-99): -- if an administrator had to delete a comment, deleted_p will be 't'
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create sequence comment_id_sequence start with 1;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table comments (
1.1 (jsc 27-Feb-99): comment_id integer primary key,
1.1 (jsc 27-Feb-99): page_id integer not null references static_pages,
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): comment_type varchar(30),
1.1 (jsc 27-Feb-99): message clob,
1.1 (jsc 27-Feb-99): html_p char(1) check (html_p in ('t','f')),
1.1 (jsc 27-Feb-99): -- null unless comment_type is 'rating'
1.1 (jsc 27-Feb-99): rating integer check (rating >= 0 and rating <= 10),
1.1 (jsc 27-Feb-99): originating_ip varchar(50),
1.1 (jsc 27-Feb-99): posting_time date,
1.1 (jsc 27-Feb-99): deleted_p char(1) default 'f' check (deleted_p in ('t','f'))
1.16 (jsc 20-Sep-99): -- columns useful for attachments, column names
1.16 (jsc 20-Sep-99): -- lifted from general_comments
1.16 (jsc 20-Sep-99): -- this is where the actual content is stored
1.16 (jsc 20-Sep-99): attachment blob,
1.16 (jsc 20-Sep-99): -- file name including extension but not path
1.16 (jsc 20-Sep-99): client_file_name varchar(500),
1.16 (jsc 20-Sep-99): file_type varchar(100), -- this is a MIME type (e.g., image/jpeg)
1.16 (jsc 20-Sep-99): file_extension varchar(50), -- e.g., "jpg"
1.16 (jsc 20-Sep-99): -- fields that only make sense if this is an image
1.16 (jsc 20-Sep-99): caption varchar(4000),
1.16 (jsc 20-Sep-99): original_width integer,
1.16 (jsc 20-Sep-99): original_height integer
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create index comments_by_page_idx on comments(page_id);
1.1 (jsc 27-Feb-99): create index comments_by_user_idx on comments(user_id);
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create view comments_not_deleted
1.1 (jsc 27-Feb-99): as
1.1 (jsc 27-Feb-99): select *
1.1 (jsc 27-Feb-99): from comments
1.1 (jsc 27-Feb-99): where deleted_p is null
1.1 (jsc 27-Feb-99): or deleted_p = 'f';
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- user-contributed links (a micro-Yahoo)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table links (
1.1 (jsc 27-Feb-99): page_id integer not null references static_pages,
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): url varchar(300) not null,
1.1 (jsc 27-Feb-99): link_title varchar(100) not null,
1.1 (jsc 27-Feb-99): link_description varchar(4000),
1.1 (jsc 27-Feb-99): -- contact if link is dead?
1.1 (jsc 27-Feb-99): contact_p char(1) default 't' check (contact_p in ('t','f')),
1.1 (jsc 27-Feb-99): status varchar(10) default 'live' check (status in ('live','coma','dead','removed')),
1.1 (jsc 27-Feb-99): originating_ip varchar(50),
1.1 (jsc 27-Feb-99): posting_time date,
1.2 (philg 02-Mar-99): -- last time this got checked
1.2 (philg 02-Mar-99): checked_date date,
1.1 (jsc 27-Feb-99): unique(page_id,url)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): --
1.1 (jsc 27-Feb-99): -- we store glob patterns (like REGEXP but simpler)
1.1 (jsc 27-Feb-99): -- of URLs that we don't want to see added
1.1 (jsc 27-Feb-99): --
1.1 (jsc 27-Feb-99): -- page_id = NULL means "applies to all pages on the site"
1.1 (jsc 27-Feb-99): --
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table link_kill_patterns (
1.1 (jsc 27-Feb-99): page_id integer references static_pages,
1.1 (jsc 27-Feb-99): -- who added the kill pattern
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): date_added date,
1.1 (jsc 27-Feb-99): glob_pattern varchar(500) not null
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): --- which pages has a user read
1.1 (jsc 27-Feb-99): --- we'll do this index-only to save space and time
1.1 (jsc 27-Feb-99): --- **** good table to put in another tablespace
1.1 (jsc 27-Feb-99): --- (add "tablespace photonet_index" AFTER the organization directive;
1.1 (jsc 27-Feb-99): --- Oracle doesn't believe in commutivity)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table user_content_map (
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): page_id integer not null references static_pages,
1.1 (jsc 27-Feb-99): view_time date not null,
1.1 (jsc 27-Feb-99): primary key(user_id, page_id))
1.1 (jsc 27-Feb-99): organization index;
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- referers (people who came in from external references)
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table referer_log (
1.1 (jsc 27-Feb-99): -- relative to the PageRoot, includes the leading /
1.1 (jsc 27-Feb-99): local_url varchar(250) not null,
1.1 (jsc 27-Feb-99): -- full URL on the foreign server, including http://
1.1 (jsc 27-Feb-99): foreign_url varchar(250) not null,
1.2 (philg 02-Mar-99): entry_date date not null, -- we count referrals per day
1.1 (jsc 27-Feb-99): click_count integer default 0
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- we need to make it really fast to get to the one relevant row
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create index referer_log_idx on referer_log (local_url, foreign_url,
1.1 (jsc 27-Feb-99): entry_date); -- **** tablespace photonet_index
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- Tcl GLOB patterns that lump referrer headers together,
1.1 (jsc 27-Feb-99): -- particularly useful for search engines (i.e., we don't want
1.1 (jsc 27-Feb-99): -- every referral from AltaVista logged separately).
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table referer_log_glob_patterns (
1.1 (jsc 27-Feb-99): glob_pattern varchar(250) primary key,
1.1 (jsc 27-Feb-99): canonical_foreign_url varchar(250) not null,
1.1 (jsc 27-Feb-99): -- not NULL if this is here for a search engine and
1.1 (jsc 27-Feb-99): -- we're also interested in harvesting query strings
1.1 (jsc 27-Feb-99): search_engine_name varchar(30),
1.1 (jsc 27-Feb-99): search_engine_regexp varchar(200)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- strings entered by users, either on our site-local search engine
1.1 (jsc 27-Feb-99): -- or at Internet-wide servers
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table query_strings (
1.1 (jsc 27-Feb-99): query_date date not null,
1.1 (jsc 27-Feb-99): query_string varchar(300) not null,
1.1 (jsc 27-Feb-99): -- if they came in from a public search engine and we
1.1 (jsc 27-Feb-99): -- picked it from the referer header
1.1 (jsc 27-Feb-99): search_engine_name varchar(30),
1.1 (jsc 27-Feb-99): -- subsection of the site from which they were searching
1.1 (jsc 27-Feb-99): subsection varchar(100),
1.1 (jsc 27-Feb-99): -- if we know who they are
1.1 (jsc 27-Feb-99): user_id integer references users,
1.1 (jsc 27-Feb-99): -- not null if this was a local query
1.1 (jsc 27-Feb-99): n_results integer
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- **** tablespace photonet_index
1.1 (jsc 27-Feb-99): create index query_strings_by_user on query_strings (user_id);
1.6 (jsc 11-Jul-99):
1.6 (jsc 11-Jul-99): create index query_strings_by_date on query_strings (query_date);
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- stuff to manage email and make sure that we don't keep sending
1.1 (jsc 27-Feb-99): -- to guys with invalid addresses
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- a bounce is event_type = 'bounce' and content NULL
1.1 (jsc 27-Feb-99): -- a bboard alert is event_type = 'alert'
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- this is actually a great candidate for an index-organized table
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table email_log (
1.1 (jsc 27-Feb-99): user_id integer not null references users,
1.1 (jsc 27-Feb-99): email_date date not null,
1.1 (jsc 27-Feb-99): event_type varchar(100) not null,
1.1 (jsc 27-Feb-99): content varchar(4000)
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- **** tablespace photonet_index
1.1 (jsc 27-Feb-99): create index email_log_idx on email_log ( user_id, event_type );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- can't have local and foreign_urls too long or they won't be
1.1 (jsc 27-Feb-99): -- indexable in Oracle
1.1 (jsc 27-Feb-99): -- note that the local URL does NOT include the starting /
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): create table clickthrough_log (
1.1 (jsc 27-Feb-99): local_url varchar(400) not null,
1.1 (jsc 27-Feb-99): foreign_url varchar(300) not null, -- full URL on the foreign server
1.1 (jsc 27-Feb-99): entry_date date, -- we count referrals per day
1.1 (jsc 27-Feb-99): click_count integer default 0
1.1 (jsc 27-Feb-99): );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- I can't decide which of these is better
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): --create index clickthrough_log_index on clickthrough_log (local_url, foreign_url );
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): -- I think this one is because we actually do use all the columns
1.1 (jsc 27-Feb-99):
1.1 (jsc 27-Feb-99): --- **** tablespace photonet_index
1.1 (jsc 27-Feb-99): create index clickthrough_log_index on clickthrough_log (local_url, foreign_url, entry_date);
1.1 (jsc 27-Feb-99):
1.3 (teadams 18-Apr-99):
1.3 (teadams 18-Apr-99): --- keep track of user sessions
1.3 (teadams 18-Apr-99): --- we keep the total in "session_count" and the number of repeaters
1.3 (teadams 18-Apr-99): -- (folks who had a last_visit cookie already set) in repeat_count
1.3 (teadams 18-Apr-99): -- entry-date is midnight on the day of interest, as with our
1.3 (teadams 18-Apr-99): -- referer and clickthrough stuff
1.3 (teadams 18-Apr-99):
1.3 (teadams 18-Apr-99): create table session_statistics (
1.3 (teadams 18-Apr-99): session_count integer default 0 not null,
1.3 (teadams 18-Apr-99): repeat_count integer default 0 not null,
1.3 (teadams 18-Apr-99): entry_date date not null
1.3 (teadams 18-Apr-99): );
1.1 (jsc 27-Feb-99):