Finding the best tokens to money ratio from mission items in Lego Universe
In the self-hostable Darkflame Lego Universe game server, you can earn 2 types of currency: money, and faction tokens. In the late game, you accumulate these in spades, but only money is still the most useful. So a lot of advanced players are interested in determining the most efficient way to convert faction tokens into money.
The way to do this scientifically is within the database. All the mission objects are stored in the CDServer.sqlite file, so we can use sqlite3 for this.
I have a whole methodology of what I did, in heading Narrative below. It's very lengthy, and really just my scratch pad combined with the steps I took to become familiar with the database.
tl;dr
All the experienced Lego Universe players are right: Castle Model Pack 3. You can buy this for 10 tokens at Honor Accolade in Nexus Tower, and sell the items from it for 798 money, which makes the token:money ratio 1:79.
Fun picture showing ratio of money to tokens at 79:1
Sql query
You need at least sqlite 3.39 for the FULL JOIN operation. My narrative explains the same query without it, but you're not going to like it.
Technically this query is still incomplete because it shows some of the "small number of random options for rewards" but they also aren't available at Honor Accolade.
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
FROM Objects o
JOIN ComponentsRegistry cr ON cr.id = o.id
JOIN ItemComponent ic ON ic.id = cr.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11)
--AND m.id IN(427,14,17,82,633)
GROUP BY o.name
) i --items
FULL JOIN (
SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
FROM (
SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type = 53
AND cr2.component_type = 11
--AND m.id IN (427,14,17,82,633,1254)
AND lm.percent > 0.98 AND lm.minToDrop >=1
AND o.id > -1
GROUP BY o.id
)
GROUP BY oid
) p --packages
ON i.oid = p.oid
ORDER BY moneypertoken DESC
;
Inferior sql query
The query without full joins and is slightly more flawed, is this one. You can run this on CentOS 7 sqlite3 (less than 3.39).
