Linux: phpAH v3

[PrBlahBlahtson]

So a while back, msulli1355 had wanted an auction house tool for Linux, and I could sort of picture a way to do it in my head, but didn't feel motivated to create it. I also knew I was about the worst PHP coder in history, so the result wouldn't be pretty.

I got bored, though.

Versions:
v1 - Total crap
v2 - Better, but still lacking
v3 - Full restock, 10 of each item, 3 seconds. Ready for practical usage.

Change the variables at the beginning of the script to match your SQL server settings.
$price is how much to sell each item for.
$forsale is how many of every item you want to be for sale.
$debug = true; will just give you status messages so you have a rough idea of what the tool is doing, without being too noisy. 6 rows are output, +1 for every 20,000 sales if sales > 20,000.
$debugv = true; will tell you any time the script adds something to the database. This is a LOT more verbose, including a debug message for each line added to the queries.

php <filename> and away you go. Add to cron for regularly scheduled auction house restocks. It's safe now.

The tool lists everything that can be sold on the AH, so Judge's equipment will be available. There are probably ways to address that, but I'm unaware of phpAH running on a public server yet. The tool itself will make sure there's a sales history listing to give price hints, and will add up to $forsale sales at $price gil, defaults being 10 and 100 gil each. The gil for any purchased items is discarded by the auction_house triggers.

I learned a bit about arrays from this project, so even if no one uses it, I had fun and learned from it.

I also feel it's absolutely necessary to thank Stubbs for writing the queries that make this tool work. All I really did from there is connect the queries to the database, then use their outputs to stock things up. Otherwise, you'd be getting 10-minute long restocks that grind your server to a halt.

v3:

Code: Select all

#!/usr/bin/php
<?php
// Kudos to Stubbs for the query.  Miles beyond what I could have produced.
// History query is based on his work

define('DS_HOST', "localhost");
define('DS_USER', "root");
define('DS_PASS', "password");
define('DS_SCHEMA', "dspdb");

// Set the price for everything.
$price = "100";

// How many you want for sale at a minimum.  Directly increases script's running time.
$forsale = "10";

// Displays status messages and logic.
$debug = false;

// Tells you whenever the script does something, like add an item.
$debugv = false;

if (!defined('PHP_EXECUTABLE'))
	define('PHP_EXECUTABLE', '/usr/bin/php');
	
function db_connect($dbhost, $dbuser, $dbpass, $dbschema) {
	$link = mysql_connect($dbhost, $dbuser, $dbpass);
	if ($link) {
		$result = mysql_select_db($dbschema, $link);
		if (!$result) {
			die("Can't select schema: " . mysql_error($link));
		}
		return $link;
	} else {
		die("Unable to connect to database (as $dbuser to $dbhost, database $dbschema): " . mysql_error());
	}
}

// Submits $query to $link, dumps an error if no connection
function db_query($link, $query, $die_on_error = true) {
	$result = mysql_query($query, $link);
	if (!$result) {
		$query = htmlspecialchars($query);
		if ($die_on_error) {
			die("Query <i>$query</i> failed: " . ($link ? mysql_error($link) : "No Connection"));
		}
	}
	return $result;
}

function db_close($link) {
	return mysql_close($link);
}

// Send messages to the prompt
function _debug($msg) {
	$ts = strftime("%H:%M:%S", time());
	if (function_exists('posix_getpid')) {
		$ts = "$ts/" . posix_getpid();
	}
	print "[$ts] $msg\n";
}

// Open transaction
$link = db_connect(DS_HOST, DS_USER, DS_PASS, DS_SCHEMA);

// Fetch any items missing auction house history entries.
$sql = mysql_query("SELECT itemid, 1 as stackSize FROM item_basic WHERE aH > 0 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 0 AND buyer_name <> '')
	UNION SELECT itemid, stackSize FROM item_basic WHERE  aH > 0 AND stackSize > 1 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 1 AND buyer_name <>'')");
$history = array();
while($line = mysql_fetch_assoc($sql)) {
	$history[] = $line;
}
if ($debug) _debug("Missing history entries: " . count($history));

