--- Begin Message ---
Hi!
I'm setting up OTRS 1.2.2, and I need it to talk to an Oracle
database. It seems to work OK now, so I thought I'd share the
modifications I needed to make. These patches are against CVS as of
right now, and it seems to work fine so far:
- The XML database schema seems to have some name collisions and
typos. Also, 'session' is a reserved word in Oracle, and
tables/indexes can't have names of more than 32 characters. The
following patch fixes some duplicate names, shortens some index
names and renames 'session' to 'sessions':
Index: scripts/database/otrs-schema.xml
===================================================================
RCS file: /home/cvs/otrs/scripts/database/otrs-schema.xml,v
retrieving revision 1.20
diff -u -r1.20 otrs-schema.xml
--- scripts/database/otrs-schema.xml 2 Feb 2004 23:02:00 -0000 1.20
+++ scripts/database/otrs-schema.xml 12 Mar 2004 13:52:34 -0000
@@ -594,7 +594,7 @@
<column name="create_by" required="true" type="INTEGER"/>
<column name="change_time" required="true" type="DATE"/>
<column name="change_by" required="true" type="INTEGER"/>
- <index name="article_id">
+ <index name="article_plain_article_id">
<index-column name="article_id"/>
</index>
</table>
@@ -727,20 +727,20 @@
<column name="create_by" required="true" type="INTEGER"/>
<column name="change_time" required="true" type="DATE"/>
<column name="change_by" required="true" type="INTEGER"/>
- <index name="index_ticket_id">
+ <index name="index_t_acc_ticket_id">
<index-column name="ticket_id"/>
</index>
</table>
-<!-- session -->
-<table name="session">
- <column name="session_id" required="true" size="120" type="VARCHAR"/>
+<!-- sessions -->
+<table name="sessions">
+ <column name="sessions_id" required="true" size="120" type="VARCHAR"/>
<column name="value" required="true" type="LONGVARCHAR"/>
<unique>
- <unique-column name="session_id"/>
+ <unique-column name="sessions_id"/>
</unique>
- <index name="index_session_id">
- <index-column name="session_id"/>
+ <index name="index_sessions_id">
+ <index-column name="sessions_id"/>
</index>
</table>
@@ -761,7 +761,7 @@
<!-- ticket_lock_index -->
<table name="ticket_lock_index">
<column name="ticket_id" required="true" type="BIGINT"/>
- <index name="index_ticket_id">
+ <index name="ticket_lock_id_index">
<index-column name="ticket_id"/>
</index>
</table>
@@ -792,7 +792,7 @@
<column name="user_id" required="true" size="250" type="VARCHAR"/>
<column name="preferences_key" required="true" size="150" type="VARCHAR"/>
<column name="preferences_value" required="false" size="250" type="VARCHAR"/>
- <index name="index_customer_preferences_user_id">
+ <index name="index_customer_prefs_user_id">
<index-column name="user_id"/>
</index>
</table>
@@ -801,10 +801,10 @@
<table name="ticket_loop_protection">
<column name="sent_to" required="true" size="250" type="VARCHAR"/>
<column name="sent_date" required="true" size="150" type="VARCHAR"/>
- <index name="index_ticket_loop_protection_sent_to">
+ <index name="index_ticket_l_prot_sent_to">
<index-column name="sent_to"/>
</index>
- <index name="index_ticket_loop_protection_sent_date">
+ <index name="index_ticket_l_prot_sent_date">
<index-column name="sent_date"/>
</index>
</table>
- This patch updates the values in Kernel/Config/Defaults.pm to
reflect the renaming of the session table:
Index: Kernel/Config/Defaults.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/Config/Defaults.pm,v
retrieving revision 1.115
diff -u -r1.115 Defaults.pm
--- Kernel/Config/Defaults.pm 5 Mar 2004 08:37:54 -0000 1.115
+++ Kernel/Config/Defaults.pm 12 Mar 2004 13:56:05 -0000
@@ -950,9 +950,9 @@
# SessionTable*
# (just needed if $Self->{SessionModule}='Kernel::System::AuthSession::DB)
# SessionTable
- $Self->{SessionTable} = 'session';
+ $Self->{SessionTable} = 'sessions';
# SessionTable id column
- $Self->{SessionTableID} = 'session_id';
+ $Self->{SessionTableID} = 'sessions_id';
# SessionTable value column
$Self->{SessionTableValue} = 'value';
- This patch to Kernel/System/AuthSession/DB.pm does the same:
Index: Kernel/System/AuthSession/DB.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/System/AuthSession/DB.pm,v
retrieving revision 1.14
diff -u -r1.14 DB.pm
--- Kernel/System/AuthSession/DB.pm 13 Feb 2004 00:50:36 -0000 1.14
+++ Kernel/System/AuthSession/DB.pm 12 Mar 2004 14:03:49 -0000
@@ -39,7 +39,7 @@
$Self->{Debug} = 0;
# session table data
$Self->{SQLSessionTable} = $Self->{ConfigObject}->Get('SessionTable')
- || 'session';
+ || 'sessions';
# id row
$Self->{SQLSessionTableID} = $Self->{ConfigObject}->Get('SessionTableID')
|| 'session_id';
- The following patch to Kernel/System/DB.pm fixes a typo
('QuoteSignle' -> 'QuoteSingle').
Index: Kernel/System/DB.pm
===================================================================
RCS file: /home/cvs/otrs/Kernel/System/DB.pm,v
retrieving revision 1.38
diff -u -b -B -r1.38 DB.pm
--- Kernel/System/DB.pm 29 Feb 2004 19:09:13 -0000 1.38
+++ Kernel/System/DB.pm 15 Mar 2004 10:50:10 -0000
@@ -120,7 +120,7 @@
if ($Self->{'DB::Type'} eq 'mysql') {
$Self->{'DB::Limit'} = 'limit';
$Self->{'DB::DirectBlob'} = 1;
- $Self->{'DB::QuoteSignle'} = '\\';
+ $Self->{'DB::QuoteSingle'} = '\\';
$Self->{'DB::QuoteBack'} = '\\';
$Self->{'DB::QuoteSemicolon'} = '\\';
$Self->{'DB::Attribute'} = {};
@@ -128,7 +128,7 @@
elsif ($Self->{'DB::Type'} eq 'postgresql') {
$Self->{'DB::Limit'} = 'limit';
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\\';
+ $Self->{'DB::QuoteSingle'} = '\\';
$Self->{'DB::QuoteBack'} = '\\';
$Self->{'DB::QuoteSemicolon'} = '\\';
$Self->{'DB::Attribute'} = {};
@@ -136,7 +136,7 @@
elsif ($Self->{'DB::Type'} eq 'oracle') {
$Self->{'DB::Limit'} = 0;
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\'';
+ $Self->{'DB::QuoteSingle'} = '\'';
$Self->{'DB::QuoteBack'} = 0;
$Self->{'DB::QuoteSemicolon'} = '';
$Self->{'DB::Attribute'} = {
@@ -147,7 +147,7 @@
elsif ($Self->{'DB::Type'} eq 'db2') {
$Self->{'DB::Limit'} = 'fetch';
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\\';
+ $Self->{'DB::QuoteSingle'} = '\\';
$Self->{'DB::QuoteBack'} = '\\';
$Self->{'DB::QuoteSemicolon'} = '\\';
$Self->{'DB::Attribute'} = {};
@@ -155,7 +155,7 @@
elsif ($Self->{'DB::Type'} eq 'sapdb') {
$Self->{'DB::Limit'} = 0;
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\'';
+ $Self->{'DB::QuoteSingle'} = '\'';
$Self->{'DB::QuoteBack'} = 0;
$Self->{'DB::QuoteSemicolon'} = '\'';
$Self->{'DB::Attribute'} = {
@@ -167,7 +167,7 @@
elsif ($Self->{'DB::Type'} eq 'mssql') {
$Self->{'DB::Limit'} = 0;
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\'';
+ $Self->{'DB::QuoteSingle'} = '\'';
$Self->{'DB::QuoteBack'} = 0;
$Self->{'DB::QuoteSemicolon'} = '\'';
$Self->{'DB::Attribute'} = {
@@ -178,7 +178,7 @@
elsif ($Self->{'DB::Type'} eq 'generic') {
$Self->{'DB::Limit'} = 0;
$Self->{'DB::DirectBlob'} = 0;
- $Self->{'DB::QuoteSignle'} = '\\';
+ $Self->{'DB::QuoteSingle'} = '\\';
$Self->{'DB::QuoteBack'} = '\\';
$Self->{'DB::QuoteSemicolon'} = '\\';
$Self->{'DB::Attribute'} = {
@@ -285,8 +286,8 @@
if ($Self->{'DB::QuoteBack'}) {
$Text =~ s/\\/$Self->{'DB::QuoteBack'}\\/g;
}
- if ($Self->{'DB::QuoteSignle'}) {
- $Text =~ s/'/$Self->{'DB::QuoteSignle'}'/g;
+ if ($Self->{'DB::QuoteSingle'}) {
+ $Text =~ s/'/$Self->{'DB::QuoteSingle'}'/g; #' dont mess up my perl mode
}
if ($Self->{'DB::QuoteSemicolon'}) {
$Text =~ s/;/$Self->{'DB::QuoteSemicolon'};/g;
(I had some other patches to that file as well, but someone fixed
them after the 1.2.2 release. Drat! :)
- This patch to scripts/database/initial_insert.sql adds a (commented
out) Oracle statement to disable substition variable scanning, which
created problems for me when I piped it through sqlplus. It also
changes the "empty answer" in standard_response from '' to ' ',
since '' violates the NOT NULL constraint in Oracle:
Index: scripts/database/initial_insert.sql
===================================================================
RCS file: /home/cvs/otrs/scripts/database/initial_insert.sql,v
retrieving revision 1.22
diff -u -b -B -r1.22 initial_insert.sql
--- scripts/database/initial_insert.sql 11 Mar 2004 14:39:44 -0000 1.22
+++ scripts/database/initial_insert.sql 15 Mar 2004 10:53:46 -0000
@@ -176,6 +176,14 @@
-- did not receive this file, see http://www.gnu.org/licenses/gpl.txt.
-- --
+-- --
+-- If you use Oracle and shove this file through sql*plus, you might
+-- want to set this, to disable the substition mechanism which is
+-- otherwise triggered by some of the notification strings:
+-- --
+-- set define off;
+-- --
+
-- table valid
INSERT INTO valid
(name, create_by, create_time, change_by, change_time)
@@ -612,7 +620,7 @@
INSERT INTO standard_response
(name, text, valid_id, create_by, create_time, change_by, change_time)
VALUES
- ('empty answer', '', 1, 1, current_timestamp, 1, current_timestamp);
+ ('empty answer', ' ', 1, 1, current_timestamp, 1, current_timestamp);
INSERT INTO standard_response
(name, text, valid_id, create_by, create_time, change_by, change_time)
VALUES
- Oracle's lack of something resembling PostgreSQL's SERIAL datatype
was a bit of a challenge. Someone pointed me at
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=rule-210694185806%4017.1.7.156
, which shows how to emulate it with triggers. It should be
possible to have the torque schemas generate such triggers, but
since it doesn't, I adapted the SQL file by hand to include such
triggers for all the ID fields using autoincrement in the XML file:
-----------------------------------------------------------------------------
-- valid
-----------------------------------------------------------------------------
DROP TABLE valid CASCADE CONSTRAINTS;
CREATE TABLE valid
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (50) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT valid_U_1 UNIQUE (name)
);
ALTER TABLE valid
ADD CONSTRAINT valid_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE valid_id_seq;
CREATE SEQUENCE valid_id_seq;
CREATE OR REPLACE TRIGGER valid_id_seq_t
before insert on valid
for each row
begin
select valid_id_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_priority
-----------------------------------------------------------------------------
DROP TABLE ticket_priority CASCADE CONSTRAINTS;
CREATE TABLE ticket_priority
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (50) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_priority_U_1 UNIQUE (name)
);
ALTER TABLE ticket_priority
ADD CONSTRAINT ticket_priority_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_priority_seq;
CREATE SEQUENCE ticket_priority_seq;
CREATE OR REPLACE TRIGGER ticket_priority_seq_t
before insert on ticket_priority
for each row
begin
select ticket_priority_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_lock_type
-----------------------------------------------------------------------------
DROP TABLE ticket_lock_type CASCADE CONSTRAINTS;
CREATE TABLE ticket_lock_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (50) NOT NULL,
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_lock_type_U_1 UNIQUE (name)
);
ALTER TABLE ticket_lock_type
ADD CONSTRAINT ticket_lock_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_lock_type_seq;
CREATE SEQUENCE ticket_lock_type_seq;
CREATE OR REPLACE TRIGGER ticket_lock_type_seq_t
before insert on ticket_lock_type
for each row
begin
select ticket_lock_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- system_user
-----------------------------------------------------------------------------
DROP TABLE system_user CASCADE CONSTRAINTS;
CREATE TABLE system_user
(
id NUMBER NOT NULL,
login VARCHAR2 (100) NOT NULL,
pw VARCHAR2 (50) NOT NULL,
salutation VARCHAR2 (50),
first_name VARCHAR2 (100) NOT NULL,
last_name VARCHAR2 (100) NOT NULL,
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT system_user_U_1 UNIQUE (login)
);
ALTER TABLE system_user
ADD CONSTRAINT system_user_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE system_user_seq;
CREATE SEQUENCE system_user_seq;
CREATE OR REPLACE TRIGGER system_user_seq_t
before insert on system_user
for each row
begin
select system_user_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- user_preferences
-----------------------------------------------------------------------------
DROP TABLE user_preferences CASCADE CONSTRAINTS;
CREATE TABLE user_preferences
(
user_id NUMBER NOT NULL,
preferences_key VARCHAR2 (150) NOT NULL,
preferences_value VARCHAR2 (250)
);
CREATE INDEX index_user_preferences_user_id ON user_preferences (user_id);
-----------------------------------------------------------------------------
-- groups
-----------------------------------------------------------------------------
DROP TABLE groups CASCADE CONSTRAINTS;
CREATE TABLE groups
(
id NUMBER NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT groups_U_1 UNIQUE (name)
);
ALTER TABLE groups
ADD CONSTRAINT groups_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE groups_seq;
CREATE SEQUENCE groups_seq;
CREATE OR REPLACE TRIGGER groups_seq_t
before insert on groups
for each row
begin
select groups_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- group_user
-----------------------------------------------------------------------------
DROP TABLE group_user CASCADE CONSTRAINTS;
CREATE TABLE group_user
(
user_id NUMBER NOT NULL,
group_id NUMBER NOT NULL,
permission_key VARCHAR2 (20) NOT NULL,
permission_value NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
-----------------------------------------------------------------------------
-- group_customer_user
-----------------------------------------------------------------------------
DROP TABLE group_customer_user CASCADE CONSTRAINTS;
CREATE TABLE group_customer_user
(
user_id VARCHAR2 (40) NOT NULL,
group_id NUMBER NOT NULL,
permission_key VARCHAR2 (20) NOT NULL,
permission_value NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
-----------------------------------------------------------------------------
-- personal_queues
-----------------------------------------------------------------------------
DROP TABLE personal_queues CASCADE CONSTRAINTS;
CREATE TABLE personal_queues
(
user_id NUMBER NOT NULL,
queue_id NUMBER NOT NULL
);
-----------------------------------------------------------------------------
-- theme
-----------------------------------------------------------------------------
DROP TABLE theme CASCADE CONSTRAINTS;
CREATE TABLE theme
(
id NUMBER (5, 0) NOT NULL,
theme VARCHAR2 (100) NOT NULL,
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT theme_U_1 UNIQUE (theme)
);
ALTER TABLE theme
ADD CONSTRAINT theme_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE theme_seq;
CREATE SEQUENCE theme_seq;
CREATE OR REPLACE TRIGGER theme_seq_t
before insert on theme
for each row
begin
select theme_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- charset
-----------------------------------------------------------------------------
DROP TABLE charset CASCADE CONSTRAINTS;
CREATE TABLE charset
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (64) NOT NULL,
charset VARCHAR2 (30) NOT NULL,
comments VARCHAR2 (80),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT charset_U_1 UNIQUE (name)
);
ALTER TABLE charset
ADD CONSTRAINT charset_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE charset_seq;
CREATE SEQUENCE charset_seq;
CREATE OR REPLACE TRIGGER charset_seq_t
before insert on charset
for each row
begin
select charset_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_state
-----------------------------------------------------------------------------
DROP TABLE ticket_state CASCADE CONSTRAINTS;
CREATE TABLE ticket_state
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
type_id NUMBER (5, 0) NOT NULL,
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_state_U_1 UNIQUE (name)
);
ALTER TABLE ticket_state
ADD CONSTRAINT ticket_state_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_state_seq;
CREATE SEQUENCE ticket_state_seq;
CREATE OR REPLACE TRIGGER ticket_state_seq_t
before insert on ticket_state
for each row
begin
select ticket_state_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_state_type
-----------------------------------------------------------------------------
DROP TABLE ticket_state_type CASCADE CONSTRAINTS;
CREATE TABLE ticket_state_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (120) NOT NULL,
comments VARCHAR2 (250),
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_state_type_U_1 UNIQUE (name)
);
ALTER TABLE ticket_state_type
ADD CONSTRAINT ticket_state_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_state_type_seq;
CREATE SEQUENCE ticket_state_type_seq;
CREATE OR REPLACE TRIGGER ticket_state_type_seq_t
before insert on ticket_state_type
for each row
begin
select ticket_state_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- salutation
-----------------------------------------------------------------------------
DROP TABLE salutation CASCADE CONSTRAINTS;
CREATE TABLE salutation
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
text VARCHAR2 (2000) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT salutation_U_1 UNIQUE (name)
);
ALTER TABLE salutation
ADD CONSTRAINT salutation_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE salutation_seq;
CREATE SEQUENCE salutation_seq;
CREATE OR REPLACE TRIGGER salutation_seq_t
before insert on salutation
for each row
begin
select salutation_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- signature
-----------------------------------------------------------------------------
DROP TABLE signature CASCADE CONSTRAINTS;
CREATE TABLE signature
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
text VARCHAR2 (2000) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT signature_U_1 UNIQUE (name)
);
ALTER TABLE signature
ADD CONSTRAINT signature_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE signature_seq;
CREATE SEQUENCE signature_seq;
CREATE OR REPLACE TRIGGER signature_seq_t
before insert on signature
for each row
begin
select signature_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- system_address
-----------------------------------------------------------------------------
DROP TABLE system_address CASCADE CONSTRAINTS;
CREATE TABLE system_address
(
id NUMBER (5, 0) NOT NULL,
value0 VARCHAR2 (200) NOT NULL,
value1 VARCHAR2 (200) NOT NULL,
value2 VARCHAR2 (200),
value3 VARCHAR2 (200),
queue_id NUMBER NOT NULL,
comments VARCHAR2 (200),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE system_address
ADD CONSTRAINT system_address_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE system_address_seq;
CREATE SEQUENCE system_address_seq;
CREATE OR REPLACE TRIGGER system_address_seq_t
before insert on system_address
for each row
begin
select system_address_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- follow_up_possible
-----------------------------------------------------------------------------
DROP TABLE follow_up_possible CASCADE CONSTRAINTS;
CREATE TABLE follow_up_possible
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT follow_up_possible_U_1 UNIQUE (name)
);
ALTER TABLE follow_up_possible
ADD CONSTRAINT follow_up_possible_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE follow_up_possible_seq;
CREATE SEQUENCE follow_up_possible_seq;
CREATE OR REPLACE TRIGGER follow_up_possible_seq_t
before insert on follow_up_possible
for each row
begin
select follow_up_possible_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- queue
-----------------------------------------------------------------------------
DROP TABLE queue CASCADE CONSTRAINTS;
CREATE TABLE queue
(
id NUMBER NOT NULL,
name VARCHAR2 (200) NOT NULL,
group_id NUMBER NOT NULL,
unlock_timeout NUMBER,
escalation_time NUMBER,
system_address_id NUMBER (5, 0) NOT NULL,
salutation_id NUMBER (5, 0) NOT NULL,
signature_id NUMBER (5, 0) NOT NULL,
follow_up_id NUMBER (5, 0) NOT NULL,
follow_up_lock NUMBER (5, 0) NOT NULL,
move_notify NUMBER (5, 0) NOT NULL,
state_notify NUMBER (5, 0) NOT NULL,
lock_notify NUMBER (5, 0) NOT NULL,
owner_notify NUMBER (5, 0) NOT NULL,
comments VARCHAR2 (200),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT queue_U_1 UNIQUE (name)
);
ALTER TABLE queue
ADD CONSTRAINT queue_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE queue_seq;
CREATE SEQUENCE queue_seq;
CREATE OR REPLACE TRIGGER queue_seq_t
before insert on queue
for each row
begin
select queue_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket
-----------------------------------------------------------------------------
DROP TABLE ticket CASCADE CONSTRAINTS;
CREATE TABLE ticket
(
id NUMBER (20, 0) NOT NULL,
tn VARCHAR2 (50) NOT NULL,
queue_id NUMBER NOT NULL,
ticket_lock_id NUMBER (5, 0) NOT NULL,
ticket_answered NUMBER (5, 0) NOT NULL,
user_id NUMBER NOT NULL,
group_id NUMBER NOT NULL,
ticket_priority_id NUMBER (5, 0) NOT NULL,
ticket_state_id NUMBER (5, 0) NOT NULL,
group_read NUMBER (5, 0),
group_write NUMBER (5, 0),
other_read NUMBER (5, 0),
other_write NUMBER (5, 0),
customer_id VARCHAR2 (150),
customer_user_id VARCHAR2 (250),
timeout NUMBER,
until_time NUMBER,
freekey1 VARCHAR2 (80),
freetext1 VARCHAR2 (150),
freekey2 VARCHAR2 (80),
freetext2 VARCHAR2 (150),
freekey3 VARCHAR2 (80),
freetext3 VARCHAR2 (150),
freekey4 VARCHAR2 (80),
freetext4 VARCHAR2 (150),
freekey5 VARCHAR2 (80),
freetext5 VARCHAR2 (150),
freekey6 VARCHAR2 (80),
freetext6 VARCHAR2 (150),
freekey7 VARCHAR2 (80),
freetext7 VARCHAR2 (150),
freekey8 VARCHAR2 (80),
freetext8 VARCHAR2 (150),
valid_id NUMBER (5, 0) NOT NULL,
create_time_unix NUMBER (20, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_U_4 UNIQUE (tn)
);
ALTER TABLE ticket
ADD CONSTRAINT ticket_PK
PRIMARY KEY (id);
CREATE INDEX index_ticket_queue_view ON ticket (ticket_state_id, ticket_lock_id,
group_id);
CREATE INDEX index_ticket_user ON ticket (user_id);
CREATE INDEX index_ticket_answered ON ticket (ticket_answered);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_seq;
CREATE SEQUENCE ticket_seq;
CREATE OR REPLACE TRIGGER ticket_seq_t
before insert on ticket
for each row
begin
select ticket_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_link
-----------------------------------------------------------------------------
DROP TABLE ticket_link CASCADE CONSTRAINTS;
CREATE TABLE ticket_link
(
ticket_id_master NUMBER (20, 0) NOT NULL,
ticket_id_slave NUMBER (20, 0) NOT NULL
);
-----------------------------------------------------------------------------
-- ticket_history
-----------------------------------------------------------------------------
DROP TABLE ticket_history CASCADE CONSTRAINTS;
CREATE TABLE ticket_history
(
id NUMBER (20, 0) NOT NULL,
name VARCHAR2 (200) NOT NULL,
history_type_id NUMBER (5, 0) NOT NULL,
ticket_id NUMBER (20, 0) NOT NULL,
article_id NUMBER (20, 0),
system_queue_id NUMBER (5, 0),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE ticket_history
ADD CONSTRAINT ticket_history_PK
PRIMARY KEY (id);
CREATE INDEX ticket_history_ticket_id ON ticket_history (ticket_id);
CREATE INDEX ticket_history_create_time ON ticket_history (create_time);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_history_seq;
CREATE SEQUENCE ticket_history_seq;
CREATE OR REPLACE TRIGGER ticket_history_seq_t
before insert on ticket_history
for each row
begin
select ticket_history_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- ticket_history_type
-----------------------------------------------------------------------------
DROP TABLE ticket_history_type CASCADE CONSTRAINTS;
CREATE TABLE ticket_history_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT ticket_history_type_U_1 UNIQUE (name)
);
ALTER TABLE ticket_history_type
ADD CONSTRAINT ticket_history_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE ticket_history_type_seq;
CREATE SEQUENCE ticket_history_type_seq;
CREATE OR REPLACE TRIGGER ticket_history_type_seq_t
before insert on ticket_history_type
for each row
begin
select ticket_history_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- article_type
-----------------------------------------------------------------------------
DROP TABLE article_type CASCADE CONSTRAINTS;
CREATE TABLE article_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT article_type_U_1 UNIQUE (name)
);
ALTER TABLE article_type
ADD CONSTRAINT article_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE article_type_seq;
CREATE SEQUENCE article_type_seq;
CREATE OR REPLACE TRIGGER article_type_seq_t
before insert on article_type
for each row
begin
select article_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- article_sender_type
-----------------------------------------------------------------------------
DROP TABLE article_sender_type CASCADE CONSTRAINTS;
CREATE TABLE article_sender_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT article_sender_type_U_1 UNIQUE (name)
);
ALTER TABLE article_sender_type
ADD CONSTRAINT article_sender_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE article_sender_type_seq;
CREATE SEQUENCE article_sender_type_seq;
CREATE OR REPLACE TRIGGER article_sender_type_seq_t
before insert on article_sender_type
for each row
begin
select article_sender_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- article
-----------------------------------------------------------------------------
DROP TABLE article CASCADE CONSTRAINTS;
CREATE TABLE article
(
id NUMBER (20, 0) NOT NULL,
ticket_id NUMBER (20, 0) NOT NULL,
article_type_id NUMBER (5, 0) NOT NULL,
article_sender_type_id NUMBER (5, 0) NOT NULL,
a_from VARCHAR2 (2000),
a_reply_to VARCHAR2 (255),
a_to VARCHAR2 (2000),
a_cc VARCHAR2 (2000),
a_subject VARCHAR2 (2000),
a_message_id VARCHAR2 (250),
a_content_type VARCHAR2 (250),
a_body VARCHAR2 (2000) NOT NULL,
incoming_time NUMBER NOT NULL,
content_path VARCHAR2 (250),
a_freekey1 VARCHAR2 (250),
a_freetext1 VARCHAR2 (250),
a_freekey2 VARCHAR2 (250),
a_freetext2 VARCHAR2 (250),
a_freekey3 VARCHAR2 (250),
a_freetext3 VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE article
ADD CONSTRAINT article_PK
PRIMARY KEY (id);
CREATE INDEX article_ticket_id ON article (ticket_id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE article_seq;
CREATE SEQUENCE article_seq;
CREATE OR REPLACE TRIGGER article_seq_t
before insert on article
for each row
begin
select article_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- article_plain
-----------------------------------------------------------------------------
DROP TABLE article_plain CASCADE CONSTRAINTS;
CREATE TABLE article_plain
(
id NUMBER (20, 0) NOT NULL,
article_id NUMBER (20, 0) NOT NULL,
body VARCHAR2 (2000) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE article_plain
ADD CONSTRAINT article_plain_PK
PRIMARY KEY (id);
CREATE INDEX article_plain_article_id ON article_plain (article_id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE article_plain_seq;
CREATE SEQUENCE article_plain_seq;
CREATE OR REPLACE TRIGGER article_plain_seq_t
before insert on article_plain
for each row
begin
select article_plain_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- article_attachment
-----------------------------------------------------------------------------
DROP TABLE article_attachment CASCADE CONSTRAINTS;
CREATE TABLE article_attachment
(
id NUMBER (20, 0) NOT NULL,
article_id NUMBER (20, 0) NOT NULL,
filename VARCHAR2 (250),
content_type VARCHAR2 (250),
content VARCHAR2 (2000) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE article_attachment
ADD CONSTRAINT article_attachment_PK
PRIMARY KEY (id);
CREATE INDEX article_id ON article_attachment (article_id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE article_attachment_seq;
CREATE SEQUENCE article_attachment_seq;
CREATE OR REPLACE TRIGGER article_attachment_seq_t
before insert on article_attachment
for each row
begin
select article_attachment_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- standard_response
-----------------------------------------------------------------------------
DROP TABLE standard_response CASCADE CONSTRAINTS;
CREATE TABLE standard_response
(
id NUMBER NOT NULL,
name VARCHAR2 (80) NOT NULL,
text VARCHAR2 (2000) NOT NULL,
comments VARCHAR2 (80),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT standard_response_U_1 UNIQUE (name)
);
ALTER TABLE standard_response
ADD CONSTRAINT standard_response_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE standard_response_seq;
CREATE SEQUENCE standard_response_seq;
CREATE OR REPLACE TRIGGER standard_response_seq_t
before insert on standard_response
for each row
begin
select standard_response_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- queue_standard_response
-----------------------------------------------------------------------------
DROP TABLE queue_standard_response CASCADE CONSTRAINTS;
CREATE TABLE queue_standard_response
(
queue_id NUMBER NOT NULL,
standard_response_id NUMBER NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
-----------------------------------------------------------------------------
-- standard_attachment
-----------------------------------------------------------------------------
DROP TABLE standard_attachment CASCADE CONSTRAINTS;
CREATE TABLE standard_attachment
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (150) NOT NULL,
content_type VARCHAR2 (150) NOT NULL,
content VARCHAR2 (2000) NOT NULL,
filename VARCHAR2 (250) NOT NULL,
comments VARCHAR2 (200),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT standard_attachment_U_1 UNIQUE (name)
);
ALTER TABLE standard_attachment
ADD CONSTRAINT standard_attachment_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE standard_attachment_seq;
CREATE SEQUENCE standard_attachment_seq;
CREATE OR REPLACE TRIGGER standard_attachment_seq_t
before insert on standard_attachment
for each row
begin
select standard_attachment_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- standard_response_attachment
-----------------------------------------------------------------------------
DROP TABLE standard_response_attachment CASCADE CONSTRAINTS;
CREATE TABLE standard_response_attachment
(
id NUMBER NOT NULL,
standard_attachment_id NUMBER NOT NULL,
standard_response_id NUMBER NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE standard_response_attachment
ADD CONSTRAINT standard_response_attachmen_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE standard_response_atch_seq;
CREATE SEQUENCE standard_response_atch_seq;
CREATE OR REPLACE TRIGGER standard_response_atch_seq_t
before insert on standard_response_attachment
for each row
begin
select standard_response_atch_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- auto_response_type
-----------------------------------------------------------------------------
DROP TABLE auto_response_type CASCADE CONSTRAINTS;
CREATE TABLE auto_response_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (50) NOT NULL,
comments VARCHAR2 (80),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT auto_response_type_U_1 UNIQUE (name)
);
ALTER TABLE auto_response_type
ADD CONSTRAINT auto_response_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE auto_response_type_seq;
CREATE SEQUENCE auto_response_type_seq;
CREATE OR REPLACE TRIGGER auto_response_type_seq_t
before insert on auto_response_type
for each row
begin
select auto_response_type_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- auto_response
-----------------------------------------------------------------------------
DROP TABLE auto_response CASCADE CONSTRAINTS;
CREATE TABLE auto_response
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (80) NOT NULL,
text0 VARCHAR2 (2000),
text1 VARCHAR2 (2000),
text2 VARCHAR2 (2000),
type_id NUMBER (5, 0) NOT NULL,
system_address_id NUMBER (5, 0) NOT NULL,
charset VARCHAR2 (80) NOT NULL,
comments VARCHAR2 (100),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT auto_response_U_1 UNIQUE (name)
);
ALTER TABLE auto_response
ADD CONSTRAINT auto_response_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE auto_response_seq;
CREATE SEQUENCE auto_response_seq;
CREATE OR REPLACE TRIGGER auto_response_seq_t
before insert on auto_response
for each row
begin
select auto_response_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- queue_auto_response
-----------------------------------------------------------------------------
DROP TABLE queue_auto_response CASCADE CONSTRAINTS;
CREATE TABLE queue_auto_response
(
id NUMBER NOT NULL,
queue_id NUMBER NOT NULL,
auto_response_id NUMBER NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE queue_auto_response
ADD CONSTRAINT queue_auto_response_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE queue_auto_response_seq;
CREATE SEQUENCE queue_auto_response_seq;
CREATE OR REPLACE TRIGGER queue_auto_response_seq_t
before insert on queue_auto_response
for each row
begin
select queue_auto_response_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- time_accounting
-----------------------------------------------------------------------------
DROP TABLE time_accounting CASCADE CONSTRAINTS;
CREATE TABLE time_accounting
(
id NUMBER (20, 0) NOT NULL,
ticket_id NUMBER (20, 0) NOT NULL,
article_id NUMBER (20, 0),
time_unit NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE time_accounting
ADD CONSTRAINT time_accounting_PK
PRIMARY KEY (id);
CREATE INDEX index_t_acc_ticket_id ON time_accounting (ticket_id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE time_accounting_seq;
CREATE SEQUENCE time_accounting_seq;
CREATE OR REPLACE TRIGGER time_accounting_seq_t
before insert on time_accounting
for each row
begin
select time_accounting_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- sessions
-----------------------------------------------------------------------------
DROP TABLE sessions CASCADE CONSTRAINTS;
CREATE TABLE sessions
(
sessions_id VARCHAR2 (120) NOT NULL,
value VARCHAR2 (2000) NOT NULL,
CONSTRAINT sessions_U_2 UNIQUE (sessions_id)
);
CREATE INDEX index_sessions_id ON sessions (sessions_id);
-----------------------------------------------------------------------------
-- ticket_index
-----------------------------------------------------------------------------
DROP TABLE ticket_index CASCADE CONSTRAINTS;
CREATE TABLE ticket_index
(
ticket_id NUMBER (20, 0) NOT NULL,
queue_id NUMBER NOT NULL,
queue VARCHAR2 (70) NOT NULL,
group_id NUMBER NOT NULL,
s_lock VARCHAR2 (70) NOT NULL,
s_state VARCHAR2 (70) NOT NULL,
create_time_unix NUMBER (20, 0) NOT NULL
);
CREATE INDEX index_ticket_id ON ticket_index (ticket_id);
-----------------------------------------------------------------------------
-- ticket_lock_index
-----------------------------------------------------------------------------
DROP TABLE ticket_lock_index CASCADE CONSTRAINTS;
CREATE TABLE ticket_lock_index
(
ticket_id NUMBER (20, 0) NOT NULL
);
CREATE INDEX ticket_lock_id_index ON ticket_lock_index (ticket_id);
-----------------------------------------------------------------------------
-- customer_user
-----------------------------------------------------------------------------
DROP TABLE customer_user CASCADE CONSTRAINTS;
CREATE TABLE customer_user
(
id NUMBER NOT NULL,
login VARCHAR2 (100) NOT NULL,
email VARCHAR2 (150) NOT NULL,
customer_id VARCHAR2 (200) NOT NULL,
pw VARCHAR2 (50) NOT NULL,
salutation VARCHAR2 (50),
first_name VARCHAR2 (100) NOT NULL,
last_name VARCHAR2 (100) NOT NULL,
comments VARCHAR2 (250) NOT NULL,
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT customer_user_U_1 UNIQUE (login)
);
ALTER TABLE customer_user
ADD CONSTRAINT customer_user_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE customer_user_seq;
CREATE SEQUENCE customer_user_seq;
CREATE OR REPLACE TRIGGER customer_user_seq_t
before insert on customer_user
for each row
begin
select customer_user_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- customer_preferences
-----------------------------------------------------------------------------
DROP TABLE customer_preferences CASCADE CONSTRAINTS;
CREATE TABLE customer_preferences
(
user_id VARCHAR2 (250) NOT NULL,
preferences_key VARCHAR2 (150) NOT NULL,
preferences_value VARCHAR2 (250)
);
CREATE INDEX index_customer_prefs_user_id ON customer_preferences (user_id);
-----------------------------------------------------------------------------
-- ticket_loop_protection
-----------------------------------------------------------------------------
DROP TABLE ticket_loop_protection CASCADE CONSTRAINTS;
CREATE TABLE ticket_loop_protection
(
sent_to VARCHAR2 (250) NOT NULL,
sent_date VARCHAR2 (150) NOT NULL
);
CREATE INDEX index_ticket_l_prot_sent_to ON ticket_loop_protection (sent_to);
CREATE INDEX index_ticket_l_prot_sent_date ON ticket_loop_protection (sent_date);
-----------------------------------------------------------------------------
-- pop3_account
-----------------------------------------------------------------------------
DROP TABLE pop3_account CASCADE CONSTRAINTS;
CREATE TABLE pop3_account
(
id NUMBER NOT NULL,
login VARCHAR2 (200) NOT NULL,
pw VARCHAR2 (200) NOT NULL,
host VARCHAR2 (200) NOT NULL,
queue_id NUMBER NOT NULL,
trusted NUMBER (5, 0) NOT NULL,
comments VARCHAR2 (250),
valid_id NUMBER (5, 0) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE pop3_account
ADD CONSTRAINT pop3_account_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE pop3_account_seq;
CREATE SEQUENCE pop3_account_seq;
CREATE OR REPLACE TRIGGER pop3_account_seq_t
before insert on pop3_account
for each row
begin
select pop3_account_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- search_profile
-----------------------------------------------------------------------------
DROP TABLE search_profile CASCADE CONSTRAINTS;
CREATE TABLE search_profile
(
login VARCHAR2 (200) NOT NULL,
profile_name VARCHAR2 (200) NOT NULL,
profile_key VARCHAR2 (200) NOT NULL,
profile_value VARCHAR2 (200) NOT NULL
);
-----------------------------------------------------------------------------
-- notifications
-----------------------------------------------------------------------------
DROP TABLE notifications CASCADE CONSTRAINTS;
CREATE TABLE notifications
(
id NUMBER NOT NULL,
notification_type VARCHAR2 (200) NOT NULL,
notification_charset VARCHAR2 (60) NOT NULL,
notification_language VARCHAR2 (60) NOT NULL,
subject VARCHAR2 (200) NOT NULL,
text VARCHAR2 (2000) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE notifications
ADD CONSTRAINT notifications_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE notifications_seq;
CREATE SEQUENCE notifications_seq;
CREATE OR REPLACE TRIGGER notifications_seq_t
before insert on notifications
for each row
begin
select notifications_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_item
-----------------------------------------------------------------------------
DROP TABLE faq_item CASCADE CONSTRAINTS;
CREATE TABLE faq_item
(
id NUMBER NOT NULL,
f_name VARCHAR2 (200) NOT NULL,
f_language_id NUMBER (5, 0) NOT NULL,
f_subject VARCHAR2 (200),
state_id NUMBER (5, 0) NOT NULL,
category_id NUMBER (5, 0) NOT NULL,
f_keywords VARCHAR2 (2000),
f_field1 VARCHAR2 (2000),
f_field2 VARCHAR2 (2000),
f_field3 VARCHAR2 (2000),
f_field4 VARCHAR2 (2000),
f_field5 VARCHAR2 (2000),
f_field6 VARCHAR2 (2000),
free_key1 VARCHAR2 (80),
free_value1 VARCHAR2 (200),
free_key2 VARCHAR2 (80),
free_value2 VARCHAR2 (200),
free_key3 VARCHAR2 (80),
free_value3 VARCHAR2 (200),
free_key4 VARCHAR2 (80),
free_value4 VARCHAR2 (200),
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT faq_item_U_1 UNIQUE (f_name)
);
ALTER TABLE faq_item
ADD CONSTRAINT faq_item_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_item_seq;
CREATE SEQUENCE faq_item_seq;
CREATE OR REPLACE TRIGGER faq_item_seq_t
before insert on faq_item
for each row
begin
select faq_item_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_language
-----------------------------------------------------------------------------
DROP TABLE faq_language CASCADE CONSTRAINTS;
CREATE TABLE faq_language
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (200) NOT NULL,
CONSTRAINT faq_language_U_1 UNIQUE (name)
);
ALTER TABLE faq_language
ADD CONSTRAINT faq_language_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_language_seq;
CREATE SEQUENCE faq_language_seq;
CREATE OR REPLACE TRIGGER faq_language_seq_t
before insert on faq_language
for each row
begin
select faq_language_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_history
-----------------------------------------------------------------------------
DROP TABLE faq_history CASCADE CONSTRAINTS;
CREATE TABLE faq_history
(
id NUMBER NOT NULL,
name VARCHAR2 (200) NOT NULL,
item_id NUMBER NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL
);
ALTER TABLE faq_history
ADD CONSTRAINT faq_history_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_history_seq;
CREATE SEQUENCE faq_history_seq;
CREATE OR REPLACE TRIGGER faq_history_seq_t
before insert on faq_history
for each row
begin
select faq_history_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_category
-----------------------------------------------------------------------------
DROP TABLE faq_category CASCADE CONSTRAINTS;
CREATE TABLE faq_category
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (200) NOT NULL,
comments VARCHAR2 (200) NOT NULL,
create_time DATE NOT NULL,
create_by NUMBER NOT NULL,
change_time DATE NOT NULL,
change_by NUMBER NOT NULL,
CONSTRAINT faq_category_U_1 UNIQUE (name)
);
ALTER TABLE faq_category
ADD CONSTRAINT faq_category_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_category_seq;
CREATE SEQUENCE faq_category_seq;
CREATE OR REPLACE TRIGGER faq_category_seq_t
before insert on faq_category
for each row
begin
select faq_category_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_state
-----------------------------------------------------------------------------
DROP TABLE faq_state CASCADE CONSTRAINTS;
CREATE TABLE faq_state
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (200) NOT NULL,
type_id NUMBER NOT NULL,
CONSTRAINT faq_state_U_1 UNIQUE (name)
);
ALTER TABLE faq_state
ADD CONSTRAINT faq_state_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_state_seq;
CREATE SEQUENCE faq_state_seq;
CREATE OR REPLACE TRIGGER faq_state_seq_t
before insert on faq_state
for each row
begin
select faq_state_seq.nextval
into :new.id
from dual;
end;
/
-----------------------------------------------------------------------------
-- faq_state_type
-----------------------------------------------------------------------------
DROP TABLE faq_state_type CASCADE CONSTRAINTS;
CREATE TABLE faq_state_type
(
id NUMBER (5, 0) NOT NULL,
name VARCHAR2 (200) NOT NULL,
CONSTRAINT faq_state_type_U_1 UNIQUE (name)
);
ALTER TABLE faq_state_type
ADD CONSTRAINT faq_state_type_PK
PRIMARY KEY (id);
-- sequence and trigger to fill the ID column:
DROP SEQUENCE faq_state_type_seq;
CREATE SEQUENCE faq_state_type_seq;
CREATE OR REPLACE TRIGGER faq_state_type_seq_t
before insert on faq_state_type
for each row
begin
select faq_state_type_seq.nextval
into :new.id
from dual;
end;
/
I just upgraded my test installation to CVS with these patches
applied, and things seem to work OK.
Regards,
--
Kristoffer.
--- End Message ---
