• View
• Edit
• History
• Print

SQLPropertyFunctions

This page details various SQL functions that will assist with property handling.

To load SQL into the DB, first type the command

 DELIMITER |

and then copy and paste exactly as written below. This will prompt the interpreter to ignore ; and look for | as the statement end command. To return back to normal SQL usage, use

 DELIMITER ;

FUNCTION get_person_prop (rid, cid, prop_name) RETURNS prop_value;

get_person_prop is used to lookup a specific property value for a specific registrant and a specific convention. The global convention is also checked. If no property by this name exists for this registrant and this or global convention, the function returns NULL.

 CREATE FUNCTION get_person_prop (v_rid INT, v_cid INT, v_pname VARCHAR(100))
   RETURNS VARCHAR(100)
   BEGIN
     DECLARE ret_val VARCHAR(100);
     SELECT prop_value INTO ret_val 
       FROM reg_properties 
      WHERE prop_rid = v_rid
        AND prop_cid = v_cid
        AND prop_name = v_pname;

     IF (ret_val IS NULL) THEN
        SELECT prop_value INTO ret_val 
          FROM reg_properties 
         WHERE prop_rid = v_rid
           AND prop_cid = 0
           AND prop_name = v_pname; 
     END IF;

     RETURN ret_val;
   END|  

FUNCTION get_default_prop (cid, prop_name) RETURNS prop_value;

get_default_prop is used to look up a specific property value for any registrant at a specific convention. The global convention is also checked. If no property by this name exists for this or the global convention, the function returns NULL.

 CREATE FUNCTION get_default_prop (v_cid INT, v_pname VARCHAR(100))
   RETURNS VARCHAR(100)
   BEGIN
     DECLARE ret_val VARCHAR(100);

     SELECT prop_default INTO ret_val
       FROM config_properties
      WHERE prop_name = v_pname
        AND prop_cid = v_cid;
     IF (ret_val IS NULL) THEN
        SELECT prop_default INTO ret_val
          FROM config_properties
	 WHERE prop_name = v_pname
	   AND prop_cid = 0;
     END IF;

     RETURN ret_val;
   END|

FUNCTION get_prop (rid, cid, prop_name) RETURNS prop_value;

get_prop is used to look up the value of a property for a registrant at a convention. Default values and the global convention are also checked. If no property by this name exists for this or the default user for this or the global convention, the function returns NULL.

 CREATE FUNCTION get_prop (v_rid INT, v_cid INT, v_pname VARCHAR(100))
   RETURNS VARCHAR(100)
   BEGIN
     DECLARE ret_val VARCHAR(100);

     SELECT IFNULL(get_person_prop(v_rid, v_cid, v_pname),
                   get_default_prop(v_cid, v_pname)) 
              INTO ret_val;

     RETURN ret_val;
   END|

FUNCTION prop_is_default (rid, cid, prop_name) RETURNS boolean;

prop_is_default is used to determine whether the value for a property for a particular registrant at a convention is the same as the DEFAULT property (i.e., a distinct value for this person has not been set). If no property by this name exists for this or the default user at this or the global convention, the function returns NULL.

 CREATE FUNCTION prop_is_default (v_rid INT, v_cid INT, v_pname VARCHAR(100))
   RETURNS BOOLEAN
   BEGIN
     DECLARE ret_val BOOLEAN;

     SELECT IF( get_prop(v_rid, v_cid, v_pname) IS NULL, NULL, 
                IF( get_prop(v_rid, v_cid, v_pname) = 
                    get_default_prop(v_cid,v_pname), 1, 0 ) ) INTO ret_val;

     RETURN ret_val;
   END |

FUNCTION prop_is_global (rid, cid, prop_name) RETURNS boolean;

prop_is_global is used to determine whether the value for a property for a particular registrant at a convention is the same as the GLOBAL property (i.e., value is the same for all conventions). If no property by this name exists for this or the default user at this or the global convention, the function returns NULL.

 CREATE FUNCTION prop_is_global (v_rid INT, v_cid INT, v_pname VARCHAR(100))
   RETURNS BOOLEAN
   BEGIN
     DECLARE ret_val BOOLEAN;

     SELECT IF( get_prop(v_rid, v_cid, v_pname) IS NULL, NULL, 
                IF ( get_prop(v_rid, v_cid, v_pname) = 
                     get_prop(v_rid, 0, v_pname), 1, 0 ) ) INTO ret_val;

     RETURN ret_val;
   END |