Design query for sell orders

[darozak]

I need to create a set of market queries that will allow players to identify resources that are being bought or sold on the market.

[darozak]

I only need to create two market queries. The first will fetch the best buy prices and the second will list the best sell prices for a particular resource. Each query will accept a resource ID and return the drone, limit, and asking price of each bid, ordered by increasing or decreasing asking prices based on whether it is a sell (increasing) or buy (decreasing) bid. The number of returned bids will be automatically limited by the size of the results buffer.

[darozak]

Since we're unable to use the "first" function in MySQL, I had to use a subquery to first create a list of drones and the most recent times that they posted a successful sell query for the targeted resource. I then ran an inner join with the events table in order to limit the output to only the events which match the drones and timestamps in the subquery.

Here is an example that appears to be working in the MySQL Workbench:

SELECT e.drone, e.resource, e.new_quantity, e.new_credits
FROM exmachinis.events AS e
    INNER JOIN (
        SELECT drone, max(timestamp) AS my_time -- list all drones and the most recent time they posted a sell order for the resource
        FROM exmachinis.events
        WHERE outcome = 1 AND event_type = 4 AND resource = 1010
        GROUP BY drone) 
        AS current_transactions
    ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone
ORDER BY e.new_credits ASC;

I tested this by having two of my drones (Speed and Rivet) post and then update competing sell orders.

The next step will be to add this query to the economy spreadsheet and install the query in the DB.

[darozak]

It would actually better if I could design the query so that it subtracts the sell threshold from the amount of resource the drone has on hand to report back the amount that is actually for sale. I should be able to do this joining the events table with another subquery that lists the amount of the resource a drone has on hand. This would be a very complex query but it should be doable.

[darozak]

Created and tested new query to report back the amount of a particular resource that's in a drone's inventory:

SELECT e.drone, e.resource, e.new_quantity
FROM exmachinis.events AS e
    INNER JOIN (
        SELECT drone, max(timestamp) AS my_time -- list all drones and the most recent time they posted a sell order for the resource
        FROM exmachinis.events
        WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = 1000 AND drone = 52
        GROUP BY drone) AS current_inventory
    ON e.timestamp = current_inventory.my_time AND e.drone = current_inventory.drone
ORDER BY e.new_credits ASC;

Now I just need to combine this with the market query to figure out how much of an offered resource is actually available for purchase.

[darozak]

Simplified the inventory query to the following:

SELECT e2.drone, e2.new_quantity AS available
FROM exmachinis.events AS e2
    INNER JOIN (
        SELECT drone, max(timestamp) AS my_time 
        FROM exmachinis.events
        WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = 1000
        GROUP BY drone) AS current_inventory
    ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone;

[darozak]

Here is the combined query that appears to work as expected. However, I'll need to test it more fully to make sure there are now errors in the values it returns:

/* This query is designed to list all those drones that are selling a specified resource
and actually have the resource available in their inventory to sell.  It reports back the
drone, asking price, and amount of resource that is available to sell.  */

