|
Some of the Oracle scripts are incorrect, I posted an
update to the list but these don't seem to have been
implemented.
These are upto date with the latest version of
Turbine/Jetspeed.
Mark.
Here are the changed scripts
<<<BEGIN
--
Copyright (c) 1997-1999 The Java Apache Project. All rights
reserved.
-- -- File: <Jyve_root>\docs\oratables_schema.sql -- -- Synopsis: -- On Unix: sqlplus userid/password @oratables_schema -- sqlplus userid/password@sid @oratables_schema -- -- Tables: -- Tables used by Turbine -- permission -- userrole -- rolepermission -- visitor -- visitorrole -- -- Tables used by Jyve: -- answer -- question -- topic -- faq -- project -- visitor -- -- Turbine Notes: a Servlet Framework for building Dynamic Websites -- Copyright (C) 1999 Jon S. Stevens Brett McLaughlin -- -- This is a schema for Oracle databases to support -- the concept of Users/Roles/Permissions in turbine. -- -- Please see the Html documentation in <Turbine_root>\docs for more information -- on this SQL. Equivalent contributions of this for other -- databases are welcome. -- -- @author Jon S. Stevens -- @author Brett McLaughlin -- -- Redistribution and use in source and binary forms, with or without -- modification, are permitted provided that the following conditions -- are met: -- -- 1. Redistributions of source code must retain the above copyright -- notice, this list of conditions and the following disclaimer. -- -- 2. Redistributions in binary form must reproduce the above copyright -- notice, this list of conditions and the following disclaimer in -- the documentation and/or other materials provided with the -- distribution. -- -- 3. All advertising materials mentioning features or use of this -- software must display the following acknowledgment: -- "This product includes software developed by the Java Apache -- Project for use in the Apache JServ servlet engine project -- <http://java.apache.org/>." -- -- 4. The names "Apache JServ", "Apache JServ Servlet Engine", "Jyve", -- "Apache Jyve", "Jyve Project", "Apache Jyve Project" and -- "Java Apache Project" must not be used to endorse or promote products -- derived from this software without prior written permission. -- -- 5. Products derived from this software may not be called "Apache JServ" -- nor may "Apache" nor "Apache JServ" appear in their names without -- prior written permission of the Java Apache Project. -- -- 6. Redistributions of any form whatsoever must retain the following -- acknowledgment: -- "This product includes software developed by the Java Apache -- Project for use in the Apache JServ servlet engine project -- <http://java.apache.org/>." -- -- THIS SOFTWARE IS PROVIDED BY THE JAVA APACHE PROJECT "AS IS" AND ANY -- EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE -- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR -- PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE JAVA APACHE PROJECT OR -- ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, -- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT -- NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; -- LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) -- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, -- STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) -- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED -- OF THE POSSIBILITY OF SUCH DAMAGE. -- -- This software consists of voluntary contributions made by many -- individuals on behalf of the Java Apache Group. For more information -- on the Java Apache Project and the Apache JServ Servlet Engine project, -- please see <http://java.apache.org/>. -- -- Modification History -- 01/01/2000 [EMAIL PROTECTED], Combined table creation processes for Jyve and Turbine -- for Oracle database. -- Original Files used to create this file: -- Turbine\docs\default_roles_permissions.sql -- Turbine\docs\oracle_users_roles_permissions.sql -- Jyve\docs\tables.schema -- 10/08/2000 [EMAIL PROTECTED] -- Modifications :- -- Add new sequences -- Add Before Insert triggers for the new sequences -- Added some primary keys and unique constraints removed in the MySQL -- translation. -- Add PROJECT_ID to faq table as per MySQL schema -- Removed VISITORID from Visitor Primary Key DROP TABLE
answer cascade constraints;
DROP TABLE question cascade constraints; DROP TABLE topic cascade constraints; DROP TABLE faq cascade constraints; DROP TABLE project cascade constraints; drop table Visitor cascade constraints; ------------------------------------- DROP SEQUENCE PERMISSION_SEQUENCE; CREATE SEQUENCE PERMISSION_SEQUENCE start with 1; ------------------------------------- DROP TABLE PERMISSION cascade constraints; CREATE TABLE PERMISSION ( PERMISSIONID INT NOT NULL, PERMISSION VARCHAR2(99) NOT NULL); ALTER TABLE
PERMISSION
ADD CONSTRAINT PERMISSION_PK PRIMARY KEY (PERMISSIONID); CREATE
UNIQUE INDEX PERMISSION_NAME_IDX ON PERMISSION(PERMISSION);
-- Add some
default permissions
insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'add_user'); insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'modify_user'); insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'add_group'); insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'modify_group'); insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'add_permission'); insert into Permission (permissionid, PERMISSION) values (permission_sequence.nextval, 'modify_permission'); -------------------------------------
DROP SEQUENCE ROLE_SEQUENCE; CREATE SEQUENCE ROLE_SEQUENCE start with 1; ------------------------------------- DROP TABLE USERROLE cascade constraints; CREATE TABLE USERROLE ( ROLEID INT NOT NULL, ROLENAME VARCHAR2(99) NOT NULL ); ALTER TABLE
USERROLE
ADD CONSTRAINT ROLE_PK PRIMARY KEY (ROLEID); --CREATE
UNIQUE INDEX ROLE_NAME ON USERROLE(ROLENAME);
-- Create a
Role or Group in this case turbine root
insert into UserRole (roleid, ROLENAME) values (role_sequence.nextval, 'turbine_root'); -------------------------------------
DROP TABLE ROLEPERMISSION cascade constraints; CREATE TABLE ROLEPERMISSION ( ROLEID INT NOT NULL, PERMISSIONID INT NOT NULL ); ALTER TABLE
ROLEPERMISSION
ADD CONSTRAINT ROLEPERMISSION_PK PRIMARY KEY (ROLEID, PERMISSIONID); -- Add some
permissions for 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'; -------------------------------------
DROP TABLE VISITORROLE cascade constraints; CREATE TABLE VISITORROLE ( VISITORID INT NOT NULL, ROLEID INT NOT NULL ); ALTER TABLE
VISITORROLE
ADD CONSTRAINT VISITORROLE_PK PRIMARY KEY (VISITORID, ROLEID); -------------------------------------
create table Visitor( VISITORID int not null, LOGINID varchar2 (32) NOT NULL UNIQUE, PASSWORD_VALUE varchar2 (32), CONFIRM_VALUE varchar2 (32), FIRST_NAME varchar2 (99) NOT NULL, LAST_NAME varchar2 (99) NOT NULL, ADDRESS1 varchar2 (255), ADDRESS2 varchar2 (255), CITY varchar2 (255), STATE varchar2 (32), POSTALCODE varchar2 (32), COUNTRY varchar2 (99), CITIZENSHIP varchar2 (32), PHONE varchar2 (32), ALTPHONE varchar2 (32), FAX varchar2 (32), CELL varchar2 (32), PAGER varchar2 (32), EMAIL varchar2 (99), DELETED char (1) default 'N' check (DELETED in ('Y','N')), -- DEFAULT 'N', MODIFIED date, CREATED date, LASTLOGIN date, OBJECTDATA long raw ); ALTER TABLE
VISITOR
ADD CONSTRAINT VISITOR_PK PRIMARY KEY (LOGINID); drop
sequence visitor_sequence;
create sequence visitor_sequence start with 1; insert into
Visitor (visitorid, LOGINID, FIRST_NAME, LAST_NAME,
PASSWORD_VALUE, MODIFIED, CREATED, LASTLOGIN, CONFIRM_VALUE ) values (visitor_sequence.nextval, 'Anonymous Guest', 'Anonymous', 'Guest', '', null, null, null, 'CONFIRMED' ); INSERT INTO
VISITOR
(VISITORID, LOGINID, PASSWORD_VALUE, FIRST_NAME, LAST_NAME) VALUES (VISITOR_SEQUENCE.NEXTVAL, 'turbine', 'turbine', 'system', 'adminstrator' ); -- Finally,
add the user turbine to the turbine_root group
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.loginid = 'turbine' AND UserRole.rolename = 'turbine_root'; -------------------------------------
CREATE TABLE answer ( --ANSWER_ID int(11) DEFAULT '0' NOT NULL auto_increment, ANSWER_ID integer not null, QUESTION_ID integer not null, ANSWER_VALUE varchar2(2000) NOT NULL, VISITORID integer default 1, DISPLAY_ORDER integer not null, DELETED char(1) default 'N' check (DELETED in ('Y','N')), --enum ('Y', 'N') NOT NULL DEFAULT 'N', SAVETYPE char(1) default 'H' check (savetype in ('H','P')), --enum ('H', 'P') NOT NULL DEFAULT 'H', DELETED_BY integer, MODIFIED_BY integer, MODIFIED_DATE date --INDEX ( ANSWER_ID, QUESTION_ID ), --PRIMARY KEY (ANSWER_ID, QUESTION_ID) ); ALTER TABLE
answer
ADD CONSTRAINT answer_pk PRIMARY KEY (answer_id,question_id); drop
sequence answer_sequence;
create sequence answer_sequence start with 1; insert into
answer (answer_id, ANSWER_VALUE, QUESTION_ID, DISPLAY_ORDER )
values (answer_sequence.nextval, 'It is a servlet engine.', 1, 1 ); insert into
answer (answer_id, ANSWER_VALUE, QUESTION_ID, DISPLAY_ORDER )
values (answer_sequence.nextval, 'It is a web application development environment.', 2, 2 ); insert into
answer (answer_id, ANSWER_VALUE, QUESTION_ID, DISPLAY_ORDER )
values (answer_sequence.nextval, 'Version 2.0 only.', 3, 3 ); insert into
answer (answer_id, ANSWER_VALUE, QUESTION_ID, DISPLAY_ORDER )
values (answer_sequence.nextval, 'Future versions of Jakarta will support the latest versions', 3, 4 ); ------------------------------------- CREATE TABLE question ( QUESTION_ID integer not null, -- int(11) DEFAULT '0' NOT NULL auto_increment, TOPIC_ID integer not null, QUESTION_VALUE varchar2(2000) NOT NULL, VISITORID integer default 1, -- DEFAULT '1', DISPLAY_ORDER integer default 0, DELETED char(1) default 'N' check (DELETED in ('Y','N')), DELETED_BY integer, MODIFIED_BY integer, MODIFIED_DATE date --INDEX ( QUESTION_ID, TOPIC_ID ), --PRIMARY KEY (QUESTION_ID, TOPIC_ID) ); ALTER TABLE
question
ADD CONSTRAINT question_pk PRIMARY KEY (question_id,topic_id); drop
sequence question_sequence;
create sequence question_sequence start with 1; insert into
question (question_id, QUESTION_VALUE, TOPIC_ID, DISPLAY_ORDER )
values (question_sequence.nextval, 'What is Apache JServ?', 1, 1 ); insert into
question (question_id, QUESTION_VALUE, TOPIC_ID, DISPLAY_ORDER )
values (question_sequence.nextval, 'What is Apache Turbine?', 2, 2 ); insert into
question (question_id, QUESTION_VALUE, TOPIC_ID, DISPLAY_ORDER )
values (question_sequence.nextval, 'What version of the JSDK does it support?', 1, 3 ); insert into
question (question_id, QUESTION_VALUE, TOPIC_ID, DISPLAY_ORDER )
values (question_sequence.nextval, 'How do I map a servlet to / ?', 4, 4 ); -------------------------------------
CREATE TABLE topic ( TOPIC_ID integer not null, TOPIC_VALUE varchar2(2000) not null, FAQ_ID int NOT NULL, VISITORID integer default 1, DISPLAY_ORDER integer default 0, DELETED char(1) default 'N' check (DELETED in ('Y','N')), DELETED_BY integer, MODIFIED_BY integer, MODIFIED_DATE date --INDEX ( TOPIC_ID, FAQ_ID ), --PRIMARY KEY (TOPIC_ID, FAQ_ID) ); ALTER TABLE
topic
ADD CONSTRAINT topic_pk PRIMARY KEY (topic_id,faq_id); drop
sequence topic_sequence;
create sequence topic_sequence start with 1; insert into
topic (topic_id, TOPIC_VALUE, FAQ_ID, DISPLAY_ORDER)
values (topic_sequence.nextval, 'General Questions', 1, 1 ); insert into
topic (topic_id, TOPIC_VALUE, FAQ_ID, DISPLAY_ORDER)
values (topic_sequence.nextval, 'General Questions', 2, 2 ); insert into
topic (topic_id, TOPIC_VALUE, FAQ_ID, DISPLAY_ORDER)
values (topic_sequence.nextval, 'General Questions', 3, 3 ); insert into
topic (topic_id, TOPIC_VALUE, FAQ_ID, DISPLAY_ORDER)
values (topic_sequence.nextval, 'Configuration Questions', 1, 4 ); ------------------------------------- CREATE TABLE faq ( FAQ_ID integer not null, --int(11) DEFAULT '0' NOT NULL auto_increment, FAQ_VALUE varchar2(2000) not null, PROJECT_ID integer NOT NULL, VISITORID integer DEFAULT 1, DISPLAY_ORDER integer default 0, DELETED char(1) default 'N' check (DELETED in ('Y','N')), DELETED_BY integer, MODIFIED_BY integer, MODIFIED_DATE date --INDEX ( FAQ_ID, PROJECT_ID ), --PRIMARY KEY (FAQ_ID, PROJECT_ID) ); ALTER TABLE
faq
ADD CONSTRAINT faq_pk PRIMARY KEY (faq_id, project_id); drop
sequence faq_sequence;
create sequence faq_sequence start with 1; insert into
faq (faq_id, FAQ_VALUE, PROJECT_ID, DISPLAY_ORDER)
values (faq_sequence.nextval, 'Apache JServ', 1, 1 ); insert into
faq (faq_id, FAQ_VALUE, PROJECT_ID, DISPLAY_ORDER)
values (faq_sequence.nextval, 'Apache Turbine', 1, 2 ); insert into
faq (faq_id, FAQ_VALUE, PROJECT_ID, DISPLAY_ORDER)
values (faq_sequence.nextval, 'Apache Tomcat', 2, 3 ); ------------------------------------- CREATE TABLE project ( PROJECT_ID integer, --auto_increment, PROJECT_VALUE varchar2(2000) not null, --text NOT NULL, VISITORID integer DEFAULT 1, DISPLAY_ORDER integer DEFAULT 0, DELETED char(1) default 'N' check (DELETED in ('Y','N')), DELETED_BY integer, MODIFIED_BY integer, MODIFIED_DATE date --INDEX ( PROJECT_ID ), --PRIMARY KEY (PROJECT_ID) ); ALTER TABLE
project
ADD CONSTRAINT project_pk PRIMARY KEY (project_id); drop
sequence project_sequence;
create sequence project_sequence start with 1; insert into
project (project_id, PROJECT_VALUE, DISPLAY_ORDER)
values (project_sequence.nextval, 'Java Apache Project', 1 ); insert into
project (project_id, PROJECT_VALUE, DISPLAY_ORDER)
values (project_sequence.nextval, 'Jakarta Project', 2 ); --
PROMPT
Creating Index 'ROLE_NAME'
CREATE UNIQUE INDEX ROLE_NAME ON USERROLE (ROLENAME) / PROMPT
Creating Index 'VISITOR_LOGINID'
CREATE INDEX VISITOR_LOGINID ON VISITOR ( VISITORID, LOGINID ) / PROMPT
Creating Sequence 'NEXT_ROLEID'
CREATE SEQUENCE NEXT_ROLEID / PROMPT
Creating Trigger 'BFI_VISITOR'
CREATE OR REPLACE TRIGGER BFI_VISITOR BEFORE INSERT ON VISITOR FOR EACH ROW BEGIN SELECT visitor_sequence.nextval INTO :new.visitorid FROM dual; END BFI_VISITOR; / PROMPT
Creating Trigger 'BFI_PERMISSION'
CREATE OR REPLACE TRIGGER BFI_PERMISSION BEFORE INSERT ON PERMISSION FOR EACH ROW BEGIN SELECT PERMISSION_SEQUENCE.nextval INTO :new.permissionid FROM dual; / PROMPT
Creating Trigger 'BFI_USERROLE'
CREATE OR REPLACE TRIGGER BFI_USERROLE BEFORE INSERT ON USERROLE FOR EACH ROW BEGIN SELECT next_roleid.nextval INTO :new.roleid FROM dual; END BFI_USERROLE; / <<<END
Hi
all, When I’m
updating the user account it gives me the error
below.: Screen=Home Action=UpdateAccount Submit2=Update
Account Nextscreen=Home I
have Oracle 8i, any problems with it? <-------------------------------------------------------- com.workingdogs.village.DataSetException: Column name: VISITORID does not exist! at com.workingdogs.village.Schema.index(Schema.java, Compiled Code) at com.workingdogs.village.Schema.index(Schema.java, Compiled Code) at com.workingdogs.village.Record.getUpdateSaveString(Record.java, Compiled Code) at com.workingdogs.village.Record.getSaveString(Record.java:522) at com.workingdogs.village.Record.saveWithUpdate(Record.java, Compiled Code) at com.workingdogs.village.Record.save(Record.java:187) at com.workingdogs.village.Record.save(Record.java:166) at org.apache.jyve.actions.UpdateAccount.doPerform(UpdateAccount.java:201) at org.apache.turbine.modules.Action.perform(Action.java:77) at org.apache.turbine.modules.ActionLoader.exec(ActionLoader.java:111) at org.apache.turbine.modules.pages.DefaultPage.doBuild(DefaultPage.java:79) at org.apache.turbine.modules.Page.build(Page.java:81) at org.apache.turbine.modules.PageLoader.exec(PageLoader.java:111) at Turbine.doGet(Turbine.java, Compiled Code) at Turbine.doPost(Turbine.java:368) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java, Compiled Code) at org.apache.tomcat.core.ContextManager.service(ContextManager.java:559) at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java, Compiled Code) at org.apache.tomcat.service.TcpConnectionThread.run(SimpleTcpEndpoint.java, Compiled Code) at java.lang.Thread.run(Thread.java, Compiled Code) ---------------------------------------------� Thanks for all saludos, Sergio Rivas Comercio Electr�nico B2B
2.000 |
- UpdateAccount Sergio Rivas
- Re: UpdateAccount Mark White
- Re: UpdateAccount burtonator
