We're updating the issue view to help you get more done. 

DB crreation fails for embedded db

Description

Hello,

I'm very new to this but I found that if you are using the embedded (HSQL) database then the tables are not created correctly.

Modify {openfire_install_dir}/plugins/asterisk-im/database/asterisk-im_hsqldb.sql to read as below, restart openfire and try to add a server again - hopefully it will work for you.

C.

create table phoneServer (
serverID bigint not null,
serverName varchar(255) not null,
hostname varchar(255) not null,
port integer not null,
username varchar(255) not null,
password varchar(255) not null,
constraint phoneServer_pk primary key(serverID)
);

create table phoneDevice (
deviceID bigint not null,
device varchar(255) not null,
extension varchar(255) not null,
callerID varchar(255),
isPrimary integer not null,
userID integer,
serverID bigint not null,
constraint phoneDevice_pk primary key (deviceID)
);
create table phoneUser (
userID bigint not null,
username varchar(255) not null,
constraint phoneUser_pk primary key (userID)
);
create unique index phoneUser_username_idx on phoneUser(username);

INSERT INTO jiveVersion (name, version) VALUES ('asterisk-im', 2);

http://www.igniterealtime.org/community/message/161098

Environment

None

Acceptance Test - Entry

None

Activity

Show:
Saman Behnam
October 12, 2008, 11:15 PM

Hi there,

i was playing with openfire 3.6.0a and the Asterisk-IM 1.4.0 plugin. Openfire was set up to use the internal DB hsql. After trying to setup a connection to the Asterisk server I was able to see the login in to the Asterisk manager in the Astrisk logs. Also the openfire info logs showed the successful connection to the Asterisk manager. But i was not able to see the server in the Openfire Admin Console in the Astersik-IM section. Searching for a workaround i saw this page < http://svn.igniterealtime.org/svn/repos/asterisk-im/trunk/server/src/main/database/upgrade/1/asterisk-im_hsqldb.sql >. After editing the file "/var/lib/openfire/plugins/asterisk-im/database/asterisk-im_hsqldb.sql" and replacing the "isPrimary integer not null" entry in the "create table phoneDevice" section with the suggested entry "UPDATE jiveVersion SET version=1 WHERE name='asterisk-im';" it didn't work for me.
After little more testing I solved the problem by not replacing the suggested entry in the "create table phoneDevice" section. I just put the line "UPDATE jiveVersion SET version=1 WHERE name='asterisk-im';" at the end of all entries. This has fixed the problem for me! I was then able to add a new Asterisk server and see the added Asterisk server in the Openfire Admin Console. Also editing the Phone Mappings worked for me after that.

But again it is important to place the line just at the end of all entries to get it correctly updated in the Openfire Admin Console.

Modify {openfire_install_dir}/plugins/asterisk-im/database/asterisk-im_hsqldb.sql to read as below, restart openfire and try to add a server again - hopefully it will work for you.

------------------------------------------------------------------------------------------------------- create table phoneServer (
serverID bigint not null,
serverName varchar(255) not null,
hostname varchar(255) not null,
port integer not null,
username varchar(255) not null,
password varchar(255) not null,
constraint phoneServer_pk primary key(serverID)
);

create table phoneDevice (
deviceID bigint not null,
device varchar(255) not null,
extension varchar(255) not null,
callerID varchar(255),
isPrimary integer not null,
userID integer,
serverID bigint not null,
constraint phoneDevice_pk primary key (deviceID)
);
create table phoneUser (
userID bigint not null,
username varchar(255) not null,
constraint phoneUser_pk primary key (userID)
);
create unique index phoneUser_username_idx on phoneUser(username);

INSERT INTO jiveVersion (name, version) VALUES ('asterisk-im', 2);

UPDATE jiveVersion SET version=1 WHERE name='asterisk-im';

-------------------------------------------------------------------------------------------------------

Michiel van Baak
November 7, 2008, 12:03 AM

Thanks, that worked.

Assignee

Stefan Reuter

Reporter

Stefan Reuter

Labels

None

Expected Effort

None

Ignite Forum URL

None

Components

Fix versions

Affects versions

Priority

Critical
Configure