Browse: Home / Replacing Union Platform Default Derby Datasource with MySQL Server

Replacing Union Platform Default Derby Datasource with MySQL Server

By Armand Niculescu on February 18, 2013

Union PlatformUnion Platform is powerful and easy-to-use multiplayer server written in Java. By default it uses an embedded Derby database. In this article I’ll show you how you can replace this datasource with a MySQL one and how to process the database with minimum downtime.

Why

The default Derby datasource is very nice because it’s zero-configuration, hassle-free and flexible. Don’t consider replacing it unless you really need to!

Why would you want to replace the embedded datasource? For one, because it’s embedded, Union has exclusive access to the database. You can’t access it while Union is running, so for any maintenance you have to write a server module. Secondly, the key-value store is very flexible but makes aggregation unfeasible. If you want to have leaderboards or remove unused accounts, the embedded Derby stands in your way. Thirdly (and this is based on testing), having a dedicated MySQL server will boost the performance significantly. Finally, the MySQL database is smaller and easier to back up.

Why not

Are there any downsides to this? Yes.

While you could simply replace Derby with MySQL, keeping the database schema the same (key-value), you actually have to decide what attributes you want saved in order to get the performance boost and aggregate data; you can no longer save arbitrary attributes. The database table will have a number of columns for the attributes and the datasource will have to have prior knowledge of them. If you want to add a new attribute, you have to alter the database and recompile the datasource module.

Requirements

This article assumes a Linux server. The concepts will work for a Windows server too, but may require some rewriting. I only tested in Ubuntu 11.04 and 12.10, with Union 2.0.0 and MySQL 5.1. I see no reason why this wouldn’t work with other distros. I also tested only with the Oracle Java 6 JDK, not the Open JDK.

You will need to install the Java JDK and Apache Derby as you’ll need the “ij” tool. I’m not providing any install info as it varies depending on OS and distro.

I used Python to write a database conversion script from Derby to MySQL. You may use a different programming language if you want and the script is for demonstration only anyway, you’ll probably need to write your own based on your database schema.

You should feel comfortable using the command line of your server OS, have shell access to the server and at least intermediate knowledge of Java and SQL.

Designing the database schema

The first thing to do is to decide what attributes you’ll be saving and how. In my case, I made some simplifications that work for me. They may or may not work for you.

I decided that I don’t actually need account roles (i.e. Moderator and such). I also decided that I can hardcode attributes flags in the datasource and that all my attributes will be global. I’m also not using persistent room attributes (although the concept would be the same as for account attributes).

The Derby database uses two tables for user info: account and account_attribute. Here’s their structure:

  • Account
    • id
    • created_at
    • updated_at
    • userid
    • password
    • moderator
  • Account_Attribute
    • id
    • created_at
    • updated_at
    • client
    • name
    • scope
    • value
    • flags

The ‘client‘ field in account_attribute table corresponds to the id in the account table.

In this simplified tutorial, I’ll use just 2 attributes, email and score, so the MySQL table will look like this:

