Hi,
I tried to execute turbine-db2.sql and found quite a few syntax errors -
the author obviously hadn't have the possibility to actually test it on a DB2:
1. DB2 doesn't understand "drop table IF EXISTS", you have to use simply
"drop table" and accept some error messages on first execution - sad but
true...
2. There is no such type as VARCHAR2, VARCHAR is the right keyword.
3. MEDIUMBLOB doesn't exist as well, you have to use BLOB (size), i.e.
BLOB(16777215) in our case.
[QUESTION: do the objectdata columns realy have to be that large, isn't a
BLOB (2^16) big enough?]
4. Key names in DB2 are not allowed to be larger that 18 characters -
that's really strange, but how it is.
5. The latest version of DB2 supports auto-increment fields, keyword
GENERATED ALWAYS AS IDENTITY.
I paste my version of the script below. It's tested and works with DB2 7.1.
Thanks,
ingo.
turbine-db2.sql
==============================================================
-----------------------------------------------------------------------------
-- Please note: early versions of DB2 don't support auto-increment fields
-- (i.e. "GENERATED ALWAYS AS IDENTITY"). With those older versions, an
-- additional ID table has to be used.
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- TURBINE_PERMISSION
-----------------------------------------------------------------------------
drop table TURBINE_PERMISSION;
CREATE TABLE TURBINE_PERMISSION
(
PERMISSIONID INT GENERATED ALWAYS AS IDENTITY,
PERMISSION VARCHAR (99) NOT NULL,
OBJECTDATA BLOB (16777215),
UNIQUE (PERMISSION)
);
ALTER TABLE TURBINE_PERMISSION
ADD CONSTRAINT TRB_PERM_PK
PRIMARY KEY (PERMISSIONID);
-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_ROLE;
CREATE TABLE TURBINE_ROLE
(
ROLEID INT GENERATED ALWAYS AS IDENTITY,
ROLENAME VARCHAR (99) NOT NULL,
OBJECTDATA BLOB (16777215),
UNIQUE (ROLENAME)
);
ALTER TABLE TURBINE_ROLE
ADD CONSTRAINT TRB_RL_PK
PRIMARY KEY (ROLEID);
-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
drop table TURBINE_GROUP;
CREATE TABLE TURBINE_GROUP
(
GROUPID INT GENERATED ALWAYS AS IDENTITY,
GROUPNAME VARCHAR (99) NOT NULL,
OBJECTDATA BLOB (16777215),
UNIQUE (GROUPNAME)
);
ALTER TABLE TURBINE_GROUP
ADD CONSTRAINT TRB_GRP_PK
PRIMARY KEY (GROUPID);
-----------------------------------------------------------------------------
-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
drop table TURBINE_ROLE_PERMISSION;
CREATE TABLE TURBINE_ROLE_PERMISSION
(
ROLEID INT NOT NULL,
PERMISSIONID INT NOT NULL
);
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TRB_RL_PERM_PK
PRIMARY KEY (ROLEID,PERMISSIONID);
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TRB_RL_PERM_FK1 FOREIGN KEY (ROLEID)
REFERENCES TURBINE_ROLE (ROLEID);
ALTER TABLE TURBINE_ROLE_PERMISSION
ADD CONSTRAINT TRB_RL_PERM_FK2 FOREIGN KEY (PERMISSIONID)
REFERENCES TURBINE_PERMISSION (PERMISSIONID);
-----------------------------------------------------------------------------
-- TURBINE_USER
-----------------------------------------------------------------------------
drop table TURBINE_USER;
CREATE TABLE TURBINE_USER
(
USERID INT GENERATED ALWAYS AS IDENTITY,
LOGINID VARCHAR (32) NOT NULL,
PASSWORD_VALUE VARCHAR (32) NOT NULL,
FIRST_NAME VARCHAR (99) NOT NULL,
LAST_NAME VARCHAR (99) NOT NULL,
ADDRESS1 VARCHAR (255),
ADDRESS2 VARCHAR (255),
CITY VARCHAR (255),
STATE VARCHAR (32),
POSTALCODE VARCHAR (32),
COUNTRY VARCHAR (99),
CITIZENSHIP VARCHAR (32),
PHONE VARCHAR (32),
ALTPHONE VARCHAR (32),
FAX VARCHAR (32),
CELL VARCHAR (32),
PAGER VARCHAR (32),
EMAIL VARCHAR (99),
MODIFIED DATE,
CREATED DATE,
LASTLOGIN DATE,
PREFIX_NAME VARCHAR (16),
MIDDLE_NAME VARCHAR (99),
SUFFIX_NAME VARCHAR (16),
COMPANY VARCHAR (255),
CONFIRM_VALUE VARCHAR (99),
OBJECTDATA BLOB (16777215),
UNIQUE (LOGINID)
);
ALTER TABLE TURBINE_USER
ADD CONSTRAINT TRB_USR_PK
PRIMARY KEY (USERID);
ALTER TABLE TURBINE_USER
ADD CONSTRAINT TRB_USR_FK1 FOREIGN KEY (USERID)
REFERENCES TURBINE_USER (USERID);
-----------------------------------------------------------------------------
-- TURBINE_USER_GROUP_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_USER_GROUP_ROLE;
CREATE TABLE TURBINE_USER_GROUP_ROLE
(
USERID INT NOT NULL,
GROUPID INT NOT NULL,
ROLEID INT NOT NULL
);
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TRB_USR_GRP_RL_PK
PRIMARY KEY (USERID,GROUPID,ROLEID);
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TRB_USR_GRP_RL_FK1 FOREIGN KEY (USERID)
REFERENCES TURBINE_USER (USERID);
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TRB_USR_GRP_RL_FK2 FOREIGN KEY (GROUPID)
REFERENCES TURBINE_GROUP (GROUPID);
ALTER TABLE TURBINE_USER_GROUP_ROLE
ADD CONSTRAINT TRB_USR_GRP_RL_FK3 FOREIGN KEY (ROLEID)
REFERENCES TURBINE_ROLE (ROLEID);
-----------------------------------------------------------------------------
-- TURBINE_JOBENTRY
-----------------------------------------------------------------------------
drop table TURBINE_JOBENTRY;
CREATE TABLE TURBINE_JOBENTRY
(
JOBID INT NOT NULL,
SECOND INT default -1 NOT NULL,
MINUTE INT default -1 NOT NULL,
HOUR INT default -1 NOT NULL,
WEEKDAY INT default -1 NOT NULL,
DAY_OF_MONTH INT default -1 NOT NULL,
TASK VARCHAR (99) NOT NULL,
EMAIL VARCHAR (99)
);
ALTER TABLE TURBINE_JOBENTRY
ADD CONSTRAINT TRB_JOBENTRY_PK
PRIMARY KEY (JOBID);
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
