• View
• Edit
• History
• Print

PropertyLookup

Table of Contents

SQL Solutions

SQL Property Functions

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')

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).

SQL Commands for Queries