Subversion Repositories ezsurvey_ezp4

[/] [trunk/] [extension/] [ezsurvey/] [sql/] [oracle/] [schema.sql] - Blame information for rev 97

Details | Compare with Previous | View Log

Line No. Rev Author Line
1 9 br
CREATE SEQUENCE s_survey;
2
CREATE SEQUENCE s_surveymetadata;
3
CREATE SEQUENCE s_surveyquestion;
4
CREATE SEQUENCE s_surveyquestionresult;
5
CREATE SEQUENCE s_surveyresult;
6 75 gg
CREATE SEQUENCE s_surveyrelatedconfig;
7
CREATE SEQUENCE s_surveyquestionmetadata;
8 9 br
 
9
CREATE TABLE ezsurvey (
10
  id INTEGER NOT NULL,
11
  title VARCHAR2(255),
12
  enabled INTEGER DEFAULT 1 NOT NULL,
13
  valid_from INTEGER DEFAULT 0 NOT NULL,
14
  valid_to INTEGER DEFAULT 0 NOT NULL,
15
  redirect_cancel VARCHAR2(255),
16
  redirect_submit VARCHAR2(255),
17
  published INTEGER DEFAULT 0 NOT NULL,
18
  persistent INTEGER DEFAULT 0 NOT NULL,
19 13 br
  one_answer INTEGER DEFAULT 0 NOT NULL,
20
  contentobject_id INTEGER DEFAULT 0 NOT NULL,
21 9 br
  contentobjectattribute_id INTEGER DEFAULT 0 NOT NULL,
22
  contentobjectattribute_version INTEGER DEFAULT 0 NOT NULL,
23 13 br
  contentclassattribute_id INTEGER DEFAULT 0 NOT NULL,
24
  language_code VARCHAR2(20) DEFAULT NULL,
25 9 br
  PRIMARY KEY(id)
26
);
27
CREATE TABLE ezsurveyquestion (
28
  id INTEGER NOT NULL,
29
  survey_id INTEGER DEFAULT 0 NOT NULL,
30 66 gg
  original_id INTEGER DEFAULT 0 NOT NULL,
31 9 br
  tab_order INTEGER DEFAULT 0 NOT NULL,
32
  mandatory INTEGER DEFAULT 1 NOT NULL,
33
  visible INTEGER DEFAULT 1 NOT NULL,
34
  type VARCHAR2(255),
35
  default_value CLOB,
36
  text CLOB,
37
  text2 CLOB,
38
  text3 CLOB,
39
  num INTEGER,
40
  num2 INTEGER,
41
  PRIMARY KEY(id)
42
);
43 66 gg
CREATE TABLE ezsurveyresult (
44
  id INTEGER NOT NULL,
45
  survey_id INTEGER DEFAULT 0 NOT NULL,
46
  user_id INTEGER DEFAULT 0 NOT NULL,
47
  tstamp INTEGER DEFAULT 0 NOT NULL,
48
  user_session_id VARCHAR2(255),
49
  PRIMARY KEY(id)
50
);
51 9 br
CREATE TABLE ezsurveyquestionresult (
52
  id INTEGER NOT NULL,
53
  result_id INTEGER DEFAULT 0 NOT NULL,
54
  question_id INTEGER DEFAULT 0 NOT NULL,
55 13 br
  questionoriginal_id INTEGER DEFAULT 0 NOT NULL,
56 9 br
  text VARCHAR2(4000),
57
  PRIMARY KEY(id)
58
);
59 66 gg
CREATE TABLE ezsurveymetadata (
60 9 br
  id INTEGER NOT NULL,
61 66 gg
  result_id INTEGER DEFAULT 0 NOT NULL,
62
  attr_name VARCHAR2(255),
63
  attr_value VARCHAR2(255),
64 9 br
  PRIMARY KEY(id)
65
);
66 13 br
CREATE TABLE ezsurveyrelatedconfig (
67
    id INTEGER NOT NULL,
68
    contentclass_id INTEGER DEFAULT 1 NOT NULL,
69
    node_id INTEGER DEFAULT 0 NOT NULL,
70
    PRIMARY KEY(id)
71
);
72
CREATE TABLE ezsurveyquestionmetadata (
73
    id INTEGER NOT NULL,
74
    result_id INTEGER DEFAULT 0 NOT NULL,
75
    question_id INTEGER DEFAULT 0 NOT NULL,
76
    question_original_id INTEGER DEFAULT 0 NOT NULL,
77
    name VARCHAR2(255),
78
    value CLOB,
79
    PRIMARY KEY(id)
80
);
81 9 br
 
