Central participants database (User panel) Development

This page contains information for developers. The documentation for common Limesurvey users can be found at Central participants database.

Database Schema

Central Storage

lime_participants

The first table is the actual central table where all the participants’ entries are stored. The table is as follows

participant_id	first_name	last_name	e_mail	blacklisted_global
4b3403665fea6	Carsten	Schmitz	carsten.schmitz@example.com	N
4b1357665fea6	Jason	Cleeland	js@example.org	N
4dc14fa0db7f4	Thilabult	Lemer	thilabault.lemer@example.net	N

language	owner_uid	date_created	date_modified	max_surveys
en	1	2007-11-04 01:00:01	2007-11-04 01:00:01	0
en	2	2007-11-04 01:00:01	2007-11-04 01:00:01	2
en	1	2007-11-04 01:00:01	2007-11-04 01:00:01	1

The fields in the table are explained below:

• participant_id : It is the primary key for the table and will be auto-generated.
• first_name : It contains the first name of the participant.
• last_name : It contains the last name of the participant.
• e_mail : It contains the email id of the participant.
• language : It contains the language preference of the participant.
• blacklisted_global : It is a field that will be updated if a participant hits a particular URL mentioned in the email sent to him. There will be two URL mentioned in the mail. One will allow the participant to blacklist himself globally from any survey from that server; the other, from a particular survey. When the user blacklists himself from the server as a whole, he will be marked against the e-mail address in the above table; when it's survey specific, he will marked with the help of email and the survey_id in the tokens table. If for a particular participant, this field is set, the administrator will not be allowed to add that user to any of the surveys. If a new survey is set, then the administrator will also not be allowed to add that particular participant. If the global setting for blacklisting is updated, then all the local tables will be updated accordingly. Once blocked, the participant will not be allowed to answer any survey. The participant will have another set of URL's in case he/she wishes to unblock himself/herself.
• owner_uid: It will contain the user id of the person who is adding the participant, this will be extracted based on who is logged in. In case the owner field is set to be editable, then it can be changed.
• date_created and date_modified are self-explanatory fields.
• max_surveys:The max_surveys field will contain a number that indicates the maximum number of surveys to which a participant can be locked. For example: if it contains 0, the participant can be added to any other survey: However, if it contains the value 2, the participant can only be locked to two surveys: so after adding him to two surveys, he can not be added to any third survey.

The combination of email and owner_uid will be the composite key for this table.

---

Locking Feature

In the lock feature there is one tables,lime_survey_info.The lime_participant_info table will contain the information regarding to which survey the participant is added.

lime_participant_links

The schema of the table is

participant_id	survey_id	token_id	date_created
4b3403665fea6	63273	1	2011-03-08 12:32
4b1357665fea6	76581	1	2011-03-08 12:32

This table will be used for two purposes as far as I can see for now,

1) It will give the clear cut info as to which survey a particular participant is added to.

2) When a user will try to share a participant, but in the settings it is set that he can't be added to more than two surveys, then a SQL COUNT query will be run for that participant and if that number is equal to the max_locks field an error message will sprung up saying, these participants can't be added to the survey.

---

Token Table

lime_tokens_xxxxxx

participant_id	token	token_id	e_mail_status	sent	reminder_sent
4b3403665fea6	afir2ski6wrcyny	1	OK	2011-03-08 12:32	N

reminder_count	completed	uses_left	valid_from	valid_until	blacklisted	att_id
0	2011-03-08 12:32	0	23.03.2011 00:00	26.03.2011 00:00	N	2

Some minor changes have been done to this table to make it work with the other Central Database System.One of then is that the user information will not be stored in this table, but in the Central Table and will be linked using the participant_id.Also, a blacklisted field is also added that will contain the survey and participant specific blacklist settings.

The Fields in table are explained below:

• participant_id : This is the key for linking participants in this system.
• token,token_id,e_mail_status,sent,reminder_sent,reminder_count,completed,uses_left,valid_from,valid_until: Same as in the previous system.
• blacklisted: This is the survey specific blacklist field which will contain as to whether the participant has chosen to blacklist himself from this survey or not.
• att_id : This field will contain a id of the attribute that is added into the system. A combination of att_id and participant_id will extract information from lime_participant_attributes table

---

Attribute Control

This feature will be using three tables namely :

1) lime_participant_attributes - Store the actual attribute information

2) lime_participant_attribute_info - Store the UI information related to the attribute

3) lime_participant_attribute_values - Store the possible values of the attribute

