PmWiki | V2 / PropertyLookup

November 26, 2008, at 02:31 PM by dbs -

Deleted line 1:

~~!!![[Definition of Problem]]~~

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

Deleted lines 45-121:

There are several 'questions' I need to have answered at various points...

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:32 AM by blk -

Changed lines 4-5 from:

to:

!!![[SQL Property Functions]]

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

Added lines 49-62:

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 |
+------------+

Added lines 65-89:

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 |
+---------------+------------+

Added lines 92-119:

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 |
+---------------+------------+----------+---------+

November 15, 2008, at 09:40 AM by dbs -

Changed lines 1-4 from:

to:

!!Table of Contents
!!![[Definition of Problem]]
!!![[SQL Solutions]]

Changed lines 63-65 from:

Most of the other properties that have been used in the past (like 'thursday night stayover' or 'dinner saturday' will be taken care in the event editor - that's pending).

to:

Most of the other properties that have been used in the past (like 'thursday night stayover' or 'dinner saturday' will be taken care in the event editor - that's pending).

!!SQL Commands for Queries

November 10, 2008, at 01:59 PM by dbs -

Added lines 1-60:

The issue is with how Properties are handled. A property is essentially a 'flag' or extraneous data tacked onto a registrant. It can be "this person is staff for this event" to "this person is dead" (obviously not specific to the current event).

The administrator sets up Properties via the properties editor - properties are defined in the config_properties table (the Data class is PropertyConfiguration), and looks like this:

CREATE TABLE config_properties (
prop_cid int(8) NOT NULL default '0',
prop_name varchar(20) default NULL,
prop_default varchar(100) default NULL,
prop_type enum('boolean','string','float','date','numeric') default NULL,
prop_regprompt tinyint(1) default NULL,
prop_cost float(8,2) default NULL,
KEY type (prop_cid),
KEY name (prop_name),
CONSTRAINT `config_properties_ibfk_1` FOREIGN KEY (`prop_cid`) REFERENCES `con_detail` (`con_cid`) ON DELETE CASCADE,
CONSTRAINT `config_properties_ibfk_2` FOREIGN KEY (`prop_cid`) REFERENCES `con_detail` (`con_cid`) ON DELETE CASCADE
) TYPE=InnoDB;

A registrant gets a property assigned to them by making an entry in the reg_properties table, which looks like this:

CREATE TABLE reg_properties (
prop_rid int(8) NOT NULL default '0',
prop_cid int(8) default NULL,
prop_name varchar(20) default NULL,
prop_value varchar(100) default NULL,
KEY rid (prop_rid),
KEY cid (prop_cid),
CONSTRAINT `0_64` FOREIGN KEY (`prop_cid`) REFERENCES `con_detail` (`con_cid`) ON DELETE CASCADE,
CONSTRAINT `0_62` FOREIGN KEY (`prop_rid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE
) TYPE=InnoDB;

(hmm, we'll need a foreign key there to the config_properties table to make sure a prop_name doesn't get set in reg_properties that's not in config_properties)

Anyway - if I want to mark someone as staff for the convention '55', and their registrant ID is '2001', i would:

1) make sure we had an entry in config_properties for 'staff'
2) insert into reg_properties values (2001,55,'staff','1'); (for boolean types, the literal '1' is used for true).

One caveat - if a property is global for a registrant, the 'cid' is set to '0' (means applies to all events -, ala, 'dead')

There are several 'questions' I need to have answered at various points...

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)

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

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.

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

Typical properties used:
Staff
Needs Early Checkin
Dealer
Banned
Dead
Security

Most of the other properties that have been used in the past (like 'thursday night stayover' or 'dinner saturday' will be taken care in the event editor - that's pending).

PropertyLookup

V2.PropertyLookup History