CREATE TABLE accounts (
  username VARCHAR(20) COLLATE latin1_general_cs NOT NULL,
  password VARCHAR(20) COLLATE latin1_general_cs NOT NULL,
  score mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  email VARCHAR(128) COLLATE latin1_general_ci NOT NULL DEFAULT ''
  PRIMARY KEY (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

I made some assumptions for this schema. First of all, you see there’s no numeric id. I’m using the case-sensitive username as the primary key. For me, it works blazingly fast because most operations are based on username anyway.  I also limited the length of the username/password and allowed only Latin characters.  You must make sure that your multiplayer app sanitizes the input to conform to these rules, or you may modify the schema to allow UTF-8 characters. It’s up to you.

I did not implement room attributes. For  server attributes I preserved the format.

The new Datasource

Union is amazingly well-written and replacing the Datasource is a snap.

You will first need to download the MySQL JDBC connector. For performance I’m using connection pooling with Tomcat JDBC Connection Pool. In turn, Tomcat Connection Pool requires Tomcat JULI, so make sure you download it too.

The Datasource skeleton follows:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
 
import net.user1.union.api.Account;
import net.user1.union.api.Datasource;
import net.user1.union.api.Room;
import net.user1.union.api.Server;
import net.user1.union.api.Status;
import net.user1.union.core.attribute.Attribute;
import net.user1.union.core.context.DatasourceContext;
import net.user1.union.core.exception.AttributeException;
import net.user1.union.core.exception.DatasourceException;
 
public class MySqlDatasource implements Datasource 
{
    private static Logger log = Logger.getLogger(MySqlDatasource.class); 
    private org.apache.tomcat.jdbc.pool.DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource();
 
    private String dbURL;
    private String dbUsername;
    private String dbPassword;
 
    private static final String[] ATTRIBUTES = {"score", "email"};
    private static final HashSet  ALLOWED_ATTRIBUTES = new HashSet(Arrays.asList(ATTRIBUTES));
 
    public boolean init(DatasourceContext ctx) 
    {
         if ((dbURL = getAttribute(ctx, "dbURL")) == null) 
         {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbURL].");
            return false;
         }
 
         if ((dbUsername = getAttribute(ctx, "dbUsername")) == null) 
         {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbUsername].");
            return false;
         }
 
         if ((dbPassword = getAttribute(ctx, "dbPassword")) == null) 
         {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbPassword].");
            return false;
         }
 
         PoolProperties p = new PoolProperties();
         p.setUrl(dbURL);
         p.setDriverClassName("com.mysql.jdbc.Driver");
         p.setUsername(dbUsername);
         p.setPassword(dbPassword);
 
         // default values
         p.setJmxEnabled(true);
         p.setTestWhileIdle(false);
         p.setTestOnBorrow(true);
         p.setValidationQuery("SELECT 1");
         p.setTestOnReturn(false);
         p.setValidationInterval(30000);
         p.setTimeBetweenEvictionRunsMillis(30000);
         p.setMaxActive(100);
         p.setInitialSize(10);
         p.setMaxWait(10000);
         p.setRemoveAbandonedTimeout(60);
         p.setMinEvictableIdleTimeMillis(30000);
         p.setMinIdle(10);
         p.setLogAbandoned(true);
         p.setRemoveAbandoned(true);
         p.setJdbcInterceptors(
           "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
           "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
 
         datasource = new DataSource();
         datasource.setPoolProperties(p);
 
        return true;
    }
 
    /**
     * Get an attribute defined in union.xml
     * @param ctx
     * @param attribute name
     * @return attribute value
     */
    private String getAttribute(DatasourceContext ctx, String name) 
    {
        Object attr = ctx.getAttributes().get(name);
        return (attr != null) ? attr.toString() : null;
    }
 
    /**
     * Return the connection
     * @return connection
     * @throws SQLException
     */
    private Connection getConnection()
    throws SQLException 
    {
    	return datasource.getConnection();
    }
 
    /**
     * Close the resources
     * @param con
     * @param ps
     * @param rs
     */
    private void close(Connection con, PreparedStatement ps, ResultSet rs) 
    {
        if (con != null) 
        {
            try 
            {
                con.close();
            } 
            catch (SQLException e) 
            {
                log.error("Could not close connection.", e);
            }
        }
 
        if (ps != null) 
        {
            try 
            {
                ps.close();
            } 
            catch (SQLException e) 
            {
                log.error("Could not close prepared statement.", e);
            }
        }
 
        if (rs != null) 
        {
            try 
            {
                rs.close();
            } 
            catch (SQLException e) 
            {
                log.error("Could not close result set.", e);
            }
        }
    }
 
	public void shutdown() 
	{
		if (datasource != null) 
			datasource.close();
	}
}