// Fetches items that need to be restocked; separate rows for individuals and stacks.
$sql = mysql_query("SELECT itemid, stackSize, COUNT(sale) AS saleCount
FROM (SELECT Item.itemid, Item.stackSize, AH.itemid AS sale
	FROM (SELECT itemid, 1 as stackSize
		FROM item_basic
		WHERE aH > 0
		UNION
		SELECT itemid, stackSize
		FROM item_basic
		WHERE  aH > 0 AND stackSize > 1) as Item
	LEFT JOIN auction_house AH
	ON Item.itemid=AH.itemid AND ((stackSize=1 AND AH.stack=0) OR (stackSize>1 AND AH.stack<>0)) AND AH.buyer_name IS NULL) AS T2
GROUP BY itemid, stackSize
HAVING saleCount < $forsale");
$sales = array();
while($line = mysql_fetch_assoc($sql)) {
	$sales[] = $line;
}
if ($debug) _debug("Sales to restock: " . count($sales));

if ($debug) _debug("Arrays are complete.  Generating queries.");
if ($debugv) _debug("Stack = 1 indicates a stack, 0 indicates individual.");

$rowsadded=0;
db_query($link, "BEGIN");

// Add missing history entries for price data.
$send = array();
if(count($history) > 0) {
	foreach($history as $row) {
		if($row['stackSize'] > 1){
			$stack = 1;
		} else {
			$stack = 0;
		}
		//itemid, stack, seller_name, date, price, buyer_name, sale, sell_date
		$send[] = "(".$row['itemid'].", ".$stack.", 'phpAH', 1325482385, ".$price.", 'phpAH', ".$price.", 1325482385)";
		if ($debugv) _debug("Adding history entry for item: ".$row['itemid']." where stack = ".$stack);
		$rowsadded++;
	}
	$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price, buyer_name, sale, sell_date) VALUES ".implode(", ",$send));
}
if ($debug) _debug("History completed.  Adding sales.");

// Empty the variable
$send = array();

// Restock the auction house
if(count($sales) > 0){
	foreach($sales as $row){
		if($row['stackSize'] > 1) {
			$stack = 1;
		} else {
			$stack = 0;
		}
		$i = $row['saleCount'];
		while($i < $forsale) {
			//itemid, stack, seller_name, date, price
			$send[] = "(".$row['itemid'].", ".$stack.", 'phpAH', 1325482385, ".$price.")";
			if ($debugv) _debug("Adding sale for item: ".$row['itemid']." where stack = ".$stack);
			$rowsadded++;
			$i++;
		}
		if (count($send) > 20000) {
			// Max possible size for $send in default conditions is around 26315 rows.
			// Therefore, we send the INSERT immediately before that limit is reached.
			// Theoretically it still fails if you're listing more than 1,000 of each item, but I think you'll break DS or FFXI first.
			if ($debug) _debug("Approaching default max packet size for MySQL.  Sending INSERT and clearing variable.");
			$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price) VALUES ".implode(", ",$send));
			$send = array();
		}
	}
	$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price) VALUES ".implode(", ",$send));
}
if ($debug) _debug("Sales completed.");

if ($debug or $debugv) _debug("Final rows added: " . $rowsadded);

db_query($link, "COMMIT");
db_close($link);
?>

Possible Improvements?
- Modify queries and create an array to limit the tool to certain categories?
- Similar, but to exclude taboo items, like L75+ armor and GM gear.
- The history query might be dangerously close to the maximum size.
- Max still fail for large (6+ digit) $price values, or combinations of $price and $forsale.

Edit 3:
Neatened up the first post substantially Current performance is approximately 3 seconds to do a total auction house restock to 10 items.

Edit 3.1:
The 25k auto-insert would have left space for approximately 50,000 characters, but I kept worrying that some unforeseen price+forsale combo would break it. Lowered to 20k, which should leave room for 240,000 characters in total. Should be completely safe, unless impractical values are used, with minimal impact upon practical usage.

[Stubbs]

I was thinking of a different approach which should greatly reduce some of the memory/cpu problems. I'll try to pseudocode it here.

---Building history for all items---
1. Select all AH-able items where the history count is zero.(should be able to do this with single sql statement)(should also return zero entries after first time program runs)
2. Add history item for each.

---Building auction list---
1. Select all AH-able items where count for sale is less than $forsale.(single sql query, go ahead and save the sale count)
2. For each item, add auctions until $forsale is reached

The first time the program builds the auction list, there will be $forsale insert statements for every possible item. The second time the program runs, there will only be one insert statement for every item sold during the time since the first run.

tldr
First run through is going to have 2 select statements, tons of inserts.
Second...third...etc. run through will only have 2 selects and (n) inserts, (n) being the number of items sold since the last time the program was run.

This is a really rough sketch of concept, but it seems to yield the intended results. I can figure out the select statements soon hopefully.

Edit: Still need to account for stacks somewhere I think

[PrBlahBlahtson]

I might try that approach next, just for fun.

I decided to take off my gloves and just slam the server with SQL queries to see how fast it could digest things if I wasn't trying to hold off from hammering it.

Fully stocked AH, worst case scenario... About 30-40% load on XP, slightly increased but not... horrible. About 5-10% load on Linux, as expected. Time?

41 seconds. Second run just to verify that. 42 seconds.

/poker face

（╯°□°）╯︵┻━┻

That's a fair bit better than 100% utilization for 6-12 minutes straight if they're on the same machine.