lime_participant_attributes

This is the table that will hold the actual values

participant_id	attribute_id	value	id
4b3403665fea6	1	Male	1
4b1357665fea6	1	Male	2

The fields of the table are explained below

• participant_id : Again this is the unique id for the participants.
• attribute_id : Each attribute in the attribute control will have a unique id similar to participant_id and a combination of participant_id and attribute_id will tell give the value of the attribute.
• value : This field contain the actual value of the attribute, in case of textbox, It will contain the value contained in the text box.

lime_participant_attribute_info

This is the table that will hold the UI related information for attribute control

attrbute_id	attribute_name	attribute_type	visible
1	sex	DD	1

• attribute_id : The unique id give to each additional attribute.
• attribute_name : This is the label that will be shown in the UI created for additional attribute.
• attribute_type : This field contains the type of UI that needs to be created for this particular attribute
• visible : This field will tell whether that attribute is to be used for the current set of participants or not

lime_participant_attribute_values

This is the table that will hold all the possible values for the particular attribute. The scehma of this table is

attribute_id	value_id	p_value
1	1	male
1	2	male
2	1

The above example will make it more clear, but keep in mind that in case of a text box and date-picker, this field will be empty because any value can be entered in the system. For multi-line text input we will use a different value_id for each text box, for example address.

---

Relationship

The relationship between the tables explained above is shown below.

}

---

User Interface

