Dears,<br>I'm developing the new postgresql driver.<br>Now I'm running all the (550!) unit tests of 7.0 RC1 release with few errors.<br>I hope to deliver the package in 1 or 2 weeks.<br>Ivan<br><br><div><span class="gmail_quote">
2007/5/11, Alexander Kandzior <<a href="mailto:alex@opencms.org">alex@opencms.org</a>>:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div text="#000000" bgcolor="#ffffff">
<div dir="ltr" align="left"><span><font face="Verdana" size="2">Regarding DB compatibility of the 7.0 RC 1 release, here's whats written
in the release notes that install with RC1:</font></span></div>
<blockquote dir="ltr" style="margin-right: 0px;">
<div dir="ltr" align="left"><font face="Verdana"><font size="2"><span>"</span>On the database side, we have tested with
MySQL 4.0, MySQL 4.1, MySQL 5.0, Oracle 9.x and Oracle 10.x. Support for other
DBs still needs to be updated for OpenCms 7.<span>"</span></font></font></div></blockquote>
<div dir="ltr" align="left"><span><font face="Verdana" size="2">Support for other DBs will be provided when the contributors have
finished their work on the drivers. I am confident PostgeSQL will be ready for
RC2. </font></span></div>
<p><font face="Verdana" size="2">Kind
Regards,<br>Alex.<br><br>-------------------<br>Alexander
Kandzior<br> <br>Alkacon
Software GmbH - The OpenCms
Experts <br></font><a href="http://www.alkacon.com/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)"><font face="Verdana" size="2">http://www.alkacon.com</font></a><font face="Verdana" size="2">
- </font><a href="http://www.opencms.org/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)"><font face="Verdana" size="2">http://www.opencms.org</font></a><font face="Verdana" size="2">
</font></p>
<div> </div><br>
<blockquote style="border-left: 2px solid rgb(0, 0, 0); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div dir="ltr" align="left" lang="de">
<hr>
<font face="Tahoma" size="2"><b>From:</b> <a href="mailto:opencms-dev-bounces@opencms.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">opencms-dev-bounces@opencms.org</a>
[mailto:<a href="mailto:opencms-dev-bounces@opencms.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">opencms-dev-bounces@opencms.org</a>] <b>On Behalf Of
</b>Marco<br><b>Sent:</b> Thursday, May 10, 2007 11:21 PM<br><b>To:</b> The
OpenCms mailing list<br><b>Subject:</b> Re: [opencms-dev] OpenCms 7 RC 1
available<br></font><br></div><div><span class="e" id="q_1127a1c60f942dfd_1">
<div></div>I got a bit further by creating my own pg.sql (postgresql
create_tables) file.<br>All files including my pg.sql file can be found on <a href="http://www.weeswel.com/logs/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://www.weeswel.com/logs/</a><br>
<br>At
the moment I am stuck on the fact that the code depends on Mysql fucking up an
schema constraint by making something up for an not null field<br>Read on for
the gory details.<br><br>P.S. Can the devs shed some light on this and create
a patch or is this a self-service scenario.<br>P.P.S. Can I trust this code to
work properly on anything else then Mysql. <br>The fact that you can't even do
a basic install of this 7.0RC1 on an postgresql doesn't inspire
confidence.This is next to the fact that the code is dependant on Mysql
quirks.<br><br><br><br>In Pseudo language my steps to get something working,
since the original postgresql database template does not
work<br><br>1)Download the script mysql2pgsql.perl<br>2)cp
%opencms%/setup/database/mysql_41/create_tables.sql
/tmp/Mysql_create_tables.sql<br>3)vi /tmp/Mysql_create_tables.sql
<br> Strip all indexes from this file<br>
Strip all Characterset utf 8 from this file<br> Strip all
TEXT BINARY from this file<br> Strip all (255) from the
PRIMARY KEY entries<br><br>3)./mysql2pgsql.perl --nodrop
/tmp/Mysql_create_tables pg.sql<br>4)Deploy your opencms.war(7.0RC1)
file wait until it is extracted<br>5)cp pg.sql
.%OPENCMS%/setup/database/postgresql/create_tables.sql<br><br>6) Open the <a href="http://yoursite/opencms/setup" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://yoursite/opencms/setup</a>
and
follow the setup routine<br>7) Choose to install all documentation to exercise
the database table a bit<br><br>This import failed, I found the following
problem in the %opencms%/WEB-INF/logs/setup.log file<br>Important part
follows<br><i>17: Caused by:
org.opencms.db.CmsDbSqlException: An SQL error occurred when executing the
following query: INSERT INTO CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID)<br>VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000').<br>
<br>23: Caused by: org.postgresql.util.PSQLException:
Parameter index out of range.<br></i><br><i><br></i>So we try to run this
query from the pgsql commandline<i><br>opencms150=# \d
CMS_ONLINE_STRUCTURE;<br> Table
"public.cms_online_structure"<br>
Column | Type |
Modifiers<br>-------------------+----------+-----------<br> structure_id
| bytea | not
null<br> resource_id |
bytea | not
null<br> parent_id |
bytea | not
null<br> resource_path |
text | not null<br> structure_state |
smallint | not null<br> date_released |
bigint | not
null<br> date_expired | bigint
| not null<br> structure_version | integer | not
null<br>Indexes:<br> "cms_online_structure_pkey" PRIMARY
KEY, btree (structure_id)<br>Check constraints:<br>
"cms_online_structure_structure_state_check" CHECK (structure_state >=
0)<br><br>opencms150=# INSERT INTO CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID)<br>opencms150-#
VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000')<br>opencms150-#
;</i><br><i>ERROR: null value in column "structure_version" violates
not-null constraint<br></i><br>So what happens is a violation of the database
schema, column structure_version is not filled in the statement so Postgresql
properly refuses to honor the request.<br><br><br>Just to pain myself I
installed the product pretending to be a database server
My-SQL.<br><i>mysql> describe
CMS_ONLINE_STRUCTURE;<br>+-------------------+----------------------+------+-----+---------+-------+<br>|
Field
|
Type
| Null | Key | Default | Extra
|<br>+-------------------+----------------------+------+-----+---------+-------+<br>|
STRUCTURE_ID |
varchar(36) |
NO | PRI |
| |<br>|
RESOURCE_ID |
varchar(36) |
NO | MUL |
| |<br>|
PARENT_ID |
varchar(36) |
NO | MUL |
| |<br>|
RESOURCE_PATH |
text
| NO | MUL |
| |<br>| STRUCTURE_STATE |
smallint(5) unsigned | NO | MUL
|
| |<br>|
DATE_RELEASED |
bigint(20) |
NO |
|
| |<br>|
DATE_EXPIRED |
bigint(20) |
NO |
|
| |<br>| STRUCTURE_VERSION |
int(11)
| NO |
|
|
|<br>+-------------------+----------------------+------+-----+---------+-------+<br>8
rows in set (0.01 sec)<br></i><br>So the NOT NULL constraint is still with us,
thats great that means it is not a problem caused by my conversion, but what
goes wrong.<br> Lets try the query in Mysql<br><br><i>mysql> INSERT
INTO<br> -> CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID
) VALUES (<br> ->
'95c23c20-ff2c-11db-9bd2-b3972de27f02','95c23c21-ff2c-11db-9bd2-b3972de27f02','/',0,0,9223372036854775807,'000000
00-0000-00<br> '> 00-0000-000000000000');<br>Query OK, 1
row affected, 2 warnings (0.05 sec)<br><br></i><br><i>mysql> select * from
CMS_ONLINE_STRUCTURE;<br>+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+
<br>|
STRUCTURE_ID
|
RESOURCE_ID
|
PARENT_ID
| RESOURCE_PATH | STRUCTURE_STATE | DATE_RELEASED |
DATE_EXPIRED | STRUCTURE_VERSION
|<br>+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+<br>
|
95c23c20-ff2c-11db-9bd2-b3972de27f02 | 95c23c21-ff2c-11db-9bd2-b3972de27f02 |
00000000-0000-00<br>00-0000-00000000000 |
/
|
0 | 0
| 9223372036854775807
|
0
|<br>+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+<br>
1
row in set (0.00 sec)<br><br><br></i>I didn't mention any versions since
My-Sql has a long reputation of mangling data and Postgresl has a long
reputation of honoring these kinds of
constraints.<br><i><br></i><br><br><br><br><br><br>Marco schreef:
</span></div><blockquote type="cite"><div><span class="e" id="q_1127a1c60f942dfd_3">It seems like
it, <br>Caused by: java.sql.SQLException: Column not found message from
server: "Unknown column 'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION' in 'field
list'" <br><br>Same as with me, the Postgresl dump doesn't contain the
STRUCTURE_VERSION field list for the CMS_ONLINE_STRUCTURE table. And
atleast one of the many Mysql dumps does contain a dump which presumably is
correct. <br><br><br><br></span></div>St�phane Verger schreef: <div><span class="e" id="q_1127a1c60f942dfd_5"><br>
<blockquote type="cite">Hi ! <br><br>I had an unexpected error trying to
install OpenCms 7 RC1 with the java 1.4 package. <br>It seems to have a
problem with the sql schemas, is it the same problem as yours, marco ?
<br><br>Has anybody already had the same probleme, and got a solution ?
<br><br>Ty, <br><br>R. <br><br><br><br>47: Shutdown
completed, total uptime was 00:00:29. <br>46: Shutting
down OpenCms, version 7 RC 1 (6.9.1) in web application "opencms7beta"
<br>45: 44:
43: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:209)
<br>42: at
org.opencms.setup.CmsSetupWorkplaceImportThread.kill(CmsSetupWorkplaceImportThread.java:118)
<br>41: at
org.opencms.main.CmsShell.exit(CmsShell.java:557)
<br>40: java.lang.NullPointerException
<br>39: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:194)
<br>38: at
org.opencms.main.CmsShell.start(CmsShell.java:646)
<br>37: at
org.opencms.main.CmsShell.executeCommands(CmsShell.java:818)
<br>36: at
org.opencms.main.CmsShell.executeCommand(CmsShell.java:743)
<br>35:
${user}@${project}>java.lang.NullPointerException
<br>34: ${user}@${project}># Turn echo on
<br>33: ... 9 more
<br>32: at
org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1653)
<br>31: at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
<br>30: at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
<br>29: at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1634)
<br>28: at
com.mysql.jdbc.Connection.execSQL(Connection.java:2281)
<br>27: at
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
<br>26: at
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
<br>25: at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
<br>24: Caused by: java.sql.SQLException: Column not
found message from server: "Unknown column
'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION' in 'field list'"
<br>23: ... 5 more
<br>22: at
org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2579)
<br>21: at
org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4034)
<br>20: at
org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:577)
<br>19: at
org.opencms.db.generic.CmsVfsDriver.createResource(CmsVfsDriver.java:471)
<br>18: at
org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1666)
<br>17: Caused by: org.opencms.db.CmsDbSqlException: An
SQL error occurred when executing the following query:
com.mysql.jdbc.PreparedStatement@12ce574: SELECT
CMS_ONLINE_STRUCTURE.STRUCTURE_ID,CMS_ONLINE_STRUCTURE.RESOURCE_ID,CMS_ONLINE_STRUCTURE.RESOURCE_PATH,CMS_ONLINE_STRUCTURE.STRUCTURE_STATE,CMS_ONLINE_STRUCTURE.DATE_RELEASED,CMS_ONLINE_STRUCTURE.DATE_EXPIRED,CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION,CMS_ONLINE_RESOURCES.RESOURCE_ID,CMS_ONLINE_RESOURCES.RESOURCE_TYPE,CMS_ONLINE_RESOURCES.RESOURCE_FLAGS,CMS_ONLINE_RESOURCES.RESOURCE_STATE,CMS_ONLINE_RESOURCES.DATE_CREATED,CMS_ONLINE_RESOURCES.DATE_LASTMODIFIED,CMS_ONLINE_RESOURCES.USER_CREATED,CMS_ONLINE_RESOURCES.USER_LASTMODIFIED,CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
LOCKED_IN_PROJECT,CMS_ONLINE_RESOURCES.RESOURCE_SIZE,CMS_ONLINE_RESOURCES.DATE_CONTENT,CMS_ONLINE_RESOURCES.SIBLING_COUNT,CMS_ONLINE_RESOURCES.RESOURCE_VERSION,CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
FROM CMS_ONLINE_STRUCTURE,CMS_ONLINE_RESOURCES WHERE
CMS_ONLINE_STRUCTURE.RESOURCE_PATH='/' AND
CMS_ONLINE_STRUCTURE.RESOURCE_ID=CMS_ONLINE_RESOURCES.RESOURCE_ID ORDER BY
CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC.
<br>16: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:155)
<br>15: at
org.opencms.main.CmsShell.<init>(CmsShell.java:450)
<br>14: at
org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1684)
<br>13: at
org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:1111)
<br>12: at
org.opencms.db.CmsSecurityManager.newInstance(CmsSecurityManager.java:168)
<br>11: at
org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2588)
<br>10: org.opencms.main.CmsInitException: Critical
error while loading driver manager. <br>9:
-------------------- <br>8: Giving up, unable to start
OpenCms. <br>7: Critical error while loading driver
manager. <br>6: The following critical error occured:
<br>5: -------------------- <br>4:
3: 2: OpenCms property file:
"C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\config\opencms.properties".
<br>1: OpenCms WEB-INF path:
"C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\".
<br><br>Marco a �crit : <br>
<blockquote type="cite">I just tried to install this release candidat
but couldn't get it to work. <br>The OpenCms logging mentioned problems
with a query, this had to do with the absence of the group_ou column in
the cms_groups tables. <br><br>This field could not be found on the
database server and in the setup/database/postgresql/create_tables file.
<br>When comparing the oracle, mysql en Postgresql dump I suspect that
the Postgresql dump is horribly out of date. <br><br>Hope this helps the
RC phase a bit further. <br><br>Cheers,
<br><br><br>_______________________________________________ <br>This
mail is sent to you from the opencms-dev mailing list <br>To change your
list options, or to unsubscribe from the list, please visit <br><a href="http://lists.opencms.org/mailman/listinfo/opencms-dev" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.opencms.org/mailman/listinfo/opencms-dev
</a>
<br><br><br></blockquote><br><br>_______________________________________________
<br>This mail is sent to you from the opencms-dev mailing list <br>To
change your list options, or to unsubscribe from the list, please visit
<br><a href="http://lists.opencms.org/mailman/listinfo/opencms-dev" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.opencms.org/mailman/listinfo/opencms-dev</a>
<br></blockquote><br><br>_______________________________________________
<br>This mail is sent to you from the opencms-dev mailing list <br>To change
your list options, or to unsubscribe from the list, please visit <br><a href="http://lists.opencms.org/mailman/listinfo/opencms-dev" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.opencms.org/mailman/listinfo/opencms-dev
</a>
<br></span></div></blockquote><br></blockquote></div>
<br><br>_______________________________________________<br>This mail is sent to you from the opencms-dev mailing list<br>To change your list options, or to unsubscribe from the list, please visit<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://lists.opencms.org/mailman/listinfo/opencms-dev" target="_blank">
http://lists.opencms.org/mailman/listinfo/opencms-dev</a><br></blockquote></div><br>