DB crreation fails for embedded db

Thanks, that worked.

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';

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