Database Schema

Database schema

The following is a list of all tables and fields. You can also click on a table (or field) in the image on the left.

admin
Experimenter (a.k.a. administrator) credentials. There is always one (and only one) row that exists, initialised to default values.
email
The experimenter e-mail address. This is used as a predefined template value for the instructions. It is also used as the destination for backups of the database. Initialises to foo@example.com.
hash
The experimenter password. Note: this is stored as cleartext, so it is not really a hash. Initialises to xyzzy.
id
Unique identifier.
isset
Bit-field on whether the email and hash have been set by the user. Contains 0x01 if the e-mail has been set, 0x02 for the password.
choice
A strategy mixture created when a player plays a round in a game.
created
When this was created (epoch).
gameid
The game.
id
Unique identifier.
playerid
The participant.
round
The round number (starting at zero).
sessid
The session.
strats
A text (space-separated) list of rational numbers of the strategy mixture ordered from the top if a row-playing role (i.e., player.role) or left if a column-player.
stratsz
Number of entries in choice.strats. This obviously equals the number of strategies available to the participant in that game, given the player role.
customquestion
Most of the time, gamelab users will want to use the pre-bundled questionnaire at the start of a game. However, sometimes they'll want their own questions. Provide a space for that here.
answer
The answer to the question. This is checked by a strict string comparison for equality.
id
Unique identifier.
question
The free-form question. This is in regular text.
rank
The rank of the question.
experiment
This describes an experiment configured by the experimenter (see admin). There is always one (and only one) row in this table.
autoadd
The experiment is currently accepting (or did accept) auto-added participants.
autoaddpreserve
Preserve the experiment.autoadd state during and after the experiment start. This is useful for rolling experiments so as not to have a break between starting the experiment and re-enabling captive mode.
awsaccesskey
If not an empty string, a Mechanical Turk access key. This and experiment.awssecretkey must be set in order to enable a Mechanical Turk experiment.
awsconvert
How to convert a Mechanical Turk participant's final score (tickets) into real currency, if applicable.
awsdesc
Description of this Mechanical Turk experiment, if applicable.
awserror
If not an empty string, set to an error from a preceeding Mechanical Turk “start new HIT” operation that failed.
awskeys
Keywords (comma-separated) of this Mechanical Turk experiment, if applicable.
awslocale
If non-empty and applicable, the locale to request (country code) for Mechanical Turk participants.
awsname
What to call this Mechanical Turk experiment, if applicable.
awsreward
The base reward to give to Mechanical Turk participants, if applicable.
awssandbox
Whether to use the Mechanical Turk sandbox in all enquiries, if applicable.
awssecretkey
If not an empty string, a Mechanical Turk secret key. This and experiment.awsaccesskey must be set in order to enable a Mechanical Turk experiment.
awswhitappr
If <=0 and applicable, the number of HITs for each Mechanical Turk participant.
awsworkers
How many Mechanical Turk workers to request, if applicable.
awswpctappr
If <=0 and applicable, the percent (out of 100) of approved HITs for each Mechanical Turk participant.
flags
Bit-field. Contains 0x01 if the history is not to be transmitted to participants, 0x02 if games and rows shouldn't be randomised prior to display, and 0x04 to show participants the relative count of rounds—relative to when they started to play.
history
A JSON file that provides a “fake” history prepended to the actual experiment history. This is only shown to users through the browser interface.
hitid
HIT (Mechanical Turk) identifier given by the Mechanical Turk server for an mturk experiment. Otherwise, an empty string.
id
Unique identifier.
instr
The instructions shown to participants. This must contain valid HTML5. It may contain @@gamelab-admin-email@@, which is filled in with the experimenter's configured e-mail address; @@gamelab-games@@ for the number of games; @@gamelab-rounds@@ for the number of rounds; and @@gamelab-round-time@@, a decimal number of the number of hours per round.
loginuri
Set when the experiment begins (i.e., when experiment.state <0), this is the URL given to players in their initial e-mail for when they log in. This is suffixed by “?ident=EMAIL&password=PASSWORD”.
lottery
Human-readable amount awarded in lottery, e.g., 10 000 SEK. If set to the empty string, there's no lottery.
minutes
The number of minutes per experiment.round. Minimum of one.
playermax
The maximum number of participants per player role at any given time. If zero, the number of participants is unbound.
prounds
The number of rounds playable by each participant. If zero, participants will play until the end of the experiment. If <0, participants will not be allowed to play more than the given number of rounds within the experiment.
questionnaire
If non-zero, new participants are not directly assigned a join round and must use the lobby facility. There, they will be asked questions and cannot proceed until all questions have been answered. See questionnaire.
round
The current round of the experiment, or -1 if the rounds have not begun incrementing. This will be set to experiment.rounds when the experiment concludes.
roundbegan
If round is non-negative, then the epoch when the round was incremented. Otherwise, this is zero.
roundmin
If roundpct is non-zero, this is the “grace time” (in minutes) before which the round will automatically advance giving the percentage.
roundpct
If <0, this represents the fraction of participants per player role who play all games and determine that the round automatically advances. In other words, if set to 0.5 (50%), then from both player roles, if 50% or more participants have played all games, the round advances in advance of the set round termination time.
roundpid
If specified when configuring the experiment, a daemon will be started that periodically checks to see if the round has advanced. Its process identifier is stored in this field. Otherwise, this is zero.
rounds
The number of rounds that will be played. Minimum of one.
start
Epoch when the experiment when the first round of the experiment begins.
state
The running state of the experiment, being either 0, for new (still in the configuration stage); 1, for started (participants can log in, though the experiment itself may not be accepting plays yet); 2, where the experiment has expired, but the winner has not been chosen by the experimenter; or 3, where the experiment has expired and the winner has been chosen.
total
When the experiment finishes (i.e., when experiment.state is set to 2), this is filled in with the total number of lottery tickets (the player.finalscore) awarded to all participants.
game
A game is a payoff bimatrix configured by the admin. There may be any non-zero number of games in a running experiment.
id
Unique identifier.
name
The name of the experiment. This is shown only to the admin.
p1
Number of strategies for row player.
p2
Number of strategies for column player.
payoffs
A list of space-separated payoffs from the top-left to the bottom-right of the payoff matrix, ordered row-player payoff, column-player payoff.
gameplay
During a given round, this records a player's status in terms of number of choice rows (plays) made.
choices
The number of games this participant has played, i.e., the count of choice rows.
id
Unique identifier.
playerid
Participant identifier.
round
The round number (starting at zero).
lottery
A lottery is created for an individual player when the corresponding participant has been granted payoff for all game rows in a round. This doesn't really have anything to do with the “lottery” concept of reward: the name is just an historic holdover.
aggrpayoff
The player's aggregate payoff (as a rational number) computing by adding the previous round's aggregate payoff to the current lottery.curpayoff.
aggrtickets
The value of lottery.aggrpayoff represented as a natural number of tickets.
curpayoff
The participant's current payoff (as a rational number) computing by accumulating her payoff.payoff for all games in the experiment. This is set to 0/1 if the participant has not played all games.
id
Unique identifier.
playerid
The participant owning the lottery.
round
The round number (starting at zero) for which this lottery was computed.
past
Records the average strategy mixture of a given player role for a given round and game when a round has concluded.
currentsp1
For the row player, the average strategy mixture of the current round. Strategy mixtures are only considered for choice rows where the player played all games for the round, so this will be a set of zeroes if no participant completed all games. This is recorded as a space-separated sequence of rational numbers, one per strategy.
currentsp2
Like past.currentsp1 but for the column player role.
gameid
Game being referenced.
id
Unique identifier.
plays
The total number of plays that were submitted (over all games) in this round if and only if the participant submitted for all games in that round.
round
The round starting at zero.
roundcount
The accumulated count of rounds not skipping, i.e., the count of zero-valued skip rounds.
skip
If zero row or column-player participants played all games, this is set to 1, else it is 0 (sufficient players played).
payoff
When the given round has completed, this consists of the payoff of the participant's choice strategy mix for a given game when played against the average strategy of the opposing player role.
gameid
Game identifier.
id
Unique identifier.
payoff
A rational number of the payoff.
playerid
Participant identifier.
round
The round number (starting at zero).
player
The central player table consists of all participants. We use the term “participant” instead of player in the literature to disambiguate a player role and an experiment subject. The player.hash variable is redacted when the experiment is wiped.
answer
If the questionnaire facility has been enabled (via experiment.questionnaire), this is non-zero if the questionnaire has been answered.
assignmentid
If the participant joined from Mechanical Turk, the assignment identifier. Otherwise set to the empty string.
autoadd
Player was auto-added (i.e., auto-added herself). These participants are never e-mailed.
email
Player e-mail address or identifier, in the event of a captive participant. This is unique in the set of all participants.
enabled
Whether a participant is allowed to login during an experiment.
finalrank
The minimum of the slot of the participant's tickets among all participants' tickets. For example, given 100 participants with roughly 10 tickets each, this might be 543 to indicate that slot 543 to 543 plus player.finalscore are this participant's slots in the lottery. Mechanical Turk players are not included in this computation, so this value is not meaningful for them.
finalscore
Set to the accumulated payoffs from lottery.aggrpayoff rounded up to the nearest integer. Mechanical Turk players are not included in this computation, so this value is not meaningful for them.
hash
The participant's password set when the experiment is started (i.e., when experiment.state is <0) or when reset, or when a captive participant registeres. Note: this is stored in the clear: it is not a hash!
hitid
If the participant joined from Mechanical Turk, the HIT identifier. Otherwise set to the empty string.
id
Unique identifier.
instr
Whether the participant should be shown instructions when she logs in (versus being taken directly to the game-play tab).
joined
The round when the participant joined (i.e., started playing) the experiment. This is set to zero when participants join at the outset. If -1, the participant exists, but has not yet started participating in the experiment.
mturkdone
Whether the Mechanical Turk participant, if applicable, has indicated that they've finished the experiment. Note that this is unprotected and can be set at any time: it is only used to display a message to report to the Mechanical Turk server.
role
The player role (0 for row player, 1 for column player) set when experiment.state is <0 or when the participant joins.
rseed
A cryptographically random number <0 given to the participant when created. This is used for many purposes, one of which being the bonus identifier for Mechanical Turk participants.
state
The state of a participant can be 0, meaning the participant is newly-added and has no password; 1 when the participant has been mailed her password; 2, the participant has logged in; and 3, an error occured when the password e-mail was attempted.
version
A number from zero that indicates the number of time the player columns have been updated. This is used to make the object cachable.
questionnaire
The questionnaire is an optional set of questions given to each participant. These participants must submit a set number of correct answers before joining the game. Only a participant's first answer counts.
first
The epoch time when the row is created.
id
Unique identifier.
playerid
Participant.
rank
The question number starting at zero.
tries
The number of tries, correct or not. This is zero only in the race after initially creating the row and noting an attempt. It should not be used and will be removed.
sess
A browser (or otherwise www) session. Sessions are the usual browser session used when participants (or the experimenter) are interacting with the system.
cookie
Magic random cookie for each session.
created
Epoch when session was created.
id
Unique identifier.
playerid
Participant identifier, if not NULL. Otherwise, this is the admin logged in.
useragent
Browser-reported user agent.
smtp
This consists of the SMTP server information used in sending e-mails. There is always only one row set, which defaults to empty values (see smtp.isset).
email
The e-mail address used as the “From” address in all communication from the server to participants in the experimenter. It is usually the same as the experimenter email set in admin, but may be set as a standard “No-Reply”. (This is discouraged, as your participants should be able to reply to you if things go wrong.)
id
Unique identifier.
isset
Whether these entries have been set.
pass
The SMTP username's password (used for logging in). Note that this is stored in cleartext, so make sure that your password isn't used elsewhere.
server
The SMTP server in smtp://server:port format.
user
The SMTP server username (used for logging in).
winner
The winner table consists of rows corresponding to a player and her winner status. This table does not exist until the experiment.state field is set to 3. This table is deleted when the database is wiped.
id
Unique identifier.
playerid
Participant.
rnum
The random number modulo the total number of tickts used for this winning draw. In other words, this is the winning lottery ticket.
winner
Boolean value as to whether the player is a winner. If this is false, then the winner.winrank and winner.rnum columns are undefined.
winrank
If the player is a winner, the rank (first, second, third draw...) of their winning.