Ask not for whom the bell tolls, it tolls for TradeDB()

[Tromador]

Background:
The original concept of TradeDB() was to load the data and use this object to answer multiple questions.
create DB
answer lots of questions
maybe never exit
I think we're all in agreement that TradeDB() is a big old monolithic device that never really achieved its aim, despite best of intentions and now, some years later with the exponential explosion of data it's using a lot of memory, pute, time to load up to answer just one question at a time.
With the SQLA refactor done, we can now use ORM to abstract the data source away and use that instead to peel away the Legacy TradeDB().

Brief:
Discuss how we move away from TradeDB() in an orderly fashion, which doesn't (we hope) break many (or any) things, keeping (as far as we can) backwards compatibility and improve Trade: Dangerous by doing so.

[Tromador]

Here's what the binary pixies have to say about this, with a particular focus on starting with cli and commandenv, per Oliver's comment in #126

Proposal: retire TradeDB() in stages (without breaking the world)

Why this is worth doing

TradeDB() currently does two very different jobs:

1. DB lifecycle / access: ensuring the local DB exists and is current, opening an engine/session, etc.
2. Huge in-memory model + indexes: loading Systems/Stations/Items into Python objects and building lookup structures intended to support “load once, answer many questions”.

In practice, CLI runs tend to do “load everything to answer one question”, and we end up re-hydrating a database into another representation just to do lookups and queries. That’s expensive and it scales badly as the dataset grows.

The SQLAlchemy refactor gives us a clean path to make the database the source of truth and use ORM/SQL queries for lookups, instead of rebuilding an in-memory DB every run.

tradeorm.py is a proof-of-concept of that direction today, but it needs work if it’s going to be a first-class replacement rather than a demo.

---

What TradeDB() currently provides (so we don’t accidentally delete behaviour)

Before we rip anything out, we should explicitly acknowledge the responsibilities that are currently bundled together:

• Engine/session bootstrap and “ensure DB is sane / (re)build if needed”.
• Legacy wrapper objects (System, Station, Item, etc.) plus large indices like systemByName, stationByID, itemByName, and support code like stellar-grid caching for range queries/routing.
• Lookup semantics users rely on: partial matching, ambiguity errors, and (now) @N disambiguation for duplicate system names.
• CommandEnv integration: argument checks resolve --from/--to/--near/--avoid/--via via tdb.lookupPlace/lookupItem etc.

This bundle is why TradeDB() has become a lodestone: everyone depends on it for something.

---

Strategy: shrink TradeDB() instead of “big bang” replacement

The safe path is to treat TradeDB() as a compatibility shim and progressively move callers onto a smaller, explicit surface area.

Phase 1 — fast name resolution before we load the world

Oliver’s point about startup time is dead-on: we currently instantiate/load TradeDB() before we can reject obviously invalid input.

Concretely: cli constructs TradeDB(...) first, then CommandEnv.run() does argument checking. That means “you typed daftness” still triggers the heavy load.

Fix direction:

• Introduce a lightweight “resolver” path using ORM for system/station/item/place lookups (i.e. what TradeORM.lookup_place/lookup_system/lookup_station is already sketching).
• Run fast validation using that resolver before we decide to load full TradeDB().

There are (at least) two ways to wire this:

• A) Change CLI flow so fast validation occurs before TradeDB() is instantiated/loaded.
• B) Always instantiate TradeDB(load=False) (no heavy in-memory load), run fast validation, then only do the heavy load if the command actually needs it.

Either way, the key outcome is: bad --from/--to/--near/... fails fast without building the giant in-memory cache.

Phase 2 — migrate CommandEnv lookups to ORM-backed resolver

Next step is to stop calling tdb.lookupPlace/lookupItem from CommandEnv for the common “parse my args” work:

• checkFromToNear()
• checkAvoids()
• checkVias()

Those should be able to resolve names using the ORM resolver (and return something usable: either ORM entities, or IDs, or minimal structs).

Important: this is where we must preserve behaviour:

• same ambiguity handling (and the same “helpful” candidate formatting),
• same acceptance of SYS, STN, SYS/STN, /STN styles,
• and eventually @N suffix support for duplicates (which TradeDB.lookupSystem() already implements).

Phase 3 — disentangle the “legacy wrapper objects” from the DB

Some modules still expect Station/System wrapper objects (not ORM entities). That’s fine short-term, but it should become optional.

Plan:

• Define a small interface that callers actually need (engine/session + a few query helpers).
• Convert the remaining “wrapper expectations” either into:
  • ORM usage directly, or
  • minimal dataclasses that don’t require preloading every record.

Phase 4 — make TradeDB() a thin compatibility layer (then optional)

At this point TradeDB() should only exist to support legacy call sites:

• It can become a wrapper around the ORM/session with optional in-memory caches where they are still beneficial.
• Once no core path requires it, we can deprecate it and stop constructing it in CLI by default.

---

Practical deliverables (what I think we should actually do next)

