In case anyone wants to know I have gotten JBoss Portal 2.0a working with
PostgreSQL. I'm attaching my setup.ddl script to make it easier. Because I
didn't feel like modifying all the code to use sequences, I just created a
single "hibernate_sequence" which allows the existing code to work. Normally I
would use different sequences for each table. I also simplified things by
using the PostgreSQL specific SERIAL datatype in place of AUTO_INCREMENT - they
do essentially the same thing. Also had to change BIT to BOOLEAN to get it
working and modified all the alter table/add constaint code as well.
Postgresql doesn't have an "if exists" so calling "drop table" will result in
an error message on your first run because those tables aren't there. You can
safely ignore these errors.
*********setup.ddl********
drop table jbp_user_prop;
drop table jbp_users;
drop table jbp_role_membership;
drop table jbp_user_pref;
drop table jbp_user_pref_set;
drop table jbp_user_pref_prop_value;
drop table jbp_roles;
drop table jbp_forums_topics;
drop table jbp_forums_users;
drop table jbp_forums_forums;
drop table jbp_forums_categories;
drop table jbp_forums_watch;
drop table jbp_forums_posts;
drop table jbp_forums_polls;
create table jbp_user_prop (
jbp_uid INTEGER not null,
jbp_value VARCHAR(255),
jbp_name VARCHAR(255) not null,
primary key (jbp_uid, jbp_name)
);
create table jbp_users (
jbp_uid SERIAL,
jbp_root_pref_set_id INTEGER unique,
jbp_uname VARCHAR(255) unique,
jbp_givenname VARCHAR(255),
jbp_familyname VARCHAR(255),
jbp_password VARCHAR(255),
jbp_realemail VARCHAR(255),
jbp_fakeemail VARCHAR(255),
jbp_regdate TIMESTAMP,
jbp_viewrealemail BOOLEAN,
jbp_enabled BOOLEAN,
primary key (jbp_uid)
);
create table jbp_role_membership (
jbp_rid INTEGER not null,
jbp_uid INTEGER not null,
primary key (jbp_uid, jbp_rid)
);
create table jbp_user_pref (
jbp_id SERIAL,
jbp_name VARCHAR(255) not null,
jbp_type INTEGER,
jbp_set_id INTEGER,
primary key (jbp_id)
);
create table jbp_user_pref_set (
jbp_id SERIAL,
name VARCHAR(255) not null,
jbp_parent_id INTEGER,
primary key (jbp_id)
);
create table jbp_user_pref_prop_value (
jbp_prop_id INTEGER not null,
jbp_value VARCHAR(255),
jbp_idx INTEGER not null,
primary key (jbp_prop_id, jbp_idx)
);
create table jbp_roles (
jbp_rid SERIAL,
jbp_name VARCHAR(255) unique,
jbp_displayname VARCHAR(255) unique,
primary key (jbp_rid)
);
create table jbp_forums_topics (
jbp_id SERIAL,
jbp_forum_id INTEGER,
jbp_views INTEGER,
jbp_replies INTEGER,
jbp_first_post_id INTEGER,
jbp_last_post_id INTEGER,
jbp_last_post_date TIMESTAMP,
jbp_poster INTEGER,
jbp_type INTEGER,
jbp_status INTEGER,
jbp_subject VARCHAR(255),
primary key (jbp_id)
);
create table jbp_forums_users (
jbp_id SERIAL,
jbp_username VARCHAR(255),
jbp_nb_posts INTEGER,
primary key (jbp_id)
);
create table jbp_forums_forums (
jbp_id SERIAL,
jbp_category_id INTEGER,
jbp_description VARCHAR(255),
jbp_last_post_id INTEGER,
jbp_name VARCHAR(255),
jbp_order INTEGER,
jbp_topic_size INTEGER,
jbp_post_size INTEGER,
jbp_prune_enable BOOLEAN,
jbp_prune_next INTEGER,
jbp_status INTEGER,
jbp_type INTEGER,
primary key (jbp_id)
);
create table jbp_forums_categories (
jbp_id SERIAL,
jbp_order INTEGER,
jbp_title VARCHAR(255),
primary key (jbp_id)
);
create table jbp_forums_watch (
jbp_id SERIAL,
jbp_forum_id INTEGER,
jbp_mode INTEGER,
jbp_user_id INTEGER,
primary key (jbp_id)
);
create table jbp_forums_posts (
jbp_id SERIAL,
jbp_topic_id INTEGER,
jbp_edit_count INTEGER,
jbp_edit_date TIMESTAMP,
jbp_create_date TIMESTAMP,
jbp_subject VARCHAR(255),
jbp_text VARCHAR(255),
jbp_poster INTEGER,
primary key (jbp_id)
);
create table jbp_forums_polls (
jbp_id SERIAL,
primary key (jbp_id)
);
CREATE SEQUENCE hibernate_sequence;
CREATE INDEX FK143B175794E6B1C9 ON jbp_user_prop (jbp_uid);
ALTER TABLE jbp_user_prop ADD CONSTRAINT FK143B175794E6B1C9 foreign key
(jbp_uid) references jbp_users (jbp_uid);
CREATE INDEX FKF606014114931AFE ON jbp_users (jbp_root_pref_set_id);
ALTER TABLE jbp_users ADD CONSTRAINT FKF606014114931AFE foreign key
(jbp_root_pref_set_id) references jbp_user_pref_set (jbp_id);
CREATE INDEX FKF410173894E6A686 ON jbp_role_membership (jbp_rid);
ALTER TABLE jbp_role_membership ADD CONSTRAINT FKF410173894E6A686 foreign key
(jbp_rid) references jbp_roles (jbp_rid);
CREATE INDEX FKF410173894E6B1C9 ON jbp_role_membership (jbp_uid);
ALTER TABLE jbp_role_membership ADD CONSTRAINT FKF410173894E6B1C9 foreign key
(jbp_uid) references jbp_users (jbp_uid);
CREATE INDEX FK93CC44D0C691B5FF ON jbp_user_pref (jbp_set_id);
ALTER TABLE jbp_user_pref ADD CONSTRAINT FK93CC44D0C691B5FF foreign key
(jbp_set_id) references jbp_user_pref_set (jbp_id);
CREATE INDEX FK97AA4873FAF7DF89 ON jbp_user_pref_set (jbp_parent_id);
ALTER TABLE jbp_user_pref_set ADD CONSTRAINT FK97AA4873FAF7DF89 foreign key
(jbp_parent_id) references jbp_user_pref_set (jbp_id);
CREATE INDEX FKC496306482E27870 ON jbp_user_pref_prop_value (jbp_prop_id);
ALTER TABLE jbp_user_pref_prop_value ADD CONSTRAINT FKC496306482E27870 foreign
key (jbp_prop_id) references jbp_user_pref (jbp_id);
CREATE INDEX FK6C1A04CA2AE40318 ON jbp_forums_topics (jbp_last_post_id);
ALTER TABLE jbp_forums_topics ADD CONSTRAINT FK6C1A04CA2AE40318 foreign key
(jbp_last_post_id) references jbp_forums_posts (jbp_id);
CREATE INDEX FK6C1A04CAC0A088C4 ON jbp_forums_topics (jbp_first_post_id);
ALTER TABLE jbp_forums_topics ADD CONSTRAINT FK6C1A04CAC0A088C4 foreign key
(jbp_first_post_id) references jbp_forums_posts (jbp_id);
CREATE INDEX FK6C1A04CAC1FFF134 ON jbp_forums_topics (jbp_poster);
ALTER TABLE jbp_forums_topics ADD CONSTRAINT FK6C1A04CAC1FFF134 foreign key
(jbp_poster) references jbp_forums_users (jbp_id);
CREATE INDEX FK6C1A04CA31595B20 ON jbp_forums_topics (jbp_forum_id);
ALTER TABLE jbp_forums_topics ADD CONSTRAINT FK6C1A04CA31595B20 foreign key
(jbp_forum_id) references jbp_forums_forums (jbp_id);
CREATE INDEX FK543743182AE40318 ON jbp_forums_forums (jbp_last_post_id);
ALTER TABLE jbp_forums_forums ADD CONSTRAINT FK543743182AE40318 foreign key
(jbp_last_post_id) references jbp_forums_posts (jbp_id);
CREATE INDEX FK543743183BA91075 ON jbp_forums_forums (jbp_category_id);
ALTER TABLE jbp_forums_forums ADD CONSTRAINT FK543743183BA91075 foreign key
(jbp_category_id) references jbp_forums_categories (jbp_id);
CREATE INDEX FKF31C8C6931595B20 ON jbp_forums_watch (jbp_forum_id);
ALTER TABLE jbp_forums_watch ADD CONSTRAINT FKF31C8C6931595B20 foreign key
(jbp_forum_id) references jbp_forums_forums (jbp_id);
CREATE INDEX FKF31C8C698C8A7708 ON jbp_forums_watch (jbp_user_id);
ALTER TABLE jbp_forums_watch ADD CONSTRAINT FKF31C8C698C8A7708 foreign key
(jbp_user_id) references jbp_forums_users (jbp_id);
CREATE INDEX FKF2C0436DDB927852 ON jbp_forums_posts (jbp_topic_id);
ALTER TABLE jbp_forums_posts ADD CONSTRAINT FKF2C0436DDB927852 foreign key
(jbp_topic_id) references jbp_forums_topics (jbp_id);
CREATE INDEX FKF2C0436DC1FFF134 ON jbp_forums_posts (jbp_poster);
ALTER TABLE jbp_forums_posts ADD CONSTRAINT FKF2C0436DC1FFF134 foreign key
(jbp_poster) references jbp_forums_users (jbp_id);
insert into jbp_users (jbp_uid, jbp_uname, jbp_password, jbp_realemail,
jbp_regdate, jbp_viewrealemail, jbp_enabled) values ('1', 'admin',
MD5('admin'), '[EMAIL PROTECTED]', NOW(), '1', '1');
insert into jbp_users (jbp_uid, jbp_uname, jbp_password, jbp_realemail,
jbp_regdate, jbp_viewrealemail, jbp_enabled) values ('2', 'user', MD5('user'),
'[EMAIL PROTECTED]', NOW(), '1', '1');
insert into jbp_roles values ('1', 'Admins', 'Administrators');
insert into jbp_roles values ('2', 'Users', 'Users');
insert into jbp_role_membership values ('1', '1');
insert into jbp_role_membership values ('2', '2');
insert into jbp_forums_categories values ('1', '1', 'Dummy demo category');
insert into jbp_forums_forums values ('1', '1', 'First description', null,
'First forum', '10', '0', '0', '1', '0', '0', '0');
insert into jbp_forums_forums values ('2', '1', 'Second description', null,
'Second forum', '20', '0', '0', '1', '0', '0', '0');
***** portal-ds.xml ******
<?xml version="1.0" encoding="UTF-8"?>
<local-tx-datasource>
<jndi-name>PortalDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/jbossportal</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>username</user-name>
password
</local-tx-datasource>
You also have to modify 2 of the hibernate-service.xml files as well.
***/deploy/portal-core.sar/portal-core.har/META-INF/hibernate-service.xml***
<?xml version="1.0" encoding="UTF-8"?>
java:PortalDS
java:portal/ForumSessionFactory
net.sf.hibernate.dialect.PostgreSQLDialect
net.sf.hibernate.cache.TreeCacheProvider
jboss.jca:service=DataSourceBinding,name=PortalDS
***/deploy/portal-forums.ear/portal-forums.har/META-INF/hibernate-service.xml***
<?xml version="1.0" encoding="UTF-8"?>
java:PortalDS
java:portal/ForumSessionFactory
net.sf.hibernate.dialect.PostgreSQLDialect
net.sf.hibernate.cache.TreeCacheProvider
jboss.jca:service=DataSourceBinding,name=PortalDS
I think that was all. I've only briefly tested this but it enabled me to
create users, login as admin, modify the forums, etc. etc. Hope this helps
someone who doesn't want to use MySQL. BTW PostgreSQL v8.0 is out today.
Mike
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3862954#3862954
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3862954
-------------------------------------------------------
This SF.Net email is sponsored by: IntelliVIEW -- Interactive Reporting
Tool for open source databases. Create drag-&-drop reports. Save time
by over 75%! Publish reports on the web. Export to DOC, XLS, RTF, etc.
Download a FREE copy at http://www.intelliview.com/go/osdn_nl
_______________________________________________
JBoss-Development mailing list
JBoss-Development@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jboss-development
