Recent Changes - Search:

CONGO

PmWiki

edit SideBar

V2 /

SQLSolutions

V2.SQLSolutions History

Hide minor edits - Show changes to output

Added line 5:
-----
Added line 22:
-----
Added line 50:
-----
Added line 2:
Changed lines 2-3 from:
blk> This page details the SQL that will help answer those questions.  The functions that underlie these solutions can be found at the page SQLFunctions.
to:
blk> This page details the SQL that will help answer those questions.  The functions that underlie these solutions can be found at the page SQLPropertyFunctions.
Changed lines 1-104 from:
This page details various SQL functions that will assist with property handling.

-----
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 GLOBAL property (i.e., a distinct value 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 |

-----
to:
dbs> There are several 'questions' I need to have answered at various points.
blk> This page details the SQL that will help answer those questions.  The functions that underlie these solutions can be found at the page SQLFunctions.

1) What is the value for such and such a property for a registrant?  This would take into account global (0 cid) entries, as well as the 'default' value for a property (see config_properties)

ANSWER:
 SELECT get_prop(RID, CID, PNAME) AS prop_value;

This will call a function which returns the value (or default value) for any given registrant, conference, and property name.

EXAMPLE:

 mysql>  SELECT get_prop(6623, 16, 'Administrator') AS prop_value;
 +------------+
 | prop_value |
 +------------+
 | 1          |
 +------------+

2) Give me a list of properties for a registrant that differs from the system default for each property (including globals). 

ANSWER:
 SELECT prop_name, get_person_prop(RID, CID, prop_name) AS prop_value
  FROM
  ( SELECT distinct prop_name from reg_properties
      WHERE prop_rid = RID
        AND (prop_cid = CID or prop_cid = 0) ) AS prop_list
  WHERE prop_is_default (RID, CID, prop_name) = 0;

This will return a list of properties and their value for all properties that registrant has, for a specific convention, which are different from their defaults. 

EXAMPLE:
 mysql>  SELECT prop_name, get_person_prop(6623, 16, prop_name) AS prop_value
    ->    FROM
    ->    ( SELECT distinct prop_name from reg_properties
    ->      WHERE prop_rid = 6623
    ->        AND (prop_cid = 16 or prop_cid = 0) ) AS prop_list
    ->  WHERE prop_is_default (6623, 16, prop_name) = 0;
 +---------------+------------+
 | prop_name    | prop_value |
 +---------------+------------+
 | Administrator | 1          |
 | Staff        | 1          |
 | Referrer      | Other      |
 +---------------+------------+

3) Give me a list of all properties available for a registrant, and their current values (including defaults).  Denote in that view whether the current value being displayed comes from a global (all events) or default (system defined default) value.

ANSWER:
  SELECT prop_name, get_prop(RID, CID, prop_name) AS prop_value,
                  prop_is_default(RID,CID,prop_name) AS 'default?',
                  prop_is_global(RID,CID,prop_name) AS 'global?'
  FROM
  ( SELECT distinct prop_name from reg_properties
      WHERE prop_rid = RID
        AND (prop_cid = CID or prop_cid = 0) ) AS prop_list;

This will return a list of properties set for that registrant, their value, whether that value is the default value, and whether that value is a global value.

EXAMPLE:
 mysql>  SELECT prop_name, get_prop(6623, 16, prop_name) AS prop_value,
    ->                    prop_is_default(6623,16,prop_name) AS 'default?',
    ->                    prop_is_global(6623,16,prop_name) AS 'global?'
    ->    FROM
    ->    ( SELECT distinct prop_name from reg_properties
    ->      WHERE prop_rid = 6623
    ->        AND (prop_cid = 16 or prop_cid = 0) ) AS prop_list ;
 +---------------+------------+----------+---------+
 | prop_name    | prop_value | default? | global? |
 +---------------+------------+----------+---------+
 | Addr_Sharing  | 2          |        1 |      1 |
 | Administrator | 1          |        0 |      0 |
 | Staff        | 1          |        0 |      0 |
 | Referrer      | Other      |        0 |      0 |
 +---------------+------------+----------+---------+

(this last one will be used when an operator looks up a registrant and says "I want to edit their properties" - they need a list of all the properties available, and the UI needs to specify where the value came from, and allow the operator to either override the default with a local value, or set a global property for the registrant [or both?])

Changed lines 102-104 from:
   END |
to:
   END |

-----
Changed lines 73-74 from:
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 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.
to:
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 GLOBAL property (i.e., a distinct value 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.
Changed lines 87-102 from:
-----
to:
-----
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 |
Changed lines 73-74 from:
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 has not been set).  If no property by this name exists for this or the default user for this or the global convention, the function returns NULL.
to:
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 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.
Changed lines 80-82 from:
     SELECT IF( get_prop(v_rid, v_cid, v_pname) =
 
              get_default_prop(v_cid,v_pname), 1 0 ) INTO ret_val
to:
     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;
Added lines 86-87:

-----
Added line 3:
-----
Added lines 52-84:

-----
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 has not been set).  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 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) =
                get_default_prop(v_cid,v_pname), 1 0 ) INTO ret_val

    RETURN ret_val;
  END |
Added line 28:
-----
Added lines 1-49:
This page details various SQL functions that will assist with property handling.

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|
Edit - History - Print - Recent Changes - Search
Page last modified on November 15, 2008, at 11:35 AM