1. Add an ORM-backed resolver with the exact lookup semantics we want to keep. TradeORM is a start, but it needs:

   • @N disambiguation parity for systems with duplicate names,
   • parity with existing partial-match and ambiguity output expectations,
   • and likely a “return IDs not ORM instances” option for cheap downstream use.

2. Move CLI to validate arguments before heavy load (this is the quickest visible win).

3. Switch CommandEnv to use the resolver for --from/--to/--near/--avoid/--via so “input parsing” doesn’t imply “load the galaxy”.

---

Risks / gotchas (so we don’t kid ourselves)

• Behaviour regressions in fuzzy matching and ambiguity output are the Update tradecalc.py #1 risk: people have scripts and muscle memory.
• ORM query performance needs index-friendly patterns (system name, station name, and system_id+station name lookups should be cheap).
• There’s a transitional awkwardness where some paths return ORM entities while others return legacy wrappers. That’s manageable if we make conversion explicit.

---

What “success” looks like

• Running trade.py ... with bad system/station args fails quickly without building big in-memory structures.
• Commands that only need name resolution don’t pay the TradeDB() tax.
• Over time, TradeDB() becomes a compatibility shim instead of the default engine of the app.

[kfsone]

An ancillary that I think will help:

At this point there should be two distinct groups of user experience:

• auto/scrape (e.g eddb) where it is 7x9s (99.99999%) stable, and of that 5x9s is addition, with only the rarest of rare changes,
• manual entry/pilot simulation, where corrections are common

Without intending disrespect, let's call them the "players" and "pilots"? I've been both at one time or another - td wouldn't exist otherwise.

The data domain sizes for the two will be vastly different, nobody is typing 1000 system corrections a minute or hour or day every day. "players" have a massive, machine-generated and maintained dataset coming near-direct from the game and so stable. We can afford to spend an extra 30s on a first time import adding indexes on system, station, etc.

I know diddly-squat about text indexing, but there is probably some kind of index or field we can add to each table that will make lookups a breeze.

Players can afford the 1-time index establishment because of the gains, for Pilots the overhead will be continuous but negligible and amortized by the payoff.

Does this sound reasonable?

[eyeonus]

Finally had time to try to play catch up on this.

That sounds plenty reasonable, and is the reason why eddblink has the solo option. I've been mentally referring to players as crowdsourcers, myself. They're all pilots ^_^

I don't have much to say at this point other than to say it certainly seems like a good idea to phase out tdb in favor of direct queries now that we've officially acknowledged that the SQL db is the true db.

@Tromador and I have had prior discussions regarding post-orm changes, and considering the scope of this proposal I think it relevant to mention them, as the tdb->orm handoff for function X makes a good time to also implement change in db Y:

Rare items are marked as such in the EDDN market updates, we no longer need to keep a manually updated list of which stations sell the rares or what the rares are called, we can just mark them as such with a relevant field in the Item table

I think it was bernd said something about having the modified time be a linux_epoch int and stored per Station entry not per StationItem entry, and same for both vendor tables as well.

I'm still wanting to get tdgui to be an actual working thing, so making the sub-command return something that isn't pre-formatted for printing to cli is a first step to making the gui actually decent.

[kfsone]

crowd vs solo, good to me :)

[kfsone]

tdgui: the intent of the separation between run and render was supposed to help there; I think I had half a mind there'd be "run_cmd.py" and "run_gui.py" which inherited it for the run method and then overwrote the render() method. I don't think anything like rich existed back then, but an easier path to the migration might be to look at something like textual first (https://textual.textualize.io/, same people as rich) to build a long-running cli command with a gui-like interface.

[kfsone]

Lookup Matching, primarily tradedb.lookupPlace().

• TradeDB does some fancy shenanigans, stripping out punctuation, yadda,
• TradeDB fails to do some fancy shenanigans modern users might expect ("Abrahammy Lincoln? Did you mean Abraham Lincoln?")
• TradeORM is still basic,

IMO best place to start untangling spaghetti and reward for doing so is here. Enabling all commands/scripts/tools/plugins to do lookups without the overhead of a TradeDB construction, at raw db speeds.

From there we'd be positioned to review plugins, scripts, and commands for now more visibly redundant uses of TradeDB/wantsTradeDB=True.

The current TradeORM lookups definitely aren't perfect: "3583/Antonio" tells me 3583 matches more than one system. I reached an impasse in my knowledge/determination to launch TradeORM where I realized that doing a naive db-crawl of names would suck.

I know zippity-flip-squat about text indexing/searching, tbh. Writing lookupPlace left a lasting sense of dread :)

Naive, ai/google-lookup free notion: every table gets a "search_name" column which is the fully-formed name in all caps separated by a character that'll have to be other-than-slash since many station names include a slash.

perhaps:

f"{grouping}:/:{name}" even where there's no grouping (enabling reuse of a single search algo). or f'"{quote_escaped_grouping}","{quote_escaped_name}"'.