We can now start putting some meat on them bones:

    /**
     * Load account attributes. All attributes are global
     * Attributes to be retrieved are hardcoded for speed.
     * @param account reference to the account to load attributes into
     * @throws DatasourceException 
     * @see loadAccountGlobalAttributes()
     */
    public void loadAllAccountAttributes(Account account)
    throws DatasourceException 
    {
        loadAccountGlobalAttributes(account);
    }
 
    /**
     * Load account attributes. All attributes are global
     * Attributes to be retrieved are hardcoded for speed.
     * @param account reference to the account to load attributes into
     * @throws DatasourceException 
     */
    public void loadAccountGlobalAttributes(Account account)
    throws DatasourceException 
    {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
 
        try 
        {
            con = getConnection();
 
            ps = con.prepareStatement("SELECT score, email FROM accounts WHERE username=? LIMIT 1");
            ps.setString(1, account.getUserID());
            rs = ps.executeQuery();
 
            if (rs.next()) 
            {
                try 
                {
                    account.setAttribute("score", rs.getString("score"), Attribute.SCOPE_GLOBAL, Attribute.FLAG_PERSISTENT);
                    account.setAttribute("email", rs.getString("email"), Attribute.SCOPE_GLOBAL, Attribute.FLAG_PERSISTENT);
                 } 
                catch (Exception e) 
                {
                	log.error("DATASOURCE ERROR loadAccountGlobalAttributes");
                    throw new DatasourceException(e);
                } 
            } 
        } 
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR loadAccountGlobalAttributes");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }   
    }

As you can see, we hardcode the attributes, scope and flags. The good thing is that it’s very fast. Notice that we’re using the primary key in the WHERE clause and we limit the result (I haven’t tested if MySQL optimizer is smart enough to know to retrieve only one row based on the primary key).

    /**
     * Save an account attribute. Allowed attributes are checked from ALLOWED_ATTRIBUTES.
     * Attributes not in the list are silently discarded.
     * @param account reference to the account
     * @param attr reference to the attribute to save
     * @throws DatasourceException 
     */
    public void saveAccountAttribute(Account account, Attribute attr)
    throws DatasourceException 
    {
    	String attrName = attr.getName();
 
    	if (!ALLOWED_ATTRIBUTES.contains(attrName))
        {
                log.error("Unrecognized attribute "+attrName);
    		return;
    	}
 
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
 
        try 
        {
        	con = getConnection();
        	ps = con.prepareStatement("UPDATE accounts SET `" + attrName + "`=? WHERE username=? LIMIT 1");
        	ps.setString(1, attr.nullSafeGetValue());
        	ps.setString(2, account.getUserID());
        	ps.executeUpdate();
        } 
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR saveAccountAttribute");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        } 
    }

When saving an attribute I’m checking if the attribute is known, otherwise the attribute is discarded and an error is logged.

    /**
     * Remove an account attribute.
     * Because in this setup the attributes are not arbitrary, they cannot be removed completely,
     * we set them to empty strings
     * @param account reference to the account
     * @param attr reference to the attribute
     * @throws DatasourceException 
     */
    public void removeAccountAttribute(Account account, Attribute attr)
    throws DatasourceException 
    {
    	String attrName = attr.getName();
 
    	if (!ALLOWED_ATTRIBUTES.contains(attrName))
    		return;
 
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
 
        try 
        {
        	con = getConnection();
        	ps = con.prepareStatement("UPDATE accounts SET `" + attrName + "` = '' WHERE username=? LIMIT 1");
        	ps.setString(1, account.getUserID());
        	ps.executeUpdate();
        } 
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR removeAccountAttribute");
        } 
        finally 
        {
            close(con, ps, rs);
        } 
    }

