Using a Database

1. Planning
2. Questionnaire data
3. Installing for the first time with a database
4. Migrating to a database after running without one
5. Disabling table creation
6. Manual table creation
7. Cluster deployment
8. Moving from development to production
9. Database Schema
10. Internal data
11. Database Verification
12. Database Manipulation

This section includes additional servlet parameters to be used during Installation.

See Database specific information for additional information about using Oracle, MSSQL, MySQL and DB2 in various database versions.
Please read the appropriate section.

See also the database and database error recovery fine-tuning parameters in the Servlet Parameters section.

Planning
ViewsFlash can be deployed with or without a database. When used without one, some features are not available, such as inivitations and staging. Cluster deployment requires using a database.

To connect to the database, ViewsFlash uses a JDBC 2.0 compliant JNDI data source, managed by the application server.
The data source should use Connection Pooling and auto-commit. It does not need to use XA Transactions.
The database user associated with the data source must have insert, update, and delete record rights. It is usually granted the right to create, alter, and drop tables as well; if this is not possible, see Disabling table creation.
ViewsFlash can run on its own database schema or on a shared schema. Allocate 10 Gigabytes to the ViewsFlash application, and provide generous auto-increments for expanding beyond that size. To use a schema other than the one normally associated with a data source, use the servlet parameter dbtablenameprefix.

The database is used to store internal data and data gathered from questionnaires.

Saving questionnaire data
While a questionnaire form is being completed, ViewsFlash writes the information to a temporary internal record. When the questionnaire is completed by clicking on its Submit button, the information is recorded permanently, before the response is shown to the respondent. If the database is temporarily out of service, storing will be retried up to three times; if the record cannot be written, the respondent is notified, the error is written to the ViewsFlash log, and the administrator is notified.

Data can be viewed from Analysis / Data page. Data can also be accessed directly off the database, with standard SQL statements, tools, and applications.

Each questionnaire specifies, on its Save page, how to store response data. These options include saving the questionnaire's data in its own table, in a common table, and as a .txt file. These three options can be turned on or off globally for all questionnaires by using the Save Data servlet parameters save.table, save.text, and save.normalized.

When data is saved in a questionnaire's own table, the table is created and updated dynamically when the Publish page is submitted, using CREATE and ALTER statements as necessary. This requires that the database user associated with the data source have the right to create, modify and drop tables. If this is not possible, see Disabling table creation.

In clustered configurations, data should not be saved on the file system, so disable this option with the save.text=false servlet parameter.

In addition to storing questionnaire data, ViewsFlash stores the live tallies of fields marked for tallying as they happen, in real time. This allows for interesting real-time applications driven by live results, which are stored in the VWFTALLIES table.

Installing for the first time with a database
If you are installing ViewsFlash for the first time and you want to use a database right away, read this section and provide the appropriate servlet parameters as described in the Installation section before deploying the ViewsFlash.war file. 

If the database user associated with the data source has been granted table creation rights, all the necessary tables will be created by the ViewsFlash application. If this right has not been granted, the ViewsFlash tables must be created before deploying the application, using the .sql files provided in the appropriate WEB-INF/sql directory.

The ViewsFlash tables will be populated automatically the first time the ViewsFlash application starts.

After the application has started, examine the viewsflash.log file in the ViewsFlash data directory.

If ViewsFlash has already been deployed without a database, use the following procedure to migrate the internal ViewsFlash data to a database.

Migrating to a database
After ViewsFlash is up and running without a database, follow these steps to connect it to a database and migrate the internal data from the file system to the database.

1 Read this entire section and note all the appropriate servlet parameters needed, including database= and datasource=.
2 Create the JNDI data source in the application server and test the connection.
3 If the JNDI data source does not have create table rights, create the tables manually using the .sql files in the appropriate WEB-INF/sql directory.
4 Stop and restart the ViewsFlash application in the application server. All the data in the file system will be migrated automatically to the database.
5 Check the viewsflash log and the application server logs for any errors.

Disabling table creation

It may not be possible to grant table creation rights to the ViewsFlash data source. In this case, use the save.table=false servlet parameter to disable table creation.
The ViewsFlash tables must be created manually before deployment by a database administrator, as described next.

Manual table creation
To create the ViewsFlash tables manually, a database administrator should use the appropriate utility to run DDL scripts on the database. The appropriate scripts are found in the expanded ViewsFlash.war file in the WEB-INF/sql directory. There are two scripts: Create.sql and CreateNormalized.sql. The Create script creates the tables described in Internal ViewsFlash data below and the CreateNormalized script creates the VWFDATA Normalized database table. Some databases, like Oracle, include more than one version of the scripts; use the one that matches the database= parameter that is used.

Cluster Deployment

Deploying ViewsFlash in a cluster requires running ViewsFlash on a database. Deployment and configuration is the same as standard deployment except for the following:

1 Instead of putting servlet initialization parameters in a /etc/cogix/viewsflash.properties file, it may be easier to put them in ViewsFlash's WEB-INF/web.xml file and repack the war file. When deploying to a Weblogic cluster, it is necessary to use a database and to deploy from the war file, rather than an exploded directory.

