home

SQuery documentation

Introduction

SQuery is a web application for creating and completing questionnaires, and collate the resulting data in Stata. SQuery uses SWire for communicating with Stata. SQuery has been developed for running on a local network.

SQuery and Stata

This is what SQuery can do:

  • it can create questionnaires and publish them on a local network
  • it can collect responses from mobile devices
  • users can complete the questionnaire from their web browser
  • data are directly stored in Stata.

Requirements

SQuery requires that SWire has been installed on Stata and that the SWire server is listening on port 50000; SWire requires Stata >= 13.

SQuery also requires:

Installation

SQuery is a web application which has been developed by using the Symfony PHP framework (version 3.1); thus all the requirements for a normal Symfony application are also required for SQuery. These requirements are described at http://symfony.com/doc/current/reference/requirements.html. Careful scrutiny of the requirements is suggested, ensuring that the php.ini file (to which the requirements refer) is that used by your web server (not your PHP CLI).

In order to install SQuery, Composer is required; the installation instructions for Composer are available here: https://getcomposer.org/download/

SQuery requires a web server and a database: the former is needed to distribute the SQuery web pages throughout local the network and the latter is necessary for storing all the data managed by SQuery (users, groups, questionnaires and settings). The suggested web server is Apache HTTP Server, while the suggested database is MySQL. Other databases supported by Symfony 3.1 can be used but only MySQL has been tested. The web server and the database must be running prior to installing SQuery.

The first step for installing SQuery is to download the squery_0.1_beta.zip file. Uncompress this file and enter the resulting squery_0.1_beta folder. From this folder, launch Composer to install all the PHP libraries required by SQuery (an internet connection is required):

:::bash
composer install --optimize-autoloader

During the execution of the previous command, Composer will request the inputting of various settings. In this phase, it is very important to correctly set: the database host, the database port, the database name, the database username and the database password. A possible database name could be squery_0.1. E-mail configuration is not required, thus the default values can be left unchanged. Finally, type a long random string to as the secret token. When Composer has terminated, check for possible reported errors and correct them prior to continuing the installation. All these settings can be changed later by editing the parameters.yml file in the squery_0.1_beta folder (parameters.yml is written by using the YAML syntax).

If everything is ok with Composer, you can tell Symfony to create the database, which is required by SQuery: type the following Symfony console command:

:::bash
php bin/console doctrine:database:create

After the database has been created, the required database tables can be created by using the following command:

:::bash
bin/console doctrine:schema:update --force

At this point no user exists in SQuery, thus it will be not possible to log into SQuery and perform administrative tasks. An administrator, therefore, needs to be created. Fortunately, Squery offers such a command for creating a new user from the console. Type the following to create an administrator user:

:::bash
php bin/console squery:create-user

The previous command will start an interactive session in which you will be asked the user type you wish to create: select "admin".

The final Symfony console command to be launched serves to initialize SQuery (several database tables will be populated in this phase):

:::bash
php bin/console squery:init

Check that the following folders and their sub-folders can be written in by your web server (note: in Ubuntu the web server user is www-data):

  • var/cache
  • var/logs
  • var/sessions

For more information about how to set up the correct file permissions for the previous folders in a Symfony application, visit http://symfony.com/doc/current/setup/file_permissions.html.

The folder to be served by your web server is web. Thus, if SQuery had been installed in /home/john/squeRy_0.1_beta, then the web folder would be /home/john/squery_0.1_beta/web. Inform the web server that this is the folder to serve. For Apache you can use the squery.conf file, which is in the squery_0.1_beta folder, as your active Apache configuration file. Edit this file by inserting the right folder names and then copy the file to the folder where Apache "expects" to find it (in Ubuntu this folder is /etc/apache2/sites-enabled). A possible configuration file could be:

:::apache
<VirtualHost *:80>
    DocumentRoot "/home/john/squey_0.1_beta/web"
    <Directory "/home/john/squey_0.1_beta/web">
        AllowOverride All
        Require all granted
        Allow from All
    </Directory>

    ErrorLog /var/log/apache2/squery_error.log
    CustomLog /var/log/apache2/squery_access.log combined
</VirtualHost>

After the Apache configuration file has been edited and copied, restart Apache. Type the following in the Ubuntu terminal:

:::bash
sudo service apache2 restart

If the web server is running correctly, you can open the http://127.0.0.1/config.php page. This page will analyze the system to check whether is ready to run Symfony applications. Check now that all requirements have been met.

Finally, start using SQuery at this address: http://127.0.0.1/app.php. Note that the app.php file is the entry-point of the entire SQuery application. If you wish to enable your users to access SQuery by only typing the host address from their web browser (for example, http://192.168.0.101 instead of http://192.168.0.101/app.php), the mod_rewrite module must be enabled in Apache2. To do this, type the following in the Ubuntu terminal:

:::bash
a2enmod rewrite

and then restart Apache.

Troubleshooting

If SQuery does not function or if it reports an error, help about the issue can be obtained by reading the log file in the log folder.

The most probable issues are:

  • incorrect database credentials
  • one of the following folders cannot be written in by the web server: cache, logs, sessions
  • an administrator was not created
  • the PHP engine used by your web server is not >= 5.4
  • the web server is not correctly configured
  • SWire is not running

How to use

SQuery has a front-end and a back-end: the front-end is for users who sign in to SQuery and complete the questionnaires; the back-end has administrative functions. Only users with an admin role can access the administration back-end. From here, the admin manages settings, users and groups of users. Furthermore, questionnaires can be created and the Stata dataset can be prepared for receiving the data from the users, who have completed the current questionnaire.

The SQuery homepage is for the "ordinary" users; they can access this page to know if they can sign in to SQuery or if they are enabled to complete the questionnaire.

SQuery homepage

From the aforementioned page is also possible to log in to SQuery as do Admin users.

The admin homepage can be reached from http://127.0.0.1/app.php/admin.

Admin homepage

From this page, the admin can set:

  • if users can sign in to SQuery
  • the group to which the users will be automatically assigned when they sign in
  • the active questionnaire, namely the questionnaire which logged-in users can complete
  • the active group, namely the group which is enabled to complete the questionnaire.

Before allowing the users to fill in the active questionnaire, the corresponding Stata dataset must be created. This dataset will have the following set of variables:

  • username: the username
  • password: the encrypted password
  • a set of variables q_1, q_2, ..., q_n, corresponding to the n items of the questionnaire.

This dataset can be created from the Stata control page (http://127.0.0.1/app.php/stata):

Stata control page

While using this page, SWire must be listening on port 50000. An example resulting dataset is the following:

Stata dataset before users fill in their questionnaires

SWire will use the username and password data to detect the correct dataset row to update with data from the user.

Also see

SWire
A software interface, enabling us to query Stata for the executing of basic operations, such as reading or writing data

2016 Italian Stata Users Group Meeting, Rome 17-18 November
Abstract and slides presented at the Italian Stata Users Group Meeting 2016

SWire Web Apps Collection
A collection of web apps, interacting with Stata through SWire

SWire4js
A JavaScript library for developing SWire web applications

SWire4R
An R package enabling R to interact with Stata for exchanging data

Swire4QGIS
A QGIS plugin enabling data exchange with Stata