slightly more expensive to build, but nobody should be doing repeated calls to this in any kind of tight loop. who cares if it takes 1250ms to match a pathologically bad input one time before telling you "'a'? yeah, that matches like a billion station names"

^^^--- I'm not proposing this ---^^^ this is my pixel fairy "this is probably the threshold of stupid".

It's just a base to touch against when we respectively go away and get claude / perplexity / gipeetee's advice on how to do text indexing in an sql database that'll work across backends :)

[kfsone]

"and likely a “return IDs not ORM instances” option for cheap downstream use."

For reasons, I suspect that's premature optimization. Why do you want, for example, a category id or a station id? There's nothing you can innately do with that. You can't filter against it. If you can, if you already retrieved a collection of station_ids, then now you're doing a "user space join" which means you left Python into C one time, got data, brought it into Python, manipulated it, took more data out into C and brought data back, and then compared them, when you probably wanted to do that all in one go.

TradeDB's sin was trying to avoid that by just bringing all the data out one time up front :) SqlAlchemy/TradeORM is the realization you don't need to, you can talk to the data hyper-efficiently without pre-loading all of it. TradeDB just never got the smarts to realize that part of itself, and we shouldn't invest the time making it have them.

This is something that's been buzzing at the back of my head and it's coming together now.

What we actually want is to help surface more SQL richness.

Think of the "avoids" list we build - we don't actually want or need that data for anything beyond passing it to the database, and we get it from the database.

It's in python because the original author is a programmer who thought like a programmer.

TradeORM should encourage thinking about data. You want to make a list of things to avoid? Add a CTE or a temp table.

CREATE TEMPORARY TABLE AvoidItems ( item_id int not null unique );

...

... and stationitem.item_id not in (select item_id from avoiditems)
...

don't even need to complicate that last part by making it conditional. the query planner will deal with that if the table is empty.

boosh. work done for us, and - back in python - we don't have to pass some table of indexes to be avoided around.

Infact, we can actually use SQLite's aliasing to create pseudo tables/views that we pass around instead:

def find_me_cheap_items(items: ORM.Model /*IDK*/, limit_cr: int):
  return items.where(price <= limit_cr)

did you see me mention avoids? no, because I either pass SA_Item or I pass an alias/cte that's SA_Item filtered by an Avoiding table I created...

CREATE TABLE Avoiding (table text, entity_id int)
AS
SELECT ("System", 69), ("Station", 489), (Item, 420)

Stopping because much typed sanity check required :)

[kfsone]

^- we could even in the schema define one or more common temporary tables that can be populated and stuff will just automatically use them if they're populated.

tdo = TradeORM()
# ^- did a CREATE TEMPORARY TABLE FilterAvoid(table text not null, entity_id int); or thereabouts; f for filter
# ^- did a CREATE TEMPORARY TABLE FilterPadSize(table text not null);
...
...
tdo.get_shiny_items_in_system("Sol")
# ^- automatically joins against FilterAvoid and FilterPadSize without further action; won't list slaves, won't list Abraham Lincoln, if you avoided both of those.
# v- and then, one of
tdo.get_all_shiny_items_in_system("Sol")  # meh
tdo.get_shiny_items_in_system("Sol", filtered=False)

[kfsone]

Todo: Persist the spatial grid, when I first created it, it was changing all the time.
Todo: Inventory the things that need doing after data imports and provide support thru tradeorm for resolving them, e.g: we could create the spatial grid in-db and then just represent it thru the orm, that way it doesn't nee pickling.

[Tromador]

Again consulting the pixies. Btw... you may be a programmer thinking like a programmer, but that may be more useful me as a sysadmin thinking like a sysadmin. On the other hand, me wanting a robust DB solution was what triggered my DB work, so I guess it's all good. Subject of which, I totally agree we should be using the database as far as possible, to deal with data. Anyway, the pixies are also in basic agreement:

---

1) “Lookup matching is the best place to start”

Agree. lookupPlace() is the choke-point where we pay the worst “load the galaxy” tax for the simplest mistake-typing. If we can make place/system/station/item resolution work directly against the DB (fast, deterministic, same ambiguity semantics), we can then stop default-constructing TradeDB(load=True) just to reject nonsense.

Also: we currently already have a “fast validator” hook (validateRunArgumentsFast) but it runs after CLI has constructed TradeDB, so it can’t be fast in practice. Fixing that control-flow is likely the quickest visible win.

2) TradeORM lookups are basic / “3583/Antonio” problem

Also agree. Right now TradeORM is doing “simple query then ambiguity”, but lookupPlace in TradeDB is doing tiered matching (exact → close → word-boundary → any) plus punctuation/whitespace normalisation. If we want TradeORM to become the resolver-of-record, we need parity with that behaviour (or a consciously documented change).

3) “Naive DB crawl of names would suck”

Yes, but we don’t need a “crawl everything into Python” to avoid that.

The pragmatic DB-side approach is:

