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
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
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 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))