Would you like to react to this message? Create an account in a few clicks or log in to continue.

Official Community Forums
 
HomeHome  SearchSearch  Latest imagesLatest images  RegisterRegister  Log in  The Wiki  Website  github Project  

 

 Using PostgreSQL With Multiverse (Almost)

Go down 
2 posters
AuthorMessage
Xangis
Foundation Developer
Xangis


Posts : 124
Join date : 2011-12-30
Location : Beaverton, OR

Using PostgreSQL With Multiverse (Almost) Empty
PostSubject: Using PostgreSQL With Multiverse (Almost)   Using PostgreSQL With Multiverse (Almost) EmptyFri 27 Apr - 18:38

I don't believe I've seen it mentioned as being supported anywhere, but I do recall seeing a passing comment somewhere (wiki, old forum, or somewhere in code maybe) that Multiverse should theoretically work with any database that has a JDBC driver.

So I gave Postgres a try. I do not know Java yet, but I know some things about Postgres since I use it as part of my day job. I normally use it on Ubuntu Linux, but since I'm sitting in front of a Windows machine today that's what I used.

PostgreSQL version 9.1.3:
http://www.enterprisedb.com/products-services-training/pgdownload#windows

PostgreSQL JDBC connector 9.1-902:
http://jdbc.postgresql.org/download.html

Installed with default settings, and used the postgres login with a password of test.

I launched PGAdmin and created a database called multiverse.

In the SQL window for that database I pasted the install.sql contents. It wouldn't run, so I nuked the create/use bits at the top. Then I changed any references to TINYINT to INT. Then I changed any references to LONGBLOB to TEXT. Removed indexes from create statements because Postgres didn't like the syntax. I'm used to adding indexes as separate statements in pg and didn't bother creating them, but would expect to go back and do that if everything else worked.

It eventually ran. Here's the SQL that worked for creating the initial tables:
Code:
-- create table for persistent game objects - metadata is used for
-- the deserializer to look for objects it would be interested in
-- and not have to scan every single stored object
-- TODO: add world_id INT NOT NULL,
create table objstore (
    obj_id BIGINT NOT NULL,
   namespace_int INT NOT NULL,
    world_name VARCHAR(64),
    locX INT,
    locY INT,
    locZ INT,
    instance BIGINT,
    metadata VARCHAR(255),
    type VARCHAR(255),
    name VARCHAR(255),
    persistence_key VARCHAR(255),
    data text,
    PRIMARY KEY (obj_id, namespace_int)
    );

-- mapping from multiverse UID to objstore ID for player characters
create table player_character (
    account_id INT,
    world_name VARCHAR(64) NOT NULL,
    obj_id BIGINT NOT NULL,
    namespace_int int NOT NULL,
   FOREIGN KEY (obj_id, namespace_int) REFERENCES objstore (obj_id, namespace_int));

-- free oids - token should always be 1, this just makes sure there is
-- only one value, ever
create table oid_manager (
    token INT NOT NULL,
    free_oid INT,
    PRIMARY KEY (token));

-- create table to hold the mapping from namespace string to int, read
-- by the server at startup
create table namespaces (
    namespace_string VARCHAR(64) NOT NULL,
    namespace_int int NOT NULL,
    PRIMARY KEY (namespace_string),
    UNIQUE(namespace_int));

-- create a "MEMORY" table into which plugins deposit their status,
-- so we can manage them.
create table plugin_status (
    world_name VARCHAR(64) NOT NULL,
    agent_name VARCHAR(64) NOT NULL,
   plugin_name VARCHAR(64) NOT NULL,
    plugin_type VARCHAR(16) NOT NULL,
    host_name VARCHAR(64) NOT NULL,
   pid INT,
   percent_cpu_load INT,
   last_update_time BIGINT,
    next_update_time BIGINT,
    status VARCHAR(255),
    info VARCHAR(255));

insert into oid_manager values (1, 1);

-- create the built-in namespaces
insert into namespaces values ('NS.transient', 1);
insert into namespaces values ('NS.master', 2);
insert into namespaces values ('NS.wmgr', 3);
insert into namespaces values ('NS.combat', 4);
insert into namespaces values ('NS.mob', 5);
insert into namespaces values ('NS.inv', 6);
insert into namespaces values ('NS.item', 7);
insert into namespaces values ('NS.quest', 8);
insert into namespaces values ('NS.playerqueststates', 9);
insert into namespaces values ('NS.voice', 10);

Next I edited the multiverse.properties file to set these things:
Code:

### Database settings
### Use multiverse.db_url property to use a specific JDBC URL.  If not specified, then
### the server constructs it in standard format from the other property values,
### as follows jdbc://db_type/db_hostname/db_name
multiverse.db_type=postgresql
multiverse.db_driver=org.postgresql.Driver
multiverse.db_name=multiverse
multiverse.db_user=postgres
multiverse.db_password=test
multiverse.db_hostname=localhost
### multiverse.db_url=jdbc:dbtype://hostName/databaseName

#Path to JDBC driver JAR file
multiverse.jdbcJarPath=C:\\postgresql-9.1-902.jdbc4.jar