2 If the ViewsFlash data directory, specified with the data= servlet parameter, can be on a shared file system, put it there. A single viewsflash.log file will be written to that directory by all ViewsFlash instances in all servers in the cluster.
If a shared directory is not available, then specify a local directory in data=. The directory can be shared by multiple instances in the same server. However, each server will have its own viewsflash.log file, which is not desirable, so write the viewsflash log to the database, and use the ViewsFlash application UI to read the log. Use the dbstores= servlet parameter to write the log to the database:
dbstores=data|polls|results|styles|votes|logs
or the simplper form
dbstores=all

3 Create the ViewsFlash tables manually before deployment using the .sql files in the appropriate WEB-INF\sql directory.

4 After deployment is complete, if staging will be used, perform the standard Staging procedures.

Moving data from a development system to a production system

To deploy ViewsFlash on a production system without any data, just follow the instructions in this section. If questionnaires have been created in the development system, use one of the procedures described below to migrate the data to the production system.

1 Deploy ViewsFlash on the production system as described here, as if there were no development system. Go to the Administration page and check all entries. Then, on the development system, use Staging to create and download a Zip file with all the data that needs to be moved , and use the Stage Import option in the production system to migrate all the data.

2 Alternatively, before deploying ViewsFlash on the production system, copy the data directory from the development system to production, and copy the database schema from development to production. Then deploy ViewsFlash on the production system as described in this section, and go to the Administration page and correct all entries which will have incorrect values left over from the development system.

Internal ViewsFlash data

In addition to the data gathered from questionnaires, ViewsFlash stores internal information, such as the questions in a questionnaire. When not using a database, the information is kept in the file system, in the ViewsFlash data directory. When using a database, it is normally kept in the database in the VWFOBJECTS table. However, this can be overriden by using the dbstores servlet parameter. If not specified, it defaults to:
dbstores=data|polls|results|styles|votes

This writes everything except the application log to the database; the application log is written to the data directory, or to a directory specified by the logpath= servlet parameter.

The dbstores parameter is any combination of these keywords, separated by | symbol:
dbstores=data|polls|results|styles|votes|logs

The table below indicates the different kinds of files stored in the database for each keyword.

Another consideration is writing HTML files for single page polls, which can be written on the server's file system when a poll is published. To enable this, you must do of the following:
- add the servlet parameter writehtmltodisk=1 to viewsflash.properties
- use the option "Save files to disk as well as on the database" in the place setup page.
- to avoid writing these fragments on the database altogether, specify a dbstores servlet parameter and remove polls from dbstores:
dbstores=data|results|styles|votes

Database Schema
You may want to create a Database User for ViewsFlash. Use the following as a guideline for Oracle:
CREATE USER COGIX IDENTIFIED BY PASSWORD;
GRANT CONNECT TO COGIX;
GRANT RESOURCE TO COGIX;

When you install ViewsFlash with database options, the viewsflash?Diagnose=1 command checks that the all necessary tables exist and reports this. If none of the tables are present, it automatically creates all the tables. If only some tables are present, it reports an error, and you will either have to create the missing tables by hand, or drop all tables, restart the servlet, and run Diagnose=1 again. You can create the tables beforehand with the DDL below. Substitute the indicated values for "timestamptype" and "longbinary", depending on your database vendor.

See the *.sql files in the .../ViewsFlash/WEB-INF/sql directory, which contains DDL statements for creating all needed tables in Oracle, MSSQL, DB2, and MySQL.

The following table explains what is stored in each table.

Database Verification
These commands help diagnose database problems.

/servlet/viewsflash?dbcmd=dbdir
Gives a listing of all objects in the VWFOBJECTS table, which stores objects in the ViewsFlash data directory in a non-database configuration.

/servlet/viewsflash?dbcmd=dbdirs
Same as dbdir, but listing is sorted by the date when the object was last modified.

/servlet/viewsflash?dbcmd=dbdirn
Lists all currently active Notifications between different instances. These expire after a short while and are automatically removed.

Database Manipulation
The following commands are similar, but operate on a single file or object.

/servlet/viewsflash?dbcmd=pagedir&kind=KIND
where KIND is one of data, polls, results, styles
Gives a directory listing of all objects in the database of the indicated kind.

/servlet/viewsflash?dbcmd=pageget&kind=KIND&name=NNN
where KIND is one of data, polls, results, styles and
name is the name of an object, such as the name of a Style Template file (use pagedir first to make sure you have the right name).
This command retrieves the file and displays it in your browser. This can also be used as an API command.

/servlet/viewsflash?dbcmd=pageremove&kind=KIND&name=NNN
where KIND is one of data, polls, results, styles and
name is the name of an object (use pagedir first to make sure you have the right name).
This command removes the indicated file from the database.

/servlet/viewsflash?dbcmd=pageimport&kind=KIND&name=NNN
where KIND is one of data, polls, results, styles and
name is the name of an object. This can be used to add files from the data directory which were not there originally when dbcmd=dbmigrate was performed.

Next: Database specific information