rdb module

Variety of shared routines which interact with resource database, kept separate
to maintain ability to change when RDM changes

In this description, PIE table names are differentiated by prefixing them with
PIE

Connect to database when file included

getrdbdesc routine

Gets the description of a resource from the RDB

Input: Item information as hash (keys as item table)
Output: Description text

PDL

If item{location} starts rdb://[id]
    Set desc - call getfield routine with parameters "summary","resource",
    "resource_id",id 
Else
    Set desc="" as item not in resource database
Return desc


getrdbhelp routine

Get a help URL from the RDB and returns formatted HTML list of links to help
pages

Input: Item information as hash (keys as PIE item table)
Output: Lists of helpurls,helptexts

PDL

If item{location} starts rdb://[id]
    Set helpurl[0] - call getfield routine with parameters "about_url",
    "resource","resource_id",id 
    Set helptext[0] - call getfield routine with parameters "about_url_text",
    "resource","resource_id",id
    Get help_urls,helptexts from location table
    Foreach location
    	Push url into helpurl array
	Push text into helptext array
Else
    Set helpurl[0],helptext[0] to "" as item not in resource database
Return helpurl,helptext

getrdblocinfo routine

Get information about a location

Input: ID of location
Output: Hashref information about location

PDL

Get information from location, licensee_inst tables about location
Return information

getrdblogo routine

Get the logo url and alt text from the RDB

Input: ID of location
Output: Reference to list (iconurl,alt text)

PDL

Set icon list to be ("","")
If item{location} starts rdb://[id]
    Get url,alt text from icon table, overwriting icon list
Return reference to icon list

getrdbresourcetypes routine

Gets a list of resource types from the RDB

Input: None
Output: Reference to list of resource type names

PDL

Get list of names of resource types from resource_type table
Return list

getrdbsearchableresources routine

Gets a list of resources which can be searched from the RDB

Input: None
Output: Reference to list of hased information about searchable resources

PDL

Get list of resources with search_protocol !="none" from resource table
Return list

getrdbtitle routine

Get the title of a resource from the rdb

Input: Item information as hash (keys as PIE item table)
Output: Title text

PDL

If item{location} starts rdb://[id]
    Set description - call getfield routine with parameters "name","resource",
    "resource_id",id
Else
    Set description="" as item not in resource database
Return description

getrdbtraining routine

Get the URL of training information for a resource from the RDB

Input: Item information as hash (keys as PIE item table)
Output: Training url text text

PDL

Set trainingurl=""
If item{location} starts rdb://[id]
    Get training url from resource table
Return trainingurl

getrdburl routine

Get the URL of a location from the RDB - now returns list of locations

Input: Location string from PIE item
Output: List of hashes of information about locations

PDL

If item{location} starts rdb://[id]
    Get location information from location table
Return location information

rdbavailable routine

Works out whether a location is available to someone at a particular IP address
and in a particular list of groups

Input: locationid, groups list, IP address
Output: String "available"|"unavailable"

PDL

Remove rdb:// from locationid
Reformat group list into simple array
Check group_right table to see if members of any of the groups in list can
access location
If no group matches return "unavailable"
Get list of IP ranges permitted to accesss location from ip_range,
location_ip_range tables
Set ipmatch=0
If there are no IP range listed set ipmatch=1
Foreach IP range
    If compareip routine returns true
    	Set ipmatch=1
	Last
Return "available" if ipmatch=1, else return "unavailable"

rdbsearch routine

Searches resource database

Input: Search information as reference to hash
Output: Array of hashed results

PDL

Set sql=""
For i=1 to 3
    Set text = text[i] from search info
    Set field = field[i] from search info
    If i>1 and ops[i] defined in search info
    	Set boolean, not from ops[i] defined in search info
    Else
    	Set boolean="BLANK", not=""
    Set hash bool_trans to transform booleans to SQL operators (AND->INTERSECT
    etc)
    Set text="" if undefined
    If text=""
    	If i=1
	    Set text="*"
	Else
	    Next i (as only the first text field can be blank)
    Next if text=" "
    Set field="basic" if field is not text, or is a field not relevant to RDB
    Set field="title" if field="name"
    Set type=search info type or "" if undefined
    Set type="any" unless type is text
    Set age=search info age or "" if undefined
    Set age="any" unless age is text
    Substitute % for * in text (make wild cards SQL compliant)
    Set text="%".text."%" if text does not contain "%"
    Make text lower case
    Append translated boolean to sql
    Append "(" to sql
    Set list of fields to search if field="basic"
    If field="basic"
    	Foreach field in list
	    Call make_search_sql routine and add result to basicsql array
	Join elements of basicsql with UNION
    Else
    	Append result of make_search_sql routine to sql
Append ")" to sql
Execute sql and get back results as array of hashes
Return results

getfield routine

Given a column, table, unique id column and unique id return what's in the
resource database

Input: column,table,id column, id
Output: text

PDL

Run sql
Return result

make_search_sql routine

Creates a chunk of sql for the rdbsearch routine
Note: this contains postgres specific SQL coding

Input: field, text, not, resource type, age
Output: SQL

PDL

Set start sql
If field="title"
    Append appropriate where clause to sql
If field="description"
    Append appropriate where clause to sql
If field="keyword"
    Append appropriate where clause to sql
If field="subject"
    Append appropriate where clause to sql
Unless type="any"
    Append appropriate where clause to sql
Unless age="any"
    Append appropriate where clause to sql
Return sql

process_age routine

Given a number of days, return date/time before now in format yyyymmddhhmmss

Input: No of days
Output: Date/time string

PDL

Get current time (in seconds since 1970)
Take off 86400 * no of days (there are 86400 seconds in a day)
Use localtime to return day, month, year etc.
Reformat results to be human readable (e.g. add 1900 to year)
Return date/time in specified format

compareip routine

Given an IP address, netmask and network address, work out if IP address is in
network

Input: IP, mask, network address
Output: 1 (true), 0 (false)

PDL

Turn addresses into arrays by splitting at .
Set return=1
For i=0 to 3
    Call binary_convert routine to turn ith part of each address into binary
    Set return = 0 if (ip AND mask) != network for ith part
Return return

binary_convert routine

Returns binary equivalent of decimal number

Input: Decimal number
Output: Binary number

PDL

Use sprintf builtin format to convert number to hex
Use substitution to convert hex digits 0-f to binary equivalents
Return binary number