I also fixed a bug. Navicat says buyer_name is null on unsold items, but PHP disagrees. History was being entered every time, while I only cared that it wasn't blank. The above code is corrected to still use arrays, but I attached PHP files for arrays or queries for good measure. I still think sending around 30,000 SELECT queries is not an acceptable solution. The performance is hard to argue with, though. The $basicid array remains in use, simply because it saved a lot of code changes.

phpAH arrays.php

v2

(5.75 KiB) Downloaded 564 times

phpAH queries.php

v2, Sadistic branch

(5.35 KiB) Downloaded 551 times

Now then, some theory crafting...

1. Select all AH-able items where the history count is zero.(should be able to do this with single sql statement)(should also return zero entries after first time program runs)

SELECT itemid, stackSize FROM item_basic WHERE aH > 0 AND itemid NOT IN (SELECT itemid FROM auction_house WHERE stack = 0 AND buyer_name <> '');
foreach($row) create history.

1. Select all AH-able items where count for sale is less than $forsale.(single sql query, go ahead and save the sale count)

Biggest problem here is that count() guarantees you will only get 1 row with the count in it in SQL. Number of queries depends on the variety of AH activity. It's inescapable as far as I've been able to determine. I like learning new things though. I still haven't figured out a way to include/exclude based on count in SQL though. It's not very good at the whole "conditional" thing.

SELECT itemid, COUNT(stack) FROM auction_house WHERE itemid = $variable AND stack = 0 AND buyer_name = '';
while count < $forsale, add item, count++

Edit: Still need to account for stacks somewhere I think

Run another query where stack = 1 to get stacks. Don't really think there's a way to do that short of separate loops, or funky array/index methods like I used. I may come up with a wacky solution later, but I think 6 hours of coding is enough for today. Unless I change my mind.

[Stubbs]

I came up with rather long, probably improvable query that returns a list of itemid's and how many AH listings it has as long as it has less listings than $forsale(I used 10 in this example).

Any item that has $forsale or more AH listings will not be returned, meaning that no listings need to be added for it.

Code: Select all

SELECT itemid, COUNT(sale) AS saleCount
FROM (SELECT Item.itemid, AH.itemid AS sale
	FROM (SELECT *
		FROM item_basic Item2
		WHERE Item2.aH > 0) as Item
	LEFT JOIN auction_house AH
	ON Item.itemid=AH.itemid) AS T2
GROUP BY itemid
HAVING saleCount <= 10;

You can then loop through this returned list and add the needed number of items.

Edit: On my machine, assuming that the AH is full and no items need to be added to it, it shouldn't take more than a second or two for the program to decide the AH is full and the program is finished...maybe only a few seconds to top-off the AH with new items.

[PrBlahBlahtson]

Oi, those queries always make my head hurt to puzzle out. That would neatly solve the problem, though.

Edit:
Hmm, think I dismissed stacks too quickly, particularly around histories...

Edit v2:
Ended up with these queries for stacks:

Code: Select all

SELECT itemid FROM item_basic WHERE aH > 0 AND stackSize > 1 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 1 AND buyer_name <> '');

Code: Select all

SELECT itemid, COUNT(sale) AS saleCount FROM (SELECT Item.itemid, AH.itemid AS sale
	FROM (SELECT * FROM item_basic Item2 WHERE Item2.aH > 0 AND Item2.stackSize > 1) as Item
	LEFT JOIN auction_house AH ON Item.itemid=AH.itemid WHERE AH.stack = 1) AS T2
	GROUP BY itemid HAVING saleCount <= 10;

Preliminary tests look good, but I have no intention of finishing another version tonight.

Interestingly enough, I original had "AND AH.stack = 1"... and it worked.

[Stubbs]

You might like this one.

Code: Select all

SELECT itemid, stackSize, COUNT(sale) AS saleCount
FROM (SELECT Item.itemid, Item.stackSize, AH.itemid AS sale
	FROM (SELECT itemid, 1 as stackSize
		FROM item_basic
		WHERE aH > 0
		UNION
		SELECT itemid, stackSize
		FROM item_basic
		WHERE  aH > 0 AND stackSize > 1) as Item
	LEFT JOIN auction_house AH
	ON Item.itemid=AH.itemid AND ((stackSize=1 AND AH.stack=0) OR (stackSize>1 AND AH.stack<>0))) AS T2
GROUP BY itemid, stackSize
HAVING saleCount < 10;

This returns 3 columns itemid, stackSize, and saleCount for all items that have less than 10 current auctions, stacks included. itemid will be listed twice with two different stack sizes if it is a stackable item. The correct number of current sales will appear next to stacks and singles. My laptop was able to squeeze out the result in 0.05sec.

It should also be possible to do bulk inserts with sql that should shave a lot of time opposed to looping and creating one listing at a time.

[PrBlahBlahtson]

I definitely need to learn how to do more advanced queries.

