[Arianne-commits] stendhal/src/games/stendhal/tools/port1_2 port.sql, 1.3, 1.4 TablesToBlob.java, 1.3, 1.4

[Hendrik <nh...@us...>]

Update of /cvsroot/arianne/stendhal/src/games/stendhal/tools/port1_2
In directory sc8-pr-cvs11.sourceforge.net:/tmp/cvs-serv28375/src/games/stendhal/tools/port1_2

Modified Files:
	port.sql TablesToBlob.java 
Log Message:
increased performance of porting code by reading the old_object_id instead of converting rpattribute.value and player.username into binary columns to do the join there.

Index: TablesToBlob.java
===================================================================
RCS file: /cvsroot/arianne/stendhal/src/games/stendhal/tools/port1_2/TablesToBlob.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** TablesToBlob.java	10 Oct 2007 18:24:41 -0000	1.3
--- TablesToBlob.java	12 Oct 2007 23:37:56 -0000	1.4
***************
*** 4,11 ****
--- 4,15 ----
  package games.stendhal.tools.port1_2;
  
+ import games.stendhal.server.StendhalRPWorld;
+ 
+ import java.io.IOException;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
+ import java.util.Properties;
  
  import marauroa.common.Configuration;
***************
*** 14,17 ****
--- 18,22 ----
  import marauroa.server.game.db.JDBCDatabase;
  import marauroa.server.game.db.JDBCTransaction;
+ import marauroa.server.game.db.NoDatabaseConfException;
  import marauroa.server.game.db.Transaction;
  
***************
*** 189,194 ****
  		System.out.println();
  		Configuration.setConfigurationFile("server.ini");
  
! 		JDBCDatabase db = JDBCDatabase.getDatabase(); 
  
  		Transaction trans = db.getTransaction();
--- 194,200 ----
  		System.out.println();
  		Configuration.setConfigurationFile("server.ini");
+ 		StendhalRPWorld.get();
  
! 		PortJDBCDatabase db = new PortJDBCDatabase(); 
  
  		Transaction trans = db.getTransaction();
***************
*** 204,208 ****
  
  			long p2 = System.currentTimeMillis();
! 			db.storeCharacter(trans, object.get("name"), object.get("name"), object);
  			trans.commit();
  			long p3 = System.currentTimeMillis();
--- 210,214 ----
  
  			long p2 = System.currentTimeMillis();
! 			db.storeRPObject(trans, object);
  			trans.commit();
  			long p3 = System.currentTimeMillis();
***************
*** 211,213 ****
--- 217,242 ----
  		}
  	}
+ 	
+ 	/**
+ 	 * PortJDBCDatabase enables access to internal methods of JDBCDatabase
+ 	 * needed for low level access.
+ 	 *
+ 	 * @author hendrik
+ 	 */
+ 	class PortJDBCDatabase extends JDBCDatabase {
+ 
+ 		/**
+ 		 * creates a new PortJDBCDatabase
+ 		 *
+ 		 * @throws NoDatabaseConfException in case of an configuration error
+ 		 */
+ 		public PortJDBCDatabase() throws NoDatabaseConfException {
+ 			super(getInitProps());
+ 		}
+ 
+ 		@Override
+ 		public int storeRPObject(Transaction transaction, RPObject object) throws IOException, SQLException {
+ 			return super.storeRPObject(transaction, object);
+ 		}
+ 	}
  }

Index: port.sql
===================================================================
RCS file: /cvsroot/arianne/stendhal/src/games/stendhal/tools/port1_2/port.sql,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** port.sql	10 Oct 2007 18:25:05 -0000	1.3
--- port.sql	12 Oct 2007 23:37:56 -0000	1.4
***************
*** 1,26 ****
  -- deleted banned user with invalid characters
  DELETE FROM marauroa.rpobject USING marauroa.rpobject, marauroa.rpattribute
! WHERE rpobject.object_id = rpattribute.object_id AND rpattribute.name LIKE 'name%' AND rpattribute.value='Snaketails>oB<';
! DELETE FROM marauroa.characters WHERE charname='Snaketails>oB<';
! DELETE FROM marauroa.player WHERE username='Snaketails>oB<';
  
- -- delete deprecated attributes
- DELETE FROM marauroa.rpattribute WHERE name in ('fullghostmode', 'features');
  
  
  INSERT INTO account(id, username, password, email, timedate, status)
  SELECT id, username, password, email, timedate, status FROM marauroa.player;
  
! alter table marauroa.rpattribute CHANGE value value BINARY(255);
! alter table marauroa.player CHANGE username username BINARY;
  
  INSERT INTO characters(player_id, charname, object_id)
! SELECT player.id, rpattribute.object_id, value 
! FROM marauroa.rpattribute, marauroa.player, marauroa.rpobject 
! WHERE player.username = rpattribute.value AND rpattribute.name LIKE 'name%' 
! AND rpobject.object_id=rpattribute.object_id AND rpobject.slot_id=0;
  
  insert into rpobject(object_id)
! select object_id from marauroa.characters;
  
  insert into banlist(id, address, mask)
--- 1,33 ----
  -- deleted banned user with invalid characters
  DELETE FROM marauroa.rpobject USING marauroa.rpobject, marauroa.rpattribute
! WHERE rpobject.object_id = rpattribute.object_id AND rpattribute.name LIKE 'name%' AND rpattribute.value LIKE 'Snaketails>oB<%' AND slot_id = 0;
! DELETE FROM marauroa.characters WHERE charname LIKE 'Snaketails>oB<%';
! DELETE FROM marauroa.player WHERE username LIKE 'Snaketails>oB<%';
  
  
+ -- delete deprecated attributes
+ DELETE FROM marauroa.rpattribute WHERE name LIKE 'fullghostmode%';
+ DELETE FROM marauroa.rpattribute WHERE name LIKE 'features%';
  
  INSERT INTO account(id, username, password, email, timedate, status)
  SELECT id, username, password, email, timedate, status FROM marauroa.player;
  
! --ALTER TABLE marauroa.rpattribute CHANGE value value BINARY(255);
! --ALTER TABLE marauroa.player CHANGE username username BINARY(255);
! 
! -- INSERT INTO characters(player_id, charname, object_id)
! -- SELECT player.id, rpattribute.value , rpattribute.object_id
! -- FROM marauroa.rpattribute, marauroa.player, marauroa.rpobject 
! -- WHERE player.username = rpattribute.value AND rpattribute.name LIKE 'name%' 
! -- AND rpobject.object_id=rpattribute.object_id AND rpobject.slot_id=0;
  
  INSERT INTO characters(player_id, charname, object_id)
! SELECT player.id, player.username, rpobject.object_id
! FROM marauroa.player, marauroa.rpobject, marauroa.characters
! WHERE player.username = characters.charname AND rpobject.old_object_id=characters.object_id AND rpobject.slot_id=0;
! 
  
  insert into rpobject(object_id)
! select object_id from characters;
  
  insert into banlist(id, address, mask)