Since it’s not actually possible to remove an attribute completely, I’m just setting it to empty string. You could alter the database schema and the query to set it to NULL instead.

    /**
     * Check if account exists
     * @param userID	id to check
     * @return true if the account exists, false otherwise
     * @throws DatasourceException 
     */
    public boolean containsAccount(String userID)
    throws DatasourceException 
    {   
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
 
        boolean accountExists = false;
        try 
        {
        	con = getConnection();
        	ps = con.prepareStatement("SELECT COUNT(username) AS num FROM accounts WHERE username=? LIMIT 1");
        	ps.setString(1, userID);
        	rs = ps.executeQuery();
 
            if (rs.next()) 
            {
            	int num = rs.getInt("num");
            	accountExists = (num==1);
            }
        } 
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR containsAccount");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        } 
 
        return accountExists;
    }
 
    /**
     * Create an account.
     * @param userID
     * @param password
     * @return Status.SUCCESS on success or Status.ACCOUNT_EXISTS if the account exists
     * @throws DatasourceException 
     */
    public String createAccount(String userID, String password)
    throws DatasourceException 
    {   
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		if (containsAccount(userID))
			return  Status.ACCOUNT_EXISTS;
 
		try
		{
			con = getConnection();
			ps = con.prepareStatement("INSERT INTO accounts (username, password) VALUES (?, ?)");
			ps.setString(1, userID);
			ps.setString(2, password);
			ps.executeUpdate();
 
			return Status.SUCCESS;
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR createAccount");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
    }
 
    /**
     * Save an account (just the password is actually saved)
     * @param account reference
     * @throws DatasourceException 
     */
    public String saveAccount(Account account)
    throws DatasourceException 
    {
 		Connection con = null;
 		PreparedStatement ps = null;
 		ResultSet rs = null;
 
 		try
 		{
 			con = getConnection();
 			ps = con.prepareStatement("UPDATE accounts SET password=? WHERE username=? LIMIT 1");
 			ps.setString(1, account.getPassword());
 			ps.setString(2, account.getUserID());
 			int rows = ps.executeUpdate();
 
 			if (rows==1)
 				return Status.SUCCESS;
 			else
 				return Status.ACCOUNT_NOT_FOUND;
 		}
         catch (SQLException e) 
         {
         	log.error("DATASOURCE ERROR saveAccount");
             throw new DatasourceException(e);
         } 
         finally 
         {
             close(con, ps, rs);
         }
    }
 
    /**
     * Get a list of all account names on the server
     * @returns List
     * @throws DatasourceException 
     */
	public List getAccounts()
	throws DatasourceException 
	{
		List users = new ArrayList();
 
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		try 
		{
			con = getConnection();
 
			ps = con.prepareStatement("SELECT username FROM accounts");
			rs = ps.executeQuery();
 
			while (rs.next())
			{
				users.add(rs.getString("username"));
			}
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR getAccounts");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
 
    	return users;
    }
 
	/**
	 * Get the password for the given username
	 * @param userID username to get the password for
	 * @return password, null if username was not found
     * @throws DatasourceException 
	 */
    public String getPassword(String userID)
    throws DatasourceException 
    {      
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		try 
		{
			con = getConnection();
 
			ps = con.prepareStatement("SELECT password FROM accounts WHERE username=? LIMIT 1");
			ps.setString(1, userID);
			rs = ps.executeQuery();
 
			if (rs.next())
				return rs.getString("password");
			else
				return null;
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR getPassword");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
    }
 
    /**
     * Remove the account
     * @param userID account username
     * @return Status.SUCCESS if the account was removed or Status.ACCOUNT_NOT_FOUND otherwise
     * @throws DatasourceException 
     */
    public String removeAccount(String userID)
    throws DatasourceException 
    {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		try 
		{
			con = getConnection();
			ps = con.prepareStatement("DELETE FROM accounts WHERE username=? LIMIT 1");
			ps.setString(1, userID);
			int rows = ps.executeUpdate();
 
			if (rows == 1)
				return Status.SUCCESS;
			else
				return Status.ACCOUNT_NOT_FOUND;
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR removeAccount");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
    }

The last batch of code lists a number of straightforward methods that should be self-explanatory.

    /**
     * Load an account. Normally the moderator status is loaded, but we're simply setting it to false
     * @param account reference
     * @throws DatasourceException 
     */
    public void loadAccount(Account account)
    throws DatasourceException 
    {
        account.initModerator(false);
    }

Since I have no need for the Moderator feature, all accounts are assumed non-moderators. You could add a separate column for the moderator status if you want.

    /**
     * 
     * @throws DatasourceException 
     */
	public void loadAccountRoomAttributes(Account account, String roomID)
	throws DatasourceException 
	{
		log.error("DATASOURCE loadAccountRoomAttributes not implemented");
	}
 
    /**
     * 
     * @throws DatasourceException 
     */
	public void loadRoomAttributes(Room room)
	throws DatasourceException 
	{
		log.error("DATASOURCE loadRoomAttributes not implemented");
	}
 
    /**
     * 
     * @throws DatasourceException 
     */
	public void saveRoomAttribute(Room room, Attribute attr)
	throws DatasourceException 
	{
		log.error("DATASOURCE saveRoomAttribute not implemented");
	}
 
    /**
     * 
     * @throws DatasourceException 
     */
	public void removeRoomAttribute(Room room, Attribute attr)
	throws DatasourceException 
	{
		log.error("DATASOURCE removeRoomAttribute not implemented");
	}

As I mentioned, I haven’t implemented any room attribute functionality. You may do it the same way as the accounts and their attributes.

   /**
     * Load server attributes
     * @throws DatasourceException 
     */
	public void loadServerAttributes(Server server)
	throws DatasourceException 
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		try 
		{
			con = getConnection();
 
			ps = con.prepareStatement("SELECT name, value, scope, flags FROM server_attributes");
			rs = ps.executeQuery();
 
			try 
			{
				while (rs.next())
				{
					server.setAttribute(rs.getString("name"), rs.getString("value"), rs.getString("scope"), rs.getInt("flags"));
				}				
			} 
			catch (AttributeException e) 
			{
				log.error("DATASOURCE ERROR loadServerAttributes");
			}
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR loadServerAttributes");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
	}
 
    /**
     * Add or update a server attribute
     * @param attr attribute to save - name, value, scope & flags supported
     * @throws DatasourceException 
     */
	public void saveServerAttribute(Attribute attr)
	throws DatasourceException 
	{
 		Connection con = null;
 		PreparedStatement ps = null;
 		ResultSet rs = null;
 
 		try
 		{
 			con = getConnection();
 			ps = con.prepareStatement("INSERT INTO server_attributes (name, value, scope, flags) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE value=?, scope=?, flags=?");
 			ps.setString(1, attr.getName());
 
 			ps.setString(2, attr.nullSafeGetValue());
 			ps.setString(3, attr.getScope());
 			ps.setInt(4, attr.getFlags());
 
 			ps.setString(5, attr.nullSafeGetValue());
 			ps.setString(6, attr.getScope());
 			ps.setInt(7, attr.getFlags());
 
 			ps.executeUpdate();
 		}
         catch (SQLException e) 
         {
         	log.error("DATASOURCE ERROR saveServerAttribute");
             throw new DatasourceException(e);
         } 
         finally 
         {
             close(con, ps, rs);
         }
	}
 
   /**
    * Remove a server attribute
    * @param attr attribute to remove
    * @throws DatasourceException 
    */
	public void removeServerAttribute(Attribute attr)
	throws DatasourceException 
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
 
		try 
		{
			con = getConnection();
			ps = con.prepareStatement("DELETE FROM server_attributes WHERE name=? LIMIT 1");
			ps.setString(1, attr.getName());
			ps.executeUpdate();
		}
        catch (SQLException e) 
        {
        	log.error("DATASOURCE ERROR removeServerAttribute");
            throw new DatasourceException(e);
        } 
        finally 
        {
            close(con, ps, rs);
        }
	}

Server attributes are handled the same way as in Derby.

You have to compile the class and place it in the modules folder in Union.

You also need to edit the start script to include the new classes:

java -Dfile.encoding=UTF-8 -classpath lib/union.jar:lib/stax-api-1.0.1.jar:lib/wstx-asl-3.2.6.jar:lib/mysql.jar:lib/tomcat-jdbc.jar:bin/tomcat-juli.jar:modules/MySqlDatasource.jar net.user1.union.core.UnionMain start &

And finally union.xml must be edited as well in the persistence section:

   <persistence>
        <datasource id="sqlDS">            
            <class>MySqlDatasource</class>
            <attributes>
                <attribute name="dbURL">jdbc:mysql://localhost:3306/union</attribute>
                <attribute name="dbUsername">**********</attribute>
                <attribute name="dbPassword">**********</attribute>
            </attributes>
        </datasource>
    </persistence>

Converting the data

We can export data from Derby in CSV format. MySQL can import CSV or SQL statements, but the real problem is in combining the data from the account table with the key-value pairs from account_attribute into one table.

For this I decided to write a Python script. You could use any scripting language.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

import pprint
import csv
 
accounts = {}
 
def sqlEscape(s):
	"Escape some characters for sql strings"
	return s.replace("'", "''").replace("\\", "\\\\")
 
# open and read account csv
# expected data is accountid,timestamp,timestamp,username,password
# we use the accountid as key for the accounts dictionary 
# and put username and password in it as another dictionary
with open('account.csv') as accountFile:
	reader = csv.reader(accountFile)
	for line in reader:
		userid   = line[0]
		username = line[3]
		password = line[4]
		accounts[userid] = {'username':username, 'password':password} 
 
print "Account read"
 
# open and read accoount attribute csv
# expected data is attributeid,timestamp,timestamp,accountid,attributename,scope,attributevalue,flags
# we use the account id to find the existing entry in accounts dictionary and add attributename:attributevalue
with open('account_attribute.csv') as attributeFile:
	reader = csv.reader(attributeFile)
	for line in reader:
		userid = line[3]
		name   = line[4]
		value  = line[6]
 
		#don't inset empty elements, we'll get defaults when we read from the accounts dictionary
		if (value==''):
			continue
 
		# check if we have an entry
		if userid in accounts:
			account = accounts[userid]
			account[name] = value
 
print "Attributes read"
 
with open('accounts.sql', 'w') as outputFile:
 
	sql = """
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  username  varchar(20)  COLLATE latin1_general_cs NOT NULL,
  password  varchar(20)  COLLATE latin1_general_cs NOT NULL,
  score     mediumint(8) unsigned                  NOT NULL DEFAULT '0',
  email     varchar(50)  COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
"""
	outputFile.write(sql)
 
	for key in accounts:
		account = accounts[key]
 
		username  = sqlEscape(account.get('username',  ""))
		password  = sqlEscape(account.get('password',  ""))
 
		score     = str(account.get('score',     "0"))
		email     = sqlEscape(account.get('email',     ""))
 
		line = "INSERT INTO accounts (username, password, score, email) VALUES "
		line += "('" + username + "', '" + password + "', " + score + ", '" + email + "');\n";
 
		outputFile.write(line)
 
print "Done!"

Python has a nice CSV reader. I first read the account.csv file and create a dictionary, with the account id as key and as value – another dictionary containing username and password. Then, I read the account_attribute.csv and insert the new attributes in the corresponding dictionary entry based on key. Next, I create the output file, write the code for creating the table and output all values as insert statements. Note how I only take the attributes that I need from the dictionary.

Automating the conversion

With everything in place now, the conversion can be automated.

This little ij script will export everything from the Union database:

CONNECT 'jdbc:derby:/path/to/union/unionDB';
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP','ACCOUNT','account.csv',NULL,NULL,NULL);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP','ACCOUNT_ATTRIBUTE','account_attribute.csv',NULL,NULL,NULL);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP','ROOM','room.csv',NULL,NULL,NULL);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP','ROOM_ATTRIBUTE','room_attribute.csv',NULL,NULL,NULL);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP','SERVER_ATTRIBUTE','server_attribute.csv',NULL,NULL,NULL);

Save it as export.sql

The last step is creating a small shell script:

1
2
3
4

#!/bin/sh
ij export.sql
python processdata.py
mysql --user=********** --password=********** union < accounts.sql

The first step runs export.sql (assuming you have the Derby tools installed and ij in path). The next step is the Python conversion script. The last step executes the generated SQL into the MySQL union database.

Assuming Union and MySQL are running and you’ve already edited the Union start script and union.xml, you’d stop Union, run the script and start Union again. The total downtime should be less than 5 minutes (depending on your server and database size).

Of course, you should try this on a testing server first, one that replicates the live environment, just to make sure you don’t hit any snags.

Conclusion

Your own requirements will most certainly be different, as your server environment. Nevertheless, I hope you’ll be able to use this article as a guideline.

Related posts:

Posted in , | Tagged , , , , | Leave a response

Armand Niculescu, BEng, MSM, is a 34 year old Art Director at Media Division. and he enjoys working with visual arts for film, web and print.

Leave a Reply


six − = 2