• store normalised forms of names in the DB (uppercase, punctuation-stripped variants), and
• index them so prefix/substring-ish lookups are cheap.

That keeps the resolver logic consistent across backends, and avoids “pull everything, then normalise” as an algorithm.

I’d probably avoid a single composite search_name string with a custom delimiter because:

• station names can contain / (and other punctuation),
• and you end up reinventing escaping rules.

Instead: keep separate columns, e.g.:

• System.name_norm, System.name_trim
• Station.name_norm, Station.name_trim
  …and do sys/stn resolution via a join.

(If we want to go further later: SQLite FTS5 / MariaDB FULLTEXT can be optional accelerators, but I wouldn’t make that a blocker for parity.)

4) IDs vs ORM instances

I think you’re right to push back on “IDs everywhere” if the caller then does user-space joins.

The actual goal should be: compose queries, don’t shuttle half-results through Python.

However, there are two distinct contexts here:

• Argument validation / resolution (interactive): returning an ORM entity (or a tiny dataclass) is fine.
• Filtering in later queries (avoids/vias/etc): we should prefer passing a SQL expression / subquery / CTE into the later query, rather than materialising Python lists unless they’re trivially small.

So rather than “return IDs not ORM instances”, the design target might be:

• resolver returns either (a) an entity, or (b) a SQLAlchemy selectable/predicate representing “the thing(s) resolved”.

That achieves your “SQL richness” point without prematurely micro-optimising.

5) Temp tables / CTEs for avoids etc

Conceptually: yes. “Avoid lists live in Python because programmer brain” is basically the right diagnosis.

Practically: temp tables are connection-scoped, and SQLAlchemy sessions can hop connections unless we’re careful, so we need to be disciplined about lifecycle (single session/transaction/connection for the query pipeline).

A lower-risk stepping stone is:

• use CTEs / VALUES(...) constructs (or IN (...) binds for small lists),
• and only graduate to temp tables once we have a clear need and a safe connection-lifetime model.

Proposed next concrete steps

1. Define “resolver semantics” as an explicit contract (match tiers, normalisation rules, ambiguity formatting, @N disambiguation).
2. Implement that resolver against ORM with indexed normalised columns.
3. Change CLI flow so “fast validation” happens before heavy TradeDB(load=True) construction.
4. Migrate CommandEnv.checkFromToNear/checkAvoids/checkVias to use the resolver (and/or build SQL predicates rather than Python lists where it matters).

---

The pixies want to go in clear steps and have a morbid fear of doing too much at once. Starting with 2 & 3 above for place lookups gives us an immediate win.

[kfsone]

Yes, but we don’t need a “crawl everything into Python” to avoid that.

• store normalised forms of names in the DB (uppercase, punctuation-stripped variants), and
• index them so prefix/substring-ish lookups are cheap.

That keeps the resolver logic consistent across backends, and avoids “pull everything, then normalise” as an algorithm.

Absolutely

I’d probably avoid a single composite search_name string with a custom delimiter because:

• station names can contain / (and other punctuation),
• and you end up reinventing escaping rules.

Instead: keep separate columns, e.g.:

• System.name_norm, System.name_trim
• Station.name_norm, Station.name_trim
  …and do sys/stn resolution via a join.

(If we want to go further later: SQLite FTS5 / MariaDB FULLTEXT can be optional accelerators, but I wouldn’t make that a blocker for parity.)

I don't know how we'd leverage those, but those were words that had skittered across my thinking :)

Wait - if this is a normalized column, we're stripping out punctuation, right? So in that column we don't have to worry about escaping - it's ascii letters and digits only. It doesn't have to be perfect, it has to be reductive so that you are looking at 128 possible fuller name matches instead of 128,000?

4) IDs vs ORM instances

I think you’re right to push back on “IDs everywhere” if the caller then does user-space joins.

The actual goal should be: compose queries, don’t shuttle half-results through Python.

However, there are two distinct contexts here:

• Argument validation / resolution (interactive): returning an ORM entity (or a tiny dataclass) is fine.
• Filtering in later queries (avoids/vias/etc): we should prefer passing a SQL expression / subquery / CTE into the later query, rather than materialising Python lists unless they’re trivially small.

So rather than “return IDs not ORM instances”, the design target might be:

• resolver returns either (a) an entity, or (b) a SQLAlchemy selectable/predicate representing “the thing(s) resolved”.

That achieves your “SQL richness” point without prematurely micro-optimising.

How about we paint id-retrieval as a "smell" (in pixie speak)? Speaking out loud to myself: the only things you need from the database are the information you need to show the user. For the rest, what you need to know is how to tell the database what fields to use to find that data; knowing what's in them is unimportant outside of debugging, and that's a different book.

5) Temp tables / CTEs for avoids etc

Conceptually: yes. “Avoid lists live in Python because programmer brain” is basically the right diagnosis.