Running start-multiverse.bat caused it to pick up the JDBC driver and run. At first I typoed the DB name and all the services but one died after a second or two (I'm sure some of you have seen that before). Logs showed 'database not found'. OK, after the typo fix everything started.

I was able to connect by running the standard MultiverseClient.exe --development --world localhost command. The character screen came up fine.

When I tried to create a character I received an "Internal Error" dialog box when clicking "Create".

I checked out the login_manager.out log and here's what I saw:
Code:
DEBUG [2012-04-27 00:08:46,569] MessageIO            handleMessageData from objmgr,488 type=msgsys.RESPONSE len=1840 class=multiverse.msgsys.ExceptionResponseMessage
ERROR [2012-04-27 00:08:46,570] LoginConnection-1    Caught exception in character factory:  multiverse.msgsys.RPCException(multiverse.msgsys.RPCException in objmgr): database error multiverse.msgsys.RPCException(multiverse.msgsys.RPCException in objmgr): database error
      at multiverse.server.engine.Database.saveObjectHelper(Database.java:1034)
      at multiverse.server.engine.Database.saveObject(Database.java:929)
      at multiverse.server.engine.Database.saveObject(Database.java:845)
      at multiverse.server.plugins.ObjectManagerPlugin$SaveObjectDataHook.processMessage(ObjectManagerPlugin.java:552)
      at multiverse.server.engine.EnginePlugin.handleMessageImpl(EnginePlugin.java:406)
      at multiverse.server.engine.EnginePlugin$QueuedMessage.run(EnginePlugin.java:440)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
Caused by: multiverse.msgsys.RPCException(multiverse.msgsys.RPCException in objmgr): Method org.postgresql.jdbc4.Jdbc4ResultSet.updateBlob(String,Blob) is not yet implemented.
      at org.postgresql.Driver.notImplemented(Driver.java:751)
      at org.postgresql.jdbc3.AbstractJdbc3ResultSet.updateBlob(AbstractJdbc3ResultSet.java:141)
      at multiverse.server.engine.Database.updateRow(Database.java:1104)
      at multiverse.server.engine.Database.saveObjectHelper(Database.java:1015)
      at multiverse.server.engine.Database.saveObject(Database.java:929)
      at multiverse.server.engine.Database.saveObject(Database.java:845)
      at multiverse.server.plugins.ObjectManagerPlugin$SaveObjectDataHook.processMessage(ObjectManagerPlugin.java:552)
      at multiverse.server.engine.EnginePlugin.handleMessageImpl(EnginePlugin.java:406)
      at multiverse.server.engine.EnginePlugin$QueuedMessage.run(EnginePlugin.java:440)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
ERROR [2012-04-27 00:08:46,570] LoginConnection-1    Character factory returned null OID
ERROR [2012-04-27 00:08:46,570] LoginConnection-1    MarsLoginPlugin: character creation failed, uid=-272263 errorMessage=Internal error
DEBUG [2012-04-27 00:08:46,570] LoginConnection-1    LoginPlugin: create character response: nProps=2
DEBUG [2012-04-27 00:08:46,570] LoginConnection-1    propertyMessage: key=errorMessage, val=Internal error
DEBUG [2012-04-27 00:08:46,571] LoginConnection-1    propertyMessage: key=status, val=false
INFO  [2012-04-27 00:08:50,979] Stats:MessageAgent  MessageAgent Local Subscription Counters: last interval/total: Created 0/1, Removed 0/0

It looks like changing that "LONGBLOB" data type to "TEXT" during the SQL conversion is what stopped me from getting glory. Since PostgreSQL doesn't have that it makes sense that there would be a not implemented exception. I imagine there might be another data type that can be substituted or something that can be worked around in code, but that's all the time I have for experimentation today.

So, the verdict is that as of today PostgreSQL *almost* works but doesn't quite get there. Doesn't seem like it's too far away from being usable. Feel free to use any info I've posted if you want to experiment with it and try to get it working.
Back to top Go down
http://zetacentauri.com
Delurin
Head of Platform Development
avatar


Posts : 424
Join date : 2011-08-03

Using PostgreSQL With Multiverse (Almost) Empty
PostSubject: Re: Using PostgreSQL With Multiverse (Almost)   Using PostgreSQL With Multiverse (Almost) EmptySat 28 Apr - 2:40

It looks like you a byte array (BYTEA) is closer to the blob format than text is...You could try switching it to that but you will probably need to modify Database.java to get this to work.
Back to top Go down
Xangis
Foundation Developer
Xangis


Posts : 124
Join date : 2011-12-30
Location : Beaverton, OR

Using PostgreSQL With Multiverse (Almost) Empty
PostSubject: Re: Using PostgreSQL With Multiverse (Almost)   Using PostgreSQL With Multiverse (Almost) EmptySun 29 Apr - 7:34

Good call -- bytea does seem like a better choice.

I don't have a Java dev environment set up to test this theory yet, but it looks like there are setBytes and getBytes calls that can safely be used in place of updateBlob/getBlob.

I could be completely wrong.
Back to top Go down
http://zetacentauri.com
Sponsored content





Using PostgreSQL With Multiverse (Almost) Empty
PostSubject: Re: Using PostgreSQL With Multiverse (Almost)   Using PostgreSQL With Multiverse (Almost) Empty

Back to top Go down
 
Using PostgreSQL With Multiverse (Almost)
Back to top 
Page 1 of 1
 Similar topics
-
» Official Closure of Multiverse Inc., and plans for The Multiverse Foundation
» New to multiverse
» Greetings From the Pixie!
» Greetings!
» Hi All :-)

Permissions in this forum:You cannot reply to topics in this forum
 :: Development :: Server Scripting and Development-
Jump to: