"Fossies" - the Fresh Open Source Software Archive

Member "phpESP/scripts/db/mysql_populate.sql" (26 Mar 2008, 9049 Bytes) of package /linux/www/old/phpESP-2.1.4.tgz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PL/SQL source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file.

    1 
    2 DROP TABLE IF EXISTS realm;
    3 CREATE TABLE realm (
    4     name        CHAR(16) NOT NULL,
    5     title       CHAR(64) NOT NULL,
    6     changed         TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    7     PRIMARY KEY(name)
    8 );
    9 
   10 -- # table of respondents (people who enter data / take surveys)
   11 DROP TABLE IF EXISTS respondent;
   12 CREATE TABLE respondent (
   13     username    CHAR(64) NOT NULL,
   14     password    CHAR(64) NOT NULL,
   15     auth        CHAR(16) NOT NULL DEFAULT 'BASIC',
   16     realm       CHAR(16) NOT NULL,
   17     fname       CHAR(16),
   18     lname       CHAR(24),
   19     email       CHAR(64),
   20     disabled    ENUM('Y','N') NOT NULL DEFAULT 'N',
   21     changed         TIMESTAMP DEFAULT '0000-00-00 00:00:00',
   22     expiration  TIMESTAMP,
   23     PRIMARY KEY (username, realm)
   24 );
   25 
   26 -- # table of designers (people who create forms / surveys)
   27 DROP TABLE IF EXISTS designer;
   28 CREATE TABLE designer (
   29     username    CHAR(64) NOT NULL,
   30     password    CHAR(64) NOT NULL,
   31     auth        CHAR(16) NOT NULL DEFAULT 'BASIC',
   32     realm       CHAR(16) NOT NULL,
   33     fname       CHAR(16),
   34     lname       CHAR(24),
   35     email       CHAR(64),
   36     pdesign     ENUM('Y','N') NOT NULL DEFAULT 'Y',
   37     pstatus     ENUM('Y','N') NOT NULL DEFAULT 'N',
   38     pdata       ENUM('Y','N') NOT NULL DEFAULT 'N',
   39     pall        ENUM('Y','N') NOT NULL DEFAULT 'N',
   40     pgroup      ENUM('Y','N') NOT NULL DEFAULT 'N',
   41     puser       ENUM('Y','N') NOT NULL DEFAULT 'N',
   42     disabled    ENUM('Y','N') NOT NULL DEFAULT 'N',
   43     changed         TIMESTAMP DEFAULT '0000-00-00 00:00:00',
   44     expiration  TIMESTAMP,
   45     PRIMARY KEY(username, realm)
   46 );
   47 
   48 -- # create the _special_ superuser group
   49 -- # members of this group have superuser status
   50 INSERT INTO realm ( name, title )
   51     VALUES ( 'superuser', 'ESP System Administrators' ),
   52         ( 'auto', 'Self added users' );
   53 
   54 -- # default root account
   55 INSERT INTO designer (username, password, fname, lname, realm, pdesign, pstatus, pdata, pall, pgroup, puser, disabled)
   56     VALUES ('root', PASSWORD('esp'), 'ESP', 'Superuser', 'superuser', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N');
   57 
   58 -- ...............................................................
   59 -- ..................... SURVEYS/FORMS ...........................
   60 -- ...............................................................
   61 
   62 -- # table of different surveys available
   63 DROP TABLE IF EXISTS survey;
   64 CREATE TABLE survey (
   65     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
   66     name        CHAR(64) NOT NULL,
   67     owner       CHAR(16) NOT NULL,
   68     realm       CHAR(64) NOT NULL,
   69     public      ENUM('Y','N') NOT NULL DEFAULT 'Y',
   70     status      INT UNSIGNED NOT NULL DEFAULT '0',
   71         open_date       DATETIME NULL,
   72         close_date      DATETIME NULL,
   73     title       CHAR(255) NOT NULL,
   74     email       CHAR(64),
   75     subtitle    TEXT,
   76     info        TEXT,
   77     theme       CHAR(64),
   78     thanks_page CHAR(255),
   79     thank_head  CHAR(255),
   80     thank_body  TEXT,
   81     changed         TIMESTAMP DEFAULT '0000-00-00 00:00:00',
   82     auto_num    ENUM('Y','N') NOT NULL DEFAULT 'Y',
   83     PRIMARY KEY (id),
   84     UNIQUE(name)
   85 );
   86 
   87 -- # types of questions
   88 DROP TABLE IF EXISTS question_type;
   89 CREATE TABLE question_type (
   90     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
   91     type        CHAR(32) NOT NULL,
   92     has_choices ENUM('Y','N') NOT NULL,
   93     response_table  CHAR(32) NOT NULL,
   94     PRIMARY KEY (id)
   95 );
   96 
   97 -- # table of the questions for all the surveys
   98 DROP TABLE IF EXISTS question;
   99 CREATE TABLE question (
  100     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  101     survey_id   INT UNSIGNED NOT NULL,
  102     name        CHAR(30) NOT NULL,
  103     type_id     INT UNSIGNED NOT NULL,
  104     result_id   INT UNSIGNED,
  105     length      INT NOT NULL DEFAULT 0,
  106     precise     INT NOT NULL DEFAULT 0,
  107     position    INT UNSIGNED NOT NULL,
  108     content     TEXT NOT NULL,
  109     required    ENUM('Y','N') NOT NULL DEFAULT 'N',
  110     deleted     ENUM('Y','N') NOT NULL DEFAULT 'N',
  111     public      ENUM('Y','N') NOT NULL DEFAULT 'Y',
  112     PRIMARY KEY (id),
  113     KEY `result_id` (`result_id`),
  114     KEY `survey_id` (`survey_id`)
  115 );
  116 
  117 -- # table of the choices (possible answers) of each question
  118 DROP TABLE IF EXISTS question_choice;
  119 CREATE TABLE question_choice (
  120     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  121     question_id INT UNSIGNED NOT NULL,
  122     content     TEXT NOT NULL,
  123     value       TEXT,
  124     PRIMARY KEY (id),
  125     KEY `question_id` (`question_id`)
  126 );
  127 
  128 -- # access control to adding data to a form / survey
  129 DROP TABLE IF EXISTS access;
  130 CREATE TABLE access (
  131     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  132     survey_id   INT UNSIGNED NOT NULL,
  133     realm       CHAR(16),
  134     maxlogin    INT UNSIGNED DEFAULT '0',
  135         resume      ENUM('Y','N') NOT NULL DEFAULT 'N',
  136         navigate    ENUM('Y','N') NOT NULL DEFAULT 'N',
  137     PRIMARY KEY(id),
  138     KEY `survey_id` (`survey_id`)
  139 );
  140 
  141 -- ...............................................................
  142 -- ..................... RESPONSE DATA ...........................
  143 -- ...............................................................
  144 
  145 -- # this table holds info to distinguish one servey response from another
  146 -- # (plus timestamp, and username if known)
  147 DROP TABLE IF EXISTS response;
  148 CREATE TABLE response (
  149     id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  150     survey_id   INT UNSIGNED NOT NULL,
  151     submitted   TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  152     complete    ENUM('Y','N') NOT NULL DEFAULT 'N',
  153     username    CHAR(64),
  154     ip      CHAR(64),
  155     PRIMARY KEY (id),
  156     KEY `survey_id` (`survey_id`)
  157 );
  158 
  159 -- # answers to boolean questions (yes/no)
  160 DROP TABLE IF EXISTS response_bool;
  161 CREATE TABLE response_bool (
  162     response_id INT UNSIGNED NOT NULL,
  163     question_id INT UNSIGNED NOT NULL,
  164     choice_id   ENUM('Y','N') NOT NULL,
  165     PRIMARY KEY(response_id,question_id),
  166     KEY `response_id` (`response_id`),
  167     KEY `question_id` (`question_id`)
  168 );
  169 
  170 -- # answers to single answer questions (radio, boolean, rate) (chose one of n)
  171 DROP TABLE IF EXISTS response_single;
  172 CREATE TABLE response_single (
  173     response_id INT UNSIGNED NOT NULL,
  174     question_id INT UNSIGNED NOT NULL,
  175     choice_id   INT UNSIGNED NOT NULL,
  176     PRIMARY KEY(response_id,question_id),
  177     KEY `response_id` (`response_id`),
  178     KEY `question_id` (`question_id`)
  179 );
  180 
  181 -- # answers to questions where multiple responses are allowed
  182 -- # (checkbox, select multiple)
  183 DROP TABLE IF EXISTS response_multiple;
  184 CREATE TABLE response_multiple (
  185     id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  186     response_id INT UNSIGNED NOT NULL,
  187     question_id INT UNSIGNED NOT NULL,
  188     choice_id   INT UNSIGNED NOT NULL,
  189     PRIMARY KEY(id),
  190     KEY `response_id` (`response_id`),
  191     KEY `question_id` (`question_id`),
  192     KEY `choice_id` (`choice_id`)
  193 );
  194 
  195 -- # answers to rank questions
  196 DROP TABLE IF EXISTS response_rank;
  197 CREATE TABLE response_rank (
  198     response_id INT UNSIGNED NOT NULL,
  199     question_id INT UNSIGNED NOT NULL,
  200     choice_id   INT UNSIGNED NOT NULL,
  201     rank        INT NOT NULL,
  202     PRIMARY KEY(response_id,question_id,choice_id),
  203     KEY `response_id` (`response_id`),
  204     KEY `question_id` (`question_id`),
  205     KEY `choice_id` (`choice_id`)
  206 );
  207 
  208 -- # answers to any fill in the blank or essay question
  209 DROP TABLE IF EXISTS response_text;
  210 CREATE TABLE response_text (
  211     response_id INT UNSIGNED NOT NULL,
  212     question_id INT UNSIGNED NOT NULL,
  213     response    TEXT,
  214     PRIMARY KEY (response_id,question_id),
  215     KEY `response_id` (`response_id`),
  216     KEY `question_id` (`question_id`)
  217 );
  218 
  219 -- # answers to any Other: ___ questions
  220 DROP TABLE IF EXISTS response_other;
  221 CREATE TABLE response_other (
  222     response_id INT UNSIGNED NOT NULL,
  223     question_id INT UNSIGNED NOT NULL,
  224     choice_id   INT UNSIGNED NOT NULL,
  225     response    TEXT,
  226     PRIMARY KEY (response_id, question_id, choice_id),
  227     KEY `response_id` (`response_id`),
  228     KEY `choice_id` (`choice_id`),
  229     KEY `question_id` (`question_id`)
  230 );
  231 
  232 -- # answers to any date questions
  233 DROP TABLE IF EXISTS response_date;
  234 CREATE TABLE response_date (
  235     response_id INT UNSIGNED NOT NULL,
  236     question_id INT UNSIGNED NOT NULL,
  237     response    DATE,
  238     PRIMARY KEY (response_id,question_id),
  239     KEY `response_id` (`response_id`),
  240     KEY `question_id` (`question_id`)
  241 );
  242 
  243 -- # populate the types of questions
  244 INSERT INTO question_type VALUES ('1','Yes/No','N','response_bool');
  245 INSERT INTO question_type VALUES ('2','Text Box','N','response_text');
  246 INSERT INTO question_type VALUES ('3','Essay Box','N','response_text');
  247 INSERT INTO question_type VALUES ('4','Radio Buttons','Y','response_single');
  248 INSERT INTO question_type VALUES ('5','Check Boxes','Y','response_multiple');
  249 INSERT INTO question_type VALUES ('6','Dropdown Box','Y','response_single');
  250 -- # INSERT INTO question_type VALUES ('7','Rating','N','response_rank');
  251 INSERT INTO question_type VALUES ('8','Rate (scale 1..5)','Y','response_rank');
  252 INSERT INTO question_type VALUES ('9','Date','N','response_date');
  253 INSERT INTO question_type VALUES ('10','Numeric','N','response_text');
  254 INSERT INTO question_type VALUES ('99','Page Break','N','');
  255 INSERT INTO question_type VALUES ('100','Section Text','N','');
  256 
  257 DROP TABLE IF EXISTS conditions;
  258 CREATE TABLE conditions (
  259         id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  260     survey_id   INT UNSIGNED NOT NULL,
  261     q1_id       INT UNSIGNED NOT NULL,
  262     q2_id       INT UNSIGNED NOT NULL,
  263     cond        INT UNSIGNED NOT NULL,
  264     cond_value  TEXT,
  265     PRIMARY KEY (id)
  266 );
  267 
  268 DROP TABLE IF EXISTS survey_statistics;
  269 CREATE TABLE survey_statistics (
  270     survey_id INT UNSIGNED NOT NULL,
  271     loginfail INT UNSIGNED NOT NULL DEFAULT 0,
  272     attempted INT UNSIGNED NOT NULL DEFAULT 0,
  273     abandoned INT UNSIGNED NOT NULL DEFAULT 0,
  274     suspended INT UNSIGNED NOT NULL DEFAULT 0,
  275     completed INT UNSIGNED NOT NULL DEFAULT 0,
  276     PRIMARY KEY (survey_id)
  277 );
  278