Practically: temp tables are connection-scoped, and SQLAlchemy sessions can hop connections unless we’re careful, so we need to be disciplined about lifecycle (single session/transaction/connection for the query pipeline).

A lower-risk stepping stone is:

• use CTEs / VALUES(...) constructs (or IN (...) binds for small lists),
• and only graduate to temp tables once we have a clear need and a safe connection-lifetime model.

Yeah - I ran out of knowledge enough to be able to really describe what I'm thinking towards. In Python terms it might be represented by an instance of an ORM Model:

bad_things = []
if cmdenv.options("--no-flamingos"):
  tdo.avoid_filter["Item"] += ["flamingo"]  # <- coder think pattern
  avoidance = SA_FilterAvoid("Item", "flamingo"); bad_things += [avoidance]  # <- more like the way we used sqlalchemy at SpaceX

Proposed next concrete steps

1. Define “resolver semantics” as an explicit contract (match tiers, normalisation rules, ambiguity formatting, @N disambiguation).
2. Implement that resolver against ORM with indexed normalised columns.
3. Change CLI flow so “fast validation” happens before heavy TradeDB(load=True) construction.
4. Migrate CommandEnv.checkFromToNear/checkAvoids/checkVias to use the resolver (and/or build SQL predicates rather than Python lists where it matters).

Derp to victory, I say.

The pixies want to go in clear steps and have a morbid fear of doing too much at once. Starting with 2 & 3 above for place lookups gives us an immediate win.

Hey now, I'm just trying to check myself :-P Also, last time I let myself go on this project, it took me 11 months to earn back my wife-points and I left you guys in the lurch.

[Tromador]

Here's some specific actionable goals to get us going:

---

PR 1: Make “fast validation” actually fast

• Change CLI flow so argument validation happens before any heavy TradeDB load.
• Worst case: construct TradeDB(load=False) (or don’t construct it at all), run the validator, only then decide whether the command needs wantsTradeDB=True.

This is the “startup speed win when you type daftness” you were pointing at, and it’s mostly wiring/control-flow, not “rewrite the world”.

---

PR 2: Define resolver semantics (contract) + implement parity

• Write down what lookupPlace semantics are (normalisation rules, match tiers, ambiguity formatting, @N handling).
• Extend tradeorm so it can meet that contract (or deliberately document a behaviour change).

Without that, every migration step becomes a regression lottery.

---

PR 3: Move CommandEnv checks onto the resolver

• checkFromToNear, then checkVias, then checkAvoids.
• For avoids/vias: prefer passing SQLAlchemy expressions/CTEs/subqueries into later queries over building Python lists when it matters.

---

[Tromador]

Here's some specific actionable goals to get us going:

PR 1: Make “fast validation” actually fast

* Change CLI flow so **argument validation happens before** any heavy `TradeDB` load.

Done!

[kfsone]

We're battening down the hatches for big incoming storm, and I'm still trying to accumulate a few reserve wife points but I'll take a crack at looup_place etc in the next week.

I'm thinking I want to add columns to the System, Station, and Item tables that does the python "translation" deformation and stores it so that we can do the same expensive transformation on the incoming string and hit the database with a lookup rather than having to pull the names in to python and test them.

[Tromador]

We're battening down the hatches for big incoming storm, and I'm still trying to accumulate a few reserve wife points but I'll take a crack at looup_place etc in the next week.

I need to get back on with trying to get spansh and listener to play nicely with proper multiprocessing too. That's what the weird lock stuff in spansh is about making a start on that. Will also try and find time soon.

[eyeonus]

no tdgui isn't gui-like it's a gui, using tkinter

…

