My previous mail was mistake. Follwing alter table script :
alter table userrole alter column userid null;
should be in oracle
alter table userrole modify (userid null);
-----Original Message-----
From: Boris Milikič [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 20, 2008 2:27 PM
To: [email protected]
Subject: RE: Upgrade to Roller 4.1 on 10g
There is no "approved", "pending" cols in "roller_comment" table in 3.1, 4.0
or 4.1.
Boris
-- update existing data to use new status column
SQL> update roller_comment set status = 'APPROVED', posttime=posttime
SQL> where approved=1;
update roller_comment set status = 'APPROVED', posttime=posttime where
approved=1
* ERROR
at line 1:
ORA-00904: "APPROVED": invalid identifier
SQL> update roller_comment set status = 'PENDING', posttime=posttime
SQL> where pending=1;
update roller_comment set status = 'PENDING', posttime=posttime where pending=1
* ERROR
at line 1:
ORA-00904: "PENDING": invalid identifier
SQL> update roller_comment set status = 'SPAM', posttime=posttime where
SQL> spam=1;
update roller_comment set status = 'SPAM', posttime=posttime where spam=1
* ERROR at
line 1:
ORA-00904: "SPAM": invalid identifier
SQL> desc roller_comment
Name Null? Type
----------------------------------------- -------- -----------------------
ID NOT NULL VARCHAR2(48)
ENTRYID NOT NULL VARCHAR2(48)
NAME VARCHAR2(255)
EMAIL VARCHAR2(255)
URL VARCHAR2(255)
CONTENT CLOB
POSTTIME NOT NULL TIMESTAMP(6)
NOTIFY NOT NULL NUMBER(1)
REMOTEHOST VARCHAR2(128)
REFERRER VARCHAR2(255)
USERAGENT VARCHAR2(255)
PLUGINS VARCHAR2(255)
CONTENTTYPE NOT NULL VARCHAR2(128)
STATUS NOT NULL VARCHAR2(20)
-----Original Message-----
From: Boris Milikič [mailto:[EMAIL PROTECTED]
Sent: Friday, February 15, 2008 4:33 PM
To: [email protected]
Subject: RE: Upgrade to Roller 4.1 on 10g
Correction:
1) There is a lot of bugs in oracle/310-to-400-migration.sql
SQL> drop table if exists rollerconfig;
drop table if exists rollerconfig
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I can't find "if exists" in Oracle DDL.
Boris
-----Original Message-----
From: Boris Milikič [mailto:[EMAIL PROTECTED]
Sent: Friday, February 15, 2008 1:09 PM
To: [email protected]
Subject: Upgrade to Roller 4.1 on 10g
I upgraded Roller 3.1 to Roller 4.1 (oracle 10g, Tomcat 5.25, Windoes xp)
roller-custom.properties:
installation.type=manual
database.configurationType=jdbc
database.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
database.jdbc.connectionURL=jdbc:oracle:thin:@irisint.com:1521:ROMA
database.jdbc.username=bmil
database.jdbc.password=bmil
mail.configurationType=properties
mail.hostName=smtp.mf
1) There is a lot of bugs in oracle/dbscripts.sql
SQL> insert into roller_permission
SQL> (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog',
0, current_timestamp
3 from rolleruser as u, website as w, roller_user_permissions as p
4 where p.user_id = u.id and p.website_id = w.id and permission_mask =
1;
from rolleruser as u, website as w, roller_user_permissions as p
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 insert into roller_permission
(id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog',
0, current_timestamp
3 from rolleruser u, website w, roller_user_permissions p
4* where p.user_id = u.id and p.website_id = w.id and permission_mask =
1
SQL> /
0 rows created.
2) Roller didn't find any user in DB, that exists in 3.1 ID
----------------------------------
USERNAME
----------------------------------
PASSPHRASE
----------------------------------
8a82f0e61763560d01176399cb1a0001
admin
----
3) I can't insert new user
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot
insert NULL into ("BMIL"."USERROLE"."USERID") {prepstmnt 14838503 INSERT INTO
userrole (id, rolename, username) VALUES (?, ?, ?) [params=(String)
47cc40d6-aeb7-4a5b-8d8a-f808c3806b01, (String) editor, (String) bmil]}
[code=1400, state=23000]
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:95)
... 189 more