82 13 br
 
83 9 br
CREATE OR REPLACE TRIGGER ezsurvey_id_tr
84
BEFORE INSERT ON ezsurvey FOR EACH ROW WHEN (new.id IS NULL)
85
BEGIN
86 66 gg
  SELECT s_survey.NEXTVAL INTO :new.id FROM dual;
87 9 br
END;
88
/
89
 
90
CREATE OR REPLACE TRIGGER ezsurveymetadata_id_tr
91
BEFORE INSERT ON ezsurveymetadata FOR EACH ROW WHEN (new.id IS NULL)
92
BEGIN
93 66 gg
  SELECT s_surveymetadata.NEXTVAL INTO :new.id FROM dual;
94 9 br
END;
95
/
96
 
97
CREATE OR REPLACE TRIGGER ezsurveyquestion_id_tr
98
BEFORE INSERT ON ezsurveyquestion FOR EACH ROW WHEN (new.id IS NULL)
99
BEGIN
100 66 gg
  SELECT s_surveyquestion.NEXTVAL INTO :new.id FROM dual;
101 9 br
END;
102
/
103
 
104
CREATE OR REPLACE TRIGGER ezsurveyquestionresult_id_tr
105
BEFORE INSERT ON ezsurveyquestionresult FOR EACH ROW WHEN (new.id IS NULL)
106
BEGIN
107 66 gg
  SELECT s_surveyquestionresult.NEXTVAL INTO :new.id FROM dual;
108 9 br
END;
109
/
110
 
111
CREATE OR REPLACE TRIGGER ezsurveyresult_id_tr
112
BEFORE INSERT ON ezsurveyresult FOR EACH ROW WHEN (new.id IS NULL)
113
BEGIN
114 66 gg
  SELECT s_surveyresult.NEXTVAL INTO :new.id FROM dual;
115 9 br
END;
116
/
117
 
118 75 gg
CREATE OR REPLACE TRIGGER ezsurveyrelatedconfig_id_tr
119
BEFORE INSERT ON ezsurveyrelatedconfig FOR EACH ROW WHEN (new.id IS NULL)
120
BEGIN
121
  SELECT s_surveyrelatedconfig.NEXTVAL INTO :new.id FROM dual;
122
END;
123
/
124
 
125
CREATE OR REPLACE TRIGGER ezsurveyquestionmetadata_id_tr
126
BEFORE INSERT ON ezsurveyquestionmetadata FOR EACH ROW WHEN (new.id IS NULL)
127
BEGIN
128
  SELECT s_surveyquestionmetadata.NEXTVAL INTO :new.id FROM dual;
129
END;
130
/
131
 
132 97 chen
CREATE INDEX ezsurvey_co_id_i ON ezsurvey (contentobject_id);
133
CREATE INDEX ezsurvey_coattribute_id_i ON ezsurvey (contentobjectattribute_id);
134
CREATE INDEX ezsurvey_coattribute_version_i ON ezsurvey (contentobjectattribute_version);
135
CREATE INDEX ezsurvey_ccattribute_id_i ON ezsurvey (contentclassattribute_id);
136 64 br
CREATE INDEX ezsurvey_language_code_i ON ezsurvey (language_code);
137
CREATE INDEX ezsurveymetadata_result_id_i ON ezsurveymetadata (result_id);
138
CREATE INDEX ezsurveymetadata_attr_name_i ON ezsurveymetadata (attr_name);
139
CREATE INDEX ezsurveymetadata_attr_value_i ON ezsurveymetadata (attr_value);
140
CREATE INDEX ezsurveyquestion_survey_id_i ON ezsurveyquestion (survey_id);
141
CREATE INDEX ezsurveyquestionresult_00040_i ON ezsurveyquestionresult (result_id);
142
CREATE INDEX ezsurveyquestionresult_00041_i ON ezsurveyquestionresult (question_id);
143 66 gg
CREATE INDEX ezsurveyresult_survey_id_i ON ezsurveyresult (survey_id);