Release Trade Dangerous (Est. 2015) Power user's highly configurable trade optimizer

It asks me to pick my trade.py, and then errors out when I've done so. EDIT: Clean install seems to've fixed that.

EDIT2: Two things:

Looks like the (Un)Laden LY isn't set anymore? Is it just my imagination that they were ever set?
All the tooltips are broken. They all say 'Tooltip-nnn' where 'n'=[0-9].
I'll check out the first point you've raised.

The laden and unladen values were never set, the profile from the cAPI does not include them and the calculation to derive these figures from the load out and engineering is complex. It may be added at a later date but for now you need to enter them yourself. I save a copy of tdh.ini when updating so that I can copy back the ship data if it gets borked.

[Edit] I've found a spreadsheet that shows the calculations for unladen and laden jump range that is not that complex so I'll add the calculations to TD Helper. It looks interesting!

[Edit 2] Looking into this further has made me decide not to do this calculation. I would be required to find out the internal name of every single item in the game for all cases and ratings as well as the mass of each item, possible engineering changes to the mass as well as additional FSD data for each FSD available. The calculation is not that difficult but the amount of additional data that I would need to find out makes it just not worth the development time and effort.

Now if someone already has that information and is willing to give it to me, well that's a different matter altogether.
 
Last edited:
Listener updated.

I presume that the following (sample of many similar)

Code:
EDDB.io's API does not include likely recently added item: 'alexandrite', using fdev_id as placeholder, please inform the current EDDB.io maintainer.
EDDB.io's API does not include likely recently added item: 'benitoite', using fdev_id as placeholder, please inform the current EDDB.io maintainer.
is expected output?
 
Listener updated.

I presume that the following (sample of many similar)

Code:
EDDB.io's API does not include likely recently added item: 'alexandrite', using fdev_id as placeholder, please inform the current EDDB.io maintainer.
EDDB.io's API does not include likely recently added item: 'benitoite', using fdev_id as placeholder, please inform the current EDDB.io maintainer.
is expected output?
Indeed. If you haven't already, you'll want to update TD and do a clean run, btw.
 
TD is updated on server end, but eddb.io is currently refusing connections, so that's going to put a fly in our ointment until it's back up.

Edit: Themroc is doing some maintenance (db upgrade). I'll keep an eye out and restart our stuff once he is done.
 
Last edited:
Whilst EDDB is still having some issues, it's working enough to support the downloads we need to make TD work.

That said, I've just hit an error on the listener - I'll ticket it on GIT.
 
Problem solved, error was on TD side, not listener. Fixed. Update TD, then force an item run to regenerate the Item.csv, no need to clean.

Latest commit (TD): dc82a20
 
Last edited:
I've updated the EDAPI plugin to the new authorization method.

If someone wanna try it out: plugins/edapi_plug.py

The first time it should open a new tab in your prefered webbrowser. You'll have to login with your frontier credentials and approve the EDAPI plugin. That's all. Everything else should be automagic.

BTW: This now also works for console players.

Hint: If you have problems please do NOT post your access or refresh token.
@gazelle: One of the TD Helper 2 users has come up with a problem that we have narrowed down to the EDAPI plugin. He is using a steam account for Elite and although he gets the login page and verification, the command "trade.py import -P edapi -O tdh" comes back with "PLUGIN ERROR: Couldn't get frontier authorization.". Do you know of any reason why this should be happening?

Thanks.
 
Last edited:
@gazelle: One of the TD Helper 2 users has come up with a problem that we have narrowed down to the EDAPI plugin. He is using a steam account for Elite and although he gets the login page and verification, the command "trade.py import -P edapi -O tdh" comes back with "PLUGIN ERROR: Couldn't get frontier authorization.". Do you know of any reason why this should be happening?

Thanks.
"TD commit from 2Mar2018" might be the reason. That's the last bgol version before I forked. He needs to update TD. Latest commit was made 8Jan2019.
 
"TD commit from 2Mar2018" might be the reason. That's the last bgol version before I forked. He needs to update TD. Latest commit was made 8Jan2019.
Quite possibly although he did say that he had the latest version. We'll see when he reads your comment. I hope that is the problem and not Steam!
 
@gazelle: One of the TD Helper 2 users has come up with a problem that we have narrowed down to the EDAPI plugin. He is using a steam account for Elite and although he gets the login page and verification, the command "trade.py import -P edapi -O tdh" comes back with "PLUGIN ERROR: Couldn't get frontier authorization.". Do you know of any reason why this should be happening?
That's an authorization error while trying to get the acces_token. He might try to login again or use "-w" to get some debug output. He should remove any "access_token" and "refresh_token" when posting the debug output.

I only tested the auth with my own account (which is not over steam). Did he use the "steam" login (click on the steam icon on the login page)?
 
That's an authorization error while trying to get the acces_token. He might try to login again or use "-w" to get some debug output. He should remove any "access_token" and "refresh_token" when posting the debug output.

I only tested the auth with my own account (which is not over steam). Did he use the "steam" login (click on the steam icon on the login page)?
The problem was due to the tdh export expecting three elements in api.text, which it will only get if the commander is docked at a station with a market and a shipyard/outfitting. This caused it to error during the writing process when api.text has less than three elements, resulting in no profile file being written at all.

It's fixed in the latest TD: c29c9d0
 
Last edited:
eyeonus, have you seen the tqdm library for Python? Fast and nice progress bar. Shows progress, iterations per second, and percentage if it knows the total, and wraps around any iterable. I didn't make a pull request, but import it and play with the code below. I like it better than what's coded in misc.progress, personally, and the author claims its significantly faster than progressbar, which I believe is what misc.progress was based on.

