[Arianne-commits] stendhal/src/games/stendhal/tools/statistics items.sql, NONE, 1.1 ItemDumper.java, NONE, 1.1

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

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

Added Files:
	items.sql ItemDumper.java 
Log Message:
initial checkin

--- NEW FILE: ItemDumper.java ---
package games.stendhal.tools.statistics;

import games.stendhal.server.StendhalPlayerDatabase;
import games.stendhal.server.StendhalRPWorld;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import marauroa.common.Configuration;
import marauroa.common.game.RPObject;
import marauroa.common.game.RPSlot;
import marauroa.server.game.GenericDatabaseException;
import marauroa.server.game.JDBCPlayerDatabase;
import marauroa.server.game.JDBCTransaction;
import marauroa.server.game.Transaction;

/**
 * Dumps the items of all players into a table called items
 *
 * @author hendrik
 */
public class ItemDumper {
	JDBCPlayerDatabase db;
	Transaction trans;
	PreparedStatement ps;
	java.sql.Date date;

	/**
	 * Creates a new ItemDumper
	 *
	 * @param db JDBCPlayerDatabase
	 * @throws GenericDatabaseException if no database connection can be created
	 */
	private ItemDumper(JDBCPlayerDatabase db) throws GenericDatabaseException {
		this.db = db;
		this.trans = db.getTransaction();
	}

	/**
	 * dumps the items
	 *
	 * @throws Exception in case of an unexspected Exception
	 */
	private void dump() throws Exception {
		JDBCPlayerDatabase.RPObjectIterator it = db.iterator(trans);
		String query = "insert into items(datewhen, charname, slotname, itemname, amount) values(?, ?, ?, ?, ?)";
	    date = new java.sql.Date(new java.util.Date().getTime());
		Connection connection = ((JDBCTransaction) trans).getConnection();
		ps = connection.prepareStatement(query);

		while (it.hasNext()) {
			int id = it.next();
			RPObject object = db.loadRPObject(trans, id);
			String name = object.get("name");
			for (RPSlot slot : object.slots()) {
				String slotName = slot.getName();
				for (RPObject item : slot) {
					if (item.has("type") && item.get("type").equals("item")) {
						logItem(name, slotName, item);
					}
				}
			}
		}
		ps.close();
		trans.commit();
	}

	/**
	 * logs an item
	 *
	 * @param name     character name
	 * @param slotName slot name
	 * @param item     item name
	 * @throws SQLException in case of a database error
	 */
	private void logItem(String name, String slotName, RPObject item) throws SQLException {
	    String itemName = item.get("name");
	    int quantity = 1;
	    if (item.has("quantity")) {
	    	quantity = item.getInt("quantity");
	    }
	    ps.setDate(1, date);
		ps.setString(2, name);
		ps.setString(3, slotName);
		ps.setString(4, itemName);
		ps.setInt(5, quantity);
		ps.executeUpdate();
    }

	/**
	 * starts the ItemDumper
	 *
	 * @param args ignored
	 * @throws Exception in case of an unexspected item
	 */
	public static void main(String[] args) throws Exception {
		StendhalRPWorld.get();
		Configuration.setConfigurationFile("marauroa.ini");
		JDBCPlayerDatabase db = (JDBCPlayerDatabase) StendhalPlayerDatabase.resetDatabaseConnection();
		ItemDumper itemDumper = new ItemDumper(db);
		itemDumper.dump();
	}
}

--- NEW FILE: items.sql ---
create table if not exists items
  (
  id integer auto_increment not null,
  datewhen date,
  charname varchar(32),
  slotname varchar(32),
  itemname varchar(32),
  amount integer,
  primary key(id)
) TYPE=INNODB;

CREATE INDEX i_items_charname ON items(charname);
CREATE INDEX i_items_slotname ON items(slotname);
CREATE INDEX i_items_itemname ON items(itemname);
CREATE INDEX i_items_amount ON items(amount);