PmWiki | V2 / SQLSolutions

November 15, 2008, at 11:35 AM by blk -

Added line 5:

-----

Added line 22:

-----

Added line 50:

-----

November 15, 2008, at 11:35 AM by blk -

Added line 2:

November 15, 2008, at 11:34 AM by blk -

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.

November 15, 2008, at 11:34 AM by blk -

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?])

November 15, 2008, at 11:33 AM by blk -

November 15, 2008, at 11:20 AM by blk -

Changed lines 102-104 from:

END |

to:

END |

-----

November 15, 2008, at 11:19 AM by blk -

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 |

November 15, 2008, at 11:17 AM by blk -

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:

-----

November 15, 2008, at 10:12 AM by blk -

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 |

November 15, 2008, at 10:01 AM by blk -

Added line 28:

-----

November 15, 2008, at 10:01 AM by blk -

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|

SQLSolutions

V2.SQLSolutions History