Code:
import cache
import codecs
import csv
import csvexport
import datetime
import json
import os
import platform
import plugins
import sqlite3
import time
import tradedb
import tradeenv
import transfers
import tqdm

from urllib import request
from calendar import timegm
from pathlib import Path
from plugins import PluginException
from importlib import reload
from builtins import str

# Constants

BASE_URL = "http://elite.ripz.org/files/"
FALLBACK_URL = "https://eddb.io/archive/v6/"
SHIPS_URL = "https://raw.githubusercontent.com/EDCD/coriolis-data/master/dist/index.json"
COMMODITIES = "commodities.json"
SYSTEMS = "systems_populated.jsonl"
STATIONS = "stations.jsonl"
UPGRADES = "modules.json"
LISTINGS = "listings.csv"
LIVE_LISTINGS = "listings-live.csv"

class DecodingError(PluginException):
    pass


class ImportPlugin(plugins.ImportPluginBase):
    """
    Plugin that downloads data from eddb.
    """

    pluginOptions = {
        'item':            "Regenerate Categories and Items using latest commodities.json dump.",
        'system':        "Regenerate Systems using latest system-populated.jsonl dump.",
        'station':        "Regenerate Stations using latest stations.jsonl dump. (Implies '-O system')",
        'ship':            "Regenerate Ships using latest coriolis.io json dump.",
        'shipvend':        "Regenerate ShipVendors using latest stations.jsonl dump. (Implies '-O system,station,ship')",
        'upgrade':        "Regenerate Upgrades using latest modules.json dump.",
        'upvend':        "Regenerate UpgradeVendors using latest stations.jsonl dump. (Implies '-O system,station,upgrade')",
        'listings':        "Update market data using latest listings.csv dump. (Implies '-O item,system,station')",
        'all':            "Update everything with latest dumpfiles. (Regenerates all tables)",
        'clean':        "Erase entire database and rebuild from empty. (Regenerates all tables.)",
        'skipvend':        "Don't regenerate ShipVendors or UpgradeVendors. (Supercedes '-O all', '-O clean'.)",
        'force':        "Force regeneration of selected items even if source file not updated since previous run. "
                        "(Useful for updating Vendor tables if they were skipped during a '-O clean' run.)",
        'fallback':        "Fallback to using EDDB.io if Tromador's mirror isn't working.",
        'solo':            "Don't download crowd-sourced market data. (Implies '-O skipvend', supercedes '-O all', '-O clean', '-O listings'.)"
    }

    def __init__(self, tdb, tdenv):
        super().__init__(tdb, tdenv)

        self.dataPath = tdb.dataPath / Path("eddb")
        self.commoditiesPath = Path(COMMODITIES)
        self.systemsPath = Path(SYSTEMS)
        self.stationsPath = Path(STATIONS)
        self.upgradesPath = Path(UPGRADES)
        self.listingsPath = Path(LISTINGS)
        self.liveListingsPath = Path(LIVE_LISTINGS)
        self.shipsPath = Path("index.json")
        self.pricesPath = Path("listings.prices")
        self.updated = {
                "Category": False,
                "Item": False,
                "RareItem": False,
                "Ship": False,
                "ShipVendor": False,
                "Station": False,
                "System": False,
                "Upgrade": False,
                "UpgradeVendor": False,
                "Listings": False
            }
    
    def execute(self, sql_cmd, args = None):
        tdb, tdenv = self.tdb, self.tdenv
        cur = tdb.getDB().cursor()
        
        success = False
        result = None
        while not success:
            try:
                if args:
                    result = cur.execute(sql_cmd, args)
                else:
                    result = cur.execute(sql_cmd)
                success = True
            except sqlite3.OperationalError as e:
                if "locked" not in str(e):
                    success = True
                    raise sqlite3.OperationalError(e)
                else:
                    print("(execute) Database is locked, waiting for access.", end = "\r")
                    time.sleep(1)
        return result
    
    def downloadFile(self, urlTail, path):
        """
        Fetch the latest dumpfile from the website if newer than local copy.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.DEBUG0("Checking for update to '{}'.", path)
        if urlTail == SHIPS_URL:
            url = SHIPS_URL
        else:
            if not self.getOption('fallback'):
                try:
                    url = BASE_URL + urlTail
                    response = request.urlopen(url)
                except:
                    # If Tromador's server fails for whatever reason,
                    # fallback to download direct from EDDB.io
                    self.options["fallback"] = True
            if self.getOption('fallback'):
                # EDDB.io doesn't have live listings.
                if urlTail == LIVE_LISTINGS:
                    return False

                url = FALLBACK_URL + urlTail
                response = request.urlopen(url)
        
        dumpModded = 0
        # The coriolis file is from github, so it doesn't have a "Last-Modified" metadata.
        if url != SHIPS_URL:
            # Stupid strptime() is locale-dependent, so we do it ourself.
            # 'Last-Modified' is in the form "DDD, dd MMM yyyy hh:mm:ss GMT"
            # dDL                                0    1    2     3          4      5
            # dTL                                                0  1  2

            # We'll need to turn the 'MMM' into a number.
            Months = {'Jan':1, 'Feb':2, 'Mar':3, 'Apr':4, 'May':5, 'Jun':6, 'Jul':7, 'Aug':8, 'Sep':9, 'Oct':10, 'Nov':11, 'Dec':12}

            # We need to split the string twice, because the time is separated by ':', not ' '.
            dDL = response.getheader("Last-Modified").split(' ')
            dTL = dDL[4].split(':')

            # Now we need to make a datetime object using the DateList and TimeList we just created,
            # and then we can finally convert that to a Unix-epoch number.
            dumpDT = datetime.datetime(int(dDL[3]), Months[dDL[2]], int(dDL[1]),\
               hour=int(dTL[0]), minute=int(dTL[1]), second=int(dTL[2]),\
               tzinfo=datetime.timezone.utc)
            dumpModded = timegm(dumpDT.timetuple())

        if Path.exists(self.dataPath / path):
            localModded = (self.dataPath / path).stat().st_mtime
            if localModded >= dumpModded and url != SHIPS_URL:
                tdenv.DEBUG0("'{}': Dump is not more recent than Local.", path)
                return False
        
        tdenv.NOTE("Downloading file '{}'.", path)
        transfers.download( self.tdenv, url, self.dataPath / path, )
        return True

    def importUpgrades(self):
        """
        Populate the Upgrade table using modules.json
        Writes directly to database.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.NOTE("Processing Upgrades: Start time = {}", datetime.datetime.now())
        with open(str(self.dataPath / self.upgradesPath), "rU") as fh:
            upgrades = json.load(fh)
        for upgrade in tqdm.tqdm(iter(upgrades), smoothing = 0.1, unit = " records"):
            upgrade_id = upgrade['id']
            name = upgrade['name'] if upgrade['name'] else upgrade['ed_symbol'].replace('_',' ')
            weight = upgrade['mass'] if 'mass' in upgrade else 0
            cost = upgrade['price'] if upgrade['price'] else 0
            
            tdenv.DEBUG1("Updating: {}, {}, {}, {}", upgrade_id, name, weight, cost)
            try:
                self.execute("""INSERT INTO Upgrade
                            ( upgrade_id,name,weight,cost ) VALUES
                            ( ?, ?, ?, ? ) """,
                            (upgrade_id, name, weight, cost))
            except sqlite3.IntegrityError:
                try:
                    self.execute("""UPDATE Upgrade
                                SET name = ?,weight = ?,cost = ?
                                WHERE upgrade_id = ?""", 
                                (name, weight, cost,
                                 upgrade_id))
                except sqlite3.IntegrityError:
                    tdenv.DEBUG0("Unable to insert or update: {}, {}, {}, {}", upgrade_id, name, weight, cost)

        
        self.updated['Upgrade'] = True
        
        tdenv.NOTE("Finished processing Upgrades. End time = {}", datetime.datetime.now())

    def importShips(self):
        """
        Populate the Ship table using coriolis.io's index.json
        Writes directly to database.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.NOTE("Processing Ships: Start time = {}", datetime.datetime.now())
        with open(str(self.dataPath / self.shipsPath), "rU") as fh:
            ships = json.load(fh)['Ships']
        for ship in tqdm.tqdm(iter(ships), smoothing = 0.1, unit = " records"):
            ship_id = ships[ship]['eddbID']
            name = ships[ship]['properties']['name']
            cost = ships[ship]['retailCost']
            fdev_id = ships[ship]['edID']
            
            #Change the names to match how they appear in Stations.jsonl
            if name == "Eagle":
                name = "Eagle Mk. II"
            if name == "Sidewinder":
                name = "Sidewinder Mk. I"
            if name == "Viper":
                name = "Viper Mk. III"

            # Make sure all the 'Mark N' ship names abbreviate 'Mark' the same.
            # Fix capitalization.
            name = name.replace('MK', 'Mk').replace('mk','Mk').replace('mK','Mk')
            # Fix no '.' in abbreviation.
            if "Mk" in name and "Mk." not in name:
                name = name.replace('Mk', 'Mk.')
            # Fix no trailing space.
            if "Mk." in name and "Mk. " not in name:
                name = name.replace("Mk.", "Mk. ")
            # Fix no leading space.
            if "Mk." in name and " Mk." not in name:
                name = name.replace("Mk.", " Mk.")
            
            tdenv.DEBUG1("Updating: {}, {}, {}, {}", ship_id, name, cost, fdev_id)
            try:
                self.execute("""INSERT INTO Ship
                                ( ship_id,name,cost,fdev_id ) VALUES
                                ( ?, ?, ?, ? ) """,
                                (ship_id, name, cost, fdev_id))
            except sqlite3.IntegrityError:
                try:
                    self.execute("""UPDATE Ship
                                    SET name = ?,cost = ?,fdev_id = ?
                                    WHERE ship_id = ?""",
                                    (name, cost, fdev_id,
                                     ship_id))
                except sqlite3.IntegrityError:
                    tdenv.DEBUG0("Unable to insert or update: {}, {}, {}, {}", ship_id, name, cost, fdev_id)

        self.updated['Ship'] = True
        
        tdenv.NOTE("Finished processing Ships. End time = {}", datetime.datetime.now())

    def importSystems(self):
        """
        Populate the System table using systems_populated.jsonl
        Writes directly to database.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.NOTE("Processing Systems: Start time = {}", datetime.datetime.now())

        def blocks(f, size = 65536):
            while True:
                b = f.read(size)
                if not b: break
                yield b

        total = 0
        with open(str(self.dataPath / self.systemsPath), "r",encoding = "utf-8",errors = 'ignore') as f:
            total += (sum(bl.count("\n") for bl in blocks(f)))

        with open(str(self.dataPath / self.systemsPath), "rU") as fh:
            for line in tqdm.tqdm(fh, total = total, smoothing = 0.1, unit = " records"):
                system = json.loads(line)
                system_id = system['id']
                name = system['name']
                pos_x = system['x']
                pos_y = system['y']
                pos_z = system['z']
                modified = datetime.datetime.utcfromtimestamp(system['updated_at']).strftime('%Y-%m-%d %H:%M:%S')
                
                result = self.execute("SELECT modified FROM System WHERE system_id = ?", (system_id,)).fetchone()
                if result:
                    updated = timegm(datetime.datetime.strptime(result[0],'%Y-%m-%d %H:%M:%S').timetuple())
                    if system['updated_at'] > updated:
                        tdenv.DEBUG0("System '{}' has been updated: '{}' vs '{}'", name, modified, result[0])
                        tdenv.DEBUG1("Updating: {}, {}, {}, {}, {}, {}", system_id, name, pos_x, pos_y, pos_z, modified)
                        self.execute("""UPDATE System
                                    SET name = ?,pos_x = ?,pos_y = ?,pos_z = ?,modified = ?
                                    WHERE system_id = ?""", 
                                    (name, pos_x, pos_y, pos_z, modified,
                                     system_id))
                        self.updated['System'] = True
                else:
                    tdenv.DEBUG0("System '{}' has been added.", name)
                    tdenv.DEBUG1("Inserting: {}, {}, {}, {}, {}, {}", system_id, name, pos_x, pos_y, pos_z, modified)
                    self.execute("""INSERT INTO System
                                ( system_id,name,pos_x,pos_y,pos_z,modified ) VALUES
                                ( ?, ?, ?, ?, ?, ? ) """,
                                (system_id, name, pos_x, pos_y, pos_z, modified))
                    self.updated['System'] = True
        tdenv.NOTE("Finished processing Systems. End time = {}", datetime.datetime.now())
        

    def importStations(self):
        """
        Populate the Station table using stations.jsonl
        Also populates the ShipVendor table if the option is set.
        Writes directly to database.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.NOTE("Processing Stations, this may take a bit: Start time = {}", datetime.datetime.now())
        if self.getOption('shipvend'):
            tdenv.NOTE("Simultaneously processing ShipVendors.")

        if self.getOption('upvend'):
            tdenv.NOTE("Simultaneously processing UpgradeVendors, this will take quite a while.")

        def blocks(f, size = 65536):
            while True:
                b = f.read(size)
                if not b: break
                yield b

        total = 0
        with open(str(self.dataPath / self.stationsPath), "r",encoding = "utf-8",errors = 'ignore') as f:
            total += (sum(bl.count("\n") for bl in blocks(f)))
        
        with open(str(self.dataPath / self.stationsPath), "rU") as fh:
            for line in tqdm.tqdm(fh, total = total, smoothing = 0.1, unit = " records"):
                station = json.loads(line)
                
                # Import Stations
                station_id = station['id']
                name = station['name']
                system_id = station['system_id']
                ls_from_star = station['distance_to_star'] if station['distance_to_star'] else 0
                blackmarket = 'Y' if station['has_blackmarket'] else 'N'
                max_pad_size = station['max_landing_pad_size'] if station['max_landing_pad_size'] and station['max_landing_pad_size'] != 'None' else '?'
                market = 'Y' if station['has_market'] else 'N'
                shipyard = 'Y' if station['has_shipyard'] else 'N'
                modified = datetime.datetime.utcfromtimestamp(station['updated_at']).strftime('%Y-%m-%d %H:%M:%S')
                outfitting = 'Y' if station['has_outfitting'] else 'N'
                rearm = 'Y' if station['has_rearm'] else 'N'
                refuel = 'Y' if station['has_refuel'] else 'N'
                repair = 'Y' if station['has_repair'] else 'N'
                planetary = 'Y' if station['is_planetary'] else 'N'
                type_id = station['type_id'] if station['type_id'] else 0
                
                system = self.execute("SELECT System.name FROM System WHERE System.system_id = ?", (system_id,)).fetchone()[0].upper()
                
                result = self.execute("SELECT modified FROM Station WHERE station_id = ?", (station_id,)).fetchone()
                if result:
                    updated = timegm(datetime.datetime.strptime(result[0],'%Y-%m-%d %H:%M:%S').timetuple())
                    if station['updated_at'] > updated:
                        tdenv.DEBUG0("{}/{} has been updated: {} vs {}", 
                                    system ,name, modified, result[0])
                        tdenv.DEBUG1("Updating: {}, {}, {}, {}, {}, {}, {},"
                                              " {}, {}, {}, {}, {}, {}, {}, {}",
                                    station_id, name, system_id, ls_from_star, blackmarket,
                                    max_pad_size, market, shipyard, modified, outfitting,
                                    rearm, refuel, repair, planetary, type_id)
                        self.execute("""UPDATE Station
                                    SET name = ?, system_id = ?, ls_from_star = ?, blackmarket = ?,
                                    max_pad_size = ?, market = ?, shipyard = ?, modified = ?,
                                    outfitting = ?, rearm = ?, refuel = ?, repair = ?, planetary = ?, type_id = ?
                                    WHERE station_id = ?""", 
                                    (name, system_id, ls_from_star, blackmarket,
                                     max_pad_size, market, shipyard, modified,
                                     outfitting, rearm, refuel, repair, planetary, type_id,
                                     station_id))
                        self.updated['Station'] = True
                else:
                    tdenv.DEBUG0("{}/{} has been added:", system ,name)
                    tdenv.DEBUG1("Inserting: {}, {}, {}, {}, {}, {}, {},"
                                              " {}, {}, {}, {}, {}, {}, {}, {}",
                        station_id, name, system_id, ls_from_star, blackmarket,
                        max_pad_size, market, shipyard, modified, outfitting,
                        rearm, refuel, repair, planetary, type_id)
                    self.execute("""INSERT INTO Station (
                                station_id,name,system_id,ls_from_star,
                                blackmarket,max_pad_size,market,shipyard,
                                modified,outfitting,rearm,refuel,
                                repair,planetary,type_id ) VALUES
                                ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) """,
                                (station_id,name,system_id,ls_from_star,
                                 blackmarket,max_pad_size,market,shipyard,
                                 modified,outfitting,rearm,refuel,
                                 repair,planetary,type_id))
                    self.updated['Station'] = True
                
                #Import shipyards into ShipVendors if shipvend is set.
                if station['has_shipyard'] and self.getOption('shipvend'):
                    if not station['shipyard_updated_at']:
                        station['shipyard_updated_at'] = station['updated_at']
                    modified = datetime.datetime.utcfromtimestamp(station['shipyard_updated_at']).strftime('%Y-%m-%d %H:%M:%S')
                    result = self.execute("SELECT modified FROM ShipVendor WHERE station_id = ?", (station_id,)).fetchone()
                    if result:
                        updated = timegm(datetime.datetime.strptime(result[0],'%Y-%m-%d %H:%M:%S').timetuple())
                    else:
                        updated = 0
                    if station['shipyard_updated_at'] > updated:
                        self.execute("DELETE FROM ShipVendor WHERE station_id = ?", (station_id,))
                        tdenv.DEBUG1("{}/{} has shipyard, updating ships sold.", system, name)
                        for ship in station['selling_ships']:
                            # Make sure all the 'Mark N' ship names abbreviate 'Mark' as '<Name> Mk. <Number>'.
                            # Fix capitalization.
                            ship = ship.replace('MK', 'Mk').replace('mk','Mk').replace('mK','Mk')
                            # Fix no '.' in abbreviation.
                            if "Mk" in ship and "Mk." not in ship:
                                ship = ship.replace('Mk', 'Mk.')
                            # Fix no trailing space.
                            if "Mk." in ship and "Mk. " not in ship:
                                ship = ship.replace("Mk.", "Mk. ")
                            # Fix no leading space.
                            if "Mk." in ship and " Mk." not in ship:
                                ship = ship.replace("Mk.", " Mk.")
                            
                            tdenv.DEBUG2("ship_id:{},station_id:{},modified:{}",
                                 ship,
                                 station_id,
                                 modified)
                            try:
                                self.execute("""INSERT INTO ShipVendor
                                        ( ship_id,station_id,modified ) VALUES
                                        ( (SELECT Ship.ship_id FROM Ship WHERE Ship.name = ?), ?, ? ) """,
                                        (ship,
                                         station_id,
                                         modified))
                            except sqlite3.IntegrityError:
                                continue
                        self.updated['ShipVendor'] = True
                        
                #Import Outfitters into UpgradeVendors if upvend is set.
                if station['has_outfitting'] and self.getOption('upvend'):
                    if not station['outfitting_updated_at']:
                        station['outfitting_updated_at'] = station['updated_at']
                    modified = datetime.datetime.utcfromtimestamp(station['outfitting_updated_at']).strftime('%Y-%m-%d %H:%M:%S')
                    result = self.execute("SELECT modified FROM UpgradeVendor WHERE station_id = ?", (station_id,)).fetchone()
                    if result:
                        updated = timegm(datetime.datetime.strptime(result[0],'%Y-%m-%d %H:%M:%S').timetuple())
                    else:
                        updated = 0
                    if station['outfitting_updated_at'] > updated:
                        self.execute("DELETE FROM UpgradeVendor WHERE station_id = ?", (station_id,))
                        tdenv.DEBUG1("{}/{} has outfitting, updating modules sold.", system, name)
                        for upgrade in station['selling_modules']:
                            tdenv.DEBUG2("upgrade_id:{},station_id:{},modified:{}",
                                 upgrade,
                                 station['id'],
                                 modified)
                            try:
                                self.execute("""INSERT INTO UpgradeVendor
                                        ( upgrade_id,station_id,cost,modified ) VALUES
                                        ( ?, ?, (SELECT Upgrade.cost FROM Upgrade WHERE Upgrade.upgrade_id = ?), ? ) """,
                                        (upgrade,
                                         station_id,
                                         upgrade,
                                         modified))
                            except sqlite3.IntegrityError:
                                continue
                        self.updated['UpgradeVendor'] = True

        tdenv.NOTE("Finished processing Stations. End time = {}", datetime.datetime.now())
        

    def importCommodities(self):
        """
        Populate the Category, and Item tables using commodities.json
        Writes directly to the database.
        """
        tdb, tdenv = self.tdb, self.tdenv
                
        tdenv.NOTE("Processing Categories and Items: Start time = {}", datetime.datetime.now())
        with open(str(self.dataPath / self.commoditiesPath), "rU") as fh:
            commodities = json.load(fh)
            
        # EDDB still hasn't added these Commodities to the API,
        # so we'll add them ourselves.
        tdenv.NOTE("Checking for missing items....")

        # Need to get the category_ids from the .csv file.
        cat_ids = dict()
        try:
            with open(str(tdb.dataPath / Path("Category.csv")), "r") as fh:
                cats = csv.DictReader(fh, quotechar="'")
                for cat in cats:
                    cat_ids[cat['name']] =    int(cat['unq:category_id'])
        # Use default if no file, such as on a 'clean' run.
        except FileNotFoundError:
            cat_ids = {'Chemicals':1, 'Consumer Items':2, 'Legal Drugs':3, 'Foods':4, 'Industrial Materials':5,
                       'Machinery':6, 'Medicines':7, 'Metals':8, 'Minerals':9, 'Slavery':10, 'Technology':11,
                       'Textiles':12, 'Waste':13, 'Weapons':14, 'Unknown':15, 'Salvage':16}
        
        # EDCD is really quick about getting new items updated, so we'll use its item list to check
        # for missing items in EDDB.io's list.
        edcd_source = 'https://raw.githubusercontent.com/EDCD/FDevIDs/master/commodity.csv'
        edcd_csv = request.urlopen(edcd_source)
        edcd_dict = csv.DictReader(codecs.iterdecode(edcd_csv, 'utf-8'))
        
        def blank_item(name,ed_id,category,category_id):
            return {"id":ed_id,"name":name,"category_id":category_id,"average_price":None,"is_rare":0,"max_buy_price":None,"max_sell_price":None,"min_buy_price":None,"min_sell_price":None,"buy_price_lower_average":0,"sell_price_upper_average":0,"is_non_marketable":0,"ed_id":ed_id,"category":{"id":category_id,"name":category}}
        
        for line in tqdm.tqdm(iter(edcd_dict), smoothing = 0.1, unit = "records"):
            if not any(c.get('ed_id', None) == int(line['id']) for c in commodities):
                tdenv.DEBUG0("'{}' with fdev_id {} not found, adding.", line['name'], line['id'])
                commodities.append(blank_item(line['name'],line['id'],line['category'],cat_ids[line['category']]))
        
        tdenv.NOTE("Missing item check complete.")
        
        # Prep-work for checking if an item's item_id has changed.
        cur_ids = dict()
        result = self.execute("SELECT fdev_id,item_id FROM Item ORDER BY fdev_id").fetchall()
        for item in result:
            cur_ids[item[0]] = item[1]
        
        tdenv.DEBUG0("Beginning loop.")
        for commodity in tqdm.tqdm(iter(commodities), smoothing = 0.1, unit = " records"):
            # Make sure the broken item(s) in EDDB.io's API isn't imported. 
            if not commodity['ed_id']:
                tdenv.DEBUG0("Skipping faulty item: {}:{}" , commodity['id'], commodity['name'])
                continue
            # Get the categories from the json and place them into the Category table.
            category_id = commodity['category']['id']
            category_name = commodity['category']['name']
            
            tdenv.DEBUG1("Updating: {}, {}", category_id, category_name)
            try:
                self.execute("""INSERT INTO Category
                            ( category_id, name ) VALUES
                            ( ?, ? ) """,
                            (category_id, category_name))
            except sqlite3.IntegrityError:
                try:
                    self.execute("""UPDATE Category
                                SET name = ?
                                WHERE category_id = ?""", 
                                (category_name, category_id))

                except sqlite3.IntegrityError:
                    tdenv.DEBUG0("Unable to insert or update: {}, {}", category_id, category_name)
            
            # Only put regular items here, rare items can't be dealt with.
            if not commodity['is_rare']:
                item_id = commodity['id']
                name = commodity['name']
                if name.lower() == 'salvageable wreckage':
                    name = 'Wreckage Components'
                if name.lower() == 'political prisoner':
                    name = 'Political Prisoners'
                if name.lower() == 'hostage':
                    name = 'Hostages'
                if name.lower() == 'methanol monohydrate':
                    name = 'Methanol Monohydrate Crystals'
                if name.lower() == 'occupied cryopod':
                    name = 'Occupied Escape Pod'
                category_id = commodity['category_id']
                avg_price = commodity['average_price']
                fdev_id = commodity['ed_id']
                # "ui_order" doesn't have an equivalent field in the json.
                
                tdenv.DEBUG1("Updating: {}, {}, {}, {}, {}", item_id,name,category_id,avg_price,fdev_id)
                
                # If the item_id has changed, we need to completely delete the old entry.
                if cur_ids.get(fdev_id) != item_id:
                    tdenv.DEBUG1("Did not match item_id:{} with fdev_id:{} -- {}", item_id, fdev_id, cur_ids.get(fdev_id))
                    if cur_ids.get(fdev_id):
                        tdenv.DEBUG0("item_id  for '{}' has changed, updating.", name)
                        self.execute("DELETE FROM Item where fdev_id = ?", (fdev_id,))
                
                try:
                    self.execute("""INSERT INTO Item
                                ( item_id,name,category_id,avg_price,fdev_id ) VALUES
                                ( ?, ?, ?, ?, ? )""",
                                (item_id,name,category_id,avg_price,fdev_id))
                except sqlite3.IntegrityError:
                    try:
                        self.execute("""UPDATE Item
                                    SET name = ?,category_id = ?,avg_price = ?,fdev_id = ?
                                    WHERE item_id = ?""", 
                                    (name,category_id,avg_price,fdev_id,
                                     item_id))
                    except sqlite3.IntegrityError:
                        tdenv.DEBUG0("Unable to insert or update: {}, {}, {}, {}, {}", item_id,name,category_id,avg_price,fdev_id)
                        
        # The items aren't in the same order in the json as they are in the game's UI.
        # This creates a temporary object that has all the items sorted first
        # by category and second by name, as in the UI, which will then be used to
        # update the entries in the database with the correct "ui_order" value.
        temp = self.execute("""SELECT
                        name, category_id, fdev_id
                        FROM Item
                        ORDER BY category_id, name
                       """)
        cat_id = 0
        ui_order = 1
        tdenv.DEBUG0("Adding ui_order data to items.")
        for line in temp:
            if line[1] != cat_id:
                ui_order = 1
                cat_id = line[1]
            else:
                ui_order+= 1
            self.execute("""UPDATE Item
                        set ui_order = ?
                        WHERE fdev_id = ?""",
                       (ui_order, line[2]))
        
        self.updated['Category'] = True
        self.updated['Item'] = True
        
        tdenv.NOTE("Finished processing Categories and Items. End time = {}", datetime.datetime.now())

    def regenerate(self):
            for table in [
                "Category",
                "Item",
                "RareItem",
                "Ship",
                "ShipVendor",
                "Station",
                "System",
                "Upgrade",
                "UpgradeVendor",
            ]:
                if self.updated[table]:
                    _, path = csvexport.exportTableToFile(
                        self.tdb, self.tdenv, table
                    )
                    self.tdenv.NOTE("{} exported.", path)

    def importListings(self, listings_file):
        """
        Updates the market data (AKA the StationItem table) using listings.csv
        Writes directly to database.
        """
        tdb, tdenv = self.tdb, self.tdenv
        
        tdenv.NOTE("Processing market data from {}: Start time = {}", listings_file, datetime.datetime.now())
        if not (self.dataPath / listings_file).exists():
            tdenv.NOTE("File not found, aborting: {}", (self.dataPath / listings_file))
            return
        
        from_live = 0 if listings_file == self.listingsPath else 1
        
        # Used to check if the listings file is using the fdev_id as a temporary
        # item_id, but the item is in the DB with a permanent item_id.
        fdev2item = dict()
        result = self.execute("SELECT fdev_id,item_id FROM Item ORDER BY fdev_id").fetchall()
        for item in result:
            fdev2item[item[0]] = item[1]
            
        
        def blocks(f, size = 65536):
            while True:
                b = f.read(size)
                if not b: break
                yield b
        
        total = 0
        with open(str(self.dataPath / listings_file), "r",encoding = "utf-8",errors = 'ignore') as f:
            total += (sum(bl.count("\n") for bl in blocks(f)))

        with open(str(self.dataPath / listings_file), "rU") as fh:
            listings = csv.DictReader(fh)
            
            cur_station = -1
            station_items = dict()
            
            for listing in tqdm.tqdm(listings, total = total, smoothing = 0.1, unit = " records"): 
                station_id = int(listing['station_id'])
                item_id = int(listing['commodity_id'])
                modified = datetime.datetime.utcfromtimestamp(int(listing['collected_at'])).strftime('%Y-%m-%d %H:%M:%S')
                demand_price = int(listing['sell_price'])
                demand_units = int(listing['demand'])
                demand_level = int(listing['demand_bracket']) if listing['demand_bracket'] != '' else -1
                supply_price = int(listing['buy_price'])
                supply_units = int(listing['supply'])
                supply_level = int(listing['supply_bracket']) if listing['supply_bracket'] != '' else -1
                
                if station_id != cur_station:
                    for item in station_items:
                        if not item:
                            self.execute("DELETE from StationItem WHERE station_id = ? and item_id = ?", (cur_station, item))
                    del station_items, cur_station
                    cur_station = station_id
                    station_items = dict()
                    cursor = self.execute("SELECT item_id from StationItem WHERE station_id = ?", (station_id,))
                    for item in cursor:
                        station_items[item] = False
                    del cursor
                
                station_items[item_id] = True
                
                # Check if listing already exists in DB and needs updated. 
                result = self.execute("SELECT modified FROM StationItem WHERE station_id = ? AND item_id = ?", (station_id, item_id)).fetchone()
                # Listing may have be using the fdev_id as a temp item_id, but that commodity now has an item_id. 
                if not result:
                    result = self.execute("SELECT modified FROM StationItem WHERE station_id = ? AND item_id = ?", (station_id, fdev2item.get(item_id))).fetchone()
                if result:
                    updated = timegm(datetime.datetime.strptime(result[0],'%Y-%m-%d %H:%M:%S').timetuple())
                    # When the dump file data matches the database, update to make from_live == 0.
                    if int(listing['collected_at']) == updated and not from_live:
                        self.execute("""UPDATE StationItem
                                    SET from_live = 0
                                    WHERE station_id = ? AND item_id = ?""",
                                    (station_id, item_id))
                    if int(listing['collected_at']) > updated:
                        tdenv.DEBUG1("Updating:{}, {}, {}, {}, {}, {}, {}, {}, {}",
                             station_id, item_id, modified,
                             demand_price, demand_units, demand_level,
                             supply_price, supply_units, supply_level)
                        try:
                            self.execute("""UPDATE StationItem
                                    SET modified = ?,
                                     demand_price = ?, demand_units = ?, demand_level = ?,
                                     supply_price = ?, supply_units = ?, supply_level = ?,
                                     from_live = ?
                                    WHERE station_id = ? AND item_id = ?""",
                                    (modified, demand_price, demand_units, demand_level, supply_price, supply_units, supply_level, from_live,
                                     station_id, item_id))
                        except sqlite3.IntegrityError:
                            tdenv.DEBUG1("Error on update.")
                else:
                    tdenv.DEBUG1("Inserting:{}, {}, {}, {}, {}, {}, {}, {}, {}",
                             station_id, item_id, modified,
                             demand_price, demand_units, demand_level,
                             supply_price, supply_units, supply_level)
                    try:
                        self.execute("""INSERT INTO StationItem
                                (station_id, item_id, modified,
                                 demand_price, demand_units, demand_level,
                                 supply_price, supply_units, supply_level, from_live)
                                VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )""",
                                (station_id, item_id, modified,
                                 demand_price, demand_units, demand_level,
                                 supply_price, supply_units, supply_level, from_live))
                    except sqlite3.IntegrityError:
                        tdenv.DEBUG1("Error on insert.")            
        
        del from_live
        self.updated['Listings'] = True
        tdenv.NOTE("Finished processing market data. End time = {}", datetime.datetime.now())

    def run(self):
        tdb, tdenv = self.tdb, self.tdenv

        #Create the /eddb folder for downloading the source files if it doesn't exist.
        try:
           Path(str(self.dataPath)).mkdir()
        except FileExistsError:
            pass
        
        # Run 'listings' by default:
        # If no options, or if only 'progbar', 'force', 'skipvend', and/or 'fallback', 
        # have been passed, enable 'listings'.
        default = True
        for option in self.options:
            if not ((option == 'force') or (option == 'fallback') or (option == 'skipvend') or (option == 'progbar')):
                default = False
        if default:
            self.options["listings"] = True
        
        # We can probably safely assume that the plugin has never been run if
        # the prices file doesn't exist, since the plugin always generates it.
        firstRun = not (tdb.dataPath / Path("TradeDangerous.prices")).exists()

        if firstRun:
            self.options["clean"] = True
        
        try:
            self.execute("ALTER TABLE Station ADD type_id INTEGER DEFAULT 0 NOT NULL")
        except sqlite3.OperationalError:
            pass
        except sqlite3.DatabaseError as e:
            self.options['clean'] = True
            tdenv.NOTE("Cleaning database: ",str(e))
        
        if self.getOption("clean"):
            # Rebuild the tables from scratch. Must be done on first run of plugin.
            # Can be done at anytime with the "clean" option.
            for name in [
                "Category",
                "Item",
                "Ship",
                "ShipVendor",
                "Station",
                "System",
                "Upgrade",
                "UpgradeVendor",
            ]:
                file = tdb.dataPath / Path(name + ".csv")
                try:
                    os.remove(str(file))
                except FileNotFoundError:
                    pass
                
            try:
                os.remove(str(tdb.dataPath) + "/TradeDangerous.db")
            except FileNotFoundError:
                pass
            try:
                os.remove(str(tdb.dataPath) + "/TradeDangerous.prices")
            except FileNotFoundError:
                pass

            # Because this is a clean run, we need to temporarily rename the RareItem.csv,
            # otherwise TD will crash trying to insert the rare items to the database,
            # because there's nothing in the Station table it tries to pull from.
            ri_path = tdb.dataPath / Path("RareItem.csv")
            rib_path = ri_path.with_suffix(".tmp")
            if rib_path.exists() and ri_path.exists():
                rib_path.unlink()
            if ri_path.exists():
                ri_path.rename(rib_path)

            tdb.reloadCache()
            
            # Now it's safe to move RareItems back.
            if ri_path.exists():
                ri_path.unlink()
            if rib_path.exists():
                rib_path.rename(ri_path)
                        
            self.options["all"] = True
            self.options["force"] = True

        tdenv.ignoreUnknown = True
        
        success = False
        while not success:
            try:
                tdb.load(maxSystemLinkLy = tdenv.maxSystemLinkLy)
                success = True
            except sqlite3.OperationalError:
                print("Database is locked, waiting for access.", end = "\r")
                time.sleep(1)
        
        #Select which options will be updated
        if self.getOption("listings"):
            self.options["item"] = True
            self.options["station"] = True

        if self.getOption("shipvend"):
            self.options["ship"] = True
            self.options["station"] = True

        if self.getOption("upvend"):
            self.options["upgrade"] = True
            self.options["station"] = True

        if self.getOption("station"):
            self.options["system"] = True

        if self.getOption("all"):
            self.options["item"] = True
            self.options["ship"] = True
            self.options["shipvend"] = True
            self.options["station"] = True
            self.options["system"] = True
            self.options["upgrade"] = True
            self.options["upvend"] = True
            self.options["listings"] = True

        if self.getOption("solo"):
            self.options["listings"] = False
            self.options["skipvend"] = True
        
        if self.getOption("skipvend"):
            self.options["shipvend"] = False
            self.options["upvend"] = False
        
        # Download required files and update tables.
        if self.getOption("upgrade"):
            if self.downloadFile(UPGRADES, self.upgradesPath) or self.getOption("force"):
                self.importUpgrades()

        if self.getOption("ship"):
            if self.downloadFile(SHIPS_URL, self.shipsPath) or self.getOption("force"):
                self.importShips()

        if self.getOption("system"):
            if self.downloadFile(SYSTEMS, self.systemsPath) or self.getOption("force"):
                self.importSystems()

        if self.getOption("station"):
            if self.downloadFile(STATIONS, self.stationsPath) or self.getOption("force"):
                self.importStations()

        if self.getOption("item"):
            if self.downloadFile(COMMODITIES, self.commoditiesPath) or self.getOption("force"):
                self.importCommodities()
        
        success = False
        while not success:
            try:
                tdb.getDB().commit()
                success = True
            except sqlite3.OperationalError:
                print("(commit) Database is locked, waiting for access.", end = "\r")
                time.sleep(1)
        
        #Remake the .csv files with the updated info.
        self.regenerate()

        if self.getOption("listings"):
            if self.downloadFile(LISTINGS, self.listingsPath) or self.getOption("force"):
                self.importListings(self.listingsPath)
            if self.downloadFile(LIVE_LISTINGS, self.liveListingsPath) or self.getOption("force"):
                self.importListings(self.liveListingsPath)

        success = False
        while not success:
            try:
                tdb.getDB().commit()
                success = True
            except sqlite3.OperationalError:
                print("(commit) Database is locked, waiting for access.", end = "\r")
                time.sleep(1)
        
        tdb.close()
        
        if self.updated['Listings']:
            tdenv.NOTE("Regenerating .prices file.")
            cache.regeneratePricesFile(tdb, tdenv)

        tdenv.NOTE("Import completed.")

        # TD doesn't need to do anything, tell it to just quit.
        return False
 
Off-topic - I made Elite Exploration this week :)

Only combat left, but having just turned expert, that's going to be a slog.
 
Top Bottom