In the user interface aspect, the entire participant control that we used to have` in the token menu will now be available globally, but the token will be generated in the survey specific table only. The link for adding participants to the limesurvey installation will be on the front screen.

When clicked on the Add Participants the following screen will come up

---

Add Participant

Explanation of the above screenshot is given below:

• ID : This is the autogenerated ID for each participant.
• First Name, Last Name, Email, Language : These fields are self-explanatory.
• Blacklisted: This is the local blacklisted field I explained earlier.
• Locked_with_survey : This is the field that will not allow the participant to be added to any other survey. The "add" link will allow the user to add more than one survey to be locked to. It will open a modal-dialog box. The unlocking will be possible only at the survey level, i.e. the participant could only be unlocked if the user has the access to that particular survey to which the participant is locked.
• Owner: This will be a fixed field by default that will contain the owner’s id of the owner who is adding that particular participant to the central database. However this field will only be editable if set in the user control options
• Max Surveys: This field will tell set the number of surveys a particular participant can be added to, for example is the user set it to 0, then the participant can be added to as many as surveys the user wants.If it it set 2 it can be added to the maximum two surveys.
• Sex: This is the additional attribute we set under attribute_control.

---

Import from CSV/LDAP

The CSV file upload feature is similar to what we used to do under tokens, however with some differences, the differences are mentioned below:

• The language is no longer an optional field, but under the mandatory fields.
• Owner is the fields have the id of the user that is currently logged in and the participants will be added under this field. This field will only be editable if the corresponding option is set in user control.
• Optional attributes are the survey specific fields, and will be added to the survey specified in the above field, along with the participants, this option will be enabled only if the participants are added to the survey table automatically based on the id they are adding.
• locked_with_survey field will have another option, besides the survey. It will be "free" that will allow the participant to be added to any survey. This will be the default value.
• Max Surveys: This field will tell set the number of surveys a particular participant can be added to, for example is the user set it to 0, then the participant can be added to as many as surveys the user wants.If it it set 2 it can be added to the maximum two surveys.
• The LDAP import will work in the same way, and with the same options, I mean the locked to survey, group_id and optional fields.

---

Display Participants

Again this screen is only for editing/deleting the participant entries. It is global, and hence nothing survey specific will be found here. If the user clicks on the edit button, then the screen similar to add participant screen will show up, showing the details of the participant, which can be edited there.

---

Blacklist Control

All the settings under this page will be stored under the settings_global table. Each of setting is

explained below

• The first option if set, will mark participant blacklist for all the survey's that are present in the system currently.
• The second option will mark the participant as blacklist for any new survey that is added to the system, if the global field is set.
• The third option will not allow the participant to be even added to the survey if the global blacklist field is set.
• The fourth option will not show the blacklisted participant in the display participant.
• In the fifth option, if the participant blacklist himself/herself globally , he will be deleted from the database .
• The sixth option will decide that whether the URL to blacklist the user will be sent in the invitation.

---

User Control

This is the user control, by user I mean the administrator. The three options under this field will be stored in the global table. The options are explained below.

• The first field if enabled or disable this feature completely.
• The second option if set to yes, when the user uploads a csv file or adding a new participant indivisually. *The new participant will be added to the survey of which the user is the owner.
• The third option will set if the owner field is editable or not.

---

Attribute Control

This is a new feature I am planning to implement, in this feature I will be allowing the administrator to add a new attribute with a proper UI, rather than just a textbox. The value related to this feature will be stored in attribute_info_table. These are the UI components that I am planning to add for additional attribute

• Drop Down
• Check Box
• Radio
• jQuery DatePicker
• Text Box

In case of Drop Down,Check Box, Radio the "Add More" will allow the administrator to add more possible values. More advanced UI's can be added once the basic feature is ready.

---

Add to survey

The UI is explained below:

• The add group is basically the user id whose participants are to be added to the survey after going through three checks, locked or not, blacklisted or not,has permission or not.
• Lock this group to the survey will lock this particular group to the survey, and will not allow any other
• This field will give all the global attributes that are present with the participants and only relevant attributes are added to the survey
• Allow blacklisted enteries to be added to the survey, if set, otherwise will not allow the blacklist enteries to be added. The main reason for this option is that in case the participant un-blacklist himself from the server, then he/she can participate in that survey. A automatic module can be made that will check for any participant that unblocked himself/herself and can be send invitation after that. I know this feature is not in the top of the list, but with many participants, will be worth it.

---

Sign Up Control

This feature will allow the participant to just go on to the home screen, and sign him up. He will be verified against his e-mail. On the front screen there will be a sign up button, that when clicked will open a pop-up asking for the e-mail id. When entered an invitation mail will be sent to him asking for the rest of details. This way we will be sure that we have a valid participant. Once those details are entered, automatically a token will be generated for that user, and he will be redirected to the survey. Alternatively, if the administrator wants, he/she can disable this feature completey, or allow the participating to only participate only when he/she sends the invitation.

The mock - up of the sign up control is as shown :

This feature again will have a survey specific settings, which will be stored in the survey's table. The local settings if set ,will override the global settings.

---

CodeIgniter Specific Details

Since we are porting LimeSurvey to codeIgniter and any new projects have to be developed according to the CodeIgniter framework. I will try and give the basic outline of the approach I will be following.

Controller

I as of now, will be adding a single controller for the whole project, and it will be named 'centralDB'. The functions under this controller will be of the form

• addParticipant //Add Participant
• import //Import CSV/LDAP
• displayparticipant //Display the jqgrid
• blcontrol //Blacklist Control
• usercontrol //User Control
• signupcontrol //signupcontrol

All the controller's listed above will be used for only showing of the view, retrieving previous settings and storing new settings, and for import and displayparticipant functions, it will have major functionality as display participant will also be used for adding the participants to the database, and import will also be providing functionality and not just storing data.

For Storing data we have functions such as

• storeParticipant
• storeBL
• storeUser
• storeSignup

These methods will get the post variables from the view and storing it in the database using appropriate model. For displayParticipant and all, there will other functions such as addtosurvey.

Models

For the models we have

• participants_model
• attribute_model
• token_model(older token table)
• survey_links_model

I think it's best to compress the three attributes table to one model. Rest of the models are pretty much simple, get and store records.This is the basic structure, what we need to concentrate on is what to do when we are joining tables and getting data from multiple tables.

View

For the view, I have decided to use the following views. These are

• headercpdb
• addparticipant_view
• displayparticipant_view
• importparticipant_view
• blcontrol_view
• usercontrol_view
• signupcontrol_view

---

Scenario Testing

The main purpose of making this proposal on the wiki page is to get the review of the community and suggest us some scenario's where you can see the system failing, so that we can make the changes before it's too late. If you see any shortcomings or any other feedback, please leave a comment and please try and answer these questions

1) What do you want to do?

2) Where will the system fail?

3) Feature Suggestion?

---

Action Plan :)

I received my date-sheet and I have exams from 24th of May to 11th of June, So I will be tweaking the action plan a bit in order to get things right :).

Plan for June

Date	What to do ?
4-7th	Talk to Shubham and Diogo during this period, as I think by that time they will be officially clear as to how they will be doing the porting, also, I will decide as to how I should go about it.If everything goes as planned I should be able to complete the add participant UI and the addition of data to the central database(without any features) along with the required validations.
12-15th	Will add the user_id info(based on the logged in user), along with the datestamp in the central database. Also will be creating the owner control as mentioned in the original proposal along with the required functionality with it. Like automatically adding participants to the token table or not, or editable owner's field or not .
16-22th June	Addition and attribute control
23-28th June	CSV and LDAP upload with attribute support
29-10th July	Add to token table and vice versa with editing logic and max surveys feature
11-15th July	Sending email with blacklist control and the required logic
(12 July - 16 July)	(Midterm Break)
16-31th July	sign up addition for participants along with the multiligual attribute support for token system
1-15th August	features from if we have time list
16-22th August	cleaning up code, video tutorials, testing and bug fixing
23-31th August	worst case scenario or else party time ;)

This is the action plan for the first milestone according to my original proposal, I will create the further action plan once I am done with this target. I will also be updating a weekly progress report here as well as a mail to the administration team of LimeSurvey, about the discussions we have during the week. The actual coding and progress report will start from 12th of June.

---

Weekly Meetings

This will contain the logs of the weekly meetings we have:

12th May 2011 -> http://docs.limesurvey.org/CPDB+Meeting+Log+12-05-2011

Logs of meeting from the 19th of May will be available at the link below

http://docs.limesurvey.org/CPDB+Meeting+Logs

---

Scenarios

Use the following section to describe (as succinctly and clearly as possible) various different scenarios in which the Central Participants Database may be used. These scenarios can assist in clarifying User Interface issues and in initial database design

Privacy Scenarios

Single Company Installation

In this scenario, LimeSurvey is installed by a single company for use by that company's staff. The right to view and access data relating to participants is shared by all staff and there is no need to fence data off between users.

Multi User Installation

LimeSurvey is installed at a University and different students or academics are using it to run surveys. Their ethics rules require that they keep their participant data to themselves, so other users cannot see it.

Survey Invitation Scenarios

Pre-existing List, Add it to Participants Database

A user has a list of 500 people in their personal spreadsheet/database that they want to invite to participate in their survey. They create a survey, create a tokens table for it and then import the list (as per existing system). They want to make sure that their list is on the participants database for future use, and that they will be able to select these 500 people easily later on for future surveys.

Pre-existing List, Don't add it to the participants database

A user has a list of 300 people in their personal spreadsheet/database that they want to invite to participate in a survey. They create a survey, create a tokens table table for it and then import the list (as per exising system). They don't want their list to be added to the central participants database.

Generate attributes for the participants database from answers to a survey

A user has run a survey which asked whether participants live in a capitol city, a major regional centre or a minor regional centre. He wants to transfer the answers from that survey to the participants attributes in the central participants table. He also wants to transfer the annual income results from a question. He already has this information for some of the participants, but wants to update it with the information collected in this survey.

Generate tokens from participants list based on earlier survey

A user creates a new survey and wants to invite all the participants from an earlier survey to this new survey. He needs to be able to select all those who participated in the earlier survey, preferably just those who actually completed it. He might want to add those who didn't complete the earlier survey later on.

Generate tokens from participants list based on their survey history

A user creates a new survey and wants to invite only participants that have not taken part in a survey for the last 2 months. He might also want to exclude all that have participated in a survey with a similar topic in the last 6 months. User will need to view a search form where he can:

1) Select search criteria from values such as "survey name", "survey completed date"

2) Fill out matching values (ie: text string for survey name, dates for completion dates)

3) Make the system search through all participants for matches to the search criteria

4) See a list of all the matching participants (and the relevant information)

5) Be able to do something with the resulting list (most likely copy them to a token table)

Generate tokens from participants list based on other attribute

A user creates a new survey and wants to invite all the participants he owns who have an income greater than $50,000 per annum and live in a capital city. User will need to be able to fill out a search form where he is able to:

1) Select the attribute or attributes to use in his search

2) Enter a search value/criteria for the search

3) Make the system search through all the participants he has rights to view, for matches to his search criteria

4) See a list of all the matching participants (and probably the relevant attribute data)

5) Do stuff with the list (most likely, copy those participants to a token table for a survey)

Taking care of the panel health

The manager of the central participant list wants to check on the panel health. He wants to know if all participants have been invited to surveys in a certain period of time, how often they have been invited, the ration between invitation and participation, etc. Ideally there would be a system to attribute "points" to each survey and those points would be added to each participant (something like the total km count for a car). Ideally there would also be another field with points that can be reset (e.g. like the day km in a car). This would help to remunerate participants for their participations, without having to "pay" them for every single survey.

After running the survey for 5 days he realises he needs more, so he then wants to add people who live in major regional centres.