def setUp(self):
from db.session import Session, DBState
from db.models import User, Item, DiscuzMember, DiscuzMemberCount
from db.base import Model
Model.metadata.drop_all(DBState.engine)
Model.metadata.create_all(DBState.engine)
s = Session()
[
s.add(i) for i in [
User(id=1,
username='******',
jiecao=100000,
ppoint=1000,
title='',
email='*****@*****.**'),
User(id=2,
username='******',
jiecao=100000,
ppoint=1000,
title='',
email='*****@*****.**'),
DiscuzMember(uid=1, username='******'),
DiscuzMember(uid=2, username='******'),
DiscuzMemberCount(uid=1, jiecao=100000),
DiscuzMemberCount(uid=2, jiecao=100000),
Item(id=1, owner_id=1, sku='foo', status='backpack'),
Item(id=2, owner_id=2, sku='bar', status='backpack'),
] if not options.freeplay
or 'DiscuzMember' not in i.__class__.__name__
]
s.commit()
def draw(uid, currency):
s = current_session()
u = Account.find(uid)
if not u:
raise exceptions.UserNotFound
helpers.require_free_backpack_slot(s, uid)
if currency == 'ppoint':
amount = constants.LOTTERY_PRICE
Account.add_user_credit(u, [('ppoint', -amount)],
exceptions.InsufficientFunds)
elif currency == 'jiecao':
amount = constants.LOTTERY_JIECAO_PRICE
Account.add_user_credit(u, [('jiecao', -amount)],
exceptions.InsufficientFunds)
else:
raise exceptions.InvalidCurrency
reward = random.choice(constants.LOTTERY_REWARD_LIST)
item = Item(owner_id=uid, sku=reward, status='backpack')
s.add(item)
s.flush()
s.add(ItemActivity(
uid=uid, action='lottery', item_id=item.id,
extra=json.dumps({'currency': currency, 'amount': amount}),
created=datetime.datetime.now(),
))
return reward
async def _create_new_items_for_alphas(category, alpha, pages, delta,
runeday):
"""
Searches through a category and alpha to get the new items
"""
url = 'http://services.runescape.com/m=itemdb_rs/api/catalogue/items.json?category=%s&alpha=%s&page=%s'
sql = 'SELECT id FROM _items WHERE id in (%s)'
for i in range(1, pages + 1):
items = (await rs.use_jca(url % (category, alpha, i)))['items']
ids = ','.join(str(item['id']) for item in items)
# Skipping if no items
if len(items) == 0:
continue
cached_items = [
i.id for i in await objects.execute(Item.raw(sql % ids))
]
# Creating items not already persisted
for item in (item for item in items
if item['id'] not in cached_items):
await Items._create_item(item, runeday)
delta -= 1
# No more items to update
if delta <= 0:
return
print(
'Did not create all items for category %s alpha %s. %s item(s) remaining.'
% (category, alpha, delta))
async def _get_alpha_numbers(category):
"""
Gets the alpha numbers for a given category
"""
sql = 'SELECT IF(SUBSTR(name, 1, 1) REGEXP \'^[0-9]\', \'#\', LOWER(SUBSTR(name, 1, 1))) as alpha, COUNT(*) as number ' \
'FROM _items ' \
'WHERE category = %s ' \
'GROUP BY alpha'
result = await objects.execute(Item.raw(sql, category))
# Populating all data
ret = {chr(i): 0 for i in range(ord('a'), ord('z') + 1)}
ret['#'] = 0
# Inserting data
for row in result:
if row.alpha is None:
continue
ret[row.alpha] = row.number
return ret
def give_item(adventurer_uid, item_name, description, count, attributes=[]):
item = Item(item_name, description, count, attributes)
with session_scope() as Session:
adventurer = Session.query(Adventurer).get(adventurer_uid)
inventory = adventurer.inventory
match = list(filter(lambda i: i.name == item.name, inventory))
if match:
match[0].count += item.count
else:
adventurer.inventory.append(item)
async def _jaro_winkler_name(name: str):
"""
Matches the partial name via jaro winkler. This is SLOW
:param name: The name of the item to jaro winkler match
"""
params = (name, )
sql = 'SELECT * ' \
'FROM _items ' \
'WHERE name LIKE \'%%{}%%\'' \
'ORDER BY sys.jaro_winkler(name, %s) DESC ' \
'LIMIT 1'.format(db.get_conn().escape_string(name))
item = await objects.execute(Item.raw(sql, *params))
return item[0] if item else None
async def _quick_match_name(name: str):
"""
Quickly matches a partial item name
:param name: The partial name to fuzzy match
"""
params = (name, ) * 3
sql = 'SELECT * ' \
'FROM _items ' \
'WHERE name LIKE %s ' \
'OR SOUNDEX(name) LIKE SOUNDEX(%s) ' \
'ORDER BY sys.jaro_winkler(Name, %s) DESC ' \
'LIMIT 1'
item = await objects.execute(Item.raw(sql, *params))
return item[0] if item else None
def add(uid, item_sku, reason=None):
s = current_session()
helpers.require_free_backpack_slot(s, uid)
item = Item(owner_id=uid, sku=item_sku, status='backpack')
s.add(item)
s.flush()
s.add(
ItemActivity(
uid=uid,
action='get',
item_id=item.id,
extra=reason and json.dumps(reason),
created=datetime.datetime.now(),
))
return item.id
async def fuzzy_match_name(name: str):
"""
Fuzzy matches a "Maybe" partial name
:param name: The name to match
:return:
The item and name of the matched item or none if the name was not matched
"""
# Checking DB first
ret = await objects.execute(
Item.raw(
'SELECT * '
'FROM _items '
'WHERE name LIKE %s '
'OR SOUNDEX(name) LIKE SOUNDEX(%s) '
'ORDER BY sys.jaro_winkler(Name, %s) DESC '
'LIMIT 1', name, name, name))
# Returning if name was matched to something
if ret:
return ret[0]
# Trying aliases
ret = await objects.execute(
Alias.raw(
'SELECT * '
'FROM aliases '
'JOIN _items ON item_id = id '
'WHERE alias LIKE %s '
'OR SOUNDEX(alias) LIKE SOUNDEX(%s) '
'LIMIT 1', name, name))
if ret:
ret = ret[0]
item = Item()
item.name = ret.name
item.id = ret.id
item.members = ret.members
item.high_alch = ret.high_alch
item.low_alch = ret.low_alch
item.description = ret.description
item.price = ret.price
item.category = ret.category
item.last_updated = ret.last_updated
item.runeday = ret.runeday
return item
# Slowly fuzzy matching DB
sanitized = db.get_conn().escape_string(name)
ret = await objects.execute(
Item.raw(
'SELECT * '
'FROM _items '
'WHERE name LIKE \'%%{}%%\''
'ORDER BY sys.jaro_winkler(name, %s) DESC '
'LIMIT 1'.format(sanitized), name))
if not ret:
return None
return ret[0]
async def _alias_match_name(name: str):
"""
Searches the alias table for aliases that match the name
:param name: The name to find the alias of
"""
params = (name, ) * 2
sql = 'SELECT * ' \
'FROM aliases ' \
'JOIN _items ON item_id = id ' \
'WHERE alias LIKE %s ' \
'OR SOUNDEX(alias) LIKE SOUNDEX(%s) ' \
'LIMIT 1'
alias = await objects.execute(Alias.raw(sql, *params))
# No alis found
if not alias:
return None
# Building item from join
item = Item()
item.name = alias.name
item.id = alias.id
item.members = alias.members
item.high_alch = alias.high_alch
item.low_alch = alias.low_alch
item.description = alias.description
item.price = alias.price
item.category = alias.category
item.last_updated = alias.last_updated
item.runeday = alias.runeday
return item
async def look_for_new_items(self, wait=False):
"""
Looks for new items in the catalogue
"""
# Waiting from previous iteration
if wait:
await asyncio.sleep(60 * 60 * 2)
categories = ("Miscellaneous", "Ammo", "Arrows", "Bolts",
"Construction materials", "Construction products",
"Cooking ingredients", "Costumes", "Crafting materials",
"Familiars", "Farming produce", "Fletching materials",
"Food and drink", "Herblore materials",
"Hunting equipment", "Hunting produce", "Jewellery",
"Mage armour", "Mage weapons",
"Melee armour - low level", "Melee armour - mid level",
"Melee armour - high level", "Melee weapons - low level",
"Melee weapons - mid level",
"Melee weapons - high level", "Mining and smithing",
"Potions", "Prayer armour", "Prayer materials",
"Range armour", "Range weapons", "Runecrafting",
"Runes, Spells and Teleports", "Seeds",
"Summoning scrolls", "Tools and containers",
"Woodcutting product", "Pocket items")
runeday = (
await
rs.use_jca('https://secure.runescape.com/m=itemdb_rs/api/info.json'
))['lastConfigUpdateRuneday']
# Checking if needs updating
max_runeday = (await objects.execute(
Item.raw('SELECT MAX(runeday) as "last" FROM _items')
))[0].last or 0
if max_runeday >= runeday:
self.item_lookup = self.bot.loop.create_task(
self.look_for_new_items(True))
return
print('New items found in GE, updating...')
# Searching all categories
for i in range(38):
category = categories[i]
json = await rs.use_jca(
'http://services.runescape.com/m=itemdb_rs/api/catalogue/category.json?category=%s'
% i)
counts = await Items._get_alpha_numbers(category)
# Checking if alphas are more than what we have
for alpha in json['alpha']:
items = alpha['items']
letter = alpha['letter']
# Alpha is the same or lower. Skipping
if items <= counts[letter]:
continue
# Getting new items in alpha category
if letter != '#':
await Items._create_new_items_for_alphas(
i, letter, math.ceil(items / 12),
items - counts[letter], runeday)
else:
await Items._create_new_items_for_numbers(
i, items - counts[letter], runeday)
print('Finished updating items.')
self.item_lookup = self.bot.loop.create_task(
self.look_for_new_items(True))