Homec4science
Diffusion invenio-infoscience (maint-2.1) Browse

R3600/modules/websubmit/lib/websubmit_dblayer.py
17b97ec69a66maint-2.1

Last Change
Raw File

websubmit_dblayer.py
View Options

# -*- coding: utf-8 -*-
## $Id$
##
## This file is part of CDS Invenio.
## Copyright (C) 2002, 2003, 2004, 2005, 2006 CERN.
##
## CDS Invenio is free software; you can redistribute it and/or
## modify it under the terms of the GNU General Public License as
## published by the Free Software Foundation; either version 2 of the
## License, or (at your option) any later version.
##
## CDS Invenio is distributed in the hope that it will be useful, but
## WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
## General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with CDS Invenio; if not, write to the Free Software Foundation, Inc.,
## 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
"""The Data Base layer for the WebSubmit module"""
__revision__ = "$Id$"
from invenio.dbquery import run_sql
def get_storage_directory_of_action(action):
"""Given the (short) name of an action (e.g. SBI, MBI, etc), query
the database to retrieve the name of the directory used by this
action for the storage of files during submissions.
@param action: (string) - the (short) name of the action.
@return: (string -OR- None) - In the case that the action exists,
return the directory name as a string; In the case that the
action does not exist, return a None value.
"""
## Initialise directory to None for case in which action doesn't exist:
directory = None
## Query the DB:
qstr = """SELECT dir FROM sbmACTION WHERE sactname=%s LIMIT 1"""
qres = run_sql(qstr, (action,))
if len(qres) > 0:
## The action exists - set the correct value of directory:
directory = qres[0][0]
## return the directory name (or None, if no action was found)
return directory
def get_longname_of_doctype(doctype):
"""Given the ID of a document type (doctype), query the database to
retrieve the doctype's long name.
@param doctype: (string) - the ID of the document type.
@return: (string -OR- None) - In the case that the document type exists,
return the name as a string; In the case that the document type does
not exist, return a None value.
"""
## Initialise the document-type's long-name to None for case in which
## document-type doesn't exist:
ldocname = None
## Query the DB:
qstr = """SELECT ldocname FROM sbmDOCTYPE WHERE sdocname=%s LIMIT 1"""
qres = run_sql(qstr, (doctype,))
if len(qres) > 0:
## The doctype exists - get the doctype long-name:
ldocname = qres[0][0]
## return the doctype long-name (or None if no doctype was found):
return ldocname
def get_longname_of_action(action):
"""Given the (short) name of an action (e.g. SBI, MBI, etc), query
the database to retrieve the action's long name.
@param action: (string) - the ID of the document type.
@return: (string -OR- None) - In the case that the action exists,
return the name as a string; In the case that the action does
not exist, return a None value.
"""
## Initialise the action's long-name to None for case in which
## action doesn't exist:
lactname = None
## Query the DB:
qstr = """SELECT lactname FROM sbmACTION WHERE sactname=%s LIMIT 1"""
qres = run_sql(qstr, (action,))
if len(qres) > 0:
## The action exists - get its long-name:
lactname = qres[0][0]
## return the action long-name (or None, if no action was found)
return lactname
def get_num_pages_of_submission(submission):
"""Given the ID of a submission (e.g. SBITEXT, MBIPICT, etc), query
the database to retrieve the number of pages making up the
submission.
@param submission: (string) - the ID of the submission.
@return: (integer -OR- None) - In the case that the submission
exists, and the number of pages stored for it is a valid integer,
return the number of pages as an integer; In the case that the
submission doesn't exist, or that the number of pages for a
submission is not a valid integer (could be NULL, for example),
return None.
"""
## Initialise the number of pages to None
numpages = None
## Query the DB:
qstr = """SELECT nbpg FROM sbmIMPLEMENT WHERE subname=%s LIMIT 1"""
qres = run_sql(qstr, (submission,))
if len(qres) > 0:
## The submission exists - set the correct value of directory:
numpages = qres[0][0]
try:
numpages = int(qres[0][0])
except (ValueError, TypeError):
## The number of pages was not an integer
numpages = None
## return the number of pages (or None, if no submission was found or
## the number of pages was not a valid integer):
return numpages
def get_parameter_value_for_doctype(doctype, paramname):
"""Get the value for a given parameter for a given document type.
@param doctype: (string) - the ID of the document type.
@param paramname: (string) - the name of the parameter for
which the value is to be retrieved.
@return: (string -OR- None) - The value of the parameter if it
could be found in the sbmPARAMETERS table for this document-
type; None if there was no value.
"""
param_value = None
qstr = """SELECT value FROM sbmPARAMETERS """ \
"""WHERE doctype=%s and name=%s """ \
"""LIMIT 1"""
qres = run_sql(qstr, (doctype, paramname))
if len(qres) > 0:
## The parameter exists for this doctype - get its value:
param_value = qres[0][0]
return param_value
def submission_exists_in_log(doctype, action, subm_id, email):
"""Given a doctype, action, submission-id and email-address,
check the submission-log in the database to determine
whether or not the submission actually exists.
If it does (i.e. 1 or more rows are found in the log for
it, return 1; else return 0.
@param doctype: (string) - the ID of the document type on
which the submission is performed.
@param action: (string) - the ID of the action for this
submission.
@param subm_id: (string) - the ID of the submission. Also
sometimes referred to as the access-number for the
submission.
@param email: (string) - the email address of the owner
of the submission (i.e. the 'submitter').
@return: (integer) - 1 if the submission exists in the
submission log; 0 if it does not exist.
"""
## reset flag indicating whether or not a submission exists:
subm_exists = 0
## Get the number of rows existing in the submission-log
## for this submission:
qstr = """SELECT count(id) FROM sbmSUBMISSIONS """ \
"""WHERE doctype=%s AND action=%s """ \
"""AND id=%s AND email=%s"""
qres = run_sql(qstr, (doctype, action, subm_id, email))
if len(qres) > 0:
num_submissions = qres[0][0]
try:
num_submissions = int(num_submissions)
except (ValueError, TypeError):
## Bad value for number of submissions, set as 0
num_submissions = 0
if num_submissions > 0:
## The submission exists in the log:
subm_exists = 1
## return submission-exists flag:
return subm_exists
def log_new_pending_submission(doctype, action, subm_id, email):
"""Insert a new submission into the submission-log.
The submission record in the log is identified by:
+ doctype
+ action
+ access-number (submission ID)
+ email (of submitter)
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the type of action performed.
@param subm_id: (string) - the access-number (or submission-
instance ID).
@param email: (string) - the email address of the submitter.
@return: (integer) - the number of rows inserted by the query.
"""
## Insert the details of the new submission into the DB:
qstr = """INSERT INTO sbmSUBMISSIONS """ \
"""(email, doctype, action, status, id, reference, cd, md) """ \
"""VALUES (%s, %s, %s, 'pending', %s, '', NOW(), NOW())"""
qres = run_sql(qstr, (email, doctype, action, subm_id))
## return the number of rows inserted:
return int(qres)
def log_new_completed_submission(doctype, action, subm_id, email, reportnum):
"""Insert a new submission into the submission-log.
The submission record in the log is identified by:
+ doctype
+ action
+ access-number (submission ID)
+ email (of submitter)
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the type of action performed.
@param subm_id: (string) - the access-number (or submission-
instance ID).
@param email: (string) - the email address of the submitter.
@param reportnum: (string) - the report number associated with
the submission.
@return: (integer) - the number of rows inserted by the query.
"""
## Insert the details of the new submission into the DB:
qstr = """INSERT INTO sbmSUBMISSIONS """ \
"""(email, doctype, action, status, id, reference, cd, md) """ \
"""(%s, %s, %s, 'finished', %s, %s, NOW(), NOW())"""
qres = run_sql(qstr, (email, doctype, action, subm_id, reportnum))
## return the number of rows inserted:
return int(qres)
def update_submission_modified_date_in_log(doctype, action, subm_id, email):
"""Update the modification date of a submission in the log.
The date is set equal to the current-date and time.
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the type of action performed.
@param subm_id: (string) - the access-number (or submission-
instance ID).
@param email: (string) - the email address of the submitter.
@return: None
"""
## Update the modification date to NOW() in the log for the submission:
qstr = """UPDATE sbmSUBMISSIONS SET md=NOW() """ \
"""WHERE doctype=%s AND action=%s AND id=%s AND email=%s"""
run_sql(qstr, (doctype, action, subm_id, email))
def update_submission_reference_in_log(doctype, subm_id, email, reference):
"""Update the reference number for a submission in the submission-log.
@param doctype: (string) - the ID of the document type.
@param subm_id: (string) - the access-number (or submission-
instance ID).
@param email: (string) - the email address of the submitter.
@param reference: (string) - the new value for the submission's reference.
@return: None
"""
## Update the reference for the submission in the log:
qstr = """UPDATE sbmSUBMISSIONS SET reference=%s """ \
"""WHERE doctype=%s AND id=%s AND email=%s"""
run_sql(qstr, (reference, doctype, subm_id, email))
def update_submission_reference_and_status_in_log(doctype,
action,
subm_id,
email,
reference,
status="finished"):
"""Update the modification date (to NOW()), the reference, and the
status of a submission in the log.
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the type of action performed.
@param subm_id: (string) - the access-number (or submission-
instance ID).
@param email: (string) - the email address of the submitter.
@param reference: (string) - the new value for the submission's reference.
@param status: (string) - the new status for the submission. NOTE:
DEFAULTS TO 'finished'.
@return: None
"""
qstr = """UPDATE sbmSUBMISSIONS """ \
"""SET md=NOW(), reference=%s, status=%s """ \
"""WHERE doctype=%s AND action=%s AND id=%s AND email=%s"""
run_sql(qstr, (reference, status, doctype, action, subm_id, email))
def form_element_sets_cookie(element):
"""Given the name of a WebSubmit form element, check its description
in the database to see whether or not it sets a cookie.
@param element: (string) - the name of the form element.
@return: (integer) - 1 if the element sets a cookie; 0 if not.
"""
## Flag to indicate whether or not the element sets a cookie:
sets_cookie = 0
## Query the DB to see whether this element sets a cookie:
qstr = """SELECT cookie FROM sbmFIELDDESC WHERE name=%s LIMIT 1"""
qres = run_sql(qstr, (element,))
if len(qres) > 0:
cookie_flag = qres[0][0]
try:
cookie_flag = int(cookie_flag)
except (ValueError, TypeError):
## Cookie flag doesn't seem to be an integer: bad value so
## assume that no cookie is set:
cookie_flag = 0
if cookie_flag == 1:
## The the element sets a cookie:
sets_cookie = 1
## return the flag-value:
return sets_cookie
def get_form_fields_on_submission_page(subname, pagenum):
"""Get the details of all form fields as they appear on the
current page of the current submission.
@param subname: (string) - the name of the submission (e.g.
SBITEXT).
@param pagenum: (string) - the page-number of the submission
for which the field details are to be retrieved.
@return: (tuple of tuples) - each tuple is a row giving
details of a submission form-field:
(subname, pagenb, fieldnb, fidesc, fitext, level, sdesc,
checkn, cd, md, fiefi1, fiefi2)
"""
qstr = """SELECT subname, pagenb, fieldnb, fidesc, fitext, """ \
"""level, sdesc, checkn, cd, md, fiefi1, fiefi2 """ \
"""FROM sbmFIELD WHERE subname=%s AND pagenb=%s """ \
"""ORDER BY fieldnb"""
qres = run_sql(qstr, (subname, pagenum))
return qres
def get_element_description(element):
"""Get the description details of a particular submission
form element.
@param element: (string) - the name of the element.
@return: (tuple -OR- None) - containing the details of the
element. (name, alephcode, marccode, type, size, rows, cols,
maxlength, val, fidesc, cd, md, modifytext, fddfi2, cookie);
OR None if the field cannot be found.
"""
element_descr = None
qstr = """SELECT name, alephcode, marccode, type, size, """ \
"""rows, cols, maxlength, val, fidesc, cd, md, """ \
"""modifytext, fddfi2, cookie """ \
"""FROM sbmFIELDDESC """ \
"""WHERE name=%s LIMIT 1"""
qres = run_sql(qstr, (element,))
if len(qres) > 0:
## Element was found - get the first row:
element_descr = qres[0]
## Return the tuple of details of the form-element
return element_descr
def get_element_check_description(check):
"""Get the desciption of a given JavaScript form-field check.
@param check: (string) - the name of the check for which the
description is to be retrieved.
@return: (string -OR- None) - if the check exists, its
description will be returned; If not, None is returned.
"""
check_descr = None
qstr = """SELECT chdesc FROM sbmCHECKS WHERE chname=%s LIMIT 1"""
qres = run_sql(qstr, (check,))
if len(qres) > 0:
## Check exists:
check_descr = qres[0][0]
## Return the check description:
return check_descr
def get_form_fields_not_on_submission_page(subname, pagenum):
"""Get the details of all form fields (for the current submission)
that are NOT on the current submission page.
@param subname: (string) - the name of the submission (e.g.
SBITEXT).
@param pagenum: (string) - the page-number of the submission
for which the field details are to be retrieved.
@return: (tuple of tuples) - each tuple is a row giving
details of a submission form-field:
(subname, pagenb, fieldnb, fidesc, fitext, level, sdesc,
checkn, cd, md, fiefi1, fiefi2)
"""
qstr = """SELECT subname, pagenb, fieldnb, fidesc, fitext, """ \
"""level, sdesc, checkn, cd, md, fiefi1, fiefi2 """ \
"""FROM sbmFIELD WHERE subname=%s AND pagenb !=%s"""
qres = run_sql(qstr, (subname, pagenum))
return qres
def function_step_is_last(doctype, action, step):
"""Given a function step, determine whether it is the last step
for the functions of a given submission.
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the action.
@param step: (integer) - the step number to be tested
for.
@return: (integer) - 1 if this is the last step for the
functions of the submission; 0 if not.
"""
last_step = 1
qstr = """SELECT step FROM sbmFUNCTIONS """ \
"""WHERE action=%s AND doctype=%s AND step > %s"""
qres = run_sql(qstr, (action, doctype, step))
if len(qres) > 0:
## Rows were returned. This means that this is not the last-step:
last_step = 0
return last_step
def get_collection_children_of_submission_collection(collection_id):
"""Get the collection IDs of all 'collection' children of a
given collection.
@param collection_id: (integer) the ID of the parent collection
for which collection-children are to be retrieved.
@return: (tuple) of tuples. Each tuple is a row containing the collection
ID of a 'collection' child of the given parent collection.
"""
## query to retrieve IDs of collections attached to a given collection:
qstr = """SELECT id_son FROM sbmCOLLECTION_sbmCOLLECTION """ \
"""WHERE id_father=%s ORDER BY catalogue_order ASC"""
qres = run_sql(qstr, (collection_id,))
return qres
def get_submission_collection_name(collection_id):
"""Get the name of a given submission-collection.
@param collection_id: (string) - the ID of the collection
for which the details are to be retrieved.
@return: (string -OR- None) - If a collection exists for
the given collection-id, the name of that collection will be
returned as a string; otherwise, None is returned.
"""
collection_name = None
qstr = """SELECT name FROM sbmCOLLECTION """ \
"""WHERE id=%s LIMIT 1"""
qres = run_sql(qstr, (collection_id,))
if len(qres) > 0:
## Get the name:
collection_name = qres[0][0]
return collection_name
def get_doctype_children_of_submission_collection(collection_id):
"""Get the doctype IDs of all 'doctype' children of
a given collection.
@param collection_id: (integer) the ID of the parent collection
for which doctype-children are to be retrieved.
@return: (tuple) of tuples. Each tuple is a row containing the collection
ID of a 'doctype' child of the given parent collection.
"""
qstr = """SELECT id_son """ \
"""FROM sbmCOLLECTION_sbmDOCTYPE """ \
"""WHERE id_father=%s """ \
"""ORDER BY catalogue_order ASC"""
qres = run_sql(qstr, (collection_id,))
return qres
def get_categories_of_doctype(doctype):
"""Get the categories of a given document type, ordered
by score and long-name.
@param doctype: (string) - the ID of the document type for which
the categories are to be retrieved.
@return: (tuple) of tuples, where each tuple contains the details
of a given category: (sname, lname, score).
"""
qstr = """SELECT sname, lname, score FROM sbmCATEGORIES """ \
"""WHERE doctype=%s """ \
"""ORDER BY score ASC, lname ASC"""
qres = run_sql(qstr, (doctype,))
return qres
def get_doctype_details(doctype):
"""Get the details of a given document type.
@param doctype: (string) - the document type for which details
are to be retrieved.
@return: (tuple -OR- None) - If the document type exists, a tuple
containing its details is returned:
(sdocname, ldocname, cd, md, description); if not, None is
returned.
"""
doctype_details = None
qstr = """SELECT ldocname, sdocname, cd, md, description """ \
"""FROM sbmDOCTYPE """ \
"""WHERE sdocname=%s """ \
"""LIMIT 1"""
qres = run_sql(qstr, (doctype,))
if len(qres) > 0:
doctype_details = qres[0]
return doctype_details
def get_actions_on_submission_page_for_doctype(doctype):
"""Given a document-type ID, get a list of the actions
(action-IDs) that are on that document-type's
submission page, ordered by the button-order.
@param doctype: (string) - the ID of the document type.
@return (tuple) of tuples, each containing the action ID
of an action that should appear as a button on the
document type's submission page.
"""
qstr = """SELECT actname FROM sbmIMPLEMENT """ \
"""WHERE docname=%s AND displayed='Y' """ \
"""ORDER BY buttonorder"""
qres = run_sql(qstr, (doctype,))
return qres
def get_action_details(action):
"""Get the details of a given action.
@param action: (string) - the ID of the given action.
@return (tuple -OR- None) - If the action exists in the DB,
a tuple containing its details is returned:
(lactname, dir, cd, md, actionbutton, statustext); If no
details were found for the action, None is returned.
"""
action_details = None
qstr = """SELECT lactname, dir, cd, md, actionbutton, statustext """ \
"""FROM sbmACTION """ \
"""WHERE sactname=%s"""
qres = run_sql(qstr, (action,))
if len(qres) > 0:
action_details = qres[0]
return action_details
def get_parameters_of_function(function):
"""Get the names of all parameters of a given function.
@param function: (string) - the name of the function.
@return: (tuple) of tuples - each tuple containing the
name of a given parameter.
"""
qstr = """SELECT param FROM sbmFUNDESC """ \
"""WHERE function=%s"""
qres = run_sql(qstr, (function,))
return qres
def get_details_of_submission(doctype, action):
"""Get the details of a given submission for a given
document type.
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the given action.
@return: (tuple -OR- None) - in the case that the
submission exists for the given document type, return
a tuple containing its details:
(docname, actname, displayed, subname, nbpg, cd, md,
buttonorder, statustext, level, score, stpage, endtxt);
In the case that the submission could not be found in
the DB, return None.
"""
submission_details = None
qstr = """SELECT docname, actname, displayed, subname, """ \
"""nbpg, cd, md, buttonorder, statustext, level, """ \
"""score, stpage, endtxt """ \
"""FROM sbmIMPLEMENT """ \
"""WHERE docname=%s AND actname=%s LIMIT 1"""
qres = run_sql(qstr, (doctype, action))
if len(qres) > 0:
submission_details = qres[0]
return submission_details
def get_functions_for_submission_step(doctype, action, step):
"""Get the function names and scores of all functions within a
given step of a given submission of a given document-type.
The functions are returned in ascending order of score.
@param doctype: (string) - the document type ID.
@param action: (string) - the action ID.
@param step: (integer) - the step number.
@return: (tuple) - the details of each function within the
given step: (function-name, score)
"""
qstr = """SELECT function, score """ \
"""FROM sbmFUNCTIONS """ \
"""WHERE action=%s AND doctype=%s AND step=%s """ \
"""ORDER BY score ASC"""
qres = run_sql(qstr, (action, doctype, step))
return qres
def get_submissions_at_level_X_with_score_above_N(doctype, level, score_N):
"""Get the details of a given submission for a given
document type.
@param doctype: (string) - the ID of the document type.
@return: (tuple) of tuples - each tuple containing the
details of a given submission:
(docname, actname, displayed, subname, nbpg, cd, md,
buttonorder, statustext, level, score, stpage, endtxt);
"""
qstr = """SELECT docname, actname, displayed, subname, """ \
"""nbpg, cd, md, buttonorder, statustext, level, """ \
"""score, stpage, endtxt """ \
"""FROM sbmIMPLEMENT """ \
"""WHERE docname=%s AND level !='0' AND level=%s AND score > %s """ \
"""ORDER BY score ASC"""
qres = run_sql(qstr, (doctype, level, score_N))
return qres
def submission_is_finished(doctype, action, subm_id, email):
"""Determine whether a submission is finished. This is done
by checking in the submission log for a submission with
the id of the current submission, and the status
'finished'.
@param doctype: (string) - the ID of the document type.
@param action: (string) - the ID of the action.
@param subm_id: (string) - the ID of the submission in the
submission log (i.e. its access-number).
@param email: (string) - the email address of the submitter.
@return: (integer) - 0 if the submission is not found with
a status of 'finished' in the log; 1 if it is (and is therefore
a finished submission).
"""
submission_finished = 0
qstr = """SELECT id FROM sbmSUBMISSIONS """ \
"""WHERE doctype=%s AND action=%s AND id=%s """ \
"""AND email=%s AND status='finished'"""
qres = run_sql(qstr, (doctype, action, subm_id, email))
if len(qres) > 0:
## At least one row was returned - this
## submission is finished:
submission_finished = 1
return submission_finished
def get_cookies_set_on_field_for_user(userid, fieldname):
"""Given a userid and a fieldname, get a list of the cookies
stored in the DB for that user/field.
@param userid: (integer) - the user-ID of the current
submitter.
@param fieldname: (string) - the name of the field for which
the list of cookies is to be retrieved.
@return: (tuple) - of tuples. Each tuple containing the
'value' of the cookie.
"""
qstr = """SELECT value FROM sbmCOOKIES """ \
"""WHERE uid=%s AND name=%s"""
qres = run_sql(qstr, (userid, fieldname))
return qres
def cookie_is_set_on_field_for_user(userid, fieldname):
"""Determine whether or not a cookie is set on a given field
for a given user.
@param userid: (integer) - the user-ID of the current
submitter.
@param fieldname: (string) - the name of the field on
which we wish to know whether a cookie was set.
@return: (integer) - 0 if no cookie has been set on the
field; 1 if a cookie has been set on it.
"""
cookie_set = 0
qstr = """SELECT id FROM sbmCOOKIES WHERE uid=%s AND name=%s"""
qres = run_sql(qstr, (userid, fieldname))
if len(qres) > 0:
## There is a row, so this cookie is set:
cookie_set = 1
return cookie_set
def update_cookie_value_on_field_for_user(userid, fieldname, value):
"""Update the value for a given cookie that has been set on a
given field for a given user.
@param userid: (integer) the user-ID of the current submitter.
@param fieldname: (string) - the name of the field for which
the cookie's value is to be updated.
@param value: (string) - the new value for the cookie.
@return: None
"""
qstr = """UPDATE sbmCOOKIES SET value=%s """ \
"""WHERE uid=%s AND name=%s"""
run_sql(qstr, (value, userid, fieldname))
def add_new_cookie_to_field_for_user(userid, fieldname, value):
"""Add a new cookie to a given field for a given user.
@param userid: (integer) - the user-ID of the current submitter.
@param fieldname: (string) - the name of the onto which the
cookie is to be added.
@param value: (string) - the value that is to be assigned to
the cookie.
@return: (integer) - the number of rows inserted.
"""
qstr = """INSERT into sbmCOOKIES (name, value, uid) """ \
"""VALUES (%s, %s, %s)"""
qres = run_sql(qstr, (fieldname, value, userid))
## Return the number of rows inserted:
return int(qres)
c4science · Help