SELECT 
    e.drone, 
        e.new_credits AS price,
    IF(i.available > e.new_quantity, i.available - e.new_quantity, 0) AS quantity
        FROM exmachinis.events AS e
    INNER JOIN (
        /* Table of drones and most recent sell order for a particular resource. */
        SELECT drone, max(timestamp) AS my_time
        FROM exmachinis.events
        WHERE outcome = 1 AND event_type = 4 AND resource = 1000
        GROUP BY drone) 
        AS current_transactions
    ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone
        INNER JOIN (
        /* Table of drones and the amount of the resource in their cargo hold. */
        SELECT e2.drone, e2.new_quantity AS available
        FROM exmachinis.events AS e2
            INNER JOIN (
                /* Table of drones and most recent change in resource quantity. */
                SELECT drone, max(timestamp) AS my_time 
                FROM exmachinis.events
                WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = 1000
                GROUP BY drone) AS current_inventory
            ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i
        ON e.drone = i.drone
ORDER BY e.new_credits ASC;

[darozak]

Here is a compressed version of the query, which returns price, quantity, and drone, in that order, and uses "[value_1] to specify the targeted resource. This is the text that I'll use when defining the query in the queries table.

SELECT e.new_credits AS price, IF(i.available > e.new_quantity, i.available - e.new_quantity, 0) AS quantity, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone ORDER BY e.new_credits ASC; 

[darozak]

I'm limiting this particular issue to developing and testing an in-game query for sell orders. I'll tackle the buy order query in under another issue.

[darozak]

I added the sell orders query to the ecconomy.xls spreadsheet (https://github.com/Darwin-River/Ex-Machinis/commit/06d8aa0da4cfed02a9cee2e3cf0cc89989c20ce0). However, when I tried to upload it to the database I got an error saying that that the query text (851 chars) was to big for the table field. It looks like I'll need to increase the size of this field.

[darozak]

Increased the size of the VARCHAR queries.script field from 512 to 2000.

[darozak]

This time when I tried to add the get sell orders query to the database it accepted it.

[darozak]

For some reason it's now taking much longer for MySQL Workbench to run the sell orders query. This may be tripping up the game engine when it tries to execute the query via FORTH.

[darozak]

The query is running much faster now. I don't know what was wrong before. However, it still doesn't solve the problem I'm encountering when I try to run the vFetchSellOrders command from my drone. The command causes the system to hang up and I get a buffer overflow error when I try to stop and then restart the system. The only way I could resolve this was by deleting the drones vm in the agents table.

[darozak]

I think I know what the problem is! I expanded the size of the query.script field from 512 to 2000. When I did similarly expanded the size of commands.email_content a while ago to allow for longer email scripts I also got a buffer overflow error and Carlos told me I need to change the C code so that it can accommodate the new field size (https://github.com/Darwin-River/Ex-Machinis/issues/172). I probable need to do the same thing for the max script size in the C code. I'll look into this.

[darozak]

Found what I was looking for in common_types.h:

#define MAX_QUERIES_SCRIPT_SIZE       512

Need to increase this to 2000 and recompile the code.

[darozak]

This worked! (https://github.com/Darwin-River/Ex-Machinis/commit/4e7e845bd8e6f4f4d14f3f7779915d2a4cd8ff7f) I no longer get an overflow error and the query returns results but I'm not sure I'm getting the fields that I'm looking for.

[darozak]

It appears that the C++ implemented MySQL query is unable to accommodate logic or math function in the select clause. It doesn't return the results of the if statement or even a simple subtraction. I only get three field returned if I modify the query script to the following:

SELECT e.new_credits AS price, e.new_quantity AS quantity, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone ORDER BY e.new_credits ASC; 

[darozak]

One work-around is to have the query return both the available amount and the floor that was set by the sell order. This would leave it for the drone to subtract the two to determine whether any materials are available for sale. Here's what it looks like:

SELECT e.new_credits AS price, i.available, e.new_quantity AS quantity, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone ORDER BY e.new_credits ASC; 

When I tested this query, it worked perfectly, returning four values to the stack.

[darozak]

Unfortunately, I can't even get the query to wok if I try to stash the if statement away in a sub query as follows:

SELECT output.drone, output.price, output.quantity FROM (SELECT e.new_credits AS price, IF(i.available > e.new_quantity, i.available - e.new_quantity, 0) AS quantity, e.drone AS drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone ORDER BY e.new_credits ASC) AS output; 

There must be somethin in the C++ call that strips this information out. I'm going to return to the previous format of returning but the sell limit and the amount of the resource that the drone has in it's inventory. I'm just going to switch the order so that the limit is returned before the amount that is currently in the drone's inventory since that will make more sense:

SELECT e.new_credits, e.new_quantity, i.available, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone ORDER BY e.new_credits ASC; 

[darozak]

I added this new query script to the online DB and the ecconomy.xls file (https://github.com/Darwin-River/Ex-Machinis/commit/0006cd71c0d190bbc13c68b9f1f3f7c6fec9f656) and tested the vFetchSellOrders from my drone. Everything works well.

The last thing I need to do is fix vPrintMarketReport so that it works with the four values that are returned vice the original three.

[darozak]

I finished updating vPrintMarketReport to print out the a formatted list of the price, limit, stock, and source of each order. The same function will work to print out the results of a buy order query, which I need to design next (https://github.com/Darwin-River/Ex-Machinis/issues/252).

[darozak]

It just occurred to me that I can probably use the WHERE clause to only return results in which the quantity of reagent in the cargo hold exceeds limit. I'll implement and test this change.

[darozak]

The following script adds a where clause to limit results to those where the drone has sufficient resources in its inventory to make the sales:

SELECT e.new_credits, e.new_quantity, i.available, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = 1000 GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = 1000 GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone WHERE i.available > e.new_quantity ORDER BY e.new_credits DESC;

It appears to work as expected when run via MySQL Workbench.

[darozak]

I just need to update this in the economy spreadsheet and the MySQL database.

[darozak]

I updated the sell order query in the economy spreadsheet and mySQL exmachinis.queries table to the following (https://github.com/Darwin-River/Ex-Machinis/commit/88868938029b61df819f22688f788320d6d98f0e):

SELECT e.new_credits, e.new_quantity, i.available, e.drone FROM exmachinis.events AS e INNER JOIN ( SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND event_type = 4 AND resource = [value_1] GROUP BY drone) AS current_transactions ON e.timestamp = current_transactions.my_time AND e.drone = current_transactions.drone   INNER JOIN ( SELECT e2.drone, e2.new_quantity AS available FROM exmachinis.events AS e2 INNER JOIN (  SELECT drone, max(timestamp) AS my_time FROM exmachinis.events WHERE outcome = 1 AND locked = 0 AND (event_type = 1 OR event_type = 2) AND resource = [value_1] GROUP BY drone) AS current_inventory ON e2.timestamp = current_inventory.my_time AND e2.drone = current_inventory.drone ) AS i ON e.drone = i.drone WHERE i.available > e.new_quantity ORDER BY e.new_credits ASC;