On Sun, Dec 14, 2025 at 4:25 PM Oliver Smith ***@***.***> wrote: tdgui: the intent of the separation between run and render was supposed to help there; I think I had half a mind there'd be "run_cmd.py" and "run_gui.py" which inherited it for the run method and then overwrote the render() method. I don't think anything like rich existed back then, but an easier path to the migration might be to look at something like textual first (https://textual.textualize.io/, same people as rich) to build a long-running cli command with a gui-like interface. — Reply to this email directly, view it on GitHub <#245 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AANHHYEW423JRJ5KJSKORKD4BXWV5AVCNFSM6AAAAACO6S7Z72VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTKMRVGQZTENY> . You are receiving this because you commented.Message ID: ***@***.*** com>

-- mu'o mi'e .aionys. .i.e'ucai ko cmima lo pilno be denpa bu .i doi.luk. mi patfu do zo'o (Come to the Dot Side! Luke, I am your father. :D )

[kfsone]

sorry, kinda tired and I deleted an explanatory sentence. I mean't "on the subject of td gui" and needing to make commands behave more consistently, the whole RowFormatter class concepts that everything is supposed to use but I got lazy about in plugins, plus the formatters of most commands have rather a lot of business logic in them.

I consider myself shit at anything involving pixels, ergo doomed at anything involving a GUI, but I have been toying with an overhaul of the formatting classes and possibly experimenting with a combined TUI (with textual) and GUI (with imgui) set of types to experiment with that.

[kfsone]

After a bit of tinkering and pondering, I think the right thing to do is to create a clean GUI-work branch with everything but the bare minimums removed, even trade.py temporarily, and add either DearPyGui, imgui_bundle, or a TUI like textualize that's not the full leap to graphics, and then bring everything back with that as a driving cost-center.

Command and Plugin frameworks were designed with a tip-of-the-hat to future GUI development, there's just a lot of code to support in finishing that refinement.

• TradeEnv, CommandEnv, need to provide better structuring and facilities; Python 3.12+ now have the facilities we need to support more structured mixins ('type') so that TradeEnv can be more of a God Object instead of an everyman's Context Object,
• We may need to taint things with "async",
• Cancellability,

I'd probably start with "nav" and "trade" (since it was the TradeORM guineapig), with an eye towards "import" as a follow-up.

It's mostly an evolution of the existing separation of concerns and ensuring that the infrastructure things like import need for long-running work are provided; c.f eddblink - it operates in a near vacuum.

Instead of directly giving it a tdb/torm to play with directly, plugins probably need to go through the CommandEnv to get those things, so that there is an exchange.

[eyeonus]

I was just going for feature-parity with TDHelper, i.e. a gui that runs TD in the background and processes the output for GUI presentation, such as collapsible route details, etc.

This sounds like a major overhaul of the whole backend.

I really fell like I ought to give you collaborator access if you're going to be doing this much on the thing.

Especially since this is your thing anyway.

[kfsone]

The point of such a branch would not be redevelopment or a do over; that initial delete everything isn't "carve out what we're going to rewrite", kind of the opposite? The problem with me doing that is - I'm shit at GUI stuff, always have been :)

[eyeonus]

I am also not great at GUI stuff, but that's kind of the point, my wanting to do it is specifically so I am no longer. I would rather everyone left the actual GUI stuff to me. I was talking about

Command and Plugin frameworks were designed with a tip-of-the-hat to future GUI development, there's just a lot of code to support in finishing that refinement.

    TradeEnv, CommandEnv, need to provide better structuring and facilities; Python 3.12+ now have the facilities we need to support more structured mixins ('type') so that TradeEnv can be more of a God Object instead of an everyman's Context Object,
    We may need to taint things with "async",
    Cancellability,

As well as all the other stuff you've been doing recently, it seems like with all of your contributions it makes sense to give you collaborator so you don't need Trom or myself to merge a PR once it's ready for it.

[kfsone]

For both the feat/trade-game and dev/local-cmd-orm-refactor I've been trying to bring the TradeORM lookup features up to scratch, but was unprepared for how complex lookupPlace is.

I explored just preloading the relevant tables so it could do the search on those, but it makes the TradeORM entry slow enough that it doesn't feel like the migration is getting you anything.

Wanted to check what @eyeonus and @Tromador want to bring forward from the current name lookup stuff.

-- brain dump/deep dive, ignore if I'm on the wrong track.

The fuzzy match is always going to be expensive one way or another - you either have to table scan or build some kind of index or install some kind of db plugin; since this is cli-facing, that can potentially afford to be sqlite specific? If you're using Maria/Postgres are you needing to be able to find sol/abraham lincoln by typing just "hamlinc"?

I'm not convinced that the current approach, to try hard to find an ambiguity for you, is helpful. We provide disambiguation constructs.

We have some existing syntax for disambiguation (@, /), is it worth making the default behavior be simple precise lookups with an operator that denotes "like this"?

That is:

# using exact match/left-anchoring -> fast
--from "@SOL/Abraham Lincoln"
--from "so/abraham"
--from "100 Rads"  # currently unique station prefix
# vs fuzzy, slow
--from hamlinc?   # or ?hamlinc, or ~hamlinc, or something, not hung up on specific syntax.

I think trying more aggressively to find a unique match and stop is going to be more user-friendly as the player-input names become more prevalent.

Some terms:

unit :- the model you're trying to lookup (Station, Item, or System) depending on the type of lookup
group :- optional parent model (System to Station, Category to Item) depending...
fold :- group if present else unit, a name for "group if group is not None else unit".

System Lookup: unit = System, group = None, fold = System
Station Lookup: unit = Station, group = System, fold = System
Item Lookup: unit = Item, group = category, fold = Category
Ship Lookup: unit = Ship, group = None, fold = Ship

The fold matters for '@' and '/' prefixes;

lookup_station("@sol/linc"), lookup_item("@slave/slave") -> "@sol" and "@slave" are group terms,
lookup_system("@sol") -> still means system, but there's no group this time.

possible syntaxes:

"@soansdo" -> fold exact "soandso" or prefix (like "soandso%") or anchored-fuzzy (must start with 's')
"soandso/" -> fold exact soandso, prefix, like ("%soandso%")
"@soandso/" -> fold exact "soandso" or prefix,

^- soandso may include @n syntax for disambiguation

"/soandso" -> unit exact "soandso", unit prefix, unit anchored-fuzzy, unit open fuzzy

Whichever way you shape it you seem to end up with some anomalies, especially now that stations can (and do) include '/' in the names: sqlite> select count(*) from station where name like '%/%'; 51)