You've built a core that makes the whole thing work so much better, Stubbs. I'm just plugging it in, at this point. I'll try to get a script written and polished in the next few days.

[PrBlahBlahtson]

Stumbled through making a history query, which went surprisingly smoothly. In my experience, that = bugs galore.

Code: Select all

SELECT itemid, 1 as stackSize FROM item_basic WHERE aH > 0 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 0 AND buyer_name IS NOT NULL)
	UNION SELECT itemid, stackSize FROM item_basic WHERE  aH > 0 AND stackSize > 1 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 1 AND buyer_name IS NOT NULL)

Came upon a small versioning issues. Older versions used '' for the buyer_name, while NULL is the default. This led to some counting issues. DSPAH uses NULL, so I will correct my queries accordingly.

Taking a short break, but encountering a fun error.

Code: Select all

$send = array();
if(count($sales) > 0){
	foreach($sales as $row){
		if($row['stackSize'] > 1) {
			$stack = 1;
		} else {
			$stack = 0;
		}
		$i = $row['saleCount'];
		while($i < $forsale) {
			//itemid, stack, seller_name, date, price
			$send[] = "(".$row['itemid'].", ".$stack.", 'phpAH', 1325482385, ".$price.")";
			if ($debugv) _debug("Adding sale for item: ".$row['itemid']." where stack = ".$stack);
			$rowsadded++;
			$i++;
		}
	}
	$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price) VALUES ".implode(", ",$send));
}

The db_query function does its proper header, including "The query: <EXTERMELY LONG QUERY> failed: MySQL server has gone away" with my edit for brevity.

I'm guessing the server done kicked me for a ridiculously long insert :V

Edit:
Altering sales count to include "AND AH.buyer_name IS NULL" seems to fix the counting issue. Reducing $forsale to 1 let the script execute successfully, further reinforcing what I suspect is a query length limit. I'll see if I can find where it breaks, and go from there.

Edit v2:
Yeah, this is probably a max_allowed_packet (1000000), or query_cache_limit (1048576), or something similar. Seemed to break between 2 and 3 items for sale (10,248 rows per sale). Roughly 38 characters per inserted row, 10248 * 2 * 38 = 778848, 10248 * 3 * 38 = 1168272, which is greater than the default 1,000,000 max_packet_size. Rather than switch back to lots and lots of inserts, I'll probably change the code to send the query if count($send) > 25000 and then reset $send for the next pass. Performance right now is... exceptional. I'd say less than 5 seconds, unless you're selling more than 10 of each item.

[Stubbs]

- Similar, but to exclude taboo items, like L75+ armor and GM gear.

Individual server admins would just set the 'aH' value to 0 in the item_basic table to prevent that item from ever being listed.

[Melodina]

Due to my lack of looking on the forums, I created another style of this... so it's just another take on the same thing.
This one however, is more command line.

The only major difference, is this will have the option to pull the median price live from FFXIAH.com for your entries. I do not recommend it for a hourly crontab tho as it takes a while.

(interesting to compare writing styles!)

Code: Select all

*************************************************
AH Generator
*************************************************

  Requirements:
	- Lynx or Links or eLinks
	- PHP 5.4.x or higher
	- MySQL Extensions for PHP
	- Internet Connection (For FFXIAH Parse)


  Usage:
syntax: php gen.php options
        baseprice=x     set the base price for the items.
        amt=x           set the amount of items you wish to add, default is 3
        ffxi=true       this will take the median price from ffxiah. SLOW.(~5500 seconds)
        history=1       history lines for price, set to 0 if you do not wish to have
                        history. default 1
        clearah=1       this will clear the AH.  [Non-Recoverable]
        duplication=1   this will duplicate the last sold values under ffxiAH name.
                        (used in crons for ffxiah prices)

example: php gen.php baseprice=100 amt=10 history=1
example: php gen.php ffxi=true amt=10 history=2
example: php gen.php clearah=1
example: php gen.php duplication=1 amt=7


	This script was designed for crontabs, however the ffxi mode is not recommended in crontabs.
	This script was designed for *nix, and require a textmode browser for ffxi mode.
	

If using on windows (untested):
	Download Lynx and Compile for Windows (http://lynx.isc.org/lynx2.8.7/index.html)
	Download PHP for windows with MySQL connector (http://windows.php.net/download/)
	Place compiled Lynx and php files (php.exe, php5ts.dll, ext\php_mysql.dll, ext\php_mysqli.dll) in same dir as the php script.
	Please note, you will need the VC9 Libraries installed on your machine (The download page for windows php has link)
	run using the options above from command line. (you can create a batch file for Windows Scheduler)

EDIT:
I found a bad bad bug... it was only adding to the history of the AH. I have since fixed it. I will release another ver when I have finished duplicating the entries from history (for those who need cron, but want FFXIAH prices.. this will be much faster) Attached new replacement ver.

2: This is now completed.