4 Replies Latest reply on Aug 13, 2003 8:13 PM by Dave Gubrud

    Nukes on Oracle 8

    Dave Gubrud Newbie

      Has anyone tried Nukes on Oracle 8? If so do you have a setup.ddl for oracle?

        • 1. Re: Nukes on Oracle 8
          ebratton Newbie

          This is what I used, but I'm still working out some kinks...



          DROP SEQUENCE nuke_sequence;
          DROP TABLE nuke_users;
          DROP TABLE nuke_group_membership;
          DROP TABLE nuke_groups;
          DROP TABLE nuke_file;
          DROP TABLE nuke_services;
          DROP TABLE nuke_services_attributes;





          CREATE SEQUENCE nuke_sequence;


          CREATE TABLE nuke_users (
          pn_uid NUMBER(11) NOT NULL,
          pn_uname VARCHAR2(25) NOT NULL,
          pn_user_regdate DATE,
          pn_user_viewemail NUMBER(1),
          pn_name VARCHAR2(60) DEFAULT '' ,
          pn_email VARCHAR2(60) DEFAULT '' ,
          pn_femail VARCHAR2(60) DEFAULT '' ,
          pn_url VARCHAR2(254) DEFAULT '' ,
          pn_timezone_offset NUMBER(4) DEFAULT '0' ,
          pn_user_avatar VARCHAR2(30) DEFAULT 'blank.gif' ,
          pn_user_icq VARCHAR2(15) DEFAULT '' ,
          pn_user_aim VARCHAR2(18) DEFAULT '' ,
          pn_user_yim VARCHAR2(25) DEFAULT '' ,
          pn_user_msnm VARCHAR2(25) DEFAULT '' ,
          pn_user_from VARCHAR2(100) DEFAULT '' ,
          pn_user_lang NUMBER(3) DEFAULT '0' ,
          pn_user_occ VARCHAR2(100) DEFAULT '' ,
          pn_user_intrest VARCHAR2(150) DEFAULT '' ,
          pn_user_sig VARCHAR2(255) DEFAULT '' ,
          pn_bio VARCHAR2(255) DEFAULT '' ,
          pn_pass VARCHAR2(40) DEFAULT '' ,
          pn_theme VARCHAR2(255) DEFAULT '' ,
          pn_storynum NUMBER(4) DEFAULT '10' ,
          pn_ublockon NUMBER(1),
          pn_ublock CLOB DEFAULT '' ,
          pn_umode VARCHAR2(10) DEFAULT '' ,
          pn_uorder NUMBER(1) DEFAULT '0' ,
          pn_thold NUMBER(1) DEFAULT '0' ,
          pn_noscore NUMBER(1),
          pn_commentmax NUMBER(11) DEFAULT '4096' ,
          PRIMARY KEY (pn_uid)
          );


          CREATE TABLE nuke_group_membership (
          pn_gid NUMBER(11),
          pn_uid NUMBER(11),
          PRIMARY KEY (pn_uid, pn_gid)
          );


          CREATE TABLE nuke_groups (
          pn_gid NUMBER(11),
          pn_name VARCHAR2(255) NOT NULL,
          PRIMARY KEY (pn_gid)
          );





          CREATE TABLE nuke_services (
          pn_sid VARCHAR2(255) NOT NULL,
          pn_last_modified NUMBER(19,0) NOT NULL,
          pn_class_name VARCHAR2(255) NOT NULL,
          PRIMARY KEY(pn_sid)
          );


          CREATE TABLE nuke_services_attributes (
          pn_sid VARCHAR2(128) NOT NULL,
          pn_aid VARCHAR2(128) NOT NULL,
          pn_value CLOB DEFAULT NULL,
          PRIMARY KEY(pn_sid, pn_aid)
          );



          CREATE TABLE nuke_file (
          pn_id NUMBER(11),
          pn_fid VARCHAR2(255) NOT NULL,
          pn_uid NUMBER(11) NOT NULL,
          pn_content_type VARCHAR2(50) NOT NULL,
          pn_content BLOB NOT NULL,
          pn_timest DATE NOT NULL,
          pn_public NUMBER(11) DEFAULT '0' NOT NULL,
          PRIMARY KEY(pn_id)
          );



          INSERT INTO nuke_users (pn_uid,pn_uname,pn_email,pn_user_viewemail,pn_pass) VALUES (1,'admin','admin@none.com',1,'21232F297A57A5A743894A0E4A801FC3');
          INSERT INTO nuke_users (pn_uid,pn_uname,pn_email,pn_user_viewemail,pn_pass) VALUES (2,'user','user@none.com',1,'EE11CBB19052E40B07AAC0CA060C23EE');
          INSERT INTO nuke_groups (pn_gid,pn_name) VALUES (1,'Admins');
          INSERT INTO nuke_groups (pn_gid,pn_name) VALUES (2,'Users');
          INSERT INTO nuke_group_membership (pn_gid, pn_uid) VALUES (1,1);
          INSERT INTO nuke_group_membership (pn_gid, pn_uid) VALUES (2,2);
          INSERT INTO NUKE_FILE VALUES(0,'index',1,'text/html','3c703e4e756b6573206973206120436f6e74656e74204d616e6167656d656e742053797374656d206d6f64656c656420616674657220506f73744e756b65207769746820616476616e63656420757365722f67726f7570206d616e6167656d656e742c2073656375726974792072756c657320616e6420706c75676761626c6520636f6d706f6e656e74732e3c2f703e0d0a0d0a3c703e4974206973206120706f7274206f66207468652066616d6f75732050485020626173656420434d5320506f73746e756b652e20556e6c696b6520506f73746e756b652c204e756b657320697320616c6c207772697474656e20696e20746865206a6176612070726f6772616d6d696e670d0a6c616e677561676520616e64206973206261736564206f6e20746865204a32454520706c6174666f726d20616e6420746865204a426f7373206170706c69636174696f6e207365727665722c207573696e67206561636820746563686e6f6c6f677920666f722074686520626573740d0a6f6620776861742069742067697665732e3c2f703e0d0a0d0a3c7020616c69676e3d2763656e746572273e4665617475726564206d6f64756c657320696e20746869732072656c656173653c703e0d0a0d0a3c7461626c6520626f726465723d2231222063656c6c73706163696e673d2230222077696474683d223634302220626f72646572636f6c6f723d226c69676874677265792220616c69676e3d2263656e746572223e0d0a202020203c74723e0d0a202020203c74683e6d6f64756c65206e616d653c2f74683e0d0a202020203c74683e6465736372697074696f6e3c2f74683e0d0a202020203c74683e504850206f726967696e3c2f74683e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e636f72653c2f74683e0d0a202020203c74643e636f72652066756e6374696f6e6e616c6974696573203a20636f6d706f6e656e74206d616e6167656d656e7420616e642073656375726974793c2f74643e0d0a202020203c74643e706e415049202b20706e4d6f643c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e757365723c2f74683e0d0a202020203c74643e75736572206d616e6167656d656e743c2f74643e0d0a202020203c74643e706e55736572202b206d6f64756c65204e532d4e6577557365723c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e796f7572206163636f756e743c2f74683e0d0a202020203c74643e757365722073657474696e67733c2f74643e0d0a202020203c74643e6d6f64756c65204e532d596f75724163636f756e743c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e68746d6c3c2f74683e0d0a202020203c74643e616c6c2074686520746f6f6c7320656e61626c696e6720636f6e74656e74206372656174696f6e2c20737570706f72747320706167652076657273696f6e696e672c207765206c6f76652069743c2f74643e0d0a202020203c74643e6d6f64756c652048746d6c3c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e6a6f75726e616c3c2f74683e0d0a202020203c74643e6f75722066617368696f6e207472656e6420626c6f673c2f74643e0d0a202020203c74643e6d6f64756c65206a6f75726e616c3c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e67726f7570733c2f74683e0d0a202020203c74643e7573657220616e642067726f7570206d616e6167656d656e743c2f74643e0d0a202020203c74643e6d6f64756c65204e532d47726f7570733c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e73656374696f6e733c2f74683e0d0a202020203c74643e61727469636c65207075626c697368696e673c2f74643e0d0a202020203c74643e6d6f64756c652053656374696f6e733c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e61646d696e6d657373616765733c2f74683e0d0a202020203c74643e73686f7274206d657373616765732062726f616463617374696e673c2f74643e0d0a202020203c74643e6d6f64756c65204e532d41646d696e4d657373616765733c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e4641513c2f74683e0d0a202020203c74643e612073696d706c65204641513c2f74643e0d0a202020203c74643e6d6f64756c65204641513c2f74643e0d0a20203c2f74723e0d0a20203c74723e0d0a202020203c74683e596f7572206d6f64756c6520686572653c2f74683e0d0a20202020203c74643e0d0a3c7020616c69676e3d63656e7465723e496620796f752077616e7420746f2068656c7020706f72742061205048502f6e756b6573206d6f64756c6520796f75206c696b65206c6574207573206b6e6f77206f6e203c6120687265663d22687474703a2f2f7777772e6a626f73732e6f72672f666f72756d2e6a73703f666f72756d3d313633223e74686520666f72756d733c2f613e3c2f74643e0d0a3c74643e796f7572206e616d652068657265203c2f74643e0d0a20203c2f74723e0d0a3c2f7461626c653e0d0a','2003-05-12',1)

          --Erich

          • 2. Re: Nukes on Oracle 8
            ebratton Newbie

            Also, here is the general set of instructions I've come up with so far, but it doesn't work yet, so this is just to help others get to the point I'm at... This is a copy of the tutorial doc for nukes with my comments inserted. So far I am only looking at using oracle for nukes data, not
            for JBoss data, which I will do eventually...



            Nukes installation (aka tutorial)


            First step : get Nukes
            So far the only way to get Nukes is through the CVS. The CVS module name is nukes. The following line will get you nukes from CVS :
            cvs -d :pserver:anonymous@cvs.jboss.sourceforge.net:/cvsroot/jboss co nukes
            got from http://jboss.sourceforge.net/snapshots/nukes-snapshot.tar.gz


            Second step : get the server
            Nukes leverages JBoss platform. You will need the following to make it work : jboss 3.2.2RC2.
            got from http://prdownloads.sourceforge.net/jboss/jboss-3.2.2RC2.zip?download


            Third step : server setup
            Set the environment variable JBOSS_HOME to the jboss installation :
            set JBOSS_HOME=C:\JAVA\JBOSS-3.2.2RC2

            copied oracle jar into C:\Dev\jboss-3.2.2RC2\server\default\lib because of errors of not finding the oracle driver... seems to work

            Create Nukes tables by sourcing the file nukes/nukes/src/resources/hsqldb/setup.ddl into hsqldb. The best way to achieve that is to connect to hsqldb through an util like squirrel sql.
            Converted mysql DDL into oracle. Was going to use a trigger to simulate auto-increment...

            *** this trigger will not be created successfully through Quantum, use sqlplus or JFacdDbc ***
            CREATE OR REPLACE TRIGGER nuke_users_trigger
            BEFORE INSERT ON nuke_users
            FOR EACH ROW
            BEGIN
            SELECT nuke_users_seq.NEXTVAL
            INTO :new.pn_uid
            FROM dual;
            END;

            but I found out that the org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand takes a sequence attribute, so we will use that instead. it seems to be a single sequence for all tables, but not 100% sure. making a generic sequence and will see if that works.

            created $nukes/nukes/src/resources/oracle directory
            containing nukes-ds.xml:
            <?xml version="1.0" encoding="UTF-8"?>


            <local-tx-datasource>
            <jndi-name>NukesDS</jndi-name>
            <connection-url>jdbc:oracle:thin:@cddevdb1.cardean.edu:1521:cddevdb1</connection-url>
            <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
            <user-name>bratton2</user-name>
            bratton2
            </local-tx-datasource>


            and containing jbosscmp-jdbc-entity-commands.xml:
            <entity-commands>
            <entity-command
            name="nukes-entity-command"
            sequence="nuke_sequence"
            class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand"/>
            </entity-commands>

            Install the datasource :
            copy $nukes/nukes/src/resources/oracle/nukes-ds.xml $JBOSS_HOME/server/default/deploy


            Fourth step : Nukes compilation and deployment
            edit $nukes/build/local.properties (do a build first to create the template before making this mod) and set:
            nukes.type-mapping=Oracle8
            nukes.mergedir=oracle

            Execute the Nukes build script in $nukes/build/build.
            Once build is done you must deploy it, so go to $nukes/nukes directory and type build deploy



            Fifth step : discover Nukes
            trying to run JBoss...

            Go to the following url : http://localhost:8080/nukes/ and enjoy it.

            • 3. Re: Nukes on Oracle 8
              Viet Master

              could you send me what I have to insert in nukes codebase to have nukes work with oracle ? that would be great to support it !

              thanks, Julien

              • 4. Re: Nukes on Oracle 8
                Dave Gubrud Newbie

                Thanks for the help Eric. I used your SQL to generate tables and I've got Nukes partially working. I can display the banner and login box on the left side but the server fails to get the "index" file for the main content. I'm using P6Spy, which logs the SQL that is generated, and this is where it's failing:

                SELECT t0_f.pn_id, t0_f.pn_timest
                FROM NUKE_FILE t0_f
                WHERE t0_f.pn_fid = 'index' AND t0_f.pn_public = (1=1)
                ORDER BY t0_f.pn_timest
                DESC