[Tromador]

So I mean... ok.

When we say fuzzy, let's be honest, we're not dealing with some well known Levenshtein algorithm. We're dealing with a mini-language parser + ranking engine + ambiguity explainer. To say it's "complex" is not exaggeration.

TradeDB.lookupPlace() isn’t just "do a LIKE". It’s doing:

• A fast-path system-only resolution first (including the newer "duplicate system name + @N index" behaviour), and only falling back to the slower combined logic if that doesn’t resolve.
• A tiered ranking scan over candidates using normalisation + trimming rules:
  1. exact match
  2. “close” match (normalised equivalence)
  3. word-boundary-ish substring
  4. trimmed substring (strip punctuation/whitespace)
  …then returns as soon as any tier yields exactly one match; otherwise ambiguity.

So if we want behavioural parity, we’re not porting “a lookup”, we’re porting a ranking engine + disambiguation rules.

We can't just throw a couple SQLA filters at this and cheer a job well done. Somewhere somehow we have to pay a price, the question is where.

A real statement of problem is probably something like lookupPlace() baked a whole UX contract into a Python in-memory scan, and ORM-by-itself doesn’t magically recreate that contract.

So we need a name-resolution-strategy that's DB-friendly.

I suspect the pin we're dancing on is we can have fast or we can have global substring magic (e.g. hamlinc), unless we build extra indexing structures.

• Exact / prefix lookup: cheap with normal B-tree indexes.
• Contains/substring (%foo%) across a big table: expensive without special indexing.
• “Find the thing from a fragment anywhere in the string” is the expensive bit.

The decision is where we want to land on that spectrum.

Which I shall address later. I've got a pile of notes to condense down, but my cat is going insane with "it's bedtime!!!" noises, which translated mean "I want my bedtime treat NOW!". And as it's 4am, she's not wrong. So expect a lot more on the subject, after sleep!

[kfsone]

No, lookupPlace isn't just 'do a like', but why eliminate the fastest options before biting the bullet and doing the slow dance?

Most of the time I've used the tool the last few weeks, I've usually pasted the system name at the very least - from Inara or EOMH or copy-and-paste from a "local" or "run" output or the in-game copy option

I'd like to preserve the snappiness the tool gives me when I do that; and it's fast enough that it doesn't mean "at the expense of when I do in-fact type sol/hamlinc".

Here (vid), "~" is being replaced with the full name, so TradeORM.lookup_... is short-circuiting on instant, exact-matches from the database. Making it super fast.

trade.mp4

and the "sol/abraham" is a combo exact-match on system + prefix-match (abraham%) on station.

Once those fail, well then I think a little delay on the lookup is something we'd all expect.

(I think we can discuss on the discord, I hope it's obvious I'm not saying the current TradeORM lookup... methods are good, just that I am tantalized by the behavior when I give it exact names and it zings. it bites a lot less when I am ham-fisting a name and it has to fall back on the expensives)

[kfsone]

Perhaps we should add "additional indexes" to the todo, for which we might want to do explains on the queries and figure out what is worth the index-build cost.

I have a hunch we'll need to guide import plugins towards change-avoidance to reduce index churn, but if we structure them well it shouldn't be too awful.

Things I suspect we might want are surfacing units in supply, definitely think there are huge gains to be had from splitting StationItem into separate supply/demand, etc, etc.

[Tromador]

This is what I'm thinking

1) Define the lookup contract we want (not “whatever lookupPlace happened to do”)

How about we break it down into an explicit ladder, fast stuff then slow stuff.

• Tier 0: parse syntax (SYS/STN, @sys, /STN, @n disambiguator) — keep it stable.
• Tier 1 (fast): exact match
• Tier 2 (fast): prefix match (foo%)
• Tier 3 (slow): contains / “our existing normalised/trim rules” resolve "hamlinc" - could be 'opt-in' from cli.

This clears up the fuzziness of our fuzziness requirement.

2) Narrow scope before doing anything expensive

This gives us a practical DB advantage:

• SYS/STN is inherently scopeable: resolve SYS first, then search stations within that system.
• Station-only /foo is the dangerous case: it forces global search unless we require more specificity or make it opt-in.
• Global fuzzy is never the default; scoped fuzzy is acceptable.

3) Stop bikeshedding string operators; make “slow mode” a flag if needed

Given / appears in names, don’t add clever syntactic meanings that will bite us later.
If we really want a “do the slow dance” switch, suggest --fuzzy / --contains or similar. That’s easy to explain, easy to document, no parsing edge cases.

4) Commit to measuring with EXPLAIN and a tiny benchmark list

KFSOne suggested “add indexes, do explains”. Let's turn that into a concrete task:

