> on 4/19/00 2:45 PM, Lacerda, Wellington (AFIS)
<[EMAIL PROTECTED]>
> wrote:
>
> > Hi jon,
> >
> > I have a doubt about the schema to generate in the database to provide
> > authentication and other services via Turbine. I have Jetspeed
configured
> > and running, I use a DB2 database, that I set correctly, but how to
generate
> > the schema ? is there a class to do this ?
> > where is the database shema anyway ?
> >
> >
> > Sorry if you already answered something similar.
> >
> > My config:
> >
> > Nt4 sp6
> > JRun 2.3.3
> > JDK 1.2.2
> >
> > Wellington
>
> We don't have a schema for DB2. The existing schema's for other databases
> are in the Turbine docs/ directory. Feel free to try to convert one of
them
> over...you are probably better off switching to something like MySQL
> though...
>
Hi,
I have schema for DB2 which I include here. I had to modify Turbine slightly
to get DB2 working.
Do following to org.apache.turbine.om.peer.BasePeer
doDelete(Criteria) and doUpdate(Criteria,Criteria) methods
finally clause:
replace
----------
if (tds != null) tds.close();
----------
with
----------
try {
if (tds != null) tds.close();
}
catch(SQLException se) {
//Log.error(se);
}
----------
For some reason DB2 throws exception on tds.close().
Lassi Immonen
[EMAIL PROTECTED]
----------------------------
db2_turbine_schema.sql
----------------------------
-- usage: db2 -v -tf db2_turbine_scheme.sql
drop table PERMISSION;
create table PERMISSION ( PERMISSIONID integer not null with default,
PERMISSION varchar(99) not null );
alter table PERMISSION
add constraint PERMISSION_PK primary key (PERMISSIONID);
create unique index PERMISSION_NAME on PERMISSION(PERMISSION);
--create trigger PERMISSION_TRIGGER
--no cascade before insert on PERMISSION
--referencing NEW as NE
--for each row mode DB2SQL
--set (NE.PERMISSIONID) =
--(select COALESCE(MAX(PERMISSIONID),0) + 1 from PERMISSION);
drop table USERROLE;
create table USERROLE (ROLEID integer not null with default,
ROLENAME varchar(99) not null);
alter table USERROLE
add constraint ROLE_PK primary key (ROLEID);
create unique index ROLE_NAME on USERROLE(ROLENAME);
--create trigger USERROLE_TRIGGER
--no cascade before insert on USERROLE
--referencing NEW as NE
--for each row mode DB2SQL
--set (NE.ROLEID) =
--(select COALESCE(MAX(ROLEID),0) + 1 from USERROLE);
drop table ROLEPERMISSION;
create table ROLEPERMISSION ( ROLEID integer not null,
PERMISSIONID integer not null);
alter table ROLEPERMISSION add constraint
ROLEPERMISSION_PK primary key (ROLEID,PERMISSIONID);
drop table VISITOR;
create table VISITOR (VISITORID integer not null with default,
LOGINID varchar(32) not null,
PASSWORD_VALUE varchar(32) not null,
FIRST_NAME varchar(99) not null,
LAST_NAME varchar(99) not null,
PREFIX_NAME varchar (16),
MIDDLE_NAME varchar (99),
SUFFIX_NAME varchar (16),
COMPANY varchar (255),
ADDRESS1 varchar(32),
ADDRESS2 varchar(32),
CITY varchar(32),
STATE varchar(32),
POSTALCODE varchar(32),
COUNTRY varchar(32),
CITIZENSHIP varchar(32),
PHONE varchar(32),
ALTPHONE varchar(32),
FAX varchar(32),
CELL varchar(32),
PAGER varchar(32),
EMAIL varchar(99),
MODIFIED timestamp,
CREATED timestamp,
LASTLOGIN timestamp,
OBJECTDATA blob(10K) );
alter table VISITOR
add constraint VISITOR_PK primary key (VISITORID);
create unique index VISITOR_LOGINID on VISITOR(LOGINID);
--create trigger VISITOR_TRIGGER
--no cascade before insert on VISITOR
--referencing NEW as NE
--for each row mode DB2SQL
--set (NE.VISITORID) =
--(select COALESCE(MAX(VISITORID),0) + 1 from VISITOR);
insert into VISITOR (VISITORID,LOGINID,PASSWORD_VALUE,
FIRST_NAME,LAST_NAME)
values (1,'turbine','turbine','system','adminstrator');
drop table VISITORROLE;
create table VISITORROLE (VISITORID integer not null,
ROLEID integer not null);
alter table VISITORROLE
add constraint VISITORROLE_PK primary key (VISITORID, ROLEID);
-- default roles & permissions
insert into PERMISSION (PERMISSIONID,PERMISSION) values (1,'view_user');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (2,'add_user');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (3,'modify_user');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (4,'view_group');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (5,'add_group');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (6,'modify_group');
insert into PERMISSION (PERMISSIONID,PERMISSION) values
(7,'view_permission');
insert into PERMISSION (PERMISSIONID,PERMISSION) values
(8,'add_permission');
insert into PERMISSION (PERMISSIONID,PERMISSION) values
(9,'modify_permission');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (10,'view_role');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (11,'add_role');
insert into PERMISSION (PERMISSIONID,PERMISSION) values (12,'modify_role');
insert into USERROLE (ROLEID,ROLENAME) values (1,'turbine_root');
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'view_user' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'view_group' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'view_permission' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'view_role' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'add_role' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'modify_role' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'add_user' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'modify_user' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'add_group' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'modify_group' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'add_permission' and
USERROLE.ROLENAME = 'turbine_root';
insert into ROLEPERMISSION (ROLEID,PERMISSIONID)
select USERROLE.ROLEID, PERMISSION.PERMISSIONID
from USERROLE, PERMISSION
where PERMISSION.PERMISSION = 'modify_permission' and
USERROLE.ROLENAME = 'turbine_root';
insert into VISITORROLE (VISITORID,ROLEID)
select VISITOR.VISITORID, USERROLE.ROLEID
from VISITOR, USERROLE
where VISITOR.LOGINID = 'turbine' and
USERROLE.ROLENAME = 'turbine_root';
-- job entry
drop table JOBENTRY;
create table JOBENTRY (
OID integer not null primary key,
MINUTE integer default -1 not null,
HOUR integer default -1 not null,
WEEKDAY integer default -1 not null,
DAY_OF_MONTH integer default -1 not null,
TASK varchar(99) not null,
EMAIL varchar(99)
);
-- id table
drop table ID_TABLE;
create table ID_TABLE (
ID_TABLE_ID integer not null,
TABLE_NAME varchar(255) not null,
NEXT_ID integer,
QUANTITY integer,
primary key (ID_TABLE_ID),
unique (TABLE_NAME)
);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (1, 'Permission', 7, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (2, 'UserRole', 2, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (3, 'Visitor', 2, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (4, 'Jobentry', 5, 10);
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Problems?: [EMAIL PROTECTED]