• Make a short list of representative lookups:
  • exact system
  • prefix system
  • exact station within system
  • prefix station within system
  • station-only lookup
  • ambiguous cases
• Run EXPLAIN QUERY PLAN / timings definitely on SQLite. Possibly other backends, but mainly SQLite is the workhorse for queries.
• Only then decide which indexes/columns are worth it.
• For each benchmark, capture the exact query shape we’re issuing from SQLA, not just the Python call site. (Avoid/surface ORM surprises).
• That stops hand-wavy “it feels slow” and gives us proper diagnostic information.

5) Identify which legacy behaviours we must carry

We have a key question to force alignment:
-Do we consider “hamlinc finds Abraham Lincoln” a core feature or a legacy convenience?

• If core: we need schema/index support (normalised column, FTS/trigram, etc.)
• If convenience: make it opt-in and keep defaults fast/predictable

For me, I think making it opt-in is a really cool idea. Or opt-out if you want to keep current default behaviour. Either way, let's take the expensive thing and make it an option.

[kfsone]

Do we have a place to store config options, such as "favor fuzzy finding". See *config file ancillary

"SYS/STN is inherently scopeable: resolve SYS first, then search stations within that system."

That's kinda what the current TradeORM does and I'm not totally digging it. If it's going to let me be a bit lazy, it needs to cope with things like "Alrai/finca" and find it ("Alrai Sector DB-X b1-6/Finca Of Pandora"); unlike my original "hamlinc" (dates back to the alpha) examples of ultra-perlian-laziness I think this is the kind of prefix-anchored base-line lookup you'd expect from a drop-down or autocomplete?

bikeshedding: yeah, I keep bouncing right off efforts to try and tackle this because there's a lot of functionality in that very large wall of original code, and for all we know I'm the only person who ever wants to be able to find unique stations by typing "hamlinc" type crap :)

class TradeORM:
  def lookup_name(self, needle: str, unit: Model, group: Model | None) -> Model | None:
     if (that := self._use_the_orm_luke(...)): return that
     for plugin in self.tdenv.search_plugins:
       if (that := plugin.run(needle, unit, group)): return that
     raise YaBooNoMatch
  def lookup_system(self, needle) -> orm.System|None: return self.lookup_name(needle, orm.System, None)
  def lookup_station(self, needle) -> orm.Station|None: return self.lookup_name(needle, orm.Station, orm.System)
  ...
  def lookup_item(self, needle) -> orm.Item|None: return self.lookup_name(needle, orm.Item, orm.Category)
  ...
  def lookup_ship(self, needle) -> orm.Ship|None: return ...

We might want like a beta/test release/branch to do this in so that we can actually have a workable version to check against. I was earnestly surprised by how much I leaned into providing exact names instead of relying on cryptic morse-signaling?

• Config-file ancillary:
  I know we have various config-like file options (although I'm not 100% familiar with any of them). Do we have a single, central config file? ~/.traderc? ~/.trade-dangerous/config.toml?

"TOML" seems to be what the kids like to use these days (*and is now batteries-included, first-class supported in python), and I kinda get why - it's like ini file but flexible; I'm slightly versed and slighty burned by yml; .ini is almost always an option but the kids tell me it doesn't hit right.

[Tromador]

Do we have a place to store config options, such as "favor fuzzy finding". See *config file ancillary

Yes, but I forget what it's called or how it works. @eyeonus will be able to answer that.

[kfsone]

There's probably a spin-off discussion to be had about establishing some sane default values to improve performance of things like "run"; for instance, having a sane "minimum profit" (gain) value.

I mean, I've been thinking: an item with a max-demand-price of 400cr can never meet a gain of 401cr. factor in the minimum supply-price and...

select item_id, min(supply_price) from stationitem where supply_price > 0 and supply_units > 0 group by 1
and
select item_id, max(demand_price) from stationitem where demand_price > 0 group by 1

(if we add 'units' to the supply index)

Does the few ms to query that give you a leg-up in reducing the search space for worthwhile trades?

(*** this is not a well thought out actionable just spitballing)

How is this relevant to TradeORM? Loosely, a general view towards "start from a higher bar" with TradeORM to emphasize performance for common use and give edge-cases a price rather than making all queries pay them?

If you wanna know how to get from 100 credits to 1000, don't be searching the entire galaxy.

[Tromador]

In my fork I now have all basic shapes in the new planner code running through tradeorm.py

1: Dedicated rocket speed single hop planner for both ends, one end or neither end anchored.
2: Multihop planner for the fully anchored case.
3: Multihop planner for either end anchored.
4: Multihop planner for neither end anchored.

In all cases, it's faster (often a LOT) faster. Queries all complete (reasonable ones, I've not tried 20 hops!) in places where current TD doesn't, or at least not in time I have patience for. Profit is comparable. Sometimes a bit less, often more.

As this covers all possible shapes and the rest is adding remaining filters, I think this discussion is now moot and so I'm closing it.